Link to home
Start Free TrialLog in
Avatar of usslindstrom
usslindstromFlag for Japan

asked on

VBScript execute SQL stored procedure with variable

Experts,

With help from here, I've just converted some VBScript SQL queries into stored procedures.

https://www.experts-exchange.com/questions/27281469/Converting-VBScript-to-SQL-Stored-Procedure.html

It works great when calling the SP from within the query window.

Would anybody happen to know how I can call that SP from VB and have it return the values back into the script for parsing?

Here is my current code (which is just running a query against the DB from within VB):  *You can see why there was a need to move the logic of this script into SQL.  :)
Const adOpenStatic = 3
        Const adLockOptimistic = 3

        Set objConnection = CreateObject("ADODB.Connection")
        Set objRecordSet = CreateObject("ADODB.Recordset")

        objConnection.Open _
            "Provider=SQLOLEDB;Data Source=" & SQLServer & ";" & _
                "Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"















Sub PrinterMap

        LogSubStart

        objLogFile.WriteLine "Sub-Routine handle:  PrinterMap"
        logLineBreak

            SQLQueryBase = "SELECT DISTINCT * FROM ( "
            SQLQueryUnion = "UNION "
            SQLQueryEnd = ") Printers"

            SQLQueryComputerPrinterName = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_Computer & " WHERE ComputerName = '" & ComputerName & "' "
            SQLQueryRoomNumberPrinterName = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_Room & " WHERE RoomNumber = '" & ComputerRoomNumber & "' "
            SQLQueryGlobalPrinters = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_Global & " "
            SQLQueryAdministrativePrinters = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_AdministrativePrinters & " "
            SQLQueryClassroomPrinters = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_ClassroomPrinters & " "
            SQLQueryICPrinters = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_ICPrinters & " "
            SQLQueryLabPrinters = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_LabPrinters & " "
            SQLQueryNotebookPrinters = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_NotebookPrinters & " "
            SQLQueryNetbookPrinters = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_NetbookPrinters & " "
            SQLQueryTeacherPrinters = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_TeacherPrinters & " "
            SQLQueryUtilityPrinters = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_UtilityPrinters & " "
            SQLQueryVirtualSchoolPrinters = "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber, Protocol, PortNumber, SNMPEnabled, ExtendedAttributes, Queue, DoubleSpool, IsDefault, PrinterSerialNumber, PrinterLocation, Manufacturer, IsColor FROM " & SQLTable_PrinterMap_VirtualSchoolPrinters & " "

                Select Case ComputerType
                    Case "Administrative"
                        SQLQuery = SQLQueryBase & SQLQueryGlobalPrinters & SQLQueryUnion & SQLQueryAdministrativePrinters & SQLQueryUnion & SQLQueryRoomNumberPrinterName & SQLQueryUnion & SQLQueryComputerPrinterName & SQLQueryEnd
                        SQLSelectText = "with 'Administrative' scoped global printers:"
                    Case "Classroom"
                        SQLQuery = SQLQueryBase & SQLQueryGlobalPrinters & SQLQueryUnion & SQLQueryClassroomPrinters & SQLQueryUnion & SQLQueryRoomNumberPrinterName & SQLQueryUnion & SQLQueryComputerPrinterName & SQLQueryEnd
                        SQLSelectText = "with 'Classroom' scoped global printers:"
                    Case "Information Center"
                        SQLQuery = SQLQueryBase & SQLQueryGlobalPrinters & SQLQueryUnion & SQLQueryICPrinters & SQLQueryUnion & SQLQueryRoomNumberPrinterName & SQLQueryUnion & SQLQueryComputerPrinterName & SQLQueryEnd
                        SQLSelectText = "with 'Information Center' scoped global printers:"
                    Case "Lab"
                        SQLQuery = SQLQueryBase & SQLQueryGlobalPrinters & SQLQueryUnion & SQLQueryLabPrinters & SQLQueryUnion & SQLQueryRoomNumberPrinterName & SQLQueryUnion & SQLQueryComputerPrinterName & SQLQueryEnd
                        SQLSelectText = "with 'Lab' scoped global printers:"
                    Case "Notebook"
                        SQLQuery = SQLQueryBase & SQLQueryGlobalPrinters & SQLQueryUnion & SQLQueryNotebookPrinters & SQLQueryUnion & SQLQueryRoomNumberPrinterName & SQLQueryUnion & SQLQueryComputerPrinterName & SQLQueryEnd
                        SQLSelectText = "with 'Notebook' scoped global printers:"
                    Case "Netbook"
                        SQLQuery = SQLQueryBase & SQLQueryGlobalPrinters & SQLQueryUnion & SQLQueryNetbookPrinters & SQLQueryUnion & SQLQueryRoomNumberPrinterName & SQLQueryUnion & SQLQueryComputerPrinterName & SQLQueryEnd
                        SQLSelectText = "with 'Netbook' scoped global printers:"
                    Case "Teacher"
                        SQLQuery = SQLQueryBase & SQLQueryGlobalPrinters & SQLQueryUnion & SQLQueryTeacherPrinters & SQLQueryUnion & SQLQueryRoomNumberPrinterName & SQLQueryUnion & SQLQueryComputerPrinterName & SQLQueryEnd
                        SQLSelectText = "with 'Teacher' scoped global printers:"
                    Case "Utility"
                        SQLQuery = SQLQueryBase & SQLQueryGlobalPrinters & SQLQueryUnion & SQLQueryUtilityPrinters & SQLQueryUnion & SQLQueryRoomNumberPrinterName & SQLQueryUnion & SQLQueryComputerPrinterName & SQLQueryEnd
                        SQLSelectText = "with 'Utility' scoped global printers:"
                    Case "Virtual School"
                        SQLQuery = SQLQueryBase & SQLQueryGlobalPrinters & SQLQueryUnion & SQLQueryVirtualSchoolPrinters & SQLQueryUnion & SQLQueryRoomNumberPrinterName & SQLQueryUnion & SQLQueryComputerPrinterName & SQLQueryEnd
                        SQLSelectText = "with 'Virtual School' scoped global printers:"
                    Case Else
                        SQLQuery = SQLQueryBase & SQLQueryGlobalPrinters & SQLQueryRoomNumberPrinterName & SQLQueryUnion & SQLQueryComputerPrinterName & SQLQueryEnd
                        SQLSelectText = "without global Sub-Scoped printers:"
                End Select

            objLogFile.WriteLine "Processing the following SQL Query " & SQLSelectText
            objLogFile.WriteLine "  " & SQLQuery

            objRecordSet.Open SQLQuery, _
                objConnection, adOpenStatic, adLockOptimistic

        If Not objRecordSet.EOF then

            objRecordSet.MoveFirst
            Do While Not objRecordSet.EOF			
			
            strPrinterName = objRecordSet.Fields("PrinterName").Value
            strDriverFileName = objRecordSet.Fields("DriverFileName").Value
            strDriverTextString = objRecordSet.Fields("DriverTextString").Value
            strModelNumber = objRecordSet.Fields("ModelNumber").Value

                    If strDriverFileName = "ntprint.inf" Then
                        strDriverLocation = "%windir%\inf\ntprint.inf"
                    Else
                        strDriverLocation = DriverLocation & "\" & strModelNumber & "\" & strDriverFileName
                    End If

                strProtocol = objRecordSet.Fields("Protocol").Value
                strPortNumber = objRecordSet.Fields("PortNumber").Value
                strSNMPEnabled = objRecordSet.Fields("SNMPEnabled").Value
                strExtendedAttributes = objRecordSet.Fields("ExtendedAttributes").Value
                strQueue = objRecordSet.Fields("Queue").Value
                strDoubleSpool = objRecordSet.Fields("DoubleSpool").Value

            strIsDefault = objRecordSet.Fields("IsDefault").Value
            strPrinterSerialNumber = objRecordSet.Fields("PrinterSerialNumber").Value
            strPrinterLocation = objRecordSet.Fields("PrinterLocation").Value
            strPrinterManufacturer = objRecordSet.Fields("Manufacturer").Value

            strIsColor = objRecordSet.Fields("IsColor").Value

                Select Case strIsColor
                    Case "1"
                        ColorText = " (Color)"
                    Case "0"
                        ColorText = " (B/W)"
                    Case Else
                        ColorText = ""
                End Select

                strComment = strPrinterManufacturer & " " & strModelNumber & ColorText & "  S/N: " & strPrinterSerialNumber
                strLocation = "Room " & strPrinterLocation

            strRegistryPrinterPort = RegistryPrinterPort & "\" & strPrinterName & "\"
            strRegistryPrinterQueue = RegistryPrinterQueue & "\" & strPrinterName & "\"
            strRegistryPrinterDriver = RegistryPrinterDriver & "\" & strPrinterName & "\"

            objLogFile.WriteLine "  Received Query result for printer " & strPrinterName & "."
            objLogFile.WriteLine "    Driver location set to:  "  & "'" & strDriverLocation & "'"
            objLogFile.WriteLine "    Driver listed in inf as:  " & "'" & strDriverTextString & "'"
            objLogFile.WriteLine "    Identified printer model:  " & "'" & strModelNumber & "'"

            objLogFile.WriteLine "    Targeted registry keys:"
            objLogFile.WriteLine "        " & strRegistryPrinterPort
            objLogFile.WriteLine "        " & strRegistryPrinterQueue
            objLogFile.WriteLine "        " & strRegistryPrinterDriver

                InstallPrinterCommand = BaseCommandString & " /u /b " & """" & strPrinterName & """" & " /n " & """" & strPrinterName & """" & " /m " & """" & strDriverTextString & """" & " /if /f " & """" & strDriverLocation & """" & " /r " & """" & strPrinterName
                SetPrinterInformation = BaseCommandString & " /Xs /n " & """" & strPrinterName & """" & " Comment " & """" & strComment & """" & " Location " & """" & strLocation & """"
                SetDefaultPrinter = BaseCommandString & " /y /n " & """" & strPrinterName

                objLogFile.WriteLine "    Installing printer..."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Creating printer port..."
                objLogFile.WriteLine "            Port information:"
                objLogFile.WriteLine "                Protocol version:  " & strProtocol
                objLogFile.WriteLine "                Port number:  " & strPortNumber
                objLogFile.WriteLine "                SNMP enabled:  " & strSNMPEnabled
                objLogFile.WriteLine "                Extended attributes:  " &  strExtendedAttributes
                objLogFile.WriteLine "                Queue:  " & strQueue
                objLogFile.WriteLine "                Double spool:  " & strDoubleSpool
                    wshShell.RegWrite strRegistryPrinterPort & "Protocol", strProtocol, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "Version", 1, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "HostName", strPrinterName, "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "IPAddress", "", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "HWAddress", "", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "PortNumber", strPortNumber, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Community", "public", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Enabled", strSNMPEnabled, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Index", 1, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "CreationDateTime", Now(), "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "CreationSource", ScriptTitle, "REG_SZ"
                        If strExtendedAttributes = "1" Then
                            wshShell.RegWrite strRegistryPrinterPort & "Queue", strQueue, "REG_SZ"
                            wshShell.RegWrite strRegistryPrinterPort & "Double Spool", strDoubleSpool, "REG_DWORD"
                        Else
                        End If
                objLogFile.WriteLine "            Printer port created."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Creating printer map..."

                        objLogFile.WriteLine "            Executing the following command:  " & """" & InstallPrinterCommand & """"

                    wshShell.Run InstallPrinterCommand,0,True

                objLogFile.WriteLine "            Printer map created."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Setting printer information..."

                        objLogFile.WriteLine "            Executing the following command:  " & """" & SetPrinterInformation & """"

                    wshShell.Run SetPrinterInformation,0,True

                objLogFile.WriteLine "            Printer information set."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "            Processing duplex/stapling settings (if applicable)..."

                    If strModelNumber = "DocuCentre 450 I" Or strModelNumber = "ApeosPort 550 I" Then
                        objLogFile.WriteLine "                Processing the following registry change:  " & strRegistryPrinterDriver & "DsDriver\printStaplingSupported"
                        wshShell.RegWrite strRegistryPrinterDriver & "DsDriver\printStaplingSupported", 01, "REG_BINARY"
                        objLogFile.WriteLine "                Processing the following registry change:  " & strRegistryPrinterDriver & "DsDriver\printDuplexSupported"
                        wshShell.RegWrite strRegistryPrinterDriver & "DsDriver\printDuplexSupported", 01, "REG_BINARY"
                        objLogFile.WriteLine "                    Printer set with duplex/stapling features."
                    Else
                        objLogFile.WriteLine "                Printer not default with duplex/stapling features."
                    End If
                ' ---------------------------------------------------------------------
                If strIsDefault = "1" Then
                    objLogFile.WriteLine "            Printer is flagged as 'Default Printer' on this unit."
                        objLogFile.WriteLine "            Executing the following command:  " & """" & SetDefaultPrinter & """"

                    wshShell.Run SetDefaultPrinter,0,True

                        objLogFile.WriteLine "            Printer set as default."

                Else
                    objLogFile.WriteLine "            Printer is not marked as 'Default Printer' for this unit."
                End If
                ' ---------------------------------------------------------------------

        objRecordSet.MoveNext

        Loop

        Else

            objLogFile.WriteLine "  The SQL Query did not return any matches.  No printers were installed."

        End If

        objRecordSet.Close

        LogLineBreak
        objLogFile.WriteLine "Printers installed."

        LogSubEnd

  End Sub

Open in new window

Avatar of andrewssd3
andrewssd3
Flag of United Kingdom of Great Britain and Northern Ireland image

As usual with ADODB there are several variants on this, but this snippet is adapted from some VBA code that does this.  The return is in the form of a recordset as with a standard SQL command.  This code returned multiple recordsets, but the principle should be clear (I hope).

    sSQL = "storedprocname"
    ' Set the command text.
    Set cmdCommand = CreateObject("ADODB.Command")
    Set cmdCommand.ActiveConnection = oConnection
    With cmdCommand
        .CommandText = sSQL
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("parmname", adDate, adParamInput, , Format(ValueDate, "dd/mmm/yyyy"))
        .Execute
    End With
    
    ' Open the recordsets- we know that two RSs are returned.....
    Set oRsDayResults = CreateObject("ADODB.Recordset")
    Call oRsDayResults.Open(cmdCommand)
    ' Open the recordsets- we know that two RSs are returned.....
    Set oRsDayResults = New Recordset
    Call oRsDayResults.Open(cmdCommand)
    Set oRsPrevDayResults = oRsDayResults.NextRecordset
    
    'enter values to worksheet. Today is not in database so use T-1
    If oRsDayResults.BOF = True And oRsDayResults.EOF = True Then

Open in new window

Avatar of Mlanda T
Try something like this:
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adVarChar = 200
Const adParamInput = 1

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objCommand = CreateObject("ADODB.Command")

objConnection.Open _
    "Provider=SQLOLEDB;Data Source=" & SQLServer & ";" & _
	"Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"

Set objCommand.ActiveConnection = objConnection

objCommand.CommandText = "usp_YourStoredProcedureNameGoesHere"
objCommand.CommandType = 4
objCommand.NamedParameters = True

'create parameter adn assign values
Set objParam = objCommand.CreateParameter("@ComputerName", adVarChar, adParamInput)
objCommand.Parameters.Append objParam
objParam.Value = "THE_COMUTER_NAME"

'execute command
objRecordSet = objCommand.Execute();

Open in new window

here is an exeample:

DIM MyResult
Set objSQLCon = CreateObject("ADODB.Connection")
objSQLCon.Open "Provider=SQLOLEDB.1;Persist Security Info=False; Integrated Security=SSPI; Data Source=.;Initial Catalog=master"

MyResult = objSQLCon.Execute ("sp_helpdb")

for each result in MyResult
      WScript.Echo result
Next
If you can post your Stored Procedure it should be evident not only what parameters need to be passed, but also confirm that there is only one resultset returned (no need for the NextRecordset method).  We can tehm tailor our solution to your requirements, rather than you have to guess.

I should also point out that doing DISTINCT with a UNION is redundant:  A UNION statement implies DISTINCT.
Avatar of usslindstrom

ASKER

:)  ACPerkins - it was the stored procedure that you just helped me make.  :)

https://www.experts-exchange.com/questions/27281469/Converting-VBScript-to-SQL-Stored-Procedure.html

I'm going to try all of these out here shortly and see if I can't tailor them to fit our needs.

I was under the impression that I would still need the DISTINCT with the UNION as what I'm trying to accomplish is install printers on client machines.  A certain printer may be assigned to a computer multiple ways (as you can see by all the query statements I have, and I didn't want the clients to have to reinstall the same printer over and over again, should for some reason the printer be assigned at two different levels (if that makes sense).

I'll keep you guys posted on my testing.
>>I was under the impression that I would still need the DISTINCT with the UNION as what I'm trying to accomplish is install printers on client machines. <<
You don't.  But if your not comfortable removing it, leave it.
Sorry for my lack of understanding here guys...  But could somebody nudge me in the right direction?

On the portion of executing the SP's - I've got the following so far...

My question is this:

For each computer type, it will basically be running three of the different stored procedures.  How would I programattically run them all, without having to duplicate everything under the "If Not objRecordSet.EOF then" for each case statement?


