Dynamic SQL - 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 2005 Forums

Transact-SQL (2005)

Dynamic SQL

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2008-10-15 : 15:54:49

Hello All,

I get a syntax error while running the following query.

There a cursor to go through all the databases from different servers (subscribers and publishers). The cursor sets server name and database names to @SubscriberServer and @SubscriberDatabase respectively.


insert into execreplcmd
select 'insert into repldbsubscriber select ' + ''''+ dest_table + '''' + ',' 
+ ' count(*) from ' + @SubscriberServer + '.' + @SubscriberDatabase + '.dbo.' + dest_table + ' where  
+ convert (varchar(2000), filter_clause) 
from ' + @SubscriberServer + '.' + @SubscriberDatabase + '.dbo.' + 'sysarticles' + 
where filter != 0

Can some one help me?

Thanks in advance,
-P

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 18:25:31

[code]

select
'insert into repldbsubscriber select dest_table , count(*) from '+@SubscriberServer+'.'+@SubscriberDatabase+'.dbo.dest_table where convert (varchar(2000), filter_clause) from '+@SubscriberServer+'.'+@SubscriberDatabase+'.dbo.sysarticles where filter != 0 '[/code]

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2008-10-15 : 19:00:28

Thanks hanbingl.

That worked -:)

-P

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2008-10-16 : 00:30:18

Sorry Hanbingl. The query compiled successfully. but the results were wrong.

The result was

insert into repldbsubscriber select dest_table , count(*) from [servername].[databasename].dbo.dest_table where convert (varchar(2000),
filter_clause) from [servername].[databasename].dbo.sysarticles where filter != 0

dest_table is not a table it is a column in sysarticles table.

My query runs correctly if I hardcode the server name and database name. Following is a query. I get an error when I get server and database from variable through cursor as I have to do it on few servers.


select 'insert into repldbsubscriber select ' + ''''+ dest_table + '''' + ',' 
+ ' count(*) from [myserver].[mydatabase].dbo.' + dest_table + ' where ' + convert (varchar(2000), filter_clause) 
from [myserver].[mydatabase].dbo.sysarticles
where filter != 0

Following is the resultset. It shows all the articles as rows from sysarticles table.

insert into repldbsubscriber select 'Employees', count(*) from [myserver].[mydatabase].dbo.Employees where (ID IN (1,2))
insert into repldbsubscriber select 'Departments', count(*) from [myserver].[mydatabase].dbo.Departments where (ID IN (1,2))
ETC....

Please let me know where I am going wrong in dynamic sql.

Thanks,
-P

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-16 : 00:39:35

sqlpal, don't you think it would help if you posted the error and the code that you are actually using?

Use variables for myserver and mydatabase.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2008-10-16 : 09:00:27

Hi tkizer

If you see code in my previous post the query runs if I hard code server and database names

Here is a full code


create table replcmds
( execstatement varchar(2000))
create table replvalsubscriber
( tablename varchar(60), 
  reccount int
)

DECLARE @SubscriberServer VARCHAR(100),
		@SubscriberDatabase VARCHAR(100)

DECLARE ReplSites CURSOR 
   FOR SELECT SubscriberServer1, SubscriberDatabase1 FROM ReplicationDatabases --to get server and database

OPEN ReplSites
FETCH NEXT FROM ReplSites INTO @SubscriberServer, @SubscriberDatabase
WHILE (@@FETCH_STATUS <> -1)
BEGIN

insert into replcmds
select 'insert into replvalsubscriber select ' + ''''+ dest_table + '''' + ',' 
+ ' count(*) from ' + @SubscriberServer + '.' + @SubscriberDatabase + '.dbo.' + dest_table + ' where  
+ convert (varchar(2000), filter_clause) 
from ' + @SubscriberServer + '.' + @SubscriberDatabase + '.dbo.' + 'sysarticles' + 
where filter != 0

DECLARE getarticles CURSOR
   FOR SELECT execstatement FROM replcmds
DECLARE @execstatement varchar(2000);
OPEN getarticles
FETCH NEXT FROM getarticles INTO @execstatement
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  EXEC ( @execstatement)
  FETCH NEXT FROM getarticles INTO @execstatement
END

FETCH NEXT FROM ReplSites INTO @SubscriberServer, @SubscriberDatabase
END
CLOSE getarticles
DEALLOCATE getarticles

CLOSE ReplSites
DEALLOCATE ReplSites
GO

Following is an error
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'where'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-17 : 00:14:10

Do you see the + sign before your WHERE clause? It shouldn't be there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources