Powershell prompt for ODBC DSN on Windows Server 2012 R2

PowerShell Help
1

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.

2

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.

3

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

$DirectoryToSave='C:\OUTPUT'
$Filename=‘My_Report’+ $fext
$From = ‘email’
$to = ‘email’

$SMTP = ‘SMTPNAME’

$DSN=‘NZPRD’

constants.

$xlCenter=-4108
$xlTop=-4160
$xlOpenXMLWorkbook=[int]51

and we put the queries in here

You can replace the SQL

$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)
}
else
{
$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 
        { 
        $ws.Activate() 
        $ws.Select() 
        $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.To.Add($emailTo)
$email.Subject = $subject
$email.Body = $body

initiate email attachment

$emailAttach = New-Object System.Net.Mail.Attachment $filePath
$email.Attachments.Add($emailAttach)
#initiate sending email
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($email)
}

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

4

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

$DSN='NZPRD'
$SQL=@"
SELECT * FROM SomeTable where SomeThing = '2'
"@
$connectstring = "DSN=$dsn"
$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
 $conn.open()
 $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)
 $conn.close()
 $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.

5

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 !