Cannot Make Inserts at Subscribers

I'm getting this error message when trying to insert a row on some
tables in my database.
The identity range managed by replication is full and must be updated
by a replication agent. The INSERT conflict occurred in database 'XXX',
table 'tblContact', column 'Number'. Sp_adjustpublisheridentityrange can
be called to get a new identity range.
These subscribers have been running for several months with out issue
and they are now all getting these errors as of yesterday. Running the
merge agent does not remedy the problem and that stored procedure seems
to only work for the publisher which is running just fine.
Running dbcc checkident ('tblContact', NORESEED) returns
Checking identity information: current identity value '94005', current
column value '94005'.
This will increase by one if I try an insert, but the insert fails.
I took everything down. Deleted the databases on the subscribers and
disabled publishing on the publisher, and started from scratch.
After setting up the replication again, some of the tables that were
giving me the problem are now working, but the majority of the tables
are still giving me this error.
I've run out of things to try. I would appreciate any further
suggestions.
Thank you,
David
Basically you have blown your identity range.
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
Automatic identity range management has a check constraint which constrains
the possible identity values which can be used on your publisher and
subscriber. If you do an insert the check constraint will kick back the
insert when you blow the range and increment the current identity value.
the proc Sp_adjustpublisheridentityrange will adjust the identity range on
the Publisher, but not the subscriber. To fix this on your subscribers you
have to sample each one and figure out where they are. Then run the merge
agent one by one. One of the first things it will do when it detects the
range has been blown is to adjust it.
If this fails to work you may have to manually intervene and reset it based
on what all your subscribers are at and the values stored in select * from
distribution.dbo.MSmerge_identity_range_allocation s
You may also need to run your merge agents more frequently or bump up the
ranges to account for the max amount of data inserted on the subscriber at
any one time between syncs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David TMS" <IdontlikeSpam@.nospam.com> wrote in message
news:MPG.20636a6d184a3e8a989680@.msnews.microsoft.c om...
> I'm getting this error message when trying to insert a row on some
> tables in my database.
> The identity range managed by replication is full and must be updated
> by a replication agent. The INSERT conflict occurred in database 'XXX',
> table 'tblContact', column 'Number'. Sp_adjustpublisheridentityrange can
> be called to get a new identity range.
> These subscribers have been running for several months with out issue
> and they are now all getting these errors as of yesterday. Running the
> merge agent does not remedy the problem and that stored procedure seems
> to only work for the publisher which is running just fine.
> Running dbcc checkident ('tblContact', NORESEED) returns
> Checking identity information: current identity value '94005', current
> column value '94005'.
> This will increase by one if I try an insert, but the insert fails.
> I took everything down. Deleted the databases on the subscribers and
> disabled publishing on the publisher, and started from scratch.
> After setting up the replication again, some of the tables that were
> giving me the problem are now working, but the majority of the tables
> are still giving me this error.
> I've run out of things to try. I would appreciate any further
> suggestions.
> Thank you,
> David
|||Running the merge agent did not fix this problem for me. The identity
ranges on the subscribers were also more than large enough. They had
room for 10,000 inserts on each table and were not able to make any on
most of the tables, but other tables seemed to work just fine. Deleting
the publication and disabling replication, but keeping the original
database seemed to make the problem recur immediately after setting up
the replication. I'm still not sure of the cause of this problem, but I
seem to have a work around. I started a new database with a fresh
schema. I then imported the data from the old database. I set
everything back up using this new database name and it went fairly
smoothly.
Thanks,
Dave
In article <#2pv#P8ZHHA.4720@.TK2MSFTNGP06.phx.gbl>,
hilary.cotter@.gmail.com says...
> Basically you have blown your identity range.
> http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
> Automatic identity range management has a check constraint which constrains
> the possible identity values which can be used on your publisher and
> subscriber. If you do an insert the check constraint will kick back the
> insert when you blow the range and increment the current identity value.
> the proc Sp_adjustpublisheridentityrange will adjust the identity range on
> the Publisher, but not the subscriber. To fix this on your subscribers you
> have to sample each one and figure out where they are. Then run the merge
> agent one by one. One of the first things it will do when it detects the
> range has been blown is to adjust it.
> If this fails to work you may have to manually intervene and reset it based
> on what all your subscribers are at and the values stored in select * from
> distribution.dbo.MSmerge_identity_range_allocation s
> You may also need to run your merge agents more frequently or bump up the
> ranges to account for the max amount of data inserted on the subscriber at
> any one time between syncs.
>

cannot insert(urgent)

Hi
I am using Mssql2000 and i am facing a problem while saving data from my
application.
This Procedure is working fine.
CREATE PROCEDURE test(@.edate datetime,@.supp_idv char
(8),@.div char(2),@.dept
char(3))
AS CREATE table #oi(supp_id char(8),inv_no char
(15),inv_date datetime
NULL,dept_code char(3),dr numeric(14,3),cr numeric
(14,3),bal
numeric(14,3),tr_type char(2),entry_type char(1))
begin
Insert into #oi
select supp_id,inv_no,inv_date,dept_code,dr,cr,
(select sum(cr) - sum(dr) from ap_spled b WHERE
a.supp_id =
b.supp_id and
a.inv_no = b.inv_no group by
b.inv_no),trans_type,entry_type
from ap_spled a where division = @.div AND inv_date <=
@.edate and supp_id =
@.supp_idv and
(select sum(cr) - sum(dr) from ap_spled b WHERE
a.supp_id =
b.supp_id and a.inv_no = b.inv_no
group by b.inv_no) < 0 ;
select
a.supp_id,a.inv_no,a.inv_date,a.dr,a.cr,a.bal,a.tr
_type,a.entry_type,a.dept,d.name from #oi
a,supplier_file d
where a.supp_id = d.supplier_id order by supp_id,inv_no
The problem is that after retrieving this report in my application,if any
other user try to save from any other module ie
inserting any record into ap_spled table, the application is
hanging and cannot insert into this table.No users can save their data this
time, it is creating big prblm.This is the insert from my appl.This syntax is
also working fine if the procedure is not execute.
INSERT INTO ap_spled
(tr_no,entry_date,supp_id,dr_amt,cr_amt,trans_type ,acc_desc,division,dept_code,entry_type,inv_no,inv _date )
VALUES
(:tr,:dt,:sup,:dmt,:amt,'uu',:descr,:division,:dep t_code,:entry_type,:no,:vd
) ;
Is their any problem in my storedproced syntax or this is due to any table
locking pblm.How can i solve this.This is creating too much prbm
Please help.
Thanks
Thanks
On Fri, 11 Mar 2005 23:13:02 -0800, shif wrote:
(snip)
>The problem is that after retrieving this report in my application,if any
>other user try to save from any other module ie
>inserting any record into ap_spled table, the application is
>hanging and cannot insert into this table.
(snip)
Hi shif,
Some follow-up questions, the answers to which might help pinpoint the
poblem:
1. When this happens, how do you solve the issue? Wait until it goes
away (and if so, how long does that take, on average)? Close and restart
the application that tries to insert? Close and restart the application
that generated the report? Shutdown and restart the server? Or yet
something else?
2. Next time this happens, BEFORE taking any corrective action, open a
Query Analyzer window; type EXEC sp_who2; EXEC sp_lock and execute (make
sure that the "Results in Text" option is chosen), then copy the output
and paste it in a reply to this message.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||This is the query analyser result after giving sp_who2 at the time of same
prblm.
And If I Close the application that generated the report,
the hanging will release and can save the data.
SPID Status Login HostName BlkBy DBName Command
CPUTime DiskIO LastBatch ProgramName SPID
-- -- -- -- -- --
-- -- -- -- -- --
1 sleeping sa . . NULL LOG WRITER
0 0 03/12 11:57:30 1
2 BACKGROUND sa . . master SIGNAL
HANDLER 0 0 03/12 11:57:30 2
3 BACKGROUND sa . . NULL LOCK
MONITOR 0 0 03/12 11:57:30 3
4 BACKGROUND sa . . NULL LAZY
WRITER 0 0 03/12 11:57:30 4
5 BACKGROUND sa . . master TASK
MANAGER 0 17 03/12 11:57:30 5
6 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 6
7 sleeping sa . . NULL CHECKPOINT
SLEEP 0 51 03/12 11:57:30 7
8 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 8
9 BACKGROUND sa . . master TASK
MANAGER 0 9 03/12 11:57:30 9
10 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 10
11 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 11
12 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 12
13 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 13
14 BACKGROUND sa . . master TASK
MANAGER 0 647 03/12 11:57:30 14
51 sleeping sa . . sns_acc AWAITING
COMMAND 5672 24 03/13 09:55:02 51
52 sleeping sa . . sns_acc AWAITING
COMMAND 2859 0 03/13 09:59:10 52
53 sleeping sa . . sns_acc AWAITING
COMMAND 2734 0 03/13 09:58:20 53
54 sleeping sa . . sns_acc AWAITING
COMMAND 11750 20 03/13 09:36:53 54
55 sleeping sa . . sns_acc AWAITING
COMMAND 4594 0 03/13 09:42:13 55
56 sleeping sa . . sns_acc AWAITING
COMMAND 2547 1 03/13 09:54:14 56
57 sleeping sa . . sns_acc AWAITING
COMMAND 4594 194 03/13 09:50:06 57
58 sleeping sa . . sns_acc AWAITING
COMMAND 5500 0 03/13 09:58:07 58
59 sleeping sa . . sns_acc AWAITING
COMMAND 5266 0 03/13 09:14:18 59
60 sleeping sa . . sns_acc AWAITING
COMMAND 8875 0 03/13 09:13:22 60
61 sleeping sa . . sns_acc AWAITING
COMMAND 9000 0 03/13 09:15:11 61
62 sleeping sa . . sns_acc AWAITING
COMMAND 4594 0 03/13 09:42:33 62
63 sleeping sa NAZAR . sns_acc AWAITING
COMMAND 5344 0 03/13 09:55:02 MS SQLEM 63
64 sleeping sa . 66 sns_acc SELECT
3031 0 03/13 10:00:11 64
65 RUNNABLE sa NAZAR . sns_acc SELECT
INTO 4594 11 03/13 09:49:33 SQL Query Analyzer 65
66 sleeping sa . 58 sns_acc INSERT
5719 0 03/13 09:59:55 66
67 sleeping sa . . sns_acc AWAITING
COMMAND 5297 0 03/13 09:52:47 67
"Hugo Kornelis" wrote:

> On Fri, 11 Mar 2005 23:13:02 -0800, shif wrote:
> (snip)
> (snip)
> Hi shif,
> Some follow-up questions, the answers to which might help pinpoint the
> poblem:
> 1. When this happens, how do you solve the issue? Wait until it goes
> away (and if so, how long does that take, on average)? Close and restart
> the application that tries to insert? Close and restart the application
> that generated the report? Shutdown and restart the server? Or yet
> something else?
> 2. Next time this happens, BEFORE taking any corrective action, open a
> Query Analyzer window; type EXEC sp_who2; EXEC sp_lock and execute (make
> sure that the "Results in Text" option is chosen), then copy the output
> and paste it in a reply to this message.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Sat, 12 Mar 2005 22:23:01 -0800, shif wrote:

>This is the query analyser result after giving sp_who2 at the time of same
>prblm.
>And If I Close the application that generated the report,
>the hanging will release and can save the data.
(snip)
Hi shif,
Thanks for posting this. Have a look at these three rows:

>SPID Status Login HostName BlkBy DBName Command
> CPUTime DiskIO LastBatch ProgramName SPID
>-- -- -- -- -- --
>-- -- -- -- -- --
(snip)
>58 sleeping sa . . sns_acc AWAITING
>COMMAND 5500 0 03/13 09:58:07 58
(...)
>64 sleeping sa . 66 sns_acc SELECT
> 3031 0 03/13 10:00:11 64
(...)
>66 sleeping sa . 58 sns_acc INSERT
> 5719 0 03/13 09:59:55 66
As you can see (even easier if you edit them to merge the split lines
again), the connection with SPID 64 is blocked by (column BlkBy) SPID
66, which is in turn blocked by connection 58.
Unfortunately, you didn't post the output from sp_lock. But I guess that
this would show that either SPID 66 or (more likely) SPID 58 is used for
generating the report, but still holds some locks that are also required
for the insert.
My guess, at this point, would be that the code that generated the
report opens a transaction, but fails to close it. In the transaction,
either some data gets updated, or a non-standard isolation level is used
to maintain locks on data being read. While the acquiring of the locks
might be legitimate in the context of the report generation, they should
really be released when the report is finished. My suspicion is that
this doesn't happen, because the report generating code fails to execute
an COMMIT TRANSACTION command.
When you close the application, the connection gets broken and SQL
Server will rollback the transaction and then release the locks. Then,
your other connection will get the locks they required and were wating
for; they'll continue to run - until you re-run the report.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi sir
I forget the sp_lock i am adding here and it is lengthy upto 450 rows i
deleted some rows.if i have your mail id i can mail you the full text file.
One more thing i forgot to tell is ,actualy the database which i am using
was working in mssql7.I was just attached to ms2000 srvr,now i exported all
data into a new database in same server,But again i saw today again from same
screen not save but hanging one time in new db.here is the sp_who2 and sp_lock
..
sp_lock
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 2 3 2 KEY (9c01d388586f) X GRANT
51 2 2 1 KEY (590093cc6231) X GRANT
52 8 0 0 DB S GRANT
53 8 0 0 DB S GRANT
54 8 0 0 DB S GRANT
55 8 0 0 DB S GRANT
56 8 0 0 DB S GRANT
56 2 6 0 TAB IX GRANT
56 2 2 0 TAB IX GRANT
56 2 1 0 TAB IX GRANT
56 2 3 1 KEY (7d00de4e4040) X GRANT
56 2 3 1 KEY (8000885e2318) X GRANT
56 8 1410104064 0 TAB S GRANT
56 2 3 1 KEY (810066f1960a) X GRANT
56 2 3 2 KEY (120262fbbe82) X GRANT
56 2 3 2 KEY (b901f41a78b7) X GRANT
56 2 3 2 KEY (a30233242999) X GRANT
56 2 3 2 KEY (a701bbd2b59b) X GRANT
56 2 3 1 KEY (820003962ab2) X GRANT
56 2 0 0 IDX IDX: 2:100335883 X GRANT
56 2 1003358839 0 TAB Sch-M GRANT
56 2 3 1 KEY (7e00bb29fcf8) X GRANT
56 2 3 2 KEY (c401f8c9de07) X GRANT
56 2 3 2 KEY (ad024e966d4d) X GRANT
57 2 1 2 KEY (b516cfe74d67) X GRANT
57 2 3 1 KEY (ef00eaadae35) X GRANT
57 2 3 2 KEY (9c020770e7da) X GRANT
57 2 2 1 KEY (e80092c5f44b) X GRANT
57 2 0 0 IDX IDX: 2:891358440 X GRANT
57 2 891358440 0 TAB Sch-M GRANT
57 2 3 2 KEY (a602bbabc534) X GRANT
57 2 3 1 KEY (ed006165a79f) X GRANT
57 2 3 1 KEY (f100d9da71d5) X GRANT
57 2 3 2 KEY (9f0226e43aa2) X GRANT
57 2 3 2 KEY (0e02b8060e3b) X GRANT
57 2 1 3 KEY (e800e561205a) X GRANT
57 2 3 1 KEY (f000bcbdcd6d) X GRANT
57 2 3 1 KEY (ec0004021b27) X GRANT
57 2 3 1 KEY (ee008fca128d) X GRANT
57 2 3 1 KEY (f2003775c4c7) X GRANT
57 2 3 2 KEY (9602c76d7c54) X GRANT
57 2 99 0 RID 1:78:1 X GRANT
57 2 3 1 KEY (ea00d85d7002) X GRANT
57 2 3 2 KEY (12035d1d7fdb) X GRANT
57 2 3 2 KEY (2b02dc4b8826) X GRANT
57 2 3 2 KEY (1e033df41ec8) X GRANT
57 2 3 1 KEY (eb00bd3accba) X GRANT
57 2 1 1 KEY (e8009061461e) X GRANT
57 2 3 2 KEY (35023d7f78d0) X GRANT
57 2 0 0 PAG 1:88 X GRANT
57 2 9 0 TAB IX GRANT
57 2 12 0 TAB IX GRANT
57 2 11 0 TAB IX GRANT
57 2 99 0 RID 1:78:0 X GRANT
57 2 0 0 PAG 1:78 X GRANT
57 2 0 0 PAG 1:77 X GRANT
57 2 0 0 EXT 1:88 X GRANT
58 2 3 1 KEY (89008d396180) X GRANT
58 2 3 2 KEY (5e01e45197c4) X GRANT
58 2 3 1 KEY (85003586b7ca) X GRANT
58 2 3 2 KEY (e601a49ec50f) X GRANT
58 2 3 1 KEY (35005b77329d) X GRANT
58 2 99 0 RID 1:121:0 X GRANT
58 2 99 0 RID 1:123:0 X GRANT
58 2 3 1 KEY (3100e3c8e4d7) X GRANT
58 2 0 0 PAG 1:121 X GRANT
58 2 3 1 KEY (4d00badd2f69) X GRANT
58 2 2 1 KEY (49007dcee1a2) X GRANT
58 2 99 0 RID 1:252:0 X GRANT
58 2 0 0 PAG 1:252 X GRANT
58 2 3 1 KEY (4c0003e5f8f4) X GRANT
58 2 3 1 KEY (4f00311526c3) X GRANT
58 2 3 1 KEY (4e00dfba93d1) X GRANT
58 2 3 1 KEY (530089aaf089) X GRANT
58 2 3 1 KEY (52006705459b) X GRANT
58 2 3 1 KEY (3a0048a3eb2f) X GRANT
58 2 3 2 KEY (000231e2855c) X GRANT
58 2 3 2 KEY (6f01f7b5e64f) X GRANT
58 2 3 2 KEY (57026d5bbde3) X GRANT
58 2 3 2 KEY (7a01dcdc9a6a) X GRANT
58 2 3 2 KEY (5b0251f72f39) X GRANT
58 2 3 1 KEY (3600f01c3d65) X GRANT
58 2 3 2 KEY (3602a72cacc0) X GRANT
58 2 622833481 0 TAB Sch-M GRANT
58 2 494833025 0 TAB Sch-M GRANT
58 2 1 3 KEY (4900a5f7bb28) X GRANT
58 2 3 1 KEY (51000262f923) X GRANT
58 2 3 2 KEY (590270009824) X GRANT
58 2 3 2 KEY (b001ef43c30a) X GRANT
58 2 3 1 KEY (3700d0bf3b37) X GRANT
58 2 3 1 KEY (320086af586f) X GRANT
58 2 3 1 KEY (36003e108e25) X GRANT
58 2 3 1 KEY (8a00e85edd38) X GRANT
58 2 3 1 KEY (8700be4ebe60) X GRANT
58 2 3 1 KEY (84008cbe6057) X GRANT
58 2 3 1 KEY (8b0006f1682a) X GRANT
58 2 3 1 KEY (30005af0334a) X GRANT
58 2 3 1 KEY (33006800ed7d) X GRANT
58 2 1 1 KEY (4900d0f7dd6c) X GRANT
58 2 3 2 KEY (7502b83841ec) X GRANT
58 2 3 2 KEY (ce016cf265f3) X GRANT
58 2 3 2 KEY (8e01a236c901) X GRANT
58 2 3 1 KEY (3200a78b5fea) X GRANT
58 2 3 2 KEY (720182f319f5) X GRANT
58 2 3 2 KEY (740139cfb2a2) X GRANT
58 2 3 1 KEY (3700a60c5e3d) X GRANT
58 2 3 1 KEY (30002c435640) X GRANT
58 2 3 1 KEY (3100c2ece352) X GRANT
58 2 3 2 KEY (8802393b156f) X GRANT
58 2 3 1 KEY (33001eb38877) X GRANT
58 2 2 1 KEY (2d000211c41d) X GRANT
58 2 3 2 KEY (ad02bb7a3e55) X GRANT
58 2 1 3 KEY (2d00f2931699) X GRANT
58 2 2 1 KEY (2f00d83b7a82) X GRANT
58 2 3 2 KEY (71010a60f6ca) X GRANT
58 2 3 2 KEY (65022c456bed) X GRANT
58 2 3 2 KEY (1c0296c64325) X GRANT
58 2 1 2 KEY (a416b6eaa566) X GRANT
58 2 3 2 KEY (5f0124fb5aa1) X GRANT
58 2 3 2 KEY (b702c6c87a81) X GRANT
58 2 3 2 KEY (7302a563642b) X GRANT
58 2 3 2 KEY (5c015f6d3c93) X GRANT
58 2 1 3 KEY (2f00b3d3188c) X GRANT
58 2 3 2 KEY (5002af3c2995) X GRANT
58 2 3 2 KEY (78017fa8ec67) X GRANT
58 2 3 2 KEY (38025c196d65) X GRANT
58 2 3 1 KEY (5400eccd4c31) X GRANT
58 2 3 1 KEY (4b006682444c) X GRANT
58 2 3 2 KEY (59024cac0afe) X GRANT
58 2 686833709 0 TAB Sch-M GRANT
58 2 0 0 IDX IDX: 2:686833709 X GRANT
58 2 3 1 KEY (2e00d1383ae0) X GRANT
58 2 3 2 KEY (b1012fe90e6f) X GRANT
58 2 3 2 KEY (3402a73ce927) X GRANT
58 2 1 1 KEY (8100cf4109b0) X GRANT
58 2 3 1 KEY (4a00882df15e) X GRANT
58 2 3 1 KEY (500054729a7b) X GRANT
58 2 1 3 KEY (8100ba416ff4) X GRANT
58 2 3 2 KEY (c60132dde66c) X GRANT
58 2 2 1 KEY (81008e0ab94d) X GRANT
58 2 3 2 KEY (63020db2dcf0) X GRANT
58 2 3 1 KEY (39002dc45797) X GRANT
58 2 3 1 KEY (3800c36be285) X GRANT
58 2 3 2 KEY (e401beea3b50) X GRANT
58 2 1 1 KEY (2f00c6d37ec8) X GRANT
58 2 3 1 KEY (8300e9d9dcef) X GRANT
58 2 3 1 KEY (8800db2902d8) X GRANT
58 2 3 1 KEY (8c006396d492) X GRANT
58 2 3 2 KEY (9601fc194a9e) X GRANT
58 2 3 2 KEY (c30122edca89) X GRANT
58 2 3 2 KEY (8b0171b6b1e4) X GRANT
58 2 3 1 KEY (34000d6751c5) X GRANT
58 2 3 1 KEY (2f003f978ff2) X GRANT
58 2 3 1 KEY (3800b5d8878f) X GRANT
58 2 3 2 KEY (7f02c58a0538) X GRANT
58 2 3 1 KEY (8200077669fd) X GRANT
58 2 3 2 KEY (ab02a6211b92) X GRANT
59 2 3 2 KEY (530262013702) X GRANT
59 2 3 2 KEY (ee01a3dbd3c6) X GRANT
59 2 3 1 KEY (c20049df58db) X GRANT
59 2 99 0 RID 1:83:4 X GRANT
59 2 3 1 KEY (c600f1608e91) X GRANT
59 2 3 1 KEY (c000958033fe) X GRANT
59 2 3 2 KEY (e202d8f3d456) X GRANT
59 2 1166835419 0 TAB Sch-M GRANT
59 2 0 0 IDX IDX: 2:116683541 X GRANT
59 2 3 2 KEY (480202cea6d6) X GRANT
59 2 3 2 KEY (2d02ce0794e9) X GRANT
59 2 3 2 KEY (e7014fa399f4) X GRANT
59 2 3 2 KEY (2002e139c422) X GRANT
59 2 3 2 KEY (f4023e1e8569) X GRANT
59 2 3 1 KEY (df006775fe0b) X GRANT
59 2 3 1 KEY (2400ca2649fa) X GRANT
59 2 3 2 KEY (ed0163711ea3) X GRANT
59 2 0 0 IDX IDX: 2:108683513 X GRANT
59 2 1086835134 0 TAB Sch-M GRANT
59 2 2 1 KEY (db00b6aaf8a1) X GRANT
59 2 1 1 KEY (7600d23c06ae) X GRANT
59 2 3 1 KEY (280072999fb0) X GRANT
59 2 3 1 KEY (dc005585203c) X GRANT
59 2 3 1 KEY (e300dfca2841) X GRANT
59 2 3 2 KEY (75023f81917e) X GRANT
59 2 3 1 KEY (dd00bb2a952e) X GRANT
59 2 99 0 RID 1:83:3 X GRANT
59 2 99 0 RID 1:265:3 X GRANT
59 2 3 2 KEY (76022a829f89) X GRANT
59 2 3 2 KEY (c502d70311df) X GRANT
59 2 1054835020 0 TAB Sch-M GRANT
59 2 0 0 IDX IDX: 2:105483502 X GRANT
59 2 0 0 IDX IDX: 2:587357357 X GRANT
59 2 99 0 RID 1:265:4 X GRANT
59 2 3 2 KEY (05034890c140) X GRANT
59 2 3 2 KEY (ea0243acc1bd) X GRANT
59 2 3 2 KEY (b801aadcc2bb) X GRANT
59 2 3 1 KEY (4f0081114225) X GRANT
59 2 3 2 KEY (9901c89022fd) X GRANT
59 2 3 2 KEY (6b0113c06a55) X GRANT
59 2 1 3 KEY (7600a73c60ea) X GRANT
59 2 3 2 KEY (dc0182a7d165) X GRANT
59 2 3 1 KEY (81008ec31911) X GRANT
59 2 587357357 0 TAB Sch-M GRANT
59 2 3 2 KEY (d5017a82f3c9) X GRANT
59 2 3 1 KEY (7d00367ccf5b) X GRANT
59 2 3 1 KEY (7700ea23a47e) X GRANT
59 2 3 2 KEY (48022e9d1de7) X GRANT
59 2 3 2 KEY (8202c9f8391e) X GRANT
59 2 3 1 KEY (af005c6d402e) X GRANT
59 2 3 1 KEY (b4006e9d9e19) X GRANT
59 2 3 1 KEY (ae00b2c2f53c) X GRANT
59 2 3 1 KEY (b800d6224853) X GRANT
59 2 1 3 KEY (db000c3967a4) X GRANT
59 2 3 1 KEY (4d000ad94b8f) X GRANT
59 2 1 1 KEY (ad001f254d94) X GRANT
59 2 99 0 RID 1:83:5 X GRANT
59 2 3 2 KEY (11032879a053) X GRANT
59 2 1 3 KEY (be00e9b4690b) X GRANT
59 2 3 2 KEY (7d023b626dd6) X GRANT
59 2 3 2 KEY (4a021f958311) X GRANT
59 2 99 0 RID 1:265:5 X GRANT
59 2 3 2 KEY (0b02e0c0b85a) X GRANT
59 2 3 2 KEY (0002fcd2ea7e) X GRANT
59 2 3 2 KEY (1d0212a37d37) X GRANT
59 2 3 2 KEY (e302a4bc0768) X GRANT
59 2 3 2 KEY (6402e44233a7) X GRANT
59 2 99 0 RID 1:83:6 X GRANT
59 2 1 2 KEY (a516e15fb233) X GRANT
59 2 3 2 KEY (a0020c139ab2) X GRANT
59 2 3 2 KEY (7c018b8b4961) X GRANT
59 2 3 2 KEY (d902d90e43bc) X GRANT
59 2 3 1 KEY (27009c362aa2) X GRANT
59 2 3 2 KEY (7602a911580d) X GRANT
59 2 3 2 KEY (4e0150db01c9) X GRANT
59 2 3 1 KEY (2100f8d697cd) X GRANT
59 2 3 1 KEY (23002489fce8) X GRANT
59 2 2 1 KEY (4c003c6ce3ea) X GRANT
59 2 3 2 KEY (f2015f39f403) X GRANT
59 2 99 0 RID 1:265:6 X GRANT
59 2 1 3 KEY (4c00df72296c) X GRANT
59 2 3 2 KEY (7b014b218404) X GRANT
59 2 3 2 KEY (b901b05ddd89) X GRANT
59 2 3 2 KEY (c3011dd2e151) X GRANT
59 2 3 1 KEY (20009db12b75) X GRANT
59 2 3 2 KEY (8e01e64c5a6a) X GRANT
59 2 3 1 KEY (2600f951961a) X GRANT
59 2 3 1 KEY (e100ecbdf7a1) X GRANT
59 2 3 1 KEY (e500540221eb) X GRANT
59 2 3 1 KEY (e400baad94f9) X GRANT
59 2 3 1 KEY (220041ee4050) X GRANT
59 2 3 1 KEY (e000021242b3) X GRANT
59 2 99 0 RID 1:83:7 X GRANT
59 2 1 3 KEY (1e00e99c369e) X GRANT
59 2 3 1 KEY (4e00e476fe9d) X GRANT
59 2 3 2 KEY (7802b44a7dca) X GRANT
59 2 3 2 KEY (ac026cfafba1) X GRANT
59 2 3 2 KEY (d702c455667b) X GRANT
59 2 475356958 0 TAB Sch-M GRANT
59 2 0 0 IDX IDX: 2:475356958 X GRANT
59 2 3 1 KEY (c3002cb8e463) X GRANT
59 2 3 2 KEY (ef010149377d) X GRANT
59 2 3 1 KEY (c100f0e78f46) X GRANT
59 2 3 1 KEY (c70094073229) X GRANT
59 2 3 1 KEY (e20089da4b19) X GRANT
59 2 99 0 RID 1:265:7 X GRANT
59 2 3 2 KEY (a6015ec98acd) X GRANT
59 2 3 1 KEY (e60031659d53) X GRANT
59 2 3 2 KEY (0b02fc0d2c21) X GRANT
59 2 3 1 KEY (7c00531b73e3) X GRANT
59 2 3 1 KEY (8000eba4a5a9) X GRANT
59 2 3 1 KEY (c400c2175171) X GRANT
59 2 1 1 KEY (4c00aa724f28) X GRANT
59 2 3 2 KEY (5902f55ebf3e) X GRANT
59 2 99 0 RID 1:255:1 X GRANT
59 2 3 1 KEY (c8007aa8873b) X GRANT
59 2 3 2 KEY (dd01420d1c00) X GRANT
59 2 99 0 RID 1:265:1 X GRANT
59 2 3 1 KEY (b3000bfa22a1) X GRANT
59 2 3 1 KEY (b000390afc96) X GRANT
59 2 3 1 KEY (b6005dea41f9) X GRANT
59 2 3 2 KEY (9202e05db1c9) X GRANT
59 2 3 1 KEY (b700b345f4eb) X GRANT
59 2 3 2 KEY (a2021148bf75) X GRANT
59 2 0 0 EXT 1:272 X GRANT
59 2 3 1 KEY (b500388dfd41) X GRANT
59 2 3 2 KEY (2802bf1647bd) X GRANT
59 2 3 1 KEY (b10080322b0b) X GRANT
59 2 3 1 KEY (2500af41f542) X GRANT
59 2 3 1 KEY (1f00731e9e67) X GRANT
59 2 3 1 KEY (290017fe2308) X GRANT
59 2 3 2 KEY (25024f5e16cb) X GRANT
59 2 3 1 KEY (57006e39f6e0) X GRANT
59 2 3 1 KEY (5300d68620aa) X GRANT
59 2 3 2 KEY (0302beef3bc5) X GRANT
59 2 3 2 KEY (110204d8baa9) X GRANT
59 2 859358326 0 TAB Sch-M GRANT
59 2 0 0 IDX IDX: 2:859358326 X GRANT
59 2 99 0 RID 1:83:1 X GRANT
59 2 2 1 KEY (1e0029c04d24) X GRANT
59 2 2 1 KEY (ad00944e5e86) X GRANT
59 2 3 2 KEY (070355cbe487) X GRANT
59 2 3 1 KEY (de00de4d2996) X GRANT
59 2 3 2 KEY (b402ab55b0bf) X GRANT
59 2 3 1 KEY (5000382995b8) X GRANT
59 2 3 1 KEY (5400809643f2) X GRANT
59 2 3 2 KEY (e8025ef7e47a) X GRANT
59 2 3 2 KEY (54026227c7c5) X GRANT
59 2 2 1 KEY (be008cfdf811) X GRANT
59 2 3 1 KEY (7a00d8d37a49) X GRANT
59 2 3 1 KEY (7800048c116c) X GRANT
59 2 3 2 KEY (6001da66a88a) X GRANT
59 2 3 1 KEY (7e00606cac03) X GRANT
59 2 3 2 KEY (fa010116779c) X GRANT
59 2 99 0 RID 1:265:2 X GRANT
59 2 3 1 KEY (56000b5e4a58) X GRANT
59 2 1 1 KEY (be009cb40f4f) X GRANT
59 2 3 1 KEY (bf007b2f86ec) X GRANT
59 2 3 2 KEY (0a023ca7e144) X GRANT
59 2 3 2 KEY (4d019071ccac) X GRANT
59 2 3 1 KEY (5200b3e19c12) X GRANT
59 2 3 1 KEY (c500a770edc9) X GRANT
59 2 99 0 RID 1:83:2 X GRANT
59 2 3 1 KEY (c9001fcf3b83) X GRANT
59 2 3 1 KEY (b200e55597b3) X GRANT
59 2 1 3 KEY (ad006a252bd0) X GRANT
59 2 3 2 KEY (bb0143fd62ce) X GRANT
59 2 1 1 KEY (db00793901e0) X GRANT
59 2 3 1 KEY (7f00050b10bb) X GRANT
59 2 3 2 KEY (0302857cb7b4) X GRANT
59 2 3 1 KEY (7b00bdb4c6f1) X GRANT
59 2 3 1 KEY (790061ebadd4) X GRANT
59 2 0 0 PAG 1:255 X GRANT
59 2 0 0 PAG 1:254 X GRANT
59 2 99 0 RID 1:255:0 X GRANT
59 2 2 1 KEY (76001c75619c) X GRANT
59 2 99 0 RID 1:249:0 X GRANT
59 2 3 2 KEY (910196bfa162) X GRANT
59 2 0 0 PAG 1:193 X GRANT
59 2 0 0 PAG 1:199 X GRANT
59 2 0 0 PAG 1:266 X GRANT
59 2 0 0 PAG 1:267 X GRANT
59 2 0 0 PAG 1:272 X GRANT
59 2 0 0 PAG 1:270 X GRANT
59 2 0 0 PAG 1:271 X GRANT
59 2 3 2 KEY (63014defe9ca) X GRANT
59 2 1 1 KEY (1e009c9c50da) X GRANT
59 2 3 2 KEY (a5019e6347a8) X GRANT
59 2 0 0 PAG 1:86 X GRANT
59 2 1 0 TAB IX GRANT
59 2 3 0 TAB IX GRANT
59 2 2 0 TAB IX GRANT
59 2 6 0 TAB IX GRANT
59 8 0 0 DB S GRANT
60 8 0 0 DB S GRANT
61 8 0 0 DB S GRANT
61 8 1410104064 0 TAB IX WAIT
62 1 85575343 0 TAB IS GRANT
sp_who
SPID Status Login HostName BlkBy DBName Command
CPUTime DiskIO LastBatch ProgramName SPID
-- -- -- -- -- --
-- -- -- -- -- --
1 sleeping sa . . NULL LOG
WRITER 31 0 03/12 11:57:30 1
2 BACKGROUND sa . . master SIGNAL
HANDLER 0 0 03/12 11:57:30 2
3 BACKGROUND sa . . NULL LOCK
MONITOR 0 0 03/12 11:57:30 3
4 BACKGROUND sa . . NULL LAZY
WRITER 0 0 03/12 11:57:30 4
5 BACKGROUND sa . . master TASK
MANAGER 0 55 03/12 11:57:30 5
6 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 6
7 sleeping sa . . NULL
CHECKPOINT SLEEP 219 246 03/12 11:57:30 7
8 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 8
9 BACKGROUND sa . . master TASK
MANAGER 0 619 03/12 11:57:30 9
10 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 10
11 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 11
12 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 12
13 BACKGROUND sa . . master TASK
MANAGER 0 0 03/12 11:57:30 13
14 BACKGROUND sa . . master TASK
MANAGER 0 652 03/12 11:57:30 14
51 sleeping sa . . sns_acc2 AWAITING
COMMAND 11094 23 03/14 10:17:27 51
52 sleeping sa . . sns_acc2 AWAITING
COMMAND 4734 29 03/14 10:36:50 52
53 sleeping sa . . sns_acc2 AWAITING
COMMAND 13875 340 03/14 10:36:14 53
54 sleeping sa . . sns_acc2 AWAITING
COMMAND 23344 0 03/14 09:47:44 54
55 sleeping sa . . sns_acc2 AWAITING
COMMAND 1125 0 03/14 10:36:37 55
56 sleeping sa . . sns_acc2 AWAITING
COMMAND 14906 25 03/14 10:28:29 56
57 sleeping sa . . sns_acc2 AWAITING
COMMAND 15781 25 03/14 10:21:47 57
58 sleeping sa . . sns_acc2 AWAITING
COMMAND 5688 4 03/14 09:09:35 58
59 sleeping sa . . sns_acc2 AWAITING
COMMAND 6281 0 03/14 10:21:41 59
60 sleeping sa . . sns_acc2 AWAITING
COMMAND 17875 0 03/14 10:23:16 60
61 sleeping sa . 56 sns_acc2 INSERT
7516 0 03/14 10:35:18 61
62 RUNNABLE sa NAZAR . master SELECT
INTO 8719 0 03/14 10:36:30 SQL Query Analyzer 62
Please give me a solution to solve this and your email id pls.

> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Sun, 13 Mar 2005 23:19:02 -0800, shif wrote:

>Hi sir
>I forget the sp_lock i am adding here and it is lengthy upto 450 rows i
>deleted some rows.
Hi shif,
Thanks for posting the sp_lock output. Even though you deleted some
rows, I think you've left in the ones that count.

>if i have your mail id i can mail you the full text file.
Normally, I prefer to keep newsgroup help in the groups and reserve my
e-mail box for my loving mother, the usual load of spam and paying
customers. But I understand you don't want to post the full output here,
so go ahead - I'll allow it this time. My e-mail address is in the
headers of all my messages - you'll have to remove "_NO_" and "_SPAM_"
before using it (they are included to confuse the spam-bots).

>here is the sp_who2 and sp_lock
This output is even more clear that the previous output. Let's start
with the sp_who2 output. This row:

>SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID
>-- -- -- -- -- -- -- -- -- -- -- --
(snip)
>61 sleeping sa . 56 sns_acc2 INSERT 7516 0 03/14 10:35:18 61
shows that connection with SPID 61 is attempting an insert, but has to
wait. It is apparently attempting to acquire a lock on a resource that
is already locked by another application - one that currently holds SPID
56, as the entry "56" in the BlkBy columns proves.
And this row:
>SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID
>-- -- -- -- -- -- -- -- -- -- -- --
(snip)
>56 sleeping sa . . sns_acc2 AWAITING COMMAND 14906 25 03/14 10:28:29 56
shows that the blocking connection is currently not doing anything.
Now, let's take at the entries in the sp_lock output that belong to SPID
61:

>spid dbid ObjId IndId Type Resource Mode Status
>-- -- -- -- -- -- -- --
(snip)
>61 8 0 0 DB S GRANT
>61 8 1410104064 0 TAB IX WAIT
As you see, two locks only. One is granted, one is waiting. This one is
the cause of the blocking. It's a table lock, and the mode is IX
(intent-exclusive) - that means that the application needs to acquire an
exclusive lock on either one page or one row that's part of a table.
Since locks can always be upgraded to table locks, the right to do this
has to be reserved to prevent deadlocking.
To find out which table, use the values in dbid and ObjId. First, use
dbid to identify the database name:
SELECT db_name(8)
Then switch to the database returned by the previous query, and find the
name of the offending table:
USE <db-name goes here>
GO
SELECT object_name(1410104064)
My guess is that you'll find the database to be sns_acc2 and the table
to be ap_spled.
Finally, let's inspect the sp_lock output belonging to the blocking SPID
56. There's a whole lot of rows there. Most of them in dbid 2 (tempdb on
most, maybe even all installations). Only one in dbid 8, but it has the
same ObjId as in the previous row:

>spid dbid ObjId IndId Type Resource Mode Status
>-- -- -- -- -- -- -- --
(snip)
>56 8 1410104064 0 TAB S GRANT
And this one is the culprit. It's a shared table lock on the same table
that the insert statement needs to get an intent-exclusive lock on. A
shared lock is normally used for reading data - it allows others to read
simultaneously, but it disallows exclusive locks (used for data
modification), so that the data being read can't change. For obvious
reasons, the shared lock on this table is not compatible with the
requested intent-exclusive lock, so SPID 61 is put in a wait state until
the lock is released. For some reason, his doesn't happen. Until you
close the report generating application - then, either the application
closes the connection (committing or rolling back any open transactions)
or the connection gets dropped (incurring a forced rollback of the open
transactions). All locks held by SPID 56 are then released, and the
inserting applications finaly gets the lock it has been witing for :-)
The next step should be to inspect the code of the application that
generates the report. SQL Server's default behaviour is to release
shared locks directly after the SELECT statement has finished executing.
But that default behaviour can be changed. For a report generation that
queries the same table several times, it might be necessary to prevent
any modification between those queries, to prevent inconsistensies in
the report (simple example: report debit first, then report credit - if
any booking is inserted in between, the debit total won't match the
credit total and your bookkeeper will have a fit). In such cases, the
transaction isolation level is increased to either repeatable read or
serializable (this forces SQL Server to hold on to shared locks until
the transaction is finished). A transaction is started and all queries
needed to generate the report are run. The first query will acquire the
shared locks; they will be kept, so that no data can change until all
queries are finished. When the report is complete, the transaction is
committed, all locks are released and the report is presented to the
user (or printed, or stored, or whatever).
In your case, you should look for transactions that are started (with
BEGIN TRANSACTION), but not ended (with either ROLLBACK TRANSACTION or
COMMIT TRANSACTION). Note that some settings can cause an implicit BEGIN
TRANSACTION. Also note that the process should not need any user input
between the start and the end of the transaction, as users tend to go
away from the computer and drink some coffee, unaware that their failure
to click "OK" causes their collegues to be unable to work.
I hope the above is helpful in determining and fixing your problem!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||HI
Again back,actualy the problem was in the stored procedure i am using an
update stmt,i checked by removing the update stmt
and the hanginh prblm for 2 days not happend hope it's ok and
thanks for your help.another thing if i want to include the update
stsmt in the procedure like this What i have to do.
update #pd_temp set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheque_no
from pay_hd where #pd_temp.tr_no = pay_hd.tr_no and (#pd_temp.trans_type='PT'
OR #pd_temp.trans_type='PV')
This stmt is creating prblm,can i give any commit here.Can you please help
with a sample stmt.
Thank you
"Hugo Kornelis" wrote:

> On Sun, 13 Mar 2005 23:19:02 -0800, shif wrote:
>
> Hi shif,
> Thanks for posting the sp_lock output. Even though you deleted some
> rows, I think you've left in the ones that count.
>
> Normally, I prefer to keep newsgroup help in the groups and reserve my
> e-mail box for my loving mother, the usual load of spam and paying
> customers. But I understand you don't want to post the full output here,
> so go ahead - I'll allow it this time. My e-mail address is in the
> headers of all my messages - you'll have to remove "_NO_" and "_SPAM_"
> before using it (they are included to confuse the spam-bots).
>
> This output is even more clear that the previous output. Let's start
> with the sp_who2 output. This row:
> (snip)
> shows that connection with SPID 61 is attempting an insert, but has to
> wait. It is apparently attempting to acquire a lock on a resource that
> is already locked by another application - one that currently holds SPID
> 56, as the entry "56" in the BlkBy columns proves.
> And this row:
> (snip)
> shows that the blocking connection is currently not doing anything.
> Now, let's take at the entries in the sp_lock output that belong to SPID
> 61:
> (snip)
> As you see, two locks only. One is granted, one is waiting. This one is
> the cause of the blocking. It's a table lock, and the mode is IX
> (intent-exclusive) - that means that the application needs to acquire an
> exclusive lock on either one page or one row that's part of a table.
> Since locks can always be upgraded to table locks, the right to do this
> has to be reserved to prevent deadlocking.
> To find out which table, use the values in dbid and ObjId. First, use
> dbid to identify the database name:
> SELECT db_name(8)
> Then switch to the database returned by the previous query, and find the
> name of the offending table:
> USE <db-name goes here>
> GO
> SELECT object_name(1410104064)
> My guess is that you'll find the database to be sns_acc2 and the table
> to be ap_spled.
> Finally, let's inspect the sp_lock output belonging to the blocking SPID
> 56. There's a whole lot of rows there. Most of them in dbid 2 (tempdb on
> most, maybe even all installations). Only one in dbid 8, but it has the
> same ObjId as in the previous row:
> (snip)
> And this one is the culprit. It's a shared table lock on the same table
> that the insert statement needs to get an intent-exclusive lock on. A
> shared lock is normally used for reading data - it allows others to read
> simultaneously, but it disallows exclusive locks (used for data
> modification), so that the data being read can't change. For obvious
> reasons, the shared lock on this table is not compatible with the
> requested intent-exclusive lock, so SPID 61 is put in a wait state until
> the lock is released. For some reason, his doesn't happen. Until you
> close the report generating application - then, either the application
> closes the connection (committing or rolling back any open transactions)
> or the connection gets dropped (incurring a forced rollback of the open
> transactions). All locks held by SPID 56 are then released, and the
> inserting applications finaly gets the lock it has been witing for :-)
> The next step should be to inspect the code of the application that
> generates the report. SQL Server's default behaviour is to release
> shared locks directly after the SELECT statement has finished executing.
> But that default behaviour can be changed. For a report generation that
> queries the same table several times, it might be necessary to prevent
> any modification between those queries, to prevent inconsistensies in
> the report (simple example: report debit first, then report credit - if
> any booking is inserted in between, the debit total won't match the
> credit total and your bookkeeper will have a fit). In such cases, the
> transaction isolation level is increased to either repeatable read or
> serializable (this forces SQL Server to hold on to shared locks until
> the transaction is finished). A transaction is started and all queries
> needed to generate the report are run. The first query will acquire the
> shared locks; they will be kept, so that no data can change until all
> queries are finished. When the report is complete, the transaction is
> committed, all locks are released and the report is presented to the
> user (or printed, or stored, or whatever).
> In your case, you should look for transactions that are started (with
> BEGIN TRANSACTION), but not ended (with either ROLLBACK TRANSACTION or
> COMMIT TRANSACTION). Note that some settings can cause an implicit BEGIN
> TRANSACTION. Also note that the process should not need any user input
> between the start and the end of the transaction, as users tend to go
> away from the computer and drink some coffee, unaware that their failure
> to click "OK" causes their collegues to be unable to work.
> I hope the above is helpful in determining and fixing your problem!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 17 Mar 2005 07:35:04 -0800, shif wrote:

>HI
>Again back,actualy the problem was in the stored procedure i am using an
>update stmt,i checked by removing the update stmt
>and the hanginh prblm for 2 days not happend hope it's ok and
>thanks for your help.another thing if i want to include the update
>stsmt in the procedure like this What i have to do.
>update #pd_temp set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheque_no
>from pay_hd where #pd_temp.tr_no = pay_hd.tr_no and (#pd_temp.trans_type='PT'
>OR #pd_temp.trans_type='PV')
>This stmt is creating prblm,can i give any commit here.Can you please help
>with a sample stmt.
>Thank you
Hi shif,
You have completely lost me now. I just re-read our complete exchange.
First, you posted a stored procedure that creates a temp table #oi,
inserts some data from ap_spled into it, then selects some data from a
join of #oi and ap_spled. You also post an INSERT INTO ap_spled
statement that hangs after this procedure has run.
Now, you suddenly claim that you have removed an update statement that
updates #pd_temp based on data in pay_hd. How is that possible? How can
you remove a statement that never was there?
Anyway - did you already check the db_name and object_name I suggested
in my previous post? Did you inspect if the front-end code leaves any
transactions open? I still believe that to be the cause of your
problems. Fix your frontend to close the transaction after generating
the report and your problems will probably go away.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi
I am sorry ,actualy i had copied a differend update syntax but both having
same
effect.
thanks
update #out_inv set dept_code = pay_hd.dept_code,inv_no = pay_hd.cheq_no
from pay_hd
where #out_inv.tr_no = pay_hd.tr_no and
(#out_inv.trans_type='PT' OR #out_inv.trans_type='PV')
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 07:35:04 -0800, shif wrote:
>
> Hi shif,
> You have completely lost me now. I just re-read our complete exchange.
> First, you posted a stored procedure that creates a temp table #oi,
> inserts some data from ap_spled into it, then selects some data from a
> join of #oi and ap_spled. You also post an INSERT INTO ap_spled
> statement that hangs after this procedure has run.
> Now, you suddenly claim that you have removed an update statement that
> updates #pd_temp based on data in pay_hd. How is that possible? How can
> you remove a statement that never was there?
> Anyway - did you already check the db_name and object_name I suggested
> in my previous post? Did you inspect if the front-end code leaves any
> transactions open? I still believe that to be the cause of your
> problems. Fix your frontend to close the transaction after generating
> the report and your problems will probably go away.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||HI
Actualy i have posted is right only thing is that i have changed the table
name instead of #oi i put some other name.
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 07:35:04 -0800, shif wrote:
>
> Hi shif,
> You have completely lost me now. I just re-read our complete exchange.
> First, you posted a stored procedure that creates a temp table #oi,
> inserts some data from ap_spled into it, then selects some data from a
> join of #oi and ap_spled. You also post an INSERT INTO ap_spled
> statement that hangs after this procedure has run.
> Now, you suddenly claim that you have removed an update statement that
> updates #pd_temp based on data in pay_hd. How is that possible? How can
> you remove a statement that never was there?
> Anyway - did you already check the db_name and object_name I suggested
> in my previous post? Did you inspect if the front-end code leaves any
> transactions open? I still believe that to be the cause of your
> problems. Fix your frontend to close the transaction after generating
> the report and your problems will probably go away.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

cannot insert to db

Cannot get insert sql to work or stored procedures for some reason. They used to

work perfectly two weeks ago. All connection strings look good.....web config ok.

Can any one suggest where I might check next... Select statements are pulling data

ok from db.....run out of ideas

heres my web config......

I did move my application into another directory.....does this second connection string connect to that new instance of sql server?

<

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<

connectionStrings>

<

addname="Databasest1000ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\richard\My Documents\Visual Studio 2005\WebSites\st1000\App_Data\Databasest1000.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

<

addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Databasest1000.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" />

</

connectionStrings>|||

Hi,

Please check if you have permission to read and write to

C:\Documents and Settings\richard\My Documents\Visual Studio 2005\WebSites\st1000\App_Data\Databasest1000.mdf

for the ASPNET or NETWORK SERVICE account. I would suggest you put the .mdf file in App_Data folder instead of the folder under documents and settings.

|||

Hi Kevin

Thanks so much for reply.......I,m six months into project and its like someone has turned the light off.

""""Please check if you have permission to read and write to

C:\Documents and Settings\richard\My Documents\Visual Studio 2005\WebSites\st1000\App_Data\Databasest1000.mdf""

how do I do that

|||Go to the folder C:\Documents and Settings\richard\My Documents\Visual Studio 2005\WebSites\st1000\App_Data\, then right click with your mouse on Databasest1000.mdf file and click on Properties. Finally click on the tab Security.|||

I appear to have four group or user names.....strange...where have they come from

1. Administrators(DICKSLAPTOP\Administrators)

2. richard(DICKSLAPTOP\richard)

3. SYSTEM

4. Users(DICKSLAPTOP\Users)

Only the last one ....... (4.)........allows full control.....

all others are greyed out.......1. 2 3.

|||

under the advanced tab.....where 5x named users are listed

the last one Users(DICKSLAPTOP\Users).....seems to be read and execute...only

Cannot insert the value NULL into column...

I am trying to create a drop?down?list?with?the?possible?usernames?that?you?can?send?an?email?to.??Everything?works?fine?except?for?the?dropdown?list?and?I?get?the?following?error?message:
Cannot insert the value NULL into column 'EmailTo', table 'db191165913.dbo191165913.Email'; column does not allow nulls. INSERT fails.
The statement has been terminated

My code is below:

<%@. Page Language="VB" MasterPageFile="~/real_world/realworld_MasterPage.master" AutoEventWireup="false" CodeFile="send_messages.aspx.vb" Inherits="send_messages" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:Label ID="email_label" runat="server" Visible="False"></asp:Label>
<asp:SqlDataSource ID="emailsend_source" runat="server" ConnectionString="<%$ ConnectionStrings:IMS_DB %>"
DeleteCommand="DELETE FROM WHERE [EmailID] = @.EmailID"
InsertCommand="INSERT INTO ([UserID], [EmailTo], [EmailFrom], [Subject], [Message], [Status], [Reply], [Forward], [UserName], [SentOn], [IP], [BrowserInfo], [DNSInfo]) VALUES (@.UserID, @.EmailTo, @.EmailFrom, @.Subject, @.Message, '@.Status', '@.Reply', '@.Forward', @.UserName, @.SentOn, @.IP, @.BrowserInfo, @.DNSInfo)"
SelectCommand="SELECT * FROM WHERE ([UserID] = @.UserID)"
UpdateCommand="UPDATE SET [UserID] = @.UserID, [EmailTo] = @.EmailTo, [EmailFrom] = @.EmailFrom, [Subject] = @.Subject, [Message] = @.Message, [Status] = @.Status, [Reply] = @.Reply, [Forward] = @.Forward, [UserName] = @.UserName, [BrowserInfo] = @.BrowserInfo, [DNSInfo] = @.DNSInfo WHERE [EmailID] = @.EmailID">
<DeleteParameters>
<asp:Parameter Name="EmailID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="UserID" />
<asp:Parameter Name="EmailTo" Type="String" />
<asp:Parameter Name="EmailFrom" Type="String" />
<asp:Parameter Name="Subject" Type="String" />
<asp:Parameter Name="Message" Type="String" />
<asp:Parameter Name="Status" Type="String" />
<asp:Parameter Name="Reply" Type="String" />
<asp:Parameter Name="Forward" Type="String" />
<asp:Parameter Name="UserName" Type="String" />
<asp:Parameter Name="BrowserInfo" Type="String" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="email_label" Name="UserID" PropertyName="Text" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="UserID" />
<asp:Parameter Name="EmailTo" Type="String" />
<asp:Parameter Name="EmailFrom" Type="String" />
<asp:Parameter Name="Subject" Type="String" />
<asp:Parameter Name="Message" Type="String" />
<asp:Parameter Name="Status" Type="String" />
<asp:Parameter Name="Reply" Type="String" />
<asp:Parameter Name="Forward" Type="String" />
<asp:Parameter Name="UserName" Type="String" />
<asp:Parameter Name="IP" Type="String" />
<asp:Parameter Name="SentOn" Type="datetime" />
<asp:Parameter Name="BrowserInfo" Type="String" />
<asp:Parameter Name="DNSInfo" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="username_email" runat="server" ConnectionString="<%$ ConnectionStrings:IMS_DB %>"
SelectCommand="SELECT [UserName] FROM [Membership] ORDER BY [UserName]">
</asp:SqlDataSource>
<asp:ValidationSummary ID="ValidationSummary1" runat="server" HeaderText="You must fill out the following forms:"
ValidationGroup="email_send" />

<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="EmailID"
DataSourceID="emailsend_source" DefaultMode="Insert" GridLines="None" Height="50px"
Width="125px">
<Fields>
<asp:BoundField DataField="EmailID" HeaderText="EmailID" InsertVisible="False" ReadOnly="True"
SortExpression="EmailID" />
<asp:TemplateField HeaderText="To" SortExpression="EmailTo">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("EmailTo") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="emailsend_source" DataTextField="UserName" SelectedValue='<%# Bind("UserName") %>' AppendDataBoundItems="True" DataValueField="UserName">
<asp:ListItem>-- Receiving User --</asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="DropDownList1"
Display="Dynamic" ErrorMessage="Message Receiver (To)" ValidationGroup="email_send"></asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("EmailTo") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="From" SortExpression="EmailFrom">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("EmailFrom") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:LoginName ID="login_name" runat="server" />
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("EmailFrom") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Subject" SortExpression="Subject">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Subject") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" MaxLength="50" Text='<%# Bind("Subject") %>'
ValidationGroup="send_email"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="TextBox3"
Display="Dynamic" ErrorMessage="Subject" ValidationGroup="email_send"></asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("Subject") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Message" SortExpression="Message">
<EditItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Message") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Columns="40" MaxLength="500" Rows="10"
Text='<%# Bind("Message") %>' TextMode="MultiLine" ValidationGroup="send_mail"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="TextBox4"
Display="Dynamic" ErrorMessage="Message" ValidationGroup="email_send"></asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Message") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField InsertText="Send Message" ShowInsertButton="True" ValidationGroup="email_send" />
</Fields>
</asp:DetailsView>
</asp:Content

What about changing the EmaiTto Parameter to a ControlParameter:

<InsertParameters>
<asp:Parameter Name="UserID" />
<asp:Parameter Name="EmailTo" Type="String" />

<asp:ControlParameter ControlID="DetailsView1$DropDownList1" Name="EmailTo" PropertyName="SelectedValue" />

.....

|||Thanks for helping me solve that problem! ?Do you happen to know the answer to my other question at http://forums.asp.net/thread/1699355.aspx

Cannot insert the value NULL into column 'rowguid'....

Hello
i'm using merge replication in SQL server 2k but when i try to synchronise a
subscriber i get the following error
Cannot insert the value NULL into column 'rowguid'...
This seems to be caused by the fact that the columns are in a different
order
ie. columns we added after the publication
is there a way to cause the publication to name the fields in the insert
therefore eliminating this problem?
Thanks
How did you add the columns after the table was published?
Donna
"kris Palmby" wrote:

> Hello
> i'm using merge replication in SQL server 2k but when i try to synchronise a
> subscriber i get the following error
> Cannot insert the value NULL into column 'rowguid'...
> This seems to be caused by the fact that the columns are in a different
> order
> ie. columns we added after the publication
> is there a way to cause the publication to name the fields in the insert
> therefore eliminating this problem?
> Thanks
>
>
|||I'd like to know this also.
"Donna Lambert" <DonnaLambert@.discussions.microsoft.com> wrote in message
news:E4388B76-FBB2-4F00-8893-1F3F0ACEFB31@.microsoft.com...[vbcol=seagreen]
> How did you add the columns after the table was published?
> Donna
> "kris Palmby" wrote:

Cannot insert the value NULL into column OrderID -- BUT IT IS NOT NULL!

I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." --But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.

Server Error in '/' Application.

---

Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.

Source Error:

Line 89: sContact.Phone = sPhone.Text.TrimLine 90: sContact.Email = sEmail.Text.TrimLine 91: sContact.Save()Line 92: Line 93: Dim bContact As Contact = New Contact()

Source File:F:\Inetpub\wwwroot\Outman Knife\Checkout.aspx.vb Line:91

Stack Trace:

[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlDataReader.HasMoreRows() +150 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214 System.Data.SqlClient.SqlDataReader.Read() +9 System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39 System.Data.SqlClient.SqlCommand.ExecuteScalar() +148 SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209 SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37 SubSonic.ActiveRecord`1.Save(String userName) +120 SubSonic.ActiveRecord`1.Save() +31 Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:\Inetpub\wwwroot\Outman Knife\Checkout.aspx.vb:91 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

Please post the code of the Save method of your Contact class, the stored procedure that it calls and the table definition.|||

I am using SubSonic, so I am not sure how I would do this. Here is the entire Contact class:

1Imports System2Imports System.Text3Imports System.Data4Imports System.Data.SqlClient5Imports System.Data.Common6Imports System.Collections7Imports System.Collections.Generic8Imports System.Configuration9Imports System.Xml10Imports System.Xml.Serialization11Imports SubSonic12131415''' <summary>16''' Strongly-typed collection for the Contact class.17''' </summary>18<Serializable> _19Public PartialClass ContactCollection20Inherits ActiveList(Of Contact)2122Private wheresAs List(Of Where) =New List(Of Where)()23Private betweensAs List(Of BetweenAnd) =New List(Of BetweenAnd)()24Private orderByAs SubSonic.OrderBy25Public Function OrderByAsc(ByVal columnNameAs String)As ContactCollection26Me.orderBy = SubSonic.OrderBy.Asc(columnName)27Return Me28 End Function29 Public Function OrderByDesc(ByVal columnNameAs String)As ContactCollection30Me.orderBy = SubSonic.OrderBy.Desc(columnName)31Return Me32 End Function33 Public Function WhereDatesBetween(ByVal columnNameAs String,ByVal dateStartAs DateTime,ByVal dateEndAs DateTime)As ContactCollection34Return Me3536 End Function3738 Public Function Where(ByVal where_RenamedAs Where)As ContactCollection39 wheres.Add(where_Renamed)40Return Me41 End Function42 Public Function Where(ByVal columnNameAs String,ByVal valueAs Object)As ContactCollection43Dim where_RenamedAs Where =New Where()44 where_Renamed.ColumnName = columnName45 where_Renamed.ParameterValue = value46Me.Where(where_Renamed)47Return Me48 End Function49 Public Function Where(ByVal columnNameAs String,ByVal compAs Comparison,ByVal valueAs Object)As ContactCollection50Dim where_RenamedAs Where =New Where()51 where_Renamed.ColumnName = columnName52 where_Renamed.Comparison = comp53 where_Renamed.ParameterValue = value54Me.Where(where_Renamed)55Return Me5657 End Function58 Public Function BetweenAnd(ByVal columnNameAs String,ByVal dateStartAs DateTime,ByVal dateEndAs DateTime)As ContactCollection59Dim betweenAs BetweenAnd =New BetweenAnd()60 between.ColumnName = columnName61 between.StartDate = dateStart62 between.EndDate = dateEnd63 betweens.Add(between)64Return Me65 End Function66 Public Overloads Function Load()As ContactCollection6768Dim qryAs Query =New Query("Contact")69For Each whereAs WhereIn wheres70 qry.AddWhere(where)71Next72 For Each betweenAs BetweenAndIn betweens73 qry.AddBetweenAnd(between)74Next7576 If Not orderByIs Nothing Then77 qry.OrderBy = orderBy78End If7980 Dim rdrAs IDataReader = qry.ExecuteReader()81Me.Load(rdr)82 rdr.Close()8384Return Me85 End Function8687Public Overloads Function Load(ByVal qryAs Query)As ContactCollection8889Dim rdrAs IDataReader = qry.ExecuteReader()90Me.Load(rdr)91 rdr.Close()9293Return Me94 End Function9596 Public Sub New()979899End Sub100101End Class102103''' <summary>104''' This is an ActiveRecord class which wraps the Contact table.105''' </summary>106<Serializable> _107Public PartialClass Contact108Inherits ActiveRecord(Of Contact)109110#Region"Default Settings"111Private Sub SetSQLProps()112If SchemaIs Nothing Then113 Schema = Query.BuildTableSchema("Contact")114End If115 End Sub116 #End Region117118 #Region"Schema Accessor"119Public Shared Function GetTableSchema()As TableSchema.Table120Dim itemAs Contact =New Contact()121Return Contact.Schema122End Function123 #End Region124125 #Region"Query Accessor"126Public Shared Function CreateQuery()As Query127Return New Query("Contact")128End Function129 #End Region130131 #Region".ctors"132Public Sub New()133 SetSQLProps()134 SetDefaults()135Me.MarkNew()136End Sub137138 Public Sub New(ByVal keyIDAs Object)139 SetSQLProps()140MyBase.LoadByKey(keyID)141End Sub142143 Public Sub New(ByVal columnNameAs String,ByVal columnValueAs Object)144 SetSQLProps()145MyBase.LoadByParam(columnName,columnValue)146End Sub147 #End Region148149 #Region"Public Properties"150 <XmlAttribute("OrderID")> _151Public Property OrderIDAs Guid152Get153 Dim resultAs Object =Me.GetColumnValue("OrderID")154Dim oOutAs Guid=Guid.Empty155Try156oOut=New Guid(result.ToString())157Catch158End Try159 Return oOut160161End Get162 Set163 Me.MarkDirty()164Me.SetColumnValue("OrderID", Value)165End Set166 End Property167 <XmlAttribute("Type")> _168Public Property TypeAs String169 Get170 Dim resultAs Object =Me.GetColumnValue("Type")171Dim sOutAs String172 If resultIs Nothing Then : sOut =String.Empty173Else : sOut = result.ToString()174End If175 Return sOut176177End Get178 Set179 Me.MarkDirty()180Me.SetColumnValue("Type", Value)181End Set182 End Property183 <XmlAttribute("LastName")> _184Public Property LastNameAs String185 Get186 Dim resultAs Object =Me.GetColumnValue("LastName")187Dim sOutAs String188 If resultIs Nothing Then : sOut =String.Empty189Else : sOut = result.ToString()190End If191 Return sOut192193End Get194 Set195 Me.MarkDirty()196Me.SetColumnValue("LastName", Value)197End Set198 End Property199 <XmlAttribute("FirstName")> _200Public Property FirstNameAs String201 Get202 Dim resultAs Object =Me.GetColumnValue("FirstName")203Dim sOutAs String204 If resultIs Nothing Then : sOut =String.Empty205Else : sOut = result.ToString()206End If207 Return sOut208209End Get210 Set211 Me.MarkDirty()212Me.SetColumnValue("FirstName", Value)213End Set214 End Property215 <XmlAttribute("Company")> _216Public Property CompanyAs String217 Get218 Dim resultAs Object =Me.GetColumnValue("Company")219Dim sOutAs String220 If resultIs Nothing Then : sOut =String.Empty221Else : sOut = result.ToString()222End If223 Return sOut224225End Get226 Set227 Me.MarkDirty()228Me.SetColumnValue("Company", Value)229End Set230 End Property231 <XmlAttribute("Address")> _232Public Property AddressAs String233 Get234 Dim resultAs Object =Me.GetColumnValue("Address")235Dim sOutAs String236 If resultIs Nothing Then : sOut =String.Empty237Else : sOut = result.ToString()238End If239 Return sOut240241End Get242 Set243 Me.MarkDirty()244Me.SetColumnValue("Address", Value)245End Set246 End Property247 <XmlAttribute("City")> _248Public Property CityAs String249 Get250 Dim resultAs Object =Me.GetColumnValue("City")251Dim sOutAs String252 If resultIs Nothing Then : sOut =String.Empty253Else : sOut = result.ToString()254End If255 Return sOut256257End Get258 Set259 Me.MarkDirty()260Me.SetColumnValue("City", Value)261End Set262 End Property263 <XmlAttribute("StateID")> _264Public Property StateIDAs Integer265 Get266 Dim resultAs Object =Me.GetColumnValue("StateID")267Dim oOutAs Integer = 0268Try269oOut =Integer.Parse(result.ToString())270Catch271End Try272 Return oOut273274End Get275 Set276 Me.MarkDirty()277Me.SetColumnValue("StateID", Value)278End Set279 End Property280 <XmlAttribute("ZipCode")> _281Public Property ZipCodeAs Integer282 Get283 Dim resultAs Object =Me.GetColumnValue("ZipCode")284Dim oOutAs Integer = 0285Try286oOut =Integer.Parse(result.ToString())287Catch288End Try289 Return oOut290291End Get292 Set293 Me.MarkDirty()294Me.SetColumnValue("ZipCode", Value)295End Set296 End Property297 <XmlAttribute("Phone")> _298Public Property PhoneAs String299 Get300 Dim resultAs Object =Me.GetColumnValue("Phone")301Dim sOutAs String302 If resultIs Nothing Then : sOut =String.Empty303Else : sOut = result.ToString()304End If305 Return sOut306307End Get308 Set309 Me.MarkDirty()310Me.SetColumnValue("Phone", Value)311End Set312 End Property313 <XmlAttribute("Email")> _314Public Property EmailAs String315 Get316 Dim resultAs Object =Me.GetColumnValue("Email")317Dim sOutAs String318 If resultIs Nothing Then : sOut =String.Empty319Else : sOut = result.ToString()320End If321 Return sOut322323End Get324 Set325 Me.MarkDirty()326Me.SetColumnValue("Email", Value)327End Set328 End Property329330 #End Region331332 #Region"ObjectDataSource support"333''' <summary>334 ''' Inserts a record, can be used with the ObjectDataSource335 ''' </summary>336Public Shared Sub Insert(ByVal OrderIDAs Guid,ByVal TypeAs String,ByVal LastNameAs String,ByVal FirstNameAs String,ByVal CompanyAs String,ByVal AddressAs String,ByVal CityAs String,ByVal StateIDAs Integer,ByVal ZipCodeAs Integer,ByVal PhoneAs String,ByVal EmailAs String)337Dim itemAs Contact =New Contact()338 item.OrderID = OrderID339item.Type = Type340item.LastName = LastName341item.FirstName = FirstName342item.Company = Company343item.Address = Address344item.City = City345item.StateID = StateID346item.ZipCode = ZipCode347item.Phone = Phone348item.Email = Email349350 item.Save(System.Web.HttpContext.Current.User.Identity.Name)351End Sub352353''' <summary>354 ''' Updates a record, can be used with the ObjectDataSource355 ''' </summary>356Public Shared Sub Update(ByVal OrderIDAs Guid,ByVal TypeAs String,ByVal LastNameAs String,ByVal FirstNameAs String,ByVal CompanyAs String,ByVal AddressAs String,ByVal CityAs String,ByVal StateIDAs Integer,ByVal ZipCodeAs Integer,ByVal PhoneAs String,ByVal EmailAs String)357Dim itemAs Contact =New Contact()358 item.OrderID = OrderID359item.Type = Type360item.LastName = LastName361item.FirstName = FirstName362item.Company = Company363item.Address = Address364item.City = City365item.StateID = StateID366item.ZipCode = ZipCode367item.Phone = Phone368item.Email = Email369370 item.IsNew =False371 item.Save(System.Web.HttpContext.Current.User.Identity.Name)372End Sub373374 #End Region375376 #Region"Columns Struct"377Public Structure Columns378Public Shared OrderIDAs String379Public Shared TypeAs String380Public Shared LastNameAs String381Public Shared FirstNameAs String382Public Shared CompanyAs String383Public Shared AddressAs String384Public Shared CityAs String385Public Shared StateIDAs String386Public Shared ZipCodeAs String387Public Shared PhoneAs String388Public Shared EmailAs String389Private xAs Integer390391392 Shared Sub New()393 OrderID ="OrderID"394Type ="Type"395LastName ="LastName"396FirstName ="FirstName"397Company ="Company"398Address ="Address"399City ="City"400StateID ="StateID"401ZipCode ="ZipCode"402Phone ="Phone"403Email ="Email"404405End Sub406 End Structure407 #End Region408409End Class410411

|||It looks like the save method is down yet another level. Search on item.Save in the class you just posted, highlight where the Save, right-click and goto to the definition. I need to see the save and the stored procedure it calls.|||

Hopefully this has what you are looking for:

1using System;2using System.Data;3using SubSonic.Utilities;4using System.Text;5namespace SubSonic6{78public abstract class ActiveRecord : AbstractRecord where T : AbstractRecord,new()9 {10public ActiveRecord()11 {12 MarkNew();13 }1415#region CommandMethods1617/// <summary>18 /// Made Public for use with transactions19 /// </summary>20 /// <param name="userName"></param>21 /// <returns></returns>22public QueryCommand GetInsertCommand(string userName)23 {24 Query q =new Query(table);25 q.QueryType = QueryType.Insert;26 QueryCommand cmd =new QueryCommand(DataService.GetSql(q));2728//loop the Columns and addin the params2930foreach (TableSchema.TableColumn columnin table.Columns)31 {32if (!column.AutoIncrement)33 {34object oVal;35if (Utility.IsMatch(column.ColumnName, ReservedColumnName.CREATED_BY) || Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_BY))36 {37 oVal = userName;38 }39else if (Utility.IsMatch(column.ColumnName, ReservedColumnName.CREATED_ON) || Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_ON))40 {41 oVal = DateTime.Now;42 }43else44 {45 oVal = GetColumnValue(column.ColumnName);4647//if the value is a boolean, it can be read improperly48 //reset to 0 or 149if (oVal !=null)50 {51if (Utility.IsMatch(oVal.ToString(),"false"))52 {53 oVal = 0;54 }55else if (Utility.IsMatch(oVal.ToString(),"true"))56 {57 oVal = 1;58 }59 }60 }61if (oVal ==null)62 {63 oVal = DBNull.Value;64 }65 cmd.Parameters.Add("@." + column.ColumnName, oVal, column.DataType);66 }67 }68return cmd;69 }7071public QueryCommand GetUpdateCommand(string userName)72 {73 Query q =new Query(table);74 q.QueryType = QueryType.Update;75 QueryCommand cmd =new QueryCommand(DataService.GetSql(q));7677//loop the Columns and addin the params78foreach (TableSchema.TableColumn columnin table.Columns)79 {80object oVal;81if (Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_BY))82 {83 oVal = userName;84 }85else if (Utility.IsMatch(column.ColumnName, ReservedColumnName.MODIFIED_ON))86 {87 oVal = DateTime.Now;88 }89else90 {91 oVal = GetColumnValue(column.ColumnName);92 }93if (oVal ==null)94 {95 oVal = DBNull.Value;96 }97 cmd.Parameters.Add("@." + column.ColumnName, oVal,column.DataType);98 }99return cmd;100 }101102public static QueryCommand GetDeleteCommand(object keyID)103 {104 Query q =new Query(table);105 q.QueryType = QueryType.Delete;106 q.AddWhere(table.PrimaryKey.ColumnName, keyID);107108return DataService.BuildCommand(q);109 }110111public static QueryCommand GetDeleteCommand(string columnName,object oValue)112 {113 Query q =new Query(table);114 q.QueryType = QueryType.Delete;115 q.AddWhere(columnName, oValue);116117return DataService.BuildCommand(q);118 }119120#endregion121122123 #region Persistence124125protected virtual void PreUpdate()126 {127 }128129protected virtual void PostUpdate()130 {131 }132133/// <summary>134 /// Saves this object's state to the selected Database.135 /// </summary>136public void Save()137 {138 Save(String.Empty);139 }140141/// <summary>142 /// Saves this object's state to the selected Database.143 /// </summary>144 /// <param name="userID"></param>145public void Save(int userID)146 {147 Save(userID.ToString());148 }149150/// <summary>151 /// Saves this object's state to the selected Database.152 /// </summary>153 /// <param name="userID"></param>154public void Save(Guid userID)155 {156string sUserID =string.Empty;157if (userID!=null)158 sUserID = userID.ToString();159160 Save(sUserID);161 }162163/// <summary>164 /// Saves this object's state to the selected Database.165 /// </summary>166 /// <param name="userName"></param>167public void Save(string userName)168 {169 PreUpdate();170171 QueryCommand cmd;172if (IsNew)173 {174 cmd = GetInsertCommand(userName);175 }176else177 {178 cmd = GetUpdateCommand(userName);179 }180181//reset the Primary Key with the id passed back by the operation182object pkVal = DataService.ExecuteScalar(cmd);183184//set the primaryKey, only if an auto-increment185 //if (table.PrimaryKey.AutoIncrement)186 // HACK: GUID fix187if (table.PrimaryKey.AutoIncrement || table.PrimaryKey.DataType == DbType.Guid)188 {189try {190 SetPrimaryKey(pkVal);191 }catch {192193//this will happen if there is no PK defined on a table. Catch this and notify194throw new Exception("No Primary Key is defined for this table. A primary key is required to use SubSonic");195 }196 }197198//set this object as old199 MarkOld();200 isDirty =false;201 PostUpdate();202203 }204205/// <summary>206 /// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()207 /// </summary>208 /// <returns>Number of rows affected by the operation</returns>209public static int Delete(object keyID)210 {211return DeleteByParameter(BaseSchema.PrimaryKey.ColumnName, keyID,null);212 }213214/// <summary>215 /// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()216 /// </summary>217 /// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>218 /// <param name="oValue">The value that will be compared against columnName to determine deletion</param>219 /// <returns>Number of rows affected by the operation</returns>220public static int Delete(string columnName,object oValue)221 {222return DeleteByParameter(columnName, oValue,null);223 }224225/// <summary>226 /// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()227 /// </summary>228 /// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>229 /// <param name="oValue">The value that will be compared against columnName to determine deletion</param>230 /// <param name="userName">The userName that the record will be updated with. Only relevant if the record contains Deleted or IsDeleted properties</param>231 /// <returns>Number of rows affected by the operation</returns>232public static int Delete(string columnName,object oValue,string userName)233 {234return DeleteByParameter(columnName, oValue, userName);235 }236237/// <summary>238 /// If the record contains Deleted or IsDeleted flag columns, sets them to true. If not, invokes Destroy()239 /// </summary>240 /// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>241 /// <param name="oValue">The value that will be compared against columnName to determine deletion</param>242 /// <param name="userName">The userName that the record will be updated with. Only relevant if the record contains Deleted or IsDeleted properties</param>243 /// <returns>Number of rows affected by the operation</returns>244private static int DeleteByParameter(string columnName,object oValue,string userName)245 {246int iOut = 0;247248bool containsDeleted = BaseSchema.Columns.Contains(ReservedColumnName.DELETED);249bool containsIsDeleted = BaseSchema.Columns.Contains(ReservedColumnName.IS_DELETED);250bool containsModifiedBy = BaseSchema.Columns.Contains(ReservedColumnName.MODIFIED_BY);251bool containsModifiedOn = BaseSchema.Columns.Contains(ReservedColumnName.MODIFIED_ON);252if (containsDeleted || containsIsDeleted)253 {254//update the column and set deleted=true;255 //new T();256 Query qry =new Query(BaseSchema);257if (containsDeleted)258 {259 qry.AddUpdateSetting(ReservedColumnName.DELETED,true);260 }261262if (containsIsDeleted)263 {264 qry.AddUpdateSetting(ReservedColumnName.IS_DELETED,true);265 }266267if (containsModifiedBy && !String.IsNullOrEmpty(userName))268 {269 qry.AddUpdateSetting(ReservedColumnName.MODIFIED_BY, userName);270 }271272if (containsModifiedOn)273 {274 qry.AddUpdateSetting(ReservedColumnName.MODIFIED_ON, DateTime.Now);275 }276 qry.AddWhere(columnName, oValue);277 qry.Execute();278 }279else280 {281 iOut = DestroyByParameter(columnName, oValue);282 }283return iOut;284 }285286/// <summary>287 /// Deletes the record in the table, even if it contains Deleted or IsDeleted flag columns288 /// </summary>289 /// <returns>Number of rows affected by the operation</returns>290public static int Destroy(object keyID)291 {292return DestroyByParameter(BaseSchema.PrimaryKey.ColumnName, keyID);293 }294295/// <summary>296 /// Deletes the record in the table, even if it contains Deleted or IsDeleted flag columns297 /// </summary>298 /// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>299 /// <param name="oValue">The value that will be compared against columnName to determine deletion</param>300 /// <returns>Number of rows affected by the operation</returns>301public static int Destroy(string columnName,object oValue)302 {303return DestroyByParameter(columnName, oValue);304 }305306/// <summary>307 /// Deletes the record in the table, even if it contains Deleted or IsDeleted flag columns308 /// </summary>309 /// <param name="columnName">The name of the column that whose value will be evaluated for deletion</param>310 /// <param name="oValue">The value that will be compared against columnName to determine deletion</param>311 /// <returns>Number of rows affected by the operation</returns>312private static int DestroyByParameter(string columnName,object oValue)313 {314 QueryCommand cmd = GetDeleteCommand(columnName, oValue);315return DataService.ExecuteQuery(cmd);316 }317318#endregion319320 #region Object Overrides321//public string Inspect() {322 // return Inspect(true);323 //}324 //public string Inspect(bool useHtml) {325 // System.Text.StringBuilder sb = new StringBuilder();326 // string sOut = "";327 // if (useHtml) {328 // sb.Append("<table><tr><td colspan=2><h3>" + this.TableName + " Inspection</h3></td></tr>");329330 // foreach (TableSchema.TableColumn col in table.Columns) {331 // sb.Append("<tr><td><b>" + col.ColumnName + "</b></td><td>" + this.GetColumnValue(col.ColumnName).ToString() + "</td></tr>");332333 // }334 // sb.Append("</table>");335 // sOut = sb.ToString();336 // } else {337 // sb.Append("#################" + this.TableName + " Inspection ####################\r\n");338339 // foreach (TableSchema.TableColumn col in table.Columns) {340 // sb.Append(col.ColumnName + ": " + this.GetColumnValue(col.ColumnName).ToString() + "\r\n");341342 // }343 // sb.Append("#############################################################################\r\n");344 // sOut = sb.ToString();345346 // }347 // return sOut;348 //}349#endregion350 }351}

|||

Hello,

problem seems to me is OrderID, as trace said.

is Save function destroy all properties(including orderid) of the Object? (i don't see on above code, but suspicious)

try to this ??

first create a new guid:

dim orderID as guid = new system.guid.newguid()

for others create new guid from orderID, each may need to have new guid with same value

obj.OrderID = new guid(orderID.tobytearray)

Not sure, but try it. (debug: and see if OrderID value stays after Save function call)

|||

Same thing. And here is my updated code:

Protected Sub btnCheckout_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnCheckout.ClickDim CartIDAs Integer = Session("CartID")Dim OrderIDAs Guid = System.Guid.NewGuid()Dim cOrderAs OnlineOrder =New OnlineOrder cOrder.OrderID =New Guid(OrderID.ToByteArray) cOrder.CartID = CartID cOrder.Status ="PEND" cOrder.PromoCode ="NONE" cOrder.Timestamp =Date.Now cOrder.CardTypeID = ddCardType.SelectedValue cOrder.CardNumber = Outman.Utility.QuickEncrypt(CardNumber.Text.Trim) cOrder.CardExp = ExpirationDate.Text cOrder.CardVerification = Outman.Utility.QuickEncrypt(CardVerification.Text) cOrder.Save()Dim sContactAs Contact =New Contact() sContact.OrderID =New Guid(OrderID.ToByteArray) sContact.Type ="SHIP" sContact.FirstName = sFirstName.Text.Trim sContact.LastName = sLastName.Text.Trim sContact.Company = sCompany.Text.Trim sContact.Address = sAddress.Text.Trim sContact.City = sCity.Text.Trim sContact.StateID = sState.SelectedValue sContact.ZipCode = sZipCode.Text.Trim sContact.Phone = sPhone.Text.Trim sContact.Email = sEmail.Text.Trim sContact.Save()Dim bContactAs Contact =New Contact() bContact.OrderID =New Guid(OrderID.ToByteArray) bContact.Type ="BILL" bContact.FirstName = bFirstName.Text.Trim bContact.LastName = bLastName.Text.Trim bContact.Company = bCompany.Text.Trim bContact.Address = bAddress.Text.Trim bContact.City = bCity.Text.Trim bContact.StateID = bState.SelectedValue bContact.ZipCode = bZipCode.Text.Trim bContact.Phone = bPhone.Text.Trim bContact.Email = bEmail.Text.Trim bContact.Save()Dim cCartAs Cart =New Cart(CartID) cCart.Status ="PEND" cCart.Save() Session.Add("CartID", Outman.ShoppingCart.Create) CartDisplay.Visible =False CheckoutComplete.Visible =True End Sub

|||

Hello,

what/where is the function GetColumnValue, SetColumnValue ?

Public Property OrderIDAs Guid
152 Get
153 Dim resultAs Object =Me.GetColumnValue("OrderID")
154 Dim oOutAs Guid=Guid.Empty155 Try
156 oOut=New Guid(result.ToString())
157 Catch
158 End Try
159 Return oOut
160
161 End Get
162 Set
163 Me.MarkDirty()
164 Me.SetColumnValue("OrderID", Value)
165 End Set
166 End Property

|||

Did you ever get this worked out?

I am having the same issue! I must have overlooked this post before I made mine a few minutes ago.

Did you try manually entering data through the table in SSMS? I found it had nothing to do with classes or page code. I cannot even enter it manually!

PLease advise if you found an answer.

Thanks!