?
Solved

Problems with sp_executesql

Posted on 2010-08-24
4
Medium Priority
?
877 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 1000 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

770 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