VBA ADO 'Invalid Object Name' Error; No Error SQL Server Management Studio

Question

I created a function in SQL Server 2008.

I can run both of these statements in SSMS without errors:

SELECT * FROM myFunction(myParam1,'myParam2');
SELECT * FROM dbo.myFunction(myParam,'myParam2');

But this in VBA gives a 'Invalid Object' error on 'myFunction' when executing the query:

Set db = New adodb.Connection
db.Open "Provider=SQLNCLI10;Data Source=aaaaa;Initial Catalog=db_ferm;Trusted_Connection=yes;"

Sql = "SELECT * FROM myFunction(" & theParam & ",'" & theParam2 & "') "
Set GetReport = db.Execute(Sql)

These 'Invalid Object Name' errors suddenly started showing up today. Even now in SSMS, I will get them occasionally and have to reset the Intellisense Cache.

Ideas?

Jacob Young · Accepted Answer · 2013-02-14 06:23:47Z

3

Rookie SMSS mistake!

When I created the function in SMSS, I had 'master' selected in the database dropdown menu (not sure how that happened) and all my SMSS query testing against the function was done in 'master'. My ADO connection string was to another database. So it couldn't find the function.

answered Feb 14, 2013 at 6:23

Jacob Young

4044 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA ADO 'Invalid Object Name' Error; No Error SQL Server Management Studio

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related