How to show progress bar in report column

Hi,

I have to show particular item progress status in a report column in graphical format.

For example, if item finish state is 60% then, column should display status in progress bar or similar to that.

Is this possible?

Any help would be appreciated.

Thanks

Hi,

you either make a graph showing 60 in the scale of 100 or use any thrid party tool from dundas to show gauges (or a different object)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

how to set up unique combinational constraint

Hi I have a table and I do not want to allow entries if two column value
match, for example
table 1
id type name value
int varchar20 varchar20 int
1 test paul 2
if name is paul and value is 3 allow insert
if name is paul and value is 2 do not allow insert.
I am trying to do this with under the index/keys menu, not quite sure how to
do it and if this is correct.
Thanks.
--
Paul G
Software engineer.If you just want to prevent the combination from being inserted into the
table, you can use an INSERT trigger. If you want the condition to always
hold for all the rows in the table, you can create a CHECK constraint on the
table.
Linchi
"Paul" wrote:
> Hi I have a table and I do not want to allow entries if two column value
> match, for example
> table 1
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> if name is paul and value is 3 allow insert
> if name is paul and value is 2 do not allow insert.
> I am trying to do this with under the index/keys menu, not quite sure how to
> do it and if this is correct.
> Thanks.
> --
> Paul G
> Software engineer.|||Consider putting a UNIQUE constraint on (name, value)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:23467935-67F3-418E-A24E-200CE74DD637@.microsoft.com...
If you just want to prevent the combination from being inserted into the
table, you can use an INSERT trigger. If you want the condition to always
hold for all the rows in the table, you can create a CHECK constraint on the
table.
Linchi
"Paul" wrote:
> Hi I have a table and I do not want to allow entries if two column value
> match, for example
> table 1
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> if name is paul and value is 3 allow insert
> if name is paul and value is 2 do not allow insert.
> I am trying to do this with under the index/keys menu, not quite sure how
to
> do it and if this is correct.
> Thanks.
> --
> Paul G
> Software engineer.|||I think I need the check constraint, I do not want any duplicates in the
table using both columns.
for example
id type name value
int varchar20 varchar20 int
1 test paul 2
2 test1 paul 3 ok
3 test2 tom 2 ok
4 test3 tom 3 ok
5 test4 tom 2 **do not allow this
entry**
Paul G
Software engineer.
"Linchi Shea" wrote:
> If you just want to prevent the combination from being inserted into the
> table, you can use an INSERT trigger. If you want the condition to always
> hold for all the rows in the table, you can create a CHECK constraint on the
> table.
> Linchi
> "Paul" wrote:
> > Hi I have a table and I do not want to allow entries if two column value
> > match, for example
> > table 1
> >
> > id type name value
> > int varchar20 varchar20 int
> > 1 test paul 2
> >
> > if name is paul and value is 3 allow insert
> > if name is paul and value is 2 do not allow insert.
> >
> > I am trying to do this with under the index/keys menu, not quite sure how to
> > do it and if this is correct.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Actually, you want a UNIQUE constraint on (name, value):
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
I think I need the check constraint, I do not want any duplicates in the
table using both columns.
for example
id type name value
int varchar20 varchar20 int
1 test paul 2
2 test1 paul 3 ok
3 test2 tom 2 ok
4 test3 tom 3 ok
5 test4 tom 2 **do not allow this
entry**
Paul G
Software engineer.
"Linchi Shea" wrote:
> If you just want to prevent the combination from being inserted into the
> table, you can use an INSERT trigger. If you want the condition to always
> hold for all the rows in the table, you can create a CHECK constraint on
the
> table.
> Linchi
> "Paul" wrote:
> > Hi I have a table and I do not want to allow entries if two column value
> > match, for example
> > table 1
> >
> > id type name value
> > int varchar20 varchar20 int
> > 1 test paul 2
> >
> > if name is paul and value is 3 allow insert
> > if name is paul and value is 2 do not allow insert.
> >
> > I am trying to do this with under the index/keys menu, not quite sure
how to
> > do it and if this is correct.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||ok thanks. Since I do not care if they are unique in a single column but
need to look at both columns together would I need to change the statement or
would the statement below still work
thanks?
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
column1 column2
paul 1 ok
paul 2 ok
paul 1 do not allow this entry match(value
in column1 and column2 matches value in column1 and column2 in previous
entry).
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Actually, you want a UNIQUE constraint on (name, value):
> alter table MyTable
> add
> constraint UK_MyTable UNIQUE (name, value)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
> I think I need the check constraint, I do not want any duplicates in the
> table using both columns.
> for example
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> 2 test1 paul 3 ok
> 3 test2 tom 2 ok
> 4 test3 tom 3 ok
> 5 test4 tom 2 **do not allow this
> entry**
>
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> > If you just want to prevent the combination from being inserted into the
> > table, you can use an INSERT trigger. If you want the condition to always
> > hold for all the rows in the table, you can create a CHECK constraint on
> the
> > table.
> >
> > Linchi
> >
> > "Paul" wrote:
> >
> > > Hi I have a table and I do not want to allow entries if two column value
> > > match, for example
> > > table 1
> > >
> > > id type name value
> > > int varchar20 varchar20 int
> > > 1 test paul 2
> > >
> > > if name is paul and value is 3 allow insert
> > > if name is paul and value is 2 do not allow insert.
> > >
> > > I am trying to do this with under the index/keys menu, not quite sure
> how to
> > > do it and if this is correct.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.
>
>|||The code I gave you is what you need, since it specifies that the uniqueness
is to be applied to the combination of both columns.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D044B7E2-3B9A-4B19-84A9-AADDA4AEBE2A@.microsoft.com...
ok thanks. Since I do not care if they are unique in a single column but
need to look at both columns together would I need to change the statement
or
would the statement below still work
thanks?
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
column1 column2
paul 1 ok
paul 2 ok
paul 1 do not allow this entry match(value
in column1 and column2 matches value in column1 and column2 in previous
entry).
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Actually, you want a UNIQUE constraint on (name, value):
> alter table MyTable
> add
> constraint UK_MyTable UNIQUE (name, value)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
> I think I need the check constraint, I do not want any duplicates in the
> table using both columns.
> for example
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> 2 test1 paul 3 ok
> 3 test2 tom 2 ok
> 4 test3 tom 3 ok
> 5 test4 tom 2 **do not allow
this
> entry**
>
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> > If you just want to prevent the combination from being inserted into the
> > table, you can use an INSERT trigger. If you want the condition to
always
> > hold for all the rows in the table, you can create a CHECK constraint on
> the
> > table.
> >
> > Linchi
> >
> > "Paul" wrote:
> >
> > > Hi I have a table and I do not want to allow entries if two column
value
> > > match, for example
> > > table 1
> > >
> > > id type name value
> > > int varchar20 varchar20 int
> > > 1 test paul 2
> > >
> > > if name is paul and value is 3 allow insert
> > > if name is paul and value is 2 do not allow insert.
> > >
> > > I am trying to do this with under the index/keys menu, not quite sure
> how to
> > > do it and if this is correct.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.
>
>

How to set the database to always use next increment number?

Is there a way to make the primary ID as Identity Column to always be in order? For example, I have 5 rows with ID 1,2,3,4,5. If I delete record number 5 and then added another record, the ID shows up as 6 and not 5. Or if I delete record number 3, the next ID is going to be 6 instead of 3. I like to keep all my ID in order if possible and not skipping if that is even possible or should I use that practice.

I think its good way if ID is incrementing by this you can come to know which record inserted when and so on ....

A good article on identity

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

|||

it is possible but you have to do it yourself by switching identity insert ON and OFF in your code and taking care about inserted value. It is not recommended way because it slow down insert process and also it can destroy your related record information if you have any, If you would like to have records order information for some purposes you can always do select statement like:

select identity(int,1,1) new_ID, old_id
into #test
from yourtablename
select * from #test
order by new_ID

and next select by new_ID your result.
You can also use ROW_NUMBER:

select ROW_NUMBER() OVER(ORDER BY old_id ASC) [OrderNo] from from yourtablename

|||

Many thanks for all your responses. The reason I was thinking about doing that is because this table is for linking pages. These links will be added and deleted often..and if there is no way to keep the ID in order, I'm afraid the ID may get too big but with less than 40 or 50 actual records in the table.

|||

There is no inbuilt solution for your problem.. you need to use alternative methods..

2 of those methods are discusses here.

http://www.sqlteam.com/item.asp?ItemID=765

|||

Many thanks for the link. It looks like I could use this:

declare @.intCounter int
set @.intCounter = 0
update Yaks
SET @.intCounter = YakSequenceNumber = @.intCounter + 1

The question I have is, do I run the above code everytime when I want to update and insert? Or do I run this code at the begingin where there is no record yet in the table?

|||

I think you need to use this while you are inserting. Is this make sense

|||

Okay, so can you give me a sample code of how I would use this while inserting in C# code behind? I'm still unclear as to how I would use it. By the way, if I'm using this, do I turn of the column Identity?

|||

I will create one example for you can give it to you

|||

This one solution has one big disadvantage that it will slow down your insert so maybe you should run this one time a day(or hour) for your table instead of running it on every insert?

But solution is very nice

How to set precision of a decimal number

there is a column which type is float in a table, i want to set the precision of its value, for example if its value is 10.333888, i want to get its value as 10.33, how to complete it in a select Sql?

thks

You can change yourcolumn to numeric or decimal data type, for example, decimal(18,2) or numeric(18,2), which will return 10.33 in your case.

You can look up the difference between numeric and float data type from Books Online which covers everything you need for SQL Server.

HTH

|||thks for HTH, i have solved my problem with your method, thks again

How to set not allow null and default

Can we use SQL to set a field of a table is not allow null and it's default.
For example, I have a St table and Seat field
I want to set the Seat field is not allow null, and it's default value to 1.
How can I wrte this SQL?CREATE TABLE St (col1 int not null, Seat int not null default 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OdXNMc9iFHA.3568@.tk2msftngp13.phx.gbl...[vbc
ol=seagreen]
> Can we use SQL to set a field of a table is not allow null and it's defaul
t.
> For example, I have a St table and Seat field
> I want to set the Seat field is not allow null, and it's default value to
1.
> How can I wrte this SQL?
>[/vbcol]

How to set not allow null and default

Can we use SQL to set a field of a table is not allow null and it's default.
For example, I have a St table and Seat field
I want to set the Seat field is not allow null, and it's default value to 1.
How can I wrte this SQL?CREATE TABLE St (col1 int not null, Seat int not null default 1)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OdXNMc9iFHA.3568@.tk2msftngp13.phx.gbl...
> Can we use SQL to set a field of a table is not allow null and it's default.
> For example, I have a St table and Seat field
> I want to set the Seat field is not allow null, and it's default value to 1.
> How can I wrte this SQL?
>

How to set not allow null and default

Can we use SQL to set a field of a table is not allow null and it's default.
For example, I have a St table and Seat field
I want to set the Seat field is not allow null, and it's default value to 1.
How can I wrte this SQL?
CREATE TABLE St (col1 int not null, Seat int not null default 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OdXNMc9iFHA.3568@.tk2msftngp13.phx.gbl...
> Can we use SQL to set a field of a table is not allow null and it's default.
> For example, I have a St table and Seat field
> I want to set the Seat field is not allow null, and it's default value to 1.
> How can I wrte this SQL?
>

how to set multiple local variable from single select statement

is it possible to set the value of multiple local variables using just 1
statement. For example assume a simple table 'E' with 3 columns 'empID',
'empName', 'empToken'. I can get their values with
declare @.ENAME as char(30), @.ETOKEN as int
SET @.ENAME = (SELECT empName FROM E WHERE empID = 1)
SET @.ETOKEN = (SELECT empToken FROM E WHERE empID = 1)
What I want to do is compine the two assignments into 1 single statement so
that the db never has to be looked up more than once. e.g.
SET @.ENAME, @.ETOKEN = (SELECT empName, empToken FROM E WHERE empID = 1)
is this possible and if it is, what is the synthax?try this.. hope this helps.
SELECT @.ENAME = empName,
@.ETOKEN = empToken
FROM E WHERE empID = 1
--|||HI,
sure:
SELECT @.ENAME = empName , @.ETOKEN = emptoken FROM E WHERE empID = 1
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--

How to send notification to .aspx page...

... as far as I understand, I need to create a custom HTTP protocol. Where do get an example of this? Shyam's book has an example of creating custom protocol that is not HTTP based. There are few remarks on HTTP custom protocol in Chapter 10, but those are not very helpful.

The scenario it is used for is as follows: ASP.NET messenger (an .aspx page) writes messages into SQL server table. The page needs to get notified as soon as new entry appears in the table (from another instance of messenger), so that the first instance of messenger would be able to get new message from the table. Is this appropriate usage of notification services? If not, can you suggest alternative ways to write ASP.NET messenger?

Thank you in advance.

Is this a kinda IM application ? If so NS is not a right choice for Real Time Communication I would prefer to explore RTC (Real Time CommunicationI API with MS Live Communication Server. These two works well for any IM/Messaging related application.

If you mention ur requirements in detail (what do u mean by message etc) i will be more clear... thanks

--Satyen

|||

Query Notifications may be a better technology choice for you in this application.

HTH...

Joe

How to send notification to .aspx page...

... as far as I understand, I need to create a custom HTTP protocol. Where do get an example of this? Shyam's book has an example of creating custom protocol that is not HTTP based. There are few remarks on HTTP custom protocol in Chapter 10, but those are not very helpful.

The scenario it is used for is as follows: ASP.NET messenger (an .aspx page) writes messages into SQL server table. The page needs to get notified as soon as new entry appears in the table (from another instance of messenger), so that the first instance of messenger would be able to get new message from the table. Is this appropriate usage of notification services? If not, can you suggest alternative ways to write ASP.NET messenger?

Thank you in advance.

Is this a kinda IM application ? If so NS is not a right choice for Real Time Communication I would prefer to explore RTC (Real Time CommunicationI API with MS Live Communication Server. These two works well for any IM/Messaging related application.

If you mention ur requirements in detail (what do u mean by message etc) i will be more clear... thanks

--Satyen

|||

Query Notifications may be a better technology choice for you in this application.

HTH...

Joe

How to send notification to .aspx page...

... as far as I understand, I need to create a custom HTTP protocol. Where do get an example of this? Shyam's book has an example of creating custom protocol that is not HTTP based. There are few remarks on HTTP custom protocol in Chapter 10, but those are not very helpful.

The scenario it is used for is as follows: ASP.NET messenger (an .aspx page) writes messages into SQL server table. The page needs to get notified as soon as new entry appears in the table (from another instance of messenger), so that the first instance of messenger would be able to get new message from the table. Is this appropriate usage of notification services? If not, can you suggest alternative ways to write ASP.NET messenger?

Thank you in advance.

Is this a kinda IM application ? If so NS is not a right choice for Real Time Communication I would prefer to explore RTC (Real Time CommunicationI API with MS Live Communication Server. These two works well for any IM/Messaging related application.

If you mention ur requirements in detail (what do u mean by message etc) i will be more clear... thanks

--Satyen

|||

Query Notifications may be a better technology choice for you in this application.

HTH...

Joe