[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Could not find server '+@SrvAbonne + ' in sys.servers

Posted on 2011-04-24
15
Medium Priority
?
348 Views
Last Modified: 2012-08-13
Hello,

I try to execute this query :

DECLARE @SrvAbonne varchar (50)
DECLARE @DbCible varchar (50)
DECLARE @strLigneSQL nvarchar(max)
DECLARE @strSQL nvarchar (max)
DECLARE @strProcName nvarchar(max)
DECLARE @DossierX3 varchar (50)
DECLARE @compteur integer
BEGIN TRY 
SET NOCOUNT ON 
SET XACT_ABORT ON
SET @SrvAbonne = @SrvAbonne
SET @DbCible = @DbCible
SET @DossierX3 = @DossierX3 
SET @compteur=0
DECLARE parcours_TableSQL CURSOR FAST_FORWARD FOR SELECT a.SQLTEXT as LigneSQL,a.name as ProcName 
FROM [+@SrvAbonne + ].[+ @Dbcible + ].dbo.[sage_maj_replsp] a
OPEN parcours_TableSQL  
FETCH NEXT FROM parcours_TableSQL INTO @strLigneSQL,@strProcName
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET QUOTED_IDENTIFIER OFF 
IF EXISTS(SELECT * FROM [+@SrvAbonne + ].[+ @Dbcible + ].sys.objects where type='P' AND NAME= @strProcName)
BEGIN
SET @strSQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@strProcName) +']' 
exec sp_executesql @strSQL  
print @strSQL
END

Open in new window


I have this error :
Could not find server '+@SrvAbonne + ' in sys.servers

How can I resolve it, please?

Thanks
bibi
0
Comment
Question by:bibi92
  • 8
  • 3
  • 2
  • +2
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35457165
FROM [+@SrvAbonne + ].[+ @Dbcible + ].dbo.[sage_maj_replsp] a
must be:

FROM ['+@SrvAbonne + '].['+  @Dbcible + '].dbo.[sage_maj_replsp] a
0
 

Author Comment

by:bibi92
ID: 35457201
I have try this but I have always the same error:
Could not find server ''+@SrvAbonne + '' in sys.servers.
0
 

Author Comment

by:bibi92
ID: 35457213
I have the @srvabonne from a table

DECLARE parcours_init_repl CURSOR FAST_FORWARD FOR SELECT distinct ServeurDestination as SrvAbonne,
BaseAbonne as DbCible
FROM master.dbo.Init_Repl
OPEN parcours_Init_repl
FETCH NEXT FROM parcours_Init_repl INTO @SrvAbonne,@DbCible                              
WHILE @@FETCH_STATUS = 0
Thanks
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 26

Expert Comment

by:tigin44
ID: 35457219
check theese seetings

SET @SrvAbonne = @SrvAbonne
SET @DbCible = @DbCible
SET @DossierX3 = @DossierX3


you should assing the correct values for these variables...
0
 

Author Comment

by:bibi92
ID: 35457232
I have the correct value, there are in a table. But like @srvabonne contains \  like SRV\OD, I have to add [ ] in the code.
0
 

Author Comment

by:bibi92
ID: 35457244
I have try :

DECLARE parcours_TableSQL CURSOR FAST_FORWARD FOR SELECT a.SQLTEXT as LigneSQL,a.name as ProcName
FROM '['+ @SrvAbonne + ']'.'['+ @Dbcible + ']'.dbo.[maj_replsp] a
Msg 102, Level 15, State 1, Procedure sage_maj_proc_X3, Line 368
Incorrect syntax near '['.
0
 
LVL 26

Expert Comment

by:tigin44
ID: 35457287
try it this way
DECLARE @SrvAbonne varchar (50)
DECLARE @DbCible varchar (50)
DECLARE @strLigneSQL nvarchar(max)
DECLARE @strSQL nvarchar (max)
DECLARE @strProcName nvarchar(max)
DECLARE @DossierX3 varchar (50)
DECLARE @compteur integer
DECLARE @sql  varchar(1000)
DECLARE @count	int
BEGIN TRY 
	SET NOCOUNT ON 
	SET XACT_ABORT ON
	SET @SrvAbonne = @SrvAbonne
	SET @DbCible = @DbCible
	SET @DossierX3 = @DossierX3 
	SET @compteur=0
	SET @sql =	'DECLARE parcours_TableSQL CURSOR FAST_FORWARD FOR '+ 
				'SELECT a.SQLTEXT as LigneSQL,a.name as ProcName '+
				'FROM ['+@SrvAbonne+'].['+@Dbcible+'].dbo.[sage_maj_replsp] a'
	EXEC (@sql)
	OPEN parcours_TableSQL  
	FETCH NEXT FROM parcours_TableSQL INTO @strLigneSQL,@strProcName
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
		SET QUOTED_IDENTIFIER OFF 
		SET @count = 0
		SET @sql = 'SELECT '+ @count + '= ISNULL(COUNT(*), 0) FROM ['+@SrvAbonne+'].['+@Dbcible+'].sys.objects where type=''P'' AND NAME= '+@strProcName
		EXEC (@sql)
		IF @count > 0
		BEGIN
		SET @strSQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@strProcName) +']' 
		--exec sp_executesql @strSQL  
		print @strSQL
		END
	END
END TRY
BEGIN CATCH
	SELECT @@ERROR
END CATCH

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35457337
The value of @SrvAbonne has to be a linked server.  If it is not than you are simply out of luck.
0
 

Author Comment

by:bibi92
ID: 35457360
The print @strsql returns 16457
0
 
LVL 26

Expert Comment

by:tigin44
ID: 35457373
you should umcomment the

   exec sp_executesql @strSQL  

line...

I tried and and worked for my connection...
0
 

Author Comment

by:bibi92
ID: 35457389
Hello, I have this error Conversion failed when converting the varchar value 'SELECT ' to data type int. #245
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35458109
Please check first server is exists or not using "select * from sys.servers" on master
0
 

Author Comment

by:bibi92
ID: 35458230
The server exists. Thanks bibi
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35458839
you put too many quotes.

I wrote :
FROM ['+@

and not any from these:
FROM [''+@
FROM ["+@
FROM '['+@
0
 

Author Closing Comment

by:bibi92
ID: 35459961
Thanks bibi
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question