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

byteboy1Asked:
Who is Participating?
 
appariCommented:
or this

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

0
 
appariCommented:
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'
0
 
byteboy1Author Commented:
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.
0
 
byteboy1Author Commented:
Thank you appari , it works when I add the aliases!!.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.