Hallo,
aktuell bin ich dabei einen Manuellen Prozess des Datenbanktausches zu automatisieren. Hierzu werden CAD-Daten kopiert und es müssen SQL-DB ausgetauscht werden.
Es funktioniert alles Einwandfrei, bis auf das ich in der Destination DB die Trigger nicht deaktivieren kann.
Hier bekomme ich die Meldung, das die Trigger nicht gefunden werden bzw. ich die Rechte nicht habe, diese zu deaktivieren. Manuell kann ich sie im SQL Manager deaktivieren.
Als Codevorlage habe ich diese Seite gehabt(mein Schlaues SQL Buch behandelt das Thema Trigger gar nicht):
social.msdn.microsoft.com/Foru…ert-a-row?forum=vbgeneral
Anbei mein Code die Trigger einschalten ausschalten sind in Zeile 40 - Zeile 60:
Danke im Voraus :-)!
aktuell bin ich dabei einen Manuellen Prozess des Datenbanktausches zu automatisieren. Hierzu werden CAD-Daten kopiert und es müssen SQL-DB ausgetauscht werden.
Es funktioniert alles Einwandfrei, bis auf das ich in der Destination DB die Trigger nicht deaktivieren kann.
Hier bekomme ich die Meldung, das die Trigger nicht gefunden werden bzw. ich die Rechte nicht habe, diese zu deaktivieren. Manuell kann ich sie im SQL Manager deaktivieren.
Als Codevorlage habe ich diese Seite gehabt(mein Schlaues SQL Buch behandelt das Thema Trigger gar nicht):
social.msdn.microsoft.com/Foru…ert-a-row?forum=vbgeneral
Anbei mein Code die Trigger einschalten ausschalten sind in Zeile 40 - Zeile 60:
VB.NET-Quellcode
-
- Public Sub CreateBackupSourcePDM(ByVal Source_SelectedRow As DataRow, ByVal Destination_SelectedRow As DataRow, ByVal Workingtable As DataTable)
- Try
- CreateDB_Backup(CStr(Source_SelectedRow("PDM_System")), "SourceDB.bak")
- CreateDB_Backup(CStr(Destination_SelectedRow("PDM_System")), "DestinationDB" & System.DateTime.Now.ToString("yyyyMMdd_HHmm") & ".bak")
- Dim Destination_SQL_Connection As SqlClient.SqlConnection = New SqlClient.SqlConnection
- Destination_SQL_Connection.ConnectionString = "Data Source =" & ServerName & "; User ID=" & UserName & " ;Password=" & PW
- Destination_SQL_Connection.Open()
- Dim CopyCommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("ALTER DATABASE " & CStr(Destination_SelectedRow("PDM_System")) & " SET OFFLINE WITH ROLLBACK IMMEDIATE", Destination_SQL_Connection)
- CopyCommand.ExecuteNonQuery()
- CopyCommand = New SqlClient.SqlCommand("DROP DATABASE " & CStr(Destination_SelectedRow("PDM_System")), Destination_SQL_Connection)
- CopyCommand.ExecuteNonQuery()
- 'RESTORE DATABASE [GOK_TEST_PDM] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\SourceDB.bak' WITH FILE = 1, MOVE N'GOK_SW_PDM' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\GOK_TEST_PDM.mdf', MOVE N'GOK_SW_PDM_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\GOK_TEST_PDM_log.ldf', NOUNLOAD, REPLACE, STATS = 5
- 'RESTORE DATABASE [GOK_TEST_PDM] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\SourceDB.bak' WITH FILE = 2
- Dim RestoreQuery As String = "RESTORE DATABASE " & CStr(Destination_SelectedRow("PDM_System")) & " FROM DISK = N'SourceDB.bak' WITH FILE = 1"
- ', MOVE N'GOK_SW_PDM' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\GOK_TEST_PDM.mdf'
- RestoreQuery = RestoreQuery & ", MOVE N'" & CStr(Source_SelectedRow("PDM_System")) & "' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\" & CStr(Destination_SelectedRow("PDM_System")) & ".mdf'"
- ', MOVE N'GOK_SW_PDM_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\GOK_TEST_PDM_log.ldf'
- RestoreQuery = RestoreQuery & ", MOVE N'" & CStr(Source_SelectedRow("PDM_System")) & "_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\" & CStr(Destination_SelectedRow("PDM_System")) & "_log.ldf'"
- ', NOUNLOAD, REPLACE, STATS = 5
- RestoreQuery = RestoreQuery & ", NOUNLOAD, REPLACE, STATS = 5"
- Debug.Print(RestoreQuery)
- CopyCommand = New SqlClient.SqlCommand(RestoreQuery, Destination_SQL_Connection)
- CopyCommand.ExecuteNonQuery()
- Destination_SQL_Connection.Close()
- Dim Destination_SQL_Connection_NewDB As SqlClient.SqlConnection = New SqlClient.SqlConnection
- Destination_SQL_Connection_NewDB.ConnectionString = "Data Source =" & ServerName & ";Initial Catalog=" & CStr(Destination_SelectedRow("PDM_System")) & "; User ID=" & UserName & " ;Password=" & PW
- Destination_SQL_Connection_NewDB.Open()
- CopyCommand = New SqlClient.SqlCommand("DISABLE TRIGGER ArchiveServers ON DATABASE", Destination_SQL_Connection_NewDB)
- CopyCommand.ExecuteNonQuery()
- CopyCommand = New SqlClient.SqlCommand("DISABLE TRIGGER Projects ON DATABASE", Destination_SQL_Connection_NewDB)
- CopyCommand.ExecuteNonQuery()
- Destination_SQL_Connection_NewDB.Close()
- ChangeDestinationValues(Workingtable)
- Destination_SQL_Connection_NewDB.Open()
- CopyCommand = New SqlClient.SqlCommand("ENABLE TRIGGER ArchiveServers ON DATABASE", Destination_SQL_Connection_NewDB)
- CopyCommand.ExecuteNonQuery()
- CopyCommand = New SqlClient.SqlCommand("ENABLE TRIGGER Projects ON DATABASE", Destination_SQL_Connection_NewDB)
- CopyCommand.ExecuteNonQuery()
- Destination_SQL_Connection_NewDB.Close()
- Catch ex As Exception
- MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace)
- End Try
- End Sub
- Public Sub ChangeDestinationValues(ByVal WorkingTables As DataTable)
- Dim FoundRow() As DataRow = WorkingTables.Select("PDM_Type = 'Destination'")
- For Each SelectedRow As DataRow In FoundRow
- If CStr(SelectedRow("Table_Name")) <> "NoSQLReading" Then
- Dim Destination_SQL_Connection As SqlClient.SqlConnection = New SqlClient.SqlConnection
- Destination_SQL_Connection.ConnectionString = "Data Source =" & ServerName & "Initial Catalog=" & CStr(SelectedRow("PDM_System")) & "; User ID=" & UserName & " ;Password=" & PW
- Destination_SQL_Connection.Open()
- Dim Querystring As String = "Select TOP (1) * From " & CStr(SelectedRow("Table_Name"))
- Dim Temp_SQLDataAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(Querystring, Destination_SQL_Connection)
- Dim SQL_Table As DataTable = New DataTable
- Temp_SQLDataAdapter.FillSchema(SQL_Table, SchemaType.Source)
- Temp_SQLDataAdapter.Fill(SQL_Table)
- For Each Temp_SelectedRow As DataRow In SQL_Table.Rows
- If CStr(SelectedRow("Table_Name")) <> "Projects" Then
- Temp_SelectedRow(CStr(SelectedRow("Table_CLM"))) = SelectedRow("Desc_Value")
- ElseIf CStr(SelectedRow("Table_Name")) = "Projects" Then
- If CInt(Temp_SelectedRow("ProjectID")) = 1 Then
- Temp_SelectedRow(CStr(SelectedRow("Table_CLM"))) = SelectedRow("Desc_Value")
- Exit For
- End If
- End If
- Next
- Temp_SQLDataAdapter.Update(SQL_Table)
- Destination_SQL_Connection.Close()
- End If
- Next
- End Sub
Danke im Voraus :-)!