[MS SQL Server] Track asp pages that are deleting records from a table

---

• Track asp pages that are deleting records from a table
• Server doesn't accept connections from the web!
• Log File Shrink

Track asp pages that are deleting records from a table Posted: 12 Apr 2013 04:42 AM PDT We have a website that has thousands of asp and asp.net pages. One of the pages has been written to do a deletion of records on a specific table. We know the table the records are being deleted from, the stored procedure that is doing the deletion, but don't know the asp page calling the stored procedure.Is there a way of doing a trace or change tracking (for the specific table) or delete trigger that captures the asp page name that is calling the deletion?

Server doesn't accept connections from the web! Posted: 27 Mar 2013 02:09 PM PDT I am making a VB.Net app that accesses my SQL Server, but when I try to run the app from a remote computer, I get an error that it can't find the server.It works fine if I access it from the local network using the local IP, but outside, using the public IP, it doesn't work!I checked the Allow Remote Connection options, but that didn't work eitherAlso, I tried to put my server as DMZ host on my router, didn't work.Additionally, I tried to disable windows firewall, no results.Environment: Windows Server 2012, SQL Server 2012Maxwell.

Log File Shrink Posted: 15 Mar 2013 05:42 AM PDT Now that we got the log file backups under control, now it's time to shrink the log file. I'm going to start with the Test DB which is only 56 gig, and when they (consultants) created the log file initialy, they set the log file size to 59 gig. See below the results of DBCC SQLPERF(logspace). It looks as though I can really shrink this sucker. What do you think would be an appropriate size, remembering this is a test DB with not a lot of transactions going through. [img]http://i1359.photobucket.com/albums/q791/midnight251/LogFileSize_zps62bd6b1a.jpg[/img]

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL 2012] Sql Server 2012 can't see 2nd drive

---

• Sql Server 2012 can't see 2nd drive
• Event ID: 7000 The SQL Server...service failed to start due to the following error: Access is denied.
• Report Format via SQL Server 2012
• Columnstore Indexing - There is insufficient system memory in resource pool 'default' to run this query.

Sql Server 2012 can't see 2nd drive Posted: 14 May 2012 02:33 PM PDT I installed Sql Server 2012 Express. Everything went without a hitch until I attempted to create a new database and place it on a second drive (E:). The location window displays C: and an external drive (Z:), but not the E: drive where I wish to place the database. Permissions show full control, but Sql Server won't display it.

Event ID: 7000 The SQL Server...service failed to start due to the following error: Access is denied. Posted: 12 Apr 2013 02:18 AM PDT Yesterday I worked through a new install of SQLServer 2012 with SP1 on Microsoft Windows Server 2012 Standard on a VMware virtual machine. Once complete I noticed the services were stopped and there were errors in the OS system log:[code]Log Name: SystemSource: Service Control ManagerDate: 4/11/2013 12:50:41 PMEvent ID: 7000Task Category: NoneLevel: ErrorKeywords: ClassicUser: N/AComputer: ...Description:The SQL Server (MSSQLSERVER) service failed to start due to the following error: Access is denied.[/code]I wrote a blog on the solution here:[url]http://jonmorisissqlblog.blogspot.com/2013/04/event-id-7000-sql-serverservice-failed.html[/url]

Report Format via SQL Server 2012 Posted: 12 Apr 2013 01:29 AM PDT HiPlease could somone advice which is the best method for creating a report from SQL that will have 1 desktop machine per page wiith all the software that has been installed.All the software and desktop information are held within SQL SEVER 2012 database.Projects Team needs to ensure when pc is refreshed the same licensed software goes back on.ThanksClive

Columnstore Indexing - There is insufficient system memory in resource pool 'default' to run this query. Posted: 11 Apr 2013 04:55 AM PDT disregard for now, hopeful that I found what I was looking for

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] Stuck on querying 3 tables where I'm looking for non-matches

---

• Stuck on querying 3 tables where I'm looking for non-matches
• Obtaining earliest change for one colum?
• Begginer Sub Query problem
• Add prefix to all table name
• Question about query hint nolock
• Format SQL (Pro)
• Extract Data From A String in SQL Server
• Multiple DMLS in one when clause of MERGE Statement
• CTE - UNION ALL - GROUP BY
• Problems with IsNumeric and PatIndex when trying to convert string to Int/BigInt
• Needed Urgent help please with a TSQL code for report
• CTE - Temp table - CROSS APPLY
• How To Validate in Procedure
• sql trigger problem

Stuck on querying 3 tables where I'm looking for non-matches Posted: 11 Apr 2013 07:41 AM PDT I have three tables: LitHold, LitHoldDetails and EmailTemplate. The definitions are as follows.[quote]CREATE TABLE [dbo].[LitHold]( [LitholdID] [int] IDENTITY(1,1) NOT NULL, [LitHoldStatusID] [tinyint] NOT NULL, [EmailReminderID] [tinyint] NULL, [ApprovedDate] [datetime] NULL, [TerminatedDate] [datetime] NULL, CONSTRAINT [PK_Lithold] PRIMARY KEY CLUSTERED ( [LitholdID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY][/quote][quote]CREATE TABLE [dbo].[LitHoldDetails]( [LitHoldDetailsID] [int] IDENTITY(1,1) NOT NULL, [LitholdID] [int] NOT NULL, [VersionID] [int] NOT NULL, [Description] [varchar](300) NULL, [ResAttorneyID] [varchar](10) NOT NULL, [Comments] [varchar](1000) NULL, [HoldStartDate] [datetime] NULL, [HoldEndDate] [datetime] NULL, [CreatedDate] [datetime] NOT NULL, [CreatedByLogin] [varchar](10) NULL, CONSTRAINT [PK_LitholdDetails] PRIMARY KEY CLUSTERED ( [LitHoldDetailsID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY][/quote][quote]CREATE TABLE [dbo].[EmailTemplate]( [TemplateID] [int] IDENTITY(1,1) NOT NULL, [LitHoldDetailsID] [int] NOT NULL, [From] [varchar](50) NULL, [To] [varchar](2000) NULL, [CC] [varchar](500) NULL, [BCC] [varchar](500) NULL, [Subject] [nvarchar](200) NULL, [MessageBody] [nvarchar](max) NULL, [SendDate] [datetime] NULL, [IsDefault] [bit] NOT NULL, CONSTRAINT [PK_EmailTemplate] PRIMARY KEY CLUSTERED ( [TemplateID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY][/quote]For each LitHold, there can be multiple LitHoldDetails. For each LitHoldDetail, there should be one EmailTemplate. I recently found that some LitHoldDetails do NOT have EmailTemplates. We're still working in development on this project, so this isn't a big deal. However, we want to get the EmailTemplate data into the database. The situation is that for each LitHold, there is at least one LitHoldDetail that has an EmailTemplate. I would like to duplicate this EmailTemplate data for all the LitHoldDetails that a) have the same LitHoldID and b) do not have an EmailTemplate. One of the approaches I've tried is:[quote]insert into EmailTemplate(LitHoldDetailsID, [From], [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault)(select (select LitHoldDetailsID from LitHoldDetails where LitholdID = d.LitholdID and LitHoldDetailsID <> e.LitHoldDetailsID), [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault from EmailTemplate e inner join LitHoldDetails d on e.LitHoldDetailsID = d.LitHoldDetailsID)[/quote]but this gets me the error message "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." How can I accomplish this?

Obtaining earliest change for one colum? Posted: 11 Apr 2013 03:07 AM PDT Guys,I've been looking at this problem on and off for a while and I can't for the life of me find a solution. Basically we have an audit system where by every time someone changes one field in one table it appends the entire row into another table, a slight simplification but you get the idea.What I want to get is a historical record for one field over time - so say this was a list of individuals and it had their wealth in dollars, their job title, their address, their age etc. It may be that their wealth in dollars didn't change over the course of say 10 rows in the table but other variables did change, I'm trying to build up a picture of their wealth changing. Totally random example but I thought it might help!With this test data:[quote]SELECT * INTO #testFROM(SELECT 1 as Audit_ID, 123 as EntID, 100 as Field_A, 10 as Field_B, '01-Jan-12' as ActionDate UNIONSELECT 2 as Audit_ID, 123 as EntID, 110 as Field_A, 10 as Field_B, '15-Feb-12' as ActionDate UNIONSELECT 3 as Audit_ID, 123 as EntID, 150 as Field_A, 15 as Field_B, '20-Mar-12' as ActionDate UNIONSELECT 4 as Audit_ID, 123 as EntID, 100 as Field_A, 10 as Field_B, '12-Jun-12' as ActionDate UNIONSELECT 5 as Audit_ID, 445 as EntID, 50 as Field_A, 4 as Field_B, '02-Apr-12' as ActionDate UNIONSELECT 6 as Audit_ID, 445 as EntID, 60 as Field_A, 3 as Field_B, '15-May-12' as ActionDate UNIONSELECT 7 as Audit_ID, 445 as EntID, 30 as Field_A, 3 as Field_B, '18-Aug-12' as ActionDate UNIONSELECT 8 as Audit_ID, 552 as EntID, 500 as Field_A, 1 as Field_B, '10-Jan-12' as ActionDate UNIONSELECT 9 as Audit_ID, 552 as EntID, 450 as Field_A, 3 as Field_B, '15-Mar-12' as ActionDate UNIONSELECT 10 as Audit_ID, 552 as EntID, 320 as Field_A, 4 as Field_B, '29-Aug-12' as ActionDate)x[/quote]Say I was trying to track field B over time for Entity (EntID) 123, I'd want a results set like this:01-Jan-12 1020-Mar-12 1512-Jun-12 10I thought I could do some kind of ranking whereby I partitioned on the field I was interested on and ranked on date, and chose those with rank 1, however, this doesn't take account for the same value at different time points - e.g. using the wealth example perhaps in Jan 2010 I had $200, in Feb 2010 this went up to 350 but then it fell to $200 again in September 2010, with my ranking idea I'd just have Feb 2010 and 200, not September too.I thought about some form of 'double join' type thing along hte lines of Field B <> Field B, I've also got pratically no experience of them but for some reason I'm wondering if a CTE is called for?!Hoepfully I've explained this not too bad and my test data gives you kind of an idea! Thanks for reading :)

Begginer Sub Query problem Posted: 11 Apr 2013 04:30 AM PDT //EDIT: I've posted in the wrong board, I should have posted in the General Board, ut I don't know how to move this!I'm fairly green with SQL (am still learning on the job!) so please accept my apologies if this both a confused question as well as a very basic question.I have a table that I'll simplify to have justthe following columns: CompanyName (string), CompanyID (Guid), Heirarchy (int), ParentCompanyID (Guid)The ParentCompanyID I suppose is self-referential in that if the Company is a sub-division of a parent company it will have say an Heirarchy =1 and the CompanyID of the Parent Company held in the same Table.I have been trying to write a Select stmt to show the Parent CompanyName instead of the ParentCompanyID:Select CompanyName, Heirarchy, [substitute ParentCompanyID for Parent CompanyName]From CompanyOrder by CompanyNameIf I use a simple Where clause of Where ParentCompanyID = CompanyID I land up filtering down to only companies that have SubDivisions. This has occupied my entire afternoon and I have gotten nowhere so am turning to the forums in the hope I can be put of my misery!Hopefully someone could point me in the right direction, unfortunately I can't change the schema as it is form the company's CRM system.

Add prefix to all table name Posted: 11 Apr 2013 07:56 AM PDT Hi,I would like add same prefix to all table names.For Example:TableATableBResult Needed:aaa_TableAaaa_TableBI would appreciate some input.Thanks in advance !!!

Question about query hint nolock Posted: 11 Apr 2013 11:00 PM PDT Hi people,I have a question about nolock. I work in a company that there is a rule: all select query we must put nolock. If we don't put it the query execution becomes slowly. don't sqlserver get manage it transactions?Is there any configuration or parameter to avoid using nolock all time when I run a select statement?Best regards.

Format SQL (Pro) Posted: 11 May 2012 04:07 AM PDT I tried Format SQL (Pro) and I really liked it.Unfortunately I will probably not be able to get it funded.There are some Free SQL Formatting Tools.Has anyone used any of them and if so what is your preferred tool?

Extract Data From A String in SQL Server Posted: 11 Apr 2013 04:16 PM PDT Hi All,I have a String as in put, I need to Extract the Data in teh String.Delimiter for the String is "[" String: -1 * [SAL] + [COMM] - [BON]Please need help...

Multiple DMLS in one when clause of MERGE Statement Posted: 11 Apr 2013 07:50 PM PDT Hi All,I want to use 2 DMLS in When not Matched by target clause ie. first insert and then update.But I am getting problem while executing this code .Please provide a better way to do it.MY CODE :[size="4"][i]Merge Vacancy.TB_FTSearchData as A using vacancy.TB_Vacancy as B on A.FTSearchVacancyInternalID=B.VacancyInternalID and A.FTSearchContractID=B.VacancyContractID When not Matched by source then Delete When not Matched by target and B.VacancyFTSupdated=1 and B.VacancyStatusID=1 then INSERT ([FTSearchVacancyInternalID] ,[FTSearchVacancyTitle] ,[FTSearchVacancyCleanDescription] ,[FTSearchVacancyCategoryName] ,[FTSearchVacancyTagName] ,[FTSearchContractID] ,[FTSearchVacancyLCID] ,[CreatedDate] ,[ModifiedDate]) Values (VacancyInternalID, VacancyTitle, VacancyCleanDescription, dbo.fn_GetCSV(B.VacancyInternalID,1) , dbo.fn_GetCSV(B.VacancyInternalID,2) , VacancyContractID,(Select la.LCID from ReferenceData.TB_Language la where la.languageInternalID =B.vacancyLanguageID), GETDATE(), NULL ) UPDATE SET B.VacancyFTSupdated=3 WHERE VacancyFTSupdated=1 and VacancyStatusID=1 and VacancyInternalID in (Select FTSearchVacancyInternalID from Vacancy.TB_FTSearchData) when matched and B.VacancyFTSupdated=2 AND B.VacancyStatusID=1 then UPDATE SET A.FTSearchVacancyTitle=B.VacancyTitle ,A.FTSearchVacancyCleanDescription=VacancyCleanDescription ,A.FTSearchVacancyCategoryName=dbo.fn_GetCSV(B.VacancyInternalID,1) ,A.FTSearchVacancyTagName=dbo.fn_GetCSV(B.VacancyInternalID,2) ,[FTSearchContractID]=VacancyContractID ,[FTSearchVacancyLCID]=(Select la.LCID from ReferenceData.TB_Language la where la.languageInternalID =B.vacancyLanguageID) ,[ModifiedDate]=GETDATE() OUTPUT $action,Deleted.*,Inserted.* ; [/i][/size]

CTE - UNION ALL - GROUP BY Posted: 11 Apr 2013 04:31 PM PDT Today I got a mail from one of our developers. It is a little complicated to explain and I cannot post the DDL. So, let me post a piece of the code ('cleaned up') and explain what was going wrong.CTE definition 'cleaned up'[code="sql"];with t2 as ( select tb.product, r.category, p.date, sum(p.value) as value, ROW_NUMBER() over (partition by tb.product, r.category order by SUM(p.value)) as cnt FROM --tables )[/code][code="sql"]select t3.product, t3.category, SUM(t3.value) * -1 as Value from( select t2.product, t2.category, t2.Value * @Ratio1 as value from t2 where cnt = @Count1 union all select t2.product, t2.category, t2.Value * @Ratio2 as value from t2 where cnt = @Count2) as t3--where t3.category = 'ABC'group by t3.product, t3.categoryorder by t3.product[/code]Here t2 is a CTE which does an aggregation (SUM) and a row_number() generation. When the developer ran this query, he was getting incorrect output for column Value. When he added the filter by category, he got the correct output.My analysis:I removed the GROUP BY and ran the query, got the correct values for the column value (say Val1 and Val2). When I added the GROUP BY, the output was wrong, i.e. SUM(t3.value) was not showing Val1 + Val2.I got a hunch that this had something to do with UNION ALL on CTE, so I modified the query as below to eliminate the UNION ALL op.[code="sql"]select t3.product, t3.category, SUM(t3.value) * -1 as Value from( select t2.product, t2.category, CASE WHEN cnt = @Count1 THEN t2.value * @Ratio1 WHEN cnt = @Count2 THEN t2.value * @Ratio2 END as value from t2 where cnt = @Count1 OR cnt = @Count2 ) as t3--where t3.category = 'ABC'group by t3.product, t3.categoryorder by t3.product[/code]This worked just fine, gave the expected output. What I would like to know is what could have caused a wrong aggregation when the query had a UNION ALL. I know the CTE gets evaluated twice but the CTE is deterministic. Any help here would be really great. I will post the CTE definition below.

Problems with IsNumeric and PatIndex when trying to convert string to Int/BigInt Posted: 11 Apr 2013 09:56 AM PDT Can someone help me understand why this is happening:If I put the following in a where clause: CASE WHEN PATINDEX('%[^0-9 ]%',h.oe_po_no) = 0 THEN Cast(RTrim(LTrim(h.oe_po_no)) as BigInt) ELSE 0 END = 0I get the following error: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to bigint.I have seen the same situation when using IsNumeric instead of PATINDEX. Why doesn't T-SQL truly recognize numeric strings and allow them to be converted? This is so frustrating.Thank you.Tammy

Needed Urgent help please with a TSQL code for report Posted: 11 Apr 2013 08:36 AM PDT Dear friends,i have a dead line to do the following requirement for 2 fields-1) Age (Working Days) "a) Don't display this if the job posting is in Closed statusb) If the job posting is in Halted status, only display this data if there is no corresponding work order.Calculate as the number of working days from the Job Posting Create Date until the day prior to the Report Run Date, inclusive.For example, if the Job Posting Create Date is March 18, 2013 and the Report Run Date is April 8, the age would be 15 days. Working days should exclude weekends."Age Group (Working Days) "a) Don't display this if the job posting is in Closed statusb) If the job posting is in Halted status, only display this data if there is no corresponding work order.2) Based on the Age (Working Days), display one of these selections: 0 - 2930 - 5960 - 8990 - 119120 +In the example display ""0 - 29""."for the 1st field I have the code -(datediff(dd,convert(int,[WorkForce_JobPosting].[Job Posting Create Date_JP]),convert(int,[WorkForce_JobPosting].[Report Run Date_BYR])+1))- (datediff(WK,convert(int,[WorkForce_JobPosting].[Job Posting Create Date_JP]),convert(int,[WorkForce_JobPosting].[Report Run Date_BYR]))*2)- (CASE when DATENAME(DW,convert(int,[WorkForce_JobPosting].[Report Run Date_BYR])) = 'Sunday' then 1 else 0 END)- (CASE when DATENAME(DW,convert(int,[WorkForce_JobPosting].[Job Posting Create Date_JP])) = 'Saturday' then 1 else 0 END)but not sure how to implement it conditionally and need help with the second field to display range based on the output of this field.thanksDhananjay

CTE - Temp table - CROSS APPLY Posted: 03 Apr 2013 07:52 PM PDT I had some free time at work and was trying out some silly queries when I came across this.CTE referenced in a subquery does not yield any result. I didn't expect this but I can understand why it can happen. Why doesn't the parser throw an error in that case?Here are the queries:[code="sql"]--Subquery with CTE - was surprised by the output;WITH cteProcDependenciesAS(SELECTTOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]FROMINFORMATION_SCHEMA.routines rINNER JOIN INFORMATION_SCHEMA.routines rdepON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'AND r.ROUTINE_NAME != rdep.ROUTINE_NAME)SELECT pd.ROUTINE_NAME, STUFF((SELECT ', ' + enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]FROM cteProcDependencies pdGROUP BY pd.ROUTINE_NAME--Subquery With Temp tableIF OBJECT_ID('tempdb..#cteProcDependencies') IS NOT NULLDROP TABLE #cteProcDependenciesGOSELECTTOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name] INTO #cteProcDependenciesFROMINFORMATION_SCHEMA.routines rINNER JOIN INFORMATION_SCHEMA.routines rdepON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'AND r.ROUTINE_NAME != rdep.ROUTINE_NAMESELECT pd.ROUTINE_NAME, STUFF((SELECT ', ' + enc_routine_name FROM #cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME ORDER BY ROUTINE_NAME FOR XML PATH('')),1,1,'') [enc_ROUTINE_NAME]FROM #cteProcDependencies pdGROUP BY pd.ROUTINE_NAME--With cross apply, as my teammate suggested;WITH cteProcDependenciesAS(SELECTTOP 10 r.ROUTINE_NAME, rdep.ROUTINE_NAME [enc_routine_name]FROMINFORMATION_SCHEMA.routines rINNER JOIN INFORMATION_SCHEMA.routines rdepON r.ROUTINE_DEFINITION LIKE '%' + rdep.ROUTINE_NAME + '%'AND r.ROUTINE_NAME != rdep.ROUTINE_NAME)SELECT pd.ROUTINE_NAME, tab.c [enc_ROUTINE_NAME]FROM cteProcDependencies pdCROSS APPLY (SELECT enc_routine_name FROM cteProcDependencies WHERE ROUTINE_NAME = pd.ROUTINE_NAME) [Tab] (c)[/code]

How To Validate in Procedure Posted: 22 Jan 2013 04:06 PM PST I Created a one Procedure ..its given Below...[code="sql"]create procedure [dbo].[USP_Reliance_Master_Upload] as begin declare @dt datetime , @Mnth varchar(10) BEGIN TRY SELECT @dt = convert(varchar(10),convert(datetime,DCPI_Date),101) from Ram_Reliance_Master END TRY BEGIN CATCH RETURN-1 -- The Date Field is In Correct Formate so Please Check Your Data END CATCH BEGIN TRY SELECT @Mnth = Mnth from Ram_Reliance_Master where isnull(Mnth,'')!='' END TRY BEGIN CATCH RETURN-1 END CATCH -- The Date Field Should Not Be Null insert into dbo.Reliance_Master (Mnth,DCPI_Date,GRP_OUTS_RIL,Interest_Cost,OverDue_Earned,Qty,EPI,insertDt,Year) select Mnth,convert(datetime,DCPI_Date),GRP_OUTS_RIL,Interest_Cost,OverDue_Earned,Qty,EPI,cast(getdate() as DATE),DATEPART(YY,DCPI_Date) from Ram_Reliance_Master end [/code]The Existing Ram_Reliance_Master Table Data is Given Below,[code="sql"]Mnth DCPI_Date GRP_OUTS_RIL Interest_Cost OverDue_Earned Qty EPI Year ---------- ---------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------- DEC 2012-12-12 NULL NULL NULL 5 2925 2012 DEC 2012-12-21 NULL NULL NULL 0.002 0.91 2012 DEC 2012-12-26 NULL NULL NULL 0.5 NULL 2012 Null 13-02-2012 NULL NULL NULL 5 NULL 2012 DEC 2012-12-12 NULL NULL NULL 5 2925 2012 [/code][b]Requirement is...[u][/u][/b]I Build that Stored Procedure in SSIS Package with Job scheduler...In that Procedure i want to check the Ram_Reliance_Master table Value1)Mnth Column Should be Not Null 2)The DCPI_Date Field Should be 101 Format ie, (MMDDYYYY)Otherwise that Procedure will not Execute ... it should be terminate ...should not commit any Rows...How To Validate in Procedure ?I dont know well the Above Query is working fine or not..or any other idea Please Share and Support Me...Thanks & RegardsSaravanan.D

sql trigger problem Posted: 26 Jan 2013 01:38 PM PST I am trying to write my first trigger on a sql server 2008 r2 database.I keep getting a procedure error the 'INSERT INTO [dbo].[RPT_Trans_Audit] (Package_ID, Received_Date, Download_Date)' statement listed below. I do not know what is wrong. Can you tell me what I need to change?USE DEV2GOCREATE TRIGGER [dbo].[RPT_Trans_Audit] ON [RPT_Trans] AFTER INSERT, UPDATE, DELETEASBEGINSET NOCOUNT ON;BEGIN TRY INSERT INTO [dbo].[RPT_Trans_Audit] (Package_ID, Received_Date, Download_Date) SELECT Package_ID, Received_Date, Download_Date FROM INSERTEDEND TRY GO

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server Data Warehousing] T-SQL SCD2

Here is the query i am using in case if it helps.

To give you little more scenario  on my request  : Our Data warehouse is very traditional approach of ETL , We have update statments that does lookup and update the Foriegn keys.

My first update statments goes and finds an employee based on a logic of Data Entry

Update dbo.SFWP 
Set EmployeeID  = we2.EmployeeSK
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on  a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
and  Src_EntryDate >= we2.EffectiveBeginDTM and 
Src_EntryDate <= we2.EffectiveEndDTM

Apparently for some reason some employees cannot be matched because  our employee table has this condition not met because of missing records

and  Src_EntryDate >= we2.EffectiveBeginDTM and
Src_EntryDate <= we2.EffectiveEndDTM

So, my new query i am trying to take a previous or Next record and find employee ID and update it.

wITH CustCTE as
(
select  
We2.SK,
We2.NK,
We2.EffectiveBeginDTM,
We2.EffectiveEndDTM,
ROW_NUMBER() OVER (PARTITION BY We2.NK ORDER BY We2.EffectiveBeginDTM) RowNum
from dbo.SFWP a
Inner Join WMDM.dbo.WMDM AT
on  a.AssignedTaskID = AT.AssignedTaskID
Inner join WMDM.dbo.Employee we1
on AT.EmployeeID = we1.SK
Inner Join WMDM.dbo.Employee we2
on we1.NK = we2.NK
Where 
 Src_EntryDate < we2.EffectiveBeginDTM or 
Src_EntryDate > we2.EffectiveEndDTM
and a.EmployeeID is NULL
)
,
CustCTE1 as (
Select 
a.SK
,a.NK
,a.EffectiveBeginDTM
,a.EffectiveEndDTM
,a.RowNum
    
From CustCTE a
Union All 
Select 
Curr.SK
,Curr.NK
,Curr.EffectiveBeginDTM
,Curr.EffectiveEndDTM
,Curr.RowNum
    
From CustCTE Curr
Inner Join      CustCTE1 Prev
on Curr.NK = Prev.NK
and Curr.RowNum = Prev.RowNum-1
)
select * From CustCTE1

But i am not getting the same thing as i expect..

Any help in this matter will be highly appreciated.

thank you in advance

.

social.technet.microsoft.com/Forums

[SQL Server] Advanced SQL Server 2008 Extended Events with Examples

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com

[SQL Server 2008 issues] Assigning Default Constraint to a column based on another Column...

---

