manage identity field at subscriber

Anyone has the standard approach for handling the identity column with replication environment.
Currently i am using following process.

http://www.databasejournal.com/features/mssql/article.php/3483421

I want to know if someone have some generic script to do this or much easier way..So your not using Auto_ID's ? & why not ?

Which Version of SQL you using ?

GW|||SQL Server 2000 SP3sql

Making Identity to be Not for replication

Hello there
I have many tables on my database (Approx 500)
Many of them have identity column and none of them are set for Not for
replication
Is there a way to change it by code automaticly?
try this
http://groups.google.com/group/micro...5?dmode=source
Hilary Cotter
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:e5gD6%23vAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I have many tables on my database (Approx 500)
> Many of them have identity column and none of them are set for Not for
> replication
> Is there a way to change it by code automaticly?
>

Making Excel Source case insensitive

Hi:

When I import data from multiple excel files, the Excel source gives validation errors if the case of a column name changes.

How can I make my excel source Case Insensitive?

TIA

Kar

I don't know of any way to do this, but it should be possible to "pre-process" the Excel spreadsheet to LOWER() the column names before running the data flow by using the Script task and the Excel object model.|||

Well, I could do that, but I hoped there was an extended property or something to set the Engine to Case-insensitive mode.

Anyways, I have a workaround. Use a SQL Query or a SQL Query from variable, and the case that you have in the query will hold for the rest of the dataflow, no matter what the source excel contains in its first row.

Thanx

Kar

|||If there is, I'm unaware of it. I'll bow out and we can see what others have to say. Good luck!

Making an IDENTITY column

Hi,
I have a field in my sql table called ORDER_NO which is also the primary
key. Now, I want to add amother field called AO_Number whose valued increment
whenever a record is added . Those values should be AO-1, AO-2, AO-3...& so
on...
How to have these values for this field coz making it an identity column
makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
Is this possible? & how?
pmud
You could have an identity column AND a calculated column that combined the
identity with the value you wanted.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> Hi,
> I have a field in my sql table called ORDER_NO which is also the primary
> key. Now, I want to add amother field called AO_Number whose valued
increment
> whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
so
> on...
> How to have these values for this field coz making it an identity column
> makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> Is this possible? & how?
> --
> pmud
|||Hi Mike,
Where do I have to write the sql code for creating a calculated column? In
user defined functions?
Thanks
"Mike Jansen" wrote:

> You could have an identity column AND a calculated column that combined the
> identity with the value you wanted.
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> increment
> so
>
>
|||Here is how to do it. You can also create a view.
alter your_table
add ao_number int not null identity(1, 1)
go
alter your_table
all ao_formatted_number as 'AO-' + ltrim(ao_number)
go
selet * from your_table
go
AMB
"pmud" wrote:
[vbcol=seagreen]
> Hi Mike,
> Where do I have to write the sql code for creating a calculated column? In
> user defined functions?
> Thanks
> "Mike Jansen" wrote:
|||Here is an example:
CREATE TABLE tbl (
key_col INT NOT NULL PRIMARY KEY,
id_col INT NOT NULL IDENTITY,
calc_col AS 'AO-' + CAST( id_col AS VARCHAR ), -- calculated column
...)
If you are looking for a truly monotonic sequence, avoid identity. There are
certain instances where identity column can have gaps its values. If the
value is something that can be derived based on some collating sequence of
existing values in other columns, consider using a ranking mechanism like
the one detailed in KBA 186133. Another alternative, is to use a view which
can generate the sequential values based on existing columns without
exposing its complexity.
Anith
|||Hi Aljandro,
That solved my problem.
Thanks
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Here is how to do it. You can also create a view.
> alter your_table
> add ao_number int not null identity(1, 1)
> go
> alter your_table
> all ao_formatted_number as 'AO-' + ltrim(ao_number)
> go
> selet * from your_table
> go
>
> AMB
> "pmud" wrote:

Making an IDENTITY column

Hi,
I have a field in my sql table called ORDER_NO which is also the primary
key. Now, I want to add amother field called AO_Number whose valued incremen
t
whenever a record is added . Those values should be AO-1, AO-2, AO-3...& so
on...
How to have these values for this field coz making it an identity column
makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
Is this possible? & how?
--
pmudYou could have an identity column AND a calculated column that combined the
identity with the value you wanted.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> Hi,
> I have a field in my sql table called ORDER_NO which is also the primary
> key. Now, I want to add amother field called AO_Number whose valued
increment
> whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
so
> on...
> How to have these values for this field coz making it an identity column
> makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> Is this possible? & how?
> --
> pmud|||Hi Mike,
Where do I have to write the sql code for creating a calculated column? In
user defined functions?
Thanks
"Mike Jansen" wrote:

> You could have an identity column AND a calculated column that combined th
e
> identity with the value you wanted.
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> increment
> so
>
>|||Here is how to do it. You can also create a view.
alter your_table
add ao_number int not null identity(1, 1)
go
alter your_table
all ao_formatted_number as 'AO-' + ltrim(ao_number)
go
selet * from your_table
go
AMB
"pmud" wrote:
[vbcol=seagreen]
> Hi Mike,
> Where do I have to write the sql code for creating a calculated column? I
n
> user defined functions?
> Thanks
> "Mike Jansen" wrote:
>|||Here is an example:
CREATE TABLE tbl (
key_col INT NOT NULL PRIMARY KEY,
id_col INT NOT NULL IDENTITY,
calc_col AS 'AO-' + CAST( id_col AS VARCHAR ), -- calculated column
..)
If you are looking for a truly monotonic sequence, avoid identity. There are
certain instances where identity column can have gaps its values. If the
value is something that can be derived based on some collating sequence of
existing values in other columns, consider using a ranking mechanism like
the one detailed in KBA 186133. Another alternative, is to use a view which
can generate the sequential values based on existing columns without
exposing its complexity.
Anith|||Hi Aljandro,
That solved my problem.
Thanks
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Here is how to do it. You can also create a view.
> alter your_table
> add ao_number int not null identity(1, 1)
> go
> alter your_table
> all ao_formatted_number as 'AO-' + ltrim(ao_number)
> go
> selet * from your_table
> go
>
> AMB
> "pmud" wrote:
>

Making an IDENTITY column

Hi,
I have a field in my sql table called ORDER_NO which is also the primary
key. Now, I want to add amother field called AO_Number whose valued increment
whenever a record is added . Those values should be AO-1, AO-2, AO-3...& so
on...
How to have these values for this field coz making it an identity column
makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
Is this possible? & how?
--
pmudYou could have an identity column AND a calculated column that combined the
identity with the value you wanted.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> Hi,
> I have a field in my sql table called ORDER_NO which is also the primary
> key. Now, I want to add amother field called AO_Number whose valued
increment
> whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
so
> on...
> How to have these values for this field coz making it an identity column
> makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> Is this possible? & how?
> --
> pmud|||Hi Mike,
Where do I have to write the sql code for creating a calculated column? In
user defined functions?
Thanks
"Mike Jansen" wrote:
> You could have an identity column AND a calculated column that combined the
> identity with the value you wanted.
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > Hi,
> >
> > I have a field in my sql table called ORDER_NO which is also the primary
> > key. Now, I want to add amother field called AO_Number whose valued
> increment
> > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> so
> > on...
> >
> > How to have these values for this field coz making it an identity column
> > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> >
> > Is this possible? & how?
> > --
> > pmud
>
>|||Here is how to do it. You can also create a view.
alter your_table
add ao_number int not null identity(1, 1)
go
alter your_table
all ao_formatted_number as 'AO-' + ltrim(ao_number)
go
selet * from your_table
go
AMB
"pmud" wrote:
> Hi Mike,
> Where do I have to write the sql code for creating a calculated column? In
> user defined functions?
> Thanks
> "Mike Jansen" wrote:
> > You could have an identity column AND a calculated column that combined the
> > identity with the value you wanted.
> >
> > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > > Hi,
> > >
> > > I have a field in my sql table called ORDER_NO which is also the primary
> > > key. Now, I want to add amother field called AO_Number whose valued
> > increment
> > > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> > so
> > > on...
> > >
> > > How to have these values for this field coz making it an identity column
> > > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> > >
> > > Is this possible? & how?
> > > --
> > > pmud
> >
> >
> >|||Here is an example:
CREATE TABLE tbl (
key_col INT NOT NULL PRIMARY KEY,
id_col INT NOT NULL IDENTITY,
calc_col AS 'AO-' + CAST( id_col AS VARCHAR ), -- calculated column
...)
If you are looking for a truly monotonic sequence, avoid identity. There are
certain instances where identity column can have gaps its values. If the
value is something that can be derived based on some collating sequence of
existing values in other columns, consider using a ranking mechanism like
the one detailed in KBA 186133. Another alternative, is to use a view which
can generate the sequential values based on existing columns without
exposing its complexity.
--
Anith|||Hi Aljandro,
That solved my problem.
Thanks
"Alejandro Mesa" wrote:
> Here is how to do it. You can also create a view.
> alter your_table
> add ao_number int not null identity(1, 1)
> go
> alter your_table
> all ao_formatted_number as 'AO-' + ltrim(ao_number)
> go
> selet * from your_table
> go
>
> AMB
> "pmud" wrote:
> > Hi Mike,
> >
> > Where do I have to write the sql code for creating a calculated column? In
> > user defined functions?
> >
> > Thanks
> >
> > "Mike Jansen" wrote:
> >
> > > You could have an identity column AND a calculated column that combined the
> > > identity with the value you wanted.
> > >
> > > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > > news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a field in my sql table called ORDER_NO which is also the primary
> > > > key. Now, I want to add amother field called AO_Number whose valued
> > > increment
> > > > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> > > so
> > > > on...
> > > >
> > > > How to have these values for this field coz making it an identity column
> > > > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> > > >
> > > > Is this possible? & how?
> > > > --
> > > > pmud
> > >
> > >
> > >

Making an Existing column an Identity column

Hi,

I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.

I tried

alter table <table_name>
alter column <col_name> int Identity (1,1)

but it fails.

Ajay

WORD4LIFE
(http://www.word4life.com)Nope...can't do it...

If you have a table with a int column and want to change it...you can graphically do it in EM...

But guess what it's really doing...

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1a
(
col1 int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a ON
GO
IF EXISTS(SELECT * FROM dbo.Table1a)
EXEC('INSERT INTO dbo.Tmp_Table1a (col1)
SELECT col1 FROM dbo.Table1a TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a OFF
GO
DROP TABLE dbo.Table1a
GO
EXECUTE sp_rename N'dbo.Tmp_Table1a', N'Table1a', 'OBJECT'
GO
GRANT SELECT ON dbo.Table1a TO public AS dbo
COMMIT

Just make sure you don't already have a temp_table...

What a hack

M$ is good at them...lots of practice...|||Look it up in BOL

TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.

And the IDENTITY_INSERTS Are inb the correct order...

btw, sql server itself generated it...look a save sql server change script in EM after you make a change to a table...sql

making a set of possible values for a column

I use to know how to do this in oracle, but cant remember... how do i set up
a constraint on a column in a table so it can say only be
"START","STOP","INPROG" only as the text that column can have? thanks!Brian
Does it relate to SQL Server ?
"Brian Henry" <nospam@.nospam.com> wrote in message
news:OZNGmLq7FHA.4076@.tk2msftngp13.phx.gbl...
>I use to know how to do this in oracle, but cant remember... how do i set
>up a constraint on a column in a table so it can say only be
>"START","STOP","INPROG" only as the text that column can have? thanks!
>|||yes? why wouldn't a constraint on a column relate to sql server
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uIV5pPq7FHA.2176@.TK2MSFTNGP14.phx.gbl...
> Brian
> Does it relate to SQL Server ?
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:OZNGmLq7FHA.4076@.tk2msftngp13.phx.gbl...
>|||I not 100% shure but something like this should work !ALTER TABLE your_table
ADD CONSTRAINT CK_emp_id CHECK (colum_name='START' or colum_name='STOP' or
colum_name='INPROG')
Regards,Predrag Stojanovic"Brian Henry" <nospam@.nospam.com> wrote in message
news:OZNGmLq7FHA.4076@.tk2msftngp13.phx.gbl...
> I use to know how to do this in oracle, but cant remember... how do i set
up
> a constraint on a column in a table so it can say only be
> "START","STOP","INPROG" only as the text that column can have? thanks!
>|||Seems that you are looking for CHECK constraints.
Here is details about Check Constraint from BOL:
CHECK constraints enforce domain integrity by limiting the values that are
accepted by a column. They are similar to FOREIGN KEY constraints in that
they control the values that are placed in a column. The difference is in ho
w
they determine which values are valid: FOREIGN KEY constraints get the list
of valid values from another table, and CHECK constraints determine the vali
d
values from a logical expression that is not based on data in another column
.
For example, it is possible to limit the range of values for a salary column
by creating a CHECK constraint that allows only data that ranges from $15,00
0
through $100,000. This prevents salaries from being entered beyond the norma
l
salary range.
You can create a CHECK constraint with any logical (Boolean) expression that
returns TRUE or FALSE based on the logical operators. For the previous
example, the logical expression is:
salary >= 15000 AND salary <= 100000
And here is an example from BOL:
This example specifies that the pub_id must be within a specific list or
follow a given pattern. This constraint is for the pub_id of the publishers
table.
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]')
"Brian Henry" wrote:

> yes? why wouldn't a constraint on a column relate to sql server
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uIV5pPq7FHA.2176@.TK2MSFTNGP14.phx.gbl...
>
>|||thanks thats what i was looking for
"Absar Ahmad" <AbsarAhmad@.discussions.microsoft.com> wrote in message
news:CBFE5462-107B-439B-B980-60882F0CA9FA@.microsoft.com...
> Seems that you are looking for CHECK constraints.
> Here is details about Check Constraint from BOL:
> CHECK constraints enforce domain integrity by limiting the values that are
> accepted by a column. They are similar to FOREIGN KEY constraints in that
> they control the values that are placed in a column. The difference is in
> how
> they determine which values are valid: FOREIGN KEY constraints get the
> list
> of valid values from another table, and CHECK constraints determine the
> valid
> values from a logical expression that is not based on data in another
> column.
> For example, it is possible to limit the range of values for a salary
> column
> by creating a CHECK constraint that allows only data that ranges from
> $15,000
> through $100,000. This prevents salaries from being entered beyond the
> normal
> salary range.
> You can create a CHECK constraint with any logical (Boolean) expression
> that
> returns TRUE or FALSE based on the logical operators. For the previous
> example, the logical expression is:
> salary >= 15000 AND salary <= 100000
> And here is an example from BOL:
> This example specifies that the pub_id must be within a specific list or
> follow a given pattern. This constraint is for the pub_id of the
> publishers
> table.
> CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
> OR pub_id LIKE '99[0-9][0-9]')
> "Brian Henry" wrote:
>sql

Making a row not show

I have one column that is an expression that either returns a value or
returns zero. I would like if it is zero to not show at all. How is this
done?
Thanks,
RyanGo to properties for that column, click on Visibility tab. Click on
Expression radio button and enter this formula:
=iif (Fields!<expression>.Value = 0, True, False)
you may have to map the expression to a data set row before this will work.
I have made it work with parameter and field values.
"Ryan Mcbee" wrote:
> I have one column that is an expression that either returns a value or
> returns zero. I would like if it is zero to not show at all. How is this
> done?
> Thanks,
> Ryan|||Carl,
I tried this and it didn't work. I think because I am using a matrix
report. Would have to put this formula in every column since it is a matrix
report?
Thanks,
Ryan
"Carl Henthorn" wrote:
> Go to properties for that column, click on Visibility tab. Click on
> Expression radio button and enter this formula:
> =iif (Fields!<expression>.Value = 0, True, False)
> you may have to map the expression to a data set row before this will work.
> I have made it work with parameter and field values.
> "Ryan Mcbee" wrote:
> > I have one column that is an expression that either returns a value or
> > returns zero. I would like if it is zero to not show at all. How is this
> > done?
> >
> > Thanks,
> > Ryan

Making a Non Primary key a unique column

I have a table in which a column that is not part of the primary key must
contain data that can not be duplicated in the same column in another row
but it should not - for other design reasons - be the primary key.
I created an index on that field in the table and in the properties for that
index I checked Create Unique and checked Constraint. I thought that that
would prevent entering duplicate values in that field in that table.
However, when I tested this in the table in the data entry screen of
Enterprise manager, I was able to enter duplicate values in that field in
several rows in that table and the database did not return any error
messages.
What's wrong here, can anyone shed light on this behaviour?
How do I achieve the goal set out above?
The field giving me the problem is an nvarchar type max 50 length.
Thanks for any help,
RDIt is difficult for us to answer without knowing exactly what you did and ho
w we can reproduce it.
Can you post CREATE TABLE, CREATE INDEX or ALTER TABLE ADD UNIQUE CONSTRAINT
with some insert
statements we can run to reproduce the behavior?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RD" <nospam@.nospam.net> wrote in message news:%23hgdDzSaFHA.2996@.TK2MSFTNGP10.phx.gbl...[c
olor=darkred]
>I have a table in which a column that is not part of the primary key must
> contain data that can not be duplicated in the same column in another row
> but it should not - for other design reasons - be the primary key.
> I created an index on that field in the table and in the properties for th
at
> index I checked Create Unique and checked Constraint. I thought that that
> would prevent entering duplicate values in that field in that table.
> However, when I tested this in the table in the data entry screen of
> Enterprise manager, I was able to enter duplicate values in that field in
> several rows in that table and the database did not return any error
> messages.
> What's wrong here, can anyone shed light on this behaviour?
> How do I achieve the goal set out above?
> The field giving me the problem is an nvarchar type max 50 length.
> Thanks for any help,
> RD
>
>[/color]|||A UNIQUE constraint should ineed prevent duplicate values. I suspect the
constraint has not been created as you wanted it. In Query Analyzer you can
easily generate the script for the constraint so that you can verify it and
edit it as necessary (right-click on the constraint in the Object Browser,
then click Script Object to New Window As > Create).
One reason I prefer to use QA rather than EM for any structure changes is
that you have better control and visibility over what is happening. However,
you can do a similar thing in EM when you change something in the Table
Designer. You can click the Save Change Script button on the toolbar (3rd
one along) to show you the actual script that will make the changes. The
complete change script EM generates is harder to read however than the
equivalent in QA.
Most of us will be glad when the EM/QA duality disappears in SQL2005 to be
replaced by a single place for all management and development tasks.
David Portas
SQL Server MVP
--|||You might want to create the table in QA with DDL and use the UNIQUE
constraint. This will document your design better. I have no idea why
EM would not do this properly.|||Thanks to all for your explanations.
Indeed it works properly as explained by you and the docs.
This morning I just tried again and realized that the duplicate data I
thought I entered was not EXACTLY duplicate after all, hence my mistaken
belief that it didn't work.
As usual the problem is 18 inches from the screen.
Sorry to have disturbed you like that, comes with old age I guess. Can't
stay up late anymore and do anything worthwhile ;-)
RD.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117914232.752590.247840@.g49g2000cwa.googlegroups.com...
> You might want to create the table in QA with DDL and use the UNIQUE
> constraint. This will document your design better. I have no idea why
> EM would not do this properly.
>

Making a column's values unique

I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!
http://www.sql-server-performance.com/rd_delete_duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>
|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.com/rd_delete_duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>
|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?

Making a column's values unique

I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!http://www.sql-server-performance.c..._duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.c..._duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?

Making a column's values unique

I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!http://www.sql-server-performance.com/rd_delete_duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.com/rd_delete_duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>>I have an INTEGER column that I want to convert to a primary key. However,
>>some of the values currently in the dataset are not unique. What query
>>will delete any records with duplicate values of the intended index?
>> Many thanks!
>|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?

make XML column not case sensitive

Is there any way that an xml column in sql can be made to not be case sensitive?

So you can search for the text() of a node matching say 'open' (properties.exist('property[. = "open"]') and get all results back where the property text = "OPEN" or any other not case sensitive combination of "open".

Collations are not supported in SQL Server 2005 for XQuery. In order to perform your collation-sensitive comparison, you should promote the value out of the XML instance using the .value() method and perform the comparison in T-SQL.
|||

If you find that you like to see such functionality added in a future release, please file a request in the SQL Server Feedback Center @. http://lab.msdn.microsoft.com/productfeedback/

Best regards
Michael

sql

Make the series stand by themselves in stacked column graph

I'm making some reports that are made for getting printed. I currently have three groups of reports, each group having four charts (four A4 papers/group). To cut down on the sheer number of papers on the boards I've been asked to merge these charts so that we'll have all three groups in one chart. This works fine for three of the charts (two line charts and one column chart), where the results gets neatly organized in group order. Where I ran into problems was the last chart..

The last chart is a stacked column chart. With one group, it works quite well; the x-axis is by year/week, and for each week there's a bar where the lower part is "done" and the upper part is "failed".
Now when doing this graph with multiple groups (groups as series), it doesn't put the different groups beside each other as I would've wished, instead it piles them on top of each other as if it was a 100% stacked column.

So the question is; is there some way to make a stacked column graph with multiple series that are one stacked bar per group instead of having all of them stacked on top of each other? Am I just missing some small detail here?What you are observing is the nature of stacked charts--the values for each category are stacked on top of each other. Unfortunately, what you are looking for, having multiple stacked columns for the same category, is not possible.|||Can you do a column graph and have some bars as a single bar and other bars be stacked?

Make Text File as DataSource in Crystal Report 10

Dear all,

We have Crystal Report 10.
And we want to make a text file (with column separator : | ) as a data source for the crystal report 10.
We check available datasource but we can not find suitable way.
And we do not see any option for text file in ODBC.

Once we were successful but it reads the whole row without splitting it into columns based on "|" sign.

And we do not want to use VB programming.

Pls help.

Thanks
hendysearch here
http://support.businessobjects.com|||You can try creating an ODBC connection to the text file with the text driver. I don't know how successful it will be, but you could try.

Make subtotal column widths bigger than normal columns?

The reason I say this is because a subtotal of a dollar amount will take up more space than other values. Right now, I'm forced to make all columns the same larger width because it appears to be all wrapped into 1 column width setting. I can try to change the value of the subtotal column, "matrixcolumn4", but it reverts to the other value after I press enter to apply the changes.

Sorry there is no good solution at this point. One alternative approach is to use side-by-side matrices to a subtotal column with different width, however you would need to "hide" the row headers of the second matrix.

For the next major SSRS release we are looking into significantly improved support of these scenarios.

-- Robert

Make subtotal column widths bigger than normal columns?

The reason I say this is because a subtotal of a dollar amount will take up more space than other values. Right now, I'm forced to make all columns the same larger width because it appears to be all wrapped into 1 column width setting. I can try to change the value of the subtotal column, "matrixcolumn4", but it reverts to the other value after I press enter to apply the changes.

Sorry there is no good solution at this point. One alternative approach is to use side-by-side matrices to a subtotal column with different width, however you would need to "hide" the row headers of the second matrix.

For the next major SSRS release we are looking into significantly improved support of these scenarios.

-- Robert

Make row data column data??

I need to make a report that looks like...
Statistic A Statistic B Statistic C Statistic D
----
99 07 102 91
It would be easy but the values are all in one column in the table, like...
KeyValue | StatisticCode | StatisticValue
001| A| 99
002| B| 07
003| D| 91
004| C| 102
What's the best way to do this?, we have several reports that we need to
build like this.
Thanks,
ShawnHave you tried using the matrix control?
"sysdesigner" wrote:
> I need to make a report that looks like...
> Statistic A Statistic B Statistic C Statistic D
> ----
> 99 07 102 91
>
> It would be easy but the values are all in one column in the table, like...
> KeyValue | StatisticCode | StatisticValue
> 001| A| 99
> 002| B| 07
> 003| D| 91
> 004| C| 102
>
> What's the best way to do this?, we have several reports that we need to
> build like this.
>
> Thanks,
> Shawn
>