All tables are owned by dbo. I did find a way to workaround this by adding
an ActiveScript task right before this task. The ActiveScript task
dynamically bulids the SQL statements and then populates this ExecuteSQL
task. Works great.
The entire package has 80+ steps at the moment - about 25 of those are
ExecuteSQL statements of various types. Of those 25 taks, I had 4 where the
SQL queries were complex enough where I had to use the workaround I
mentioned above. They all involved various subqueries of some sort.
For example, I had a task with just this one delete statement:
DELETE FROM Bonus WHERE LeaseID IN
(SELECT ID FROM Lease WHERE AccountID IN
(SELECT ID FROM Account WHERE BranchID = ?))
No matter how I wrote this query in the ExecuteSQL task, I couldn't get it
to accept the parameter, so I used the ActiveScript task to build the
statement at runtime based on the global variable.
- Mark
[quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:%232hZT$RvEHA.2536@TK2MSFTNGP11.phx.gbl...
> If you move each DELETE out to its own task?
>
> Who owns Payee?
>
>
>
> --
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>
www.SQLDTS.com - The site for all your DTS needs.
>
www.konesans.com - Consultancy from the people who know
>
>
> "Mark Peterson" <petersonmd@hotmail.com> wrote in message
> news:ec7HU6DvEHA.3416@TK2MSFTNGP09.phx.gbl...
>> I'm attempting to execute several DELETE statements via an ExecuteSQL
>> task. I need to access a global variable that's set earlier in the
>> package. I've gotten this to work for simpler queries, but for this
>> particular set of SQL statements, I'm getting the following errors:
>>
>> Error Source: Microsoft OLEDB Provider for SQL Server
>> Error Description: The column prefix 'Payee' does not match with a table
>> name or alias name used in the query. (repeated 4 times)
>>
>> Here's the query...
>>
>> DELETE AssignedPayee
>> FROM AssignedPayee INNER JOIN Payee ON AssignedPayee.PayeeID = Payee.ID
>> WHERE Payee.BranchID = ?
>>
>> DELETE PayeeAddress
>> FROM PayeeAddress INNER JOIN Payee ON PayeeAddress.PayeeID = Payee.ID
>> WHERE Payee.BranchID = ?
>>
>> DELETE PayeeDeduction
>> FROM PayeeDeduction INNER JOIN Payee ON PayeeDeduction.PayeeID = Payee.ID
>> WHERE BranchID = ?
>>
>> DELETE FROM Payee
>> WHERE BranchID = ?
>>
>> At first, I had written these queries using subqueries, such as...
>>
>> DELETE FROM AssignedPayee
>> WHERE PayeeID IN (SELECT ID FROM Payee WHERE BranchID = ?)
>>
>> However, it seems DTS doesn't like parameters in subqueries, so I rewrote
>> the queries as shown above.
>>
>> Any ideas on how to get this to work?
>>
>> Thanks!
>> Mark
>>
>>
>>
>
>
