October 19, 2011 at 10:00 am
Hi there,
I'm trying to run an Execute SQL Task using a linked server.
Code in Execute SQL Task on a SQL Server Connection
SELECT *
FROM LinkedServer.DB.dbo.RFSH_Table
where ServerName = (Select @@ServerName)
The linked server is also a sql server.
I receive the error that the linked server does not exist. However, it works fine on SSMS
Error
[Execute SQL Task] Error: Executing the query "SELECT.." failed with the following error: "Could not find server 'LinkedServer' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any help greatly appreciated.
¤ §unshine ¤
October 19, 2011 at 12:13 pm
Sounds like permissions. What account is being used to run the SSIS package? How is the linked server security setup?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2011 at 12:55 pm
The linked server is using security to be made using the login's current security context. The SSIS Package is created and being tested using sql service. The query runs just fine fine from SSMS.
It errors as an execute sql task on SSIS. Result set is set to none. Server source is using windows authentication logged in as sql service.
¤ §unshine ¤
October 19, 2011 at 1:00 pm
can I ask why you are using a linked server to talk to the other server and not connecting directly to it from SSIS?
CEWII
October 25, 2011 at 12:03 pm
Because we need a join between the 2 servers for information.
I found the solution. It was that SSIS was not running as the same account sql services were running. Changing this worked.
🙂
¤ §unshine ¤
October 25, 2011 at 1:38 pm
I am very glad it worked out for you but I would strongly encourage moving away from linked servers in nearly all cases.
CEWII
October 25, 2011 at 1:55 pm
Elliott Whitlow (10/25/2011)
I am very glad it worked out for you but I would strongly encourage moving away from linked servers in nearly all cases.CEWII
I'm afraid I'll have to disagree with you there, Elliott. Merge Join in SSIS = Evil. I'd much rather run anything that doesn't lend itself intelligently to a Lookup down at the engine level.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2011 at 2:04 pm
Evil Kraig F (10/25/2011)
Elliott Whitlow (10/25/2011)
I am very glad it worked out for you but I would strongly encourage moving away from linked servers in nearly all cases.CEWII
I'm afraid I'll have to disagree with you there, Elliott. Merge Join in SSIS = Evil. I'd much rather run anything that doesn't lend itself intelligently to a Lookup down at the engine level.
Then we will have to agree to disagree, linked servers are no less evil and depending on how you do the join just as poorly performing. Don't get me wrong, I don't like Merge Join either but I dislike Linked Servers more. With that said, there IS a place for each.
CEWII
October 25, 2011 at 2:16 pm
Elliott Whitlow (10/25/2011)
Then we will have to agree to disagree, linked servers are no less evil and depending on how you do the join just as poorly performing. Don't get me wrong, I don't like Merge Join either but I dislike Linked Servers more. With that said, there IS a place for each.
Oh, I'll agree with that. It can cost more getting a flatfile into a table and staging it for engine work then simply doing a memory sort and merge-joining enroute, or if you had to do transformations before doing a join.
I'm just not against linked servers when the data is already engine loaded, and I hate stream blockers. It doesn't help that I'm in a war for memory on a "dedicated" SSIS server atm, either.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2011 at 2:19 pm
I've been "lurking" on this thread to see some different opinions. I have to say I agree with Elliot so far. If I'm using SSIS I'd avoid linked servers. Yes the Merge Join is bear, but, in my experience, doing a JOIN across a linked server is just as bad. With linked servers I'd usually find out how to get the subset of data I need from the linked server into a temp table BEFORE doing the join if I can. I also usually use an SP on the Linked Server to do that to keep as much of the processing on the linked server as possible. Just how I've seen linked servers work best. So, with all of that, I think it is easier to maintain and probably performs as well in SSIS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 25, 2011 at 2:30 pm
Jack Corbett (10/25/2011)
I've been "lurking" on this thread to see some different opinions. I have to say I agree with Elliot so far. If I'm using SSIS I'd avoid linked servers. Yes the Merge Join is bear, but, in my experience, doing a JOIN across a linked server is just as bad. With linked servers I'd usually find out how to get the subset of data I need from the linked server into a temp table BEFORE doing the join if I can. I also usually use an SP on the Linked Server to do that to keep as much of the processing on the linked server as possible. Just how I've seen linked servers work best. So, with all of that, I think it is easier to maintain and probably performs as well in SSIS.
Under a lot of circumstances where a merge join was a possibility the problem was less the foreign data (restricted linked server data that I'll pull after processing foreignly), but having to resort the primary data to allow for the merge join is just too expensive in memory and time. Everything has their niche, but I truly consider Merge Join a last resort or a special case scenario.
Admittedly I may just not use it enough to know all the tricks to not end up with a few gigs of data hanging around in memory getting resorted.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply