June 28, 2017 at 7:08 am
Jeff Moden - Wednesday, June 28, 2017 6:55 AMHeh... what's the last cursor you wrote and what did it do?
I can answer!
It was uploading set of order files.
And there was another one - generating set of XML reports.
Gosh, I feel so blessed right now to be on another side of the Globe!
Even Golden Eye won't find me here.
_____________
Code for TallyGenerator
June 28, 2017 at 7:30 am
Sergiy - Wednesday, June 28, 2017 7:08 AMJeff Moden - Wednesday, June 28, 2017 6:55 AMHeh... what's the last cursor you wrote and what did it do?
I can answer!
It was uploading set of order files.
And there was another one - generating set of XML reports.Gosh, I feel so blessed right now to be on another side of the Globe!
Even Golden Eye won't find me here.
This morning. Because the business still thinks SQL Server is a glorified (and expensive) mail merge tool.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 28, 2017 at 7:55 am
Thom A - Wednesday, June 28, 2017 7:30 AMSergiy - Wednesday, June 28, 2017 7:08 AMJeff Moden - Wednesday, June 28, 2017 6:55 AMHeh... what's the last cursor you wrote and what did it do?
I can answer!
It was uploading set of order files.
And there was another one - generating set of XML reports.Gosh, I feel so blessed right now to be on another side of the Globe!
Even Golden Eye won't find me here.This morning. Because the business still thinks SQL Server is a glorified (and expensive) mail merge tool.
Just now. I had to add up all the prices we charged for the items for the last month.
So I put it into a cursor that took each price, one by one, and kept adding them into a variable.
I then created a second cursor that took the quantities for each item and added them.
Then, I took those two values, and divided them after I converted them to a float to get the average price for each item.
Then, I rounded the results to get to 2 decimal places.
Just kidding.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 28, 2017 at 8:09 am
Jeff Moden - Wednesday, June 28, 2017 6:55 AMBrandie Tarvin - Wednesday, June 28, 2017 4:51 AMjonathan.crawford - Tuesday, June 27, 2017 9:01 AMGreat. Jeff's moved on from pork chop launchers to "nuke it from orbit". Nobody write any cursors!
HA! I defy your directive and write one anyway!
(Cursors do have their place, ya know).
Heh... what's the last cursor you wrote and what did it do?
Just a few months ago, I wrote a cursor for a SQL Agent job to take database backups. Yes, a cursor for backing up databases.
Our situation, inherited from corporate after they switched to deduplication technology for most of the servers, is that each server instance has its own folder on a locked snapshotted NAS where the folder structure consists of InstanceName -> DatabaseName -> DatedFolder (with _FULL on it if it's the day of a FULL backup, otherwise just the date). Differentials happen on the non-FULL backup days and transaction logs go in the dated folders.
The cursor also creates the db and dated folders if they don't already exist by the time the backup runs. It's actually pretty elegant all things considered. Especially as the native backup technology to SQL Server doesn't quite adhere to the folder structure mandated by corporate rules.
June 28, 2017 at 9:05 am
Jeff Moden - Wednesday, June 28, 2017 6:55 AMBrandie Tarvin - Wednesday, June 28, 2017 4:51 AMjonathan.crawford - Tuesday, June 27, 2017 9:01 AMJeff Moden - Monday, June 26, 2017 6:44 PMEric M Russell - Saturday, June 24, 2017 8:24 PMSo, did anyone else have a strangely intense dream recently about being, outside looking up at the night sky, and then a constellation of stars coalesce into a UFO that lands nearby?Crud. Sorry. Didn't mean for you to see me.
Great. Jeff's moved on from pork chop launchers to "nuke it from orbit". Nobody write any cursors!
HA! I defy your directive and write one anyway!
(Cursors do have their place, ya know).
Heh... what's the last cursor you wrote and what did it do?
Every tool in SQL Server has its use cases; there are no bad tools. Well, except auto-shrink.
I wrote two cursors in the last year, both for the same subsystem. This subsystem reads files with products, stock and pricing from various vendors and stores the data in two tables: headers (one per file) and lines (one per line). Files can be as small as a few dozen lines, or as big as 100K lines, Some files are imported daily, others up to 24x per day.
Cursor 1: Iterates over the headers. For each header, determine the type of purging allowed (there are three versions), delete lines that can be purged, then update the linecount in the header. The deletion could easily have been done in a single set-based query (well, three queries), but the amount of rows affected would bloat the log so I'd have to add chunking. And then the update of the headers to set the correct linecount would become much more complex. The cursor makes the code easier, gives me chunking for free, and it performs in less then 1% of the available maintenance window.
Cursor 2: Because all searches in the imported data are based on substrings (LIKE '%' + @Search + '%'), they are slow. So we cache search results. If the same search string is reused, we simply get the results from a cache table. After business hours, a process starts that uses a cursor to loop over search strings (in order of relevance, based on how often and how long ago the search string was used) and refreshes the search results based on the then current data. Half an hour before start of business the process aborts and any remaining cached search results are thrown out to prevent serving outdated results. The cursor here gives me a very easy way to interrupt the process when it runs out of time, and to ensure that the most relevant search terms are refreshed first.
June 28, 2017 at 10:20 am
So much fun figuring out how to do something in a more "elegant" and "scalable" way.
I've been capturing the Transactions/sec counter to a table for one database for a while now, to get a feel for how "busy" it is for the customer (also capturing Batch Requests/sec for the server,) and today I decided I wanted to be able to compare to other databases on the same server, but without having one table for each database...
I *thought* I might be able to use a windowing function to pull the data out of the table in my SSRS report and happily I was right.
Now I'm much happier with the query, the report lets me pick and choose which database I want to look at (hmm, I wonder if I could do a checkbox select for the graph and show multiple DBs at once?) so I've got a way to compare them to each other.
It's the little things that make me happy...
June 28, 2017 at 12:23 pm
Michael L John - Wednesday, June 28, 2017 7:55 AMThom A - Wednesday, June 28, 2017 7:30 AMSergiy - Wednesday, June 28, 2017 7:08 AMJeff Moden - Wednesday, June 28, 2017 6:55 AMHeh... what's the last cursor you wrote and what did it do?
I can answer!
It was uploading set of order files.
And there was another one - generating set of XML reports.Gosh, I feel so blessed right now to be on another side of the Globe!
Even Golden Eye won't find me here.This morning. Because the business still thinks SQL Server is a glorified (and expensive) mail merge tool.
Just now. I had to add up all the prices we charged for the items for the last month.
So I put it into a cursor that took each price, one by one, and kept adding them into a variable.
I then created a second cursor that took the quantities for each item and added them.
Then, I took those two values, and divided them after I converted them to a float to get the average price for each item.
Then, I rounded the results to get to 2 decimal places.Just kidding.
Because I know you, I think you'll appreciate these two points:
1. I certainly hope you're kidding. The second cursor should have been embedded inside the first cursor.
2. If you'd been thinking ahead, you would have written the logic into a trigger on the sales order lines table and accumulated it there...with a cursor, of course.
😛
June 28, 2017 at 12:48 pm
Ed Wagner - Wednesday, June 28, 2017 12:23 PMMichael L John - Wednesday, June 28, 2017 7:55 AMThom A - Wednesday, June 28, 2017 7:30 AMSergiy - Wednesday, June 28, 2017 7:08 AMJeff Moden - Wednesday, June 28, 2017 6:55 AMHeh... what's the last cursor you wrote and what did it do?
I can answer!
It was uploading set of order files.
And there was another one - generating set of XML reports.Gosh, I feel so blessed right now to be on another side of the Globe!
Even Golden Eye won't find me here.This morning. Because the business still thinks SQL Server is a glorified (and expensive) mail merge tool.
Just now. I had to add up all the prices we charged for the items for the last month.
So I put it into a cursor that took each price, one by one, and kept adding them into a variable.
I then created a second cursor that took the quantities for each item and added them.
Then, I took those two values, and divided them after I converted them to a float to get the average price for each item.
Then, I rounded the results to get to 2 decimal places.Just kidding.
Because I know you, I think you'll appreciate these two points:
1. I certainly hope you're kidding. The second cursor should have been embedded inside the first cursor.
2. If you'd been thinking ahead, you would have written the logic into a trigger on the sales order lines table and accumulated it there...with a cursor, of course.😛
Stored in a float.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 28, 2017 at 12:57 pm
Jeff Moden - Wednesday, June 28, 2017 6:55 AMHeh... what's the last cursor you wrote and what did it do?
I am hoping I can learn from this one but I wrote one recently for changing all job owners to be sa instead of me.
Our IT team rebooted one of the domain controllers and SQL got grumpy about running any jobs because I was the owner of some of them.
Instead of going into each and every job on each and every server and changing it, used a cursor (appropriately named cursewords) to get the job_id and generate the script for me to run.
Could have tossed that into an "exec", but there were 2 servers I didn't want to accidentally run it on as I didn't know for sure what would all break (our SSRS instance and our financial instance).
I am thinking that is an acceptable use of a cursor, no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 28, 2017 at 2:18 pm
Ed Wagner - Wednesday, June 28, 2017 12:23 PMBecause I know you, I think you'll appreciate these two points:1. I certainly hope you're kidding. The second cursor should have been embedded inside the first cursor.
2. If you'd been thinking ahead, you would have written the logic into a trigger on the sales order lines table and accumulated it there...with a cursor, of course.😛
No. That trigger would simply load some variables from the inserted pseudo table. And then when you notice the failure on multi-column inserts, you would add an instead of trigger to capture the original change, amd cursor over it to materialize the changes one by one.
(And I so hope I don't need to do this over here but I am not taking any risks: none of this is to be taken serious)
June 28, 2017 at 2:31 pm
Hugo Kornelis - Wednesday, June 28, 2017 2:18 PMEd Wagner - Wednesday, June 28, 2017 12:23 PMBecause I know you, I think you'll appreciate these two points:1. I certainly hope you're kidding. The second cursor should have been embedded inside the first cursor.
2. If you'd been thinking ahead, you would have written the logic into a trigger on the sales order lines table and accumulated it there...with a cursor, of course.😛
No. That trigger would simply load some variables from the inserted pseudo table. And then when you notice the failure on multi-column inserts, you would add an instead of trigger to capture the original change, amd cursor over it to materialize the changes one by one.
(And I so hope I don't need to do this over here but I am not taking any risks: none of this is to be taken serious)
But Hugo told me this was the way I should do it!!! :w00t:
_______________________________________________________________
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/
June 28, 2017 at 2:53 pm
Jeff Moden - Wednesday, June 28, 2017 6:55 AMHeh... what's the last cursor you wrote and what did it do?
unfortunately had to write one this week... look through rows of a document table for the last 48 hours and compare the FileName column to the results of xp_fileexist to try to find missing documents (without waiting for the users to say "hey... the file doesn't open!")
June 29, 2017 at 4:14 am
Chris Harshman - Wednesday, June 28, 2017 2:53 PMJeff Moden - Wednesday, June 28, 2017 6:55 AMHeh... what's the last cursor you wrote and what did it do?unfortunately had to write one this week... look through rows of a document table for the last 48 hours and compare the FileName column to the results of xp_fileexist to try to find missing documents (without waiting for the users to say "hey... the file doesn't open!")
Okay, I'm going to pretend to be a little obtuse, but couldn't you run the results of xp_fileexists into a temp table, pull the document table into another temp table with a "FileExists" bit column set to 0 (or 1 if you prefer your falses to be 1), then update that column in a set-based update for all file names that match?
June 29, 2017 at 5:16 am
Sean Lange - Wednesday, June 28, 2017 2:31 PMHugo Kornelis - Wednesday, June 28, 2017 2:18 PMEd Wagner - Wednesday, June 28, 2017 12:23 PMBecause I know you, I think you'll appreciate these two points:1. I certainly hope you're kidding. The second cursor should have been embedded inside the first cursor.
2. If you'd been thinking ahead, you would have written the logic into a trigger on the sales order lines table and accumulated it there...with a cursor, of course.😛
No. That trigger would simply load some variables from the inserted pseudo table. And then when you notice the failure on multi-column inserts, you would add an instead of trigger to capture the original change, amd cursor over it to materialize the changes one by one.
(And I so hope I don't need to do this over here but I am not taking any risks: none of this is to be taken serious)
But Hugo told me this was the way I should do it!!! :w00t:
Twice! 😉
June 29, 2017 at 9:31 am
Chris Harshman - Wednesday, June 28, 2017 2:53 PMJeff Moden - Wednesday, June 28, 2017 6:55 AMHeh... what's the last cursor you wrote and what did it do?unfortunately had to write one this week... look through rows of a document table for the last 48 hours and compare the FileName column to the results of xp_fileexist to try to find missing documents (without waiting for the users to say "hey... the file doesn't open!")
Just thinking that there may be a better way but would need more details.
Viewing 15 posts - 59,056 through 59,070 (of 66,549 total)
You must be logged in to reply to this topic. Login to reply