SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem in executing bunch of Insert statements


Problem in executing bunch of Insert statements

Author
Message
Learner44
Learner44
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 438
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.
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7377 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86950 Visits: 41107
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7377 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Learner44
Learner44
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 438
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
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2127 Visits: 872
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.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search