CODEWITHSUNDEEP
sql-server-2008exceladovba
Jacob Young
Jacob Young

Reputation: 404

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

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?

Upvotes: 1

Views: 3343

Answers (1)

Jacob Young
Jacob Young

Reputation: 404

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.

Upvotes: 3

Related Questions