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

DTSrun permissions

Hi

We are attempting to run a dtsrun statement remotely, via a stored procedure. We are getting the errors below, although I have ran this from a c: prompt on my PC.

C:\>dtsrun /SNT_LAKES02 /Usa /P-- /Nbmmenudev /Ap_menucode:8=101

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.

We have also tried this with the /E option with the same results.

Can anyone offer any help - thanks in advanceexec master..xp_cmdshell 'dtsrun /SNT_LAKES02 /Usa /P'

well you most likely will need to have permissions to exec master..xp_cmdshell. This most likely is the root of you problems, that is what was my issue when doing this for the first time.

DTSRUN

When I am on the sql server and I issue my dtsrun statement, the package
runs fine, however, when I run it from another server, it reports that the
package ran successfully, however, upon further inspection I see that the
package did not run at all.
Any ideas ?
I would check security issues first.
Secondly, what do you mean by it ran successfully? Does your DTS package
throw errors when it runs into problems, or does it just go merrily on about
it's business and report that it finished all of it's steps. (Whether they
were successful or not).
Not much help, but more information would be useful and I'm sure others here
will have more ideas for you.
Rick Sawtell
MCT, MCSD, MCDBA
"rob" <rwc1960@.bellsouth.net> wrote in message
news:TfH%c.75928$N11.50380@.bignews5.bellsouth.net. ..
> When I am on the sql server and I issue my dtsrun statement, the package
> runs fine, however, when I run it from another server, it reports that the
> package ran successfully, however, upon further inspection I see that the
> package did not run at all.
> Any ideas ?
>
>
|||I execute the following from the DOS prompt from a server other than the SQL
server...
C:\dtsrun /SSqlServerIPaddressHere /Uusername /Ppasword /Npackagename
1. Messages appear in the DOS window which state that the package is running
2. The final message states that the package successfully completed
However, upon further review, none of the tasks in the package were
completed.
I did get permission errors to begin with, but after I fixed them, I just
get that the package completed successfully when in fact, none of the tasks
were completed.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:uDER1vclEHA.644@.tk2msftngp13.phx.gbl...
> I would check security issues first.
> Secondly, what do you mean by it ran successfully? Does your DTS package
> throw errors when it runs into problems, or does it just go merrily on
about
> it's business and report that it finished all of it's steps. (Whether
they
> were successful or not).
>
> Not much help, but more information would be useful and I'm sure others
here[vbcol=seagreen]
> will have more ideas for you.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "rob" <rwc1960@.bellsouth.net> wrote in message
> news:TfH%c.75928$N11.50380@.bignews5.bellsouth.net. ..
the[vbcol=seagreen]
the
>
|||Try using the /L option and specifying a log file for the package status
information. This may help you narrow down the problems.
When you run the package from the SQL Server machine, are you logged in as a
local Admin? I'm not sure about the security issues with the DTS packages,
but that is still the first place I would be looking in this case. Check
the log file and see what information is coming back to you.
Rick Sawtell
MCT, MCSD, MCDBA
"rob" <rwc1960@.bellsouth.net> wrote in message
news:GcI%c.76012$N11.16495@.bignews5.bellsouth.net. ..
> I execute the following from the DOS prompt from a server other than the
SQL
> server...
> C:\dtsrun /SSqlServerIPaddressHere /Uusername /Ppasword /Npackagename
> 1. Messages appear in the DOS window which state that the package is
running
> 2. The final message states that the package successfully completed
> However, upon further review, none of the tasks in the package were
> completed.
> I did get permission errors to begin with, but after I fixed them, I just
> get that the package completed successfully when in fact, none of the
tasks[vbcol=seagreen]
> were completed.
>
>
>
> "Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
> news:uDER1vclEHA.644@.tk2msftngp13.phx.gbl...
package[vbcol=seagreen]
> about
> they
> here
package
> the
> the
>

DTSRUN

When I am on the sql server and I issue my dtsrun statement, the package
runs fine, however, when I run it from another server, it reports that the
package ran successfully, however, upon further inspection I see that the
package did not run at all.
Any ideas ?I would check security issues first.
Secondly, what do you mean by it ran successfully? Does your DTS package
throw errors when it runs into problems, or does it just go merrily on about
it's business and report that it finished all of it's steps. (Whether they
were successful or not).
Not much help, but more information would be useful and I'm sure others here
will have more ideas for you.
Rick Sawtell
MCT, MCSD, MCDBA
"rob" <rwc1960@.bellsouth.net> wrote in message
news:TfH%c.75928$N11.50380@.bignews5.bellsouth.net...
> When I am on the sql server and I issue my dtsrun statement, the package
> runs fine, however, when I run it from another server, it reports that the
> package ran successfully, however, upon further inspection I see that the
> package did not run at all.
> Any ideas ?
>
>|||I execute the following from the DOS prompt from a server other than the SQL
server...
C:\dtsrun /SSqlServerIPaddressHere /Uusername /Ppasword /Npackagename
1. Messages appear in the DOS window which state that the package is running
2. The final message states that the package successfully completed
However, upon further review, none of the tasks in the package were
completed.
I did get permission errors to begin with, but after I fixed them, I just
get that the package completed successfully when in fact, none of the tasks
were completed.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:uDER1vclEHA.644@.tk2msftngp13.phx.gbl...
> I would check security issues first.
> Secondly, what do you mean by it ran successfully? Does your DTS package
> throw errors when it runs into problems, or does it just go merrily on
about
> it's business and report that it finished all of it's steps. (Whether
they
> were successful or not).
>
> Not much help, but more information would be useful and I'm sure others
here
> will have more ideas for you.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "rob" <rwc1960@.bellsouth.net> wrote in message
> news:TfH%c.75928$N11.50380@.bignews5.bellsouth.net...
> > When I am on the sql server and I issue my dtsrun statement, the package
> > runs fine, however, when I run it from another server, it reports that
the
> > package ran successfully, however, upon further inspection I see that
the
> > package did not run at all.
> >
> > Any ideas ?
> >
> >
> >
>|||Try using the /L option and specifying a log file for the package status
information. This may help you narrow down the problems.
When you run the package from the SQL Server machine, are you logged in as a
local Admin? I'm not sure about the security issues with the DTS packages,
but that is still the first place I would be looking in this case. Check
the log file and see what information is coming back to you.
Rick Sawtell
MCT, MCSD, MCDBA
"rob" <rwc1960@.bellsouth.net> wrote in message
news:GcI%c.76012$N11.16495@.bignews5.bellsouth.net...
> I execute the following from the DOS prompt from a server other than the
SQL
> server...
> C:\dtsrun /SSqlServerIPaddressHere /Uusername /Ppasword /Npackagename
> 1. Messages appear in the DOS window which state that the package is
running
> 2. The final message states that the package successfully completed
> However, upon further review, none of the tasks in the package were
> completed.
> I did get permission errors to begin with, but after I fixed them, I just
> get that the package completed successfully when in fact, none of the
tasks
> were completed.
>
>
>
> "Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
> news:uDER1vclEHA.644@.tk2msftngp13.phx.gbl...
> > I would check security issues first.
> >
> > Secondly, what do you mean by it ran successfully? Does your DTS
package
> > throw errors when it runs into problems, or does it just go merrily on
> about
> > it's business and report that it finished all of it's steps. (Whether
> they
> > were successful or not).
> >
> >
> > Not much help, but more information would be useful and I'm sure others
> here
> > will have more ideas for you.
> >
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> > "rob" <rwc1960@.bellsouth.net> wrote in message
> > news:TfH%c.75928$N11.50380@.bignews5.bellsouth.net...
> > > When I am on the sql server and I issue my dtsrun statement, the
package
> > > runs fine, however, when I run it from another server, it reports that
> the
> > > package ran successfully, however, upon further inspection I see that
> the
> > > package did not run at all.
> > >
> > > Any ideas ?
> > >
> > >
> > >
> >
> >
>

DTSLookup error handling

Hello all,
Does any one know how I can capture the sql error message when the sql statement in DTSLookup step fails?
e.g.
I have this sql statement in a DTSLookup task:
insert into table1 (col1) values (?); select @.@.ERROR

Let's say for some reason (bad data), my insert statement fails and I want to know why. So I added "select @.@.ERROR" to capture the error. However, from the Lookup window, I cannot save the result of @.@.ERROR to a global variable or to an output parameter. I want to log this error in the next step that handles the OnFailure workflow, but I don't know how.

Can someone help please?

Thanks,
K.Hello all,

Can someone help please?

Thanks,
K.

Check out this link (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q221193).

From what I recall, you cannot handle DTS errors in VB Script, which is maddening to me. We sorta got around this by creating a VB DLL and then instantiated the VB DLL from within a calling DTS package. I've never been really happy with the solution, but I'm just the production DBA and not the "real" DBA :rolleyes: .

Regards,

hmscott

DTSDestination - equivalent in SSIS?

Hi,

I am converting an ActiveX script. I am looking to replace the functionality of this statement:

If DTSDestination("CartoonCharacter") = "Felix" Then ...

So, it's checking the value in the TABLE, not the file.

I need to rewrite this for SSIS. What is the recommended approach? Is there a built-in task that will do this and how?

Thanks much

You'd use a lookup component to query the destination and then later use a derived column or a conditional split transformation to compare the lookup result with the source value (or a hardcoded value, if you wish)

DTS w/ Oracle Issue

Im having an issue with DTS and I dont know if anyone else has
experienced this...In my transformation I have a sql statement that
executes against oracle..in it I'm selecting COUNT(DISTINCT
I.PX_INVOICE_ID) ...however when the DTS package executes I get an
error stating that "DISTINCTI"."PX_INVOICE_ID" is an invalid
identifier..why is it concatenting this?

Thanks

JimJimbo wrote:

Quote:

Originally Posted by

Im having an issue with DTS and I dont know if anyone else has
experienced this...In my transformation I have a sql statement that
executes against oracle..in it I'm selecting COUNT(DISTINCT
I.PX_INVOICE_ID) ...however when the DTS package executes I get an
error stating that "DISTINCTI"."PX_INVOICE_ID" is an invalid
identifier..why is it concatenting this?
>
Thanks
>
Jim

It isn't Oracle ... so what version, what connection driver, etc?

But do you really have a schema named "I"? Seems strange.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||I agree with Daniel that this is most likely a client driver issue. I
haven't run into the problem myself but I've only run only simple SQL
statements using the Oracle drives. Can you post the full SQL statement?

You might quoting the identifiers as a possible workaround. For example:

SELECT COUNT("I"."PX_INVOICE_ID")
FROM "MYSCHEMA"."INVOICES" I;

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Jimbo" <jamesferris@.hotmail.comwrote in message
news:1180626867.763717.260180@.j4g2000prf.googlegro ups.com...

Quote:

Originally Posted by

Im having an issue with DTS and I dont know if anyone else has
experienced this...In my transformation I have a sql statement that
executes against oracle..in it I'm selecting COUNT(DISTINCT
I.PX_INVOICE_ID) ...however when the DTS package executes I get an
error stating that "DISTINCTI"."PX_INVOICE_ID" is an invalid
identifier..why is it concatenting this?
>
Thanks
>
Jim
>

|||It appears to be with the OLE data provider...when upgrading it to 10
it worked..also the ODBC driver for Oracle worked...really weird

-Jim

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
> > > >
> > > >
> > >
> > >
> > >
>
>