Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    Belgium
    Posts
    43

    Angry Unanswered: ODBC error #306 Access97/2000 SQL7

    Greetings !

    Recently i made a form (in access 97) to a (merge published) replicated table on one of our servers (MSSQL7).

    I got a specific ODBC error which, i confess, I don't understand:

    [Microsoft][ODBC SQL Server Driver][SQL server]
    The text, ntext and image data types cannot be used in the WHERE,
    HAVING or ON clause, except with the LIKE or IS NULL predicates. (#306)

    I can CREATE NEW records! but i can't change data on existing ones without the above error.

    I tried solving the problem by using Access 2000 but the problem remains. All the latest SP's for SQL7 and ODBC are installed. Replication itself works like a charm.

    Can anyone give me a tip how to solve this (urgent) problem?

    Thank you!
    VincentJS
    IT Dept. City Gent

  2. #2
    Join Date
    Sep 2002
    Posts
    53

    Re: ODBC error #306 Access97/2000 SQL7

    Hello VincentJS!

    [Microsoft][ODBC SQL Server Driver][SQL server]
    The text, ntext and image data types cannot be used in the WHERE,
    HAVING or ON clause, except with the LIKE or IS NULL predicates. (#306)

    I can CREATE NEW records! but i can't change data on existing ones without the above error.

    Hopefully this will help shed some light on your problem and at least, point you in the right direction.

    When using text, ntext and image datatypes that are not stored "in row" then the data is not actually allocated to its column. Instead it is written to the filesystem in a binary tree. The column holds a pointer (reference) to the tree structure where the data is stored. There are specific functions used to manipulate these data types: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT to name a few. The tree is structured in 8Kb pages and they are NOT in sequential order. This will not allow a direct comparison on the column such as the WHERE clause. The HAVING clause is used for aggregrate or grouping functions so this wouldn't work for this type of structure either. However, LIKE compares each character one at a time using pattern matching which is allowable by this operation. Finally IS NULL only checks to see if the column is null it is not actually trying to parse the tree structure.

    One final thing to note on this is that depending on how large your data fields are they can generally not be retrieved all at once due to the buffer size allocated by SQL Server. If this happens then it once again returns a pointer to the structure and not the value. When inserting it is only writing the data to the filesystem and upon completion marks the field with the proper pointer.

    Look into the functions that I mentioned above to possibly offer some more assistance. I hope this at least gives you an understanding of the "why" unfortunately you will have to figure out the "how".

    Best of Luck!
    KDK

  3. #3
    Join Date
    Jun 2002
    Location
    Belgium
    Posts
    43

    Re: ODBC error #306 Access97/2000 SQL7

    Hi!

    Ok i read your information. It does shed SOME light on the matter-)

    I was thinking about something... It (the error) says that ntext or text cannot be used in a WHERE clause. Now... even when i work on the raw data opening the table in Access as a view, i cannot delete or change records.

    Have you ever had a similar error in another situation comparable to the one which started this thread? (barring user rights and things like that - I checked everything. Rights odbc links SP's etc. it all looks ok to me) Is it possible this is a bug in Access?

    Thanks for any tips!

    VincentJS



    Hopefully this will help shed some light on your problem and at least, point you in the right direction.

    When using text, ntext and image datatypes that are not stored "in row" then the data is not actually allocated to its column. Instead it is written to the filesystem in a binary tree. The column holds a pointer (reference) to the tree structure where the data is stored. There are specific functions used to manipulate these data types: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT to name a few. The tree is structured in 8Kb pages and they are NOT in sequential order. This will not allow a direct comparison on the column such as the WHERE clause. The HAVING clause is used for aggregrate or grouping functions so this wouldn't work for this type of structure either. However, LIKE compares each character one at a time using pattern matching which is allowable by this operation. Finally IS NULL only checks to see if the column is null it is not actually trying to parse the tree structure.

    One final thing to note on this is that depending on how large your data fields are they can generally not be retrieved all at once due to the buffer size allocated by SQL Server. If this happens then it once again returns a pointer to the structure and not the value. When inserting it is only writing the data to the filesystem and upon completion marks the field with the proper pointer.

    Look into the functions that I mentioned above to possibly offer some more assistance. I hope this at least gives you an understanding of the "why" unfortunately you will have to figure out the "how".

    Best of Luck! [/SIZE][/QUOTE]

  4. #4
    Join Date
    Sep 2002
    Posts
    53

    Re: ODBC error #306 Access97/2000 SQL7

    Vincent,

    Here is a link that talks about how to work with BLOB data.

    http://msdn.microsoft.com/library/de...md_04_6zec.asp

    What type of data are you storing in these fields? My own personal experience is to avoid BLOBs at any cost. For example: instead of storing an image in the database. I record the url, absolute path, name, height, width (if required), and then store the image on the filesyste.

    When it becomes absolutely necessary to use the BLOB object, I have some code written in a COM object that will do the trick, using AppendChunk and GetChunk, I do not use a stored proc for that.

    Unfortunately I am not an access developer, I tinker with it from time to time, but I have never worked with it on a regular basis. In about another month I will have to write one in C# but from what I have seen from the documentation it is alot easier in this language.

    Best of luck!
    KDK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •