Merge Join - HELP

Hi,

I have a SQL Statatment:

SELECT * FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X
AND A.Y= B.Y

When i execute this code in sql server returns 549 lines. I created a package with two oledb sources one for each table, sorted the tables with fields X and Y after placed a Merge Join with the fields:

A.Y join B.Y order 1

A.X join B.X order 2

both fields with the Join Key checked

But my package return 411 lines.

What's happened? :(

When a i have the code:

SELECT A.X, A.Y, B.X, B.Y
FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X

When i did the join only one field SSIS worked fine, sql server returns 622 and SSIS returns 622 lines.

Please help-me...

Thanks,

Andr

Are your join keys all integers or does one of them have characters. SSIS is case senstive while SQL Server by default isn't. You may want to check this.|||

Also check that the inputs of the Merge Join are sorted...

Rafael Salas

|||

Thanks Jay,

My columns are characters type and my oledb source 1 columns is lowercase and oledb source 2 columns is uppercase.

I created a Derived Column replaced my column to uppercase. :)

|||Character Map Transform might be more appropriate to change case|||

Rafael Salas wrote:

Also check that the inputs of the Merge Join are sorted...

Rafael Salas

Very good point. And note that setting IsSorted=1 on an output does NOT sort the data.

-Jamie

Merge Join - HELP

Hi,

I have a SQL Statatment:

SELECT * FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X
AND A.Y= B.Y

When i execute this code in sql server returns 549 lines. I created a package with two oledb sources one for each table, sorted the tables with fields X and Y after placed a Merge Join with the fields:

A.Y join B.Y order 1

A.X join B.X order 2

both fields with the Join Key checked

But my package return 411 lines.

What's happened? :(

When a i have the code:

SELECT A.X, A.Y, B.X, B.Y
FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X

When i did the join only one field SSIS worked fine, sql server returns 622 and SSIS returns 622 lines.

Please help-me...

Thanks,

Andr

Are your join keys all integers or does one of them have characters. SSIS is case senstive while SQL Server by default isn't. You may want to check this.|||

Also check that the inputs of the Merge Join are sorted...

Rafael Salas

|||

Thanks Jay,

My columns are characters type and my oledb source 1 columns is lowercase and oledb source 2 columns is uppercase.

I created a Derived Column replaced my column to uppercase. :)

|||Character Map Transform might be more appropriate to change case|||

Rafael Salas wrote:

Also check that the inputs of the Merge Join are sorted...

Rafael Salas

Very good point. And note that setting IsSorted=1 on an output does NOT sort the data.

-Jamie

MERGE

Hi all,
What would be SQL Server's version of this Oracle code:
****************************************
*********************
MERGE INTO destination_table dest
USING (SELECT col1, col2, col3 FROM source_table) source
ON (dest.col1 = source.col1)
WHEN MATCHED THEN
UPDATE SET dest.col2 = source.col2,
dest.col3 = source.col3
WHEN NOT MATCHED THEN
INSERT (dest.col1, dest.col2, dest.col3)
VALUES (source.col1, source.col2, source.col3)
****************************************
**************************
Thanks in advance
GoranGoran Djuranovic (goran.djuranovic@.newsgroups.nospam) writes:
> What would be SQL Server's version of this Oracle code:
> ****************************************
*********************
> MERGE INTO destination_table dest
> USING (SELECT col1, col2, col3 FROM source_table) source
> ON (dest.col1 = source.col1)
> WHEN MATCHED THEN
> UPDATE SET dest.col2 = source.col2,
> dest.col3 = source.col3
> WHEN NOT MATCHED THEN
> INSERT (dest.col1, dest.col2, dest.col3)
> VALUES (source.col1, source.col2, source.col3)
> ****************************************
**************************
Alas, MERGE is implemented in SQL Server, so you will need to do:
UPDATE ...
INSERT ...
SELECT ...
WHERE NOT EXISTS
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97DC4CB7551Yazorman@.127.0.0.1...
> Goran Djuranovic (goran.djuranovic@.newsgroups.nospam) writes:
>
> Alas, MERGE is implemented in SQL Server, so you will need to do:
> UPDATE ...
> INSERT ...
> SELECT ...
> WHERE NOT EXISTS
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Memory Leak Reading Appointment from MS Exchange Mailbox

Hi,

When using the following code to read an appointment from a MS Exchange mailbox the application experiences a memory leak.

I can provide a test application that easily demonstrates the issue.

ADODB.Connection _Conn = new ADODB.Connection();

_Conn.Provider = "exoledb.datasource";

try

{

this.Cursor = Cursors.WaitCursor;

_Conn.Open("http://" + Environment.UserDomainName + "." + Environment.MachineName + "/Exchange/" + this.txtMailbox.Text + "/Calendar", "", "", 0);

CDO.Appointment cdoAppointment = new CDO.Appointment();

try

{

for (int i = 1; i < int.Parse(this.txtNoOfTestReads.Text); i++)

{

cdoAppointment.DataSource.Open("http://" + Environment.UserDomainName + "." + Environment.MachineName + "/Exchange/" + this.txtMailbox.Text + "/Calendar/" + this.txtApptFileName.Text + ".eml", _Conn, ADODB.ConnectModeEnum.adModeReadWrite, ADODB.RecordCreateOptionsEnum.adFailIfNotExists, ADODB.RecordOpenOptionsEnum.adOpenSource, "", "");

}

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

cdoAppointment = null;

}

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

if (_Conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)

_Conn.Close();

_Conn = null;

this.Cursor = Cursors.Default;

}

Regards,

Craig

I would post this to the exchange dl ->

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.exchange.applications

This could just be a GC issue, try adding a line in the loop like below:

System.Gc.Collect();

System.Gc.WaitForPendingFinalizers();

Matt