set affinity for SqlExpress: implement

Showing posts with label implement. Show all posts

Friday, March 30, 2012

how to simulate a block

could someone provide some simple code to simulate a block. I want to test
a blocking script I want to implement on prod.
TIA
C
Execute this on one connection:
create table ##foo (SomeColumn int)
go
begin transaction
insert into ##foo (SomeColumn) values (1)
waitfor delay '0:00:40' /* hold the transaction open for 40 seconds */
go
commit transaction
/*****************************/
Execute this on another connection:
select * from ##foo
Keith
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:%23PbFfLMeFHA.1920@.tk2msftngp13.phx.gbl...
> could someone provide some simple code to simulate a block. I want to
> test a blocking script I want to implement on prod.
> TIA
> C
>
|||Try,
-- conn 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
-- conn 2
use northwind
go
select * from orders
Connection 2 will be blocked until you commit or rollback the transaction in
connection 1.
AMB
"CD" wrote:

> could someone provide some simple code to simulate a block. I want to test
> a blocking script I want to implement on prod.
> TIA
> C
>
>
|||Look at tablockx holdlock keywords
these will hold locks on tables with in a block of code...
i.e.
create procedure ...
select @.nextid = id from foo with (tablockx holdlock)
/* tablockx forces an exclusive table lock
tablock to do a non-exclusive lock
holdlock tells SQL Server to hold locks for duration of the transaction */
update foo set [id] = [id]+ @.block
return @.nextid
...
this will prevent n users from getting the same id
GO
"CD" wrote:

> could someone provide some simple code to simulate a block. I want to test
> a blocking script I want to implement on prod.
> TIA
> C
>
>

how to simulate a block

could someone provide some simple code to simulate a block. I want to test
a blocking script I want to implement on prod.
TIA
CExecute this on one connection:
create table ##foo (SomeColumn int)
go
begin transaction
insert into ##foo (SomeColumn) values (1)
waitfor delay '0:00:40' /* hold the transaction open for 40 seconds */
go
commit transaction
/*****************************/
Execute this on another connection:
select * from ##foo
Keith
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:%23PbFfLMeFHA.1920@.tk2msftngp13.phx.gbl...
> could someone provide some simple code to simulate a block. I want to
> test a blocking script I want to implement on prod.
> TIA
> C
>|||Try,
-- conn 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
-- conn 2
use northwind
go
select * from orders
Connection 2 will be blocked until you commit or rollback the transaction in
connection 1.
AMB
"CD" wrote:

> could someone provide some simple code to simulate a block. I want to tes
t
> a blocking script I want to implement on prod.
> TIA
> C
>
>|||Look at tablockx holdlock keywords
these will hold locks on tables with in a block of code...
i.e.
create procedure ...
select @.nextid = id from foo with (tablockx holdlock)
/* tablockx forces an exclusive table lock
tablock to do a non-exclusive lock
holdlock tells SQL Server to hold locks for duration of the transaction */
update foo set [id] = [id]+ @.block
return @.nextid
...
this will prevent n users from getting the same id
GO
"CD" wrote:

> could someone provide some simple code to simulate a block. I want to tes
t
> a blocking script I want to implement on prod.
> TIA
> C
>
>sql

how to simulate a block

could someone provide some simple code to simulate a block. I want to test
a blocking script I want to implement on prod.
TIA
CExecute this on one connection:
create table ##foo (SomeColumn int)
go
begin transaction
insert into ##foo (SomeColumn) values (1)
waitfor delay '0:00:40' /* hold the transaction open for 40 seconds */
go
commit transaction
/*****************************/
Execute this on another connection:
select * from ##foo
Keith
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:%23PbFfLMeFHA.1920@.tk2msftngp13.phx.gbl...
> could someone provide some simple code to simulate a block. I want to
> test a blocking script I want to implement on prod.
> TIA
> C
>|||Try,
-- conn 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
-- conn 2
use northwind
go
select * from orders
Connection 2 will be blocked until you commit or rollback the transaction in
connection 1.
AMB
"CD" wrote:
> could someone provide some simple code to simulate a block. I want to test
> a blocking script I want to implement on prod.
> TIA
> C
>
>|||Look at tablockx holdlock keywords
these will hold locks on tables with in a block of code...
i.e.
create procedure ...
select @.nextid = id from foo with (tablockx holdlock)
/* tablockx forces an exclusive table lock
tablock to do a non-exclusive lock
holdlock tells SQL Server to hold locks for duration of the transaction */
update foo set [id] = [id]+ @.block
return @.nextid
...
this will prevent n users from getting the same id
GO
"CD" wrote:
> could someone provide some simple code to simulate a block. I want to test
> a blocking script I want to implement on prod.
> TIA
> C
>
>

Monday, March 12, 2012

How to set the default value

I want to use SQL to set the default of a field.
How can I implement with DDL?ad
CREATE TABLE #Test
(
col1 INT NOT NULL PRIMARY KEY,
col2 DATETIME DEAFULT GETDATE()
)
INSERT INTO #Test (col1) VALUES (1)
GO
SELECT * FROM #Test
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
> I want to use SQL to set the default of a field.
> How can I implement with DDL?
>|||CREATE TABLE Testdata
(
TestIDCOlumn INT,
TestColumn varchar(10) DEFAULT 'Test'
)
Insert into Testdata(TestIDColumn) Values (1)
Select * from Testdata
Drop Table Testdata
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
>I want to use SQL to set the default of a field.
> How can I implement with DDL?
>|||Thank,
If the TestColumn have create before, but did not set default value yet.
Can I modify the structrue now , set the default value to that column?
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> ¼¶¼g©ó¶l
¥ó·s»D:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
> CREATE TABLE Testdata
> (
> TestIDCOlumn INT,
> TestColumn varchar(10) DEFAULT 'Test'
> )
> Insert into Testdata(TestIDColumn) Values (1)
> Select * from Testdata
> Drop Table Testdata
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
> news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
> >I want to use SQL to set the default of a field.
> > How can I implement with DDL?
> >
> >
>|||alter table TestData
add constraint DF_TestDate_TestColumn
default ('Test') for TestColumn
HTH,
Gert-Jan
ad wrote:
> Thank,
> If the TestColumn have create before, but did not set default value yet.
> Can I modify the structrue now , set the default value to that column?
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> ¼¶¼g©ó¶l
> ¥ó·s»D:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
> > CREATE TABLE Testdata
> > (
> > TestIDCOlumn INT,
> > TestColumn varchar(10) DEFAULT 'Test'
> > )
> >
> > Insert into Testdata(TestIDColumn) Values (1)
> >
> > Select * from Testdata
> >
> > Drop Table Testdata
> >
> > --
> > HTH, Jens Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> > "ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
> > news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
> > >I want to use SQL to set the default of a field.
> > > How can I implement with DDL?
> > >
> > >
> >
> >|||You would probably have to
rename the column
add a new column with default value
move data from orig column to new column
drop orig column
rename new column to orig name
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23BW3EA%23XFHA.4036@.tk2msftngp13.phx.gbl...
> Thank,
> If the TestColumn have create before, but did not set default value yet.
> Can I modify the structrue now , set the default value to that column?
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> ¼¶¼g©ó¶l
> ¥ó·s»D:ew5otC5XFHA.2076@.TK2MSFTNGP15.phx.gbl...
>> CREATE TABLE Testdata
>> (
>> TestIDCOlumn INT,
>> TestColumn varchar(10) DEFAULT 'Test'
>> )
>> Insert into Testdata(TestIDColumn) Values (1)
>> Select * from Testdata
>> Drop Table Testdata
>> --
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
>> news:OZmeP74XFHA.3584@.TK2MSFTNGP14.phx.gbl...
>> >I want to use SQL to set the default of a field.
>> > How can I implement with DDL?
>> >
>> >
>>
>

Friday, March 30, 2012

how to simulate a block

how to simulate a block

how to simulate a block

Monday, March 12, 2012

How to set the default value

How to set the default value

How to set the default value

set affinity for SqlExpress

Blog Archive

About Me