Linked Server Performance

I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
DaveDoes usp_myproc only work with data on the remote server ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DaveF" <davef@.comcast.net> wrote in message
news:%23ekAsrpbDHA.3248@.tk2msftngp13.phx.gbl...
I call a stored proc via a linked server like:
EXEC LINKEDSERVER.dbname.dbo.usp_myproc
The proc is very basic and executes immediately when called from the local
box. But when it is called as a linked server call, it takes 30-40 seconds
to execute.
The newtork traffic is not heavy. What are some things I should look at to
improve this performance?
Thanks
Dave

Linked Server in SQL Server Express

I am just trying to setup a connection to an Oracl DB with a linked server i
n
SQL Server Express. It was successful to setup the linkedserver, since I
needed to login I tried a script like this:
USE [MyDB]
GO
EXEC sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'MylinkedServer'
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
GO
Now I got a message wrong syntax near MylinkedServer (message 102, level 15,
status 1, line 2) and I do not know what that means. Also I thought I would
be able to see my linkedserver somewhere in SQL Server Management Studio
Express, but unfortunately all articles only refer to Enterprise Manager
which I do not have with the Express Edition.Hi,
In your call to sp_addlinkedsrvlogin do you really have the square
brackets ([ and ]) around @.rmtsrvname? If you do, this is what is
causing the error. Also, you are missing a comma after the
'MyLinkedServer'. Try calling sp_addlinkedsrvlogin like:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'MylinkedServer',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
This doesn't generate any syntax errors on my SQL Server Express
instance so maybe it will work for you.
In SQL Server Management Studio Express you can see your linked servers
under the "Server Objects" node. Your object browser in Management
Studio Express should look like:
Instance Name
|
| <There's some stuff in the middle but don't worry about it>
|
|
-- Server Objects
|
|
-- Linked Servers
Hope that helps a bit|||Hi,
thanks I really forgot to remove the brackets. Sometimes you just don't see
the forest for the trees.
However in SQL Server Management Studio Express I still cannot find the
server. (Unfortunately) I do have a german version of it, by translating the
names I can find Serverobjects and something that might be a translation of
linked servers. There is only another submenu called contractor (with a lot
of entries) and nowhere is my linked server.
Since I am just reading through all that stuff, could you or anybody else
also tell me how to query tables on the linked server? I hoped to see them
somewhere under the linked server in the management studio and get a first
hint by that...|||Sorry I was to fast, got the answer for my last question about querying the
linked server here:
http://msdn.microsoft.com/library/d...br />
5xix.asp|||...and after restarting the management studio I can see my linked server!|||Hi Thomas,
Yeah, I find that sometimes a fresh pair of eyes to look at something
is good...

>From what you are describing I think you have found the linked servers
node. I believe the submenu (contractor) you are talking about would be
the "Provider" menu in the English version. Just for information, this
menu lists the provides (e.g. OLE DB, Native, Jet) that you can use to
connect to a linked server. No need to worry about this menu so much.
It's strange that you can't see your linked server in the GUI. Have you
tried adding a linked server via Management Studio Express. You can add
a linked server by clicking on the linked servers node and selecting
"New Linked Server..." (not sure what that would be in German)|||> It's strange that you can't see your linked server in the GUI. Have you
> tried adding a linked server via Management Studio Express. You can add
> a linked server by clicking on the linked servers node and selecting
> "New Linked Server..." (not sure what that would be in German)
I can see it now, it just needs a refresh sometimes. Do you have some
experience about its performance regarding queries? I linked an oracle serve
r
and when I query something it is relatively slow, even compared to Jet which
I used before.|||Not really, sorry. Are you running across a network? Maybe congestion
has something to do with it?
Also, I remember hearing that certain queries cause an entire result
set to be transmitted to your local server and not just the rows you
want. Don't remember any more than that, sorry.sql

Linked Server in SQL Server Express

I am just trying to setup a connection to an Oracl DB with a linked server in
SQL Server Express. It was successful to setup the linkedserver, since I
needed to login I tried a script like this:
USE [MyDB]
GO
EXEC sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'MylinkedServer'
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
GO
Now I got a message wrong syntax near MylinkedServer (message 102, level 15,
status 1, line 2) and I do not know what that means. Also I thought I would
be able to see my linkedserver somewhere in SQL Server Management Studio
Express, but unfortunately all articles only refer to Enterprise Manager
which I do not have with the Express Edition.Hi,
In your call to sp_addlinkedsrvlogin do you really have the square
brackets ([ and ]) around @.rmtsrvname? If you do, this is what is
causing the error. Also, you are missing a comma after the
'MyLinkedServer'. Try calling sp_addlinkedsrvlogin like:
EXEC sp_addlinkedsrvlogin @.rmtsrvname = 'MylinkedServer',
@.useself = 'FALSE',
@.locallogin = NULL,
@.rmtuser = 'me',
@.rmtpassword = 'myrmtpassword'
This doesn't generate any syntax errors on my SQL Server Express
instance so maybe it will work for you.
In SQL Server Management Studio Express you can see your linked servers
under the "Server Objects" node. Your object browser in Management
Studio Express should look like:
Instance Name
|
| <There's some stuff in the middle but don't worry about it>
|
|
-- Server Objects
|
|
-- Linked Servers
Hope that helps a bit|||Hi,
thanks I really forgot to remove the brackets. Sometimes you just don't see
the forest for the trees.
However in SQL Server Management Studio Express I still cannot find the
server. (Unfortunately) I do have a german version of it, by translating the
names I can find Serverobjects and something that might be a translation of
linked servers. There is only another submenu called contractor (with a lot
of entries) and nowhere is my linked server.
Since I am just reading through all that stuff, could you or anybody else
also tell me how to query tables on the linked server? I hoped to see them
somewhere under the linked server in the management studio and get a first
hint by that...|||Sorry I was to fast, got the answer for my last question about querying the
linked server here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp|||...and after restarting the management studio I can see my linked server!|||Hi Thomas,
Yeah, I find that sometimes a fresh pair of eyes to look at something
is good...
>From what you are describing I think you have found the linked servers
node. I believe the submenu (contractor) you are talking about would be
the "Provider" menu in the English version. Just for information, this
menu lists the provides (e.g. OLE DB, Native, Jet) that you can use to
connect to a linked server. No need to worry about this menu so much.
It's strange that you can't see your linked server in the GUI. Have you
tried adding a linked server via Management Studio Express. You can add
a linked server by clicking on the linked servers node and selecting
"New Linked Server..." (not sure what that would be in German)|||> It's strange that you can't see your linked server in the GUI. Have you
> tried adding a linked server via Management Studio Express. You can add
> a linked server by clicking on the linked servers node and selecting
> "New Linked Server..." (not sure what that would be in German)
I can see it now, it just needs a refresh sometimes. Do you have some
experience about its performance regarding queries? I linked an oracle server
and when I query something it is relatively slow, even compared to Jet which
I used before.|||Not really, sorry. Are you running across a network? Maybe congestion
has something to do with it?
Also, I remember hearing that certain queries cause an entire result
set to be transmitted to your local server and not just the rows you
want. Don't remember any more than that, sorry.

Linked Server Hang up

My MS Access app links to an SQL Server 2000 db which includes a linked
server.
A pass through query like "select * from linkedtable" works fine. But
"select * from linkedtable where linkedtablekey=1" fails - ODBC timeout
error.
The same thing happens when I try the queries in Query Analyzer.
This error only occurs on the production computer - the queries work fine in
the test environment.
Thanks, in advance, for suggestions.
DT
I would check and see if schema of the production and test servers are
identicall (indexes, triggers, constraints...). Also, if indexes are not
set up properly and there is much more data on production server, it could
be the cause for time out (large scan operation on source)
MC
"David Taylor" <dreddog@.gmail.com> wrote in message
news:ekE%23ZPRoIHA.2292@.TK2MSFTNGP03.phx.gbl...
> My MS Access app links to an SQL Server 2000 db which includes a linked
> server.
> A pass through query like "select * from linkedtable" works fine. But
> "select * from linkedtable where linkedtablekey=1" fails - ODBC timeout
> error.
> The same thing happens when I try the queries in Query Analyzer.
> This error only occurs on the production computer - the queries work fine
> in the test environment.
> Thanks, in advance, for suggestions.
> DT
>

Linked Server Error

We had to reinstall MSSQL on one of our servers now when I set up a linked
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:

> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.

Linked Server Error

We had to reinstall MSSQL on one of our servers now when I set up a linked
server and try to associate a login I get the following error:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote
server 'TEST'.
Is there a fix for this it works from another server with the same edition
and configuration.
I figured it out:
ALTER SERVICE MASTER KEY FORCE REGENERATE
"davids@.thorntonsinc.com" wrote:

