SQL UniqueIdentifier - OBJECTGUID error

  • Thread starter Thread starter Bmack500
  • Start date Start date
B

Bmack500

Thank you in Advance!
I'm storing a record for an AD object using the
Object GUID and storing it as a UniqueIdentifier in SQL server, like
this:

svrStruct is a Structure with svrStruct as: Dim oGUID as GUID
'***************
Dim objectGUID As Guid


svrStruct.oError = False
'Fetch the GUID from AD, then...
Try
objectGUID = New
Guid(DirectCast(oResult.Properties("objectGUID")(0), Byte()))
svrStruct.oGUID = objectGUID
Catch ex As Exception
strGUID = "No objectGUID Found"
End Try
'***************
no problems. I then need to go back and update the record, so I use the

following code:


'**************
strSQL = "UPDATE pma_SERVERINFO " _
& "SET DOMAIN = @domain, HOSTNAME = @hostname, NAME = @name, DN =
@distinguishedname, " _
& "DESCRIPTION = @description, OS = @os, OS_VERSION = @os_version, TZ=

@tz, " _
@create_date, " _
& "SPMAJOR = @sp_major, SPMINOR = @sp_minor " _
& "WHERE (OBJECTGUID = '@GUID')"


Dim sqlCMD As SqlClient.SqlCommand = New SqlCommand(strSQL,
sqlConnection)
sqlCMD.CommandType = CommandType.Text
sqlCMD.Parameters.Add(New SqlParameter("@GUID", SqlDbType.VarChar,
20)).Value = svrInfo.oGUID
sqlCMD.Parameters.Add(New SqlParameter("@domain", SqlDbType.VarChar,
20)).Value = svrInfo.oDomain
sqlCMD.Parameters.Add(New SqlParameter("@hostname", SqlDbType.VarChar,
150)).Value = svrInfo.oDNSHOSTNAME
sqlCMD.Parameters.Add(New SqlParameter("@name", SqlDbType.VarChar,
50)).Value = svrInfo.oNAME
sqlCMD.Parameters.Add(New SqlParameter("@distinguishedname",
SqlDbType.VarChar, 250)).Value = svrInfo.oDN
sqlCMD.Parameters.Add(New SqlParameter("@description",
SqlDbType.VarChar, 150)).Value = svrInfo.oDescription
sqlCMD.Parameters.Add(New SqlParameter("@os", SqlDbType.VarChar,
50)).Value = svrInfo.oOS
sqlCMD.Parameters.Add(New SqlParameter("@os_version",
SqlDbType.VarChar, 50)).Value = svrInfo.oOSVersion
sqlCMD.Parameters.Add(New SqlParameter("@tz", SqlDbType.Int, 0)).Value
= svrInfo.oTZ
sqlCMD.Parameters.Add(New SqlParameter("@mng_by", SqlDbType.VarChar,
250)).Value = svrInfo.oManagedBy
sqlCMD.Parameters.Add(New SqlParameter("@last_logon",
SqlDbType.DateTime, 0)).Value = svrInfo.oLastLogon
sqlCMD.Parameters.Add(New SqlParameter("@create_date",
SqlDbType.DateTime, 0)).Value = svrInfo.oCreateDate
sqlCMD.Parameters.Add(New SqlParameter("@sp_major", SqlDbType.Int,
0)).Value = svrInfo.oSPMajor
sqlCMD.Parameters.Add(New SqlParameter("@sp_minor", SqlDbType.Int,
0)).Value = svrInfo.oSPMinor
If sqlConnection.State = ConnectionState.Closed Then
sqlConnection.Open()
End If
Try
rowsAffected = sqlCMD.ExecuteNonQuery()
Catch ex As Exception
writeLog("UPDATE SERVER INFO sql error: " & ex.Message)

writeLog("UPDATE SERVER INFO sql string: " &
sqlCMD.CommandText)
writeLog("Server: " & svrInfo.oDNSHOSTNAME &
ControlChars.CrLf & "GUID: " & svrInfo.oGUID.ToString)


writeLog("*****************************************************************­**********")

Exit Function
Finally
sqlCMD.Dispose()
End Try
'**************
My logs show the following error:
UPDATE SERVER INFO sql error: Syntax error converting from a character
string to uniqueidentifier.


I don't get it - I'm able to use almost the same syntax to search, so
why doesn't this work? Thanks in advance!
 
Change

