Check if substring exists within string

I am building a string of the form:

FirstName LastName, FirstName LastName, FirstName LastName,...

and I would like to only add the current name if it isn't already in the string.

The reason I am doing this is because the table I'm working on has multiple entries for the same key but with different values for one of the columns.

The problem is I can't figure out how to check if current name already exists in the name list string that was previously built.

I appreciate your replies. Thank you.

The direct answer is,

CharIndex('Your Searching String', 'Your Main string)

If the result is NON ZERO Then the value is already exists.

Example,

Select Case When CharIndex('Three Four','One Two,Two Three,Three Four') <> 0 Then 'Yes' Else 'No' End --Yes

Select Case When CharIndex('Three Five','One Two,Two Three,Three Four') <> 0 Then 'Yes' Else 'No' End --No

NOTE:

But it is really bad idea to keep the multiple entry values as columns.

Better you can have a one more table where you can make a new row for each names.

The design which you are using is not recommended.

|||

I am pulling data from several tables and in one of them for some reason ( it might be a bug in how that table is generated or in how inserts are being made) I have multiple values for the same column for the same key. Which is generating multiple rows in my query. I cannot change that design. I can only work with it.

Thanks for your answer.

|||hi you can try this options..

SELECT *
INTO #Names
FROM (
SELECT 'John' AS FirstName, 'Doe' AS LastName, 1 AS OtherColumn UNION ALL
SELECT 'John' AS FirstName, 'Doe' AS LastName, 2 AS OtherColumn UNION ALL
SELECT 'Rhamille' AS FirstName, 'Golimlim' AS LastName, 3 AS OtherColumn UNION ALL
SELECT 'Rhamille' AS FirstName, 'Golimlim' AS LastName, 4 AS OtherColumn UNION ALL
SELECT 'Princess Quamille' AS FirstName, 'Golimlim' AS LastName, 4 AS OtherColumn
) Names

SELECT *
FROM #Names

-- option 1
DECLARE @.Names varchar(100)

SET @.Names = ''

SELECT @.Names = @.Names + FirstName + ' ' + LastName + ', '
FROM (
SELECT DISTINCT
FirstName
, LastName
FROM #Names
) Names
ORDER BY
FirstName
, LastName

SET @.Names = LEFT(@.Names,LEN(@.Names) - 1)

-- option 2
DECLARE @.Names2 varchar(100)

SET @.Names2 = ''

SELECT @.Names2 = @.Names2 + (CASE WHEN CHARINDEX(FirstName + ' ' + LastName, @.Names2) > 0 THEN '' ELSE FirstName + ' ' + LastName + ', ' END)
FROM #Names
ORDER BY
FirstName
, LastName

SET @.Names2 = LEFT(@.Names2,LEN(@.Names2) - 1)

SELECT @.Names as FromOption1, @.Names2 AS FromOption2

DROP TABLE #Names

Check if files exist Stored Proc

Hi,
This stored proc code uses DOS Copy command and xp_cmdshell stored
procedure to copy files form one location to another. See below the
code. It is working fine!. What I need is to add a check if the files in
the Source location exist or not. If not, then send an email (using
xp_sendmail) to us saying "files do not exist". If yes, then start
copying the files. How do I do that?
Thanks for your help.
declare @.source varchar(150)
declare @.destination varchar(150)
declare @.DOScmd varchar(300)
select @.source =
case @.@.servername
when 'A' then '\\server1\folder1\a*.*'
when 'B' then '\\server2\folder2\b*.*'
when 'C' then '\\server3\folder3\c*.*'
end,
@.destination =
case @.@.servername
when 'A' then '\\serverx\folderx'
when 'B' then '\\serverx\folderx'
when 'C' then '\\serverx\folderx'
end
-- copy only if the files in the source folder exist, otherwise send an
email for "files do not exixts".
set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
rtrim(@.destination)+'"'
exec master.dbo.xp_cmdshell @.DOScmd
GO
*** Sent via Developersdex http://www.examnotes.net ***Look up xp_fileexist in Books Online.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23CYCQDguFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> This stored proc code uses DOS Copy command and xp_cmdshell stored
> procedure to copy files form one location to another. See below the
> code. It is working fine!. What I need is to add a check if the files in
> the Source location exist or not. If not, then send an email (using
> xp_sendmail) to us saying "files do not exist". If yes, then start
> copying the files. How do I do that?
> Thanks for your help.
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> select @.source =
> case @.@.servername
> when 'A' then '\\server1\folder1\a*.*'
> when 'B' then '\\server2\folder2\b*.*'
> when 'C' then '\\server3\folder3\c*.*'
> end,
> @.destination =
> case @.@.servername
> when 'A' then '\\serverx\folderx'
> when 'B' then '\\serverx\folderx'
> when 'C' then '\\serverx\folderx'
> end
> -- copy only if the files in the source folder exist, otherwise send an
> email for "files do not exixts".
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
> rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Whoops, never mind! I forgot this proc is not documented / supported.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwg8xEguFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Look up xp_fileexist in Books Online.|||A particular file can be checked with:
EXEC master..xp_fileexist 'c:\boot.ini'
HTH, Jens Suessmeyer.|||If this were implemented as a DTS package, you would perhaps find the file
system object more suitable for copying files, etc.
http://msdn.microsoft.com/library/d...ystemObject.asp
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23CYCQDguFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> This stored proc code uses DOS Copy command and xp_cmdshell stored
> procedure to copy files form one location to another. See below the
> code. It is working fine!. What I need is to add a check if the files in
> the Source location exist or not. If not, then send an email (using
> xp_sendmail) to us saying "files do not exist". If yes, then start
> copying the files. How do I do that?
> Thanks for your help.
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> select @.source =
> case @.@.servername
> when 'A' then '\\server1\folder1\a*.*'
> when 'B' then '\\server2\folder2\b*.*'
> when 'C' then '\\server3\folder3\c*.*'
> end,
> @.destination =
> case @.@.servername
> when 'A' then '\\serverx\folderx'
> when 'B' then '\\serverx\folderx'
> when 'C' then '\\serverx\folderx'
> end
> -- copy only if the files in the source folder exist, otherwise send an
> email for "files do not exixts".
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
> rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks for the respone. The "xp_fileexist" works great if you know the
exact file name. In my case, I dont know the file name. All I need is to
capture the files start with "a" i.e. a*.*
So, EXEC master..xp_fileexist '\\server1\folder1\a*.*' does not work.
Any other ideas?
*** Sent via Developersdex http://www.examnotes.net ***|||CREATE TABLE #files
(
filename SYSNAME NULL
)
SET NOCOUNT ON
INSERT #files EXEC master..xp_cmdshell 'dir \\server1\folder1\a*.* /b'
SELECT COUNT(*) FROM #files WHERE filename IS NOT NULL
SELECT * FROM #files WHERE filename IS NOT NULL
DROP TABLE #files
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uvoSyaguFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks for the respone. The "xp_fileexist" works great if you know the
> exact file name. In my case, I dont know the file name. All I need is to
> capture the files start with "a" i.e. a*.*
> So, EXEC master..xp_fileexist '\\server1\folder1\a*.*' does not work.
> Any other ideas?
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||If you're already planning on using xp_cmdshell, then try this:
declare @.cmd varchar(1024)
declare @.path varchar(1024)
create table #fs
(
fId int identity (1, 1) primary key
,fName varchar(1024)
)
set @.path = 'c:\Inetpub*.*'
set @.cmd = 'dir /b ' + @.path
insert #fs
(
fName
)
exec master.dbo.xp_cmdshell @.cmd
if (exists (
select fs.fId
from #fs fs
where (fs.fName is not null)
))
begin
print 'exists'
end
else
begin
print 'does not exist'
end
ML|||Aaron! Thanx a lot!!!. It works like a champ!!!
*** Sent via Developersdex http://www.examnotes.net ***

Check for Duplicate values in Access

Hi, I'm creating a database that needs to check a value in a text field =
=
called JobCreate in a form called frmJobCreated against a table called =
tblJobCreated which has a whole list of job numbers in a field called =
JobNo, then come back with a message telling the user if there is a =
duplicate value...any help would be greatly appreciated,,thanks, FraserPlease, post DDL, sample data, and specify expected results.
Are you trying to report duplicate values, prevent them from being inserted
or what?
ML|||Hi, sorry for my ignorance but I'm quite new at databases what is DDL?
I'm trying to tell the user that a job number is already created if they
enter a duplicate number in the form that is already held in another
table, so it will display a message box telling them this, thanks
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
First of all, This is not the right forum because there is a dedicated forum
for access.
well. Use primary key (SOME THING LIKE SELECT COLUMN WHILE DESIGININGG AND
RIGHT CLICK SELECT PRIMARY KEY) in access on the column so that it will
prevent and throws an error. you can customise your error using VBA.
You have to use VBA because you are not using SQL SERVER 2000
Regards
R.D
--Knowledge gets doubled when shared
"Fraser Hetherington" wrote:

> Hi, sorry for my ignorance but I'm quite new at databases what is DDL?
> I'm trying to tell the user that a job number is already created if they
> enter a duplicate number in the form that is already held in another
> table, so it will display a message box telling them this, thanks
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Why are you making your users think of unique values? Why would any user car
e
what number his job is given?
Use a column constraint and design a function that will automatically
generate unique values for the job column. You sound like a crappy ISP -
designed to serve millions of users, yet each of them must create his/her ow
n
*globally unique* username in order to create a valid account. A total waste
of time.
ML|||"Fraser" <fraser68@.tiscali.co.uk> wrote in message
news:op.sy2fmed6s8gdz1@.acer64...
Hi, I'm creating a database that needs to check a value in a text
field
called JobCreate in a form called frmJobCreated against a table called
tblJobCreated which has a whole list of job numbers in a field called
JobNo, then come back with a message telling the user if there is a
duplicate value...any help would be greatly appreciated,,thanks,
Fraser
Fraser,
Pleaes try reposting your above message in *one* of the following
newsgroups:
comp.databases.ms-access
microsoft.public.access.forms
microsoft.public.access.formscoding
Sincerely,
Chris O.

Check constraints not being replicated properly

Hello, I'm at a loss with a tough problem I've yet to find a solution for:
SCENARIO:
I am replicating as a form of disaster-recovery. Our published application
uses local partitioned views, partitioned by month... so there is one
database per month. We have a date_created partitioning column in each
table which specifies that records should only go in that month. Example
for our Feb partition on the Processor_File table:
ALTER TABLE [dbo].[Processor_File] WITH CHECK ADD CONSTRAINT
[DF_Processor_File_date_created] CHECK (([date_created]>='02/01/2008' AND
[date_created]<'03/01/2008'))
When I add the tables as articles to the database's publication, I AM
setting the Copy Check Constraints = true option. I WANT this to occur,
because there's this crazy condition with partitioned views whereby the
check constraints can be enabled and yet somehow they are not "confident" in
the status of their checked data and therefore views that include the table
are considered non-updateable (which sucks -- can anybody explain THAT to me
either?!)
Regardless... I WANT the Check Constraints to be copied over AND I want them
enabled for replication. The problem, when the SNAPSHOT is generated to
create the database schema, it looks just like this:
IF OBJECT_ID(N'[dbo].[DF_Processor_File_date_created]') IS NULL
BEGIN
ALTER TABLE [dbo].[Processor_File] ADD CONSTRAINT
[DF_Processor_File_date_created] CHECK NOT FOR REPLICATION
([date_created]>='02/01/2008' AND [date_created]<'03/01/2008')
ALTER TABLE [dbo].[Processor_File] CHECK CONSTRAINT
[DF_Processor_File_date_created]
END
Notice: the NOT FOR REPLICATION clause is set. THAT IS EVIL!!! I do NOT
want that clause to be set!!! That clause is _NOT_ set in the source
description of my published table. How can I make sure the snapshot does
not create the tables on the subscriber side with that option?
TIA for your support,
Eric Gavin
Alliance Payment Technologies
Do you want this constraint enforced on the subscriber when the DML is
occurring due to a replication process? If so you do not want that
constraint to be not for replication? It sounds like this is likely
something you don't want, but I don't think it is necessarily evil.
Please post your schema of the table with the constraints and I'll show you
how to create your publication.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AACH Developers" <AACHDevs@.community.nospam> wrote in message
news:ubzHrdXWIHA.4448@.TK2MSFTNGP03.phx.gbl...
> Hello, I'm at a loss with a tough problem I've yet to find a solution for:
> SCENARIO:
> I am replicating as a form of disaster-recovery. Our published
> application uses local partitioned views, partitioned by month... so there
> is one database per month. We have a date_created partitioning column in
> each table which specifies that records should only go in that month.
> Example for our Feb partition on the Processor_File table:
> ALTER TABLE [dbo].[Processor_File] WITH CHECK ADD CONSTRAINT
> [DF_Processor_File_date_created] CHECK (([date_created]>='02/01/2008' AND
> [date_created]<'03/01/2008'))
> When I add the tables as articles to the database's publication, I AM
> setting the Copy Check Constraints = true option. I WANT this to occur,
> because there's this crazy condition with partitioned views whereby the
> check constraints can be enabled and yet somehow they are not "confident"
> in the status of their checked data and therefore views that include the
> table are considered non-updateable (which sucks -- can anybody explain
> THAT to me either?!)
> Regardless... I WANT the Check Constraints to be copied over AND I want
> them enabled for replication. The problem, when the SNAPSHOT is generated
> to create the database schema, it looks just like this:
> IF OBJECT_ID(N'[dbo].[DF_Processor_File_date_created]') IS NULL
> BEGIN
> ALTER TABLE [dbo].[Processor_File] ADD CONSTRAINT
> [DF_Processor_File_date_created] CHECK NOT FOR REPLICATION
> ([date_created]>='02/01/2008' AND [date_created]<'03/01/2008')
> ALTER TABLE [dbo].[Processor_File] CHECK CONSTRAINT
> [DF_Processor_File_date_created]
> END
> Notice: the NOT FOR REPLICATION clause is set. THAT IS EVIL!!! I do NOT
> want that clause to be set!!! That clause is _NOT_ set in the source
> description of my published table. How can I make sure the snapshot does
> not create the tables on the subscriber side with that option?
> TIA for your support,
> Eric Gavin
> Alliance Payment Technologies
>
|||[Note to Microsoft folks -- I still need help please!]
Thanks for trying to help, but part of the frustration I have is that I seem
to WANT to be doing something that nobody else really wants to do. And so
I've scrolled through a million google posts trying to figure out how to do
what nobody else wants to do. I DO want to enforce the constraints at the
DML during replication because of the partitioned view requirements.
Not meaning to sound rude, but my original post has every answer to your
question already in it, so I'll just re-post it as a response:
[Repost]
> occurring due to a replication process?

> constraint to be not for replication?
[vbcol=seagreen]
Lastly, the schema of the constraint in the published table and the schema
that the snapshot generates has already been posted. May I ask why it would
be important to know the whole schema of the table?
Thanks, and I'm sorry if any offense was given. I have been at this for
quite some time now and am still frustrated because after reading the books
I appear to be doing everything right, but the snapshot just "refuses" to
create WITHOUT the "NOT FOR REPLICATION" option set during constraint
creation. I NEED that constraint to NOT be used when the snapshot as
applied at the subscriber.
Thanks,
Eric Gavin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23vC48aeWIHA.6140@.TK2MSFTNGP02.phx.gbl...
> Do you want this constraint enforced on the subscriber when the DML is
> occurring due to a replication process? If so you do not want that
> constraint to be not for replication? It sounds like this is likely
> something you don't want, but I don't think it is necessarily evil.
> Please post your schema of the table with the constraints and I'll show
> you how to create your publication.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "AACH Developers" <AACHDevs@.community.nospam> wrote in message
> news:ubzHrdXWIHA.4448@.TK2MSFTNGP03.phx.gbl...
>
|||Confusion posted by me on the last bit:

> Thanks, and I'm sorry if any offense was given. I have been at this for
> quite some time now and am still frustrated because after reading the
> books I appear to be doing everything right, but the snapshot just
> "refuses" to create WITHOUT the "NOT FOR REPLICATION" option set during
> constraint creation. I NEED that constraint to NOT be used when the
> snapshot as applied at the subscriber.
Please correct this to:
I NEED the _OPTION_ (NOT FOR REPLICATION) to NOT be used when the snapshot
is applied.
For further clarity, I must, want, desire and need the check constraints to
be checked during the application of DML because of replication.
Eric
|||UPDATE: Apparently the schema_option:
0x10000
Replicates CHECK constraints as NOT FOR REPLICATION so that the
constraints are not enforced during synchronization.
Exists, but BOL seems to leave out the small (yet necessary detail) that it
is simply un-settable from the GUI. So I think this problem is solved if
I'm willing to run sp_changearticles for all of my table articles with check
constraints (which I am.)
But if MS-folk read this and thinking I'm headed in the wrong direction, or
misguided in my belief that the UI doesn't allow for this setting to be
updated, I'd appreciate learning so. Thanks!!
Eric Gavin
"AACH Developers" <AACHDevs@.community.nospam> wrote in message
news:e31zdvfWIHA.5132@.TK2MSFTNGP02.phx.gbl...
> Confusion posted by me on the last bit:
>
> Please correct this to:
> I NEED the _OPTION_ (NOT FOR REPLICATION) to NOT be used when the snapshot
> is applied.
> For further clarity, I must, want, desire and need the check constraints
> to be checked during the application of DML because of replication.
> Eric
>
|||Hi Eric,
I understand that you would like to have the snapshot does not create the
tables on your subscriber with the NOT FOR REPLICATION clause.
If I have misunderstood, please let me know.
The option is a default option for SQL Server 2000/2005 replication when
there is any foreign key constraints or check constraints; however you can
programmatically use T-SQL statements to change the replication schema
options (schema_option) by yourself. Please refer to the following articles:
Frequently Asked Questions for Replication Administrators
-- How do I manage constraints on published tables?
http://msdn2.microsoft.com/en-us/library/ms151740.aspx
Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
http://msdn2.microsoft.com/en-us/library/ms152529.aspx
How to: Specify Schema Options (Replication Transact-SQL Programming)
http://msdn2.microsoft.com/en-us/library/ms147887.aspx
sp_addarticle (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms147887.aspx
sp_changearticle (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms175980.aspx
For your other concern, "When I add the tables as articles to the
database's publication, I AM setting the Copy Check Constraints = true
option. I WANT this to occur, because there's this crazy condition with
partitioned views whereby the check constraints can be enabled and yet
somehow they are not "confident" in the status of their checked data and
therefore views that include the table are considered non-updateable (which
sucks -- can anybody explain THAT to me either?!)", I could not understand
it very clearly. To let me better understand your issue, could you please
answer me the following questions:
1. What it the type of your replication?
2. Did you mean that your views on your publisher became non-updatable
after you set the replication option "Copy Check Constraints = true" for
those table articles?
If you have any other questions or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Check before INSERT

I have a pretty standard form that inserts users name, office, and team. It generates a random 10 digit ID for them. How would i got about checking the table to make sure that ID doesn't exist?

Here's my insert code.

string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;
SqlConnection myConnection = new SqlConnection(strConnection);

string usercode = GenPassWithCap(9);

String insertCmd = "INSERT into users (ID, firstname, lastname, office, team) values (@.id, @.firstname, @.lastname, @.office, @.team)";
SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);

myCommand.Parameters.Add(new SqlParameter("@.id", SqlDbType.VarChar, 10));
myCommand.Parameters["@.id"].Value = usercode;

myCommand.Parameters.Add(new SqlParameter("@.firstname", SqlDbType.VarChar, 50));
myCommand.Parameters["@.firstname"].Value = txtFirstName.Text;

myCommand.Parameters.Add(new SqlParameter("@.lastname", SqlDbType.VarChar, 50));
myCommand.Parameters["@.lastname"].Value = txtLastName.Text;

myCommand.Parameters.Add(new SqlParameter("@.office", SqlDbType.VarChar, 75));
myCommand.Parameters["@.office"].Value = dwnOffice.SelectedValue;

myCommand.Parameters.Add(new SqlParameter("@.team", SqlDbType.VarChar, 20));
myCommand.Parameters["@.team"].Value = dwnTeam.SelectedValue;

myCommand.Connection.Open();

myCommand.ExecuteNonQuery();

Do I run a completey different select command before hand and try to match that field?

Instead of an adhoc T-SQL like this, you could pass the parameters to a proc and do a check before inserting.

IF NOT EXISTS(SELECT * FROM TableWHERE Id = @.ID AND Lastname = @.LastName And...)

INSERT INTO Users (...)

Charting question

I have a report where each page presents some data in tabular form, the
bottom row (numerically) representing a histogram; i.e., % of values falling
into the "bucket" corresponding to that column. Below this row I'd like to
have a bar chart that graphically displays this histogram with each bar
aligned with the column above. Although I've figured out how to constuct
the chart object there doesn't seem to be any way to actually get things to
line up properly.
The portion of the last row containing the data consists of 7 cells. There
are a couple of padding columns so the first data item is in column 3. What
I tried was to insert a row below (actually a footer row), then merge the 7
cells below the data along with the left adjacent pad cell. I included the
extra cell to allow space for the y axis labels.
So, I can get the chart to appear, but due to the formatting of the chart;
i.e., white space, things don't line up properly. I'm trying to duplicate
(as much as possible) a Crystal generated report in order to determine the
feasibility of using Reporting Services instead. I'm afraid this might be a
deal breaker -- so if anyone has any suggestions I'm anxious to hear back!
Thanks in advance for any help.
BillSo, anybody got any ideas on this one? I'd hoped to get some help before
this scrolls off the edge of the earth...
Bill
"Bill Cohagan" <bill@.teraXNOSPAMXquest.com> wrote in message
news:eDoS$jukEHA.1936@.TK2MSFTNGP12.phx.gbl...
>I have a report where each page presents some data in tabular form, the
> bottom row (numerically) representing a histogram; i.e., % of values
> falling
> into the "bucket" corresponding to that column. Below this row I'd like to
> have a bar chart that graphically displays this histogram with each bar
> aligned with the column above. Although I've figured out how to constuct
> the chart object there doesn't seem to be any way to actually get things
> to
> line up properly.
> The portion of the last row containing the data consists of 7 cells.
> There
> are a couple of padding columns so the first data item is in column 3.
> What
> I tried was to insert a row below (actually a footer row), then merge the
> 7
> cells below the data along with the left adjacent pad cell. I included the
> extra cell to allow space for the y axis labels.
> So, I can get the chart to appear, but due to the formatting of the chart;
> i.e., white space, things don't line up properly. I'm trying to duplicate
> (as much as possible) a Crystal generated report in order to determine the
> feasibility of using Reporting Services instead. I'm afraid this might be
> a
> deal breaker -- so if anyone has any suggestions I'm anxious to hear back!
> Thanks in advance for any help.
> Bill
>

chart problems

Hey all, I'm in a bit of a problem. I have a set of data on an excel spread sheet and i want that data to make a chart on a user form. I figured out how to get a blank chart on the user form but don't know how to link any data. Anyone think they can help?It would help if you posted this in the Excel forum rather than the MSSQL forum.

character strings as primary keys

Hello,

How bad is normalizing the database to the 3rd form, which requires that all fields depend on nothing but primary key. Consider the first table you create -- users. They have int primary keys, which duplicate the real primary keys -- user names. When user logs in, the user's entry is uniquely identified by its name, which is not primary key. The fundamental design rule -- avoid redundancy -- is violated. A VERY serious reason should be there for that.

Usually, design is compromised by redundancy for performance. Here, both copies are stored in one remote database, but integer keys may be located/used faster. Additionally, using long string references everywhere instead of short integer keys may save a lot of storage space (additionally increasing speed). How serious these impacts are? Am I missing something?

Usually, login names are not allowed to change. You have problems changing primary keys because all the foreign keys must be updated accordingly. Does it reveal that most user databases use character strings as primary keys?

Valentin,

Names are rarely UNIQUE -a primary requirement for a Primary Key. You only have to look at a telephone book to see that using Names as a Primary Key will be impossible. (Granted, a few 'small' countries mandate name uniqueness.)

From: http://howmanyofme.com/people/John_Smith/

There are 49,842 people in the U.S. named John Smith.

User Names, while sometimes, in small systems, seem unique, and seem suitable for Primary Key usage, will, over time prove to be a significant problem. Then the system will have to devolve to using ( Name + identifier ), e.g., JohnSmith1, JohnSmith2, etc. I have found that it is so problemmatic to use Names as keys that is is not worth even considering. As you indicate, Names 'should' allow for changes, i.e, marriage, divorce, etc. But the Primary Key 'should not' change. If you consider the domain security system, the User Name is NOT the Primary Key, and the Login Name is changable. The Primary Key is the SID, a unique identifier normally hidden from view, somewhat like using a IDENTITY field (or other indentifier) -in fact, a surrogate key. (Beware: simplistic explanition.)

It is extremely difficult to design a system for identifying People with a 'natural' Primary Key. Some adamately espouse using the SSN (in the USA) as a Primary Key. But it is NOT a naturally occuring characteristic of the individual -it is a surrogate key created by a remote system. It is legally protected, and increased being enforced, as 'sensitive' data. Most data systems 'should' NOT use the SSN as a Primary Key -of course, exceptions are allowed, or required, by law. So what does that leave as a naturally occuring characteristic of the entity -nothing really, except perhaps biometrics. Now try using the DNA sequence as a key -talk about size... (Though I know of some attempts to synthesize through the sequence redundancy to create smaller and more usable keys. -Think DNA/Fingerprint/Biometric databases.)

From: http://www.eogn.com/archives/news0202.htm

The fact that DNA is inherited and that each individual is the product of his/her progenitors means that DNA can be used to not only create unique identifications, but also to identify members of the same family, the same clan or tribal group, or the same population.

I hope I have sufficiently challenged your assertion that names are usable as primary keys.

... which duplicate the real primary keys -- user names.

A Passport number a NOT good Primary Key candidate -many people do not have a passport.

A Driver's License Number is NOT a good Primary Key candidate -many people do not have one.

So the real question is, in my system, for our business needs, what are appropriate surrogate keys?

|||By the character strings, I did not mean the real names. It is clear that real people can be namesakes. But in the internet, rarely anybody uses their numeric ids. For instance, Microsoft uses emails as user identifiers. The DNS maps between real IP addresses and memorizable symbolic names. People enjoy having fancy monikers.
|||Trying to grasp the subtle difference between "is unique" indeces and "unique key" columns, I came across the following text:

SQL Server 2005 Books Online

Unique Index Design Guidelines
Designing and Creating Databases > Indexes > Designing Indexes >

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

The engine designers foresee the situation where there is a redundancy -- two key columns in a table. But they do not explain why there may appear a need for such abnormal relation. Isn't EmployeeID redundant if they are identified by national id?

|||I think you are confusing a "primary key" with a "unique index/constraint".

I "primary key" is the link between other tables. For example, you are not, and should not, be linking Employees to Addressses by the "EmployeeName" field, you use the "EmployeeID".

Also, you NEVER EVER use a link between tables which the user could to change. Otherwise, you need to update every single table which links to the item.

For linking tables, you want very fast linking. Integers are 1000 times faster links than using a character string.

In your case, you are only wanting the "loginname" to be UNIQUE, not the link between tables. They are two totally different things and conform to the normalization rule because you don't duplicate "loginname" in other tables.

|||

Tom Phillips wrote:

I think you are confusing a "primary key" with a "unique index/constraint".

Yes, to things for the same (unique ID) purpose cannot be not confusing.

Tom Phillips wrote:

I "primary key" is the link between other tables. For example, you are not, and should not, be linking Employees to Addressses by the "EmployeeName" field, you use the "EmployeeID".

What is the problem to use the unique key "NationalID" for linking instead of EmploeeID primary key? Please, do not tell that the Microsoft decided so.

Tom Phillips wrote:

Also, you NEVER EVER use a link between tables which the user could to change. Otherwise, you need to update every single table which links to the item.

For linking tables, you want very fast linking. Integers are 1000 times faster links than using a character string.

This is what I wanted to see. It is not obvious, since the hash tables reach O(1) efficiency by hashing strings as well as serial numbers and the speed could easily be of the same order.

Tom Phillips wrote:

In your case, you are only wanting the "loginname" to be UNIQUE, not the link between tables. They are two totally different things and conform to the normalization rule because you don't duplicate "loginname" in other tables.

In my case I want to know the reason for overcomplicating the relations by inferring the redundant IDs to the emails I already have. The redundancy does violate the normalization as I have explained before. Normalization = elimination of redundancy.

|||

Another reason not to use SSN. SSN is not guaranteed to be unique.

Credit agencies got burned by this in the past|||

Valentin,

There are times when it is necessary to handle what you classsify as 'redundancy'. For example, in the USA, it is necessary to have an Employees TaxID number stored and available for government related reports and tax filings. However, that is 'protected' and sensitive data, and therefor 'should' NOT be used as a Primary Key for normal reports and operations. And we don't have 'mandated' NationalID number -yet.

Isn't EmployeeID redundant if they are identified by national id?

So in most situations, there will be an EmployeeID, Primary Key, and also a need to verify and be certain that there are no duplicate TaxIDs. So the Unique Constraint (Unique Index) helps in that respect.

With a Unique Constraint, you DO NOT have to have a value (as a Primary Key requires) -BUT if you do provide a value, it must be UNIQUE.

|||They are not the "same thing". They are used for different purposes. The ONLY thing the same is they are both unique. They are not redundant.

You NEVER, EVER link tables on a user changeable data. In the MS example, I assume, "NationalID" is a user entered field (probably Social Security Number). Although, it needs to be unique, because 2 people cannot have the same number, the user MIGHT change it for many reasons (it was entered wrong or changed for some other reason). Therefore, you do not link tables on the NationalID field. You use the EmployeeID field, which is also unique, but NEVER changes after it is created by the software.

From what you describe in your situation, lets say you have 2 tables:

User:
UserID INT IDENTITY(1,1) PK
UserName varchar(50)
UserEmailAddress varchar(255) (this is the max size of an SMTP email address) UNIQUE

LoginData:
LoginID INT IDENTITY(1,1) PK
UserID INT FK to User
LoginDate DATETIME

What would happen if the user changes his/her email address? The answer, NOTHING.

What I think you are suggesting is the following:

User:
UserID INT IDENTITY(1,1) PK
UserName varchar(50)
UserEmailAddress varchar(255) UNIQUE (this is the max size of an SMTP email address)

LoginData:
LoginID INT IDENTITY(1,1) PK
UserEmailAddress varchar(255) FK to User
LoginDate DATETIME

Now if the email address changes, you need to change every record in LoginData AND you have violated normalization rules by duplicating USER DATA (UserEmailAddress) in your tables. Also, you have made your table linking take 10,000 times longer to search by using a varchar(255) rather than the int.

|||Now, lets further complicate your issue by talking about adding some way to find "Last Login Date".

Normalization would say, you have it:

SELECT MAX(LoginData.LoginDate) FROM LoginData WHERE LoginID=@.LoginID

However, it is a terrible waste of time to scan 1 billion records.

FYI, in MS SQL (but no other engine) you could do:

SELECT TOP 1 LoginData.LoginDate FROM LoginData WHERE LoginID=@.LoginID ORDER BY LoginData.LoginDate DESC

So most people would just add "LastLoginDate datetime" to User and update it when the user logged in.

Yes, it violates normalization rules, but one field is nothing. Then it is 2, then 10, then 100 and then normalization is out the window.

|||

Tom Phillips wrote:

They are not the "same thing". They are used for different purposes. The ONLY thing the same is they are both unique. They are not redundant.

You NEVER, EVER link tables on a user changeable data. In the MS example, I assume, "NationalID" is a user entered field (probably Social Security Number). Although, it needs to be unique, because 2 people cannot have the same number, the user MIGHT change it for many reasons (it was entered wrong or changed for some other reason). Therefore, you do not link tables on the NationalID field. You use the EmployeeID field, which is also unique, but NEVER changes after it is created by the software.

<skip>
Now if the email address changes, you need to change every record in LoginData AND you have violated normalization rules by duplicating USER DATA (UserEmailAddress) in your tables. Also, you have made your table linking take 10,000 times longer to search by using a varchar(255) rather than the int.

The fact that changing primary key (like object address in a program) is problematic does not mean that adding an extra reference you do no incur a redundancy.

Arnie Rowland wrote:

Valentin,

There are times when it is necessary to handle what you classsify as 'redundancy'. For example, in the USA, it is necessary to have an Employees TaxID number stored and available for government related reports and tax filings. However, that is 'protected' and sensitive data, and therefor 'should' NOT be used as a Primary Key for normal reports and operations. And we don't have 'mandated' NationalID number -yet.

But I'm addressing the case when the symbolic name is unique and mandatory. In our app, user logs in by email. In this case, email is not just unique. Since it is used to uniquely identify users, it is a key (nulls are not allowed). The int ID uses the same purpose. It duplicates the email field. The same situations I have in 'groups' table where human-manageable groups must have unique names but we refer the groups by ID. In addition to being redundant, the design overcomplicates the things because user normally wants to see the group (s)he belongs in text rather than an integer id of the group.

Though, the keys are duplicated, I cannot bring an inconsistency example, which proves the redundancy. But 3rd normal form requires the record fields to depend only on the primary key. Meantime, users in my database are identified/refered/addressed/pointed to by email. It is a natural primary key. Integer IDs were artificially introduced to do the same job.

character strings as primary keys

Hello,

How bad is normalizing the database to the 3rd form, which requires that all fields depend on nothing but primary key. Consider the first table you create -- users. They have int primary keys, which duplicate the real primary keys -- user names. When user logs in, the user's entry is uniquely identified by its name, which is not primary key. The fundamental design rule -- avoid redundancy -- is violated. A VERY serious reason should be there for that.

Usually, design is compromised by redundancy for performance. Here, both copies are stored in one remote database, but integer keys may be located/used faster. Additionally, using long string references everywhere instead of short integer keys may save a lot of storage space (additionally increasing speed). How serious these impacts are? Am I missing something?

Usually, login names are not allowed to change. You have problems changing primary keys because all the foreign keys must be updated accordingly. Does it reveal that most user databases use character strings as primary keys?

Valentin,

Names are rarely UNIQUE -a primary requirement for a Primary Key. You only have to look at a telephone book to see that using Names as a Primary Key will be impossible. (Granted, a few 'small' countries mandate name uniqueness.)

From: http://howmanyofme.com/people/John_Smith/

There are 49,842 people in the U.S. named John Smith.

User Names, while sometimes, in small systems, seem unique, and seem suitable for Primary Key usage, will, over time prove to be a significant problem. Then the system will have to devolve to using ( Name + identifier ), e.g., JohnSmith1, JohnSmith2, etc. I have found that it is so problemmatic to use Names as keys that is is not worth even considering. As you indicate, Names 'should' allow for changes, i.e, marriage, divorce, etc. But the Primary Key 'should not' change. If you consider the domain security system, the User Name is NOT the Primary Key, and the Login Name is changable. The Primary Key is the SID, a unique identifier normally hidden from view, somewhat like using a IDENTITY field (or other indentifier) -in fact, a surrogate key. (Beware: simplistic explanition.)

It is extremely difficult to design a system for identifying People with a 'natural' Primary Key. Some adamately espouse using the SSN (in the USA) as a Primary Key. But it is NOT a naturally occuring characteristic of the individual -it is a surrogate key created by a remote system. It is legally protected, and increased being enforced, as 'sensitive' data. Most data systems 'should' NOT use the SSN as a Primary Key -of course, exceptions are allowed, or required, by law. So what does that leave as a naturally occuring characteristic of the entity -nothing really, except perhaps biometrics. Now try using the DNA sequence as a key -talk about size... (Though I know of some attempts to synthesize through the sequence redundancy to create smaller and more usable keys. -Think DNA/Fingerprint/Biometric databases.)

From: http://www.eogn.com/archives/news0202.htm

The fact that DNA is inherited and that each individual is the product of his/her progenitors means that DNA can be used to not only create unique identifications, but also to identify members of the same family, the same clan or tribal group, or the same population.

I hope I have sufficiently challenged your assertion that names are usable as primary keys.

... which duplicate the real primary keys -- user names.

A Passport number a NOT good Primary Key candidate -many people do not have a passport.

A Driver's License Number is NOT a good Primary Key candidate -many people do not have one.

So the real question is, in my system, for our business needs, what are appropriate surrogate keys?

|||By the character strings, I did not mean the real names. It is clear that real people can be namesakes. But in the internet, rarely anybody uses their numeric ids. For instance, Microsoft uses emails as user identifiers. The DNS maps between real IP addresses and memorizable symbolic names. People enjoy having fancy monikers.
|||Trying to grasp the subtle difference between "is unique" indeces and "unique key" columns, I came across the following text:

SQL Server 2005 Books Online

Unique Index Design Guidelines
Designing and Creating Databases > Indexes > Designing Indexes >

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

The engine designers foresee the situation where there is a redundancy -- two key columns in a table. But they do not explain why there may appear a need for such abnormal relation. Isn't EmployeeID redundant if they are identified by national id?

|||I think you are confusing a "primary key" with a "unique index/constraint".

I "primary key" is the link between other tables. For example, you are not, and should not, be linking Employees to Addressses by the "EmployeeName" field, you use the "EmployeeID".

Also, you NEVER EVER use a link between tables which the user could to change. Otherwise, you need to update every single table which links to the item.

For linking tables, you want very fast linking. Integers are 1000 times faster links than using a character string.

In your case, you are only wanting the "loginname" to be UNIQUE, not the link between tables. They are two totally different things and conform to the normalization rule because you don't duplicate "loginname" in other tables.

|||

Tom Phillips wrote:

I think you are confusing a "primary key" with a "unique index/constraint".

Yes, to things for the same (unique ID) purpose cannot be not confusing.

Tom Phillips wrote:

I "primary key" is the link between other tables. For example, you are not, and should not, be linking Employees to Addressses by the "EmployeeName" field, you use the "EmployeeID".

What is the problem to use the unique key "NationalID" for linking instead of EmploeeID primary key? Please, do not tell that the Microsoft decided so.

Tom Phillips wrote:

Also, you NEVER EVER use a link between tables which the user could to change. Otherwise, you need to update every single table which links to the item.

For linking tables, you want very fast linking. Integers are 1000 times faster links than using a character string.

This is what I wanted to see. It is not obvious, since the hash tables reach O(1) efficiency by hashing strings as well as serial numbers and the speed could easily be of the same order.

Tom Phillips wrote:

In your case, you are only wanting the "loginname" to be UNIQUE, not the link between tables. They are two totally different things and conform to the normalization rule because you don't duplicate "loginname" in other tables.

In my case I want to know the reason for overcomplicating the relations by inferring the redundant IDs to the emails I already have. The redundancy does violate the normalization as I have explained before. Normalization = elimination of redundancy.

|||

Another reason not to use SSN. SSN is not guaranteed to be unique.

Credit agencies got burned by this in the past|||

Valentin,

There are times when it is necessary to handle what you classsify as 'redundancy'. For example, in the USA, it is necessary to have an Employees TaxID number stored and available for government related reports and tax filings. However, that is 'protected' and sensitive data, and therefor 'should' NOT be used as a Primary Key for normal reports and operations. And we don't have 'mandated' NationalID number -yet.

Isn't EmployeeID redundant if they are identified by national id?

So in most situations, there will be an EmployeeID, Primary Key, and also a need to verify and be certain that there are no duplicate TaxIDs. So the Unique Constraint (Unique Index) helps in that respect.

With a Unique Constraint, you DO NOT have to have a value (as a Primary Key requires) -BUT if you do provide a value, it must be UNIQUE.

|||They are not the "same thing". They are used for different purposes. The ONLY thing the same is they are both unique. They are not redundant.

You NEVER, EVER link tables on a user changeable data. In the MS example, I assume, "NationalID" is a user entered field (probably Social Security Number). Although, it needs to be unique, because 2 people cannot have the same number, the user MIGHT change it for many reasons (it was entered wrong or changed for some other reason). Therefore, you do not link tables on the NationalID field. You use the EmployeeID field, which is also unique, but NEVER changes after it is created by the software.

From what you describe in your situation, lets say you have 2 tables:

User:
UserID INT IDENTITY(1,1) PK
UserName varchar(50)
UserEmailAddress varchar(255) (this is the max size of an SMTP email address) UNIQUE

LoginData:
LoginID INT IDENTITY(1,1) PK
UserID INT FK to User
LoginDate DATETIME

What would happen if the user changes his/her email address? The answer, NOTHING.

What I think you are suggesting is the following:

User:
UserID INT IDENTITY(1,1) PK
UserName varchar(50)
UserEmailAddress varchar(255) UNIQUE (this is the max size of an SMTP email address)

LoginData:
LoginID INT IDENTITY(1,1) PK
UserEmailAddress varchar(255) FK to User
LoginDate DATETIME

Now if the email address changes, you need to change every record in LoginData AND you have violated normalization rules by duplicating USER DATA (UserEmailAddress) in your tables. Also, you have made your table linking take 10,000 times longer to search by using a varchar(255) rather than the int.

|||Now, lets further complicate your issue by talking about adding some way to find "Last Login Date".

Normalization would say, you have it:

SELECT MAX(LoginData.LoginDate) FROM LoginData WHERE LoginID=@.LoginID

However, it is a terrible waste of time to scan 1 billion records.

FYI, in MS SQL (but no other engine) you could do:

SELECT TOP 1 LoginData.LoginDate FROM LoginData WHERE LoginID=@.LoginID ORDER BY LoginData.LoginDate DESC

So most people would just add "LastLoginDate datetime" to User and update it when the user logged in.

Yes, it violates normalization rules, but one field is nothing. Then it is 2, then 10, then 100 and then normalization is out the window.

|||

Tom Phillips wrote:

They are not the "same thing". They are used for different purposes. The ONLY thing the same is they are both unique. They are not redundant.

You NEVER, EVER link tables on a user changeable data. In the MS example, I assume, "NationalID" is a user entered field (probably Social Security Number). Although, it needs to be unique, because 2 people cannot have the same number, the user MIGHT change it for many reasons (it was entered wrong or changed for some other reason). Therefore, you do not link tables on the NationalID field. You use the EmployeeID field, which is also unique, but NEVER changes after it is created by the software.

<skip>
Now if the email address changes, you need to change every record in LoginData AND you have violated normalization rules by duplicating USER DATA (UserEmailAddress) in your tables. Also, you have made your table linking take 10,000 times longer to search by using a varchar(255) rather than the int.

The fact that changing primary key (like object address in a program) is problematic does not mean that adding an extra reference you do no incur a redundancy.

Arnie Rowland wrote:

Valentin,

There are times when it is necessary to handle what you classsify as 'redundancy'. For example, in the USA, it is necessary to have an Employees TaxID number stored and available for government related reports and tax filings. However, that is 'protected' and sensitive data, and therefor 'should' NOT be used as a Primary Key for normal reports and operations. And we don't have 'mandated' NationalID number -yet.

But I'm addressing the case when the symbolic name is unique and mandatory. In our app, user logs in by email. In this case, email is not just unique. Since it is used to uniquely identify users, it is a key (nulls are not allowed). The int ID uses the same purpose. It duplicates the email field. The same situations I have in 'groups' table where human-manageable groups must have unique names but we refer the groups by ID. In addition to being redundant, the design overcomplicates the things because user normally wants to see the group (s)he belongs in text rather than an integer id of the group.

Though, the keys are duplicated, I cannot bring an inconsistency example, which proves the redundancy. But 3rd normal form requires the record fields to depend only on the primary key. Meantime, users in my database are identified/refered/addressed/pointed to by email. It is a natural primary key. Integer IDs were artificially introduced to do the same job.