September 3, 2009 at 10:29 pm
Hi all
i have connecting two databases in different servers using the following statements:
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = 'ServerName')
begin
EXEC sp_dropserver @server=ServerName'', @droplogins='droplogins';
end
EXECUTE sp_addlinkedserver @server = 'ServerName'
exec sp_addlinkedsrvlogin @rmtsrvname =N'ServerName',@useself ='false',@locallogin ='sa',@rmtuser ='sa',@rmtpassword ='pwd'
and it works fine....
but at some cases am getting the follwing Error:
Could not find server 'ServerName' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Am conneting this servers only at login time in web....
Please help me...
September 4, 2009 at 12:25 am
EXEC sp_dropserver @server=ServerName'', @droplogins='droplogins';
That's not even going to run. You'll get a syntax error because the quotes are in the wrong place.
What line does it say is generating the error?
Is this code being generated at login time?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 4, 2009 at 12:36 am
Thanks for ur reply
the code error (quotes) is made when typing......
i have given correctly in Procedure.....
yes this Procedure is runing at Login and Logout time to read values from other database.....
September 4, 2009 at 12:48 am
Please post the procedure with a copy-and-paste, instead of typing?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 4, 2009 at 1:22 am
This is my procedure:
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ServerName')
begin
EXEC sp_dropserver @server=N'ServerName', @droplogins='droplogins';
end
EXECUTE sp_addlinkedserver @server = N'ServerName'
exec sp_addlinkedsrvlogin @rmtsrvname =N'ServerName',@useself ='false',@locallogin ='sa',@rmtuser ='sa',@rmtpassword ='pwd'
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempRelease]') AND type in (N'U')) DROP TABLE [dbo].[TempRelease];
declare @SQL varchar(max)
set @SQL = 'select Top 3 RelNO, RelDate INTO dbo.[TempRelease]
from [ServerName].CRE.dbo.tablename where RelDate >= getdate()-2 order by RelDate';
exec(@SQL);
select * from TempRelease
drop table TempRelease
EXEC sp_dropserver @server=N'ServerName', @droplogins='droplogins';
and This is the Error am getting occasionally
Could not find server 'ServerName' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. Cannot find the object "TempRelease" because it does not exist or you do not have permissions.
September 4, 2009 at 6:13 am
Can multiple copies of this procedure run at the same time? Perhaps when two users are logging at once?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2009 at 9:27 pm
Sorry i was out of station for past two days.....
S it runs....
September 6, 2009 at 11:24 pm
and This is the Error am getting occasionally
Could not find server 'ServerName' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. Cannot find the object "TempRelease" because it does not exist or you do not have permissions
Are you getting this error for any specific server, or random server?
The server(s), for which you are getting error, are located in local LAN or any remote places?
"Don't limit your challenges, challenge your limits"
September 7, 2009 at 1:24 am
the server is located in LAN only......
and the error is not for specific server.....
September 7, 2009 at 1:47 am
Are you able to connect to linked server(s) through SSMS?
Also check if there is any firewall effecting? Is all your server running with default port; OR any of ports are blocked by firewall (if there is any)?
Or any Anti-virus software is running during your error?
"Don't limit your challenges, challenge your limits"
September 7, 2009 at 2:42 am
yes, I am able to connect to linked server(s) through SSMS....
And the servers are running with default port only.... and no Anti-virus software is running....
September 7, 2009 at 5:24 am
Please try this,
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'ServerName')
begin
EXEC sp_dropserver @server=N'ServerName', @droplogins='droplogins';
end
GO
EXECUTE sp_addlinkedserver @server = N'ServerName'
exec sp_addlinkedsrvlogin @rmtsrvname =N'ServerName',@useself ='false',@locallogin ='sa',@rmtuser ='sa',@rmtpassword ='pwd'
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempRelease]') AND type in (N'U')) DROP TABLE [dbo].[TempRelease];
declare @SQL varchar(max)
set @SQL = 'select Top 3 RelNO, RelDate INTO dbo.[TempRelease]
from [ServerName].CRE.dbo.tablename where RelDate >= getdate()-2 order by RelDate';
exec(@SQL);
GO
select * from TempRelease
drop table TempRelease
GO
EXEC sp_dropserver @server=N'ServerName', @droplogins='droplogins';
"Don't limit your challenges, challenge your limits"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply