SQL Command join to external server/database

[tmccoy1]

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

 

[PeterDimitrov]

What is the error ?
You can try to use [server].[database].[owner].

or OPENQUERY

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[tmccoy1]

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 ]

 

[PeterDimitrov]

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

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.