Tag: sql-server-2008-r2

msdb.dbo.sp_send_dbmail Invalid Recipients get stripped out automatically

Looking at the documentation on http://technet.microsoft.com/en-us/library/ms190307.aspx (And some more googling) I cannot find any information about the following behaviour: I’m trying to send a database email on Microsoft SQL 2008R2 using the following command EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘Profilename’, @recipients=’valid@gmail.com; unvalid_gmailcom’, @body = ‘Testing’, @body_format = ‘HTML’, @subject = ‘Testing’; The obvious problem is that […]

How to change column name based on given month in sql server 2008R2

i want to show my pivot table column name as Monday 1,Tuesday 2 based on the month from the given date…i.e if it is July the month then Monday 1 Tuesday 2 so on for all days of the month.. this is my coding how to achieve this set @query=’SELECT P.tenant_id,CI.companyname,BM.brandname,bl.buildingname,T.Unitname,tm.tradecategoryname,l.locationname,T.adsrid,T.carpetarea,T.chargeablearea, P.[1],P.[2],P.[3],P.[4],P.[5],P.[6],P.[7],P.[8],P.[9],P.[10],P.[11],P.[12],P.[13],P.[14],P.[15],P.[16],P.[17], P.[18],P.[19],P.[20],P.[21],P.[22],P.[23],P.[24],P.[25],P.[26],P.[27],P.[28],P.[29],P.[30],P.[31] FROM ( […]

Breaking out yearly payments into monthly payments with month name in a 3 year period

I was wondering where to go from my initial idea. I used the query below to get the month beginning dates for each of the three years: DECLARE @STARTDATE DATETIME, @ENDDATE DATETIME; SELECT @STARTDATE=’2013-01-01 00:00:00.000′, @ENDDATE=’2015-12-31 00:00:00.000′; WITH [3YearDateMonth] AS ( SELECT TOP (DATEDIFF(mm,@STARTDATE,@ENDDATE) + 1) MonthDate = (DATEADD(mm,DATEDIFF(mm,0,@STARTDATE) + (ROW_NUMBER() OVER (ORDER BY (SELECT […]

RangeS-U and U locks at the same time and on the same resource

I’m working with some stored procedure and deadlock situation. I’ve been using SQL Server Profiler and get some information about the deadlock from ‘Deadlock graph’ event in form of the .xdl file. Below content of that file. I’ve just cleared some informations about the statement.The statement marked as <executionStack> in the .xdl was something like […]

Update Statements in Stored Procedure Suquentially?

Below UPDATE A iam updating facot Sqares table -iam using updatd factor from the sqare table to calulate final value in Parameter Below UPDATE A iam updating facot Sqares table Below UPDATE A iam updating facot Sqares table DECLARE @Year char(8) = ‘2017’; WITH E1 AS( SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(N) ), E3 AS( SELECT […]

Some clarification on a database design issue please, How much to use one table?

I have a “LibraryItem” which is linked to a “Library” Table: Library-<LibraryItem>-Organisation. I use the LibraryItem table for many different types of LibraryItems, and also each LibrayItem is linked to an organisation. I now realise that I need to provide personal user libraries, and I could use the LIbraryItem table to hold Personal Library Item […]

SQL Server Performance and Query Execution

Few day before it was hard time.., I have developed an Application for Online Admission process for College students and was quite successful. Let me come to the problem i faced, 2 tables were involved in the problem : Student_AdmissionDetails ( contains almost 30-35 fields and most of them were having datatype of nvarchar(70)) and […]

How to Generate row number as same as inserted without create or insert or cte

I’ve this script: Create table #temp (id int,name varchar(10),city varchar(10),sal int) Insert into #temp Select 2,’kishor’,’hyd’, 100 Union all Select 3,’kumar’,’sec’, 200 Union all Select 4,’santosh’,’kp’, 300 Union all Select 1,’sudeep’,’myp’, 300 now I want to generate row number as same as data inserted without using a create or insert or CTE or Update commands, […]

how to query for list of databases which are never backed up, and save that result into new table..?

I want to find list of databases which are never backed up, I have code to list last backup time for all databases: SELECT sdb.Name AS DatabaseName , COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101), ‘-‘) AS LastBackUpTime FROM sys.sysdatabases sdb LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.Name GROUP BY sdb.Name

Unable to create database in SQL Server 2008 R2

I have Microsoft SOL Server 2008 R2 on my server. When I am trying to open SOL Server Management Studio as administrator and creating new database, I get this error message create database permission denied in database ‘master’ error 262 Also when I am adding sysadmin in login property window section in my login. Then […]

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