Problem using ROWLOCK with INSERT/UPDATE.

(SQL Server 2005)
I am having a little trouble that I hope someone can clarify for me.
I have a session running with implicit_transactions turned on, and another
session trying to access a table undergoing some changes in that first
session. I had thought that I could sort of isolate the two sessions from
one another using ROWLOCK, but it seems as if (at least in my example) that
the ROWLOCK always gets promoted to a table lock.
Please see the attached DDL/DML for an example of what I'm trying to
accomplish (note that I ran this in [tempdb]).
Is there any way for me to have the second session successfully report the
counts of the *committed* rows without being blocked on the first session if
that first session is within a transaction scope (with, presumably, a
default isolation level of read-committed)?
Thanks for any help that anyone can provide!
Kind regards,
John Peterson
On Mon, 4 Jun 2007 15:20:15 -0700, John Peterson wrote:

>(SQL Server 2005)
>I am having a little trouble that I hope someone can clarify for me.
>I have a session running with implicit_transactions turned on, and another
>session trying to access a table undergoing some changes in that first
>session. I had thought that I could sort of isolate the two sessions from
>one another using ROWLOCK, but it seems as if (at least in my example) that
>the ROWLOCK always gets promoted to a table lock.
>Please see the attached DDL/DML for an example of what I'm trying to
>accomplish (note that I ran this in [tempdb]).
>Is there any way for me to have the second session successfully report the
>counts of the *committed* rows without being blocked on the first session if
>that first session is within a transaction scope (with, presumably, a
>default isolation level of read-committed)?
>Thanks for any help that anyone can provide!
>Kind regards,
>John Peterson
>
Hi John,
I didn't read the complete code, nor did I try to run it, but based on
your narrative I think you need to add a READPAST locking hint.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||I don't think specifying ROWLOCK hint prevents the engine from escalating
locks as it sees appropriate, be it page, extent, etc.
Off the top of my head I can't think of a way to interact with the committed
rows directly from a separate transaction.
TheSQLGuru
President
Indicium Resources, Inc.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23AIcMavpHHA.208@.TK2MSFTNGP05.phx.gbl...
> (SQL Server 2005)
> I am having a little trouble that I hope someone can clarify for me.
> I have a session running with implicit_transactions turned on, and another
> session trying to access a table undergoing some changes in that first
> session. I had thought that I could sort of isolate the two sessions from
> one another using ROWLOCK, but it seems as if (at least in my example)
> that the ROWLOCK always gets promoted to a table lock.
> Please see the attached DDL/DML for an example of what I'm trying to
> accomplish (note that I ran this in [tempdb]).
> Is there any way for me to have the second session successfully report the
> counts of the *committed* rows without being blocked on the first session
> if that first session is within a transaction scope (with, presumably, a
> default isolation level of read-committed)?
> Thanks for any help that anyone can provide!
> Kind regards,
> John Peterson
>
>
|||Hello Hugo!
The READPAST hint appears to do exactly what I want! Thanks for the tip.
Is this a SQL Server 2005 feature, or was this in SQL Server 2000, too?
(I'm too lazy to bring up a 2000 BOL. ;-)
Thanks again!
John Peterson
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:f6696390qvpj9qtqcv89tovhp0pjdvsfg2@.4ax.com...
> On Mon, 4 Jun 2007 15:20:15 -0700, John Peterson wrote:
>
> Hi John,
> I didn't read the complete code, nor did I try to run it, but based on
> your narrative I think you need to add a READPAST locking hint.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Tue, 5 Jun 2007 07:02:31 -0700, John Peterson wrote:

>Hello Hugo!
>The READPAST hint appears to do exactly what I want! Thanks for the tip.
>Is this a SQL Server 2005 feature, or was this in SQL Server 2000, too?
Hi John,
Typing a message and going back to check for an answer is less effort
than double-clicking an icon on your desktop? <shakes head>
READPAST is available in SQL Server 2000 as well.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:catb6317978pkrfm33rimiofeqrguu524j@.4ax.com...
> On Tue, 5 Jun 2007 07:02:31 -0700, John Peterson wrote:
>
> Hi John,
> Typing a message and going back to check for an answer is less effort
> than double-clicking an icon on your desktop? <shakes head>
Heh! Well, as it turns out, when I sent the previous reply, it wasn't from
a machine that had access to SQL Server at the time. :-)

> READPAST is available in SQL Server 2000 as well.
Ah, interesting. I didn't realize that -- good to know.
Thanks again for your help!

> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Problem using dates in XMLtoFLFF Transformation

I am having a bit of trouble transforming a XML (xs:date) type to the DBDATE in my FLFF. I believe the problem is because the SSIS DF (Metadeta) converts xs:date to DT_DBTIMESTAMP which becomes 01/01/2005 00:00:00 -- appending the time. So when I try to transform in my fixed length flat file, it throws a truncation error when the length is set to 10. (which is the length of date)

Is it possible to (A) use some other xs format that changes DF Metadata to only use date and not DT_DBTIMESTAMP, or (B) is there a field type in the destination FLFF Connection that will allow a length of 10 without throwing a truncation error?The only output column date type supported by the XmlSrc is DT_DBTIMESTAMP. You could try changing the output column type to a string in the advanced editor, and set it to the length you want, and set the truncation disposition to ignore truncations.

Or, You could place a Data Conversion transform on the output of the XmlSrc, and either convert the DT_DBTIMESTAMP column to DT_DBDATE (which does not include the time), or actually convert the DT_DBTIMESTAMP to a string of the desired length and set the truncation disposition to ignore truncations.

Let me know if this solves your problem, or you need a different solution.

Mark|||Thanks Mark the feedback. Yep, I ended up implementing the first solution and works ok. The second solution is a good one as well but seems a bit too much, we will just enforce no date_type validations for now. Do you (or anyone else) know if this ability will ever be natively incorporated into SSIS?|||Do you mean, will it be possible to map the xml input to a date type other than just DT_DBTIMESTAMP directly in the XmlSrc adapter? If so, I know of no plans for that, but it would be great if you could open a DCR bug to that effect.

problem updating text datatype

Hi All:
I am having trouble updating a TEXT column (I know that TEXT is a blob but I
am dealing with very large amounts of data that VARCHAR can not hold!) What
I want to happen is all rows from TableA that meet a certain criteria to be
inserted into TableB but if it already exists in TableB then update it
instead - this sounds simple enough and is working fine except that one of
the fields is TEXT datatype. So when I try to update I get an error "The
text, ntext, and image datatypes are invalid in this subquery or aggregate
expression." (Apparently you can not do an update select with a text
datatype) I did a search and found that I need to be using the UPDATETEXT
function - however I am not quite sure how this works. Any suggestions
would be greatly appreciated!
TableA
ID INT
MenuID INT
Content TEXT
TableB
ID INT
MenuID INT
Content TEXT
Thanks!Nancy,
Without seeing the query, it's impossible to know what the
problem is. Post the query that is causing the error, at
least, if you want a more specific answer.
In any case, it is certainly possible to update [text]
with an UPDATE statement. In general, you may want
something like this:
update TableB set
Content = TableA.Content
from TableA join TableB
on TableA.ID = TableB.ID
and TableA.MenuID = TableB.MenuID
insert into TableB(ID, MenuID, Content)
select ID, MenuID, Content
from TableA
where not exists (
select * from TableB as B2
where B2.ID = TableA.ID
and B2.MenuID = TableA.MenuID
)
Steve Kass
Drew University
Nancy Shelley wrote:

> Hi All:
> I am having trouble updating a TEXT column (I know that TEXT is a blob but
I
> am dealing with very large amounts of data that VARCHAR can not hold!) Wha
t
> I want to happen is all rows from TableA that meet a certain criteria to
be
> inserted into TableB but if it already exists in TableB then update it
> instead - this sounds simple enough and is working fine except that one of
> the fields is TEXT datatype. So when I try to update I get an error "The
> text, ntext, and image datatypes are invalid in this subquery or aggregate
> expression." (Apparently you can not do an update select with a text
> datatype) I did a search and found that I need to be using the UPDATETEXT
> function - however I am not quite sure how this works. Any suggestions
> would be greatly appreciated!
>
> TableA
> ID INT
> MenuID INT
> Content TEXT
> TableB
> ID INT
> MenuID INT
> Content TEXT
> Thanks!
>|||Hi Steve:
Thanks for the quick response. Supplying BLOB columns with text or image
data that's less than or equal to 8000 bytes in size is as straightforward
as updating any other type of column (you can use insert/update) but when
values are larger than 8000 you need to use updatetext or writetext. This
is what I was having trouble with - I know the syntax for updating my table
the regular way I just wasn't sure about using updatetext or writetext.
I ended up solving my own problem - my pointer was invalid. I was getting
an invalid pointer reference to the text column.
Thanks for your help!
Nancy
"Steve Kass" <skass@.drew.edu> wrote in message
news:OqsmT2jWFHA.3488@.tk2msftngp13.phx.gbl...
> Nancy,
> Without seeing the query, it's impossible to know what the
> problem is. Post the query that is causing the error, at
> least, if you want a more specific answer.
> In any case, it is certainly possible to update [text]
> with an UPDATE statement. In general, you may want
> something like this:
>
> update TableB set
> Content = TableA.Content
> from TableA join TableB
> on TableA.ID = TableB.ID
> and TableA.MenuID = TableB.MenuID
> insert into TableB(ID, MenuID, Content)
> select ID, MenuID, Content
> from TableA
> where not exists (
> select * from TableB as B2
> where B2.ID = TableA.ID
> and B2.MenuID = TableA.MenuID
> )
> Steve Kass
> Drew University
> Nancy Shelley wrote:
>|||Nancy,
I believe the syntax I suggested works for any length [text] or
[image] column.
You can also update or insert data into a text or image column if you
provide
it as a literal string or binary value. The 8000 byte limit does not
apply to
literals.
SK
Nancy Shelley wrote:

>Hi Steve:
>Thanks for the quick response. Supplying BLOB columns with text or image
>data that's less than or equal to 8000 bytes in size is as straightforward
>as updating any other type of column (you can use insert/update) but when
>values are larger than 8000 you need to use updatetext or writetext. This
>is what I was having trouble with - I know the syntax for updating my tabl
e
>the regular way I just wasn't sure about using updatetext or writetext.
>I ended up solving my own problem - my pointer was invalid. I was getting
>an invalid pointer reference to the text column.
>Thanks for your help!
>Nancy
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OqsmT2jWFHA.3488@.tk2msftngp13.phx.gbl...
>
>
>