usslindstrom
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. :)
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
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();
here is an exeample:
DIM MyResult
Set objSQLCon = CreateObject("ADODB.Connec tion")
objSQLCon.Open "Provider=SQLOLEDB.1;Persi st 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
DIM MyResult
Set objSQLCon = CreateObject("ADODB.Connec
objSQLCon.Open "Provider=SQLOLEDB.1;Persi
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.
I should also point out that doing DISTINCT with a UNION is redundant: A UNION statement implies DISTINCT.
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.
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.
You don't. But if your not comfortable removing it, leave it.
ASKER
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?
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
Why not combine them all into a single Stored Procedure?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
ASKER
I saw one error already "objCommand.Text" and have fixed it, but it still doesn't return any results.
ASKER
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.
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.
ASKER
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.
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
ASKER
Disregard the misspelt "StoredProcedure" it's been corrected.
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
Open in new window