I need to exec a stored proc with one int input param, one int output param,
and a Return int from within an ActiveX VBScript. I have been working on this
for two days and am stumped using an ADO connection. Can't find much on MSDN
for VBScript.

Thanks,

Michael

Can you not use an ExecuteSQL task?

Global Variables and Stored Procedure Parameters
(http://www.sqldts.com/default.aspx?234)




"Snake" <Snake (AT) discussions (DOT) microsoft.com> wrote

No, I need to integrate global variables in and out of several DTS steps.

"Allan Mitchell" wrote:

In message <D3508300-6CAD-4544-8722-124A74459DB3 (AT) microsoft (DOT) com>, Snake
<Snake (AT) discussions (DOT) microsoft.com> writes
That doesn't mean you cannot use the Exec SQL Task, it supports using
global variables for input and output.

If you insist on using ADO, then look at the ADO Command object, and the
parameters collection.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Hi Snake,

"Snake" <Snake (AT) discussions (DOT) microsoft.com> schrieb im Newsbeitrag
news3508300-6CAD-4544-8722-124A74459DB3 (AT) microsoft (DOT) com...
I'm afraid Allans idea is not the worst.
AFAIK ADO doesn't support output parameters of stored procs nativly.
So the only way to get them is to execute a batch which collects them and
return them as one SELECT statement to build a recordset.
But thats exactly what you can do within a ExecuteSQLTask, where you can
pass this result to a global rowset variable.

And if you have problems passing the parameters to the sql statements, you
may think about dynamicly rewriting the sql statements with an AciveX task.
E.G. I created a package where the sql statements contained placeholders
for the variables and an ActiveX task cycled through all sql tasks of the
package and replaced them with the variable values.

Helge

Helge,
Thanks a lot! Your explanation is textbook quality. Those of us learning
this stuff appreciate the extra effort.

I saw an MSDN ADO coding example in VB and thought that it could be
modified into VBscript but was apparently wrong.

Best regards,
Michael

"Helge C. Rutz" wrote:

I have just spent the last several hours trying to use ExecuteSQL task, and
have not been successful. As you know, the DTS designer is too stupid to
handle output variables correctly, so the DTSSQL article suggests a method to
fool DTS into creating the output variables using "metadata" spoofing. I am
sure it works for someone, but it does not seem to work for me. I am going to
have to go back to my boss and tell him he is out of luck.

So much for DTS!



"Darren Green" wrote:

Hi Snake,

"Snake" <Snake (AT) discussions (DOT) microsoft.com> schrieb im Newsbeitrag
news:F04915AC-4A59-4ED3-BA2F-8B45B8F5D62A (AT) microsoft (DOT) com...
I'm sorry to hear that, but I cannot completly understand it.
If you mean the problem to define input and output parameters for complex
statements/batches, yes thats ugly, but nothing you can't workaround.
Maybee you should post some code here to demonstrate and discuss.
I'm nearly sure there is a solution for your problem.
To don't let you out in the rain I would like to offer you to look over a
sample package to see if there is a way out.

Helge

Helge,

Here is the Spoof:
select PropertyId as PropertyImportId
from propertyparcel where propertyid = ? and propertyparcelId = ?

Here is the target code:
DECLARE @PropertyImportId int
EXEC @Retstat = dbo.DeedsImport ?, ?, @PropertyImportId output
SELECT @PropertyImportId as PropertyImportId

Here is the proc defn:
CREATE PROCEDURE DeedsImport @@VendorId int = 1,
@@FileName Varchar(50),
@@PropertyImportId int OUTPUT
AS
SET NOCOUNT ON
..
..


Here is the table from the spoof:
CREATE TABLE [PropertyParcel] (
[PropertyParcelID] [int] IDENTITY (1, 1) NOT NULL ,
[CreationDate] [smalldatetime] NOT NULL CONSTRAINT
[DF_PropertyParcel_CreationDate] DEFAULT (getdate()),
[LastUpdated] [smalldatetime] NOT NULL CONSTRAINT
[DF_PropertyParcel_LastUpdated] DEFAULT (getdate()),
[PropertyID] [int] NOT NULL

I appreciate the help. I am at my wits-end!

Michael

"Helge C. Rutz" wrote:

Did you saw my reply to the new threat?

Helge