Search Results

Search found 37012 results on 1481 pages for 'sql query'.

Page 232/1481 | < Previous Page | 228 229 230 231 232 233 234 235 236 237 238 239  | Next Page >

• SQL Server catch error from extended stored procedure

  - by haxelit

  Hello I have an extended stored procedure that sends an error message. srv_sendmsg(pSrvProc, SRV_MSG_ERROR, errorNum, SRV_FATAL_SERVER, 1, NULL, 0, (DBUSMALLINT) __LINE__, buff, SRV_NULLTERM); I've set the severity to SVR_FATAL_SERVER just as a test to see if I can cause the message to throw an exception in the sql. In my SQL i'm doing: BEGIN TRY EXEC dbo.xp_somethingCool SET @Error = @@ERROR END TRY BEGIN CATCH PRINT 'AN Error occoured!' SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage; END CATCH I would think that when my xp sends the error message the tsql would catch the error and select the error_number and error_message. Instead what ends up happening is that the xp sends the message and the T-SQL continues on its way like nothing happened. The @@Error variable doesn't get set either. So I was wondering if there was any trick to getting SQL to catch an error from an XP ? Thanks, Raul

  Read the article

• Adding one subquery makes query a little slower, adding another makes it way slower

  - by Jason Swett

  This is fast: select ba.name, penamt.value penamt, #address_line4.value address_line4 from account a join customer c on a.customer_id = c.id join branch br on a.branch_id = br.id join bank ba on br.bank_id = ba.id join account_address aa on aa.account_id = a.id join address ad on aa.address_id = ad.id join state s on ad.state_id = s.id join import i on a.import_id = i.id join import_bundle ib on i.import_bundle_id = ib.id join (select * from unused where heading_label = 'PENAMT') penamt ON penamt.account_id = a.id #join (select * from unused where heading_label = 'Address Line 4') address_line4 ON address_line4.account_id = a.id where i.active=1 And this is fast: select ba.name, #penamt.value penamt, address_line4.value address_line4 from account a join customer c on a.customer_id = c.id join branch br on a.branch_id = br.id join bank ba on br.bank_id = ba.id join account_address aa on aa.account_id = a.id join address ad on aa.address_id = ad.id join state s on ad.state_id = s.id join import i on a.import_id = i.id join import_bundle ib on i.import_bundle_id = ib.id #join (select * from unused where heading_label = 'PENAMT') penamt ON penamt.account_id = a.id join (select * from unused where heading_label = 'Address Line 4') address_line4 ON address_line4.account_id = a.id where i.active=1 But this is slow: select ba.name, penamt.value penamt, address_line4.value address_line4 from account a join customer c on a.customer_id = c.id join branch br on a.branch_id = br.id join bank ba on br.bank_id = ba.id join account_address aa on aa.account_id = a.id join address ad on aa.address_id = ad.id join state s on ad.state_id = s.id join import i on a.import_id = i.id join import_bundle ib on i.import_bundle_id = ib.id join (select * from unused where heading_label = 'PENAMT') penamt ON penamt.account_id = a.id join (select * from unused where heading_label = 'Address Line 4') address_line4 ON address_line4.account_id = a.id where i.active=1 Why is it fast when I include just one of the two subqueries but slow when I include both? I would think it should be twice as slow when I include both, but it takes a really long time. On on MySQL.

  Read the article

• Most optimal order (of joins) for left join

  - by Ram

  I have 3 tables Table1 (with 1020690 records), Table2(with 289425 records), Table 3(with 83692 records).I have something like this SELECT * FROM Table1 T1 /* OK fine select * is bad when not all columns are needed, this is just an example*/ LEFT JOIN Table2 T2 ON T1.id=T2.id LEFT JOIN Table3 T3 ON T1.id=T3.id and a query like this SELECT * FROM Table1 T1 LEFT JOIN Table3 T3 ON T1.id=T3.id LEFT JOIN Table2 T2 ON T1.id=T2.id The query plan shows me that it uses 2 Merge Join for both the joins. For the first query, the first merge is with T1 and T2 and then with T3. For the second query, the first merge is with T1 and T3 and then with T2. Both these queries take about the same time(40 seconds approx.) or sometimes Query1 takes couple of seconds longer. So my question is, does the join order matter ?

  Read the article

• multi word query in mysql

  - by salmane

  Hi there , in order to make things easier for users i want to add multiple keyword search to my site. so that in the input the user would do something like : " keyword1 keyword 2" ( similar to google for example. would i need to write a code that would parse that string and do queries based on that or is there something built in mysql that could do it?

  Read the article

• Sort a List<T> using query expressions - LINQ C#

  - by Dan Yack

  I have a problem using Linq to order a structure like this : public class Person { public int ID { get; set; } public List<PersonAttribute> Attributes { get; set; } } public class PersonAttribute { public int ID { get; set; } public string Name { get; set; } public string Value { get; set; } } A person might go like this: PersonAttribute Age = new PersonAttribute { ID = 8, Name = "Age", Value = "32" }; PersonAttribute FirstName = new PersonAttribute { ID = 9, Name = "FirstName", Value = "Rebecca" }; PersonAttribute LastName = new PersonAttribute { ID = 10, Name = "LastName", Value = "Johnson" }; PersonAttribute Gender = new PersonAttribute { ID = 11, Name = "Gender", Value = "Female" }; I would like to use LINQ projection to sort a list of persons ascending by the person attribute of my choice, for example, sort on Age, or sort on FirstName. I am trying something like string mySortAttribute = "Age" PersonList.OrderBy(p => p.PersonAttribute.Find(s => s.Name == mySortAttribute).Value); But the syntax is failing me. Any clues? Thanks in advance!

  Read the article

• SQL to CodeIgniter Array Missing Data Issue

  - by SamD

  $query = $this->db->query("SELECT t1.numberofbets, t1.profit, t2.seven_profit, t3.28profit, user.user_id, username, password, email, balance, user.date_added, activation_code, activated FROM user LEFT JOIN (SELECT user_id, SUM(amount_won) AS profit, count(tip_id) AS numberofbets FROM tip GROUP BY user_id) as t1 ON user.user_id = t1.user_id LEFT JOIN (SELECT user_id, SUM(amount_won) AS seven_profit FROM tip WHERE date_settled > '$seven_daystime' GROUP BY user_id) as t2 ON user.user_id = t2.user_id LEFT JOIN (SELECT user_id, SUM(amount_won) AS 28profit FROM tip WHERE date_settled > '$twoeight_daystime' GROUP BY user_id) as t3 ON user.user_id = t3.user_id where activated = 1 GROUP BY user.user_id ORDER BY user.date_added DESC"); return $query->result_array(); The query works fine running it in phpMyAdmin and returns complete results (in image attached). However, printing the array in CodeIgniter, it has no value for one field ,seven_profit, where it is there in the SQL query ran in phpMyAdmin, just the discrepancy in this one field, from sql to php array... I just can’t see why, when printing the array, that one field, which should have value of 26, contains nothing? Any ideas? I changed the field name from starting with a number in attempt to fix it, but no difference. I know this is complex and looks horrible, any help or just people coming across something similar would be great to know about, thanks. Sam

  Read the article

• locked stored procedures in sql

  - by Greg

  Hi, I am not too familiar with sql server 2005. I have a schema in sql which has stored procedures with small lock on them. As I understand they were created using C#, all these locked procedures have a source file in C# with the code of the procedures. The thing is I can't access them. I need to modify one of these procedures but it doesn't let me modify them. I have the source code (from visual studio) with these procedures but when I change something in the code, it doesn't affect the procedures in the sql. How can I change the path to assembly in sql server 2005 or is there any other way I can access these stored procedures? Thanks in advance, Greg

  Read the article

• Query column and everything subordinate (hard to describe, non native speaker, PLS let me explain)

  - by MAD9

  A few weeks ago, I asked a question about how to generate hierarchical XML from a table, that has a parentID column. It all works fine. The point is, according to the hierarchy, I also want to query a table. I'll give you an example: Thats the table with the codes: ID CODE NAME PARENTID 1 ROOT IndustryCode NULL 2 IND Industry 1 3 CON Consulting 1 4 FIN Finance 1 5 PHARM Pharmaceuticals 2 6 AUTO Automotive 2 7 STRAT Strategy 3 8 IMPL Implementation 3 9 CFIN Corporate Finance 4 10 CMRKT Capital Markets 9 From which I generate (for displaying in a TreeViewControl) this XML: <record key="1" parentkey="" Code="ROOT" Name="IndustryCode"> <record key="2" parentkey="1" Code="IND" Name="Industry"> <record key="5" parentkey="2" Code="PHARM" Name="Pharmaceuticals" /> <record key="6" parentkey="2" Code="AUTO" Name="Automotive" /> </record> <record key="3" parentkey="1" Code="CON" Name="Consulting"> <record key="7" parentkey="3" Code="STRAT" Name="Strategy" /> <record key="8" parentkey="3" Code="IMPL" Name="Implementation" /> </record> <record key="4" parentkey="1" Code="FIN" Name="Finance"> <record key="9" parentkey="4" Code="CFIN" Name="Corporate Finance"> <record key="10" parentkey="9" Code="CMRKT" Name="Capital Markets" /> </record> </record> </record> As you can see, some codes are subordinate to others, for example AUTO << IND << ROOT What I want (and have absolutely no idea how to realise or even, where to start) is to be able to query another table (where one column is this certain code of course) for a code and get all records with the specific code and all subordinate codes For example: I query the other table for "IndustryCode = IND[ustry]" and get (of course) the records containing "IND", but also AUTO[motive] and PHARM[aceutical] (= all subordinates) Its an SQL Express Server 2008 with Advanced Services.

  Read the article

• SQL latest/top items in category

  - by drozzy

  What is a scalable way to select latest 10 items from each category. I have a schema list this: item category updated so I want to select 10 last update items from each category. The current solution I can come up with is to query for categories first and then issue some sort of union query: query = none for cat in categories: query += select top 10 from table where category=cat order by updated I am not sure how efficient this will be for bigger databases (1 million rows). If there is a way to do this in one go - that would be nice. Any help appreciated.

  Read the article

• Opaque tenant identification with SQL Server & NHibernate

  - by Anton Gogolev

  Howdy! We're developing a nowadays-fashionable multi-tenanted SaaS app (shared database, shared schema), and there's one thing I don't like about it: public class Domain : BusinessObject { public virtual long TenantID { get; set; } public virtual string Name { get; set; } } The TenantID is driving me nuts, as it has to be accounted for almost everywhere, and it's a hassle from security standpoint: what happens if a malicious API user changes TenantID to some other value and will mix things up. What I want to do is to get rid of this TenantID in our domain objects altogether, and to have either NHibernate or SQL Server deal with it. From what I've already read on the Internets, this can be done with CONTEXT_INFO (here's a NHibernatebased implementation), NHibernate filters, SQL Views and with combination thereof. Now, my requirements are as follows: Remove any mentions of TenantID from domain objects ...but have SQL Server insert it where appropriate (I guess this is achieved with default constraints) ...and obviously provide support for filtering based on this criteria, so that customers will never see each other's data If possible, avoid SQL Server views. Have a solution which plays nicely with NHibernate, SQL Servers' MARS and general nature of SaaS apps being highly concurrent What are your thoughts on that?

  Read the article

