Powershell prompt for ODBC DSN on Windows Server 2012 R2

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Shalini Sharma Shalini Sharma 2 weeks, 3 days ago.

  • Author
  • #54811
    Profile photo of Shalini Sharma
    Shalini Sharma

    I have a powershell script which reads the ODBC DSN and generates the report fine on my local computer(Windows 7 enterprise).
    However same script when runs on central server hosting Windows Server 2012 R2, it always prompts for ODBC DSN. Why is it so?
    Also when I manually execute the powershell on central server, I never see the system ODBC DSN that I have created for the script.
    I want to schedule this script to run on this server automatically, but since it is prompting for ODBC DSN the job hangs all the time.
    Any help in this regard is appreciated. Thanks.

  • #54816
    Profile photo of Jonathan Warnken
    Jonathan Warnken

    Without the script my guess would be that you are using the 32 bit ODBC DSN on the x64 OS and the script is reading from the default dsn path which would be just the x64 ODBC DSN.

    If that is the case you would need to have the script check both locations or to be aware of the os architecture and check accordingly. Or run your script as a 32bit process.

  • #54844
    Profile photo of Shalini Sharma
    Shalini Sharma

    Thank you so much for your prompt reply. However I am using the 64 bit ODBC DSN on the x64 OS only.
    Below is the PowerShell I am using:
    $todaydate = Get-Date -format "MM-dd-yyyy"
    $fext = Get-Date -format "MMddyyyy"
    #Just change the below parameters

    $Filename='My_Report'+ $fext
    $From = 'email'
    $to = 'email'



    # constants.
    # and we put the queries in here

    # You can replace the SQL

    My SQL code has beee written here

    #Create a Excel file to save the data
    # if the directory doesn't exist, then create it

    if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing
    New-Item "$DirectoryToSave" -type directory | out-null

    $excel = New-Object -Com excel.application #open a new instance of Excel
    #$excel.Visible = $True #make it visible (for debugging more than anything)
    $wb = $excel.Workbooks.Add() #create a workbook
    $currentWorksheet=1 #there are three open worksheets you can fill up
    if ($currentWorksheet -lt 4)
    $ws = $wb.Worksheets.Item($currentWorksheet)
    $ws = $wb.Worksheets.Add()
    } #add if it doesn't exist
    $currentWorksheet += 1 #keep a tally

    # You can refresh it

    $qt = $ws.QueryTables.Add("ODBC;DSN=$DSN", $ws.Range("A1"), $SQL)
    # and execute it
    if ($qt.Refresh()) #if the routine works OK
    $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
    $excel.Rows.Item(1).VerticalAlignment = $xlTop
    $excel.Rows.Item("1:1").Font.Name = "Calibri"
    $excel.Rows.Item("1:1").Font.Size = 11
    $excel.Rows.Item("1:1").Font.Bold = $true
    $Excel.Columns.Item(1).Font.Bold = $true
    $filename = "$DirectoryToSave$filename.xlsx" #save it according to its title
    if (test-path $filename ) { rm $filename } #delete the file if it already exists
    $wb.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
    $wb.Saved = $True #flag it as being saved
    $wb.Close() #close the document
    $Excel.Quit() #and the instance of Excel
    $wb = $Null #set all variables that point to Excel objects to null
    $ws = $Null #makes sure Excel deflates
    $Excel=$Null #let the air out

    #Function to send email with an attachment

    Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath)
    #initate message
    $email = New-Object System.Net.Mail.MailMessage
    $email.From = $emailFrom
    $email.Subject = $subject
    $email.Body = $body
    # initiate email attachment
    $emailAttach = New-Object System.Net.Mail.Attachment $filePath
    #initiate sending email
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)

    #Call Function
    sendEmail -emailFrom $from -emailTo $to -subject "My Report for $todaydate" -body "My_Report_LoadStatus_$todaydate" -smtpServer $SMTP -filePath $filename

  • #54879
    Profile photo of Jonathan Warnken
    Jonathan Warnken

    I was able to duplicate you issue on a x64 OS with a 32 bit version of Office installed.

    I created a DSN in the x64 ODBC control panel and verified it worked with this code

    SELECT * FROM SomeTable where SomeThing = '2'
    $connectstring = "DSN=$dsn"
    $conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
     $cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)
     $da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
     $dt = New-Object system.Data.datatable
     $null = $da.fill($dt)

    Your code with the same DSN being used in Excel prompted for the dsn and only displayed the 32 ODBC dsn entries.
    You will need to create a 32 bit DSN entry ,install the x64 version of excel, or add the connection string into your script.

  • #54949
    Profile photo of Shalini Sharma
    Shalini Sharma

    Thank you. Connection string method works, it doesn't prompt for DSN anymore.
    However my requirement is to write this data in to excel (xlsx) format and email.
    Do you have the code on how to write $dt (i.e. system.Data.datatable) data into excel sheet, top line should print the column header and following lines as data?
    Appreciate your help !

You must be logged in to reply to this topic.