Query Report

[Former Member]

Hi Experts,

I have two servers A and B. Each is having two databases P,Q in A and X, Y in B.

Now i want combined reports,

1) Sales Analysis Report - From P, X, Y databases

2) Stock Report - From P, X, Y

3) Pending PO's of P.

4) Pending Po's of X and Y.

How can i get above reports. Please let me know the solution.

Thanks

[Johan_Hakkesteegt]

Hi,

Either connect the different databases to one database with MS SQL Server's linked server functionality, or use the OPENROWSET function to link databases inline in the query.

Regards,

Johan

[Former Member]

Hi Johan,

How can i get the sales analysis report by items across 4 databases from different server.?

I have never used OPENROWSET yet. If you can help me, please send a sample query which connects multiple databases from multiple server.

Thanks

[Johan_Hakkesteegt]

Hi,

Come to think of it, better use the OPENDATASOURCE function. It lets you use a table from a different server / database in a normal JOIN.

Example of use:

SELECT T0.DocNum

FROM OPENDATASOURCE('SQLOLEDB','Data Source=YourServerNameHere;User ID=YourDbUserIdHere;Password=YourDbUserPasswordHere').[YourDatabaseNameHere].[OINV] T0

Please be aware that this function is not the most efficient in very demanding queries. In that case it is better to use linked server.

Regards,

Johan