I have a powershell script which reads the ODBC DSN and generates the report fine on my local computer(Windows 7 enterprise).
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.
Thank you so much for your prompt reply. However I am using the 64 bit ODBC DSN on the x64 OS only.
$SMTP = 'SMTPNAME'
# You can replace the SQL
#Create a Excel file to save the data
if (!(Test-Path -path "$DirectoryToSave")) #create it if not existing
$excel = New-Object -Com excel.application #open a new instance of Excel
# You can refresh it
$qt = $ws.QueryTables.Add("ODBC;DSN=$DSN", $ws.Range("A1"), $SQL)
#Function to send email with an attachment
Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath)
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.
Thank you. Connection string method works, it doesn't prompt for DSN anymore.
You must be logged in to reply to this topic.