Hello All,

I keep getting this error when I run BCP.

SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login '[primate_quotes]'. Login fails.

EXEC master..xp_cmdshell
'bcp [DB].dbo.[table].[TXTCOL] out c:\test.txt -c -Uxxx -PXXX -Sservername'

Some of the query variants I have tried:

"[DB].dbo.[table].[TXTCOL]"
DB.dbo.table.TXTCOL
DB.dbo.table
[DB].dbo.[table]
"c:\test.txt"
-U xxx -PXXX -Sservername
-U"xxx" -P"XXX" -S"servername"

and everything inbetween.

I have researched this on Google and tried a lot of the suggestions, but still nothing.

Most of the suggestions I have read point to user pass not being correct for the table access rights.

I know the user and pass I am using have system admin rights, but I went ahead and created a new user with all priv and rights, but again the same results.

Please any suggestions?

Lookup the BCP utility (overview) in Books Online... there you will find the correct syntax for the command. Your problem is that you are using a column name where the database name goes and that cannot be done...

If you don't want to export the whole table, you will need to provide a query to the BCP command. The query can be an EXEC of an existing stored procedure.

--Jeff Moden

Hi Jeff,

Thank you for the advice.

I went back and reviewed the BOL ref and omitted the .[TEXTCOL] and got the same results. I found the .[TEXTCOL] from a google result. Somtimes google is my friend, sometimes not so much.

I tried 3 examples from BOL total before I came up with this variant that ended up working for me...

EXEC master..xp_cmdshell
'bcp "SELECT * FROM datbase_name.dbo.[table_name]" queryout "c:\test.txt" -c -S"servername" -U"xxx" -P"XXX"'

The two things I did different from BOL is removed the [] brackets from dbo and changed the out statement to be queryout as highlighted in red.

Thank you again.

Ok to make everything easier I put it into an SP, here it is...

DECLARE @FileName varchar(50),
        @bcpCommand varchar(2000)

        SET @FileName = 'c:\_dir\'+'_filename'+CONVERT(char(6),GETDATE(),1 2)+'.txt'
        SET @bcpCommand = 'bcp "SELECT * FROM primate_quotes.dbo.[CC_CSV]" queryout "'
        SET @bcpCommand = @bcpCommand + @FileName + '" -c -S"SNAME" -U"xxxx" -P"XXXX"'

EXEC master..xp_cmdshell @bcpCommand

NOW you're cooking! Thanks for the feedback!

--Jeff Moden