Tag: sql-server-2008-r2

How to use WHERE clause with a PIVOT

Why, do my where clauses here get ignored, it’s summing invoices regardless of the valued entered as parameters, spent hours on this and going mad! ALTER PROCEDURE [dbo].[Usp_custom_dash_metric_invoicespend] @CompanyGUID UNIQUEIDENTIFIER, @UserGUID UNIQUEIDENTIFIER, @CompanyUserGUID UNIQUEIDENTIFIER, @InvoiceYearFrom CHAR(4), @InvoiceYearTo CHAR(4), @CompanySupplierGUID UNIQUEIDENTIFIER AS BEGIN WITH _d AS (SELECT a.totalgrossvaluehome1 AS tgvh1, invoicedate AS ind, c.guid AS cug, […]

Table Check Constraint allows invalid data

When I create a check constraint using the test script below, then data which violates the constraint is still allowed into the table, and the constraint is still shown as trusted. I realize that the check constraint does not check for NULLs correctly (it includes column = null instead of column IS null), but I […]

Enable TCP connection for SQL Server for specific users only

Can I enable TCP connection in SQL server 2008 R2 to only specific users, or at the other side, can I prevent some users from connecting to SQL Server remotely using TCP?

Update unique/duplicate Data Records using single Stored Procedure in SQL Server 2008 R3

I have a table FileInfo with below columns **(SID int) Primary Key & AutoIncrement, FileName varchar, FilePath varchar, fileExtn varchar, CreatedDate smalldatetime, Duplicate varchar, LatestCopy varchar.** the records in this are like shown below 1, 02-2511-510.pdf, C:\Desktop\02-2511-510.pdf, .pdf, 2000-10-12 00:00:00, NULL, NULL 2, 04-4648-500.doc, C:\Desktop\04-4648-500.doc, .doc, 2010-10-01 00:00:00, NULL, NULL 3, 02-2511-510.pdf, D:\Users\ers\02-2511-510.pdf, .pdf, 2000-11-13 […]

SQL Query Optimizer full exploration

According to Connor Cunningham and Kimberly Tripp {Referenced from sql server 2008 r2 internalls book}. There are 3 stages to plan exploration. Stage 0,1,2. In stage 0 the query is assigned a trival plan and in stage 2 sql server does a full exploration of the plan. How can I force sql server to do […]

xp_cmdshell dir different results when passing command as variable

I am creating a stored procedure to track some server’s folders’ used space using SQL Server 2008 R2. I have run into an interesting issue for a particular directory. When I run EXEC master.dbo.xp_cmdshell ‘dir “\\servername\e$\media\Google” /s /-C’ I get a result: However, when I use a variable for the dos command DECLARE @dir VARCHAR(255) […]

T-sql for checking SQL Server EDITION can compress backup

How can you check, through t-sql, if your SQL-server edition (not version) supports compressing backups through WITH COMPRESSION ? Also, if only possible to do this through checking your edition somehow (like this: https://stackoverflow.com/a/1658724/1655580), how re-usable is such an approach if developing against SQL Server 2008 R2 and then comparing with later versions? What I’m […]

Joining Tables and Concatenating Fields

Using MS-SQL 2008r2, I have two tables, with multiple secondary ID’s which I need to join into a single view (for export) which has a single row per secondary ID and the table data concatenated into a single field per table. tbl-1 id | adid | ImImage1 | ImName2 1 | 1 | Im_a | […]

Linq to Sql isnull in where clause not working

I have Linq query as: var notstartedcount = (from te in context.EH_PP_TeacherEvaluations join tesrt in context.EH_PP_TeacherEvaluationStatusesRefTables on te.EH_PP_TESRT_TeacherEvalStatusID equals tesrt.EH_PP_TESRT_TeacherEvalStatusID where tesrt.EH_PP_TESRT_TeacherEvalStatusID.Equals(“1”) || tesrt.EH_PP_TESRT_TeacherEvalStatusID.Equals(“”) select te).Count(); I just wanted to put Isnull(ColumnName,replacement) in where condition as we do it in Sql. Eg.: where isNull(EH_PP_TESRT_TeacherEvalStatusID,0)=1 I kept same condition for linq as: var notstartedcount = (from […]

A Tale of Two Schemas: One Deadlocks, One Does Not

Background I have an application that periodically receives large amounts of data from an external source (happens to be an XML file) and inserts that data into a database. During this operation, nothing else is accessing the database. Since there is a large amount of data, I use a configurable number of threads to perform […]

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