sqlCMD.Parameters.Add(New SqlParameter("@GUID", SqlDbType.VarChar,

to

sqlCMD.Parameters.Add(New SqlParameter("@GUID", SqlDbType.UniqueIdentifier)

and

& "WHERE (OBJECTGUID = '@GUID')"

to

& "WHERE (OBJECTGUID = @GUID)"


Thank you in Advance!
I'm storing a record for an AD object using the
Object GUID and storing it as a UniqueIdentifier in SQL server, like
this:

svrStruct is a Structure with svrStruct as: Dim oGUID as GUID
'***************
Dim objectGUID As Guid


svrStruct.oError = False
'Fetch the GUID from AD, then...
Try
objectGUID = New
Guid(DirectCast(oResult.Properties("objectGUID")(0), Byte()))
svrStruct.oGUID = objectGUID
Catch ex As Exception
strGUID = "No objectGUID Found"
End Try
'***************
no problems. I then need to go back and update the record, so I use the

following code:


'**************
strSQL = "UPDATE pma_SERVERINFO " _
& "SET DOMAIN = @domain, HOSTNAME = @hostname, NAME = @name, DN =
@distinguishedname, " _
& "DESCRIPTION = @description, OS = @os, OS_VERSION = @os_version, TZ =

@tz, " _
@create_date, " _
& "SPMAJOR = @sp_major, SPMINOR = @sp_minor " _
& "WHERE (OBJECTGUID = '@GUID')"


Dim sqlCMD As SqlClient.SqlCommand = New SqlCommand(strSQL,
sqlConnection)
sqlCMD.CommandType = CommandType.Text
sqlCMD.Parameters.Add(New SqlParameter("@GUID", SqlDbType.VarChar,
20)).Value = svrInfo.oGUID
sqlCMD.Parameters.Add(New SqlParameter("@domain", SqlDbType.VarChar,
20)).Value = svrInfo.oDomain
sqlCMD.Parameters.Add(New SqlParameter("@hostname", SqlDbType.VarChar,
150)).Value = svrInfo.oDNSHOSTNAME
sqlCMD.Parameters.Add(New SqlParameter("@name", SqlDbType.VarChar,
50)).Value = svrInfo.oNAME
sqlCMD.Parameters.Add(New SqlParameter("@distinguishedname",
SqlDbType.VarChar, 250)).Value = svrInfo.oDN
sqlCMD.Parameters.Add(New SqlParameter("@description",
SqlDbType.VarChar, 150)).Value = svrInfo.oDescription
sqlCMD.Parameters.Add(New SqlParameter("@os", SqlDbType.VarChar,
50)).Value = svrInfo.oOS
sqlCMD.Parameters.Add(New SqlParameter("@os_version",
SqlDbType.VarChar, 50)).Value = svrInfo.oOSVersion
sqlCMD.Parameters.Add(New SqlParameter("@tz", SqlDbType.Int, 0)).Value
= svrInfo.oTZ
sqlCMD.Parameters.Add(New SqlParameter("@mng_by", SqlDbType.VarChar,
250)).Value = svrInfo.oManagedBy
sqlCMD.Parameters.Add(New SqlParameter("@last_logon",
SqlDbType.DateTime, 0)).Value = svrInfo.oLastLogon
sqlCMD.Parameters.Add(New SqlParameter("@create_date",
SqlDbType.DateTime, 0)).Value = svrInfo.oCreateDate
sqlCMD.Parameters.Add(New SqlParameter("@sp_major", SqlDbType.Int,
0)).Value = svrInfo.oSPMajor
sqlCMD.Parameters.Add(New SqlParameter("@sp_minor", SqlDbType.Int,
0)).Value = svrInfo.oSPMinor
If sqlConnection.State = ConnectionState.Closed Then
sqlConnection.Open()
End If
Try
rowsAffected = sqlCMD.ExecuteNonQuery()
Catch ex As Exception
writeLog("UPDATE SERVER INFO sql error: " & ex.Message)

writeLog("UPDATE SERVER INFO sql string: " &
sqlCMD.CommandText)
writeLog("Server: " & svrInfo.oDNSHOSTNAME &
ControlChars.CrLf & "GUID: " & svrInfo.oGUID.ToString)


writeLog("*****************************************************************­**********")

Exit Function
Finally
sqlCMD.Dispose()
End Try
'**************
My logs show the following error:
UPDATE SERVER INFO sql error: Syntax error converting from a character
string to uniqueidentifier.


I don't get it - I'm able to use almost the same syntax to search, so
why doesn't this work? Thanks in advance!
 
Ouch! Too much copying & pasting... that's why it works in the other
section of my code!

Thanks much!
 
Back
Top