SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN DECLARE @res INT EXEC @res = sp_getapplock @Resource = 'This a Lock ID 3', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 60000, @DbPrincipal = 'public' if @res < 0 begin declare @errorMessage nvarchar(200) set @errorMessage = case @r...
Sorry about the last message. I did a paste over my question. Long question short, when using sp_GetAppLock inside of a try / catch block, should I call sp_ReleaseAppLock when an exception is caught? SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN DECLARE @res INT EXEC @res = sp_getapplock @Resource = 'This ...
<game xmlns="http://my.name.space" ></game> This is my root element. I've written a stored procedure to insert elements into it. To sum up the stored procedure, here's the SQL UPDATE ChessGame SET GameHistory.modify('insert <move><player>black</player><piece>pawn</piece><start>E7</start><end>E6</end></move> as last into (/game)[0]') W...
How could i create a trigger that at any insertion on my table [users] will change automatically the content of its [password] field to its MD5 hash? Ps: I do not want this being done at client side. ...
I have heard that DateTime.Now is very expensive call (from here) Is GETDATE() in SQL 2005/2008 expensive? Have I to cache it into a variable if my stored procedure uses it a number of times? ...
I created few user defined types in DB as below CREATE TYPE [dbo].[StringID] FROM [nvarchar](20) NOT NULL and assigned to various tables. My tables in db are in various schemas (not only dbo) But I realized I need bigger field, and I need to alter, e.g increase from nvarchar to nvarchar, but there is no ALTER TYPE statement I need a...
According to MSDN BOL (Books Online) description on SOME | ANY (Transact-SQL), SOME and ANY are equivalent. It does make sense to use either SOME | ANY to make a query more readable. But is that the only reason why there are 2 keywords in TSQL where they serve the exactly the same purpose? Are there any historic reasons why they ...
Using SQL Server 2000 My Query. SELECT (Format(IIf(CLng(OutTime) > 180000, CDate('18:00:00'), CDate(Format(OutTime, '00:00:00'))) - IIf(CLng(InTime) < 90000, CDate('09:00:00'), CDate(Format(InTime, '00:00:00'))), 'hh:nn:ss')) As WorkTime, (Format(IIf(CLng(InTime) < 90000, CDate('09:00:00') - CDate(Format(InTime, '00:00:00')), ...
When I select a series of rows from a sql server 2005 table using an "order by" clause and then insert them into a different (and empty) sql server 2005 table can I count on the rows staying in the same order. The reason I am asking is that I want to manipulate the rows using ADO.net. I want the rows in a specific order to improve th...
In SQL Server you can use an XML datatype and map it to relational columns using an AXSD schema. Mapping between XML and relational storage By using an annotated schema (AXSD), the XML is decomposed into columns in one or more tables. This preserves fidelity of the data at the relational level. As a result, the hierarchi...
Using SQL Server 2000 Table PersonID Date 001 11-02-2009 002 11-02-2009 003 11-02-2009 001 12-02-2009 004 12-02-2009 005 12-02-2009 003 13-02-2009 005 13-02-2009 So on…, I want to display all the Personid by Date wise. Expected Output PersonID Date 001 11-02-2009 002 11-02-2009 003 11-02-2009 004 11-02-2009 005 11-02-2009 001 ...
Is it a good idea to index varchar columns only used in LIKE opertations? From what I can read from query analytics I get from the following query: SELECT * FROM ClientUsers WHERE Email LIKE '%niels@bosmainter%' I get an "Estimated subtree cost" of 0.38 without any index and 0.14 with an index. Is this a good metric to use for anlayzi...
How do I alter a primary clustered index to become a non-clustured index. (Being a "secondary" table I want to use the clustured index for the foreign key column of the "header" table.) This doen't work for me (error seems reasonable :) DROP INDEX ClientUsers.PK_ClientUsers CREATE UNIQUE CLUSTERED INDEX IDX_ClientUsers_Id ON ClientUser...
Hi, I have sql server procedure, please see below. ALTER PROCEDURE [dbo].[uspInsertDelegate] ( @CourseID int, @CPUserID int, @StatusID int, @CreateUser varchar(25) ) AS SET NOCOUNT OFF; INSERT INTO tblDelegate ( CourseID, CPUserID, StatusID, CreateUser ) VALUES ( ...
I have a problem regarding SQL batches in SQL Server. Assume I execute a SqlCommand using the following code: private void Example(SqlConnection connection, SqlTransaction transaction) { using (SqlCommand cmd = new SqlCommand("select * from T1; EXECUTE('update T2 set 1=2')", connection, transaction)) { SqlDataReader ...
I am seeing some odd behavior in SQL Server AVG calcuation. On manual calculation, you get 49.277588 but SQL Server is reporting that the average is 50.9914 as shown below. Question: Can someone explain the difference and why this is happening? You can try out the query on AdventureWorks2008 Database with following query select C.P...
I use SQL Server 2000. Suppose I have two tables like the following: Area ---------------------------------- ID| Name | HierarchyLevel ---------------------------------- 1 | World | 1 2 | America| 2 3 | Europe | 2 4 | Africa | 2 5 | USA | 3 and AreaHierarchy ------------------------ ID | ParentID | ChildID...
According to BOL, "SNAPSHOT Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction" However, that is not exactly what I am observing. Consider this: In the first tab, run this: CREATE TABLE dbo.TestTable(i INT); GO ...
Situation: c#, sql 2000 I have a table, lets call it 'mytable' with 30 million rows. The primary key is made up of fields A and B: A char(16) B smallint(2) When i do a search like this, it runs really slowly (eg it does a full tablescan) string a="a"; int b=1; string sql = "select * from table(nolock) where a=@a and b=@b"; using (Sq...
Does anyone have any suggestions about how to schedule the creation of reports and how to allow users to view them in a SQL Server 2005 Work Group environment where scheduling and subscriptions do not appear to be supported. SQL Server is installed on a separate machine to that running IIS to support multiple production websites. ...