Trying to access data via DSN connection

[thorntong]

Good Morning All,
I am trying to see if I can make a direct SQL call using an OPENDATASOURCE call to a DSN that has been setup on my SQL 2005 server. And I am having trouble with the syntax using this type of call. I am trying to access the OD_RECEIPTS table within a ODBC System Name of CPSIODBC. This is what I have now:

SELECT *
FROM OPENDATASOURCE('ODBC','DSN=CPSIODBC;User ID=;Password=').od_receipts

And I getting an 'Invalid object name 'od_receipts' error. Does anyone what the proper syntax that I should be using? It seems to me like I'm missing more in way of the table syntax rather than the ODBC credentials. I just don't know.
Kind Regards,
Glenn

 

[pjw001]

Try looking at this (if you haven't already)

http://msdn.microsoft.com/en-us/library/ms179856.aspx