SQL Prompt is amazing and it’s my favorite tool. It’s actually what many people want first from Redgate because it makes coding much easier. One of the most used features is the reformat feature (CTRL+K,Y), since formatting really matters to developers.
One of the neat features of SQL Prompt is that you can format just a portionof your script. That means if you’re working on a large script and want to reformat just a part of it, you can.
Here’s how:
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
If you are wondering what permissions a role has, and don’t want to spend time searching and digging through Books Online, there are a few system procedures that can help. These will give you the permissions for a role, or all roles.
Let’s see how to use these.
Let’s say that you are wondering what a Processadmin can do. There is a procedure, sp_srvrrolepermission, that you can use to get the list of permissions. This can be run with no parameters, in which case you’ll get all roles and all permissions.
However, often you’ll want a more manageable set of data that a person can understand. Let’s see what processadmin can do.
Only two permissions here, so I can see that granting this isn’t going to affect security or databases.
The database has a related procedure, sp_dbfixedrolepermission, that looks for database roles. This can again be run without any parameters, as shown here.
If we include a role, we see just those permissions.
This is a short post, really about 5 minutes, based on my need to solve another issue. However, this is something a DBA should know, and it’s something I’ll likely use again.
It’s that time again, and this month we have a good topic from Wayne Sheffield. We’re asked about getting stuck, about being blocked, about encountering a brick wall.
I have to say that I don’t think I encounter these very often, usually because I try to be effective, even if things get ugly in code. The important thing for me is to deliver value. That doesn’t mean I build slapdash applications and leave them. I do my best, and try to continue to learn to build things better. However, in the heat of the moment, I might need to do something silly that I end up either rewriting later or having someone else rebuild the solution.
With that…
I once worked for a financial services company. This was decades ago, back in the age of spinning disks, SQL Server 6.5, and flip phones. We had a number of clients on our main application, but one large client that dominated our business.
We needed to provide quarterly summaries of activity and portfolio performance for all clients, but for this main customer, they also wanted us to add monthly reports. They were actually their own management firm, but we provided them with back end services. Within a month, they would have millions of transactions that we had to group by some organization and then calculate performance.
This wasn’t a big deal with other clients, though most of them had relatively simple calculations. For this client, however, they had separate rules for certain types of activity and the result was a calculation that we couldn’t easily get into CASE statements or other types of query workflows.
In trying to handle all their requirements, I (and my junior team), got stuck. We couldn’t come up with a set of queries that would work. We didn’t want to go down the road of writing custom queries for each organization as this would be an ongoing nightmare as new requirements were introduced.
We had delay after delay in trying to implement their monthly reporting and pressure kept mounting. Eventually we turned to using temp tables and making different passes through them to handle specific requirements. We had ugly flags in our temp tables to let us know which set of rules we’d applied and which we hadn’t.
The code worked, but it was slow. Too slow, in fact, and my boss wasn’t pleased. After a particular unpleasant dressing down, I was wondering if this was the career for me. I’d talked with friends, posted questions, but in this pre-SQLServerCentral era, there were limited places to get assistance. I had hit brick wall.
I never got to find a solution to the problems. My boss at the time was one of my worst, and he became petty. I started to get assigned various inane tasks, which were slightly annoying. However, on a night off, while on a date with my wife at a concert, he paged me with an emergency. I had to leave the show to answer and call in. When I did, he told me this was just a test to see if I would respond, then berated me for taking more than 5 minutes to do so.
I quit the next day.
Perhaps not the best reaction, but between the brick wall and poor treatment, I decided to move on. Hopefully someone else managed to solve the the performance issues and help the client.
As much as I liked the ability to quickly and easily build development and test databases with SQL Provision, I thought the dashboard of cloned databases was hideous. It left a lot to be desired, and frankly, the dark theme is annoying to me. Here’s my old dashboard.
I wasn’t alone, as various customers were asking about enhancements and additions. The team has been listening and I talked with them a few months ago during a meeting about possible ideas and designs. I saw an early mock up, and was hoping it would be released soon.
After coming back from vacation, I saw an update was available, so I applied it. After a few minutes, I saw this:
Once this was done, the page refreshed, and I saw the dashboard. I know, not much has changed, but look at the upper right part of the screen. There’s a blue box that says “Preview new dashboard”.
Once you click this, you get a new dashboard, which thankfully doesn’t use the dark theme. What’s nice is that I also get some information at the top of what my activity is. I can see the total clones and images, and the machines that are working or having issues.
I also have options for resorting the clones and images. I can change the sorting, which is set by the client, not the server. This means one person can see clones by instance, while another can see clones by image.
If I change this, you can see that I get a new view at the bottom.
There are more changes needed, and some coming. There is a feedback item when you switch to give feedback to the team, and I’d encourage you to do so. Certainly I think sizes or some calculation of total sizes for images and clones is needed. It would be nice to get filters for sizing, so I can also tell if someone is using a clone to do a lot of work and growing it’s size. One of the important things here is that you ought to not get to wedded to a particular clone. We want to rebuild these as needed.
I’d also like to see some way to link images to a source and perhaps group them so that I know how many copies I have of some database, like production. While I think we definitely need a couple of images at any time for rotation, we want to get control of our systems and limit the number.
If you have other feedback, let us know, and we’ll build a better dashboard together.
Voice of the DBA 