Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 884
  • Last Modified:

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?
0
Frans_Truyens
Asked:
Frans_Truyens
  • 2
  • 2
1 Solution
 
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
 
Guy Hengel [angelIII / a3]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
 
Frans_TruyensAuthor Commented:
thanks for the help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now