?
Solved

Syntax error in SQL STATEMENT

Posted on 2011-04-24
4
Medium Priority
?
380 Views
Last Modified: 2012-05-11
Hello,

I try to execute this query by linked server and I have an error :
Could not find server '@SrvAbonne' 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.


DECLARE @SrvAbonne varchar (50)
DECLARE @DbCible varchar (50)
DECLARE parcours_init_repl CURSOR FAST_FORWARD FOR SELECT a.SQLTEXT as LigneSQL,a.name as ProcName  
FROM ['+@SrvAbonne + '].['+ @DbCible + '].dbo.maj_replsp  a
OPEN parcours_init_replfinance
FETCH NEXT FROM parcours_init_repl INTO @strLigneSQL,@strProcName
WHILE @@FETCH_STATUS = 0

How can I resolve it?

Thanks

Regards

bibi

0
Comment
Question by:bibi92
  • 2
4 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 35456021
You define @SrvAbonne and @DbCible, but didn't set them to a value. If you don't, they are empty, and you get a syntax error.
If you omitted those settings by mistake only when preparing your question, you need to make sure the server in @SrvAbonne is defined in fact as a Linked Server in Enterprise Manger / Management Studio.
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 2000 total points
ID: 35456022
DECLARE @SrvAbonne varchar (50)
DECLARE @DbCible varchar (50)


Insert into #Temp
Exec ('SELECT a.SQLTEXT as LigneSQL,a.name as ProcName  
FROM ['+@SrvAbonne + '].['+ @DbCible + '].dbo.maj_replsp  a')

DECLARE parcours_init_repl CURSOR FAST_FORWARD FOR SELECT a.SQLTEXT as LigneSQL,a.name as ProcName  
FROM #temp  a
OPEN parcours_init_replfinance
FETCH NEXT FROM parcours_init_repl INTO @strLigneSQL,@strProcName
WHILE @@FETCH_STATUS = 0
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35456060
Oh yes, of course you cannot use dynamic SQL in-midst of the cursor declaration. Since syntax is checked on compile time, the approach as posted by imran_fast is correct.
0
 

Author Closing Comment

by:bibi92
ID: 35456114
Thanks bibi
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

809 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