Hi all im trying to archive my database over two servers.
AND ICANT DO IT
lol
i have copied over the database. and got it on the new server it has all the fields and some prmary keys everything. I need to be able to do SET IDENTITY_INSERT products ON on the tables and do inserts from servera to serverb
I got the server linked (that i though) here is the code i used to link servers.
EXEC sp_addlinkedserver
@server = 'servB',
@srvproduct = 'servB',
@provider = 'SQLOLEDB'
when i ran a selection
select * from servA.dbname.dbo.table
thought it worked but actually it was getting data from the database i was connected at meaning it was returning
select * from table
(because the table names were the same. i didnt notice it didnt work until i emptied out the servA table)
Now i need help on the linked server thingy. OR any other suggestions to do this. I am going to write a vb code if i cant do it but i really dont want to lose that much time on it if i can do it in tsql. PLEASE HELP, its killing me.Were you at serverA or serverB? If you were at serverA and currently in "dbname", then select * from servA.dbname.dbo.table is going to be logically equivalent to select * from table in the case where there's only one "table" and the owner is dbo.
AND ICANT DO IT
loli have copied over the database. and got it on the new server it has all the fields and some prmary keys everything. I need to be able to do SET IDENTITY_INSERT products ON on the tables and do inserts from servera to serverb
I got the server linked (that i though) here is the code i used to link servers.
EXEC sp_addlinkedserver
@server = 'servB',
@srvproduct = 'servB',
@provider = 'SQLOLEDB'
when i ran a selection
select * from servA.dbname.dbo.table
thought it worked but actually it was getting data from the database i was connected at meaning it was returning
select * from table
(because the table names were the same. i didnt notice it didnt work until i emptied out the servA table)
Now i need help on the linked server thingy. OR any other suggestions to do this. I am going to write a vb code if i cant do it but i really dont want to lose that much time on it if i can do it in tsql. PLEASE HELP, its killing me.Were you at serverA or serverB? If you were at serverA and currently in "dbname", then select * from servA.dbname.dbo.table is going to be logically equivalent to select * from table in the case where there's only one "table" and the owner is dbo.