• Query to find tables modified in the last hour

  - by oalo

  I want to find out which tables have been modified in the last hour in a MySQL database. How can I do this? I am new to MySQL so please try to explain it as simply as you can. thank you!

  Read the article

• MS Query Analizer/Management Studio replacement?

  - by kprobst

  I've been using SQL Server since version 6.5 and I've always been a bit amazed at the fact that the tools seem to be targeted to DBAs rather than developers. I liked the simplicity and speed of the Query Analizer for example, but hated the built-in editor, which was really no better than a syntax coloring-capable Notepad. Now that we have Management Studio the management part seems a bit better but from a developer standpoint the tools is even worse. Visual Studio's excellent text editor... without a way to customize keyboard bindings!? Don't get me started on how unusable is the tree-based management hierarchy. Why can't I re-root the tree on a list of stored procs for example the way the Enterprise Manager used to allow? Now I have a treeview that needs to be scrolled horizontally, which makes it eminently useless. The SQL server support in Visual Studio is fantastic for working with stored procedures and functions, but it's terrible as a general ad hoc data query tool. I've tried various tools over the years but invariably they seem to focus on the management side and shortchange the developer in me. I just want something with basic admin capabilities, good keyboard support and requisite DDL functionality (ideally something like the Query Analyzer). At this point I'm seriously thinking of using vim+sqlcmd and a console... I'm that desperate :) Those of you who work day in and day out with SQL Server and Visual Studio... do you find the tools to be adequate? Have you ever wished they were better and if you have found something better, could you share please? Thanks!

  Read the article

