SQL Server : insert into with openxml and timestamp

Question

Ok, here is the process that was working

xml files in our production server xml files where being created for each table
based of those xml files the data was imported

One thing has changes in production. A field called RowVer was created that is a timestamp.

So now on the xml files that are exported the RowVer and its value is there. Not only that its encoded in base64, and there is now way to insert explicit values into a timestamp field.

Anyway, in the stored procedure, there is a rules table we have were if the table is x and the field is y then ignore it.

Basically the SQL code is this:

--declare @ImportPath varchar(1000) = 'E:\ExportTest\171026140442\' --bunch of xml files
--declare @DB varchar(1000) = 'DestinationDb.dbo'
--declare @debug bit = 1
ALTER PROCEDURE [dbo].[STP_StageImp_ImportData]
    @ImportPath varchar(1000),
    @DB varchar(1000),
    @debug bit
AS

if object_id('tempdb..#Files') is not null drop table #Files
create table #Files
(
ID int
,TableName varchar(1000)
,Depth int
,IsFile bit
)

insert into #Files (TableName, Depth, IsFile) exec xp_dirtree @ImportPath, 10, 1

declare @DataTableName varchar(1000)
declare @TargetDataTableName varchar(1000)
declare @sql nvarchar(max)


while (select count(*) from #Files) > 0
 begin
    set @DataTableName = (select top 1 TableName from #Files order by ID, TableName)



if object_id('tempdb..#RawData') is not null drop table #RawData

create table #RawData
(
TableName varchar(1000),
RawXML xml
)

set @sql = ' insert into #RawData
            select ''' + parsename(@DataTableName,2) + ''', BulkColumn from openrowset(  
            bulk ''' + @ImportPath + @DataTableName + ''',  
            single_nclob) as x'


exec (@sql)

declare @XMLData xml
select @XMLData = RawXML from #RawData

declare @ColumnNames nvarchar(MAX)
declare @ColumnSchema nvarchar(MAX)
declare @hDoc int

if object_id('tempdb..#columns') is not null drop table #columns
create table #columns
(
    column_name varchar(1000),
    column_full varchar(1000)
)


insert into #columns
exec STP_StageImp_TableCols @TargetDataTableName


delete from #columns where column_name IN ('ID','RowVer')

    delete @data
insert into @data select c.Column_Name + ',' from #columns c
exec STP_StageImp_MergeRows @data, @debug, @ColumnNames output


exec sp_xml_preparedocument @hDoc output, @XMLData

set @sql = N'insert into ' +  @TargetDataTableName + ' select * from openxml(@hDoc, ''table/row'') with (' + @ColumnSchema + N')'

exec sp_executesql @SQL, N'@hDoc int', @hDoc

exec sp_xml_removedocument @hDoc

delete from #Files where TableName = @DataTableName
end

The error is

server column name or number of supplied values does not match table definition

BUT THAT IS SIMPLY NOT TRUE

If I run select * from Destionation.dbo.Table and dumped into an Excel file and the table schema and did a comparison

select * from openxml(@hDoc, ''table/row'') with (' + @ColumnSchema + N')'

I verified the destination tables does not have any "hidden" fields.

if i change it to

set @sql = N'insert into ' +  @TargetDataTableName + ' (' + @ColumnNames + ')
             select * from openxml(@hDoc, ''table/row'') with (' + @ColumnSchema + N')'

(the difference above is using the @ColumnNames explicitly) THIS WORKS

So in the end... I did not want to have to change our flash stage to QA stored procedures, because of a new timestamp column.

If anyone has any idea why adding a timestamp column to a table would break insert into table select * fro openxml, I would appreciate it.

This is not super high priority, as I have a workaround, but if anyone has any suggestions post away.

Your insert statement is very brittle here. You should always specify the columns in the insert statement and specify the columns in the source query. You are relying on these always being 100% exactly the same. Make your code robust by being explicit and this error will fix itself. — Sean Lange
– Sean Lange, Commented Feb 12, 2018 at 19:14
i agree, but in this case the columns are exact, i debugged that fact — MichaelEvanchik
– MichaelEvanchik, Commented Feb 12, 2018 at 20:03
Well we don't have a lot of detail here. the only thing we know is that you stated that adding a timestamp column broke your process. We have no chance of running this at all. But are you really using the timestamp datatype? Or are you just saying that in reference to a datetime? — Sean Lange
– Sean Lange, Commented Feb 12, 2018 at 21:12
So you have a column in your table that was recently added that is a timestamp datatype? Perhaps sharing some details would help considerably here. Did you try adding a timestamp column to a columnless insert???? Hint....it fails. — Sean Lange
– Sean Lange, Commented Feb 12, 2018 at 21:27

Sean Lange · Accepted Answer · 2018-02-12 21:31:13Z

1

The error is because your insert is not explicitly naming the columns. The exact warning I posted in a comment a couple hours ago. This is very easy to prove.

create table Something
(
    SomeVal int
)

insert Something
select 1

select * from Something

--at this point you have 1 row in the table

--now add the new column
alter table Something add asdf timestamp

insert Something
select 1
--whoa....it failed.

--but if I name the columns it works again.
insert Something(SomeVal)
select 1

select * from Something

You might also take a look at the documentation as the timestamp datatype has been deprecated for quite some time. https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql

answered Feb 12, 2018 at 21:31

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : insert into with openxml and timestamp

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related