Search Results

Search found 27339 results on 1094 pages for 'sql tempdb'.

Page 343/1094 | < Previous Page | 339 340 341 342 343 344 345 346 347 348 349 350  | Next Page >

• Fill in missing values in a SELECT statement

  - by benjamin button

  If i have a table with two fields.customer id and order. let's say i have in total order ID 1,2,3,4 all the customer can have all the four orders.like below 1234 1 1234 2 1234 3 1234 4 3245 3 3245 4 5436 2 5436 4 you can see above that 3245 customer doesnt have order id 1 and 2. how could i print in the query output like 3245 1 3245 2 5436 1 5436 3 EDIT: i dont have order table but i have list of order's like we can hard code it in the query(1,2,3,4) i dont have an orders table.

  Read the article

• how can i substitute a NULL value for a 0 in an SQL Query result

  - by Name.IsNullOrEmpty

  SELECT EmployeeMaster.EmpNo, Sum(LeaveApplications.LeaveDaysTaken) AS LeaveDays FROM EmployeeMaster FULL OUTER JOIN LeaveApplications ON EmployeeMaster.id = LeaveApplications.EmployeeRecordID INNER JOIN LeaveMaster ON EmployeeMaster.id = LeaveMaster.EmpRecordID GRoup BY EmployeeMaster.EmpNo order by LeaveDays Desc with the above query, if an employee has no leave application record in table LeaveApplications, then their Sum(LeaveApplications.LeaveDaysTaken) AS LeaveDays column returns NULL. What i would like to do is place a value of 0 (Zero) instead of NULL. I want to do this because i have a calculated column in the same query whose formular depends on the LeaveDays returned and when LeaveDays is NULL, the formular some how fails. Is there away i can put 0 for NULL such that that i can get my desired result.

  Read the article

• Adding more OR searches with CONTAINS Brings Query to Crawl

  - by scolja

  I have a simple query that relies on two full-text indexed tables, but it runs extremely slow when I have the CONTAINS combined with any additional OR search. As seen in the execution plan, the two full text searches crush the performance. If I query with just 1 of the CONTAINS, or neither, the query is sub-second, but the moment you add OR into the mix the query becomes ill-fated. The two tables are nothing special, they're not overly wide (42 cols in one, 21 in the other; maybe 10 cols are FT indexed in each) or even contain very many records (36k recs in the biggest of the two). I was able to solve the performance by splitting the two CONTAINS searches into their own SELECT queries and then UNION the three together. Is this UNION workaround my only hope? Thanks. SELECT a.CollectionID FROM collections a INNER JOIN determinations b ON a.CollectionID = b.CollectionID WHERE a.CollrTeam_Text LIKE '%fa%' OR CONTAINS(a.*, '"*fa*"') OR CONTAINS(b.*, '"*fa*"') Execution Plan (guess I need more reputation before I can post the image):

  Read the article

• Skip first letters of all values returned from a sql server database

  - by Peter

  Hi, as topic says, I don't want to return the first two letters in the return values just an example: select companyname from companies returns companyX Can I write a query that returns panyX instead? Thanks in advance

  Read the article

• Connecting to an MS SQL Server from Silverlight?

  - by cam

  Normally, I would use a PHP webservice to do this, but since the front-end is hosted on a linux box, I need another way to do this (so I don't have to go through the trouble of installing FreeTDS, etc. I will if I have to). Is there a better way to do this? I'm not a web guy, but I'm trying my best.

  Read the article

• help troubleshooting deadlocks in sql server database

  - by Makach

  I've got two database servers, (1) production (2) test on the production database I get frequent deadlocks and I'm trying to find out what is causing it. I take a backup of the database in production and restore it in test and when I perform the exact same scenario that yields deadlocks on the production server I am unable to reproduce in test. any ideas/tips/hints would be much appreciated.

  Read the article

• FreeText Query is slow - includes TOP and Order By

  - by Eric P

  The Product table has 700K records in it. The query: SELECT TOP 1 ID, Name FROM Product WHERE contains(Name, '"White Dress"') ORDER BY DateMadeNew desc takes about 1 minute to run. There is an non-clustered index on DateMadeNew and FreeText index on Name. If I remove TOP 1 or Order By - it takes less then 1 second to run. Here is the link to execution plan. http://screencast.com/t/ZDczMzg5N Looks like FullTextMatch has over 400K executions. Why is this happening? How can it be made faster?

  Read the article

• There is already an open DataReader associated with this Command error in Linq To sql

  - by dinesh

  I am getting the above error when i retreived the data from the Datacontext Model. it is happening now and then also.

  Read the article

• SQL where clasue to work with Group by clasue after performing a count()

  - by Matt

  Tried my usual references at w3schools and google. No luck I'm trying to produce the following results. QTY is a derived column | Position | QTY -------------------- 1 Clerk 2 2 Mgr 2 Here's what I'm not having luck with: SELECT Position, Count(position) AS 'QTY' FROM tblemployee Where ('QTY' != 1) GROUP BY Position I know that my Position is set up as varchar(255) Count produces a integer data and my where clasue is accurate so that leads me to believe that that Count() is jamming me up. Please throw up an example so I can reference later. Thanks for the help!

  Read the article

• sqlserver how to set job priority

  - by Buzz

  Is there any way to set one job priority higher then other, In my case there are two jobs those are working on same set of tables,first JOB-A which is running every 12 hr and other JOB-B is every 10 minutes , i think at some time when they run simultaneously JOB-B is getting in to deadlock and get failed, i google the topic and found sqlgoverner is helpful in such cases does anyone know how to resolve?

  Read the article

• Is there a way to split the results of a select query into two equal halfs?

  - by Matthias

  I'd like to have a query returning two ResultSets each of which holding exactly half of all records matching a certain criteria. I tried using TOP 50 PERCENT in conjunction with an Order By but if the number of records in the table is odd, one record will show up in both resultsets. Example: I've got a simple table with TheID (PK) and TheValue fields (varchar(10)) and 5 records. Skip the where clause for now. SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID asc results in the selected id's 1,2,3 SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID desc results in the selected id's 3,4,5 3 is a dup. In real life of course the queries are fairly complicated with a ton of where clauses and subqueries.

  Read the article

• Figuring out the resource a lock in SQL Server 2000 affects

  - by Michael Lang

  I am adding a simple web-interface to show data from a commercial off the shelf (COTS) application. This COTS issues locks on any record the user is actively looking at (whether they intend to edit and update it or not). I have found sp_lock and the Microsoft sp_lock2 scripts and can see the locks, so that's all well and good. However, I cannot figure out how I can tell if a specific record I am about to update has been affected by one of these locks. If I submit the update request and there is in fact a lock, the web-interface will wait indefinitely until the user closes the window in the COTS. How can I either: a) determine before issuing an update that the record has been locked OR b) issue an update that will immediately return with a LOCKED status rather than indefinitely waiting on the COTS user to close their window on that record?

  Read the article

• using NEWSEQUENTIALID() with UPDATE Trigger

  - by Ram

  I am adding a new GUID/Uniqueidentifier column to my table. ALTER TABLE table_name ADD VersionNumber UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWSEQUENTIALID() GO And when ever a record is updated in the table, I would want to update this column "VersionNumber". So I create a new trigger CREATE TRIGGER [DBO].[TR_TABLE_NAMWE] ON [DBO].[TABLE_NAME] AFTER UPDATE AS BEGIN UPDATE TABLE_NAME SET VERSIONNUMBER=NEWSEQUENTIALID() FROM TABLE_NAME D JOIN INSERTED I ON D.ID=I.ID/* some ID which is used to join*/ END GO But just realized that NEWSEQUENTIALID() can only be used with CREATE TABLE or ALTER TABLE. I got this error The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression. Is there a workaround for this ? Edit1: Changing NEWSEQUENTIALID() to NEWID() in the trigger solves this, but I am indexing this column and using NEWID() would be sub-optimal

  Read the article

• SqlServer 2008 - create database script (schema + data) with commadline

  - by dynback.com

  In SSMS when click on database "Tasks - Generate Scripts" I get an output script. How to make the same with non graphical tool?

  Read the article

• Is INT the correct datatype for ABS(CHECKSUM(NEWID()))?

  - by Chad Sellers

  I'm in the process of creating unique customers ID's that is an alternative Id for external use. In the process of adding a new column "cust_uid" with datatype INT for my unique ID's, When I do an INSERT into this new column: Insert Into Customers(cust_uid) Select ABS(CHECKSUM(NEWID())) I get a error: Could not create an acceptable cursor. OLE DB provider "SQLNCLI" for linked server "SHQ2IIS1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. I've check all data types on both tables and the only things that has changed is the new column in both tables. The update is being done on one Big @$$ table...and for reasons above my pay grade, we would like to have new uid's that are different form the one's that we currently have "so users don't know how many accounts we actually have." Is INT the correct datatype for ABS(CHECKSUM(NEWID())) ?

  Read the article

• Sum two rows in one - My Sql

  - by user303832

  I have found some similar posts, but I didn't find them useful. But I didn't know how to group them. I would like to Sum 'No' and 'Not Set' to one row, and to lose 'Not Set' row. So : 'No' = 'No' + 'Not Set' I have something like this : TEST TestCount Month 'Yes' 123 March 'No' 432 March 'Not Set' 645 March 'Yes' 13 April 'No' 42 April 'Not Set' 45 April 'Yes' 133 May 'No' 41 May 'Not Set' 35 May .... And I would like something like this : TEST TestCount Month 'Yes' 423 March 'No' 410 March 'Yes' 154 April 'No' 192 April 'Yes' 130 May 'No' 149 May .... Can anybody help me with this, tnx in advance

  Read the article

• query sql database for specific value in vb.net

  - by user2952298

  I am trying to convert VBA code to vb.net, im having trouble trying to search the database for a specific value around an if statement. any suggestions would be greatly appriciated. thedatabase is called confirmation, type is the column and email is the value im looking for. could datasets work? Function SendEmails() As Boolean Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookRecip As Outlook.Recipient Dim objOutlookAttach As Outlook.Attachment Dim intResponse As Integer Dim confirmation As New ADODB.Recordset Dim details As New ADODB.Recordset On Error GoTo Err_Execute Dim MyConnObj As New ADODB.Connection Dim cn As New ADODB.Connection() MyConnObj.Open( _ "Provider = sqloledb;" & _ "Server=myserver;" & _ "Database=Email_Text;" & _ "User Id=bla;" & _ "Password=bla;") confirmation.Open("Confirmation_list", MyConnObj) details.Open("MessagesToSend", MyConnObj) If details.EOF = False Then confirmation.MoveFirst() Do While Not confirmation.EOF If confirmation![Type] = "Email" Then ' Create the Outlook session. objOutlook = CreateObject("Outlook.Application") ' Create the message. End IF

  Read the article

• Convert datetime field in sql server 2005....

  - by Pandiya Chendur

  I have a datetime field which has a value like this 5/11/2010 12:04:20 PM .... I am converting this field convert(varchar, dbo.Clients.CreatedDate,103) as CreatedDate and i get the result as 11/5/2010..... But how to get 11/5/2010 12:04 PM.....

  Read the article

• SQL query showing missing expression

  - by Ashok Dasari

  Query to pull the data between Yseterday 6AM to today 6AM ... SELECT lot_id, log_time, batch_no, eqp_id, STATION_ID, EXTRACTVALUE (META_DATA, '/lot_info/A3') AS A3, EXTRACTVALUE (META_DATA, '/lot_info/A3Info') AS A3Info, EXTRACTVALUE ( META_DATA, '/lot_info/apc_status_info' ) AS apc_status_info FROM t_dlis_log_history WHERE ( (EQP_ID = 'ALC4360') OR (EQP_ID = 'ALC4361') OR (EQP_ID = 'ALC1360') OR (EQP_ID = 'ALC1361') OR (EQP_ID = 'ALC1362') OR (EQP_ID = 'ALC1363') OR (EQP_ID = 'ALC1364') OR (EQP_ID = 'ALC1365') OR (EQP_ID = 'ALC355') OR (EQP_ID = 'ALC353') OR (EQP_ID = 'ALC4350') OR (EQP_ID = 'ALC354') ) AND (( log_time >= DATEADD ( HOUR, 6, CONVERT(VARCHAR (10), GETDATE (), 110) ) AND ( log_time <= DATEADD ( HOUR, 6, CONVERT(VARCHAR (10), GETDATE () + 1, 110) ) ) ) It is showing error missing expression ...

  Read the article

• How I can move table to another filegroup ?

  - by denisioru

  Hello, I have MSSQL 2008 Ent and OLTP database with two big tables. How I can move this tables to another filegroup without service interrupting? Now, about 100-130 records inserted and 30-50 records updated each second in this tables. Each table have about 100M records and six fields (including one field geography). I looking for solution via google, but all solutions contain "create second table, insert rows from first table, drop first table, bla bla bla". Can I use partitioning functions for solving this problem? Thank you.

  Read the article

• SQL Server 2005: Insert a row in a table and update the same row

  - by vikas

  eg:table pkey --guid annualpay datefrom dateto--if null means current record percentannualincrease percent annual increase will be calculated only if there is a difference in newly inserted and previously existing last differing value. percentannualincrease = ([newannualpay-just previous pay(if different from current)]/newannualpay)*100 eg newid(),5000,today,null,0--very first row newid(),5000,today+1,null(*),0 newid,5500,today+2,null(*),?????????????--> need to be calculated before insert *--insert will close the previous record by updating dateto=null to todays date How can I do this stuff in a trigger???

  Read the article

• SQL - get latest records from table where field is unique

  - by 89stevenharris

  I have a table of data as follows id status conversation_id message_id date_created 1 1 1 72 2012-01-01 00:00:00 2 2 1 87 2012-03-03 00:00:00 3 2 2 95 2012-05-05 00:00:00 I want to get all the rows from the table in date_created DESC order, but only one row per conversation_id. So in the case of the example data above, I would want to get the rows with id 2 and 3. Any advice is much appreciated.

  Read the article

• Database normalization and duplicate values

  - by bretddog

  Consider a Parent / Child / GrandChild structure in a database table schema, or even a deeper hierarchy. These being in the same aggregate. One table DAYS keeps a single row per day, and has a "Date" field. This is the root table, or maybe a child of the root. No row can ever be deleted in this table. In this case, however complex my table schema looks like, however far away in the hierarchy any other table is, is there any reason why any other table would hold a Date value? Can't it instead just have a FK to the DAYS table. I obviously assume that the creation of these date fields happen not before such datefield exist in the DAYS table. I'm now thinking just about the date part to be relevant, not the time part. Not sure if all databases can store these individually. That's maybe relevant, but not really the focus of the question.

  Read the article

• How i can do the following querry to get needed information

  - by Night Walker

  Hello there I have two tables CompList table with following columns : CompId , McID , station , slot ,subslot , and several others BookingTable with columns: CompId , LineID , McID , station , slot ,subslot. I want to get following result: rows only that CompList.CompId == BookingTable.CompId (only CompId that is in both tables) and i need in the result columns from CompList: CompId , McID , station , slot ,subslot . and from BookingTable: LineID , McID , station , slot ,subslot and how i will be able to distinguish between same columns with same table in the result table them in the result table? Thanks for help.

  Read the article

• Keeping Linq to SQL alive when using ViewModels (ASP.NET MVC)

  - by Kohan

  I have recently started using custom ViewModels (For example, CustomerViewModel) public class CustomerViewModel { public IList<Customer> Customers{ get; set; } public int ProductId{ get; set; } public CustomerViewModel(IList<Customer> customers, int productId) { this.Customers= customers; this.ProductId= productId; } public CustomerViewModel() { } } ... and am now passing them to my view instead of the Entities themselves (for example, var Custs = repository.getAllCusts(id) ) as it seems good practice to do so. The problem i have encountered is that when using ViewModels; by the time it has got to the the view i have lost the ability to lazy load on customers. I do not believe this was the case before using them. Is it possible to retain the ability of Lazy Loading while still using ViewModels? Or do i have to eager load using this method? Thanks, Kohan.

  Read the article

< Previous Page | 339 340 341 342 343 344 345 346 347 348 349 350  | Next Page >