March 11, 2011 at 9:43 am
Hi All,
I am trying to do an update, using an inner join. It is a self-referencing table, and I need to set a value to null. I have tried a couple of different ways, and they work in SSMS, but, depending on the update statement I use, I get errors when I try to use it in my package.
This is the table definition (and yes, I know this is a bad name. I had no control over it. 🙂 ):
CREATE TABLE [dbo].[function](
[functionid] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](100) NULL,
[returntype] [nvarchar](100) NULL,
[moduleid] [int] NULL,
[successor_functionid] [int] NULL,
[checkedout_username] [varchar](92) NULL,
[checkedin_username] [varchar](92) NULL,
[buildid] [int] NULL,
[branchfrom_buildid] [int] NULL,
[revisionnotes] [varchar](max) NULL,
[functionmasterid] [int] NOT NULL,
[description] [nvarchar](max) NULL,
[archived] [bit] NOT NULL,
[iscurrent] AS (CONVERT([bit],case when [successor_functionid] IS NULL AND [checkedout_username] IS NULL AND [archived]=(0) then (1) else (0) end,(0))),
[codeid] [int] NULL,
CONSTRAINT [PK__function__4316F928] PRIMARY KEY CLUSTERED
([functionid] ASC) )
GO
ALTER TABLE [dbo].[function] WITH CHECK ADD FOREIGN KEY([successor_functionid])
REFERENCES [dbo].[function] ([functionid])
GO
Here is the update:
UPDATE [dbo].[function]
SET [successor_functionid] = NULL
FROM [dbo].[function]
INNER JOIN [dbo].[function] functionParent
ON functionParent.functionid = [function].successor_functionid
WHERE functionParent.functionid = ?
That one gives me the error:
The multi-part identifier "functionParent.functionid" could not be bound.
This update:
UPDATE functionChild
SET [successor_functionid] = NULL
FROM [dbo].[function] functionChild
INNER JOIN [dbo].[function] functionParent
ON functionParent.functionid = functionChild.successor_functionid
WHERE functionParent.functionid = ?
gives me the error:
"Invalid object name 'functionChild'."
I've also tried with a CTE, and gives me a syntax error.
Any help would be much appreciated.
Thanks,
Leonard
March 11, 2011 at 2:36 pm
>> The multi-part identifier "functionParent.functionid" could not be bound.
How is the 'DelayValidation' property on the component that is causing this error set? I've seen situations where late binding of parameters can cause a package to produce an error if DelayValidation is set to false.
March 11, 2011 at 3:21 pm
it doesn't have delay validation. I know it's on 2008, but this is 2005. there is a ValidateExternalMetadata, that I turned to false, but that didn't help.
Leonard
March 13, 2011 at 6:25 am
Hi lrutkowski
The way you are writing the script will work fine with SSIS 2008, though in your case i would rather suggest to use Script Task. You can update your function table using script task by consuming variable as read only input for passing argument into your SQL.
Thanks
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
March 13, 2011 at 7:02 am
Anjan Wahwar (3/13/2011)
Hi lrutkowskiThe way you are writing the script will work fine with SSIS 2008, though in your case i would rather suggest to use Script Task. You can update your function table using script task by consuming variable as read only input for passing argument into your SQL.
Thanks
As this is pure T-SQL, I disagree with your suggestion; it adds an unnecessary layer of complexity.
I just tried this on my system and it worked fine. Are you using an EXECUTE SQL task to execute the code? What sort of database connection are you using?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 14, 2011 at 6:53 am
I am using an OLE DB Command, in a data flow task. There are some technical and business reasons for not using 2008. In fact, this is the only SSIS package that I have in 2005. All the rest are in 2008. Using a SQL task outside the dataflow causes it's own set of coding issues, and using a Script task is an idea, but I was trying to keep this simple. The connection is SQLNCLI.1 so that may be part of the problem (most of my other connections use the .10 connector).
I know there are ways around this, but I was trying to simplify. part of the data flow is a look up transformation, so I still need to do that to determine if I need to do the delete.
Leonard
March 14, 2011 at 8:27 am
I tried the following and worked, no problems on 2008, and cant see why it wount work on 2005 as well.
UPDATE T1
SET [successor_functionid] = NULL
FROM [dbo].[function] T1
INNER JOIN [dbo].[function] T2
ON T2.functionid = T1.successor_functionid
WHERE T2.functionid = 99999
March 14, 2011 at 8:30 am
it works fine on 2008. I don't know why it wouldn't work on 2005. No matter, I was overthinking it, and I don't actually need to do the join, I just need to update the succssor_functionid where it equals my input functionid.
Anyway, thanks for the help.
Leonard
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply