stored procedure

[Mónica]

I am trying to store the turn out of a stored procedure in a temporary
table to make filters. I do it of the following way:

SELECT a.*
FROM OPENROWSET('SQLOLEDB', 'servidor';'user';'pwd' , 'EXEC
VERT_PERIODOS_CCV
''07/06/2003'',''31/12/2003'',''49'','''',''1'','''','''',''''') AS a

but it gives back the following error to me:

Servidor: mensaje 7357, nivel 16, estado 2, línea 1
No se puede procesar el objeto 'EXEC VERT_PERIODOS_CCV
'07/06/2003','31/12/2003','49','','1','','','''. El proveedor OLE DB
'SQLOLEDB' indica que el objeto no tiene columnas.
Traza de error de OLE DB [Non-interface error: OLE DB provider unable to
process object, since the object has no columnsProviderName='SQLOLEDB',
Query=EXEC VERT_PERIODOS_CCV
'07/06/2003','31/12/2003','49','','1','','','''].

Somebody knows why?

Thanks

Mónica


--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

[Erland Sommarskog]

[posted and mailed, please reply in news]

Mónica (mv******@hotmail.com) writes:

I am trying to store the turn out of a stored procedure in a temporary
table to make filters. I do it of the following way:

SELECT a.*
FROM OPENROWSET('SQLOLEDB', 'servidor';'user';'pwd' , 'EXEC
VERT_PERIODOS_CCV
''07/06/2003'',''31/12/2003'',''49'','''',''1'','''','''',''''') AS a

but it gives back the following error to me:

Servidor: mensaje 7357, nivel 16, estado 2, línea 1
No se puede procesar el objeto 'EXEC VERT_PERIODOS_CCV
'07/06/2003','31/12/2003','49','','1','','','''. El proveedor OLE DB
'SQLOLEDB' indica que el objeto no tiene columnas.
Traza de error de OLE DB [Non-interface error: OLE DB provider unable to
process object, since the object has no columnsProviderName='SQLOLEDB',
Query=EXEC VERT_PERIODOS_CCV
'07/06/2003','31/12/2003','49','','1','','','''].

Somebody knows why?

When you run a query through OPENROWSET or OPENQUERY, the query is in
fact run twice. First SQLOLEDB runs it with SET FMTONLY ON to detect
any results sets from the query, after which SQLOLEDB runs the query
with SET FMTONLY to actually execute it. In the mode FMTONLY ON, SQL
Server does not execute the statements, but just sifts through them
to find the result sets.

Unfortunately there are situations when SQL Server does not find any
result sets, and the most typical is when you create a temp table in
the procedure. Since the procedure is created in NOEXEC mode, the
table is not created, so there you go.

One way to cirumvent this is to use a table variable instead. This
should work, although I cannot recall whether I actually tested it.

You can also fool SQLOLEDB by adding SET FMTONLY OFF first to
the remote statement, but notice that this means that the procedure
is actually executed twice.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp