Wednesday, April 17, 2013

[SQL Server 2008 issues] Data Import // MS Query // Header Missing

[SQL Server 2008 issues] Data Import // MS Query // Header Missing

Data Import // MS Query // Header Missing

Posted: 15 Apr 2013 10:21 PM PDT

Hi Guys,i wonder if anybody can assist me this is doing my head-in. I am using Query and linking it with Excel 03 to pull the result. The query works fine in Sql server but when it runs in Excel Query it removes the header for all the allias used within the query. Any suggestion why its happining. I can't manually keep writing since its a regular report.select rtrim(service) as service,sum(NumberOfPassengers)Passenger,DepartureName,ArrivalName,Company, DepartureMonthName, DepartureYearfrom table1where DepartureDate between '01-jan-2012'and getdate()and (DepartureName like 'random%'or ArrivalName like 'ramdp,%')and Company='ran'group by ServiceNumberOld,DepartureName,ArrivalName,Company,DepartureMonthName,DepartureYear,ServiceNameOld

how to find the file in user define filegroup

Posted: 16 Apr 2013 06:44 PM PDT

Hi ,when i used alter database MYDB addd file(name ='mydb_1',filename='c:ew\mydb_1.ndf,size=10mb,maxsize=200mb,filegrowth=10 mb) to filegroup FG1then file showing in FG1but when we add the another file then file going in primary filegroupe. I have already default filegroup assinged FG1.

Logic creation

Posted: 16 Apr 2013 05:51 PM PDT

Hi all,I have to write a script for a scenario but don't know how to implement it...CREATE TABLE [dbo].[GV_STNDetails]( [STNID] [int] IDENTITY(1,1) NOT NULL, [CourierName] [varchar](30) NULL, [AWBNo] [int] NULL, [DispatchedDate] [datetime] NULL, [STNNO] [varchar](20) NULL, [From_StoreCode] [int] NULL, [TO_StoreCode] [int] NULL)Above is the table structure.Let me explain the scenario:I have to generate a STN number which should be generate in this manner: StoreCode(4digit) + series of number '000001' e.g 0001000001if a store is assigning a voucher to another store e.f store1 (0001) assign some voucher to another store(0002) then STN no would be 0001000001.If a same store 0001 is assigning to another store 0003 then STN no be 0001000002If a store 0002 is assigning to store 0004 then STN be 0002000001There is AWB number which is entered by a user manually on the aplication screen. Suppose 0001 store is again assigning vocuher to store code 0002 then in this condition no new STN will be generated and in this case STN will be 0001000001. Once a AWB no gets entered for this then if again 0001 assigned some voucher to 0002 thn in this STN will be 0001000002.Hope this scenario is clear to you!!Please tell me how to accomplish this..

view more efficient than before or not?

Posted: 16 Apr 2013 10:23 AM PDT

I have a view, if we runs it individually in SSMS, it runs ok, but we have a batch process at night that uses it for each individual. so overall it makes the process slow.Now we change the view to hopefully speed it up. But I would like to compare with older version , which version runs faster. By running both of them in SSMS, I cannot see much difference, is there a way to see which view cost less time?I run it in SSMS with Execution plan, I see in : older version: Sort cost : 58%, Clustered Index scan: 41%new version: Sort 53%, Clustered index scan 47%So their total is all 100%, so how can I know which one is more efficient ?I know we probably can figure that out by running our overnight process, but it usually takes more thatn 3 hours to find out?

Strange datetime conversion issue

Posted: 16 Apr 2013 04:36 PM PDT

I have been using a query that joins several raw tables (nvarchar datatypes), has several conversions, nested queries, CASE WHEN statements. In other words it is messy, but I didn't create it nor do I have time to recreate it properly. It has been generating a very strange conversion error (the cause not the error itself is strange) where there is some kind of datetime conversion error when converting a string datatype. I have debugged every single field from every table in the query that is either converted explicitly or implicitly (these have all been updated to explicit conversions), with no problems present.The error is very strange because if I change the select fields (none of which are converting to a datetime implicitly or otherwise) to * the error message goes away, in fact if I simply add ,* after the last field in the select list it also goes away. However I can't use this because don't want every single field from every table in the query.Further, if I change the Select statement to SELECT TOP XXX where XXX is greater than the number of rows returned the error still does not occur. But if I use SELECT TOP 100 PERCENT the error occurs again.This made me think that it might be an error in the query execution cache. Which I cleared and tried again with the same errors still occurring. The database is set up to use Korean_Wansung_CI_AS on SQL Server Enterprise Edition v10.50.1600.1This problem is not critical as I can set it to use SELECT TOP XXX such that XXX is far larger than will ever be actually required. It was just very strange behavior that I had never seen before.Unfortunately, I can't provide the code or data both are quite confidential, however I'm quite sure that this is not directly related to the code itself, as the code is working using the two scenarios mentioned.Has anyone seen this behavior and/or know of the cause?

Assigning a fieldname with rollup

Posted: 16 Apr 2013 04:26 AM PDT

I have two tables that have racing data, and I need to add up the number of cancelled races and make it my aliased field name (in this case as you see the query below numCancelled, meaning number of races cancelled):(select a.eid, count(*) as numCancelled from(select ev4.eid,f.nvid, f.perfdate, f.racenumber from Finish f (nolock)join event ev4 (nolock) on ev4.eid = f.eid wheref.NoRace = 'true' and f.PerfDate = '2013-04-11') a group by a.eid with rollup)....the resultset here just lists all the eids (event ids) then the rollup total (in this case it is referred to a NULL in that eid column), but I need my numCancelled field to contain that grand total 7, as right now it comes back as 1 when I run this subquery in my stored procedure.??Z

Checkpoint Background Process?

Posted: 16 Apr 2013 12:57 AM PDT

Hi All, I'm new to SQL DBA tasks. I noticed in sp_who2 output a database is showing checkpoint as shown below. Is this process hung since it shows date of 03/08/13? I assume checkpoint is a regular automatic process.SPID Status DBName Command CPUTime DiskIO LastBatch13 BACKGROUND Micro_AB CHECKPOINT 3861851 123296 2013/03/08 10:43Thanks-Alison

Partition alignment w/ GPT partitions

Posted: 03 Mar 2011 02:41 AM PST

I'm new here about a month and have been looking through the environment to correct some performance issues. I've come across this and I'm not sure how to read this. The question is, are these partitions aligned or not? The server info is:Windows 2008 R2 Enterprise Active/ Passive clusterSQL 2008 R2 EnterpriseSAN attached for the L & M drives.The drives I am questioning are SAN attached.The drive partitions are also GPT partitions, NOT MBR.I have read that the partitions in Windows 2008 and above now come aligned correctly and the GPT(Guid Partition Table) partitions round to sector alignment, unlike MBR partitions that round to cylinder alignment. But my starting offsets still add up to a number that is not evenly divisible by 64KB. This is what I'm seeing, any help is appreciated.DISKPART> list partition Partition ### Type Size Offset ------------- ---------------- ------- ------- Partition 1 Reserved 128 MB 17 KB Partition 2 Primary 24 GB 129 MBthanksChris

List Partitioning in SQL Server 2012

Posted: 16 Apr 2013 10:05 AM PDT

Hi I got a requirement to partition the table on year, the column datatype is int and the values are like 03,04,05,06,07,08,09,10,11,12,13,14 etc.. Does SQL Server only supprts RANGE partitioning? Can someone help me in partitioning the table here? Thanks a alot in advance

Please help before 4:15 today- Thanks

Posted: 16 Apr 2013 06:44 AM PDT

I am posting the backup script to backup specific databases in subdirectories. Luckily the backup and subdirectories are creating but seperately. Can you please check the script and correct it so that backup should stores in subdirectorys.DECLARE @Baksql VARCHAR(8000)DECLARE @BackupFolder VARCHAR(100)DECLARE @BackupFile VARCHAR(100)DECLARE @BAK_PATH VARCHAR(4000)DEclare @BackupDate varchar(100)DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)INSERT INTO @DirTree(subdirectory, depth)EXEC master.sys.xp_dirtree @BAK_PATH-- Setting value of backup date and folder of the backupSET @BackupDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551SET @BackupFolder = 'D:\AdHoc_Backup\Full_Backup\' SET @Baksql = ''-- Declaring cursorDECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR SELECT NAME FROM SYS.DATABASESWHERE state_desc = 'ONLINE' -- Consider databases which are onlineAND database_id > 4 -- Exluding system databasesand NAME not in('TestAssetDB_new','TestAssetDB','Service Center','DSExAudit','Cordanterview','DSemailnder','BOEXI2','BOX2Audit','A2G','A2G_ew','Alris','Alris','AM11')and NAME not like '%Manager'-- Opening and fetching next values from sursor OPEN c_bakupFETCH NEXT FROM c_bakup INTO @BackupFileWHILE @@FETCH_STATUS = 0BEGIN SET @BAK_PATH = @BackupFolder + @BackupFileif (not exists(select 1 from @DirTree where subdirectory = @BackupFolder)) begin -- create the subdirectory EXEC master.dbo.xp_create_subdir @BAK_PATH end -- Creating dynamic script for every databases backupSET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_FullBackup_'+@BackupDate+'.bak'''-- WITH COMPRESSION, INIT, STATS = 10;' -- Executing dynamic queryPRINT (@Baksql)EXEC(@Baksql)-- Opening and fetching next values from cursorFETCH NEXT FROM c_bakup INTO @BackupFileEND-- Closing and Deallocating cursorCLOSE c_bakupDEALLOCATE c_bakup

Data file size > Max file size

Posted: 16 Apr 2013 07:33 AM PDT

Simply put, the data file size is larger than the max file size. Data file size = 105 Mb, Max file size = 100 Mb.SQL Server 2008 R2 - SP2, Full mode, compatibility level 90.We can increase the max file size, but the question lingers...how did this occur?Advice/experience appreciated, thanks.

tsql script

Posted: 16 Apr 2013 08:43 AM PDT

I want to print out the day as in numeric, but when i run this query I don't have any output for display. Can someone please help, I don't know what I am doing wrong[center] DECLARE @myVariable AS NUMERIC SELECT @myVariable = DATEDIFF(DAY,GETDATE(),convert(datetime,[myColumn],0))) from mydatabase.dbo.mytablePRINT @myVariable[/center]

performance benefit of separating LOG and DATA path

Posted: 16 Apr 2013 06:51 AM PDT

SQL Server 2008 R2 on Windows Server 2008 R2.With a Simple recovery model, is there a performance benefit to creating separate LOG and DATA paths?Thanks!

Count number of visits by week, ending on Saturday

Posted: 16 Apr 2013 01:32 AM PDT

I am trying to get a count of visits by week with the week ending on Saturday. My problem is getting the count to start on the first day of the month and end on the last day of the month. So, in the data provided, it should look like this.....I also included the code to create a Calendar table. The code for the calendar was supplied by this forum.[code="plain"]3/2/2013 3/9/2013 3/16/2013 3/23/2013 3/30/2013 3/31/201312 20 47 55 22 0[/code][code="sql"]create table Visits(MRN varchar(10),SchedProv varchar(50),ApptDt2 datetime,VisitType varchar(10))insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00044999','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00001724','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('785444','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('751229','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('759859','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('734041','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00500405','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('761328','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('786448','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('861823','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00546686','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00625883','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-01 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00360723','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('861496','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00053157','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('840426','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('803846','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00520938','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('761171','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('852069','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('328194','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00593430','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00516848','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('753541','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-07 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00108154','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00126417','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00029901','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00627830','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00562954','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('696010','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('765594','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('845976','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00104297','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00092110','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('707406','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00565956','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('743346','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00588900','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00121731','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00056537','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00378582','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00000575','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00603826','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00120478','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('844102','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('331427','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00051696','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00538642','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-14 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('771090','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('693655','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00005482','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('855161','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00559069','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('846759','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('737714','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('698712','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00585124','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('831352','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00058411','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('718979','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('802643','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('678904','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00622960','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00549645','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('857410','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('726198','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('855148','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('727006','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00092747','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00532457','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('665884','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-15 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('720278','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00510940','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('712845','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('663168','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00007244','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('826229','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00611974','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00066879','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('794126','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('087155','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00605167','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('340509','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('861855','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00588218','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00619095','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00014292','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00599619','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00521141','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('726198','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-19 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('779716','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('706899','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('862442','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00053817','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00547709','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00121795','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00537475','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00122094','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00503702','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('697260','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00074082','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('337766','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00006862','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('768686','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00547362','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00504664','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('707406','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00016247','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('837784','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-21 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00533346','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00042183','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('747814','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00025299','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00017372','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00588185','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('761328','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00068902','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('825164','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00111070','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00101789','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('680568','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00017154','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('726837','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('346376','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00078071','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00056451','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('861823','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('783502','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('805628','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00532457','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('802126','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-28 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00557398','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('841602','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00027459','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00016729','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00122094','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00013788','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00040832','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('774855','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('730596','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('844524','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00000575','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('861584','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00623818','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('796700','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('783502','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('822339','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00506708','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00619095','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-05 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('774203','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('653249','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('760872','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('724820','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00040905','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('765594','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('087155','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00582613','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00010173','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('697264','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00035143','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('804973','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','CON')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('325149','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('768446','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('855161','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('00051237','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')insert into Visits(MRN, SchedProv, ApptDt2, VisitType) values('732545','KELLEY-HEDGEPETH M.D.,ALYSON','2013-03-22 00:00:00.000','OVT')SELECT TOP 60000 IDENTITY(INT, 1, 1) AS NINTO dbo.TallyFROM sys.all_columns ac1CROSS JOIN sys.all_columns ac2CROSS JOIN sys.all_columns ac3ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100SET DATEFORMAT YMDDECLARE @Date_Start AS DATETIMEDECLARE @Date_End AS DATETIMESET @Date_Start = '2000-01-01'SET @Date_End = '2020-12-31'CREATE TABLE dbo.Calendar ( calendar_date_ID INT IDENTITY(1, 1) NOT NULL, calendar_week_ID INT, calendar_date DATETIME PRIMARY KEY CLUSTERED, calendar_year SMALLINT, calendar_month TINYINT, calendar_day TINYINT, calendar_quarter TINYINT, first_day_in_month DATETIME, last_day_in_month DATETIME, day_of_week TINYINT, week_of_year TINYINT, days_in_month TINYINT, day_of_year SMALLINT, is_weekday INT, day_name VARCHAR (10), month_name VARCHAR (10), iso_date CHAR (8), fiscal_year SMALLINT, fiscal_month TINYINT );INSERT INTO dbo.Calendar (calendar_date)SELECT t.N - 1 + @Date_StartFROM dbo.Tally tWHERE t.N - 1 + @Date_Start <= @Date_EndUPDATE dbo.CalendarSET calendar_week_ID = calendar_date_id / 7 + 1, calendar_year = Datepart (YEAR, calendar_date), fiscal_year = CASE WHEN Datepart(M, calendar_date) >= 10 THEN Datepart (YEAR, calendar_date) + 1 ELSE Datepart (YEAR, calendar_date) END, calendar_month = Datepart (MONTH, calendar_date), fiscal_month = CASE WHEN Datepart(M, calendar_date) >= 10 THEN Datepart(M, calendar_date) - 9 ELSE Datepart(M, calendar_date) + 3 END, calendar_day = Datepart (DAY, calendar_date), calendar_quarter = Datepart (QUARTER, calendar_date), first_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date), 0), last_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1, day_of_week = Datepart (WEEKDAY, calendar_date), week_of_year = Datepart (WEEK, calendar_date), day_of_year = Datepart (DAYOFYEAR, calendar_date), is_weekday = Isnull (( CASE WHEN ( ( @@DATEFIRST - 1 ) + ( Datepart (WEEKDAY, calendar_date) - 1 ) )%7 NOT IN ( 5, 6 ) THEN 1 END ), 0), day_name = Datename (WEEKDAY, calendar_date), month_name = Datename (MONTH, calendar_date), iso_date = CONVERT(CHAR(8), calendar_date, 112), days_in_month = Datepart(dd, ( Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1 ))SELECT TOP 1000 *FROM dbo.Calendar[/code]

Backup failure History....

Posted: 28 Dec 2011 11:44 AM PST

Hi Team,I need to find out the backup failure history..... Till date how many backup are failed in sql server how can I find that...please help...

What does "SET XACT_ABORT ON" mean?

Posted: 16 Apr 2013 12:56 AM PDT

I created a transaction stored procedure adding a line as Begin TranBut someone told me that need to modify asSET XACT_ABORT ON BEGIN TRANIs that true? What does "SET XACT_ABORT ON" mean?

How to select first non Null value from multiple fields

Posted: 16 Apr 2013 02:54 AM PDT

Hi,what is the best way to select fist non Null value from mutliple fiels K1,.... K3, I heard about case, but still can't figure out how to use single case fro multiple columns?Appreciate your help.Mario[code="plain"]select * from T01-- source table::::PK K1 K2 K32 Bravo 3 Charlie1 Alpha 48 Whiskey --wanted output:select PK, case (.....) from T01PK K1 Alpha2 Bravo3 Charlie48 Whiskey[/code]

Considering Moving to SQL Server

Posted: 16 Apr 2013 02:55 AM PDT

My company currently uses Access 2007 and wants me to look at moving to SQL Server 2008/2012. My question is what all is needed in order to move our current database structure from Access 2007 to SQL Server 2008/2012? My question is geared towards a hardware and software perspective.

Searching Database tables for a specific value within a database field

Posted: 15 Apr 2013 11:51 PM PDT

Hi,I'm new to SQL Server and I need to find out if the value 'Criminal' is stored in any of the Column/Fields within the database. Is there a way to do this with a Select statement? This is as far as I have gotten but this Select will only give me columns name Criminal not the value within the column name.select colmun_name, table_name from Information_Schema.columns where column_name has 'Criminal' in it.Thanks Much, Jim

two jobs runnig at a time

Posted: 15 Apr 2013 11:30 PM PDT

hi in my environment one job is running at 9 pmc, i want to run the another job at same time 9 pm i want to run the two jobs at a time in sql server agent, is it will effect to any jobs, or is it going to any business impact. please let me know

Capturing Blocking info

Posted: 15 Apr 2013 09:43 PM PDT

I am trying to capture blocking info with the teqnique detailed below. The issue i am having is that if i run the TSQL Listed at the bottom of this post to capture the blocking info then it works ok when i manually run from ssms but when i try to automate it, it runs (i cheked the job history) but i dont see any output ?I have created a table as detailed below:CREATE TABLE dbo.t1 (c1 INT, (c2 INT, (c3 DATETIME);INSERT INTO dbo.t1VALUES (11, 12, GETDATE());INSERT INTO dbo.t1VALUES (21, 22 GETDATE());I then create a blocking situation using the 2 queries below:BEGIN TRAN User1UPDATE dbo.t1SET c3 = GETDATE();Then i run query 2:BEGIN TRAN User2SELECT c2 FROM dbo.t1 WHERE c1 = 11;COMMITThe alert condition i have created fires a job to capture the blocking info:The TSQL is listed below:SELECT tl.request_session_id AS WaitingSessionID ,wt.blocking_session_id AS BlockingSessionID ,wt.resource_description ,wt.wait_type ,wt.wait_duration_ms ,DB_NAME(tl.resource_database_id) AS DatabaseName ,tl.resource_associated_entity_id AS WaitingAssociatedEntity ,tl.resource_type AS WaitingResourceType ,tl.request_type AS WaitingRequestType ,wrt.[text] AS WaitingTSql ,btl.request_type BlockingRequestType ,brt.[text] AS BlockingTsqlFROM sys.dm_tran_locks tl JOIN sys.dm_os_waiting_tasks wt ON tl.lock_owner_address = wt.resource_address JOIN sys.dm_exec_requests wr ON wr.session_id = tl.request_session_id CROSS APPLY sys.dm_exec_sql_text(wr.sql_handle) AS wrt LEFT JOIN sys.dm_exec_requests br ON br.session_id = wt.blocking_session_id OUTER APPLY sys.dm_exec_sql_text(br.sql_handle) AS brt LEFT JOIN sys.dm_tran_locks AS btl ON br.session_id = btl.request_session_idwhy does this work ok when i manually run it (ie i get to see the blocked process info) but doesnt work when i run it via an alert/sql job ( as i said it does run it is just i cant see any output)This is probably something really basic so i hope somebody can help.ThanksSteven
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog