Duplicates

Hi all,
This works fine to show duplicates of NumbB Column. I want to show the
duplicates and = different lastnames with those that
are duplicates.
Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
where NumbB in(select NumbB from PC
group by NumbB
having count(NumbB)>1)order by NumbB
thanks
GVOne general approach is:
SELECT * -- use column names
FROM tbl t1
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.col <= t1.col ) >= 2 ;
As a side note, for such questions, always post table DDLs & sample data
along with expected results so that others can understand your requirements
better.
Anith|||> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
Can you rephrase what you want?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"gv" wrote:

> Hi all,
> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
> Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
> where NumbB in(select NumbB from PC
> group by NumbB
> having count(NumbB)>1)order by NumbB
> thanks
> GV
>
>|||Hi again and thanks
Ok, thanks for your help
I want to pull duplicates of Col2, order them by Col2 and were
Col3(lastname) is the same within the duplicates.
SELECT Col1,Col2,Col3,Col4,Col5
FROM Table1
WHERE Col2 IN(SELECT Col2 FROM Table1
GROUP BY Col2
HAVING COUNT(Col2)>1)
ORDER BY Col2
Sample rows
Col1 Col2 Col3 Col4 Col5
532 000 doe J Intraductal EUS
8675 000 loe S Gastric
266 240 Smith D Pancreatic
266 240 Smith D Pancreatic
193 029 VuV N Esophagus
193 029 VuV N Esophagus
836 632 QQQ F Gastric
So would like the return to be:
Col1 Col2 Col3 Col4 Col5
266 240 Smith D Pancreatic
266 240 Smith D Pancreatic
193 029 VuV N Esophagus
193 029 VuV N Esophagus
thanks
GV
"gv" <viatorg@.musc.edu> wrote in message
news:eCNoS2kAFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> This works fine to show duplicates of NumbB Column. I want to show the
> duplicates and = different lastnames with those that
> are duplicates.
> Select id,NumbA,NumbB,Lastname,firstname,typepr
oc,dateofex from PC
> where NumbB in(select NumbB from PC
> group by NumbB
> having count(NumbB)>1)order by NumbB
> thanks
> GV
>|||Try (not tested),
SELECT a.Col1,a.Col2,a.Col3,a.Col4,a.Col5
FROM
Table1 as a
inner join
(
SELECT Col2, col5
FROM Table1
GROUP BY Col2, col5
HAVING COUNT(*) > 1
) as b
on a.col2 = b.col2 and a.col5 = b.col5
order by
a.col2, a.col5
AMB
"gv" wrote:

> Hi again and thanks
>
> Ok, thanks for your help
> I want to pull duplicates of Col2, order them by Col2 and were
> Col3(lastname) is the same within the duplicates.
>
> SELECT Col1,Col2,Col3,Col4,Col5
> FROM Table1
> WHERE Col2 IN(SELECT Col2 FROM Table1
> GROUP BY Col2
> HAVING COUNT(Col2)>1)
> ORDER BY Col2
> Sample rows
> Col1 Col2 Col3 Col4 Col5
> 532 000 doe J Intraductal EUS
> 8675 000 loe S Gastric
> 266 240 Smith D Pancreatic
> 266 240 Smith D Pancreatic
> 193 029 VuV N Esophagus
> 193 029 VuV N Esophagus
> 836 632 QQQ F Gastric
> So would like the return to be:
> Col1 Col2 Col3 Col4 Col5
> 266 240 Smith D Pancreatic
> 266 240 Smith D Pancreatic
> 193 029 VuV N Esophagus
> 193 029 VuV N Esophagus
>
> thanks
> GV
>
>
>
>
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:eCNoS2kAFHA.3236@.TK2MSFTNGP15.phx.gbl...
>
>

Duplicate UID in PK column

Please excuse my noviceness, but how is it possible that I
can have duplicate records that are exactly the same in
every way when there is a PK field that is meant to be
unique?
Because this has happened, I've had to remove and re-index.
Can anyone please help this novice?That shouldn't happen, and I've never heard that it really happened. But it
is difficult to do anything or troubleshoot if you don't have a repro or
don't have the data anymore.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Benjamin" <ben.jones@.trendwest.com.au> wrote in message
news:2c7301c3a8ad$61c25480$3101280a@.phx.gbl...
> Please excuse my noviceness, but how is it possible that I
> can have duplicate records that are exactly the same in
> every way when there is a PK field that is meant to be
> unique?
> Because this has happened, I've had to remove and re-index.
> Can anyone please help this novice?

Duplicate strings

I have a column called professional_degrees in my table
and it has the following data

professional_degrees

bs,ms,mba
bs,mba,
bs,ms,bs,ms
mba,mba
bs,mba

In the above data u can see some of the degrees are repeated.So how can i find them and delete them from the table.

Thanks.You have to normalize you data if you can - otherwise use charindex and replace functions for updating. I do not envy you ;) in last case.|||I was talking abt the duplicates in the same string,not the duplicate recs in database.|||Do you know if they all are comma delimited?

In any event, you'll have to do something like

Parse out every row, 1 by 1, into n rows into a temp table
Then SELECT Distinct
Then INSERT into a new table
The UPDATE the original

probably need a function for the parsing...

