Validating all published databases

Question

Post reply

Validating all published databases

Roust_m

SSCoach

Points: 17410

December 26, 2003 at 2:41 am

#81813

Hi!

I am trying to run a validation procedure on all articles for all published databases on the server. Because of difficulties of running 'USE mydb' instruction for a dynamic query I use this construction:

SELECT @procname = @db_name + '..sp_executesql sp_article_validation ''' +  @publication + ''', ''' + @article + ''', ' 

+ CAST(@rowcount_only AS varchar(1)) + ', ' + CAST(@full_or_fast AS varchar(1))

PRINT @procname

get this:



mydb..sp_executesql sp_article_validation  'ACCOUNTING_Data', 'ACCOUNTING_ARTICLE', 2, 0

Everything seems correct, but if I run:

 

SELECT @procname = @db_name + '..sp_executesql sp_article_validation ''' +  @publication + ''', ''' + @article + ''', ' 

+ CAST(@rowcount_only AS varchar(1)) + ', ' + CAST(@full_or_fast AS varchar(1))

EXEC (@procname)

I get:

 

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'ACCOUNTING_Data'.

this doesn't work too:

 

SELECT @procname = @db_name + '..sp_executesql sp_article_validation ''' +  @publication + ''', ''' + @article + ''', ' 

+ CAST(@rowcount_only AS varchar(1)) + ', ' + CAST(@full_or_fast AS varchar(1))

EXEC @RC=@procname

just get:



Server: Msg 2812, Level 16, State 62, Line 61

Could not find stored procedure 'mydb..sp_executesql sp_article_validation 'ACCOUNTING_Data', 'ACCOUNTING_ARTICLE', 2, 0'.

How can I change my code to make it run?

Thanks.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

quote:
Hi!
I am trying to run a validation procedure on all articles for all published databases on the server. Because of difficulties of running 'USE mydb' instruction for a dynamic query I use this construction:
SELECT @procname = @db_name + '..sp_executesql sp_article_validation ''' + @publication + ''', ''' + @article + ''', '
+ CAST(@rowcount_only AS varchar(1)) + ', ' + CAST(@full_or_fast AS varchar(1))
PRINT @procname
get this:

mydb..sp_executesql sp_article_validation 'ACCOUNTING_Data', 'ACCOUNTING_ARTICLE', 2, 0
Everything seems correct...

But it's not; try executing that statement from QA. You need to have the first parameter of the sp_executesql proc as a nvarchar string, e.g.:





exec mydb..sp_executesql N'sp_article_validation ''ACCOUNTING_Data'', ''ACCOUNTING_ARTICLE'', 2, 0'

quote:
How can I change my code to make it run?
Thanks.

Try something like this:



SET @procname = @db_name + '..sp_article_validation ''' +  @publication + ''', ''' + @article

                 + ''', ' + CAST(@rowcount_only AS varchar(1)) + ', ' + CAST(@full_or_fast AS varchar(1))

EXEC (@procname)

--Jonathan