> We had to reinstall MSSQL on one of our servers now when I set up a linked
> server and try to associate a login I get the following error:
> Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
> An error occurred during decryption.
> Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
> There is no remote user 'sa' mapped to local user '(null)' from the remote
> server 'TEST'.
> Is there a fix for this it works from another server with the same edition
> and configuration.

Linked Server and Distributed Transactions

We have a developer that is trying to do a distributed
transaction using a View that in turn uses a Linked
Server.
The Linked server is the MSSQL 2000 server, so both
databases reside on the same server.
She is receiving the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: New transaction cannot enlist
in the specified transaction coordinator.]
Has anyone see this error? Is it an Option in the OLE
DB Provider or a Linked Server Option that is the problem?
I am able to do distributed transactions inside a MSSQL
Trigger that goes out to an Oracle DB and inserts data,
without an error. So, I am not sure why MSSQL cannot
use a Linked Server for a "local" database connection?
Any help is much appreciated,
MarkHi Mark,
"Mark" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:016401c3c668$e315c8e0$a401280a@.phx.gbl...
> We have a developer that is trying to do a distributed
> transaction using a View that in turn uses a Linked
> Server.
> The Linked server is the MSSQL 2000 server, so both
> databases reside on the same server.
> She is receiving the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
> provider returned message: New transaction cannot enlist
> in the specified transaction coordinator.]
> Has anyone see this error? Is it an Option in the OLE
> DB Provider or a Linked Server Option that is the problem?
> I am able to do distributed transactions inside a MSSQL
> Trigger that goes out to an Oracle DB and inserts data,
> without an error. So, I am not sure why MSSQL cannot
> use a Linked Server for a "local" database connection?
> Any help is much appreciated,
> Mark
>
Seems to be a Loopback-Situation.
See http://support.microsoft.com/?kbid=306212 and follow the Link "Loopback
Linked Servers"...
Patrick

Linked Server & XACT_ABORT

Is there anyway to set XACT_ABORT ON for a linked server? We have a linked
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it o
n
at the linked server level?
Thanks.
Paula.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF
."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:

>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> linked
> turn
> on
>
>

Linked Server & XACT_ABORT

Is there anyway to set XACT_ABORT ON for a linked server? We have a linked
server setup between SQL 2000 and DB2. To use transactions we need to turn
XACT_ABORT on in every single stored procedure. Is there a way to turn it on
at the linked server level?
Thanks.
Paul
a.. If an application has a local transaction and the option
REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
escalates the local transaction to a distributed transaction.
This can be set via sp_configure I beleive... I have never tried this, but
perhaps it is worth a few minutes of your time..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> Is there anyway to set XACT_ABORT ON for a linked server? We have a
linked
> server setup between SQL 2000 and DB2. To use transactions we need to
turn
> XACT_ABORT on in every single stored procedure. Is there a way to turn it
on
> at the linked server level?
> Thanks.
> Paul
>
|||Thank you for the reply. Unfortunately that did not work. I ran the
following command to turn that option on:
EXEC sp_configure 'remote proc trans', 1
I ran the following statements:
BEGIN TRAN
UPDATE D SET
D.Location = 999
FROM LS..DB2TDBM.DEPARTMENT D
WHERE D.ADMRDEPT = 'E01'
COMMIT TRAN
And got the following error, which is the same error when the Remote Proc
Trans is truned off:
"Unable to start a nested transaction for OLE DB provider 'IBMDADB2'. A
nested transaction was required because the XACT_ABORT option was set to OFF."
Any ideas?
Thanks.
Paul
"Wayne Snyder" wrote:

>
> a.. If an application has a local transaction and the option
> REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure
> escalates the local transaction to a distributed transaction.
> This can be set via sp_configure I beleive... I have never tried this, but
> perhaps it is worth a few minutes of your time..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2488655-A223-4C02-AAEF-33AD82485C53@.microsoft.com...
> linked
> turn
> on
>
>

Linked Server

What is the syntax for querying through a linked server without usiing openquery?
ie.
LinkedServer.Database.owner.table ??Yes, exactly that.|||What is the syntax for querying through a linked server without usiing openquery?
ie.
LinkedServer.Database.owner.table ??

Yes,

SELECT CustomerID
,CompanyName
FROM LinkedServer.Northwind.dbo.Customers