Select Case ComputerType

                    Case "Administrative"
                        objLogFile.WriteLine "Processing global 'Administrative' group scoped printers..."
                        objCommand.CommandText = "SP_AdminPrinterName"
                    Case "Classroom"
                        objLogFile.WriteLine "Processing global 'Classroom' group scoped printers..."
                        objCommand.CommandText = "SP_ClassroomPrinterName"
                    Case "Information Center"
                        objLogFile.WriteLine "Processing global 'Information Center' group scoped printers..."
                        objCommand.CommandText = "SP_ICPrinterName"
                    Case "Lab"
                        objLogFile.WriteLine "Processing global 'Lab' group scoped printers..."
                        objCommand.CommandText = "SP_LabPrinterName"
                    Case "Notebook"
                        objLogFile.WriteLine "Processing global 'Notebook' group scoped printers..."
                        objCommand.CommandText = "SP_NotebookPrinterName"
                    Case "Netbook"
                        objLogFile.WriteLine "Processing global 'Netbook' group scoped printers..."
                        objCommand.CommandText = "SP_NetbookPrinterName"
                    Case "Teacher"
                        objLogFile.WriteLine "Processing global 'Teacher' group scoped printers..."
                        objCommand.CommandText = "SP_TeacherPrinterName"
                    Case "Utility"
                        objLogFile.WriteLine "Processing global 'Utility' group scoped printers..."
                        objCommand.CommandText = "SP_UtilityPrinterName"
                    Case "Virtual School"
                        objLogFile.WriteLine "Processing global 'Virtual School' group scoped printers..."
                        objCommand.CommandText = "SP_VirtualSchoolPrinterName"
                    Case Else
                        objLogFile.WriteLine "Processing without global group scoped printers..."
                        objCommand.CommandText = ""
                End Select

        If Not objRecordSet.EOF then

            objRecordSet.MoveFirst
            Do While Not objRecordSet.EOF			
			
            strPrinterName = objRecordSet.Fields("PrinterName").Value
            strDriverFileName = objRecordSet.Fields("DriverFileName").Value
            strDriverTextString = objRecordSet.Fields("DriverTextString").Value
            strModelNumber = objRecordSet.Fields("ModelNumber").Value

                    If strDriverFileName = "ntprint.inf" Then
                        strDriverLocation = "%windir%\inf\ntprint.inf"
                    Else
                        strDriverLocation = DriverLocation & "\" & strModelNumber & "\" & strDriverFileName
                    End If

                strProtocol = objRecordSet.Fields("Protocol").Value
                strPortNumber = objRecordSet.Fields("PortNumber").Value
                strSNMPEnabled = objRecordSet.Fields("SNMPEnabled").Value
                strExtendedAttributes = objRecordSet.Fields("ExtendedAttributes").Value
                strQueue = objRecordSet.Fields("Queue").Value
                strDoubleSpool = objRecordSet.Fields("DoubleSpool").Value

            strIsDefault = objRecordSet.Fields("IsDefault").Value
            strPrinterSerialNumber = objRecordSet.Fields("PrinterSerialNumber").Value
            strPrinterLocation = objRecordSet.Fields("PrinterLocation").Value
            strPrinterManufacturer = objRecordSet.Fields("Manufacturer").Value

            strIsColor = objRecordSet.Fields("IsColor").Value

                Select Case strIsColor
                    Case "1"
                        ColorText = " (Color)"
                    Case "0"
                        ColorText = " (B/W)"
                    Case Else
                        ColorText = ""
                End Select

                strComment = strPrinterManufacturer & " " & strModelNumber & ColorText & "  S/N: " & strPrinterSerialNumber
                strLocation = "Room " & strPrinterLocation

            strRegistryPrinterPort = RegistryPrinterPort & "\" & strPrinterName & "\"
            strRegistryPrinterQueue = RegistryPrinterQueue & "\" & strPrinterName & "\"
            strRegistryPrinterDriver = RegistryPrinterDriver & "\" & strPrinterName & "\"

            objLogFile.WriteLine "  Received Query result for printer " & strPrinterName & "."
            objLogFile.WriteLine "    Driver location set to:  "  & "'" & strDriverLocation & "'"
            objLogFile.WriteLine "    Driver listed in inf as:  " & "'" & strDriverTextString & "'"
            objLogFile.WriteLine "    Identified printer model:  " & "'" & strModelNumber & "'"

            objLogFile.WriteLine "    Targeted registry keys:"
            objLogFile.WriteLine "        " & strRegistryPrinterPort
            objLogFile.WriteLine "        " & strRegistryPrinterQueue
            objLogFile.WriteLine "        " & strRegistryPrinterDriver

                InstallPrinterCommand = BaseCommandString & " /u /b " & """" & strPrinterName & """" & " /n " & """" & strPrinterName & """" & " /m " & """" & strDriverTextString & """" & " /if /f " & """" & strDriverLocation & """" & " /r " & """" & strPrinterName
                SetPrinterInformation = BaseCommandString & " /Xs /n " & """" & strPrinterName & """" & " Comment " & """" & strComment & """" & " Location " & """" & strLocation & """"
                SetDefaultPrinter = BaseCommandString & " /y /n " & """" & strPrinterName

                objLogFile.WriteLine "    Installing printer..."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Creating printer port..."
                objLogFile.WriteLine "            Port information:"
                objLogFile.WriteLine "                Protocol version:  " & strProtocol
                objLogFile.WriteLine "                Port number:  " & strPortNumber
                objLogFile.WriteLine "                SNMP enabled:  " & strSNMPEnabled
                objLogFile.WriteLine "                Extended attributes:  " &  strExtendedAttributes
                objLogFile.WriteLine "                Queue:  " & strQueue
                objLogFile.WriteLine "                Double spool:  " & strDoubleSpool
                    wshShell.RegWrite strRegistryPrinterPort & "Protocol", strProtocol, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "Version", 1, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "HostName", strPrinterName, "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "IPAddress", "", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "HWAddress", "", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "PortNumber", strPortNumber, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Community", "public", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Enabled", strSNMPEnabled, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Index", 1, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "CreationDateTime", Now(), "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "CreationSource", ScriptTitle, "REG_SZ"
                        If strExtendedAttributes = "1" Then
                            wshShell.RegWrite strRegistryPrinterPort & "Queue", strQueue, "REG_SZ"
                            wshShell.RegWrite strRegistryPrinterPort & "Double Spool", strDoubleSpool, "REG_DWORD"
                        Else
                        End If
                objLogFile.WriteLine "            Printer port created."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Creating printer map..."

                        objLogFile.WriteLine "            Executing the following command:  " & """" & InstallPrinterCommand & """"

                    wshShell.Run InstallPrinterCommand,0,True

                objLogFile.WriteLine "            Printer map created."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Setting printer information..."

                        objLogFile.WriteLine "            Executing the following command:  " & """" & SetPrinterInformation & """"

                    wshShell.Run SetPrinterInformation,0,True

                objLogFile.WriteLine "            Printer information set."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "            Processing duplex/stapling settings (if applicable)..."

                    If strModelNumber = "DocuCentre 450 I" Or strModelNumber = "ApeosPort 550 I" Then
                        objLogFile.WriteLine "                Processing the following registry change:  " & strRegistryPrinterDriver & "DsDriver\printStaplingSupported"
                        wshShell.RegWrite strRegistryPrinterDriver & "DsDriver\printStaplingSupported", 01, "REG_BINARY"
                        objLogFile.WriteLine "                Processing the following registry change:  " & strRegistryPrinterDriver & "DsDriver\printDuplexSupported"
                        wshShell.RegWrite strRegistryPrinterDriver & "DsDriver\printDuplexSupported", 01, "REG_BINARY"
                        objLogFile.WriteLine "                    Printer set with duplex/stapling features."
                    Else
                        objLogFile.WriteLine "                Printer not default with duplex/stapling features."
                    End If
                ' ---------------------------------------------------------------------
                If strIsDefault = "1" Then
                    objLogFile.WriteLine "            Printer is flagged as 'Default Printer' on this unit."
                        objLogFile.WriteLine "            Executing the following command:  " & """" & SetDefaultPrinter & """"

                    wshShell.Run SetDefaultPrinter,0,True

                        objLogFile.WriteLine "            Printer set as default."

                Else
                    objLogFile.WriteLine "            Printer is not marked as 'Default Printer' for this unit."
                End If
                ' ---------------------------------------------------------------------

        objRecordSet.MoveNext

        Loop

        Else

            objLogFile.WriteLine "  The SQL Query did not return any matches.  No printers were installed."

        End If

        objRecordSet.Close

        LogLineBreak
        objLogFile.WriteLine "Printers installed."

        LogSubEnd

  End Sub

Open in new window

Why not combine them all into a single Stored Procedure?
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ACPerkins, you truly are a genious.  When I read your last post, it hit me like a breath of fresh air.  Of course!  Use a single Stored Procedure per type of computer.  It takes the complexity out of having to figure out multiple stored procedures, temp tables, etc.



Alright, so here's where I'm at.  *Code pasted below.  The stored procedure is run without any errors (from what I can tell) - but I'm not returning any records from the script.  (It's probably the "If Not objRecordSet.EOF then" command).

But would anybody know what I'm doing wrong here?


'SQL Connection
        LogSubStart

        objLogFile.WriteLine "Connecting to SQL Server Instance:  " & SQLServer
        Const adOpenStatic = 3
        Const adLockOptimistic = 3
        Const adVarChar = 200
        Const adParamInput = 1

        Set objConnection = CreateObject("ADODB.Connection")
        Set objRecordSet = CreateObject("ADODB.Recordset")
        Set objCommand = CreateObject("ADODB.Command")

        objConnection.Open _
            "Provider=SQLOLEDB;Data Source=" & SQLServer & ";" & _
                "Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"

        Set objCommand.ActiveConnection = objConnection

            objCommand.CommandType = 4
            objCommand.NamedParameters = True

        LogSubEnd










Sub PrinterMap

        LogSubStart

        objLogFile.WriteLine "Sub-Routine handle:  PrinterMap"
        logLineBreak

                Select Case ComputerType

                    Case "Administrative"
                        objLogFile.WriteLine "Processing global 'Administrative' group scoped printers..."
                        objCommand.CommandText = "SP_PrinterMap_Admin"
                    Case "Classroom"
                        objLogFile.WriteLine "Processing global 'Classroom' group scoped printers..."
                        objCommand.CommandText = "SP_PrinterMap_Classroom"
                    Case "Information Center"
                        objLogFile.WriteLine "Processing global 'Information Center' group scoped printers..."
                        objCommand.CommandText = "SP_PrinterMap_IC"
                    Case "Lab"
                        objLogFile.WriteLine "Processing global 'Lab' group scoped printers..."
                        objCommand.CommandText = "SP_PrinterMap_Lab"
                    Case "Notebook"
                        objLogFile.WriteLine "Processing global 'Notebook' group scoped printers..."
                        objCommand.CommandText = "SP_PrinterMap_Notebook"
                    Case "Netbook"
                        objLogFile.WriteLine "Processing global 'Netbook' group scoped printers..."
                        objCommand.CommandText = "SP_PrinterMap_Netbook"
                    Case "Teacher"
                        objLogFile.WriteLine "Processing global 'Teacher' group scoped printers..."
                        objCommand.CommandText = "SP_PrinterMap_Teacher"
                    Case "Utility"
                        objLogFile.WriteLine "Processing global 'Utility' group scoped printers..."
                        objCommand.CommandText = "SP_PrinterMap_Utility"
                    Case "Virtual School"
                        objLogFile.WriteLine "Processing global 'Virtual School' group scoped printers..."
                        objCommand.CommandText = "SP_PrinterMap_VirtualSchool"
                    Case Else
                        objLogFile.WriteLine "Processing without global group scoped printers..."
                        objCommand.CommandText = "SP_PrinterMap_Global"
                End Select

            Set objComputerRoomNumberParam = objCommand.CreateParameter("@ComputerRoomNumberName", adVarChar, adParamInput)
            Set objComputerNameParam = objCommand.CreateParameter("@ComputerName", adVarChar, adParamInput)

                objCommand.Parameters.Append objComputerRoomNumberParam, objComputerNameParam

            objComputerRoomNumberParam.Value = ComputerRoomNumber
            objComputerNameParam.Value = ComputerName

                objRecordSet = objCommand.Execute()

        If Not objRecordSet.EOF then

            objRecordSet.MoveFirst
            Do While Not objRecordSet.EOF			
			
            strPrinterName = objRecordSet.Fields("PrinterName").Value
            strDriverFileName = objRecordSet.Fields("DriverFileName").Value
            strDriverTextString = objRecordSet.Fields("DriverTextString").Value
            strModelNumber = objRecordSet.Fields("ModelNumber").Value

                    If strDriverFileName = "ntprint.inf" Then
                        strDriverLocation = "%windir%\inf\ntprint.inf"
                    Else
                        strDriverLocation = DriverLocation & "\" & strModelNumber & "\" & strDriverFileName
                    End If

                strProtocol = objRecordSet.Fields("Protocol").Value
                strPortNumber = objRecordSet.Fields("PortNumber").Value
                strSNMPEnabled = objRecordSet.Fields("SNMPEnabled").Value
                strExtendedAttributes = objRecordSet.Fields("ExtendedAttributes").Value
                strQueue = objRecordSet.Fields("Queue").Value
                strDoubleSpool = objRecordSet.Fields("DoubleSpool").Value

            strIsDefault = objRecordSet.Fields("IsDefault").Value
            strPrinterSerialNumber = objRecordSet.Fields("PrinterSerialNumber").Value
            strPrinterLocation = objRecordSet.Fields("PrinterLocation").Value
            strPrinterManufacturer = objRecordSet.Fields("Manufacturer").Value

            strIsColor = objRecordSet.Fields("IsColor").Value

                Select Case strIsColor
                    Case "1"
                        ColorText = " (Color)"
                    Case "0"
                        ColorText = " (B/W)"
                    Case Else
                        ColorText = ""
                End Select

                strComment = strPrinterManufacturer & " " & strModelNumber & ColorText & "  S/N: " & strPrinterSerialNumber
                strLocation = "Room " & strPrinterLocation

            strRegistryPrinterPort = RegistryPrinterPort & "\" & strPrinterName & "\"
            strRegistryPrinterQueue = RegistryPrinterQueue & "\" & strPrinterName & "\"
            strRegistryPrinterDriver = RegistryPrinterDriver & "\" & strPrinterName & "\"

            objLogFile.WriteLine "  Received Query result for printer " & strPrinterName & "."
            objLogFile.WriteLine "    Driver location set to:  "  & "'" & strDriverLocation & "'"
            objLogFile.WriteLine "    Driver listed in inf as:  " & "'" & strDriverTextString & "'"
            objLogFile.WriteLine "    Identified printer model:  " & "'" & strModelNumber & "'"

            objLogFile.WriteLine "    Targeted registry keys:"
            objLogFile.WriteLine "        " & strRegistryPrinterPort
            objLogFile.WriteLine "        " & strRegistryPrinterQueue
            objLogFile.WriteLine "        " & strRegistryPrinterDriver

                InstallPrinterCommand = BaseCommandString & " /u /b " & """" & strPrinterName & """" & " /n " & """" & strPrinterName & """" & " /m " & """" & strDriverTextString & """" & " /if /f " & """" & strDriverLocation & """" & " /r " & """" & strPrinterName
                SetPrinterInformation = BaseCommandString & " /Xs /n " & """" & strPrinterName & """" & " Comment " & """" & strComment & """" & " Location " & """" & strLocation & """"
                SetDefaultPrinter = BaseCommandString & " /y /n " & """" & strPrinterName

                objLogFile.WriteLine "    Installing printer..."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Creating printer port..."
                objLogFile.WriteLine "            Port information:"
                objLogFile.WriteLine "                Protocol version:  " & strProtocol
                objLogFile.WriteLine "                Port number:  " & strPortNumber
                objLogFile.WriteLine "                SNMP enabled:  " & strSNMPEnabled
                objLogFile.WriteLine "                Extended attributes:  " &  strExtendedAttributes
                objLogFile.WriteLine "                Queue:  " & strQueue
                objLogFile.WriteLine "                Double spool:  " & strDoubleSpool
                    wshShell.RegWrite strRegistryPrinterPort & "Protocol", strProtocol, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "Version", 1, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "HostName", strPrinterName, "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "IPAddress", "", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "HWAddress", "", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "PortNumber", strPortNumber, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Community", "public", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Enabled", strSNMPEnabled, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Index", 1, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "CreationDateTime", Now(), "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "CreationSource", ScriptTitle, "REG_SZ"
                        If strExtendedAttributes = "1" Then
                            wshShell.RegWrite strRegistryPrinterPort & "Queue", strQueue, "REG_SZ"
                            wshShell.RegWrite strRegistryPrinterPort & "Double Spool", strDoubleSpool, "REG_DWORD"
                        Else
                        End If
                objLogFile.WriteLine "            Printer port created."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Creating printer map..."

                        objLogFile.WriteLine "            Executing the following command:  " & """" & InstallPrinterCommand & """"

                    wshShell.Run InstallPrinterCommand,0,True

                objLogFile.WriteLine "            Printer map created."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Setting printer information..."

                        objLogFile.WriteLine "            Executing the following command:  " & """" & SetPrinterInformation & """"

                    wshShell.Run SetPrinterInformation,0,True

                objLogFile.WriteLine "            Printer information set."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "            Processing duplex/stapling settings (if applicable)..."

                    If strModelNumber = "DocuCentre 450 I" Or strModelNumber = "ApeosPort 550 I" Then
                        objLogFile.WriteLine "                Processing the following registry change:  " & strRegistryPrinterDriver & "DsDriver\printStaplingSupported"
                        wshShell.RegWrite strRegistryPrinterDriver & "DsDriver\printStaplingSupported", 01, "REG_BINARY"
                        objLogFile.WriteLine "                Processing the following registry change:  " & strRegistryPrinterDriver & "DsDriver\printDuplexSupported"
                        wshShell.RegWrite strRegistryPrinterDriver & "DsDriver\printDuplexSupported", 01, "REG_BINARY"
                        objLogFile.WriteLine "                    Printer set with duplex/stapling features."
                    Else
                        objLogFile.WriteLine "                Printer not default with duplex/stapling features."
                    End If
                ' ---------------------------------------------------------------------
                If strIsDefault = "1" Then
                    objLogFile.WriteLine "            Printer is flagged as 'Default Printer' on this unit."
                        objLogFile.WriteLine "            Executing the following command:  " & """" & SetDefaultPrinter & """"

                    wshShell.Run SetDefaultPrinter,0,True

                        objLogFile.WriteLine "            Printer set as default."

                Else
                    objLogFile.WriteLine "            Printer is not marked as 'Default Printer' for this unit."
                End If
                ' ---------------------------------------------------------------------

        objRecordSet.MoveNext

        Loop

        Else

            objLogFile.WriteLine "  The SQL Query did not return any matches.  No printers were installed."

        End If

        objRecordSet.Close

        LogLineBreak
        objLogFile.WriteLine "Printers installed."

        LogSubEnd

  End Sub

