I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass 80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen during very high traffic. I have isolated the query, included at the bottom of this post (cleaned up). There is very little info on this event class out on the web. Version is SQL 2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall direction without causing you much work/time.
Issues:
1. Even though only the value of product_id in the HAVING clause changes, I do not always get the MJP. I would expect that a query without a JP is a query without a JP and it would be all-or-none.
2. Although it happens maybe 20-30 % of the time in production, I can’t make it happen in testing.
Questions:
Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed light? Know of good links, etc?
Thanks!! bbRichbb
SELECT p.Product_Id, MIN(ae.Enum_Value) AS color, p.Product_Name, p.Status_Code, ps.Curr_Price, s.Section_Id, COUNT(ps.SWATCH_STATUS) AS total_available_colors FROM Attribute_Enum_Value ae INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id AND ae.Attribute_Type_Id = pae.Attribute_Type_Id INNER JOIN Product p INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr AND p.Product_Id = ps.PRODUCT_ID WHERE (pae.Attribute_Type_Id = 500001) AND (p.Product_Class_Id = 2) AND (p.Status_Code = 'ACTV') AND (ps.SWATCH_STATUS = 'ACTV') GROUP BY p.Sequence_Number, p.Product_Id, p.Product_Name, p.Status_Code, ps.Curr_Price, s.Section_Id HAVING (p.Product_Id = 1209645) ORDER BY p.Sequence_Number, p.Product_Id
I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass
80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen
during very high traffic. I have isolated the query, included at the bottom of this post
(cleaned up). There is very little info on this event class out on the web. Version is SQL
2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall
direction without causing you much work/time.
Issues:
1. Even though only the value of product_id in the HAVING clause changes, I do not always get
the MJP. I would expect that a query without a JP is a query without a JP and it would be
all-or-none.
2. Although it happens maybe 20-30 % of the time in production, I can€™t make it happen in
testing.
Questions:
Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed
light? Know of good links, etc?
Thanks!! bbRichbb
SELECT p.Product_Id, MIN(ae.Enum_Value) AS color, p.Product_Name, p.Status_Code, ps.Curr_Price, s.Section_Id, COUNT(ps.SWATCH_STATUS) AS total_available_colors FROM Attribute_Enum_Value ae INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id AND ae.Attribute_Type_Id = pae.Attribute_Type_Id INNER JOIN Product p INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr AND p.Product_Id = ps.PRODUCT_ID WHERE (pae.Attribute_Type_Id = 500001) AND (p.Product_Class_Id = 2) AND (p.Status_Code = 'ACTV') AND (ps.SWATCH_STATUS = 'ACTV') GROUP BY p.Sequence_Number, p.Product_Id, p.Product_Name, p.Status_Code, ps.Curr_Price, s.Section_Id HAVING (p.Product_Id = 1209645) ORDER BY p.Sequence_Number, p.Product_Id
I am trying to get a count by product, month, year even if there are is no record for that particular month.
Current outcome: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 XYZ April 2014 34 ...
Desired outcome: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 XYZ March 2014 0 XYZ April 2014 34 ...
The join statement is simple: Select Product, Month, Year, Count(*) As Count From dbo.Products Group By Product, Month, Year
I have also tried the following code and left joining it with my main query but the product is left out as is seen:
DECLARE @Start DATETIME, @End DATETIME; SELECT @StartDate = '20140101', @EndDate = '20141231'; WITH dt(dt) AS ( SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start), 0)) FROM ( SELECT TOP (DATEDIFF(MONTH, @Start, @End) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 FROM sys.all_objects ORDER BY [object_id] ) AS n )
2nd attempt: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 NULL March 2014 0 XYZ April 2014 34 ...
What I want is this (as is shown above). Is this possible?
Desired outcome: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 XYZ March 2014 0 XYZ April 2014 34 ...
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total 100, 1, 2 100, 2, 2 200, 1, 1 200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
USE [International_Forecast_New] GO /****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[Code] ....
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this Period Quarter Year Period Start Period End 2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
Hi, We would like to capture events in our system. There seem to be three obvious capture points for us - application, triggers, transaction log. The latter seems to be the most attractive, since we`re looking for a solution with minimal performance impact. In general, our problem is similar to populating data warehouses from on-line databases. Can anyone proffer some advice? In particular, being quite new to SQL Server, I am not sure how difficult/possible it is to read the transaction log in order to cull events. Some direction here would be greatly appreciated. Thanks, Karl
am encountering the NO JOIN PREDICATE warning in a query plan for a particualr SQL statement. In poking around, I've come across psotings that mention the CU 4 for SQL Server 2005. The update applies to a KB article that addresses complex queries with an outer join.
But in my case, it's not an outer join causeing the problem - they are all left joins. Here is the piece of SQL that is running extremely slowly:
Code Snippet UPDATE @TESTTABLE SET [@TESTTABLE].SignInCancelledNoEMR = (SELECT q.cnt FROM (SELECT COUNT(*) AS cnt,cl.Clinic_id AS ClinicIDq,cl.CLINIC_REG_ID AS MarketIDq FROM dbo.tblactivity AS t LEFT JOIN [ERIIProduction].dbo.computer c on t.machineid = c.comp_windowsname LEFT JOIN [ERIIProduction].dbo.clinic cl on cl.clinic_id = c.comp_clinic_id LEFT JOIN [ERIIProduction].dbo.Region r ON cl.CLINIC_REG_ID = r.REG_ID WHERE t.status = '1' AND cast(checkindate as datetime) >= @startDate AND cast(checkindate as datetime) < DATEADD(d, 1, @endDate) AND cl.clinic_reg_id IN (SELECT Item FROM dbo.Split(@regionIDList,',')) AND NOT EXISTS (SELECT 1 FROM [ERIIProduction].dbo.VisitPatient vp WHERE (t.activityid = vp.VSPAT_GalvanonActivityID)) --AND (t.memberid = vp.VSPAT_GalvanonMemberID)) GROUP BY cl.Clinic_id,cl.CLINIC_REG_ID) AS q WHERE q.ClinicIDq = ClinicID AND q.MarketIDq = MarketID)
Before I apply the CU and enable the trace flags, I'd like to know if there is something else I can do within the query itself to make it run faster. I've tried the OPTION(FORCE ORDER ) hint, but that doesn't have any effect on the queery run time. And as a whole, the query cost relative to the entire batch jumped from 75% to 96%.
Granted the execution time of the entire batch dropped from 9 minutes to 1 minute, this query should not even take that long.
I have two tables. Table A has 2162 rows and table B has 101 rows. There isn't any join keys that I can join on these two tables. But I need everything from table B and only one column (Col1) from table A. It will result in a cross join and retruns 218362 (2162 * 101) rows, which is correct. But this takes about 30 seconds to complete. Any workaround to accomplish this? Optimizer shows no predicate joins on the Nested Loop and to me it is correct.
Here is what I have so far.
SELECT B.*, A.col1 FROM TableB B LEFT JOIN TABLE A A ON 1 = 1
Hi, our application is failing sometimes, with some select queries. After making traces in the database, I found the following error: Missing join predicate. I googled that, and I only found this useless tip:
Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. The fix to this is to add a join predicate.
So, I dont know what a join predicate is... maybe I used it, but I don't know it by that name.
I have created a trigger on a table that get's too many updates so to analyze what records and what columns get updates I have coded a trigger that inserts into a table the record key and a flag on each column to let me know what is updates. Now that I am presenting the data to the developers I have been asked to provide the update / insert or delete statement
So on my trigger I have tried using dynamic management views but its returning the actual trigger code not the parent SQL Script that triggered it.
This is what I am using.
select@dDate as dDate, dest.[dbid], dest.[objectid], dest.[number], dest.[encrypted], case when sder.[statement_start_offset] > 0 --the start of the active command is not at the beginning of the full command text thencasesder.[statement_end_offset]
I have around 100 packages (all [packages run at same time) each package calls a stored Procedure once the Stored proc Execution is completed the package will write the log information into a log table, here how can i capture the CPU and Memory usage for execution of each stored proc.
I'm in the process of verifying that all functionality in a version 6.5 database is still there in SQL 7.0. After the upgrade, one issue seems different.
When I attach to a SQL 6.5 database, the full username used in the connection is displayed in the Windows NT Event Viewer. But when I attach to the same database in SQL 7, the username shows up in the Event Viewer as 'N/A'. I've verified that audit permissions are set the same on both boxes both under SQL and in User Manager. Any ideas??
Hi,One of our backup jobs failed this morning but nothing got written intowin nt application event log. This way our MOM 2005 couldn't generatean alert on this event since mom inspetcs application event log forthat purpose (event id 17055). I see backup errors about 10 days ago inthe appl log. What could have caused that? Any help will beappreciated.Stan
I have 2 tables, table one with 772 pieces of compliant data. Table 2 has 435 pieces of data that meet another criteria (all the columns are identical it was just passes through an additional filter). I need to capture the values that are excluded from table 2.
Example Table 1 ID some value 1 x 2 x 3 x 4 x 5 x
Table 2 ID some value 2 x 3 x 5 x
I need to capture the data from ID 1 and 4 and assign a new value to it, it is extra compliant data. Thanks!
I am trying to use xquery to get event data back from extended events. I am trying to use some sample data from Grant Fritchey but I am getting null records back. Below is the xml - I just want to retrieve a distinct list of the client_hostname and client_app_name.
WITH xEvents AS (SELECT object_name AS xEventName, CAST (event_data AS xml) AS xEventData FROM sys.fn_xe_file_target_read_file ('C:LoginTraceShared_0*.xel', NULL, NULL, NULL)) SELECT distinct top 1000 xEventName, xEventData.value('(/event/data[@action_name=''Client_APP_Name'']/value)[1]','varchar') Client_APP_Name, xEventData.value('(/event/data[@action_name=''Client_Host_Name'']/value)[1]','varchar') Client_Host_Name FROM xEvents
SELECT event_data.value('(event/data/value)[4]', 'bigint') AS cpu_time, --database name event_data.value('(event/data/value)[5]', 'bigint') AS duration, --estimated cost --estimated rows --nest level
[code]...
Basically, is a simple T-SQL query that reads the local file for my already setup extended event sessions. But I can't find the way to retrieve the following attributes as part as the T-SQL query:
--database name --estimated cost --estimated rows --nest level --object name
I am trying to find a BOL or some MS link with the full list of possible values for event_data.value but can't find one.
The following snippet of code returns something like that: string;string1;string2
Up to here fine. I woner how to export such value to ssis variable??? That variable will contain the value needed for the FILEATTACHMENTS property (Send Mail Task)
Thanks a lot,
declare @anex as varchar(500) declare @anex2 as varchar(700) set @anex2 = '' DECLARE anexos CURSOR FOR SELECT [Ruta] + [Fichero] as ANEXO FROM SVC_FICHEROS INNER JOIN SVC_ENVIOS ON SVC_FICHEROS.IDENVIO = SVC_ENVIOS.IDENVIO WHERE ENVIADO = 0 OPEN anexos; FETCH NEXT FROM anexos INTO @anex
WHILE @@FETCH_STATUS = 0 BEGIN IF @anex2 = '' begin set @anex2 = @anex end else begin set @anex2 = @anex2 + ';' + @anex end FETCH NEXT FROM anexos INTO @anex END CLOSE anexos DEALLOCATE anexos
Today I have a very similar situation, only today I am dealing with missing text data, not numeric data.
DECLARE @MissingTextData TABLE ( RowID int ,UserID int , EmailAddress varchar(20) ,StreetAddress varchar(20)
[code]...
I would like to fill in the NULL columns with data from the other row, and then select the one row that is filled with all data. I was able to use MAX() for a numeric value, but I am really stumped on the text data. Everything that I have tried is not working.
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Create Table Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) )
insert into Sample
select 1, 1000, 1, 'Mike' union select 2, 1000, 2, 'Mikey' union select 3, 1000, 3, 'Michel' union select 4, 1001, 1, 'Carmel' union
[code]....
select * from SampleI have here sample data given. What I want to do is, I want to check the RefID which is not having proper order of sequence number. If you see the RefID 1000, 1001 they are having properly sequence order in SeqNo field. But it is not in RefID 1002. RefID 1002 does not have proper order. It is because user has deleted a row which was having seqno 2. So i want to get what are all the RefID's are not having properly sequenced. So that I would be able to know these are all the RefID's are affected by delete statement that was done by user.
Write the query that produces the below results. I'm not ale to join the two sets in a way so that it displays NULLs if no purchase was made on a given day for a particular product. I need NULLs or s so that it shows up correctly on my SSRS report.
;with testdata as( SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL SELECT 1 ,'1/8/2014', 25 UNION ALL SELECT 1 ,'1/9/2014', 30 UNION ALL SELECT 1 ,'1/10/2014', 60 UNION ALL SELECT 1 ,'1/5/2015', 3800 UNION ALL SELECT 1 ,'1/6/2015', 7120 UNION ALL
In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
One of our Application creating run time SQL job to run batch process and application itself deleting this run time SQL job, but when doing deletion it’s not checking whether job is running or not, just doing direct delete. Our challenge is how to capture deleted job details, after incident happen we could see error details only in this log.
We can run profile to trace but we don’t know when it will trigger and we cannot possible keep active profiler as it will kill server.so we can't run the trace for log time as we don't know when the issue happens.
hi , what is the definition and difference between predicate and residual predicate. give me some examples..Basically the columns used in where clause are called as predicates. Am i right.
I have recently started using replication in SQL 2012 SP1. When a stored procedure is altered on the source, the changes are replicated to the subscribers; however, the comment headers are removed at the subscribers. Due to the vast number of stored procedures I have, I do not want to move the comments below the Create Procedure statement. Are there any other ways to have comment header move with the stored procedures?
Here is what I am experiencing
Source SP
ALTER PROCEDURE [dbo].[SPTest] AS BEGIN SELECT GETDATE() END
Destination SP
ALTER PROCEDURE [dbo].[SPTest] AS BEGIN SELECT GETDATE() END
missing witness server information and the fail-over is broken suddenly? 4:00am no maintenance job. I have one sql job on 10pm for backup on database transaction log only.
I can see the primary have problem then perform fail-over to mirror database, the auto fail-over was broken.
I re-build the sql mirror is OK , but i want to find the root cause.
Windows application event was full when there have many failed event, i have increase log size for application event. Â
