Delete all level child item using sql query

I have a table where I have menus listed where I can insert and delete. Structure goes like:- ID Name ParentId 1 1. Home 0 2 2. Products 0 3 a. SubProduct1 2 4 b. SubProduct2 2 5 i. Subsub 4 6 ii. …… 4 7 3. About 0 Top-level menu ParentId is always 0 […]

SQL Server query to show summary list for particular months or between dates

I have an attendance table which contains student’s attendance date and attendance status. Is there any way to display a summary something like this: Student 20-09-2012 21-09-2012 22-09-2012 23-09-2012 xyz P A P P abc P P P P Through a SQL Server query? I have tried with the PIVOT but it is not working […]

How to update only exact value matches?

I am using SQL update query [in powershell script] to update machine name present in column. The column values are stored as URL. So machine name is part of the string. ( Column Name : URL Example column value : “http://Machine1:80/Impl100/Core/Data/Config.0”) I am using following query to do the update. $UpdateQuery=”UPDATE [$DatabaseName].[dbo].[$TableName] SET $columnName=Replace(“$ColumnName”, ‘$OldMachineName’, […]

transport-level error has occurred when receiving results from the server. error: 18 – Connection has been closed by peer

I’m getting the aforementioned exception while trying to do an SqlDataReader sdr = command.ExecuteReader(); This is a complex (to me) program that takes a few hours to run before throwing this exception. I thought it was because there might be connections that aren’t being closed, but when I look at the database there’s only 1 […]

Trigger to Only Allow a Single Null Value Per Item ID

I am working on a database for equipment management, where users can sign in and out equipment. I have a table called TBL_Usage which keeps track of this. When an item is signed out, a new record is created in this table with the sign out date, and when it is signed back in, a […]

How to sort hierarchically in SQL Server using Common Table Expressions

Possible Duplicate: A real recursion with CTE? Given a hierarchical table that references itself, such as an Employee table that has the following columns: Table Employee Column Id INT NOT NULL Column ParentId INT NOT NULL (references Id) Column Name NVARCHAR(60) NOT NULL The following query will give me all records rooted at a given […]

how to merge two select queries?

I solved that query… but now I have to merge these two queries: (SELECT Zila, COUNT(AbhidayMaster.OfficeRegId) As AbhidayPaidSansthan, SUM(AbhidayMaster.TotalEmp) As TotalWorkerPaid FROM PanjikaranMaster, AbhidayMaster WHERE PanjikaranMaster.OfficeRegId=AbhidayMaster.OfficeRegId AND AbhidayMaster.DipositDate Between (‘2012-06-22’) AND (‘2012-09-19’) GROUP BY Zila) Select ((SELECT count(distinct OfficeRegId) FROM PanjikaranMaster) – (SELECT count(distinct OfficeRegId) FROM AbhidayMaster)) As AbhidayNotPaidSansthan

Data integrity in Entity Framework

Are there any consensus as to what is considered the best method in always maintaining data integrity in Entity Framework. i.e. when adding or deleting entities, how do I make sure the loaded model is the most recent, and that nothing has changed on the db behind my back? A rough method that seem to […]

SQL server : clearing cache has no effect on IO stats

Query used: (had copied the cache cleanup code from somewhere online) CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO DBCC DROPCLEANBUFFERS GO SET STATISTICS TIME ON; SET STATISTICS IO ON; select a.a,a.b from t1 a JOIN t1 b on a.i=b.i t1 ‘s schema is CREATE TABLE [dbo].[t1]( [i] [int] NULL, [a] [varchar](1000) NULL, [b] [varchar](1000) NULL, [c] [varchar](1000) […]

SQL case when using IN clause, clean way?

Hi all I am trying to use an IN clause base on a condition. I don’t want 2 separate SQL statements and was hoping for an inline solution. I want to say, if the string is blank then don’t apply the IN query, however if it isn’t then use it. I know I can do […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.