Database Engine Tuning Advisor gives non existent errors in SQL 2005

Hello,

I am sure you have heard of Community server - if not you are just using it ;)

I decided to try to optimise the performance of my site, run a trace and then DETA.

And I am getting errors like these:

---

E000 exec dbo.cs_user_Get @.UserName=N'jded',@.UserID=0,@.IsOnline=0,@.LastAction=N'',@.SettingsID=1000 122 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_user_Get'.
exec dbo.cs_thread_IsTracked @.ThreadID=5969,@.UserID=28236,@.SettingsID=1000,@.IsTracked=@.p4 output
select @.p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 declare @.p4 bit
set @.p4=0
exec dbo.cs_thread_IsTracked @.ThreadID=414,@.UserID=1001,@.SettingsID=1000,@.IsTracked=@.p4 output
select @.p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 exec dbo.cs_Section_GetSectionIDByPostID @.SettingsID=1000,@.PostID=44641 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Section_GetSectionIDByPostID'.

---

The "trouble" is that those sprocs do exist and that the site apparently is working fine. But not for DETA. As far as DETA is concerned... 54% of my processing power is used to serve syntax errors!

A couple of hints.
The database was an upgrade from 2000.:
- I changed the compatibility level to 2005 but no luck there. I tried with a brand new database, and the errors keep cropping up.
B. The errors were observed in a kit that comprises of a 32bit IIS and 64bit SQL2005 and thought that it had to do with the connectivity of those two.
- I run the traces in one (32bit) server that hosts both IIS and SQL and I am getting the same errors.

Any help will be greatly appreciated.

Thank you.

Which catelog are these stored proc resides? What is the catalog that you are using?|||

Hi!

The database name is CommunityServer - the sprocs reside in it.

It is referenced like this in the web.config:
<add key="SiteSqlServer" value="uid=userXXX;server=SUXXX;pwd=XXXXX;database=CommunityServer;Min Pool Size=5;Max Pool Size=5000;Connect Timeout=20;" />

Does this help you to help me?

Thank you,

Dimitris

|||

The issue has to do with the fact that we use ADO.NET to access the data whereas the tuning advisor uses SQL Native Client and thus it compares the actions to SQLNCLI's syntax.

The team of the tuning advisor should something about it.

|||

Can you please use the -d option (dta.exe) or "Database for Workload Analysis" option in UI to set the database context to the database that contains the stored procedures.

Thanks

Sanjay

|||What if the "Database for Workload Analysis" dropdown is way way way too small to see the entire database name? If I have 50 databases there and they all begin with the same first 20 characters, that's all I can see. How could I possible choose the correct database name in the dropdown?