Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Server to SQL 2000


Linked Server to SQL 2000

Author
Message
russds
russds
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 94
Hello, sorry, newbie with question. I'm pulling my hair out and hoping someone here smarter than me can point me in the right direction.
I'm setting up a linked server in SQL Server 2008 R2 to a server with SQL 2000 on it. Everything goes smooth, I'm using MSQLSMS -> Server Objects -> Linked Servers right click, New linked server. i add the server, and the local login mapped to remote login. All works fine, but when i try to run the stored procedure I get error message:

OLE DB provider "SQLNCLI10" for linked server "SQL2000SERVER.COM" returned message "Client unable to establish connection".
Msg 20, Level 16, State 1, Line 0
Encryption not supported on SQL Server.

And i'm just stuck with that, I've tried various provider types, and what not, but nothing pans out. I've googled, and it looks like it could be a 64bit machine to a 32bit machine connection issue. Any direction or possible threads in this forum would help. I'm still searching threads around here.

Thanks!
-Russ
sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
did you check RPC and RPC out options in linked server properties ?
Make it true and Try

-----------------------------------------------------------------------------
संकेत कोकणे
russds
russds
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 94
yes, i did check those as true.
I believe the problem to be around the fact that the 2008 machine is 64 bit. I have setup the linked server on a 32 bit machine and that works fine.

Is anyone aware of issues with linking a 32bit machine with a 64bit machine?
Thanks!
Robert klimes
Robert klimes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1847 Visits: 3408
have a look at this http://sqlblog.com/blogs/roman_rehak/archive/2009/05/10/issue-with-64-bit-sql-server-using-sql-2000-linked-server.aspx. not exactly the same error but same symptom.

Bob
-----------------------------------------------------------------------------
How to post to get the best help
russds
russds
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 94
Thanks Bob. I had seen that before, different error message and plus I don't have access to the SQL 2000 machine, so I didn't fully go down that path. However i did find a solution for those interested:

This is what I did:

1. Create ODBC connection to SOMESERVER.COM, call it dsnSOME
2. Create a Linked Server to dsnSOM:
- Linked server: "SOMESERVER" (I don't think it matters)
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Product Name: dsnSOME
- Data source: dsnSOME
And that's it. Now when I call the stored procedure I call:

[SOMESERVER].database.owner.storedprocedure

And it works!

It seems that for whatever reason, when trying to run a stored procedure on SQL 64 bit to a SQL 32 bit linked server directly, it gives this weird message about Encryption. However going through the ODBC and it works great.
Robert klimes
Robert klimes
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1847 Visits: 3408
check this out although i think it's a moot point because it would require you to change settings on the 2000 box

http://support.microsoft.com/default.aspx?kbid=318605

Bob
-----------------------------------------------------------------------------
How to post to get the best help
russds
russds
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 94
Thanks Bob. Yeah I had found that article too during my research. As you said, trouble is i don't have access to the SQL 2000 box. I'm not sure why it was giving me the Encryption error message, but oh well, I got it working using odbc connection. Thanks for your help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search