May 14, 2007 at 1:43 am
Yes, it does. That's what the -S<servername> parameter says...
/Kenneth
January 16, 2010 at 5:38 am
Gentlemen
I am using BCP in a stored procedure to create an excel XML file
everything goes fine and i get the following output when the BCP command is executed
NULL
Starting copy...
NULL
4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (4000.00 rows per sec.)
NULL
but the problem is i cannot find the file that is created/or should have been created
SET @testString2 = 'BCP "SELECT MAN_Content FROM [DBNAME].[dbo].[TABLENAME] ORDER BY id" queryout "C:\Temp\ECC_MSXLSBySQLServer2005.xls" -C RAW -S SERVERNAME -T -w'
Exec Master..xp_cmdshell @testString2
I cant find the file , which i assume should be in my C drive inside temp folder.
any help shall be highly appreciated.
Regards
Zeeshan
January 16, 2010 at 2:47 pm
It's on server's C: drive inside temp folder.
_____________
Code for TallyGenerator
January 16, 2010 at 11:21 pm
Thanks a lot. That's what i was confused with.
June 17, 2010 at 11:12 pm
Hi,
I am trying to execute
exec Master..xp_cmdshell 'bcp "Select _Id_ from PM.dbo._TST_" queryout "C:\_ID_Test.txt" -c -U SA -P AA112233** -S MYLAP\SQLEXPRESS'
Gives an Output
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL
The Same Statement when I execute from cmd prompt
bcp "Select _Id_ from PM.dbo._TST_" queryout "C:\_ID_Test.txt" -c -U SA -P AA112233** -S MYLAP\SQLEXPRESS
Gives the Output
Starting copy...
4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 94 Average : (42.55 rows per sec.)
Where am I going wrong...
-- Regards
Alban Lijo
[font="Courier New"]-- Alban Lijo <SQL Rookie> :-)[/font]
June 18, 2010 at 5:06 pm
When you run bcp on your local machine C:\ is your local drive.
When you run bcp on the server C:\ is a local drive on he server.
Compare:
"dir C:\" from command prompt and
exec master.dbo.xp_cmdshell 'dir c:\'
_____________
Code for TallyGenerator
June 18, 2010 at 10:05 pm
Thanks Sergiy for the reply..
Sql Server resides in the machine where im executing xp_cmdshell
in that case my local machine and server are the same..
all other commands works fine with xp_cmdshell.. only bcp throws an error ..
So i don't think that is the problem. Any way I have tried Wat u have told
Here are the results and they are Identical
-- Through xp_cmdshell
Volume in drive C has no label.
Volume Serial Number is 402B-0039
NULL
Directory of c:NULL
06/15/2010 10:54 AM 17,408 1.xls
04/14/2010 05:23 PM <DIR> 80613812935150ba72
04/14/2010 03:27 PM 0 AUTOEXEC.BAT
05/12/2010 04:55 PM 1,652 bar.emf
05/07/2010 06:14 PM <DIR> bujji
05/19/2010 12:48 PM <DIR> BUJJI_RD
04/14/2010 03:27 PM 0 CONFIG.SYS
06/14/2010 10:44 AM <DIR> db
06/07/2010 02:39 PM <DIR> Documents and Settings
02/19/2010 03:55 PM 8,327,264 Firefox Setup 3.6.exe
06/03/2010 10:27 AM <DIR> Inetpub
04/14/2010 05:14 PM 244 Install.log
04/14/2010 05:10 PM <DIR> Intel
06/17/2010 06:21 PM <DIR> Lijo
06/18/2010 10:40 AM 12 lijo.txt
04/19/2010 01:25 PM <DIR> MSDE_Database
06/02/2010 06:22 PM <DIR> MSVisualStudio6.00
06/09/2010 11:48 AM <DIR> oboutSuite
06/18/2010 06:12 PM <DIR> Official
06/18/2010 04:12 PM <DIR> Program Files
04/14/2010 05:12 PM 1,769 RHDSetup.log
06/19/2010 09:27 AM 970 service.log
05/05/2010 06:18 PM <DIR> Softwares
05/25/2010 03:05 PM <DIR> Stanley
05/25/2010 03:07 PM 160,178,714 Stanley.zip
04/02/2010 02:30 PM 15,944,192 TA.bak
06/14/2010 10:38 AM 28,521 test.jpg
06/15/2010 10:54 AM 17,408 Test.xls
06/15/2010 10:49 AM 8,281 Test.xlsx
04/23/2010 11:39 AM <DIR> SAmple
06/18/2010 10:31 AM <DIR> WINDOWS
06/17/2010 06:14 PM <DIR> WindowsApplication1
05/18/2010 01:08 PM <DIR> working folder
14 File(s) 184,526,435 bytes
19 Dir(s) 81,251,590,144 bytes free
-- Through Cmd prompt
Volume in drive C has no label.
Volume Serial Number is 402B-0039
Directory of c:
06/15/2010 10:54 AM 17,408 1.xls
04/14/2010 05:23 PM <DIR> 80613812935150ba72
04/14/2010 03:27 PM 0 AUTOEXEC.BAT
05/12/2010 04:55 PM 1,652 bar.emf
05/07/2010 06:14 PM <DIR> bujji
05/19/2010 12:48 PM <DIR> BUJJI_RD
04/14/2010 03:27 PM 0 CONFIG.SYS
06/14/2010 10:44 AM <DIR> db
06/07/2010 02:39 PM <DIR> Documents and Settings
02/19/2010 03:55 PM 8,327,264 Firefox Setup 3.6.exe
06/03/2010 10:27 AM <DIR> Inetpub
04/14/2010 05:14 PM 244 Install.log
04/14/2010 05:10 PM <DIR> Intel
06/17/2010 06:21 PM <DIR> Lijo
06/18/2010 10:40 AM 12 lijo.txt
04/19/2010 01:25 PM <DIR> MSDE_Database
06/02/2010 06:22 PM <DIR> MSVisualStudio6.00
06/09/2010 11:48 AM <DIR> oboutSuite
06/18/2010 06:12 PM <DIR> Official
06/18/2010 04:12 PM <DIR> Program Files
04/14/2010 05:12 PM 1,769 RHDSetup.log
06/19/2010 09:22 AM 145 service.log
05/05/2010 06:18 PM <DIR> Softwares
05/25/2010 03:05 PM <DIR> Stanley
05/25/2010 03:07 PM 160,178,714 Stanley.zip
04/02/2010 02:30 PM 15,944,192 TA.bak
06/14/2010 10:38 AM 28,521 test.jpg
06/15/2010 10:54 AM 17,408 Test.xls
06/15/2010 10:49 AM 8,281 Test.xlsx
04/23/2010 11:39 AM <DIR> SAmple
06/18/2010 10:31 AM <DIR> WINDOWS
06/17/2010 06:14 PM <DIR> WindowsApplication1
05/18/2010 01:08 PM <DIR> working folder
14 File(s) 184,525,610 bytes
19 Dir(s) 81,251,434,496 bytes free
--Regards
Alban Lijo
[font="Courier New"]-- Alban Lijo <SQL Rookie> :-)[/font]
January 7, 2013 at 12:50 am
Thank you. It was the wrong directory.
March 21, 2013 at 11:07 am
Hi everyone,
I have this error output in SSMS for the following query
exec Master..xp_cmdshell 'bcp "select blobdata from SynDocs.dbo.BLOBDATA where blobid = 2" queryout "c:\filename2.img" -S ROK-W7STHONUK-7 -T'
The output is 2 rows
NULL
Enter the file storage type of field blobdata [image]:
when i run the same query thru cmd prompt, the result is
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file.
any of you help me with this please.
June 9, 2014 at 1:41 pm
Kenneth Wilhelmsson (11/16/2005)
Whenever I get this message, it's because of one ofthree things:
1) The path/filename is incorrect (check your typing / spelling)
2) The file does not exist. (make sure the file is where you expect it to be)
3) The file is already open by some other app. (close the other app to release the file)
For 1) and 2) - rememberthat <EM>paths arerelative to where bcp is executing.</EM> Make sure that bcp.exe can access the file/path from it's context.
/Kenneth
This advice helped a lot. I just ran into this BCP problem after a version 8 to version 10 migration.
We are using UNC paths to the local machine (don't ask why), and the file couldn't be accessed. Troubleshooting found that the BCP command needed to be updated to include the -T switch, and the priv's on the \\UNC needed to be reset for the SQL Agent service account.
rbs
July 4, 2014 at 9:26 am
Kenneth Wilhelmsson (11/16/2005)
Whenever I get this message, it's because of one ofthree things:
1) The path/filename is incorrect (check your typing / spelling)
2) The file does not exist. (make sure the file is where you expect it to be)
3) The file is already open by some other app. (close the other app to release the file)
For 1) and 2) - rememberthat <EM>paths arerelative to where bcp is executing.</EM> Make sure that bcp.exe can access the file/path from it's context.
/Kenneth
adding one more point to Kenneth's points
4) Ensure your SQL Server Agent is Enabled / Running, else make it enabled..
Note: Because this point was the issue in my case.
sample Code I executed is as below:
use AdventureWorks2008R2
go
exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'ad hoc distributed queries',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go
--exec master..xp_fixeddrives
declare @sql varchar(8000),@tablename varchar(150)
declare exporttabletotextfile cursor global
for select name from sys.objects where [type]=N'U'
open exporttabletotextfile
fetch exporttabletotextfile into @tablename
while @@FETCH_STATUS=0
begin
select @sql = 'bcp "select * from AdventureWorks2008R2..'+@tablename+'" queryout C:\SSIS\'+@tablename+'.txt -c -t, -T -S ' + @@servername
print @sql
exec xp_cmdshell @sql
fetch next from exporttabletotextfile into @tablename
end
close exporttabletotextfile;
deallocate exporttabletotextfile;
exec sp_configure 'xp_cmdshell',0
go
reconfigure
go
exec master..sp_configure 'ad hoc distributed queries',0
go
reconfigure
go
exec master..sp_configure 'show advanced options',0
go
reconfigure
go
Regards,
Prabhu
July 4, 2014 at 5:06 pm
tsandeep1407 (3/21/2013)
Hi everyone,I have this error output in SSMS for the following query
exec Master..xp_cmdshell 'bcp "select blobdata from SynDocs.dbo.BLOBDATA where blobid = 2" queryout "c:\filename2.img" -S ROK-W7STHONUK-7 -T'
The output is 2 rows
NULL
Enter the file storage type of field blobdata [image]:
when i run the same query thru cmd prompt, the result is
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file.
any of you help me with this please.
I know it's an older post but the problem here is that the "-c" parameter wasn't specified. I would also recommend the "-C Raw" parameter (notice the difference in case) but leaving that out isn't a showstopper.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2015 at 5:57 am
The USER might not have permission to the folder mentioned.
-Ajith
January 8, 2016 at 6:25 am
I want to import data to my database via bcp like this
bcp mydb.dbo.mytable in 'C:\_value_.txt' -S mypc\instancename -T
But the error I keep getting is unable to open BCP host data -file
Why?
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply