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,550 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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…

736 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