Improve company productivity with a Business Account.Sign Up

x
?
Solved

How do I fix a Query fails on linked server

Posted on 2012-03-16
19
Medium Priority
?
800 Views
Last Modified: 2012-03-25
SCENARIO:
I have 3 SQL Servers running: SERVER A, SERVER B, SERVER C

A table on SERVER A where trigger a INSERTS data on SERVER B and then calls Stored Proc (SP) on SERVER B.

The SP on SERVER B updates that same table with data retrieved from SERVER A and SERVER C. This is where it fails.

If I copied both tables to SERVER B the SP executes successfully... So I know the syntax is correct.
The problem occurs when and ONLY when I attempt to UPDATE the table on SERVER B with the join that links back to SERVER A.  Any ideas.

In Visual studio or enterprise mgr. and do the single query to that table it passes the results.
0
Comment
Question by:LIFEEXPERT
  • 9
  • 7
  • 2
  • +1
19 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 37733268
Hi,

My first thought is that htere is too much happening in one trigger firing.

That is, calling a stored procedure on another server is holding an implicit transaction too long, so this is likely to not scale well.

How have you created your linked servers? What is the security like on them?

So you have a linked server to A from B,  to B from A and to B from C - Correct?

Regards
  David
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37733472
What is the exact error you are getting? It can be a double hop problem. And yup I second david's comment to make it simpler.
0
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 37737030
You have linked servers created in all servers, so they can connect to each other? And those linked servers are configured to allow RPC?
If you post the error message will help.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 

Author Comment

by:LIFEEXPERT
ID: 37737322
dtodd:
My first thought is that htere is too much happening in one trigger firing.

That is, calling a stored procedure on another server is holding an implicit transaction too long, so this is likely to not scale well.
This was my first thought as well until I copied the tables to the server.

How have you created your linked servers?
What is the security like on them?

So you have a linked server to A from B,  to B from A and to B from C - Correct?

   Please explain.. not sure what you are looking for:
    Server A.. has RPC, RPC OUT set to FALSE (I had changed this to TRUE, but it didn't make a difference so I changed it back to the way it was)
    Server B.. has RPC, RPC OUT set to TRUE
    Server C..I was in error... SERVER C is actually a different Database on SERVER B.

Security is set to SQL Server authentication and Windows Authentication Mode.


TempDBA..
Where would I find the error... none is being shown... it doesn't show anything.. I had tried trapping the error by running as stored proc and it generated something like...
The OLE DB provider "SQLCLI" for linked SERVER B does not contain the VIEW/TABLE name. The table either does not exists or the current user does not have permissions on that table.
I checked the permissions for SA and it looks a though they have rights to everything.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37737539
So, sa has the sysadmin role on all the servers right. And your link servers are running using sa login?
0
 

Author Comment

by:LIFEEXPERT
ID: 37737579
TempDBA: Yes to both of your questions
0
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 37737736
Can you post the sp code? At least the T-SQL command that are trying to access the linked server?
0
 

Author Comment

by:LIFEEXPERT
ID: 37738230
EXEC SERVERB.DATABASE.DBO.PROCNAME, @ID_1, @NAME
0
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 37738246
I meant the code inside the sp, not the code for calling the sp.
0
 

Author Comment

by:LIFEEXPERT
ID: 37738366
This is the portion that if remarked out it works fine...if not it fails

UPDATE T1            
    SET      A= RTRIM(T5.DDD) + '@HAPPY.NET' ,
      B= RTRIM(T2.NAME) ,
      C= (SELECT RTRIM(NAME) + '@HAPPY.NET' FROM SERVERB.DATABASE.DBO.PEOPLE WHERE (ID = T5.SSS)) ,
      D= (SELECT RTRIM(LNAME) + ', ' + RTRIM(FNAME) FROM SERVERB.DATABASE.DBO.PEOPLE WHERE (ID = T5.SSS)),
      E= T5.AAA,
      F= 1
FROM EMAIL_TABLE T1
LEFT OUTER JOIN SERVERA.DATABASE.DBO.ABC AS T2 ON T1.ID = T2.ID
LEFT OUTER JOIN SERVERB.DATABASE.DBO.DEF AS T5 ON T2.ID = T5.ID
WHERE T1.ID= @ID1

I also was able to determine that the if I remarked out :
LEFT OUTER JOIN SERVERA.DATABASE.DBO.ABC AS T2 ON T1.ID = T2.ID
and replaced it with a  local table on SERVERB it would work...
0
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 37738445
There is an issue with query linked server tables/views:
The base table or view should be created with QUOTED_IDENTIFIER and ANSI_NULLS set to ON.
Can you check that?
0
 

Author Comment

by:LIFEEXPERT
ID: 37738533
They are both set to ON
0
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 37738623
Can you change your query and use OPENQUERY instead of direct reference?
Example:
SELECT * FROM OPENQUERY(ServerB, 'SELECT * FROM Database.dbo.People')
0
 

Author Comment

by:LIFEEXPERT
ID: 37738665
Are you suggesting...create a table variable for that table. Use your query to insert into it... then join that to the update query?
0
 

Author Comment

by:LIFEEXPERT
ID: 37738824
Please keep in mind that if I run this from SERVER B. It has no problems executing as originally written. However, I did tried the OPENQUERY and work also if run from SERVER B.

SERVER A is still the not executing.

It's as if the Trigger won't allow an update to a table that isn't on its own server.
0
 
LVL 54

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 37741507
I tried to find information about issues between triggers and linked servers but didn't find nothing relevant.
Have you thought about replicating data between databases instead of updating with trigger?
0
 

Accepted Solution

by:
LIFEEXPERT earned 0 total points
ID: 37741517
I had to come up with a make shift solution. I had all the data gathered and prepared on SERVER A then had the trigger insert it into the table on to SERVER B. Don't know why but this works.
0
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 37742610
Unfortunaly I couldn't get it :(
Only found an issue with deleted and inserted tables, but seems like you aren't use them.
A least you resolved your problem.

Cheers
0
 

Author Closing Comment

by:LIFEEXPERT
ID: 37762380
initial problem was not resolved, but found and alternative
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question