August 20, 2015 at 7:46 am
Someone who's more familiar with mirroring please have a look here: http://www.sqlservercentral.com/Forums/Topic1713336-2799-1.aspx
Does not look right to me that a mirrored DB isn't in the system views (I wonder if she's connected to the wrong instance perhaps)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2015 at 8:13 am
Sean Lange (8/20/2015)
Ed Wagner (8/20/2015)
Sean Lange (8/20/2015)
Agreed 100% that the other way should be an issue but seriously...the best part was I spent the better part of an hour trying to figure out how I was going to deal with a datetime to date conversion because the source is 2005 and as such can't cast as date. Only to find out that in that case it will do implicit conversion. GAH!!!Now that part is kinda funny. Not that you spent an hour on it, but that it'll do an implicit cast sometimes but not all the time.
Granted, I don't know much about SSIS other than how to spell it...and sometimes I get that part wrong. 😛
Personally, I do my ETL using T-SQL. I know that doesn't help me learn anything new, but it's fast, reliable and straightforward. There's also the issue of familiarity. Gee...I hope I'm not becoming old and stodgy. 😉
I typically do it using t-sql myself but in this case SSIS just makes sense because the data is coming from so many places. As SSIS packages go this is one is super simple. I am grateful for the chance to learn some stuff if only because I have much more knowledge about what the people who do this all the time (i.e. Koen) have to work with on a routine basis. The flexibility offered is a LONG way from DTS and it is all more intuitive.
Well, as long as I don't travel any further down the road. Then again, I want to stay on the road, as I don't like the alternative. :hehe:
August 20, 2015 at 8:17 am
Jack Corbett (8/20/2015)
Sean Lange (8/20/2015)
I typically do it using t-sql myself but in this case SSIS just makes sense because the data is coming from so many places. As SSIS packages go this is one is super simple. I am grateful for the chance to learn some stuff if only because I have much more knowledge about what the people who do this all the time (i.e. Koen) have to work with on a routine basis. The flexibility offered is a LONG way from DTS and it is all more intuitive.I agree with pretty much all you have to say here. If I can use T-SQL to do the task I use it because I can usually develop it faster and, often times, it will run faster because I take advantage of set-based processing, but when you have multiple sources of data and/or multiple stages your data needs to pass through I find SSIS is a better solution, even if it is just being used as a controller.
Precisely. I've compared the performance of SSIS packages (written by someone who knows what they're doing) against a T-SQL procedure and I found that the procedure wins. It was a simple one - FTP download, import, validate and write to reporting tables. I also had the control of T-SQL for error-handling and my development time was less. Both will get the job done, though.
August 20, 2015 at 12:01 pm
Lynn Pettis (8/19/2015)
Silver spoon, silver spoon, silver spoon, when are you going to admit you are in over your head and really need to find a different career.
I was really surprised with this post and then by the questions made by this person in previous threads.
http://www.sqlservercentral.com/Forums/Topic1713458-3077-1.aspx
August 20, 2015 at 12:17 pm
Lynn Pettis (8/19/2015)
And then there are those Oracle people seeking help on a MS SQL Server forum and seem surprised that a T-SQL solution won't work on an Oracle server.
Lynn, it was great to finally get to meet you in person last night at the Colorado Springs SSUG. 'Hope to see you there again. By the way, Paul Nielsen's a great speaker too. We're lucky he's around.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
August 20, 2015 at 12:28 pm
mtillman-921105 (8/20/2015)
Lynn Pettis (8/19/2015)
And then there are those Oracle people seeking help on a MS SQL Server forum and seem surprised that a T-SQL solution won't work on an Oracle server.Lynn, it was great to finally get to meet you in person last night at the Colorado Springs SSUG. 'Hope to see you there again. By the way, Paul Nielsen's a great speaker too. We're lucky he's around.
It was nice meeting you as well. I wish I could have stayed for the presentation last night.
August 20, 2015 at 12:51 pm
Luis Cazares (8/20/2015)
Lynn Pettis (8/19/2015)
Silver spoon, silver spoon, silver spoon, when are you going to admit you are in over your head and really need to find a different career.I was really surprised with this post and then by the questions made by this person in previous threads.
http://www.sqlservercentral.com/Forums/Topic1713458-3077-1.aspx
The urge to pile on for that question was strong. So sad they have to post how to "fix" that query. Obvious they have absolutely no idea how the current query works at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 21, 2015 at 1:54 am
Sean Lange (8/20/2015)
...Obvious they have absolutely no idea how the current query works at all.
About sums me up with queries I write :hehe: 😀
Does that mean you won't help me or will I get piled on 😛
Far away is close at hand in the images of elsewhere.
Anon.
August 21, 2015 at 2:05 am
Sean Lange (8/20/2015)
Luis Cazares (8/20/2015)
Lynn Pettis (8/19/2015)
Silver spoon, silver spoon, silver spoon, when are you going to admit you are in over your head and really need to find a different career.I was really surprised with this post and then by the questions made by this person in previous threads.
http://www.sqlservercentral.com/Forums/Topic1713458-3077-1.aspx
The urge to pile on for that question was strong. So sad they have to post how to "fix" that query. Obvious they have absolutely no idea how the current query works at all.
There have been a few of those this week. The temptation to say "You really don't have a clue how this works do you?" can become overwhelming. Just think back to when you were constructing some C# to pick up filenames in a SSIS package and how awful and humbling it felt to be a noob again.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 21, 2015 at 2:26 am
ChrisM@Work (8/21/2015)
Sean Lange (8/20/2015)
Luis Cazares (8/20/2015)
Lynn Pettis (8/19/2015)
Silver spoon, silver spoon, silver spoon, when are you going to admit you are in over your head and really need to find a different career.I was really surprised with this post and then by the questions made by this person in previous threads.
http://www.sqlservercentral.com/Forums/Topic1713458-3077-1.aspx
The urge to pile on for that question was strong. So sad they have to post how to "fix" that query. Obvious they have absolutely no idea how the current query works at all.
There have been a few of those this week. The temptation to say "You really don't have a clue how this works do you?" can become overwhelming. Just think back to when you were constructing some C# to pick up filenames in a SSIS package and how awful and humbling it felt to be a noob again.
It's okay to be a n00b if you're new at something, but please do a bit of research before asking questions on a forum.
If he would have read his query, that filter in the where clause would have stood out a bit. If he didn't understand it, googling dateadd wouldn't be that hard.
I learned myself T-SQL, C#, VB and Biml, and I can count the number of forum questions on one hand.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 21, 2015 at 4:47 am
Sean Lange (8/20/2015)
Luis Cazares (8/20/2015)
Lynn Pettis (8/19/2015)
Silver spoon, silver spoon, silver spoon, when are you going to admit you are in over your head and really need to find a different career.I was really surprised with this post and then by the questions made by this person in previous threads.
http://www.sqlservercentral.com/Forums/Topic1713458-3077-1.aspx
The urge to pile on for that question was strong. So sad they have to post how to "fix" that query. Obvious they have absolutely no idea how the current query works at all.
I actually decided to chime in precisely because that query is such a mess. I get the need for 1 subquery, but not for 3 of them. Not for the results the OP is looking for.
I want to see how the OP responds before I post my advice on a fix, but at risk of blowing up The Thread, I thought I'd throw it here and see what y'all think. Is it still too complicated? Does it give you the same results as the OP's code or different results?
SELECT @@SERVERNAME, sj.name,
CASE sjh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled'
WHEN 4 THEn 'In Progress' END AS RunStatus,
sjh.step_name, sjs.output_file_name
FROM msdb.dbo.sysjobhistory sjh
INNER JOIN (SELECT job_id, step_id, MAX(instance_id) AS Instance_ID
FROM msdb.dbo.sysjobhistory
GROUP BY job_id, step_id) sjh2
ON sjh.instance_id = sjh2.Instance_ID
INNER JOIN msdb.dbo.sysjobsteps sjs
ON sjh.job_id = sjs.job_id
AND sjh.step_id = sjs.step_id
INNER JOIN msdb.dbo.sysjobs sj
ON sjh.job_id = sj.job_id
WHERE sjh.run_status IN (0,2,3,4)
AND CONVERT(DATETIME,(SUBSTRING(CONVERT(CHAR(8),(sjh.run_date)),5,2) + '/'
+ SUBSTRING(CONVERT(CHAR(8),(sjh.run_date)),7,2) + '/'
+ SUBSTRING(CONVERT(CHAR(8),(sjh.run_date)),1,4))) >= DATEADD(DAY,-1, GETDATE());
August 21, 2015 at 5:29 am
Haven't been following that thread, but I do have some suggestions for your query. Here's mine, derived from yours.
select ServerName = @@SERVERNAME
,JobName = sj.name
,rs.RunStatus
,sjh.step_name
,sjs.output_file_name
from msdb.dbo.sysjobhistory sjh
join (select job_id
,step_id
,max(instance_id) as Instance_ID
from msdb.dbo.sysjobhistory
group by job_id
,step_id
) sjh2 on sjh.instance_id = sjh2.Instance_ID
join msdb.dbo.sysjobsteps sjs on sjh.job_id = sjs.job_id
and sjh.step_id = sjs.step_id
join msdb.dbo.sysjobs sj on sjh.job_id = sj.job_id
cross apply (select RunStatus = case sjh.run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In Progress'
end
) rs
where sjh.run_status in (0, 2, 3, 4)
and sjh.run_date >= (year(getdate()) * 10000 + month(getdate()) * 100 + day(getdate()) - 1);
Most importantly is avoidance of the run_date cast. As we are using YYYYMMDD int format, the simple -1 works and we don't lose the possibility of an index being used.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 21, 2015 at 5:32 am
Phil Parkin (8/21/2015)
Most importantly is avoidance of the run_date cast. As we are using YYYYMMDD int format, the simple -1 works and we don't lose the possibility of an index being used.
I also haven't been following the thread, but if the requirement is for the previous day's jobs, then the -1 will work for every day except the first of the month.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2015 at 5:45 am
Ah yes, tried to take a sneaky shortcut. Needs to be fixed:
and sjh.run_date >= (year(getdate() - 1) * 10000 + month(getdate() - 1) * 100 + day(getdate() - 1));
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 21, 2015 at 6:01 am
Phil Parkin (8/21/2015)
Ah yes, tried to take a sneaky shortcut. Needs to be fixed:and sjh.run_date >= (year(getdate() - 1) * 10000 + month(getdate() - 1) * 100 + day(getdate() - 1));
That's an interesting way of dealing with the date stuff in the sys tables. I hadn't thought about that. Thanks, Phil.
Viewing 15 posts - 50,296 through 50,310 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply