01004 - 0 - [Microsoft][ODBC SQL Server Driver]String data, right


T

TomT

Hi,

I have an MS Access app (2003), which uses pass thru queries to retrieve
data from a SQL 2005 database. A number of these queries, when run, create
tables, e.g. Admin - 0,0 which contain the message: 01004 - 0 -
[Microsoft][ODBC SQL Server Driver]String data, right truncation.

Is there anyway to prevent this from happening? The queries run fine, but
dealing with all those tables is quite annoying.....

Thanks for any suggestions
 
Ad

Advertisements

C

Charles Wang [MSFT]

Hi Tom,
Before we further analyze this issue, please first help confirm on the
following questions:
1. Does this issue happen on all of your SQL Server tables or only a
specific table?

2. Was the LogMessage property for your pass-through query set to "Yes"? If
so, please turn it off.

3. Could you please script out one of your problematic tables and post the
T-SQL script here?

Besides to let me better understand your scenario, please send me
(changliw_at_microsoft_dot_com) a screenshot of your problem.

Thanks.

Best regards,
Charles Wang
Microsoft Online Community Support
 
T

TomT

Thanks for your response Charles.

1. I don't believe this is a matter of all or specific tables, I think it is
happening pulling back varchar data.

2. LogMessage is already set to off

3. I'm not sure this would help - I'm pulling data back in this pass through
query based on a stored procedure. The only time I get these admin - 00 etc.
tables is when this sp runs to return data.

I will send a screenshot, and the sp that pulls back the data.

Thanks
 
C

Charles Wang [MSFT]

Dear Tom,
Thank you for your clarification.

This is a known issue in Access 2003 and has been fixed in Access 2007. It
is related to the date type varchar(MAX). By design, when Access 2003 tries
to retrieve VARCHAR data from a SQL Server database, it recognize it as
Text(255) in Access because Access 2003 was released very earlier than SQL
Server 2005. That is why the string truncation happened. You may consider
upgrading your Access 2003 to 2007 or try changing the varchar(max) column
'item_note' to the text data type to see if it helps.

Thanks.

Best regards,
Charles Wang
 
T

TomT

Thanks Charles - I'm sorry to hear it's a bug. Changing the column to TEXT
did not make any difference. The original value for that column in the sp was
varchar(6960), as it was originally run against a SQL 2000 database, which I
updated to 2005 when it came out. I changed it to max just to see if that
would help with this issue.

I had high hopes for Access 2007, but I was disappointed it forced the
ribbon on developers (I've used Access since version 1) - and removed some
things I and my clients rely on, e.g. What's This buttons for help. I won't
be using that version (2007) - and hope the next one gives us the option of
using or not using the ribbon....

I guess I'll have to write some code to remove those tables, as over time
they will accumulate - and btw, this app was developed against a SQL 2000
database, which pre-dated Access 2003, so I don't see how this version of
Access is unable to handle varchar columns up to the 8000 character limit....
 
D

David Nye

Hi Tom, Did you get any further with this? i have just encountered the issue when moving a client's application from Windows 2000 to Vista i.e. as far as I can tell those tables do not get created running the identical application on a Windows 2000 workstation. Although it could be related to some other difference in the setup of the two machines of course.



TomT wrote:

Thanks Charles - I'm sorry to hear it's a bug.
11-Sep-09

Thanks Charles - I am sorry to hear it is a bug. Changing the column to TEXT
did not make any difference. The original value for that column in the sp was
varchar(6960), as it was originally run against a SQL 2000 database, which I
updated to 2005 when it came out. I changed it to max just to see if that
would help with this issue.

I had high hopes for Access 2007, but I was disappointed it forced the
ribbon on developers (I have used Access since version 1) - and removed some
things I and my clients rely on, e.g. What's This buttons for help. I will not
be using that version (2007) - and hope the next one gives us the option of
using or not using the ribbon....

I guess I will have to write some code to remove those tables, as over time
they will accumulate - and btw, this app was developed against a SQL 2000
database, which pre-dated Access 2003, so I do not see how this version of
Access is unable to handle varchar columns up to the 8000 character limit....

Previous Posts In This Thread:

On 09 September 2009 16:42
TomT wrote:

01004 - 0 - [Microsoft][ODBC SQL Server Driver]String data, right
Hi,

I have an MS Access app (2003), which uses pass thru queries to retrieve
data from a SQL 2005 database. A number of these queries, when run, create
tables, e.g. Admin - 0,0 which contain the message: 01004 - 0 -
[Microsoft][ODBC SQL Server Driver]String data, right truncation.

Is there anyway to prevent this from happening? The queries run fine, but
dealing with all those tables is quite annoying.....

Thanks for any suggestions

On 10 September 2009 04:47
changli wrote:

Hi Tom,Before we further analyze this issue, please first help confirm on the
Hi Tom,
Before we further analyze this issue, please first help confirm on the
following questions:
1. Does this issue happen on all of your SQL Server tables or only a
specific table?

2. Was the LogMessage property for your pass-through query set to "Yes"? If
so, please turn it off.

3. Could you please script out one of your problematic tables and post the
T-SQL script here?

Besides to let me better understand your scenario, please send me
(changliw_at_microsoft_dot_com) a screenshot of your problem.

Thanks.

Best regards,
Charles Wang
Microsoft Online Community Support

On 10 September 2009 13:51
TomT wrote:

Thanks for your response Charles. 1.
Thanks for your response Charles.

1. I do not believe this is a matter of all or specific tables, I think it is
happening pulling back varchar data.

2. LogMessage is already set to off

3. I am not sure this would help - I am pulling data back in this pass through
query based on a stored procedure. The only time I get these admin - 00 etc.
tables is when this sp runs to return data.

I will send a screenshot, and the sp that pulls back the data.

Thanks

:

On 11 September 2009 05:25
changli wrote:

Dear Tom,Thank you for your clarification.
Dear Tom,
Thank you for your clarification.

This is a known issue in Access 2003 and has been fixed in Access 2007. It
is related to the date type varchar(MAX). By design, when Access 2003 tries
to retrieve VARCHAR data from a SQL Server database, it recognize it as
Text(255) in Access because Access 2003 was released very earlier than SQL
Server 2005. That is why the string truncation happened. You may consider
upgrading your Access 2003 to 2007 or try changing the varchar(max) column
'item_note' to the text data type to see if it helps.

Thanks.

Best regards,
Charles Wang

On 11 September 2009 13:41
TomT wrote:

Thanks Charles - I'm sorry to hear it's a bug.
Thanks Charles - I am sorry to hear it is a bug. Changing the column to TEXT
did not make any difference. The original value for that column in the sp was
varchar(6960), as it was originally run against a SQL 2000 database, which I
updated to 2005 when it came out. I changed it to max just to see if that
would help with this issue.

I had high hopes for Access 2007, but I was disappointed it forced the
ribbon on developers (I have used Access since version 1) - and removed some
things I and my clients rely on, e.g. What's This buttons for help. I will not
be using that version (2007) - and hope the next one gives us the option of
using or not using the ribbon....

I guess I will have to write some code to remove those tables, as over time
they will accumulate - and btw, this app was developed against a SQL 2000
database, which pre-dated Access 2003, so I do not see how this version of
Access is unable to handle varchar columns up to the 8000 character limit....

EggHeadCafe - Software Developer Portal of Choice
Compact Framework: Encrypted WebService Methods
http://www.eggheadcafe.com/tutorial...12-f1268211391f/compact-framework-encryp.aspx
 
Ad

Advertisements

D

David Nye

I should add that this application is Access 2002 and is linked to SQL Server 2000, so the MS explanation does not seem to hold water.



David Nye wrote:

Any progress?
27-Oct-09

Hi Tom, Did you get any further with this? i have just encountered the issue when moving a client's application from Windows 2000 to Vista i.e. as far as I can tell those tables do not get created running the identical application on a Windows 2000 workstation. Although it could be related to some other difference in the setup of the two machines of course.

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
BOOK REVIEW: C# In Depth by Jon Skeet (Manning)
http://www.eggheadcafe.com/tutorial...b020-2b02f9f436a9/book-review-c-in-depth.aspx
 
D

David

I was able to stop the table creation by modifying code e.g. by running the query using ADODB rather than DAO, or by modifying the SQL query to prevent it returning the unwanted messages to Access. See hidden notes on http://office.microsoft.com/en-us/access/HP051880531033.aspx "Some pass-through queries can return messages in addition to data. If you set the query's LogMessages property to Yes, Access creates a table that contains any returned messages. The table name is the user name concatenated with a hyphen (-) and a sequential number starting at 00. For example, the default user name is ADMIN so the tables returned would be named "ADMIN - 00," "ADMIN - 01," and so on. "



David Nye wrote:

Versions
27-Oct-09

I should add that this application is Access 2002 and is linked to SQL Server 2000, so the MS explanation does not seem to hold water.

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Uploading & Downloading Files using ASP.NET
http://www.eggheadcafe.com/tutorial...cd-7cc6fc1980a4/uploading--downloading-f.aspx
 
Ad

Advertisements

Joined
Jan 12, 2010
Messages
1
Reaction score
0
I am having the same issues as mentioned. I have found that it will create the table 'Admin -00'... every time I run a report (using a pass-through query) where the data itself is larger than 255 characters for a given field (I.E: Notes - (Ntext)). I am using MS SQL Server 2008, MS Access 2007 and have used both SQL ODBC drivers (SQL Server and SQL Server Native Client 10.0). In the 'Admin-0X' table it will have a row for each time it finds the data over 255 chars. I have started this venture using SQL 2000 and Access 2002/3.

Just to clarify the comment above, 'If you set the query's LogMessages property to Yes' are you referring to SETANSI_WARNINGSOFF in the SP itself or is there somewhere else that I am missing? I have also removed the check mark from the 'Use ANSI nulls, paddings and warnings.' in the ODBC File DNS.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top