sql server msde: Hi,

Joseph Geretz schrieb:

[quoted text, click to view]

I have done this on access dbs some times ago using the ADO command
object. Should work for SQL server too. Attached is an excerpt of the
class I wrote for this purpose. Take it for recognizing the basic
principles. Some helper methods used by this class are not shown but
their working should be clear from its names or/and the comments.

<class_code>

'**********************************************************************************
'* Private types and consts
'**********************************************************************************

Private Const mcCLASSNAME As String = "CSQLExecute"

Private Const mcSQL_EXPRESSIONDELIMITER As String = ";"
Private Const mcSQL_COMMENT As String = "-"

'**********************************************************************************
'* Public properties and methods
'**********************************************************************************

'Executes a string containing zero, one or more valid SQL statements.
'The SQL statements must be non record returning statements
'(that is DDL or some of the DML statements, no SELECT-statement for
example)
'The SQLString parameter is cleared after execution of ParseSQL.

Public Sub Execute(ByVal DatabasePath As String, _
ByRef SQLString As String)
Dim EH As CHLPErrorExit
Dim SQLCode() As String, SQLCodeCount As Long

On Error GoTo MethodError

gErr.Clear
gErr.EnterMethod EH, "Execute", mcCLASSNAME

If Not gFH.FileExists(DatabasePath) Then
gErr.SetError "No database file " & DatabasePath & " found"
Exit Sub
End If

ParseSQL SQLString, SQLCode, SQLCodeCount
ExecuteSQLCode DatabasePath, SQLCode, SQLCodeCount

Exit Sub

MethodError:

gErr.SetError "Unexpected VB error"

End Sub

'**********************************************************************************
'* Private workers
'**********************************************************************************


'Parses inputstring to separate SQL expressions
'Puts each SQL-expression as one string into SQLCode(n)

'Inputstring format:

'Lines starting with "-" or "<whitespace>-" are treated as SQL comments
'Lines with trailing comments are not allowed
'Empty lines (containing whitespace only) are ignored

'Each SQL expression must start at a new line (this implies: only one
SQL expression
'per line is allowed)
'Each SQL expression must end with a semicolon
'The SQL expression may span one or several lines
'The finishing semicolon may be on its own line
'Semicola in an expression line (for example in textvalues) are allowed
as long
'they are not the last character (ignoring following whitespace) on the line

'Above format should be valid too for other tools being capable of
interpreting SQL


Private Sub ParseSQL(ByRef SQLString As String, _
ByRef SQLCode() As String, _
ByRef SQLCodeCount As Long)

Dim i As Long, InSQLExpression As Boolean
Dim sarr() As String, s As String, ss As String

If Len(SQLString) = 0 Then Exit Sub
sarr = Split(SQLString, vbCrLf)
SQLString = vbNullString 'release memory

For i = 0 To UBound(sarr)

'For parsing remove trailing and leading whitespace
'In the resulting parsed code the original string is preserved!
gTrimWS s, sarr(i)

Select Case True

Case Len(s) = 0 'Skip empty line
Case Left$(s, 1) = mcSQL_COMMENT 'Skip comment line starting with
mcSQL_COMMENT

Case InSQLExpression

If Right$(s, 1) = mcSQL_EXPRESSIONDELIMITER Then
gAddString ss & (vbCrLf & sarr(i)), SQLCode, SQLCodeCount
InSQLExpression = False
Else
ss = ss & (vbCrLf & sarr(i))
End If

Case Else

If Right$(s, 1) = mcSQL_EXPRESSIONDELIMITER Then
gAddString sarr(i), SQLCode, SQLCodeCount
Else
InSQLExpression = True
ss = sarr(i)
End If

End Select

Next i

End Sub

'Executes the SQLcode

'If an error during execution of a SQL-statement, the procedure stops
further
'processing. This makes sense for SQL-statements which must be executed in a
'certain order and rely on earlier statements been successfully executed.

Private Sub ExecuteSQLCode(ByRef DBFilePath As String, _
ByRef SQLCode() As String, _
ByRef SQLCodeCount As Long)

Dim i As Long
Dim Conn As ADODB.Connection, Cmd As ADODB.Command
Dim EH As CHLPErrorExit

gErr.EnterMethod EH, "ExecuteSQLCode"

If SQLCodeCount = 0 Then
gErr.SetError "No SQL expressions found"
Exit Sub
End If

Set Conn = gGetNewOpenConn(DBFilePath, adUseClient)
If gHasError Then
gErr.SetError "Unable to connect to database"
Exit Sub
End If

On Error Resume Next

Set Cmd = New ADODB.Command
Set Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdText
If Err.Number <> 0 Then
gErr.SetError "Unable to create ADO command object": Err.Clear
gDestroyConn Conn
Exit Sub
End If

On Error GoTo MethodError

For i = 0 To SQLCodeCount - 1
Cmd.CommandText = SQLCode(i)
Cmd.Execute
Next i

gDestroyConn Conn

Exit Sub

MethodError:

gErr.SetError "Error during execution of SQL expression number " & (i +
1) & ":", _
"", SQLCode(i), "", gErr.DumpADOErrors(Conn)
gDestroyConn Conn

End Sub

</class_code>

As an example part of a script the class could execute:

<script>

- ************************************************************
- * Umsetzung Feld KORN zu kategorischem Datum
- * Erzeugt tabKAT_KORNGROESSE
- ************************************************************

- Gefunden: <leer>, |"f"|f |f,g|f,m|f/g|f+g|ff |f-g|f-m|g |g-f|
- |gg |g-m|g-r|m |m,g|m,r|m-f|m-g|m-r|r |r-g|

- Erzeugung der kategorischen Tabelle tabKAT_KORNGROESSE in DNSAORG,
- aufbereitet für direkten Export per Copy.

- Anlegen eines neuen Feldes fkKAT_KORNGROESSE in dnsa, welches
ebenfalls direkt
- exportiert werden kann.

- Die kategorischen Werte sind die 1:1 Kopien der vorgefundenen, aber
getrimmten
- Werte. NULL-Werte bleiben erhalten, Einträge nur mit Spaces oder leer
wurden NULL gesetzt.

UPDATE DNSA SET KORN = Trim(KORN)
WHERE Not IsNull(KORN);
UPDATE DNSA SET KORN = NULL
WHERE Not IsNull(KORN) AND Len(KORN) = 0;

INSERT INTO tabKAT_KORNGROESSE
SELECT KORN AS txtKAT_KORNGROESSE_Kurz,
KORN AS txtKAT_KORNGROESSE_Voll
FROM DNSA
WHERE Not IsNull(KORN)
GROUP BY KORN;

ALTER TABLE DNSA
ADD COLUMN fkKAT_KORNGROESSE LONG;

We have certain SQL tasks which we've been carrying out by running scripts
in Query analyzer (sample below). In some contexts, the users won't be
sophisticated enough to execute these scripts in this manner. We'd therefore
like to develop a small VB SQL script execution utility program which we can
distribute.

What's the best way to execute this and similar scripts from a VB program?
Note that I don't want to code our utility to perform specific database
maintenance actions. I'd like to leave the actual database maintenance
details in an external script file. The VB utility should simply read in the
script file, log in to the database server, and submit the script for
execution. It's this last operation that I'm not sure how to accomplish.

Thanks for your advice.

- Joe Geretz -

USE master
GO

IF EXISTS (select * from dbo.sysdatabases where name = 'FreedomDEMO')
BEGIN
DROP DATABASE [FreedomDEMO]
END
GO

RESTORE DATABASE [FreedomDEMO]
FROM DISK = 'c:\SRSServer\Database\FreedomDEMO.bak'
WITH
MOVE 'FreedomDEMO_data' TO 'c:\SRSServer\Database\FreedomDEMO.mdf',
MOVE 'FreedomDEMO_log' TO 'c:\SRSServer\Database\FreedomDEMO.ldf'

GO

IF 0 = (SELECT COUNT(LOGINNAME) FROM SYSLOGINS WHERE LOGINNAME =
'srssoftware')
BEGIN
EXEC sp_addlogin 'userblah', 'blahdeblahbla', 'FreedomDEMO'
END
GO

You could fire off OSQL and pass the script file as a parameter -Q or as a
file -i

For adding users why not allow the use of SEM?... There is also a restore
wizard...

--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)

I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
[quoted text, click to view]