Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQLServer
ALTER PROCEDURE procSSWPrimaryKeyUpdateAllRelationships
/*
'----------------------------------------------
' Copyright 2002 Superior Software for Windows
'----------------------------------------------
' www.ssw.com.au All Rights Reserved.
' VERSION AUTHOR DATE COMMENT
' 1.0 RD,ST 30/10/2002
' SAMPLE USAGE:
' rename values in a column
' DECLARE @strMessage varchar(200)
' EXECUTE procSSWPrimaryKeyUpdateAllRelationships 'Client','ClientID', 'ZZTEST', 'ZZNEW1', @strReturnMessage = @strMessage OUTPUT
'----------------------------------------------
(One table) Children (many table) UpdateCascadeON
Client.ClientID
|___________________ClientContact.ClientID Yes
|
|___________________ClientDiary.ClientID No
|
|___________________ClientInvoice.ClientID Yes
Execution plan:
- select children where CascadeUpdate is turned off (due to SQL Server limitations)
- open a cursor
- build SQL_NOCHECK statement to turn OFF Enforce Referential Integrity
- build SQL_CHECK statement to turn ON Enforce Referential Integrity
- build SQL_UPDATE statement to update children where UpdateCascadeON is NO (ClientDiary.ClientID)
- close a cursor
- append SQL_UPDATE with SQL updating main table (Client.ClientID)
- execute SQL_NOCHECK
- execute SQL_UPDATE
- execute SQL_CHECK
*/
@pstrTableName varchar (200),
@pstrColumnName varchar (200),
@pstrOldValue varchar (200),
@pstrNewValue varchar (200),
@pstrEmpUpdated varchar (150),
@strReturnMessage varchar (200) OUTPUT
AS
DECLARE @strForeignTableName varchar (200)
DECLARE @strForeignColumnName varchar (200)
DECLARE @strConstraintName varchar (200)
DECLARE @strPrimaryTableName varchar (200)
DECLARE @strPrimaryColumnName varchar (200)
DECLARE @strColumnType varchar (10)
-- select children where CascadeUpdate is turned off (due to SQL Server limitations)
DECLARE curTableName_ConstraintName CURSOR FOR
SELECT sysobjects_foreign.name AS foreign_table,
syscolumns_foreign.name AS foreign_column,
sysobjects_primary.name AS primary_table,
syscolumns_primary.name AS primary_column,
sysobjects.name AS constraint_name,
systypes.name AS ColumnType
FROM sysforeignkeys
INNER JOIN sysobjects
ON sysforeignkeys.constid = sysobjects.id
INNER JOIN sysobjects sysobjects_primary
ON sysforeignkeys.rkeyid = sysobjects_primary.id
INNER JOIN sysobjects sysobjects_foreign
ON sysforeignkeys.fkeyid = sysobjects_foreign.id
AND sysobjects.parent_obj = sysobjects_foreign.id
INNER JOIN syscolumns syscolumns_foreign
ON sysforeignkeys.fkey = syscolumns_foreign.colid
AND sysobjects_foreign.id = syscolumns_foreign.id
INNER JOIN syscolumns syscolumns_primary
ON sysforeignkeys.rkey = syscolumns_primary.colid
AND sysobjects_primary.id = syscolumns_primary.id
INNER JOIN systypes
ON syscolumns_foreign.xusertype = systypes.xusertype
WHERE OBJECTPROPERTY(sysobjects.id, 'CnstIsUpdateCascade') = 0
AND sysobjects_primary.name = @pstrTableName
AND syscolumns_primary.name = @pstrColumnName
ORDER BY sysobjects_foreign.name, syscolumns_foreign.name
DECLARE @SQLStringNoCheckConstraint NVARCHAR(4000)
SET @SQLStringNoCheckConstraint =''
DECLARE @SQLStringCheckConstraint NVARCHAR(4000)
SET @SQLStringCheckConstraint = ''
DECLARE @SQLStringUPDATE NVARCHAR(4000)
SET @SQLStringUPDATE =''
DECLARE @strColumnTypetemp varchar (10)
SET @strColumnTypeTemp = ''
DECLARE @SQLStringSSWEmpUpdated NVARCHAR (500)
SET @SQLStringSSWEmpUpdated = ',' + + CHAR(13) + CHAR(10) +
'EmpUpdated=' + '''' + @pstrEmpUpdated + ''''
DECLARE @SQLStringSSWDateUpdated NVARCHAR (500)
SET @SQLStringSSWDateUpdated = ',' + + CHAR(13) + CHAR(10) +
'DateUpdated=' + '''' + CONVERT(NVARCHAR,GETDATE()) + ''''
-- open a cursor
OPEN curTableName_ConstraintName
FETCH NEXT FROM curTableName_ConstraintName
INTO @strForeignTableName, @strForeignColumnName, @strPrimaryTableName, @strPrimaryColumnName, @strConstraintName, @strColumnType
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strColumnTypeTemp = @strColumnType
-- build SQL_NOCHECK statement to turn OFF Enforce Referential Integrity
SET @SQLStringNoCheckConstraint = @SQLStringNoCheckConstraint +
N'ALTER TABLE [' + @strForeignTableName + '] ' +
'NOCHECK CONSTRAINT [' + @strConstraintName + ']' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
-- build SQL_CHECK statement to turn ON Enforce Referential Integrity
SET @SQLStringCheckConstraint = @SQLStringCheckConstraint +
N'ALTER TABLE [' + @strForeignTableName + '] ' +
'CHECK CONSTRAINT [' + @strConstraintName + ']' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
-- build SQL_UPDATE statement to update children where UpdateCascadeON is NO (ClientDiary.ClientID)
IF @strColumnType IN ('nchar', 'char', 'nvarchar', 'varchar')
BEGIN
SET @SQLStringUPDATE = @SQLStringUPDATE +
N'UPDATE [' + @strForeignTableName + ']' + CHAR(13) + CHAR(10) +
'SET [' + @strForeignColumnName + ']=' + '''' + @pstrNewValue + ''''
IF EXISTS (SELECT syscolumns.name FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id
WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'EmpUpdated'))
SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWEmpUpdated
IF EXISTS (SELECT syscolumns.name FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id
WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'DateUpdated'))
SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWDateUpdated
SET @SQLStringUPDATE = @SQLStringUPDATE + CHAR(13) + CHAR(10) + 'WHERE [' + @strForeignColumnName + ']=' + ''''+ @pstrOldValue + '''' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
END
ELSE
BEGIN
SET @SQLStringUPDATE = @SQLStringUPDATE +
N'UPDATE [' + @strForeignTableName + ']' + CHAR(13) + CHAR(10) +
'SET [' + @strForeignColumnName + ']=' + @pstrNewValue
IF EXISTS (SELECT syscolumns.name FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id
WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'EmpUpdated'))
SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWEmpUpdated
IF EXISTS (SELECT syscolumns.name FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id
WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'DateUpdated'))
SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWDateUpdated
SET @SQLStringUPDATE = @SQLStringUPDATE + CHAR(13) + CHAR(10) + 'WHERE [' + @strForeignColumnName + ']=' + @pstrOldValue + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
END
FETCH NEXT FROM curTableName_ConstraintName
INTO @strForeignTableName, @strForeignColumnName, @strPrimaryTableName, @strPrimaryColumnName, @strConstraintName, @strColumnType
END
-- close a cursor
CLOSE curTableName_ConstraintName
DEALLOCATE curTableName_ConstraintName
-- append SQL_UPDATE with SQL updating main table (Client.ClientID)
IF @strColumnType IN ('nchar', 'char', 'nvarchar', 'varchar')
BEGIN
SET @SQLStringUPDATE = @SQLStringUPDATE + N'UPDATE [' + @pstrTableName + ']' + CHAR(13) + CHAR(10) +
'SET [' + @pstrColumnName + ']=' + ''''+ @pstrNewValue + ''''
IF EXISTS (SELECT syscolumns.name FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id
WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'EmpUpdated'))
SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWEmpUpdated
IF EXISTS (SELECT syscolumns.name FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id
WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'DateUpdated'))
SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWDateUpdated
SET @SQLStringUPDATE = @SQLStringUPDATE + CHAR(13) + CHAR(10) + 'WHERE [' + @pstrColumnName + ']=' + '''' + @pstrOldValue + ''''
END
ELSE
BEGIN
SET @SQLStringUPDATE = @SQLStringUPDATE + N'UPDATE [' + @pstrTableName + ']' + CHAR(13) + CHAR(10) +
'SET [' + @pstrColumnName + ']=' + @pstrNewValue
IF EXISTS (SELECT syscolumns.name FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id
WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'EmpUpdated'))
SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWEmpUpdated
IF EXISTS (SELECT syscolumns.name FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id
WHERE (sysobjects.name = @strForeignTableName) AND (syscolumns.name = 'DateUpdated'))
SET @SQLStringUPDATE = @SQLStringUPDATE + @SQLStringSSWDateUpdated
SET @SQLStringUPDATE = @SQLStringUPDATE + CHAR(13) + CHAR(10) + 'WHERE [' + @pstrColumnName + ']=' + @pstrOldValue
END
-- execute SQL_NOCHECK
EXECUTE sp_executesql @SQLStringNoCheckConstraint
DECLARE @intCount int
SET @intCount = 0
DECLARE @intError int
SET @intError = 0
BEGIN TRANSACTION
-- execute SQL_UPDATE
EXECUTE sp_executesql @SQLStringUPDATE
SET @intError = @@Error
SET @intCount = @@ROWCOUNT
IF @intError <> 0
BEGIN
ROLLBACK TRANSACTION
IF @intError = 2627
SET @strReturnMessage= 'Error: 2627' + CHAR(10) + CHAR(13) + 'Violation of PRIMARY KEY constraint.' + CHAR(10) + CHAR(13) + 'Cannot insert duplicate key.'
ELSE
SET @strReturnMessage='Errors were encountered during renaming from ' + @pstrOldValue + ' to ' + @pstrNewValue + CHAR(10) + CHAR(13) +
' Error: ' + CONVERT(VARCHAR, @intError)
END
ELSE
BEGIN
COMMIT TRANSACTION
SET @strReturnMessage = 'Successfuly updated ' + CONVERT(varchar(20), @intCount) + ' record(s). Renamed from ' + @pstrOldValue + ' to ' + @pstrNewValue
END
ExitProc:
BEGIN
-- execute SQL_CHECK
EXECUTE sp_executesql @SQLStringCheckConstraint
RETURN
END
This function will open an udl dialog, to create new udl file or edit existing
Private Sub picUDL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles picUDL.Click
Try
Dim objUDL As MSDASC.DataLinks = New MSDASC.DataLinks()
Dim objcnn As ADODB.Connection = New ADODB.Connection()
objcnn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=ROO")
'Dim pp As Object = AddressOf (objcnn.)
'objUDL.PromptEdit(objcnn)
Dim str As String
Dim obj As Object
Dim a As SByte
' str = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=ROO"
obj = objUDL.PromptNew()
' objUDL.GetInitializationString(objcnn, a, str)
obj.open()
MsgBox(obj.connectionstring())
' obj.GetInitializationString(obj, a, str)
'this is working
objUDL.LoadStringFromStorage(Application.StartupPath & "\udl.udl", str)
'this is working
objUDL.PromptEdit(CType(objcnn, Object))
' Dim obj As Object = objUDL.PromptNew()
' objUDL.PromptEdit(gobjMergeGroup.SourceCnnString)
Catch ex As Exception
MessageBox.Show(ex.Message.ToString, "frmMain_picUDL_Click", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
Finally
'Do nothing
End Try
End Sub
sp_helpdb Northwind
USE Northwind
--Shrink first one
DBCC SHRINKFILE
( 'Northwind', 1)
--Shrink second one
DBCC SHRINKFILE
( 'Northwind_log', 1)
--etc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.