January 19, 2017 at 2:44 pm
This is the message I get
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type ntext to varbinary(max) is not allowed.
Here is the code .....
USE REPORTSERVER
GO
set nocount on
declare @table nvarchar(255)
Set @table = 'dbo.Subscriptions'
declare @is_identity bit;
declare @columns nvarchar(max);
declare @values nvarchar(max);
declare @script nvarchar(max);
if isnull(charindex('.', @table), 0) = 0
begin
print ' Please provide @table parameter in the form of schema_name.table_name';
end
else
begin
-- initialize variables as otherwise the padding will fail (return nulls for nvarchar(max) types)
set @is_identity = 0;
set @columns = '';
set @values = '';
set @script = '';
/*
The following select makes an assumption that the identity column should be included in
the insert statements. Such inserts still work when coupled with identity_insert toggle,
which is typically used when there is a need to "plug the holes" in the identity values.
Please note the special handling of the text data type. The type should never be present
in SQL Server 2005 tables because it will not be supported in future versions, but there
are unfortunately plenty of tables with text columns out there, patiently waiting for
someone to upgrade them to varchar(max).
*/
select
@is_identity = @is_identity | columnproperty(object_id(@table), column_name, 'IsIdentity'),
@columns = @columns + ', ' + '['+ column_name + ']',
@values =
@values + ' + '', '' + isnull(master.dbo.fn_varbintohexstr(cast(' +
case data_type
when 'text' then 'cast([' + column_name + '] as varchar(max))'
else '[' + column_name + ']'
end + ' as varbinary(max))), ''null'')'
from
information_schema.columns
where
table_name = substring(@table, charindex('.', @table) + 1, len(@table))
and data_type != 'timestamp'
order by ordinal_position;
set @script =
'select ''insert into ' + @table + ' (' + substring(@columns, 3, len(@columns)) +
') values ('' + ' + substring(@values, 11, len(@values)) + ' + '');'' from '
+ @table
+
' WHERE OwnerID = ''E9114A66-14F2-4297-960B-98DB50E46DD0'' ; ' ;
if @is_identity = 1
print ('set identity_insert ' + @table + ' on');
/*
generate insert statements. If the results to text option is set and the query results are
completely fit then the prints are a part of the batch, but if the results to grid is set
then the prints (identity insert related) can be gathered from the messages window.
*/
exec sp_executesql @script;
if @is_identity = 1
print ('set identity_insert ' + @table + ' off');
end
set nocount off
--Select @@VERSION
January 19, 2017 at 2:55 pm
After this:
when 'text' then 'cast([' + column_name + '] as varchar(max))'
add this:
when 'ntext' then 'cast([' + column_name + '] as nvarchar(max))'
January 19, 2017 at 3:14 pm
mw112009 - Thursday, January 19, 2017 2:44 PMThis is the message I get
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type ntext to varbinary(max) is not allowed.Here is the code .....
USE REPORTSERVER
GOset nocount on
declare @table nvarchar(255)
Set @table = 'dbo.Subscriptions'
declare @is_identity bit;
declare @columns nvarchar(max);
declare @values nvarchar(max);
declare @script nvarchar(max);
if isnull(charindex('.', @table), 0) = 0
begin
print ' Please provide @table parameter in the form of schema_name.table_name';
end
else
begin
-- initialize variables as otherwise the padding will fail (return nulls for nvarchar(max) types)
set @is_identity = 0;
set @columns = '';
set @values = '';
set @script = '';
/*
The following select makes an assumption that the identity column should be included in
the insert statements. Such inserts still work when coupled with identity_insert toggle,
which is typically used when there is a need to "plug the holes" in the identity values.
Please note the special handling of the text data type. The type should never be present
in SQL Server 2005 tables because it will not be supported in future versions, but there
are unfortunately plenty of tables with text columns out there, patiently waiting for
someone to upgrade them to varchar(max).
*/
select
@is_identity = @is_identity | columnproperty(object_id(@table), column_name, 'IsIdentity'),
@columns = @columns + ', ' + '['+ column_name + ']',
@values =
@values + ' + '', '' + isnull(master.dbo.fn_varbintohexstr(cast(' +
case data_type
when 'text' then 'cast([' + column_name + '] as varchar(max))'
else '[' + column_name + ']'
end + ' as varbinary(max))), ''null'')'
from
information_schema.columns
where
table_name = substring(@table, charindex('.', @table) + 1, len(@table))
and data_type != 'timestamp'
order by ordinal_position;
set @script =
'select ''insert into ' + @table + ' (' + substring(@columns, 3, len(@columns)) +
') values ('' + ' + substring(@values, 11, len(@values)) + ' + '');'' from '
+ @table
+
' WHERE OwnerID = ''E9114A66-14F2-4297-960B-98DB50E46DD0'' ; ' ;
if @is_identity = 1
print ('set identity_insert ' + @table + ' on');
/*
generate insert statements. If the results to text option is set and the query results are
completely fit then the prints are a part of the batch, but if the results to grid is set
then the prints (identity insert related) can be gathered from the messages window.
*/exec sp_executesql @script;
if @is_identity = 1
print ('set identity_insert ' + @table + ' off');
end
set nocount off--Select @@VERSION
Lynn: That Worked.!.. you mean like this .(below)........
USE REPORTSERVER
GO
set nocount on
declare @table nvarchar(255)
Set @table = 'dbo.Subscriptions'
declare @is_identity bit;
declare @columns nvarchar(max);
declare @values nvarchar(max);
declare @script nvarchar(max);
if isnull(charindex('.', @table), 0) = 0
begin
print ' Please provide @table parameter in the form of schema_name.table_name';
end
else
begin
-- initialize variables as otherwise the padding will fail (return nulls for nvarchar(max) types)
set @is_identity = 0;
set @columns = '';
set @values = '';
set @script = '';
/*
The following select makes an assumption that the identity column should be included in
the insert statements. Such inserts still work when coupled with identity_insert toggle,
which is typically used when there is a need to "plug the holes" in the identity values.
Please note the special handling of the text data type. The type should never be present
in SQL Server 2005 tables because it will not be supported in future versions, but there
are unfortunately plenty of tables with text columns out there, patiently waiting for
someone to upgrade them to varchar(max).
*/
select
@is_identity = @is_identity | columnproperty(object_id(@table), column_name, 'IsIdentity'),
@columns = @columns + ', ' + '['+ column_name + ']',
@values =
@values + ' + '', '' + isnull(master.dbo.fn_varbintohexstr(cast(' +
case data_type
when 'text' then 'cast([' + column_name + '] as varchar(max))'
when 'ntext' then 'cast([' + column_name + '] as nvarchar(max))'
else '[' + column_name + ']'
end + ' as varbinary(max))), ''null'')'
from
information_schema.columns
where
table_name = substring(@table, charindex('.', @table) + 1, len(@table))
and data_type != 'timestamp'
order by ordinal_position;
set @script =
'select ''insert into ' + @table + ' (' + substring(@columns, 3, len(@columns)) +
') values ('' + ' + substring(@values, 11, len(@values)) + ' + '');'' from '
+ @table
+
' WHERE OwnerID = ''E9114A66-14F2-4297-960B-98DB50E46DD0'' ; ' ;
if @is_identity = 1
print ('set identity_insert ' + @table + ' on');
/*
generate insert statements. If the results to text option is set and the query results are
completely fit then the prints are a part of the batch, but if the results to grid is set
then the prints (identity insert related) can be gathered from the messages window.
*/
exec sp_executesql @script;
if @is_identity = 1
print ('set identity_insert ' + @table + ' off');
end
set nocount off
--Select @@VERSION
January 19, 2017 at 3:35 pm
Looks good, except for my OCD when it comes to formatting.<
January 19, 2017 at 9:58 pm
This was removed by the editor as SPAM
January 20, 2017 at 8:20 am
JasonClark - Thursday, January 19, 2017 9:58 PMmw112009 - Thursday, January 19, 2017 2:44 PMThis is the message I get
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type ntext to varbinary(max) is not allowed.Here is the code .....
USE REPORTSERVER
GOset nocount on
declare @table nvarchar(255)
Set @table = 'dbo.Subscriptions'
declare @is_identity bit;
declare @columns nvarchar(max);
declare @values nvarchar(max);
declare @script nvarchar(max);
if isnull(charindex('.', @table), 0) = 0
begin
print ' Please provide @table parameter in the form of schema_name.table_name';
end
else
begin
-- initialize variables as otherwise the padding will fail (return nulls for nvarchar(max) types)
set @is_identity = 0;
set @columns = '';
set @values = '';
set @script = '';
/*
The following select makes an assumption that the identity column should be included in
the insert statements. Such inserts still work when coupled with identity_insert toggle,
which is typically used when there is a need to "plug the holes" in the identity values.
Please note the special handling of the text data type. The type should never be present
in SQL Server 2005 tables because it will not be supported in future versions, but there
are unfortunately plenty of tables with text columns out there, patiently waiting for
someone to upgrade them to varchar(max).
*/
select
@is_identity = @is_identity | columnproperty(object_id(@table), column_name, 'IsIdentity'),
@columns = @columns + ', ' + '['+ column_name + ']',
@values =
@values + ' + '', '' + isnull(master.dbo.fn_varbintohexstr(cast(' +
case data_type
when 'text' then 'cast([' + column_name + '] as varchar(max))'
else '[' + column_name + ']'
end + ' as varbinary(max))), ''null'')'
from
information_schema.columns
where
table_name = substring(@table, charindex('.', @table) + 1, len(@table))
and data_type != 'timestamp'
order by ordinal_position;
set @script =
'select ''insert into ' + @table + ' (' + substring(@columns, 3, len(@columns)) +
') values ('' + ' + substring(@values, 11, len(@values)) + ' + '');'' from '
+ @table
+
' WHERE OwnerID = ''E9114A66-14F2-4297-960B-98DB50E46DD0'' ; ' ;
if @is_identity = 1
print ('set identity_insert ' + @table + ' on');
/*
generate insert statements. If the results to text option is set and the query results are
completely fit then the prints are a part of the batch, but if the results to grid is set
then the prints (identity insert related) can be gathered from the messages window.
*/exec sp_executesql @script;
if @is_identity = 1
print ('set identity_insert ' + @table + ' off');
end
set nocount off--Select @@VERSION
SQL Server maintains its log data in varbinary format while converting Explicitly sometimes we get this error. I also face the same problem, have a look on this: https://raresql.com/tag/explicit-conversion-from-data-type-ntext-to-varbinarymax-is-not-allowed/
Hope this will also help you......
No No, this happens only in 2008, Didnt get the error in 2012 and above versions
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply