Conversion failed when converting the varchar value when calling stored procedure from vb.net

EDN Admin · Jun 9, 2011

Hello, 
 
I am developing a Windows application for restoring specific databases from our Production platform to "field" tablets by using backups that are taken daily to disk. To preclude "orphaned users" on the tablets, I want to extract the "user name", "password"
(hashed) and "sid". I am calling the MS Stored Procedure "sp_help_revlogin" that produces screen text to copy and paste into a query to recreate those users on another SQL server instance. I have modified the stored procedure to return results
to vb.net as shown here (only the modified part is shown for brevity: 

<span style="color:Blue; IF (@denylogin = 1)
BEGIN -- login <span style="color:Blue; is denied access
<span style="color:Blue; SET @tmpstr = @tmpstr + <span style="color:Green; ; DENY CONNECT SQL TO + QUOTENAME( @name )
<span style="color:Blue; END
<span style="color:Blue; ELSE <span style="color:Blue; IF (@hasaccess = 0)
BEGIN -- login exists but does <span style="color:Blue; not have access
<span style="color:Blue; SET @tmpstr = @tmpstr + <span style="color:Green; ; REVOKE CONNECT SQL TO + QUOTENAME( @name )
<span style="color:Blue; END
<span style="color:Blue; IF (@is_disabled = 1)
BEGIN -- login <span style="color:Blue; is disabled
<span style="color:Blue; SET @tmpstr = @tmpstr + <span style="color:Green; ; ALTER LOGIN + QUOTENAME( @name ) + DISABLE
<span style="color:Blue; END
--PRINT @tmpstr
<span style="color:Blue; RETURN @tmpstr
<span style="color:Blue; END

FETCH <span style="color:Blue; NEXT <span style="color:Blue; FROM login_curs <span style="color:Blue; INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
<span style="color:Blue; END
CLOSE login_curs
DEALLOCATE login_curs
--
<pre>System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=91
Message="Conversion failed when converting the varchar value CREATE LOGIN [##MS_PolicyEventProcessingLogin##] WITH PASSWORD = 0x01006DA46EC361441FFE1AAF55DA1E690782214AB7BE49806DC6 HASHED, SID = 0x4EAF544D095570419CF280E6C9D103A6, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyEventProcessingLogin##] DISABLE to data type int."
Number=245
Procedure="sp_help_revlogin"
Server="(local)"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at RetrieveUserSID.Module1.Main() in C:Usersjacobsb.NET1FNSBDocumentsVisual Studio 2008ProjectsRetrieveUserSIDRetrieveUserSIDModule1.vb:line 20
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
[/code]
This is the "test" console I am using to test this out:
<pre>Module Module1

Sub Main()
Dim conn As New SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=True")
Dim cmd As New SqlCommand
Dim reader As SqlDataReader

cmd.CommandText = "sp_help_revlogin"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = conn

conn.Open()
Data is accessible through the DataReader object here
*********************************************************
reader = cmd.ExecuteReader()
While reader.Read()
Console.WriteLine(reader.GetString(0))
End While

conn.Close()
End Sub

End Module[/code]
I would appreciate any help possible as I am "weak" in this area of vb.net. We get many new "field tablet" in and we refresh all of our tablets daily with this program already. I am making the enhancements as I said to preclude "orphaned users"
errors that we itermittently receive after a refresh (when users try to run an associated application). Thank you for your responses inadvance.

View the full article

Conversion failed when converting the varchar value when calling stored procedure from vb.net

EDN Admin

Well-known member