Help with multi MDB import. 1

[sparkbyte]

I keep getting the 1st file imported but when it goes to the second it errors out with.

2136044
D:\AccessDBs\CentOps\Archived Databases\History 09-09.mdb
History 09-09.mdb has been imported.
2136044
D:\AccessDBs\CentOps\Archived Databases\History 09-09_Backup.mdb
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.".
Msg 7341, Level 16, State 4, Line 2
Cannot get the current row value of column "[Microsoft.Jet.OLEDB.4.0].REMARK" from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". [COLUMN_NAME= REMARK STATUS=Unknown DBBINDSTATUS]


Code is here thread183-1582207

any help would be greatly appreciated!!!




Thanks

John Fuhrman
faq329-6766

 

[gmmastros]

Multiple-step OLE DB operation generated errors."

This error message often times means you are trying to store too much data in a column. For example, if you have a varchar(50) column and you try to store a string that is 51 characters (or longer), you may get this error.

I encourage you to check the lengths of your string columns as a first step towards resolving this problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Thanks. Will do.

BTW, is there a way to query for the max length the data stored in a field within a table?



Thanks

John Fuhrman
faq329-6766

 

[sparkbyte]

By the way here is a create for the table.

CREATE TABLE [dbo].[Monthly](
[OFFICE_ID] [smallint] NULL,
[PREFIX] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUFFIX] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SEQUENCE_NUM] [smallint] NULL,
[TRANSACTION_DATE] [datetime] NULL,
[GOVE_ID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SEC_OPT_ID] [smallint] NULL,
[REMARK] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OFFICE_DESC] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SECTION_DESC] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RESP_DESC] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RESP_CODE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SECTION_CODE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TRANSFER_IN_OUT_FCO_ID] [smallint] NULL,
[GOVE_FULL_NAME] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TRANSACTION_CODE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FCO] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

and the Error states

Cannot get the current row value of column "[Microsoft.Jet.OLEDB.4.0].REMARK" from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". [COLUMN_NAME= REMARK STATUS=Unknown DBBINDSTATUS]


Not sure what I can do when the field is nvarchar(MAX) already.


Thanks

John Fuhrman
faq329-6766

 

[gmmastros]

We're talking about the Access database, right?

You should be able to do something like...

Select Max(Len(YourColumnName)) As YourColumnName_MaxLength
From   YourTableName

I know this query would work with a SQL table, not so sure about an Access table though. If this doesn't work, then something very similar would.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Thanks.


BTW. The Remark field is a MEMO type in Access.

Thanks

John Fuhrman
faq329-6766

 

[sparkbyte]

Something curious.

When I do the legth query in Access I get a max length of 383 and from SQL server 766.

DECLARE @SQL varchar(MAX)
DECLARE @FilePath varchar(200)

Set @FilePath = 'D:\AccessDBs\CentOps\Archived Databases\History 09-09.mdb'

Set @SQL= '
Select Max(Datalength(Remark)) As Remark_MaxLength
FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''$FilePath$'';''admin'';'''',Monthly)
'
Set @SQL = Replace(@SQL,'$FilePath$',@FilePath)

print @SQL
Exec (@SQL)

766

Which should not cause a problem with nvarchar(MAX).

Thanks!!!!!


Thanks

John Fuhrman
faq329-6766

 

[gmmastros]

When I do the legth query in Access I get a max length of 383 and from SQL server 766.

766 is exactly double 383. In access, you stored data in a text column. text columns require 1 byte per character with the data stored in ASCII. In SQL Server, you're using nvarchar. nvarchar columns require 2 bytes per character and can store unicode data. If you wanted to do an Apple-for-Apple translation, you could pick varchar(max) as your data type. varchar stores ASCII and only requires 1 byte per character.

DataLength returns the storage size, Len returns the number of characters. With nvarchar, Len and DataLength return the same thing.

Ex:

Declare @UnicodeData nvarchar(max)
Declare @ASCIIData varchar(max)

Set @UnicodeData = N'Hello World'
Set @ASCIIData = @UnicodeData

Select Len(@UnicodeData), DataLength(@UnicodeData), Len(@ASCIIData), DataLength(@ASCIIData)

I would encourage you to check the length of the other columns in your table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

With nvarchar, Len and DataLength return the same thing.

I meant:
With [!]varchar[/!], Len and DataLength return the same thing.
With [!]nvarchar[/!], Len and DataLength [!]do not[/!] return the same thing.

You can also use DataLength with other data types to see what the storage requirements are.

ex:

Declare @DateVar DateTime
Set @DateVar = GetDate()
Select DataLength(@DateVar)

The code above returns 8 because SQL Server uses 8 bytes to store a datetime value.

BTW, what version of Access are you using?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Access 2003

Thanks for the explanation of varchar vs nvarchar. I had read some information on them but had not understood it until your explanation. Thanks

If I understand things..

varchar(MAX) is eqiv to varchar(8000) or a max of 8000 characters

nvarchar(MAX) is equiv to nvarchar(8000) or a max of 4000 characters because of the 2 byte storage per character to support unicode.

In SQL server I had to change LEN to DataLength because it errors on converting the MEMO field in Access.

-------------------------------------------
Select Max(len(Remark)) As Remark_MaxLength
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','D:\AccessDBs\CentOps\Archived Databases\History09-03.mdb';'admin';'',Monthly)
------------------------------------------
Argument data type ntext is invalid for argument 1 of len function.

Thanks

John Fuhrman
faq329-6766

 

[gmmastros]

varchar(MAX) is eqiv to varchar(8000) or a max of 8000 characters

nvarchar(MAX) is equiv to nvarchar(8000) or a max of 4000 characters because of the 2 byte storage per character to support unicode.

Almost, but not quite.

varchar(max) and nvarchar(max) can store gigabytes of data (not limited to 8000 characters).


Depending on your version of SQL Server, there are up to 8 different string data types.

ASCII string data:
char
varchar
varchar(max)
text

UNICODE string data:
nchar
nvarchar
nvarchar(max)
ntext

when you use char, nchar, varchar, and nvarchar, you should ALWAYS specify a length. The length you give it identifies the number of characters (not the storage size). char and varchar max out at 8000 characters. nchar and nvarchar max out at 4000 characters.

char and nchar will always pad the data with trailing spaces. So, if you create a table that has char(8000), and store the letter 'A', sql will use 8000 bytes to store this data. The first byte will be A followed by 7999 spaces. char and nchar can have slightly better performance than varchar and nvarchar. I suspect you would only notice the difference in performance if you had several million rows in a table. The performance improvement is a bit dubious though, especially if you end up with a lot of empty space in your tables that cause you to use extra data pages to store the data. char and nchar are usually only used if you have relatively small columns where the data in each column will (almost always) fill the column. For example, state abbreviation.

text and ntext are a humongous pain to work with because many of the string handling functions do not work with them. Back in the SQL7/SQL2000 days developers complained A LOT about this problem.

varchar(max) and nvarchar(max) were the solution to this problem. varchar(max) and nvarchar(max) can store the same amount of data as text and ntext (a couple gigs worth) but all the string handling functions work with varchar(max) and nvarchar(max). If you are using SQL2005 or greater, you should NOT use text or ntext. You should use varchar(max) and nvarchar(max) instead.

In SQL Server (as in other database engines) there are certain limitations. Behind the scenes, SQL stores data on a hard drive (obviously). But, it stores this data in 8 kilobyte chunks (called pages). 8K = 8192 bytes. There is some overhead for each row, so the most you can store in a data page is 8060 bytes. This also happens to be the limit on the size of data in a row of a table. Since the most data you can store in a row is 8060 bytes, the maximum size for nchar and nvarchar is 4000.

text and ntext can be gigabytes big. To accomplish this, SQL Server stores a 16 byte pointer in the data row for each text/ntext column. The actual data is stored elsewhere.

varchar(max) and nvarchar(max) gets a little fuzzy (for me). I think the data is stored in the row if it fits in the row (like a varchar(20) column). If it doesn't fit, then I think a pointer is stored in the row (like it is with text and ntext).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Yes, that makes sense.

So, I need to analyze the column data and change the data types to match the data better that is coming from Access.

For the monthly DB there are 14 total MDBs that each have a Monthly and Monthly1 table in them with appoximately 4,272,088 rows in each table.

Like OFFICE_ID is a 2 digit integer and can be left as (smallint,null), whereas PREFIX is a filenumber that will never be more than 8 characters (ie. a12345678) and should be changed to CHAR(8) to make better use of the 8060 character row size limitation and hopefully tighten the data that is stored to disk.

All this iformation is GREAT and is certainly helping me get a grasp of what I have been given as a task, so Thanks Again and I will give you a * for the time.

One other question, I do a little VB scripting as well and I had written a VBscript to make changes to several Excel files and found that I had to make sure that the Excel application got closed before the next file could be processed. Could OPENROWSET have a simular requirement to properly import the records from the next file?

Thanks

John Fuhrman
faq329-6766

 

[sparkbyte]

Sorry, Numbers are wrong

For the monthly DB there are 14 total MDBs that each have a Monthly and Monthly1 table in them with appoximately 4,272,088 rows in each table.

Should be 2,136,044 rows in each table or 4,200,000 combined.

Thanks

John Fuhrman
faq329-6766

 

[gmmastros]

What's the process here?

Do you plan on importing data monthly from the various access db's and importing to SQL Server? What I mean is.... Will this code will be run on a regular basis?

One other question, I do a little VB scripting as well and I had written a VBscript to make changes to several Excel files and found that I had to make sure that the Excel application got closed before the next file could be processed. Could OPENROWSET have a simular requirement to properly import the records from the next file?

I don't really know too much about Access. I know Excel likes to lock files and hold on them real tight. I also know that Access can open a database in "exclusive/single user" mode, or it can open a database in a shared/multi-user mode. This may be an issue for you, but I suspect it's not.

In the referenced thread, you mention 100+ Access databases, and now you're mentioning 14 databases. This confuses me a little. From the sounds of it, you need to regularly merge 14 access databases in to a single SQL database (probably for reporting purposes). I'm guessing that the remaining databases are historical, so you only need to get the data once from each of these.

In the other thread, you show code that is something like...

Insert Into Table(columns) Select (columns) from OpenRowset

To rule out an Access issue, I encourage you to select the data in to a temp table, just for debugging purposes. Like this...

Select ColumnList Into #Temp From OpenRowset(....)

You'll know this works if you can then do:
Select * From #Temp

If you can get the data in to a temp table using the Select Into syntax, then you definitely have a data type mismatch issue.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

What is happening here is this.

I have been tasked with migrating all data that is currently in the Access Databases to MS SQL.

There are a total of 366 MDB files spread access the various departments. Most of these are either not being used now or are left overs from a previous conversion project(before my time) or previous versions of the DB Application.

After attempting to eliminate the "junk" I am left with somewhere around 100 or so files that will need to be migrated into SQL server. Of those files a dozen or so are the Accual Access Applications that are in use.

So, right now I am attempting to write a few scripts to import the data from the "archives" of the dozen or so Access Applications.

Once the data has been put into SQL server I will then start to modify the Access Applications forms, and reports to point to SQL server instead of using the local DBs and convert the Access queries to SQL queries on MS SQL. In short convert the Access Applications to Access Projects.

One other challenge that I have found with this project is that many of the Access Applications use linked tables to pull data from each other.

The two largest of the Access Applications are Monthly and eWARTZ both of which don't link to other data sources, but other Access Applications link to them.

Because I have not had much experience with Applications built on Access the difference in data types has been a bit of a problem, but I have been able to solve most.

The SSMA (SQL Server Migration Assistant) from MS has been a useful tool but lacks the ability to import multiple files into the same tables. It wants to overwrite the previous data. (truncate the tables before starting the next file.)

So I started down the OPENROWSET road to be able to append the data from each of the Access DBs semi-manualy. As I got further into this I found that as the Access Application grew so did the tables. The eWARTZ Application has had about 8 columns added to it. So I have a table on the SQL server that has ALL Columns but need to adjust the insert statement to account for the older archived data and pad the archived data with defaault values for the missing column data during the insert.

Hope this helps explain what I am trying to get accomplished.

Thanks so much for your help.

Thanks

John Fuhrman
faq329-6766

 

[gmmastros]

I see.

Have you looked in to using SQL Server Integration Services. This is exactly what it was built to handle.

For a variety of ways to import data, take a look at this:

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/title-12

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

I started to use it but got frustrated with the interface and started to code it instead.

I will check the link.

Thanks..

Thanks

John Fuhrman
faq329-6766

 

[sparkbyte]

Checked out the example.

I was able to get SISS to work and did a good job of importing the data from a single archive file. Problem I had was that I couldn't find any good examples of importing data from multiple files.



Thanks

John Fuhrman
faq329-6766

 

[gmmastros]

onpnt (the author of that blog) is a good friend of mine. I'm certain that he would respond if you posted a comment on the blog. He knows SSIS inside and out. If anyone can help, it's him. I happen to know he's gone home for the day, but you could probably expect a comment from him by mid-morning tomorrow.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Thanks !!! Shot him a copy of the discription I gave for the project and his suggestion for SISS was simple and sweet. I was able to get it to work in about 15 minutes from what I had already tried. Just had to add his suggestion to my exising project and rerun it.

I can see I am going to have to invest in a book on SISS for some of the things I'd like it to do.

Thanks

John Fuhrman
faq329-6766