We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Linked Server Problem

Medium Priority
1,018 Views
Last Modified: 2012-05-06
Hello there.   I have a SQL Server on my machine.  I successfully link to a corporate database (BF-SQL3) using the sp_addlinkedsvr script and am able to see all tables associated with that server, as I am supposed to be.  However,  we are trying to migrate my SQL Server Database to another corporate wide server (BF-SQL1).  For some reason I cannot link the two!!!

-What MS SQL permissions do I need in order to allow this to happen?

Thanks!
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
You can run SQL Scripts which will work perfectly..
But if you use Stored Procedure, you need to enable MSDTC service and other set of things to make it work as given below:

http://support.microsoft.com/kb/839279
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Kindly give me the exact error message you have obtained so that I can guide you on it.
FYI, I worked on similar things before..

Author

Commented:
Thanks guys, I run the sp_addlinkedserver and that actually works!!  However, it will not allow me
access to the tables, which are protected with a UN/PASS combo.  While running the
sp_addlinkedsrvlogin I receive the following message:



Msg 15247, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 25
User does not have permission to perform this action.



Author

Commented:
BTW the script for that is:

EXEC sp_addlinkedsrvlogin 'SERVER', 'FALSE' ,null, 'TEST_USER', '*******'
 GO



Author

Commented:
Another interesting thing is that Im Using SQL Server Express 2008 on my machine and the linked server is 2005.  
I am reluctant to truly consider this an issue because whilst connect to my own machines server, both the aforementioned scripts work correctly. Hmm.
I believe I found a solution. If I hard code the SELECT statement from the linkserver.catalog.view I get the results, but for some reason, the objects themselves aren't visible in the GUI. Perhaps a compatability issue.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.