BCP error

[andyc209]

trying to create a BCP that will export data from a table to a CSV file.

I have the following code:

DECLARE @bcpCmd VARCHAR(8000)
SET @bcpCmd = 'bcp "SELECT company, address, postcode FROM tbl_ifa" queryout D:\folder\test.csv -c -t, -r -T -S SERVERNAME'
EXEC master..xp_cmdshell @bcpCmd

but get the errors:

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'tbl_ifa'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL

but the table tbl_ifa does exist in one of the databases on the server - do i need to specify the database somewhere as well as the servername

 

[gmmastros]

change it like this:

DECLARE @bcpCmd VARCHAR(8000)
SET @bcpCmd = 'bcp "SELECT company, address, postcode 
                   FROM [!][DatabaseNameHere].dbo.[/!]tbl_ifa" 
               queryout D:\folder\test.csv -c -t, -r -T -S SERVERNAME'
EXEC master..xp_cmdshell @bcpCmd

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[andyc209]

thanks for the help

tried this - i changed to :

DECLARE @bcpCmd VARCHAR(8000)
SET @bcpCmd = 'bcp "SELECT company, address, postcode
FROM mytestdatabase.dbo.tbl_ifa"
queryout D:\folder\test.csv -c -t, -r -T -S SERVERNAME'
EXEC master..xp_cmdshell @bcpCmd

where mytestdatabase is the name of the database.

Do i need to change the SERVERNAME to the name of the servername for the SQL server?

I connect to a remote SQL server by ipaddress\name,port - e.g. 12.13.14.15\id123456,1234
Is this what i need to use? Is it something else?

I tried

DECLARE @bcpCmd VARCHAR(8000)
SET @bcpCmd = 'bcp "SELECT company, address, postcode
FROM PENSIONS_PORTAL.dbo.tbl_ifa"
queryout D:\folder\test.csv -c -t, -r -T -S 94.229.166.12\id23241,1334'
EXEC master..xp_cmdshell @bcpCmd

and get an output usage list and nothing in the csv file.