Hi All
We recently moved from FW 1.1 to FW 2.0, and 2003 to 2005, also now using SQL Express.
The problem im having is that in code ie from this function:
visual basic code:
Protected Function ExecuteNonQuery(ByVal sSql As String, ByVal eDBType As cDBSettings.DatabaseType, Optional ByVal sDatabaseName As String = "", Optional ByVal bUseTransaction As Boolean = True) As Boolean
'Executes a sql statement and returns the result
Dim oCommand As SqlCommand = Nothing
Dim oConnection As SqlConnection = Nothing
Dim oTransaction As SqlTransaction = Nothing
Dim bSuccess As Boolean
Try
oConnection = GetDBConnection(eDBType, sDatabaseName)
oConnection.Open()
If bUseTransaction Then
oTransaction = oConnection.BeginTransaction()
End If
oCommand = New SqlCommand(sSql, oConnection)
With oCommand
If bUseTransaction Then
.Transaction = oTransaction
End If
.ExecuteNonQuery()
If bUseTransaction Then
oTransaction.Commit()
End If
End With
bSuccess = True
Catch e As Exception
If Not oTransaction Is Nothing Then
oTransaction.Rollback()
End If
bSuccess = False
Finally
If Not oTransaction Is Nothing Then oTransaction.Dispose()
If Not oCommand Is Nothing Then oCommand.Dispose()
If Not oConnection Is Nothing Then oConnection.Close() : oConnection.Dispose()
oCommand = Nothing
oConnection = Nothing
oTransaction = Nothing
End Try
Return bSuccess
End Function
Running this SQL statement:
visual basic code:
if (SELECT DATABASEPROPERTYEX('GateKeeper_1014', 'UserAccess')) = 'MULTI_USER' exec sp_dboption N'GateKeeper_1014', N'dbo use only', N'true'
The problem throws an Error that i must declare a scalar variable, but i pasted that into the SQL Manager and it runs.
So i put sql.tolower and i get the problem that it cannot find gatekeeper_1014, but again if i paste this into the SQL Manager it runs fine.
Also the fact that other developers in my office having the same setup some require uppercase SQL and some require them to be lower on non reserved words, as soon as i have reserved words lower case it throughs a general SQL error.
I have found that the colliation might be the issue but i have tried the compatibility mode on the Express install but still no avail!
If anyone can shed some light on this or put me in the direction of some information, i would be grateful.
Regards
Danny
Hi Danny,
There are differences between running a T-SQL command in a query window and trunning it using a SqlCommand object. (I'm guessing you're using a SqlCommand.) I find that it's easier to run single statements in a SqlCommand at a time. Here is a sample that does what you want:
Sub Main()
Dim cnn As SqlConnection
Dim cmd As SqlCommand
cnn = New SqlConnection("Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=True")
cnn.Open()
cmd = New SqlCommand("SELECT DATABASEPROPERTYEX('AdventureWorks', 'UserAccess')")
cmd.Connection = cnn
Dim s As String
s = cmd.ExecuteScalar()
If s = "MULTI_USER" Then
cmd.CommandText = "sp_dboption N'AdventureWorks', N'dbo use only', N'true'"
cmd.ExecuteNonQuery()
End If
cnn.Close()
End Sub
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
