insert using a VIEW stucks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

insert using a VIEW stucks

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-20 : 05:38:38

i have an insert statment where i insert a row using a view,
in the view there is a select on some table (each one of a diffrent month), and in each month there is a constraint on the date column -where only a data in the range of a given month can be inserted.
sometimes it just stuck!
and only inserting an empty row using the Stored Procedure from the query analayzer release the stucked DB.
this is the code :


INSERT vw_PartitionDataReports2007 WITH(ROWLOCK)
		(Company,Application,MID,Del,Stamp)
	VALUES
		(@Company,@Application,@MID,@Stamp)

thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-20 : 05:52:59

why are you using rowlock??

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-20 : 05:59:12

beacuse when i insert i dont want to lock all the table but just 1 row for insert
if i dont use it it wil lock all the table, and a select cant be done at the same time

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-20 : 07:00:18

emm... huh? since when does an insert lock the whole table???

and BTW you values is missing a value


INSERT vw_PartitionDataReports2007 WITH(ROWLOCK)
		(Company,  Application,  MID,  Del,             Stamp)
	VALUES
		(@Company, @Application, @MID, WhatGoesHere, @Stamp)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-20 : 07:04:57

ok maybe i misse something when i copied the code to here
but still what can cause this problem>?

thbaks
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-20 : 07:31:05

Sounds like you are trying to use a partitioned view but it's not partitioned.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 07:33:24

Double check requirements for horizontal partitions here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Requirements%20for%20Horizontal%20Partitions

Kristen

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-20 : 07:53:49

the thing is that it works
and sometime (once in few 10000 or m,ore it stocks)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 07:56:18

Maybe your constraints do not seamlessly cover the total range of data?

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2007-06-20 : 08:18:54

Kristen what do u mean by that?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-20 : 08:27:08

maybe you should show us the create statement for your view

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-20 : 08:28:58

quote:
Originally posted by pelegk2

the thing is that it works
and sometime (once in few 10000 or m,ore it stocks)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

There is an issue that if the database uses simple recovery model and you insert a lot of rows into a table it will sometimes take a long time to do an insert. My guess is it's expanding and clearing the log at the same time.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 10:00:54

"Kristen what do u mean by that?"

As per the link I posted above:

"[u]INSERT statements must supply a value that satisfies the logic of the CHECK constraint defined on the partitioning column for one of the member tables.[/i]"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources