我试图将Windows身份验证上的生产数据库（SQL Server 2000）链接到混合模式身份验证上的测试数据库（SQL Server 2008）。
我已经从这个链接尝试了下面的代码：
DECLARE @LinkName SYSNAME SET @LinkName = 'PRODUCTIONSERVER' DECLARE @SrvName SYSNAME SET @SrvName = 'PRODUCTIONSERVER' DECLARE @LocalLogin SYSNAME SET @LocalLogin = 'sa' --login on test db DECLARE @RmtLogin SYSNAME SET @RmtLogin = 'DOMAIN\UserName' --win auth login on prod db DECLARE @RmtPwd SYSNAME SET @RmtPwd = 'password' --win auth password for the login on prod db IF NOT EXISTS (SELECT * FROM Master..Sysservers WHERE IsRemote = 1 AND SrvName = @LinkName) BEGIN EXECUTE sp_addlinkedserver @server = @LinkName, @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @SrvName EXECUTE sp_addlinkedsrvlogin @rmtsrvname = @LinkName, @useself = 'false', @locallogin = @LocalLogin, @rmtuser = @RmtLogin, @rmtpassword = @RmtPwd END...已成功添加链接服务器，但当我尝试查询时：
SELECT COUNT(*) FROM PRODUCTIONSERVER.ProdDatabase.dbo.ProdTable我收到一个错误：
Msg 18456, Level 14, State 1, Line 0 Login failed for user 'DOMAIN\UserName'.我也尝试使用提到的Windows身份验证凭据（我的个人登录）登录prod数据库，我可以通过。
链接的原因是因为我想测试实时数据（从sql 2000）到测试数据库（sql 2008）的合并。
关于如何链接服务器的任何想法？
非常感谢你。 :)
I am trying to link a production database (SQL Server 2000) that is on Windows authentication to a test database (SQL Server 2008) that is on mixed mode authentication.
I have already tried the code below from this link:
DECLARE @LinkName SYSNAME SET @LinkName = 'PRODUCTIONSERVER' DECLARE @SrvName SYSNAME SET @SrvName = 'PRODUCTIONSERVER' DECLARE @LocalLogin SYSNAME SET @LocalLogin = 'sa' --login on test db DECLARE @RmtLogin SYSNAME SET @RmtLogin = 'DOMAIN\UserName' --win auth login on prod db DECLARE @RmtPwd SYSNAME SET @RmtPwd = 'password' --win auth password for the login on prod db IF NOT EXISTS (SELECT * FROM Master..Sysservers WHERE IsRemote = 1 AND SrvName = @LinkName) BEGIN EXECUTE sp_addlinkedserver @server = @LinkName, @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @SrvName EXECUTE sp_addlinkedsrvlogin @rmtsrvname = @LinkName, @useself = 'false', @locallogin = @LocalLogin, @rmtuser = @RmtLogin, @rmtpassword = @RmtPwd END...a linked server is successfully added but when I try a query like:
SELECT COUNT(*) FROM PRODUCTIONSERVER.ProdDatabase.dbo.ProdTableI get an error:
Msg 18456, Level 14, State 1, Line 0 Login failed for user 'DOMAIN\UserName'.I have also tried logging into the prod database using the mentioned windows authentication credentials (my personal login) and I can get through.
The reason for the linking is because I want to test the merging of live data (from sql 2000) into the test database (sql 2008).
Any ideas on how I can link the servers?
Thank you very much. :)
最满意答案
通过SSIS包管理解决方法（感谢同事的帮助）：
将生产到测试服务器的数据导入到SQL2008数据库中创建的临时表中 执行MERGE脚本（在SQL任务中），该脚本执行从临时表到实际表的更新以进行更新Managed to have a workaround (thanks to a colleague's help) by an SSIS package that:
Imports the data from production to test server into a temp table that was created in the SQL2008 database Executes the MERGE script (in a SQL task), which performs the merges from the temp table into the real table to be updated更多推荐
发布评论