I am trying to use Toad Data Point to connect to a SAS dataset source using an ODBC connection. When I use my most current version of 3.0.1.1734, the connection is made and I can see my libraries listed in the object explorer, and the datasets listed as the list of tables. The problem occurs when I open a dataset to browse, I get the following error:
System.ArgumentOutOfRangeException
Year, Month, and Day parameters describe an un-representable DateTime.
Stack Trace:
at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)
at Quest.Toad.NativeOdbc.UnManagedColumnDescriptor.GetBufferDataAsOdbcDate(UInt16 rowOffset)
at Quest.Toad.NativeOdbc.OdbcDataReader.GetValue(Int32 i)
at Quest.Toad.Odbc.OdbcToadConnection.ReadRow(IDataReader reader, FastTable datatable, FastReadOptions options, PrePostProcessHandler prePostProcessHandler)
at Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()
I also tried using an older version of Toad for Data Analysts that I still had (2.1.0.227) and found that the ODBC connection to SAS worked just fine. I could open the dataset and browse with no errors.
My SAS driver version is 9.02.00.9225. Any ideas on something I can do to fix the error and be able to use newer Toad with SAS?
Thanks!
Amanda
There appears to be a datevalue that is not valid when we convert to datetime format. Can you use somekind of casting function on the date when retrieving? Something like to_char(date, formatstring)? This has the conversion done on the server and not in TDP/
Debbie
It appears that more than just datetime are not being translated correctly. This morning I tried to run a query for a couple of fields that were not dates. The results had extra rows included, sometimes with jibberish characters in them (see attached image). The facility_id field is a four digit number and the clinical_app field is a text field.
We don’t have SAS here to test the ODBC driver. Does this result set have unicode chars in it? Often there are separate drivers or settings to support unicode.
What version of the ODBC API does the SAS driver comply with? While ODBC is a standard protocol, we find that each driver tends to implement it differently.
Debbie
Hi,
I don’t think the datasets should have any unicode characters in it, although I did look at the driver documentation and it says unicode UTF-8 encoding is supported. The version of the driver I have is 9.22, which according to the documentation is ODBC 3.5 compliant.
On a related note, I had a previous version of the SAS driver that was 9.1. It is ODBC 2.0 compliant. I had been able to use it with TDP to access the datasets previously although I would get a message box at initial connection about not meeting minimum driver requirements. After clicking continue in that message box I was able to use the datasets. I’ve tried reverting back to the older driver, but then I get an error at connection with the message “Invalid pointer.”
Were you able to resolve this problem as I have the exact same issue now. Same error when pulling dates and gibberish text. We did update to SAS grid recently. Prior it was all working.
Unfortunately, I don’t believe I ever did solve the problem. I’ve moved away from using Toad Data Point and am now using SAS Enterprise Guide.