duplicate rows?

hi all..
how will i fetch duplicate rows in a table i want the count also
eg in my table if the same company name exist morethan once i want to fetch it
thnks in advance
Jagduplicate rows is not the same thing as multiple rows with the same value in a particular column

presumably if your table has a primary key, you will never have a duplicate row

for your question about company names, try this --select companyname
, count(*) as rows
from daTable
group by companyname
having count(*) > 1|||u r right
its not duplicate its multiple nd thnks for ur querysql

Duplicate record question

In order to check that a new users ID does not already exist in the database I thought it would be a good idea to put the Insert into a Try Catch statement so that I can test for the duplicate record exception and inform the user accordingly. I was also trying to avoid querying the data base before executing the Insert.

The problem is what to actually test for. When the code throws the exception it is a big long string . .

"Violation of PRIMARY KEY constraint 'PK_Users_2__51'. Cannot insert duplicate key in object 'Users'"

I just thought that there has to be something simplar to test for than comparing the exception to the above string.

Can anyone tell me of a better way of doing this ?

(by the way I am only using Web Matrix and MSDE in case it matters)

MarkI would use a stored procedure, check for dups in the SP, and then return a code indicating success or failure.|||Isn't the error code that you'd compare against?

Dumb question

I have a simple flow that loads a data table from some flat files. It works properly but I can't figure out how to add only rows that exist (so I won't get an error from the duplicate ID). I added a lookup that redirects records that don't match any ID, but when I run it I get a timeout error (?). It seems to pick up the right # of records to add, but when it gets to the SQL Server Destination it seems to generate a timeout.

SSIS package "ImportAL3.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [872]: Truncation may occur due to inserting data from data flow column "SampleID" with a length of 4000 to database column "SampleID" with a length of 10.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 17]) + REPLICATE(" ",10 - LEN(TRIM([Column 17])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 2]) + REPLICATE(" ",25 - LEN(TRIM([Column 2])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [872]: Truncation may occur due to inserting data from data flow column "SampleID" with a length of 4000 to database column "SampleID" with a length of 10.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 17]) + REPLICATE(" ",10 - LEN(TRIM([Column 17])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 2]) + REPLICATE(" ",25 - LEN(TRIM([Column 2])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002785.AL3" has started.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 17]) + REPLICATE(" ",10 - LEN(TRIM([Column 17])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Warning: 0x800470D8 at Data Flow Task, Derived Column [1446]: The result string for expression "TRIM([Column 2]) + REPLICATE(" ",25 - LEN(TRIM([Column 2])))" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
Information: 0x400490F4 at Data Flow Task, LookupGrade [2832]: component "LookupGrade" (2832) has cached 11 rows.
Information: 0x400490F4 at Data Flow Task, LookupTestID [5608]: component "LookupTestID" (5608) has cached 0 rows.
Error: 0xC0202009 at Data Flow Task, SQL Server Destination [872]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002785.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002785.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002786.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002786.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002786.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002787.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002787.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002787.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002788.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002788.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002788.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002789.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002789.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002789.AL3" has ended.
Information: 0x402090DC at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002790.AL3" has started.
Information: 0x402090DE at Data Flow Task, Flat File Source [100]: The total number of data rows processed for file "C:\temp\LW002790.AL3" is 1.
Information: 0x402090DD at Data Flow Task, Flat File Source [100]: The processing of file "C:\temp\LW002790.AL3" has ended.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (872)" wrote 6 rows.
Warning: 0x80019002 at Data Flow Task: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Task failed: Data Flow Task
Warning: 0x80019002 at ImportAL3: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "ImportAL3.dtsx" finished: Failure.

You have quite a few errors there.

You might want to replace the SQL Server destination with an OLE DB Destination.

It seems that you have the right idea using the lookup transformation. The red arrow will direct non-matches.|||Thanks for the help, that worked!

Actually, I get just one error - the rest are just truncation warnings. I don't see any need to address them - if the file has a >4,000 character key, it's been corrupted, and I want it to fail in that case! The one error that I did get, is solved by using an OLE DB destination. (I don't intend on scanning all files, every time, normally I should get no duplicates, but for the sake of error tolerance I had to put a check in.)

Now I'm doing a bit of reading to see just why the SQL Server Destination fails in such a case - it emphasises BULK transfers, I'm not sure of the significance of this.

Another dumb question, is there any way to post graphic files here? Since coding is being replaced by a graphical interface, you can't really post the code here.
|||You need to have your SQL Server destination database running on the same machine that the package is stored on. Also, a SQL Server destination won't perform any automatic type conversions -- you have to be sure that the metadata going into it is an exact match for the SQL Server table data types.|||Well, destination database is on the same machine. And when I went to the datatable, cleared out the records in question, and removed the SSIS module that checked for duplicate keys, and just went straight from the flat files into the database, it worked fine.

dual NIC cards SQL Server does not exist

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied
I have two NIC cards on my client machine.
NIC #1 is the corporate network where my SQLServer resides.
NIC #2 is for a local device accessed via TCP/IP.
When I try to open a connection to the corporate database I sometimes get
the error above.
If the other device on NIC #2 is powered off I always find the server.
I know the database is always on NIC card #1.
How can I force DBNETLIB to always connect to try a specific NIC card?
Does using named pipes affect this?
Eggle
Try connecting using the IP address for NIC 1.
-Sue
On Wed, 7 Mar 2007 07:22:03 -0800, RedBear
<RedBear@.discussions.microsoft.com> wrote:

>[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
>denied
>I have two NIC cards on my client machine.
>NIC #1 is the corporate network where my SQLServer resides.
>NIC #2 is for a local device accessed via TCP/IP.
>When I try to open a connection to the corporate database I sometimes get
>the error above.
>If the other device on NIC #2 is powered off I always find the server.
>I know the database is always on NIC card #1.
>How can I force DBNETLIB to always connect to try a specific NIC card?
>Does using named pipes affect this?
|||I have tried IP Address and WINDS name.
The problem is intermittent - connect #1 works, #2 fails, more work more fail.
It seems like the connect is getting routed to NIC #2 where it fails as
expected but never tried on NIC #1 where it would have succeeded.
Can I force a connect onto a specifc NIC card?
The low level connect code should try all NIC cards and declare failure only
after all NIC cards have replied negative or timeed out.
Eggle
"Sue Hoegemeier" wrote:

> Try connecting using the IP address for NIC 1.
> -Sue
> On Wed, 7 Mar 2007 07:22:03 -0800, RedBear
> <RedBear@.discussions.microsoft.com> wrote:
>
>
|||The only way I know of to force it to a specific card is to use that
cards IP address.
Being that it's intermittent, it will be very difficult to
troubleshoot although it's more likely something on the hardware side
of the configuration - maybe something with how the dual NICs are set
up, if you have something like teaming enabled, etc.
And it could be that it's not related to the dual NICs - you could
possible have the intermittent connectivity errors with just one NIC.
The following article has a lot of troubleshooting steps:
http://support.microsoft.com/kb/328306
-Sue
On Mon, 12 Mar 2007 05:18:03 -0700, RedBear
<RedBear@.discussions.microsoft.com> wrote:

>I have tried IP Address and WINDS name.
>The problem is intermittent - connect #1 works, #2 fails, more work more fail.
>It seems like the connect is getting routed to NIC #2 where it fails as
>expected but never tried on NIC #1 where it would have succeeded.
>Can I force a connect onto a specifc NIC card?
>The low level connect code should try all NIC cards and declare failure only
>after all NIC cards have replied negative or timeed out.

DtsConnection -- Consuming DTS DataReader In Code w/ Parameters

I am trying to pass a parameter into the DtsCommand object, but always get the error "a requested parameter does not exist in the package". I have a variable defined at the top scope named EmailPromotion so I tried the following

command.Parameters.Add(new DtsDataParameter("User::EmailPromotion", 1));

and

command.Parameters.Add(new DtsDataParameter("EmailPromotion", 1));

no luck either way
It seems for the variable to be configurable by DtsClient,
it has to be in DtsClient namespace. Create a variable
DtsClient::EmailPromotion, and use the short name
"EmailPromotion" when constructing the parameter object.

Hope this helps,
Michael.

P.S. Sorry, I could not find any documentation for this.
I'll open a bug to get it documented.|||That was it.

Thx|||this is the method that reporting services uses to build reports on top of SSIS. I cannot get this to show up in our VS.NET report designer. I have posted in that forum with no responses. Any help there would be greatly appreciated.

I have already uncommented the SSIS data extension from the rsreportserver.config and restarted the service.

It shows up in web report manager, but not in vs.net

Note using sept ctp and vs.net RC|||I believe you should also uncomment SSIS sections
in RsReportDesigner.config.|||Does this exist for 2005? It looks like things are little different.|||

The RSReportDesigner.config file is in
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

|||that fixed it. much appreciated|||For those of you who are looking for the steps to change the variable namespace, follow these steps:

1) In the SSIS package, open the variable window
2) In the buttons at the top of the variables window, click the one farthest on the right (Choose Variable Columns)
3) Click "Namespace" and press OK
4) For the variable you are populating in code, change the namespace to DtsClient (this is case sensitive)
5) Now, within any objects that utilize the variable, you'll have to change the variable reference (for example, change from User::VariableName to DtsClient::VariableName)
6) In the code of the app consuming the SSIS package, the ParameterName you'll set won't include the DtsClient:: portion of the variable name

DtsConnection -- Consuming DTS DataReader In Code w/ Parameters

I am trying to pass a parameter into the DtsCommand object, but always get the error "a requested parameter does not exist in the package". I have a variable defined at the top scope named EmailPromotion so I tried the following

command.Parameters.Add(new DtsDataParameter("User::EmailPromotion", 1));

and

command.Parameters.Add(new DtsDataParameter("EmailPromotion", 1));

no luck either wayIt seems for the variable to be configurable by DtsClient,
it has to be in DtsClient namespace. Create a variable
DtsClient::EmailPromotion, and use the short name
"EmailPromotion" when constructing the parameter object.

Hope this helps,
Michael.

P.S. Sorry, I could not find any documentation for this.
I'll open a bug to get it documented.|||That was it.

Thx|||this is the method that reporting services uses to build reports on top of SSIS. I cannot get this to show up in our VS.NET report designer. I have posted in that forum with no responses. Any help there would be greatly appreciated.

I have already uncommented the SSIS data extension from the rsreportserver.config and restarted the service.

It shows up in web report manager, but not in vs.net

Note using sept ctp and vs.net RC|||I believe you should also uncomment SSIS sections
in RsReportDesigner.config.|||Does this exist for 2005? It looks like things are little different.|||

The RSReportDesigner.config file is in
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

|||that fixed it. much appreciated|||For those of you who are looking for the steps to change the variable namespace, follow these steps:

1) In the SSIS package, open the variable window
2) In the buttons at the top of the variables window, click the one farthest on the right (Choose Variable Columns)
3) Click "Namespace" and press OK
4) For the variable you are populating in code, change the namespace to DtsClient (this is case sensitive)
5) Now, within any objects that utilize the variable, you'll have to change the variable reference (for example, change from User::VariableName to DtsClient::VariableName)
6) In the code of the app consuming the SSIS package, the ParameterName you'll set won't include the DtsClient:: portion of the variable name