This is all you need in the stored procedure.

CREATE PROCEDURE sp_test(
    @paramater1 varchar(10)
    , @parameter2 varchar(100)
)
AS
SET NOCOUNT ON 
INSERT INTO tblName (value1, value2) VALUES (@paramater1 , @parameter2 )
SELECT @new_id...

Probably the best option is to keep your DTS import as a  simple Datapump and transfer the data into a seperate table. Then just use standard T-SQL to handle you...

Put the code you've got into a stored procedure. Pass the parameters for the insert in as input parameters, capture the return from SCOPE_IDENTITY() in the output parameters.

Nice one AJ I think you may have been censored

Yuvraj, take a look at this post...

Hmmm ... been ages since I worked with MDX, and I haven't used MDX in Reporting Services, but shouldn't you have something in the statement that acts as the parameter?

For...

These two articles might be useful reading as well.

http://msdn.microsoft.com/library/en-us/script56/html/sgWorkingWithFiles.asp?frame=true

http://msdn.microsoft.com/library/en-us/dnclinic/html/scripting03092004.asp?frame=true

And this has a ton of code that you could grab some examples from.

http://msdn.microsoft.com/library/en-us/script56/html/sgFSOSample.asp?frame=true

Ok, first things first. Run off to the MSDN website and download the "Microsoft Windows Script 5.6 Documentation" and "Microsoft Windows Script Debugger" from this page,

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/webdev.asp

1) Yes but it's not too optimal. A better solution is to have a end-user application that validates the values and then passes them to the package as global variables,...

If you are using DTS, can't you make a connection to DB2 without the linked server?

You probably already have the DB2 client tools installed right ?

Isn't there an OLE/DB or...

Please don't cross post.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=161925

Don't worry about the DTS stuff, once you get the data into seperate files its point-click stuff.

Have you done anything with VBScript before?

That's right. That way you eliminate the frustrations you're experiencing now with poor performance, data quality, etc...

For the import part, you can have a single DTS package with multiple datapumps...

You won't need the dynamic SQL.

From your previous post on this problem, you have a data file that looks like this,

A222|ZS08          |0005      |H350        |GENERAL_REPAIR      |A              |28.02.2004|01.02.2004|0000352836     |000
A223|ZS16          |0005      |02           ...

"I need to run the stored procedure in Sql Server and It's going to be called by Oracle, Oracle has the data."

This sort of information would have been helpful in your original...

Your going down the right track. Instead of using a temp table in tempdb, use a permanent table in your database, or if need be, a seperate staging database. Just truncate...