MSSQL cross-database Queries

[Petya]

Hi again folks,

Today I wrote a query that references another database - other than the one in TZConnection.Database

Code: Select all

  SELECT Rendszam,FszgA,FszgB,CI
  FROM Online FULL OUTER JOIN BUSDATADKV.dbo.Jarmu
  ON (BUSDATADKV.dbo.Jarmu.FSZGA=Online.CI) OR (BUSDATADKV.dbo.Jarmu.FSZGB=Online.CI)
  WHERE CI=999 OR BUSDATADKV.dbo.Jarmu.FSZGA=999 OR BUSDATADKV.dbo.Jarmu.FSZGB=999 ORDER BY Rendszam ;

If I execute it from a dinamically created TZQuery component, I get the following error :

Code: Select all

General SQL Server error: Check messages from the SQL Server.  
The database name component of the object qualifier must be the name of the current database. 

There's a commonly used manager program for MS SQL Server 2000. It's called EMS SQL Manager for SQL Server. If I copy-paste the query into this program, it runs fine, gives expected results. This (commercial) program is probably using Zeos also, because it has the same 'busy' cursor than Zeos displays if you set TZConnection.SQLHourGlass to true.
Therfore it's very likely that it's possible to do this with Zeos.

To resolve the problem, I've tried to empty the database property from ZConnection and put exact database references before each table name in the query, but it gave me the same error.

I was also searching in the MSSQL manual, but found nothing about connections and databases, but this is the expected case from Microsoft Corp.

So can anybody tell me please, how can I access two MSSQL databases from one ZQuery on one ZConnection ?

Thank You.

Cheers,
Peter

[Petya]

With other words :

Can I access two (or more) databases with a single ZConnection component ? How ?

[bangfauzan]

Hi, Petya.

of course you can use cross-query, and also cross-server and cross-host. but you have to remove "dbo" clause in your sql statement.

so, try to change your sql above to be:

SELECT Rendszam,FszgA,FszgB,CI
FROM Online FULL OUTER JOIN BUSDATADKV..Jarmu
ON (BUSDATADKV..Jarmu.FSZGA=Online.CI) OR (BUSDATADKV..Jarmu.FSZGB=Online.CI)
WHERE CI=999 OR BUSDATADKV..Jarmu.FSZGA=999 OR BUSDATADKV..Jarmu.FSZGB=999 ORDER BY Rendszam ;

Note:
if you want to make cross-DBMS query or cross-host query using MSSQL, you can use OpenRowSet() function.

Regards.

[bangfauzan]

attention:
do not remove ".." between database_name and table_name

just remove the "dbo" and leave the ".."

example:

select * from db_name..tbl_name

[Petya]

Problem solved, thank you !

:-))