Are Blank String and String with only Space characters equal to each other? They should not be!!

I have a table called Table_1

Table_1
--
a varchar(10),
b varchar(20)

and Column a is the Primary Key.

INSERT INTO Table_1
(a ,b)
VALUES
('' , 'BLANK STRING')

INSERT INTO Table_1
(a ,b)
VALUES
(' ' ,'4 SPACES')

When i execute this Insertions, it inserts the First Record but on the Second one it gives Violation of Uniqueness

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 9
Violation of PRIMARY KEY constraint 'PK_Table_1'. Cannot insert duplicate key in object 'dbo.Table_1'.
The statement has been terminated.

My Questions is that; HOW can a Blank String or a String with 4(or any number) space charcters in it can be EQUAL? In my opinion they should not be regarded as the same! Is there a solution to this issue? That's not the case in Oracle Database.

Thanks for your help!

Regards,
Cem

According to ANSI standards (or is it SQL Server's implementation of ANSI...), anyway, for sorting purposes, leading blanks are ignored. (And Primary Keys are 'sorted'...)

Try these two statements, the first will provide the 'right' answer, and the second will provide the 'real' answer.

SELECT len( (replicate( ' ', 4 )) )
SELECT datalength( (replicate( ' ', 4 )) )

|||

You should check the setting of SET ANSI_PADDING. If you want behavior such as you find in ORACLE, you will need SET ANSI_PADDING ON. That way trailing blanks will not be trimmed from your VARCHAR insert statements.

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

Dan

Application Databases storing location

We have SQL Database in Cluster volume F:
Cluster volume is mounted in the primary server A1.
We have production and test databases of the applications
stored in the Cluster Storage F:.
Is it possible to store all the test databases of the
applications in the Clocal harddisk) of the primary
server?
If there is a solution pls advice.
You can copy data to anywhere you would like. To use the data with
clustering, you have to have the ability for that data to be reached by any
clustered node, even after a failure. If you keep anything local, and that
machine
fails, other nodes will not be able to access it.
Cheers,
Rod
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:743201c430db$f3018270$a401280a@.phx.gbl...
> We have SQL Database in Cluster volume F:
> Cluster volume is mounted in the primary server A1.
> We have production and test databases of the applications
> stored in the Cluster Storage F:.
> Is it possible to store all the test databases of the
> applications in the Clocal harddisk) of the primary
> server?
> If there is a solution pls advice.
>
|||A clustered SQL server must store data on a drive that is in the same
resource group. The SQL server must be dependant on the physical disk(s)
that clustered data is stored on.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:743201c430db$f3018270$a401280a@.phx.gbl...
> We have SQL Database in Cluster volume F:
> Cluster volume is mounted in the primary server A1.
> We have production and test databases of the applications
> stored in the Cluster Storage F:.
> Is it possible to store all the test databases of the
> applications in the Clocal harddisk) of the primary
> server?
> If there is a solution pls advice.
>
|||Can the Clocal harddisk) of the primary server be
included as part of the same resource group as the
clustered SQL server?

>--Original Message--
>A clustered SQL server must store data on a drive that is
in the same
>resource group. The SQL server must be dependant on the
physical disk(s)
>that clustered data is stored on.
>
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Anonymous" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:743201c430db$f3018270$a401280a@.phx.gbl...
applications
>
>.
>
|||Fine. We do not want the test data in the cluster mode and
need not be available in case of failure.
Is it possible to have SQL server in Cluster and
applications data in the local?

>--Original Message--
>You can copy data to anywhere you would like. To use the
data with
>clustering, you have to have the ability for that data to
be reached by any
>clustered node, even after a failure. If you keep
anything local, and that
>machine
>fails, other nodes will not be able to access it.
>Cheers,
>Rod
>"Anonymous" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:743201c430db$f3018270$a401280a@.phx.gbl...
applications
>
>.
>
|||No. C: is not a shared cluster resource.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:886301c4326d$56ac1a00$a301280a@.phx.gbl...[vbcol=seagreen]
> Can the Clocal harddisk) of the primary server be
> included as part of the same resource group as the
> clustered SQL server?
> in the same
> physical disk(s)
> in message
> applications
|||No, sorry you can't do that. All SQL data needs to be on the shared storage.
Cheers,
Rod
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:886b01c4326e$406dde30$a301280a@.phx.gbl...[vbcol=seagreen]
> Fine. We do not want the test data in the cluster mode and
> need not be available in case of failure.
> Is it possible to have SQL server in Cluster and
> applications data in the local?
> data with
> be reached by any
> anything local, and that
> in message
> applications

AppendStored Procedure

I got this stored procedure I'm running that inserts data from one table to another, but it keeps telling me that I'm violating the primary key rule which means one those records or 12 for all I know, already exsist in the other table. How do I add a IF ALREADY EXSIST CLAUSE TO MY STORED PROCEDURE, is that the correct word for it?? I'll look it up also. Heres the stored procedure I have so far.

ALTER PROCEDURE dbo.[2003_CorovanInsert]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[2003 TERMINATIONS]
WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
GOYou are almost there. Try this (it will need a little bit of work from you, but you should get it quickly)

ALTER PROCEDURE dbo.[2003_CorovanInsert]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[2003 TERMINATIONS] left join dbo.Corovan_Table on dbo.Corovan_Table.(primary key field) = dbo.[2003 TERMINATIONS].(primary key field)
WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
and dbo.corovan_table.(primary key field) is null
Go

You will have to supply the (primary key field)'s. Hope this helps.|||ALTER PROCEDURE dbo.[2003_CorovanInsert]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[2003 TERMINATIONS] left join dbo.Corovan_Table on dbo.Corovan_Table.(primary key field) = dbo.[2003 TERMINATIONS].(primary key field)
WHERE (TerminationDate BETWEEN CONVERT(DATETIME, '2003-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2003-12-31 00:00:00', 102))
and not exists (select <primary key field> from dbo.corovan_table)Go|||Problem solved guys thanks so much for your help, I sure do appreciate it, was struggling with that all day. At least it was a nice learning experience for me.

Thanks guys :)|||Hi guys its me again I'm having problems with my stored procedure again it executes as long as theres no primary key rule violations. But there is a violation Of the primary key because the the stored procedure was executed yesterday, but today I have discovered that since this is based on the 2003 terms date not all the 2003 terms were dated as 2003 (user error) so now I need to edit the stored procedure, which of course I'm not doing correctly.

Help? Again Please

ALTER PROCEDURE dbo.[03Corovans]
AS INSERT INTO dbo.Corovan_Table
([TM #], FirstName, LastName, [SS #], TerminationDate)
SELECT [TM #], FirstName, LastName, SocialSecurityNumber, TerminationDate
FROM dbo.[03TermsWithoutMatchingCorovan]
and not exists (select <TM #> from dbo.Corovan_table)Go|||Oh good Grief Never mind I looked at the script again and did the necessary corrections now it works just fine. AND I FEEL LIKE AN IDIOT

SORRY GUYS|||I'm so ashamed and embarrassed.....DUH!!!!!!!!!!!!!!!!!!!!!!

Merry Christmas and
Happy Holidays everyone|||AND I FEEL LIKE AN IDIOTDoes that mean "with your hands" ?

-PatP|||That means with my hands and My SO CALLED BRAIN|||Did that answer your question Pat or did I miss the point??|||Did that answer your question Pat or did I miss the point??You are assuming that my questions have a point... Not everyone would jump on that bus!

Yes, an idiot normally feels things with their hands, so if you are "feeling like an idiot", I'd assume that you were wandering about with a goofy expression, putting your hands on everything you could.

Nevermind... I never claimed that it made any sense, I just found it humorous!

-PatP|||You know Pat I know alot of people with very High IQ's who have a simular sense of humor as yourself, so I should have known better. Next time I'll just let that bus go right by me. :)

Anyway to set a primary key value

Hi just wondering if anyone knows if there is anyway to set a primary key
value? for example
pri key vharchar
1 paul
2 tom
999 Not Active
Can I somehow set the primary key for Not Active to 999?
thanks.
Paul G
Software engineer.
If it is a one-time thing, take a look at IDENITY_INSERT
Rick Sawtell

Anyway to set a primary key value

Hi just wondering if anyone knows if there is anyway to set a primary key
value? for example
pri key vharchar
1 paul
2 tom
999 Not Active
Can I somehow set the primary key for Not Active to 999?
thanks.
--
Paul G
Software engineer.Figured out a way around this one.
"Paul" wrote:
> Hi just wondering if anyone knows if there is anyway to set a primary key
> value? for example
> pri key vharchar
> 1 paul
> 2 tom
> 999 Not Active
> Can I somehow set the primary key for Not Active to 999?
> thanks.
> --
> Paul G
> Software engineer.|||If it is a one-time thing, take a look at IDENITY_INSERT
Rick Sawtell

Anyway to set a primary key value

Hi just wondering if anyone knows if there is anyway to set a primary key
value? for example
pri key vharchar
1 paul
2 tom
999 Not Active
Can I somehow set the primary key for Not Active to 999?
thanks.
Paul G
Software engineer.If it is a one-time thing, take a look at IDENITY_INSERT
Rick Sawtell