Syntax Error while running a stored procedue

Joined
Feb 21, 2018
Messages
216
Reaction score
86
ALTER PROCEDURE [dbo].[ServerRefUpdate]
@str varchar(999),@server varchar(50)


AS
BEGIN
set @str = 'Update [dbo].[Lists] SET ASREFSER='' + @server + '' where isnull(ASREFSER,'')='+''''

exec ( @str )



Please help me resolve the following error message

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')='.
 
Last edited:
Joined
Feb 21, 2018
Messages
216
Reaction score
86
Try this instead:

SQL:
set @str = "Update [dbo].[Lists] SET ASREFSER='" + @server + "' where isnull(ASREFSER,'')=''"
I tried but unfortunately still no luck....
Strange thing is the insert command using the @server variable is working but Update/SET is failing...


set @str = 'insert into Products select * from ['+@server+'].seregation.[dbo].[Products] P where convert(varchar,P.ProductID)+P.ProductName not in (select distinct convert(varchar,ProductID)+ProductName from Products A) and P.Productid in (select A.ProductId from ['+@server+'].seregation.dbo.batches A, ['+@server+'].seregation.dbo.Products B where A.status=1 and A.ProductID=B.ProductID)'

exec ( @str )



set @str = "Update [dbo].[Lists] SET [ASREFSER]='" + @server + "' where isnull([ASREFSER],'')=''"

exec ( @str )

error msg is as follows:
Msg 207, Level 16, State 1, Procedure LoadSubscriberData, Line 38
Invalid column name 'Update [dbo].[Lists] SET [ASREFSER]=''.
Msg 207, Level 16, State 1, Procedure LoadSubscriberData, Line 38
Invalid column name '' where isnull([ASREFSER],'')='''.
 

Abraham Andres Luna

Child of God
Joined
Mar 14, 2018
Messages
699
Reaction score
227
So don't you need the server name to be a part of the update statement?

Code:
set @str = "Update [Seregation_Master].[dbo].[Lists] SET [ASREFSER]='" + @server + "' where isnull([ASREFSER],'')=''"
 
Joined
Feb 21, 2018
Messages
216
Reaction score
86
So don't you need the server name to be a part of the update statement?

Code:
set @str = "Update [Seregation_Master].[dbo].[Lists] SET [ASREFSER]='" + @server + "' where isnull([ASREFSER],'')=''"
Yes. I dont need to update remote server....Records are being pulled from remote server and inserted into the local db....
I was lucky to resolve this issue by making the following change: Abraham ! thanks a lot for your precious time & help .


BEGIN
UPDATE Lists
SET
ASREFSER=@server
Where isnull(ASREFSER,'')=''
END
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top