Connect to indivitual database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Connect to indivitual database

Author

Topic

kernelvn
Starting Member

4 Posts

Posted - 2007-05-10 : 02:58:33

I need to connect to 1 of 2 database server in an individual situation of Mirroring and failover occur. In this case: t-sql insert data to partnera server, but when failover is happen, it re-direct to partnerb server

Here is my code and the problem is in the red line when using ":connect <servername>" code:

declare @ConnectA as smallint
declare @id as bigint
:setvar Database2Mirror Mirroring

set @ConnectA = 1
set @id = 1

while (1 > 0) begin
set @id = @id + 1
--print @id
begin try
if (@ConnectA = 1)
begin
print 'Connect to A'
:connect partnera use $(Database2Mirror)
end
else
begin
print 'Connect to B'
:connect partnerb use $(Database2Mirror)
end
insert into dbo.UserInfor (IncreaseNumber) values (@id)
end try
begin catch
print 'Failover happenning when insert number ' + @id;
if (@ConnectA = 1)
set @ConnectA = 2
else
set @ConnectA = 1
continue
end catch
end

Even I tried to put it in IF THEN ELSE but it alway jump to :connect partnerb (the partnerb server is in norecovery status and there for an error of unaccessable happen)

Please help hot to control connection to databaserver without using ":connect databaseservername"!!

Thanks & Best regards,

cvkhanh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources