Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, that should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value for the identity
column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
Showing posts with label type. Show all posts
Showing posts with label type. Show all posts
Monday, March 26, 2012
Error inserting record in IDENTITY column
Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JT
Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JT
Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
Error inserting record in IDENTITY column
Hi,
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity
value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, tha
t should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value
for the identity
column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should hav
e
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicat
e
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
I have a counter field with data type IDENTITY in a table which should have
primary key constraint to that ID field.
When it tried to insert a new record, it has error "cannot insert duplicate
key in the counter field" because of the constraint.
Supposed that whenever adding a new record, it should add the increment to
the last ID. How can I fix it to allow inserting new records ?
Regards,
JTWhat version of SQL Server? Seems like the internal counter for the identity
value is messed up,
something I haven't seen since the 6.5 days. Read up on DBCC CHECKIDENT, tha
t should be able to fix
it for you.
Of course, I assume you don't use SET IDENTITY_INSERT ON and specify a value
for the identity
column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should hav
e
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert duplicat
e
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT|||Johnny
How do you insert the values?
create table #tmp (id int not null identity(1,1) primary key, c char(1) not
null)
go
insert into #tmp(c) values ('a')
insert into #tmp(c) values ('b')
insert into #tmp(c) values (c')
go
select * from #tmp
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:8D318201-8122-49E6-A710-558B161A4069@.microsoft.com...
> Hi,
> I have a counter field with data type IDENTITY in a table which should
> have
> primary key constraint to that ID field.
> When it tried to insert a new record, it has error "cannot insert
> duplicate
> key in the counter field" because of the constraint.
> Supposed that whenever adding a new record, it should add the increment to
> the last ID. How can I fix it to allow inserting new records ?
> Regards,
> JT
Sunday, March 11, 2012
Error in Reading Float data
I am using SQL Server 2000, VS 2003
I have Education table in which there is a field CGPA having float data type (null allowed) I retreive the data from SQL server using stroed proc and store it in SqlDataReader dr while reading if CGPA contains 0 then it raises an error that "Specified cast is not valid" other wise it does not raise any error.
while (dr.Read())
{
Education e = new Education();
e.EducationId = dr.GetInt32(0);
e.Country = dr.GetInt32(1);
e.InstitutionName = dr.GetString(2);
e.Grade = dr.GetString(3);
e.CGPA = dr.GetFloat(4); // ERROR HERE
e.Percentage = dr.GetFloat(5);
e.PassingYear = dr.GetString(6);
}
where as in Education CGPA is also the float property can any one tell me how to read 0 value of float from SQL server
GPA can be handled with Decimal or Numric data type, there are some tasks like complex calculus that require Float data type because the T-SQL functions are in Float but student grade is simple Arithmetic so you can use Decimal instead of Float. The reason Float is very unstable, long time SQL Server users use it for calculations but convert the value to Decimal for storage because you can set precision and scale with Decimal. Hope this helps.|||
Avoid floats, reals, single, doubles if at all possible in the database. They are imprecise numbers and well... They cause all kinds of weird issues. It's not SQL Server issues, it's just issues with those data types in general.
That said, I don't see how it's causing your problem. If you know the column names of the record format, try:
e.CGPA=dr("CGPA")
That assumes the field/column is named CGPA of course.
Or try:
Dim o as object
o=dr("CGPA")
e.CGPA=o
that way you can see what "o" is before you try and convert it to whatever type e.CGPA is.
|||Another option is a simple ANSI ALTER TABLE to change FLOAT to DECIMAL. Run a search for ALTER TABLE in SQL Server BOL (books online). Hope this helps.Error in Query
The database scheme consists of four relations:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The relation "Product" shows the maker, model number, and type (pc,
laptop, or printer). It is assumed that model numbers are unique for
all the makers and product types. For each model number specifying pc
in the relation "PC", its listed speed (of the processor in MGz), total
RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'),
and the price. The relation "Laptop" is similar to that one of PCs
except for the CD ROM speed which is replaced by screen size (in
inches). For each printer model in the relation "Printer" it is pointed
whether the printer is color or not (color attribute is 'y' for color
printers; otherwise it is 'n'), printer type (laser, jet, or matrix),
and the price.
I need to write a query for Find printer makers.
Result set: maker.
My Query is
select distinct product.maker from product inner join printer on
product.model = printer.model
I get the message
Your query produced correct result set on main database, but it failed
test on second, checking database.
* Wrong number of records (less by 1)
This question is an SQL exercise in URL http://www.sql-ex.ru/
Can anyobe explain me "correct result set on main database," why "but
it failed test on second, checking database."
* Wrong number of records (less by 1)
Thank you very much,
MiksHi Miks
> Can anyobe explain me "correct result set on main database," why "but
> it failed test on second, checking database."
> * Wrong number of records (less by 1)
My guess (emphasis on "guess") is that it has something to do with the fact
that you don't need a second table for this query. The writers of this
website, must be doing some sort of parsing of your query, this is not a
standard SQL Server error message.
This query is sufficient
select distinct maker from Product where type = 'printer'
--
-Dick Christoph
"Miks" <akmeera2k4@.gmail.com> wrote in message
news:1142418307.696828.171550@.j33g2000cwa.googlegr oups.com...
> The database scheme consists of four relations:
> Product(maker, model, type)
> PC(code, model, speed, ram, hd, cd, price)
> Laptop(code, model, speed, ram, hd, screen, price)
> Printer(code, model, color, type, price)
> The relation "Product" shows the maker, model number, and type (pc,
> laptop, or printer). It is assumed that model numbers are unique for
> all the makers and product types. For each model number specifying pc
> in the relation "PC", its listed speed (of the processor in MGz), total
> RAM (in MGb), hd capacity (in Gb), CD ROM speed (for example, '4x'),
> and the price. The relation "Laptop" is similar to that one of PCs
> except for the CD ROM speed which is replaced by screen size (in
> inches). For each printer model in the relation "Printer" it is pointed
> whether the printer is color or not (color attribute is 'y' for color
> printers; otherwise it is 'n'), printer type (laser, jet, or matrix),
> and the price.
> I need to write a query for Find printer makers.
> Result set: maker.
> My Query is
> select distinct product.maker from product inner join printer on
> product.model = printer.model
>
> I get the message
> Your query produced correct result set on main database, but it failed
> test on second, checking database.
> * Wrong number of records (less by 1)
> This question is an SQL exercise in URL http://www.sql-ex.ru/
> Can anyobe explain me "correct result set on main database," why "but
> it failed test on second, checking database."
> * Wrong number of records (less by 1)
> Thank you very much,
> Miks|||homework?|||any place where you can find the answer of these exercies?
im stuck at exercse 10:
Exercise: 10
Find the printers having the highest price.
Result set: model, price.
my query:
select model, max(price)price from printer
http://www.sql-ex.ru/exercises.php#answer_ref
any place for the answers?|||Hi Daniel
Here is one answer (One SQL Query that works)
select Model, Price
from Printer
where price = (select max(price) from Printer)
--
-Dick Christoph
"Daniel" <dtukkers@.gmail.com> wrote in message
news:1143709387.198548.201780@.z34g2000cwc.googlegr oups.com...
> any place where you can find the answer of these exercies?
> im stuck at exercse 10:
> Exercise: 10
> Find the printers having the highest price.
> Result set: model, price.
> my query:
> select model, max(price)price from printer
> http://www.sql-ex.ru/exercises.php#answer_ref
> any place for the answers?
Error in olap report
I am trying to write an olap report using RS2005. I created a datasource
with type "Microsoft Analysis Services" then added a report and defined
measures and dimensions in the query builder. Report is working fine but if
I make any modification, I will not be able to save the report and as soon
as I switch to any other tab (for example click on Preview tab or Data tab)
I will get the error "Microsoft Visual Studio has encountered a problem and
needs to close." I tried same senario by using "Analysis Services Toturial"
cube and I got same result.
Any idea?
Thanks.Actually, I'm used to getting this if I stress my development computer too
much... For bigger reports based on OLAP cubes, I just don't use the preview
function. I tweak my MDX query to just return enough data for the fields to
fill up right, and then I just save as quickly as possible. If you just add
a =" to the beginning of your query and a " at the end and make it be one
long string (no new lines), you will be able to save without the report
going to pieces. Remeber to use the "Save all" button after having added a
new report to your solution, or it will not be saved with your solution.
(But it will be available through "Add existing item".)
Not sure if this is what you wanted to hear, but it might save you from some
frustration.
Kaisa
"Tooraj" <abc@.abc.com> wrote in message
news:uEmibczBGHA.984@.tk2msftngp13.phx.gbl...
>I am trying to write an olap report using RS2005. I created a datasource
>with type "Microsoft Analysis Services" then added a report and defined
>measures and dimensions in the query builder. Report is working fine but if
>I make any modification, I will not be able to save the report and as soon
>as I switch to any other tab (for example click on Preview tab or Data tab)
>I will get the error "Microsoft Visual Studio has encountered a problem and
>needs to close." I tried same senario by using "Analysis Services Toturial"
>cube and I got same result.
> Any idea?
> Thanks.
>
with type "Microsoft Analysis Services" then added a report and defined
measures and dimensions in the query builder. Report is working fine but if
I make any modification, I will not be able to save the report and as soon
as I switch to any other tab (for example click on Preview tab or Data tab)
I will get the error "Microsoft Visual Studio has encountered a problem and
needs to close." I tried same senario by using "Analysis Services Toturial"
cube and I got same result.
Any idea?
Thanks.Actually, I'm used to getting this if I stress my development computer too
much... For bigger reports based on OLAP cubes, I just don't use the preview
function. I tweak my MDX query to just return enough data for the fields to
fill up right, and then I just save as quickly as possible. If you just add
a =" to the beginning of your query and a " at the end and make it be one
long string (no new lines), you will be able to save without the report
going to pieces. Remeber to use the "Save all" button after having added a
new report to your solution, or it will not be saved with your solution.
(But it will be available through "Add existing item".)
Not sure if this is what you wanted to hear, but it might save you from some
frustration.
Kaisa
"Tooraj" <abc@.abc.com> wrote in message
news:uEmibczBGHA.984@.tk2msftngp13.phx.gbl...
>I am trying to write an olap report using RS2005. I created a datasource
>with type "Microsoft Analysis Services" then added a report and defined
>measures and dimensions in the query builder. Report is working fine but if
>I make any modification, I will not be able to save the report and as soon
>as I switch to any other tab (for example click on Preview tab or Data tab)
>I will get the error "Microsoft Visual Studio has encountered a problem and
>needs to close." I tried same senario by using "Analysis Services Toturial"
>cube and I got same result.
> Any idea?
> Thanks.
>
Friday, March 9, 2012
Error in loading big file
Hi All,
I have an application on WL8.1 with sp3, the database is SQL Server 2000. I
have code below to load local file into database. The data type in database
is image.
PreparedStatement pStatement = null;
InputStreammyStream myStream = new InputStream();
myStream.setEmbeddedStream( is );
pStatement.setBinaryStream( 1, myStream, -1 );
pStatement.executeUpdate();
pStatement.close();
pStatement = null;
is is InputStream from a local file and the sql statement is
insert into file_content(content) values(?)
This workes fine for the files with size less than 150M, but for those big
files (>150M), it doesn't work and I got error message as below:
<Feb 11, 2005 12:00:41 PM PST> <Notice> <EJB> <BEA-010014> <Error occurred
while attempting to rollback transaction:
javax.transaction.SystemException: Heuristic hazard:
(weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
(javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
been closed.))
javax.transaction.SystemException: Heuristic hazard:
(weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
(javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
been closed.))
at weblogic.transaction.internal.ServerTransactionImp l.internalRollback
(ServerTransactionImpl.java:396)
at weblogic.transaction.internal.ServerTransactionImp l.rollback
(ServerTransactionImpl.java:362)
Any body can help? Thanks in advance.
Message posted via http://www.sqlmonster.com
Fred Wang via SQLMonster.com wrote:
> Hi All,
> I have an application on WL8.1 with sp3, the database is SQL Server 2000. I
> have code below to load local file into database. The data type in database
> is image.
> PreparedStatement pStatement = null;
> InputStreammyStream myStream = new InputStream();
> myStream.setEmbeddedStream( is );
> pStatement.setBinaryStream( 1, myStream, -1 );
> pStatement.executeUpdate();
> pStatement.close();
> pStatement = null;
> is is InputStream from a local file and the sql statement is
> insert into file_content(content) values(?)
> This workes fine for the files with size less than 150M, but for those big
> files (>150M), it doesn't work and I got error message as below:
> <Feb 11, 2005 12:00:41 PM PST> <Notice> <EJB> <BEA-010014> <Error occurred
> while attempting to rollback transaction:
> javax.transaction.SystemException: Heuristic hazard:
> (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
> (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
> been closed.))
> javax.transaction.SystemException: Heuristic hazard:
> (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
> (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
> been closed.))
> at weblogic.transaction.internal.ServerTransactionImp l.internalRollback
> (ServerTransactionImpl.java:396)
> at weblogic.transaction.internal.ServerTransactionImp l.rollback
> (ServerTransactionImpl.java:362)
> Any body can help? Thanks in advance.
>
Hi. That means the DBMS choked on the submission and killed the whole
JDBC connection. Check your DBMS log for problems. You may be running
out of space in the DBMS's transaction log, which must have a separate
copy of your insert data, to prepare for DBMS commit or rollback.
Joe Weinstein at BEA
|||Thanks Joe. I do agree. So what shall I do? We don't have idea how big
could the file be. Is there any way we may cut file into smaller pieces,
ship the to db and reassembly there? Thanks a lot
Message posted via http://www.sqlmonster.com
|||Fred Wang via SQLMonster.com wrote:
> Thanks Joe. I do agree. So what shall I do? We don't have idea how big
> could the file be. Is there any way we may cut file into smaller pieces,
> ship the to db and reassembly there? Thanks a lot
Well, there might be some ugly hacks, but fundamentally you just want to
get some SQLServer DBA help to configure it so it will handle the type of
transaction you want to do. An RDBMS isn't usually ideal as a store for
huge blobs... Sort of like using the bank to deposit those huge polynesian
coral wheel money tokens. Even the islanders stopped moving them around.
One sunk in a bay while being transported by boat, so they just left it there,
and people just started agreeing on who owned it at any time... ;)
Joe Weinstein at BEA
|||| From: "Fred Wang via SQLMonster.com" <forum@.SQLMonster.com>
| Subject: Error in loading big file
| Date: Fri, 11 Feb 2005 23:13:31 GMT
| Organization: http://www.SQLMonster.com
| Message-ID: <2f0972878fc74c0f8e40521ba0ad6ae3@.SQLMonster.com >
| X-Abuse-Report: http://www.SQLMonster.com/Uwe/NB/Abuse.aspx
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: 178.67-18-207.reverse.theplanet.com 67.18.207.178
| Lines: 1
| Path:
TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp13
.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:866
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi All,
| I have an application on WL8.1 with sp3, the database is SQL Server 2000.
I
| have code below to load local file into database. The data type in
database
| is image.
|
| PreparedStatement pStatement = null;
| InputStreammyStream myStream = new InputStream();
| myStream.setEmbeddedStream( is );
| pStatement.setBinaryStream( 1, myStream, -1 );
| pStatement.executeUpdate();
| pStatement.close();
| pStatement = null;
|
| is is InputStream from a local file and the sql statement is
| insert into file_content(content) values(?)
|
| This workes fine for the files with size less than 150M, but for those big
| files (>150M), it doesn't work and I got error message as below:
|
| <Feb 11, 2005 12:00:41 PM PST> <Notice> <EJB> <BEA-010014> <Error occurred
| while attempting to rollback transaction:
| javax.transaction.SystemException: Heuristic hazard:
| (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
| (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
| been closed.))
| javax.transaction.SystemException: Heuristic hazard:
| (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
| (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
| been closed.))
| at weblogic.transaction.internal.ServerTransactionImp l.internalRollback
| (ServerTransactionImpl.java:396)
| at weblogic.transaction.internal.ServerTransactionImp l.rollback
| (ServerTransactionImpl.java:362)
|
| Any body can help? Thanks in advance.
|
| --
| Message posted via http://www.sqlmonster.com
|
You could insert the new data in chunks by calling UPDATETEXT multiple
times. From Java, you could iteratively call read() on a FileInputStream
object and populate a byte array buffer. For each iteration, you can
execute the stored procedure below and pass the buffer as an input
parameter. This operation may not be logged, depending on the recovery
model of your database (simple or bulk-logged). This may be the way to go
to avoid excessive transaction log usage. However, the downfall is that
you do not get the benefit of a transaction, so a failure along the way
would leave the BLOB data in an incomplete state.
T-SQL
======
CREATE TABLE FredWang([ID] INT PRIMARY KEY, [blob] IMAGE)
GO
INSERT INTO FredWang VALUES(1, CONVERT(VARBINARY(8000), ''))
GO
CREATE PROCEDURE usp_InsertBlob
(
@.row BIGINT,
@.insertOffset INT = NULL,
@.deleteLength INT = 0,
@.data VARBINARY(8000)
)
AS
BEGIN
DECLARE @.textPointer BINARY(16)
SELECT @.textPointer = TEXTPTR(blob) FROM FredWang WHERE [ID] = @.row
DECLARE @.query VARCHAR(4000)
UPDATETEXT FredWang.blob @.textPointer @.insertOffset @.deleteLength @.data
END
GO
Java
=====
import java.sql.*;
import java.io.*;
public class query
{
public static void main(String[] args) throws Exception
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int chunkSize = 8000;
try
{
// Load the JDBC driver
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
// Connect to SQL Server
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jdbc;";
conn = DriverManager.getConnection(url, "sa", "password");
// Load the file into memory
FileInputStream file = new FileInputStream("c:\\large_file.jpg");
// Insert the BLOB data in chunks
String sql = "EXEC usp_InsertBlob 1, DEFAULT, DEFAULT, ?";
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
byte [] buff = new byte[chunkSize];
while(file.read(buff) != -1)
{
pstmt.setBytes(1, buff);
pstmt.executeUpdate();
}
file.close();
}
catch (SQLException sqlEx)
{
while (sqlEx != null)
{
System.out.println("SQLState: " + sqlEx.getSQLState());
System.out.println("Message: " + sqlEx.getMessage());
System.out.println("Error code: " + sqlEx.getErrorCode());
sqlEx = sqlEx.getNextException();
System.out.println();
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
// Cleanup
if (rs != null)
rs.close();
rs = null;
if (pstmt != null)
pstmt.close();
pstmt = null;
if (conn != null)
conn.close();
conn = null;
}
}
}
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
I have an application on WL8.1 with sp3, the database is SQL Server 2000. I
have code below to load local file into database. The data type in database
is image.
PreparedStatement pStatement = null;
InputStreammyStream myStream = new InputStream();
myStream.setEmbeddedStream( is );
pStatement.setBinaryStream( 1, myStream, -1 );
pStatement.executeUpdate();
pStatement.close();
pStatement = null;
is is InputStream from a local file and the sql statement is
insert into file_content(content) values(?)
This workes fine for the files with size less than 150M, but for those big
files (>150M), it doesn't work and I got error message as below:
<Feb 11, 2005 12:00:41 PM PST> <Notice> <EJB> <BEA-010014> <Error occurred
while attempting to rollback transaction:
javax.transaction.SystemException: Heuristic hazard:
(weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
(javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
been closed.))
javax.transaction.SystemException: Heuristic hazard:
(weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
(javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
been closed.))
at weblogic.transaction.internal.ServerTransactionImp l.internalRollback
(ServerTransactionImpl.java:396)
at weblogic.transaction.internal.ServerTransactionImp l.rollback
(ServerTransactionImpl.java:362)
Any body can help? Thanks in advance.
Message posted via http://www.sqlmonster.com
Fred Wang via SQLMonster.com wrote:
> Hi All,
> I have an application on WL8.1 with sp3, the database is SQL Server 2000. I
> have code below to load local file into database. The data type in database
> is image.
> PreparedStatement pStatement = null;
> InputStreammyStream myStream = new InputStream();
> myStream.setEmbeddedStream( is );
> pStatement.setBinaryStream( 1, myStream, -1 );
> pStatement.executeUpdate();
> pStatement.close();
> pStatement = null;
> is is InputStream from a local file and the sql statement is
> insert into file_content(content) values(?)
> This workes fine for the files with size less than 150M, but for those big
> files (>150M), it doesn't work and I got error message as below:
> <Feb 11, 2005 12:00:41 PM PST> <Notice> <EJB> <BEA-010014> <Error occurred
> while attempting to rollback transaction:
> javax.transaction.SystemException: Heuristic hazard:
> (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
> (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
> been closed.))
> javax.transaction.SystemException: Heuristic hazard:
> (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
> (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
> been closed.))
> at weblogic.transaction.internal.ServerTransactionImp l.internalRollback
> (ServerTransactionImpl.java:396)
> at weblogic.transaction.internal.ServerTransactionImp l.rollback
> (ServerTransactionImpl.java:362)
> Any body can help? Thanks in advance.
>
Hi. That means the DBMS choked on the submission and killed the whole
JDBC connection. Check your DBMS log for problems. You may be running
out of space in the DBMS's transaction log, which must have a separate
copy of your insert data, to prepare for DBMS commit or rollback.
Joe Weinstein at BEA
|||Thanks Joe. I do agree. So what shall I do? We don't have idea how big
could the file be. Is there any way we may cut file into smaller pieces,
ship the to db and reassembly there? Thanks a lot
Message posted via http://www.sqlmonster.com
|||Fred Wang via SQLMonster.com wrote:
> Thanks Joe. I do agree. So what shall I do? We don't have idea how big
> could the file be. Is there any way we may cut file into smaller pieces,
> ship the to db and reassembly there? Thanks a lot
Well, there might be some ugly hacks, but fundamentally you just want to
get some SQLServer DBA help to configure it so it will handle the type of
transaction you want to do. An RDBMS isn't usually ideal as a store for
huge blobs... Sort of like using the bank to deposit those huge polynesian
coral wheel money tokens. Even the islanders stopped moving them around.
One sunk in a bay while being transported by boat, so they just left it there,
and people just started agreeing on who owned it at any time... ;)
Joe Weinstein at BEA
|||| From: "Fred Wang via SQLMonster.com" <forum@.SQLMonster.com>
| Subject: Error in loading big file
| Date: Fri, 11 Feb 2005 23:13:31 GMT
| Organization: http://www.SQLMonster.com
| Message-ID: <2f0972878fc74c0f8e40521ba0ad6ae3@.SQLMonster.com >
| X-Abuse-Report: http://www.SQLMonster.com/Uwe/NB/Abuse.aspx
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: 178.67-18-207.reverse.theplanet.com 67.18.207.178
| Lines: 1
| Path:
TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFT NGP08.phx.gbl!tk2msftngp13
.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:866
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi All,
| I have an application on WL8.1 with sp3, the database is SQL Server 2000.
I
| have code below to load local file into database. The data type in
database
| is image.
|
| PreparedStatement pStatement = null;
| InputStreammyStream myStream = new InputStream();
| myStream.setEmbeddedStream( is );
| pStatement.setBinaryStream( 1, myStream, -1 );
| pStatement.executeUpdate();
| pStatement.close();
| pStatement = null;
|
| is is InputStream from a local file and the sql statement is
| insert into file_content(content) values(?)
|
| This workes fine for the files with size less than 150M, but for those big
| files (>150M), it doesn't work and I got error message as below:
|
| <Feb 11, 2005 12:00:41 PM PST> <Notice> <EJB> <BEA-010014> <Error occurred
| while attempting to rollback transaction:
| javax.transaction.SystemException: Heuristic hazard:
| (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
| (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
| been closed.))
| javax.transaction.SystemException: Heuristic hazard:
| (weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
| (javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
| been closed.))
| at weblogic.transaction.internal.ServerTransactionImp l.internalRollback
| (ServerTransactionImpl.java:396)
| at weblogic.transaction.internal.ServerTransactionImp l.rollback
| (ServerTransactionImpl.java:362)
|
| Any body can help? Thanks in advance.
|
| --
| Message posted via http://www.sqlmonster.com
|
You could insert the new data in chunks by calling UPDATETEXT multiple
times. From Java, you could iteratively call read() on a FileInputStream
object and populate a byte array buffer. For each iteration, you can
execute the stored procedure below and pass the buffer as an input
parameter. This operation may not be logged, depending on the recovery
model of your database (simple or bulk-logged). This may be the way to go
to avoid excessive transaction log usage. However, the downfall is that
you do not get the benefit of a transaction, so a failure along the way
would leave the BLOB data in an incomplete state.
T-SQL
======
CREATE TABLE FredWang([ID] INT PRIMARY KEY, [blob] IMAGE)
GO
INSERT INTO FredWang VALUES(1, CONVERT(VARBINARY(8000), ''))
GO
CREATE PROCEDURE usp_InsertBlob
(
@.row BIGINT,
@.insertOffset INT = NULL,
@.deleteLength INT = 0,
@.data VARBINARY(8000)
)
AS
BEGIN
DECLARE @.textPointer BINARY(16)
SELECT @.textPointer = TEXTPTR(blob) FROM FredWang WHERE [ID] = @.row
DECLARE @.query VARCHAR(4000)
UPDATETEXT FredWang.blob @.textPointer @.insertOffset @.deleteLength @.data
END
GO
Java
=====
import java.sql.*;
import java.io.*;
public class query
{
public static void main(String[] args) throws Exception
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int chunkSize = 8000;
try
{
// Load the JDBC driver
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
// Connect to SQL Server
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jdbc;";
conn = DriverManager.getConnection(url, "sa", "password");
// Load the file into memory
FileInputStream file = new FileInputStream("c:\\large_file.jpg");
// Insert the BLOB data in chunks
String sql = "EXEC usp_InsertBlob 1, DEFAULT, DEFAULT, ?";
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
byte [] buff = new byte[chunkSize];
while(file.read(buff) != -1)
{
pstmt.setBytes(1, buff);
pstmt.executeUpdate();
}
file.close();
}
catch (SQLException sqlEx)
{
while (sqlEx != null)
{
System.out.println("SQLState: " + sqlEx.getSQLState());
System.out.println("Message: " + sqlEx.getMessage());
System.out.println("Error code: " + sqlEx.getErrorCode());
sqlEx = sqlEx.getNextException();
System.out.println();
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
// Cleanup
if (rs != null)
rs.close();
rs = null;
if (pstmt != null)
pstmt.close();
pstmt = null;
if (conn != null)
conn.close();
conn = null;
}
}
}
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Sunday, February 26, 2012
Error In Event Viewer
Hi I am getting the following error:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 4/14/2004
Time: 09:32:05 AM
User: SYSTEM
Computer: BSEW2k01
Description:
Error: 50000, Severity: 5, State: 1
Error Calling StatusToStreet In DCS0 Code 7202
Please help.
ThanksThe error doesn't seems to be a system generated as its a user-defined from the application, check the vendor for that application.|||And I would venture to say that
StatusToStreet
Is the sproc and
DCS0
Is the database
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 4/14/2004
Time: 09:32:05 AM
User: SYSTEM
Computer: BSEW2k01
Description:
Error: 50000, Severity: 5, State: 1
Error Calling StatusToStreet In DCS0 Code 7202
Please help.
ThanksThe error doesn't seems to be a system generated as its a user-defined from the application, check the vendor for that application.|||And I would venture to say that
StatusToStreet
Is the sproc and
DCS0
Is the database
Error in Derived Column component
I am getting this error,
[Derived Column [192]] Error: Converting an input column from type DT_STR to type DT_WSTR failed. An error occurred while performing the implicit conversion on the input column.
But I don really understand y there is a attempt to type cast at all ?
Please advise ....
thanks in advance
The expression evaluator almost always implicitly converts DT_STR columns to DT_WSTR, because all string functions are implemented for Unicode only. There are only a couple very specific exceptions.
You might try to putting a data conversion transform in your flow and doing an explicit convert from DT_STR to DT_WSTR for that column and see if you get a more helpful error message. Or, redirect that row to an error output, and inspect the data and post it here.
Thanks
Mark
Mark Durley wrote:
You might try to putting a data conversion transform in your flow and doing an explicit convert from DT_STR to DT_WSTR for that column and see if you get a more helpful error message.
I thought of this earlier and when I tried with a Data Conversion Component, I was not able to locate an option as DT_WSTR. Am I missing something here?
thanks for the Help so far...
|||In the Data Conversion transform UI, in the Data Type drop down, you should see an option:
Unicode string [DT_WSTR]
Mark
|||In the column marked Data Type in your Derived Column Shape change the value from Unicode [DT_WSTR] to string [DT_STR]
Does this answer your question?
|||Thx for the reply :)Sunday, February 19, 2012
Error In Application Logs
hii...
I am using SQL 2000 With sp3a on win200server.
i am getting following error in application log.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 10/31/2003
Time: 3:52:54 PM
User: N/A
Computer: BNET8
Description:
Error: 17832, Severity: 20, State: 8
Connection opened but invalid login packet(s) sent. Connection closed. Data:
0000: a8 45 00 00 14 00 00 00 =A8E.....
0008: 06 00 00 00 42 00 4e 00 ...B.N.
0010: 45 00 54 00 38 00 00 00 E.T.8...
0018: 00 00 00 00 ...
Please explain? what is this and solution.
Thanks in advanceHi ,
The error (17832) do not indicate a problem with SQL Server, but rather a
network failure, or client application problem.
17832 can happen if a client starts to connect, but never successfully
completes the attempt.
Normally this error is caused by the network failing between the time a
connection attempt is initiated, and when it completes.
You can just ignore this error incase the occurance of this error is
limited. Incase if the occurance is higher please check with your network
services.
Thanks
Hari
MCDBA
"sandi" <anonymous@.discussions.microsoft.com> wrote in message
news:057a01c3a026$9171c6a0$a301280a@.phx.gbl...
hii...
I am using SQL 2000 With sp3a on win200server.
i am getting following error in application log.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 10/31/2003
Time: 3:52:54 PM
User: N/A
Computer: BNET8
Description:
Error: 17832, Severity: 20, State: 8
Connection opened but invalid login packet(s) sent.
Connection closed.
Data:
0000: a8 45 00 00 14 00 00 00 ¨E.....
0008: 06 00 00 00 42 00 4e 00 ...B.N.
0010: 45 00 54 00 38 00 00 00 E.T.8...
0018: 00 00 00 00 ...
Please explain? what is this and solution.
Thanks in advance|||Dear Hari,
Thanks for your help.
sandi
>--Original Message--
>Hi ,
>The error (17832) do not indicate a problem with SQL Server, but rather a
>network failure, or client application problem.
>17832 can happen if a client starts to connect, but never successfully
>completes the attempt.
>Normally this error is caused by the network failing between the time a
>connection attempt is initiated, and when it completes.
>You can just ignore this error incase the occurance of this error is
>limited. Incase if the occurance is higher please check with your network
>services.
>Thanks
>Hari
>MCDBA
>"sandi" <anonymous@.discussions.microsoft.com> wrote in message
>news:057a01c3a026$9171c6a0$a301280a@.phx.gbl...
>hii...
>I am using SQL 2000 With sp3a on win200server.
>i am getting following error in application log.
>Event Type: Error
>Event Source: MSSQLSERVER
>Event Category: (2)
>Event ID: 17052
>Date: 10/31/2003
>Time: 3:52:54 PM
>User: N/A
>Computer: BNET8
>Description:
>Error: 17832, Severity: 20, State: 8
>Connection opened but invalid login packet(s) sent.
>Connection closed.
>Data:
>0000: a8 45 00 00 14 00 00 00 =A8E.....
>0008: 06 00 00 00 42 00 4e 00 ...B.N.
>0010: 45 00 54 00 38 00 00 00 E.T.8...
>0018: 00 00 00 00 ...
>Please explain? what is this and solution.
>Thanks in advance
>
>.
>
I am using SQL 2000 With sp3a on win200server.
i am getting following error in application log.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 10/31/2003
Time: 3:52:54 PM
User: N/A
Computer: BNET8
Description:
Error: 17832, Severity: 20, State: 8
Connection opened but invalid login packet(s) sent. Connection closed. Data:
0000: a8 45 00 00 14 00 00 00 =A8E.....
0008: 06 00 00 00 42 00 4e 00 ...B.N.
0010: 45 00 54 00 38 00 00 00 E.T.8...
0018: 00 00 00 00 ...
Please explain? what is this and solution.
Thanks in advanceHi ,
The error (17832) do not indicate a problem with SQL Server, but rather a
network failure, or client application problem.
17832 can happen if a client starts to connect, but never successfully
completes the attempt.
Normally this error is caused by the network failing between the time a
connection attempt is initiated, and when it completes.
You can just ignore this error incase the occurance of this error is
limited. Incase if the occurance is higher please check with your network
services.
Thanks
Hari
MCDBA
"sandi" <anonymous@.discussions.microsoft.com> wrote in message
news:057a01c3a026$9171c6a0$a301280a@.phx.gbl...
hii...
I am using SQL 2000 With sp3a on win200server.
i am getting following error in application log.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 10/31/2003
Time: 3:52:54 PM
User: N/A
Computer: BNET8
Description:
Error: 17832, Severity: 20, State: 8
Connection opened but invalid login packet(s) sent.
Connection closed.
Data:
0000: a8 45 00 00 14 00 00 00 ¨E.....
0008: 06 00 00 00 42 00 4e 00 ...B.N.
0010: 45 00 54 00 38 00 00 00 E.T.8...
0018: 00 00 00 00 ...
Please explain? what is this and solution.
Thanks in advance|||Dear Hari,
Thanks for your help.
sandi
>--Original Message--
>Hi ,
>The error (17832) do not indicate a problem with SQL Server, but rather a
>network failure, or client application problem.
>17832 can happen if a client starts to connect, but never successfully
>completes the attempt.
>Normally this error is caused by the network failing between the time a
>connection attempt is initiated, and when it completes.
>You can just ignore this error incase the occurance of this error is
>limited. Incase if the occurance is higher please check with your network
>services.
>Thanks
>Hari
>MCDBA
>"sandi" <anonymous@.discussions.microsoft.com> wrote in message
>news:057a01c3a026$9171c6a0$a301280a@.phx.gbl...
>hii...
>I am using SQL 2000 With sp3a on win200server.
>i am getting following error in application log.
>Event Type: Error
>Event Source: MSSQLSERVER
>Event Category: (2)
>Event ID: 17052
>Date: 10/31/2003
>Time: 3:52:54 PM
>User: N/A
>Computer: BNET8
>Description:
>Error: 17832, Severity: 20, State: 8
>Connection opened but invalid login packet(s) sent.
>Connection closed.
>Data:
>0000: a8 45 00 00 14 00 00 00 =A8E.....
>0008: 06 00 00 00 42 00 4e 00 ...B.N.
>0010: 45 00 54 00 38 00 00 00 E.T.8...
>0018: 00 00 00 00 ...
>Please explain? what is this and solution.
>Thanks in advance
>
>.
>
Wednesday, February 15, 2012
Error Handling SQL 2000
Hello,
Could it be possible to catch into a variable any type of sql server error?
I have an stored procedured that executes as a part of a cycle one stored procedured many times (one for each branch). I need to make the stored procedured to continue even when I have an error in one of the executions (one of the procedures of a branch).
For example, I execute this as a part of a While Statement
Exec @.return_status = @.sp_name @.Link, @.Historia, @.begindate, @.enddate
If @.return_status <> 0
Begin Print ' /* Exito */'
..... update
End
Else
Begin Print '/* Error */'
....
EXEC master.dbo.xp_sendmail
@.recipients = @.mail_recipient,
@.subject = @.mail_subject,
@.message = @.mail_query
End
... But when I get an error like this, the stored just ends the cycle and the stored procedured.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. Authentication failed.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
Wish you can help me, thank you
I wish I could help you too. In SqL Server 2000 the only thing you can do is check the value of the error number in the @.@.error system function (used like a variable). So, you may be able to do something like this:
Exec @.return_status = @.sp_name @.Link, @.Historia, @.begindate, @.enddate
If @.@.error <> 0 or @.return_status <> 0
But it could stop the procedure, and it will return the error to the client for handling. In 2005 you have TRY...CATCH, but even that isn't flawless with some errors. For an excellent reference on 2000 error handling, check this article: http://www.sommarskog.se/error-handling-II.html
|||and don't forget the first in the series:
http://www.sommarskog.se/error-handling-I.html
http://www.elsasoft.org
|||Thank you Louis
Error Handling in SQL?
Hi there,
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LX
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LX
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
Error Handling in SQL?
Hi there,
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LXhttp://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
stored
> procedure, which is part of a large system. We don't have the error
handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LXhttp://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
stored
> procedure, which is part of a large system. We don't have the error
handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
Error Handling in SQL?
Hi there,
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LXhttp://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
--
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
stored
> procedure, which is part of a large system. We don't have the error
handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
A insert query fails because of the erroneous data (primary key violation,
data type mismatch, etc). This insert statement is in the middle of a stored
procedure, which is part of a large system. We don't have the error handling
code in the system. Whenver error happens, it simply exits.
The big problem is that for such case, one bad record in the insert query
can take down the entire system.
It will be ideal if this query can automatically re-run when it failed in
the first run and filter out the "problem" records and succeed in the second
attempt without taking down the entire system.
Any idea, sample code or suggestion are highly appreciated.
LXhttp://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
will get you started.
--
Jacco Schalkwijk
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
> stored
> procedure, which is part of a large system. We don't have the error
> handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
> second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>|||data type mis-match will abort processing, primary key errors won't. Jacco's
already pointed you out to the best articles available on tsql error
handling but I'd add that you really need to handle error conditions in your
client application if you want re-try logic. It's the only place where
that's even possible if TSQL aborts processing..
HTH
Regards,
Greg Linwood
SQL Server MVP
"FLX" <nospam@.hotmail.com> wrote in message
news:%23qlKbtXeEHA.724@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> A insert query fails because of the erroneous data (primary key violation,
> data type mismatch, etc). This insert statement is in the middle of a
stored
> procedure, which is part of a large system. We don't have the error
handling
> code in the system. Whenver error happens, it simply exits.
> The big problem is that for such case, one bad record in the insert query
> can take down the entire system.
> It will be ideal if this query can automatically re-run when it failed in
> the first run and filter out the "problem" records and succeed in the
second
> attempt without taking down the entire system.
> Any idea, sample code or suggestion are highly appreciated.
> LX
>
Subscribe to:
Posts (Atom)