November 3, 2008 at 5:57 pm
I am using Access 2003 via ODBC to connect to tables in a SQL Server
database. (The database was created using SQL Server 2005. I'm using SQL Server 2008 Express to connect to copies of the database files for
development purposes.)
Several of the tables have fields with XML data types. After linking to the
tables from Access, I note that the data type for these XML fields, as
reported by Access, is "text" and has a length of only 255 characters. As a result, most of the ~2500 bytes of data in the XML field is lost and all that I see is the last ~180 bytes.
What options are available for working around this problem? I'm new to SQL server. Thanks for any help in advance.
November 4, 2008 at 5:48 am
Here's a blog post over at Microsoft that might help
http://www.microsoft.com/technet/scriptcenter/resources/officetips/oct05/tips1020.mspx
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 4, 2008 at 9:32 am
Thanks for your response. The article used an XML file as the import source, which doesn't map to my situation. The data that I need resides in the SQL server tables and only one of the fields in the table is in XML format.
I attempted to use an MS Access 2003 pass-through query but got the same results. The XML field was truncated. I can get external data from MS Excel 2003 and see all of the XML data, so the problem appears to be localized to MS Access. I'd like to use Access, if possible, because I'm very familiar with that programming environment. Do you have any other suggestions?
November 4, 2008 at 10:17 am
I'm not that familiar with Access.
Whether or not the referenced project was coming from a file or a query, it's still storing the XML and dealing with it within Access code. I would assume there would be pointers there. It sounds like you're hitting an implicit data conversion. It may be that Access needs to stream XML data rather than read it like was a VARCHAR field.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 4, 2008 at 10:29 am
Are you using an Access Project (*.adp) or an Access Database (*.mdb)?
November 4, 2008 at 11:13 am
I have tried both approaches, .adp and .mdb, and see the same phenomenon. I have been able to export to an excel spreadsheet from SQL server and then import into access without data loss. So that will be a fall back position if I can't find another alternative. Thanks for your post.
December 24, 2008 at 8:31 am
Ok, I've a question too. I'm using sql server 2008 and I want to use web services as data source. I found some web services but when I use them as data source I don't know how to add query in dataset.
If anyone could show me an example how to do it I would truly appreciate it ( chose any web service I could use too and type query for calling some method from that web service)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply