SQL2005 Export Stored Proc Result using Parameters

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

General SQL Server Forums

New to SQL Server Programming

SQL2005 Export Stored Proc Result using Parameters

Author

Topic

CiaranEire
Starting Member

7 Posts

Posted - 2014-03-26 : 15:55:52

Hi there, I am a complete novice when it comes to SQL and this is my first post so thanks in advance if you choose to continue reading. I am trying to export the result of a stored procedure from SQL Server Management Studio Express 2005 to a CSV file. I have tried using code posted by user sunitabeck at [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=162397[/url] but cannot get it to work while passing a required parameter (@DataXML) to the stored procedure. Here is my code and the resultant error message:


DECLARE @Data varchar (685)
DECLARE @sql varchar (8000)

SET @Data = '<XML_DATA_IS_HERE>'
SELECT @sql = 'bcp "exec MYDBASE..MYSTOREDPROC @DataXML=@Data" '
+ 'queryout c:\bcp\sysobjects.csv -c -t, -T -S'
+ @@servername
EXEC master..xp_cmdshell @sql

quote:

1. SQLState = 42000, NativeError = 137
2. Error = [Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@Data".
3. SQLState = 42000, NativeError = 8180
4. Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
5. NULL

Please note that I am unable to change the stored procedure in any way (such as to produce an Output parameter). Any help is much appreciated, even if only to say that what I'm trying isn't possible or should be done another way.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-26 : 16:58:22

DECLARE @Data varchar (685)
DECLARE @sql varchar (8000)

SET @Data = '<XML_DATA_IS_HERE>'
SELECT @sql = 'bcp "exec MYDBASE..MYSTOREDPROC @DataXML=''' + @Data + '''" '
+ 'queryout c:\bcp\sysobjects.csv -c -t, -T -S'
+ @@servername
EXEC master..xp_cmdshell @sql

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

CiaranEire
Starting Member

7 Posts

Posted - 2014-03-27 : 10:42:42

quote:

DECLARE @Data varchar (685)
DECLARE @sql varchar (8000)

SET @Data = '<XML_DATA_IS_HERE>'
SELECT @sql = 'bcp "exec MYDBASE..MYSTOREDPROC @DataXML=''' + @Data + '''" '
+ 'queryout c:\bcp\sysobjects.csv -c -t, -T -S'
+ @@servername
EXEC master..xp_cmdshell @sql

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi Tara, thanks for responding. Unfortunately, SQL now throws the below error using your solution. Is my bcp sytax incorrect? I really appreciate the help.


1. Copy direction must be either 'in', 'out' or 'format'.
2. usage: bcp {dbtable | query} {in | out | queryout | format} datafile
3.  [-m maxerrors]            [-f formatfile]          [-e errfile]
4.  [-F firstrow]             [-L lastrow]             [-b batchsize]
5.  [-n native type]          [-c character type]      [-w wide character type]
6.  [-N keep non-text native] [-V file format version] [-q quoted identifier]
7.  [-C code page specifier]  [-t field terminator]    [-r row terminator]
8.  [-i inputfile]            [-o outfile]             [-a packetsize]
9.  [-S server name]          [-U username]            [-P password]
10. [-T trusted connection]   [-v version]             [-R regional enable]
11. [-k keep null values]     [-E keep identity values]
12. [-h "load hints"]         [-x generate xml format file]
13. NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-27 : 11:42:12

Add PRINT @sql to your script and run the script. Show us the output of @sql.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

CiaranEire
Starting Member

7 Posts

Posted - 2014-03-27 : 12:11:12

When I put PRINT @sql on the last line of my script, result tab is the same and message tab shows


(13 row(s) affected)
bcp "exec QCS..SP_Get_Sales @DataXML='<XML_DATA_IS_HERE>'" queryout c:\bcp\sysobjects.csv -c -t, -T -SMIDOFFICE

I'm using "XML_DATA_IS_HERE" as it's sensitive info and also because the script does work when not using bcp.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-27 : 13:05:07

The problem might be in the xml string for bcp though. Try this though:

DECLARE @Data varchar (685)
DECLARE @sql varchar (8000)

SET @Data = '<XML_DATA_IS_HERE>'
SELECT @sql = 'bcp "exec MYDBASE..MYSTOREDPROC ''' + @Data + '''" '
+ 'queryout c:\bcp\sysobjects.csv -c -t, -T -S'
+ @@servername
EXEC master..xp_cmdshell @sql

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

CiaranEire
Starting Member

7 Posts

Posted - 2014-03-27 : 13:26:36

quote:
Originally posted by tkizer

The problem might be in the xml string for bcp though. Try this though:

DECLARE @Data varchar (685)
DECLARE @sql varchar (8000)

SET @Data = '<XML_DATA_IS_HERE>'
SELECT @sql = 'bcp "exec MYDBASE..MYSTOREDPROC ''' + @Data + '''" '
+ 'queryout c:\bcp\sysobjects.csv -c -t, -T -S'
+ @@servername
EXEC master..xp_cmdshell @sql

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi Tara, I get the exact same result with that code. I thought the XML wouldn't be the issue as it worked before but below is my full query just in case it could be the problem (plus it isn't actually sensitive info per se). There aren't any line breaks in the @DataXML in my actual query, I put them in below for readability. Apart from that it's exactly what I have.
WORKING, OUTPUT TO SQL:


DECLARE @DF varchar(19)
DECLARE @DT varchar(19)
DECLARE @Data varchar (673)
DECLARE @RC int

SET @DF = convert(varchar, dateadd(day,datediff(day,1,getdate()),0), 120)
SET @DT = convert(varchar, dateadd(s,-1,dateadd(day,datediff(day,0,GETDATE()),0)), 120)
SET @Data = '<RequestData RequestID="TMPEDT"> <CustomData > <JournalOptionsTemplate Description="test" Period="Yesterday" 
DateTimeFrom="' + @DF + '" DateTimeTo="' + @DT + '" Is24Hour="0" Is24HourEOD="0" IsRealTime="0" RealTimeInterval="5" 
IsLimitListSize="0" LimitListSize="100" ShowOnlyTotals="0" ReceiptNoFrom="" ReceiptNoTo="" CardNoFrom="" CardNoTo="" 
IsTotalValue="1" IsTotalQuantity="1" IsValueTotal="1" IsQuantityTotal="1" Report="0" SiteID="00135" SiteDesc="00135" 
OnlyActiveCashiers="1" >  <PaidInReasons>  </PaidInReasons>  <PaidOutReasons>  </PaidOutReasons>  <DiscountReasons>  </DiscountReasons>  
</JournalOptionsTemplate>  </CustomData>  </RequestData>'

EXECUTE @RC = [QCS].[dbo].[SP_Totals_GetX] 
   @DataXML = @Data

NOT WORKING, OUTPUT TO CSV:


DECLARE @DF varchar(19)
DECLARE @DT varchar(19)
DECLARE @Data varchar (685)
DECLARE @sql varchar (8000)

SET @DF = convert(varchar, dateadd(day,datediff(day,1,getdate()),0), 120)
SET @DT = convert(varchar, dateadd(s,-1,dateadd(day,datediff(day,0,GETDATE()),0)), 120)
SET @Data = '<RequestData RequestID="TMPEDT"> <CustomData > <JournalOptionsTemplate Description="test" Period="Yesterday" 
DateTimeFrom="' + @DF + '" DateTimeTo="' + @DT + '" Is24Hour="0" Is24HourEOD="0" IsRealTime="0" RealTimeInterval="5" 
IsLimitListSize="0" LimitListSize="100" ShowOnlyTotals="0" ReceiptNoFrom="" ReceiptNoTo="" CardNoFrom="" CardNoTo="" 
IsTotalValue="1" IsTotalQuantity="1" IsValueTotal="1" IsQuantityTotal="1" Report="0" SiteID="00135" SiteDesc="00135" 
OnlyActiveCashiers="1" >  <PaidInReasons>  </PaidInReasons>  <PaidOutReasons>  </PaidOutReasons>  <DiscountReasons>  </DiscountReasons>  
</JournalOptionsTemplate>  </CustomData>  </RequestData>'

SELECT @sql = 'bcp "exec QCS..SP_Totals_GetX ''' + @Data + '''" '
+ 'queryout c:\bcp\sysobjects.csv -c -t, -T -S'
+ @@servername
EXEC master..xp_cmdshell @sql

Again, I really am grateful for the help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-27 : 13:33:23

Show us the output of PRINT @sql with that.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

CiaranEire
Starting Member

7 Posts

Posted - 2014-03-27 : 13:59:06

quote:
Originally posted by tkizer

Show us the output of PRINT @sql with that.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi, here is the PRINT OUTPUT of your second suggestion:
[code]
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]

CiaranEire
Starting Member

7 Posts

Posted - 2014-03-27 : 14:01:04

...
NULL

MESSAGES


(13 row(s) affected)
bcp "exec QCS..SP_Totals_GetX @DataXML='<RequestData RequestID="TMPEDT"> <CustomData > <JournalOptionsTemplate Description="Z-READ YESTERDAY" Period="Yesterday" DateTimeFrom="2014-03-26 00:00:00" DateTimeTo="2014-03-26 23:59:59" Is24Hour="0" Is24HourEOD="0" IsRealTime="0" RealTimeInterval="5" IsLimitListSize="0" LimitListSize="100" ShowOnlyTotals="0" ReceiptNoFrom="" ReceiptNoTo="" CardNoFrom="" CardNoTo="" IsTotalValue="1" IsTotalQuantity="1" IsValueTotal="1" IsQuantityTotal="1" Report="0" SiteID="00135" SiteDesc="00135" OnlyActiveCashiers="1" >  <PaidInReasons>  </PaidInReasons>  <PaidOutReasons>  </PaidOutReasons>  <DiscountReasons>  </DiscountReasons>  </JournalOptionsTemplate>  </CustomData>  </RequestData>'" queryout c:\bcp\sysobjects.csv -c -t, -T -SMIDOFFICE

Apologies for edited post above, wanted to clarify situation fully.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-27 : 14:04:44

It's the double quotes that are doing it. As soon as it finds the first double quote in @DataXML, it thinks it's the end of your stored procedure call. I am not sure how to resolve that, except by hiding it inside another stored procedure. Is the stored procedure just a select statement or is it more complex?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

CiaranEire
Starting Member

7 Posts

Posted - 2014-03-27 : 14:34:59

quote:
Originally posted by tkizer

It's the double quotes that are doing it. As soon as it finds the first double quote in @DataXML, it thinks it's the end of your stored procedure call. I am not sure how to resolve that, except by hiding it inside another stored procedure. Is the stored procedure just a select statement or is it more complex?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

It's a complex procedure ~1700 lines. I manage the back-end operations of a small group of convenience stores (I don't have an IT background).

The procedure itself pulls data from various tables in a back-office database and produces a totals report of cash, credit cards, sales etc. I'm trying to get this into CSV format as the report is currently only available from within our back-office software application, with no usable method of exporting it to file.

I will look into modifying the code to use a second stored procedure as you've suggested. I will post any updates or, hopefully, a solution in this thread. For now though, thanks so much Tara for looking at this, I don't want to take too much of your time as the help I need may be a bit too much to ask from free forum contributors

Ciaran

PS
How on earth do I get it to start a new line for my posts? They look fine in preview until I post and you have to scroll across to read.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-27 : 14:40:31

You're welcome, glad to help. Good luck!

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources