Query Analyzer für den MS SQL-Server in VB Mit diesem Workshop möchten wir Ihnen zeigen, wie Sie komplexe "Transact SQL (T-SQL)" Scripte mit VB und ADO auf einem MS SQL-Server, bzw. der kostenlosen MSDE absetzen können. Was wir dafür benötigen, ist ein Query Analyzer den wir uns in diesem Workshop bauen wollen.
Autor:  Roland Wutzke	Bewertung:     [ Jetzt bewerten ]	Views:  37.367

Geben Sie hier Ihre persönliche Bewertung ab! Anzahl Bewertungen bisher: 5 Bewertung insgesamt: gut Durchschnittliche Bewertung: 4.0 von 5 Sternen

sehr gut     gut     nützlich     mittelmäßig     mangelhaft

Mit diesem Workshop möchten wir Ihnen zeigen, wie Sie komplexe "Transact SQL (T-SQL)" Scripte mit VB und ADO auf einem MS SQL-Server, bzw. der kostenlosen MSDE absetzen können. Was wir dafür benötigen, ist ein Query Analyzer den wir uns in diesem Workshop bauen wollen.

Grundlagen

Zunächst wollen wir Ihnen einige Grundlagen über T-SQL vermitteln.

Transact SQL, auch T-SQL genannt, sind i.d.R. komplexe Anweisungsscripte für unseren Server. Dabei kann ein solches Script aus mehreren Anweisungsblöcken bestehen. Solche Scripte führen meist direkte serverseitige Aktionen aus um Daten zu aktualisieren, zu löschen oder aber Daten zu importieren und exportieren usw...

Aufbau eines Scripts:
Wir bereits erwähnt, besteht ein T-SQL Script aus mehreren Anweisungsblöcken. Die Blöcke selber werden mit dem Schlüsselwort "GO" abgeschlossen.

USE DatabaseName
Go

Anweisungsblock 1
...
...
Go

Anweisungsblock 2
...
...
Go

Wie hier im Aufbau zu sehen, verwenden wir ein weiteres Schlüsselwort: "USE".

Mit Use <DatabaseName> wird die Datenbank gewechselt, also zur aktuellen Datenbank für das Script gemacht, da unser Server ja mehrere Datenbanken beinhaltet.

Neue Datenbank erzeugen:
Die wohl elementarste Funktion unseres Servers ist die, eine neue Datenbank anlegen zu können. Das Schlüsselwort hierzu ist die "CREATE" Anweisung. Im Script sieht das dann so aus:

USE master
Go

CREATE DATABASE [Verkauf]
Go

Datenbank löschen:
Das Schlüsselwort um ein Serverobjekt zu löschen ist "DROP". Also eine Datenbank löschen geht mit folgendem Script:

/* ACHTUNG !!!
Dieses Script löscht die Datenbank Verkauf */

USE master
Go

DROP1 DATABASE [Verkauf] -- Löscht die DB Verkauf
Go

Kommentieren Sie Ihr Script für die Nachwelt:
Schauen wir uns das letzte Beispiel an, so sehen wir zwei Möglichkeiten das Script zu dokumentieren. Sie können ganze Blöcke kommentieren, indem Sie diese mit "/* Kommentarblock */" einfassen. Die zweite Möglichkeit kommentiert mit zwei Minuszeichen "-" den Rest einer Zeile aus.

Verwenden von Variablen:
In T-SQL können Sie lokale Variablen verwenden. Variablen werden mit der Anweisung "DECLARE" unter Angabe des Datentyps definiert. Wahlweise können Sie das Schlüsselwort "AS" verwenden. Vor dem Variablennamen wird immer ein @ gesetzt:

DECLARE @var1 AS Int
DECLARE @var2 AS Smallmoney
DECLARE @var3 AS Varchar(5)

Die Wertzuweisung einer Variable erfolgt auf zwei Arten:

• SET Anweisung
• SELECT Anweisung

DECLARE @Umsatz AS Int
DECLARE @Verkäufer AS Varchar(50)

SET @Umsatz = 5000
SET @Verkäufer = (SELECT Nachname FROM Personal WHERE PersNr = 456)

Im folgenden Beispiel wird die Tabelle Abverkauf mittels eines Schwellenwerts aktualisiert:

/* Aktualisieren der Tabelle Abverkauf */

USE [Verkauf]
Go

DECLARE @Umsatz AS Smallmoney
SET @Umsatz = 5000

UPDATE Abverkauf SET Verkaufshit = 10 WHERE Gesamtverkauf > @Umsatz
Go

IF Bedingungen:
Genau wie in VB kennt T-SQL ebenfalls die IF-Bedingung. Doch im Gegensatz zu VB gibt es kein "Then" und "End If".

IF Bedingung
   Anweisung
ELSE
   Anweisung

Eine einfache IF Anweisung im Script könnte dann so aussehen:

/* Aktualisieren der Tabelle Abverkauf */

USE [Verkauf]
Go

DECLARE @Umsatz AS Smallmoney
SET @Umsatz = 5000

IF @Umsatz < 1000
   UPDATE Abverkauf SET Verkaufshit = 0
ELSE
   UPDATE Abverkauf SET Verkaufshit = 10

Go

Anweisungsblöcke in einer IF-Anweisung werden mit BEGIN und END eingefasst:

IF Bedingung
BEGIN
   Anweisung 1
   ...
   Anweisung n
END
ELSE
BEGIN
   Anweisung 1
   ...
   Anweisung n
END

Datenimport mit BULK INSERT:
Mit T-SQL können Sie den Massenimport einer flachen Textdatei, bspw. einer CSV Datei, per BULK INSERT vornehmen. Der BULK INSERT ist extrem schnell und mit nur wenigen Zeilen zu realisieren:

USE [Verkauf]
GO

BULK INSERT Tabellenname FROM 'C:\Daten\Import.csv'
   WITH (FIELDTERMINATOR = ';',
      DATAFILETYPE = 'char',
      CODEPAGE = 'RAW')

Go

Bitte beachten Sie die Pfadangabe im BULK INSERT. Der Pfad muss ein gültiger Pfad für den Server sein, sonst kommt es zu einem Fehler. 

Table und View:
Wie Tabellen und Sichten angelegt und gelöscht werden, wird bereits im Workshop  Grundlagen: Arbeiten mit der kostenlosen MSDE-Datenbank von Microsoft von Wolfgang Christ beschrieben. Ich möchte an dieser Stelle lediglich auf diesen Workshop verweisen.

Die hier aufgeführten Beispiele stellen nur eine kleine Übersicht über T-SQL dar. Wenn Sie mehr über T-SQL erfahren möchten, sollten Sie folgenden Link besuchen:
 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp.

Die Benutzerverwaltung des SQL-Servers:
Mit dem Tipp  User auf dem MS SQL-Server anlegen haben wir Ihnen bereits gezeigt, wie die Userverwaltung auf dem Server aufgebaut ist. Nun möchten wir das Script aufzeigen um einen User anzulegen, damit wir dieses später in unserem Query Analyzer einsetzen können.

/* Anlegen eines neuen Users auf dem Server
und Zulassen für die Datenbank Verkauf. Der
neue User bekommt die Zugriffsrechte DataReader
und DataWriter */

/* Wichtig! Da ein neuer User nur über die
Master DB angelegt werden kann */
USE master
Go

/* User auf dem Server anlegen */
EXEC sp_addlogin 'Paul', 'x1234'
Go

/* Wechseln zur Datenbank Verkauf */
USE Verkauf
Go

/* Den neuen User die DB zuweisen */
EXEC sp_grantdbaccess 'Paul'
Go

/* Jetzt den User die Zugriffsrollen zuweisen */
EXEC sp_addrolemember 'db_datareader', 'Paul'
Go
EXEC sp_addrolemember 'db_datawriter', 'Paul'
Go

Stored Procedures:

Stored Procedures sind Scripte, die auf dem Server liegen und auch dort ausgeführt werden. Es gibt zwei Typen von Stored Procedures: Die globalen serverweit gültigen und die datenbankspezifischen Procedures. Die globalen Procedures liegen i.d.R. in der Master-DB. Eine globale Procedure kann von jeder DB auf dem Server ausgeführt werden, eine datenbankspezifische dagegen nur aus der eigenen Datenbank. Sie ist demnach auch nur für diese Datenbank gültig.

Die Ergebnisse dieser Scripte werden an den Client als Recordset zurückgegeben. Um eine Stored Procedure auszuführen, wird das Schlüsselwort "EXEC" verwendet.

EXEC sp_who2

Diese Procedure bspw. liefert Ihnen alle laufenden Prozesse auf dem Server.

Der MS SQL-Server, bzw. die MSDE werden mit einer Reihe von Stored Procedures ausgeliefert. Nachfolgend möchten wir Ihnen einige vorstellen.

sp_droplogin:
Diese Procedure löscht einen Server Login-User. Sie ist das Gegenstück zu sp_addlogin.

EXEC sp_droplogin 'LoginName'

sp_revokedbaccess:
Mit dieser Procedure wird ein Datenbankbenutzer aus einer Datenbank gelöscht. Das Gegenstück hierzu ist sp_grantdbaccess.

EXEC sp_revokedbaccess 'Datenbankbenutzer'

sp_droprolemember:
Diese Procedure löscht einen Datenbankbenutzer aus der angegebenen Datenbankrolle. Das Gegenstück hierzu ist sp_addrolemember.

EXEC sp_droprolemember 'DBUser'

sp_addsrvrolemember:
Mit dieser Procedure wird ein LoginUser in eine Serverrolle eingetragen. Die Serverrolle für einen System-Administrator ist 'sysadmin'

EXEC sp_addsrvrolemember 'LoginUser', 'ServerRole'

sp_dropsrvrolemember:
Löscht einen angegebenen LoginUser aus einer Serverrolle

EXEC sp_dropsrvrolemember 'LoginName', 'ServerRole'

sp_addrole:
Eine neue Datenbankrolle wird mit dieser Procedure angelegt.

EXEC sp_addrole 'RoleName'

sp_droprole:
Gelöscht wird eine Datenbankrolle mit dieser Procedure.

EXEC sp_droprole 'RoleName'

sp_helpdb:
Diese Procedure listet alle Datenbanken des Servers auf. Es wird ein Recordset zurückgeliefert.

EXEC sp_helpdb [Optional DatabaseName]

sp_helpgroup:
Diese Procedure listet alle Rollen der aktuellen Datenbank auf. Es wird ein Recordset zurückgeliefert.

EXEC sp_helpgroup [Optional RoleName]

sp_helpindex:
Diese Procedure listet alle Indizes einer Tabelle oder Sicht aus der aktuellen Datenbank auf. Es wird ein Recordset zurückgeliefert.

EXEC sp_helpindex 'ObjectName'

sp_helplogins:
Diese Procedure listet alle LoginUser des Servers auf. Es wird ein Recordset zurückgeliefert.

EXEC sp_helplogins [Optional LoginUser]

sp_helprolemember:
Diese Procedure listet alle Mitglieder aller Rollen der aktuellen Datenbank auf. Es wird ein Recordset zurückgeliefert.

EXEC sp_helprolemember [Optional RoleName]

sp_helpuser:
Diese Procedure listet alle Benutzernamen der aktuellen Datenbank auf. Es wird ein Recordset zurückgeliefert.

EXEC sp_helpuser [Optional Benutzername]

sp_helptext:
Diese Procedure listet die Regel (SQL-Statement) einer Sicht, Stored Procedure, Funktion und Trigger auf. Tabellen werden nicht unterstützt. Es wird ein Recordset zurückgeliefert.

EXEC sp_helptext 'ObjectName'

Wenn Sie mehr über Stored Procedues erfahren möchten, dann sollten Sie die MSDN Online Seite besuchen:
 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp

Goldgrube:
Eine sehr gute Fundgrube für SQL-Scripte ist die Webseite von SQL-Server Central. Hier finden Sie Scripte, Tipps & Tricks und ein gutes Forum. Sie müssen sich registrieren lassen und bekommen 2-3 mal wöchentlich einen Newsletter. Die Seite ist ansonsten kostenlos. Empfehlung: ***
 http://www.sqlservercentral.com/

Query Analyzer:

So, nun aber zu unserem Query Analyzer in VB6. Das nachfolgende Klassenmodul bildet das Kernstück unseres Query Analyzers.

Über die ADO Schnittstelle kann Zugriff auf den MS SQL-Server, bzw. der kostenlosen MSDE genommen werden. Das Klassenmodul ist in der Lage, komplexe T-SQL Scripte auszuführen. Dabei werden die Anweisungsblöcke, die mit "GO<CR>" getrennt sein müssen, geparst und einzeln über ein Command-Objekt ausgeführt. Die Rückgabe der betroffenen Records erfolgt über das Ereignis "RecordsAffected".

' ***********************************************************************
' *
' * clsQueryAnalyzer VB6 Klassenmodul
' *
' * Dieses Klassenmodul stellt die Basisroutinen des Query Analyzers
' * zur Verfügung. Über die ADO Schnittstelle kann Zugriff auf den
' * MS SQL-Server, bzw. der kostenlosen MSDE genommen werden.
' *
' * Das Klassenmodul ist in der Lage, komplexe T-SQL Scripte
' * auszuführen. Dabei werden die Anweisungsblöcke, die mit "GO<CR>"
' * getrennt sein müssen, geparst und einzeln über ein Command-Objekt
' * ausgeführt. Die Rückgabe der betroffenen Records erfolgt über
' * das Ereignis "RecordsAffected".
' *
' * Juli 2005 - VB-Power.net www.vb-power.net
' *
' ***********************************************************************
 
Option Explicit
 
' Klassenvariablen
Private WithEvents cConn As ADODB.Connection
Private cTimeOut As Long
 
' Events
Public Event RecordsAffected(ByVal AnzRecords As Long)

' Klasse initialisieren
Private Sub Class_Initialize()
  cTimeOut = 30
End Sub

' Active Connection
Public Property Set ActiveConn(vData As ADODB.Connection)
  Set cConn = vData
End Property

' Command TimeOut
Public Property Let TimeOut(ByVal vData As Long)
  cTimeOut = vData
End Property
 
Public Property Get TimeOut() As Long
  TimeOut = cTimeOut
End Property

' ===========================
' Hauptroutine Query Analyzer
' ===========================
Public Function RunTSQL(TSQL As String) As Boolean
  Dim oCmd As New ADODB.Command
  Dim oRSCmd As ADODB.Recordset
  Dim cmdSet() As String
  Dim lRet As Long
  Dim X As Long
 
  ' Überprüfungen
  If cConn Is Nothing Then Exit Function
  If TSQL = "" Then Exit Function
 
  On Error GoTo RunTSQL_Error
 
  ' Das Script parsen. Dabei werden die Anweisungsblöcke
  ' in das Array geschoben. Es wird angenommen,
  ' dass die einzelnen Anweisungen im Script
  ' mit "go<CR>" getrennt sind.
  cmdSet = TSQLParser(TSQL)
 
  With oCmd
    ' Command initialisieren
    .ActiveConnection = cConn
    .CommandType = adCmdText
    .CommandTimeout = cTimeOut
 
    ' Schleife über alle Anweisungen
    ' aus dem T-SQL Script.
    For X = 0 To UBound(cmdSet)
      If cmdSet(X) <> "" Then
        ' RollBack sicherstellen
        cConn.BeginTrans
 
        ' T-SQL ausführen
        .CommandText = Trim(cmdSet(X))
        Set oRSCmd = .Execute(lRet)
 
        ' Wenn kein Fehler, dann übernehmen
        cConn.CommitTrans
 
        ' Ereignis auslösen
        RaiseEvent RecordsAffected(lRet)
      End If
    Next X
  End With
 
  ' Command und Recodset terminieren
  Set oCmd = Nothing
  Set oRSCmd = Nothing
 
  RunTSQL = True
  Exit Function
 
RunTSQL_Error:
  cConn.RollbackTrans
  Set oCmd = Nothing
  Set oRSCmd = Nothing
  RunTSQL = False
 
  MsgBox Err.Description, vbCritical, "Fehler"
End Function

' =====================
' SQL-Syntax überprüfen
' =====================
Public Function CheckTSQL(TSQL As String) As Boolean
  If cConn Is Nothing Then Exit Function
  If TSQL = "" Then Exit Function
 
  ' Ausführung unterdrücken
  cConn.Execute "SET NOEXEC ON"
 
  ' Syntax prüfen
  If RunTSQL(TSQL) Then
    CheckTSQL = True
  Else
    CheckTSQL = False
  End If
 
  ' Ausführung wieder zulassen
  cConn.Execute "SET NOEXEC OFF"
End Function

' Hilfsfunktion
Private Function TSQLParser(ByVal oText As String) As Variant
  Dim tmpStr As String
 
  tmpStr = oText
  tmpStr = Replace(tmpStr, "go" & vbCrLf, "GO" & vbCrLf, , , vbBinaryCompare)
  tmpStr = Replace(tmpStr, "Go" & vbCrLf, "GO" & vbCrLf, , , vbBinaryCompare)
  tmpStr = Replace(tmpStr, "gO" & vbCrLf, "GO" & vbCrLf, , , vbBinaryCompare)
 
  TSQLParser = Split(tmpStr, "GO" & vbCrLf)
End Function

' Ereignis der Connection
Private Sub cConn_InfoMessage(ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)
 
  If adStatus = adStatusErrorsOccurred Then
    MsgBox Err.Description
  End If
End Sub

Demoprojekt Query Analyzer:
Das VB6 Demoprojekt ist die ideale Ergänzung für alle Benutzer der kostenlosen MSDE - aber auch für die SQL-Server Benutzer. Es zeigt den Einsatz der Klasse "clsQueryAnalyzer". Besonders hilfreich dabei sind die vorgefertigten Scripte, welche die Arbeit mit dem Server erleichtern. Die Scripte können einfach aus dem Kontextmenue abgerufen werden.

Das Demoprojekt gibt es hier zum downloaden.
 QueryAnalyzer.zip (15 KB)

Ich wünsche Ihnen gutes Gelingen mit dem Query Analyzer.

Roland Wutzke