OPENQUERY with stored procedures error

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?

Hi
http://support.microsoft.com/kb/314530
http://support.microsoft.com/kb/270119


"Pr09" wrote in message

>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?