Solved

Problems with sp_executesql

Posted on 2010-08-24
4
866 Views
Last Modified: 2013-12-25
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
Comment
Question by:Frans_Truyens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33511185
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
 

Author Comment

by:Frans_Truyens
ID: 33511675
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33511798
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
 

Author Closing Comment

by:Frans_Truyens
ID: 33519696
thanks for the help
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question