I'm running into difficulty usinging parameters in my data driven query task.
I have an MS Access data source that I'm trying to Pump (update/insert) data
into a SQL Server Table. The data should be inserted if a destination record
does not exist and updated if a record already exists for the data set. The
complication is that the tables are poorly designed with approximately 90
columns. That means selecting/configuring 90+ parameters in the Data Driven
Query Task. I've attempted to write an Update Query that first queries the
destination table to see if a record exists. If it does then the update
query uses "update" to update data. If the record doesn't exist for the
source data then an insert is used.

The problem that I have is that I receive an "Access Violation" error
related to the parameterization of the query if I have a statement similiar
to the following:

IF Exists(Select productcode from destination where productcode = sourcecode)

I accidently posted my question without finishing my thoughts....Here is the
full post:


I'm running into difficulty usinging parameters in my data driven query task.
I have an MS Access data source that I'm trying to Pump (update/insert) data
into a SQL Server Table. The data should be inserted if a destination record
does not exist and updated if a record already exists for the data set. The
complication is that the tables are poorly designed with approximately 90
columns. That means selecting/configuring 90+ parameters in the Data Driven
Query Task. I've attempted to write an Update Query that first queries the
destination table to see if a record exists. If it does then the update
query uses "update" to update data. If the record doesn't exist for the
source data then an insert is used.

The problem that I have is that I receive an "Access Violation" error
related to the parameterization of the query if I have a statement similiar
to the following:

IF Exists(Select productcode from destination where productcode = ?)
Update destination
SET column1 = ?
column2 = ?
:
:
column<waytomany> = ?
WHERE productcode = ?
ELSE
Insert INTO destination
Values (?,?,?.......<waytomany>)

I know my SQL syntax may be a little off, but it's the Exist part that I'm
having a problem with. The error refers to an access violation. If I only
use the Insert or Update part of the query with parameterization then the
task works. It's only when I try to use the exist part or attempt to set a
local variable equal to a parameter that I get the error. The error occurs
when I select the "Parse/Show Parameters" button on the "Queries" tab of the
Data Driven Query Task Properties page. The error is:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Syntax error or access violation.

The confusing part for me is that if I hardcode the value instead of using
"?" to parameterize the value then everything works.

Is there a better way to use a data pump to be intelligent enough to
determine if it needs to update or insert on the destination? Is my syntax
incorrect when using the parameterizations?

Thanks,
jeremy

Personally I would t use the DDQ. It is slow and clumsy.

I would make two passes.

Pass 1. Check using Key Values if the row exists. If not then INSERT
Pass 2. Check using Key Values if the row exists. If it does then UPDATE

The update you will either need to further qulify by making it finer i.e. if
col1, col3 and col4 are different then UPDATE
OR you will just issue a blanket update.

You can use a Linked server and ExecuteSQL tasks to do this

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


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