Wednesday, March 13, 2013

[T-SQL] Word wrapping query output

[T-SQL] Word wrapping query output

Word wrapping query output

Posted: 13 Mar 2013 05:51 AM PDT

This one is probably very simple, but I can't find the answer to save my life.I have a query that I run in which the output has to be in text mode (not grid mode). One of the output columns is text that often is several hundred characters long. This of course makes my output extremely wide. I'd like to format this one column so that it's only 50 characters wide but the text is wrapped to several lines tall.Is this possible?

Convert String to Time Format

Posted: 13 Mar 2013 04:26 AM PDT

Hello EveryoneI have been working most of the morning to try and figure out how I can convert a string to a time format. So far, I have not found anything that will allow this in SQL Server.So, I have come up with another way, unless someone has a nifty convert function in their toolbox.I have a string that is taken from a file name. And I would like to store that in time format column in a table. I am already storing the date portion, which seems to convert just fine.This is my example of a string that I have:[code="sql"]012345[/code]I need to insert a colon after every 2 digits, just like a time would be formatted.[code="sql"]01:23:45[/code]I can easily convert this to a Time(0) format. As long as the colons are in place.But, if for some odd reason, the string would happen to come by as '12345', missing the leading zero.How can I handle that so that the time format would still be correct?Either way, I need this to be able to be converted to a Time(0) format.Thank You in advance for all your assistance, suggestions, and comments.Andrew SQLDBA

Recursive Update Trigger

Posted: 13 Mar 2013 03:32 AM PDT

I am trying to create an update trigger that updates isactive field depending on different criteria.If DateField1 is >= GETDATE() OR DateField2 >= GETDATE() AND Extension =1 then IsActive is true.If DateField1 is < GETDATE() AND DateField2 < GETDATE() OR DateField2 IS NULL then IsActive is false.Right now I have this as two separate updates, but that makes the trigger recursive. Can I combine these using CASE statement?

Split A String into 4 parts

Posted: 13 Mar 2013 01:44 AM PDT

Hello EveryoneThank you all for the suggestions yesterday. I have written a SSIS package that now needs to read the file name of a text file to pump in.I need to read the file name, split the file into pieces and use that parts of the file name for some other things. My real issue is splitting the file name and getting the parts that I need. I cannot use the splitter that is part of the tally table. I just need to have the SQL functions to work and split the file name.This is the file name, which consists of: TypeofData_Date_Time.FileExtension[code="sql"]Notes_20130204_003015.txt[/code]I would like to split the string at each underscore. I do not care about the file extension or the underscores. All I need are the other three parts. The file name will be changing everyday when the file is generated. This will be generated multiple times per day. Hence the time in the file name.I am in need of these three parts of the file.Notes20130204003015Thank You in advance for all your assistance, suggestions and commentsAndrew SQLDBA

Performance Problems converting data into columns

Posted: 12 Mar 2013 10:33 PM PDT

Hello, I am in need of some help with an SSIS package (SQL 2008). I am trying to extract data out of a progress database and provide a reporting database for our users.The area I am having some trouble is with some tables which have columns where data is stored and split by a ';' . This data in the system actually represents different fields in the application so the end game is to have these in their own columns for people to select from.Example of table:GroupNumber Group_id GroupName AddressLine1 AddressLine2 AddressLine3 Postcode DataColumnGroup1 0001 TestGroup Capital letter Ltd Test Street Test Town XX1 22A 00-00-00;112115;TestBankGROUP2 0002 TestGroup2 Bad letter Ltd Test2 Street Test2 Town XX2 11B 11-00-55;522445;TestBank2 The way the data needs to end up is to have the data column split out into: Sortcode AccountNumber BankName00-00-00 112115 TestBank11-00-55 522445 TestBank2The issue I am having is performance of the methods I have tried so far: the DataColum has up to 200 'columns' within it. This is fine over a few records <100 but when it performs across >40,000 then I am starting to notice some serious performance problems results are taking as long as 8 minutes. If I only split 10 out of 100 columns it performs much better over 40,000 rows than splitting them all. Example data:CREATE TABLE GroupTable ( GroupNumber VARCHAR(6) NULL ,Group_ID INT ,GroupName VARCHAR(10) NULL ,AddressLine1 VARCHAR(50) NULL ,AddressLine2 VARCHAR(50) NULL ,AddressLine3 VARCHAR(50) NULL ,Postcode VARCHAR(8) NULL ,Datacolumn VARCHAR(300) );INSERT INTO GroupTable (GroupNumber,Group_ID,GroupName,AddressLine1,AddressLine2,AddressLine3,Postcode,datacolumn)VALUES ('Group1',1,'testgroup','Capital letter Ltd','1 Test Street','Test Town','XX1 22A','00-00-00;112115;TestBank'),('Group2',2,'testgroup2','Low letter Ltd','2 Test2 Street','Test2 Town','XX2 11B','11-00-55;522445;TestBank2'),('Group1',1,'testgroup','Capital letter Ltd','1 Test Street','Test Town','XX1 22A','10-10-10;112115;TestBank'),('Group2',2,'testgroup2','Low letter Ltd','2 Test2 Street','Test2 Town','XX2 11B','11-00-00;522445;TestBank2')I originally started using the following function and it was working ok until I got to pull larger amounts of data (and adding in more columns to split): (this was in an old post on these forums)SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[FromProgressArray] (@array NVARCHAR(4000), @index INT)RETURNS NVARCHAR(256)ASBEGINIF @index = 0RETURN( LEFT(@array, CHARINDEX(';', @array) - 1) )DECLARE @counter INTSELECT @counter = 0WHILE @counter +1 < @index BEGINIF (CHARINDEX(';', @array) <> 0)SELECT @array = SUBSTRING(@array, CHARINDEX(';', @array) + 1, LEN(@array))ELSESELECT @array = ''SELECT @counter = @counter + 1ENDIF CHARINDEX(';', @array) <> 0SELECT @array = LEFT(@array, CHARINDEX(';', @array) - 1)RETURN( @array )ENDExample query:SELECT GroupNumber ,Group_ID ,GroupName ,AddressLine1 ,AddressLine2 ,AddressLine3 ,Postcode ,dbo.FromProgressArray(datacolumn,1) AS sortcode ,dbo.FromProgressArray(datacolumn,2) AS AccountNumber ,dbo.FromProgressArray(datacolumn,3) AS BankNameFROM dbo.GroupTable I have also tried to use the tally table method mentioned by Jeff Moden: http://www.sqlservercentral.com/articles/T-SQL/62867/Although this performs 50% quicker I have a problem here transposing the rows into columns. I was using the function DelimitedSplit8KExample query:SELECT GroupNumber ,GroupName ,AddressLine1 ,AddressLine2 ,AddressLine3 ,Postcode ,MAX(CASE WHEN dsk.ItemNumber = 1 THEN dsk.Item END) AS SortCode ,MAX(CASE WHEN dsk.ItemNumber = 2 THEN dsk.Item END) AS AccountNumber ,MAX(CASE WHEN dsk.ItemNumber = 3 THEN dsk.Item END) AS BankNameFROM GroupTableCROSS APPLY dbo.DelimitedSplit8K(datacolumn, ';') dskGROUP BY GroupNumber ,GroupName ,AddressLine1 ,AddressLine2 ,AddressLine3 ,PostcodeThe problem here is that the group record can have multiple entries in the table and the only way to distinguish the differences is once the datacolumn has been split out. This causes problems when using the above method as the grouping messes it up.As you can see only two results are shown not the 4 that should be. If you add in dsk.itemnumber to the grouping the case ceases to work. I will also point out that the datacolumn can have no data between the ; .In the event it finds one it should add in a blank or NULL entry.I have also looked into using the derived column feature but am not sure about the best way of doing this and haven't found that much when using search engines (I maybe using the wrong keywords though).Could someone tell me which way should be more efficient? Using a function or derived column in SSIS and assist with getting me on the right road so to say?Many Thanks

Converting Rows to columns

Posted: 12 Mar 2013 10:19 AM PDT

i want to convert rows to columns220output2 2 0

Insert Performance

Posted: 12 Mar 2013 01:49 PM PDT

Hi,Which will perform faster/better inserting million rows to a table or put all insert in a transaction then commit?Does it have a considerable difference?Exampleinsert into ...insert into ...insert into ...insert into ...insert into ...orbegin transactioninsert into ...insert into ...insert into ...insert into ...insert into ...commit transaction;Thanks!

Very frustrating performance tuning!

Posted: 12 Mar 2013 09:54 AM PDT

So I have run into this most aggravating of issues :-DHere is what I have; I am loading sales order data from staging data. I have this in production, and I have it in test. Our production server is well over 2x the resources (16 cores, 50gb memory, several raid 5's etc) vs test (8 cores, 24gb memory, 1 raid 5)I have been having some performance issues with said load script in production, so I restored to test and began tuning. One thing I noticed right away is that test was running the same exact data and script 2x faster! So diving into the first script which basically removes records from the destination that no longer exist in the source, I noticed in the execution plan that it is using a merge join instead of a hash join to join the 2 large tables (~13 million records). On the test system it is using the hash join. So just to test, I used the hash join option on the production server and it runs the same speed as the test server. I rebuilt all the indexes, created new statistics, messed with maxdop and recompile options to no avail. So my question is...what the heck am I missing? I have not come across such a conundrum in all my years as a DBA!

Advanced String Manipulation

Posted: 12 Mar 2013 10:12 AM PDT

I have a string that needs some serious help...I need to rewrite many strings with the aggregate value of anything between position 1, 12 and on a per UserID basis. Essentially...I need to GROUP BY UserID.The formatting needs to stay exactly the same fixed length.Here's the breakdown of the string:The [Amount] is anything between position 1, 12. The [UserID] is the final 6.Thanks in advance...[code="sql"]IF OBJECT_ID('TempDB..#StrungOut','U') IS NOT NULL DROP TABLE [#StrungOut] CREATE TABLE [dbo].[#StrungOut]( [Value] varchar (255) NOT NULL) INSERT INTO #StrungOut (Value) SELECT 'D000000206408312335308055102 Joe Blow 1002220200000290357' UNION ALLSELECT 'D00000248190001233930343216 Sue O''Malley 1004539500000290360' UNION ALLSELECT 'D00031228690003213965347415 Sue O''Malley 1004539500000290360' UNION ALLSELECT 'D00000022940010007560323433 Fred Greenface 1002403000000290361' UNION ALLSELECT 'D000000063100013455348487150327 Bill Guy 9835627000000290363' UNION ALLSELECT 'D000000063700043445238454350364 Bill Guy 1004695300000290363' UNION ALLSELECT 'D00000020640010005467116730 billy buck 9836280000000290376' UNION ALLSELECT 'D00000020640010001334112330 billy buck 9836280000000290376' UNION ALLSELECT 'D00000350640010001356162670 billy buck 1236280000000290400' UNION ALLSELECT 'D00000020640001602130103454 dave user 1004253500000290379' UNION ALLSELECT 'D00001220440002304835606876 dave user 5004253500000290379'SELECT * FROM #StrungOut[/code]

How do I optimize a query with a text column?

Posted: 12 Mar 2013 02:14 AM PDT

I am looking for a way to optimize a SELECT query that includes a column with a data type text. I cannot change the table. I am open to any and all suggestions! Thanks!HawkeyeDBA

Reporting on report execution over the last 12 months

Posted: 12 Mar 2013 11:02 AM PDT

Hi guys,I'm looking to create a report that will detail how many times each SSRS report was executed over the last 12 months. I'm aware that by default the execution log only holds 2 months worth of data but we're going to be changing this to hold 12 months worth.My initial thought was to do some kind of pivot but I'm not sure how to make this dynamic in terms of the dates. I'd like the report to look something like:Report Name Jan 2013 Feb 2013 Mar 2013Report1 12 15 10....... and so onWhat would be the best way for me to do this?Thanks in advance

How to make triple-pass UPDATE single-pass?

Posted: 12 Mar 2013 04:34 PM PDT

I'm attempting to optimize some code that I was just handed, and I'm not exactly sure if what I want to do is possible. The way it's currently written, there are 3 temp tables that have just a few rows eachHeader has 5 million rowsDetails has 3 million rowsI have included a stripped donw version of the code as it is currently written: 3 updates to populate a "summary row", making three passes of multi-million row tables. I am trying to find a way to do this in a single pass. Expected output (sorry for any formatting glitches):EmployeeID Code1PeriodToDate Code1YearToDate Code2PeriodToDate Code2YearToDate Code3PeriodToDate Code3YearToDate1 3.00 3.00 4.00 4.00 5.00 5.00We are running SQL 2008 R2 Standard. Thanks in advance for any suggestions. sqlnyc[code="sql"]CREATE TABLE CodeIDTable1 ( CodeID INT NOT NULL,Code CHAR(1) NOT NULL)CREATE TABLE CodeIDTable2 ( CodeID INT NOT NULL,Code CHAR(1) NOT NULL)CREATE TABLE CodeIDTable3 ( CodeID INT NOT NULL,Code CHAR(1) NOT NULL)INSERT CodeIDTable1 VALUES (1, 'A') INSERT CodeIDTable1 VALUES (2, 'B') INSERT CodeIDTable1 VALUES (3, 'C') INSERT CodeIDTable2 VALUES (4, 'D') INSERT CodeIDTable2 VALUES (5, 'E') INSERT CodeIDTable2 VALUES (6, 'F') INSERT CodeIDTable3 VALUES (7, 'G') INSERT CodeIDTable3 VALUES (8, 'H') INSERT CodeIDTable3 VALUES (9, 'I') CREATE TABLE Details ( HeaderID INT NOT NULL ,DetailID INT NOT NULL ,CodeID INT NOT NULL ,EmployeeID INT NOT NULL ,Date Datetime NOT NULL ,Amount DECIMAL (18,2) NOT NULL)CREATE TABLE Header ( HeaderID INT NOT NULL ,EmployeeID INT NOT NULL ,HeaderDate DATETIME NOT NULL)INSERT Header VALUES (1, 1, '2013-01-01')INSERT Header VALUES (2, 1, '2013-01-01')INSERT Header VALUES (3, 1, '2013-01-01')INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 1, 1, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 2, 1, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 3, 1, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 4, 5, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 5, 5, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 6, 5, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 7, 6, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 8, 7, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 9, 7, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 10, 7, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 11, 7, 1, '2013-01-01', 1.00)INSERT Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 12, 7, 1, '2013-01-01', 1.00)--drop TABLE EmployeeSummary CREATE TABLE EmployeeSummary ( EmployeeID INT NOT NULL ,Code1PeriodToDate DECIMAL(18,2) NULL ,Code1YearToDate DECIMAL(18,2) NULL ,Code2PeriodToDate DECIMAL(18,2) NULL ,Code2YearToDate DECIMAL(18,2) NULL ,Code3PeriodToDate DECIMAL(18,2) NULL ,Code3YearToDate DECIMAL(18,2) NULL)--TRUNCATE TABLE EmployeeSummaryINSERT EmployeeSummary (EmployeeID) VALUES (1)DECLARE @FirstDayOfYear DATETIME = '2013-01-01', @LastDayOfYear DATETIME = '2013-12-31'DECLARE @ReportStartingDate DATETIME = '2013-01-01', @ReportEndingDate DATETIME = '2013-12-31'UPDATE EmployeeSummarySET Code1PeriodToDate = Summary.AmountPeriod ,Code1YearToDate = Summary.AmountYtdFROM ( SELECT Header.EmployeeID ,SUM(CASE WHEN Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN Details.Amount ELSE 0 END) AS AmountPeriod ,SUM(CASE WHEN Header.HeaderDate <= @ReportEndingDate THEN Details.Amount ELSE 0 END) AS AmountYtd FROM Details INNER JOIN CodeIDTable1 ON CodeIDTable1.CodeID = Details.CodeID INNER JOIN Header ON Details.HeaderID = Header.HeaderID WHERE Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate GROUP BY Header.EmployeeID ) AS SummaryWHERE EmployeeSummary.EmployeeId = Summary.EmployeeIDUPDATE EmployeeSummarySET Code2PeriodToDate = Summary.AmountPeriod ,Code2YearToDate = Summary.AmountYtdFROM ( SELECT Header.EmployeeID ,SUM(CASE WHEN Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN Details.Amount ELSE 0 END) AS AmountPeriod ,SUM(CASE WHEN Header.HeaderDate <= @ReportEndingDate THEN Details.Amount ELSE 0 END) AS AmountYtd FROM Details INNER JOIN CodeIDTable2 ON CodeIDTable2.CodeID = Details.CodeID INNER JOIN Header ON Details.HeaderID = Header.HeaderID WHERE Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate GROUP BY Header.EmployeeID ) AS SummaryWHERE EmployeeSummary.EmployeeId = Summary.EmployeeIDUPDATE EmployeeSummarySET Code3PeriodToDate = Summary.AmountPeriod ,Code3YearToDate = Summary.AmountYtdFROM ( SELECT Header.EmployeeID ,SUM(CASE WHEN Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN Details.Amount ELSE 0 END) AS AmountPeriod ,SUM(CASE WHEN Header.HeaderDate <= @ReportEndingDate THEN Details.Amount ELSE 0 END) AS AmountYtd FROM Details INNER JOIN CodeIDTable3 ON CodeIDTable3.CodeID = Details.CodeID INNER JOIN Header ON Details.HeaderID = Header.HeaderID WHERE Header.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate GROUP BY Header.EmployeeID ) AS SummaryWHERE EmployeeSummary.EmployeeId = Summary.EmployeeIDSELECT * FROM EmployeeSummary[/code]

FOR XML and trying to avoid nested cursors

Posted: 02 Mar 2013 05:03 PM PST

Alright, history first. I've got a bit of a mess I've inherited. Groups inheriting groups inheriting... you get the drift. Each of these groups have external attributes that also need to be considered, but I'm not at that point yet. Right now, I'm trying to get a collar on the group hierarchy itself. Antiques littered with obsoletes have caused a reality that the business is just creating new groups to avoid the pitfalls that any old group my bring up.This is primarily because the vendor app can't find a good way to display the hierarchy to the users. Avoiding that entire discussion, I'm trying to build something that I can personally use to troubleshoot with, and eventually put into a position that my business users can troubleshoot themselves.So, what's the deal? I've got 'n' level hierarchies in this mess with a theoretical lack of an upper bound. Realistically it's seven because the software will otherwise crap out but I don't trust the historicals. There's more than likely forgotten hierarchies stuffed in there somewhere. Add to this the hierarchy is NOT org-chart particular. A subgroup can belong to multiple parents. If anything this is similar to a Bill of Materials chart. IE: The wheels go on both cars and trucks, the wheels have multiple components, one of which is bolts, and bolts go all over the danged place. And you can't trust a particular component to always exist at a particular tier of the hierarchy.My current intent is to build myself an XML document I can stuff into IE and collapse/expand/search nodes to figure out what all is affected when a particular group is selected. See, whenever you select one, it unrolls in the app to all underlying levels. So I need to be able to get a full node list of all the underlying pieces.That XML is killing me, because it pivots EVERYTHING to be usable as a node tree. I'm hoping you guys can give me a bit of a hand.First, some sample data to work with:[code="sql"]IF OBJECT_ID('tempdb..#RecursiveGroupFinding') IS NOT NULL DROP TABLE #RecursiveGroupFindingIF OBJECT_ID('tempdb..#GroupList') IS NOT NULL DROP TABLE #GroupListCREATE TABLE #RecursiveGroupFinding (ParentID INT NOT NULL, ChildID INT NOT NULL )CREATE TABLE #GroupList (GroupID INT IDENTITY(1,1) NOT NULL, GroupName VARCHAR(30) NOT NULL )CREATE CLUSTERED INDEX idx_c_RecursiveGroupFinding ON #RecursiveGroupFinding (ParentID, ChildID)CREATE CLUSTERED INDEX idx_c_GroupList ON #GroupList (GroupID)INSERT INTO #GroupListVALUES ('Parent1'),('Parent2'),('Child1'),('Child2'),('Child3'),('SubChild1'),('Subchild2'),('Subchild3'),('Icing')INSERT INTO #RecursiveGroupFindingVALUES ( 1, 3), (1, 4), (2, 3),(2,5), (3, 6), (3, 7), (4, 8), (6, 9), (7,9)[/code]Simple enough, you get this as a result list:[code="sql"]SELECT g.GroupName AS Parent, g2.GroupName AS ChildFROM #RecursiveGroupFinding AS gf JOIN #GroupList AS g ON gf.ParentID = g.GroupID JOIN #GroupList AS g2 ON gf.ChildID = g2.GroupID[/code][code="plain"]Parent1 Child1Parent1 Child2Parent2 Child1Parent2 Child3Child1 SubChild1Child1 Subchild2Child2 Subchild3SubChild1 IcingSubchild2 Icing[/code]Now, the code I've got so far illustrates a few of the uglies I'm arguing with:[code="sql"]SELECT RootLevel.GroupName, Tier1.GroupName AS T1GroupName, Tier2.GroupName AS T2GroupName, Tier3.GroupName AS T3GroupName, Tier4.GroupName AS T4GroupNameFROM (SELECT g.GroupID, g.GroupName FROM #GroupList AS g LEFT JOIN #RecursiveGroupFinding AS gf ON g.GroupID = gf.ChildID WHERE gf.ChildID IS NULL ) AS RootLevel OUTER APPLY (SELECT g.GroupID, g.GroupName FROM #GroupList AS g JOIN #RecursiveGroupFinding AS gf ON g.GroupID = gf.ChildID WHERE gf.ParentID = RootLevel.GroupID ) AS Tier1 OUTER APPLY (SELECT g.GroupID, g.GroupName FROM #GroupList AS g JOIN #RecursiveGroupFinding AS gf ON g.GroupID = gf.ChildID WHERE gf.ParentID = Tier1.GroupID ) AS Tier2 OUTER APPLY (SELECT g.GroupID, g.GroupName FROM #GroupList AS g JOIN #RecursiveGroupFinding AS gf ON g.GroupID = gf.ChildID WHERE gf.ParentID = Tier2.GroupID ) AS Tier3 OUTER APPLY (SELECT g.GroupID, g.GroupName FROM #GroupList AS g JOIN #RecursiveGroupFinding AS gf ON g.GroupID = gf.ChildID WHERE gf.ParentID = Tier3.GroupID ) AS Tier4FOR XML AUTO[/code]There are rCTE methods out there for BoM I can use but they 'stack' the results. Pivoting ends up looking like this. I'm not AGAINST a pivot per se, but the part I can't seem to kick is the results that look like this:[code="other"] [/code]Please note all the extraneous tiers because of non-existant data but columns needing to exist."Craig, get to the question!!" Errr, yeah, sorry, rambling a bit. Was hoping as I typed this out the answer would come to me, but it hasn't. Here's what I'd like to do:1) Turn the above node list into Parent1Child1 (etc...). Note this is impossible with named columns in the select list.2) Make this n-tier recursion.3) Remove extraneous tier levels.4) Avoid my last recourse... recursive cursors.The only solution I can see to this is nesting cursors via proc executions and passing a VARCHAR(MAX) around to build out the XML exactly as I want it.I'm rather open to suggestions on avoiding that... Also, if my google-fu has just failed me utterly (Bill of Materials XML Node list being one of my search patterns) please point me in the right direction with a simple lmgtfy. I can find plenty of VB/C# code to get the result I want, but I can't find anything at the proc level and I'd rather not have to dump this entire tableset through the pipes to a front end for them to bubblesort (orwhatever) the records together to build the hierarchy... I'm also not [i]that [/i]good at it and I don't have an app coder to spare.

sysjobsteps.last_run_outcome = 0 or is it?

Posted: 12 Mar 2013 07:40 AM PDT

I'm trying to create some code to quickly run on each server to get a listing of failed jobs currently on that particular server.The code so far is as follows:[code="sql"]SELECT DISTINCT(sj.name), sjs.last_run_outcome, sjh.run_statusFROM msdb.dbo.sysjobs AS SJ INNER JOIN msdb.dbo.sysjobhistory AS SJHON SJ.job_id = SJH.job_idINNER JOIN msdb.dbo.sysjobsteps AS SJSON sj.job_id = SJS.job_idWHERESJS.last_run_outcome = 0ANDSJH.run_status = 0[/code]The code runs but in my case on my test server I get 3 results back showing 0's in the "[b]last_run_outcome[/b]" column & the "[b]run_status[/b]" column.The thing is...when I look at the Job Activity Monitor, it shows "[b]Succeeded[/b]" under the "[b]Last Run Outcome[/b]" column for those 3 particular jobs.Am I doing something wrong? Querying the wrong table?Thanks!

Trigger with computed column criteria

Posted: 12 Mar 2013 05:53 AM PDT

Would a update after trigger work if the where clause criteria contains a computed column? The computed column is persisted.

Split a String

Posted: 12 Mar 2013 01:57 AM PDT

Hello EveryoneI am working on some old data that should not be store this way, but it is.My data is two values with pipe separators.[code="sql"]10.0||14.5or2||34or7.1||19or4||11.7[/code]I need to query this column and get each value separately. I have no idea how to go about this. There are always double pipe in the middle. But as you can see, there may or may not be decimal values in each. I think that is what is throwing me off. When perhaps, it really may not matter about the actual values.I appreciate any and all assistance, suggestions and commentsAndrew SQLDBA
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog