return linked server sp results into temporary table
-
Dear all,
I have an SQL2k box here in the UK and a linked server in Spain.
I can successfully execute a stored proc on the Spanish box and see the results in QA (results returned in 13 seconds, 1030 rows);
exec [<linked_server>].db.dbo.sp '2006-03-13', '2006-03-14', 1
What I need to do however is insert those results into a temp table such that I can union the results with another result set ( the final product of the query being a csv file generated through bcp).
The problem is that when I perform the following
create table ##temp
(
col_1 numeric(9,0) NULL
,col_2nvarchar(40) NULL
...
...
etc...
)
insert ##temp
exec [<linked_server>].<db>.dbo.sp '2006-03-13', '2006-03-14', 1
select * from ##temp
the query takes for ever ( the longest I've let it run is 15 minutes - it usually takes 13 seconds to return) - cancelling the query results in the following error returned in QA (approx 5 minutes after I hit the cancel button in QA);
Query cancelled by User
[Microsoft][ODBC SQL Server Driver]Operation canceled
[Microsoft][ODBC SQL Server Driver]Timeout expired
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Although QA reports link-failure I can then immediately execute the sp (without the insert into temp table) and see the results returned again in 13 seconds.
I've tried temp table (single-#) and global temp table as above (double-#) - but both behave the same - do I perhaps need to try a table variable?
please help!!!
-
I'm not sure of the cause of the problem, but try this:
- insert #tmp
- select * from openquery ( <linked_server> , 'exec <db>.dbo.sp ''2006-03-13'', ''2006-03-14'', 1' )
You might want to try it without the insert first.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Many thanks for the suggestion Tim,
However, the Spanish link has just gone down!, I'll try your suggestion as soon as possible....
-
Hi Tim,
connection re-established... reaults of using openquery as follows;
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'exec cd01.dbo.cli_ipack_uk '2006-03-13', '2006-03-14', 1'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=exec cd01.dbo.cli_ipack_uk '2006-03-13', '2006-03-14', 1'].
any clues?
cheers
-
Does the sp return any warnings or rowcounts when you run it in QA?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Hi Tim,
I've located the cause of the problem, the sp on the Spanish server is not a simple select, the final statements in the sp are;
...
--first part writes all query data to #TBRESUL
------------------------------
select * from #TBRESUL
COMMIT TRAN
RETURN 0
ON_ERROR:
ROLLBACK TRAN
RETURN (@RESUL)
GO
the problem seems to be because the final statement in the sp is returning a single value rather than a recordset, which ties in with the error message.
Cheers,
Paul
-
That's a return value, which is placed directly into a variable in the execute statement if one is specified. It's not output to the client automatically.
Actually, ODBC will take the first output from the sp. This could be a warning (e.g. null values eliminated from aggregate) or a rowcount, or the output of a 'print' statement. You should be able to set ansi_warnings off and nocount on in the string you pass to OPENQUERY, occurring directly before the procedure call. You would have to get rid of any 'print' statements from the sp.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Hi Tim, have tried "set nocount on" ( openquery would not allow set ansi_warnings off) with the same results, complete syntax of the remote sp as follows;
CREATE PROCEDURE CLI_IPACK_UK
(
@INICIO DATEtime,
@FIN as datetime,
@Registrar bit=0
)
AS
DECLARE @RESUL AS INT
BEGIN TRAN
SELECT c.col1, c.col2 ...etc...
INTO #TBRESUL
FROM CLIENTES as c
JOIN MARKETING AS M on m.num_cliente=c.num_cliente
LEFT JOIN PROVINCIAS AS P on c.provincia=p.id_provincia
left join titulos_pais as tp on tp.id_titulo=c.id_titulo and tp.id_pais=dbo.pais_centro(c.id_centro)
left join medios_publicidad_trad as mp on mp.id_medio=c.id_medio and mp.id_idioma=7
left join medios_publicidad2_trad as mp2 on mp2.id_medio2=c.id_medio2 and mp.id_idioma=7
WHERE m.ipack=1 AND C.BLOQUEADO=0 AND DBO.PAIS_CENTRO(C.ID_cENTRO)=5
and m.fipack between @inicio and @fin
SET @RESUL=@@ERROR
IF @RESUL <>0 GOTO ON_ERROR
if @Registrar=1
begin
declare @IdEnvio as int
declare @Registros as int
select @Registros=count(*) from #TBRESUL
INSERT INTO ENV_ENVIOS
(col_list...) VALUES
(values_list... )
SET @RESUL=@@ERROR
IF @RESUL <>0 GOTO ON_ERROR
select @IdEnvio=@@IDENTITY
INSERT INTO ENV_CLIENTES_ENVIOS (cols...)
SELECT <cols...> FROM #TBRESUL
SET @RESUL=@@ERROR
IF @RESUL <>0 GOTO ON_ERROR
end
------------------------------
select * from #TBRESUL
COMMIT TRAN
RETURN 0
ON_ERROR:
ROLLBACK TRAN
RETURN (@RESUL)
GO
...does this shed any light?, I have tried executing the sp with @Registrar = 0 and @Registrar = 1, and the results are the same....
Many thanks for your help so far,
Cheers,
Paul
-
One last shot in the dark - if you can amend the sp, try putting the select before the inserts...
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
I think you need to use the following :
- insert #tmp
- select * from openquery ( <linked_server> , 'SET FMTONLY ON; exec <db>.dbo.sp ''2006-03-13'', ''2006-03-14'', 1' )
* Noel -
Did you try OPENROWSET?
Also, going back to the original problem, I had problems with Distributed Transaction Coordinator message "Unable to participate in Distributed Transaction" when going towards some servers with everything fine with other servers. This is exactly when I wanted to insert returned data into temp. table. Because if it is only Select, it works, but if it is Insert...Select, it is already a distributed transaction. I was able to run my OPENROWSET queries towards all servers only when I was running from SQL Server 2005.
Regards,Yelena Varsha
-
Many thanks for all reponses;
the suggestion from noeld:
insert #tmp
select * from openquery ( <linked_server> , 'SET FMTONLY ON; exec <db>.dbo.sp ''2006-03-13'', ''2006-03-14'', 1' )
resulted in:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'SET FMTONLY ON; exec cd01.dbo.cli_ipack_uk '2006-03-14', '2006-03-14', 0'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=SET FMTONLY ON; exec cd01.dbo.cli_ipack_uk '2006-03-14', '2006-03-14', 0'].
the suggestion from Yelena Varshal to use OPENROWSET resulted in the same message:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'exec cd01.dbo.cli_ipack_uk '2006-03-14', '2006-03-14', 1'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=exec cd01.dbo.cli_ipack_uk '2006-03-14', '2006-03-14', 1'].
Unless anyone out there has an answer to this I will have to re-write the remote sp to suit,
many thanks,
Paul
-
Make sure also that the first statement of your proc is
SET NOCOUNT ON
* Noel -
Thanks Noel,
ufortunately this results in the same error message - I will have to have our Spanish friends re-write the stored procedure.
Cheers,
Paul
-
Paul,
I believe that definitely a re-write is definitely the only posibility
If you can, keep us posted!
* Noel
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply