Linked server - cant exec procedure - can run procedure select????
-
I've got a linked server set up, I can't exec a procedure on the local machine that does a cross-database query to the linked server. I know the procedure works on a different server, so it's not the procedure itself. I can actually copy the SELECT and run that in QA succesfully. RPC and RPC out are on in the linked server connection. The user is a dbo owner and is being impersonated.
What's going on?
:wq -
Bump...
Anyone?
:wq -
try this way:
execute [remoteservername].[databasename].[dbo].[spStoredProc]
-
Can you post the error message and the part of the query that involves the reference to the linked server.
Also to be clear, you have server A and server B. You run a procedure from Query Analyzer on server A that selects from a table on server B and it fails because of some kind of login failure. You copy the select statement out of the procedure, paste it into the same query analyzer session and it runs successfully. Is all of this exactly correct?
dk
-
Yes that is correct. I'm executing a procedure on server A that's selecting from a table on server B.
I don't get an error, I haven't let the procedure run to an error or timeout. It's just churning (I've let it run for about 2 minutes). On the production server, the query returns in less than 1 second. The procedure runs in the production environment fine, so the only thing that's changing in this scenario is server A.
This is the part that references the linked server:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
DECLARE @Total INT
SELECT
--removed for clarity
FROM CALLSWAREHOUSEOLE.Calls_DB.dbo.Calls
WHERE
Username = @login
AND MONTH(DATEADD(SECOND,LogoffTime-AcctSessionTime,DATEADD(HOUR,DATEDIFF(HOUR,GETUTCDATE(),GETDATE()),'Jan 01 1970 12:00AM'))) = @MonthI AND YEAR(DATEADD(SECOND,LogoffTime-AcctSessionTime,DATEADD(HOUR,DATEDIFF(HOUR,GETUTCDATE(),GETDATE()),'Jan 01 1970 12:00AM'))) = @YearI
ORDER BY
LogoffTime DESC
:wq -
Are you physically on the Local machine using query analyser?, or are you using query analyser from your workstation to the local machine?
If you are connected via your workstation, you will need to map your login to the linked server user, but that will get it working only for you, so you can test. If that works then map the local server user account to the linked server user. This is all done in Security-Linked Server, right click on you linked server, and select security and add the mapping there.
If you are using a SQL Server linked server type, you may need to execute sp_setnetname 'linkedservername', 'actualservername'. This maps the linked server connection to a particular server.
Failing that, post the error messages.
Mark S
-
I'm on my workstation, logged in to QA as the login that's being impersonated in the server mapping. I'm not using a SQL Server link, I'm using the "other data source" type. I'll termserv in to the server and see if that makes a difference.
:wq -
If you are not getting an error message could it be a network problem that is causing the select statement to be very slow returning the data? I would expect an error message immediately if there was a problem with your linked server setup. Did you say that you can run the select statement on server A and retrieve data from server B in less than a second like on the production server?
-
I can paste the select statement from the procedure, paste it into QA and it returns a resultset in 3 seconds. I try to exec the procedure from the same QA window and it just runs and runs...
That leads me to believe it's not an issue with the linked server set up - unless it's an RPC issue. I've got RPC and RPC out checked in the linked server properties.
:wq -
Sorry, you just went beyond my knowledge of linked servers.
This may sound dumb but as one last suggestion, is there any possibility that the sql that you are running is not the same as the sql that is compiled in the procedure? You may want to recompile it. Or is there something else going on in the procedure before or after the select statement that would cause it to hang? It seems like the procedure should be using the same connection as the QA session where you ran the select statement. That suggests a problem within the procedure to me.
Hopefully someone else has a better idea.
Good luck
dk
-
Okay, I finally just let the procedure run and it completed after 20 minutes
It seems that when I run the procedure on the test server, it's not using the index. When I run it on the production server, the index is being hit. However, that is just a theory because I can't see the execution plan since it's being called on a remote server. It seems that the remote server would be the one to decide whether or not to use an index?
The 2 procedures are exact, I scripted (is that a word
TGIF) it off the production server.So moving with the theory that it's not a network issue and not a linked server issue - how can I test the index issue.
BTW, there are 127,588,461 records in the table, so that would explain 20 minutes without an index.
:wq -
I just wanted to follow up on this issue. I finally just moved the procedure to the remote server and created a procedure on the local server to wrap the remote call. This works fine, but it still bugs me that it didn't work like it does on the production server. I've got to drive on with the heaps of work on my plate, so it will be the quick fix for now. I may call Microsoft support on this and see if they can resolve the issue. If I do, I'll post a reply here in case someone has this issue in the future.
Thanks of all the replies on this.
:wq -
I had a simmilar issue over linked server. Although it was Oracle as Linked Server, but the problem were simmilar.
A query of this format
SELECT COL1, COL2, COL3 FROM SERVERA..OWNER.TABLE
WHERE COL1 = @a
works quickly when @a AND COL1 are numeric values. but took a huge amount of time when it were char.
i did some investigations on query plans for both. Both case it used a remote query in execution plan. my findings were that when remote query is executed, there were two distinct cases
for numeric , where condition was part of remote query (hence returned few records from linked server)
for character data, where condition was NOT part of remote query (hence entire table fetched by remote query and then a FILTER applied by local server).
I replaced this query WITH OPENQUERY
SELECT COL1, COL2, COL3 FROM OPENQUERY(SERVERA, "SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 'AAAA' " )
and it worked correctly AND quickly.
I suggest you look at estimated query plan for your procedure.
-
SQL server 2000 linked server problem
S2 is added in S1 as Linked server. I am trying to run SP1 in Server1 that in turn calls Sp2 in server2. there is no other machine involved in this test.
I am getting following error:
Server: Msg 7399, Level 16, State 1, Procedure SP1, Line 3
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ].
I tried both way, mixedmode and windows authentication. Did not work.
Any idea? Help...
What am I supposed to have in Linked Server following Property?
Product Name
Date Source
Provider String
I left those blank.
-
I know it is a while since this/theese question was posted, but I had the same prob and no answer ..... so I will post my answer all the same:
1.st part of Thread- Has to do with the Congifuration of the linkserver - its quicker with numeric, cause sorting is different and can be done on remote server.
If linkserver isnt configured right, then Filters on alphanumeric datatypes such as char, varchar etc. will resort in retrieving the whole amount of data and applying the filter on the local server. This is to avoid problems arising from Servers have different settings as opposed to sorting. (Codepage). If both Servers have the same sorting options, then tell the linkserver so, and the sorting will be done on remote server giving back only the datasets, that apply to the filter conditions. If the sorting settings are different, you can tell the linkserver, the codepage of the remote server.
This drastically sped up a gfew of my queries - from 45 Secs to under a second
2nd Part of Thread:
When I got that Error I realized (but i admit it did take me a while) that I had the linkserver added, but i hadn't set a user to login to the remote database. So Was hte user set??? Did that user have the right proveleges on remote server???
greetings from Germany
nano
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply