how to call a sql server stored procedure?

[kranthi]

Hi,

I am using DS 7.5.2 and my job design is as follows.

PX job:
XMLInput -> transformer -> ODBC enterprise(sql server)

I am basically reading data from xml and inserting into the DB. I have a new requirement now to call a stored procedure which drops all the foreign key constraints before loading the DB.

I tried to look to see if I could use a STP, but I see only three options. There's no sql server vendor option. After reading through forums, I understand I need to use a DRS stage.

Can someone guide me on how to use DRS stage to call a stored procedure?

Do I have to import this stored procedure into manager before calling it in DRS?

Your help is much appreciated!

Thanks!
kk

[Nagin]

There is a before and after sub tabs in SQL tab in DRS stage, you can paste the sql for dropping foreign keys in the before tab. When the job executes, the sql in the before tab gets executed first and then the main sql for loading the table executes.

[kranthi]

When I pasted the following sql in the before tab to drop constraints, and ran my job, I am getting the following warning:

Dynamic_RDBMS_243,0: Warning: PJ_OCC_EVE_ETL_TDctyDocsTypeDB.Dynamic_RDBMS_243: SQLExecDirect: Error executing statement 'SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc spForeignKeyconstraintNOCHECK
as
set nocount on
declare @TableName varchar(255),@FKeyName varchar(255),@RecID int
if object_id('tempdb..#AllUserTables1')>0 drop table #AllUserTables1
create table #AllUserTables1
(
RecID int identity(1,1)
,TableName varchar(255) null
,FKeyName varchar(255) null
)
insert #AllUserTables1 (TableName,FKeyName)
select TableName = object_name(parent_obj),FKeyName = name
from sysobjects
where type='F'
order by object_name(parent_obj),name
while (select count(*) from #AllUserTables1)>0
begin
select top 1 @TableName=TableName,@FKeyName=FKeyName,@RecID=RecID from #AllUserTables1
exec ('alter table ' + @TableName + ' NOCHECK CONSTRAINT ' + @FKeyName)
delete #AllUserTables1 where RecID=@RecID
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
'. See following database message for details.

Any ideas on solving this? I am using sql server 2000.

[bkumar103]

Try putting EXEC <Stored procedure name>

[chulett]

When I pasted the following sql in the before tab to drop constraints...

That's not "sql", as in not a sql statement - that's a procedure. Which is why the suggestion to simply call or execute it instead.

[blue3zer0]

The code that you pasted above will create or alter the stored procedure and needs to be run in Enterprise Manager so that the procedure is ready to be called from DS. When you call the procedure from DS you would the use the format.

Code: Select all

exec spForeignKeyconstraintNOCHECK

This will run the procedure.