You can use a deadlock graph and gather the information you require from the log file. The only other way I could suggest is digging through the information by using EXEC SP_LOCK (Soon to be deprecated), EXEC SP_WHO2 or the sys.dm_tran_locks table. SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, … Read more
This checks if primary key exists, if not it is created IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND TABLE_NAME = ‘Persons’ AND TABLE_SCHEMA =’dbo’) BEGIN ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id) END ELSE BEGIN — Key exists END fiddle: http://sqlfiddle.com/#!6/e165d/2
For the field to be able to store unicode characters, you have to use the type nvarchar (or other similar like ntext, nchar). To insert the unicode characters in the database you have to send the text as unicode by using a parameter type like nvarchar / SqlDbType.NVarChar. (For completeness: if you are creating SQL … Read more
You could do it this way: — Notice how STATE got moved inside the condition: CASE WHEN STATE = 2 AND RetailerProcessType IN (1, 2) THEN ‘”AUTHORISED”‘ WHEN STATE = 1 AND RetailerProcessType = 2 THEN ‘”PENDING”‘ ELSE ‘”DECLINED”‘ END The reason you can do an AND here is that you are not checking the … Read more
I can’t see your whole query as it doesn’t seem to have posted correctly. However, I believe your problem is purely a lack of a name for your derived table / nested subquery. Give it an alias, such as MyTable in this example SELECT AVG(pageCount) FROM ( SELECT COUNT(ActionName) AS pageCount FROM tbl_22_Benchmark ) MyTable
Try this: SELECT a.*, CASE WHEN totalPayments>1 THEN ‘NO’ ELSE ‘YES’ END IsFirstPayment FROM( SELECT p.payment_id, p.user_id, ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.payment_date DESC) AS paymentNumber, SUM(1) OVER (PARTITION BY p.user_id) AS totalPayments FROM payment p ) a WHERE paymentNumber = 1
SELECT [schema] = s.name, [table] = t.name FROM sys.schemas AS s INNER JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id] WHERE EXISTS ( SELECT 1 FROM sys.identity_columns WHERE [object_id] = t.[object_id] );
What you can do is alias the selected field like this: SELECT name as [name .] FROM … The spaces and the dot will expand the column width.
INSERT dbo.DataValue(DateStamp, ItemId, Value) SELECT DateStamp, ItemId, Value FROM dbo.tmp_holding_DataValue AS t WHERE NOT EXISTS (SELECT 1 FROM dbo.DataValue AS d WHERE DateStamp = t.DateStamp AND ItemId = t.ItemId);
I think your question needs a bit of explanation. If you want to take the sums grouped by t.client you can use: SELECT t.client, SUM(t.asset) FROM the-table t GROUP BY t.client Then, if you want to take the average of this sume, just make: SELECT AVG(asset_sums) FROM ( SELECT t.client, SUM(t.asset) AS asset_sums FROM the-table … Read more