BCP help needed. 1

[MarkButler]

I have a database with a table within it that I want to extract into a text file. On my laptop I have the database defined and have no problem with the following command. The difference is that on my laptop the database name is test and on the actual server it is a numeric name. I beleive that to be my problem.

DECLARE @xp_cmd varchar (500)
SET @xp_cmd = 'BCP "10245..web_employer" out web_em.txt -c'
EXEC master..xp_cmdshell @xp_cmd

On the laptop I can issue the command as follows:

SET @xp_cmd = 'BCP test..web_employer out web_em.txt -c'

I am actually constructing the name in a stored procedure as follows. On the test machine vs the production machine the only difference in the command is the database name which is test vs 10245.

SET @xp_cmd = 'BCP ' + db_name() +'..' + @table_name + ' out ' + @text_file + ' -c'

I have added quotes and brackets around the database name and the database and table name without success. What am I missing?

TIA
Mark

 

[MarkButler]

I forgot the error message.

SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '10245.'.

 

[MartinCroft]

Hi

have you tried using a print statement

Print @xp_cmd

and then attempting to run the statement that is returned
it might point you in the direction you are missing, If the statement doesn't run fix what the issue is in the statement then you can correct your dynamic sql statment

It looked like the issue with spaces in names but you said you have already tried [test vs 10245] but it wasn't in the code shown above


If still struggling post the output of the print statement.

 

[MarkButler]

Actually that is how I got the generated statement for this post. I have placed the statement into QA and have been playing with it and still no luck. It all revolves around the input database/table name.

In the following I picked a system table in the "pubs" database and the one I am having problems with. In the following example using "pubs" the statement works fine. If I switch the set statements it fails with the syntax error previously noted.

DECLARE @xp_cmd varchar (500)
SET @xp_cmd='BCP "10245..sysobjects" out e:\web_em.txt -c'
SET @xp_cmd='BCP "pubs..sysobjects" out e:\web_em.txt -c'
EXEC master..xp_cmdshell @xp_cmd

If I try brackets around the database name, it can't find the database (same message as if you entered xxx as the database name). I am getting desparate. This application was supposed to be up on Monday. Any help would be greatly appreciated.

 

[nigelrivett]

SET @xp_cmd='BCP "select * from [10245]..sysobjects" queryout e:\web_em.txt -c'

You could also create a view in tempdb referencing the table and use that.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MarkButler]

Thanx nigelrivett that worked. Why the original method did not work is still a mystery. If it works just move on!! Thanx again.