export file using xp_cmmdshell

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 2005 Forums

Transact-SQL (2005)

export file using xp_cmmdshell

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-09 : 12:15:52

I am trying to save the result of the below SP to .csv file in the directory : \\production\Slo\xSlo Import\Import\Invt\ using Xp_cmdshell

Create procedure RAIInvtltemsScript ( @siteid varchar(5) )
AS

Select 'LEVEL0',
rtrim(InvtID) as InvtID,
'@siteid' as SiteID,
'SR01' as DfltBin1,
'SR01' as DfltBin2,
'SR01' as DfltBin3,
'SR01' as DfltBin4,
rtrim(CogsAcct) as COGSAcct,
'@siteid' as COGSSub,
rtrim(DfltSalesAcct) as SalesAcct,
'@siteid' as SalesSub,
rtrim(InvtAcct) as InvtAcct,
'@siteid' as InvtSub,
rtrim(Supplr1) as PrimVendID,
rtrim(SupplrItem1) as VendPartNum,
'0' as ReOrdQty,
'0' as ReOrdPt
From inv

the file name should be INB + @sitenme + timestamp.txt

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-09 : 12:29:27

look at "bcp utility" in BOL

www.elsasoft.org

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-09 : 14:11:27

Put your query into a view and then bcp out the view. bcp.exe usually can't handle queries like this.

EDIT: You deleted your last post which is what I'm referring to in this post.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-09 : 14:14:59

thanks Tara,

I can create the below SP without an error:

alter procedure RAIInvtltemsScript ( @siteid varchar(5) )
as

Declare @sql varchar(8000)

begin

