how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
misoMiso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
miso
Miso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>
|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how to create row locks

Hello,
We would like to use the following concept :
There are two processes.
First one creates the transacton, makes update on one row via the primary
key
(by the command UPDATE not by UPDATE CURSOR).
We need to achieve
that in the duration this transaction
there is not possible to execute UPDATE for this row by the second process,
but we must have the possibility to make UPDATE on the another rows.
Our problem is
that by using the above construction
the whole table is locked for the second process.
Thanks
misoMiso
By deafult SQL Server creates a lock on row level.
BEGIN TRAN
UPDATE Table SET col1 =@.par1,col2=@.par2 WHERE PK=something
COMMIT TRAN
"Miso" <valent@.spirit.sk> wrote in message
news:eQl5Hw08GHA.2128@.TK2MSFTNGP05.phx.gbl...
> Hello,
> We would like to use the following concept :
> There are two processes.
> First one creates the transacton, makes update on one row via the primary
> key
> (by the command UPDATE not by UPDATE CURSOR).
> We need to achieve
> that in the duration this transaction
> there is not possible to execute UPDATE for this row by the second
> process,
> but we must have the possibility to make UPDATE on the another rows.
> Our problem is
> that by using the above construction
> the whole table is locked for the second process.
>
> Thanks
> miso
>|||Miso,
the update on the single row within the transaction will have an exclusive
lock on that row, but as it's a single row, I don't see how lock escalation
has increased it to a table lock (an intent exclusive is expected). How many
rows are in the table? Are there other transactions operating here? What is
returned if you run sp_lock (for the relevant objid)? Also, what happens if
you use the ROWLOCK hint?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How to create a trigger such that it can delete the rows whenever any other application such as

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

Hi,

actually Select has no trigger action, only DML operations are capable. You could use a stored procedure to read the rows rather than using a select statement (don′t know if BizTalk is able to do this through stored procedures).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot Jens.

How to create a trigger such that it can delete the rows whenever any other application such as

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

Hi,

actually Select has no trigger action, only DML operations are capable. You could use a stored procedure to read the rows rather than using a select statement (don′t know if BizTalk is able to do this through stored procedures).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to Create a Sequence Generator number in SSIS

Hi,

I got 5000 rows in source and when i am sending the data to destination it has to create a sequence generator number for each row.

Can any one help me which transformation do i need to take for doing this in SSIS.

Row Number Transformation
(http://www.sqlis.com/default.aspx?93)|||

Alternatively you can also look the following articles

Surrogate key generation in SSIS @. http://www.sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx

Generating Surrogate Keys @. http://www.sqlis.com/default.aspx?37

Thanks,
Loonysan
http://mystutter.blogspot.com/

How to create a row that has size greater than 8060

Hi all
When I updated database I got the error message that size of row can't
greater than 8060. Howerver, I think there must be a solution because 8060
is too small. Help me!
ThanksHi
You can ignore the error message and the table will be created although if
the data inserted into the table exceeds the size then the insert will fail.
A table with a size greater than 8060 is a very wide table and you may want
to think about re-designing the database so that you use several tables. You
can create a view that joins these tables if you always wish to avoid joinin
g
the tables "manually".
John
"ghostnguyen" wrote:

> Hi all
> When I updated database I got the error message that size of row can't
> greater than 8060. Howerver, I think there must be a solution because 8060
> is too small. Help me!
> Thanks
>
>|||And all this really, really, really needs to be in a single table? Can you
post some DDL, please?
ML|||Size of a Single row cannot be more than 8060 bytes
Madhivanan|||Create two tables related by a common primary key.
David Portas
SQL Server MVP
--|||Short of redesigning your table (using two tables, for example, as has been
suggested), you may want to consider using [text] instead of long [varchar]
columns. They will be harder to work with, but they are one way to get
around this limitation.
Steve Kass
Drew University
ghostnguyen wrote:

>Hi all
>When I updated database I got the error message that size of row can't
>greater than 8060. Howerver, I think there must be a solution because 8060
>is too small. Help me!
>Thanks
>
>|||If 8060 is too small, seriously look at your database design, or use
datatypes like ntext if you need large text storage.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:DB030F6E-7BA3-4187-817E-EA3FF8088575@.microsoft.com...
> Hi
> You can ignore the error message and the table will be created although if
> the data inserted into the table exceeds the size then the insert will
> fail.
> A table with a size greater than 8060 is a very wide table and you may
> want
> to think about re-designing the database so that you use several tables.
> You
> can create a view that joins these tables if you always wish to avoid
> joining
> the tables "manually".
> John
> "ghostnguyen" wrote:
>

How to create a row that calucates percentage in a Tabular report, RS 2005?

Hello,

I have a report in a Tabular format and I am wondering if anyone knows how I can create a row that generates a percentage number in RS 2005. Sample data is highlited here in yellow?

Thanks,

School

Dept

Level

Category

Total Courses

Enrollment

Grades Awarded

Grades Attempted

Average GPA

A

A-

B+

B

B-

BUE

MGMT

300

Classroom Instruction

30

844

813

817

3.213

140

166

164

138

109

17%

20%

20%

17%

13%

400

Classroom Instruction

32

751

734

740

3.417

227

173

135

83

59

31%

24%

18%

11%

8%

Does the number under each grade come from a different database field? Assuming for the "A" column, the number is from FieldA, you can calculate the percentage (for number of "A"s for this level from total number of "A"s) using =Fields!FieldA.Value/Sum(Fields!FieldA.Value). For the percentage of the number of "A"s from total number for all grades for a specific level, use =Fields!FieldA.Value/(Fields!FieldA.Value+Fields!FieldA-.Value+Fields!FieldB+.Value+Fields!FieldB.Value+Fields!FieldB-.Value).

How to count the tasks inside a package programmatically?

Hi there,

I'm trying to keep track of the ETL process inserting/updating a row in one table for each package that finish in my ETL process when executing. So far, I created a Script task that increments by one a variable (counter) and then open a connection to my database an insert/update my table. What I want to see is Step 1/30, Step 2/30 and so on. Right know I can display Step 1, Step 2 but how can I get the overall number of tasks within a package?

Thanks in advance for any help you can give me.

Sincerely,
Ricardo

A script task cannot interrogate the package in which it resides to find out that information. Unlike in DTS, tasks do not receive a pointer to package itself. So you can't do what you used to do in DTS i.e. Get a reference to DTSGlobalVariables.Parent and iterate over the object model.

This is one of the big differences in SSIS.

Some extra reading:

But it used to work in DTS (1) - Modifing a Package in script

(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/SSIS_3A00_-But-it-used-to-work-in-DTS-_2800_1_2900_-_2D00_-Modifing-a-Package-in-script.aspx)

-Jamie

|||

You could handle the counting yourself - store one variable with the total count of steps, and another variable that you increment with script tasks as the package executes.

It's a little more work, but it should accomplish what you want.

|||

jwelch wrote:

You could handle the counting yourself - store one variable with the total count of steps, and another variable that you increment with script tasks as the package executes.

It's a little more work, but it should accomplish what you want.

Good idea. Thought you'll probably want to put the script task that increments the value into the OnPostExecute eventhandler scoped to the package.

-Jamie

|||Well, to get the count what I ended up doing is to call the package itself from a Script Task so I can do package.Executables.Count in order to get the total number of tasks inside a package.

I still used the Post-Execute event to increment my counter and to insert/update a row in my audit table.

Thank you guys for the suggestions.

Ricardo|||

bachocha wrote:

Well, to get the count what I ended up doing is to call the package itself from a Script Task so I can do package.Executables.Count in order to get the total number of tasks inside a package.

I still used the Post-Execute event to increment my counter and to insert/update a row in my audit table.

Thank you guys for the suggestions.

Ricardo

So you're building a package solely for the purpose of calling another package? You could of course write a .Net app to do this for you - I find it interesting that you chose not to and instead use SSIS itself as the host for your .Net code.

Anyway...I'm waffling. Glad you got what you wanted.

-Jamie

|||Actually, I think he's calling a new instance of the package from inside the same package. Seems like a lot of overhead, but to each his own.|||Yes, John is right. I'm loading the same package from inside my package using a Script Task. I was thinking to create a new package which only loads the package I want to get the number of tasks and then store this count into a variable.

I don't think I could use a .NET app, I mean I can build it but probably I will not be able to deploy it in production. I can only move dtsx packages. I really would like to implement the best solution for this problem. So if you think that loading the same pacakge from itself is an overhead, what would be the right way?

Thanks!

Ricardo|||

bachocha wrote:

Yes, John is right. I'm loading the same package from inside my package using a Script Task. I was thinking to create a new package which only loads the package I want to get the number of tasks and then store this count into a variable.

I don't think I could use a .NET app, I mean I can build it but probably I will not be able to deploy it in production. I can only move dtsx packages. I really would like to implement the best solution for this problem. So if you think that loading the same pacakge from itself is an overhead, what would be the right way?

Thanks!

Ricardo

Its not an overhead if it doesn't cause any problems. It seems like a strange thing to do but if it works for you - go for it.

-Jamie

|||

bachocha wrote:

So if you think that loading the same pacakge from itself is an overhead, what would be the right way?

"Right way" - I don't know. My comment about overhead was that you are essentially loading the package twice - once to do the real work, once to count the executables. Not a big deal if you are doing it in one or two packages. Across a few dozen, it might have more impact. However, just loading the package doesn't have the same overhead as executing it, so maybe it's not a problem. Like Jamie said, if it works for you, go with it.

How To Count how many ROWS are returned in SQL Data Reader

My code keeps reading for another row when none are present....

There's are only unique records in the dB so far, but dupes are possible

While reader.has rows

reader.read

if value not db.null

get values

end if

end while

The program gets the row and reads it. After processing the values, it goes to thru the While and reads the next row (none), it then crashes on if value not db.null... because the value is null or no record. The dB has Values Required option on - no empty fields allowed.

Is there a way to count the ROWS returned?

Thanks.

That depends on your coding language, which one are you using ? For example the .NET Framework has the read() method which returns false if no record can be read anymore, leading to the following code:

while dr.Read()
{}

and to the fact that you won′t get an error if no rows is read anymore.

In my good old ASP ages there was something similar like rs.EOF or something, I guess there has to be be something in your coding language too.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I'm using VB.

But I'm using the WHILE there are ROWS, READ, so it shouldn't read if no more rows are present. Only ONE ROW is returned, so the WHILE statement should return FALSE on the second run of the loop and exit the LOOP because no more ROWS are present.

Moverover, it continued to READ, so perhaps the framework (NET) detected another row (empty) and reads it (while rows are present, read). Howbeit, this ROW is empty so an error is generated at the if not null.db line - but even so, it should NOT generate an error because NOT db.Null should detect that there is a Null Value and return FALSE and exit the loop.