Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Programming / SQL / July 2008

Tip: Looking for answers? Try searching our database.

OPENQUERY with stored procedures error

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Pr09 - 27 Jul 2008 06:19 GMT
I am currently using SQL Server 2005 Express Edition and SSMSE.  I have
developed a unit testing suite for my stored procedures.  The way it works
is, all of my tests (stored procedures on the database that call the actual
stored procedures to determine if they return the correct output) are
executed and their results are stored in a table to be queried from.  So
basically I write a test and then I write the stored procedure that the test
will verify.  Well some of my stored procs are simple select statements.  To
verify these, I need a way to call the stored procedure from within my test
stored procedure and iterate through the returned recordset.  I use a call to
open query for this:

SELECT @Count = count(*) FROM OPENQUERY(LOCALSERVER, 'EXEC
MyDb..mysp_SPToTest')

Now if I just execute this statement, their are no problems and @Count is
equal to the correct value.  However, when my test suite is run it goes
something like this:

EXEC Run (stored procedure) --> For every test, EXEC Test (stored procedure)
--> EXEC SPToTest (stored procedure)

The call to OPENQUERY is made from within the 'EXEC Test' stored procedure
and I am given an error of:

OLE DB provider "SQLNCLI" for linked server "LOCALSERVER" returned message
"Query timeout expired".
Msg 7399, Level 16, State 1, Procedure ut_Names_GetAll, Line 12
The OLE DB provider "SQLNCLI" for linked server "LOCALSERVER" reported an
error. Execution terminated by the provider because a resource limit was
reached.
Msg 7320, Level 16, State 2, Procedure ut_Names_GetAll, Line 12
Cannot execute the query "EXEC TMF_PMS..tmfsp_GetAllNames" against OLE DB
provider "SQLNCLI" for linked server "LOCALSERVER".

A few more facts:
LOCALSERVER is set as:

EXEC sp_addlinkedserver  
  @server='LOCALSERVER',
  @srvproduct='',
  @provider='SQLNCLI',
  @datasrc='.\SQLEXPRESS'
EXEC sp_serveroption 'LOCALSERVER', 'connect timeout', '10'
EXEC sp_serveroption 'LOCALSERVER', 'query timeout', '10'
EXEC sp_serveroption 'LOCALSERVER', 'collation compatible', 'true'

If I leave the query timeout to '0' then it just keeps the query stalled
until the 10 minute default query times out.  I found on the web an article
saying their may be trouble if port 1433 is blocked by your firewall, however
I have unblocked this at every level of my firewall that is available, so
this cannot be my problem.  How do I get around this query timeout or what
may be causing it?
Uri Dimant - 27 Jul 2008 07:38 GMT
Hi
http://support.microsoft.com/kb/314530
http://support.microsoft.com/kb/270119

>I am currently using SQL Server 2005 Express Edition and SSMSE.  I have
> developed a unit testing suite for my stored procedures.  The way it works
[quoted text clipped - 56 lines]
> this cannot be my problem.  How do I get around this query timeout or what
> may be causing it?
Plamen Ratchev - 27 Jul 2008 19:07 GMT
Not direct answer to your question, but rather a suggestion. You may want to
look at client side unit testing for stored procedures. I have done both
T-SQL and .NET client side unit testing and in my experience client side
provides more options and much easier to handle. Plus it can integrate
easily into some global application unit testing process.

Here are two articles by Alex Kuznetsov on the topic:
http://www.simple-talk.com/sql/t-sql-programming/close-those-loopholes---testing
-stored-procedures--/

http://www.simple-talk.com/sql/t-sql-programming/close-those-loopholes-stress-te
st-those-stored-procedures/


Plamen Ratchev
http://www.SQLStudio.com
Erland Sommarskog - 27 Jul 2008 23:01 GMT
> I am currently using SQL Server 2005 Express Edition and SSMSE.  I have
> developed a unit testing suite for my stored procedures.  The way it
[quoted text clipped - 6 lines]
> stored procedure from within my test stored procedure and iterate
> through the returned recordset.  I use a call to open query for this:

I agree with Plamen, do this client-side. OPENQUERY is going to buy you
a lot more troubles, you ain't seen nothing yet!

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.