Error Inserting Text

jrusso76

When trying to synchronize a table with a text column, the following code is generated:

/*
Script created by SQL Data Compare version 7.2.0.3 from Red Gate Software Ltd at 6/1/2009 10:33:38 AM

Note that this script will carry out all DELETE commands for all tables first, then all the UPDATES and then all the INSERTS
It will disable foreign key constraints at the beginning of the script, and re-enable them at the end
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

BEGIN TRANSACTION

-- Delete 2 rows from [dbo].[reports]
DELETE FROM [dbo].[reports] WHERE [id]=1246
DELETE FROM [dbo].[reports] WHERE [id]=1248

-- Add 2 rows to [dbo].[reports]
SET IDENTITY_INSERT [dbo].[reports] ON
EXEC(N'INSERT INTO [dbo].[reports] ([id], [menu_id], [report_name], [proc_name], [report_file]) VALUES (1251, 1, ''Client_Daily_Status_Of_Accounts_Report_New.rpt'', ''r_DailyStatusOfAccountsReport_New'', ''ÐÏࡱ

The [report_file] column is a text column that holds an actual crystal report file (I know it should be binary or image, but that's what I inherited). It appears that the script is getting cut off probably because the binary file has a null character. Is there any way to fix this?

Chris Auckland

Thanks for your post.

I think I've seen something similar to this in the past.

Does the script parse if you open the script in SSMS, or does it fail at the same place?

Could you also try opening the sql file in notepad, and then save it again with a different name. Does that file parse in SSMS?

jrusso76

Here's the steps I took:

Started Sychronization Wizard, clicked on View SQL Script. Copying and pasting does not work as it cuts off most of the script. I saved the script and opened it in SSMS. Tried to run it and got the following message:

Msg 105, Level 15, State 1, Line 12
Unclosed quotation mark after the character string 'UPDATE [dbo].[reports] SET [report_name]='Daily_Status_Of_Accounts_Report_New.rpt', [report_file]='ÐÏࡱá
'.

I then opened the file in notepad, saved it with a different name and reran it in SSMS. I now get the following message:

Msg 518, Level 16, State 1, Line 605
Cannot convert data type ntext to text.
The statement has been terminated.

I should note that I am synchronizing from SQL Server 2000 to SQL Server 2005.

Chris Auckland

Thanks for your reply.

Can you use SQL Compare to check that the structures of the table are identical. It looks like one might have ntext and the other text.

You might find you need to synchronize the structures of the databases before you try and synchronize the data.

jrusso76

I double-checked the data structures and they are identical.

I was looking at the generated script file and I noticed the following statements:

EXEC(N'DECLARE @pv binary(16)
'+N'SELECT @pv=TEXTPTR([report_file]) FROM [dbo].[reports] WHERE [id]=1252
UPDATETEXT [dbo].[reports].[report_file] @pv NULL NULL N''sY,7

I left out the rest of the binary data for brevity. However, the UPDATETEXT statement seems to be trying to insert ntext data into a text column. Could this be the cause?

Is there a way for me to send you the script files?

Thank you for your assistance.

Chris Auckland

We can work on this through a private support ticket if you like, we already have one open for you.

If you email support@red-gate.com and include the reference F0022861 in the subject line, it will update the correct ticket.

If the scripts are too big for email, I can set up an ftp area for you.

jrusso76

For those who may experience the same issue as I have, here's what I did to solve it: re-import the file into the source database.

I'd like to thank Red Gate's tech support, specially Chris Auckland, for their time, patience, and dedication. Thank you.