Duplicate Values Problem

I trying to insert values from a temporary table into a permanent table. Th
e
problem is the temporary table has duplicate UpdateTime values (issues with
the database used to populate the temporary table) and the UpdateTime is a
primary key in the permanent table.
Is there a way I can remove, or exclude, the duplicate values in the
temporary table before inserting the values into the permanent table?
RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
value with the larger RUN_SER_NO is the one to keep.
UpdateTime GRADE RUN_SER_NO
11-Aug-04 04:00 AA 634
11-Aug-04 05:00 AA 634
11-Aug-04 06:00 AA 634
11-Aug-04 07:00 VVV 636
11-Aug-04 07:00 VVV 635
11-Aug-04 08:00 VVV 635
11-Aug-04 08:00 VVV 636
11-Aug-04 09:00 VVV 636
11-Aug-04 09:00 VVV 635
11-Aug-04 10:00 VVV 635
11-Aug-04 10:00 VVV 636
11-Aug-04 11:00 VVV 636
11-Aug-04 12:00 VVV 636
11-Aug-04 13:00 VVV 636
11-Aug-04 14:00 VVV 636
11-Aug-04 15:00 VVV 636
Thanks in advance,
Raulselect UpdateTime from table_name group by UpdateTime having count(*) > 1
will give you the duplicates.You can remove them by using this query before
inserting into the permanent table.
"Raul" wrote:

> I trying to insert values from a temporary table into a permanent table.
The
> problem is the temporary table has duplicate UpdateTime values (issues wit
h
> the database used to populate the temporary table) and the UpdateTime is a
> primary key in the permanent table.
> Is there a way I can remove, or exclude, the duplicate values in the
> temporary table before inserting the values into the permanent table?
> RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
> value with the larger RUN_SER_NO is the one to keep.
> UpdateTime GRADE RUN_SER_NO
> 11-Aug-04 04:00 AA 634
> 11-Aug-04 05:00 AA 634
> 11-Aug-04 06:00 AA 634
> 11-Aug-04 07:00 VVV 636
> 11-Aug-04 07:00 VVV 635
> 11-Aug-04 08:00 VVV 635
> 11-Aug-04 08:00 VVV 636
> 11-Aug-04 09:00 VVV 636
> 11-Aug-04 09:00 VVV 635
> 11-Aug-04 10:00 VVV 635
> 11-Aug-04 10:00 VVV 636
> 11-Aug-04 11:00 VVV 636
> 11-Aug-04 12:00 VVV 636
> 11-Aug-04 13:00 VVV 636
> 11-Aug-04 14:00 VVV 636
> 11-Aug-04 15:00 VVV 636
> Thanks in advance,
> Raul
>|||How about:
SELECT update_time, grade, run_ser_no
FROM tbl t1
WHERE t1.run_ser_no = ( SELECT MAX( t2.run_ser_no )
FROM tbl t2
WHERE t2.UpdateTime = t1.UpdateTime
AND t1.grade = t2.grade ) ;
Anith|||On Tue, 8 Feb 2005 14:11:04 -0800, Raul wrote:

>I trying to insert values from a temporary table into a permanent table. T
he
>problem is the temporary table has duplicate UpdateTime values (issues with
>the database used to populate the temporary table) and the UpdateTime is a
>primary key in the permanent table.
>Is there a way I can remove, or exclude, the duplicate values in the
>temporary table before inserting the values into the permanent table?
Hi Raul,
SELECT UpdateTime, Grade, Run_ser_no
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.UpdateTime = a.UpdateTime
AND b.Run_ser_no > a.Run_ser_no)
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all who replied!
These suggestions are really helpful.
Thanks again,
Raul
"Raul" wrote:

> I trying to insert values from a temporary table into a permanent table.
The
> problem is the temporary table has duplicate UpdateTime values (issues wit
h
> the database used to populate the temporary table) and the UpdateTime is a
> primary key in the permanent table.
> Is there a way I can remove, or exclude, the duplicate values in the
> temporary table before inserting the values into the permanent table?
> RUN_SER_NO = 635 is the bad actor in this case. Typically the duplicate
> value with the larger RUN_SER_NO is the one to keep.
> UpdateTime GRADE RUN_SER_NO
> 11-Aug-04 04:00 AA 634
> 11-Aug-04 05:00 AA 634
> 11-Aug-04 06:00 AA 634
> 11-Aug-04 07:00 VVV 636
> 11-Aug-04 07:00 VVV 635
> 11-Aug-04 08:00 VVV 635
> 11-Aug-04 08:00 VVV 636
> 11-Aug-04 09:00 VVV 636
> 11-Aug-04 09:00 VVV 635
> 11-Aug-04 10:00 VVV 635
> 11-Aug-04 10:00 VVV 636
> 11-Aug-04 11:00 VVV 636
> 11-Aug-04 12:00 VVV 636
> 11-Aug-04 13:00 VVV 636
> 11-Aug-04 14:00 VVV 636
> 11-Aug-04 15:00 VVV 636
> Thanks in advance,
> Raul
>

Duplicate Values on Top Level of Dimension

Hey there community,

I have a problem, and i am lost as to how i can fix it.. I would be over joyed if someone can give me a hand in identiffy it.. I will try to explain below::

I have a cube for sales, that displays sales by a Market heiracy of Market->Division->Family->Item. When i drill down, sales numbers look fine..

I added in a budget file, which only specifys budgets down the the Division Level.. I have added the Budget Figure as a Measure Group. In the dimension usage tab, i specify the Division as the Granuality for the Budget Measure Group on the Market Dimension..

	Measure Groups
Dimensions	Standard Measure	Budget Measures	Freight Amount
Date	Date	Period	Date
Sales Person	Sales Person		Sales Person
Order Type	Order Type
Customer	Customer	Country	Customer ID
Market	Item	Division

When i build and deploy my cube, and i drag in the Market Heiracy, i add the sales fugures and the budget amounts. The top level, should split down on the Budget Figure, but i get the same value repeated all the way through like below:

Market	Sales Amount	Budget Amount
aaaaa	527434.08	3819283
bbbbb	1605726.50999999	3819283
cccccc	640.06	3819283
ddddd	1488549.15	3819283
eeeee	9107.59	3819283
Grand Total	3631457.39	3819283

when i Drill Down on the Market to the Division Level, the splits are correct, but the totals are all the same all the way down.. The should be the total of that market...

Market	Division	Sales Amount	Budget Amount
aaaaa	GP	24100.27	13244
	PT	503333.81	635552
	Total	527434.08	3819283
bbbbb	GP	333209.5	335234
	Other	167680.46	127426
	PT	1104836.55000001	1122647
	Total	1605726.50999999	3819283
cccccc	PT	640.06	0
	Total	640.06	3819283
ddddd	GP	790620.499999999	794611
	Other	11206.16	14831
	PT	686722.49	769930
	Total	1488549.15	3819283
eeeee	Other	9107.59	5808
	Total	9107.59	3819283
Grand Total		3631457.39	3819283

I would like the totals to be the total budget for each market.. I am stuck, i have tried some different things, but this is as far as can get..

If i choose the Market to be the Granuality for the Budget Measure Group, then i get the reverse of this.. The market splits are correct, but when i drill down to the Division Level, the values repeat..

Please can someone give me some ideas of what to try!

I can provide more data if need be.

Many thanks in advance!

Thanks Scott Lancaster

I reckon you don't have relationship defined between Market and Division. If you will make Market a member property of Division - everything should become fine (and keep Division as granularity for Budget measure group).|||

Thanks Mosha..

Could you please explain how i do this? Im pretty new to SSAS and have taught myself most of the way

Again.. very very much appriciated..

Thanks

|||In the dimension editor, drag Market attribute and drop it into Division attribute. This will create the needed relationship. If still in doubt, read documentation on the subject of "related attributes" or "member properties".|||

Thanks Mosha!!!!

your are a champion.. I have done this and it has all worked perfectly...

Again.. thanks mate..

Scotty

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

duplicate values

hey guys,

ive run into an issue and im not quite sure how to fix it.. let me explain

i have a cube, by market and i wish to show sales and freight costs.

The freight Fact table has no relationship to the market dimension as freigth is charged on customer order level, not item level.

I have data showing like the following:

Year

2006

Market

Sales

Freight Amount

Auto OEM

$7,115,315.22

$56,620.73

Auto Repl

$18,517,004.07

$56,620.73

Intercompany

$4,772,910.37

$56,620.73

Ind OEM

$12,039.74

$56,620.73

Ind Repl

$18,679,313.71

$56,620.73

Other

$84,263.19

$56,620.73

Grand Total

$49,180,846.30

$56,620.73

I would only like the freigth amount to show at the Grand Total Level. Is this possible? or, if not, is there a way you can combine two dimensions into 1? ie, combine a Freight Account dimemsion and the Market Dimension so that i can see Markets, then the different Freight Accounts by rows?

Thanks in advance,

Scotty

If you look in the cube structure tab in BIDS, you will see that each measure group has a property called IgnoreUnrelatedDimensions, this defaults to true which means that when you slice a measure by an unrelated measure you see the total amount (which is the behaviour you are seeing). Changing this property to false will mean that the measures will only display when they are either sliced by a related dimension or at the highest level.

It's hard to be sure, but from what you have said I don't think combining the two dimension would work.

Duplicate Values

I have a table which is a license holder table (i.e., plumbers, electricians etc...) There are some people who appear in the table more than once as they have more than 1 type of license. I am tasked with querying out 200 of these people a week for mailing a recruitment letter which I am doing using the following select statement:

SELECT TOP 200 Technicians.Name, Technicians.Address, Technicians.City, Technicians.State, Technicians.ZipCode, Technicians.LicenseType
FROM Technicians

My problem is that this doesn't deal with the duplicates and distinct won't work because I need to pass the license type and that's the one field that's always distinct while the name and adress fields duplicate.You'll need to determine which LicenseType to return in cases where there is more than one. This example returns the lowest LicenseType alphabetically:

SELECT TOP 200
Technicians.Name,
Technicians.Address,
Technicians.City,
Technicians.State,
Technicians.ZipCode,
min(Technicians.LicenseType) LicenseType
FROM Technicians
group by Technicians.Name,
Technicians.Address,
Technicians.City,
Technicians.State,
Technicians.ZipCode|||Why don't you break the structure into PersonMaster, LicenseMaster, and PersonLicense? Then you can do an insert ... select distinct into those tables accordingly. The rest is common sense.|||Yes, if you can solve your problem by improving your database schema that is always preferable.|||Assume name is enough (You may have to do the whole row).

Notice the duplicity of Data...You really have 2 tables. That would make the SQL even easier...

There's no substitute for a good design..

USE Northwind
GO

CREATE TABLE myTable99(
[Name] varchar(50)
, Address varchar(255)
, City varchar(50)
, State char(2)
, ZipCode varchar(10)
, LicenseType varchar(10)
)
GO

INSERT INTO myTable99(
[Name]
, Address
, City
, State
, ZipCode
, LicenseType)
SELECT 'Brett','123 Main St','Newark','NJ','00000','ABC123' UNION ALL
SELECT 'Brett','123 Main St','Newark','NJ','00000','ABC456' UNION ALL
SELECT 'Brett','123 Main St','Newark','NJ','00000','ABC789' UNION ALL
SELECT 'Blinddude','123 Main St','OhiYo','OH','00000','XXX123' UNION ALL
SELECT 'Blinddude','123 Main St','OhiYo','OH','00000','XXX456' UNION ALL
SELECT 'rdjabarov','123 Main St','San Antonio','TX','00000','EFG123'
GO

SELECT TOP 200
[Name]
, Address
, City
, State
, ZipCode
, LicenseType
FROM myTable99 o
WHERE EXISTS (SELECT
[Name]
, Address
, City
, State
, ZipCode
FROM myTable99 i
WHERE i.[Name] = o.[Name]
GROUP BY
[Name]
, Address
, City
, State
, ZipCode
HAVING COUNT(*) > 1)
GO

DROP TABLE myTable99
GO|||Brett, i am disappointed i'm not in there as a plumber or something

by the way, your query only pulls out people who are multi-licensed

i guess that is one way to interpret "these people" in the original question

i personally would not have interpreted it as 200 of people with more than one license, but rather, 200 people overall, but no individual more than once

once again, good specs are shown to be crucial before we go merrily traipsing down the WHERE EXISTS path...

;) ;) ;)

in any case, would your WHERE clause not work better like this, assuming you were actually interested in pick only multi-license people...
WHERE 1 < ( SELECT count(*)
FROM myTable99 i
WHERE i.[Name] = o.[Name] )it's a correlated subquery after all, so it shouldn't need grouping

p.s. where's that thread where we were talking about the DBA getting the shaft for poor design? i have a link i want to add to it|||Rudy,

There are some people who appear in the table more than once as they have more than 1 type of license.

I just felt that THESE meant THOSE:D

And yes I was debating you're syntax...but I figured dup rows less the license meant the same guy...

Either way...it's a poor design, which I'm sure they're stuck with...

Maybe an updateable view would be a good thing here..|||Originally posted by Brett Kaiser
Maybe an updateable view would be a good thing here.. Nahhh, an updatable view would be a work-around. Fixing the underlying problems in the schema would be the good thing in this case!

-PatP|||Unfortunately, the database is provided directly by the State Board of Licensing and constantly updated so fixing the schema is not really an option. Great suggesions by the way. Because I didn't have a lot of time when I first posted I created a stored procedure that found duplicate license holders and marked all but instance as having already been mailed so therefore my original query which looks for licensees that have not already been mailed works without locating those duplicates. As the State updates the database I'll import only the new rows into the database (with the added [Mailed] bit field) and rerun the stored proc to mark duplicates as having already been mailed leaving me with a distinct record set. Thanks again for all your help!sql

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.
SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
David Portas
SQL Server MVP
|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:

> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Duplicate values

I have a table with 6 columns. I want to create a primary key for the first
two columns but I am getting a message that I already have duplicate values
in the combination of the two column.
Can someone help me with SQL to find the duplicate rows on two columns of
the same table ?
Thanks.SELECT T1.col1, T1.col2, T1.col3, T1.col4, T1.col5, T1.col6
FROM YourTable AS T1,
(SELECT col1, col2
FROM YourTable
GROUP BY col1, col2
HAVING COUNT(*)>1) AS T2
WHERE T1.col1 = T2.col2
AND T1.col1 = T2.col2
(untested)
--
David Portas
SQL Server MVP
--|||CORRECTION:
...
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
David Portas
SQL Server MVP
--|||Thanks......I found it........Now, I need to find a way to remove the
duplicates (Leave only one row).
Thanks.
"David Portas" wrote:
> CORRECTION:
> ...
> WHERE T1.col1 = T2.col1
> AND T1.col2 = T2.col2
>
> --
> David Portas
> SQL Server MVP
> --
>
>

duplicate RPC:Completed events in trace file

Has anyone ever seen duplicate RPC:Completed events in a trace file?
I've got several rows in which the colum values are the exact same. I
was guessing this might be parallel execution, but its just a guess...
thx,
--oj.Hi
I have not seen duplicated events. What version of SQL Server are you using?
What columns/filters are using? Are you logging to screen/file or table?
John
"seraph" wrote:

> Has anyone ever seen duplicate RPC:Completed events in a trace file?
> I've got several rows in which the colum values are the exact same. I
> was guessing this might be parallel execution, but its just a guess...
> thx,
> --oj.
>

duplicate RPC:Completed events in trace file

Has anyone ever seen duplicate RPC:Completed events in a trace file?
I've got several rows in which the colum values are the exact same. I
was guessing this might be parallel execution, but its just a guess...
thx,
--oj.
Hi
I have not seen duplicated events. What version of SQL Server are you using?
What columns/filters are using? Are you logging to screen/file or table?
John
"seraph" wrote:

> Has anyone ever seen duplicate RPC:Completed events in a trace file?
> I've got several rows in which the colum values are the exact same. I
> was guessing this might be parallel execution, but its just a guess...
> thx,
> --oj.
>

duplicate RPC:Completed events in trace file

Has anyone ever seen duplicate RPC:Completed events in a trace file?
I've got several rows in which the colum values are the exact same. I
was guessing this might be parallel execution, but its just a guess...
thx,
--oj.Hi
I have not seen duplicated events. What version of SQL Server are you using?
What columns/filters are using? Are you logging to screen/file or table?
John
"seraph" wrote:
> Has anyone ever seen duplicate RPC:Completed events in a trace file?
> I've got several rows in which the colum values are the exact same. I
> was guessing this might be parallel execution, but its just a guess...
> thx,
> --oj.
>

duplicate reference key values

HI, I keep getting this warning with a lookup (full cache mode) that retreives data form a table that contains the following information:

SRCE_SYS TABLE_NAME FIELD_NAME CODE ENGLISH_DESCRIPTION
STATIC STATIC PM_PC_TX_TYPE_CODE G GROSS
STATIC STATIC PM_PC_TX_TYPE_CODE E EXCESS
STATIC STATIC PM_PC_TX_TYPE_CODE F FACULTATIVE
STATIC STATIC PM_PC_TX_TYPE_CODE S SURPLUS
STATIC STATIC PM_PC_TX_RIDER_CODE BOAT BOAT
STATIC STATIC PM_PC_TX_RIDER_CODE RIDER RIDER
STATIC STATIC PM_PC_TX_RIDER_CODE CONT CONTENTS

The column tab matches SRCR_SYS, TABLE_NAME and FIELD_NAME (using constants defined in a derived column transform) .The code column comes from the source. We want to retreive the english_description colum from SRCR_SYS, TABLE_NAME,FIELD_NAME and CODE in the dataflow.

I would normally ignore the warning but sometimes, it seems that the lookup does not match any values and enabling memory restriction on the advanced tab resolve the issue and suppress the warning.

As I said, I keep getting this warning and I don't know why since there are no duplicates in the table? Am I missing something?

Thank you,

Christian

For the three fields SRCR_SYS, TABLE_NAME, and FIELD_NAME, there are duplicate keys.

You aren't matching on CODE as well?|||

Yes, I match the code as well so the combination of all 4 columns is unique. Does SSIS looks for all columns that are matched in the column tab or just the first one?

Thanks,

Christian

|||It matches on all columns that you select as lookup columns.

The following query should identify any duplicates:
select srce_sys,table_name,field_name,code,count(*) from table group by srce_sys,table_name,field_name,code having count(*)>1

Duplicate display of values in the Details section

Hi folks,

i have a peculiar problem, i am passing a query from VB 6.0 to crystal reports to revieve the data i want and i am successful in doing that. But the problem is, it is
duplicating the output, i'm seeking for. it duplicates when the output is places in the Details section but when i place it in the Header section then there is no duplication.
can any one please tell me why?

thanx in advance.......

number3I hope I've understood the question correctly...

How many rows is your query returning? The details section is the only section that creates a new section for every row returned by the query. If you drag the field into the header you are only going to get the value from the first row. This is how crystal is supposed to work.

If you need a second details section (from another result set), you need to look at placing a subreport in the header.|||you need to run your SQL in a database viewer tool. possibly you have linked some tables together but not put a criteria in the select expert..

you CAN choose "select distinct records" on the database menu, but this will make the report run more slowly. you should really fix up your sql so it doesnt return you duplicatessql

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.

Dupes with NULL

"I am trying to select all of the duplicate values out of a table like this:

field1 field2 field3 field4 field5
jason anderson 266985421 Florida NULL
Derek Lee 56898755 Louisiana 32
jason anderson 266985421 Florida NULL

the first and third are duplicate records but with the sql I have so far it doesn't reconize them as dupes becuase of the NULL.

SELECT field1, field2, field3, field4, field5
FROM table
WHERE ((([field1]) In
(SELECT [field1]
FROM [table] AS TMP
GROUP BY field1, field2, field3, field4, field5
HAVING Count(*)>1 And [field1] = [table].[field1] AND .....and field5 = table.field5 )))
ORDER BY field1,field2, ......field5

what do I do???????"select f1,f2,f3,f4,f5,count(*) from table group by f1,f2,f3,f4,f5
having count(*) > 1

this will give all duplicate records plus count of them

best of luck|||I forgot one specific part...........there is a 6th field, and the 6th field is not one of the fields that can have a dupe, but i have to display it in the output

field1 field2 field3 field4 field5 field6
jason anderson 266985421 Florida NULL programmer
Derek Lee 56898755 Louisiana 32
jason anderson 266985421 Florida NULL dba

so the output must display like this since this is a dupe record
field1 field2 field3 field4 field5 field6
jason anderson 266985421 Florida NULL programmer
jason anderson 266985421 Florida NULL dba

.......I have to show both fields|||if you add f6 in the query , desired output will be generated|||I tryed adding f6 to the query and it did not work...the query also will not show BOTH the dupe records, and that is what I need, I need it to show the all 6 fields even though 5 of them have to match, the 6th field does not have to match. so my ouput needs to be as such......

fied1 field2 field3 field 4 field 5 field6
match match match match match match doesn't matter
match match match match match match doesn't matter

.........and my other probelm was that if the field was a null then it would not count as a dupe........do you have any suggestions|||Try this

select a.f1, a.f2,a.f3,a.f4,f5=ISNULL(a.f5,'OOPS') ,b.f6
FROM (select f1, f2,f3,f4,f5=ISNULL(f5,'OOPS'),cc=count(*) from #temp group by f1, f2,f3,f4,f5=ISNULL(f5,'OOPS') having count(*) > 1) AS A,
(select f1, f2,f3,f4,f5=ISNULL(f5,'OOPS'),f6 from #temp ) AS B
where a.f1 = b.f1 and
a.f2 = b.f2 and
a.f3 = b.f3 and
a.f4 = b.f4 and
a.f5 = b.f5

NULL is hanndled by converting it to 'OOPS' , if you don't like OOPS in your output U can use CASE statement to convert it back to null in FIRST SELECT statement

In case f6 is also having Dup then use Distinct in first select statement

Dumping SQL Server Variables

In Oracle and other databases there is a way to dump all the values that hav
e
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files
.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted whe
n
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:

>In Oracle and other databases there is a way to dump all the values that ha
ve
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream file
s.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a sele
ct
>insert from one table into another and only 1995 rows are being inserted wh
en
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen o
r
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believ
e
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:

>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>sql

Dumping SQL Server Variables

In Oracle and other databases there is a way to dump all the values that have
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitations
I don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:

>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>
|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.co m> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations
|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:

>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>

Dumping SQL Server Variables

In Oracle and other databases there is a way to dump all the values that have
been set using the SET command. For example, SET ROWCOUNT.
Is there a way to do this in SQL Server?
The reason I ask is because somewhere in a stream of some 1000 sql files
someone using some SET parameters that are scewing up some down stream files.
In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
insert from one table into another and only 1995 rows are being inserted when
we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
want to find out where along the way things are getting screwed up.
Searching for SET ROWCOUNT has not yielded any result.
What I would like to do is dump all the SET variables to a file or screen or
someplace before the problem file runs.
As an FYI, the files are all being executed via SQL-DMO but I do not believe
there any internal SQL-DMO limitationsI don't think there's a way to get this value - it's a property of the
session that doesn't seem to be stored in any table. You can trace the
workload with SQL Profiler and look for SET ROWCOUNT.
Steve Kass
Drew University
enzo_maini@.dotnetfan.net wrote:
>In Oracle and other databases there is a way to dump all the values that have
>been set using the SET command. For example, SET ROWCOUNT.
>Is there a way to do this in SQL Server?
>The reason I ask is because somewhere in a stream of some 1000 sql files
>someone using some SET parameters that are scewing up some down stream files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a select
>insert from one table into another and only 1995 rows are being inserted when
>we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
>want to find out where along the way things are getting screwed up.
>Searching for SET ROWCOUNT has not yielded any result.
>What I would like to do is dump all the SET variables to a file or screen or
>someplace before the problem file runs.
>As an FYI, the files are all being executed via SQL-DMO but I do not believe
>there any internal SQL-DMO limitations
>|||Hi Enzo
DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
value) for the current connection.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"enzo_maini@.dotnetfan.net"
<enzo_maini@.dotnetfan.net@.discussions.microsoft.com> wrote in message
news:074E8B77-B9A9-4959-A962-CD09660B7793@.microsoft.com...
> In Oracle and other databases there is a way to dump all the values that
> have
> been set using the SET command. For example, SET ROWCOUNT.
> Is there a way to do this in SQL Server?
> The reason I ask is because somewhere in a stream of some 1000 sql files
> someone using some SET parameters that are scewing up some down stream
> files.
> In one case, setting SET ROWCOUNT 0 fixes the problem. We are doing a
> select
> insert from one table into another and only 1995 rows are being inserted
> when
> we know there are 4979. Using SET ROWCOUNT 0 clears up the problem but we
> want to find out where along the way things are getting screwed up.
> Searching for SET ROWCOUNT has not yielded any result.
> What I would like to do is dump all the SET variables to a file or screen
> or
> someplace before the problem file runs.
> As an FYI, the files are all being executed via SQL-DMO but I do not
> believe
> there any internal SQL-DMO limitations|||Thanks for the correction, Kalen!
SK
Kalen Delaney wrote:
>Hi Enzo
>DBCC USEROPTIONS will show you the settings (including the SET ROWCOUNT
>value) for the current connection.
>
>

dtsx iterate rows of table

In a dtsx , is it possible to use a For Loop or ForEach loop to iterate
through the rows of a table , extract the values of certain columns , and
conditionally call a sproc ( that takes as parameters the values of some of
the columns ) ?
How to do this ? Or is a dtsx loop not the recommended way to perform this
task.On Mar 13, 6:33 pm, "John Grandy" <johnagrandy-at-gmail-dot-com>
wrote:
> In a dtsx , is it possible to use a For Loop or ForEach loop to iterate
> through the rows of a table , extract the values of certain columns , and
> conditionally call a sproc ( that takes as parameters the values of some of
> the columns ) ?
> How to do this ? Or is a dtsx loop not the recommended way to perform this
> task.
You may want to repost on the DTS/SSIS message groups. They are:
microsoft.public.sqlserver.dts and
microsoft.public.sqlserver.integrationsvcs. Sorry I could not be of
greater assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer

DTS TRansform Task giving inconsistent results.

Hi,
This is an interesting problem...I have a simple Select statement that will
work perfectly, producing all the right values for the fields as required,
but I get two different results, depending on if I execute the task on its
own,or if it is part of the whole package being executed. Run standalone, it
works fine. As part of thePackage, one of the fields does not populate.
Weird, huh? Anyone got a clue?
Cheers
Wal
Can you show us your data and SELECT statement?
"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> Hi,
> This is an interesting problem...I have a simple Select statement that
will
> work perfectly, producing all the right values for the fields as required,
> but I get two different results, depending on if I execute the task on its
> own,or if it is part of the whole package being executed. Run standalone,
it
> works fine. As part of thePackage, one of the fields does not populate.
> Weird, huh? Anyone got a clue?
> Cheers
>
|||Hi,
here's the SQL, but I cant show you the data - client confidentiality etc...
SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
FROM dbo.tblFact_Contact LEFT JOIN
dbo.tblFact_CaseParts ON
dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
WHERE dbo.tblFact_Contact.Contact_Status_id = 2
Thanks
"Uri Dimant" wrote:

> Wal
> Can you show us your data and SELECT statement?
> "Wal" <Wal@.discussions.microsoft.com> wrote in message
> news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> will
> it
>
>
|||Hi Uri,
I have fixed the problem by deleting the task and recreating
it...infuriating, cos I dont know what caused the problem, so it mght
rec-cur.. Thanks for your interest, Uri
Cheers
"Wal" wrote:
[vbcol=seagreen]
> Hi,
> here's the SQL, but I cant show you the data - client confidentiality etc...
> SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
> FROM dbo.tblFact_Contact LEFT JOIN
> dbo.tblFact_CaseParts ON
> dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
> WHERE dbo.tblFact_Contact.Contact_Status_id = 2
> Thanks
>
> "Uri Dimant" wrote:
|||Don't use the SELECT * syntax, even if you do also specify named columns.
DTS parses the field list at design time. If for whatever reason, if the
ordinal positions change, you will get odd results. The reason the manual
process works is because the * is reparsed befor execution.
Sincerely,
Anthony Thomas

"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:46F56513-B3DB-4D12-AB15-FB909EA3391A@.microsoft.com...
Hi Uri,
I have fixed the problem by deleting the task and recreating
it...infuriating, cos I dont know what caused the problem, so it mght
rec-cur.. Thanks for your interest, Uri
Cheers
"Wal" wrote:

> Hi,
> here's the SQL, but I cant show you the data - client confidentiality
etc...[vbcol=seagreen]
> SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
> FROM dbo.tblFact_Contact LEFT JOIN
> dbo.tblFact_CaseParts ON
> dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
> WHERE dbo.tblFact_Contact.Contact_Status_id = 2
> Thanks
>
> "Uri Dimant" wrote:
required,[vbcol=seagreen]
its[vbcol=seagreen]
standalone,[vbcol=seagreen]
populate.[vbcol=seagreen]
|||Thanks Anthony. It's really nice to know why it gave me problems.
Rgds
Warren
"AnthonyThomas" wrote:

> Don't use the SELECT * syntax, even if you do also specify named columns.
> DTS parses the field list at design time. If for whatever reason, if the
> ordinal positions change, you will get odd results. The reason the manual
> process works is because the * is reparsed befor execution.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Wal" <Wal@.discussions.microsoft.com> wrote in message
> news:46F56513-B3DB-4D12-AB15-FB909EA3391A@.microsoft.com...
> Hi Uri,
> I have fixed the problem by deleting the task and recreating
> it...infuriating, cos I dont know what caused the problem, so it mght
> rec-cur.. Thanks for your interest, Uri
>
> Cheers
> "Wal" wrote:
> etc...
> required,
> its
> standalone,
> populate.
>
>

DTS TRansform Task giving inconsistent results.

Hi,
This is an interesting problem...I have a simple Select statement that will
work perfectly, producing all the right values for the fields as required,
but I get two different results, depending on if I execute the task on its
own,or if it is part of the whole package being executed. Run standalone, it
works fine. As part of thePackage, one of the fields does not populate.
Weird, huh? Anyone got a clue?
CheersWal
Can you show us your data and SELECT statement?
"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> Hi,
> This is an interesting problem...I have a simple Select statement that
will
> work perfectly, producing all the right values for the fields as required,
> but I get two different results, depending on if I execute the task on its
> own,or if it is part of the whole package being executed. Run standalone,
it
> works fine. As part of thePackage, one of the fields does not populate.
> Weird, huh? Anyone got a clue?
> Cheers
>|||Hi,
here's the SQL, but I cant show you the data - client confidentiality etc...
SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
FROM dbo.tblFact_Contact LEFT JOIN
dbo.tblFact_CaseParts ON
dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
WHERE dbo.tblFact_Contact.Contact_Status_id = 2
Thanks
"Uri Dimant" wrote:
> Wal
> Can you show us your data and SELECT statement?
> "Wal" <Wal@.discussions.microsoft.com> wrote in message
> news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> > Hi,
> >
> > This is an interesting problem...I have a simple Select statement that
> will
> > work perfectly, producing all the right values for the fields as required,
> > but I get two different results, depending on if I execute the task on its
> > own,or if it is part of the whole package being executed. Run standalone,
> it
> > works fine. As part of thePackage, one of the fields does not populate.
> > Weird, huh? Anyone got a clue?
> > Cheers
> >
> >
>
>|||Hi Uri,
I have fixed the problem by deleting the task and recreating
it...infuriating, cos I dont know what caused the problem, so it mght
rec-cur.. Thanks for your interest, Uri
Cheers
"Wal" wrote:
> Hi,
> here's the SQL, but I cant show you the data - client confidentiality etc...
> SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
> FROM dbo.tblFact_Contact LEFT JOIN
> dbo.tblFact_CaseParts ON
> dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
> WHERE dbo.tblFact_Contact.Contact_Status_id = 2
> Thanks
>
> "Uri Dimant" wrote:
> > Wal
> > Can you show us your data and SELECT statement?
> >
> > "Wal" <Wal@.discussions.microsoft.com> wrote in message
> > news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> > > Hi,
> > >
> > > This is an interesting problem...I have a simple Select statement that
> > will
> > > work perfectly, producing all the right values for the fields as required,
> > > but I get two different results, depending on if I execute the task on its
> > > own,or if it is part of the whole package being executed. Run standalone,
> > it
> > > works fine. As part of thePackage, one of the fields does not populate.
> > > Weird, huh? Anyone got a clue?
> > > Cheers
> > >
> > >
> >
> >
> >|||Don't use the SELECT * syntax, even if you do also specify named columns.
DTS parses the field list at design time. If for whatever reason, if the
ordinal positions change, you will get odd results. The reason the manual
process works is because the * is reparsed befor execution.
Sincerely,
Anthony Thomas
"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:46F56513-B3DB-4D12-AB15-FB909EA3391A@.microsoft.com...
Hi Uri,
I have fixed the problem by deleting the task and recreating
it...infuriating, cos I dont know what caused the problem, so it mght
rec-cur.. Thanks for your interest, Uri
Cheers
"Wal" wrote:
> Hi,
> here's the SQL, but I cant show you the data - client confidentiality
etc...
> SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
> FROM dbo.tblFact_Contact LEFT JOIN
> dbo.tblFact_CaseParts ON
> dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
> WHERE dbo.tblFact_Contact.Contact_Status_id = 2
> Thanks
>
> "Uri Dimant" wrote:
> > Wal
> > Can you show us your data and SELECT statement?
> >
> > "Wal" <Wal@.discussions.microsoft.com> wrote in message
> > news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> > > Hi,
> > >
> > > This is an interesting problem...I have a simple Select statement that
> > will
> > > work perfectly, producing all the right values for the fields as
required,
> > > but I get two different results, depending on if I execute the task on
its
> > > own,or if it is part of the whole package being executed. Run
standalone,
> > it
> > > works fine. As part of thePackage, one of the fields does not
populate.
> > > Weird, huh? Anyone got a clue?
> > > Cheers
> > >
> > >
> >
> >
> >|||Thanks Anthony. It's really nice to know why it gave me problems.
Rgds
Warren
"AnthonyThomas" wrote:
> Don't use the SELECT * syntax, even if you do also specify named columns.
> DTS parses the field list at design time. If for whatever reason, if the
> ordinal positions change, you will get odd results. The reason the manual
> process works is because the * is reparsed befor execution.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Wal" <Wal@.discussions.microsoft.com> wrote in message
> news:46F56513-B3DB-4D12-AB15-FB909EA3391A@.microsoft.com...
> Hi Uri,
> I have fixed the problem by deleting the task and recreating
> it...infuriating, cos I dont know what caused the problem, so it mght
> rec-cur.. Thanks for your interest, Uri
>
> Cheers
> "Wal" wrote:
> > Hi,
> >
> > here's the SQL, but I cant show you the data - client confidentiality
> etc...
> >
> > SELECT dbo.tblFact_Contact.*, dbo.tblFact_CaseParts.Region
> > FROM dbo.tblFact_Contact LEFT JOIN
> > dbo.tblFact_CaseParts ON
> > dbo.tblFact_Contact.Contact_Id = dbo.tblFact_CaseParts.Contact_Id
> > WHERE dbo.tblFact_Contact.Contact_Status_id = 2
> >
> > Thanks
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> > > Wal
> > > Can you show us your data and SELECT statement?
> > >
> > > "Wal" <Wal@.discussions.microsoft.com> wrote in message
> > > news:E42EA2D5-B22F-43B0-A2E2-44827857602C@.microsoft.com...
> > > > Hi,
> > > >
> > > > This is an interesting problem...I have a simple Select statement that
> > > will
> > > > work perfectly, producing all the right values for the fields as
> required,
> > > > but I get two different results, depending on if I execute the task on
> its
> > > > own,or if it is part of the whole package being executed. Run
> standalone,
> > > it
> > > > works fine. As part of thePackage, one of the fields does not
> populate.
> > > > Weird, huh? Anyone got a clue?
> > > > Cheers
> > > >
> > > >
> > >
> > >
> > >
>
>