Options
Error Inserting Text
jrusso76
Posts: 5
When trying to synchronize a table with a text column, the following code is generated:
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?
/* 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?
Comments
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?
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:
I then opened the file in notepad, saved it with a different name and reran it in SSMS. I now get the following message:
I should note that I am synchronizing from SQL Server 2000 to SQL Server 2005.
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.
I was looking at the generated script file and I noticed the following statements:
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.
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.
I'd like to thank Red Gate's tech support, specially Chris Auckland, for their time, patience, and dedication. Thank you.