December 30, 2009 at 10:38 am
I have a customer who is down that I'm working with that needed adjustments to their SQL tables for our application. I am not part of the programming team in the company, but handle the database work and have a mid-level working knowledge of SQL (just enough to be dangerous). With the development team I designed a series of scripts to re-map data in certain tables in SQL 2005/2008.
The issue is now I need to translate into SQL 2000 and my understanding is that it's not dramatically different and I know I'm not that far off. I have already updated "sys.objects" to "sysobjects". I'm not receiving an error for "invalid column name" for OBJECT_ID, which doesn't appear to have been a value that has remapped itself from 2000 to 2005. I'd appreciate any assistance that could be given:
-- Creation of Stored Procedure
IF EXISTS (SELECT * FROM sysobjects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[spAAModifyCode]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spAAModifyCode]
GO
CREATE PROCEDURE spAAModifyCode
-- Add the parameters for the stored procedure here
@OldCode nvarchar(2),
@NewCode nvarchar(4)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- *************************************************************************************************************
--
--
--Find and replace 3 with Segment ID
--
--
-- *************************************************************************************************************
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblAcctCode_3]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblAcctCode_3]
SET [sCodeID] = @NewCode
WHERE [sCodeID] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblAcctCode_3_UDF]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblAcctCode_3_UDF]
SET [sCodeIDf] = @NewCode
WHERE [sCodeIDf] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblAllocCodeSegTarget]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblAllocCodeSegTarget]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblAllocTempReport]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblAllocTempReport]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblAPOpenTrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblAPOpenTrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblAROpenTrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblAROpenTrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblBDWorksheetTrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblBDWorksheetTrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblBLTrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblBLTrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblComboEdit]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblComboEdit]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblCurrency]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblCurrency]
SET [sCodeIDf_3R] = @NewCode
WHERE [sCodeIDf_3R] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblDistCodeDetail]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblDistCodeDetail]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblDLTrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblDLTrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblENOpenTrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblENOpenTrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblENTrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblENTrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblEReqHistTrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblEReqHistTrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblEReqTrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblEReqTrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblFAHistoryDetail]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblFAHistoryDetail]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblFAPreTransfer]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblFAPreTransfer]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblGLBLBalance]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblGLBLBalance]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblGroup_3]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblGroup_3]
SET [sGroupCodeID] = @NewCode
WHERE [sGroupCodeID] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblGroupAssign_3]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblGroupAssign_3]
SET [sCodeID] = @NewCode
WHERE [sCodeID] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblMemTrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblMemTrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblPOTETrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblPOTETrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblPRHistEarnDetails]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblPRHistEarnDetails]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblTempBudgetHistory]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblTempBudgetHistory]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblTempPRTransfer]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblTempPRTransfer]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[tblTETrans]') AND type in (N'U'))
BEGIN
UPDATE [dbo].[tblTETrans]
SET [sCodeIDf_3] = @NewCode
WHERE [sCodeIDf_3] = @OldCode
END
END
GO
-- Creation of Table Cursor
DECLARE @OldName varchar(2),
@NewName varchar(4)
DECLARE Table_Cursor CURSOR FOR
SELECT *
FROM AA_MAPPING
OPEN Table_Cursor
FETCH NEXT From Table_Cursor INTO @OldName, @NewName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC spAAModifyCode @OldName , @NewName
FETCH NEXT From Table_Cursor INTO @OldName, @NewName
END
-- Removal of Table Cursor
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
GO
-- Removal of Stored Procedure
IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[spAAModifyCode]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spAAModifyCode]
GO
December 30, 2009 at 10:42 am
What exactly is your question?
You're "throwing" almost 250 lines of code at us and didn't even tell us what to do with it...
December 30, 2009 at 10:45 am
My apologies. The issue I'm experiencing is not knowing what field(s) I am possibly mis-translating by reworking this SQL 2005 script back to SQL 2000. The following error message is what I'm currently receiving when I run the above script on the database (and continues for each of the separate table update commands):
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'OBJECT_ID'.
Server: Msg 207, Level 16, State 3, Procedure spAAModifyCode, Line 21
Invalid column name 'object_id'.
Server: Msg 207, Level 16, State 1, Procedure spAAModifyCode, Line 28
Invalid column name 'object_id'.
December 30, 2009 at 10:54 am
Where you have sysobjects.object_id it should be sysobjects.id
It looks as if someone has tried to translate the use of the system views in SQL2005/SQL2008 into the legacy tables and forgotten that the field names don't match. The OBJECT_ID function gives the correct id value on versions of SQL Server from 2000 onwards.
December 30, 2009 at 11:02 am
I apologize if this is a novice question, but are you saying if we look at the first line that where it reads:
IF EXISTS (SELECT * FROM sysobjects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[spAAModifyCode]') AND type in (N'P', N'PC'))
It should read instead:
IF EXISTS (SELECT * FROM sysobjects WHERE sysobjects.ID = OBJECT_ID(N'[dbo].[spAAModifyCode]') AND type in (N'P', N'PC'))
Or am I reading that incorrectly? I do not see any direct reference to sysobjects.object_id in the original code. Thanks again!
December 30, 2009 at 11:08 am
Actually, you do have a direct reference to sysobjects.object_id:
Example:
SELECT * FROM sysobjects WHERE object_id
is equal to
SELECT * FROM sysobjects WHERE sysobjects.object_id
The original statement just left out the table qualifier.
December 30, 2009 at 11:34 am
Got it!
I had to reference another script I had from another one of our programmers that I remembered working whether it was 2000 or 2005/2008:
First he simplified the creation/dropping of the procedure
-- Creation of Stored Procedure
IF OBJECT_ID('sp__AAModifyCode') IS NOT NULL
BEGIN
DROP PROCEDURE sp__AAModifyCode
PRINT '<<< DROPPED PROCEDURE sp__AAModofyCode >>>'
END
GO
Then the "IF" lines became:
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[tblAcctCode_XX]') AND type in (N'U'))
December 30, 2009 at 12:09 pm
I am rather curious as to who wrote the sprocs with a profix of "sp"? And named the tables with a prefix of "tbl"?
I fired some guy for prefixing all the table names that way. That is very poor naming convention. How can you easily find anything in the list of tables, or in the list of the sprocs? You know they are tables, they do not need a prefix.
Just my three cents worth
Andrew SQLDBA
December 30, 2009 at 12:19 pm
AndrewSQLDBA (12/30/2009)
I fired some guy for prefixing all the table names that way. That is very poor naming convention.Andrew SQLDBA
I'd rather fire the guy who'd be responsible for the rules defined in the "company-wide programming convention"... 😉
If there wasn't any rule available, what did that guy do wrong?
It would have been a different story if there was a naming convention defined, but not followed, even after retraining...
But this subject is a completely different story anyway. :unsure:
December 30, 2009 at 1:39 pm
I'd fire the guy that hired two people... the one who didn't write/correctly promulgate the company wide rules and the guy that interviewed/hired him. 😛
I do agree, though... classic Hungarian notation really has no place in the databases or even in GUI code... if the type of object changes, say, a table to a view or a table to a synonym, then you're stuck with an object that has the wrong Hungarian notation because it's just too bloody costly to go back and fix everything with the new notation.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply