Speeding up Data Cutover from Access to SQL-S
-
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
-
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
-
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
-
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.
-
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.
-
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!
-
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
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
-
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply