Creating a parameter (date) query in Excel connecting it to a Sybase Adaptive server 12.5 (ODBC PROBLEM?)

Hi there,

I'm stuck on a little problem.
I'm trying to create some management reports in Excel. After filling a table by executing a stored procedure I want to get a range of values from that table into excel.

I selected all values in Microsoft Query (Select * from F_sel_DossierTurnOver).
Then I added a Criterium, InvDate > [Please enter a date:]
When I enter a value in Microsoft Query there is no problem, until I go to microsoft Excel, it asks to enter a value or enter a cell location for the parameter value.

The following error occurs: [MERANT][ODBC Sybase driver]Inconsistent descriptor information

When I try to change the datatype of the parameter an other error occurs:
Timestamp parameters with zero scale must have a precision of 13, 16, or 19. Parameter number: 7, precision: 2

Can anybody help me? Thanx in advance!
scrAsked:
Who is Participating?
 
SNilssonConnect With a Mentor Commented:
I dont have many idéas regarding a fix for the problem you have with your ODBC connection, but I might have another suggestion.

If you know how to create a DSN less OLE DB connection in  VBA you could try that way (much more sturdy and reliable than ODBC via msquery).

(If you dont and is willing to do so, I can help you with the code)

Some sybase connection string examples below:

_______________________________________________________________________________

ODBC

          o  Standard Sybase System 12 (or 12.5) Enterprise Open Client:
            "Driver={SYBASE ASE ODBC Driver};Srvr=Aron1;Uid=username;Pwd=password"


          o  Standard Sybase System 11:
            "Driver={SYBASE SYSTEM 11};Srvr=Aron1;Uid=username;Pwd=password;"

                Do you know a userguide for Sybase System 11, 12, 12.5? E-mail the URL to connectionstrings.com now!! >> 

          o  Intersolv 3.10:
            "Driver={INTERSOLV 3.10 32-BIT Sybase};Srvr=Aron1;Uid=username;Pwd=password;"


          o  Sybase SQL Anywhere (former Watcom SQL ODBC driver):
            "ODBC; Driver=Sybase SQL Anywhere 5.0; DefaultDir=c:\dbfolder\;Dbf=c:\mydatabase.db;Uid=username;Pwd=password;Dsn="""""

Note! The two double quota following the DSN parameter at the end are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax. The empty DSN parameter is indeed critical as not including it will result in error 7778.


OLE DB

          o  Adaptive Server Anywhere (ASA):
            "Provider=ASAProv;Data source=myASA"

                Read more in the ASA User Guide >> 

          o  Adaptive Server Enterprise (ASE) with Data Source .IDS file:
            "Provider=Sybase ASE OLE DB Provider; Data source=myASE"

                Note that you must create a Data Source .IDS file using the Sybase Data Administrator. These .IDS files resemble ODBC DSNs.

          o  Adaptive Server Enterprise (ASE):
            "Provider=Sybase.ASEOLEDBProvider;Srvr=myASEserver,5000;Catalog=myDBname;User Id=username;Password=password"
               - some reports on problem using the above one, try the following as an alternative -

            "Provider=Sybase.ASEOLEDBProvider;Server Name=myASEserver,5000;Initial Catalog=myDBname;User Id=username;Password=password"

This one works only from Open Client 12.5 where the server port number feature works, allowing fully qualified connection strings to be used without defining any .IDS Data Source files.
____________________________________________________________________________________
0
 
SNilssonCommented:

After...... enter a cell location for the parameter value.. when you select the cell with the date in it and press ok.... your error happens, right ?

If you change your query in MSquery, and remove the date as a wildcard and put another criteria there a text column for example, do you still get the same error ?

Just to rule out or comfirm that it's a date format problem.
0
 
scrAuthor Commented:
Hi,

1> Thats right

2> First, I tried in MS Query to enter a date (like #31/12/2003# (its the dutch form for dates, us style should by like this 12/31/2003)). This works fine. Then I added a parameter, it askes for a value and again, I enter 31/12/2003. This gives the same result. Now, when I return to Excel (leaving the value 31/12/2003 unchanged) I get a error. [MERANT][ODBC Sybase driver]Inconsistent descriptor information
The query change is discarded after pressing ok.

When I try to do this all over but leaving the date parameter empty before returning to Excel, the parameter screen popups asking for a cell. When I enter this cell I get the same error again. [MERANT][ODBC Sybase driver]Inconsistent descriptor information

It does not matter which kind of date I enter in MSQuery. The value in the database is this: 2003-11-26 00:00:00,000. When I enter this value in the parameter in MSQuery, it works fine, but when I enter this value in Excel it generates a problem (as a constant also).
I tried to convert the cells to the right form (=Text(C5;"DD-MM-JJJJ")) and all the other forms but without any result. (J = Y in dutch)

If any of this isn't clear please tell me, I will try to make it clear, I will explain it by images if u want.

Tnx!
0
 
scrAuthor Commented:
I'm thinking about an ODBC driver problem or something like that. Maybe there is a new version available?

Is it possible that there is a problem with the communication between Excel and MSQuery?

I'm running Office XP right now. I tried the same with Office 2003 but with the same results.
0
 
scrAuthor Commented:
Hi there,

I've created a workaround.
I added an other column in the table with the name SelDate and did the following.
update F_sel_DossierTurnOver
set seldate = convert(varchar,InvDate,112)

So I convert the datetime field value to a YYYYMMDD varchar value for selecting date ranges. This works fine now. But it's still strange that the datetime communication between Excel and MSQuery isn't correct.

Thanks for your research and solution.
You get my points for the effort ;)

Greetz ScR
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.