Problem in executing bunch of Insert statements

Question

Problem in executing bunch of Insert statements

Learner44

SSCarpal Tunnel

Points: 4466
More actions
July 2, 2013 at 11:33 am

#295881

Hello friends,
I am executing bunch of Insert satements as follows:
use Testing
go
INSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'Students'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM [10.198.68.39]..dbo.Students;
.
.
.
.
.
INSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'teachers'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM [10.198.68.39]..dbo.teachers;
I have 1450 insert statments, which I am running in SQL SERVER to insert the data from "Linked Server" to "SQL Server".
when I run them in one go ..it give me few successfully inserted records. around 250 out of 1450 and following error
The OLE DB provider "OraOLEDB.Oracle" for linked server "10.198.68.39" supplied inconsistent metadata for a column. The column "NOTESTEXT" (compile-time ordinal 3) of object ""dbo"."Notes"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.
But, When I run them individually or in samll bunch around 23-30 at a time it success fully executes.
I don't know how to get rid of this, because, every time it is very costly to run them individually or in small bunch.
please help.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

Learner44 (7/2/2013)
Hello friends,
I am executing bunch of Insert satements as follows:
use Testing
go
INSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'Students'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM [10.198.68.39]..dbo.Students;
.
.
.
.
.
INSERT INTO Table_Update(TableName,StartlastWritten) SELECT 'teachers'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten FROM [10.198.68.39]..dbo.teachers;
I have 1450 insert statments, which I am running in SQL SERVER to insert the data from "Linked Server" to "SQL Server".
when I run them in one go ..it give me few successfully inserted records. around 250 out of 1450 and following error
The OLE DB provider "OraOLEDB.Oracle" for linked server "10.198.68.39" supplied inconsistent metadata for a column. The column "NOTESTEXT" (compile-time ordinal 3) of object ""dbo"."Notes"" was reported to have a "DBCOLUMNFLAGS_ISNULLABLE" of 0 at compile time and 32 at run time.
But, When I run them individually or in samll bunch around 23-30 at a time it success fully executes.
I don't know how to get rid of this, because, every time it is very costly to run them individually or in small bunch.
please help.
Thanks.

The first thing I'd try is to combine your 1450 INSERTs into one result set on the remote server.

INSERT INTO Table_Update(TableName,StartlastWritten)

SELECT 'Students'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten

FROM [10.198.68.39]..dbo.Students;

UNION ALL

.

. -- Next 1448 SELECTs

.

UNION ALL

SELECT 'teachers'As Tbl, MAX(LASTWRITTEN) AS StartLastWritten

FROM [10.198.68.39]..dbo.teachers;[/b]

No guarantees that will help but if you were to compare speed of 1450 inserts on a local server (no remote server) vs. 1 INSERT that combines all 1450 tables I'm sure the difference in speed would be quite apparent.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

Wait a minute, please. There's something I don't understand. It would appear that you're capturing data from one table at a time. Are you telling me that you have 1500 tables that you're trying to write from? What do some of the other SELECTs look like and do you ever repeat the table you're selecting but with a different column name?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

Jeff Moden (7/2/2013)
Wait a minute, please. There's something I don't understand. It would appear that you're capturing data from one table at a time. Are you telling me that you have 1500 tables that you're trying to write from? What do some of the other SELECTs look like and do you ever repeat the table you're selecting but with a different column name?

I was kind of wondering about that too but I chose to go with it. 🙂

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 4

I have one table called Table_Update in my local server, which had three columns( Table_name, Start_date, End_date)

I have around 1500 tables on linked server, that has "Start Date" and "Last Date" columns.

I need the this 1500 table names and their start and last date in my Table_Update column.

But when I run that statements it gives me above error for some of the tables and , I am not sure what that error is..

Please help.

thanks.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

There is something that does not seem quite right here. You say that you retrieve StartDate and EndDate, but your sample query only has two columns.

Your error message barfs about column 3 and says that it is called Notetext?

Also, dbo seems like a funny name for a schema in an Oracle database.

But all apart from that, what happens is this: First SQL Server wants to compile the batch, so it queries the OLE DB provider about the columns returned by the query, and the OLE DB provider reports that this particular column is nullable. However, when you actually run the query the provider says that the column is nullable, and SQL Server does not appreciate this joke.

This kind of situation is very difficult to troubleshoot, since it requires knowledge about both SQL Server and Oracle. Although, I would hold Oracle and the OLE DB provider as the prime suspect. I would make sure that you have the latest version of the provider, and that it matches the version of Oracle you are connecting to.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]