asked on

Using xp_cmdshell and getting error when using multiple tables, whats wrong?

When I try to select from 2 different tables then join them together I get an error. When I only select from 1 table it works fine
This is similar to my statement:

This Works
EXEC master..xp_cmdshell 'bcp "select * from MyDB..inv where style = ''101x''" queryout c:\test.xls -U -P -c'

This doesnt work
EXEC master..xp_cmdshell 'bcp "select * from MyDB..inv,inv_dtl where inv.inv_id = inv_dtl.inv_id and style = ''101x''" queryout c:\test.xls -U -P -c'

I get these errors when executing the second one
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'inv_dtl'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

appari

try adding dbname (MyDB..) to inv_dtl also.

EXEC master..xp_cmdshell 'bcp "select * from MyDB..inv, MyDB..inv_dtl where inv.inv_id = inv_dtl.inv_id and style = ''101x''" queryout c:\test.xls -U -P -c'

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byteboy1

ASKER

When I do that it still fails with:

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'inv' does not match with a table name or alias name used in the query.

byteboy1

ASKER

Thank you appari , it works when I add the aliases!!.