SQL Server 2008 R2 export to Access fails with error

SSIS and Import/Export
1

I have a line of business application that the company decided to disable (valid license purchased but now it's old so they just disabled the authentication system, leaving me without ANY of my data in an accessible format). They sent me a link to just use SQL Export Wizard to get it into Access or Excel (I chose Access). Anyway, There are about 750 tables and the export wizard fails with the following message:

Could not connect destination component. Error 0xc0204016: SSIS.Pipeline: The "output column "Printitem" (655)" has a length that is not valid. The length must be between 0 and 4000.

I was able to step through some of this by finding the table with the offending column but this is EXTREMELY tedious and I still have over 600 tables to work with!

Is there a way to globally fix this? I have "Ignore" selected on conversion errors but it still fails. Worse, yet, the error message does not include the offending table name! So I have to try to find it (even more tedium).

HELP, Please. I'm not a DBA.

Note: I did track down this column in a table. It's a VarChar field 9n SQL Server, being converted to LongText in Access .mdb format. Funny thing is, there's NO DATA in this table!

2

NVarchar used to have a limit of 4000 characters, so that is probably what that is about.

Varchar limit was 8000 - so assuming that all export is restricted to 4000 characters any varchar column defined with 4001 - 8000 characters is going to be a problem.

There was also a (legacy) datatype TEXT and NTEXT which held unlimited length data - so if the APP used that those column(s) will be a problem too. SQL 2008 also had varchar(MAX) and NVarchar(MAX) which store unlimited text and could also be a problem. My guess is that if the APP is old that you are unlikely to have the newer varchar(MAX) but you might have the older, deprecated, TEXT datatype columns.

Presumably you legitimately have access to the SQL database? Why not just use that (instead of Access of Excel)?

If you cannot access SQL on the machine the database is on you could install SQL on another machine and (preferably) restore a Backup file from the old database to create a new database (on the new machine) or, failing that, copy the database files across (there are some special steps that you would need to follow in order to safely copy the database file, itself, hence why a Backup File would be preferable).

What were you planning to do with the data once you exported it? Perhaps you could do that direct onto a SQL database instead?

3

I'm not that familiar with this. The database was part of a larger, PSA application we purchased some time ago. The data is filtered by the (now defunct) application. So I figured if I just converted it to an Access .mdb file I could at least look for/at the data in Access. I suspect (admittedly I have little experience with Access) I can create some type of UI to intelligently look at the data.

Eventually I will be exporting subsets of the data to a new PSA we are subscribing to but I have no idea how all the data in the SQL database is related. As such, I don't know "where" (what tables) much of it is stored. And there are hundreds of tables.

Is there a way to get the SQL Export wizard to just down convert the data columns so I can get this all exported?

4

I think you would be much better off connecting Access to the SQL database, rather than trying to export the data into Access.

You might wind up exporting subsets, but until you know what-is-where its a stab in the dark, and on every table you may need to "fiddle" to get a successful transfer- as you have already found. You are also likely to find, potentially "long after the fact", that the export for a specific table failed in some way :frowning: then you are going to ahve to go back and sort it out. Connecting Access direct to SQL Database avoids that pitfall.

Access has an ability to "Link" to an external database (i..e the SQL Database in you case) table - so you can set up a Linked Table, in Access, which will link to that table in SQL.

Its a while since I've done it, and no doubt it has improved, but back then there was some sort of Wizard (in Access) which displayed a list of all the tables (in the SQL database) and you just ticked all the ones you wanted to link to. I suppose you could tick all of them, get them "linked", and then use Access query tools to find out which tables have data you are interested in, which are empty, and which are of no interest, and then just focus on the useful ones.

But that presupposes you can connect to the SQL database - which it seems the permissions have been removed from?

If that's the case you may have to "move" the database to a different SQL Server in order to regain permission. Here's hoping that you can just leave it where it is, and connect/link to it there.

5

Oh, that would be GREAT! I'd much rather leave the database as is and access it through Access if possible!

I'll check Access to see how I can do that. Thanks!

6

I tried attaching to the SQL database. All I get from the DSN are all the sys.xxx files. None of the tables that appear in the import list are tables I see from the SQL Server export wizard (all the data tables). What I'm seeing seems to be all the SQL server database management system tables (lots of schema stuff and security stuff). NONE of the tables represent data that I would normally access from the application.

What am I missing??

7

Yes, the sys.xxx tables will be system ones.

Perhaps you are not connected to the correct database? DSN probably connecting to MASTER database by default?

If you are able to run a query (your connection with the sys.xxx tables should do fine) then this:

SELECT name
FROM sys.databases
ORDER BY name

if you then wanted to see the Tables in a specific database then this:

SELECT name
FROM YourDatabaseName.sys.tables
ORDER BY name

both those should work regardless of what database you are connected to

8

Also:

SELECT db_name() AS [CurrentDatabaseName]

to give you the name of the database you are currently connected to

9

I was connecting to the "Master" database, not the database of the application. That was it. Thanks, Kristen! It's working, now.

1 Like