Tag: sql-server-2008-r2

How to write into a table without locking other transactions

I have a central logging table, where various activities are logged. There is a procedure used for writing into the log, like this: create procedure [client].[procClient_LogText] ( @LogText nvarchar(4000), @LogLevel tinyint = 1, @LogLongText nvarchar(max) = NULL, @LogModule nvarchar(200) = NULL ) as insert into dbo.tblLog(LogLevel, LogText, LogLongText, LogModule) values (@LogLevel, @LogText, @LogLongText, @LogModule); Now […]

Query to find records following same sequence of patterns?

How to find records which matches certain sequences from the below table: ID s_id task 1 1 a 1 2 b 1 3 b 1 4 c 1 5 c 1 6 d 1 7 a 2 1 a 2 2 c 2 3 c 3 1 a 3 2 b 3 3 c 3 […]

Purge job optimization

SQL Server 2008 R2 Enterprise I have a database with 3 tables that I am keeping a retention time of 15 days. This is a logging database that is very active and about 500 GB in size and eats about 30GB a day unless purged. I can’t seem to get caught up on one of […]

TOP clause slowing down query

I have 2 queries: one with a TOP clause and one without. The result is the exact same yet the one with the TOP clause is significantly slower. Why would that be? Server: Microsoft SQL Server 2008 R2 (SP2) Query 1 – Regular insert into #Buffer (details, persistentID, productID, [date]) select de.details, lg.databaseID % 1000, […]

How to EXECUTE dynamic query in WITH clause in SQL Server

Here is the my sample query, Can any one suggest me how to EXECUTE dynamic query in WITH clause. DECLARE @V_TBLID NVARCHAR(MAX) = 1, @V_QUERY NVARCHAR(MAX) = ”, @IN_PAGESIZE INT = 5, @IN_PAGEINDEX INT = 1; SET @V_QUERY = N’SELECT * FROM MYTABLE’+ @V_TBLID; WITH RESULTS AS ( EXEC(@V_QUERY) ) SELECT * FROM RESULTS WHERE […]

Replace function in SQL Server 2014 adds trailing zeros in Char field

I am upgrading from SQL Server 2008 R2 to SQL Server 2014. I have a SQL task in one of my SSIS packages which produces different results in the two different SQL versions. This is the SQL I am running in both versions. The column MfgPartNbr is a CHAR(50) column. Select REPLACE(MfgPartNbr, ‘ ‘, ‘0’) […]

MS Access passthrough select query causing page lock in SQL Server

I’m working with an Access 2007 frontend linked to an SQL Server 2008 R2 backend and have encountered what I think is strange locking behavior caused by even simple passthrough select queries. More concretely: I have a passthrough query of the simple form “SELECT * FROM tbl_A”. If I open this query in access (normal […]

Programmatically Insert Rows Into Table

I am trying to write a procedure that will check all rows in my table and if itemID = ‘CC100’ then I need to insert a new row into the table. I have generated this script, but I am getting an error when I try to execute it Msg 156, Level 15, State 1, Line […]

SQL Server – Drop constraint and add default value at once

I need to alter ‘ID’ column of my table ‘MyTable’ in order to remove a constraint but also to add a default value so I do: To drop constraint I do: ALTER TABLE MyTable DROP CONSTRAINT [ID] To add default value to column: ALTER TABLE MyTable ADD DEFAULT 0 FOR ID Is it possible to […]

Get Min In time and Max out TIme. Fom Attendance record in Sql Server

I am working on attendance system, and below is my data in sql server database |EmpCode|Date |WorkDate |CheckIn |CheckOut |TotalTime |143 |2017-02-13 |2017-02-13 |20:09:02 |22:38:50 |2.496666000 |143 |2017-02-13 |2017-02-13 |22:59:19 |23:18:15 |0.315555000 |143 |2017-02-13 |2017-02-13 |23:33:47 |05:04:24 |5.510277000 |143 |2017-02-14 |2017-02-13 |02:52:38 |05:12:04 |2.323888000 |143 |2017-02-14 |2017-02-14 |20:09:26 |21:59:27 |1.833611000 |143 |2017-02-14 |2017-02-14 |22:30:10 |22:49:26 […]

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