April 4, 2010 at 8:09 pm
Hello,
I am trying to write a code that will
1. Query sysarticles and syspublications to get unique publications for corresponding articles. so this will give me unique publications.
2. Now I want to have that publications name as input to sp_helparticle proc.
This is what I have done so far:
DECLARE @RowCnt INT
DECLARE @maxrows INT
DECLARE @ExecSql NVARCHAR(255)
DECLARE @Proc NVARCHAR (50)
DECLARE @publication SYSNAME
SELECT @rowCnt = 1
SELECT @Proc = 'sp_helparticle'
-- this CTE will give me publicationNames
; with PubName
as (SELECT distinct p.name AS PublicationName
FROM syspublications p
INNER JOIN sysarticles a ON p.pubid = a.pubid)
SELECT @maxrows = COUNT (*) FROM PubName
WHILE @RowCnt <= @maxrows
BEGIN
-- this will exec sp_helparticle procedure but need publication names ?
SELECT @ExecSql = 'exec' + ' ' + @proc + ' ' + ** I want publication names from cte here but cannot figure out how **
PRINT @ExecSql
SELECT @Rowcnt = @rowCnt + 1
END;
How can I have the output for all publicaitons in a single temp table ?
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
April 4, 2010 at 10:29 pm
Buddy, here is the code u had requested.. It will loop thro the PubName CTE and execute sp_helpArticle for each PublicationName without WHILE Loop. Please note that i have commented your code and i have added my code.
DECLARE @ExecSql NVARCHAR(255)
DECLARE @Proc NVARCHAR (50)
DECLARE @publication SYSNAME
SET @ExecSql = '' -- Added for your request
SELECT @Proc = 'sp_helparticle'
-- this CTE will give me publicationNames
; with PubName (PublicationName)
as (
SELECT distinct p.name AS PublicationName
FROM syspublications p
INNER JOIN sysarticles a ON p.pubid = a.pubid
)
/* -- YOU DONT NEED THIS WHILE LOOP ITSELF
SELECT @maxrows = COUNT (*) FROM PubName
WHILE @RowCnt <= @maxrows
BEGIN
-- this will exec sp_helparticle procedure but need publication names ?
SELECT @ExecSql = 'exec' + ' ' + @proc + ' ' + ** I want publication names from cte here but cannot figure out how **
PRINT @ExecSql
SELECT @Rowcnt = @rowCnt + 1
END;
*/
-- HERE IS THE CODE FOR UR REQUEST :
--** I want publication names from cte here but cannot figure out how **
SELECT @ExecSql = @ExecSql + 'EXEC sp_helparticle ''' + PublicationName + '''' + CHAR(10) FROM PubName
PRINT @ExecSql
Please inform us if tahts what u need 🙂
April 5, 2010 at 8:24 am
COldCoffee (4/4/2010)
Buddy, here is the code u had requested.. It will loop thro the PubName CTE and execute sp_helpArticle for each PublicationName without WHILE Loop. Please note that i have commented your code and i have added my code.
DECLARE @ExecSql NVARCHAR(255)
DECLARE @Proc NVARCHAR (50)
DECLARE @publication SYSNAME
SET @ExecSql = '' -- Added for your request
SELECT @Proc = 'sp_helparticle'
-- this CTE will give me publicationNames
; with PubName (PublicationName)
as (
SELECT distinct p.name AS PublicationName
FROM syspublications p
INNER JOIN sysarticles a ON p.pubid = a.pubid
)
/* -- YOU DONT NEED THIS WHILE LOOP ITSELF
SELECT @maxrows = COUNT (*) FROM PubName
WHILE @RowCnt <= @maxrows
BEGIN
-- this will exec sp_helparticle procedure but need publication names ?
SELECT @ExecSql = 'exec' + ' ' + @proc + ' ' + ** I want publication names from cte here but cannot figure out how **
PRINT @ExecSql
SELECT @Rowcnt = @rowCnt + 1
END;
*/
-- HERE IS THE CODE FOR UR REQUEST :
--** I want publication names from cte here but cannot figure out how **
SELECT @ExecSql = @ExecSql + 'EXEC sp_helparticle ''' + PublicationName + '''' + CHAR(10) FROM PubName
PRINT @ExecSql
Please inform us if tahts what u need 🙂
Hello,
Thanks for your reply. When I run the query, it is complaining about syspublications
Msg 208, Level 16, State 1, Line 24
Invalid object name 'syspublications'.
But when I run the CTE, I am able to get the results.
; with PubName (PublicationName)
as (
SELECT distinct p.name AS PublicationName
FROM syspublications p
INNER JOIN sysarticles a ON p.pubid = a.pubid
)
SELECT publicationName FROM PubName
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
April 5, 2010 at 8:24 am
COldCoffee (4/4/2010)
Buddy, here is the code u had requested.. It will loop thro the PubName CTE and execute sp_helpArticle for each PublicationName without WHILE Loop. Please note that i have commented your code and i have added my code.
DECLARE @ExecSql NVARCHAR(255)
DECLARE @Proc NVARCHAR (50)
DECLARE @publication SYSNAME
SET @ExecSql = '' -- Added for your request
SELECT @Proc = 'sp_helparticle'
-- this CTE will give me publicationNames
; with PubName (PublicationName)
as (
SELECT distinct p.name AS PublicationName
FROM syspublications p
INNER JOIN sysarticles a ON p.pubid = a.pubid
)
/* -- YOU DONT NEED THIS WHILE LOOP ITSELF
SELECT @maxrows = COUNT (*) FROM PubName
WHILE @RowCnt <= @maxrows
BEGIN
-- this will exec sp_helparticle procedure but need publication names ?
SELECT @ExecSql = 'exec' + ' ' + @proc + ' ' + ** I want publication names from cte here but cannot figure out how **
PRINT @ExecSql
SELECT @Rowcnt = @rowCnt + 1
END;
*/
-- HERE IS THE CODE FOR UR REQUEST :
--** I want publication names from cte here but cannot figure out how **
SELECT @ExecSql = @ExecSql + 'EXEC sp_helparticle ''' + PublicationName + '''' + CHAR(10) FROM PubName
PRINT @ExecSql
Please inform us if tahts what u need 🙂
Hello,
Thanks for your reply. When I run the query, it is complaining about syspublications
Msg 208, Level 16, State 1, Line 24
Invalid object name 'syspublications'.
But when I run the CTE, I am able to get the results.
; with PubName (PublicationName)
as (
SELECT distinct p.name AS PublicationName
FROM syspublications p
INNER JOIN sysarticles a ON p.pubid = a.pubid
)
SELECT publicationName FROM PubName
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
April 5, 2010 at 11:09 am
Hello,
Sorry my bad .. your code works fine .. Thanks a lot 🙂
Also, is there a way that I can use this code's output and compare with a standard table. The standard table has only 1 row and it is like a base line that has to be evaluated with the output of sp_helparticle and if any column value is not matched then it should email out.
Writing code is what I am learning now.
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
April 5, 2010 at 11:09 am
Hello,
Sorry my bad .. your code works fine .. Thanks a lot 🙂
Also, is there a way that I can use this code's output and compare with a standard table. The standard table has only 1 row and it is like a base line that has to be evaluated with the output of sp_helparticle and if any column value is not matched then it should email out.
Writing code is what I am learning now.
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply