Sunday, January 21, 2018

TSQL Tuesdays # 50

This month's topic is: Automating Tasks and is presented by SQLChow (blog | twitter).

Automation - its a blessing and a curse. The curse part is that you want to do it, but who has the time? Of course, the blessing is it will save you time in the long run, but you have to put in the time up-front in order to gain that extra time. :)

I use SSIS daily for my job. I work with the ETL, so automating the data warehouse steps comes naturally. I try to ask each time I do something for someone, whether this is a 1 time thing, or if it will be repeated. I would rather automate something if I can.

Although I am sure this T-SQL Tuesday topic is about administrative type of processes that are automated, I think the best thing we have automated at my job recently, are a set of "checks" at a certain time to make sure everything is as it should be. We are only checking some things, but it has led me to do more. Right now, the checks make sure there is data in certain tables where it should be and it checks certain data in the warehouse to make sure it is within a 5-10% range of yesterday's totals. This automated check has really saved our bacon a couple of times. Before those checks were automated we had to look at a report daily to make sure the data looked ok.

So, back at the main topic. I have recently been learning PowerShell. It is a great tool to automate multiple servers in a single script. My latest automation is to update the DEV database from a copy of the PROD backup file. I get the latest backup filename and location, move the file from the production server to the development server, then restore that with different final data file locations, and finally update users and permissions. It was my first PowerShell script and thank you to Sean @ MidnightDBA for the help!

I know that this year (2014), will be spent learning more about database administration, and more automation scripts will come to be. It is exciting to know that I will feel more in control of the overall SQL environment when I have automated more of it.

Write comment (0 Comments)

DBA and Project Management?

What do Database Management and Project Management have in common?  I am finishing up the piece of paper that some would call a Bachelor's degree (I only say it that way because I havent learned much during the several years it has taken me to pay for it on my own - mostly because I have learned everything myself).

One of the last classes I have to take is Project Management.  I am excited about this class, but at the same time a little non-chalant about it.  I hope to glean some wonderful information about the topic, but this is also something where I have learned quite a few things along the way.  I have had some great teachers in the form of co-workers, who have helped me get the knowledge I have today.  I have also gone through some trial and error situations to learn what works best for me. 

It took a long time to understand that I have to have something written down for me to remember it - and I had to change my processes to actually write everything down in the same place!  Now, though, I have a system that works well for me.  I am flexible with the software I use to keep my projects list, but it is the consistency that I cherish.  While this is more along the lines of Time Management, I am glad that I have these skills before this class.

I will be posting in the Project Management category while I am learning so that I can actually keep the knowledge in my brain, well hopefully!  I will also probably come up with a presentation on this exact topic - What a DBA needs to know about Project Management - or maybe How Project Management skills help a DBA. :)

Write comment (0 Comments)

RIP Cursor - took you down from 90 mins to 5 mins

I finished something recently, that I am very proud of - I literally had to hold on to my chair at my desk to not jump up and shout Wooo-Hooo! I tore into a cursor and obliterated it!! :)  I wanted to talk about the process of the transformation - just thought I would record my thoughts so that hopefully they will help someone.

I work in the health care industry, and the cursor is used to calculate the number of active patients for each day.The dataset has an admit date and a discharge date, along with some other things. Due to the way the transactional database is set up, there are important conditions to make sure you get the correct insurance, etc. for past dates and such. The total number of active patients can change due to late data entry and/or mistakes, therefore the entire summary table is re-built each night.

Read more: RIP Cursor - took you down from 90 mins to 5 mins

Write comment (0 Comments)

Who has the #@!*$% file open?

I just found out about a nifty tool and thought I would share.

Scenario: The nightly ETL uses files as source data to insert into the data warehouse. The ETL failed because the file was locked and not accessible. The file is on a server in a shared folder. I needed to find out who had the file open, although I had a suspicion it was sql agent, not an actual person. Either way, the file needed to be closed, or the connection terminated.

Answer: openfiles.exe

I had never heard of this utility, but it works wonders. It was able to tell me who had the file open and allow me to disconnect the user's session with that file. In my case, the ETL was able to run successfully again. :) I did find out afterwards that our IT department performed maintenance over the weekend which must have caused the connection to 2 of these files to be "hung up".

The link above shows all the options available, but here is a summary also.

You can access the following information on the local server/workstation, or from remote. If you are remotely querying/disconnecting, then use the following switch:

  • /s ComputerName - you can use the name or IP of the remote computer.

You can perform all of these functions as yourself, or specify a specific user. You can use the following switches to indicate a specific user:

  • /u - specify the username to run the task as, format is domain\user
  • /p - enter the password for the username entered

List of files open and by whom
/query

  • /fo {TABLE | LIST | CSV} - this alows you to specify the format of the results
  • /nh - this allows you to hide the headers in the results, but this switch can only be used when the /fo tag indicates a TABLE or CSV formatted resultset
  • /v - this stands for verbose and shows the verbose task information

Terminate the connection
/disconnect

  • /id ### - disconnects the file specified; use /query to determine the id of each file
  • /a username - disconnects all files opened by the specific username
  • /o mode - disconnects all open files that are in the specified mode (values expect either Read/Write or Read)
  • /se sessionname - disconnects all open files in the specified session
  • /op filename - disconnects the specific file by file name

Note: I tried to disconnect the files using the ID specified from the /query switch. However, it didnt disconnect as I expected. When I tried the /o Read/Write, it did work as expected and closed all files.  Also, when looking at files on a software share, you can use the ip address.

Hope this helps someone - I am sure I will be referring back to my own blog for this one a few times!

Write comment (0 Comments)

How much juice can you squeeze from a lemon?

How much juice can you squeeze from a lemon? I had no idea there are quite a few websites that teach you how to get EVEN MORE juice from a lemon. That is what I thought about when I realized I was going to write this blog post, so of course I had to look it up.

Since this blog is about SQL, not lemons, let me explain where my mind was earlier this evening. I was sitting in a pizza joint waiting for dinner (the kids love Pizza night, and I love the no cooking), and I started typing up an email that I had been putting off because I just didnt have time for it. I was rather proud of myself that I used some time that would have normally been wasted. Then, I started thinking, what if I actually started looking for times like these to stick quick tasks into? And that got me thinking about... is that how the Donald Trump's of the world do it (the kind of people who look like they accomplish sooo much that it sometimes, almost convinces me I wont ever be ale to keep up)?

I am wanting to start a user group in my town. Actually, let me jump back a moment, I am planning things out and will make the final decision when I feel I have more data to use to decide if I can actually do a good job of it and if it is desired in the community. Part of my planning has been to try to decide if I can actually put the needed amount of time into it. I dont want to drop the ball on something else, nor this new project, just because I had a hair-brained idea.

My head was swimming with thoughts and I remember the last one that crossed my head, just before the pizza was served - "How am I going to be able to play computer games, if I am busy all the time?" I know, sounds crazy, who pictures a MOM playing computer games? But... I am not too shabby :) Its a valid point though, will I have enough quality time for me, even though I am going to have to spend more time being productive?

I am asking around for tips and opinions about what challenges others have faced when starting/leading a user group, and what their advice is for things I dont even know to ask about. We will see how they respond, and go from there. Ohh, if you have advice, please share - you can email me here This email address is being protected from spambots. You need JavaScript enabled to view it.. :)

Hope you had a great day, and I will write again soon!

Write comment (0 Comments)

SSIS - Stored Procedures with Return Values

I am finally making a blog post about this. Everytime I go to use this technique, I always forget the syntax and have to scout around to find it.

The scenario: I want to use an "Execute SQL Task" transformation within SSIS to execute a stored procedure, using an OLE DB connection. Then, I want to capture the return value. My problem is, what is the syntax within the Parmeter Mapping configuration screen? I seem to always forget what to type in as the parameter name. :)

I start out providing the needed information about which stored procedure to execute, using which connection. I am adding in a screenshot below, but generally, the syntax for what I need is exec ? = usp_storedprocedurenamehere. (See screenshot 1). Then, when I go to the Parameter Mapping screen, I select the variable I am capturing the integer into, change the direction to be ReturnValue, and leave the default Data Type as LONG. Here is where I get stumped - The ParameterName needs to be 0 (that is a zero). (See screenshot 2) For Input/Ouput parameters, you can specify the numerical sequence of the parameter, or the parameter name. I will show that in another blog post.

Well, this is a short and sweet post - mostly to give me somewhere I can look when I forget, yet again. :)

Talk to you later!

Read more: SSIS - Stored Procedures with Return Values

Write comment (0 Comments)

TSQL Tuesdays # 47

This month's topic is all about SWAG. Kendal Van Dyke (blog | twitter) is hosting this month and wants to know "the story about the best SQL Server swag you've ever received." This is definitely a topic that will have some fun answers. :)

I know there will be several people this month who will have to really think about what the best SWAG they have received is. They will have to look at several things before making a decision. I am not in that predicament - not yet anyway. Of course, if any vendors are reading this, feel free to send me some stuff to put me in that position! :)

I have received exactly 1 thing so far that is my favorite (I have actually only received 2 things, so my choice was not hard). During TechEd 2013, there were daily A&Q themed twitter posts from Microsoft. I participated each day - and I won! I have never won anything, so it was a lot of fun. The prize was a USB power stick. I had never heard of these things before, so I read the instructions that came with it. It is an 8GB flash drive, and also keeps a charge that you can use to power other electronic devices. At the time I got it I wasnt sure where I would use it, but there have been 2 specific times it has come in really handy.

  1. I went on a trip with my son this past summer. We went to the easternmost point of the US (up in Maine), saw a couple of waterfalls, and went through Kentucky and Florida before coming home to Louisiana. I brought it with me and it was a good thing. We went camping (with a tent and sleeping on the ground - real camping stuff) and I was able to still use my ipod touch because of it.
  2. On the same said trip, while in Kentucky, my friend/coworker, Joe, and I went to a music festival. We got to see Willie Nelson (woo hoo!). Joe didnt have his phone charged - voila! - I was able to help him out. Without that, he wouldnt have gotten pictures of Willie Nelson. (I just had to say his name again - it was sooo exciting and I was up standing up front real close).

Pic coming as soon as I get home! :)

So, my favorite SWAG helped me help a friend - and he got more pictures of Willie Nelson. I am sure there will be someone who can beat that - I am excited about reading the other posts. Thanks Kendal for hosting this month!

 

EDIT: 10/8 9:26 PM CST - here is the picture! :)

 

Write comment (0 Comments)

Archives

Powered by mod LCA