Exporting result set of a stored procedure.

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)

Exporting result set of a stored procedure.

Author

Topic

chloee
Starting Member

13 Posts

Posted - 2004-07-07 : 08:28:47

I need to export to a text file the result set of a stored procedure. The procedure is dynamic (crosstab) and number of columns changes.

I tried BCP
exec master..xp_cmdshell 'bcp "exec db_name.dbo.sproc_name" queryout d:\foo.txt -Sserver -Ppass -Uuser -c'

but I can't get it to work with parameters.

Any idea how can I do that?

Thanks!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-07 : 08:51:14

How about Putting the results from the storedproc into a staging table and then using bcp to extract the values from the staging table?

Duane.

chloee
Starting Member

13 Posts

Posted - 2004-07-07 : 09:14:29

I could have done it if I knew the number of columns in the result set, but I don't... :-(

The only way I can think about it to create a linked server to my server and select into #temp from openquery (link_to_local_sql_server, 'exec sproc')

but I was hoping there is a more elegant solution.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-07 : 09:16:10

And if you drop & create or alter the table in the sp?

Duane.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-07 : 10:03:27

Chloee,

You say you could not exec the BCP with parameter's ... do you mean with dynamic paramater's, or did you test the bcp using hard-coded values?

this works:

master..xp_cmdshell 'bcp "EXEC Northwind..sp_Help Orders" queryout D:\Tax\order.txt -c -Snjros1d151\njros1d151dev -Usa -Ppwd'

 exec master..xp_cmdshell 'isql -E -S opsws148\opsws148 -d RG_Scrap_Pad -Q "exec sp_helplogins orders" -o c:\temp\sphelpl.txt'

Did you consider building up your bcp dynamically?


set @bcp='master..xp_cmdshell 'bcp "EXEC Northwind..sp_Help '+@parm+'" queryout D:\Tax\order.txt -c -Snjros1d151\njros1d151dev -Usa -Ppwd'
exec (@bcp)

or soemthing on that line ?
[/code]

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

chloee
Starting Member

13 Posts

Posted - 2004-07-07 : 17:33:49

the isql does it ok, but I can't figure out how to get it better formatted. Headers tab delimiter for columns and new line for rows. any idea how to do that?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-07 : 17:47:43

your sp could output headers and delimiters

select 'header1'+char(39)+'header2'...
union
select col1+char(39)+col2+char(39)...

chloee
Starting Member

13 Posts

Posted - 2004-07-07 : 17:55:12

won't this is work if they all have the same datatype?
Any idea how to get an output as nice as the bcp?
I am starting to think that my only choice is to create the table dynamically and bcp it...

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-07 : 20:04:47

Yeah, you would have to convert numeric data to varchar and put quotes around character data.

How to you pass parameters to openquery and get the results into a temp table?

chloee
Starting Member

13 Posts

Posted - 2004-07-08 : 05:09:18

You have to dynamically buils the string. but it will only work if the result set is fixed, meaning is number of columns is known :( and I did not test how does it affects performance.

BCP is not working because there is a global temp table in the stored procedure (with SET ANSI_WARNINGS OFF)

isql is working but I can't figure out how to format the output file.

HELP!!!

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-08 : 05:23:14

Here is a hack if it's the last resort ;)


if object_id('p_temp_bcp') is not null
    exec ('drop procedure p_temp_bcp')

declare @loginame sysname
declare @sql varchar(2000)
set @loginame = 'sa'
set @sql = 'create procedure p_temp_bcp as exec master.dbo.sp_who '+@loginame
exec (@sql)

SELECT * into #tmp FROM  OPENROWSET('SQLOLEDB', 'Trusted_Connection=yes;Data Source=myserver;', 'EXEC OD.dbo.p_temp_bcp') 
select top 1 * from #tmp

spid   ecid   status               ...
------ ------ ---------------------...
1      0      background           ...

chloee
Starting Member

13 Posts

Posted - 2004-07-08 : 05:48:27

Thanks Ken.
but it doesn't work. same problem. number and type of columns for sp_who are known so it doesn't give an error.
I have in my procedure a crosstab table

Here is the error I get

Could not process object 'EXEC db_name.dbo.sproc_name'. 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 db_name.dbo.sproc_name'].

There must be a way to do it!!! :#

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-08 : 05:51:39

What!?!? No procedures number of columns are known until runtime. Something else is different.

I think you are getting no rows rather than unknown column count.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-08 : 05:55:06

I have a theory.
Add to the openrowset

'SET FMTONLY OFF; EXEC OD.dbo.p_temp_bcp'

Openrowset may try to get column list prior to execution with.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-08 : 05:56:07

Chloee,

I'm not grasping exactly the concern with the output from the ISQL. Can you post an example ofwhat you are getting, and what you want ? Just short snippets.

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

chloee
Starting Member

13 Posts

Posted - 2004-07-08 : 06:55:46

Thanks Regan.

use pubs
create procedure bla
as
set nocount on
select * from authors

exec master..xp_cmdshell 'bcp "EXEC pubs..bla" queryout D:\authors_bcp.txt -c -Sservername -Usa -Ppass'
This is what I would like to have if possible with column names.
I need the file in excel format and this is the more simple way to get it (jsut rename it to .xls).

exec master..xp_cmdshell 'isql -E -S servername -d pubs -Q "exec bla" -o d:\authors_isql.txt'
here I get a messy file...

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-08 : 07:22:24

hmm - guess I need to go and recreate the pubs db ... will gte to it when i can...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 08:34:00

Can you output in an XML format that is friendly for Excel?

Perhaps save an existing "minimal" Excel file as XML to see what it genrates and use that as a template?

Kristen

chloee
Starting Member

13 Posts

Posted - 2004-07-09 : 05:34:24

I got rid of the temp table and got it to work. To get the column headers I used union all as Ken suggested. I had to change a few ints to varchar but it is only a staging table so no harm is done.
Thanks a lot for all the input!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-09 : 05:51:33

Happy days... glad the answer was found. Still using the ISQL method, btw?

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources