Problems with sp_executesql

If I am executing a query in MS Query Analyzer it runs in a few seconds. But when I want an ADO recordset of that query, it gives a timeout after 60 seconds.

I remarked that if I execute the query myself in MS Query it looks like this:
SELECT * FROM Customers

But when I intercept the query in Profiler, it looks like this:
exec sp_executesql N'(SELECT * FROM Customers (nolock))' And then it times out

When I add ‘exec sp_executesql’ in front of my query in MS Query Analyzer, it also gives timeouts.

Who can I avoid that MS SQL Server adds this command in front of my query?
Frans_TruyensAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I see, I misunderstood your statement.

what is your connection string, aka how exactly do you run that query in your vb code, please?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot change that behaviour . that's the way sql query analyzer works ...

now, if that query times out, you have a general resource issue on your database, you should check that.
do you really need "all customers", and filter actually on the recordset?
if yes: don't do it that way.
0
 
Frans_TruyensAuthor Commented:
Angellll,
That is not an answer. It is not the database, it is only for that query. Also it works fine through Query Analyzer, but when VB code adds 'exec sp_executesql ' in front of the SQL statement, it gets horribly slow. It isn't an easy one.
0
 
Frans_TruyensAuthor Commented:
thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.