T-SQL correct or not??? - 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)

T-SQL correct or not???

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-05-28 : 00:38:25

Hi

I am writing T-SQL pls tell me wheather it is correct syntax or not

DECLARE @Chg1 VARCHAR(500)
SET @Chg1 = 'declare @AntID numeric exec casp_Switch_BackupData @AntID = #ANTID#'
SET @Chg1 = REPLACE(@Chg1,'#ANTID#','@AntID')
EXEC (@Chg1)

As I am getting following output
Command(s) completed successfully.

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 00:53:22

Can youspecify what you are trying to achieve? I dont think you require dynamic SQL here.

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-05-28 : 01:00:15

Hi,

i have .sql file where i need to convert #ANTID# into @AntID and then i need to pass variable @AntID
that i get from application.

I am using Execute SQL Task as SSIS for it where i defined @AntID in it. Previously it was using DTS and vb code
where each line is checked and if found #ANTID# then it replaces by @AntID and variable passed to it.

now we convert our vb code to c# and DTS to SSIS.

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 01:16:03

quote:
Originally posted by under2811

Hi,

i have .sql file where i need to convert #ANTID# into @AntID and then i need to pass variable @AntID
that i get from application.

I am using Execute SQL Task as SSIS for it where i defined @AntID in it. Previously it was using DTS and vb code
where each line is checked and if found #ANTID# then it replaces by @AntID and variable passed to it.

now we convert our vb code to c# and DTS to SSIS.

T.I.A

Why are you pssing the string #ANTID# to casp_Switch_BackupData data? what does that sp do?

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-05-28 : 01:33:42

hi,

it doing some creation of back up table and insert data into these tables and then do some buissness logic and again delete these tables if that @AntID exists..its previous version application requirement...

so i m not able to do search and replace in SSIS by checking each and every line in SP, actually it is some line part code of my SP
so i take that much in string variable and do replace and again execute that statement...

basic for that VB and DTS uses is

/***************************************/
insert dbo.Mess (RuleTy,State,MessText)
select '2 - SQL2','S','Creating Stored Procedures Completed : ' + convert(varchar(30),getdate())

insert dbo.Mess (RuleTy,State,MessText)
select '3 - SQL3','S','Backup/Delete Existing User Edits Started : ' + convert(varchar(30),getdate())

declare @sql varchar (100)
select @sql = 'exec sp_dboption ''' + db_name() + ''', ''SELECT INTO/BULKCOPY'', true'
exec (@sql)
GO
exec casp_Switch_BackupData @AntID = #ANTID#
GO

casp_HelpDelEdits @AntID = #ANTID#
GO

declare @sql varchar (100)
select @sql = 'exec sp_dboption ''' + db_name() + ''', ''SELECT INTO/BULKCOPY'', false'
exec (@sql)
GO

insert dbo.Mess (RuleTy,State,MessText)
select '3 - SQL3','S','Backup/Delete Existing User Edits Ended : ' + convert(varchar(30),getdate())

T.I.A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 02:04:16

Ok . Then isnt this enough?
DECLARE @Chg1 VARCHAR(500)
SET @Chg1 = 'declare @AntID numeric exec casp_Switch_BackupData @AntID = '+@AntID
EXEC (@Chg1)

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2008-05-28 : 02:09:02

hi,
i dont think so as vb code and DTS what we previously using we have .sql file that contains #ANTID# so i need to use that only....

T.I.A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources