Devart Forums

Posted: **Tue 06 Sep 2016 14:49**

Hello,
I use dbforge Studio for SQL Server Professional 5.2.177. I renamed some columns and tables in my database and then i tried a schema comparision between the original and the renamed database. I matched the columns through the assistens and then I use the scrip generator. Then I executed the script and got some errors. Looking into the autogenerated sqlcode I realised some errors in the code. Some columns were not renamed. The were droped and some lines later created with the new name. This is bad, because it will cause dataloss and destroy my database. And there are some more errors in the script. Fore example:

Code: Select all

--
-- Rename column "[Bezeichnung]" to "[Prozessweg]" on table "dbo.TBL_Virtuell_Weg"
--
PRINT (N'Rename column "[Bezeichnung]" to "[Prozessweg]" on table "dbo.TBL_Virtuell_Weg"')
GO
IF NOT EXISTS (
  SELECT 1 FROM sys.columns
  WHERE name = N'Prozessweg' AND object_id = OBJECT_ID(N'[dbo].[TBL_Prozessweg]'))
  AND EXISTS (
  SELECT 1 FROM sys.columns
  WHERE name = N'Bezeichnung' AND object_id = OBJECT_ID(N'[dbo].[TBL_Virtuell_Weg]'))
BEGIN
DECLARE @res int
EXEC @res = sp_rename N'[dbo].[TBL_Virtuell_Weg].[Bezeichnung]', N'Prozessweg', 'COLUMN'
IF @res <> 0
  RAISERROR ('Error while Rename column "[Bezeichnung]" to "[Prozessweg]" on table "dbo.TBL_Virtuell_Weg"', 11, 1 );
END
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

the "If not exists" question with the OBJECT_ID(N'[dbo].[TBL_Prozessweg]') is wrong because the table wasn't renamed at this point. the rename of the table follows some lines after this rename statement. Are these mistakes in the autogeneration process or did I made a mistake during the configuration of the script? My autogenerated script has 18766 lines and I would like to use it without checking every line and correct the generated mistakes. Your tool is great and would be phenomenal without these mistakes.

Posted: **Wed 07 Sep 2016 12:03**

Please note that you can unselect the 'Check for object existence' and 'Include print comments' options on the 'Options' page of the Schema Synchronization wizard.

Posted: **Fri 09 Sep 2016 13:48**

Thank you for your reply. Could you please answer to the first error: I use dbforge Studio for SQL Server Professional 5.2.177. I renamed some columns and tables in my database and then i tried a schema comparision between the original and the renamed database. I matched the columns through the assistens and then I use the scrip generator. Then I executed the script and got some errors. Looking into the autogenerated sqlcode I realised some errors in the code. Some columns were not renamed. They were droped and some lines later created with the new name. This is bad, because it will cause dataloss and destroy my database. Here the code example for this error:

Code: Select all

-- Drop column "[ID_Virtuell_Weg]" from table "[dbo].[TBL_Virtuell_Weg]"
--
PRINT (N'Drop column "[ID_Virtuell_Weg]" from table "[dbo].[TBL_Virtuell_Weg]"')
GO
IF EXISTS (
  SELECT 1 FROM sys.columns
  WHERE name = N'ID_Virtuell_Weg' AND object_id = OBJECT_ID(N'[dbo].[TBL_Virtuell_Weg]'))
ALTER TABLE [dbo].[TBL_Virtuell_Weg]
  DROP COLUMN [ID_Virtuell_Weg]
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

-- rename some other columns from this table

--
-- Rename table "[dbo].[TBL_Virtuell_Weg]" to "[TBL_Prozessweg]"
--
PRINT (N'Rename table "[dbo].[TBL_Virtuell_Weg]" to "[TBL_Prozessweg]"')
GO
IF OBJECT_ID(N'[dbo].[TBL_Virtuell_Weg]', 'U') IS NOT NULL
  AND OBJECT_ID(N'[dbo].[TBL_Prozessweg]', 'U') IS NULL
DECLARE @res int
EXEC @res = sp_rename N'[dbo].[TBL_Virtuell_Weg]', N'TBL_Prozessweg', 'OBJECT'
IF @res <> 0
  RAISERROR ('Error while Rename table "[dbo].[TBL_Virtuell_Weg]" to "[TBL_Prozessweg]"', 11, 1 );
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

-- rename some other columns from this table


-- Create column "[ID_Prozessweg]" on table "[dbo].[TBL_Prozessweg]"
--
PRINT (N'Create column "[ID_Prozessweg]" on table "[dbo].[TBL_Prozessweg]"')
GO
IF NOT EXISTS (
  SELECT 1 FROM sys.columns
  WHERE name = N'ID_Prozessweg' AND object_id = OBJECT_ID(N'[dbo].[TBL_Prozessweg]'))
ALTER TABLE [dbo].[TBL_Prozessweg]
  ADD [ID_Prozessweg] [bigint] IDENTITY
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

Posted: **Fri 09 Sep 2016 15:37**

The columns get recreated if they have different types. You can manually map columns on the 'Table Mapping' page of the schema comparison wizard.

Could you please send us the CREATE definitions of the tables TBL_Virtuell_Weg and TBL_Prozessweg?

You can reply straight to our support system at supportATdevartDOTcom

Devart Forums

Syncronice objects scripts generates faulty script

Syncronice objects scripts generates faulty script

Re: Syncronice objects scripts generates faulty script

Re: Syncronice objects scripts generates faulty script

Re: Syncronice objects scripts generates faulty script