Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem in executing bunch of Insert statements Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 11:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
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.

Post #1469689
Posted Tuesday, July 2, 2013 6:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1469813
Posted Tuesday, July 2, 2013 9:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1469831
Posted Tuesday, July 2, 2013 9:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1469832
Posted Wednesday, July 3, 2013 10:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:34 AM
Points: 134, Visits: 259
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.
Post #1470144
Posted Wednesday, July 3, 2013 2:22 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:20 PM
Points: 807, Visits: 725
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
Post #1470228
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse