Adding a constraint question

Can you add a constraint using the below syntax but to check whether a field
has only numeric digits?
So that if Threshold was updated to say 'marc1' it would fail.
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold CHECK (Threshold in ('Y','N'))Something like this?
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (isnumeric(Threshold) = 1)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"marcmc" wrote:

> Can you add a constraint using the below syntax but to check whether a fie
ld
> has only numeric digits?
> So that if Threshold was updated to say 'marc1' it would fail.
> ALTER TABLE misre_threshold WITH NOCHECK
> ADD CONSTRAINT Threshold CHECK (Threshold in ('Y','N'))|||Try this:
...check (<column name> not like '%[^0-9]%')
ML
http://milambda.blogspot.com/|||thanks guys BUT
I tried the following:
but the Threshold Column is dataType money
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (isnumeric(convert(money,Threshold)) =
1)
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (Threshold not like '%[^0-9]%')
Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.|||Aha, I assumed it was a character column (you haven't mentioned the actual
data type).
Use CAST in the constraint:
ALTER TABLE misre_threshold WITH NOCHECK
ADD CONSTRAINT Threshold_Const CHECK (cast(Threshold as varchar(64)) not
like '%[^0-9]%')
ML
http://milambda.blogspot.com/|||By the way - have you checked out this nice article regarding the ISNUMERIC
function?
http://www.aspfaq.com/show.asp?id=2390
ML
http://milambda.blogspot.com/|||thanks that does build it but my .net application runs as follows and so
encounters an error, should i change my .net app? I would rather not.|||Thanks for that..
Kewl article.. But I am using 2000 SP4 and the results are different. But
some misses nevertheless.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"ML" wrote:

> By the way - have you checked out this nice article regarding the ISNUMERI
C
> function?
> http://www.aspfaq.com/show.asp?id=2390
>
> ML
> --
> http://milambda.blogspot.com/|||Whenever a constraint is violated the SQL Server returns an error saying so,
and the error should be handled by the calling application. Isn't the
constraint already implemented on the application tier? Why not?
What exactly do you need?
An alternative is to use a trigger and simply rollback any transactions that
would otherwise violate the constraint, but this is IMHO a kludge, since the
data would not be inserted and the user would not be notified of this - in m
y
view - vital consequence.
ML
http://milambda.blogspot.com/|||I see your point.
I don't know what your exposure is to validating data on a datagrid within
.net but it aint pretty.
sample ddl,
What I want is the first update to break the constraint, the second should
not.
create table marc(
ThresholdType varchar(6),
Threshold money)
insert into marc values('RecEst', 20000.00)
ALTER TABLE marc WITH NOCHECK
ADD CONSTRAINT Threshold CHECK (cast(Threshold as varchar(64)) not
like '%[^0-9]%')
select * from marc
update marc set Threshold = 'marc1' where ThresholdType = 'RecEst'
update marc set Threshold = 20001.00 where ThresholdType = 'RecEst'

Add Spaces before text

Hi,

I want to add some extra spaces before a text value. I'm using the below expression

=IIF(Fields!ParentLevel.Value=2or Fields!ParentLevel.Value=1,Space(4) & UCase(Fields!Business_Unit_Name.Value),(Fields!Business_Unit_Name.Value))

Spaces are added when I preveiw the report in VS.NET IDE. However, If I deploy the report on Report Server and view the report in Browser (IE7) I'm not able to see extra spaces that I'm seeing in preview.

Is there anything I'm missing here?

Quick help is highly appreciated.

Thanks and Regards,
Chakra

Hi,
This will more than likely be a html render "issue" as opposed to your code.
Instead of inserting spaces try inserting 4 non-breaking spaces...

Add SAN Disk Resource to Active/Active SQL cluster

Looking to see if I am missing any steps below to adding a SAN disk
resource to an Active/Active SQL cluster:
-Create Volumes
-Present LUNs to both Nodes
-Failover Cluster Groups to Node2
-On Node1
Disable ClusDisk
Disable Cluster Services
Reboot Node1
Rescan for hardware changes
Write the disk signature
Set disk to basic
Create Partition
Assign Drive Letter
Enable ClusDisk
Enable Cluster Services
Reboot Node1
-On Node2
Failover Cluster Groups to Node1
Disable ClusDisk
Disable Cluster Services
Reboot Node2
Rescan for hardware changes
Assign Drive Letter
Enable ClusDisk
Enable Cluster Services
Reboot Node2
-Assign disk resource to Cluster Groups
-On Node1
Take SQL Server Resource offline
Add SQL Server Resource dependency to disk resource
Bring SQL Server Resource online
-On Node2
Repeat above
-Test failover
Thanks,
Ian in SD
With Windows 2003 as the host, you may be able to skip some of the reboots.
You can rescan the disk list and pick up newly added disks. I have never
had to disable or stop the cluster service to pick up new disks. I do make
sure and do all the work on a node that does not currently host any cluster
resources. Once I am done, I can rescan the disk list on the other node(s)
and failover at will. If you have the downtime, the reboots won't hurt, but
they may not be necessary.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<theredmiata@.hotmail.com> wrote in message
news:1142967852.423752.294080@.i39g2000cwa.googlegr oups.com...
> Looking to see if I am missing any steps below to adding a SAN disk
> resource to an Active/Active SQL cluster:
>
> -Create Volumes
> -Present LUNs to both Nodes
> -Failover Cluster Groups to Node2
> -On Node1
> Disable ClusDisk
> Disable Cluster Services
> Reboot Node1
> Rescan for hardware changes
> Write the disk signature
> Set disk to basic
> Create Partition
> Assign Drive Letter
> Enable ClusDisk
> Enable Cluster Services
> Reboot Node1
> -On Node2
> Failover Cluster Groups to Node1
> Disable ClusDisk
> Disable Cluster Services
> Reboot Node2
> Rescan for hardware changes
> Assign Drive Letter
> Enable ClusDisk
> Enable Cluster Services
> Reboot Node2
> -Assign disk resource to Cluster Groups
> -On Node1
> Take SQL Server Resource offline
> Add SQL Server Resource dependency to disk resource
> Bring SQL Server Resource online
> -On Node2
> Repeat above
> -Test failover
> Thanks,
> Ian in SD
>
|||Thanks for the reply Geoff. This the first time for this process as we
are trying to split the file groups and would like to have each group
on a seperate volume.

Add member failed for DatabaseRole 'db_datareader'

I tried to give myself datareader and datawriter for my domain login and got this error below. I'm logged into the server with my domain account which has admin rights.

TITLE: Microsoft SQL Server Management Studio

Add member failed for DatabaseRole 'db_datareader'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+DatabaseRole&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot use the special principal 'dbo'. (Microsoft SQL Server, Error: 15405)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15405&LinkId=20476

BUTTONS:

OK

Because you are conected as a sysadmin, you are also the dbo of the database. You cannot add dbo as a member of any fixed role. dbo already has the permissions of db_datareader and db_datawriter.

Thanks
Laurentiu

|||Thanks!

Add ID at Insert

The below code works fine, but for one reason, I need to get the ReferenceID inserted into a seperate table along with the newly created unique ID in the table Box. Any ideas how I can accomplish this...

CREATE PROCEDURE AddBox
(
@.BoxNumber nvarchar(40),
@.LocId int,
@.BoxNotes Text,
--This gets added to a different table that I'll call References, but can't go until I can get the uniqueID of what is inserted.
@.ReferenceID int
)
AS
INSERT INTO box
(
box_num,
loc_id,
box_notes,
status_id
)
VALUES
(
@.BoxNumber,
@.Locid,
@.BoxNotes,
2
)

didnt quite understand your question but check out BOL for SCOPE_IDENTITY().

hth|||Just the point in the right direction I needed, Thanks.

Once I inserted a new box file into the Box table I then was able to get the Unique ID number and then insert that into a reference table. The end code (after the first insert) for my previous post ends up looking as such:

Set @.Identity = SCOPE_IDENTITY()
Set @.Reference = @.ReferenceNumber
Insert into reference
(
reference_name,
box_id
)
VALUES
(
 @.Reference,
 @.Identity
)
GO

add cariage return

I'm trying to add a carriage return in the below function, but nothing is
happening.
The end result is to cut-n-paste into notepad with carriage returns.
What am I doing wrong?
thanks!
CREATE FUNCTION dbo.fctConcatTitles
(
@.O VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.r VARCHAR(8000)
SELECT @.r = ISNULL(@.r+ Char(13) , '') + Title + ' - ' + Artist
FROM Titles
WHERE OrderNo = @.O
RETURN @.r
ENDshank wrote on Wed, 11 Jan 2006 05:15:01 -0500:

> I'm trying to add a carriage return in the below function, but nothing is
> happening.
> The end result is to cut-n-paste into notepad with carriage returns.
> What am I doing wrong?
Try CHAR(13) + CHAR(10). Windows uses a Carriage Return + Line Feed for end
of line termination, not just a CR.
Dan|||The above code works and an optional solution is manually adding a
linefeed,
like this:
SELECT @.r = ISNULL(@.r + '
' , '') + Title + ' - ' + Type
FROM Titles
It's not pretty - but efficient...
/ola|||shank (shank@.tampabay.rr.com) writes:
> I'm trying to add a carriage return in the below function, but nothing is
> happening.
> The end result is to cut-n-paste into notepad with carriage returns.
> What am I doing wrong?
> thanks!
> CREATE FUNCTION dbo.fctConcatTitles
> (
> @.O VARCHAR(32)
> )
> RETURNS VARCHAR(8000)
> AS
> BEGIN
> DECLARE @.r VARCHAR(8000)
> SELECT @.r = ISNULL(@.r+ Char(13) , '') + Title + ' - ' + Artist
> FROM Titles
> WHERE OrderNo = @.O
> RETURN @.r
> END
Beside the CR issue, note that this piece of code relies on undefined
behaviour, so there is no guarantee that you will get the result you are
looking for.
In SQL 2000, the only guaranteed way is to run a cursor. And most
probably you want an ORDER BY as well, so that you don't the data
in some funny order.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I've tried this...
SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + Title + ' - ' + Artist
Then this...
SELECT @.r = ISNULL(@.r+ '
', '') + Title + ' - ' + Artist
...without any luck.
To be clear, I'm expecting to see results in QA.
I'm also cut-n-pasting from QA into notepad and there's no carriage returns.
Can you explain more about the cursor?
I don't believe I've ever used code that uses the cursor.
thanks to all!
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974881545AC39Yazorman@.127.0.0.1...
> shank (shank@.tampabay.rr.com) writes:
> Beside the CR issue, note that this piece of code relies on undefined
> behaviour, so there is no guarantee that you will get the result you are
> looking for.
> In SQL 2000, the only guaranteed way is to run a cursor. And most
> probably you want an ORDER BY as well, so that you don't the data
> in some funny order.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Use the "Results to Text" option.
ML
http://milambda.blogspot.com/|||shank (shank@.tampabay.rr.com) writes:
> I've tried this...
> SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + Title + ' - ' + Artist
> Then this...
> SELECT @.r = ISNULL(@.r+ '
> ', '') + Title + ' - ' + Artist
> ...without any luck.
> To be clear, I'm expecting to see results in QA.
> I'm also cut-n-pasting from QA into notepad and there's no carriage
> returns.
To echo ML's post: you are using text more, aren't you? In grid mode
you will not see any CRs.

> Can you explain more about the cursor?
> I don't believe I've ever used code that uses the cursor.
That's good! Too many inexperienced programmers use cursors when they
shouldn't, so I almost feel bad for showing you, but since this only
works with cursors (in SQL 2000), there is not much choice:
DECLARE @.r varchar(8000),
@.item varchar(50)
DECLARE thiscur CURSOR LOCAL FAST_FORWARD FOR
SELECT title + '-' + artist
FROM Titles
WHERE OrderNo = @.OrderNo
ORDER BY title, artist
OPEN thiscur
WHILE 1 = 1
BEGIN
FETCH thiscur INTO @.item
IF @.@.fetch_status <> 0
BREAK
SELECT @.r = CASE WHEN @.r IS NULL THEN ''
ELSE @.r + char(10) + char(13)
END + @.item
END
DEALLOCATE thiscur
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog:
> To echo ML's post: you are using text mode, aren't you? In grid mode
> you will not see any CRs
Well that's not true is it? The CRs is shown as "squares" in grid mode
and when c-n-p
from QA to notepad they tag along and do result in CRs.
shank:
I cannot understand why you don't get the expected result - can you try
the
following code (NB you have to have example database "pubs" installed)
-- code start
use pubs
declare @.r varchar(8000)
SELECT @.r = ISNULL(@.r+ Char(13) + Char(10), '') + title + ' - ' + type
>From titles
Select @.r
-- code end
/o

Add a number to string. (conversion problem)

Lets say I have a table named Projects with the data below.
Y07001
Y07002
Y07003
Y07010
Y07011
I want to pick up the last 3 numbers and add a one to it so.

Declare @.Countas varchar(3)SELECTTop 1 @.Count=Right(Project, 3)+1FROM ProjectsOrderby IDdesc

SELECT @.Count

This is what I am using. It picks up the last entry, add a one to it and gives me 12 in return. What i want is 012, not 12

What you want is probably this?

SELECT

RIGHT('000'+Convert(varchar,@.Count),3)