Search Results

Search found 17552 results on 703 pages for 'deferred query'.

Page 2/703 | < Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

• SQL server recursive query error.The maximum recursion 100 has been exhausted before statement completion

  - by ienax_ridens

  I have a recursive query that returns an error when I run it; in other databases (with more data) I have not the problem. In my case this query returns 2 colums (ID_PARENT and ID_CHILD) doing a recursion because my tree can have more than one level, bit I wanna have only "direct" parent. NOTE: I tried to put OPTION (MAXRECURSION 0) at the end of the query, but with no luck. The following query is only a part of the entire query, I tried to put OPTION only at the end of the "big query" having a continous running query, but no errors displayed. Error have in SQL Server: "The statement terminated.The maximum recursion 100 has been exhausted before statement completion" The query is the following: WITH q AS (SELECT ID_ITEM, ID_ITEM AS ID_ITEM_ANCESTOR FROM ITEMS_TABLE i JOIN ITEMS_TYPES_TABLE itt ON itt.ID_ITEM_TYPE = i.ID_ITEM_TYPE UNION ALL SELECT i.ID_ITEM, q.ID_ITEM_ANCESTOR FROM q JOIN ITEMS_TABLE i ON i.ID_ITEM_PADRE = q.ID_ITEM JOIN ITEMS_TYPES_TABLE itt ON itt.ID_ITEM_TYPE = i.ID_ITEM_TYPE) SELECT ID_ITEM AS ID_CHILD, ID_ITEM_ANCESTOR AS ID_PARENT FROM q I need a suggestion to re-write this query to avoid the error of recursion and see the data, that are few.

  Read the article

• SQL SERVER – How to Ignore Columnstore Index Usage in Query

  - by pinaldave

  Earlier I wrote about SQL SERVER – Fundamentals of Columnstore Index and very first question I received in email was as following. “We are using SQL Server 2012 CTP3 and so far so good. In our data warehouse solution we have created 1 non-clustered columnstore index on our large fact table. We have very unique situation but your article did not cover it. We are running few queries on our fact table which is working very efficiently but there is one query which earlier was running very fine but after creating this non-clustered columnstore index this query is running very slow. We dropped the columnstore index and suddenly this one query is running fast but other queries which were benefited by this columnstore index it is running slow. Any workaround in this situation?” In summary the question in simple words “How can we ignore using columnstore index in selective queries?” Very interesting question – you can use I can understand there may be the cases when columnstore index is not ideal and needs to be ignored the same. You can use the query hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX to ignore the columnstore index. SQL Server Engine will use any other index which is best after ignoring the columnstore index. Here is the quick script to prove the same. We will first create sample database and then create columnstore index on the same. Once columnstore index is created we will write simple query. This query will use columnstore index. We will then show the usage of the query hint. USE AdventureWorks GO -- Create New Table CREATE TABLE [dbo].[MySalesOrderDetail]( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL, [LineTotal] [numeric](38, 6) NOT NULL, [rowguid] [uniqueidentifier] NOT NULL, [ModifiedDate] [datetime] NOT NULL ) ON [PRIMARY] GO -- Create clustered index CREATE CLUSTERED INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail] ( [SalesOrderDetailID]) GO -- Create Sample Data Table -- WARNING: This Query may run upto 2-10 minutes based on your systems resources INSERT INTO [dbo].[MySalesOrderDetail] SELECT S1.* FROM Sales.SalesOrderDetail S1 GO 100 -- Create ColumnStore Index CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore] ON [MySalesOrderDetail] (UnitPrice, OrderQty, ProductID) GO Now we have created columnstore index so if we run following query it will use for sure the same index. -- Select Table with regular Index SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice, SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty FROM [dbo].[MySalesOrderDetail] GROUP BY ProductID ORDER BY ProductID GO We can specify Query Hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX as described in following query and it will not use columnstore index. -- Select Table with regular Index SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice, SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty FROM [dbo].[MySalesOrderDetail] GROUP BY ProductID ORDER BY ProductID OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX) GO Let us clean up the database. -- Cleanup DROP INDEX [IX_MySalesOrderDetail_ColumnStore] ON [dbo].[MySalesOrderDetail] GO TRUNCATE TABLE dbo.MySalesOrderDetail GO DROP TABLE dbo.MySalesOrderDetail GO Again, make sure that you use hint sparingly and understanding the proper implication of the same. Make sure that you test it with and without hint and select the best option after review of your administrator. Here is the question for you – have you started to use SQL Server 2012 for your validation and development (not on production)? It will be interesting to know the answer. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Index, SQL Optimization, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• How to use PHPunit's Database Test Case Methods of Query to compare with doctrine query

  - by Bhavin Rana

  How to use PHPunit's Database Test Case Methods of Query to compare with doctrine query i m using phpunit with doctrine framework .. i want to test my doctrine query with phpunit query with using phpunit framework methods.. any buddy with Doctrine Query and PhpUnit Query plz Contact... //Bhavin Rana.

  Read the article

• jQuery - deferred versus promise

  - by fletchsod

  What is the difference between Deferred and Promise other than the jQuery versions? What should I use for my need? I only want to call the fooExecute(). I only need the fooStart() and fooEnd() to toggle the html div status for example. //I'm using jQuery v2.0.0 function fooStart() { /* Start Notification */ } function fooEnd() { /* End Notification */ } function fooExecute() { /* Execute the scripts */ } $('#button1').on('click', function() { var deferred1 = $.Deferred(); var promise1 = $.Promise(); deferred1.??? promise1.??? });

  Read the article

• SQL – Quick Start with Explorer Sections of NuoDB – Query NuoDB Database

  - by Pinal Dave

  This is the third post in the series of the blog posts I am writing about NuoDB. NuoDB is very innovative and easy-to-use product. I can clearly see how one can scale-out NuoDB with so much ease and confidence. In my very first blog post we discussed how we can install NuoDB (link), and in my second post I discussed how we can manage the NuoDB database transaction engines and storage managers with a few clicks (link). Note: You can Download NuoDB from here. In this post, we will learn how we can use the Explorer feature of NuoDB to do various SQL operations. NuoDB has a browser-based Explorer, which is very powerful and has many of the features any IDE would normally have. Let us see how it works in the following step-by-step tutorial. Let us go to the NuoDBNuoDB Console by typing the following URL in your browser: http://localhost:8080/ It will bring you to the QuickStart screen. Make sure that you have created the sample database. If you have not created sample database, click on Create Database and create it successfully. Now go to the NuoDB Explorer by clicking on the main tab, and it will ask you for your domain username and password. Enter the username as a domain and password as a bird. Alternatively you can also enter username as a quickstart and password as a quickstart. Once you enter the password you will be able to see the databases. In our example we have installed the Sample Database hence you will see the Test database in our Database Hierarchy screen. When you click on database it will ask for the database login. Note that Database Login is different from Domain login and you will have to enter your database login over here. In our case the database username is dba and password is goalie. Once you enter a valid username and password it will display your database. Further expand your database and you will notice various objects in your database. Once you explore various objects, select any database and click on Open. When you click on execute, it will display the SQL script to select the data from the table. The autogenerated script displays entire result set from the database. The NuoDB Explorer is very powerful and makes the life of developers very easy. If you click on List SQL Statements it will list all the available SQL statements right away in Query Editor. You can see the popup window in following image. Here is the cool thing for geeks. You can even click on Query Plan and it will display the text based query plan as well. In case of a SELECT, the query plan will be much simpler, however, when we write complex queries it will be very interesting. We can use the query plan tab for performance tuning of the database. Here is another feature, when we click on List Tables in NuoDB Explorer.  It lists all the available tables in the query editor. This is very helpful when we are writing a long complex query. Here is a relatively complex example I have built using Inner Join syntax. Right below I have displayed the Query Plan. The query plan displays all the little details related to the query. Well, we just wrote multi-table query and executed it against the NuoDB database. You can use the NuoDB Admin section and do various analyses of the query and its performance. NuoDB is a distributed database built on a patented emergent architecture with full support for SQL and ACID guarantees.  It allows you to add Transaction Engine processes to a running system to improve the performance of your system.  You can also add a second Storage Engine to your running system for redundancy purposes.  Conversely, you can shut down processes when you don’t need the extra database resources. NuoDB also provides developers and administrators with a single intuitive interface for centrally monitoring deployments. If you have read my blog posts and have not tried out NuoDB, I strongly suggest that you download it today and catch up with the learnings with me. Trust me though the product is very powerful, it is extremely easy to learn and use. Reference: Pinal Dave (http://blog.sqlauthority.com)   Filed under: Big Data, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: NuoDB

  Read the article

• Mysql 100% CPU + Slow query

  - by felipeclopes

  I'm using the RDS database from amazon with a some very big tables, and yesterday I started to face 100% CPU utilisation on the server and a bunch of slow query logs that were not happening before. I tried to check the queries that were running and faced this result from the explain command +----+-------------+-------------------------------+--------+----------------------------------------------------------------------------------------------+---------------------------------------+---------+-----------------------------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------------------+--------+----------------------------------------------------------------------------------------------+---------------------------------------+---------+-----------------------------------------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | businesses | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | activities_businesses | ref | PRIMARY,index_activities_users_on_business_id,index_tweets_users_on_tweet_id_and_business_id | index_activities_users_on_business_id | 9 | const | 2252 | Using index condition; Using where | | 1 | SIMPLE | activities_b_taggings_975e9c4 | ref | taggings_idx | taggings_idx | 782 | const,myapp_production.activities_businesses.id,const | 1 | Using index condition; Using where | | 1 | SIMPLE | activities | eq_ref | PRIMARY,index_activities_on_created_at | PRIMARY | 8 | myapp_production.activities_businesses.activity_id | 1 | Using where | +----+-------------+-------------------------------+--------+----------------------------------------------------------------------------------------------+---------------------------------------+---------+-----------------------------------------------------------------+------+----------------------------------------------+ Also checkin in the process list, I got something like this: +----+-----------------+-------------------------------------+----------------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-------------------------------------+----------------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | 1 | my_app | my_ip:57152 | my_app_production | Sleep | 0 | | NULL | | 2 | my_app | my_ip:57153 | my_app_production | Sleep | 2 | | NULL | | 3 | rdsadmin | localhost:49441 | NULL | Sleep | 9 | | NULL | | 6 | my_app | my_other_ip:47802 | my_app_production | Sleep | 242 | | NULL | | 7 | my_app | my_other_ip:47807 | my_app_production | Query | 231 | Sending data | SELECT my_fields... | | 8 | my_app | my_other_ip:47809 | my_app_production | Query | 231 | Sending data | SELECT my_fields... | | 9 | my_app | my_other_ip:47810 | my_app_production | Query | 231 | Sending data | SELECT my_fields... | | 10 | my_app | my_other_ip:47811 | my_app_production | Query | 231 | Sending data | SELECT my_fields... | | 11 | my_app | my_other_ip:47813 | my_app_production | Query | 231 | Sending data | SELECT my_fields... | ... So based on the numbers, it looks like there is no reason to have a slow query, since the worst execution plan is the one that goes through 2k rows which is not much. Edit 1 Another information that might be useful is the slow query_log SET timestamp=1401457485; SELECT my_query... # User@Host: myapp[myapp] @ ip-10-195-55-233.ec2.internal [IP] Id: 435 # Query_time: 95.830497 Lock_time: 0.000178 Rows_sent: 0 Rows_examined: 1129387 Edit 2 After profiling, I got this result. The result have approximately 250 rows with two columns each. +----------------------+----------+ | state | duration | +----------------------+----------+ | Sending data | 272 | | removing tmp table | 0 | | optimizing | 0 | | Creating sort index | 0 | | init | 0 | | cleaning up | 0 | | executing | 0 | | checking permissions | 0 | | freeing items | 0 | | Creating tmp table | 0 | | query end | 0 | | statistics | 0 | | end | 0 | | System lock | 0 | | Opening tables | 0 | | logging slow query | 0 | | Sorting result | 0 | | starting | 0 | | closing tables | 0 | | preparing | 0 | +----------------------+----------+ Edit 3 Adding query as requested SELECT activities.share_count, activities.created_at FROM `activities_businesses` INNER JOIN `businesses` ON `businesses`.`id` = `activities_businesses`.`business_id` INNER JOIN `activities` ON `activities`.`id` = `activities_businesses`.`activity_id` JOIN taggings activities_b_taggings_975e9c4 ON activities_b_taggings_975e9c4.taggable_id = activities_businesses.id AND activities_b_taggings_975e9c4.taggable_type = 'ActivitiesBusiness' AND activities_b_taggings_975e9c4.tag_id = 104 AND activities_b_taggings_975e9c4.created_at >= '2014-04-30 13:36:44' WHERE ( businesses.id = 1 ) AND ( activities.created_at > '2014-04-30 13:36:44' ) AND ( activities.created_at < '2014-05-30 12:27:03' ) ORDER BY activities.created_at; Edit 4 There may be a chance that the indexes are not being applied due to difference in column type between the taggings and the activities_businesses, on the taggable_id column. mysql> SHOW COLUMNS FROM activities_businesses; +-------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | activity_id | bigint(20) | YES | MUL | NULL | | | business_id | bigint(20) | YES | MUL | NULL | | +-------------+------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> SHOW COLUMNS FROM taggings; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | tag_id | int(11) | YES | MUL | NULL | | | taggable_id | bigint(20) | YES | | NULL | | | taggable_type | varchar(255) | YES | | NULL | | | tagger_id | int(11) | YES | | NULL | | | tagger_type | varchar(255) | YES | | NULL | | | context | varchar(128) | YES | | NULL | | | created_at | datetime | YES | | NULL | | +---------------+--------------+------+-----+---------+----------------+ So it is examining way more rows than it shows in the explain query, probably because some indexes are not being applied. Do you guys can help m with that?

  Read the article

• SQLAuthority News – Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 – Microsoft Whitepaper

  - by pinaldave

  I recently presented session on Statistics and Best Practices in Virtual Tech Days on Nov 22, 2010. The sessions was very popular and I got many questions right after the sessions. The number question I had received was where everybody can get the further information. I am very much happy that my sessions created some curiosity for one of the most important feature of the SQL Server. Statistics are the heart of the SQL Server. Microsoft has published a white paper on the subject how statistics are useful to Query Optimizer. Here is the abstract of the same white paper from Microsoft. Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 Writer: Eric N. Hanson and Yavor Angelov Microsoft SQL Server 2008 collects statistical information about indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data. This paper describes what data is collected, where it is stored, and which commands create, update, and delete statistics. By default, SQL Server 2008 also creates and updates statistics automatically, when such an operation is considered to be useful. This paper also outlines how these defaults can be changed on different levels (column, table, and database). In addition, it presents how certain query language features, such as Transact-SQL variables, interact with use of statistics by the optimizer, and it provides guidance for using these features when writing queries so you can obtain good query performance. Link to white paper Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 ?Reference: Pinal Dave (http://blog.SQLAuthority.com)   Filed under: Pinal Dave, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQL White Papers, SQLAuthority News, T SQL, Technology

  Read the article

• Do a query only if there are no results on previous query

  - by yes123

  Hi guys: I do this query(1): (1)SELECT * FROM t1 WHERE title LIKE 'key%' LIMIT 1 I need to do a second(2) query only if this previous query has no results (2)SELECT * FROM t1 WHERE title LIKE '%key%' LIMIT 1 basically i need only 1 row who got the most close title to my key. Atm i am using an UNION query with a custom field to order it and a LIMIT 1. Problem is I don't want to do the others query if already the first made the result. Thanks

  Read the article

• Lazy/deferred loading of a CollectionViewSource?

  - by Shimmy

  When you create a CollectionViewSource in the Resources section, is the set Source loaded when the resources are initalized (i.e. when the Resources holder is inited) or when data is bound? Is there a xamly way to make a CollectionViewSource lazy-load? deferred-load? explicit-load?

  Read the article

• SQL SERVER – Monday Morning Puzzle – Query Returns Results Sometimes but Not Always

  - by pinaldave

  The amount of email I receive sometime it is impossible for me to answer every email. Nonetheless I try to answer pretty much every email I receive. However, quite often I receive such questions in email that I have no answer to them because either emails are not complete or they are out of my domain expertise. In recent times I received one email which had only one or two lines but indeed attracted my attention to it. The question was bit vague but it indeed made me think. The answer was not straightforward so I had to keep on writing the answer as I remember it. However, after writing the answer I do not feel satisfied. Let me put this question in front of you and see if we all can come up with a comprehensive answer. Question: I am beginner with SQL Server. I have one query, it sometime returns a result and sometime it does not return me the result. Where should I start looking for a solution and what kind of information I should send to you so you can help me with solving. I have no clue, please guide me. Well, if you read the question, it is indeed incomplete and it does not contain much of the information at all. I decided to help him and here is the answer, which I started to compose. Answer: As there are not much information in the original question, I am not confident what will solve your problem. However, here are the few things which you can try to look at and see if that solves your problem. Check parameter which is passed to the query. Is the parameter changing at various executions? Check connection string – is there some kind of logic around it? Do you have a non-deterministic component in your query logic? (In other words – does your result is based on current date time or any other time based function?) Are you facing time out while running your query? Is there any error in error log? What is the business logic in your query? Do you have all the valid permissions to all the objects used in the query? Are permissions changing or query accessing a different object in various executions? (Add your suggestions here) Meanwhile, have you ever faced this situation? If yes, do share your experience in the comment area. I will send a copy of my book SQL Server Interview Questions and Answers to one of the most interesting comment. The winner will be announced by next Monday.  Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Interview Questions and Answers, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Postfix - suspend domain from which deferred status was received?

  - by Al Bundy

  Is there a possibility to make Postfix stop trying (for a period of time) to send emails to a domain from which it received a deferred response? Currently my Postfix goes through each address in the queue. Please see the below example. At 09:48:32 the status=deferred appears. After this Postfix should stop trying to send stuff to the yahoo.com domain. Jun 6 09:48:20 mailer postfix/smtp[8644]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[98.138.112.35]:25, delay=37163, delays=36519/638/1.2/4.9, dsn=2.0.0, status=sent (250 ok dirdel 5/0) Jun 6 09:48:20 mailer postfix/smtp[8644]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[98.138.112.35]:25, delay=37163, delays=36519/638/1.2/4.9, dsn=2.0.0, status=sent (250 ok dirdel 5/0) Jun 6 09:48:20 mailer postfix/smtp[8644]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[98.138.112.35]:25, delay=37163, delays=36519/638/1.2/4.9, dsn=2.0.0, status=sent (250 ok dirdel 5/0) Jun 6 09:48:30 mailer postfix/smtp[8643]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[63.250.192.46]:25, delay=37173, delays=36519/645/1.4/7.4, dsn=2.0.0, status=sent (250 ok dirdel 5/0) Jun 6 09:48:30 mailer postfix/smtp[8643]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[63.250.192.46]:25, delay=37173, delays=36519/645/1.4/7.4, dsn=2.0.0, status=sent (250 ok dirdel 5/0) Jun 6 09:48:30 mailer postfix/smtp[8643]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[63.250.192.46]:25, delay=37173, delays=36519/645/1.4/7.4, dsn=2.0.0, status=sent (250 ok dirdel 5/0) Jun 6 09:48:30 mailer postfix/smtp[8643]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[63.250.192.46]:25, delay=37173, delays=36519/645/1.4/7.4, dsn=2.0.0, status=sent (250 ok dirdel 5/0) Jun 6 09:48:30 mailer postfix/smtp[8643]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[63.250.192.46]:25, delay=37173, delays=36519/645/1.4/7.4, dsn=2.0.0, status=sent (250 ok dirdel 5/0) Jun 6 09:48:32 mailer postfix/smtp[8644]: C779A233C0: host mta6.am0.yahoodns.net[98.138.112.38] said: 421 4.7.0 [TS01] Messages from x.x.x.250 temporarily deferred due to user complaints - 4.16.55.1; see http://postmaster.yahoo.com/421-ts01.html (in reply to MAIL FROM command) Jun 6 09:48:32 mailer postfix/smtp[8644]: C779A233C0: lost connection with mta6.am0.yahoodns.net[98.138.112.38] while sending RCPT TO Jun 6 09:48:33 mailer postfix/smtp[8644]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[98.138.112.35]:25, delay=37176, delays=36519/655/2.5/0.18, dsn=4.7.0, status=deferred (host mta7.am0.yahoodns.net[98.138.112.35] said: 421 4.7.0 [TS01] Messages from x.x.x.250 temporarily deferred due to user complaints - 4.16.55.1; see http://postmaster.yahoo.com/421-ts01.html (in reply to MAIL FROM command)) Jun 6 09:48:33 mailer postfix/smtp[8644]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[98.138.112.35]:25, delay=37176, delays=36519/655/2.5/0.18, dsn=4.7.0, status=deferred (host mta7.am0.yahoodns.net[98.138.112.35] said: 421 4.7.0 [TS01] Messages from x.x.x.250 temporarily deferred due to user complaints - 4.16.55.1; see http://postmaster.yahoo.com/421-ts01.html (in reply to MAIL FROM command)) Jun 6 09:48:34 mailer postfix/smtp[8644]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[98.138.112.35]:25, delay=37176, delays=36519/655/2.5/0.18, dsn=4.7.0, status=deferred (host mta7.am0.yahoodns.net[98.138.112.35] said: 421 4.7.0 [TS01] Messages from x.x.x.250 temporarily deferred due to user complaints - 4.16.55.1; see http://postmaster.yahoo.com/421-ts01.html (in reply to MAIL FROM command)) Jun 6 09:48:34 mailer postfix/smtp[8644]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[98.138.112.35]:25, delay=37176, delays=36519/655/2.5/0.18, dsn=4.7.0, status=deferred (host mta7.am0.yahoodns.net[98.138.112.35] said: 421 4.7.0 [TS01] Messages from x.x.x.250 temporarily deferred due to user complaints - 4.16.55.1; see http://postmaster.yahoo.com/421-ts01.html (in reply to MAIL FROM command)) Jun 6 09:48:34 mailer postfix/smtp[8644]: C779A233C0: to=<[email protected]>, relay=mta7.am0.yahoodns.net[98.138.112.35]:25, delay=37176, delays=36519/655/2.5/0.18, dsn=4.7.0, status=deferred (host mta7.am0.yahoodns.net[98.138.112.35] said: 421 4.7.0 [TS01] Messages from x.x.x.250 temporarily deferred due to user complaints - 4.16.55.1; see http://postmaster.yahoo.com/421-ts01.html (in reply to MAIL FROM command)) Jun 6 09:48:34 mailer postfix/error[8661]: C779A233C0: to=<[email protected]>, relay=none, delay=37177, delays=36519/658/0/0.07, dsn=4.4.2, status=deferred (delivery temporarily suspended: lost connection with mta7.am0.yahoodns.net[98.138.112.35] while sending RCPT TO) Jun 6 09:48:34 mailer postfix/error[8661]: C779A233C0: to=<[email protected]>, relay=none, delay=37177, delays=36519/658/0/0.18, dsn=4.4.2, status=deferred (delivery temporarily suspended: lost connection with mta7.am0.yahoodns.net[98.138.112.35] while sending RCPT TO) Jun 6 09:48:34 mailer postfix/error[8661]: C779A233C0: to=<[email protected]>, relay=none, delay=37177, delays=36519/658/0/0.35, dsn=4.4.2, status=deferred (delivery temporarily suspended: lost connection with mta7.am0.yahoodns.net[98.138.112.35] while sending RCPT TO) Jun 6 09:48:34 mailer postfix/error[8661]: C779A233C0: to=<[email protected]>, relay=none, delay=37177, delays=36519/658/0/0.4, dsn=4.4.2, status=deferred (delivery temporarily suspended: lost connection with mta7.am0.yahoodns.net[98.138.112.35] while sending RCPT TO) Jun 6 09:48:34 mailer postfix/error[8661]: C779A233C0: to=<[email protected]>, relay=none, delay=37177, delays=36519/658/0/0.46, dsn=4.4.2, status=deferred (delivery temporarily suspended: lost connection with mta7.am0.yahoodns.net[98.138.112.35] while sending RCPT TO) Jun 6 09:48:35 mailer postfix/error[8661]: C779A233C0: to=<[email protected]>, relay=none, delay=37179, delays=36519/660/0/0.16, dsn=4.4.2, status=deferred (delivery temporarily suspended: lost connection with mta7.am0.yahoodns.net[98.138.112.35] while sending RCPT TO) Jun 6 09:48:35 mailer postfix/error[8661]: C779A233C0: to=<[email protected]>, relay=none, delay=37179, delays=36519/660/0/0.22, dsn=4.4.2, status=deferred (delivery temporarily suspended: lost connection with mta7.am0.yahoodns.net[98.138.112.35] while sending RCPT TO) Jun 6 09:48:36 mailer postfix/error[8661]: C779A233C0: to=<[email protected]>, relay=none, delay=37179, delays=36519/660/0/0.31, dsn=4.4.2, status=deferred (delivery temporarily suspended: lost connection with mta7.am0.yahoodns.net[98.138.112.35] while sending RCPT TO) Jun 6 09:48:36 mailer postfix/error[8661]: C779A233C0: to=<[email protected]>, relay=none, delay=37179, delays=36519/660/0/0.36, dsn=4.4.2, status=deferred (delivery temporarily suspended: lost connection with mta7.am0.yahoodns.net[98.138.112.35] while sending RCPT TO)

  Read the article

• Enforcing a query in MySql to use a specific index

  - by Hossein

  Hi, I have large table. consisting of only 3 columns (id(INT),bookmarkID(INT),tagID(INT)).I have two BTREE indexes one for each bookmarkID and tagID columns.This table has about 21 Million records. I am trying to run this query: SELECT bookmarkID,COUNT(bookmarkID) AS count FROM bookmark_tag_map GROUP BY tagID,bookmarkID HAVING tagID IN (-----"tagIDList"-----) AND count >= N which takes ages to return the results.I read somewhere that if make an index in which it has tagID,bookmarkID together, i will get a much faster result. I created the index after some time. Tried the query again, but it seems that this query is not using the new index that I have made.I ran EXPLAIN and saw that it is actually true. My question now is that how I can enforce a query to use a specific index? also comments on other ways to make the query faster are welcome. Thanks

  Read the article

• Google Web Toolkit Deferred Binding Issue

  - by snctln

  I developed a web app using GWT about 2 years ago, since then the application has evolved. In its current state it relies on fetching a single XML file and parsing the information from it. Overall this works great. A requirement of this app is that it needs to be able to be ran from the filesystem (file:///..) as well as the traditional model of running from a webserver (http://...) Fetching this file from a webserver works exactly as expected using a RequestBuilder object. When running the app from the filesystem Firefox, Opera, Safari, and Chrome all behave as expected. When running the app from the filesystem using IE7 or IE8 the RequestBuilder.send() call fails, the information about the error suggests that there is a problem accessing the file due to violating the same origin policy. The app worked as expected in IE6 but not in IE7 or IE8. So I looked at the source code of RequestBuilder.java and saw that the actual request was being executed with an XMLHttpRequest GWT object. So I looked at the source code for XMLHttpRequest.java and found out some information. Here is the code (starts at line 83 in XMLHttpRequest.java) public static native XMLHttpRequest create() /*-{ if ($wnd.XMLHttpRequest) { return new XMLHttpRequest(); } else { try { return new ActiveXObject('MSXML2.XMLHTTP.3.0'); } catch (e) { return new ActiveXObject("Microsoft.XMLHTTP"); } } }-*/; So basically if an XMLHttpRequest cannot be created (like in IE6 because it is not available) an ActiveXObject is used instead. I read up a little bit more on the IE implementation of XMLHttpRequest, and it appears that it is only supported for interacting with files on a webserver. I found a setting in IE8 (Tools-Internet Options-Advanced-Security-Enable native XMLHTTP support), when I uncheck this box my app works. I assume this is because I am more of less telling IE to not use their implementation of XmlHttpRequest, so GWT just uses an ActiveXObject because it doesn't think the native XmlHttpRequest is available. This fixes the problem, but is hardly a long term solution. I can currently catch a failed send request and verify that it was trying to fetch the XML file from the filesystem using normal GWT. What I would like to do in this case is catch the IE7 and IE8 case and have them use a ActiveXObject instead of a native XmlHttpRequest object. There was a posting on the GWT google group that had a supposed solution for this problem (link). Looking at it I can tell that it was created for an older version of GWT. I am using the latest release and think that this is more or less what I would like to do (use GWT deferred binding to detect a specific browser type and run my own implementation of XMLHttpRequest.java in place of the built in GWT implementation). Here is the code that I am trying to use package com.mycompany.myapp.client; import com.google.gwt.xhr.client.XMLHttpRequest; public class XMLHttpRequestIE7or8 extends XMLHttpRequest { // commented out the "override" so that eclipse and the ant build script don't throw errors //@Override public static native XMLHttpRequest create() /*-{ try { return new ActiveXObject('MSXML2.XMLHTTP.3.0'); } catch (e) { return new ActiveXObject("Microsoft.XMLHTTP"); } }-*/; // have an empty protected constructor so the ant build script doesn't throw errors // the actual XMLHttpRequest constructor is empty as well so this shouldn't cause any problems protected XMLHttpRequestIE7or8() { } }; And here are the lines that I added to my module xml <replace-with class="com.mycompany.myapp.client.XMLHttpRequestIE7or8"> <when-type-is class="com.google.gwt.xhr.client.XMLHttpRequest"/> <any> <when-property-is name="user.agent" value="ie7" /> <when-property-is name="user.agent" value="ie8" /> </any> </replace-with> From what I can tell this should work, but my code never runs. Does anyone have any idea of what I am doing wrong? Should I not do this via deferred binding and just use native javascript when I catch the fail case instead? Is there a different way of approaching this problem that I have not mentioned? All replies are welcome.

  Read the article

• Use depth bias for shadows in deferred shading

  - by cubrman

  We are building a deferred shading engine and we have a problem with shadows. To add shadows we use two maps: the first one stores the depth of the scene captured by the player's camera and the second one stores the depth of the scene captured by the light's camera. We then ran a shader that analyzes the two maps and outputs the third one with the ready shadow areas for the current frame. The problem we face is a classic one: Self-Shadowing: A standard way to solve this is to use the slope-scale depth bias and depth offsets, however as we are doing things in a deferred way we cannot employ this algorithm. Any attempts to set depth bias when capturing light's view depth produced no or unsatisfying results. So here is my question: MSDN article has a convoluted explanation of the slope-scale: bias = (m × SlopeScaleDepthBias) + DepthBias Where m is the maximum depth slope of the triangle being rendered, defined as: m = max( abs(delta z / delta x), abs(delta z / delta y) ) Could you explain how I can implement this algorithm manually in a shader? Maybe there are better ways to fix this problem for deferred shadows?

  Read the article

• MERGE Bug with Filtered Indexes

  - by Paul White

  A MERGE statement can fail, and incorrectly report a unique key violation when: The target table uses a unique filtered index; and No key column of the filtered index is updated; and A column from the filtering condition is updated; and Transient key violations are possible Example Tables Say we have two tables, one that is the target of a MERGE statement, and another that contains updates to be applied to the target.  The target table contains three columns, an integer primary key, a single character alternate key, and a status code column.  A filtered unique index exists on the alternate key, but is only enforced where the status code is ‘a’: CREATE TABLE #Target ( pk integer NOT NULL, ak character(1) NOT NULL, status_code character(1) NOT NULL,   PRIMARY KEY (pk) );   CREATE UNIQUE INDEX uq1 ON #Target (ak) INCLUDE (status_code) WHERE status_code = 'a'; The changes table contains just an integer primary key (to identify the target row to change) and the new status code: CREATE TABLE #Changes ( pk integer NOT NULL, status_code character(1) NOT NULL,   PRIMARY KEY (pk) ); Sample Data The sample data for the example is: INSERT #Target (pk, ak, status_code) VALUES (1, 'A', 'a'), (2, 'B', 'a'), (3, 'C', 'a'), (4, 'A', 'd');   INSERT #Changes (pk, status_code) VALUES (1, 'd'), (4, 'a');          Target                     Changes +-----------------------+    +------------------+ ¦ pk ¦ ak ¦ status_code ¦    ¦ pk ¦ status_code ¦ ¦----+----+-------------¦    ¦----+-------------¦ ¦  1 ¦ A  ¦ a           ¦    ¦  1 ¦ d           ¦ ¦  2 ¦ B  ¦ a           ¦    ¦  4 ¦ a           ¦ ¦  3 ¦ C  ¦ a           ¦    +------------------+ ¦  4 ¦ A  ¦ d           ¦ +-----------------------+ The target table’s alternate key (ak) column is unique, for rows where status_code = ‘a’.  Applying the changes to the target will change row 1 from status ‘a’ to status ‘d’, and row 4 from status ‘d’ to status ‘a’.  The result of applying all the changes will still satisfy the filtered unique index, because the ‘A’ in row 1 will be deleted from the index and the ‘A’ in row 4 will be added. Merge Test One Let’s now execute a MERGE statement to apply the changes: MERGE #Target AS t USING #Changes AS c ON c.pk = t.pk WHEN MATCHED AND c.status_code <> t.status_code THEN UPDATE SET status_code = c.status_code; The MERGE changes the two target rows as expected.  The updated target table now contains: +-----------------------+ ¦ pk ¦ ak ¦ status_code ¦ ¦----+----+-------------¦ ¦  1 ¦ A  ¦ d           ¦ <—changed from ‘a’ ¦  2 ¦ B  ¦ a           ¦ ¦  3 ¦ C  ¦ a           ¦ ¦  4 ¦ A  ¦ a           ¦ <—changed from ‘d’ +-----------------------+ Merge Test Two Now let’s repopulate the changes table to reverse the updates we just performed: TRUNCATE TABLE #Changes;   INSERT #Changes (pk, status_code) VALUES (1, 'a'), (4, 'd'); This will change row 1 back to status ‘a’ and row 4 back to status ‘d’.  As a reminder, the current state of the tables is:          Target                        Changes +-----------------------+    +------------------+ ¦ pk ¦ ak ¦ status_code ¦    ¦ pk ¦ status_code ¦ ¦----+----+-------------¦    ¦----+-------------¦ ¦  1 ¦ A  ¦ d           ¦    ¦  1 ¦ a           ¦ ¦  2 ¦ B  ¦ a           ¦    ¦  4 ¦ d           ¦ ¦  3 ¦ C  ¦ a           ¦    +------------------+ ¦  4 ¦ A  ¦ a           ¦ +-----------------------+ We execute the same MERGE statement: MERGE #Target AS t USING #Changes AS c ON c.pk = t.pk WHEN MATCHED AND c.status_code <> t.status_code THEN UPDATE SET status_code = c.status_code; However this time we receive the following message: Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.#Target' with unique index 'uq1'. The duplicate key value is (A). The statement has been terminated. Applying the changes using UPDATE Let’s now rewrite the MERGE to use UPDATE instead: UPDATE t SET status_code = c.status_code FROM #Target AS t JOIN #Changes AS c ON t.pk = c.pk WHERE c.status_code <> t.status_code; This query succeeds where the MERGE failed.  The two rows are updated as expected: +-----------------------+ ¦ pk ¦ ak ¦ status_code ¦ ¦----+----+-------------¦ ¦  1 ¦ A  ¦ a           ¦ <—changed back to ‘a’ ¦  2 ¦ B  ¦ a           ¦ ¦  3 ¦ C  ¦ a           ¦ ¦  4 ¦ A  ¦ d           ¦ <—changed back to ‘d’ +-----------------------+ What went wrong with the MERGE? In this test, the MERGE query execution happens to apply the changes in the order of the ‘pk’ column. In test one, this was not a problem: row 1 is removed from the unique filtered index by changing status_code from ‘a’ to ‘d’ before row 4 is added.  At no point does the table contain two rows where ak = ‘A’ and status_code = ‘a’. In test two, however, the first change was to change row 1 from status ‘d’ to status ‘a’.  This change means there would be two rows in the filtered unique index where ak = ‘A’ (both row 1 and row 4 meet the index filtering criteria ‘status_code = a’). The storage engine does not allow the query processor to violate a unique key (unless IGNORE_DUP_KEY is ON, but that is a different story, and doesn’t apply to MERGE in any case).  This strict rule applies regardless of the fact that if all changes were applied, there would be no unique key violation (row 4 would eventually be changed from ‘a’ to ‘d’, removing it from the filtered unique index, and resolving the key violation). Why it went wrong The query optimizer usually detects when this sort of temporary uniqueness violation could occur, and builds a plan that avoids the issue.  I wrote about this a couple of years ago in my post Beware Sneaky Reads with Unique Indexes (you can read more about the details on pages 495-497 of Microsoft SQL Server 2008 Internals or in Craig Freedman’s blog post on maintaining unique indexes).  To summarize though, the optimizer introduces Split, Filter, Sort, and Collapse operators into the query plan to: Split each row update into delete followed by an inserts Filter out rows that would not change the index (due to the filter on the index, or a non-updating update) Sort the resulting stream by index key, with deletes before inserts Collapse delete/insert pairs on the same index key back into an update The effect of all this is that only net changes are applied to an index (as one or more insert, update, and/or delete operations).  In this case, the net effect is a single update of the filtered unique index: changing the row for ak = ‘A’ from pk = 4 to pk = 1.  In case that is less than 100% clear, let’s look at the operation in test two again:          Target                     Changes                   Result +-----------------------+    +------------------+    +-----------------------+ ¦ pk ¦ ak ¦ status_code ¦    ¦ pk ¦ status_code ¦    ¦ pk ¦ ak ¦ status_code ¦ ¦----+----+-------------¦    ¦----+-------------¦    ¦----+----+-------------¦ ¦  1 ¦ A  ¦ d           ¦    ¦  1 ¦ d           ¦    ¦  1 ¦ A  ¦ a           ¦ ¦  2 ¦ B  ¦ a           ¦    ¦  4 ¦ a           ¦    ¦  2 ¦ B  ¦ a           ¦ ¦  3 ¦ C  ¦ a           ¦    +------------------+    ¦  3 ¦ C  ¦ a           ¦ ¦  4 ¦ A  ¦ a           ¦                            ¦  4 ¦ A  ¦ d           ¦ +-----------------------+                            +-----------------------+ From the filtered index’s point of view (filtered for status_code = ‘a’ and shown in nonclustered index key order) the overall effect of the query is:   Before           After +---------+    +---------+ ¦ pk ¦ ak ¦    ¦ pk ¦ ak ¦ ¦----+----¦    ¦----+----¦ ¦  4 ¦ A  ¦    ¦  1 ¦ A  ¦ ¦  2 ¦ B  ¦    ¦  2 ¦ B  ¦ ¦  3 ¦ C  ¦    ¦  3 ¦ C  ¦ +---------+    +---------+ The single net change there is a change of pk from 4 to 1 for the nonclustered index entry ak = ‘A’.  This is the magic performed by the split, sort, and collapse.  Notice in particular how the original changes to the index key (on the ‘ak’ column) have been transformed into an update of a non-key column (pk is included in the nonclustered index).  By not updating any nonclustered index keys, we are guaranteed to avoid transient key violations. The Execution Plans The estimated MERGE execution plan that produces the incorrect key-violation error looks like this (click to enlarge in a new window): The successful UPDATE execution plan is (click to enlarge in a new window): The MERGE execution plan is a narrow (per-row) update.  The single Clustered Index Merge operator maintains both the clustered index and the filtered nonclustered index.  The UPDATE plan is a wide (per-index) update.  The clustered index is maintained first, then the Split, Filter, Sort, Collapse sequence is applied before the nonclustered index is separately maintained. There is always a wide update plan for any query that modifies the database. The narrow form is a performance optimization where the number of rows is expected to be relatively small, and is not available for all operations.  One of the operations that should disallow a narrow plan is maintaining a unique index where intermediate key violations could occur. Workarounds The MERGE can be made to work (producing a wide update plan with split, sort, and collapse) by: Adding all columns referenced in the filtered index’s WHERE clause to the index key (INCLUDE is not sufficient); or Executing the query with trace flag 8790 set e.g. OPTION (QUERYTRACEON 8790). Undocumented trace flag 8790 forces a wide update plan for any data-changing query (remember that a wide update plan is always possible).  Either change will produce a successfully-executing wide update plan for the MERGE that failed previously. Conclusion The optimizer fails to spot the possibility of transient unique key violations with MERGE under the conditions listed at the start of this post.  It incorrectly chooses a narrow plan for the MERGE, which cannot provide the protection of a split/sort/collapse sequence for the nonclustered index maintenance. The MERGE plan may fail at execution time depending on the order in which rows are processed, and the distribution of data in the database.  Worse, a previously solid MERGE query may suddenly start to fail unpredictably if a filtered unique index is added to the merge target table at any point. Connect bug filed here Tests performed on SQL Server 2012 SP1 CUI (build 11.0.3321) x64 Developer Edition © 2012 Paul White – All Rights Reserved Twitter: @SQL_Kiwi Email: [email protected]

  Read the article

• DNS Query.log - Multiple query’s for ripe.net

  - by Christopher Wilson

  Currently I run a DNS server (bind9) that handles queries from clients over the internet lately I have noticed hundreds of queries from all different address's that look like this (Server IP removed) client 216.59.33.210#53: query: ripe.net IN ANY +ED (0.0.0.0) client 216.59.33.204#53: query: ripe.net IN ANY +ED (0.0.0.0) client 208.64.127.5#53: query: ripe.net IN ANY +ED (0.0.0.0) client 184.107.255.202#53: query: ripe.net IN ANY +ED (0.0.0.0) client 208.64.127.5#53: query: ripe.net IN ANY +ED (0.0.0.0) client 208.64.127.5#53: query: ripe.net IN ANY +ED (0.0.0.0) client 205.204.65.83#53: query: ripe.net IN ANY +ED (0.0.0.0) client 69.162.110.106#53: query: ripe.net IN ANY +ED (0.0.0.0) client 216.59.33.210#53: query: ripe.net IN ANY +ED (0.0.0.0) client 69.162.110.106#53: query: ripe.net IN ANY +ED (0.0.0.0) client 216.59.33.204#53: query: ripe.net IN ANY +ED (0.0.0.0) client 208.64.127.5#53: query: ripe.net IN ANY +ED (0.0.0.0) Can someone please explain why there are so many clients querying for ripe.net ?

  Read the article

• SQLAuthority News – Download Whitepaper – Understanding and Controlling Parallel Query Processing in SQL Server

  - by pinaldave

  My recently article SQL SERVER – Reducing CXPACKET Wait Stats for High Transactional Database has received many good comments regarding MAXDOP 1 and MAXDOP 0. I really enjoyed reading the comments as the comments are received from industry leaders and gurus. I was further researching on the subject and I end up on following white paper written by Microsoft. Understanding and Controlling Parallel Query Processing in SQL Server Data warehousing and general reporting applications tend to be CPU intensive because they need to read and process a large number of rows. To facilitate quick data processing for queries that touch a large amount of data, Microsoft SQL Server exploits the power of multiple logical processors to provide parallel query processing operations such as parallel scans. Through extensive testing, we have learned that, for most large queries that are executed in a parallel fashion, SQL Server can deliver linear or nearly linear response time speedup as the number of logical processors increases. However, some queries in high parallelism scenarios perform suboptimally. There are also some parallelism issues that can occur in a multi-user parallel query workload. This white paper describes parallel performance problems you might encounter when you run such queries and workloads, and it explains why these issues occur. In addition, it presents how data warehouse developers can detect these issues, and how they can work around them or mitigate them. To review the document, please download the Understanding and Controlling Parallel Query Processing in SQL Server Word document. Note: Above abstract has been taken from here. The real question is what does the parallel queries has made life of DBA much simpler or is it looked at with potential issue related to degradation of the performance? Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL White Papers, SQLAuthority News, T SQL, Technology

  Read the article

• SQL SERVER – MAXDOP Settings to Limit Query to Run on Specific CPU

  - by pinaldave

  This is very simple and known tip. Query Hint MAXDOP – Maximum Degree Of Parallelism can be set to restrict query to run on a certain CPU. Please note that this query cannot restrict or dictate which CPU to be used, but for sure, it restricts the usage of number of CPUs in a single [...]

  Read the article

• SQL SERVER – Quiz and Video – Introduction to Hierarchical Query using a Recursive CTE

  - by pinaldave

  This blog post is inspired from SQL Queries Joes 2 Pros: SQL Query Techniques For Microsoft SQL Server 2008 – SQL Exam Prep Series 70-433 – Volume 2.[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza] This is follow up blog post of my earlier blog post on the same subject - SQL SERVER – Introduction to Hierarchical Query using a Recursive CTE – A Primer. In the article we discussed various basics terminology of the CTE. The article further covers following important concepts of common table expression. What is a Common Table Expression (CTE) Building a Recursive CTE Identify the Anchor and Recursive Query Add the Anchor and Recursive query to a CTE Add an expression to track hierarchical level Add a self-referencing INNER JOIN statement Above six are the most important concepts related to CTE and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right. Quiz 1) You have an employee table with the following data. EmpID FirstName LastName MgrID 1 David Kennson 11 2 Eric Bender 11 3 Lisa Kendall 4 4 David Lonning 11 5 John Marshbank 4 6 James Newton 3 7 Sally Smith NULL You need to write a recursive CTE that shows the EmpID, FirstName, LastName, MgrID, and employee level. The CEO should be listed at Level 1. All people who work for the CEO will be listed at Level 2. All of the people who work for those people will be listed at Level 3. Which CTE code will achieve this result? WITH EmpList AS (SELECT Boss.EmpID, Boss.FName, Boss.LName, Boss.MgrID, 1 AS Lvl FROM Employee AS Boss WHERE Boss.MgrID IS NULL UNION ALL SELECT E.EmpID, E.FirstName, E.LastName, E.MgrID, EmpList.Lvl + 1 FROM Employee AS E INNER JOIN EmpList ON E.MgrID = EmpList.EmpID) SELECT * FROM EmpList WITH EmpListAS (SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl FROM Employee WHERE MgrID IS NULL UNION ALL SELECT EmpID, FirstName, LastName, MgrID, 2 as Lvl ) SELECT * FROM BossList WITH EmpList AS (SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl FROM Employee WHERE MgrID is NOT NULL UNION SELECT EmpID, FirstName, LastName, MgrID, BossList.Lvl + 1 FROM Employee INNER JOIN EmpList BossList ON Employee.MgrID = BossList.EmpID) SELECT * FROM EmpList 2) You have a table named Employee. The EmployeeID of each employee’s manager is in the ManagerID column. You need to write a recursive query that produces a list of employees and their manager. The query must also include the employee’s level in the hierarchy. You write the following code segment: WITH EmployeeList (EmployeeID, FullName, ManagerName, Level) AS ( –PICK ANSWER CODE HERE ) SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level] FROM Employee WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FullName mgr.FullName, 1 + 1 AS [Level] FROM Employee emp JOIN Employee mgr ON emp.ManagerID = mgr.EmployeeId SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level] FROM Employee WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1 FROM EmployeeList mgr JOIN Employee emp ON emp.ManagerID = mgr.EmployeeId Now make sure that you write down all the answers on the piece of paper. Watch following video and read earlier article over here. If you want to change the answer you still have chance. Solution 1) 1 2) 2 Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct. Available at USA: Amazon India: Flipkart | IndiaPlaza Volume: 1, 2, 3, 4, 5 Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz? Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Relationship with Parallelism with Locks and Query Wait – Question for You

  - by Pinal Dave

  Today, I have one very simple question based on following image. A full disclaimer is that I have no idea why it is like that. I tried to reach out to few of my friends who know a lot about SQL Server but no one has any answer. Here is the question: If you go to server properties and click on Advanced you will see the following screen. Under the Parallelism section if you noticed there are four options: Cost Threshold for Parallelism Locks Max Degree of Parallelism Query Wait I can clearly understand why Cost Threshold for Parallelism and Max Degree of Parallelism belongs to Parallelism but I am not sure why we have two other options Locks and Query Wait belongs to Parallelism section. I can see that the options are ordered alphabetically but I do not understand the reason for locks and query wait to list under Parallelism. Here is the question for you – Why Locks and Query Wait options are listed under Parallelism section in SQL Server Advanced Properties? Please leave a comment with your explanation. I will publish valid answers on this blog with due credit. Reference: Pinal Dave (http://blog.sqlauthority.com)   Filed under: PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Undocumented Query Plans: Equality Comparisons

  - by Paul White

  The diagram below shows two data sets, with differences highlighted: To find changed rows using TSQL, we might write a query like this: The logic is clear: join rows from the two sets together on the primary key column, and return rows where a change has occurred in one or more data columns.  Unfortunately, this query only finds one of the expected four rows: The problem, of course, is that our query does not correctly handle NULLs.  The ‘not equal to’ operators <> and != do not evaluate...(read more)

  Read the article

• specify query timeout when using toplink essential query hint

  - by yhzs8

  Hi, For glassfish v2, I have searched through the web and I cannot find anyway to specify query timeout when using TopLink essential query hint. We have another option to migrate to EclipseLink but that is not feasible. have tried the solution in http://forums.oracle.com/forums/thread.jspa?threadID=974732&tstart=-1 but it seems the DatabaseQuery which one could set a timeout value is actually for Toplink, not TopLink essential. Do we have some other way to instruct the JDBC driver for this timeout value other than the query hint? I need to do it on query-basis and not system-basis (which is just to change the value of DISTRIBUTED_LOCK_TIMEOUT)

  Read the article

• mysql query query

  - by nightcoder1

  basically i need to write a query for mysql, but i have no experience in this and i cant find good tutorials on the old tinternet. i have a table called rels with columns "hosd_id" "linkedhost_id" "text link" and a table called hostlist with columns "id" "hostname" all i am trying to achieve is a query which outputs the "hostname" and "linked_id" when "host_id" is equal to "id" any help or pointers on syntax or code would be helpfull, or even a good mysql query guide

  Read the article

• mod_rewrite to redirect URL with query string

  - by meeble

  I've searched all over stackoverflow, but none of the answers seem to be working for this situation. I have a lot of working mod_rewrite rules already in my httpd.conf file. I just recently found that Google had indexed one of my non-rewritten URLs with a query string in it: http://domain.com/?state=arizona I would like to use mod_rewrite to do a 301 redirect to this URL: http://domain.com/arizona The issue is that later on in my rewrite rules, that 2nd URL is being rewritten to pass query variables on to WordPress. It ends up getting rewritten to: http://domain.com/index.php?state=arizona Which is the proper functionality. Everything I have tried so far has either not worked at all or put me in an endless rewrite loop. This is what I have right now, which is getting stuck in a loop: RewriteCond %{QUERY_STRING} state=arizona [NC] RewriteRule .* http://domain.com/arizona [R=301,L] #older rewrite rule that passes query string based on URL: RewriteRule ^([A-Za-z-]+)$ index.php?state=$1 [L] which gives me an endless rewrite loop and takes me to this URL: http://domain.com/arizona?state=arizona I then tried this: RewriteRule .* http://domain.com/arizona? [R=301,L] which got rid of the query string in the URL, but still creates a loop.

  Read the article

• Combining two-part SQL query into one query

  - by user332523

  Hello, I have a SQL query that I'm currently solving by doing two queries. I am wondering if there is a way to do it in a single query that makes it more efficient. Consider two tables: Transaction_Entries table and Transactions, each one defined below: Transactions - id - reference_number (varchar) Transaction_Entries - id - account_id - transaction_id (references Transactions table) Notes: There are multiple transaction entries per transaction. Some transactions are related, and will have the same reference_number string. To get all transaction entries for Account X, then I would do SELECT E.*, T.reference_number FROM Transaction_Entries E JOIN Transactions T ON (E.transaction_id=T.id) where E.account_id = X The next part is the hard part. I want to find all related transactions, regardless of the account id. First I make a list of all the unique reference numbers I found in the previous result set. Then for each one, I can query all the transactions that have that reference number. Assume that I hold all the rows from the previous query in PreviousResultSet UniqueReferenceNumbers = GetUniqueReferenceNumbers(PreviousResultSet) // in Java foreach R in UniqueReferenceNumbers // in Java SELECT * FROM Transaction_Entries where transaction_id IN (SELECT * FROM Transactions WHERE reference_number=R Any suggestions how I can put this into a single efficient query?

  Read the article

< Previous Page | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >