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)
 BCP to create format file

Author  Topic 

jayram
Starting Member

47 Posts

Posted - 2014-03-13 : 15:15:40
i am using this command to create a format file that i can use to load txt files but the output says 'The system cannot find the file specified.'

this is my command

exec xp_cmdshell 'bcp Databasename.tablename format nul -T -c U<sa> -S<sql2005> -x -f C:\Documents\tablename.xml'

what am i doing wrong?

thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-13 : 16:19:09
quote:
Originally posted by jayram

i am using this command to create a format file that i can use to load txt files but the output says 'The system cannot find the file specified.'

this is my command

exec xp_cmdshell 'bcp Databasename.tablename format nul -T -c U<sa> -S<sql2005> -x -f C:\Documents\tablename.xml'

what am i doing wrong?

thanks


The path it looks for is relative to the server. So in this instance, do you have the tablename.xml file in C:\Documents folder on the server?
Go to Top of Page

jayram
Starting Member

47 Posts

Posted - 2014-03-13 : 16:24:12
Yes i do have it on the server in the specified path. the output is 'The system cannot find the file specified' in the first row
and a NULL in the second
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-13 : 16:40:47
Couple of changes that you would need - don't know if that is all, but these you certainly need:

exec xp_cmdshell 'bcp Databasename.dbo.tablename format nul -T -c -U<sa> -P<password> -S<sql2005> -x -f C:\Documents\tablename.xml'
Go to Top of Page

jayram
Starting Member

47 Posts

Posted - 2014-03-18 : 11:38:00
Thanks but it still does not work. Anyways i am going to use the OPENROWSET and insert from source file

INSERT INTO TableName
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=No;Database=C:\Documents\tablename.xls',
'SELECT * FROM [Crosswalk$A2:Y]') GO
Go to Top of Page
   

- Advertisement -