• Query MSQL for winners, starting at xth place using SELECT

  - by incrediman

  In my MySQL table Winners, I have a list of people who have won. What I'd like to do is select a list of the names of 10 winners. So what I have right now is this: SELECT name FROM Winners ORDER BY points DESC LIMIT 10 This returns the first 10 winners which is great. But how can I make it (for example) return 10 winners, but starting at 20th place?

  Read the article

• Help with SQL Query

  - by djfrear

  With regards to the following statement: Select * From explorer.booking_record booking_record_ Inner Join explorer.client client_ On booking_record_.labelno = client_.labelno Inner Join explorer.tour_hotel tour_hotel_ On tour_hotel_.tourcode = booking_record_.tourrefcode Inner Join explorer.hotelrecord hotelrecord_ On tour_hotel_.hotelcode = hotelrecord_.hotelref Where booking_record_.bookingdate Not Like '0000-00-00' And booking_record_.tourdeparturedate Not Like '0000-00-00' And hotelrecord_.hotelgroup = "LPL" And Year(booking_record_.tourdeparturedate) Between Year(AddDate(Now(), Interval -5 Year)) And Year(Now()) My MySQL skills are certainly not up to scratch, the actual result set I wish to find is "a customer who has been to 5 or more LPL hotels in the past 5 years". So far I havent got as far as dealing with the count as I'm getting a huge number of results with some 250+ per customer. I assume this is to do with the way I'm joining tables. Schema wise the booking_record table contains a tour reference code, which links to tour_hotel which then contains a hotelcode which links to hotelrecord. This hotelrecord table contains the hotelgroup. The client table is joined to the booking_record via a booking reference and a client may have many bookings. If anyone could suggest a way for me to do this I'd be very grateful and hopefully learn enough to do it myself next time! I've been scratching my head over this one for a few hours now! Customers may have many bookings within booking_record Daniel.

  Read the article

• Error while installing Microsoft SQL Server 2005 on Windows 7

  - by Brian Roisentul

  I was trying to install Microsoft SQL Server 2005 on my Windows 7 pc. When I tried to install it, it tells me that I have a newer version installed on my pc. So, I checked that Microsoft Sharepoint 2010 installed some sql server 2008 tools for me. I decided to uninstall SharePoint 2010(which I wasn't using) and then tried to install MS SQL Server 2005 again. This threw me the following exception: Error 1603 installing microsoft sql server setup support files Searching on Google I found this article: http://support.microsoft.com/kb/916760 but it's not my case actually. Any help will be appreciated. I really need this to continue working.

  Read the article

• SQL Server 2008 - db mail issue

  - by Chris

  Hello. I have two instances of SQL Server 2008. One was upgraded from SQL Server 2000 and one was a clean, new install. SQL Mail operates perfectly on both instances. DB Mail operates perfectly on the newly installed instance. On the upgraded instance, DB Mail does not send any mail. Of course, I am not positive that the fact this instance is upgraded has anything to do with the issue, but it might. The configuration of my db mail profile and account looks identical to my functioning instance. In the configuration of the 'alerts' tab in the SQL Agent properties i have tried selecting both DB Mail and SQL Mail to no avail. Both instances use the same SMTP server with the same authentication (domain with db engine account). All messages sent via sp_send_db mail and those sent via the 'test email' option are visible in the sysmail_allitems queue and remain there as 'unsent'. The send_status eventually changes to 'failed'. The only messages in the sysmail_event_log are 'mail queue stopped by login domain\myuser', 'mail queue started by login domain/myuser' and 'activiation successful.'. selecting from the externalmailqueue has the same number of rows as sysmail_allitems. i have tried bouncing the agent, the entire instance and moving the other functioning instance to the other node in the cluster. any thoughts? thx.

  Read the article

• Mysterious constraints problem with SQL Server 2000

  - by Ramon

  Hi all I'm getting the following error from a VB NET web application written in VS 2003, on framework 1.1. The web app is running on Windows Server 2000, IIS 5, and is reading from a SQL server 2000 database running on the same machine. System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. at System.Data.DataSet.FailedEnableConstraints() at System.Data.DataSet.EnableConstraints() at System.Data.DataSet.set_EnforceConstraints(Boolean value) at System.Data.DataTable.EndLoadData() at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) The problem appears when the web app is under a high load. The system runs fine when volume is low, but when the number of requests becomes high, the system starts rejecting incoming requests with the above exception message. Once the problem appears, very few requests actually make it through and get processed normally, about 2 in every 30. The vast majority of requests fail, until a SQL Server restart or IIS reset is performed. The system then start processing requests normally, and after some time it starts throwing the same error. The error occurs when a data adapter runs the Fill() method against a SELECT statement, to populate a strongly-typed dataset. It appears that the dataset does not like the data it is given and throws this exception. This error occurs on various SELECT statements, acting on different tables. I have regenerated the dataset and checked the relevant constraints, as well as the table from which the data is read. Both the dataset definition and the data in the table are fine. Admittedly, the hardware running both the web app and SQL Server 2000 is seriously outdated, considering the numbers of incoming requests it currently receives. The amount of RAM consumed by SQL Server is dynamically allocated, and at peak times SQL Server can consume up to 2.8 GB out of a total of 3.5 GB on the server. At first I suspected some sort of index or database corruption, but after running DBCC CHECKDB, no errors were found in the database. So now I'm wondering whether this error is a result of the hardware limitations of the system. Is it possible for SQL Server to somehow mess up the data it's supposed to pass to the dataset, resulting in constraint violation due to, say, data type/length mismatch? I tried accessing the RowError messages of the data rows in the retrieved dataset tables but I kept getting empty strings. I know that HasErrors = true for the datatables in question. I have not set the EnableConstraints = false, and I don't want to do that. Thanks in advance. Ray

  Read the article

• Installer downloads wrong version of sql server compact edition

  - by MartinStettner

  I have a Visual Studio setup project which defines SQL Server 3.5 as prerequisite. I also set it up to download the required files from the source (i.e. Microsoft). My project uses Sql Server 3.5 SP1. If I create the installer on my development machine, everything works fine. When the installer is built on the build machine, it downloads the "old" 3.5 version (without SP1) which makes my application crash. (If I install SP1 on the test system manually, the application just works find...) I installed both Visual Studio 2008 SP1 and Sql Server 3.5 SP1 on the build machine. This seems to be some issue with the prerequisite packages but they look the same on the development and the build machines. Any idea what goes wrong here? EDIT Ok, I rechecked it and the package descriptions are'nt the sames. Oddly enough, the .msi file is the right one on the build machine but the old one on the development machine, so I guess if I tried to build an installer which includes the prerequisites, I'd get the wrong result on the dev machine and the right one on the build machine. Extended question: How are the prerequesite packages (under C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\SQL Server Compact Edition) meant to be updated to SP1? Is the SQL Server CE SP1 installer responsible for this?

  Read the article

• Load SQL query result data into cache in advance

  - by Marc

  I have the following situation: .net 3.5 WinForm client app accessing SQL Server 2008 Some queries returning relatively big amount of data are used quite often by a form Users are using local SQL Express and restarting their machines at least daily Other users are working remotely over slow network connections The problem is that after a restart, the first time users open this form the queries are extremely slow and take more or less 15s on a fast machine to execute. Afterwards the same queries take only 3s. Of course this comes from the fact that no data is cached and must be loaded from disk first. My question: Would it be possible to force the loading of the required data in advance into SQL Server cache? Note My first idea was to execute the queries in a background worker when the application starts, so that when the user starts the form the queries will already be cached and execute fast directly. I however don't want to load the result of the queries over to the client as some users are working remotely or have otherwise slow networks. So I thought just executing the queries from a stored procedure and putting the results into temporary tables so that nothing would be returned. Turned out that some of the result sets are using dynamic columns so I couldn't create the corresponding temp tables and thus this isn't a solution. Do you happen to have any other idea?

  Read the article

• ORM vs SQL XML, very simple middle-tier

  - by synergetic

  I know it is rather heated question. But anyway I'd like to hear opinions of those in Stackoverflow. Given that XML support is quite good in SQL Server 2005/2008, and there's no concern about database independency, why one need Linq-to-SQL, Entity Framework, NHibernate and the likes, which are quite complex and awkward in advanced use-cases, if by using POCOs, XmlSerializer, and stored procedures which process XML, one can achieve a lot less complex middle-tier? For reference, see the link: http://weblogs.asp.net/jezell/archive/2007/04/13/who-needs-orm-i-ve-got-sql-2005.aspx

  Read the article

• cannot read multiple rows from sqldatareader

  - by amby

  Hi, when i query for only one record/row, sqldatareader is giving correct result but when i query for multiple rows, its giving error on the client side. below is my code. please tell me what is the problem here. string query = "select * from Customer_Order where orderNumber = " + order;//+" OR orderNumber = 17"; DataTable dt = new DataTable(); Hashtable sendData = new Hashtable(); try { using (SqlConnection conn = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand(query, conn)) { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt.Load(dr); } }

  Read the article

• Installation doesn't recognize SQL server 2008 saying Server components not found

  - by Aarthi

  Please answer these 2 questions: Our installation is not recognising the SQL server 2008 and if we try to create a DB, its telling Server components not found. Please tell what may be the issue and why it's not recognising. But our installation proceeds as client installation(if i point to the same instance with DB already created) without any issues. Can we install SQL express 2005 in a machine where SQL server 2008 is already installed. Please clarify.

  Read the article

• Display field from another table in SQL

  - by Roland Bengtsson

  I'm a newbie with SQL... Now I want to do display some instances of AddrDistances from DevExpress CxGrid with SQL. Select Cast((DistanceAsMeters * 0.001) as Decimal(8,1)) DistanceAsKm, bold_id, created, fromAddress, toAddress From AddrDistance Where DistanceAsMeters = 0 and PseudoDistanceAsCostKm = 0 and not AddrDistance.bold_id in (select bold_id from DistanceQueryTask) Order By Created Desc This SQL is working and the result is: DistanceAsKM Bold_ID Created FromAddress ToAddress 0 134808 16.02.2010 121795 134570 0 121701 10.03.2010 120850 122991 The result I want is this: DistanceAsKM Bold_ID Created FromAddress ToAddress 0 134808 16.02.2010 Kalmar Stockholm 0 121701 10.03.2010 Falkenberg Oslo So the amount of rows is right but I want to replace the numbers in FromAddress and ToAddress with strings from another table. The numbers shows here is just the boldid. Every object in the database have an unique boldid. The addresses above is stored in table Address and it have a City field with the column and a boldid as a key. What should I write in SQL to get this right ? Is there something in the CxGrid that could help here ? Regards

  Read the article

• Doctrine/symfony: getSqlQuery() output in phpMyAdmin/SQL tab

  - by user248959

  Hi, i have created this query that works OK: $q1 = Doctrine_Query::create() ->from('Usuario u') ->leftJoin('u.AmigoUsuario a ON u.id = a.user2_id OR u.id = a.user1_id') ->where("a.user2_id = ? OR a.user1_id = ?", array($id,$id)) ->andWhere("u.id <> ?", $id) ->andWhere("a.estado LIKE ?", 1); echo $q1->getSqlQuery(); The calling to getSqlQuery outputs this clause: SELECT s.id AS s_id, s.username AS s_username, s.algorithm AS s_algorithm, s.salt AS s_salt, s.password AS s__password, s.is_active AS s__is_active, s.is_super_admin AS s__is_super_admin, s.last_login AS s__last_login, s.email_address AS s__email_address, s.nombre_apellidos AS s__nombre_apellidos, s.sexo AS s__sexo, s.fecha_nac AS s__fecha_nac, s.provincia AS s_provincia, s.localidad AS s_localidad, s.fotografia AS s_fotografia, s.avatar AS s_avatar, s.avatar_mensajes AS s__avatar_mensajes, s.created_at AS s__created_at, s.updated_at AS s__updated_at, a.id AS a__id, a.user1_id AS a__user1_id, a.user2_id AS a__user2_id, a.estado AS a__estado FROM sf_guard_user s LEFT JOIN amigo_usuario a ON ((s.id = a.user2_id OR s.id = a.user1_id)) WHERE ((a.user2_id = ? OR a.user1_id = ?) AND s.id < ? AND a.estado LIKE ?) If i take that clause to phpmyadmin SQL tab i get this error 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? OR a.user1_id = ?) AND s.id < ? AND a.estado LIKE ?) LIMIT 0, 30' at line 1 Why i'm getting this error? Regards Javi

  Read the article

• SQL Server full text query across multiple tables - why so slow?

  - by Mikey Cee

  Hi. I'm trying to understand the performance of an SQL Server 2008 full-text query I am constructing. The following query, using a full-text index, returns the correct results immediately: SELECT O.ID, O.Name FROM dbo.EventOccurrence O WHERE FREETEXT(O.Name, 'query') ie, all EventOccurrences with the word 'query' in their name. And the following query, using a full-text index from a different table, also returns straight away: SELECT V.ID, V.Name FROM dbo.Venue V WHERE FREETEXT(V.Name, 'query') ie. all Venues with the word 'query' in their name. But if I try to join the tables and do both full-text queries at once, it 12 seconds to return: SELECT O.ID, O.Name FROM dbo.EventOccurrence O INNER JOIN dbo.Event E ON O.EventID = E.ID INNER JOIN dbo.Venue V ON E.VenueID = V.ID WHERE FREETEXT(E.Name, 'search') OR FREETEXT(V.Name, 'search') Here is the execution plan: http://uploadpad.com/files/query.PNG From my reading, I didn't think it was even possible to make a free text query across multiple tables in this way, so I'm not sure I am understanding this correctly. Note that if I remove the WHERE clause from this last query then it returns all results within a second, so it's definitely the full-text that is causing the issue here. Can someone explain (i) why this is so slow and (ii) if this is even supported / if I am even understanding this correctly. Thanks in advance for your help.

  Read the article

< Previous Page | 228 229 230 231 232 233 234 235 236 237 238 239  | Next Page >