Internet solutions team

Accessing Different Database on Different SQL Servers
I was trying to Access two databases on two different servers. I was usring four part name to SQlServer
“[sqlserver02].MyDatabase.dbo.aspnet_membership” but it was not letting me do that and was showing following error message.

Msg 7202, Level 11, State 2, Line 1
Could not find server ’sqlserver02′ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Though server was present physically and name was 100% correct. You might think I must be using four part sql server name like this “[sqlserver02sqlserver02].MyDatabase.dbo.aspnet_membership”.
I tried above but it didn’t work, I googled it and found I should if “sqlserver02″ is being added to my sys.servers I tried to add using
Following Stored procedure which adds the server in sys.servers
sp_addserver ’sqlserver02′,’local’
and I got the message
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server ’sqlserver02′ already exists.
Then I used following select query
Select * from sys.servers
which returned all servers and I found that sqlserver02 name was different it was sqlserver02_Alias. I used “[sqlserver02_Alias].MyDatabase.dbo.aspnet_membership” and everthing worked fine.

Two methods to updated data from one table to another table using sql and TSQL,

T1=Table1

T2=Table2

F1=Field1

F2=Field2

F3=Filed3

F4=Field4

Method one, Update date using subquery from one table to another table

update T2 set

T2.F1=isnull(T1.F1,”),

T2.F2= T1.F2,

T2.F3= T1.F3 from T1

inner join T2 on T2.F2= T1.F4

where

T2.F2<> T1.F2 and T2.F3<> T1.F3

and isnull(T2.F1,”)<>isnull(T1.F1,”)

Method 2 Use While Loop and iterate throught whole table to update data using a temporary or temp table,  Choice is yours.

DECLARE @myERROR int

Declare @F4 varchar(50)

Declare @F1varchar(255)

Declare @F2bigint

Declare @F3 bigint

declare @counter int;

declare @count int;

–*****************Update Data

–1. Select common records in outputqas4, postcodelookup

select F4,F1, F2,F3 into ##tempEdit from T1 where F4 in(

select F4 from T1

intersect

select F4 from T2 )

–Set Counter

set @counter=1;

–Get total number of records from temp table

Select @count = Count(*) From ##tempEdit

While (@counter) < @count

Begin

Select Top 1 @F4 =F4,@F1=F1,@F2=F2,@F3=F3 From ##tempEdit

if not Exists(select * from T2 where F4 = @F4 and F1=@F1and @F3=@F3 and F2=@F2)

begin

–Update dispaly order

update T1 set F1=@F1,F3=@F3,F2=@F2 Where F4 = @F4

–Error handling

SELECT @myERROR = @@ERROR

print ‘Record updated for post code :’ + @F4

IF @myERROR != 0 GOTO HANDLE_ERROR

end

else

print ‘Record already exists’

–Delete entery from temp table

delete from ##tempEdit where F4=@F4

–Counter

set @counter= @counter +1

End