just a guess...|||Ok, borrowing from a previous post (http://www.dbforums.com/t997070.html), you can get the parser. You could then create a "glue distinct" function something like:CREATE FUNCTION dbo.fGlue(@.original VARCHAR(8000), @.splitter VARCHAR(8000))
RETURNS VARCHAR(8000) AS BEGIN

DECLARE @.c VARCHAR(8000)

SELECT @.c = Coalesce(@.c + @.splitter + item, item)
FROM dbo.fSplit(@.original, @.splitter)
GROUP BY item

RETURN @.c
END
GO

SELECT dbo.fGlue('bs,ms,phd,ms,ms,ms,MCSE', ',')-PatP|||I was talking abt the duplicates in the same string,not the duplicate recs in database.
A database is (technically) normalized if it has no repeating groups - all data in fields are atomic.|||SELECT @.c = Coalesce(@.c + @.splitter + item, item)
FROM dbo.fSplit(@.original, @.splitter)
GROUP BY item

I couldn't understand what this query does,partularly with the following line

FROM dbo.fSplit(@.original, @.splitter)

WHy did u put @.original,@.splitter in the braces after the table name.What does ti do.
Thanks.|||dbo.fSplit is the table-valued function that was from the previous posting the I referenced. You need them both (dbo.fSplit and dbo.fGlue) to make this work.

-PatP|||I answered something similar over here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35121|||SELECT dbo.fGlue('bs,ms,phd,ms,ms,ms,MCSE', ',')

WHEn i run the above query it returned the value 'bs,MCSE,ms,phd'
But I want that to be as 'bs,ms,phd,mcse'

Thanks.|||Can you give me every possible notation you might use, in the order that you want to see them presented?

-PatP|||Better yet, here's a solution that will alllow you to specify which degrees you want presented, and the order that you want them presented! Just put them into the tGlue table, and set the seq column values based on the order that you want the degrees to present. Any degrees not listed in tGlue will be listed in alphabetical order after the degrees that are listed in tGlue.DROP FUNCTION dbo.fSplit
GO
-- ptp 20040507 Split a string on a separator, like VB

CREATE FUNCTION dbo.fSplit(
@.pcString VARCHAR(8000)
, @.pcSep VARCHAR(8000) = ','
) RETURNS @.r TABLE (
item VARCHAR(8000)
) AS
BEGIN

DECLARE @.i INT

SET @.i = CharIndex(@.pcSep, @.pcString)

WHILE 0 <> @.i
BEGIN
INSERT INTO @.r (item) SELECT Left(@.pcString, @.i - 1)
SET @.pcString = SubString(@.pcString, @.i + Len(@.pcSep), 8000)
SET @.i = CharIndex(@.pcSep, @.pcString)
END

INSERT INTO @.r (item) SELECT @.pcString

RETURN
END
GO
DROP TABLE tGlue
GO

CREATE TABLE tGlue(
seq INT
CONSTRAINT XPKtGlue PRIMARY KEY (seq)
, abbrev VARCHAR(900)
CONSTRAINT XAK01tGlue UNIQUE (abbrev)
)

INSERT INTO tGlue (seq, abbrev) -- you need to decide what
SELECT 1000, 'aa' -- abbreviations you want
UNION ALL SELECT 1100, 'bs' -- and what sequence you
UNION ALL SELECT 1200, 'ba' -- want them presented
UNION ALL SELECT 1300, 'ms'
UNION ALL SELECT 1400, 'ma'
UNION ALL SELECT 1500, 'mba'
UNION ALL SELECT 1600, 'phd'
GO
DROP FUNCTION dbo.fGlue
GO
CREATE FUNCTION dbo.fGlue(@.original VARCHAR(8000), @.splitter VARCHAR(8000))
RETURNS VARCHAR(8000) AS BEGIN

DECLARE @.c VARCHAR(8000)

SELECT @.c = Coalesce(@.c + @.splitter + item, item)
FROM (SELECT TOP 100 PERCENT item, Coalesce(seq, 2147483647) AS s2
FROM dbo.fSplit(@.original, @.splitter)
LEFT OUTER JOIN tGlue
ON (abbrev = item)
GROUP BY item, seq
ORDER BY 2, 1) AS a

RETURN @.c
END
GO

SELECT dbo.fGlue('bs,ms,phd,ms,ms,ms,MCSE', ',')-PatP

Duplicate primary keys in input file

'm trying to import a text file but the primary key column contains duplicatres (tunrs out to be the nature of the legacy data). How can I kick out all duplicates except, say, for a single primary key value?

TIA,

Barkingdog

SORT component has the ability to eradicate dupes. Have you looked into using that?

-Jamie

|||

No, but I will.

BTW does SORT allow me to send those dups to an "erorr" file so I can see I can further investigate those "bad" records?

Barkingdog

|||

No, you can't do that. But you could easily use an AGGREGATE followed by a conditional split to get all those where the key appears more than once.

-Jamie

Duplicate Foreign keys.

Hi all,

SQL server allows to create as many as foreign key constraints on a same table for a same column.

Will this affect the design or performance in anyway ?

Naming the constraint would be a good way to avoid this.But in case if someone has already created, How do I remove the existing duplicate keys ?

======================
For Example , I have 2 tables Author and Book. I could execute the below query n times and create as many as foreign keys I want.

ALTER TABLE Books
ADD
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID)

======================

Thanks is advance,

DBAnalyst

this may affect the performance. foreign keys shud be named..eg

ALTER TABLE Books with nocheck

ADD constraint fkname

FOREIGN KEY (AuthorID)

REFERENCES Authors (AuthorID).

if u want to check the existing foreign keys to check ne duplicacy use the REFERENTIAL_CONSTRAINTS view of information_schema..

select * from information_schema.REFERENTIAL_CONSTRAINTS

Duplicate Counts per column

I'm have a problem with trying to generate a view that has a count of duplicates values per column in a table.

example I have a table with the following structure:

CREATE TABLE [dbo].[TestDpln](
[CountryCode] [smallint] NOT NULL DEFAULT ((0)),
[NPA] [smallint] NOT NULL DEFAULT ((0)),
[NXX] [smallint] NOT NULL DEFAULT ((0)),
[XXXX] [smallint] NOT NULL DEFAULT ((0)),
[3-Digit] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[4-Digit] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteIdx] [int] NULL DEFAULT ((0)),
CONSTRAINT [TestDpln$PrimaryKey] PRIMARY KEY NONCLUSTERED
(
[CountryCode] ASC,
[NPA] ASC,
[NXX] ASC,
[XXXX] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

the data loaded looks like this

INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')

the results I need are

SiteIdx 3 Digit Count 4 Digit Count
--- ---- ----
1 2 0
2 2 0

I've tried various queries the closest being:

SELECT DISTINCT SiteIdx, COUNT(*) as "3-Digit Count"
FROM dbo.TestDpln
GROUP BY SiteIdx, "3-Digit"
HAVING COUNT(*) > 1

but it only shows one column and one site I'm not sure how to get the '4 Digit Count' column to show up and the rest of the sites. below are the results I get so far.

SiteIdx 3 Digit Count
--- ----
1 2

any help would be great.
Thanks
MikeTry:
COUNT(DISTINCT [YourColumn])|||That did not work I was thinking it would need to be some sort of select statement run on each column then grouped by the siteidx then a count done by site. I'm just not sure how to write it.|||Your data and required output doesn't agree
3d 4d idx
INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
No duplicates here but you want 2 0 1

INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')
and no duplicates here but you want 2 0 2
And then you also ask how to display the rest of the sites
So do you want all zeros returned if a siteidx has no duplicates?|||How do you expect to get 4-Digit counts of zero from the data you supplied, which obviously contains multiple 4-Digit count values?|||Sorry for being so unclear it was a long day and not enough coffee

First the piece that I forgot to put in is that there is an input string that we pass in and are searching for such as '51212' this string is then broken down in to 3 digits from the right giving the search string for the 3 digit column of 212 and then four digits from the right giving the search string for the four digit column of 1212

so with this information and some updated data this is what I'm looking for

Input string 51212
3d 4d Site
INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2212','212','2212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2213','213','2213','1')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')
3 digit 212 duplicates would be 4
4 digit 1212 duplicates would be 2

But the final output should look like

SiteIdx 3digit 4digit
--- -- --
1 4 2|||I hope this example is better|||You sure pick some bad column names.
set nocount on
CREATE TABLE [dbo].[TestDpln](
[CountryCode] [smallint] NOT NULL DEFAULT ((0)),
[NPA] [smallint] NOT NULL DEFAULT ((0)),
[NXX] [smallint] NOT NULL DEFAULT ((0)),
[XXXX] [smallint] NOT NULL DEFAULT ((0)),
[3-Digit] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[4-Digit] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteIdx] [int] NULL DEFAULT ((0)),
CONSTRAINT [TestDpln$PrimaryKey] PRIMARY KEY NONCLUSTERED
(
[CountryCode] ASC,
[NPA] ASC,
[NXX] ASC,
[XXXX] ASC
))

INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','533','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2212','212','2212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','577','2213','213','2213','1')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')

declare @.InputString char(5)
set @.InputString = '51212'

select SiteIDx,
sum(case when [3-Digit] = right(@.InputString, 3) then 1 else 0 end) as '3digit',
sum(case when [4-Digit] = right(@.InputString, 4) then 1 else 0 end) as '4digit'
from [dbo].[TestDpln]
group by SiteIDx

drop table [dbo].[TestDpln]|||That worked great a million thanks to you I never would have thought about using sum for this.
P.S.
I know the column names are bad I inherited this from someone who was trying to use MSACCESS for this data and now I need to really make it work on MSSQL we are redoing the whole schema as we build this.

duplicate column data

In a table I have users registering for a mailing. The mailing should be
limited to one per household, so I would like to exclude all but 1 from each
unique home address. There is a unique id for each registrant, but its
possible another family member could have registered more than once with the
same home address. Whats the best way to do this?
TIA
VanIt depends on how the family members are represented in your schema. Is
there some kind of relationship which identifies one registrant as a family
member of another? Is so, to which registrant should the mailing be
addressed?
Please post your table schema, a few sample data & expected results so that
others can better understand your requirements.
Based on similar requests posted in this forum, the query most likely look
something along the lines of:
SELECT *
FROM tbl t1
WHERE < registrant Identifier > =
( SELECT MAX( < registrant Identifier > )
FROM tbl t2
WHERE t2. < address Identifier > = t1. < address Identifier > )
where < registrant Identifier > is the column or set of columns which
uniquely identifies a registrant while < address Identifier > is column or
set of columns which uniquely identifies an address.
Anith|||Thanks for your reply. Unfortunately I got an out of memory exception when I
tried that query.
As you suggested to post my current query looks like this:
SELECT FirstName, LastName, Email, Address1, Address2, City, State, Zip
FROM Orange_Members
INNER JOIN Orange_MembersPromo ON Orange_Members.RegId =
Orange_MembersPromo.RegId
WHERE(Orange_MembersPromo.PromoId = 600)
In the Orange_Members table there may be Address1 values that are duplicate.
There is a unique id primary key in Orange_Members called RegID.
I would like to return every unique Address1 row. In otherwords if Address1
has:
120 Greene Street twice, I would only like to have the first one that
occurs(actually I really dont care which row it is, as long as it only comes
up once) -- and of course any Address that is only in one row in the DB
should be returned by the select also.
TIA

> Based on similar requests posted in this forum, the query most likely look
> something along the lines of:
> SELECT *
> FROM tbl t1
> WHERE < registrant Identifier > =
> ( SELECT MAX( < registrant Identifier > )
> FROM tbl t2
> WHERE t2. < address Identifier > = t1. < address Identifier > )
> where < registrant Identifier > is the column or set of columns which
> uniquely identifies a registrant while < address Identifier > is column or
> set of columns which uniquely identifies an address.
> --
> Anith|||>> As you suggested to post my current query ..
Actually I asked you to post your table schema, a few sample data & expected
results. For details refer to www.aspfaq.com/5006
Based on your narrative, here is another guesswork:
SELECT *
FROM Orange_Members m1
WHERE m1.RegId = ( SELECT MAX( m2.RegId )
FROM Orange_Members m2
WHERE m2.Address1 = m1.Address1 )
AND m1.PromoId = 600 ;
Anith|||Thanks, that worked!

Duplicate Column

Hi,

I have a column which I need to duplicate with the excat enteries.

I have column name jDateComplete which I need to duplicate with DateComplete.

Can somebody please help me with this?

Thanks,

Aash.

Quote:

Originally Posted by patelaashish

Hi,

I have a column which I need to duplicate with the excat enteries.

I have column name jDateComplete which I need to duplicate with DateComplete.

Can somebody please help me with this?

Thanks,

Aash.

update Table
set jDateComplete = DateComplete|||Hi,

I tried the update statement and it has put NULL values in both of the columns in my table.

Please let me know If I am doing something wrong.

Thanks,

Aash.|||I assumed that one column already had data in it - if that is not the case then update/insert your table as normal

e.g.

update mytable
set col1 = somedata,
col2 = somedata

insert into mytable col1, col2
values(somdata, somdata)|||Hi,

Yes the jDateComplete has got data in it.

Thanks|||assuming your table has an ID column - this will do the trick
substitute ID for your table's primary key

declare @.counter int, @.MaxID int
set @.counter = (select min(id) from mytable)
set @.MaxID = (select max(id) from mytable)

while @.counter <= @.MaxID
begin
update mytable
set jDateComplete = (Select DateComplete from mytable where id = @.counter)
where id = @.counter

set @.counter = @.counter + 1
endsql

duplicate check

Hi,
I've a problem to check if there is a duplicate entry.
In my table i have a computed column which checks for a certain type of
entry which must be unique. E.g. there can be two types of entries. One
which has a type of 1 where period can be 6 or 7. The other type is 2
where period can be from 1 to 5.
Now what i would like to do is when type 1 is being entered it can only
be unique for 6 or 7. There cannot be both entries of 6 and 7. For type
2 it should be unique within each period, in that case there may be more
entries.
E.g.
type | contactid | period
1 1 6
1 1 7 <-should not be possible
2 1 1
2 1 2
2 1 3
2 5 1
2 5 1 <-should not be possible
Below is my ddl of my table and trigger:
CREATE TABLE [Mytable] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [int] NOT NULL ,
[contactId] [int] NOT NULL ,
[Period] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or [Period] =
7))) then null else [Id] end) ,
CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
(
[Id],
[TypeChk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TRIGGER DupCheck ON [dbo].[Mytable]
FOR INSERT, UPDATE
AS
IF (select count(contactid)
from mytable
group by contactid, period, type
having count(contactid)>1 and type=2)>1
ROLLBACK TRANSACTION
RAISERROR('there is a duplicate entry',16,1)
Can anyone help me?Jason
Why do you have three rows of 2 1 and it did not throw the error
My guess yiou need something like that
create table #test
(
pk int not null primary key,
type int not null,
contactid int not null,
period int,
)
go
alter table #test ADD CONSTRAINT
UNQ_test UNIQUE
(
type,
contactid
) ON [PRIMARY]
insert into #test values(1,1,1,6)
insert into #test values(2,1,1,7)--failed
insert into #test values(3,2,1,6)
insert into #test values(4,2,1,7)--failed
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:OU8bZM$%23FHA.1600@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've a problem to check if there is a duplicate entry.
> In my table i have a computed column which checks for a certain type of
> entry which must be unique. E.g. there can be two types of entries. One
> which has a type of 1 where period can be 6 or 7. The other type is 2
> where period can be from 1 to 5.
> Now what i would like to do is when type 1 is being entered it can only be
> unique for 6 or 7. There cannot be both entries of 6 and 7. For type 2 it
> should be unique within each period, in that case there may be more
> entries.
> E.g.
> type | contactid | period
> 1 1 6
> 1 1 7 <-should not be possible
> 2 1 1
> 2 1 2
> 2 1 3
> 2 5 1
> 2 5 1 <-should not be possible
>
> Below is my ddl of my table and trigger:
> CREATE TABLE [Mytable] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Type] [int] NOT NULL ,
> [contactId] [int] NOT NULL ,
> [Period] [int] NOT NULL ,
> [Date] [datetime] NOT NULL ,
> [TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or [Period] = 7)))
> then null else [Id] end) ,
> CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
> (
> [Id],
> [TypeChk]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE TRIGGER DupCheck ON [dbo].[Mytable]
> FOR INSERT, UPDATE
> AS
> IF (select count(contactid)
> from mytable
> group by contactid, period, type
> having count(contactid)>1 and type=2)>1
> ROLLBACK TRANSACTION
> RAISERROR('there is a duplicate entry',16,1)
>
> Can anyone help me?|||First of all the trigger might not work as expected - multiple statements
following the IF statement must be enclosed in a BEGIN...END section if they
are to be executed only if the condition is true.
Right now the transaction in your trigger is rolled back every time, while
the error is raised only when it should be.
IF (select count(contactid)
from mytable
group by contactid, period, type
having count(contactid)>1 and type=2)>1
begin
ROLLBACK TRANSACTION
RAISERROR('there is a duplicate entry',16,1)
end
ML
http://milambda.blogspot.com/|||I was thinking pretty much the same way, but was thrown off by apparent
inconsistencies.
Maybe a table constraint using the following expression might help enforce
the business rule:
((Type = 1) and (Period between 1 and 5)) or ((Type = 2) and (Period between
6 and 7))
ML
http://milambda.blogspot.com/|||Uri Dimant wrote:
> Jason
> Why do you have three rows of 2 1 and it did not throw the error
> My guess yiou need something like that
> create table #test
> (
> pk int not null primary key,
> type int not null,
> contactid int not null,
> period int,
> )
> go
> alter table #test ADD CONSTRAINT
> UNQ_test UNIQUE
> (
> type,
> contactid
> ) ON [PRIMARY]
> insert into #test values(1,1,1,6)
> insert into #test values(2,1,1,7)--failed
> insert into #test values(3,2,1,6)
> insert into #test values(4,2,1,7)--failed
>
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:OU8bZM$%23FHA.1600@.TK2MSFTNGP11.phx.gbl...
>
>
>
Hi Uri,
The three 2 1 are what i want because they are a type 2 entry. It should
not allow a duplicate entry if the period is the same like the last row
in my example.
Type 1 should be unique for a period of 6 or 7. ML suggested a table
constraint would that work for me?|||On Thu, 08 Dec 2005 13:29:52 +0100, Jason wrote:

>Hi,
>I've a problem to check if there is a duplicate entry.
>In my table i have a computed column which checks for a certain type of
>entry which must be unique. E.g. there can be two types of entries. One
>which has a type of 1 where period can be 6 or 7. The other type is 2
>where period can be from 1 to 5.
>Now what i would like to do is when type 1 is being entered it can only
>be unique for 6 or 7. There cannot be both entries of 6 and 7. For type
>2 it should be unique within each period, in that case there may be more
>entries.
>E.g.
>type | contactid | period
>1 1 6
>1 1 7 <-should not be possible
>2 1 1
>2 1 2
>2 1 3
>2 5 1
>2 5 1 <-should not be possible
Hi Jason,
You can do this without a trigger, by changing the computed column and
the unique constraint:
CREATE TABLE [Mytable] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [int] NOT NULL ,
[contactId] [int] NOT NULL ,
[Period] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or
[Period] = 7))) then null else [Period] end) ,
CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
(
[Type],
[contactId],
[TypeChk]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Thu, 08 Dec 2005 13:29:52 +0100, Jason wrote:
>
>
> Hi Jason,
> You can do this without a trigger, by changing the computed column and
> the unique constraint:
> CREATE TABLE [Mytable] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [Type] [int] NOT NULL ,
> [contactId] [int] NOT NULL ,
> [Period] [int] NOT NULL ,
> [Date] [datetime] NOT NULL ,
> [TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or
> [Period] = 7))) then null else [Period] end) ,
> CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
> (
> [Type],
> [contactId],
> [TypeChk]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> Best, Hugo
Hi Hugo,
That did the job, thnx!

Dundas Web Chart Control

I'm having trouble understanding how to build my bar chart. I have a column of info called Issue and then I'm coutning the record.

I bascially need to alter my query into time periods. I need a series for quarter 1 (1/1/2006 - 3/1/2006), one for quarter 2, 3, 4 and the entire year as a whole.

Can this data all be organized in a single select query?

Here is my current query getting the year as a whole data only:

SELECT

Issue,COUNT(*)AS NumRecordsFROM CSP_Item CI

INNER

JOIN CSP_ProblemNotification CPON CP.ID= CI.CSPNum

WHERE

CP.PNDateBETWEEN'01/01/2007'AND'01/31/2007'

GROUP

BY IssueORDERBY NumRecordsASC

Maybe...

SelectDatePart(quarter, theDate) as Quarter.

group byDatePart(quarter, theDate)

dumptrdate column gone in sql server 2000?

Hello,
I am running an environment that has Sybase and SQL Server 2000, and i've
been using the dumptrdate column from sysdatabases on all SYbase servers, but
it appears that it has been replaced by a Reserved column. Is there an
alternative to this in SQL server? I want to be able to query the database
from command line and see the last transaction log backup time. Viewing it in
taskpad works great, but i really need it in a command line. Any help is
greatly apprciated.
Thanks!
FS
Thanks, i'll give this a try.
"Tibor Karaszi" wrote:

> How about the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
>
|||That worked, thanks.
Do you know how to modify the query to give me the last entry only?
so i have 9 databases that i want to generate a report for to list the last
transaction log dump. I tried putting a "where backup_start_date >= '<date
time>'
is there a better way?
"FS" wrote:
[vbcol=seagreen]
> Thanks, i'll give this a try.
>
> "Tibor Karaszi" wrote:
|||That first select statement worked perfectly. Thank you!
"Tibor Karaszi" wrote:

> Here are a couple of options, depending on how much information you need:
> SELECT database_name, MAX(backup_start_date)
> FROM dbo.backupset
> GROUP BY database_name
> SELECT b1.database_name, *
> FROM dbo.backupset AS b1
> INNER JOIN(
> SELECT database_name, MAX(backup_start_date) AS max_date
> FROM dbo.backupset
> WHERE type = 'L'
> GROUP BY database_name
> ) AS b2
> ON b1.database_name = b2.database_name
> AND b1.backup_start_date = b2.max_date
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:AEBE819C-493A-4556-AF29-83505FF135E3@.microsoft.com...
>

dumptrdate column gone in sql server 2000?

Hello,
I am running an environment that has Sybase and SQL Server 2000, and i've
been using the dumptrdate column from sysdatabases on all SYbase servers, but
it appears that it has been replaced by a Reserved column. Is there an
alternative to this in SQL server? I want to be able to query the database
from command line and see the last transaction log backup time. Viewing it in
taskpad works great, but i really need it in a command line. Any help is
greatly apprciated.
Thanks!
FSHow about the backup history tables in msdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"FS" <FS@.discussions.microsoft.com> wrote in message
news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
> Hello,
> I am running an environment that has Sybase and SQL Server 2000, and i've
> been using the dumptrdate column from sysdatabases on all SYbase servers, but
> it appears that it has been replaced by a Reserved column. Is there an
> alternative to this in SQL server? I want to be able to query the database
> from command line and see the last transaction log backup time. Viewing it in
> taskpad works great, but i really need it in a command line. Any help is
> greatly apprciated.
> Thanks!
> FS|||Thanks, i'll give this a try.
"Tibor Karaszi" wrote:
> How about the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
> > Hello,
> >
> > I am running an environment that has Sybase and SQL Server 2000, and i've
> > been using the dumptrdate column from sysdatabases on all SYbase servers, but
> > it appears that it has been replaced by a Reserved column. Is there an
> > alternative to this in SQL server? I want to be able to query the database
> > from command line and see the last transaction log backup time. Viewing it in
> > taskpad works great, but i really need it in a command line. Any help is
> > greatly apprciated.
> >
> > Thanks!
> > FS
>|||That worked, thanks.
Do you know how to modify the query to give me the last entry only?
so i have 9 databases that i want to generate a report for to list the last
transaction log dump. I tried putting a "where backup_start_date >= '<date
time>'
is there a better way?
"FS" wrote:
> Thanks, i'll give this a try.
>
> "Tibor Karaszi" wrote:
> > How about the backup history tables in msdb?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "FS" <FS@.discussions.microsoft.com> wrote in message
> > news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
> > > Hello,
> > >
> > > I am running an environment that has Sybase and SQL Server 2000, and i've
> > > been using the dumptrdate column from sysdatabases on all SYbase servers, but
> > > it appears that it has been replaced by a Reserved column. Is there an
> > > alternative to this in SQL server? I want to be able to query the database
> > > from command line and see the last transaction log backup time. Viewing it in
> > > taskpad works great, but i really need it in a command line. Any help is
> > > greatly apprciated.
> > >
> > > Thanks!
> > > FS
> >
> >|||Here are a couple of options, depending on how much information you need:
SELECT database_name, MAX(backup_start_date)
FROM dbo.backupset
GROUP BY database_name
SELECT b1.database_name, *
FROM dbo.backupset AS b1
INNER JOIN(
SELECT database_name, MAX(backup_start_date) AS max_date
FROM dbo.backupset
WHERE type = 'L'
GROUP BY database_name
) AS b2
ON b1.database_name = b2.database_name
AND b1.backup_start_date = b2.max_date
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"FS" <FS@.discussions.microsoft.com> wrote in message
news:AEBE819C-493A-4556-AF29-83505FF135E3@.microsoft.com...
> That worked, thanks.
> Do you know how to modify the query to give me the last entry only?
> so i have 9 databases that i want to generate a report for to list the last
> transaction log dump. I tried putting a "where backup_start_date >= '<date
> time>'
> is there a better way?
> "FS" wrote:
>> Thanks, i'll give this a try.
>>
>> "Tibor Karaszi" wrote:
>> > How about the backup history tables in msdb?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://sqlblog.com/blogs/tibor_karaszi
>> >
>> >
>> > "FS" <FS@.discussions.microsoft.com> wrote in message
>> > news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
>> > > Hello,
>> > >
>> > > I am running an environment that has Sybase and SQL Server 2000, and i've
>> > > been using the dumptrdate column from sysdatabases on all SYbase servers, but
>> > > it appears that it has been replaced by a Reserved column. Is there an
>> > > alternative to this in SQL server? I want to be able to query the database
>> > > from command line and see the last transaction log backup time. Viewing it in
>> > > taskpad works great, but i really need it in a command line. Any help is
>> > > greatly apprciated.
>> > >
>> > > Thanks!
>> > > FS
>> >
>> >|||That first select statement worked perfectly. Thank you!
"Tibor Karaszi" wrote:
> Here are a couple of options, depending on how much information you need:
> SELECT database_name, MAX(backup_start_date)
> FROM dbo.backupset
> GROUP BY database_name
> SELECT b1.database_name, *
> FROM dbo.backupset AS b1
> INNER JOIN(
> SELECT database_name, MAX(backup_start_date) AS max_date
> FROM dbo.backupset
> WHERE type = 'L'
> GROUP BY database_name
> ) AS b2
> ON b1.database_name = b2.database_name
> AND b1.backup_start_date = b2.max_date
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:AEBE819C-493A-4556-AF29-83505FF135E3@.microsoft.com...
> > That worked, thanks.
> > Do you know how to modify the query to give me the last entry only?
> > so i have 9 databases that i want to generate a report for to list the last
> > transaction log dump. I tried putting a "where backup_start_date >= '<date
> > time>'
> > is there a better way?
> >
> > "FS" wrote:
> >
> >> Thanks, i'll give this a try.
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> > How about the backup history tables in msdb?
> >> >
> >> > --
> >> > Tibor Karaszi, SQL Server MVP
> >> > http://www.karaszi.com/sqlserver/default.asp
> >> > http://sqlblog.com/blogs/tibor_karaszi
> >> >
> >> >
> >> > "FS" <FS@.discussions.microsoft.com> wrote in message
> >> > news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
> >> > > Hello,
> >> > >
> >> > > I am running an environment that has Sybase and SQL Server 2000, and i've
> >> > > been using the dumptrdate column from sysdatabases on all SYbase servers, but
> >> > > it appears that it has been replaced by a Reserved column. Is there an
> >> > > alternative to this in SQL server? I want to be able to query the database
> >> > > from command line and see the last transaction log backup time. Viewing it in
> >> > > taskpad works great, but i really need it in a command line. Any help is
> >> > > greatly apprciated.
> >> > >
> >> > > Thanks!
> >> > > FS
> >> >
> >> >
>

dumptrdate column gone in sql server 2000?

Hello,
I am running an environment that has Sybase and SQL Server 2000, and i've
been using the dumptrdate column from sysdatabases on all SYbase servers, bu
t
it appears that it has been replaced by a Reserved column. Is there an
alternative to this in SQL server? I want to be able to query the database
from command line and see the last transaction log backup time. Viewing it i
n
taskpad works great, but i really need it in a command line. Any help is
greatly apprciated.
Thanks!
FSHow about the backup history tables in msdb?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"FS" <FS@.discussions.microsoft.com> wrote in message
news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
> Hello,
> I am running an environment that has Sybase and SQL Server 2000, and i've
> been using the dumptrdate column from sysdatabases on all SYbase servers,
but
> it appears that it has been replaced by a Reserved column. Is there an
> alternative to this in SQL server? I want to be able to query the database
> from command line and see the last transaction log backup time. Viewing it
in
> taskpad works great, but i really need it in a command line. Any help is
> greatly apprciated.
> Thanks!
> FS|||Thanks, i'll give this a try.
"Tibor Karaszi" wrote:

> How about the backup history tables in msdb?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:6C600A7F-1076-4603-8517-3EBA33DC518D@.microsoft.com...
>|||That worked, thanks.
Do you know how to modify the query to give me the last entry only?
so i have 9 databases that i want to generate a report for to list the last
transaction log dump. I tried putting a "where backup_start_date >= '<date
time>'
is there a better way?
"FS" wrote:
[vbcol=seagreen]
> Thanks, i'll give this a try.
>
> "Tibor Karaszi" wrote:
>|||Here are a couple of options, depending on how much information you need:
SELECT database_name, MAX(backup_start_date)
FROM dbo.backupset
GROUP BY database_name
SELECT b1.database_name, *
FROM dbo.backupset AS b1
INNER JOIN(
SELECT database_name, MAX(backup_start_date) AS max_date
FROM dbo.backupset
WHERE type = 'L'
GROUP BY database_name
) AS b2
ON b1.database_name = b2.database_name
AND b1.backup_start_date = b2.max_date
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"FS" <FS@.discussions.microsoft.com> wrote in message
news:AEBE819C-493A-4556-AF29-83505FF135E3@.microsoft.com...[vbcol=seagreen]
> That worked, thanks.
> Do you know how to modify the query to give me the last entry only?
> so i have 9 databases that i want to generate a report for to list the las
t
> transaction log dump. I tried putting a "where backup_start_date >= '<date
> time>'
> is there a better way?
> "FS" wrote:
>|||That first select statement worked perfectly. Thank you!
"Tibor Karaszi" wrote:

> Here are a couple of options, depending on how much information you need:
> SELECT database_name, MAX(backup_start_date)
> FROM dbo.backupset
> GROUP BY database_name
> SELECT b1.database_name, *
> FROM dbo.backupset AS b1
> INNER JOIN(
> SELECT database_name, MAX(backup_start_date) AS max_date
> FROM dbo.backupset
> WHERE type = 'L'
> GROUP BY database_name
> ) AS b2
> ON b1.database_name = b2.database_name
> AND b1.backup_start_date = b2.max_date
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "FS" <FS@.discussions.microsoft.com> wrote in message
> news:AEBE819C-493A-4556-AF29-83505FF135E3@.microsoft.com...
>

Dumb trigger question

Table with last UpdatedBy Column and LastUpdatedOn Column.
Want to create Insert and Update Triggers that would automatically write
Logged in User Name and System Date Time to each field.
I Think User is SUSER_SNAME function and System date Is GETDATE() but can't
figure out how to write these two to row from within a trigger in table.
Any help greatly appreciated.
BobYou can use the inserted psuedo-table to identify the updated rows. One
method:
CREATE TRIGGER TR__Update_MyTable
ON MyTable FOR UPDATE
AS
UPDATE MyTable
SET UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
WHERE EXISTS
(
SELECT *
FROM inserted
WHERE inserted.PK = MyTable.PK
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>|||Use an INSTEAD OF trigger. The deleted pseudotable is a copy of any
existing rows that are about to be updated, the inserted pseudotable
contains the new values. The deleted pseudotable will be empty if the
operation was INSERT. The inserted pseudotable will be empty if the
operation was DELETE. Both pseudotables will have the same number of rows
if the operation was UPDATE. The reason you should use an INSTEAD OF
trigger is that it allows you to change values before it hits the database.
The only problem with them is that the cascade kludge won't coexist, which
in my opinion is a good thing.
In an INSTEAD OF UPDATE trigger, you simply issue an update statement:
UPDATE tableName
SET column1 = inserted.column1,
column2 = inserted.column2,
..,
UpdatedBy = SUSER_SNAME(),
LastUpdatedOn = GETDATE()
FROM inserted
WHERE primaryKeyColumn = inserted.primaryKeyColumn
The only time this is a problem is if you're using natural keys. If that's
the case, then you need to test for multiple rows
put code similar to this at the top of the trigger
--Don't put anything before this!!!
DECLARE @._ROWCOUNT INT SET @._ROWCOUNT = @.@.ROWCOUNT
IF @._ROWCOUNT = 0 RETURN
IF @._ROWCOUNT > 1 AND (UPDATE(primaryKeyColumn1) OR
UPDATE(primaryKeyColumn2))
BEGIN
ROLLBACK TRANSACTION
RAISERROR('ATTEMPT TO UPDATE Primary Key using set-based operation', 16, 0)
RETURN
END
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>|||Thank you both very much
Bob
"Bob" <bdufour@.sgiims.com> wrote in message
news:uwISI5NzFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Table with last UpdatedBy Column and LastUpdatedOn Column.
> Want to create Insert and Update Triggers that would automatically write
> Logged in User Name and System Date Time to each field.
> I Think User is SUSER_SNAME function and System date Is GETDATE() but
> can't figure out how to write these two to row from within a trigger in
> table.
> Any help greatly appreciated.
> Bob
>

Dual y-axis chart combo chart

Can reporting service 2000 do combo chart (column and line) with two y-axis?Hi,
You can place two charts side by side.
Amarnath
"qye2020" wrote:
> Can reporting service 2000 do combo chart (column and line) with two y-axis?
>
>|||Use the "Plot data as line" option to mix lines with bars or with columns.
I was able to develop a way to simulate a second y axis. In my case the
data source was a cube. I had two types of series, one with large, whole
numbers, and one with percentages, which caused the percentages to be at the
zero point when displayed with the other type of series. My solution was to
write a calculated member that found the maximum value of the whole number
series for all rows, and to round that value up to the next highest number.
Therefore, if the series returned 8,235 as the maximum value in any row, the
result of my calculated member would be 9,000. The 9,000 value of the
calculated member was returned for every row. In the graph I used the value
property of the percentage series to multiply the actual value by my
calculated member value. I used the label property to obtain the actual
percentage value. Thus, 80% became .8 * 9000 for plotting purposes, but the
data point label said 80%. The result was a successful combination of the
two very different types of series. The only thing that I could not do
without a true second y axis was having the tick marks and labels of a second
axis along the right side of the graph.
--
Brian C. Berg
MCSE+I, MCDBA
Berg Information Technology, Inc.
This posting is provided "AS IS" with no warranties, and confers no rights.
"qye2020" wrote:
> Can reporting service 2000 do combo chart (column and line) with two y-axis?
>
>

Dual axes in RS2k5

I seem to remember reading that in Reporting Services 2005 it would be
possible to create column charts with dual axes, in particular with a dual y
axis.
I've been playing around with the charts but I can't seem to find a way to
do this. Did this functionality find its way into the product in the end? How
is it done?
Thanks,
Matt.Hi Matt,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood would like to know whether Dual axes
will be supported in SQL Server 2005 Reporting Services. If I have
misunderstood your concern, please feel free to point it out.
Unfortunately, dual axes are not supported in the built-in charting
included with SQL Server 2005 RS. However, Dundas (see
http://www.dundasreporting.com/home/index.aspx) will be providing a plug-in
that supports this functionality.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

dtswizard.exe Only one row returned importing .XLS

When importing data from an xls file, only first column of first row is returned? Am I missing something?Make sure that in the Source component you're selecting a worksheet name that ends in "$". Otherwise, what you're selecting is actually a range and it might only be defined as the first row.
|||Thanks a Million!|||

Hi JayH,

Can you tell me how to loop on one table. I have an input mapped to a column of this table. Is there a way to accomplish it within DFT ?

Will appreciate your help.

Thanks,

Lohan

|||Could you give a better description of your problem and what you want to accomplish? Typically, looping is not done inside a data flow, it is done with a For Loop container in the control flow.

DTS/bcp a date into a datetime column

Is it possible to bcp a date like '1990-01-01' into a datetime column? The flat file does not contain any time.

I know it is possible using DTS and VB but I am trying to avoid this method. For those familiar with Oracle SQL Loader, you can specify a date format or date conversion function in a control file. I am wondering if I can use the function convert() in some similiar manner.

Thanks,
TPHBCP will handle all reccognized date formats. You do not need to do any type of conversions.

DTS vs Excel numeric conversion

I am having a problem importing an Excel spreadsheet. I have a column in
an Excel sheet with alphanumeric text and some of the cells are numeric.
Some of the cells contain numbers like 12345.6 and when DTS is done
importing it into a field that is nvarchar the results are
"12345.600000000001". I have tried:

1. Changing the format of the Excel column to text
2. Using the formula =text(a1,0) which only truncates the .6
3. Using the formula =t(a1) which will remove some numeric representations
4. Exporting the sheet to CSV or TXT first which will not enclose the cell
contents with ""
5. Beating the computer with a nine iron

None of these options work. Any idea anyone?

Don VonderBurgFormatting the cells as text after the data are there won't help.
Copy the cells to another location which is PREformatted as text, then
copy the copy back onto the original cells and try again. :)

On Thu, 13 May 2004 22:03:51 GMT, Don.Vonderburg@.nospam.com wrote:

>I am having a problem importing an Excel spreadsheet. I have a column in
>an Excel sheet with alphanumeric text and some of the cells are numeric.
>Some of the cells contain numbers like 12345.6 and when DTS is done
>importing it into a field that is nvarchar the results are
>"12345.600000000001". I have tried:
>1. Changing the format of the Excel column to text
>2. Using the formula =text(a1,0) which only truncates the .6
>3. Using the formula =t(a1) which will remove some numeric representations
>4. Exporting the sheet to CSV or TXT first which will not enclose the cell
>contents with ""
>5. Beating the computer with a nine iron
>None of these options work. Any idea anyone?
>Don VonderBurg|||Never thought of that one. Thank you.

Don|||Hi,

I vaguely remember that when you import the Excel file through DTS
that you can set the data type somewhere. Perhaps that helps.

When you look at the numbers that are wrongly imported in the Excel
formula bar, do you see the error as well? I guess when this is the
result in an Excel calculcated cell you may expect these rounding
errors. Remember that in Excel you never actually see the underlying
value. All values are always displayed using some kind of a display
mask. You can use the round function in Excel to round your results.
That should take care of it.

Just to make you aware of another way to pump your data into the
database. I wrote an addin for Excel called SQL*XL. Its goal is to
remove these hassles from the end user. You can use SQL*XL to get data
from the database into Excel or to pump data from Excel into the
database. It even lets you change retrieved data in Excel and post the
changes back.

If you are interested, have a look at SQL*XL at www.oraxcel.com

Best regards, Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com

DTS Transfer Table with Text Column slow

I am using DTS to transfer some tables from one server to another as part of a migration. We want to be down for as little time as possible, but we need the most up-to-date copy of the database tables in question.

I am currently testing the transfer process in our test environment by migrating the data from one database to another on the same SQL instance.

There are 7 tables to transfer and the total size of the database is 450 MB (with around 117 MB used). The two largest tables have around 17,000 records each.

One table (the header) has no text column and it takes just a few seconds to transfer. The other table (the detail) has two columns, one of which is a text column (actually, its not fair to call it the detail table; the relationship is actually one-to-one, but for the sake of this discussion, let's leave it at that).

The header takes seconds to transfer, but the detail takes up to 18 minutes.

Physically, our test server is quite robust; 2 processors, a 3 disk RAID-5 for the data files and a separate RAID 1 partition for the logs. Performance counters don't indicate any real issues: during the transfer, the disk utilization on the data partition occasionally spikes to a high level, but comes right back down until the next spike (the spikes being separated by about 1 minute. No issues with memory, paging or CPU.

I have removed the clustered index on the affected table as well as the PK. No help.

Are text columns just slow? Is there something that I am missing?

Regards,

hmscottAre you migraging the entire database, or just the seven tables? If it's the entire database, you should really just detach, move the data and log file, and attach them. Text columns are naturally slower due to how they are stored though.|||Just as in other posts, I'd pick BCP...out...-n/BCP...in...-n -a32764 -h"TABLOCK"|||'kay. Thanks for the responses. Detach/Attach may be a realistic alternative. My only worry is that the servers are in different domains (though I think that should be manageable).

Regards,

hmscott|||He's a question though...

What is the best way to get text out? What about image?