| Author |
Topic |
|
killtacularmania
Starting Member
22 Posts |
 Posted - 2010-04-26 : 14:29:59
|
| Does anyone know a quick way to export a query result in sql 2005 to a pipe delimited file using either a sql script or a store procedure? Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
killtacularmania
Starting Member
22 Posts |
Posted - 2010-04-26 : 15:21:44
|
| Can you explain what the last portion -Sserver1\instance1 -T -t| -c -r\r\n' means? Thanks |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
killtacularmania
Starting Member
22 Posts |
Posted - 2010-04-27 : 08:03:48
|
| If I run the line below I get two messages.EXEC master.dbo.xp_cmdshell 'bcp PMHSQL01.dbo.Staging out \\PMHSQL01\d$\somefile.txt -Smyserver -Userverusername -Pserverpw -t| -c -r\r\n'I get '-c' is not recognized as an internal or external commandand the second message I get is operable program or batch file.Thanks |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-27 : 08:45:39
|
| You may want to edit that last post, and remove the sa password. :)------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
killtacularmania
Starting Member
22 Posts |
Posted - 2010-04-27 : 10:29:18
|
| Come on im not that dumb ;-) that is not the real server name nor password lol |
|
|
|
killtacularmania
Starting Member
22 Posts |
Posted - 2010-04-27 : 10:30:33
|
| is that better dba? haha |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-27 : 10:42:42
|
quote:
Originally posted by killtacularmania
Come on im not that dumb ;-) that is not the real server name nor password lol
It wouldn't be the first time I've seen sensitive details accidentally posted on the internet.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
killtacularmania
Starting Member
22 Posts |
Posted - 2010-04-27 : 10:46:52
|
| I hear you.....I am still have this issue does anyone know what I need to change in my script? |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-27 : 11:04:27
|
quote:
Originally posted by killtacularmania
I hear you.....I am still have this issue does anyone know what I need to change in my script?
Sorry, I meant to put this in my first post. You have to put double quotes around the pipe character, otherwise it will be interpreted as a pipe by the cmd interpreter. What you typed in tells the cmd interpreter to take the output of "bcp PMHSQL01.dbo.Staging out \\PMHSQL01\d$\somefile.txt -Smyserver -Userverusername -Pserverpw -t" and pipe it to the program "-c" with the params "-r\r\n". Use this instead:EXEC master.dbo.xp_cmdshell 'bcp PMHSQL01.dbo.Staging out \\PMHSQL01\d$\somefile.txt -Smyserver -Userverusername -Pserverpw -t"|" -c -r\r\n'------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-27 : 12:31:56
|
| FYI. The clue was in the error message:'-c' is not recognized as an internal or external commandoperable program or batch file.That's the same error you get at the dos prompt if you type an unknown command. So it was trying to run a program called "-c". The pipe character was immediately before the -c, so it was being interpreted as a command line pipe.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
killtacularmania
Starting Member
22 Posts |
Posted - 2010-04-28 : 07:14:59
|
| One last question....I want to be able to define what the file name should be...can you guys alter the code below so this will work? I get an error "Incorrect syntax near '&'"Declare @TextName varchar(30)Declare @FileName varchar(60)Set @TextName = '\\myserver\e$\myfolder'Set @FileName = @TextName+CONVERT(varchar(12), GetDate(), 101)+'.txt'EXEC master.dbo.xp_cmdshell 'bcp MyDatabase.dbo.MyTable out' & @FileName & '-Smyserver -Uusername -Ppassword -t"|" -c -r\r\n' |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-28 : 08:06:59
|
| The string concatenation operator in TSQL is + not &. Replace & with +NOTE: You should probably have a \ after myfolder.Also, style 101 of the convert function will give you date in the format of mm/dd/yyyy, which is an illegal file name. You could use something like style 112 instead, which will use the format yyyymmdd. That's a better format, as the files will sort alphabetically in the correct order.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
|
Export Query to Pipe Del File