Managing change in SQL Server applications requires a process that protects data integrity & maximizes production uptime, while providing flexibility for new development. I would propose the following as a minimum configuration to accomplish these goals.
Create 3 database environments -- one for production, one for development, one for QA. Only the DBA has direct access to production or QA. The developer has direct access to only the development db. These 3 db's can sit on separate servers, or alternately the development and QA db's can run on named instances on the same server if size & activity is modest.
Create 3 application front-ends -- one for each database; containing some code, config file or other means to connect the front-end to the correct db; having some method to prominently display the connected server & database when it is not connected to the production db. (The front-end is really a single design, it is only the connection string that varies between the 3 copies.)
To begin, the DBA takes a backup from production & restores it to both the development & QA servers.
The developer connects the DEV front-end to the DEV db, writes T-SQL scripts to make any changes to the design, and updates the front-end for those changes. It is possible to use the GUI to generate the change script, but the GUI is never used to actually apply those changes - that is accomplished with the script. The developer also prepares documentation for the desired change in the front-end.
At this point, the developer may decide to share the DEV copy with select stakeholders, especially if the change is complex, for feedback & verification that the change meets the requirements. This step is commonly referred to as user acceptance testing.
When the developer verifies the change in that environment, they pass the change script to the DBA who reviews the script, and then applies it to the QA db. The developer will then connect the QA front-end to the QA db and verify the change.
The QA person will then evaluate & verify the change using the documentation provided by the developer. Upon their sign-off, the app is ready for release.
The developer notifies all users that the database update will commence.
The DBA will backup all 3 db's and then apply the script to production. Following that, the developer will connect the production front-end to the production db & verify. The developer publishes the new front-end & notifies the users that the update is complete.
The DBA takes a fresh production backup & restores to both DEV and QA, and the developer reconnects DEV to DEV and QA to QA. The DBA may also pass a copy of the backup file to the developer, if the 4th environment is used as follows.
A 4th possible environment is for the developer to maintain a copy of the DEV db on their PC, using e.g. the MSSQL Developer Edition along with a 4th copy of the front-end. This provides ultimate flexibility of design; the developer restores the backup to that system & can use that as a test-bed for feasibility studies of design changes without impacting the true DEV environment.
Throughout all the above processes, source control is used to maintain matching copies of the front-end & database scripts to allow rolling back changes that have unintended consequences.
Sunday, April 26, 2009
Saturday, April 11, 2009
SQL scripts without sp_executesql
There is a totally useless scripting option in SSMS 2005 that you think might mimic the behavior of EM 2000 but it does quite the contrary.
Using Enterprise Manager in MSSQL 2000 you could generate a script to DROP and / or CREATE an object. The DROP statement used the "if exists" syntax, so you could replace an object with a new version. If you wanted to change an object (except for tables) you generate just the CREATE and then change that one word to ALTER. Simple stuff.
However, in SSMS there is an option "Include IF NOT EXISTS clause" which does several totally unnecessary things:
First of all, you will see
EXEC dbo.sp_executesql @statement = N'
all your statements are here
'
If you choose Create, it tests IF NOT EXISTS followed by CREATE, but you just scripted an object that exists, so it does nothing.
If you choose Modify, it tests IF NOT EXISTS followed by ALTER, but how can you alter an object that does not exist?
What a mess.
If you want to generate a script like 2000, then you need to turn that option OFF, and then separately generate the scripts for the DROP and the CREATE, and put them together.
Using Enterprise Manager in MSSQL 2000 you could generate a script to DROP and / or CREATE an object. The DROP statement used the "if exists" syntax, so you could replace an object with a new version. If you wanted to change an object (except for tables) you generate just the CREATE and then change that one word to ALTER. Simple stuff.
However, in SSMS there is an option "Include IF NOT EXISTS clause" which does several totally unnecessary things:
First of all, you will see
EXEC dbo.sp_executesql @statement = N'
all your statements are here
'
If you choose Create, it tests IF NOT EXISTS followed by CREATE, but you just scripted an object that exists, so it does nothing.
If you choose Modify, it tests IF NOT EXISTS followed by ALTER, but how can you alter an object that does not exist?
What a mess.
If you want to generate a script like 2000, then you need to turn that option OFF, and then separately generate the scripts for the DROP and the CREATE, and put them together.
Saturday, April 04, 2009
SQL - list columns in a table
I have gotten into the habit of using either sp_help or sp_columns to view a list of column names, for example
sp_help 'MyTable'
or
sp_columns 'MyTable'
which are much faster than using the Object Explorer in SSMS to drill-down thru the database, table and finally the columns. The downside of these sp's is that they display much more info than usually needed; I just want a list of columns & data types etc.
I created a new stored procedure wm_columns, click the link to view.
I began with sp_columns since it returns a single record set. Then, after looking in BOL I created a temp table to capture the output, and then execute the sp to populate the table:
DECLARE
@sql nvarchar(4000)
SET NOCOUNT ON
CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
DATA_TYPE smallint,
[TYPE_NAME] sysname,
[PRECISION] int,
[LENGTH] int,
SCALE smallint,
RADIX smallint,
NULLABLE smallint,
REMARKS varchar(254),
COLUMN_DEF nvarchar(4000),
SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(254),
SS_DATA_TYPE tinyint
)
INSERT INTO #temp
EXEC sp_columns @Table
Once that it done, it is simple to select only the columns that interest me:
SELECT
COLUMN_NAME,
[TYPE_NAME],
[PRECISION],
[LENGTH],
SCALE,
NULLABLE,
COLUMN_DEF
FROM
#temp
. . . but it would be nice to be able to sort the list alphabetically by the field name, so after populating the temp table, I create a unique index based on an argument @SortAlpha to my stored procedure:
EDIT ON 04/09/2009: There was a bug in this part of the code, the alpha sort did not work. Fixed here & also in the download file . . .
SET @sql = 'CREATE UNIQUE CLUSTERED INDEX UQ_temp ON #temp (' +
CASE @SortAlpha
WHEN 0
THEN 'ORDINAL_POSITION'
ELSE 'COLUMN_NAME'
END + ')'
. . .END OF EDIT
EXEC sp_executesql @sql
. . . which causes the ouput to be sorted according to the argument.
sp_help 'MyTable'
or
sp_columns 'MyTable'
which are much faster than using the Object Explorer in SSMS to drill-down thru the database, table and finally the columns. The downside of these sp's is that they display much more info than usually needed; I just want a list of columns & data types etc.
I created a new stored procedure wm_columns, click the link to view.
I began with sp_columns since it returns a single record set. Then, after looking in BOL I created a temp table to capture the output, and then execute the sp to populate the table:
DECLARE
@sql nvarchar(4000)
SET NOCOUNT ON
CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
DATA_TYPE smallint,
[TYPE_NAME] sysname,
[PRECISION] int,
[LENGTH] int,
SCALE smallint,
RADIX smallint,
NULLABLE smallint,
REMARKS varchar(254),
COLUMN_DEF nvarchar(4000),
SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE varchar(254),
SS_DATA_TYPE tinyint
)
INSERT INTO #temp
EXEC sp_columns @Table
Once that it done, it is simple to select only the columns that interest me:
SELECT
COLUMN_NAME,
[TYPE_NAME],
[PRECISION],
[LENGTH],
SCALE,
NULLABLE,
COLUMN_DEF
FROM
#temp
. . . but it would be nice to be able to sort the list alphabetically by the field name, so after populating the temp table, I create a unique index based on an argument @SortAlpha to my stored procedure:
EDIT ON 04/09/2009: There was a bug in this part of the code, the alpha sort did not work. Fixed here & also in the download file . . .
SET @sql = 'CREATE UNIQUE CLUSTERED INDEX UQ_temp ON #temp (' +
CASE @SortAlpha
WHEN 0
THEN 'ORDINAL_POSITION'
ELSE 'COLUMN_NAME'
END + ')'
. . .END OF EDIT
EXEC sp_executesql @sql
. . . which causes the ouput to be sorted according to the argument.
Wednesday, April 01, 2009
Linking Access to SQL Server
I have an Access app that links to SQL Server, and when I tried to attach a new SQL table in code I ran into a snag - the linked table would forget the user name & password, so Access would pop up one of those login dialog boxes.
I compared the value of the table Attributes (a long integer) & discovered that my new table was different. A quick trip to Access Help was truly helpful - I was missing the attribute dbAttachSavePWD so basically here is what worked (the fix is in bold):
The Or operator sets the appropriate flag for Access to save the info.
I compared the value of the table Attributes (a long integer) & discovered that my new table was different. A quick trip to Access Help was truly helpful - I was missing the attribute dbAttachSavePWD so basically here is what worked (the fix is in bold):
Sub SQL_AttachOne(TableName As String)
Dim db As DAO.Database, tdf As DAO.TableDef
Const s = "the connection string goes here"
Set db = CurrentDb
Set tdf = db.CreateTableDef
With tdf
.Name = TableName
.SourceTableName = TableName
.Connect = s
.Attributes = .Attributes Or dbAttachSavePWD
End With
db.TableDefs.Append tdf
End Sub
The Or operator sets the appropriate flag for Access to save the info.
Friday, March 27, 2009
Loop thru SQL tables
"How can I loop through all the tables in a SQL Server database, and run T-SQL on each table where the name matches a pattern, without using cursors?"
This question was recently posted at a SQL forum. Here is the solution. . .
SQL Server has a system stored procedure sp_tables that displays all the tables in the database, and has an optional parameter that allows filtering the results. For example, to list all tables that have the prefix 'tbl' you would use:
sp_tables 'tbl%'
To use these results in subsequent processing, we need to save the output to a table. After looking up the sp_tables procedure in BOL, we can create a table to hold the results, and then execute the procedure to populate the table:
CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
TABLE_TYPE varchar(32),
REMARKS varchar(254)
)
INSERT INTO #temp
EXEC sp_tables 'tbl%'
Now we need a strategy for looping thru these records & processing each table one-by-one - - We can add a flag to the temp table to mark when each record is processed:
EXEC sp_executesql N'ALTER TABLE #temp ADD Processed bit NOT NULL DEFAULT (0)'
Now, we select the first table name, run our T-SQL on that table, and mark the flag as being processed. The WHILE loop continues as long as there are any records that have not been processed. At the beginning of our code we need to insert the line:
DECLARE @TABLE_NAME sysname
and then we can write our loop:
WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
PRINT @TABLE_NAME
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END
...which simply prints the name of each table, where the table name matched our filter.
To execute a real SQL statement against each table, we need to add another variable at the top of our code:
DECLARE @sql nvarchar(4000)
and then the loop is written like this:
WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
-- replace next line with your SQL statement
SET @sql = 'SELECT COUNT(*) FROM ' + @TABLE_NAME
EXEC sp_executesql @sql
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END
...which prints the number of rows in the table, for each table where the name matched our filter.
You can view the complete code here:
SQL_Loop_thru_tables
This question was recently posted at a SQL forum. Here is the solution. . .
SQL Server has a system stored procedure sp_tables that displays all the tables in the database, and has an optional parameter that allows filtering the results. For example, to list all tables that have the prefix 'tbl' you would use:
sp_tables 'tbl%'
To use these results in subsequent processing, we need to save the output to a table. After looking up the sp_tables procedure in BOL, we can create a table to hold the results, and then execute the procedure to populate the table:
CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
TABLE_TYPE varchar(32),
REMARKS varchar(254)
)
INSERT INTO #temp
EXEC sp_tables 'tbl%'
Now we need a strategy for looping thru these records & processing each table one-by-one - - We can add a flag to the temp table to mark when each record is processed:
EXEC sp_executesql N'ALTER TABLE #temp ADD Processed bit NOT NULL DEFAULT (0)'
Now, we select the first table name, run our T-SQL on that table, and mark the flag as being processed. The WHILE loop continues as long as there are any records that have not been processed. At the beginning of our code we need to insert the line:
DECLARE @TABLE_NAME sysname
and then we can write our loop:
WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
PRINT @TABLE_NAME
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END
...which simply prints the name of each table, where the table name matched our filter.
To execute a real SQL statement against each table, we need to add another variable at the top of our code:
DECLARE @sql nvarchar(4000)
and then the loop is written like this:
WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
SET @TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE Processed = 0)
-- replace next line with your SQL statement
SET @sql = 'SELECT COUNT(*) FROM ' + @TABLE_NAME
EXEC sp_executesql @sql
UPDATE #temp SET Processed = 1 WHERE TABLE_NAME = @TABLE_NAME
END
...which prints the number of rows in the table, for each table where the name matched our filter.
You can view the complete code here:
SQL_Loop_thru_tables
Friday, March 13, 2009
Tracking Access Button Clicks
I am working with an older Access application that has almost 500 command buttons, and our task is to determine which features are still being used & which ones can be retired. Hopefully we'll recover screen space, plus improve the maintenance by removing obsolete queries & reports.
Of course, asking the users which features they are using is one option, but with over 100 users that is not really practical.
So we created some code to track button clicks, and using the Screen object in Access makes that quite simple. Basically we created a module to save the click info to a table, and then used Word to mass-update the code modules to add a line to invoke the tracking code. More on that later.
The ClickTracker code:
First, we test if the user clicked a command button. This is necessary because you can have a click event on a list box, combo box or even the form itself. The test looks like this:
If Screen.ActiveControl.ControlType = acCommandButton Then
' ( code goes in here )
End If
Then we can get the relevant info:
The form name is
Screen.ActiveForm.Name
The button name is
Screen.ActiveControl.Name
...and the button caption is
Screen.ActiveControl.Caption
The complete module can be viewed here. ( see * and ** below )
Part 2 - adding that line of code in 500 places...
With that many command buttons, we needed a quick way to insert that code. Easy. Select the text of the entire code module & copy / paste into Word. Then you can do a Find & Replace All, using some of the special characters available in Word:
Find this: _Click()
Replace with: _Click()^p^tClickTracker
...which will insert a new line right at the beginning of every click event to invoke the ClickTracker code. Then you just select all the text from Word & paste it back into the code module & overwrite the old version.
----
* our table uses an Autonumber for the first field, that why we skip rs.Fields(0). The other fields can be text(64) because that is the max length of a form or control name in Access.
** our version includes calls to custom functions 1: GetUser() calls the Windows API to get the ID of the Windows user, and 2: DateNoTime() strips off the time portion from Now() so we are saving only the date, not the time. You don't need these for the rest of it to work, but you do need to modify the code to skip those last two fields.
Of course, asking the users which features they are using is one option, but with over 100 users that is not really practical.
So we created some code to track button clicks, and using the Screen object in Access makes that quite simple. Basically we created a module to save the click info to a table, and then used Word to mass-update the code modules to add a line to invoke the tracking code. More on that later.
The ClickTracker code:
First, we test if the user clicked a command button. This is necessary because you can have a click event on a list box, combo box or even the form itself. The test looks like this:
If Screen.ActiveControl.ControlType = acCommandButton Then
' ( code goes in here )
End If
Then we can get the relevant info:
The form name is
Screen.ActiveForm.Name
The button name is
Screen.ActiveControl.Name
...and the button caption is
Screen.ActiveControl.Caption
The complete module can be viewed here. ( see * and ** below )
Part 2 - adding that line of code in 500 places...
With that many command buttons, we needed a quick way to insert that code. Easy. Select the text of the entire code module & copy / paste into Word. Then you can do a Find & Replace All, using some of the special characters available in Word:
Find this: _Click()
Replace with: _Click()^p^tClickTracker
...which will insert a new line right at the beginning of every click event to invoke the ClickTracker code. Then you just select all the text from Word & paste it back into the code module & overwrite the old version.
----
* our table uses an Autonumber for the first field, that why we skip rs.Fields(0). The other fields can be text(64) because that is the max length of a form or control name in Access.
** our version includes calls to custom functions 1: GetUser() calls the Windows API to get the ID of the Windows user, and 2: DateNoTime() strips off the time portion from Now() so we are saving only the date, not the time. You don't need these for the rest of it to work, but you do need to modify the code to skip those last two fields.
Access subform VBA
Forms behave differently when they are used as subforms, and this can be a challenge when using VBA. Normally, when you load a form, Access adds it to the Forms collection. So if you open a form MyForm then
Forms.Count = 1
and you can refer to a control on that form using:
Forms!MyForm!MyControl
However, if that form contains a subform, MySubform, then
Forms.Count = 1
and this will fail:
Forms!MySubform!MySubformControl
So if you want to refer to a control on the subform, you need to use:
Forms!MyForm!TheSubformControl.Form!MySubformControl
Note that TheSubformControl is the name of the subform control on MyForm that contains the form MySubform, not the name of the embedded form. Nowhere do you see the actual name of the form that is being used as a subform.
Confusing? You bet. If you use the Expression Builder it can allow you build the incorrect version, so you need to make sure to drill down through the main form to the subform.
Let's say that you want to invoke code on the subform when an event occurs on the main form - for example, when you navigate to a different record on the main form, you want to set the value of a combo box on the subform & then run its AfterUpdate code. Due to the fact that the subform properties & methods are not exposed, in the Form_Current event on the main form you might use this:
Forms!MyForm!TheSubformControl.Form!MySubformCombo = DCount("*","MyTable")
Forms!MyForm!TheSubformControl.Form.MySubformCombo_AfterUpdate
But wait - that doesn't work (yet). Why? The answer is quite simple: Scope.
By default, code in standard modules are declared as Public, so they can be accessed from anywhere in the application. But code-behind in forms is declared as Private. So in the subform code, all you have to do is change this
Private Sub MySubformCombo_AfterUpdate()
'
End Sub
to this:
Public Sub MySubformCombo_AfterUpdate()
'
End Sub
...and it will work as expected.
Forms.Count = 1
and you can refer to a control on that form using:
Forms!MyForm!MyControl
However, if that form contains a subform, MySubform, then
Forms.Count = 1
and this will fail:
Forms!MySubform!MySubformControl
So if you want to refer to a control on the subform, you need to use:
Forms!MyForm!TheSubformControl.Form!MySubformControl
Note that TheSubformControl is the name of the subform control on MyForm that contains the form MySubform, not the name of the embedded form. Nowhere do you see the actual name of the form that is being used as a subform.
Confusing? You bet. If you use the Expression Builder it can allow you build the incorrect version, so you need to make sure to drill down through the main form to the subform.
Let's say that you want to invoke code on the subform when an event occurs on the main form - for example, when you navigate to a different record on the main form, you want to set the value of a combo box on the subform & then run its AfterUpdate code. Due to the fact that the subform properties & methods are not exposed, in the Form_Current event on the main form you might use this:
Forms!MyForm!TheSubformControl.Form!MySubformCombo = DCount("*","MyTable")
Forms!MyForm!TheSubformControl.Form.MySubformCombo_AfterUpdate
But wait - that doesn't work (yet). Why? The answer is quite simple: Scope.
By default, code in standard modules are declared as Public, so they can be accessed from anywhere in the application. But code-behind in forms is declared as Private. So in the subform code, all you have to do is change this
Private Sub MySubformCombo_AfterUpdate()
'
End Sub
to this:
Public Sub MySubformCombo_AfterUpdate()
'
End Sub
...and it will work as expected.
Saturday, February 28, 2009
Compact and Repair Database
MS-Access files tend to grow over time, for a variety of reasons such as
NOTE: It is essential for all users to exit the database, and to make a backup copy of the file before performing the following...
One solution is to do regular maintenance on the database. In Access versions thru 2003, you can use
Tools ~ Database Utilities ~ Compact & Repair Database
which will in most cases shrink the file considerably. The typical reduction can be 10-20%, but can much greater if the Compact has not been done for a long time. In fact, I've seen one case where there was 100:1 reduction for a file that had never been compacted.
An alternate solution is to turn on the option for Auto-Compact, using
Tools ~ Options ~ General, and check the box Compact on Close
...however this option may crash the app if you are running on Terminal Server and/or using Access replication and/or the Access runtime version.
So, how often to perform this task? Good question. It depends on the list of reasons above. You could actually write some code to check the file size, compare it to some arbitrary limit, and then alert the user if the size exceeds that limit. Here is some sample code to check the file size:
- adding many new records
- large number of user edits
- running action queries (insert, update, delete)
- design changes, especially to forms & reports
- temp tables that are emptied & repopulated
- using the Name AutoCorrect options
- Access replication
- WorkGroup Security with encryption
NOTE: It is essential for all users to exit the database, and to make a backup copy of the file before performing the following...
One solution is to do regular maintenance on the database. In Access versions thru 2003, you can use
Tools ~ Database Utilities ~ Compact & Repair Database
which will in most cases shrink the file considerably. The typical reduction can be 10-20%, but can much greater if the Compact has not been done for a long time. In fact, I've seen one case where there was 100:1 reduction for a file that had never been compacted.
An alternate solution is to turn on the option for Auto-Compact, using
Tools ~ Options ~ General, and check the box Compact on Close
...however this option may crash the app if you are running on Terminal Server and/or using Access replication and/or the Access runtime version.
So, how often to perform this task? Good question. It depends on the list of reasons above. You could actually write some code to check the file size, compare it to some arbitrary limit, and then alert the user if the size exceeds that limit. Here is some sample code to check the file size:
Function GetMyFileSize() As Long
' this requires a reference to
' Microsoft Scripting Runtime
' c:\windows\system32\scrrun.dll
Dim fso As FileSystemObject
Dim oFolder As Folder, oFile As File
'
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(CurrentProject.Path)
Set oFile = oFolder.Files(CurrentProject.Name)
GetMyFileSize = oFile.Size
Set oFile = Nothing
Set oFolder = Nothing
Set fso = Nothing
End Function
Sunday, February 15, 2009
Access replicas and runtimes
We recently completed a lengthy project, migrating a replicated MS-Access 2003 application to a client/server model using Access as the front-end and SQL Server 2005 as the database. The application had previously been modified as necessary to utilize the Access 2003 runtime on terminal server.
There are a few features in the app that display query results in a datasheet view, and every one of those queries (even queries that are not updateable) is opened as read-only to prevent accidental changes, for example:
DoCmd.OpenQuery "MyQuery", acViewNormal, acReadOnly
Since the Access runtime leaves out all the built-in toolbars, a custom toolbar was added to allow the user to filter & sort the data for analysis. After the user completes their analysis & closes the query, any filter or sort is discarded by the runtime, so subsequent views return to the predefined values. At least, that is what we expected.
Final testing of the client / server version of the app revealed that query filter / sort operations, and even changing column widths, upon close would prompt to the user "save design changes?" how could that happen with the runtime & the read-only status?
As it turns out, the answer is quite simple. In the replicated system, changes could only be made in the design master. Any filter / sort etc. in the replica disappeared when the query is closed. However, the new front-end is not replicated, and so although the Access runtime prevents access to the full design environment, we are exposing the filter / sort capability to the user & therefore they can persist changes to their query.
Now, is that defect or a feature? Each user has their own copy of the front-end file, so they are now able to customize their vision of each query for their needs. The data itself continues to be protected due to the read-only status. That sounds like a feature.
Thursday, February 05, 2009
Identity Crisis, part 2
Talk about being lucky.
We reviewed the tables that were almost out of headroom as far as PK identity values hitting the limit of an int, and found that for the two tables that were almost out of room, the PK's were not FK's but were simply in there to satisfy the basic rule that every table should have a primary key.
These tables had only 20-25,000 records; it was only the fact that they were migrated from a replicated Access database that resulted in the very large autonumbers.
Therefore, for both tables we could just go in there & drop the existing identity column & add a new identity column with a seed of 1 and increment of 1. Problem solved.
We reviewed the tables that were almost out of headroom as far as PK identity values hitting the limit of an int, and found that for the two tables that were almost out of room, the PK's were not FK's but were simply in there to satisfy the basic rule that every table should have a primary key.
These tables had only 20-25,000 records; it was only the fact that they were migrated from a replicated Access database that resulted in the very large autonumbers.
Therefore, for both tables we could just go in there & drop the existing identity column & add a new identity column with a seed of 1 and increment of 1. Problem solved.
Subscribe to:
Posts (Atom)