Right Truncation warning

[Guest]

I found another set of posts concerning this issue, but without any
resolution. I have an Access 2003 front end to a SQL Server 2005 backend.

I have a passthrough query which retrieves records from the SQL Server
database, and when it runs I get a table created named Admin - 00 (and then
Admin - 01, etc. each time it runs), with one or more records with a column
heading ODBC, and values of 01004 - 0 - [Microsoft][ODBC SQL Server
Driver]String data, right truncation.

How can I prevent this from happening?

Thanks for any assistance,

TomT

 

[Charles Wang[MSFT]]

Hi Tom,
I understand that a new table named "Admin-nn" (n=0,1,2,...) was created
every time you ran your pass-through query in Access 2003 to your SQL
Server 2005.
If I have misunderstood, please let me know.

I performed a test at my side, but unfortunately I could not reproduce your
issue. My environment and test steps are as follows:
Environment:
Windows XP SP2, Access 2003 SP2, SQL Server 2005 Ent SP2

Local test steps:
1. Open "Control Panel", double click "Administrative Toos", double click
"Data Sources (ODBC)";
2. Switch to the "System DSN" tab, click "Add...", select "SQL Native
Client" as the driver and click "Finish".
3. Follow the wizard to input a reference name, select the data source, use
"Windows Integrated Windows Authentication", specify a default database and
click finish.
4. Create a blank Access 2003 database, select "Queries", double click
"Create query in Design view", close the popup "Show Table" window, on the
"Query" menu, point to "SQL Specific" and then click "Pass -Through";
5. Click the "Properties" icon, and select the ODBC data source that I
former created.
6. Input "SELECT * FROM Orders" to the window and run it.

After the above steps, I saved the pass-through query and checked my
database tables in SQL Server Management Studio; however I did not see a
new table created.

To let me better understand your issue, I would like that you could let me
know the following questions:
1. What is the version of your Access 2003 and SQL Server 2005 respectively?
2. Could you please show me some screenshots of your issue? You can
directly mail the screenshots to me (changliw_at_microsoft_dot_com).
3. Can you reproduce this issue from other computers? If so, could you
please provide me a simple SQL Server 2005 database backup file and the
pass-through query so that I can reproduce your issue at my side?

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Guest]

Charles, thanks for your reponse. I should mention that in all cases where
I've seen this behavior, the query has included at least one column with a
large varchar column (in the latest case 500).

I'm guessing this has something to do with the problem. Did your testing
include something like this?

 

[Charles Wang[MSFT]]

Hi Tom,
I appended a new column with varchar(1000) and insert some records over 600
bytes into the field.
Then I created a new Pass-through Query, and it also worked fine. No new
tables created in my SQL Server 2005 database.

I appreciate that you could answer my three questions in my last reply so
that we can perform further research on this issue.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Charles Wang[MSFT]]

Hi Tom,
I have not received your response. Would you mind letting me know the
current situation? If you need further assistance, feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Guest]

Charles, here are the answers you requested, sorry for the delay.

1. Access version: 2006 (11.6566.8132) sp2; SQL Server 2005 9.03054 sp2
2. Screenshots have been emailed
3. Problem did not occur on one other system I tested: Windows 2003 Server.

My OS is Vista, but I don't think that's a factor.

Thanks for your help,

Tom

 

[Charles Wang[MSFT]]

Hi Tom,
Thanks for your email response.

I reproduce your issue in my Vista machine. No matter SQL Server driver or
SQL Native Client driver can result in the same issue in Windows Vista, but
this issue could not be reproduced on Windows 2003 and Windows XP. Per my
test, even if a table column has less than 500 characters, this issue will
also be rose when I run the query. I believe that this is a product issue
which only occurs in Windows Vista. Now I am trying to contact product team
to confirm if this is a product issue and see if there is any workaround
now. I will get back to you as soon as possible. I appreciate your
patience.

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Charles Wang[MSFT]]

Hi Tom,
I am further discussing this issue with someone from the product team. I
may need more time to get back to you.
Appreciate your patience.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Charles Wang[MSFT]]

Hi Tom,
This is most likely to be a product issue, however I could not get further
discussions from the product team. It seemed that they were in vacation
recently. I will discuss this issue with our Tech Lead tommorrow and I will
let you know the result as soon as possible. Sorry for bringing you any
inconvenience.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Charles Wang[MSFT]]

Hi Tom,

Since this issue can be reproduced, I also recommend that you submit a
feedback to Microsoft. Since there is no public connect site for Access and
that this issue also referenced SQL Server, please send a feedback via
https://connect.microsoft.com/sql so that the product team can hear your
voice and that this issue may be fixed in future.

Also I would like to confirm with you to see whether this issue is
important or urgent for you. If so, please post back. We will discuss to
see if we can include a senior support channel which may be able to publish
a seperate hotfix for you if this issue is urgent.

Look forward to your response.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Charles Wang[MSFT]]

Just a clarification to the community, this issue was resolved at last. The
resolution was as following:
"We created a new database and copied the SQL statements from the old
database into the new database and the Admin tables are no longer created.
The Admin tables are used when the query property "log messages" is turned
on, but your database has this property turned off. It appears Access was
retaining this setting incorrectly."

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================