MS SQL and Powershell Studio

Ask your Windows PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
User avatar
swehner
Posts: 7
Meble kuchenne na zamówienie - na wymiar - Wrocław
Joined: Sun Mar 03, 2013 12:42 pm

MS SQL and Powershell Studio

Post by swehner »

Hi all,

i have a Problem using SQL and PowerShell-Studio lates Version 64Bit.
in PowerShell-Studio the Code doesn't work. If i use
PowerShell-ISE the Code works fine.
  1. $sqlCon = New-Object System.Data.SqlClient.SqlConnection
  2.  
  3. $sqlCon.ConnectionString = "Data Source=OFWHSQT1\INFRASTRUKTUR; Integrated Security=True; Initial Catalog=Anmeldedatenbank"
  4.  
  5. $sqlCon.open()
  6.  
  7.  
  8.  
  9. $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
  10.  
  11. $sqlCmd.Connection = $sqlCon
  12.  
  13. $strCmd = "Select * from Usermappings where username = '$env:USERNAME' and MappingType ='P'"
  14.  
  15. $sqlCmd.CommandText = $strCmd
  16.  
  17.  
  18.  
  19. $sqlReader = $sqlCmd.ExecuteReader()
  20.  
  21. # +++ Datensätze einlesen
  22.  
  23. # F_WriteLogFile ("Folgende Drucker wurden in der Datenbank gefunden!")
  24.  
  25. $intLoopcount = 0
  26.  
  27.  
  28.  
  29. [color=#FF0000]The following part doesn't work. PowerShell jumps straight  to SQLReader Close[/color]
  30.  
  31.  
  32.  
  33. While ($sqlReader.Read())
  34.  
  35. {
  36.  
  37.     [String]$strServer = $sqlReader.Item("PrintServer").ToString().Trim()
  38.  
  39.     [String]$strShareName = $sqlReader.Item("ShareName").ToString().ToUpper()
  40.  
  41.     [String]$strPrinterName = $sqlReader.Item("PrinterName").ToString().ToUpper()
  42.  
  43.     [String]$strPath = "\\" + $strServer + "\" + $strShareName
  44.  
  45.     [String]$strKey = "\\" + $strServer + "\" + $strPrinterName
  46.  
  47.     $dicDBprinter.Add($strKey, $strPath)
  48.  
  49.     [Int]$intLoopcount = $intLoopcount + 1
  50.  
  51.     #F_WriteLogFile("`t" + $strPrinterName)
  52.  
  53.     If ($intLoopcount -gt 30)
  54.  
  55.     {
  56.  
  57.         #F_WriteLogFile "Möglicher Fehler in DB, Druckeranbindung in Schleife hängengeblieben!!"
  58.  
  59.         [Bool]$bolLoopdetect = $True
  60.  
  61.         Exit
  62.  
  63.     }
  64.  
  65. }
  66.  
  67. $sqlReader.Close()

User avatar
brittneyr
Site Admin
Posts: 432
Joined: Thu Jun 01, 2017 7:20 am

Re: MS SQL and Powershell Studio

Post by brittneyr »

What errors are you receiving?
Brittney Ryn
SAPIEN Technologies, Inc.

User avatar
swehner
Posts: 7
Joined: Sun Mar 03, 2013 12:42 pm

Re: MS SQL and Powershell Studio

Post by swehner »

Hi. I Get no error.
$sqlReader say's that $sqlReader has rows, but the while loop jumps directly to $sqlReader.close()

If i use the PowershellIse it works fine.

jvierra
Posts: 14368
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: MS SQL and Powershell Studio

Post by jvierra »

SQlReader does not have rows in the normal sense. It has to be moved forward with a "read()" command then the data for a row is available. The object can only be read once then it must be closed and reopened to use it again. There is no need to use the reader. Just load the DataTable and you can then do anything you need easily.

The code you are using is badly converted VB.Net code that is floating around the Internet. No programmer would do this with PowerShell. I can tell this because all of the constructs are from the VB.Net examples of how to code with a reader. They are also old and may have come from a bad VBScript example converted to ADO.Net or PowerShell. We haven't used Hungarian notation for 20 years but old VB and VBScri0pt coders seem to continue using it.

Here is how to code a reader in PowerShell:

Code: Select all

$intLoopcount = 0
While ($sqlReader.Read()){
    
    $strServer = $sqlReader.Item('PrintServer')
    $strShareName = $sqlReader.Item('ShareName')
    $strPrinterName = $sqlReader.Item('PrinterName')
    $strPath = "\\$strServer\$strShareName"
    $strKey = "\\$strServer\$strPrinterName"
    Write-Host $strKey,$strPath 
    $dicDBprinter.Add($strKey, $strPath)
    
    If ($intLoopcount++ -ge 30){
        #F_WriteLogFile "Möglicher Fehler in DB, Druckeranbindung in Schleife hängengeblieben!!"
        Write-Host 'Exiting PowerShell'
        pause
        Exit
    }
}

jvierra
Posts: 14368
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: MS SQL and Powershell Studio

Post by jvierra »

I just ran your code with my changes in PowerShell Studio and it ran just fine.

Locked