September 6, 2017 at 6:15 am
Neil Burton - Wednesday, September 6, 2017 2:12 AMThom A - Wednesday, September 6, 2017 1:57 AMNeil Burton - Wednesday, September 6, 2017 1:02 AMQuick techie question to see if something warrants further investigation: does anybody know if writing back to the database from an SSRS report is supported? Er, asking for a friend.Depends what you mean by write back. I've used a report with hyperlinks on it, which reloaded the report with an additional parameter. That parameter, when passed, updated the specific record in its value with a the next step in the status chain. I.e. Pending -> Processing -> Completed. It wasn't really the tool for the job, but it solved the problem (much like a hammer could get a screw in a hole 🙂 )
We have a data warehouse we provide to a third party. They have a series of reports on their own database on our server (not ideal but politics) and they use some views on our database to build these reports. Some of these reports take a user parameter and write to other tables in their database so they can record the reason a task was or wasn't completed. Yesterday our data warehouse ran long because a user had an open transaction locking one of the fact tables. My theory is that it was one of these write-back reports of theirs that caused it. If I remember correctly, if you are updating a table from a view, locks are taken on the underlying tables until the update is committed. If the transaction is not committed the locks will stay in place. We're now looking at ways of preventing this happening again and one of the options is telling them not to do it. If could say that writing to databases from SSRS is not supported by Microsoft then we'd be in a stronger position.
You'll have to (obviously) consider SQL Injection, but I did a proof of concept to do just exactly what you're suggesting, works fine. I was completely unhappy with trying this without creating an audit table that captured every change, userids and stamps of when it happened, because you're necessarily exposing the record to modification just by returning it to the report. PM me if you want an example
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
September 6, 2017 at 6:15 am
Brandie Tarvin - Wednesday, September 6, 2017 6:04 AMSo... I'm not going to be around as much as usual over the next couple of months. I've been working on a major project over the past 8 months that (as one coworker put it) was going straight from development to production. Unfortunately, the release date happened 6 weeks earlier than expected and now I'm kind of focusing on fixing the bugs 24/7 for the next 8+ weeks. I hope y'all don't fall apart without me. @=)BTW, the project I'm talking about? It had to do with an inadvertent use of DBCC TIMEWARP() on a pregnancy. Baby says "hi" or would if he could do more than gurgle at the moment.
What the heck did I just get myself into? SIGH. Please be careful with the use of that function. Apparently it has side effects no one warned me about. :crazy:
CONGRATULATIONS!
Just wait until the first time the kid decides to paint the cat with house paints because they want a pink kitty instead of nice calico...
September 6, 2017 at 6:25 am
Brandie Tarvin - Wednesday, September 6, 2017 6:04 AMSo... I'm not going to be around as much as usual over the next couple of months. I've been working on a major project over the past 8 months that (as one coworker put it) was going straight from development to production. Unfortunately, the release date happened 6 weeks earlier than expected and now I'm kind of focusing on fixing the bugs 24/7 for the next 8+ weeks. I hope y'all don't fall apart without me. @=)BTW, the project I'm talking about? It had to do with an inadvertent use of DBCC TIMEWARP() on a pregnancy. Baby says "hi" or would if he could do more than gurgle at the moment.
What the heck did I just get myself into? SIGH. Please be careful with the use of that function. Apparently it has side effects no one warned me about. :crazy:
Your real life has just begun, everything up until now has been fluffy training. Congratulations, and enjoy.
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
September 6, 2017 at 6:25 am
jonathan.crawford - Wednesday, September 6, 2017 6:15 AMNeil Burton - Wednesday, September 6, 2017 2:12 AMThom A - Wednesday, September 6, 2017 1:57 AMNeil Burton - Wednesday, September 6, 2017 1:02 AMQuick techie question to see if something warrants further investigation: does anybody know if writing back to the database from an SSRS report is supported? Er, asking for a friend.Depends what you mean by write back. I've used a report with hyperlinks on it, which reloaded the report with an additional parameter. That parameter, when passed, updated the specific record in its value with a the next step in the status chain. I.e. Pending -> Processing -> Completed. It wasn't really the tool for the job, but it solved the problem (much like a hammer could get a screw in a hole 🙂 )
We have a data warehouse we provide to a third party. They have a series of reports on their own database on our server (not ideal but politics) and they use some views on our database to build these reports. Some of these reports take a user parameter and write to other tables in their database so they can record the reason a task was or wasn't completed. Yesterday our data warehouse ran long because a user had an open transaction locking one of the fact tables. My theory is that it was one of these write-back reports of theirs that caused it. If I remember correctly, if you are updating a table from a view, locks are taken on the underlying tables until the update is committed. If the transaction is not committed the locks will stay in place. We're now looking at ways of preventing this happening again and one of the options is telling them not to do it. If could say that writing to databases from SSRS is not supported by Microsoft then we'd be in a stronger position.
You'll have to (obviously) consider SQL Injection, but I did a proof of concept to do just exactly what you're suggesting, works fine. I was completely unhappy with trying this without creating an audit table that captured every change, userids and stamps of when it happened, because you're necessarily exposing the record to modification just by returning it to the report. PM me if you want an example
It's not something I want to do, personally I think it's a horrible idea for many reasons, not least SQL injection. I know it can be done, I'd just rather it wasn't. Having some kind of official line against it would be ammunition in the discussion with the customer about how they're not going to break our databases in future. There's all kinds of politics involved in this sh!tpile and the upshot is I'll probably have to make changes to my data warehouse to allow their idiots to do stuff no sensible person would want to do. Why, because that's going to cheapest!
Congratulations Brandie 🙂
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 6, 2017 at 6:34 am
Neil Burton - Wednesday, September 6, 2017 6:25 AMjonathan.crawford - Wednesday, September 6, 2017 6:15 AMNeil Burton - Wednesday, September 6, 2017 2:12 AMThom A - Wednesday, September 6, 2017 1:57 AMNeil Burton - Wednesday, September 6, 2017 1:02 AMQuick techie question to see if something warrants further investigation: does anybody know if writing back to the database from an SSRS report is supported? Er, asking for a friend.Depends what you mean by write back. I've used a report with hyperlinks on it, which reloaded the report with an additional parameter. That parameter, when passed, updated the specific record in its value with a the next step in the status chain. I.e. Pending -> Processing -> Completed. It wasn't really the tool for the job, but it solved the problem (much like a hammer could get a screw in a hole 🙂 )
We have a data warehouse we provide to a third party. They have a series of reports on their own database on our server (not ideal but politics) and they use some views on our database to build these reports. Some of these reports take a user parameter and write to other tables in their database so they can record the reason a task was or wasn't completed. Yesterday our data warehouse ran long because a user had an open transaction locking one of the fact tables. My theory is that it was one of these write-back reports of theirs that caused it. If I remember correctly, if you are updating a table from a view, locks are taken on the underlying tables until the update is committed. If the transaction is not committed the locks will stay in place. We're now looking at ways of preventing this happening again and one of the options is telling them not to do it. If could say that writing to databases from SSRS is not supported by Microsoft then we'd be in a stronger position.
You'll have to (obviously) consider SQL Injection, but I did a proof of concept to do just exactly what you're suggesting, works fine. I was completely unhappy with trying this without creating an audit table that captured every change, userids and stamps of when it happened, because you're necessarily exposing the record to modification just by returning it to the report. PM me if you want an example
It's not something I want to do, personally I think it's a horrible idea for many reasons, not least SQL injection. I know it can be done, I'd just rather it wasn't. Having some kind of official line against it would be ammunition in the discussion with the customer about how they're not going to break our databases in future. There's all kinds of politics involved in this sh!tpile and the upshot is I'll probably have to make changes to my data warehouse to allow their idiots to do stuff no sensible person would want to do. Why, because that's going to cheapest!
Congratulations Brandie 🙂
Thanks...
BTW, aren't reports supposed to "report"? As in the idea is that applications change data, reports just tell you the state of the data. That's the way I've always seen the issue. It doesn't make sense to have a report that does application work.
September 6, 2017 at 6:46 am
Brandie Tarvin - Wednesday, September 6, 2017 6:04 AMSo... I'm not going to be around as much as usual over the next couple of months. I've been working on a major project over the past 8 months that (as one coworker put it) was going straight from development to production. Unfortunately, the release date happened 6 weeks earlier than expected and now I'm kind of focusing on fixing the bugs 24/7 for the next 8+ weeks. I hope y'all don't fall apart without me. @=)BTW, the project I'm talking about? It had to do with an inadvertent use of DBCC TIMEWARP() on a pregnancy. Baby says "hi" or would if he could do more than gurgle at the moment.
What the heck did I just get myself into? SIGH. Please be careful with the use of that function. Apparently it has side effects no one warned me about. :crazy:
Congratulations, Brandie! I hope you're both doing well and recovering from the "release to production" that came early. Even though you're going to be sleep deprived for a while, enjoy this time. It won't seem like it now, but you'll look back on it and wonder how it went so fast. Life is about to become even more rewarding. Again, congratulations.
September 6, 2017 at 7:07 am
Brandie Tarvin - Wednesday, September 6, 2017 6:04 AMSo... I'm not going to be around as much as usual over the next couple of months. I've been working on a major project over the past 8 months that (as one coworker put it) was going straight from development to production. Unfortunately, the release date happened 6 weeks earlier than expected and now I'm kind of focusing on fixing the bugs 24/7 for the next 8+ weeks. I hope y'all don't fall apart without me. @=)BTW, the project I'm talking about? It had to do with an inadvertent use of DBCC TIMEWARP() on a pregnancy. Baby says "hi" or would if he could do more than gurgle at the moment.
What the heck did I just get myself into? SIGH. Please be careful with the use of that function. Apparently it has side effects no one warned me about. :crazy:
CONGRATS!!!! Life has gotten interesting now. We didn't have kids until we were older than most parents and my only regret is not doing it 10 years earlier. Such a huge change in your lifestyle and how you view the world. Enjoy the little one!!!
_______________________________________________________________
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/
September 6, 2017 at 7:08 am
Hey guys, a bunch of you are older and a bunch of you are SF fans, so I'm hoping the Venn touches.
There was a book from the 70s called Highways or Exits or something like that. It had a pickup truck on the cover. It was about a road that if you could find the entrance to allowed you to travel through time & space depending on which exit you took. One of the characters had a load of rifles he planned on delivering to the Spartans at Thermopylae as soon as he could find the right exit. Any help on the title to this please?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2017 at 7:13 am
Brandie Tarvin - Wednesday, September 6, 2017 6:04 AMSo... I'm not going to be around as much as usual over the next couple of months. I've been working on a major project over the past 8 months that (as one coworker put it) was going straight from development to production. Unfortunately, the release date happened 6 weeks earlier than expected and now I'm kind of focusing on fixing the bugs 24/7 for the next 8+ weeks. I hope y'all don't fall apart without me. @=)BTW, the project I'm talking about? It had to do with an inadvertent use of DBCC TIMEWARP() on a pregnancy. Baby says "hi" or would if he could do more than gurgle at the moment.
What the heck did I just get myself into? SIGH. Please be careful with the use of that function. Apparently it has side effects no one warned me about. :crazy:
WHOOOOOP!!!!
Congratulations! That's perfectly wonderful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2017 at 7:13 am
Ed Wagner - Wednesday, September 6, 2017 5:09 AMSean Lange - Tuesday, September 5, 2017 2:12 PMIf it has been a long time then yes the beer on this side of the pond has improved a thousand fold. There was a time all you could get was the crap pilsner that had to be served about 30F because once it warmed up to more than about 34F it started to gain flavor...and the flavor was nothing short of horrific. You definitely give some American brewers another shot. There are small batch breweries in nearly every city and many of them don't have any distribution outside of the building. That being said I am going to be trying to search out more of the European lagers to try.I am not all that surprised that finding a barman who knows how to make a Manhattan is challenging. The American palette has still not progressed greatly when it comes to traditional mixed drinks.
I'm sorry to interject an unrelated question into the beer conversation, but Sean, how did things go this past weekend. I believe you had a deployment scheduled.
Haha. We did. I came into the office for about 6 hours on Saturday and then another 4-5 hours on Sunday from home. I was able to get out and see a band Saturday night (Living End from Australia). Then I got to spend all day Monday with the family. The release of course has a lot of craziness now but nothing where the sky is falling or anything that horrific. All in all it has been pretty smooth. One of or VPs stated that it has been almost 4 years since we started this project. Our last branches in the distribution division will cut over this next Monday which will mean that our mainframe application will no longer be in use. There may be an AS400 or two on the resale market in the near future. Now if we had just chosen a better ERP to migrate too all would be good.
_______________________________________________________________
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/
September 6, 2017 at 7:17 am
Grant Fritchey - Wednesday, September 6, 2017 7:08 AMHey guys, a bunch of you are older and a bunch of you are SF fans, so I'm hoping the Venn touches.There was a book from the 70s called Highways or Exits or something like that. It had a pickup truck on the cover. It was about a road that if you could find the entrance to allowed you to travel through time & space depending on which exit you took. One of the characters had a load of rifles he planned on delivering to the Spartans at Thermopylae as soon as he could find the right exit. Any help on the title to this please?
Are you talking about Roadmarks by Roger Zelazny, the cover has a pickup truck and a flying dragon IIRC
😎
September 6, 2017 at 7:21 am
Eirikur Eiriksson - Wednesday, September 6, 2017 7:17 AMGrant Fritchey - Wednesday, September 6, 2017 7:08 AMHey guys, a bunch of you are older and a bunch of you are SF fans, so I'm hoping the Venn touches.There was a book from the 70s called Highways or Exits or something like that. It had a pickup truck on the cover. It was about a road that if you could find the entrance to allowed you to travel through time & space depending on which exit you took. One of the characters had a load of rifles he planned on delivering to the Spartans at Thermopylae as soon as he could find the right exit. Any help on the title to this please?
Are you talking about Roadmarks by Roger Zelazny, the cover has a pickup truck and a flying dragon IIRC
😎
WINNER!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 6, 2017 at 7:21 am
Brandie Tarvin - Wednesday, September 6, 2017 6:04 AMSo... I'm not going to be around as much as usual over the next couple of months. I've been working on a major project over the past 8 months that (as one coworker put it) was going straight from development to production. Unfortunately, the release date happened 6 weeks earlier than expected and now I'm kind of focusing on fixing the bugs 24/7 for the next 8+ weeks. I hope y'all don't fall apart without me. @=)BTW, the project I'm talking about? It had to do with an inadvertent use of DBCC TIMEWARP() on a pregnancy. Baby says "hi" or would if he could do more than gurgle at the moment.
What the heck did I just get myself into? SIGH. Please be careful with the use of that function. Apparently it has side effects no one warned me about. :crazy:
Congratulations! and good luck. 🙂
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 6, 2017 at 7:23 am
Brandie Tarvin - Wednesday, September 6, 2017 6:04 AMSo... I'm not going to be around as much as usual over the next couple of months. I've been working on a major project over the past 8 months that (as one coworker put it) was going straight from development to production. Unfortunately, the release date happened 6 weeks earlier than expected and now I'm kind of focusing on fixing the bugs 24/7 for the next 8+ weeks. I hope y'all don't fall apart without me. @=)BTW, the project I'm talking about? It had to do with an inadvertent use of DBCC TIMEWARP() on a pregnancy. Baby says "hi" or would if he could do more than gurgle at the moment.
What the heck did I just get myself into? SIGH. Please be careful with the use of that function. Apparently it has side effects no one warned me about. :crazy:
Heh... only someone in IT would relate in such a fashion. Brought a real smile to my face. Congratulations to you and yours, Brandie.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 6, 2017 at 7:29 am
Grant Fritchey - Wednesday, September 6, 2017 7:21 AMEirikur Eiriksson - Wednesday, September 6, 2017 7:17 AMGrant Fritchey - Wednesday, September 6, 2017 7:08 AMHey guys, a bunch of you are older and a bunch of you are SF fans, so I'm hoping the Venn touches.There was a book from the 70s called Highways or Exits or something like that. It had a pickup truck on the cover. It was about a road that if you could find the entrance to allowed you to travel through time & space depending on which exit you took. One of the characters had a load of rifles he planned on delivering to the Spartans at Thermopylae as soon as he could find the right exit. Any help on the title to this please?
Are you talking about Roadmarks by Roger Zelazny, the cover has a pickup truck and a flying dragon IIRC
😎WINNER!
And after reading the Wikipedia page on it, added to my own "find it" list.
Viewing 15 posts - 59,821 through 59,835 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply