Solved

Problems with sp_executesql

Posted on 2010-08-24
4
873 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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

717 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