Hi. I had 2 SQL Server 2000 Standard servers. 1 was a master server, the second was the web server. there was a DTS job on the master server that coppied specific tables from the master to the web server.
We recently upgraded the hardware and decided it would be a good time to try upgrading the SQL server... however we were jjust going to start with the Web server.
I have installed SQL Server 2008r2 Web edition on the web server, the master server still has Server 2000 standard.
The DTS job cannot connect.
I tried to create a SQL Server Agent job, and configuring a Multi-server configuration with a Master/Target role, but I keep getting errors. If I try to set it up on the master server, I get an "Access Denied" message from the target server (2008r2 web)
If I try to configure from the target server, I get a "Login failed for user (null)" despite the fact that the connection used to the master server is done with SQL Auth using the dbo account.
So then I linked the web server to the master server in Server Objects. And I tried to build a "Insert into Webserver.db.dbo.table select from Masterserver.db.dbo.tablename" but that query just takes forever... I even put a limit on the select (TOP 100)
and it still wouldn't run quickly
View Complete Post