Error: Cannot use text, ntext or image in the "inserted" and "deleted" tables
Eugene asked 6 years ago
Hi,
I’m trying to have email data from GP SY01200 table replicated. I have created real time MS SQL data source on SY01200 as follows:
SELECT [dbo].[SY01200].[Master_ID], [dbo].[SY01200].[INET1],
CONVERT([VARCHAR](500),[dbo].[SY01200].[EmailToAddress]) AS [EmailTo],
CONVERT([VARCHAR](500),[dbo].[SY01200].[EmailCcAddress]) AS [EmailCc],
CONVERT([VARCHAR](500),[dbo].[SY01200].[EmailBccAddress]) AS [EmailBcc],
[dbo].[SY01200].[Master_Type], [dbo].[SY01200].[ADRSCODE]
FROM [dbo].[SY01200]
This is what query window shows.
Data preview works fine and I can see the data I need.
I map my real time data source to the stored proc that does all required data manipulation.
And procedure works also fine – I have tested it separately.
however, after I create my mapping and trying to register my data source with the map I get this error message:
Cannot use text, ntext or image in the “inserted” and “deleted” tables
In SY01200 table columns EmailToAddress, EmailCcAddress, EmailBccAddress are of type text.
How can I get around of this problem?
Thank you
I’m trying to have email data from GP SY01200 table replicated. I have created real time MS SQL data source on SY01200 as follows:
SELECT [dbo].[SY01200].[Master_ID], [dbo].[SY01200].[INET1],
CONVERT([VARCHAR](500),[dbo].[SY01200].[EmailToAddress]) AS [EmailTo],
CONVERT([VARCHAR](500),[dbo].[SY01200].[EmailCcAddress]) AS [EmailCc],
CONVERT([VARCHAR](500),[dbo].[SY01200].[EmailBccAddress]) AS [EmailBcc],
[dbo].[SY01200].[Master_Type], [dbo].[SY01200].[ADRSCODE]
FROM [dbo].[SY01200]
This is what query window shows.
Data preview works fine and I can see the data I need.
I map my real time data source to the stored proc that does all required data manipulation.
And procedure works also fine – I have tested it separately.
however, after I create my mapping and trying to register my data source with the map I get this error message:
Cannot use text, ntext or image in the “inserted” and “deleted” tables
In SY01200 table columns EmailToAddress, EmailCcAddress, EmailBccAddress are of type text.
How can I get around of this problem?
Thank you
Answers
Lorren Zemke Staff answered 6 years ago
Eugene,
Microsoft SQL Server will not let you use the text, ntext or image columns in a trigger. It’s not an issue with SmartConnect but Microsoft SQL Server doesn’t allow it.
The only option that you would have is to create a Changes Only map against that table. This will only create a trigger that uses the Index fields for the SY01200 table. When the map runs, typically on a schedule, it will then allow you to pull the data into your destination.
Microsoft SQL Server will not let you use the text, ntext or image columns in a trigger. It’s not an issue with SmartConnect but Microsoft SQL Server doesn’t allow it.
The only option that you would have is to create a Changes Only map against that table. This will only create a trigger that uses the Index fields for the SY01200 table. When the map runs, typically on a schedule, it will then allow you to pull the data into your destination.
Any response?
will anyone provide any answer??
Do I have to book the support ticket for this?