October 20, 2005 at 9:47 am
I have a table in SQL Server 2000 which has three columns ...
Statement, ServerName1, ServerName2
I am wanting to join together the contents of the three columns and then execute this string. To join the contents of the columns I am using ...
Select [Statement] + '''' + [ServerName1] + '''' + ', ' + '''' + [ServerName2] + '''' From MyTable
That statement gives me the following ...
Exec sp_MYprocedure 'MyServer1', 'MyServer2'
I am then using the following piece of code to execute one of the rows in the table ...
declare @sql nvarchar(4000)
select @sql = (Select Top 1 [Statement] + '''' + [ServerName1] + '''' + ', ' + '''' + [ServerName2] + '''' From MyTable)
--print @sql
exec sp_executesql @sql
The table contains multiple rows, therefore, what is the best way to write a cursor to loop through this table and execute the @sql statement?
Thanks in advance.
www.sqlAssociates.co.uk
October 20, 2005 at 10:05 am
This is a more evolved solution than you need but you'll get the idea :
IF object_id('Data') > 0
DROP TABLE dbo.Data
GO
IF object_id('Target') > 0
DROP TABLE dbo.Target
GO
IF object_id('Transfers') > 0
DROP TABLE dbo.Transfers
GO
CREATE TABLE dbo.Data (nbr int not null primary key clustered)
GO
Insert into dbo.Data (nbr) values (24)
Insert into dbo.Data (nbr) values (64)
Insert into dbo.Data (nbr) values (35)
Insert into dbo.Data (nbr) values (7536)
Insert into dbo.Data (nbr) values (3)
Insert into dbo.Data (nbr) values (1)
Insert into dbo.Data (nbr) values (38)
Insert into dbo.Data (nbr) values (-14)
Insert into dbo.Data (nbr) values (-4)
Insert into dbo.Data (nbr) values (0)
GO
CREATE TABLE dbo.Target (nbr int not null primary key clustered, WhereCond varchar(100) not null)
GO
CREATE TABLE dbo.Transfers (WhereCond varchar(100) not null primary key clustered)
GO
Insert into dbo.Transfers (WhereCond) values (' WHERE nbr 0 and nbr 64')
GO
Select * from dbo.Data
Select * from dbo.Transfers
Select * from dbo.Target
GO
EXEC master.dbo.xp_execresultset 'Select ''Insert into dbo.Target (nbr, WhereCond) Select nbr, '''''' + WhereCond + '''''' from dbo.Data '' + WhereCond As execQuery from dbo.Transfers', 'test'
GO
Select * from dbo.Target --everything except 64
GO
DROP TABLE dbo.Data
DROP TABLE dbo.Target
DROP TABLE dbo.Transfers
October 20, 2005 at 10:17 am
Hi,
Thanks for that. The example works but when I change it to reference my tables it falls over.
As the data volumes will be very small (less than 50 rows) is there anyway this can be placed inside a cursor?
Thanks in advance.
www.sqlAssociates.co.uk
October 20, 2005 at 10:20 am
Yup... but it's faster to write with xp_execresultset.
Not really any better solution here as they both have to use some sort of looping so you use whatever you preffer.
October 20, 2005 at 10:24 am
Hi,
Do you know where I can find out how to execute the statement below using xp_execresultset? The code below basically creates a string that contains
exec sp_Myprocedure 'param1', 'param2'
How can I then take the @sql and execute it using xp_execresultset?
declare @sql nvarchar(4000)
select @sql = (Select Top 1 [Statement] + '''' + [ServerName1] + '''' + ', ' + '''' + [ServerName2] + '''' From MyTable)
--print @sql
exec sp_executesql @sql
Thanks for your help, much appreciated.
www.sqlAssociates.co.uk
October 20, 2005 at 11:50 am
Please post the table definition, sample data and I'll create the script for you...
October 20, 2005 at 11:58 am
The table that contains the data is ...
CREATE TABLE [dbo].[MyTable] (
[Statement] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[ServerName] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[ReplaceValue] [varchar] (30) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
The table data is ...
INSERT INTO IPMDBA_t_ServerName
VALUES ('EXEC sp_FindReplace', 'ServerName1', 'ServerName2')
The stored procedure that is called from the data in the table above is in another post I have in this T-SQL forum. The post is called Find And Replace Script Needs Filtering
Thank you for all your help, its really, really appreciated!
www.sqlAssociates.co.uk
October 20, 2005 at 12:00 pm
Sorry, the script to insert data into the table should have read ...
INSERT INTO MyTable
VALUES ('EXEC sp_FindReplace', 'ServerName1', 'ServerName2')
www.sqlAssociates.co.uk
October 20, 2005 at 12:17 pm
EXEC master.dbo.xp_execresultset 'SELECT Statement + '' '''''' + ServerName + '''''', '''''' + ReplaceValue + '''''''' AS ExecStatement FROM dbo.MyTable', 'DB you want the code executed from'
October 20, 2005 at 12:22 pm
Hi,
Thanks for that. I've got an error ....
Error converting data type varchar to bit.
Any ideas?
www.sqlAssociates.co.uk
October 20, 2005 at 12:26 pm
Hi,
I've even tried inserting the entire string into MyTable2, and then running ...
EXEC master.dbo.xp_execresultset 'select * from MyTable2', 'MyDB'
And it returns the following ...
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'EXEC'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'IPMDBA'.
www.sqlAssociates.co.uk
October 20, 2005 at 12:26 pm
The parameters are not all varchars and of variable datatypes???
You'll have to quote the varchars in the raw data directly and remove the quotes from the select statement (4 per column for each side of the string).
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply