[dba-SQLServer] Preparing for upsizing

Robert L. Stewart rl_stewart at highstream.net
Tue Jan 24 10:37:13 CST 2006


Actually Erwin, I use Erwin (now called AllFusion Data Modeler from 
Computer Associates) to do all my upsizing.  :-)  Yes, it is an 
expensive tool, about $3,000 a seat.  But, it is fast and works great 
with a number of databases.  I can redo a design in about 3 or 4 days 
from Access to almost any database.

At 11:13 PM 1/23/2006, you wrote:
>Date: Mon, 23 Jan 2006 21:48:24 +0100
>From: "Erwin Craps - IT Helps" <Erwin.Craps at ithelps.be>
>Subject: Re: [dba-SQLServer] Preparing for upsizing
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID:
>         <46B976F2B698FF46A4FE7636509B22DF1B62D7 at stekelbes.ithelps.local>
>Content-Type: text/plain;       charset="us-ascii"
>
>
>Pfff, a month work, getting discouraged already.
>Altough I'm aware theres no magic tool.
>
>I was hoping to migrate the app, without to much change to the code.
>Yeah yeah, I still believe in fairytales.
>This app is really the app for which I lost sleep, blood, sweat and my
>(good?) sense of humor. It's has a huge amount of code and special
>tricks in it and its the largest database I have (about 600 MB,
>1,608,014 records only in the main table).
>
>And the funny part is that not a single customer works with it.
>We at our office are the only ones using it with 3 people, that is
>except for the webclient)
>
>I know I will need to upsize one day, but I'm gonna get gray hair of it.
>Whats the MDB file size limit again? Maybe I can pull it a couple of
>years further...
>
>Maybe in a couple of years MS stops with MDB files and creates us a
>wonderfull full analytic and upsize wizard that does the job over a
>cofee or two?!
>
>Erwin
>
>
>
>
>-----Oorspronkelijk bericht-----
>Van: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] Namens Jim Lawrence
>Verzonden: maandag 23 januari 2006 18:46
>Aan: dba-sqlserver at databaseadvisors.com
>Onderwerp: Re: [dba-SQLServer] Preparing for upsizing
>
>Erwin:
>
>Three months ago I upsized a legacy system. Before the final process was
>ran, it took almost a month to build and test the importing and data
>manipulation routines. The structure needed some better functionality,
>better normalisation, some new tables, loss some tables and a thorough
>testing as once the process was started on the live data it had to go
>straight through without errors.
>
>The upsize process when started, at 5:00PM Friday night, completed
>without an incident by 11:00PM and everyone was online, Monday morning.
>It was well worth the preparation time.
>
>My 2 cents worth.
>Jim
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Erwin
>Craps
>- IT Helps
>Sent: January 23, 2006 1:12 AM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] Preparing for upsizing
>
>
>I was thinking about writing such a tool to, in order to upsize one of
>my biggest database. I'm postponing this upsize already for a year or
>two now, and I supose it will be not until after the summer holiday...
>
>
>But I was considering to first store the SQL strings in a table , run a
>grouping on it/analyse to minimize the number of queries and posibly to
>improve them (manualy), and afterwards alter the forms/reports.
>
>This to avoid to have 1 query per combo/listbox.
>
>Erwin
>
>
>
>
>
>
>
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan
>Waters
>Sent: Friday, January 20, 2006 4:10 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] Preparing for upsizing
>
>John,
>
>There's nothing quite like making your own tools!
>
>Dan
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John
>Colby
>Sent: Thursday, January 19, 2006 10:23 PM
>To: 'Access Developers discussion and problem solving';
>dba-sqlserver at databaseadvisors.com
>Subject: [dba-SQLServer] Preparing for upsizing
>
>I am preparing for upsizing a client's access database.  In order to do
>this I needed to search all the objects looking for SQL statements and
>save them as saved queries.  In order to do this with forms I created
>the following two functions:
>
>FindSQL opens each form in the database in design view, then passes the
>name of the form and the form's RecordSource off to a function
>BldSavedQuery.  It then looks for all controls in the form's control
>collection looking for combos and list boxes, and does the same with
>them.
>
>BldSavedQuery looks for issues like nothing in the SQL statement passed
>in, the "QryName" already being the name of a query or table, and if
>neither of those conditions exist, then it tries to build a named query
>def.  If it succeeds (the SQL passed in is valid and the name passed in
>is not already a saved query) it creates a saved query and passes the
>name back to FindSQL which then stores the saved query name back in
>place of the SQL statement.
>When all processing is finished, the form is then saved and closed.
>
>It works reasonably well.  The only things found so far are delimited
>lists instead of SQL statements and what appear to be saved query names
>where the saved query was deleted.  Only about 17 of those, all but one
>are delimited lists.  It took me ~ 2 hours to write / debug this code
>and saved me who knows how much time manually hunting for these things.
>
>I now have to do the same thing for reports, though there are so few I
>might just do them manually.
>
>Function BldSavedQuery(strQryName As String, strSQL As String) As String
>On Error GoTo Err_BldSavedQuery Dim tdf As DAO.TableDef Dim qdf As
>DAO.QueryDef Dim qdfs As DAO.QueryDefs Dim db As DAO.Database Dim
>lstrQryName As String
>
>     '
>     'This object has nothing in the rowsource/recordsource (probably an
>unbound form)
>     '
>     If strSQL = "" Then
>         BldSavedQuery = ""
>         GoTo Exit_BldSavedQuery
>     End If
>     Set db = CurrentDb
>     '
>     'Check to see if the strsql is the name of a table
>     '
>     On Error Resume Next
>     Set tdf = db.TableDefs(strSQL)
>     If Err = 0 Then
>         GoTo Exit_BldSavedQuery
>     End If
>     Err.Clear
>     '
>     'Check if the strSQL is a query name
>     '
>     Set qdf = db.QueryDefs(strSQL)
>     If Err = 0 Then
>         GoTo Exit_BldSavedQuery
>     End If
>     Err.Clear
>On Error GoTo Err_BldSavedQuery
>
>     lstrQryName = "q" & strQryName
>     Set qdfs = db.QueryDefs
>     Set qdf = New QueryDef
>     With qdf
>         .SQL = strSQL
>         .Name = lstrQryName
>     End With
>     qdfs.Append qdf
>     qdfs.Refresh
>     BldSavedQuery = qdf.Name
>Exit_BldSavedQuery:
>On Error Resume Next
>     If Not (qdf Is Nothing) Then qdf.Close: Set qdf = Nothing
>     Set qdfs = Nothing
>     If Not (db Is Nothing) Then db.Close: Set db = Nothing Exit Function
>Err_BldSavedQuery:
>     Select Case Err
>     Case 3012   'Query already exists
>         BldSavedQuery = ""
>         Debug.Print "ERROR: " & lstrQryName & " already exists, but this
>control has a SQL statement in the rowsource"
>         Resume Exit_BldSavedQuery
>     Case 3129   'Not an SQL statement - mostly delimited lists or
>deleted
>saved query names
>         BldSavedQuery = ""
>         Debug.Print "ERROR: " & lstrQryName & " not a sql statement in
>the rowsource but not a saved query or table either"
>         Debug.Print vbTab & "Rowsource: " & strSQL
>         Resume Exit_BldSavedQuery
>     Case Else
>         MsgBox Err.Description, , "Error in Function
>basFindSQL.BldSavedQuery"
>         Resume Exit_BldSavedQuery
>     End Select
>     Resume 0    '.FOR TROUBLESHOOTING
>End Function
>'
>'This module will find all sql statements in objects such as forms,
>combos and list boxes 'and save them as saved queries in the name
>format:
>'
>'qfrmName
>'qfrmname-CboName
>'qfrmName-LstName
>'
>Function FindSQL()
>On Error GoTo Err_FindSQL
>Dim db As DAO.Database
>Dim doc As DAO.Document
>Dim frm As Form
>Dim ctl As Control
>Dim strQryName As String
>     Set db = CurrentDb
>     For Each doc In db.Containers(2).Documents
>         DoCmd.OpenForm doc.Name, acDesign
>         Set frm = Forms(doc.Name)
>         strQryName = BldSavedQuery(frm.Name, frm.RecordSource)
>         If Len(strQryName) > 0 Then
>             frm.RecordSource = strQryName
>         End If
>         For Each ctl In frm.Controls
>             Select Case ctl.ControlType
>             Case acComboBox, acListBox
>                 strQryName = BldSavedQuery(frm.Name & "-" & ctl.Name,
>ctl.RowSource)
>                 If Len(strQryName) > 0 Then
>                     ctl.RowSource = strQryName
>                 End If
>             Case Else
>             End Select
>
>         Next ctl
>         DoCmd.Close acForm, doc.Name, acSaveYes
>     Next doc
>Exit_FindSQL:
>On Error Resume Next
>     If Not (frm Is Nothing) Then frm.Close: Set frm = Nothing
>     If Not (db Is Nothing) Then db.Close: Set db = Nothing Exit Function
>Err_FindSQL:
>         MsgBox Err.Description, , "Error in Function basFindSQL.FindSQL"
>         Resume Exit_FindSQL
>     Resume 0    '.FOR TROUBLESHOOTING
>End Function
>
>John W. Colby
>www.ColbyConsulting.com
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list