BCP to create format file - SQL Server Forums

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?

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

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'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources