I have the following code: select order_number,received_date from order_head where order_head.order_number not in (select order_number from csa_log group by order_number) and ordernature in ('02','03') and received_date > '01.01.2010' and buyer_code = 'GAP' group by order_number,received_date order by received_date desc When run in cr...
Hi, I want to move full text catalogue for 1 database to a different location on same SQL server. I am using SQL 2005. One of the source said: SQL Server 2005 full-text search provides the ability to easily detach and move full-text catalogs in the same way that SQL Server database files may be detached, moved, and re-att...
OK. I'm doing an update on a single row in a table. All fields will be overwritten with new data except for the primary key. However, not all values will change b/c of the update. For example, if my table is as follows: TABLE (id int ident, foo varchar(50), bar varchar(50)) The initial value is: id foo bar ----------------- 1 ...
Why i get this error? Object cannot be cast from DBNull to other types. when i inserted null value for a numeric datatype in sql server 2005 Here is my code, if (MeasurementTr.Visible == true) { materialIn.measurementId = Convert.ToInt64(DlMeasurement.SelectedValue.ToString()); } else { materialIn.me...
How to use if and onlf if in a select statement in sql server 2005? Here is my select statement select Mat_Id,MeasurementId from InTime... Now i want to show measurementName which is Measurment Table based on MeasurementId.. Mind you MeasurementId is not a foriegn key... How to make join with Measurement table if there is value in...
How to Concat two column in a select statement sql server 2005? Here is my statement Select FirstName,secondName from Table... Now i did try concating secondName with FirstName by using Select FirstName + ' ' + secondName from Table But some values are NULL in secondName column for some records.. My select statement returns NULL i...
How to write if not exists statement in sql server 2005 for the following condition If @MeasurementId say values (1,2,3) those are the existing values the below statement work but when @MeasurementId=0 it doesn't seem to work... If not exists(select Mat_Id,MeasurementId from MaterialQuantity where Mat_Id=@Id and MeasurementId=@Measur...
Hi, I have a query joining 4 tables with a lot of conditions in the WHERE clause. The query also includes ORDER BY clause on a numeric column. It takes 6 seconds to return which is too long and I need to speed it up. Surprisingly I found that if I remove the ORDER BY clause it takes 2 seconds. Why the order by makes so massive difference...
I am asking this question in stackoverflow because its the right place to ask... I know its a very vast topic to start but some small ones which may be really handy... It might be useful for young developers like me to know about query optimization.. Some Tips and Tricks about query optimization in SQL Server 2005.. ...
I am having two tables, one is Staging and another one is Report. All processing happens in Staging and upon completion of such process I have to copy all the records to Report. The Staging table contains millions of records so I just want to know what is the fastest way to copy this data to Report. 3 options which I know are: Insert...
When capturing a deadlock event in the logs, I am getting a message that indicates: Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. When I look in the ERRORLOG file, I see something like this: 2010-01-18 04:47:15.69 spid4s Wait-for graph...
I have these data on a table (using SQL Server 2005): ID ParentID StartTime EndTime 77 62 08:00:00 11:00:00 78 62 12:00:00 15:00:00 79 62 18:00:00 22:00:00 and I want to transform it into this: ParentID BreakfastStart BreakfastEnd LunchStart LunchEnd DinnerStart DinnerEnd...
One third party app is storing data in a huge database (SQL Server 2000/2005). This database has more than 80 tables. How would I come to know that how many tables are affected when application stores a new record in database? Is there something available I can retrieve the list of tables affected? ...
Is there a way to truncate tables with constraints ? I tried to DEACTIvATE with this: DECLARE @SQLtxt varchar(max) SET @SQLtxt = '-- DESACTIVER LES CONTRAINTES' + CHAR(10) SELECT @SQLtxt = @SQLtxt + 'ALTER TABLE [' + name + '] NOCHECK CONSTRAINT ALL;' FROM sys.tables PRINT @SQLtxt Of course, it didn't worked. I have to drop the const...
Using SQL Server 2005: I have one stored proc that calls several others within it. One code segment, as an example, goes: INSERT INTO Log (...) VALUES (A...) EXECUTE StoredProcA params... INSERT INTO Log (...) VALUES (A...) INSERT INTO Log (...) VALUES (B...) EXECUTE StoredProcB params... INSERT INTO Log (...) VALUES (B...) It appe...
I have a stored procedure being executed from an ASP.NET application. Is it possible to attach to the procedure and step through it using SQL Server 2005? Note, in this instance, I am not using a DataAdapter. I'm going in blind. The connection string is being created on the fly, so I don't have access to the schema from the Visual St...
Normally I would just do this in the code itself, but I am curious if this can be accomplished efficiently in TSQL. Table 1 Date - Value Table 2 Date - Discount Table 1 contains entries for each day. Table 2 contains entries only when the discount changes. A discount applied to a value is considered valid until a new discount is en...
I have 4 tables Table1 (employee) id name -------------------- 1 a 2 b Table2 (appointment) id table1id table3id table4id sdate edate typeid ----------------------------------------------------------------------------------- 1 1 1 1 1/1/09 NULL 100...
I have a .bak file which contains backup sets of two different databases. It was made by sql server maintenance plan. Now I have to restore both databases. The problem is, that while the first database is restored ok (db_companies) the other database (db_data) gives an error: Restore failed for Server 'SBSERVER'. (Microsoft.SqlSer...
I want to do this, because I would like to know how many times a particular row has been changed. Is this possible? Thanks ...