Search Results

Search found 21434 results on 858 pages for 'query master'.

Page 105/858 | < Previous Page | 101 102 103 104 105 106 107 108 109 110 111 112  | Next Page >

• Convert query with system objects from SQL 2000 to 2005/2008

  - by Dan

  I have some SQL I need to get working on SQL 2005/2008. The SQL is from SQL 2000 and uses some system objects to make it work. master.dbo.spt_provider_types master.dbo.syscharsets systypes syscolumns sysobjects I know SQL 2005 does no longer use system tables and I can get the same information from views, but I am looking for a solution that will work for both SQL 2000 and 2005/2008. Any ideas? select top 100 percent TABLE_CATALOG = db_name(), TABLE_SCHEMA = user_name(o.uid), TABLE_NAME = o.name, COLUMN_NAME = c.name, ORDINAL_POSITION = convert(int, ( select count(*) from syscolumns sc where sc.id = c.id AND sc.number = c.number AND sc.colid <= c.colid )), IS_COMPUTED = convert(bit, c.iscomputed) from syscolumns c left join syscomments m on c.cdefault = m.id and m.colid = 1, sysobjects o, master.dbo.spt_provider_types d, systypes t, master.dbo.syscharsets a_cha /* charset/1001, not sortorder.*/ where o.name = @table_name and permissions(o.id, c.name) <> 0 and (o.type in ('U','V','S') OR (o.type in ('TF', 'IF') and c.number = 0)) and o.id = c.id and t.xtype = d.ss_dtype and c.length = case when d.fixlen > 0 then d.fixlen else c.length end and c.xusertype = t.xusertype and a_cha.type = 1001 /* type is charset */ and a_cha.id = isnull(convert(tinyint, CollationPropertyFromID(c.collationid, 'sqlcharset')), convert(tinyint, ServerProperty('sqlcharset'))) -- make sure there's one and only one row selected for each column order by 2, 3, c.colorder ) tbl where IS_COMPUTED = 0

  Read the article

• Query specific logs from event log using nxlog

  - by user170899

  Below is my nxlog configuration define ROOT C:\Program Files (x86)\nxlog Moduledir %ROOT%\modules CacheDir %ROOT%\data Pidfile %ROOT%\data\nxlog.pid SpoolDir %ROOT%\data LogFile %ROOT%\data\nxlog.log <Extension json> Module xm_json </Extension> <Input internal> Module im_internal </Input> <Input eventlog> Module im_msvistalog Query <QueryList>\ <Query Id="0">\ <Select Path="Security">*</Select>\ </Query>\ </QueryList> </Input> <Output out> Module om_tcp Host localhost Port 3515 Exec $EventReceivedTime = integer($EventReceivedTime) / 1000000; \ to_json(); </Output> <Route 1> Path eventlog, internal => out </Route> <Select Path="Security">*</Select>\ - * gets everything from the Security log, but my requirement is to get specific logs starting with EventId - 4663. How do i do this? Please help. Thanks.

  Read the article

• Query Execution Failed in Reporting Services reports

  - by Chris Herring

  I have some reporting services reports that talk to Analysis Services and at times they fail with the following error: An error occurred during client rendering. An error has occurred during report processing. Query execution failed for dataset 'AccountManagerAccountManager'. The connection cannot be used while an XmlReader object is open. This occurs sometimes when I change selections in the filter. It also occurs when the machine has been under heavy load and then will consistently error until SSAS is restarted. The log file contains the following error: processing!ReportServer_0-18!738!04/06/2010-11:01:14:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'AccountManagerAccountManager'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'AccountManagerAccountManager'. ---> System.InvalidOperationException: The connection cannot be used while an XmlReader object is open. at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CheckConnection() at Microsoft.AnalysisServices.AdomdClient.XmlaClient.ExecuteStatement(String statement, IDictionary connectionProperties, IDictionary commandProperties, IDataParameterCollection parameters, Boolean isMdx) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(CommandBehavior behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters) at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.DataExtensions.AdoMdCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunDataSetQuery() Can anyone shed light on this issue?

  Read the article

• Query Execution Failed in Reporting Services reports

  - by Chris Herring

  I have some reporting services reports that talk to Analysis Services and at times they fail with the following error: An error occurred during client rendering. An error has occurred during report processing. Query execution failed for dataset 'AccountManagerAccountManager'. The connection cannot be used while an XmlReader object is open. This occurs sometimes when I change selections in the filter. It also occurs when the machine has been under heavy load and then will consistently error until SSAS is restarted. The log file contains the following error: processing!ReportServer_0-18!738!04/06/2010-11:01:14:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'AccountManagerAccountManager'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'AccountManagerAccountManager'. ---> System.InvalidOperationException: The connection cannot be used while an XmlReader object is open. at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CheckConnection() at Microsoft.AnalysisServices.AdomdClient.XmlaClient.ExecuteStatement(String statement, IDictionary connectionProperties, IDictionary commandProperties, IDataParameterCollection parameters, Boolean isMdx) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(CommandBehavior behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters) at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.DataExtensions.AdoMdCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunDataSetQuery() Can anyone shed light on this issue?

  Read the article

• How to avoid Master Browser errors, MRxSmb Event ID 8003?

  - by Sanoj

  I have a domain with Windows SBS 2003 as domain controller. It's very common to get Master Browser errors in the logs, MRxSmb Event ID 8003. How can I avoid that? What have I done wrong? I know how to solve this: Stop the Computer Browser service on clients, but I don't know how to avoid this since the problem comes back every time I add a new client and I forget to stop the Computer Browser service. Error message: The master browser has received a server announcement from the computer [computer] that believes that it is the master browser for the domain on transport NetBT_Tcpip_{#######-####-####-#. The master browser is stopping or an election is being forced. Is there an configuration of the server to avoid this problem?

  Read the article

• Migrating master-slave MySQL database servers to 2 new servers, any tips or suggestions?

  - by mmattax

  I'm setting up 2 new database servers that will be replacing a current master-slave setup. All boxes are running / will be running MySQL on RHEL. Our current naming conventions: db1 - master database db2 - slave (using MySQL replication) db01 - new master db02 - new slave We need to get db01 to be the new master with db02 as the new slave. What is the best way to migrate db1 and db2 to db01 and db02? db1 and db2 are running in a production setting and we need to minimize all downtime; db1 has roughly 30GB of data in the database. Any suggestions or tips on how to migrate to our new servers would be much appreciated.

  Read the article

• SSRS2008R2 report times out, but the underlying query executes in the Management Studio

  - by Matthew Belk

  A customer of mine recently moved servers and the new server has SQL2008R2. His old server was SQL2005. The new server has substantially better CPU, RAM, and disk performance than the old, but several reports time out while executing. When I run the underlying query in the SQL Management Studio, the query executes in sub-second time. The exact error message returned via the Report Manager UI is: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. It must be noted that this database is not just analytical; it's also fairly transactional, although the transaction volume is not exceptionally high. What can I do to improve the performance of the SSRS query engine? Are there settings in the data source I can adjust, or in the SSRS config files?

  Read the article

• How can I fix puppet refusing to start and asking for "master.pp"?

  - by cwd

  I'm using the very latest version of puppet and have been following the Apress "Pro Puppet" guide step by step. I have installed puppet sudo aptitude install ruby libshadow-ruby1.8 sudo aptitude install puppet puppetmaster facter I have edited /etc/puppet/puppet.conf to include certname [master] certname=puppet.mydomain.com I have edited /etc/hosts and added the following line 127.0.0.1 puppet.mydomain.com puppet I have set the hostname of the server echo "puppet.mydomain.com" > /etc/hostname hostname -F /etc/hostname And then I try and run puppet from the command line. puppet master --verbose --no-daemonize And puppet gives me this error: Could not parse for environment production: Could not find file /master.pp I'm running all commands with sudo and the last line of the error message always says that it can't find master.pp and the path before it is to my current working directory. What am I doing wrong? I should also mention that I don't have a DNS record set up for puppet.mydomain.com - I saw some online documentation mentioning this might be a problem - however I was fairly sure that the hosts file would let me get around that.

  Read the article

• UCARP: prevent the original master from taking over the VIP when it comes back after failure?

  - by quanta

  Keepalived can do this by combining the nopreempt option and the BACKUP state on the both nodes: Prevent VRRP Master from becoming Master once it has failed Prevent master to fall back to master after failure How about the UCARP? Name : ucarp Arch : x86_64 Version : 1.5.2 Release : 1.el5.rf Size : 81 k Repo : installed Summary : Common Address Redundancy Protocol (CARP) for Unix URL : http://www.ucarp.org/ License : BSD Description: UCARP allows a couple of hosts to share common virtual IP addresses in order : to provide automatic failover. It is a portable userland implementation of the : secure and patent-free Common Address Redundancy Protocol (CARP, OpenBSD's : alternative to the patents-bloated VRRP). : Strong points of the CARP protocol are: very low overhead, cryptographically : signed messages, interoperability between different operating systems and no : need for any dedicated extra network link between redundant hosts. If I don't use the --preempt option and set the --advskew to the same value, both nodes become master. /etc/sysconfig/carp/vip-010.conf # Virtual IP configuration file for UCARP # The number (from 001 to 255) in the name of the file is the identifier # $Id: vip-001.conf.example 1527 2004-07-09 15:23:54Z dude $ # Set the same password on all mamchines sharing the same virtual IP PASSWORD="pa$$w0rd" # You are required to have an IPADDR= line in the configuration file for # this interface (so no DHCP allowed) BIND_INTERFACE="eth0" # Do *NOT* use a main interface for the virtual IP, use an ethX:Y alias # with the corresponding /etc/sysconfig/network-scripts/ifcfg-ethX:Y file # already configured and ith ONBOOT=no VIP_INTERFACE="eth0:0" # If you have extra options to add, see "ucarp --help" output # (the lower the "-k <val>" the higher priority and "-P" to become master ASAP) OPTIONS="-z -k 255" /etc/sysconfig/network-scripts/ifcfg-eth0:0 DEVICE=eth0:0 ONBOOT=no BOOTPROTO= IPADDR=192.168.6.8 NETMASK=255.255.255.0 USERCTL=yes IPV6INIT=no node 1: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000 link/ether c6:9b:8e:af:a7:69 brd ff:ff:ff:ff:ff:ff inet 192.168.6.192/24 brd 192.168.6.255 scope global eth0 inet 192.168.6.8/24 brd 192.168.6.255 scope global secondary eth0:0 inet6 fe80::c49b:8eff:feaf:a769/64 scope link valid_lft forever preferred_lft forever node 2: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000 link/ether 00:30:48:f7:0f:81 brd ff:ff:ff:ff:ff:ff inet 192.168.6.38/24 brd 192.168.6.255 scope global eth1 inet 192.168.6.8/24 brd 192.168.6.255 scope global secondary eth1:0 inet6 fe80::230:48ff:fef7:f81/64 scope link valid_lft forever preferred_lft forever

  Read the article

• Mysql Query - That Is Returning Blatanty Incorrect Result

  - by user866190

  I am building a VPS node that is running Ubuntu 10.10LTS, Apache2, Mysql 5.1 and php5. I could not log in to my website admin through the browser, even though I am using the correct login details. So I logged in from the command line to check the results. When I run this query I get expected results: mysql> select * from users; +----+----------+-----------------------+----------+ | id | username | email | password | +----+----------+-----------------------+----------+ | 1 | myUserName | [email protected] | myPassword | +----+----------+-----------------------+----------+ And the same goes for this query: mysql> select * from users where id = 1; +----+----------+-----------------------+----------+ | id | username | email | password | +----+----------+-----------------------+----------+ | 1 | myUserName | [email protected] | myPassword | +----+----------+-----------------------+----------+ 1 row in set (0.00 sec) But when I run this query I get this 'unexpected response': mysql> select * from users where username = 'myUserName' and password = 'myPassword'; Empty set (0.00 sec) I am not sure why this is happening. Any help would be greatly appreciated. BTW.. I will be encrypting the user details but for now I just want to get it set up. Please help, Thanks

  Read the article

• Automating Access 2007 Queries (changing one criteria)

  - by Graphth

  So, I have 6 queries and I want to run them all once at the end of each month. (I know a bit about SQL but they're simply built using Access's design view). So, in the next few days, perhaps I'll run the 6 queries for May, as May just ended. I only want the data from the month that just ended, so the query has Criteria set as the name of the month (e.g., May). Now, it's not hugely time consuming to change all of these each month, but is there some way to automate this? Currently, they're all set to April and I want to change them all to May when I run them in a few days. And each month, I'd like to type the month (perhaps in a textbox in a form or somewhere else if you know a better way) just once and have it change all 6 queries, without having to manually open all 6, scroll over to the right field and change the Criteria. Note (about VBA): I have used Excel VBA so I know the basics of VBA but I don't really know anything specific to Access (other than seeing code a few times). And, others will use this who do not know anything about Access VBA. So, I think I have found a similar question/answer that could do this in VBA, but I'd rather do it some other way. If the query needs to be slightly redesigned later, probably by someone who doesn't know Access VBA at all, it'd be nice to have a solution not involving VBA if that is even possible.

  Read the article

• [SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'master..xp_jdbc_open2'.

  - by Vijaya Moderator -Oracle

  When connecting to MS SQL Server Database via Weblogic Datasource and using XA jdbc driver, the following error is thrown. <Jun 3, 2014 5:16:49 AM PDT> <Error> <Console> <BEA-240003> <Console encountered the following error java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'master..xp_jdbc_open2'. at weblogic.jdbc.sqlserverbase.ddb_.b(Unknown Source)at weblogic.jdbc.sqlserverbase.ddb_.a(Unknown Source)at weblogic.jdbc.sqlserverbase.ddb9.b(Unknown Source)at weblogic.jdbc.sqlserverbase.ddb9.a(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddr.v(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddq.a(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)at weblogic.jdbc.sqlserver.ddj.m(Unknown Source)at weblogic.jdbc.sqlserverbase.ddel.e(Unknown Source)at weblogic.jdbc.sqlserverbase.ddel.a(Unknown Source)  The cause behind the issue is that  the MS SQL Server was not installed with the Stored procedures to enable JTA/XA Solution To connect to SQL Server via XA Driver from WLS Datasource you need to install Stored Procedures for JTATo use JDBC distributed transactions through JTA, your system administrator should use the following procedure to install Microsoft SQL Server JDBC XA procedures. This procedure must be repeated for each MS SQL Server installation that will be involved in a distributed transaction.To install stored procedures for JTA:1. Copy the appropriate sqljdbc.dll and instjdbc.sql files from the WL_HOME\server\lib directory to the SQL_Server_Root/bin directory of the MS SQL Server database server, where WL_HOME is the directory in which WebLogic server is installed, typically c:\Oracle\Middleware\wlserver_10.x.  Note:  If you are installing stored procedures on a database server with multiple Microsoft SQL Server instances, each running SQL Server instance must be able to locate the sqljdbc.dll file.Therefore the sqljdbc.dll file needs to be anywhere on the global PATH or on the application-specific path. For the application-specific path, place the sqljdbc.dll file into the :\Program Files\Microsoft SQL Server\MSSQL$\Binn directory for each instance. 2. From the database server, use the ISQL utility to run the instjdbc.sql script. As a precaution, have your system administrator back up the master database before running instjdbc.sql. At a command prompt, use the following syntax to run instjdbc.sql:  ISQL -Usa -Psa_password -Sserver_name -ilocation\instjdbc.sql  where:  sa_password is the password of the system administrator.  server_name is the name of the server on which SQL Server resides.  location is the full path to instjdbc.sql. (You copied this script to the SQL_Server_Root/bin directory in step 1.)  The instjdbc.sql script generates many messages. In general, these messages can be ignored; however, the system administrator should scan the output for any messages that may indicate an execution error. The last message should indicate that instjdbc.sql ran successfully. The script fails when there is insufficient space available in the master database to store the JDBC XA procedures or to log changes to existing procedures.

  Read the article

• Computer Science Career Advice: Master's in Computer Science vs. Software Engineering?

  - by Everton

  Hello, I am a college student and I am majoring in Computer Science and Applied Mathematics. As I get closer to my senior year I have noticed that continuing my studies is the best choice right for me now. I see that several universities offer an Computer Science Master's Degree and an Software Engineering degree. What are their pros and cons? I feel that while the Computer Science master's degree seems a little too broad the Software Engineering is too restrictive. I did not decide yet between an career of Software development or research ( algorithm development among other things ). Any advice would be greatly apreciated!

  Read the article

• Where does a scrum master draw the line with blocking issues?

  - by Pete2k

  I understand the concept that a Scrum Master should remove impediments. However I'd like to know where exactly the line should be drawn. For example a team member might have an impediment whereby they need to get support to log onto a live server and get some details. They can't progress a bit of work until this is done. Its a blocking issue essentially. But should it be the Scrum master that goes to the Ops department and does this for the developer or if the developer can do it themselves should they just do it themselves. At what point does an issue become a blocking issue?

  Read the article

• Design Pattern for error handling in ASP.NET 3.5 site

  - by Kevin

  I am relatively new to ASP.NET programming, and web programming in general. We have a site we recently ported from .NET 1.1 to 3.5. Currently we have two methods of error handling: either catching the error during data load on a page and displaying the formatted error in a label on the page, or redirecting to a generic error page. Both of these are somewhat annoying, as right now I'm trying to redesign how our errors are displayed. We are soon moving to Master pages, and I'm wondering if there is a way to "build in" an error handling control. What I mean by this is using a ASP.NET user control I've designed that simply gets passed the error string returned from the server. If an error occurs, the page would not display the content, and instead display the error control. This provides us with the ability to retain the current banner/navigation during an error (which we don't get with the generic error page), as well as keeping me from having to add the control to every aspx page we have (which I have to do with using the label-per-page system). Does something like this make sense? Ultimately I just want to have the error control added to a single page, and all other pages have access to it directly. Is this something Master pages help with? Thanks!

  Read the article

• git rebase branch with all subbranches

  - by knittl

  is it possible to rebase a branch with all it's subbranches in git? i often use branches as quick/mutable tags to mark certain commits. * master * * featureA-finished * * origin/master now i want to rebase -i master onto origin/master, to change/reword the commit featureA-finished^ after git rebase -i --onto origin/master origin/master master, i basically want the history to be: * master * * featureA-finished * (changed/reworded) * origin/master but what i get is: * master * * (same changeset as featureA-finished) * (changed/reworded) | * featureA-finished |.* (original commit i wanted to edit) * origin/master is there a way around it, or am i stuck with recreating the branches on the new rebased commits?

  Read the article

• Ruby forwarding method calls

  - by JP

  I have an instance of a master class which generates instances of a subclass, these subclasses need to forward some method calls back to the master instance. At the moment I have code looking something like this, but it feels like I should be able to do the same thing more efficiently (maybe with method_missing?) class Master def initalize(mynum) @mynum = mynum end def one_thing(subinstance) "One thing with #{subinstance.var} from #{@mynum}" end def four_things(subinstance) "Four things with #{subinstance.var} from #{@mynum}" end def many_things(times,subinstance) "#{times} things with #{subinstance.var} from #{@mynum}" end def make_a_sub(uniqueness) Subthing.new(uniqueness,self) end class Subthing def initialize(uniqueness,master) @u = uniqueness @master = master end # Here I'm forwarding method calls def one_thing master.one_thing(self) end def four_things master.four_things(self) end def many_things(times) master.many_things(times,self) end end end m = Master.new(42) s = m.make_a_sub("very") s.one_thing === m.one_thing(s) s.many_things(8) === m.many_things(8,s) I hope you can see what's going on here. I would use method_missing, but I'm not sure how to cope with the possibility of some calls having arguments and some not (I can't really rearrange the order of the arguments to the Master methods either) Thanks for reading!

  Read the article

• Mail won't start up after migration to Mountain Lion Server

  - by Meltemi

  I'm not sure where to start. Our hard disk died on server running 10.6.8. Installed new disk. Followed Apple's migration instructions to 10.8.2. All services are operational except Mail. /var/log/mail.log Dec 6 17:52:17 [email protected] postfix/master[10370]: fatal: bind: private/smtp: Permission denied Dec 6 17:52:27 [email protected] postfix/master[10374]: fatal: bind: private/smtp: Permission denied Dec 6 17:52:37 [email protected] postfix/master[10376]: fatal: bind: private/smtp: Permission denied Dec 6 17:52:47 [email protected] postfix/master[10380]: fatal: bind: private/smtp: Permission denied Dec 6 17:52:57 [email protected] postfix/master[10382]: fatal: bind: private/smtp: Permission denied Dec 6 17:53:07 [email protected] postfix/master[10392]: fatal: bind: private/smtp: Permission denied Dec 6 17:53:17 [email protected] postfix/master[10394]: fatal: bind: private/smtp: Permission denied Dec 6 17:53:27 [email protected] postfix/master[10398]: fatal: bind: private/smtp: Permission denied Dec 6 17:53:38 [email protected] postfix/master[10400]: fatal: bind: private/smtp: Permission denied Dec 6 17:53:48 [email protected] postfix/master[10404]: fatal: bind: private/smtp: Permission denied Dec 6 17:53:58 [email protected] postfix/master[10411]: fatal: bind: private/smtp: Permission denied Dec 6 17:54:08 [email protected] postfix/master[10421]: fatal: bind: private/smtp: Permission denied

  Read the article

• Hibernate Query Exception

  - by dharga

  I've got a hibernate query I'm trying to get working but keep getting an exception with a not so helpful stack trace. I'm including the code, the stack trace, and hibernate chatter before the exception is thrown. If you need me to include the entity classes for MessageTarget and GrpExclusion let me know in comments and I'll add them. public List<MessageTarget> findMessageTargets(int age, String gender, String businessCode, String groupId, String systemCode) { Session session = getHibernateTemplate().getSessionFactory().openSession(); List<MessageTarget> results = new ArrayList<MessageTarget>(); try { String hSql = "from MessageTarget mt where " + "not exists (select GrpExclusion where grp_no = ?) and " + "(trgt_gndr_cd = 'A' or trgt_gndr_cd = ?) and " + "sys_src_cd = ? and " + "bampi_busn_sgmnt_cd = ? and " + "trgt_low_age <= ? and " + "trgt_high_age >= ? and " + "(effectiveDate is null or effectiveDate <= ?) and " + "(termDate is null or termDate >= ?)"; results = session.createQuery(hSql) .setParameter(0, groupId) .setParameter(1, gender) .setParameter(2, systemCode) .setParameter(3, businessCode) .setParameter(4, age) .setParameter(5, age) .setParameter(6, new Date()) .setParameter(7, new Date()) .list(); } catch (Exception e) { System.err.println(e.getMessage()); e.printStackTrace(); } finally { session.close(); } return results; } Here's the stacktrace. [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R java.lang.NullPointerException [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.ast.util.SessionFactoryHelper.findSQLFunction(SessionFactoryHelper.java:365) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.ast.tree.IdentNode.getDataType(IdentNode.java:289) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:165) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:831) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:619) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:672) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.collectionFunctionOrSubselect(HqlSqlBaseWalker.java:4465) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4165) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1864) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1839) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1789) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1789) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1789) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1789) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1789) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1789) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1789) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:818) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:604) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:288) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:231) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:254) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:185) [5/6/10 15:05:21:041 EDT] 00000017 SystemErr R at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1651) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.bcbst.bamp.ws.dao.MessageTargetDAOImpl.findMessageTargets(MessageTargetDAOImpl.java:30) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.bcbst.bamp.ws.common.AlertReminder.findMessageTargets(AlertReminder.java:22) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at java.lang.reflect.Method.invoke(Method.java:599) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.apache.axis2.jaxws.server.dispatcher.JavaDispatcher.invokeTargetOperation(JavaDispatcher.java:81) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.apache.axis2.jaxws.server.dispatcher.JavaBeanDispatcher.invoke(JavaBeanDispatcher.java:98) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.apache.axis2.jaxws.server.EndpointController.invoke(EndpointController.java:109) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.apache.axis2.jaxws.server.JAXWSMessageReceiver.receive(JAXWSMessageReceiver.java:159) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:188) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:275) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.websvcs.transport.http.WASAxis2Servlet.doPost(WASAxis2Servlet.java:1389) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:738) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at javax.servlet.http.HttpServlet.service(HttpServlet.java:831) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1536) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:829) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:458) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:175) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:3742) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:276) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:929) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1583) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:178) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:455) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:384) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:272) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905) [5/6/10 15:05:21:057 EDT] 00000017 SystemErr R at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1550) Here's the hibernate chatter. [5/6/10 15:05:20:651 EDT] 00000017 XmlBeanDefini I org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions Loading XML bean definitions from class path resource [beans.xml] [5/6/10 15:05:20:823 EDT] 00000017 Configuration I org.slf4j.impl.JCLLoggerAdapter info configuring from url: file:/C:/workspaces/bampi/AlertReminderWS/WebContent/WEB-INF/classes/hibernate.cfg.xml [5/6/10 15:05:20:838 EDT] 00000017 Configuration I org.slf4j.impl.JCLLoggerAdapter info Configured SessionFactory: java:hibernate/Alert/SessionFactory1.0.3 [5/6/10 15:05:20:838 EDT] 00000017 AnnotationBin I org.hibernate.cfg.AnnotationBinder bindClass Binding entity from annotated class: com.bcbst.bamp.ws.model.MessageTarget [5/6/10 15:05:20:838 EDT] 00000017 EntityBinder I org.hibernate.cfg.annotations.EntityBinder bindTable Bind entity com.bcbst.bamp.ws.model.MessageTarget on table MessageTarget [5/6/10 15:05:20:854 EDT] 00000017 AnnotationBin I org.hibernate.cfg.AnnotationBinder bindClass Binding entity from annotated class: com.bcbst.bamp.ws.model.GrpExclusion [5/6/10 15:05:20:854 EDT] 00000017 EntityBinder I org.hibernate.cfg.annotations.EntityBinder bindTable Bind entity com.bcbst.bamp.ws.model.GrpExclusion on table GrpExclusion [5/6/10 15:05:20:854 EDT] 00000017 CollectionBin I org.hibernate.cfg.annotations.CollectionBinder bindOneToManySecondPass Mapping collection: com.bcbst.bamp.ws.model.MessageTarget.exclusions -> GrpExclusion [5/6/10 15:05:20:885 EDT] 00000017 AnnotationSes I org.springframework.orm.hibernate3.LocalSessionFactoryBean buildSessionFactory Building new Hibernate SessionFactory [5/6/10 15:05:20:901 EDT] 00000017 ConnectionPro I org.slf4j.impl.JCLLoggerAdapter info Initializing connection provider: org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider [5/6/10 15:05:20:901 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info RDBMS: Microsoft SQL Server, version: 9.00.4035 [5/6/10 15:05:20:901 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info JDBC driver: Microsoft SQL Server 2005 JDBC Driver, version: 1.2.2828.100 [5/6/10 15:05:20:901 EDT] 00000017 Dialect I org.slf4j.impl.JCLLoggerAdapter info Using dialect: org.hibernate.dialect.SQLServerDialect [5/6/10 15:05:20:916 EDT] 00000017 TransactionFa I org.slf4j.impl.JCLLoggerAdapter info Transaction strategy: org.springframework.orm.hibernate3.SpringTransactionFactory [5/6/10 15:05:20:916 EDT] 00000017 TransactionMa I org.slf4j.impl.JCLLoggerAdapter info No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended) [5/6/10 15:05:20:916 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Automatic flush during beforeCompletion(): disabled [5/6/10 15:05:20:916 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Automatic session close at end of transaction: disabled [5/6/10 15:05:20:916 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Scrollable result sets: enabled [5/6/10 15:05:20:916 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info JDBC3 getGeneratedKeys(): enabled [5/6/10 15:05:20:916 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Connection release mode: auto [5/6/10 15:05:20:916 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Default batch fetch size: 1 [5/6/10 15:05:20:916 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Generate SQL with comments: disabled [5/6/10 15:05:20:916 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Order SQL updates by primary key: disabled [5/6/10 15:05:20:932 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Order SQL inserts for batching: disabled [5/6/10 15:05:20:932 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory [5/6/10 15:05:20:932 EDT] 00000017 ASTQueryTrans I org.slf4j.impl.JCLLoggerAdapter info Using ASTQueryTranslatorFactory [5/6/10 15:05:20:932 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Query language substitutions: {} [5/6/10 15:05:20:932 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info JPA-QL strict compliance: disabled [5/6/10 15:05:20:932 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Second-level cache: enabled [5/6/10 15:05:20:932 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Query cache: disabled [5/6/10 15:05:20:932 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Cache region factory : org.hibernate.cache.impl.bridge.RegionFactoryCacheProviderBridge [5/6/10 15:05:20:932 EDT] 00000017 RegionFactory I org.slf4j.impl.JCLLoggerAdapter info Cache provider: org.hibernate.cache.NoCacheProvider [5/6/10 15:05:20:948 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Optimize cache for minimal puts: disabled [5/6/10 15:05:20:948 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Structured second-level cache entries: disabled [5/6/10 15:05:20:948 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Statistics: disabled [5/6/10 15:05:20:948 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Deleted entity synthetic identifier rollback: disabled [5/6/10 15:05:20:948 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Default entity-mode: pojo [5/6/10 15:05:20:948 EDT] 00000017 SettingsFacto I org.slf4j.impl.JCLLoggerAdapter info Named query checking : enabled [5/6/10 15:05:20:979 EDT] 00000017 SessionFactor I org.slf4j.impl.JCLLoggerAdapter info building session factory [5/6/10 15:05:21:010 EDT] 00000017 SessionFactor I org.slf4j.impl.JCLLoggerAdapter info Factory name: java:hibernate/Alert/SessionFactory1.0.3 [5/6/10 15:05:21:010 EDT] 00000017 NamingHelper I org.slf4j.impl.JCLLoggerAdapter info JNDI InitialContext properties:{} [5/6/10 15:05:21:010 EDT] 00000017 NamingHelper I org.slf4j.impl.JCLLoggerAdapter info Creating subcontext: java:hibernate [5/6/10 15:05:21:010 EDT] 00000017 NamingHelper I org.slf4j.impl.JCLLoggerAdapter info Creating subcontext: Alert [5/6/10 15:05:21:010 EDT] 00000017 SessionFactor I org.slf4j.impl.JCLLoggerAdapter info Bound factory to JNDI name: java:hibernate/Alert/SessionFactory1.0.3 [5/6/10 15:05:21:026 EDT] 00000017 SessionFactor W org.slf4j.impl.JCLLoggerAdapter warn InitialContext did not implement EventContext [5/6/10 15:05:21:041 EDT] 00000017 PARSER E org.slf4j.impl.JCLLoggerAdapter error <AST>:0:0: unexpected end of subtree

  Read the article

• SUBSONIC 3.0.0.3 Subsonic.Query.SqlQuery

  - by dancingn27

  New to subsonic and having issues figuring it out. I am simply just trying to do a distinct search and any documentation I find is telling me to use the class/method SubSonic.SqlQuery Though I am finding out that since I am using the newest version, a lot of the documentation I am finding does not apply. For example, I am getting this query working beautifully using Subsonic.Query.SqlQuery though there is NO distinct method hanging off of it as suggested by what I have seen. Please advice! SubSonic.Query.SqlQuery query = brickDB.SelectColumns(new string[] { "DomainName" }).From<Web.Data.DB.WebLog>() .Where(Web.Data.DB.WebLogTable.DomainNameColumn).IsNotNull(); -> No distinct hanging off of From<>()....

  Read the article

• Slow query with unexpected index scan

  - by zerkms

  Hello I have this query: SELECT * FROM sample INNER JOIN test ON sample.sample_number = test.sample_number INNER JOIN result ON test.test_number = result.test_number WHERE sampled_date BETWEEN '2010-03-17 09:00' AND '2010-03-17 12:00' the biggest table here is RESULT, contains 11.1M records. The left 2 tables about 1M. this query works slowly (more than 10 minutes) and returns about 800 records. executing plan shows clustered index scan (over it's PRIMARY KEY (result.result_number, which actually doesn't take part in query)) over all 11M records. RESULT.TEST_NUMBER is a clustered primary key. if I change 2010-03-17 09:00 to 2010-03-17 10:00 - i get about 40 records. it executes for 300ms. and plan shows index seek (over result.test_number index) if i replace * in SELECT clause to result.test_number (covered with index) - then all become fast in first case too. this points to hdd IO issues, but doesn't clarifies changing plan. so, any ideas? UPDATE: sampled_date is in table sample and covered by index. other fields from this query: test.sample_number is covered by index and result.test_number too. UPDATE 2: obviously than sql server in any reasons don't want to use index. i did a small experiment: i remove INNER JOIN with result, select all test.test_number and after that do SELECT * FROM RESULT WHERE TEST_NUMBER IN (...) this, of course, works fast. but i cannot get what is the difference and why query optimizer choose such inappropriate way to select data in 1st case.

  Read the article

• Query a location's WOEID using YQL and javascript/jquery

  - by nav

  Hi , I need to query a locations WOEID and grab the WOEID value from the xml returned. So the user would type e.g. London, UK and I need to load the query as below: http://query.yahooapis.com/v1/public/yql?q=select%20woeid%20from%20geo.places%20where%20text%20%3D%20%22London%2C%20UK%2C%20UK%22&format=xml After which I need to grab the WOEID value from the returned XML. Is there a way to do this all client side, using Javascript/ JQuery? Thanks alot

  Read the article

• Cache the result of a MySQLdb database query in memory

  - by ensnare

  Our application fetches the correct database server from a pool of database servers. So each query is really 2 queries, and they look like this: Fetch the correct DB server Execute the query We do this so we can take DB servers online and offline as necessary, as well as for load-balancing. But the first query seems like it could be cached to memory, so it only actually queries the database every 5 or 10 minutes or so. What's the best way to do this? Thanks.

  Read the article

• Sql Query - Selecting rows where user can be both friend and user

  - by Gublooo

  Hey Sorry the title is not very clear. This is a follow up to my earlier question where one of the members helped me with a query. I have a following friends Table Friend friend_id - primary key user_id user_id_friend status The way the table is populated is - when I send a friend request to John - my userID appears in user_id and Johns userID appears in user_id_friend. Now another scenario is say Mike sends me a friend request - in this case mike's userID will appear in user_id and my userID will appear in user_id_friend So to find all my friends - I need to run a query to find where my userID appears in both user_id column as well as user_id_friend column What I am trying to do now is - when I search for user say John - I want all users Johns listed on my site to show up along with the status of whether they are my friend or not and if they are not - then show a "Add Friend" button. Based on the previous post - I got this query which does part of the job - My example user_id is 1: SELECT u.user_id, f.status FROM user u LEFT OUTER JOIN friend f ON f.user_id = u.user_id and f.user_id_friend = 1 where u.name like '%' So this only shows users with whom I am friends where they have sent me request ie my userID appears in user_id_friend. Although I am friends with others (where my userID appears in user_id column) - this query will return that as null To get those I need another query like this SELECT u.user_id, f.status FROM user u LEFT OUTER JOIN friend f ON f.user_id_friend = u.user_id and f.user_id = 1 where u.name like '%' So how do I combine these queries to return 1 set of users and what my friendship status with them is. I hope my question is clear Thanks

  Read the article

• MySQL Query performance - huge difference in time

  - by Damo

  I have a query that is returning in vastly different amounts of time between 2 datasets. For one set (database A) it returns in a few seconds, for the other (database B)....well I haven't waited long enough yet, but over 10 minutes. I have dumped both of these databases to my local machine where I can reproduce the issue running MySQL 5.1.37. Curiously, database B is smaller than database A. A stripped down version of the query that reproduces the problem is: SELECT * FROM po_shipment ps JOIN po_shipment_item psi USING (ship_id) JOIN po_alloc pa ON ps.ship_id = pa.ship_id AND pa.UID_items = psi.UID_items JOIN po_header ph ON pa.hdr_id = ph.hdr_id LEFT JOIN EVENT_TABLE ev0 ON ev0.TABLE_ID1 = ps.ship_id AND ev0.EVENT_TYPE = 'MAS0' LEFT JOIN EVENT_TABLE ev1 ON ev1.TABLE_ID1 = ps.ship_id AND ev1.EVENT_TYPE = 'MAS1' LEFT JOIN EVENT_TABLE ev2 ON ev2.TABLE_ID1 = ps.ship_id AND ev2.EVENT_TYPE = 'MAS2' LEFT JOIN EVENT_TABLE ev3 ON ev3.TABLE_ID1 = ps.ship_id AND ev3.EVENT_TYPE = 'MAS3' LEFT JOIN EVENT_TABLE ev4 ON ev4.TABLE_ID1 = ps.ship_id AND ev4.EVENT_TYPE = 'MAS4' LEFT JOIN EVENT_TABLE ev5 ON ev5.TABLE_ID1 = ps.ship_id AND ev5.EVENT_TYPE = 'MAS5' WHERE ps.eta >= '2010-03-22' GROUP BY ps.ship_id LIMIT 100; The EXPLAIN query plan for the first database (A) that returns in ~2 seconds is: +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | ps | range | PRIMARY,IX_ETA_DATE | IX_ETA_DATE | 4 | NULL | 174 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | ev0 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev1 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev2 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev3 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev4 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev5 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_PROD.ps.ship_id,const | 1 | | | 1 | SIMPLE | psi | ref | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1 | IX_po_shipment_item_po_shipment1 | 4 | UNIVIS_PROD.ps.ship_id | 1 | | | 1 | SIMPLE | pa | ref | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | FK_po_alloc_po_shipment1 | 4 | UNIVIS_PROD.psi.ship_id | 5 | Using where | | 1 | SIMPLE | ph | eq_ref | PRIMARY,IX_HDR_ID | PRIMARY | 4 | UNIVIS_PROD.pa.hdr_id | 1 | | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------+------+----------------------------------------------+ The EXPLAIN query plan for the second database (B) that returns in 600 seconds is: +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | ps | range | PRIMARY,IX_ETA_DATE | IX_ETA_DATE | 4 | NULL | 38 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | psi | ref | PRIMARY,IX_po_shipment_item_po_shipment1,FK_po_shipment_item_po_shipment1 | IX_po_shipment_item_po_shipment1 | 4 | UNIVIS_DEV01.ps.ship_id | 1 | | | 1 | SIMPLE | ev0 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | | | 1 | SIMPLE | ev1 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | | | 1 | SIMPLE | ev2 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | | | 1 | SIMPLE | ev3 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | | | 1 | SIMPLE | ev4 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.psi.ship_id,const | 1 | | | 1 | SIMPLE | ev5 | ref | IX_EVENT_ID_EVENT_TYPE | IX_EVENT_ID_EVENT_TYPE | 36 | UNIVIS_DEV01.ps.ship_id,const | 1 | | | 1 | SIMPLE | pa | ref | IX_po_alloc_po_shipment_item2,IX_po_alloc_po_details_old,FK_po_alloc_po_shipment1,FK_po_alloc_po_shipment_item1,FK_po_alloc_po_header1 | IX_po_alloc_po_shipment_item2 | 4 | UNIVIS_DEV01.ps.ship_id | 4 | Using where | | 1 | SIMPLE | ph | eq_ref | PRIMARY,IX_HDR_ID | PRIMARY | 4 | UNIVIS_DEV01.pa.hdr_id | 1 | | +----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------+------+----------------------------------------------+ When database B is running I can look at the MySQL Administrator and the state remains at "Copying to tmp table" indefinitely. Database A also has this state but for only a second or so. There are no differences in the table structure, indexes, keys etc between these databases (I have done show create tables and diff'd them). The sizes of the tables are: database A: po_shipment 1776 po_shipment_item 1945 po_alloc 36298 po_header 71642 EVENT_TABLE 1608 database B: po_shipment 463 po_shipment_item 470 po_alloc 3291 po_header 56149 EVENT_TABLE 1089 Some points to note: Removing the WHERE clause makes the query return < 1 sec. Removing the GROUP BY makes the query return < 1 sec. Removing ev5, ev4, ev3 etc makes the query get faster for each one removed. Can anyone suggest how to resolve this issue? What have I missed? Many Thanks.

  Read the article

< Previous Page | 101 102 103 104 105 106 107 108 109 110 111 112  | Next Page >