OPENROWSET pb with Exec - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

OPENROWSET pb with Exec

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2005-10-17 : 08:02:48

I would like to use the result of a sp (it returns a select based on certain parameters) in a DTS in order to export the data in CSV. The DTS package doesn't accept the sp call directly because it cannot determine the column names (my analyse).

The following works with Query Analyser (but not in the DTS)
GFS_LUX_DB.dbo.usp_A_Table @Mytable='Rates',@OnlyFields='CURRENCY', @WhereClause=''

In order to avoid the problem, I am trying to use OPENROWSET but
it fails

SELECT *
FROM OPENROWSET('SQLOLEDB','lux2252';'User_Read_Only';'password',
' GFS_LUX_DB.dbo.usp_A_Table @Mytable=''Rates'',@OnlyFields=''CURRENCY'', @WhereClause=''''')

--the same procedure fails
I looked with the profiler and it does the following

declare @P1 int
set @P1=4
exec sp_prepare @P1 output, NULL, N'exec GFS_LUX_DB.dbo.usp_A_Table @Mytable=''Rates'',@OnlyFields=''CURRENCY'', @WhereClause='''',1'
select @P1

--when running it again in the query analyser I got

Server: Msg 8179, Level 16, State 2, Procedure sp_prepare, Line 3
Could not find prepared statement with handle 4.

(1 row(s) affected)

Any clue?
Thanks, Mathias

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources