Tag: sql-server-2008-r2

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 2008: Convert Individual Time Card Entries To Calendar

I would like to create a time card view from individual time card entries in a SQL table. So the entry table looks something like: LABORID WORKORDERID STARTDATE HOURS —————————————————– 58 13 2014-07-01 00:00:00.000 3.00 58 19 2014-07-07 00:00:00.000 3.00 58 22 2014-07-17 00:00:00.000 8.00 58 34 2014-08-05 00:00:00.000 3.00 58 34 2014-08-05 00:00:00.000 5.00 […]

Is it possible to retrieve Selected Columns from Stored Procedure?

I have a stored procedure which returns a few columns from a SELECT. Now I need to grab 2 columns out of those columns in my new stored procedure and use them.. I am trying to do this using EXEC method. Is it possible to do this? Ex : Original stored procedure: CREATE PROCEDURE myBaseProcedure […]

sql select only specific fields and insert them in another table + static values

I would like to insert data from specific fields from one table in another table + some static values. Roughly I would like to do this: INSERT INTO TableA(Field1, Field2, Field3) SELECT Field1, Field2, ‘staticvalue’, Field3 FROM TableB WHERE TableB.Field6 = ‘XYZ’ Any idea how can I mix both specific fields from one table and […]

Attachment File is Invalid in SQL Server 2008 R2

Good day! I am currently creating a job that should generate an auto-email containing Claims Tagged Yesterday. I’ve done creating the job and creating the store procedure in getting the data which will be inside an Excel file. When I try to execute the job I created, it gives me an error like this: Msg […]

Aggregate totals and create data for weeks that no data exists

I have a table like such: Region Date Cases France 1-1-2014 5 Spain 2-5-2014 6 France 3-5-2014 7 … What I would like to do is run an aggregated function like so, to group the total number of cases in weeks for each region. select region, datepart(week, date) weeknbr, sum(cases) cases from <table> group by […]

How do I know if I should create an Non clustered Index on a Clustered Index or on a heap?

I have a DB containing some tables, no table has non-clustered index defined. The big application which uses this DB is slow(because the number of rows are close to a million). I want to optimize DB fetch operations by adding indexes. When I read about indexes I came across index names like: Clustered Index Non […]

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