• Assigning Default Constraint to a column based on another Column...
• Pushing Data from T-SQL to Visual Studio While Debugging
• SSIS: Using a Full Result Set variable in a Data Flow Task
• Adding to Sales Quantities
• Add Monthly Revenue together - must be an easier way ...
• Please help=SQL 2008 backup script on all the user databases excluding the databases end with SPoint
• Query Help
• To Index, or Not to Index
• Divide by zero error message
• Error when executing dts from BIDS
• Parallel Data Warehouse for OLTP
• Database Query
• How to install BIDS for using SSIS for existing Sql Server 2008 r2?
• Convert string into xml and insert Sql Server
• Data Dictionary in SQL
• Difference between Index and Table Compression?
• Unable to view sql error log on Sql Server 2008 r2
• Dropping and re-creating full-text catalog
• bcp CSV to DB table
• Restore/Backup minimal permissions
• index stats Review
• SSRS configured through PowerShell (SQLPS)
• Cursor help
• Row count per filegroup
• Alternative for Excell (Access).
• Need help on Powershell for SQL 2008 R2
• Copy a table from one db to another including all configurations
• Unused Indexes
• Initial size for TempDb data and Log file?
• Converting Filestream to varchar/xml
• PIVOTing multiple columns?
• Weekly report through whole year

Assigning Default Constraint to a column based on another Column... Posted: 11 Apr 2013 07:11 PM PDT Hi All,I have two Columns in my Table Status A and Status B. I want to create a Default Constraint for column Status B based on the Value from Status A.If Status A = 1 then Status B = 1If Status A = 0 then Status B = 0 ...Both are Bit Data type. Is it possible to Create a Default Constraint to assign Status B with value in Status A.Thanks in Advance...

Pushing Data from T-SQL to Visual Studio While Debugging Posted: 06 Apr 2013 07:52 PM PDT I am debugging my T-SQL code in Visual Studio ... is it possible for me to write something in my Transact-SQL code and have it pushed in either the Output Window or Immediate Window of Visual Studio... if yes how do I do it?If I were writing a VB code for example it is simple .. I can just write debug.print "text write" ... and boom the text I want to end up in the Immediate Window gets there ... so how about in T-SQL being debugged in VS, how is it done?I have spent hours in books and google without getting any closer to the answer I am seeking and I am hoping people out there who have more experience would shed light and give me some enlightenment on this.This technique if possible would help me a lot in my debugging work. Thanks so much for all the help.Matt

SSIS: Using a Full Result Set variable in a Data Flow Task Posted: 05 Feb 2010 01:33 AM PST I attempting to find a solution for data extraction from a remote Oracle server using SSIS. I have a list of several hundred thousand IDs (Primary Key) in my local SQL Server database. What I would like to do is pull back the matching records in a Oracle database where the ID is shared. I do not have write rights on the remote Oracle database, so I am not able to load the list of records to a table on that server and use it in a join to extract the records I am interested in. What I was was wondering is if there is a way to pass a Full Result Set Variable to a Data Flow Task (essentially passing the list of ID's to be used in a WHERE clause of the statement. I am able to successfully build the Execute SQL Task to write the list to a object variable, but I am struggling with the next step, which is passing the list to the Data Flow Task. Any help would be greatly appreciated or if there is a different method that one has had success with in the past to solve a similiar solution I would definitely be interested in trying that option.Thanks in advance.

Adding to Sales Quantities Posted: 16 Mar 2013 03:23 AM PDT Hi I have a table that forecasts our Sales for the upcoming year. It contains the following columns: Year (DATE), Company (VARCHAR), Product (VARCHAR), ProductType (VARCHAR), LastYearsSales (INT), ForecastSales (INT).I would like to increase the ForecastSales for particular ProductTypes in the table. So for example for all the LEISURE products I would like to increase the forecasted sales figures by 1000. I don't want to add a 1000 to each LEISURE product but share the 1000 over all of the LEISURE products ie if there were 10 LEISURE products in the table then increase each of their sales by 100 (1000/10). Thanks in advance.BO

Add Monthly Revenue together - must be an easier way ... Posted: 11 Apr 2013 02:37 PM PDT Hi,I have a Cube file with 24 months of data & one field has the Month/Yr & another containing each months Revenue amounts.I want to nominate 3 consecutive months & add those months Revenue data together.Rather than use something like [Month].&[201206],[Revenue] + [Month].&[201207],[Revenue] + [Month].&[201208],[Revenue] is there a better way?Thanks

Please help=SQL 2008 backup script on all the user databases excluding the databases end with SPoint Posted: 11 Apr 2013 03:23 AM PDT HI All,Please provide me a script which takes a back up of all user databases in sql 2008 except for few that the db name end with SPoint. Can you please help me out?

Query Help Posted: 11 Apr 2013 03:05 AM PDT HelloI need one help to develop logicplease help me to this [code="sql"]create table #Enrollement(StudentID Varchar(10),SchoolID Int,EntryDate int,EntryCode Char(3),WithdrawalDate int,WithdrawalCode char(3),SchoolYear int)insert into #Enrollement values ('0082600',101,20120830,'A',20120128,NULL,2012)insert into #Enrollement values ('0082600',103,20130201,'A',20991231,NULL,2012)create table #Student(StudentKey int,StudentID Varchar(10),CurrentIEPStatus int,FRLunch int,EnrolledSchoolID int,EnrolledSchoolKey int,StartDate int,EndDate int,CurrentFlag int)insert into #Student values (740681,'0082600',0,0,101,6540,20120830,20120930,0)insert into #Student values (740682,'0082600',0,1,101,6540,20120930,20991231,1)[/code]in requirement, based on EndDate in #Student, I need to display StudentKey and EnrolledSchoolKey. I try this [code="sql"]select Distinct E.StudentID, MAX(S.StudentKey) AS StudentKey, --S.EnrolledSchoolID, E.SchoolID, MAX(s.EnrolledSchoolKey) AS SchoolKey, E.EntryDate, E.EntryCode, E.WithDrawalDate, E.WithDrawalCode from #Student sjoin #Enrollement Eon E.StudentID = S.StudentID where S.EndDate <=e.withdrawalDateand s.StudentID = '0082600'group by E.StudentID, E.SchoolID, E.EntryDate, E.EntryCode, E.WithDrawalDate, E.WithDrawalCode, E.SchoolYearorder by StudentKey[/code]and i got output as below[code="plain"]StudentID StudentKey SchoolID SchoolKey EntryDate EntryCode WithDrawalDate WithDrawalCode0082600 740682 103 6540 20130201 A 20991231 NULL[/code]but desired out put is[code="plain"]StudentID StudentKey SchoolID SchoolKey EntryDate EntryCode WithDrawalDate WithDrawalCode0082600 740681 101 6540 20120830 A 20120128 NULL0082600 740682 103 6540 20130201 A 20991231 NULL[/code]Please help me to do this

To Index, or Not to Index Posted: 11 Apr 2013 01:45 AM PDT I have a huge table (80 mil) records where I need to append an extra URL column. The query is kind of like:Update a set URL = b.URLfrom ainner join bon a.ID = b.IDwhere a.URL is not nullI'm doing batch updates right now, but the progress is very slow. I'm debating if I should index on a.URL, which is a varchar(500) field and the table already has massive indexes which cannot be dropped because they are used by production. What's your take on this, to index, or not to index on the URL column? Thanks!

Divide by zero error message Posted: 10 Apr 2013 08:21 PM PDT Dear All,I'm getting the following error message Msg 8134, Level 16, State 1, Procedure "stored procedure name", Line 149Divide by zero error encountered, and I don't seem to find a way to fix the issue. I've looked at some Forums and one of them suggested that using SET ARITHABORT OFFGOSET ANSI_WARNINGS OFFGOShould resolve the problem, which it does but for some reason after I've altered the Stored Procedure to include it and reopen the SP, SET ARITHABORT OFFGOSET ANSI_WARNINGS OFFGO is no longer there. I'm not sure what I'm missing or are there other ways to resolve the error message?Thank you in advance!

Error when executing dts from BIDS Posted: 11 Apr 2013 08:08 AM PDT For some reason I can no longer execute(test) dts packages from BIDS on my Windows 7 laptop.I get the following error after waiting about 1 minute. "Cannot communicate with the debug host process. Failed to obtain child process active object."I have tried repair install, uninstall-reinstall and I've had my laptop reimaged and started from scratch intalling SQL client again.Any idea what might be causing this?

Parallel Data Warehouse for OLTP Posted: 08 Apr 2013 05:10 PM PDT Hi,Is Parallel Data Warehouse feature of SQL Server 2008 R2 targeted only for OLAP/BI impementations?Can we use it for hosting OLTP databases as well?Thanks.

Database Query Posted: 11 Apr 2013 07:45 AM PDT Hi,I have couple of questions related to sql server, though I googled it, did not get any definite answer. I am sure one of you SSC guru would help me to understand SQL Server in a better way :-).Why the joining between integer columns are faster?How the sql server joins strings internally, does it checks character by character or uses ASCII to compare strings?Does the database engine performs an order by on the group by column first to group the data easily?Thanks in advance.

How to install BIDS for using SSIS for existing Sql Server 2008 r2? Posted: 11 Apr 2013 02:46 AM PDT Can someone please tell me how to get BIDS installed for developing SSIS packages to the existing SQL Server 2008 R2 Enterprise edition? and where to find it?In the past I developed and used SSIS packages in Sql Server 2005 but not in Sql Server 2008. I need to start working on a new project that I would like to use SSIS packages to import raw data files into SQL Server 2008 Staging database/tables. please help me how to get BIDS or required tools started to be able to create and use SSIS packages.Thanks!

Convert string into xml and insert Sql Server Posted: 11 Apr 2013 04:34 AM PDT We have a sql server 2008 R2 database table with an xml stored in a column as a VARCHAR data type.I now have to fetch some of the elements of the xml. So I want to first convert the xml stored as a VARCHAR data type, to an xml stored as an xml data type.example : Table A Id(int) , ProductXML (varchar(max))Table BId(int), ProductXML(XML)I want to convert the ProductXML from Table A into XML data type and insert into Table B.I tried using the CAST() and CONVERT() function as shown below :insert into TableB (ProductXML)select CAST(ProductXML as XML) from TableA;similarly tried convert but I get an error 'XML Parsing : unable to switch encoding'.Is there any way I can convert the varchar entries in the table into xml entries ?About the *XML* : The XML is huge with many nodes , and its structure changes dynamically. Example : One row can have and XML entry for 1 product and another row can have an xml entry for multiple products.

Data Dictionary in SQL Posted: 11 Apr 2013 06:22 AM PDT Anyone know what's equivalent to data dictionary in SQL server? I think it's the same as DMVs?I need to create a login which have read only access to data dictionary in SQL server.I think the requestor means, to create a login and map to master db, with db_datareader permission, this will give the person to select from DMVs. Any suggestions?Thanks,SueTons.

Difference between Index and Table Compression? Posted: 11 Apr 2013 03:36 AM PDT If i have 1 Clustered ,4 Non-clustered indices and rebuild all these indices with Page compression enabled would that be equal to Table compression? If not whats the difference?

Unable to view sql error log on Sql Server 2008 r2 Posted: 11 Apr 2013 04:05 AM PDT Hi,i have sql server 2008 r2 cluster on win 2008 server.We are not able view the sql error log through SSMS and by xp_readerrorlog....Failed to open loopback connection. Please see event log for more information.Msg 22004, Level 16, State 1, Line 0error log location not foundPlease let me know if any one faced same issue.MAny thanks,

Dropping and re-creating full-text catalog Posted: 11 Apr 2013 04:22 AM PDT Hi, the user is reporting the following error, i have not done this previously. Any suggestions would be appreciated."Users having an issue with "databaseName" . Users receiving the following error. Full-text catalog 'catalogName' is in an unusable state. Drop and re-create this full-text catalog."Anyone have any script to re-create this, and also what should I make sure before doing this, this is a production change.Thanks,SueTons.

bcp CSV to DB table Posted: 11 Apr 2013 01:15 AM PDT To all:I am trying to import a CSV file into a database table using bcp, however I am getting the message "Unexpected EOF encountered in the BCP data-file"I have looked at many forum posts and articles on Google, but just can't seem to find the solution.Here is my code:declare @cmd varchar(8000)select @cmd = 'bcp STH_D_Test.dbo.BCP_Test in "C:\TestFile.csv" -c -T' exec master.dbo.xp_cmdshell @cmdThank you for any help in advance! =)

Restore/Backup minimal permissions Posted: 02 Nov 2010 11:47 PM PDT We use Netbackup for our SQL servers to backup and restore databases. I would like the service account used by Netbackup to have as limited permissions as possible. The account should be able to backup and restore a db without being able to read any of the content. Right now the account jobs fail if the service account is not in the sysadmin role. I removed the account from sysadmin and limited it to dbcreator and public but the job fail. Any idea how to setup an account so that people who know the service account password can't log in with that account and read db information?Thanks!

index stats Review Posted: 11 Apr 2013 03:00 AM PDT I have a table with the 2 indexes performing as shown below:Index Name Index Type Queries Which Read Queries Which Wrote Reads/WriteIX_index2 NONCLUSTERED 1283151 2 641575PK_index1 CLUSTERED 516435 439378 1Looking at the above stats, would I be better changing the Primary Key to index2 and making index1 the non clustered index ?

SSRS configured through PowerShell (SQLPS) Posted: 11 Apr 2013 02:42 AM PDT Hi!I have a question about the SSRS and Powershell (SQLPS)We trying to configure the SSRS through POWERSHELL, we are able to set all settings of SSRS except the Windows Services account and password.Someone have any idea how to do this?Sincerely Eric

Cursor help Posted: 10 Apr 2013 09:13 PM PDT Hi,I have made a stored procedure with a cursor:[code="sql"]USE [DB_admin1]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [rept].[suscribedSystems] -- Add the parameters for the stored procedure here @startDate DATETIME, @endDate DATETIMEASBEGIN SET NOCOUNT ON; DECLARE @SubID INT, @sql VARCHAR(8000) DECLARE crSystem CURSOR FOR SELECT [SystemNum] ,[Date] AS SubStart , (SELECT TOP (1) [Date] FROM [DB_admin1].[dbo].[tblRenewals] REN2 WHERE REN2.[Date] > REN.[Date] AND REN.SystemNum = REN2.SystemNum ORDER BY [Date] ASC ) AS SubEnd FROM [DB_admin1].[dbo].[tblRenewals] REN WHERE [Date] <= @startDate AND (SELECT TOP (1) [Date] FROM [DB_admin1].[dbo].[tblRenewals] REN2 WHERE REN2.[Date] > REN.[Date] AND REN.SystemNum = REN2.SystemNum ORDER BY [Date] ASC ) > @endDate AND (SELECT TOP (1) [Date] FROM [DB_admin1].[dbo].[tblRenewals] REN2 WHERE REN2.[Date] > REN.[Date] AND REN.SystemNum = REN2.SystemNum ORDER BY [Date] ASC ) <> '2999-01-01' ORDER BY SubStart OPEN crSystem FETCH NEXT FROM crSystem INTO @SubId WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'INSERT INTO rept.tblSubscribedSystems ([systemNum] ,[dtuStart] ,[dtuEnd] ,[total]) VALUES (@SubID ,@startDate ,@endDate ,(SELECT COUNT (*) FROM rept.tblSubscribedSystems) )' --PRINT @sql EXEC (@sql) FETCH NEXT FROM crSystem INTO @SubId END CLOSE crSystem; DEALLOCATE crSystem; END[/code]When i run the stored procedure--EXEC [rept].[suscribedSystems] '2009-05-01', '2009-06-01'I get this error:Msg 16924, Level 16, State 1, Procedure suscribedSystems, Line 52Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.Thank you in advance for your time and help.

Row count per filegroup Posted: 11 Apr 2013 12:10 AM PDT Hi,I have a partitioned table and its spread on filegroups monthly.My question is how do i find out how many records does each individual file have?thanx

Alternative for Excell (Access). Posted: 10 Apr 2013 10:59 PM PDT Dear reader,[b]It's not difficult to convince any manager; that storage in SQL-server would be a good idea. But because of lack of interaction software for the manager, most (if not all) managers [u]keep using spreadsheats[/u] to accumulate and spread data/info into the organisation.[/b]SQL-server is a good: 1. For the storage of data.2. For BI (reporting, intergration, analys and dataminging).3. For protection of the data. (Availability, data loss, and constriants on the data).But SQL-server does [b]not[/b] offer a good solution for user interaction with the database.So what is needed is a frontend for SQL-server which is acceptable for managers to work with.The building of a databasemodel and implementing a database is not a problem.The problem lies in the building of a frontend. Where managers expect the solution to be ready before they have thought of it and which has an 'acceptable' userinterface. Specifications are never complete and rarely very clear.The amount of data is always very limited, the number of users is limited. The requirements for safety, security and availability are low.As everybody knows the problem with spreadsheat is: copies, copies and more copies, versions, no good search or retrieval system. But they are still the very favorable method for managers to accumulate, and spread their data.We have a number of set's of data which would be very suitable to be stored in SQL-server. But the lack of frontend's prevents this data to be put in a 'solid' database.Access can be used as a frontend for SQL-server. Allthough this is a solution it is not a brilliant solution.Resources: The SQL-server database is available, all users have a PC with office components. But there are not plenty of resources to build a full blown application for every requirement.Suggestions? Solutions?Please help the managers.Thanks for your time and attention,ben brugman

Need help on Powershell for SQL 2008 R2 Posted: 09 Apr 2013 06:25 AM PDT Folks ,I created powershell script to be used with SQL 2008 which uses Invoke-sqlcmd . The script runs fine on SQL 2008 environment , however when i try to run the same on other server with SQL 2005 installed on it , i get an error . On troubleshooting further it was found that "Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2" needs to be installed on the server . Can someone guide if the above installation for Powershell extensions has any prerequisite . The environment on this this needs to be installed is production and hence every positive and negative points needs to be taken into consideration .Kindly help !!!

Copy a table from one db to another including all configurations Posted: 11 Apr 2013 01:22 AM PDT How to copy a table from one db to another including all configurations, such as index, primary key...I use select * into newtable from originaltablebut missing indexes.

Unused Indexes Posted: 10 Apr 2013 11:01 PM PDT Hello,I have been running the DBTA on a database to get some recommendations, I have the results, nothing has been applied.Afterwards I ran the script below that I got from the web, I first ran it in dev (where DBTA had not been run) to test it, I then ran it in live to compare the output. The script appears to have also listed all the indexes listed in the DBTA results as well. I have checked to make sure these were not applied in live, why have all the dta indexes also been listed? Where are they and how can I prevent them from being listed? I only want to list the indexes currently in use. After the code I have a another question.SET NOCOUNT ON;DECLARE @First [smallint] ,@Last [smallint] ,@IsUnique [smallint] ,@HasNonKeyCols [char](1) ,@TableName [varchar](256) ,@IndexName [varchar](256) ,@IndexType [varchar](13) ,@IndexColumns [varchar](1000) ,@IncludedColumns [varchar](1000) ,@IndexColsOrder [varchar](1000) ,@IncludedColsOrder [varchar](1000) DECLARE @Indexes TABLE ([RowNo] [smallint] IDENTITY(1, 1) ,[TableName] [varchar](256) ,[IndexName] [varchar](256) ,[IsUnique] [smallint] ,[IndexType] [varchar](13))DECLARE @AllIndexes TABLE ([RowNo] [smallint] IDENTITY(1, 1) ,[TableName] [varchar](256) ,[IndexName] [varchar](256) ,[IndexType] [varchar](13) ,[KeyColumns] [varchar](512) ,[NonKeyColumns] [varchar](512) ,[KeyColumnsOrder] [varchar](512) ,[NonKeyColumnsOrder] [varchar](512) ,[IsUnique] [char](1) ,[HasNonKeyColumns] [char](1))IF OBJECT_ID('Tempdb.dbo.#Temp') IS NOT NULL DROP TABLE #TempSELECT o.[object_id] AS [ObjectID] ,OBJECT_NAME(o.[object_id]) AS [TableName] ,i.[index_id] AS [IndexID] ,i.[name] AS [IndexName] ,CASE i.[type] WHEN 0 THEN 'Heap' WHEN 1 THEN 'Clustered' WHEN 2 THEN 'Non-Clustered' WHEN 3 THEN 'XML' ELSE 'Unknown' END AS [IndexType] ,ic.[column_id] AS [ColumnID] ,c.[name] AS [ColumnName] ,ic.[is_included_column] [IncludedColumns] ,i.[is_unique] AS [IsUnique]INTO #TempFROM sys.indexes iINNER JOIN sys.objects o ON i.object_id = o.object_id AND o.type = 'U' AND i.index_id > 0INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_idINNER JOIN sys.columns c ON c.column_id = ic.column_id AND c.object_id = ic.object_idINSERT INTO @IndexesSELECT DISTINCT [TableName] ,[IndexName] ,[IsUnique] ,[IndexType]FROM #TempSELECT @First = MIN([RowNo]) FROM @IndexesSELECT @Last = MAX([RowNo]) FROM @IndexesWHILE @First <= @LastBEGIN SET @IndexColumns = NULL SET @IncludedColumns = NULL SET @IncludedColsOrder = NULL SET @IndexColsOrder = NULL SELECT @TableName = [TableName] ,@IndexName = [IndexName] ,@IsUnique = [IsUnique] ,@IndexType = [IndexType] FROM @Indexes WHERE [RowNo] = @First SELECT @IndexColumns = COALESCE(@IndexColumns + ', ', '') + [ColumnName] FROM #Temp WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 0 ORDER BY [IndexName], [ColumnName] SELECT @IncludedColumns = COALESCE(@IncludedColumns+ ', ', '') + [ColumnName] FROM #Temp WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 1 ORDER BY [IndexName], [ColumnName] SELECT @IndexColsOrder = COALESCE(@IndexColsOrder + ', ', '') + [ColumnName] FROM #Temp WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 0 SELECT @IncludedColsOrder = COALESCE(@IncludedColsOrder + ', ', '') + [ColumnName] FROM #Temp WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 1 SET @HasNonKeyCols = 'N' IF @IncludedColumns IS NOT NULL BEGIN SET @HasNonKeyCols = 'Y' END INSERT INTO @AllIndexes ([TableName] ,[IndexName] ,[IndexType] ,[IsUnique] ,[KeyColumns] ,[KeyColumnsOrder] ,[HasNonKeyColumns] ,[NonKeyColumns] ,[NonKeyColumnsOrder] ) SELECT @TableName ,@IndexName ,@IndexType ,CASE @IsUnique WHEN 1 THEN 'Y' WHEN 0 THEN 'N' END ,@IndexColumns ,@IndexColsOrder ,@HasNonKeyCols ,@IncludedColumns ,@IncludedColsOrder SET @First = @First + 1 ENDSELECT 'Listing All Indexes' AS [Comments]SELECT [TableName] ,[IndexName] ,[IndexType] ,[KeyColumns] ,[HasNonKeyColumns] ,[NonKeyColumns] ,[KeyColumnsOrder] ,[NonKeyColumnsOrder] ,[IsUnique]FROM @AllIndexesSELECT 'Listing Duplicate Indexes' AS [Comments]SELECT DISTINCT a1.[TableName] ,a1.[IndexName] ,a1.[IndexType] ,a1.[KeyColumns] ,a1.[HasNonKeyColumns] ,a1.[NonKeyColumns] ,a1.[KeyColumnsOrder] ,a1.[NonKeyColumnsOrder] ,a1.[IsUnique]FROM @AllIndexes a1JOIN @AllIndexes a2ON a1.[TableName] = a2.TableNameAND a1.[IndexName] <> a2.[IndexName]AND a1.[KeyColumns] = a2.[KeyColumns] AND ISNULL(a1.[NonKeyColumns], '') = ISNULL(a2.[NonKeyColumns], '') WHERE a1.[IndexType] <> 'XML'SET NOCOUNT OFF;Is it bad practice to have a non clusted index on top of a clusted index?Thanks for any help.Regards,D.

Initial size for TempDb data and Log file? Posted: 12 Sep 2011 10:33 AM PDT Hi,We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them. What are the best values to start with?U ->Tempdbdata having tempdb.mdf fileV->Tempdblog having templog.ldf fileThanks

Converting Filestream to varchar/xml Posted: 11 Apr 2013 01:05 AM PDT Hello everyone, We have an xml that has been converted into a filestream format and stored into a column of a table. I now have to extract some of the data from the xml So I would like to know if there is a way of converting a fiestream datatype (varbinary(max) Filestream) into a varchar or xml datatype ?

PIVOTing multiple columns? Posted: 11 Apr 2012 08:30 AM PDT I'm working on a PIVOT, and I can't see to get it to work. Here is the PIVOT query:[code="sql"]SELECT *FROM( SELECT Listing.Person_ID ,Person.Person_Gender FROM Listing JOIN Person ON Listing.Person_ID = Person.Person_ID WHERE Listing.Program_Version_Code_Listing = 'AR-2016' AND Listing.Entity_Status_Listing IN ('Accept', 'Conditional Accept') GROUP BY Listing.Person_ID ,Person.Person_Gender) AS sPIVOT( COUNT(s.Person_ID) FOR Person_Gender IN (Male, Female, [Not Specified])) as p[/code]Now I need to somehow get this query inside the above query. Obviously I written it as a standalone SELECT statement here, so I know it will need to be modified.[code="sql"]SELECT (AVG(DATEDIFF(DD,Birth_Date_Person,GETDATE()))/365.25) AS 'Avg Age'FROM Listing JOIN Person ON Listing.Person_ID = Person.Person_IDWHERE Listing.Program_Version_Code_Listing = 'AR-2016'AND Listing.Entity_Status_Listing IN ('Accept', 'Conditional Accept')[/code]The final results will need to be something like this:[code="plain"]Male Female Not Specified Avg Age100 89 0 19.8541[/code]I've been wrestling with this all day. Does anyone have any thoughts?

Weekly report through whole year Posted: 10 Apr 2013 10:51 PM PDT Hi thereI have 2 tables and sample data:--------------------------------------------------------------------------------------------------------------------------------------------------------CREATE TABLE [dbo].[tblProduct]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_tblProduct] PRIMARY KEY CLUSTERED ( [ProductID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]--------------------------------------------------------------------------------------------------------------------------------------------------------CREATE TABLE [dbo].[tblOrder]( [OrderID] [int] IDENTITY(1,1) NOT NULL, [ProductID] [int] NOT NULL, [OrderDate] [datetime] NOT NULL, CONSTRAINT [PK_tblOrder] PRIMARY KEY CLUSTERED ( [OrderID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]--------------------------------------------------------------------------------------------------------------------------------------------------------INSERT INTO tblProduct (Name) VALUES('Coffee')INSERT INTO tblProduct (Name) VALUES('Tea')INSERT INTO tblProduct (Name) VALUES('Lager')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-01 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-08 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-01-08 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (3, '2013-01-09 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-10 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-01-10 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (3, '2013-01-11 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-11 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-01-15 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-18 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-30 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-05-01 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (3, '2013-07-15 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-09-23 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-10-01 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-10-08 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-10-11 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-11-01 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (3, '2013-11-06 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-11-08 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (3, '2013-12-10 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-12-25 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-12-30 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-12-30 09:00:00')--------------------------------------------------------------------------------------------------------------------------------------------------------I need to generate report which shows number of orders for each ProductID for each week in year 2013. Ideally would be to generate 54 weeks as pivot table so week1, week2 would be columns and 3 rows for 3 products and order count for each product each week.Product

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Testing with Profiler Custom Events and Database Snapshots

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

.

sqlteam.com