not a valid identifier ? - 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

General SQL Server Forums

New to SQL Server Programming

not a valid identifier ?

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-09 : 17:09:32

how to work out this bcp command ? my file is using comma as delimiter. the error message is

"not a valid identifier"


declare @cmd varchar(max);
set @cmd = 'bcp #tmpTbl in "C:\Documents and Settings\Desktop\dd.txt" -c -t ","';  
print @cmd
exec @cmd

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-11-09 : 17:28:26

You've got a few problems here.
- bcp is not a sql command - from t-sql code you would need to call it via xp_cmdshell.
- temp tables will not be in scope - you need to use permanent tables
- You're missing a security switch

try this:


declare @cmd varchar(8000);
set @cmd = 'bcp #tmpTbl in "C:\Documents and Settings\Desktop\dd.txt" -T -c -t ","';  
print @cmd
exec master..xp_cmdshell @cmd

Be One with the Optimizer
TG

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-09 : 21:38:42

thanks for your reply. the new bcp command can work in the DOS window, but the script below can't run in the Management Studio.

The error is


Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file

-------------------
another question is, why we don't use varchar(max) ?


declare @cmd varchar(8000);
set @cmd = 'bcp TestDB..tmpTbl1 in "C:\Users\ch\Desktop\data.txt" -T -c -t ","';  
print @cmd
exec master..xp_cmdshell @cmd

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 21:50:05

you do know bcp is look at the server drive, not your "C" drive

Also

it needs to be

-t","

NO SPACE

And I don't think you can bcp in to a emp table..could be wrong

and the table should be

<db>.<owner>.<table>

Also you need to specify the Server

-S<servername>

AND you either need to supply a login and password

-U<loginb> -P<password>

OR use a trusted connection

-T

Good luck

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

java148
Yak Posting Veteran

63 Posts

Posted - 2011-11-09 : 22:28:08

the weird thing is, if I use this command in the DOS window, it works fine.


bcp TestDB..tmpTbl1 in "C:\Users\chenzhenying\Desktop\data.txt" -T -c -t","

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources