In SQL Server 2000, I'm trying to create a linked server to SQL Server 2008. Everything I've tried raises errors of one flavor or another.
In SQL Server 2000 Enterprise Manager, I:
- Right-click Linked Servers under the server's Security node, then choose New Linked Server... and name the link "SQL2008R2"
- I choose the "Other data source" radio button because I don't think SQL Server 2000 can natively connect to 2008. 开发者_JS百科
- I choose "SQL Server Native Client 10.0" in the Provider name field, type "SQL2008R2" in the Product name field, enter "VIRTUALXP-62910\SQLEXPRESS" (Servername and named instance name) in the Data source field, leave Provider string blank, and type the database name "RWUtils" in the Catalog field.
- On the security tab, I type "Link" as the local login and as the Remote user, and enter the password. I choose the "Not be made" radio button for logins not listed above. I previously set up a "Link" user on both servers, with the same password, with access to the right databases.
- I click OK to create the link. The SQL2008R2 link is created.
When I expand the link I just created and click on Tables, I get the error,
Error 7416: Access to the remote server is denied because no login-mapping exists.
Can anyone tell me what I'm doing wrong or what the correct sequence of steps or T-SQL is to create this link successfully?
Thanks much.
I just did this for the first time (after seeing your question) and it worked - going from SQL 2000 Standard 32-bit (default instance) to a named instance on SQL 2008 R2 64-bit.
Using similar steps to what you have above:
- fine
- choose "SQL Server" as the Server Type, enter servername\instance
- not applicable
- I chose "Be made using this security context", and entered in my new SQL Standard Account that exists on the target server only (2008 R2).
- I tested with a query and it worked. Be sure new SQL Standard user has access to the database and table. Query should look like: select * from [linkedservername\instance].[database].[dbo].[table]
Other notes: I took the defaults on the Server Options tab, which are "Data Access", "RPC", and "RPC Out" all checked.
The other direction: I previously had it working the other direction from SQL 2008 R2 linked server to SQL 2000. That required this: http://blog.raffaeu.com/archive/2008/06/19/sql-2005-and-linked-server-cannot-obtain-the-schema-rowset.aspx.
I have use this steps and configuration options:
SqlServerEnterpriseManager\Security\Linked Servers\New Linked Server
- General:
- Linked server: mylinkedservername
- Provider name: Microsoft OLE DB Prvider for SQL Server
- Product name:
- Data Source: (my server name)
- Provider string:
- Location:
- Catalog:
- Security:
- Be made using this security context:
- Remote login: (my sql remote user. I use sa for the test)
- With password: (my remote password)
- Server Options:
- DataAccess: checked
- RPC: checked
- RPC Out: checked
- Use Remote Collation: checked
- General:
Qry used in the test:
select * from [linkedservername].[database].dbo.[table]
精彩评论