July 2, 2013 at 11:33 am
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.
July 2, 2013 at 6:56 pm
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 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
July 2, 2013 at 9:19 pm
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
Change is inevitable... Change for the better is not.
July 2, 2013 at 9:28 pm
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 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
July 3, 2013 at 10:04 am
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.
July 3, 2013 at 2:22 pm
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]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply