Error on BCP export into .CSV file via xp_cmdShell
-
Hi All,
I am using SQL Server 2005 where I have created SP to export out data into CSV file. I am using BCP utility to export out my data into csv file but I am getting below error message when i execute SP.
Error Message:
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
NULL
Here is my sample BCP export script
DECLARE @temp VARCHAR(1000),
@tablename VARCHAR(600)
DECLARE @rptdirMaster VARCHAR(300)
SET @rptdirMaster = 'C:\'
SET @tablename = @rptdirMaster + 'My-DailyClaimFile_'+CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')+'.csv'
SET @temp = 'bcp "SELECT * FROM ##My_Final ORDER BY ID" queryout '+@tablename +' -c -t"," -T'
--PRINT @temp
EXEC master..xp_cmdshell @temp
Note:
- xp_cmdshell is enabled on surface area configuration
- remote_connection is set to TCP/IP only (not with name piped)
Please let me know how do i fix above error because this will be my daily job that i need put in job activity monitor.
Please advice,
Thank You,
Keyun
-
keyun (6/23/2010)
Hi All,I am using SQL Server 2005 where I have created SP to export out data into CSV file. I am using BCP utility to export out my data into csv file but I am getting below error message when i execute SP.
Error Message:
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
NULL
Here is my sample BCP export script
DECLARE @temp VARCHAR(1000),
@tablename VARCHAR(600)
DECLARE @rptdirMaster VARCHAR(300)
SET @rptdirMaster = 'C:\'
SET @tablename = @rptdirMaster + 'My-DailyClaimFile_'+CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')+'.csv'
SET @temp = 'bcp "SELECT * FROM ##My_Final ORDER BY ID" queryout '+@tablename +' -c -t"," -T'
--PRINT @temp
EXEC master..xp_cmdshell @temp
Note:
- xp_cmdshell is enabled on surface area configuration
- remote_connection is set to TCP/IP only (not with name piped)
Please let me know how do i fix above error because this will be my daily job that i need put in job activity monitor.
Please advice,
Thank You,
Keyun
How about using username and password i.e sql server authentication instead of using trusted connection ?
If not make sure that the account you are using is able to login to the db !
HTH,
Cheers !
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂 -
Hi Mr or Mrs 500,
I am login into server as Administrator and login into SQL Server as Administrator as Window Authentication. but it is giving me an error.
Also, i have another account as DBA with sysadmin access but it is still giving me an error.
Would it be posibile the firewall on server dont allow sql connection when i use BCP xp_cmdshell?
Any thoguths!
-
you cannot use a trusted connection via sp_cmdShell. SQL doesn't use your credentials after you've connected to SQL.
this is a common security misconception. The problem is that when you access any resource OUTSIDE of SQL server, like network shares, local hard drives,xp_cmdshell,sp_OA type functions etc, it doesn't matter what YOUR credentials are. Whether you are Domain Admin,Local Admin , logged in as sa, administrative login on a laptop, etc, because SQL will not carry those credentials to the "outside of SQL" security context.
SQL Server uses the account it starts with to try and access the resource:
That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.
As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.
Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.
you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the linked server works when SQL is run your credentials, so you'd know you need a domain account to access the resource.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell - Thanks for the detail explanation but the sql script that i have on my post is working perfectly on my server but it does not work on my 3rd party server where i have enable xp_cmdshell and also, change TCP/IP criteria too.
Also, when i looked at the SQL Server(MSSQLSERVER) Properties on my server and it has "Log on as: Local System Account" and my script work fine.
So, I am just wondering after enable TCP/IP and xp_cmdshell why am i not able to export data out of table into flat file via xp_cmdshell?
Now, i understood very little bit about it access through out side and does not carry any credential but I am still little fuzzy and not quit sure how-to-do?
Please advice,
-
keyun i'm sure the issue is related to attempting to use a trusted connection.
if you pass a SQL username and password, your command to bcp via xp_cmdShell will owrk.
to see who's identity you are using, run this command:
DECLARE @Results table(
ID int identity(1,1) NOT NULL,
TheOutput varchar(1000))
insert into @Results (TheOutput)
exec master..xp_cmdshell 'whoami' --nt authority\system for example
insert into @Results (TheOutput)
exec master..xp_cmdshell 'cd %userprofile%' --NULL because nt authority\system is not a user...command fails.
select * from @Results
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell - this is a nice script. It gave me output as "nt authrity\system"
Since, I am new to BCP untility, i am tring to find out how to supply username and password with my bcp.
here is what i am thinking but giving me an error at ","
DECLARE @temp1 VARCHAR(1000),
@tablename1 VARCHAR(5000),
@rptdirMaster varchar(1000)
SET @rptdirMaster = 'F:\'
SET @tablename1 = @rptdirMaster + 'KeyunMistryTestFile_'+'.csv'
SET @temp1 = 'bcp "SELECT * from testpilot.dbo.My_Final" queryout '+@tablename1 +' -c -U "KMTest" -P "Soccer@1" "," -T'
PRINT @temp1
EXEC master..xp_cmdshell @temp1
Please let me know your thoughts!
Thank You,
Keyun
-
keyun this example works for me... outputs 3 rows to my file:
no quotes or spaces for the username or password bvariables being passed...your way may work, but it's not how i've done it in the past.
DECLARE @temp1 VARCHAR(1000),
@tablename1 VARCHAR(5000),
@rptdirMaster varchar(1000)
SET @rptdirMaster = 'C:\'
SET @tablename1 = @rptdirMaster + 'KeyunMistryTestFile_'+'.csv'
SET @temp1 = 'bcp "SELECT top 3 * from master.sys.tables" queryout '+ @tablename1 +' -c -Usa -PNotARealPassword -SD223\SQLEXPRESS'
PRINT @temp1
EXEC master..xp_cmdshell @temp1
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell - Awesome it did work for me but I hate to say that i need to have this as comma delaminated file ","
How would I do that with supplying UserName and Passworld? If i can do that then i should be fine.
I really appreciate for your kind help.
Thanks
Keyun
-
when i did bcp /? from a command window, i saw one if the switches is a lower case t:
[-t field terminator]
changing my command to this gave me comma delimited output: note all i did was add -t,
that's telling it to use the comma for the delimiter...
DECLARE @temp1 VARCHAR(1000),
@tablename1 VARCHAR(5000),
@rptdirMaster varchar(1000)
SET @rptdirMaster = 'C:\'
SET @tablename1 = @rptdirMaster + 'KeyunMistryTestFile_'+'.csv'
SET @temp1 = 'bcp "SELECT top 3 * from master.sys.tables" queryout "'+ @tablename1 + '" -t, -c -Usa -PNotARealPassword -SD223\SQLEXPRESS'
PRINT @temp1
EXEC master..xp_cmdshell @temp1
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell - dang...you are the MAAANNN...this is great... It worked.
I was looking at the detail on website what are the option we have with xp_cmdshell like -t -u -p but i could not find on website.
Do you know where can i find the description of -t, -U, -P? so it can help me out to understand what are these stand for?
Again, I really thankful to you that you took some time and help me out.
Thanks a million.
Keyun
-
the switches we are talking about are for the program bcp.exe, not xp_cmdShell...easy to confuse
i would reckon books online has a complete description of each delimiter, but the command line /? gave me most of what i needed:
C:\>bcp /?
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]
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell -you are correct, i was asking for BCP but somehow i miss type to xp_cmdshell.
Thanks for the detail i will have to save it to safe location
THanks a lot.
Keyun
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply