Syntax Error In SQL Statement - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Syntax Error In SQL Statement

Author

Topic

tomhobbs
Starting Member

3 Posts

Posted - 2011-01-06 : 06:59:02

Hi All,

I'm trying to experiment with Nonpartitioned, Bidirectional, Transactional Replication and I've copy and pasted the Microsoft sample script from here [url]http://msdn.microsoft.com/en-us/library/ms147929.aspx[/url]. However, it seems to have some kind of syntax error which I can't find. Specifically;

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ','.

By commenting out parts of the script I've narrowed it down to the following snippet;

USE test1
GO

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscription_db AS sysname;
SET @publication = N'two_way_pub_test1';
SET @subscriber = $(SubServer2);
SET @subscription_db = N'test2';
/*
EXEC sp_addsubscription @publication = @publication, 
    @article = N'all', 
	@subscriber = @subscriber, 
    @destination_db = @subscription_db, 
	@sync_type = N'none', 
    @status = N'active', 
	@update_mode = N'read only', 
    @loopback_detection = 'true';
*/
EXEC sp_addpushsubscription_agent 
	@publication = @publication, 
	@subscriber = @subscriber, 
	@subscriber_db = @subscription_db, 
	@subscriber_security_mode = 0,
	@subscriber_login = $(Login), 
	@subscriber_password = $(Password);
GO

I had to modify the script slightly to set the security mode to 0 and replace the "job_login" with "subscriber_login" because I'm using SQL Server authentication.

The only thing that is different with running the script this time is how I've defined the variable "SubServer2", namely;

:setvar SubServer2 @@ServerName

I'm running the query in Management Studio with the query in SQLCMD Mode. I can't see anything wrong with the above though. To make matters worse, this is not my first experiment with this particular script, I have had it working before.

I'm running SQL Server 2008 R2 on a Windows Server 2008.

Can anyone help me shed some light on this?

Many thanks,

Tom

Kristen
Test

22859 Posts

Posted - 2011-01-06 : 07:22:14

Is the "$(parameter)" stuff PHP or somesuch?

maybe the parameter values being inserted contain a comma? (perhaps because you have the field name used twice on the HTML form, and thus you have an "array" of values?)

And if it is a $(parameter) substitution beware of SQL injection

tomhobbs
Starting Member

3 Posts

Posted - 2011-01-06 : 08:22:02

The $(parameter) stuff is sqlcmd scripting variables. I have them defined at the top of the query like this;

:setvar Login N'SQL';
:setvar Password N'password'
:setvar SubServer1 @@ServerName
:setvar SubServer2 @@ServerName

I'm not worried about sql injection because they are all defined locally in the script. It's not user input as such.

Kristen
Test

22859 Posts

Posted - 2011-01-06 : 09:07:35

Does sqlcmd substitute them as a string, or as a variable?

if it is as a String you will need some quotes around them in the assignment.

Take them out (for now) and hardwire some strings in their place for a test?

tomhobbs
Starting Member

3 Posts

Posted - 2011-01-10 : 05:21:35

Thanks for the suggestion, you put me on the right track.

I added the following lines;

DECLARE @login AS sysname;
DECLARE @password AS sysname;
set @login = $(Login);
set @password = $(Password);

and changed

@subscriber_login = $(Login), 
@subscriber_password = $(Password);

to read

@subscriber_login = @login, 
@subscriber_password = @password;

And the syntax error goes away. Of course, now the question become or, "Why doesn't the script I downloaded from the Microsoft page work?"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources