SQLServer

A-RenamePrimaryKey-RD

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

B-Open UDL Dialog-DH

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

B-ShrinkAllFiles-AC

sp_helpdb Northwind

USE Northwind

--Shrink first one
DBCC SHRINKFILE 
    ( 'Northwind', 1)
 
--Shrink second one
DBCC SHRINKFILE 
    ( 'Northwind_log', 1)

--etc