I am trying to use the merge join example in the following link. To import new records only.
http://www.sqlis.com/311.aspx
The problem is that for some unkown reason the join is not woeking correctly. One of the records is incorrectly showing a NULL on the output. This would indicate that it would be a new record, but it is not it already exists in the new table.
I created a dummy table in SQL and executed the same join and I always get the right answer. What the heck could be wrong?
For example. Table A has 20 records Table B has 3 records. Table B has the new records I want to import into Table A. The package runs corectly the first time, only importing the 3 new records. Then the next time the package runs it shows 1 of the 3 records as being new still, and tries to import the record causing a PK error. Adding a watch to the MERGE output shows that the one record has a NULL on the join.
OLEDB source 1 SELECT ... ,[MANUAL DCD ID] <-- this column set to sort order = 1 ... FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2 SELECT ... ,[Bo Tkt Num] <-- this column set to sort order = 1 ... FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above. join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column) ... 4-400-8000119 4-400-8000120 4-400-8000121 4-400-8000122 <--row not joining 4-400-8000123 4-400-8000124 ...
result of source2 (..tkt num column) ... 4-400-1000118 4-400-1000119 4-400-1000120 4-400-1000121 4-400-1000122 <--row not joining 4-400-1000123 4-400-1000124 4-400-1000125 ...
All other rows are joining as expected. Why is it failing for this one row?
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail from tblcodes 1, tblelements 2
I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.) THANKS
I have this code below, one thing i am trying to do is merge 2 columns, it seems what i have now is suming them can someone give me a hint or two thanks.
Code Snippet SELECT a.*, b.*, a.Company + b.[Customer Suffix] AS AdditionalColumn FROM ( SELECT abCUSA AS Company, abCUSB AS Account, 0 AS DeliverTo, abcnam AS [Account Name], abcad1 AS [Line Address 1], abcad2 AS [Line Address 2], abcad3 AS [Line Address 3], abcad4 AS [Line Address 4] FROM LIVEAS400.S65C422B.WRFDTA.PARNADR ) a inner JOIN ( SELECT SLDATE as Date, SLCUSA as [Compny Number], SLCUSB as [Customer Suffix], SLDELN as [Deliber To Number], SLTREF as [Trans Reference], SLCNAM as [Customer Name], -- SLTRT2 as [Trans type], CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLGDSV * -1 ELSE SLGDSV END as [Goods Value], CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLVATV * -1 ELSE SLVATV END as [VAT], CASE WHEN SLTRT2 IN (92, 93, 94, 95, 98) THEN SLTOTV * -1 ELSE SLTOTV END as Total, SLDESC as [Trans Description] FROM LIVEAS400.S65C422B.WRFDTA.SQLSLDGR ) b ON a.Account = b.[Customer Suffix]
We have configured one-way merge replication in our topology. That is data flows from Subscriber to Publisher only. We have a publisher and a subscriber. There are 3 publications in this category and each publication has a subscription. We use SQL Server 2005 SP1 in both the servers. The retention period is the 14 days (default). After this period, I get the following error in the subscription in Replication Monitor. The Error message is
Error messages:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)
I read the post http://forums.microsoft.com/MSDN/ShowPost.aspxPostID=372790&SiteID=1
which said that this error might be solved in the SP2. We have not yet applied SP2, but even after applying SP2, will this error be solved for One Way Merge Replication since the data from publisher will not go to the subscriber always in this type of topology??.
Kindly get back to us regarding this as soon as possible. Thanks in advance.
We have configured the following in the Publisher server..
1) Merge Replication - Synchronisation to be running in the continuous mode.
2) Merge Replication - Synchronisation in Scheduled mode.
The issue that we are facing here is the transaction log file of the databases which are in replication are growing very largely. And we get this error message in the Subscriber :
Error messages:
The Merge Agent failed to retrieve article information for publication 'MCC_Pos_CashlessPub'. Increase the -QueryTimeOut parameter and restart the synchronization. When troubleshooting, use SQL Profiler or restart the agent with a higher value for -HistoryVerboseLevel and check the output log file for errors. Correct any database engine conditions that may be causing internal replication stored procedures to fail. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201017) Get help: http://help/MSSQL_REPL-2147201017
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (Source: MSSQLServer, Error number: 9002) Get help: http://help/9002
In fact for the past two days there are no data movement or any changes to the table, still we are able to see the growth in the transaction log file.
As mentioned in the error description when checked the log_reuse_wait_desc column of the sys.databases table, it showed the value "LOG_BACKUP". So took database backup 3 times. and took transaction log backup 2 times from the subscriber server in which the error was thrown. Still the issue persists. There is no change in the transaction log size.
What is the reason behind the growth of LDF?? Is it because of Merge Replication Configured in the server??
Kindly check and let as know regarding this issue. We are facing lot of problems because of this issue. Thanks in advance.
I have done a couple of little projects in VB express and was pleased with the results. Now I have something bigger in mind and need to know if VB express version of SQL will support merge replication. If not will the full version support it? Thanks Dave
We are using SQL Server 2005, on Windows server 2003 R2.
We Have Two Database Servers say DBServer1 and DBServer2, Now I wants to do Replication between these to servers, such that
1. The Changes at DBServer1 should be reflected at DBServer2 2. The Changes at DBServer2 should be reflected at DBServer1 3. Changes includes Data changes and Schema Changes 4. After every Synchronization Both Databases should be Identical
I tried doing so, what i did was I cofigured Distribution at DBServer1, also Publisher and Publication at DBServer1 and Made a Subscription at DBServer2.
What I successfully done is If Publisher means DBServer1 do some changes then it gets updated at DBServer2. But New Rows added at DBServer2 doesn't gets added at DBServer1
Thanks in Advance, Vishalgiri Goswami Kalptaru Infosoft Pvt. Ltd.
I am trying to use a merge transformation task and receiving an error that I don't know how to troubleshoot further. Could I please have some advice on what else to look at to try to resolve the problem.
The error message text is: Error at Data Flow Task [Merge [1245]]: The metadata for "input column "LOCATION" (5451)" does not match the metadata for the associated output column
I have looked at the metadata and cannot see any differences: the following is output from the data flow path. Name Data TypePrecisionScaleLengthCode PageSort Key PositionSource Component ACCOUNT DT_STR 0 0 6 1252 1 Sort - FinSysData PROGRAM DT_STR 0 0 6 1252 2 Sort - FinSysData LOCATION DT_STR 0 0 6 1252 3 Sort - FinSysData PROJECT DT_STR 0 0 6 1252 4 Sort - FinSysData SUBPROJECTDT_STR 0 0 2 1252 5 Sort - FinSysData ACTIVITY DT_STR 0 0 6 1252 6 Sort - FinSysData FUNDING DT_STR 0 0 3 1252 7 Sort - FinSysData CLIENT DT_STR 0 0 6 1252 8 Sort - FinSysData NTWAGE DT_STR 0 0 3 1252 9 Sort - FinSysData TYPE DT_STR 0 0 1 1252 10 Sort - FinSysData PERIOD DT_STR 0 0 6 1252 11 Sort - FinSysData CO DT_STR 0 0 2 1252 12 Sort - FinSysData FIN_YEAR DT_I4 0 0 0 0 13 Sort - FinSysData BALANCES DT_R8 0 0 0 0 14 Sort - FinSysData
SELECT * FROM A INNER JOIN B ON A.AId = (SELECT B.BId FROM B WHERE (B.AId = 4))
I have two tables Table A has several columns and B has just two columns B.AId and B.BId.
I will pass B.AId as a parameter and that will return more than one B.BId values. Then I need to create a join where A.AId matches B.BId.
I tried the above SQL but it says subquery can't return more than one value. In my case it must return more than on value. How can I construct the SQL query. Please Help. Thanks
Ok, getting a little better at joins, but now need to take it two more steps.Main table tblEquipmentsFields: ID, EquipmentType, Location, StatusSecond Table tblEquipments_GroupFields: equipmentID, categoryID, subCategoryID (all are ints)I joined these two together and got the info back I needed:SELECT a.ID, a.Description, a.Status, a.Location, from tblEquipments ainner join tblEquipments_Group U on u.categoryID = a.EquipmentTypeand u.subCategoryID = @type Now, the a.status returns a number. What the status actually is is in tblStatus.Fields: statusID (int) , Status (varchar).So, the statusID matches a.Status.I need to get the text from tblStatus in the field Status and replace the a.Status (which is a number still) with the text.The other one, location, is the same situation.I've been trying to do another join and am at an end on this.Suggestions?Thanks!!Zath
I’m attempting to use a Left Join to pull information from a table that contains my objectives and place in with information from a Summary table. I use one where clause per Left Join, that defines which rows from the objective table I want that left join to represent (for instance “(mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL )� ). I have 7 left joins and where statements.
The problem I’m having is that the where clause are removing all rows where the Left Join does not result in a match, despite the fact that I have the OR IS NULL in the where clause.
Can anyone help!
The full SQL is as follows:
SELECT DATE_DAY , SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , FORCE_STATUS , NUM_CREATED , NUM_OPEN , NUM_AWAITING_DISPATCH , NUM_OPEN_IN_DUE_DATE , NUM_DISPATCH_TO , DURATION_DISPATCH_TO , SLA_DURATION_DISPATCH_TO , SLA_DURATION_DISPATCH_TO_G_L , MET_SLA_DISPATCH_TO , mdt.SLA AS SLA_MET_DISPATCH_TO , mdt.SLA_G_L AS SLA_MET_DISPATCH_TO_G_L , C_NUM_DISPATCH_TO , C_DURATION_DISPATCH_TO , C_SLA_DURATION_DISPATCH_TO , C_SLA_DURATION_DISPATCH_TO_G_L , C_MET_SLA_DISPATCH_TO , mdt.SLA AS C_SLA_MET_DISPATCH_TO , mdt.SLA_G_L AS C_SLA_MET_DISPATCH_TO_G_L , NUM_DISPATCH_FROM , DURATION_CREATE_TO_DISPATCH_FROM , NUM_DISPATCH_TO_TO_FROM , DURATION_DISPATCH_FROM , SLA_DURATION_DISPATCH_FROM , SLA_DURATION_DISPATCH_FROM_G_L , MET_SLA_DISPATCH_FROM , mdf.SLA AS SLA_MET_DISPATCH_FROM , mdf.SLA_G_L AS SLA_MET_DISPATCH_FROM_G_L , C_NUM_DISPATCH_FROM , C_DURATION_CREATE_TO_DISPATCH_FROM , C_NUM_DISPATCH_TO_TO_FROM , C_DURATION_DISPATCH_FROM , C_SLA_DURATION_DISPATCH_FROM , C_SLA_DURATION_DISPATCH_FROM_G_L , C_MET_SLA_DISPATCH_FROM , mdf.SLA AS C_SLA_MET_DISPATCH_FROM , mdf.SLA_G_L AS C_SLA_MET_DISPATCH_FROM_G_L , NUM_CLOSED , DURATION_WIP_TO_RESOLVE , SLA_WIP_TO_RESOLVE , SLA_WIP_TO_RESOLVE_G_L , MET_SLA_WIP_TO_RESOLVE , wip.SLA AS SLA_MET_WIP_TO_RESOLVE , wip.SLA_G_L AS SLA_MET_WIP_TO_RESOLVE_G_L , DURATION_MTTR , SLA_MTTR , SLA_MTTR_G_L , MET_SLA_MTTR , mttr.SLA AS SLA_MET_MTTR , mttr.SLA_G_L AS SLA_MET_MTTR_G_L , NUM_RESOLVE_FD , rfd.SLA AS SLA_RESOLVE_FD , rfd.SLA_G_L AS SLA_RESOLVE_FD_G_L , NUM_DISPATCH_NTF , ntf.SLA AS SLA_DISPATCH_NTF , ntf.SLA_G_L AS SLA_DISPATCH_NTF , AR_NUM_MET_DUE_DATE , ar.SLA AS AR_SLA_MET_DUE_DATE , ar.SLA_G_L AS AR_SLA_MET_DUE_DATE FROM ( SELECT DATE_DAY , CASE WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN (CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END) WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN (CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END) ELSE SEVERITY END AS SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END AS FORCE_STATUS , sum( NUM_CREATED ) AS NUM_CREATED , sum( NUM_OPEN ) AS NUM_OPEN , sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH , sum( NUM_OPEN_IN_DUE_DATE ) AS NUM_OPEN_IN_DUE_DATE , sum( NUM_DISPATCH_TO ) AS NUM_DISPATCH_TO , sum( DURATION_DISPATCH_TO ) AS DURATION_DISPATCH_TO , max( SLA_DURATION_DISPATCH_TO ) AS SLA_DURATION_DISPATCH_TO , max( SLA_DURATION_DISPATCH_TO_G_L ) AS SLA_DURATION_DISPATCH_TO_G_L , sum( MET_SLA_DISPATCH_TO ) AS MET_SLA_DISPATCH_TO , sum( C_NUM_DISPATCH_TO ) AS C_NUM_DISPATCH_TO , sum( C_DURATION_DISPATCH_TO ) AS C_DURATION_DISPATCH_TO , max( C_SLA_DURATION_DISPATCH_TO ) AS C_SLA_DURATION_DISPATCH_TO , max( C_SLA_DURATION_DISPATCH_TO_G_L ) AS C_SLA_DURATION_DISPATCH_TO_G_L , sum( C_MET_SLA_DISPATCH_TO ) AS C_MET_SLA_DISPATCH_TO , sum( NUM_DISPATCH_FROM ) AS NUM_DISPATCH_FROM , sum( DURATION_CREATE_TO_DISPATCH_FROM ) AS DURATION_CREATE_TO_DISPATCH_FROM , sum( NUM_DISPATCH_TO_TO_FROM ) AS NUM_DISPATCH_TO_TO_FROM , sum( DURATION_DISPATCH_FROM ) AS DURATION_DISPATCH_FROM , max( SLA_DURATION_DISPATCH_FROM ) AS SLA_DURATION_DISPATCH_FROM , max( SLA_DURATION_DISPATCH_FROM_G_L ) AS SLA_DURATION_DISPATCH_FROM_G_L , sum( MET_SLA_DISPATCH_FROM ) AS MET_SLA_DISPATCH_FROM , sum( C_NUM_DISPATCH_FROM ) AS C_NUM_DISPATCH_FROM , sum( C_DURATION_CREATE_TO_DISPATCH_FROM ) AS C_DURATION_CREATE_TO_DISPATCH_FROM , sum( C_NUM_DISPATCH_TO_TO_FROM ) AS C_NUM_DISPATCH_TO_TO_FROM , sum( C_DURATION_DISPATCH_FROM ) AS C_DURATION_DISPATCH_FROM , max( C_SLA_DURATION_DISPATCH_FROM ) AS C_SLA_DURATION_DISPATCH_FROM , max( C_SLA_DURATION_DISPATCH_FROM_G_L ) AS C_SLA_DURATION_DISPATCH_FROM_G_L , sum( C_MET_SLA_DISPATCH_FROM ) AS C_MET_SLA_DISPATCH_FROM , sum( NUM_CLOSED ) AS NUM_CLOSED , sum( DURATION_WIP_TO_RESOLVE ) AS DURATION_WIP_TO_RESOLVE , max( SLA_WIP_TO_RESOLVE ) AS SLA_WIP_TO_RESOLVE , max( SLA_WIP_TO_RESOLVE_G_L ) AS SLA_WIP_TO_RESOLVE_G_L , sum( MET_SLA_WIP_TO_RESOLVE ) AS MET_SLA_WIP_TO_RESOLVE , sum( DURATION_MTTR ) AS DURATION_MTTR , max( SLA_MTTR ) AS SLA_MTTR , max( SLA_MTTR_G_L ) AS SLA_MTTR_G_L , sum( MET_SLA_MTTR ) AS MET_SLA_MTTR , sum( NUM_RESOLVE_FD ) AS NUM_RESOLVE_FD , sum( NUM_DISPATCH_NTF ) AS NUM_DISPATCH_NTF , sum( AR_NUM_MET_DUE_DATE ) AS AR_NUM_MET_DUE_DATE FROM CTS.dbo.NDC_Scorecard_Summary_Table t WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-30,1) as datetime) AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime) AND upper(EQ_REGION) IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' ) AND upper(EQ_MARKET_CLUSTER) IN ( 'NEW ENGLAND','GEORGIA' ) GROUP BY DATE_DAY , CASE WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN (CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END) WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN (CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END) ELSE SEVERITY END-- AS SEVERITY , ENTRY_TYPE , EQ_REGION , EQ_MARKET_CLUSTER , CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END-- AS FORCE_STATUS ) t LEFT JOIN CTS.dbo.NDC_SLAs mdt ON t.SEVERITY=mdt.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs mdf ON t.SEVERITY=mdf.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs wip ON t.SEVERITY=wip.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs mttr ON t.SEVERITY=mttr.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs rfd ON t.SEVERITY=rfd.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs ntf ON t.SEVERITY=ntf.SLA_SEVERITY LEFT JOIN CTS.dbo.NDC_SLAs ar ON t.SEVERITY=ar.SLA_SEVERITY WHERE (mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL ) AND (mdf.SLA_NAME = 'Duration NDC to Field % Met SLA' OR mdf.SLA_NAME IS NULL ) AND (wip.SLA_NAME = 'Duration Dispatch Out % Met SLA' OR wip.SLA_NAME IS NULL ) AND (mttr.SLA_NAME = 'Total MTTR % Met SLA' OR mttr.SLA_NAME IS NULL ) AND (rfd.SLA_NAME = '% Resolved 1st Dispatch' OR rfd.SLA_NAME IS NULL ) AND (ntf.SLA_NAME = '% Dispatched NTF' OR ntf.SLA_NAME IS NULL ) AND (ar.SLA_NAME = '% Met Due Date' OR ar.SLA_NAME IS NULL )
I have two tables populated during the use of an application to loguser events and application states. They are named "EventTable" and"StateTable" and the structures follow:EventTable:ID EventTimeStep EventID-- ------------- ---------1 5 E12 22 E23 56 E3StateTableID StateTimeStep StateID-- ------------- -------1 1 S12 39 S2I want to perform a query that reports the StateID of the applicationat the time that each event was logged to the EventTable. The desiredoutput is:ID TimeStep EventID StateID-- -------- ------- -------1 5 E1 S12 22 E2 S13 56 E3 S2I have tried to create a query with an INNER JOIN where the value forthe StateID output field comes from the last row in the StateTableWHERE StateTable.TimeStep <= EventTable.TimeStep and where I use aGROUP_BY EventTable.ID to merge the following rows from the join:3 56 E3 S13 56 E3 S2However, the closest I can get is a query that gives me the wrongstate when applying the GROUP BY clause3 56 E3 S1I also think that the queries I have written is slow and inefficient.Is there a better way to perform this query or is my database designfatally flawed?Thanks,Adam Nemitoff
THIS LOOKS LIKE A LOT BUT I JUST WANTED TO GIVE ALL INFORMATION NEEDED FOR YOU TO EASILY ASSIST ME. I am not good with SQL so please bare with me. I will give a dumbed down table for us to work with:timestamp - DATA1 - DATA2 - MAXVALUENEEDED - DATA32006/8/12 12:00 123 125 1289 121 2006/8/12 12:05 124 128 1291 1232006/8/15 12:00 130 128 1190 1242006/8/15 1:00 131 127 1196 123 The columns labeled DATA do not really matter. The only important ones are timestamp and MAXVALUENEEDED but I do need the query to return all columns. What the query has to do: The query needs to find the MAX of MAXVALUENEEDED for a given DAY. Example Output- 2006/8/12 124 128 1291 1232006/8/15 131 127 1196 123As you can see the MAX values were grabbed for each given day and the unique records were returned on a per day basis. My current SQL Query is: SELECT CONVERT(varchar, timestamp, 101) AS Time, CONVERT(int,LV50AEPM_AMPS_A_VAL0) AS AMPS_A, CONVERT(int,LV50AEPM_AMPS_B_VAL0) AS AMPS_B, CONVERT(int,LV50AEPM_AMPS_C_VAL0) AS AMPS_C, CONVERT(int,LV50AEPM_KVA_TOT_VAL0) AS KVA_TOTAL, CONVERT(int,LV50AEPM_KW_TOT_VAL0), CONVERT(int,LV50AEPM_VLL_CA_VAL0), CONVERT(int,LV50AEPM_VLL_BC_VAL0), CONVERT(int,LV50AEPM_VLL_AB_VAL0) FROM Table_1 T1 WHERE CONVERT(int,T1.LV50AEPM_KVA_TOT_VAL0) = (SELECT MAX(CONVERT(int,T2.LV50AEPM_KVA_TOT_VAL0)) FROM Table_1 T2 WHERE CONVERT(varchar,T1.timestamp,101) = T2.timestamp) ORDER BY Time ASC The problem is it only returns the max value for one day instead of all days. Can anyone help me correct this? Thanks in advance for your help.
I have got a query in which a merge join is 99% of the cost .... and I am confused ... is not merge join supposed to be the fastest ??? Anyone seen this before ???
Any ideas why this could be happening ... and sorry ... do not ask me to post the code coz I will not be able to ...
I need to use Merge Join transformation to join two sources. One is from a PIVOT transformation and one of the output columns is ISSORTED, the other is from an OLE DB Source using a query. The Merge Join transformation requires both input source have to be sorted. I cannot find the ISSORTED property on the OLE DB Source!! I tried to use Derived/ copy transformations but cannot find the property also. How can set the OLE query sorted in order to use the MergeJoin?
I have a package where I use merge join for two sorted inputs and the output is stored in a raw file.
In another package, the raw file from above package is again merge joined with another sorted input. Now my question is....do we need to sort again the raw file from first package? or is it OK to set the isSorted property to True and define the sort keys?
I am new to this SSIS. I have a simple join query like this select a.id from tbl_a a, tbl_b b where a.id = b.id and I want insert the result to my temp table. the query results is 1500 rows. but when I use merge join in SSIS, it only inserts to my temp table 4 rows. I use inner join and I already set the IsSorted to true and specify the sort position for the columns in both source tables In tbl_a, there are one million rows, in tbl_b, there are 2000 rows. I don't know why the merge join cannot work out my task.Is there other way that I can just run this simple join query in SSIS to copy the data? Please help, thanks in advance.
Hello, I have a Merge Join transformation and when i sort values in OLEDB source the merge join fails, but if i use a sort transformation it works! Why?? Best regards, Fred
Hi Group,My apologies for the lengthy post, but here goes...I have the following tables:TABLE Vehicles([ID] nvarchar(5),[Make] nvarchar(20),[Model] nvarchar(20),)TABLE [Vehicle Status]([ID] int, /* this is an auto-incrementing field*/[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */[Status] nvarchar(20),[Status Date] datetime)Here's my problem...I have the following data in my [Vehicles] and [Vehicle Status] tables:[ID] [Make] [Model]----------------------H80 Nissan SkylineH86 Toyota Aristo[ID] [Vehicle ID] [Status] [Status Date]----------------------------------------1 H80 OK 2006-10-012 H80 Damage 2006-10-053 H86 OK 2006-10-134 H86 Dent 2006-10-155 H86 Scratched 2006-10-16I need a query that will join the two tables so that the most recentstatus of each vehicle can be determined. I've gotten as far as:SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [VehicleStatus].[Vehicle ID]Of course this produces the following results:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline OK 2006-10-01H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo OK 2006-10-13H86 Toyota Aristo Dent 2006-10-15H86 Toyota Aristo Scratched 2006-10-16How do I filter these results so that I get only the MOST RECENT vehiclestatus?i.e:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo Scratched 2006-10-16Thanks in advance,Rommel the iCeMAn*** Sent via Developersdex http://www.developersdex.com ***
I have two employee tables called EmpA and EmpB.Each table has the same attributes of Employee ID and Email address.I do an inner join on email address like this:
select * from EmpA inner join EmpB on EmpA.email = EmpB.email where EmpB like '%@mydomain.com'
I now want to modify the above where I want to output rows such that
Hi guys! I'm trying to figure out how to join 3 tables, but I can't seem to find a solution. What I want to do is to put table 1, table 2 and table 3 into table_merged. table_merged = table 1 + table 2 + table 3 Is it possible to merge tables even if they have different fields?
i'm merge joining 2 data sources, one is oracle and the other is excel...the problem is in the oracle source, it's a sql statement like:
select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd from qctrl_div_ord_header hdr, (select max(sub.eff_dt_from) min_eff_dt_from, div_ord_no from qctrl_div_ord_header sub group by div_ord_no ) tmp where hdr.eff_dt_from = tmp.min_eff_dt_from and hdr.div_ord_no = tmp.div_ord_no
having that sql statement, merging will come out with 0 rows
however, having a simple query like:
select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd from qctrl_div_ord_header hdr
merging will come out with 2 rows
you may think that the data in the first sql statement is not there for the merge, which causing the 0 rows, however, the data is there, i'm only joining by one column and definitely the data is there, the merge result should be 2 rows for both query statements
i believe this is a problem with SSIS, anyway around this?
I am working on an ssis package and i find an problem while using the merge join for merging 2 OLEDB Data sources .
data source 1 is : - The table formed my an sal server comand , that out put is given to a multicast since i want to sare that output amoung 2o other tables.
So the the left input for the merge join is OLEDB source , which contains direct data from source table
I am usong Inner join on one column
The problem is i am not getting the expected rows as out put of merge .
I tried to join the two tables in sqlserver query window and i am getting expected result
What could be the problem
The first table is
Reservations.ReservationManual
second table is Out put of the following query
Select Distinct B.ReservationID as R
from Property.Main A ,Reservations.Reservations B ,Reservations.ReservationRooms C
Where
A.propertyID = B.PropertyId And
C.ReservationID = B.ReservationID And
getdate() >=C.Until +A.ReservationOffLineDays
i am not getting the expected result here in SSIS package merge join
But if i try to execute the following in query editer in management studio i am getting the expected result !!
declare @temp as table
(ResID Varchar(50)
)
Insert into @temp
(ResID)
Select Distinct B.ReservationID as R
from Property.Main A ,Reservations.Reservations B ,Reservations.ReservationRooms C
Where
A.propertyID = B.PropertyId And
C.ReservationID = B.ReservationID And
getdate() >=C.Until +A.ReservationOffLineDays
select * from Reservations.ReservationManual A , @temp b
I am trying to normalize data using the unpivot transform. I have to unpivot using more than one key so I have a multicast feeding into two unpivot transforms then into a sort transform. This is where my problem starts - I have tried using a Merge Join (inner Join) transform but dont get the expected result.
My original data looks like this:
Pk_ID
Choice1
Choice2
Feedback1
Feedback2
10
a
b
x
y
After the mulitcast - unpivot - Merge Join, the expected result is: (pk_newID is an identity)
Pk_newID
fk_ID
Choice
Feedback
563
10
a
x
564
10
b
y
However with a Merge-Join (inner join on pk_ID) I get
