February 20, 2019 at 3:59 am
Hi,
I'm trying to recreate a stored procedure within SSIS .
Basically what I'm trying to do is to pass through variables from a table A ,check that these records exist within table B ,if they do then go ahead and update table C .This is all done through variables like this Select * from Table1 where Company_Id = ?
And in table C it is updated with variables too .i.e Update tableC
set company_name = ?
The problem I'm having is that I can't set foreign keys of a table to null when I'm doing an update to the table .This should be possible within SSIS ,
the error messages I'm getting back look like this :
"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Table1Tbl_Table1_ID_Table2_Table2ID".
The conflict occurred in database "TestDatabase", table "dbo.Table2_Tbl", column 'Table2_ID'
It works if drop the constraints but I'm sure there is a different way of doing this ..
Please let me know of other methods I could do this .
Thank you
February 20, 2019 at 11:49 am
arthovs94 - Wednesday, February 20, 2019 3:59 AMHi,
I'm trying to recreate a stored procedure within SSIS .
Basically what I'm trying to do is to pass through variables from a table A ,check that these records exist within table B ,if they do then go ahead and update table C .This is all done through variables like this Select * from Table1 where Company_Id = ?
And in table C it is updated with variables too .i.e Update tableC
set company_name = ?The problem I'm having is that I can't set foreign keys of a table to null when I'm doing an update to the table .This should be possible within SSIS ,
the error messages I'm getting back look like this :"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Table1Tbl_Table1_ID_Table2_Table2ID".
The conflict occurred in database "TestDatabase", table "dbo.Table2_Tbl", column 'Table2_ID'It works if drop the constraints but I'm sure there is a different way of doing this ..
Please let me know of other methods I could do this .
Thank you
Which part of your update requires an ALTER TABLE, and why?
February 20, 2019 at 12:00 pm
When you create a FK constraint, you can specify whether the key can be NULL or not. If the creating code says it can't be set to NULL -- either because you menat to set it that way or because it defaulted to that way -- then naturally SQL won't allow it to be set to NULL.
When you create the FK, be sure to specify "ON UPDATE SET NULL" rather than letting it default to "NO ACTION" (iirc it defaults to "NO ACTION" = raise an error, don't allow the UPDATE).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply