| Author |
Topic |
|
nhess80
Yak Posting Veteran
83 Posts |
 Posted - 2008-10-22 : 15:11:04
|
| I am trying to create a txt file from an sql table. The code below use to work then I had to reload sql server 2005. Since then it does not work or give me any errors indicating there is a problem. Does anyone know what the problem could be or does anyone have an easier way of doing this. Also I do have xp_cmdshell enabled. Thanks for your help. DECLARE @cmd VARCHAR(2048) SET @cmd = '@bcp ' + ' "SELECT StartTime,OrigNumber,TermNumber,CallDurationSec,Cost ' + ' FROM CDRBilling.dbo.ANPI_SIP_CDRData Order by StartTime" queryout' + ' D:\ANPI\ANPI_IP_test.txt -c -t\, -r \n -T' EXEC master..xp_cmdshell @cmd, NO_OUTPUT |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-10-22 : 15:37:27
|
quote:
Originally posted by tkizer
Well what's the error? Why do you have @bcp in the string? Where's the S switch?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I don't receive an error at all. It runs as though it works correctly but does not create the file. And the @bcp isn't suppose to be there. Didn't mean to put that in. For that S switch i never had one in there and it worked correctly. This code was code that i found and modified myself. I really know nothing about bcp. If you know a better way to write this script then let me know.Thanks for the quick reply. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-10-22 : 15:52:09
|
quote:
Originally posted by tkizer
Remove NO_OUTPUT (plus the comma) from your script so that you can see if you are receiving a cmd error. Let us know.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
This is the error that I received when I ran it in SQL Server Management Studio. This script normally gets run in a ColdFusion Page and when I do that I don't see any errors.SQLState = 08001, NativeError = 17Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.SQLState = 01000, NativeError = 2Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-10-22 : 17:21:04
|
quote:
Originally posted by tkizer
You'll need to add the -S switch to your bcp command. -Shostname or -Shostname\instancename or -Sipaddress or -Sipaddress\instancename or -Shostname,portnumber or -Sipaddress,portnumber, ...I could go on and on.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
I was able to get rid of the errors I added the ServerName but still no file was produced. Do you know of a site that I can read about bcp? |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-22 : 17:31:28
|
| http://msdn.microsoft.com/en-us/library/ms162802.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-22 : 18:11:48
|
| Couple of things to notice... even there won't be any error, your file is stored on the SQL server not LOCAL. If you get an error, Do you have D:\ANPI directory created... |
|
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-10-22 : 18:13:18
|
quote:
Originally posted by hanbingl
Couple of things to notice... even there won't be any error, your file is stored on the SQL server not LOCAL. If you get an error, Do you have D:\ANPI directory created...
Yes it is created. This use to work before and I had to reload SQL Server 2005. Now it has stopped working. |
|
|
|
nhess80
Yak Posting Veteran
83 Posts |
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-10-22 : 18:14:51
|
| No when I execute the below script it does not create the file but it does say the Query executed successfully and it gives me this output.bcp: unknown option ,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"]NULL DECLARE @cmd VARCHAR(2048) SET @cmd = 'bcp ' + ' "SELECT StartTime,OrigNumber,TermNumber,CallDurationSec,Cost ' + ' FROM CDRBilling.dbo.ANPI_SIP_CDRData Order by StartTime" queryout' + ' D:\ANPI\test.txt -c -t\, -r \n -T, -SPE2950\METRO' EXEC master..xp_cmdshell @cmd |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-10-22 : 18:18:13
|
quote:
+ ' D:\ANPI\test.txt -c -t\, -r \n -T, -SPE2950\METRO'
edit: the , after -T i thinkWebfredNo, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-10-22 : 18:25:38
|
quote:
Originally posted by webfred
quote:
+ ' D:\ANPI\test.txt -c -t\, -r \n -T, -SPE2950\METRO'
maybe this backslash after -t OR the , after -TWebfredNo, you're never too old to Yak'n'Roll if you're too young to die.
NICE WORK!!! --- It was the , after the -T |
|
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-10-22 : 18:26:47
|
| THANKS EVERYONE FOR HELPING IT TURNED OUT TO BE A COMBINATION OF THINGS IN MY SCRIPT. |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-22 : 18:27:32
|
why put in -S when you are running it on a commandline. "," after -T isn't needed.you don't need -SPE2950\METRO either... If you are running on that server.+ ' D:\ANPI\test.txt -c -t\, -r \n -T, -SPE2950\METRO' |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-22 : 18:29:06
|
quote:
Originally posted by nhess80
THANKS EVERYONE FOR HELPING IT TURNED OUT TO BE A COMBINATION OF THINGS IN MY SCRIPT.
Just curious, why didn't the T-SQL in your OP didn't work?? |
|
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-10-22 : 19:53:20
|
quote:
Originally posted by hanbingl
why put in -S when you are running it on a commandline. "," after -T isn't needed.you don't need -SPE2950\METRO either... If you are running on that server.+ ' D:\ANPI\test.txt -c -t\, -r \n -T, -SPE2950\METRO'
I was just testing it out in SQL Server Management. I am actually running the script in a ColdFusion page. |
|
|
|
nhess80
Yak Posting Veteran
83 Posts |
Posted - 2008-10-22 : 19:54:35
|
quote:
Originally posted by hanbingl
quote:
Originally posted by nhess80
THANKS EVERYONE FOR HELPING IT TURNED OUT TO BE A COMBINATION OF THINGS IN MY SCRIPT.
Just curious, why didn't the T-SQL in your OP didn't work??
I don't know why it work |
|
|
|
|
Creating a txt file from sql table