Speeding up Data Cutover from Access to SQL-S

Question

Speeding up Data Cutover from Access to SQL-S

Lester Vincent

Ten Centuries

Points: 1227
More actions
May 21, 2006 at 6:15 pm

#76530

Hi people.   Im sure many of you have done this before, and I wonder if you can offer some advice on what worked for you.
I have prepared an SQL-S database (SQL Server 2000) to take over from an Access 2000 application which has been running 'Live' for 18 months and has outgrown reasonable Access limitations (very slow now over a multi-workstation network of users).
I have synchronised all of the SQL tables to receive the Access table contents (about 150 tables) and all is working OK, if I use a DTS package to import from the Acess tables.
As my client is dependant almost fully on the Access application, I cannot have the system down for any length of time to perform the cutover.
I therefore have to devise a process of
(a) deleting/truncating the test data from all SQL-S tables, then
(b) importing the 'Live' data from the 'currently used Access tables.   A small number of these tables have more than 150,000 rows, but all have to be imported in a short down-time period.
I have created and saved a DTS package to do the importing, and this can be scheduled to run after-hours if necessary, or on a Saturday morning.
The problem is ...
Is there a simple way, within a SPROC, to loop through all tables in the SQL-S database to delete the contents of each table?
I have looked at the system stored procedure, 'sp_tables' and it creates a nice rowset of all table-names and table-types.   But how does one use a WHILE statement to loop through this rowset and perform a TRUNCATE TABLE process.
Also, does this "empty-and-refill tables" process cause, in SQL-S, the same bloating effect as it does in Access?   If so, what is the SQL-S version of the Access Compact/Repair method?
I realise that the above could be a dangerous process to leave lying around in a database, and intend to delete it after this one-time necessary step.
If you prefer to contact me direct, instead of floating ideas on this within the forum, please use my personal email address, which you will find in my profile in this forum.
Best wishes,

Lester Vincent
Sydney

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Charles Wannall Ten Centuries Points: 1003 More actions · Answer 1

If you know how to handle CURSORs in SQL Servers it's no problem.

Suggestion: Instead of doing the fanciest approach, there's another kind of elegant. Unless the tables you need to empty change constantly (making it essential to loop through the most current list of tables), just do a one time query against sysobjects to get your list of tables, slap that in Word, use some find and replace operations to create the list of Delete * From [xxxxxx] statements and include that in your sproc.

You'll be through in about 10 minutes and get on with your life. I'm not at a server right now, so I can't check this, but I think the object type you'll be looking for is "u", indicating "user table".

Hell, you can even put the text in with your query, come to think of it:

use mydatabase

select 'Delete * From [' + o.name + ']' from sysobjects o

where xtype = 'u'

The resulting output should be the delete statements you need. Mine gave me this:

Delete * From [tblUserRole]

Delete * From [sysdiagrams]

Delete * From [tblEmailListMaster]

Delete * From [Mission Requirement Master Revision]

Delete * From [tblEmailList]

Delete * From [ConversionFactors]

Delete * From [Project]

etc

HTH

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 2

Thanks Charles.

Your solution sounds so simple it has me worried, and I don't know why. Maybe I'm just being ultra-careful in some unfamiliar territory.

Since my posting, I found the sysobjects table and created a view which says

SELECT name FROM sysobjects

WHERE (type='U') AND (NOT(name=N'dtproperties'))

ORDER BY name.

Simply selecting on type='U' left me with a list that included the dtproperties table. Don't know yet what it contains, but it isn't one I created.

This produced a rowset of the table names I wanted to clear.

I am now going to try using a WHILE statement and execute a dynamic query like 'DELETE * FROM '+ @Table

I need to test its operations thoroughly before the big cutover event, so when I execute it, all has been tested and is in place, ready to go.

I will try what you have suggested too, and should get it done from there.

I do understand how Cursors work, but have been reading so much against them that I try to avoid them wherever possible.

Do you know if SQL tables cause the database to bloat like Access does, when tables are emptied and refilled?

If so, is there anything like the Access Compact/Repair method in SQL Server?

Again thanks.

Lester Vincent

Sydney

Charles Wannall Ten Centuries Points: 1003 More actions · Answer 3

There's not a one-button equivalent, but check out these topics:

dbcc shrinkdatabase

dbcc shrinkfile

backup (study LOG and NO_TRUNCATE options)

These will get you started. Our dba sets up our official backups, so I'm not practiced enough with to tell you exactly what you need. However, I'll look for a writeup (there are lots on the Internet) and post one here if it's clear and complete.

Charles Wannall Ten Centuries Points: 1003 More actions · Answer 4

While we're at it, start here:

http://www.sqlservercentral.com/search/turbo.asp

In the search textbox, just type shrinkdatabase and you'll find a couple of articles and quite a few discussions.

Rick Heiges SSChasing Mays Points: 660 More actions · Answer 5

There is currently an Access option in the SQL Server Migration Assistant in beta. The current SSMA is only targeted at Oracle DBs. Although I have not tested it out personally, I would think that there may be some useful tools for you there.

Hope this helps!

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 6

Lester,

If all your tables are linked to the Access Front-end then the following routine will delete all transactions.

Function ZapTables()

On Error Resume Next ' Gets over invalid table name ie Currency

Dim rst As Recordset

Dim tbl As TableDef

Dim t As Double

Dim MFile As String

t = Timer

For Each tbl In CurrentDb.TableDefs

If tbl.Connect <> "" Then

Set rst = CurrentDb.OpenRecordset("select * from msysobjects where name='" & tbl.Name & "'")

If Nz(rst("connect"), "") <> "" And Nz(rst("database"), "") = "" Then

MFile = rst("Name")

Debug.Print rst("Name")

DoCmd.RunSQL "Delete * from " & rst("Name")

End If

Next tbl

t = Timer - t

MsgBox "Zap Complete in " & Round(t, 2)

End Function

Linked SQL tables do not bloat the Access database quite as much.

Richard

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 7

Thanks Charles, Richard and Rick for your terrific help on this.

I'm on the way with it now, using a SPROC which

(a) gets a rowset of tablenames from sysobjects, (as per my reply to Charles Wannall),

(b) fills a 2-column temp table with the table names (the other column is 'ID int identity".

(c) using a While statement and a row counter, loops through the temp table, assembling a dynamic query,ie

@Query='TRUNCATE TABLE '+CHAR(39)+TableName+Char(39)

WHERE @RowCount=ID'

EXEC @Query

@RowCount=@RowCount+1

That'll work for me, but I have commented out the "EXEC @Query" line till I am ready to use it !!!

Richard, thanks for the great bit of VBA Code, but in my case it would not apply, as none of my tables are linked to SQL Server. That is a delberate decision, for scalability reasons.

Thanks for the clue on "ShrinkDatabase", Charles. I had forgotten all about that one.

My sincere thanks to you all. What a great forumThis is!

Lester Vincent

Sydney