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
>
>

how to show summary numbers inside data

Hello, I am very new to Rpt Svcs, and am stuck on a report I have to build.
I have a simple report that shows agent code, state, and zipcode for hte
clients of a given agent. I need to modify this report to show a summary of
how many clients are in each zipcode in each state for each agent.
How do I design my report to allow a first glance to be the agent(s), then
drill down to a state-->count of clients/state rpt, then drill down to number
of clients per zip in that state, then drill down to show the full data for
that zip?
is this a code issue or can rpt svc group my results and display the data
the way I want it?
thank you in advance!!No and yes.
Sort the data by agent, zipcode and client.
Add group for agent and for zipcode.
In the footer of each group, add a field to capture count. For the
expression:
=Count(Fields!ClientId.Value) or whatever uniquely identifies the client in
the result set.
After that, get some beer and sandwiches and do what I do, chase your golf
balls around in the woods all day.
-Tim
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
> Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
> build.
> I have a simple report that shows agent code, state, and zipcode for hte
> clients of a given agent. I need to modify this report to show a summary
> of
> how many clients are in each zipcode in each state for each agent.
> How do I design my report to allow a first glance to be the agent(s), then
> drill down to a state-->count of clients/state rpt, then drill down to
> number
> of clients per zip in that state, then drill down to show the full data
> for
> that zip?
> is this a code issue or can rpt svc group my results and display the data
> the way I want it?
> thank you in advance!!|||Hey pretty simple, can be done using toggle visibility.. If you want some
samples go to adventureWorks Sample and go to "Territory Sales Drilldown"
after seeing that I think you can build your reports.
Amarnath
"Tim Dot NoSpam" wrote:
> No and yes.
> Sort the data by agent, zipcode and client.
> Add group for agent and for zipcode.
> In the footer of each group, add a field to capture count. For the
> expression:
> =Count(Fields!ClientId.Value) or whatever uniquely identifies the client in
> the result set.
> After that, get some beer and sandwiches and do what I do, chase your golf
> balls around in the woods all day.
> -Tim
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
> > Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
> > build.
> > I have a simple report that shows agent code, state, and zipcode for hte
> > clients of a given agent. I need to modify this report to show a summary
> > of
> > how many clients are in each zipcode in each state for each agent.
> > How do I design my report to allow a first glance to be the agent(s), then
> > drill down to a state-->count of clients/state rpt, then drill down to
> > number
> > of clients per zip in that state, then drill down to show the full data
> > for
> > that zip?
> > is this a code issue or can rpt svc group my results and display the data
> > the way I want it?
> > thank you in advance!!
>
>|||Rule Number 1. Never respond to peoples' requests for assistance after
beers. Actually, I still think my advice was sound...
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:FCE0E20C-2D62-4277-BBBC-FD1437165F8C@.microsoft.com...
> Hey pretty simple, can be done using toggle visibility.. If you want some
> samples go to adventureWorks Sample and go to "Territory Sales Drilldown"
> after seeing that I think you can build your reports.
> Amarnath
>
> "Tim Dot NoSpam" wrote:
>> No and yes.
>> Sort the data by agent, zipcode and client.
>> Add group for agent and for zipcode.
>> In the footer of each group, add a field to capture count. For the
>> expression:
>> =Count(Fields!ClientId.Value) or whatever uniquely identifies the client
>> in
>> the result set.
>> After that, get some beer and sandwiches and do what I do, chase your
>> golf
>> balls around in the woods all day.
>> -Tim
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:E53C91FB-FC7F-4B29-B91F-5ACB6C4E177C@.microsoft.com...
>> > Hello, I am very new to Rpt Svcs, and am stuck on a report I have to
>> > build.
>> > I have a simple report that shows agent code, state, and zipcode for
>> > hte
>> > clients of a given agent. I need to modify this report to show a
>> > summary
>> > of
>> > how many clients are in each zipcode in each state for each agent.
>> > How do I design my report to allow a first glance to be the agent(s),
>> > then
>> > drill down to a state-->count of clients/state rpt, then drill down to
>> > number
>> > of clients per zip in that state, then drill down to show the full data
>> > for
>> > that zip?
>> > is this a code issue or can rpt svc group my results and display the
>> > data
>> > the way I want it?
>> > thank you in advance!!
>>

how to set integer value in buffer

I am trying to set a decimal value to the pipelinecolumn buffer, but it doesnt get set, and the value is NULL.

Here is the portion of the code of what I am trying to do:

if (columnInfos[x].colName.EndsWith("_CRC"))

{

int a;

a_cmp tst = new a_cmp();

a= tst.a_crc32(inputbufferstream); this function returns a integer value

buffer.SetDecimal(colInfo.bufferColumnIndex, Convert.ToDecimal(a));

}

Please let me know how to set a decimal value in the buffer.

That line of code looks correct, assuming the bufferColumnIndex is correct and the column is of type DT_CY, DT_DECIMAL, or DT_NUMERIC.

Are you seeing an exception?

Thanks
Mark

|||

Do you want to set an integer or do you wanta decimal. Subject says one thing, text says another.

An agnostic way of doing this is to just assign direct, e.g.

buffer[colInfo.bufferColumnIndex] = a;

How to set celldata and Permissions to Role

Hi All,

I know how to create a role and adding Members(users) to that role by using AMO class library,but i need some sample code like how we can set the database permissions to the role and how we can set the celldata to that particular role.If any one knows about this please send reply immediately.

it is very urgent.

Thanks in advance.

Hi,

I believe the Adventure Works sample AMO application has code for creating permissions (search for CubePermission or DimensionPermission in the code):

http://msdn2.microsoft.com/en-us/library/ms160736.aspx

In general, you can use this 'reverse engineering' trick to get AMO code:

- create the objects you want with the user interface (in BI Development Studio with wizard/editors or in SQL Management Studio); in this case, use the security dialog to setup the database/dimensions/cubes permissions

- then look at the XML: in BI Development Studio, right click on the cube item -> View Code and search for the CubePermission XML fragment to see the CubePermission, similar for DimensionPermission; in SQL Management you can right click on the cube, script Alter or Create, and then find the CubePermission XML fragment

- to be easier to read, copy the CubePermission in a separate file

- there is a 1-to-1 relationship between the AMO classes and properties and the XML, by reading the XML you will know what classes and properties to set; for example, the <CubePermission> element tells you to create in AMO a CubePermission object; the <Name> sub-element corresponds to the Name property

Adrian Dumitrascu

How to set a ReportParameter to the value NULL?

Hi,

how can I set a ReportParameter in C# to NULL? My code is as follows

ReportParameter[] param = new ReportParameter[1];
param[0] = new ReportParameter("LanguageID", "1");
param[1] = new ReportParameter("TopCount", "30");
this.ReportViewer1.ServerReport.SetParameters(param);

Sometimes I want to pass the value NULL for the parameter TopCount so my stored proc can handle a special case. I tried

param[1] = new ReportParameter("TopCount", null);

but it doesn't work...

I know, that there is a possibility for using urls to pass parms. There you have to put the parm name, followed by ":isnull=true".

Thanks,
Dirk

You didn't say exactly how it's not working, but I'm guessing that you're getting a compilation error because the second argument to the ReportParameter constructor is ambiguos.

Try this:

string val = null;
param[1] = new ReportParameter("TopCount", val);

|||Thanks, Chris, thats it!

how to send sql mail using condition statement

I try to run the following code in DTS, but i not work. What i want is
if do not have duplicate data, then the table will be dropped. While
if have duplicate data, the table will not be dropped but an email
notification will be sent. Why when there is no duplicate data, the
email still will be sent out?
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
if exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
use master
go
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'&

---

Hello SQL Server SMTP SQL
Mail

---

',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
goYour send mail script is in a different batch because of the 'GO' batch
terminator. Consider revising your script like the example below:
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
begin
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'&

---

Hello SQL Server SMTP SQL
Mail

---

',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
end
go
--
Hope this helps.
Dan Guzman
SQL Server MVP
"tchangmian" <tchangmian@.yahoo.com.sg> wrote in message
news:6447ee25.0410042022.52f52ac5@.posting.google.com...
>I try to run the following code in DTS, but i not work. What i want is
> if do not have duplicate data, then the table will be dropped. While
> if have duplicate data, the table will not be dropped but an email
> notification will be sent. Why when there is no duplicate data, the
> email still will be sent out?
>
> if not exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> drop table [dbo].[Rewards_CatalogProducts_CS]
> end
>
> else
> if exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
>
> use master
> go
> declare @.FROM NVARCHAR(4000),
> @.FROM_NAME NVARCHAR(4000),
> @.TO NVARCHAR(4000),
> @.CC NVARCHAR(4000),
> @.BCC NVARCHAR(4000),
> @.priority NVARCHAR(10),
> @.subject NVARCHAR(4000),
> @.message NVARCHAR(4000),
> @.type NVARCHAR(100),
> @.attachments NVARCHAR(4000),
> @.codepage INT,
> @.rc INT
> select @.FROM = N'sqlmail@.cyber-village.net',
> @.FROM_NAME = N'ChangMian',
> @.TO = N'tchangmian@.yahoo.com.sg',
> @.CC = N'changmian@.cyber-village.net',
> @.priority = N'High',
> @.subject = N'Ei,can wor',
> @.message = N'<HTML><H1>Hello SQL Server SMTP SQL
> Mail</H1></HTML>',
> @.type = N'text/html',
> @.attachments = N'',
> @.codepage = 0
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = @.FROM,
> @.TO = @.TO,
> @.CC = @.CC,
> @.priority = @.priority,
> @.subject = @.subject,
> @.message = @.message,
> @.type = @.type,
> @.attachments = @.attachments,
> @.codepage = @.codepage,
> @.server = N'mail.cyber-village.net'
> select RC = @.rc
> go

how to send sql mail using condition statement

I try to run the following code in DTS, but i not work. What i want is
if do not have duplicate data, then the table will be dropped. While
if have duplicate data, the table will not be dropped but an email
notification will be sent. Why when there is no duplicate data, the
email still will be sent out?
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
if exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
use master
go
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'<HTML><H1>Hello SQL Server SMTP SQL
Mail</H1></HTML>',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
go
Your send mail script is in a different batch because of the 'GO' batch
terminator. Consider revising your script like the example below:
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
begin
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'<HTML><H1>Hello SQL Server SMTP SQL
Mail</H1></HTML>',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
end
go
Hope this helps.
Dan Guzman
SQL Server MVP
"tchangmian" <tchangmian@.yahoo.com.sg> wrote in message
news:6447ee25.0410042022.52f52ac5@.posting.google.c om...
>I try to run the following code in DTS, but i not work. What i want is
> if do not have duplicate data, then the table will be dropped. While
> if have duplicate data, the table will not be dropped but an email
> notification will be sent. Why when there is no duplicate data, the
> email still will be sent out?
>
> if not exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> drop table [dbo].[Rewards_CatalogProducts_CS]
> end
>
> else
> if exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
>
> use master
> go
> declare @.FROM NVARCHAR(4000),
> @.FROM_NAME NVARCHAR(4000),
> @.TO NVARCHAR(4000),
> @.CC NVARCHAR(4000),
> @.BCC NVARCHAR(4000),
> @.priority NVARCHAR(10),
> @.subject NVARCHAR(4000),
> @.message NVARCHAR(4000),
> @.type NVARCHAR(100),
> @.attachments NVARCHAR(4000),
> @.codepage INT,
> @.rc INT
> select @.FROM = N'sqlmail@.cyber-village.net',
> @.FROM_NAME = N'ChangMian',
> @.TO = N'tchangmian@.yahoo.com.sg',
> @.CC = N'changmian@.cyber-village.net',
> @.priority = N'High',
> @.subject = N'Ei,can wor',
> @.message = N'<HTML><H1>Hello SQL Server SMTP SQL
> Mail</H1></HTML>',
> @.type = N'text/html',
> @.attachments = N'',
> @.codepage = 0
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = @.FROM,
> @.TO = @.TO,
> @.CC = @.CC,
> @.priority = @.priority,
> @.subject = @.subject,
> @.message = @.message,
> @.type = @.type,
> @.attachments = @.attachments,
> @.codepage = @.codepage,
> @.server = N'mail.cyber-village.net'
> select RC = @.rc
> go