Query accross servers

Hi,
Please could someone provide me with a best example of how to query records
from different tables in different databases, where the databases are
located on different servers on the same network.
Your assistance is much appreciated.If you want to use different servers you have to build up some linked
servers (look in the BOL). There you can go with the four-point name to
query them.
Select * from [Servername].[Databasename].[owner].[Objectname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"11Oppidan" <11Oppidan@.community.nospam> schrieb im Newsbeitrag
news:us3gSr6WFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Please could someone provide me with a best example of how to query
> records from different tables in different databases, where the databases
> are located on different servers on the same network.
> Your assistance is much appreciated.
>|||You may also want to check out OPENQUERY in BOL. Sometimes it yields
better performance than four-part names.
Here's an excerpt from BOL:
This example creates a linked server named OracleSvr against an Oracle
database using the Microsoft OLE DB Provider for Oracle. Then this
example uses a pass-through query against this linked server.
Note This example assumes that an Oracle database alias called ORCLDB
has been created.
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
HTH...
--
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Wed, 18 May 2005 15:13:15 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>If you want to use different servers you have to build up some linked
>servers (look in the BOL). There you can go with the four-point name to
>query them.
>Select * from [Servername].[Databasename].[owner].[Objectname]|||Thanks guys!
"Joe Webb" <joew@.webbtechsolutions.com> wrote in message
news:67gm815emd4m6g2vb7dr70tsn01vsciaon@.
4ax.com...
> You may also want to check out OPENQUERY in BOL. Sometimes it yields
> better performance than four-part names.
> Here's an excerpt from BOL:
> This example creates a linked server named OracleSvr against an Oracle
> database using the Microsoft OLE DB Provider for Oracle. Then this
> example uses a pass-through query against this linked server.
> Note This example assumes that an Oracle database alias called ORCLDB
> has been created.
>
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
>
> HTH...
> --
> Joe Webb
> SQL Server MVP
>
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/t...il/-/0972688811
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>
> On Wed, 18 May 2005 15:13:15 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>

Quer runs well on its own but when put into stored procedure, it runs slowly

Hi,

Appreciate if any one can provide some pointers on this issue on a stored procedure that I have.

The required indexes (both clustered and non-clustered) have been created in the database tables.

When the T-SQL statements in the stored procedure are run in the SQL Query directly, it performs well and returns the results back within seconds.

However, the stored procedure is run in the SQL Query window directly, it takes minutes before it returns.

When the execution plan is examined for both, they are both different and it appears that when executing the stored procedure, it doesn't make use of the indexes, whereas in the first case of executing the same T-SQL statements directly, it does make use of the indexes.

We tried using the WITH RECOMPILE attribute to refresh the execution plan, but it doesn't seem to work for the stored procedure.

Wonder if anyone got any ideas or encounter anything similar?

Thanks,

Desmond

Can you post the SP and the DDL/Indexes?

|||

Hi Desmond T,

> When the T-SQL statements in the stored procedure are run in the SQL Query directly, it performs well and

> returns the results back within seconds.

> However, the stored procedure is run in the SQL Query window directly, it takes minutes before it returns.

Can you show us some light, please?

Look in BOL for auto-parameterization and also about the statistics used by the query optimizer. If you executed the "select" statement from QA or SSMS, using variables to simulate the parameters in the stored procedure, like in:

Code Snippet

use northwind

go

select orderid, customerid, orderdate

into dbo.t1

from dbo.orders

go

create index t1_orderdate_nu_nc_ix

on dbo.t1(orderdate)

go

dbcc show_statistics('dbo.t1', 't1_orderdate_nu_nc_ix') with STAT_HEADER, DENSITY_VECTOR, HISTOGRAM

go

create procedure dbo.usp_p1

@.orderdate datetime

as

set nocount on

select orderid, customerid, orderdate

from dbo.t1

where orderdate = @.orderdate

go

dbcc freeproccache

dbcc dropcleanbuffers

go

declare @.orderdate datetime

set @.orderdate = '19960704'

select orderid, customerid, orderdate

from dbo.t1

where orderdate = @.orderdate

go

dbcc freeproccache

dbcc dropcleanbuffers

go

exec dbo.usp_p1 '19960704'

go

drop table dbo.t1

go

drop procedure dbo.usp_p1

go

They seem to be the same, but the query optimizer uses different statistics to estimate cardinality and based on those statistics, it will choose an execution plan. In the sp case, the query procesor will use the values from the histogram, but for the query, it will use the "All density" value for that column and could be that those values yield different execution plan.

In my box, I got these values for "Estimated Number of Rows".

sp --> 1

query using variables --> 1.72917 (0.002083333 * 830) See "All density" value from the result of dbcc

The query optimizer chose to scan the table for the query using variables and an "index seek" operation followed by bookmark lookup for the sp.

One way of testing the query in QA / SSMS is using sp_executesql, parameterizing the statement.

Code Snippet

declare @.sql nvarchar(4000)

set @.sql = N'select orderid, customerid, orderdate from dbo.t1 where orderdate = @.orderdate'

exec sp_executesql @.sql, N'@.orderdate datetime', '19960704'

go

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

AMB

|||hi,

i had a similar problem before, can you post your proc?, i resolved my problem by tweaking the proc a bit.

/rh4m1ll3