CODEWITHSUNDEEP
sql-servervbscriptuniqueidentifier
Tester101
Tester101

Reputation: 8172

Convert string to uniqueidentifier in VBScript

I have a script that runs a stored procedure in my SQL server database, the problem is the stored procedure takes a uniqueidentifier parameter. I have a function that grabs a session id from the database (which is an nvarchar), so VBScript makes it a string and I need to convert it to pass it to the stored procedure.


Function GetOpenSession
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=" & Source
    rs.CursorLocation = 3
    rs.Open "SELECT top 1 OpenSession FROM OpenSessions with (nolock)" , conn, 3, 3
    If rs.RecordCount = 0 Then
        MsgBox "No Connection"
    Else
        GetOpenSession = rs.Fields(0).Value
    End If
End Function

I get "Invalid character value for cast specification" when I try to execute the stored procedure.


    set cnParam = cmd.CreateParameter("@ActiveSession",72,1,,GetOpenSession)
    cmd.Parameters.Append cnParam

I can't change anything in the database, so I need a way to overcome this in my script.

Upvotes: 3

Views: 6072

Answers (5)

Ryan
Ryan

Reputation: 8005

Depending on the data type of the SELECT OpenSession, you may be able to cast/convert it in the query and VBScript may possibly keep the data type as a GUID:

SELECT top 1 CONVERT(uniqueidentifier, OpenSession)
FROM OpenSessions with (nolock)

When you use GetOpenSession or rs.Fields(0).Value, hopefully VBScript will keep it as a GUID.

The other possibility seems to be a Win32 API using CoCreateGuid and StringFromGUID2. An example is found here, but it requires external Win32 functions and a Type for GUID.

Upvotes: 1

Tmdean
Tmdean

Reputation: 9309

You could forget about using ADO Commands altogether and just execute the stored procedure using plain SQL.

Set rs = CreateObject("ADODB.Recordset")
rs.Open "EXEC usp_MySP '" & GetOpenSession & "'", cnn

Obviously it's terrible to build SQL commands like this, but it's just for a test, after all...

Upvotes: 0

cmsjr
cmsjr

Reputation: 59215

I believe VBScript expects GUIDs to be brace terminated. Is your Session id of the same format as the following {D6CA6263-E8E1-41C1-AEA6-040EA89BF030}

Upvotes: 5

Adam Ralph
Adam Ralph

Reputation: 29956

What is the value of GetOpenSession after you have assigned it? Which datatype? It sounds like it is not compatible with type 72 (GUID), which you are stating in the CreateParameter call.

Upvotes: 0

AnthonyWJones
AnthonyWJones

Reputation: 189505

I usually change the parameter type of the stored procedure to varchar(38).

SQL Server makes a better job of coercing the string to a GUID when needed.

Upvotes: 0

Related Questions