How to declare values in DTS? 1

[Jozopiso]

Hi Everybody,

i'm transfering one big SQLscript into DTS package. Here is my problem. Whem i'm puting 'Create procedure' from that script into stored procedures, it always tells me that I didn't declare values(@WorkForce INT,@Recr_ID INT)...here is code from that script:

-- Create Import Procedures
if exists (select * from sysobjects where id = object_id(N'Truncate_import_tables') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Truncate_import_tables]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Truncate_import_tables
AS

truncate table Import_Person
truncate table Import_PersonJob
truncate table Import_PersonEducation
truncate table Import_PersonSkill


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from sysobjects where id = object_id(N'Import_temp_data') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [Import_temp_data]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Import_temp_data
@WorkForce INT,
@Recr_ID INT
AS
begin transaction Import

declare @id_person int
declare @id_othercat int
......
.....
.....
etc

AND I WOULD LIKE TO CALL THOSE "PROCEDURES" FROM STORED PROCEDURES LIKE:
-- Create Import Procedures


SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

exec Truncate_import_tables



SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON





SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON


exec Import_temp_data

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

THANX VERY MUCH

 

[gradley]

My guess is that the Execute SQL task in DTS sees the @ prefix and assumes it is a variable.

You may want to try creating the Create Procedure statement as a string stored in a variable and then using the EXEC

Ex:

Declare @strCmd varchar(500)

SET @strCmd = 'CREATE PROCEDURE Import_temp_data
        @WorkForce INT,
        @Recr_ID INT
    AS
    begin transaction Import

    declare @id_person int
    declare @id_othercat int
     etc.....'

EXEC(@strCmd)

Hope this helps!

 

[gradley]

One other note; You should avoid using the GO word in DTS. If I'm not mistaken "GO" is a keyword recognized only by Query Analyzer as a method for separating scripts into individual batches. It is not a recognized command in T-SQL.