help required - 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 2000 Forums

Import/Export (DTS) and Replication (2000)

help required

Author

Topic

swati
Starting Member

2 Posts

Posted - 2003-04-30 : 01:01:57

Hello,

I am trying to run the replication script using a stored procedures where I
will allow user to send databseName etc as the input parameters to the
procedure.The procedure then internally calls the system procedures required
for replication using the inout parameter values.When I trying to create the
procedure it gives me error as 'Must declare the variable '@databaseName'

I am pasting the part of the code I am using ,Please tell me where I am
going wrong and why this error is coming.

create procedure spDynamicReplicationNew
(
@databaseName varchar(100) = 'N%',
@publication varchar(100),
@snapshotName varchar(100),
@tableName varchar(50)
)

AS
-- Enabling the replication database
--use master
GO
use[master]

exec sp_replicationdboption @dbname = @databaseName ,
@optname = N'merge publish', @value = N'true'
GO

Thanks in advance

Regards,
Swati

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-30 : 02:49:58

quote:

GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. SQL Query Analyzer and the osql and isql command prompt utilities implement GO differently. For more information, see osql Utility, isql Utility, and SQL Query Analyzer.

Your stored proc was being created to do nothing.

Once you removed the GO you would have gotten this error

Server: Msg 154, Level 15, State 1, Procedure spDynamicReplicationNew, Line 12
a USE database statement is not allowed in a procedure or trigger.

The key is to use the Catalog.Schema.Object Convention for calling the sproc.

There that's all better now.

create procedure spDynamicReplicationNew
(
@databaseName varchar(100) = 'N%',
@publication varchar(100),
@snapshotName varchar(100),
@tableName varchar(50)
)

AS

exec master..sp_replicationdboption @dbname = @databaseName ,
@optname = N'merge publish', @value = N'true'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources