Duplicates

Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...

This sounds like a transact-sql question, not an SSIS question.

But, what is the primary key of the table Dim_date?|||

Your post is not clear enough...

How you are getting a violation PK error when running a Select?

Please provide more details

|||Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...|||

curiousss wrote:

Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...

Fine. But we still don't know anything about the destination table. What is the primary key?|||

Ah, ok, the destination table primary key is Day (int).

|||

How is this Day columnn populated? Is it a surrogate key or is it a date format like 20070214?

You say you want to delete the duplicates in the destination. And then insert them again? Why not skip the records that are already there?

Pipo1

|||See the first thread on the main page of this forum for checking if a record exists, and if it does update it else insert.

Take that logic, and omit the update statement if you don't want to do anything when the record exists.|||Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day

FROM dbo.Dim_Date ?|||

curiousss wrote:

Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day

FROM dbo.Dim_Date ?

truncate table dbo.dim_date will delete the whole table
delete from dbo.dim_date will do the same thing, sort of

This whole scenario you have set up here isn't very clear. If day is the key to DIM_DATE, then there are no duplicates...|||Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.|||

curiousss wrote:

Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

You might want to try a "select distinct column from table" in your lookup.|||Thanks, not eliminated with DISTINCT. Should I leave them or remove them?|||

curiousss wrote:

Thanks, not eliminated with DISTINCT. Should I leave them or remove them?

Only you can make that decision now. You know the data best, and we cannot help you further.

You might want to head over to the Transact-SQL forum here to get help in writing queries to suit your needs. Basically, you need to work on getting a distinct list returned in your lookup query. If you cannot do that due to data issues, then well, you'll need to determine how to work with that data. We cannot tell you which route to take.|||How would I actually remove /delete the duplicates manually(without T-sql). I see the duplicates in destination Preview. Like this?
"DELETE id, salary from dbo.Dim_D where id = 199sql

Duplicates

Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...

This sounds like a transact-sql question, not an SSIS question.

But, what is the primary key of the table Dim_date?|||

Your post is not clear enough...

How you are getting a violation PK error when running a Select?

Please provide more details

|||Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...|||

curiousss wrote:

Ok, let me clarify..
I have a sql command in OLE db Source:
SELECT DISTINCT convert (char(8),date,112) as day, etc etc (works fine)

But Execution data flow Date returns: [OLE DB Destination [2466]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".

Want to solve this by Lookup SQL command, deleting the duplicates in destination. Just dont know how...
Delete day from Dim_Date where ...

Fine. But we still don't know anything about the destination table. What is the primary key?|||

Ah, ok, the destination table primary key is Day (int).

|||

How is this Day columnn populated? Is it a surrogate key or is it a date format like 20070214?

You say you want to delete the duplicates in the destination. And then insert them again? Why not skip the records that are already there?

Pipo1

|||See the first thread on the main page of this forum for checking if a record exists, and if it does update it else insert.

Take that logic, and omit the update statement if you don't want to do anything when the record exists.|||Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day FROM dbo.Dim_Date ?|||

curiousss wrote:

Thank you so much!
How about if I delete and empty the whole table. This command does not seem to work though.

DELETE day FROM dbo.Dim_Date ?

truncate table dbo.dim_date will delete the whole table
delete from dbo.dim_date will do the same thing, sort of

This whole scenario you have set up here isn't very clear. If day is the key to DIM_DATE, then there are no duplicates...|||Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.|||

curiousss wrote:

Thank you...
Problems disappeared when I deleted the tables, and executed them again.

So now all are ok, my Fact_table is green as well! But execution of Fact returns error as below Dim_Salesperson, Dim_demografic. Should I care or let it be?

[Lookup Salesperson [7405]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

You might want to try a "select distinct column from table" in your lookup.|||Thanks, not eliminated with DISTINCT. Should I leave them or remove them?|||

curiousss wrote:

Thanks, not eliminated with DISTINCT. Should I leave them or remove them?

Only you can make that decision now. You know the data best, and we cannot help you further.

You might want to head over to the Transact-SQL forum here to get help in writing queries to suit your needs. Basically, you need to work on getting a distinct list returned in your lookup query. If you cannot do that due to data issues, then well, you'll need to determine how to work with that data. We cannot tell you which route to take.|||How would I actually remove /delete the duplicates manually(without T-sql). I see the duplicates in destination Preview. Like this?
"DELETE id, salary from dbo.Dim_D where id = 199

Duplicate Server Name When Creating ODBC

When creating a SQL Server ODBC connection via Data Source applet on a WXP
workstation, when clicking the drop down menu to select the server, two
instances of the same server appear. We recently migrated our SQL server to
new hardware. Turned off the old server and gave the new server the same name
and IP. Also renamed the instance of SQL on the server to the old name. I am
sure this is the cause but not sure how to remove the duplicate entry on the
workstation. We are only seeing this on one workstation? Thanks for your
feedback.
netwerktek
On this computer do the following:
Go to Start - Run
Type in cliconfg and click OK
This will start the Client Network Utility
Go to the Alias tab
There should be an alias defined for this server
Delete the alias. this the where the second entry is coming from.
(Before deleting note the properties. You may need to add it back if
connections fail after deleting it.)
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||That did it. Thank you very much!
"Rand Boyd [MSFT]" wrote:

> On this computer do the following:
> Go to Start - Run
> Type in cliconfg and click OK
> This will start the Client Network Utility
> Go to the Alias tab
> There should be an alias defined for this server
> Delete the alias. this the where the second entry is coming from.
> (Before deleting note the properties. You may need to add it back if
> connections fail after deleting it.)
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Duplicate Server Name When Creating ODBC

When creating a SQL Server ODBC connection via Data Source applet on a WXP
workstation, when clicking the drop down menu to select the server, two
instances of the same server appear. We recently migrated our SQL server to
new hardware. Turned off the old server and gave the new server the same nam
e
and IP. Also renamed the instance of SQL on the server to the old name. I am
sure this is the cause but not sure how to remove the duplicate entry on the
workstation. We are only seeing this on one workstation? Thanks for your
feedback.
--
netwerktekOn this computer do the following:
Go to Start - Run
Type in cliconfg and click OK
This will start the Client Network Utility
Go to the Alias tab
There should be an alias defined for this server
Delete the alias. this the where the second entry is coming from.
(Before deleting note the properties. You may need to add it back if
connections fail after deleting it.)
Rand
This posting is provided "as is" with no warranties and confers no rights.|||That did it. Thank you very much!
"Rand Boyd [MSFT]" wrote:

> On this computer do the following:
> Go to Start - Run
> Type in cliconfg and click OK
> This will start the Client Network Utility
> Go to the Alias tab
> There should be an alias defined for this server
> Delete the alias. this the where the second entry is coming from.
> (Before deleting note the properties. You may need to add it back if
> connections fail after deleting it.)
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Duplicate script help

I need to select duplicates from a table into a newly
created table. Here is the script I wrote that was
supposed to select about a million rows and is doing
nothing. Can someone help re-write it please? I am very
poor with scripts writing. Thanks you.
select caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
into temp_tabcases FROM tabCases_new n with (Index =
idx_nnn), Nnn_C_Codes
group BY caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
having count(dlastname) > 1;
What does "doing nothing" mean? Are you getting an error? Getting no rows?
Why are you both grouping by and counting DLastName? Try changing your
HAVING clause to:
HAVING COUNT(*) > 1
It's very difficult to know how to check for duplicates when we don't have
any knowledge of what your table or data look like. Please post DDL (in the
form of a CREATE TABLE statement) and sample data (in the form of INSERT
statements), including some sample duplicate data.
"Bianca Riley" <anonymous@.discussions.microsoft.com> wrote in message
news:4f6b01c473e0$3f6a82f0$a501280a@.phx.gbl...
> I need to select duplicates from a table into a newly
> created table. Here is the script I wrote that was
> supposed to select about a million rows and is doing
> nothing. Can someone help re-write it please? I am very
> poor with scripts writing. Thanks you.
> select caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> into temp_tabcases FROM tabCases_new n with (Index =
> idx_nnn), Nnn_C_Codes
> group BY caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> having count(dlastname) > 1;
|||In addition to what Adam has said, what motivated you to use an Index Hint?
Did you test performance with and without the hint? You may harm performance
if you're forcing the use of a non-covering index. What
clustered/nonclustered indexes do you have and on what columns?
David Portas
SQL Server MVP

Duplicate script help

I need to select duplicates from a table into a newly
created table. Here is the script I wrote that was
supposed to select about a million rows and is doing
nothing. Can someone help re-write it please? I am very
poor with scripts writing. Thanks you.
select caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
into temp_tabcases FROM tabCases_new n with (Index = idx_nnn), Nnn_C_Codes
group BY caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
having count(dlastname) > 1;What does "doing nothing" mean? Are you getting an error? Getting no rows?
Why are you both grouping by and counting DLastName? Try changing your
HAVING clause to:
HAVING COUNT(*) > 1
It's very difficult to know how to check for duplicates when we don't have
any knowledge of what your table or data look like. Please post DDL (in the
form of a CREATE TABLE statement) and sample data (in the form of INSERT
statements), including some sample duplicate data.
"Bianca Riley" <anonymous@.discussions.microsoft.com> wrote in message
news:4f6b01c473e0$3f6a82f0$a501280a@.phx.gbl...
> I need to select duplicates from a table into a newly
> created table. Here is the script I wrote that was
> supposed to select about a million rows and is doing
> nothing. Can someone help re-write it please? I am very
> poor with scripts writing. Thanks you.
> select caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> into temp_tabcases FROM tabCases_new n with (Index => idx_nnn), Nnn_C_Codes
> group BY caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> having count(dlastname) > 1;|||In addition to what Adam has said, what motivated you to use an Index Hint?
Did you test performance with and without the hint? You may harm performance
if you're forcing the use of a non-covering index. What
clustered/nonclustered indexes do you have and on what columns?
--
David Portas
SQL Server MVP
--

Duplicate script help

I need to select duplicates from a table into a newly
created table. Here is the script I wrote that was
supposed to select about a million rows and is doing
nothing. Can someone help re-write it please? I am very
poor with scripts writing. Thanks you.
select caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
into temp_tabcases FROM tabCases_new n with (Index =
idx_nnn), Nnn_C_Codes
group BY caseNo, n.StFips, n.CoFips,Code,
CourtType,
[DataSource],
[DType],
[DLastName],
[DFirstName],
[DMidName],
[DSuffix] ,
[DStAddress] ,
[DApartment] ,
[DCity] ,
[DState],
[DZip] ,
[DTaxID]
having count(dlastname) > 1;What does "doing nothing" mean? Are you getting an error? Getting no rows?
Why are you both grouping by and counting DLastName? Try changing your
HAVING clause to:
HAVING COUNT(*) > 1
It's very difficult to know how to check for duplicates when we don't have
any knowledge of what your table or data look like. Please post DDL (in the
form of a CREATE TABLE statement) and sample data (in the form of INSERT
statements), including some sample duplicate data.
"Bianca Riley" <anonymous@.discussions.microsoft.com> wrote in message
news:4f6b01c473e0$3f6a82f0$a501280a@.phx.gbl...
> I need to select duplicates from a table into a newly
> created table. Here is the script I wrote that was
> supposed to select about a million rows and is doing
> nothing. Can someone help re-write it please? I am very
> poor with scripts writing. Thanks you.
> select caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> into temp_tabcases FROM tabCases_new n with (Index =
> idx_nnn), Nnn_C_Codes
> group BY caseNo, n.StFips, n.CoFips,Code,
> CourtType,
> [DataSource],
> [DType],
> [DLastName],
> [DFirstName],
> [DMidName],
> [DSuffix] ,
> [DStAddress] ,
> [DApartment] ,
> [DCity] ,
> [DState],
> [DZip] ,
> [DTaxID]
> having count(dlastname) > 1;|||In addition to what Adam has said, what motivated you to use an Index Hint?
Did you test performance with and without the hint? You may harm performance
if you're forcing the use of a non-covering index. What
clustered/nonclustered indexes do you have and on what columns?
David Portas
SQL Server MVP
--sql

Duplicate Records

HI all,

Would some please be able to help me out here, I have a table in which I need to select all duplicate records and then eventually delete them [Sybase dB]

Can someone pls help me out here with the syntax for the select and then the delete.

The Table has no Primary key @. the moment, as soon as I've cleansed that data i'll assign them.

Many thanksselect col1, col2, col3
from yourtable
group by col1, col2, col3
having count(*) > 1

here col1, col2, col3 are the columns which you want to screen for duplicate combinations

since you want to eventually delete rows, could you please clarify -- delete all duplicates, or delete all but one of each?

and if all but one, how will you determine which one? i.e. what other column will be used for this?

rudy|||Thanks Rudy.....

since you want to eventually delete rows, could you please clarify -- delete all duplicates, or delete all but one of each?
>>>>> I would want to delete all duplicates...only keep on instance of the record

and if all but one, how will you determine which one? i.e. what other column will be used for this?
>>>>>I don't have an Index column, so I'm I'm not too sure how this can be done...
I was thinking of Creating a new sybase table and assign my primary/indez keys and then try to import the data from the original tanle into the new one, all duplicated inserts that violate the primary key would then be aborted...?

But not to sure how to import that data once I've created the structure of the table...

What do you think?

Thanks,
Rocks|||sorry for the delay replying

if you have a primary key in mind, perhaps you could give a few examples of so-called duplicated rows and which one of the group you might want to keep

rudy|||I was thinking of Creating a new sybase table and assign my primary/indez keys and then try to import the data from the original tanle into the new one, all duplicated inserts that violate the primary key would then be aborted...?

If you only have one non-key value:

Select a.key1, a.key2, ..., a.val
FROM table a
WHERE a.val IN (SELECT TOP 1 val from table b
WHERE a.key1 = b.key1 and a.key2 = b.key2 and...)

Then you realize that it's nigh-impossible to compare rows in SQL and you wind up doing something with cursors.|||Managed to sort this out!

Thanks for the help!!!

Rocks

Duplicate Record

Hi guys how do you hide duplicate records, how would I do a select statement for that
In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )
I have about had it with this database I have been asked to make a report out ofwhat do you want to do ?

Lists only unique AccountNo ?
select distinct AccountNo from [2006_CheckHistory]|||i personally don't "hide" duplicate rows, i prevent them from happening|||so there was nothing I could do about the duplicates, they are making the query difficult to create, and this thing is driving me crazy. Some took data and just threw it into a database and called it a day (was a VB programmer)

what do you want to do ?

Lists only unique AccountNo ?
select distinct AccountNo from [2006_CheckHistory]

I'm trying to make the AccountNo unique, you dont need it entered more then once in a table.|||I want to delete them I just want to create a query that shows me all the duplicates and then delete them, leaving only one AccountNo instead of several. Does that make sense??|||okay, you want one row per AccountNo

what about the other columns? which values would you like from those?

here's an example of the problem you could be facing --
AccountNo Name Age City
1234 Fred 23 Chicago
1234 Fred 23 Waukegan
1234 Fred 26 Chicago
1234 Todd 23 Chicago
5678 Mary 16 Milwaukee
5678 Mary 15 Milwaukee
5678 Mary 17 Milwaukeenow how are we supposed to know which row to keep?|||Yeah, the problem that I need to work around is when I have it as a report I cant create a total per AccountNo it just totals it for all the records rather then per account... like if there is an account for number 000111222 then I cant get the total amount for just that record. I need one record to show and its total for that year and this is such a BIG PAIN. I didnt create this database so its a matter of some serious fixing. The problem is gettign this done before the 22nd of January...ACCCKKKK HELP PLEASE!!!!!!!!!!!!!!

Then as soon as I'm done I'm going to do some serious restructuring|||It seems that you are talking about one table, but think about those tables which are related with this one.

Whatever you plan, plan considering all related tables in the database.|||Take your offending table, and run this query against just that table (no, joins, no fancy stuff, just this query alone):SELECT Count(AccountNo), Count(DISTINCT AccountNo)
FROM OffendingTable-PatP|||Oh yeah, while you are at it could you explain what you consider to be a "duplicate record" ? Does that mean that all columns in more than one row are identical? Does it mean that one column (AccontNo) in more than one row are identical? Does it mean something completely different that I haven't thought of yet?

-PatP|||Thank you so much guys,

It means that there is identical [AccountNo] in the table, these are businesses, now when the business changes ownership they indicate that with a letter like A, B..etc, or if theres equipment at that business thats indicated with a letter. I was thinking of making the AccountNo Unique by getting rid of the duplicates and making it unique. And having a seperate table with the A, B and the date they changed hands linked to the AccountNo table, since this is table would reflect changes to the [AccountNo] table. The [AccountNo] table would have the information concering the business, like address, name of business..etc. Does that make sense??|||Only problem is what about the other columns...which values do you want? Does it matter to you? It usually does.

Post the DDL of the table and some sample data, and what you want in the final table...

And what are the report req's?|||Only problem is what about the other columns...which values do you want? Does it matter to you? It usually does.

Post the DDL of the table and some sample data, and what you want in the final table...

And what are the report req's?

OwnerManagerID AccountNo CheckDate CheckNumber CheckAmount OwnerAddress1 NameFor1099 OwnerCity OwnerState OwnerZip
A 00010002 2006-09-20 37065 $621.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-03-20 19006 $679.25 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-10-23 38341 $587.13 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-02-16 28548 $723.38 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-06-20 33119 $994.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-08-23 35806 $578.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-07-24 34551 $488.50 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-05-18 31820 $573.50 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-04-21 30734 $582.88 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-12-20 40892 $614.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-01-17 24366 $427.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-11-17 39614 $730.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010004 2006-06-22 23676 $1,972.79 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-02-09 23644 $1,737.02 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-03-10 23660 $1,632.50 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-05-31 32492 $1,586.44 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-09-12 36608 $1,778.60 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-07-20 34540 $1,715.68 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-04-11 30257 $1,951.40 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-08-07 35355 $1,362.58 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-01-06 25876 $1,834.49
A 00010004 2006-12-06 40426 $1,647.72 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-11-13 39605 $1,795.44 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-11-06 39164 $1,524.11 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937|||I want in the ownermanager_table Ownermanagerid, Address, city, state zip..ect, AccountNo, fedtaxid.

That should be one table and the other table should be the check amount, checkdate, name of bank..etc|||sorry, that looked like really private information so i obfuscated it|||Thank you appreciate it|||WOOHOO I GOT THE QUERY I NEEDED ITS OVER THIS HORRIBLE NIGHTMARE IS OVER....WOOOHOOOOOOOO!!!!!!!!!!!!!!!!!!!

I'M SO HAPPY I COULD SCREAM...Good grief this person just took live DATA and stuck in a table and just called it a day, no documentation no nothing. The whole thing is structured so badly its a nightmare...EEEEEEEEEEEEEEEEEE!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!

Thank you guys

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

Dummy Where Clause Allowing Dummy Select Of Data - Utilizing Where value = 1

Years ago, I remember while doing maintenance on a stored procedure seeing a 'Select x, y, z Where 'some value' = 1.

The function of this, I believe was to make the select work but not retrieve any actual values.

I am attempting to use this in an 'Insert Into Select values From' statement. This insert uses multiple selects via unions and I need a final dummy Select statement with no Where criteria.

What I am thinking may not even apply to what I need to do here.

If you recognize something even remotely near what I am trying to get across I would appreciate your sending me the code.

Another solution for me is just inserting one row with a final RecId = 6 and ' ' or 0 values for the other fields into a table

but I was hoping this would work.

Example:

Insert Into table

Select

1 as RecId,

' ' as field1,

field2

From test1

Where field2 = 'CA'

Union

Select

2 as RecId,

' ' as field1,

field2

From test1

Where field2 = 'NJ'

Union

/*Final Select */

Select

6 as RecId,

' ' as field1,

field2

From test1

Where 'some value' = 1'

Thanks much for your assistance!!!

TADEG

If you want to create a dummy select that never returns rows, just add WHERE 0=1 to the end.

Code Snippet

SELECT x,y,x FROM Table WHERE 0=1

Dumb Date Format Question

I want the following query to result in "2004/10".
SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
From myTable
However, I get the error:
Syntax error converting the varchar value '/' to a column of data type int.
Is there a better way to do this?
Also, I realize I may be asking this in the wrong forum. Is there a place
for MSSQL newbies to ask their dumb questions?
Thank you!
MatthewHi Matthew
Ask whatever you like here, just don't call anything a dumb question. That
way, you give people who have only been using the product a bit longer than
you a chance to answer questions and feel like they have learned something.
But nobody wants to feel like they can only answer DUMB questions. ;-)
& is the bitwise 'AND' operator, and its operands must be integers. Please
read about bit operations in the Books Online.
My guess is that you want to concatenate strings. In TSQL we use '+' for
concatenation.
But, it still won't work because datepart returns a numeric value. Try using
datename. Or converting to character before you concatenate.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:O6wl$vfvEHA.3276@.TK2MSFTNGP15.phx.gbl...
>I want the following query to result in "2004/10".
> SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
> From myTable
> However, I get the error:
> Syntax error converting the varchar value '/' to a column of data type
> int.
> Is there a better way to do this?
> Also, I realize I may be asking this in the wrong forum. Is there a place
> for MSSQL newbies to ask their dumb questions?
> Thank you!
> Matthew
>|||> Ask whatever you like here, just don't call anything a dumb question. That
> way, you give people who have only been using the product a bit longer
> than you a chance to answer questions and feel like they have learned
> something. But nobody wants to feel like they can only answer DUMB
> questions. ;-)
Sorry about that. I'll be more careful in the future.

> My guess is that you want to concatenate strings. In TSQL we use '+' for
> concatenation.
Yup, that's right.

> But, it still won't work because datepart returns a numeric value. Try
> using datename. Or converting to character before you concatenate.
OK, I'll give that a try.
Thank you!
Matthew

Dumb Date Format Question

I want the following query to result in "2004/10".
SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
From myTable
However, I get the error:
Syntax error converting the varchar value '/' to a column of data type int.
Is there a better way to do this?
Also, I realize I may be asking this in the wrong forum. Is there a place
for MSSQL newbies to ask their dumb questions?
Thank you!
Matthew
Hi Matthew
Ask whatever you like here, just don't call anything a dumb question. That
way, you give people who have only been using the product a bit longer than
you a chance to answer questions and feel like they have learned something.
But nobody wants to feel like they can only answer DUMB questions. ;-)
& is the bitwise 'AND' operator, and its operands must be integers. Please
read about bit operations in the Books Online.
My guess is that you want to concatenate strings. In TSQL we use '+' for
concatenation.
But, it still won't work because datepart returns a numeric value. Try using
datename. Or converting to character before you concatenate.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew" <turn.deletethis@.alltel.net> wrote in message
news:O6wl$vfvEHA.3276@.TK2MSFTNGP15.phx.gbl...
>I want the following query to result in "2004/10".
> SELECT DATEPART(yyyy, datein) & '/' & DATEPART(mm, datein) AS theDate
> From myTable
> However, I get the error:
> Syntax error converting the varchar value '/' to a column of data type
> int.
> Is there a better way to do this?
> Also, I realize I may be asking this in the wrong forum. Is there a place
> for MSSQL newbies to ask their dumb questions?
> Thank you!
> Matthew
>
|||> Ask whatever you like here, just don't call anything a dumb question. That
> way, you give people who have only been using the product a bit longer
> than you a chance to answer questions and feel like they have learned
> something. But nobody wants to feel like they can only answer DUMB
> questions. ;-)
Sorry about that. I'll be more careful in the future.

> My guess is that you want to concatenate strings. In TSQL we use '+' for
> concatenation.
Yup, that's right.

> But, it still won't work because datepart returns a numeric value. Try
> using datename. Or converting to character before you concatenate.
OK, I'll give that a try.
Thank you!
Matthew

Duduce difference

Hi all,
I have a query that adds up two tables as below:
SELECT SUM(RPAAP / 100) AS [Sales Ledger]
FROM F03B11
SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3)
I would like to take this a step further and deduce the difference
between the two. However I?m not sure how this should be done.
Any help would be most welcome.
Sam
?new person on windows 2000?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Use sub queries thus:
select [Sales Ledger] - gl as difference
from
(SELECT SUM(RPAAP / 100) AS [Sales Ledger] FROM F03B11) Q1 join
(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06
+ GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3)) Q2
Assuming Q2 returns just the one row
"Sam G" <moby@.spamhole.com> wrote in message
news:%23aCWsvTnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a query that adds up two tables as below:
> SELECT SUM(RPAAP / 100) AS [Sales Ledger]
> FROM F03B11
> SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
> GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
> AS GL
> FROM F0902
> WHERE (GBAID = '00667809') AND (GBFY = 3)
> I would like to take this a step further and deduce the difference
> between the two. However I'm not sure how this should be done.
> Any help would be most welcome.
> Sam
> "new person on windows 2000"
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003|||SELECT SUM(RPAAP / 100) AS [Sales Ledger] -
(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
AS GL
FROM F0902
WHERE (GBAID = '00667809') AND (GBFY = 3))
FROM F03B11
Hope this helps
Wayne Snyder, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sam G" <moby@.spamhole.com> wrote in message
news:%23aCWsvTnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a query that adds up two tables as below:
> SELECT SUM(RPAAP / 100) AS [Sales Ledger]
> FROM F03B11
> SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +
> GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100
> AS GL
> FROM F0902
> WHERE (GBAID = '00667809') AND (GBFY = 3)
> I would like to take this a step further and deduce the difference
> between the two. However I'm not sure how this should be done.
> Any help would be most welcome.
> Sam
> "new person on windows 2000"
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

DTS: single SELECT from 2 databases possible?

Just wondering, in DTS, can I run a SELECT script that selects from 2 different databases (both on the same server)?

I can run this in Sql Query Analyzer, but in a DTS, it doesnt accept my database name prefixes:

SELECT
a.something
FROM
DB_ONE.dbo.product a, DB_ONE.dbo.mp_brand b, DB_TWO.dbo.lk_pcat_cutover c
WHERE
a.PCat <> c.Pcat

(where DB_ONE and DB_TWO are the 2 different db names)

I have one connection to DB_ONE... does this mean I cant access DB_TWO when using this connection? I want to try and avoid using a temporay table for storing DB_TWO's data in DB_ONE... Is this possible?

Thanks,

Andre

Dont know if this is the one and only answer, but by changing the datasource to the MASTER db, it then allows me to use my original SQL.

DTS: import large database

Importing data from an Access database, I cannot overcome the limit of 1,000 records.
In DTS, I "copy one or more tables", select tables, run, and cannot see my 1,052 entries.
Where can I set a max size of ~1,500 in my sql target base?There's a limit on what you can copy? By default, I have been able to transfer (even from Access) more than 100,000 records at a time. Heck, from other db's, I have been able to transfer MILLIONS of records by default.

A quick workaround would be to do an export to text (preferrably .csv) and import that into SQL. You can even do that through SQL, I think, by selecting your source as Access and your target as Text file through DTS.

DTS using #temp tables

I have a rather complex SP that creates a #temp table and then populates tha
t
table using several select/inserts/update statements. I need the results of
this #table to be exported to external text file nightly so that our
mainframe FTPs can grab it.
DTS apparently wont let me use #temp tables. I get invalid object errors.
Ive heard of global ##temp, but even changing the table all the references
to be ##table isn’t solving the problem.
Is there commands I can place in the SP to create the external file without
using DTS, or can I define the #temp table in a way that DTS can see it?
Help appreciated
--
JP
.NET Software DeveloperHi
This seems similar to http://tinyurl.com/82fqd
Temporary tables have a limited scope see the section in:
http://msdn.microsoft.com/library/d...r />
_4hk5.asp
Depending on what you are trying to do, you may not require the temporary
table or you could use a permanent table that you clear down before each run
.
Alternatively using a derived table or use of the CASE statement may be
possible options.
John
"JP" wrote:

> I have a rather complex SP that creates a #temp table and then populates t
hat
> table using several select/inserts/update statements. I need the results o
f
> this #table to be exported to external text file nightly so that our
> mainframe FTPs can grab it.
> DTS apparently wont let me use #temp tables. I get invalid object errors.
> Ive heard of global ##temp, but even changing the table all the reference
s
> to be ##table isn’t solving the problem.
> Is there commands I can place in the SP to create the external file withou
t
> using DTS, or can I define the #temp table in a way that DTS can see it?
> Help appreciated
> --
> JP
> .NET Software 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
> > > >
> > > >
> > >
> > >
> > >
>
>

DTS Transfer of 'view over view' fails

Hi - I'm doing a simple SQL object transfer.
If I have a SQL view, aaView that, is for example
Create view aaView as Select * from xxView
View aaView will not be transferred. It appears this is because the creation
of aaView fails because of the reference to xxView (since DTS appears to
re-create the views in alphabetical order of name).
How do I get around this simple issue?
THanks,
Paul.
There is no *good* way...
You can change the name of the view ( painful)... Or simply add another Move
Objects task after the initial and select only the *funky* stuff...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul W" <qqq@.qqq.com> wrote in message
news:eBlcKp1EFHA.2456@.TK2MSFTNGP10.phx.gbl...
> Hi - I'm doing a simple SQL object transfer.
> If I have a SQL view, aaView that, is for example
> Create view aaView as Select * from xxView
> View aaView will not be transferred. It appears this is because the
> creation
> of aaView fails because of the reference to xxView (since DTS appears to
> re-create the views in alphabetical order of name).
> How do I get around this simple issue?
> THanks,
> Paul.
>