INSRET/UPDATE trigger

Hi,

I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this rollback record in the table? It seems it does and I am wondering if there is any workaround.

Thanks,

It depends on the severity of the error. You should do RAISERROR with a lesser severity and then do ROLLBACK explicitly. See Books Online topic on RAISERROR and how to use it in triggers in SQL Server 2005. There are several samples that shows how to use RAISERROR.

Insertion failed

I have a service that writes a lot of records to many
tables in SQL server. Recently, the service failed in
writing record to any of the tables. But, the retrieving
of the records is still OK. By examining the application
log file, it seems that the SQL server started to
deteriorate by not allowing insertion in one table at a
time and eventually all tables are rejected for insertion.
I ended up re-booting the system and everything went back
to normal. After the reboot, I checked the SQL log file
and noticed that about 18,000 records were rolled forward
during SQL Server start-up.
Does anybody have any idea as why the insertion would
fail? And why the checkpoint was not executed even there
are about 18,000 committed transactions?Mike,
Perhaps the database space filled up as one table attempted to grow again.
Tables that still had some space left in them could take a few more rows,
then they were filled up as well and would no longer take any more rows.
Etc. Etc.
If this is the case, either allocate more space or turn on autogrow for the
database in question.
With regard to your closing comment, checkpoint is not equal to a commit.
Without knowing the period covered by the 18,000 records and something more
about your server it is not possible to offer details. However, I would
think that checkpointing was working just fine.
Russell Fields
"mike" <mike@.ftsl.com> wrote in message
news:5c0401c3e5e3$cb4094d0$a101280a@.phx.gbl...

quote:

---

> I have a service that writes a lot of records to many
> tables in SQL server. Recently, the service failed in
> writing record to any of the tables. But, the retrieving
> of the records is still OK. By examining the application
> log file, it seems that the SQL server started to
> deteriorate by not allowing insertion in one table at a
> time and eventually all tables are rejected for insertion.
> I ended up re-booting the system and everything went back
> to normal. After the reboot, I checked the SQL log file
> and noticed that about 18,000 records were rolled forward
> during SQL Server start-up.
> Does anybody have any idea as why the insertion would
> fail? And why the checkpoint was not executed even there
> are about 18,000 committed transactions?
>

---

|||Russell - Thank you for your reply.
I was also thinking about the possible disk space problem
but I don't think it's the case here. There was about 10G
left in the disk when this happened. At the same time, the
database files (data & transaction) are set to auto-grow.
If disk space was the problem, I would think rebooting the
server couldn't solve the problem. Based on my
calculation, in 5 mins period, properly 3500 record would
have been inserted to the table.

quote:

---

>--Original Message--
>Mike,
>Perhaps the database space filled up as one table

---

attempted to grow again.

quote:

---

>Tables that still had some space left in them could take

---

a few more rows,

quote:

---

>then they were filled up as well and would no longer take

---

any more rows.

quote:

---

>Etc. Etc.
>If this is the case, either allocate more space or turn

---

on autogrow for the

quote:

---

>database in question.
>With regard to your closing comment, checkpoint is not

---

equal to a commit.

quote:

---

>Without knowing the period covered by the 18,000 records

---

and something more

quote:

---

>about your server it is not possible to offer details.

---

However, I would

quote:

---

>think that checkpointing was working just fine.
>Russell Fields
>"mike" <mike@.ftsl.com> wrote in message
>news:5c0401c3e5e3$cb4094d0$a101280a@.phx.gbl...
insertion.[QUOTE]
back[QUOTE]
forward[QUOTE]
>
>.
>

---

|||Mike,
Sorry that I do not have a better idea.
We had problems at one time when another process besides SQL Server was
using the same disk. It wrote LOTS of data into an operating system file
eating up the disk, then either deleted it again or ran out of space itself,
causing the file write to abort. Since you have 10 GB free, then it is hard
to believe that a similar thing is happening to you.
Regarding checkpoints, this is set using the "recovery interval" option.
You might read up on this and discover that the timing of checkpoints is
more complicated than you would have thought.
Russell Fields
"mike" <mike@.ftsl.com> wrote in message
news:635a01c3e5ef$46160ae0$a501280a@.phx.gbl...[QUOTE]
> Russell - Thank you for your reply.
> I was also thinking about the possible disk space problem
> but I don't think it's the case here. There was about 10G
> left in the disk when this happened. At the same time, the
> database files (data & transaction) are set to auto-grow.
> If disk space was the problem, I would think rebooting the
> server couldn't solve the problem. Based on my
> calculation, in 5 mins period, properly 3500 record would
> have been inserted to the table.
>
> attempted to grow again.
> a few more rows,
> any more rows.
> on autogrow for the
> equal to a commit.
> and something more
> However, I would
> insertion.
> back
> forward|||Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As my understanding of your problem, when inserting a large amount of
records to many tables but failed by insert rejection to tables one by one.
You rebooted the system and everything seems OK again. and you found in SQL
log that SQL Server rolled forward the inserting during the startup, so
many records are inserting, you wonder why insert failed and how the
rolling forward come, right?
I would better to explain the rolling forwand first. The log records for a
transaction ( both a transaction.. commit structure or a single T-SQL
statement) are written to disk before the commit acknowledgement is sent to
the client process, but the actual changed data might not have been
physically written out to the data pages. That is, writes to data pages
need only be posted to the operating system, and SQL Server can check later
to see that they were completed. They don't have to complete immediately
because the log contains all the information needed to redo the work, even
in the event of a power failure or system crash before the write completes.
When you reboot you system and start SQL Server, recovery performs both
redo (rollforward) and undo (rollback) operations. In a redo operation, the
log is examined and each change is verified as being already reflected in
the database. (After a redo, every change made by the transaction is
guaranteed to have been applied.) If the change doesn't appear in the
database, it is again performed from the information in the log. Every
database page has an LSN in the page header that uniquely identifies it, by
version, as rows on the page are changed over time. This page LSN reflects
the location in the transaction log of the last log entry that modified a
row on this page. During a redo operation of transactions, the LSN of each
log record is compared to the page LSN of the data page that the log entry
modified; if the page LSN is less than the log LSN, the operation indicated
in the log entry should be redo, that is, should be roll forward. As for
the 18,000 record insert in the SQL start process, they are the modified
records that have been recorded in the transaction log, but have not been
writen to the database.
When you commit the transaction, the data modifications will be have been
made a permanent part of the database. no roll forward or roll back is
needed.
For the checkpiont, it will flush dirty data and log pages from the buffer
cache of the current database, minimizing the number of modifications that
have to be rolled forward during a recovery. So, that 18,000 records
insertings active operations that have been recorded in the log file after
or when check point occures, they are logged into the transaction log. For
more information of checkpoint, when it will occure, what SQL Server will
do when checkpoint come, please refer to 'checkpoing' in the SQL Server
Books Online.
As for your questions of how the records insertings are rejected by tables
one by one, it would be very helpful for you to provide the detailed error
message, the error log and the T-SQL you were excuting at that time for
further analysis. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
I have a service that writes a lot of records to many
tables in SQL server. Recently, the service failed in
writing record to any of the tables. But, the retrieving
of the records is still OK. By examining the application
log file, it seems that the SQL server started to
deteriorate by not allowing insertion in one table at a
time and eventually all tables are rejected for insertion.
I ended up re-booting the system and everything went back
to normal. After the reboot, I checked the SQL log file
and noticed that about 18,000 records were rolled forward
during SQL Server start-up.
Does anybody have any idea as why the insertion would
fail? And why the checkpoint was not executed even there
are about 18,000 committed transactions?|||From: v-baiwei@.online.microsoft.com (Baisong Wei[MSFT])
Date: Fri, 30 Jan 2004 05:10:41 GMT
Subject: RE: Insertion failed
Newsgroups: microsoft.public.sqlserver.server
Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As my understanding of your problem, when inserting a large amount of
records to many tables but failed by insert rejection to tables one by one.
You rebooted the system and everything seems OK again. and you found in SQL
log that SQL Server rolled forward the inserting during the startup, so
many records are inserting, you wonder why insert failed and how the
rolling forward come, right?
I would better to explain the rolling forwand first. The log records for a
transaction ( both a transaction.. commit structure or a single T-SQL
statement) are written to disk before the commit acknowledgement is sent to
the client process, but the actual changed data might not have been
physically written out to the data pages. That is, writes to data pages
need only be posted to the operating system, and SQL Server can check later
to see that they were completed. They don't have to complete immediately
because the log contains all the information needed to redo the work, even
in the event of a power failure or system crash before the write completes.
When you reboot you system and start SQL Server, recovery performs both
redo (rollforward) and undo (rollback) operations. In a redo operation, the
log is examined and each change is verified as being already reflected in
the database. (After a redo, every change made by the transaction is
guaranteed to have been applied.) If the change doesn't appear in the
database, it is again performed from the information in the log. Every
database page has an LSN in the page header that uniquely identifies it, by
version, as rows on the page are changed over time. This page LSN reflects
the location in the transaction log of the last log entry that modified a
row on this page. During a redo operation of transactions, the LSN of each
log record is compared to the page LSN of the data page that the log entry
modified; if the page LSN is less than the log LSN, the operation indicated
in the log entry should be redo, that is, should be roll forward. As for
the 18,000 record insert in the SQL start process, they are the modified
records that have been recorded in the transaction log, but have not been
writen to the database.
When you commit the transaction, the data modifications will be have been
made a permanent part of the database ( in the log file).
For the checkpiont, it will flush dirty data and log pages from the buffer
cache of the current database, minimizing the number of modifications that
have to be rolled forward during a recovery. So, that 18,000 records
insertings active operations that have been recorded in the log file after
or when check point occures, they are logged into the transaction log. For
more information of checkpoint, when it will occure, what SQL Server will
do when checkpoint come, please refer to 'checkpoing' in the SQL Server
Books Online. You could also refer to 'recoery mode' too.
As for your questions of how the records insertings are rejected by tables
one by one, it would be very helpful for you to provide the detailed error
message, the error log and the T-SQL you were excuting at that time for
further analysis. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As my understanding of your problem, when inserting a large amount of
records to many tables but failed by insert rejection to tables one by one.
You rebooted the system and everything seems OK again. and you found in SQL
log that SQL Server rolled forward the inserting during the startup, so
many records are inserting, you wonder why insert failed and how the
rolling forward come, right?
I would better to explain the rolling forwand first. The log records for a
transaction ( both a transaction.. commit structure or a single T-SQL
statement) are written to disk before the commit acknowledgement is sent to
the client process, but the actual changed data might not have been
physically written out to the data pages. That is, writes to data pages
need only be posted to the operating system, and SQL Server can check later
to see that they were completed. They don't have to complete immediately
because the log contains all the information needed to redo the work, even
in the event of a power failure or system crash before the write completes.
When you reboot you system and start SQL Server, recovery performs both
redo (rollforward) and undo (rollback) operations. In a redo operation, the
log is examined and each change is verified as being already reflected in
the database. (After a redo, every change made by the transaction is
guaranteed to have been applied.) If the change doesn't appear in the
database, it is again performed from the information in the log. Every
database page has an LSN in the page header that uniquely identifies it, by
version, as rows on the page are changed over time. This page LSN reflects
the location in the transaction log of the last log entry that modified a
row on this page. During a redo operation of transactions, the LSN of each
log record is compared to the page LSN of the data page that the log entry
modified; if the page LSN is less than the log LSN, the operation indicated
in the log entry should be redo, that is, should be roll forward. As for
the 18,000 record insert in the SQL start process, they are the modified
records that have been recorded in the transaction log, but have not been
writen to the database.
When you commit the transaction, the data modifications will be have been
made a permanent part of the database ( in the log file).
For the checkpiont, it will flush dirty data and log pages from the buffer
cache of the current database, minimizing the number of modifications that
have to be rolled forward during a recovery. So, that 18,000 records
insertings active operations that have been recorded in the log file after
or when check point occures, they are logged into the transaction log. For
more information of checkpoint, when it will occure, what SQL Server will
do when checkpoint come, please refer to 'checkpoing' in the SQL Server
Books Online. You could also refer to 'recoery mode' too.
As for your questions of how the records insertings are rejected by tables
one by one, it would be very helpful for you to provide the detailed error
message, the error log and the T-SQL you were excuting at that time for
further analysis. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Baisong,
Thank you for your reply and I understand the description
provided. Here is more background regarding to my problem:
1. We use ADO command object to perform single T-SQL
statement with a time-out value set to one minute.
2. On average, we perform approximately 36,960 T-SQL
statements per hour.
3. Database and transaction files are set to auto grew at
a rate of (10%)
4. Unfortunately, we don't know what the error message was
generated by SQL Server when the records failed to be
written.
5. All the non-SQL processes appeared to be running
normally at the time of the restart. This information was
gathered using Windows Task Manager.
6. No rolled-forward information was logged in SQL Server
log files in previous restarts.
7. Prior to the restart, new information was unable to
insert into the database, but old records can be retrieved.
8. The largest table in the database had 25,000,000
records at the time of the problem.
Question:
What could be happening on the server that would result in
1) failed to insert, and 2) rolled forward of 18000
records in restart.|||Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
In you first post, you mentioned that 'By examining the application log
file, it seems that the SQL server started to deteriorate by not allowing
insertion in one table at a time and eventually all tables are rejected for
insertion.' and in your last post, you mentioned that 'we don't know what
the error message was generated by SQL Server when the records failed to be
written'. I wonder if you could provide the content of this part of the log
that you made this judgement, what made you think that the insertion is
rejected while no error message indicating any abnormal. Any blocking of
locks or any information from you application side? This information is
helpful for our analysis. Second, as I mentioned in my last post, when an
operation is executed, this operation will be write to log but not
necessary to write to .MDB file, when checkpoint come, they will be write
to the MDB file. I think that it is because the 18,000 records' insert have
been written into the log file, but not to the .MDB file, so, when SQL
Server restart, it will compare with the LSN of page and log to decide
roll-forward or roll-back, no difference between, in the SQL Server start
process, no this action is taken, as in your past restart. Besides the log
message, could you please provide the recovery mode of you database?
As the ADO time-out option, I suggest you to set to zero, which prepresent
no limit for time-out.
Looking for your reply and thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Baisong,
What I meant by 'By examining the application log
file,...' is checking the SQL server tables that the
application is writing to. All the written records
contain a time stamp; therefore I can tell when the
insertion started to fail on these tables.
'we don't know what the error message was generated by SQL
Server when the records failed' simply means that the
application did not log any error returned by the ADO
command object.
The recovery mode that the database uses is 'Full Recovery
Mode'
Mike

Insertion failed

I have a service that writes a lot of records to many
tables in SQL server. Recently, the service failed in
writing record to any of the tables. But, the retrieving
of the records is still OK. By examining the application
log file, it seems that the SQL server started to
deteriorate by not allowing insertion in one table at a
time and eventually all tables are rejected for insertion.
I ended up re-booting the system and everything went back
to normal. After the reboot, I checked the SQL log file
and noticed that about 18,000 records were rolled forward
during SQL Server start-up.
Does anybody have any idea as why the insertion would
fail? And why the checkpoint was not executed even there
are about 18,000 committed transactions?Mike,
Perhaps the database space filled up as one table attempted to grow again.
Tables that still had some space left in them could take a few more rows,
then they were filled up as well and would no longer take any more rows.
Etc. Etc.
If this is the case, either allocate more space or turn on autogrow for the
database in question.
With regard to your closing comment, checkpoint is not equal to a commit.
Without knowing the period covered by the 18,000 records and something more
about your server it is not possible to offer details. However, I would
think that checkpointing was working just fine.
Russell Fields
"mike" <mike@.ftsl.com> wrote in message
news:5c0401c3e5e3$cb4094d0$a101280a@.phx.gbl...
> I have a service that writes a lot of records to many
> tables in SQL server. Recently, the service failed in
> writing record to any of the tables. But, the retrieving
> of the records is still OK. By examining the application
> log file, it seems that the SQL server started to
> deteriorate by not allowing insertion in one table at a
> time and eventually all tables are rejected for insertion.
> I ended up re-booting the system and everything went back
> to normal. After the reboot, I checked the SQL log file
> and noticed that about 18,000 records were rolled forward
> during SQL Server start-up.
> Does anybody have any idea as why the insertion would
> fail? And why the checkpoint was not executed even there
> are about 18,000 committed transactions?
>|||Russell - Thank you for your reply.
I was also thinking about the possible disk space problem
but I don't think it's the case here. There was about 10G
left in the disk when this happened. At the same time, the
database files (data & transaction) are set to auto-grow.
If disk space was the problem, I would think rebooting the
server couldn't solve the problem. Based on my
calculation, in 5 mins period, properly 3500 record would
have been inserted to the table.
>--Original Message--
>Mike,
>Perhaps the database space filled up as one table
attempted to grow again.
>Tables that still had some space left in them could take
a few more rows,
>then they were filled up as well and would no longer take
any more rows.
>Etc. Etc.
>If this is the case, either allocate more space or turn
on autogrow for the
>database in question.
>With regard to your closing comment, checkpoint is not
equal to a commit.
>Without knowing the period covered by the 18,000 records
and something more
>about your server it is not possible to offer details.
However, I would
>think that checkpointing was working just fine.
>Russell Fields
>"mike" <mike@.ftsl.com> wrote in message
>news:5c0401c3e5e3$cb4094d0$a101280a@.phx.gbl...
>> I have a service that writes a lot of records to many
>> tables in SQL server. Recently, the service failed in
>> writing record to any of the tables. But, the retrieving
>> of the records is still OK. By examining the application
>> log file, it seems that the SQL server started to
>> deteriorate by not allowing insertion in one table at a
>> time and eventually all tables are rejected for
insertion.
>> I ended up re-booting the system and everything went
back
>> to normal. After the reboot, I checked the SQL log file
>> and noticed that about 18,000 records were rolled
forward
>> during SQL Server start-up.
>> Does anybody have any idea as why the insertion would
>> fail? And why the checkpoint was not executed even there
>> are about 18,000 committed transactions?
>
>.
>|||Mike,
Sorry that I do not have a better idea.
We had problems at one time when another process besides SQL Server was
using the same disk. It wrote LOTS of data into an operating system file
eating up the disk, then either deleted it again or ran out of space itself,
causing the file write to abort. Since you have 10 GB free, then it is hard
to believe that a similar thing is happening to you.
Regarding checkpoints, this is set using the "recovery interval" option.
You might read up on this and discover that the timing of checkpoints is
more complicated than you would have thought.
Russell Fields
"mike" <mike@.ftsl.com> wrote in message
news:635a01c3e5ef$46160ae0$a501280a@.phx.gbl...
> Russell - Thank you for your reply.
> I was also thinking about the possible disk space problem
> but I don't think it's the case here. There was about 10G
> left in the disk when this happened. At the same time, the
> database files (data & transaction) are set to auto-grow.
> If disk space was the problem, I would think rebooting the
> server couldn't solve the problem. Based on my
> calculation, in 5 mins period, properly 3500 record would
> have been inserted to the table.
> >--Original Message--
> >Mike,
> >
> >Perhaps the database space filled up as one table
> attempted to grow again.
> >Tables that still had some space left in them could take
> a few more rows,
> >then they were filled up as well and would no longer take
> any more rows.
> >Etc. Etc.
> >
> >If this is the case, either allocate more space or turn
> on autogrow for the
> >database in question.
> >
> >With regard to your closing comment, checkpoint is not
> equal to a commit.
> >Without knowing the period covered by the 18,000 records
> and something more
> >about your server it is not possible to offer details.
> However, I would
> >think that checkpointing was working just fine.
> >
> >Russell Fields
> >"mike" <mike@.ftsl.com> wrote in message
> >news:5c0401c3e5e3$cb4094d0$a101280a@.phx.gbl...
> >> I have a service that writes a lot of records to many
> >> tables in SQL server. Recently, the service failed in
> >> writing record to any of the tables. But, the retrieving
> >> of the records is still OK. By examining the application
> >> log file, it seems that the SQL server started to
> >> deteriorate by not allowing insertion in one table at a
> >> time and eventually all tables are rejected for
> insertion.
> >> I ended up re-booting the system and everything went
> back
> >> to normal. After the reboot, I checked the SQL log file
> >> and noticed that about 18,000 records were rolled
> forward
> >> during SQL Server start-up.
> >>
> >> Does anybody have any idea as why the insertion would
> >> fail? And why the checkpoint was not executed even there
> >> are about 18,000 committed transactions?
> >>
> >
> >
> >.
> >|||Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As my understanding of your problem, when inserting a large amount of
records to many tables but failed by insert rejection to tables one by one.
You rebooted the system and everything seems OK again. and you found in SQL
log that SQL Server rolled forward the inserting during the startup, so
many records are inserting, you wonder why insert failed and how the
rolling forward come, right?
I would better to explain the rolling forwand first. The log records for a
transaction ( both a transaction.. commit structure or a single T-SQL
statement) are written to disk before the commit acknowledgement is sent to
the client process, but the actual changed data might not have been
physically written out to the data pages. That is, writes to data pages
need only be posted to the operating system, and SQL Server can check later
to see that they were completed. They don't have to complete immediately
because the log contains all the information needed to redo the work, even
in the event of a power failure or system crash before the write completes.
When you reboot you system and start SQL Server, recovery performs both
redo (rollforward) and undo (rollback) operations. In a redo operation, the
log is examined and each change is verified as being already reflected in
the database. (After a redo, every change made by the transaction is
guaranteed to have been applied.) If the change doesn't appear in the
database, it is again performed from the information in the log. Every
database page has an LSN in the page header that uniquely identifies it, by
version, as rows on the page are changed over time. This page LSN reflects
the location in the transaction log of the last log entry that modified a
row on this page. During a redo operation of transactions, the LSN of each
log record is compared to the page LSN of the data page that the log entry
modified; if the page LSN is less than the log LSN, the operation indicated
in the log entry should be redo, that is, should be roll forward. As for
the 18,000 record insert in the SQL start process, they are the modified
records that have been recorded in the transaction log, but have not been
writen to the database.
When you commit the transaction, the data modifications will be have been
made a permanent part of the database. no roll forward or roll back is
needed.
For the checkpiont, it will flush dirty data and log pages from the buffer
cache of the current database, minimizing the number of modifications that
have to be rolled forward during a recovery. So, that 18,000 records
insertings active operations that have been recorded in the log file after
or when check point occures, they are logged into the transaction log. For
more information of checkpoint, when it will occure, what SQL Server will
do when checkpoint come, please refer to 'checkpoing' in the SQL Server
Books Online.
As for your questions of how the records insertings are rejected by tables
one by one, it would be very helpful for you to provide the detailed error
message, the error log and the T-SQL you were excuting at that time for
further analysis. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
I have a service that writes a lot of records to many
tables in SQL server. Recently, the service failed in
writing record to any of the tables. But, the retrieving
of the records is still OK. By examining the application
log file, it seems that the SQL server started to
deteriorate by not allowing insertion in one table at a
time and eventually all tables are rejected for insertion.
I ended up re-booting the system and everything went back
to normal. After the reboot, I checked the SQL log file
and noticed that about 18,000 records were rolled forward
during SQL Server start-up.
Does anybody have any idea as why the insertion would
fail? And why the checkpoint was not executed even there
are about 18,000 committed transactions?|||From: v-baiwei@.online.microsoft.com (Baisong Wei[MSFT])
Date: Fri, 30 Jan 2004 05:10:41 GMT
Subject: RE: Insertion failed
Newsgroups: microsoft.public.sqlserver.server
Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As my understanding of your problem, when inserting a large amount of
records to many tables but failed by insert rejection to tables one by one.
You rebooted the system and everything seems OK again. and you found in SQL
log that SQL Server rolled forward the inserting during the startup, so
many records are inserting, you wonder why insert failed and how the
rolling forward come, right?
I would better to explain the rolling forwand first. The log records for a
transaction ( both a transaction.. commit structure or a single T-SQL
statement) are written to disk before the commit acknowledgement is sent to
the client process, but the actual changed data might not have been
physically written out to the data pages. That is, writes to data pages
need only be posted to the operating system, and SQL Server can check later
to see that they were completed. They don't have to complete immediately
because the log contains all the information needed to redo the work, even
in the event of a power failure or system crash before the write completes.
When you reboot you system and start SQL Server, recovery performs both
redo (rollforward) and undo (rollback) operations. In a redo operation, the
log is examined and each change is verified as being already reflected in
the database. (After a redo, every change made by the transaction is
guaranteed to have been applied.) If the change doesn't appear in the
database, it is again performed from the information in the log. Every
database page has an LSN in the page header that uniquely identifies it, by
version, as rows on the page are changed over time. This page LSN reflects
the location in the transaction log of the last log entry that modified a
row on this page. During a redo operation of transactions, the LSN of each
log record is compared to the page LSN of the data page that the log entry
modified; if the page LSN is less than the log LSN, the operation indicated
in the log entry should be redo, that is, should be roll forward. As for
the 18,000 record insert in the SQL start process, they are the modified
records that have been recorded in the transaction log, but have not been
writen to the database.
When you commit the transaction, the data modifications will be have been
made a permanent part of the database ( in the log file).
For the checkpiont, it will flush dirty data and log pages from the buffer
cache of the current database, minimizing the number of modifications that
have to be rolled forward during a recovery. So, that 18,000 records
insertings active operations that have been recorded in the log file after
or when check point occures, they are logged into the transaction log. For
more information of checkpoint, when it will occure, what SQL Server will
do when checkpoint come, please refer to 'checkpoing' in the SQL Server
Books Online. You could also refer to 'recoery mode' too.
As for your questions of how the records insertings are rejected by tables
one by one, it would be very helpful for you to provide the detailed error
message, the error log and the T-SQL you were excuting at that time for
further analysis. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As my understanding of your problem, when inserting a large amount of
records to many tables but failed by insert rejection to tables one by one.
You rebooted the system and everything seems OK again. and you found in SQL
log that SQL Server rolled forward the inserting during the startup, so
many records are inserting, you wonder why insert failed and how the
rolling forward come, right?
I would better to explain the rolling forwand first. The log records for a
transaction ( both a transaction.. commit structure or a single T-SQL
statement) are written to disk before the commit acknowledgement is sent to
the client process, but the actual changed data might not have been
physically written out to the data pages. That is, writes to data pages
need only be posted to the operating system, and SQL Server can check later
to see that they were completed. They don't have to complete immediately
because the log contains all the information needed to redo the work, even
in the event of a power failure or system crash before the write completes.
When you reboot you system and start SQL Server, recovery performs both
redo (rollforward) and undo (rollback) operations. In a redo operation, the
log is examined and each change is verified as being already reflected in
the database. (After a redo, every change made by the transaction is
guaranteed to have been applied.) If the change doesn't appear in the
database, it is again performed from the information in the log. Every
database page has an LSN in the page header that uniquely identifies it, by
version, as rows on the page are changed over time. This page LSN reflects
the location in the transaction log of the last log entry that modified a
row on this page. During a redo operation of transactions, the LSN of each
log record is compared to the page LSN of the data page that the log entry
modified; if the page LSN is less than the log LSN, the operation indicated
in the log entry should be redo, that is, should be roll forward. As for
the 18,000 record insert in the SQL start process, they are the modified
records that have been recorded in the transaction log, but have not been
writen to the database.
When you commit the transaction, the data modifications will be have been
made a permanent part of the database ( in the log file).
For the checkpiont, it will flush dirty data and log pages from the buffer
cache of the current database, minimizing the number of modifications that
have to be rolled forward during a recovery. So, that 18,000 records
insertings active operations that have been recorded in the log file after
or when check point occures, they are logged into the transaction log. For
more information of checkpoint, when it will occure, what SQL Server will
do when checkpoint come, please refer to 'checkpoing' in the SQL Server
Books Online. You could also refer to 'recoery mode' too.
As for your questions of how the records insertings are rejected by tables
one by one, it would be very helpful for you to provide the detailed error
message, the error log and the T-SQL you were excuting at that time for
further analysis. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Baisong,
Thank you for your reply and I understand the description
provided. Here is more background regarding to my problem:
1. We use ADO command object to perform single T-SQL
statement with a time-out value set to one minute.
2. On average, we perform approximately 36,960 T-SQL
statements per hour.
3. Database and transaction files are set to auto grew at
a rate of (10%)
4. Unfortunately, we don't know what the error message was
generated by SQL Server when the records failed to be
written.
5. All the non-SQL processes appeared to be running
normally at the time of the restart. This information was
gathered using Windows Task Manager.
6. No rolled-forward information was logged in SQL Server
log files in previous restarts.
7. Prior to the restart, new information was unable to
insert into the database, but old records can be retrieved.
8. The largest table in the database had 25,000,000
records at the time of the problem.
Question:
What could be happening on the server that would result in
1) failed to insert, and 2) rolled forward of 18000
records in restart.|||Hi Mike,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
In you first post, you mentioned that 'By examining the application log
file, it seems that the SQL server started to deteriorate by not allowing
insertion in one table at a time and eventually all tables are rejected for
insertion.' and in your last post, you mentioned that 'we don't know what
the error message was generated by SQL Server when the records failed to be
written'. I wonder if you could provide the content of this part of the log
that you made this judgement, what made you think that the insertion is
rejected while no error message indicating any abnormal. Any blocking of
locks or any information from you application side? This information is
helpful for our analysis. Second, as I mentioned in my last post, when an
operation is executed, this operation will be write to log but not
necessary to write to .MDB file, when checkpoint come, they will be write
to the MDB file. I think that it is because the 18,000 records' insert have
been written into the log file, but not to the .MDB file, so, when SQL
Server restart, it will compare with the LSN of page and log to decide
roll-forward or roll-back, no difference between, in the SQL Server start
process, no this action is taken, as in your past restart. Besides the log
message, could you please provide the recovery mode of you database?
As the ADO time-out option, I suggest you to set to zero, which prepresent
no limit for time-out.
Looking for your reply and thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Baisong,
What I meant by 'By examining the application log
file,...' is checking the SQL server tables that the
application is writing to. All the written records
contain a time stamp; therefore I can tell when the
insertion started to fail on these tables.
'we don't know what the error message was generated by SQL
Server when the records failed' simply means that the
application did not log any error returned by the ADO
command object.
The recovery mode that the database uses is 'Full Recovery
Mode'
Mike|||Hi Mike,
Thank you for your update.
Yes, from the time stamps in your table that indicating when the insertion
was inserted, we could figure out that the inserting is stopped. However,
we could this can just provide the information about when the inserting
failure happened. From the error log and other informations collected,
could we figure out the cause of the problem. From the information now I
could have, I doubt that there were blocks and later deadlocks that cause
the insertion stopped and then cause all the insert failed. As I mentioned
in my previous reply, before the deadlock happen, some of the insert is
succeeded and this inserting is logged in the transactional log file, but
not in the data file. Before the checkpoint came, you re-booted the system,
and the SQL Server will also restart. SQL Server will compare the LSN of
data page and the log, then will decide which to roll forward and which to
rolled back. The operations already in the transactional log file will be
rolled forward, such as the 18,000 record in your case.
However, this is just a assumption based on my experience. To judge what
happend at that time, we need the corresponding error log for analysis.
Also, when this problem happened again, we could collect the useful
information by the following command. They are:
1) sp_lock: displaying the active locking information
2) sp_who, sp_who2: displaying the current users and processes information
You could also refer to this part in the SQL Server Books Online:
Troubleshooting Deadlocks
Hope this helps!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Insertion data via Stored Procedure [URGENT]!

Hi all;

Question:
=======
Q1) How can I insert a record into a table "Parent Table" and get its ID (its PK) (which is an Identity "Auto count" column) via one Stored Procedure??

Q2) How can I insert a record into a table "Child Table" whose (FK) is the (PK) of the "Parent Table"!! via another one Stored Procedure??

Example:
----
I have two tables "Customer" and "CustomerDetails"..

SP1: should insert all "Customer" data and return the value of an Identity column (I will use it later in SP2).

SP2: should insert all "CustomerDetials" data in the record whose ID (the returned value from SP1) is same as ID of the "Customer" table.

FYI:
--
MS SQL Server 2000
VS.NET EA 2003
Win XP SP1a
VB.NET/ASP.NET :)

Thanks in advanced!There are a couple of ways to get the last inserted IDENT, but I prefer to use IDENT_CURRENT('table_name') to get the ID from the last inserted record of thespecified table.

INSERT INTO Customer (val1,val2,etc) VALUES (@.Val1,@.val2,etc)
INSERT INTO CustomerDetails (ID,Val1,Val2, etc) VALUES ((IDENT_CURRENT('Customer'),@.val1,@.val2,etc)

|||Ok, what about if I want to do these process in two SPs??

I want to take the IDDENTITY value from the fitrst "INSERT INTO" statment, because I need this value in my source code as well as other Stored Procedure(s).

Thanks in advanced!|||Insert Blah...;
Return SCOPE_IDENTITY()|||Thanks gays.

I think my problem is how to get the returned value (IDDENTITY value) from the VB.NET code (in other words, how to extract it from VB.NET/ADO.NET code)?

Note:
--
VB.NET/ADO.NET
or
C#.NET/ADO.NET

Are are ok, if you would like to demonstrate your replay. ( I want the answer!).

Thanks again.|||YES!!

The problem was from my ADO.NET part.

Thanks for your help gays.

Insertion / updation problem in SSIS

“I have a scenario where i am trying to insert 200,000 lac records & update 200,000 lac record in destination table using SISS Package (SQL SERVER 2005) but I am not able to neither update nor insert the records . while executing the package its not showing any error also . what could be the problem ? “

We have business logic in Package creation 1) Insert New records and 2) Update Existing Records using the follow Data flow diagram

For update we are using OLEDB command, for insert we are using OLEDB Destination.

We are using merge join for spliting record into insert and update.

Perhaps there is blocking on the destination table. This can often happen if you're attempting 2 operations simultaneously.

Execute sp_who2 to see if there's any blocking going on.

-Jamie

|||

is there any other solution for this problem, is this not possible to run for achive both insertion and updation in the same package? if i try to run the package for less records, it is succeded, if i try to run the package for more records, then same problem coming again and again

Thanks & Regards

S.Nagarajan

|||

In that case I am even more sure that blocking is a problem. Did you bother to execute sp_who2 like I advised?

There is an easy fix to this problem. Continue to do the insert but push teh adta to be updated into a raw file. You can then use the contents of the raw file in another data-flow in order to do the update,.

-Jamie

|||I ran sp_who2 and found the blocking, how can i remove the blocking, is there any query to remove the blocking. I couldn't get your solution clearly, please brief me your alternate solution.|||

Do you know what raw files are? If not, go away and study them. When you have finished read #1 here: http://blogs.conchango.com/jamiethomson/archive/2006/02/17/2877.aspx

It describes a different scenario for using raw files but the usage is the same.

-Jamie

|||

We have completed upto move to flat destination files using raw files, could you please help to move this flat files data (for update) to database. what logic we need to use? is it required to add dataflow diagram after raw file destination component

|||

Please stop writing the same question in multiple threads simultaneously. People are here to help and don't want to waste their time clicking through and reading the same thing more than once.

-Jamie

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind RegardsFor double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind RegardsFor double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting, updating Record having Single, double quotes.

Hi all
I need to insert some text in a table that contains single as well as double
quotes but its return error during inserting or updating.
I converted single as well as double quote to chr(39) and chr(34) but still
facing problem.
Please advise how I can solve it.
Kind Regards
For double quotes, check if you have SET QUOTED_IDENTIFIER ON; single
quotes hae simply to be duplicated iside the string. Example:
USE tempdb
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE T1
(a varchar(50))
GO
INSERT INTO T1 VALUES ('A single '' apostrophe; and a double " one')
SELECT * FROM T1
DROP TABLE T1
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e68qySBuEHA.1308@.tk2msftngp13.phx.gbl...
> Hi all
> I need to insert some text in a table that contains single as well as
double
> quotes but its return error during inserting or updating.
> I converted single as well as double quote to chr(39) and chr(34) but
still
> facing problem.
> Please advise how I can solve it.
> Kind Regards
>
>
>

Inserting values

Dear All,

In sql-server2000
In a table i am having a column of datatype varchar(8000).
While inserting the record through executenonquery, i am insert only
255 characters rest of the characters are getting trucated.

My question in how i will able to insert the row of that particular
column more than 255 characters

Thanx in advance.

Regardsfix the query.

i dunno what the query is or the command parameter info, so you'll just have to read on how to change the length of that sqldbtype.|||Dear Kragie,

thanx for your reply.

i am using command parameter info is command text and and directly using "Insert command".

Regards

inserting text after a certain record of a recordset

What I'm trying to accomplish is to add some text after the 5th
dispalyed record of a recrod set, I tried using absolute position and
the following:
<%
if rs.absoluteposition = 5 then
Response.Write "Testing"
end if
%>
I'm probably missing something or there is some better way to do this,
thanks for any assistance in advance.counter = 0
do while not rs.eof
counter = counter + 1
if counter = 5 then
response.write "testing"
end if
rs.movenext
loop
<dabootleg@.gmail.com> wrote in message
news:1134328540.390884.113180@.g47g2000cwa.googlegroups.com...
> What I'm trying to accomplish is to add some text after the 5th
> dispalyed record of a recrod set, I tried using absolute position and
> the following:
> <%
> if rs.absoluteposition = 5 then
> Response.Write "Testing"
> end if
> %>
> I'm probably missing something or there is some better way to do this,
> thanks for any assistance in advance.
>

Inserting Records, Skipping Duplicates

I'd like to ask if there's any statement to insert records into a table, suc
h
that if any record violates the primary key constraint, it will "neglect" th
e
record and insert the next one.
Thank youAn exception/error will be generated if you try to insert a row that
violates the primary key constraint. You can pre-empt the primary key
constraint violation by checking each row on insert via trigger.
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:CC9F8302-1C4D-4141-B9B1-EADC999CC01B@.microsoft.com...
> I'd like to ask if there's any statement to insert records into a table,
> such
> that if any record violates the primary key constraint, it will "neglect"
> the
> record and insert the next one.
> Thank you|||Hi,
Maybe if you perform your inserts one row at a time within a loop you could
handle the errors with @.@.ERROR.
Ray
"wrytat" wrote:

> I'd like to ask if there's any statement to insert records into a table, s
uch
> that if any record violates the primary key constraint, it will "neglect"
the
> record and insert the next one.
> Thank you|||Add a WHERE NOT EXISTS() to the INSERT.
INSERT Whatever
VALUES('a', 'b', 'c')
WHERE NOT EXISTS
(select * from Whatever as X
where X.pk = 'a')
or
INSERT Whatever
SELECT A, B, C
FROM Somewhere
WHERE NOT EXISTS
(select * from Whatever as X
where X.pk = Somewhere.A)
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 19:30:02 -0700, wrytat
<wrytat@.discussions.microsoft.com> wrote:

>I'd like to ask if there's any statement to insert records into a table, su
ch
>that if any record violates the primary key constraint, it will "neglect" t
he
>record and insert the next one.
>Thank you

Inserting Records via Stored Procedure

I am trying to insert a record in a SQL2005 Express database. I can use the sp fine and it works inside of the database, but when I try to launch it via ASP.NET it fails...

here is the code. I realize it is not complete, but the only required field is defined via hard code. The error I am getting states it cannot find "sp_InserOrder"

===

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click

Dim connAs SqlConnection =Nothing

Dim transAs SqlTransaction =Nothing

Dim cmdAs SqlCommand

conn =New SqlConnection(ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString)

conn.Open()

trans = conn.BeginTransaction

cmd =New SqlCommand()

cmd.Connection = conn

cmd.Transaction = trans

cmd.CommandText ="usp_InserOrder"

cmd.CommandType = Data.CommandType.StoredProcedure

cmd.Parameters.Add("@.MaterialID", Data.SqlDbType.Int)

cmd.Parameters.Add("@.OpenItem", Data.SqlDbType.Bit)

cmd.Parameters("@.MaterialID").Value = 3

cmd.ExecuteNonQuery()

trans.Commit()

=====

I get an error stating cannot find stored procedure. I added the Network Service account full access to the Web Site Directory, which is currently running locally on Windows XP Pro SP2.

Please help, I am a newb and lost...as you can tell from my code...

Are you absolutely sure that your stored procedure is called usp_InserOrder. It would make more sense if it was called usp_InsertOrder.|||It is called that. That is what I was thinking at firs, but I checked the database and that is what I named it.|||

I ran it inside of SQL 2005 here is the output...

(0 row(s) returned)

@.RETURN_VALUE = 0

Finished running [dbo].[InserOrder].

|||You code should read -cmd.CommandText ="InserOrder" as that is what it is called in the report you posted above.

inserting records - mswebtasks error

SQL Server 2005 on a Windows XP PC

If I "Open" a table and try to insert a new record, I get an error:

The data in row 100 was not comitted.

Error Source: .Net SqlClient Data Provider.

Error Message: [Microsoft][SQL Native CLient][Sql Server] Invalid object name 'msdb..mswebtasks'.

SQL web Assistant: Could not execute the SQL statement.

press ESC....

Any ideas ?

thanks

John

Do you have any triggers on the underlying table?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Thankyou for the triggers comment. There was an old dis-used trigger hiding in the background !

thanks

John

|||Brilliant! I'd been rebuilding indexes many times thinking I had a corruption. I too had old triggers hidden I didn't know about.

inserting records - mswebtasks error

SQL Server 2005 on a Windows XP PC

If I "Open" a table and try to insert a new record, I get an error:

The data in row 100 was not comitted.

Error Source: .Net SqlClient Data Provider.

Error Message: [Microsoft][SQL Native CLient][Sql Server] Invalid object name 'msdb..mswebtasks'.

SQL web Assistant: Could not execute the SQL statement.

press ESC....

Any ideas ?

thanks

John

Do you have any triggers on the underlying table?

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Thankyou for the triggers comment. There was an old dis-used trigger hiding in the background !

thanks

John

|||Brilliant! I'd been rebuilding indexes many times thinking I had a corruption. I too had old triggers hidden I didn't know about.

Inserting Record to db1.table from db2.table

Hello!
Can we do this in SQL2000?
Insert into <db1>.<tablename> (fld1,fld2...) Select fld1,fld2 from <db2>.<tablename>
Assuming that these two tables have the same fields but reside different database.
Thanks in advance
bernieinsert into db1.dbo.table select * from db2.dbo.table

Inserting record into table as the first record

Hi all,

I would like to know whether i can insert a record as the first record to a table which is not empty using sqlserver(that is i need to display that record as the first record)....

Please help me...

Regards,

MathewYou can not rely on position of a record in a table.
Always use order by Asc or Desc to make record first or last.

Good Luck.

Inserting Record into table

I'm working on an app that when a record is added in Table1, that id needs
to be populated into Table2 and Table3. When a user enters a new record, a
process would have to check table2 and 3 for the existence of that id. I
searched the net and didn't have any luck? I think a trigger would work to
kick off the process but not sure how to create the procedure to search for
the existence of a record id.
Any help would be appreciated.
thanks
- rob
hi Rob,
"Rob" <temp@.dstek.com> ha scritto nel messaggio
news:uW730eC8EHA.4072@.TK2MSFTNGP10.phx.gbl
> I'm working on an app that when a record is added in Table1, that id
> needs to be populated into Table2 and Table3. When a user enters a
> new record, a process would have to check table2 and 3 for the
> existence of that id. I searched the net and didn't have any luck?
> I think a trigger would work to kick off the process but not sure how
> to create the procedure to search for the existence of a record id.
> Any help would be appreciated.
> thanks
> - rob
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE tableA (
ID INT NOT NULL PRIMARY KEY ,
Name VARCHAR(10) NOT NULL ,
Country VARCHAR(10) NOT NULL
)
CREATE TABLE tableB (
ID INT NOT NULL PRIMARY KEY ,
Name VARCHAR(10) NOT NULL ,
Country VARCHAR(10) NOT NULL
)
CREATE TABLE tableC (
ID INT NOT NULL PRIMARY KEY ,
Name VARCHAR(10) NOT NULL ,
Country VARCHAR(10) NOT NULL
)
GO
CREATE TRIGGER tr_I_tableA ON tableA
FOR INSERT
AS BEGIN
IF @.@.ROWCOUNT = 0 RETURN
INSERT INTO tableB SELECT i.ID , i.Name , i.Country
FROM INSERTED i WHERE NOT EXISTS (SELECT ID FROM tableB b WHERE
b.ID = i.ID)
INSERT INTO tableC SELECT i.ID , i.Name , i.Country
FROM INSERTED i WHERE NOT EXISTS (SELECT ID FROM tableC c WHERE
c.ID = i.ID)
END
GO
INSERT INTO tableB VALUES ( 1 , 'xxx', 'xxx' ) -- this row will not be
overwritten in tableB
INSERT INTO tableC VALUES ( 2 , 'xxx', 'xxx' ) -- this row will not be
overwritten in tableC
INSERT INTO tableA VALUES ( 1 , 'Andrea', 'Italy' )
INSERT INTO tableA VALUES ( 2 , 'Rob', 'USA' )
PRINT 'tableB'
SELECT * FROM tableB
PRINT 'tableC'
SELECT * FROM tableC
GO
DROP TABLE tableC, tableB, tableA
--<--
tableB
ID Name Country
-- -- --
1 xxx xxx
2 Rob USA
tableC
ID Name Country
-- -- --
1 Andrea Italy
2 xxx xxx
have a wonderfull new year
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||thanks ... this is great !!!!
- rob
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:33o9duF40p561U1@.individual.net...
> hi Rob,
> "Rob" <temp@.dstek.com> ha scritto nel messaggio
> news:uW730eC8EHA.4072@.TK2MSFTNGP10.phx.gbl
> SET NOCOUNT ON
> USE tempdb
> GO
> CREATE TABLE tableA (
> ID INT NOT NULL PRIMARY KEY ,
> Name VARCHAR(10) NOT NULL ,
> Country VARCHAR(10) NOT NULL
> )
> CREATE TABLE tableB (
> ID INT NOT NULL PRIMARY KEY ,
> Name VARCHAR(10) NOT NULL ,
> Country VARCHAR(10) NOT NULL
> )
> CREATE TABLE tableC (
> ID INT NOT NULL PRIMARY KEY ,
> Name VARCHAR(10) NOT NULL ,
> Country VARCHAR(10) NOT NULL
> )
> GO
> CREATE TRIGGER tr_I_tableA ON tableA
> FOR INSERT
> AS BEGIN
> IF @.@.ROWCOUNT = 0 RETURN
> INSERT INTO tableB SELECT i.ID , i.Name , i.Country
> FROM INSERTED i WHERE NOT EXISTS (SELECT ID FROM tableB b WHERE
> b.ID = i.ID)
> INSERT INTO tableC SELECT i.ID , i.Name , i.Country
> FROM INSERTED i WHERE NOT EXISTS (SELECT ID FROM tableC c WHERE
> c.ID = i.ID)
> END
> GO
> INSERT INTO tableB VALUES ( 1 , 'xxx', 'xxx' ) -- this row will not be
> overwritten in tableB
> INSERT INTO tableC VALUES ( 2 , 'xxx', 'xxx' ) -- this row will not be
> overwritten in tableC
> INSERT INTO tableA VALUES ( 1 , 'Andrea', 'Italy' )
> INSERT INTO tableA VALUES ( 2 , 'Rob', 'USA' )
> PRINT 'tableB'
> SELECT * FROM tableB
> PRINT 'tableC'
> SELECT * FROM tableC
> GO
> DROP TABLE tableC, tableB, tableA
> --<--
> tableB
> ID Name Country
> -- -- --
> 1 xxx xxx
> 2 Rob USA
> tableC
> ID Name Country
> -- -- --
> 1 Andrea Italy
> 2 xxx xxx
> have a wonderfull new year
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

inserting record into second database from a stored procedure in first database

is it possible to insert record into second database from a stored procedure which is in first database?
If the store proc include an INSERT INTO statement. Run a search for INSERT INTO in SQL Server BOL(books online). Hope this helps.

Inserting record in DB2 from Sql Server 2000

I am trying to insert a record in DB2 Database which is a Linked Server in SQl SERVER 2000.

I have tried fetching the value from DB2 Database using Openquery which is working fine, but when i try insertion of record it gives an error.

Follwoing is the Tsql I am using for insertion

insert into openquery(DB2_DB2T, 'Select Audit_nbr from $ZUDBA01.TPT200_VOLS where 1=0') values(9999999)

When i execute this Tsql i get following Error message

Server: Msg 7399, Level 16, State 1, Line 26
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].

What could be the mistake I am making, Please help me out.

Thanks in Advance

Pranjal

You are doing an insert into a table and specifying a filter - it could be complaining about that

Try

insert into openquery(DB2_DB2T, 'Select Audit_nbr from $ZUDBA01.TPT200_VOLS') values(9999999)

also try

insert into openquery(DB2_DB2T, 'Select * from $ZUDBA01.TPT200_VOLS') values(all the values for the row)

|||

Hi,

I tried the query as you suggested by still I am encountering the same error. Please advice. Thanks

inserting ole-object

Hello

I want to insert an iostream object into a ms acces db. I use ole-obect as
the data type for the specific column. Inserting a new record works fine but
somehow the field where the ole-object (iostream) has to be interted stays
empty.

Is ole-obect a proper data type for an iostream or should I use something
else.

thanks for the response

stijn[posted and mailed]

Stijn Oude Brunink (soudebrunink@.chello.nl) writes:
> I want to insert an iostream object into a ms acces db. I use ole-obect
> as the data type for the specific column. Inserting a new record works
> fine but somehow the field where the ole-object (iostream) has to be
> interted stays empty.
> Is ole-obect a proper data type for an iostream or should I use something
> else.

You should probably ask this question in comp.databases.ms-access. This
newsgroup is for MS SQL Server, which is something else.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp