Re: Global Variables and SQL Task

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Richard Highton (RichardHighton_at_discussions.microsoft.com)
Date: 07/19/04 

Date: Mon, 19 Jul 2004 08:30:04 -0700

Allan,

Thanks for looking at this for me, but I have just managed to get round the problem by converting the statement to a select, and then mapping the parameter. Having done that re-converted it to an update and it runs ok. If I need to change the statment at all in the future, I would need to repeat the procedure each time if I need to parse the query again (as that fails as well). For that reason it would still be nice to know if there is a better way to deal with this issue.

"Richard Highton" wrote:

> No real reason why the MIG database is in quotes. I agree it works without them as well. I am migrating data into an oracle db. I had a problem setting the connection to the oracle db which I can't remember now. t8 should read t1.
>
> "Allan Mitchell" wrote:
>
> > Why is everything enclosed in "
> > You are using 4 part naming so I presume you are going to a different
> > server? Why? In the executeSQL task you simply create a connection for the
> > MIG server
> > What is T8? (set t2.act_key = t8.act_key)
> >
> >
> >
> >
> >
> >
> > --
> > --
> >
> > 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
> >
> >
> > "Richard Highton" <Richard Highton@discussions.microsoft.com> wrote in
> > message news:D57490F5-A7F9-49F0-A0A5-66B0BD452A44@microsoft.com...
> > > I have successfully set up several Transform data tasks which use a
> > variable parameter in the where clause. However, when I try and use the same
> > technique in an SQL Task, which is an update, I get an error - invalid
> > object name 't2'. It works if I convert it to a select statment, but always
> > fails as an update. I should add that the table I am updating is an oracle
> > database. Has anyone got round this before?
> > >
> > > The code I am using is below:
> > >
> > > update t2
> > > set t2.act_key = t8.act_key
> > > from "MIG".."MIG"."SMXA" as t1
> > > join "MIG".."MIG"."ORDERH" as t2 on t1.cc = t2.cc and t1.act_code =
> > t2.act_code
> > > where t2.sector = ?
> >
> >
> >