select
@sql = 'bcp "select ''\"LEVEL0\"'',''\"InvtID\"'',''\"SiteID\"'',''\"DfltBin1\"'',''\"DfltBin2\"'',''\"DfltBin3\"'', ''\"DfltBin4\"'',''\"COGSACCT\"'' ,''\"COGSSub\"'',''\"SalesAcct\"'' ,''\"SalesSub\"'' ,''\"InvtAcct\"'',''\"InvtSub\"'',''\"PrimVendID\"'' ,''\"VendPartNum\"'',''\"ReOrdQty\"'' ,''\"ReOrdPt\"'' union all select ''\"''+ ''LEVEL0''+''\"'', ''\"''+ rtrim(InvtID) + ''\"'', ''\"''+ STUFF(rtrim(ltrim(''' +@siteid +''')), 4, 0, ''-'') + ''\"'', ''\"'' + ''SR01'' + ''\"'', ''\"'' + ''SR02'' + ''\"'' , ''\"'' + ''SR03'' + ''\"'', ''\"'' + ''SR04'' + ''\"'' , ''\"''+ rtrim(CogsAcct) + ''\"'',''\"'' + '''' + ''\"'', ''\"''+ rtrim(DfltSalesAcct) + ''\"'' , ''\"'' + '''' + ''\"'', ''\"''+ rtrim(InvtAcct) + ''\"'' , ''\"'' + '''' + ''\"'' ,''\"''+ rtrim(Supplr1) + ''\"'' , ''\"''+ rtrim(SupplrItem1) + ''\"'' , ''\"''+ ''0'' + ''\"'', ''\"''+ ''0'' + ''\"'' from Inventory ''''" queryout "
\\Sol\import\' + RTRIM(@siteid) + '.txt" -c -t "," -SNSHPWSOL01 -Ujung -Ppassword'

exec master..xp_cmdshell @sql
End

But if I execute the SP, I keep getting an error

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Password:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ''.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

--this is Print @sql

bcp "select '\"LEVEL0\"','\"InvtID\"','\"SiteID\"','\"DfltBin1\"','\"DfltBin2\"','\"DfltBin3\"', '\"DfltBin4\"','\"COGSACCT\"' ,'\"COGSSub\"','\"SalesAcct\"' ,'\"SalesSub\"' ,'\"InvtAcct\"','\"InvtSub\"','\"PrimVendID\"' ,'\"VendPartNum\"','\"ReOrdQty\"' ,'\"ReOrdPt\"' union all select '\"'+ 'LEVEL0'+'\"', '\"'+ rtrim(InvtID) + '\"', '\"'+ STUFF(rtrim(ltrim('A5101')), 4, 0, '-') + '\"', '\"' + 'SR01' + '\"', '\"' + 'SR02' + '\"' , '\"' + 'SR03' + '\"', '\"' + 'SR04' + '\"' , '\"'+ rtrim(CogsAcct) + '\"','\"' + '' + '\"', '\"'+ rtrim(DfltSalesAcct) + '\"' , '\"' + '' + '\"', '\"'+ rtrim(InvtAcct) + '\"' , '\"' + '' + '\"' ,'\"'+ rtrim(Supplr1) + '\"' , '\"'+ rtrim(SupplrItem1) + '\"' , '\"'+ '0' + '\"', '\"'+ '0' + '\"' from Inventory ''" queryout "
\\Sol\import\A5101.txt" -c -t "," -SNSHPWSOL01 -Ujung -Ppassword

what am i doing wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-09 : 14:20:52

Creating a stored procedure successfully doesn't mean that it works correctly. You need to create a view with your select query and then use that view for the queryout.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-09 : 14:25:34

quote:
Originally posted by tkizer

Creating a stored procedure successfully doesn't mean that it works correctly. You need to create a view with your select query and then use that view for the queryout.

Tara Kizer

thanks can you show me some code example how to do that?
Is is going to try to create a view everytime users run this query?

Kristen
Test

22859 Posts

Posted - 2007-01-09 : 14:25:50

All that escaping is a nightmare

Tara's approach is easier IMHO:

CREATE VIEW MyView
AS
SELECT Col1, Col2, Col3
FROM MyTable

then

bcp "select * from MyView ORDER BY Col1" queryout "\\Sol\import\A5101.txt" -c -t "," -SNSHPWSOL01 -Ujung -Ppassword

should do the trick (untested though, and not very carefully checked either!)

Kristen

jung1975
Aged Yak Warrior

503 Posts

Posted - 2007-01-09 : 15:44:49

thanks.. I've changed the quey

alter procedure RAIInvtltemsScript ( @siteid varchar(5) )
as

Declare @sql varchar(8000)

begin

Select 'LEVEL0' as LEVEL0,
rtrim(InvtID) as InvtID,
stuff(@siteid, 4,0, '-') as SiteID,
'SR01' as DfltBin1,
'SR01' as DfltBin2,
'SR01' as DfltBin3,
'SR01' as DfltBin4,
rtrim(CogsAcct) as COGSAcct,
@siteid as COGSSub,
rtrim(DfltSalesAcct) as SalesAcct,
@siteid as SalesSub,
rtrim(InvtAcct) as InvtAcct,
@siteid as InvtSub,
rtrim(Supplr1) as PrimVendID,
rtrim(SupplrItem1) as VendPartNum,
'0' as ReOrdQty,
'0' as ReOrdPt

into #temp1
From inventory

select
@sql ='bcp "select ''*'' from #temp1 ''" queryout "\\Sol\import\' + RTRIM(@siteid) + '.txt" -c -t "," -SNQLS -Ujung -Ppassword'

exec master..xp_cmdshell @sql

End

but still getting an error:

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQLState = 37000, NativeError = 105
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ''.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-09 : 15:49:12

That isn't going to work. xp_cmdshell won't have access to your temp table. Use a view!

Also, test the view via the command line rather than Query Analyzer. You are over-complicating the testing right now by putting it in xp_cmdshell.

Tara Kizer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-09 : 15:51:46

I don't understand why the extra double quotes. It should look more like this:
'bcp "select * from ViewName" queryout \\Sol\import\' + RTRIM(@siteid) + '.txt -c -t, -SNQLS -T'

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources