Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Command join to external server/database

Status
Not open for further replies.

tmccoy1

MIS
Jan 24, 2013
15
US
Anyone have any tips for the correct syntax to use in the SQL command to join a table from an external server and database? This works in a straight sql query with brackets around the server name, but Crystal rejects that with an error. It also works to manually select the tables from each connection, but I need to use an SQL command because the join criteria requires conversion of the data with a REPLACE command on one of fields.

I have tried: server.database.owner.table
and [server].database.owner.table
 
Crystal reports version is 2008. Errors encountered when attempting to add sql command:

1. With brackets around linked server name: [TIMMY\TIMMY].LIHP.dbo.table_name
Failed to retrieve data from the database. Details: 42000[Cache ODBC][State: 37000][Native Code 1] …[Location: <Prepare>]
[%msg:<IDENTIFIER expected, [ found^SELECT DISTINCT field1, field2, field3, etc.

Any use of brackets including around all components such as, [server].[database].[owner].[table_name], Results in this error.

2. Without brackets using linked server name: TIMMY\TIMMY].LIHP.dbo.table_name
Failed to retrieve data from the database. Details: 42S02[Cache ODBC][State: S0002][Native Code 30]…[Location: <Prepare>]
[SQLCODE: <-30>:<Table or View not found>] [Location: <Prepare>]
[%msg: <Table ‘SYSTEM.TIMMY’ not found>] Database Vendor Code: 30 ]

(SYSTEM is the owner name of the main database and TIMMY is the linked server name)

3. Attempt to use ODBC name: timmy.LIHP.dbo.table_name
Same error codes as #2 above with this: [%msg: <Table ‘DBO.table_name’ not found>] Database Vendor Code: 30 ]
 
Is this SQLServer database? If yes then you can try to use OLE DB connection instead of ODBC.
Another approach would be to use OPENQUERY instead of full path to the table



Viewer and Scheduler for Crystal reports and SSRS.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top