Solved

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

Posted on 2004-08-13
5
4,503 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:scr
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:SNilsson
ID: 11791842

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
 

Author Comment

by:scr
ID: 11792272
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
 

Author Comment

by:scr
ID: 11792294
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
 
LVL 8

Accepted Solution

by:
SNilsson earned 500 total points
ID: 11792566
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
 

Author Comment

by:scr
ID: 11792709
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now