Hi,
I hope this is the correct place for this post...
Anyway, I have a stored procedure that takes 3 input parameters. It then builds a temp table and etc.. Then does a select on the temp table.
Now I have a powershell script that I am trying to get to connect to the stored procedure, pass in the parameters and get the selected results from the query. I have been trying variuos approach and I am a bit stuck.. Any suggestions or documetation you can recommend on using the ddtek obbc in powershell?
Here is a bit of code (VERY ROUGH DRAFT... )
function Invoke-GetStoredProcedureIQDatabase{
# Get todays date.
# If todays date is less then July 1 then subtract 1 from current year and build PYXXXX
# If todays date is july 1 or greater then use current year and build PYXXXX.
$ErrorActionPreference = "Stop"
$today = Get-Date
$Global:PY = ($today).Year
$july1 = (Get-Date -Day 1 -Month 7)
if( $today -lt $july1) {
$Global:PY = [int]$Global:PY - 1
}
$dataset = New-Object System.Data.DataSet
$DbConn = New-Object System.Data.Odbc.OdbcConnection
$DbConn.ConnectionString = $Global:IqConnectionString
$DbConn.Open()
$DBCmd= $DbConn.CreateCommand()
$DbCmd.CommandTimeout = 3000
# supply the name of the stored procedure
$DBCmd.CommandText = "dbo.OBSWeekly"
$DBCmd.CommandType = [System.Data.CommandType]::StoredProcedure;
#now we have created the command and set it to be a stored procedure
#we now add the parameters to the stored procedures
$DBCmd.Parameters.Add("environment", [System.Data.SqlDbType]::VarChar) | out-null;
$DBCmd.Parameters["environment"].Direction = [System.Data.ParameterDirection]::Input;
$DBCmd.Parameters["environment"].Value = "DEV";
$DBCmd.Parameters.Add("dateFilter", [System.Data.SqlDbType]::VarChar) | out-null;
$DBCmd.Parameters["dateFilter"].Direction = [System.Data.ParameterDirection]::Input;
$DBCmd.Parameters["dateFilter"].Value = $today;
$DBCmd.Parameters.Add("programYear", [System.Data.SqlDbType]::VarChar) | out-null;
$DBCmd.Parameters["programYear"].Direction = [System.Data.ParameterDirection]::Input;
$DBCmd.Parameters["programYear"].Value ="PY$Global:PY";
THIS DOES NOT WORK...
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter $DBCmd
adapter.Fill($dataSet) | Out-Null
$DBConn.Close()
$DBConn.Dispose()
Here is another approach...
Almost works, but having issues with building the string to execute correctly...
function Invoke-GetStoredProcedureIQDatabase{
# Get todays date.
# If todays date is less then July 1 then subtract 1 from current year and build PYXXXX
# If todays date is july 1 or greater then use current year and build PYXXXX.
$ErrorActionPreference = "Stop"
$today = Get-Date -format d
$Global:PY = ($today).Year
$july1 = (Get-Date -Day 1 -Month 7)
if( $today -lt $july1) {
$Global:PY = [int]$Global:PY - 1
}
$Environment = "DEV";
$PY ="PY$Global:PY";
$SqlToRun = [string]::Format(“EXECUTE dbo.OBSWeekly ""{0}"",""{1}"",""{2}""”,$Environment,$today,$PY)
WRITE-HOST $SqlToRun;
$dataset = New-Object System.Data.DataSet
$DbConn = New-Object System.Data.Odbc.OdbcConnection
$DbConn.ConnectionString = $Global:IqConnectionString
$DbConn.Open()
$DBCmd= $DbConn.CreateCommand()
$DbCmd.CommandTimeout = 3000
$DBCmd.CommandText = $SqlToRun
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter $DBCmd
$adapter.Fill($dataSet) | Out-Null
$DBConn.Close()
$DBConn.Dispose()
return $dataset
Third approach...
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = $Global:IqConnectionString
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "dbo.OBSWeekly '$Environment', '$today', '$PY'"
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)
$dataset = New-Object System.Data.DataSet
[void]$adapter.Fill($dataset)
$dataset.tables[0]
THE ERROR I AM GETTING:
D:\TFS\PowerShell\RobinTeam\OBSWeekly\OBSWeekly.ps1 : Exception calling "Fill"
with "1" argument(s): "ERROR [HY000] [Sybase][ODBC Driver][Adaptive Server
Anywhere]Data conversion failed
-- (dfe_HDBValue.cxx 351) "
+ CategoryInfo : NotSpecified: (:) [OBSWeekly.ps1], MethodInvocat
ionException
+ FullyQualifiedErrorId : OdbcException,OBSWeekly.ps1
PS U:\>
Rose Marie,
Based on the error message above, it appears that you are using the ODBC driver for Sybase ASE provided by SAP rather than a Sybase driver from Progress. According to our records you are licensed for the the Progress DataDirect 4.2 ADO.NET Provider for Sybase. Could you please modify your code to use the Progress Provider and reply with the results of your testing?
Thanks,
Brian
Do you have an example?
Hmmm, I am using a ODBC connection - Sysbase IQ jr
Please open a support case to get further assistance with this issue.
Thanks,
Brian
Hi Rose, I am getting also this error while establishing a ODBC connection with powershell.
Did your issue resolved?
Which ODBC driver you are trying to use in powershell? What error are you getting?