Open in new window

I saw one error already "objCommand.Text" and have fixed it, but it still doesn't return any results.
ACPerkins,  My apologies - it took me a while to edit my post, and didn't see yoru comment 36457554.

If you wouldn't mind, could you assist me in adapting your version of code to the vbscript I have posted?

It's a bit above me what's happening in your version.
All you have to do is change the connection string and add your Recordset code at the end.
First, I need to thank you expecially ACPerkins, as well as everyone else that's been assisting me, but I need to apologize, in that I'm missing something fundamental here.

Here's the current version of code.  It's not returning any lines of data.
'SQL Connection
        LogSubStart

        objLogFile.WriteLine "Connecting to SQL Server Instance:  " & SQLServer

        Const adCmdStoredProc = 4, adVarChar = 200, adParamInput = 1

        Dim cn, cmd, rs

            Set cn = CreateObject("ADODB.Connection")
            Set cmd = CreateObject("ADODB.Command")

                cn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & SQLServer & ";" & _
                                      "Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"

            cn.CursorLocation = adUseClient
            cn.Open

        LogSubEnd








Sub PrinterMap

        LogSubStart

        objLogFile.WriteLine "Sub-Routine handle:  PrinterMap"
        logLineBreak

                Select Case ComputerType

                    Case "Administrative"
                        objLogFile.WriteLine "Processing global 'Administrative' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Admin"
                    Case "Classroom"
                        objLogFile.WriteLine "Processing global 'Classroom' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Classroom"
                    Case "Information Center"
                        objLogFile.WriteLine "Processing global 'Information Center' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_IC"
                    Case "Lab"
                        objLogFile.WriteLine "Processing global 'Lab' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Lab"
                    Case "Notebook"
                        objLogFile.WriteLine "Processing global 'Notebook' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Notebook"
                    Case "Netbook"
                        objLogFile.WriteLine "Processing global 'Netbook' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Netbook"
                    Case "Teacher"
                        objLogFile.WriteLine "Processing global 'Teacher' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Teacher"
                    Case "Utility"
                        objLogFile.WriteLine "Processing global 'Utility' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Utility"
                    Case "Virtual School"
                        objLogFile.WriteLine "Processing global 'Virtual School' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_VirtualSchool"
                    Case Else
                        objLogFile.WriteLine "Processing without global group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Global"
                End Select

            With cmd
                Set .ActiveConnection = cn
                    .CommandText = StoredProcedue
                    .CommandType = adCmdStoredProc
                    .Parameters.Append .CreateParameter("@ComputerRoomNumber", adVarChar, adParamInput, 100, ComputerRoomNumber)
                    .Parameters.Append .CreateParameter("@ComputerName", adVarChar, adParamInput, 100, ComputerName)

                    Set rs = .Execute
            End With

            Set cmd = Nothing

        If Not rs.EOF then

            rs.MoveFirst
            Do While Not rs.EOF			
			
            strPrinterName = rs.Fields("PrinterName").Value
            strDriverFileName = rs.Fields("DriverFileName").Value
            strDriverTextString = rs.Fields("DriverTextString").Value
            strModelNumber = rs.Fields("ModelNumber").Value

                    If strDriverFileName = "ntprint.inf" Then
                        strDriverLocation = "%windir%\inf\ntprint.inf"
                    Else
                        strDriverLocation = DriverLocation & "\" & strModelNumber & "\" & strDriverFileName
                    End If

                strProtocol = rs.Fields("Protocol").Value
                strPortNumber = rs.Fields("PortNumber").Value
                strSNMPEnabled = rs.Fields("SNMPEnabled").Value
                strExtendedAttributes = rs.Fields("ExtendedAttributes").Value
                strQueue = rs.Fields("Queue").Value
                strDoubleSpool = rs.Fields("DoubleSpool").Value

            strIsDefault = rs.Fields("IsDefault").Value
            strPrinterSerialNumber = rs.Fields("PrinterSerialNumber").Value
            strPrinterLocation = rs.Fields("PrinterLocation").Value
            strPrinterManufacturer = rs.Fields("Manufacturer").Value

            strIsColor = rs.Fields("IsColor").Value

                Select Case strIsColor
                    Case "1"
                        ColorText = " (Color)"
                    Case "0"
                        ColorText = " (B/W)"
                    Case Else
                        ColorText = ""
                End Select

                strComment = strPrinterManufacturer & " " & strModelNumber & ColorText & "  S/N: " & strPrinterSerialNumber
                strLocation = "Room " & strPrinterLocation

            strRegistryPrinterPort = RegistryPrinterPort & "\" & strPrinterName & "\"
            strRegistryPrinterQueue = RegistryPrinterQueue & "\" & strPrinterName & "\"
            strRegistryPrinterDriver = RegistryPrinterDriver & "\" & strPrinterName & "\"

            objLogFile.WriteLine "  Received Query result for printer " & strPrinterName & "."
            objLogFile.WriteLine "    Driver location set to:  "  & "'" & strDriverLocation & "'"
            objLogFile.WriteLine "    Driver listed in inf as:  " & "'" & strDriverTextString & "'"
            objLogFile.WriteLine "    Identified printer model:  " & "'" & strModelNumber & "'"

            objLogFile.WriteLine "    Targeted registry keys:"
            objLogFile.WriteLine "        " & strRegistryPrinterPort
            objLogFile.WriteLine "        " & strRegistryPrinterQueue
            objLogFile.WriteLine "        " & strRegistryPrinterDriver

                InstallPrinterCommand = BaseCommandString & " /u /b " & """" & strPrinterName & """" & " /n " & """" & strPrinterName & """" & " /m " & """" & strDriverTextString & """" & " /if /f " & """" & strDriverLocation & """" & " /r " & """" & strPrinterName
                SetPrinterInformation = BaseCommandString & " /Xs /n " & """" & strPrinterName & """" & " Comment " & """" & strComment & """" & " Location " & """" & strLocation & """"
                SetDefaultPrinter = BaseCommandString & " /y /n " & """" & strPrinterName

                objLogFile.WriteLine "    Installing printer..."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Creating printer port..."
                objLogFile.WriteLine "            Port information:"
                objLogFile.WriteLine "                Protocol version:  " & strProtocol
                objLogFile.WriteLine "                Port number:  " & strPortNumber
                objLogFile.WriteLine "                SNMP enabled:  " & strSNMPEnabled
                objLogFile.WriteLine "                Extended attributes:  " &  strExtendedAttributes
                objLogFile.WriteLine "                Queue:  " & strQueue
                objLogFile.WriteLine "                Double spool:  " & strDoubleSpool
                    wshShell.RegWrite strRegistryPrinterPort & "Protocol", strProtocol, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "Version", 1, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "HostName", strPrinterName, "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "IPAddress", "", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "HWAddress", "", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "PortNumber", strPortNumber, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Community", "public", "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Enabled", strSNMPEnabled, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "SNMP Index", 1, "REG_DWORD"
                    wshShell.RegWrite strRegistryPrinterPort & "CreationDateTime", Now(), "REG_SZ"
                    wshShell.RegWrite strRegistryPrinterPort & "CreationSource", ScriptTitle, "REG_SZ"
                        If strExtendedAttributes = "1" Then
                            wshShell.RegWrite strRegistryPrinterPort & "Queue", strQueue, "REG_SZ"
                            wshShell.RegWrite strRegistryPrinterPort & "Double Spool", strDoubleSpool, "REG_DWORD"
                        Else
                        End If
                objLogFile.WriteLine "            Printer port created."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Creating printer map..."

                        objLogFile.WriteLine "            Executing the following command:  " & """" & InstallPrinterCommand & """"

                    wshShell.Run InstallPrinterCommand,0,True

                objLogFile.WriteLine "            Printer map created."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "        Setting printer information..."

                        objLogFile.WriteLine "            Executing the following command:  " & """" & SetPrinterInformation & """"

                    wshShell.Run SetPrinterInformation,0,True

                objLogFile.WriteLine "            Printer information set."
                ' ---------------------------------------------------------------------
                objLogFile.WriteLine "            Processing duplex/stapling settings (if applicable)..."

                    If strModelNumber = "DocuCentre 450 I" Or strModelNumber = "ApeosPort 550 I" Then
                        objLogFile.WriteLine "                Processing the following registry change:  " & strRegistryPrinterDriver & "DsDriver\printStaplingSupported"
                        wshShell.RegWrite strRegistryPrinterDriver & "DsDriver\printStaplingSupported", 01, "REG_BINARY"
                        objLogFile.WriteLine "                Processing the following registry change:  " & strRegistryPrinterDriver & "DsDriver\printDuplexSupported"
                        wshShell.RegWrite strRegistryPrinterDriver & "DsDriver\printDuplexSupported", 01, "REG_BINARY"
                        objLogFile.WriteLine "                    Printer set with duplex/stapling features."
                    Else
                        objLogFile.WriteLine "                Printer not default with duplex/stapling features."
                    End If
                ' ---------------------------------------------------------------------
                If strIsDefault = "1" Then
                    objLogFile.WriteLine "            Printer is flagged as 'Default Printer' on this unit."
                        objLogFile.WriteLine "            Executing the following command:  " & """" & SetDefaultPrinter & """"

                    wshShell.Run SetDefaultPrinter,0,True

                        objLogFile.WriteLine "            Printer set as default."

                Else
                    objLogFile.WriteLine "            Printer is not marked as 'Default Printer' for this unit."
                End If
                ' ---------------------------------------------------------------------

        rs.MoveNext

        Loop

        Else

            objLogFile.WriteLine "  The SQL Query did not return any matches.  No printers were installed."

        End If

        rs.Close

        LogLineBreak
        objLogFile.WriteLine "Printers installed."

        LogSubEnd

  End Sub

Open in new window

Disregard the misspelt "StoredProcedure" it's been corrected.
I'm still not quite getting it...

Can't get it to return results.  I've tried different versions of the code pasted above by everyone, but unfortunately - I'm missing something basic here.

Any ideas on what I'm doing wrong?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As usual, superp coding ACPerkins!

My apologies on my confusion, but you were exactly right on the money here with your comment 'way too much code'.

I ended up just putting together a test vb script to get results, and sure enough - as soon as I had the bare code from what you gave me before, SQL server gladly told me what was wrong.

Turns out I forgot to give permissions to execute the StoredProcedure to anybody other than when it was called from SSMS.  It flagged an error as soon as I tried to run the code.

I have NO idea why my original code didn't tell me anything at all, but once I set permissions, the original worked as well.

I can't thank you enough.  It's much appreciated!