Command line generates faulty script
jrowland
Posts: 9
When I use the command line to generate a script to synchronize this table with one that doesn't have the DF_COMMENTS_FLAG_ID constraint or the DF_COMMENTS_DATE_CREATED default:
CREATE TABLE [dbo].[COMMENTS]
(
[INT_ID] [int] NOT NULL IDENTITY(1000, 1),
[GUID_ACCOUNT] [uniqueidentifier] NOT NULL,
[CLASS_ID] [int] NOT NULL,
[DATE_CREATED] [datetime] NOT NULL CONSTRAINT [DF_COMMENTS_DATE_CREATED] DEFAULT (getdate()),
[CREATED_BY] [int] NOT NULL,
[FLAG_ID] [int] NULL CONSTRAINT [DF_COMMENTS_FLAG_ID] DEFAULT (2),
[STR_COMMENTS] [varchar] (7900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
It produces a script that creates a temp table and tries to place the textimage on a filegroup, but there is not text column. The faulty section of code created by the command line is:
PRINT N'Rebuilding [dbo].[COMMENTS]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_COMMENTS]
(
[INT_ID] [int] NOT NULL IDENTITY(1000, 1),
[GUID_ACCOUNT] [uniqueidentifier] NOT NULL,
[CLASS_ID] [int] NOT NULL,
[DATE_CREATED] [datetime] NOT NULL CONSTRAINT [DF_COMMENTS_DATE_CREATED] DEFAULT (getdate()),
[CREATED_BY] [int] NOT NULL,
[FLAG_ID] [int] NULL CONSTRAINT [DF_COMMENTS_FLAG_ID] DEFAULT (2),
[STR_COMMENTS] [varchar] (7900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
It produces the following error:
Cannot use TEXTIMAGE_ON when a table has no text, ntext, or image columns.
When I use the GUI interface, it goes about synchronizing the tables in a completely different way. It generates an alter table statement on the destination table.
ALTER TABLE [dbo].[COMMENTS]
ADD CONSTRAINT [DF_COMMENTS_DATE_CREATED]
DEFAULT (getdate()) FOR [DATE_CREATED]
ALTER TABLE [dbo].[COMMENTS]
ADD CONSTRAINT [DF_COMMENTS_FLAG_ID]
DEFAULT (2) FOR [FLAG_ID]
How can I get the command line to synch the table the same way the gui does? Or at least make it stop trying to put the textimage on a filegroup when there is no text column.
CREATE TABLE [dbo].[COMMENTS]
(
[INT_ID] [int] NOT NULL IDENTITY(1000, 1),
[GUID_ACCOUNT] [uniqueidentifier] NOT NULL,
[CLASS_ID] [int] NOT NULL,
[DATE_CREATED] [datetime] NOT NULL CONSTRAINT [DF_COMMENTS_DATE_CREATED] DEFAULT (getdate()),
[CREATED_BY] [int] NOT NULL,
[FLAG_ID] [int] NULL CONSTRAINT [DF_COMMENTS_FLAG_ID] DEFAULT (2),
[STR_COMMENTS] [varchar] (7900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
It produces a script that creates a temp table and tries to place the textimage on a filegroup, but there is not text column. The faulty section of code created by the command line is:
PRINT N'Rebuilding [dbo].[COMMENTS]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_COMMENTS]
(
[INT_ID] [int] NOT NULL IDENTITY(1000, 1),
[GUID_ACCOUNT] [uniqueidentifier] NOT NULL,
[CLASS_ID] [int] NOT NULL,
[DATE_CREATED] [datetime] NOT NULL CONSTRAINT [DF_COMMENTS_DATE_CREATED] DEFAULT (getdate()),
[CREATED_BY] [int] NOT NULL,
[FLAG_ID] [int] NULL CONSTRAINT [DF_COMMENTS_FLAG_ID] DEFAULT (2),
[STR_COMMENTS] [varchar] (7900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
It produces the following error:
Cannot use TEXTIMAGE_ON when a table has no text, ntext, or image columns.
When I use the GUI interface, it goes about synchronizing the tables in a completely different way. It generates an alter table statement on the destination table.
ALTER TABLE [dbo].[COMMENTS]
ADD CONSTRAINT [DF_COMMENTS_DATE_CREATED]
DEFAULT (getdate()) FOR [DATE_CREATED]
ALTER TABLE [dbo].[COMMENTS]
ADD CONSTRAINT [DF_COMMENTS_FLAG_ID]
DEFAULT (2) FOR [FLAG_ID]
How can I get the command line to synch the table the same way the gui does? Or at least make it stop trying to put the textimage on a filegroup when there is no text column.
Comments
<?xml version="1.0"?>
<commandline>
<database1>DBName</database1>
<server1>Server1</server1>
<database2>DBName</database2>
<server2>Server2</server2>
<force></force>
<out>D:\Scripts\Synchronization\SQLCompareOut.txt</out>
<exclude>Role</exclude>
<exclude>User</exclude>
<include>Table:\[COMMENTS\]</include>
<options>if,ip,ift,iu,iup</options>
<sync></sync>
</commandline>