Dear All,

While Updating/Deleteing input parameter will take only corresponding
Table.filed.
My prob is I would like to pass other tables.filed as input parameter.

Detals in Example
CREATE TABLE [StageTable] (
[DateExtracted] [datetime] NOT NULL CONSTRAINT
[DF_StageTable_DateExtracted] DEFAULT (getdate()),
[LoginName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[VendorID] [int] NULL ,
[StgSlno] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_StageTable] PRIMARY KEY CLUSTERED
(
[DateExtracted],
[LoginName]
) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [OdsTable] (
[LoginName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NOT NULL ,
[StgSlno] [int] NOT NULL ,
CONSTRAINT [PK_OdsTable] PRIMARY KEY CLUSTERED
(
[LoginName]
) ON [PRIMARY]
) ON [PRIMARY]
GO

StageTable

DateExtracted LoginName Name Date VendorID StgSlno
6/15/2005 21:56 Ashok Ashok 6/14/05 12:00 AM 1 1
6/15/2005 21:56 Rajesh Rajesh 6/14/05 12:00 AM 1 2
6/15/2005 21:57 Ashok Ashok M 6/14/05 12:00 AM 1 3
6/15/2005 21:57 Mohit Mohit 6/14/05 12:00 AM 1 4
6/15/2005 21:57 Rajesh Rajesh 6/14/05 12:00 AM 1 5

Here DateExtracted and LoginName as PK

OdsTable

LoginName Name Date StgSlno
Ashok Ashok 6/14/05 12:00 AM 1
Rajesh Rajesh 6/14/05 12:00 AM 2
LoginName as PK

Update ODsTable set OdsTable.Name=StageTable.Name,
OdsTable.Date=StageTable.Date, OdsTable.StgSlno = StageTable.StgSlno Where
OdsTable.LoginName=StageTable.LoginName and StageTable.DateExtracte=(select
Max(DateExtracted) from StageTable where VendorID = ?) and VendorID = ? and
StageTable.Stgslno<=?

In above Query I not able to pass Vendor ID As parameter, I getting Access
Voilation Error

How I can get this solution with out Stored Proc.

I am wating for your valuable responce.
Tahanks and Regards.
Rajesha

Whilst you should not get an AV, just a syntax error, the parameter
syntax validation is not very clever. FO rthis I would just create an
SP, or do it the old way, generate the SQL in an ActiveX Script Task and
set it that way.

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

Darren

In message <DF5C17F1-D015-4A62-B780-324EE23A4727 (AT) microsoft (DOT) com>, Rajesha
<Rajesha (AT) discussions (DOT) microsoft.com> writes
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

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

HI Daren,
Thanks for your suggestion; ActiveX script will do slower the performance. I
not interested to use this and also SP.

I request you please provide me delete or update records in [ODSTable]
Table, using VendorID As Input parameter.

Thanks and Regards,
Rajesh


"Darren Green" wrote:

I don't think using a script to setup the task will slow this down in any
meaningfull way. It'd not like a script is executing more than once per
package execution.

Darren

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

Hi Darren,

Thanks for your suggessiton, I will do the same.

Regards,
Rajesha


"Darren Green" wrote: