how to change the default table locking type ?

Hi guys,
Is there anyway to change the default recode locking type (Table
Locking) to row locking !! ?
Thanks in help
-SniperSniper
As far as I know SQL Server begins with a low type (row locking) and
increase if it needs.
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
> Hi guys,
> Is there anyway to change the default recode locking type (Table
> Locking) to row locking !! ?
> Thanks in help
> -Sniper|||Using the sp_indexoption stored procedure you can play with the options:
AllowRowLocks, AllowPageLocks, DisAllowRowLocks, DisAllowPageLocks for each
index.
By default, SQL Server considers the locking granularity automatically, and
the default choices it makes are usually best.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
> Hi guys,
> Is there anyway to change the default recode locking type (Table
> Locking) to row locking !! ?
> Thanks in help
> -Sniper|||In addition to the other posts, supporting the query with good and usable in
dexes reduces the risk
of getting table locks.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
> Hi guys,
> Is there anyway to change the default recode locking type (Table
> Locking) to row locking !! ?
> Thanks in help
> -Sniper|||Hi Tibor,
Thanks for your reply. it's like this, When I do a start transaction and do
a row modification, it locks the hole table, it there anyway to lock only th
e
modified row ?
Thanks alot for u r time and response,
-Aruna
"Tibor Karaszi" wrote:

> In addition to the other posts, supporting the query with good and usable
indexes reduces the risk
> of getting table locks.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sniper" <kakopappa@.hotmail.com> wrote in message
> news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
>
>|||Not directly. As I said, make sure that you support the WHERE clauses you us
e in the UPDATE with
proper indexes and you are most likely to end up with row locks. How did you
determine that the
whole table is locked?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:ABBBC0D2-0273-435A-88FA-135E1F554DB4@.microsoft.com...
> Hi Tibor,
> Thanks for your reply. it's like this, When I do a start transaction and d
o
> a row modification, it locks the hole table, it there anyway to lock only
the
> modified row ?
> Thanks alot for u r time and response,
> -Aruna
>
> "Tibor Karaszi" wrote:
>|||When I open the table from the Enterprise Manager it doesn't show the table
data. or when I do a SELECT * FROM table it's waitting till the table lock t
o
be released.
thanks,
Sniper
"Tibor Karaszi" wrote:

> Not directly. As I said, make sure that you support the WHERE clauses you
use in the UPDATE with
> proper indexes and you are most likely to end up with row locks. How did y
ou determine that the
> whole table is locked?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sniper" <kakopappa@.hotmail.com> wrote in message
> news:ABBBC0D2-0273-435A-88FA-135E1F554DB4@.microsoft.com...
>
>|||Use sp_who, sp_who2, sp_lock etc to determine what types of locks the connec
tion which has modified
the data has on the table. Also, you still haven't provided us with the tabl
e structure and what
indexes you have on the table and what your UPDATE statement look like.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:5923EEE1-890F-454C-B694-7E9279D605F3@.microsoft.com...
> When I open the table from the Enterprise Manager it doesn't show the tab
le
> data. or when I do a SELECT * FROM table it's waitting till the table lock
to
> be released.
> thanks,
> Sniper
> "Tibor Karaszi" wrote:
>|||Also, if you have updated some row, and haven't committed that transaction,
any process which tries
to read *all* rows will be blocked.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:5923EEE1-890F-454C-B694-7E9279D605F3@.microsoft.com...
> When I open the table from the Enterprise Manager it doesn't show the tab
le
> data. or when I do a SELECT * FROM table it's waitting till the table lock
to
> be released.
> thanks,
> Sniper
> "Tibor Karaszi" wrote:
>

How to change column length on published DB - Merge Replication

Guys,
I have Merge Replication on SQL SERVER 2000.
Nilay,
Directly this can't be done. Indirectly it can, but not nicely! You could add a new column with the new datatype (sp_repladdcolumn), do an update on the table to populate the column, then drop the column (sp_repldropcolumn). Do this again to create the co
lumn having the same original name. Hopefully this functionality will be directly available in SQL Server 2005.
HTH,
Paul Ibison
|||The sp_repladdcolumn proc has a @.schema_change_script parameter where you can package a update statement to simplifiy the operation of this.
ie
update table TableYouAreModifiying set newColumn=oldColumn
Here is a code sample of how to do this using SQL-DMO
Option explicit
dim objServer, objReplication, objReplicationDatabases,_ objReplicationDatabase, objReplicationTables,_ objReplicationTable
set objServer=CreateObject("SQLDMO.SQLServer")
objServer.Connect "hilary2kp" , "sa","se1cure#"
set objReplication=objServer.Replication
set objReplicationDatabases=objReplication._
ReplicationDatabases
set objReplicationDatabase=objReplicationDatabases("pu bs")
set objReplicationTables=objReplicationDatabase._
ReplicationTables
for each objReplicationTable in objReplicationTables
if objReplicationTable.Name ="authors" then
objReplicationTable.ReplicationAddColumn _ "temp","char(100)","pubs",_
"c:\temp\updatetemp.sql"
objReplicationTable.ReplicationDropColumn _
"au_lname"
objReplicationTable.ReplicationAddColumn _
"au_lname","char(100)","pubs",_
"c:\temp\updatetemp2.sql"
objReplicationTable.ReplicationDropColumn "temp"
end if
next
set objReplicationTable=Nothing
set objReplicationTables=Nothing
set objReplicationDatabase=Nothing
set objReplicationDatabases=Nothing
set objReplication=Nothing
set objServer=Nothing
Here are my update scripts
updatetetmp.sql
update authors set temp=au_lname
updatetemp2.sql
update authors set au_lname=temp
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks Guys,
This helps.

How To Change All Records in a Single Field?

Hey guys,
I am in a bit of a pickle. I have to do a software training today,
and my training database is hosed. I have a production database, but
I do not want the users to actually see a customer=92s live data on the
screen. I want to make a global alternation to all records on a
single field such as change everyone=92s account number to a specific
value.
Is there a way to do this in SQL 2005?
Thanks!Create a view and show them the results of the view.
CREATE VIEW dbo.foo
AS
SELECT col1, col2, AccountNumber = 'foo', col3, ...
FROM dbo.Original_Table;
<alvinstraight38@.hotmail.com> wrote in message
news:d2e508c2-4c8e-4627-90dc-5b7e38dabb91@.e39g2000hsf.googlegroups.com...
Hey guys,
I am in a bit of a pickle. I have to do a software training today,
and my training database is hosed. I have a production database, but
I do not want the users to actually see a customer?s live data on the
screen. I want to make a global alternation to all records on a
single field such as change everyone?s account number to a specific
value.
Is there a way to do this in SQL 2005?
Thanks!|||UPDATE ThatTable
SET SomeColumn = 'NewValue'
Since there is no WHERE clause to limit the effect, every row in
ThatTable will be updated.
The problem with changing a customer's account number that way is that
it is likely to violate a UNIQUE constraint, as well as appearing in
many tables. It is likely to be used for JOINs.
Roy Harvey
Beacon Falls, CT
On Tue, 29 Apr 2008 06:18:42 -0700 (PDT),
"alvinstraight38@.hotmail.com" <alvinstraight38@.hotmail.com> wrote:
>Hey guys,
>I am in a bit of a pickle. I have to do a software training today,
>and my training database is hosed. I have a production database, but
>I do not want the users to actually see a customer?s live data on the
>screen. I want to make a global alternation to all records on a
>single field such as change everyone?s account number to a specific
>value.
>Is there a way to do this in SQL 2005?
>Thanks!

HOW to Cast an AS ConnectionManager into a SriptTask?

In fact, we could use AMO in the Sript Task, just by include the AMO.dll, many guys have talked about it on the forum.

But now, O my god, I met a big problem.

I declare a AS connectionManager in the SSIS package. In the Script Task I can't use it.

If this is a OEL DB ConnectionManager and connect to SQL Sever, I know I could write this inside the Sript Task:

Public myKPIConnection As SqlClient.SqlConnection

myKPIConnection = _
DirectCast(Dts.Connections("CYF.KPIOperation").AcquireConnection(Dts.Transaction), _
SqlClient.SqlConnection)

Then I could use myKPIConnection inside the Task.

But, How to do the similar thing to a AS ConnectionManager? I need to DirectCast the AS connectionManager to What?

By the way, the only thing I want to do is to Start an AS transaction inside the Sript Task, and let a Process Task outside to be enlisted in the trransaction. So I need to use the same AS connectionmanager.

Thanks.

Your example is not correct. You cannot use an OLE-DB connection for SQL and utilise it inside a Script Task. You have to be using the ADO.NET connection, such as with sub-type of SqlClient.SqlConnection, which means you can cast the connection manager AcquireConnection to that type.

You have the same issue here, the AS connection is the MSOLAP90 OLE-DB provider, which cannot be used in .Net directly, as this a native OLE-DB provider.

The best thing to do woudl be to read the ConnectionString property from the connection and use that against a Microsoft.AnalysisServices.Server class, calling the Connect method. That is no doubt what MS have done when using AS connections in managed code.

|||

Thanks, DarrenSQLIS.

I know I could do this, but I want to bound another Process Task in the package into the same stransaction of the Script Task. I mean, I could use that against a Microsoft.AnalysisServices.Server class, calling the Connect method, and begin an AS transaction inside the Script Task, then I want to enlist the Process Task into this transaction.

How could I do this?