27.1.14

Forcing Results To Conform For Exports

This one is a bit off for me. I've had to make SSIS exports kick out in specific ways such as how a number is returned or how many spaces a column returned every time regardless of how long the actual return was. Here's a few things I've put in use that seemed to work out well in this situation. If you all have any others I don't have here that are more common or a better fit, I'm always interested adding something new to my list of tools.

Two of these were created by Igor Nikiforov. They are included at the bottom of this page. They need to be added before this script will work. If you take nothing else from this post, please visit Igor's page and look at a few of his User Defined Functions. These are very useful if you're background isn't strong into coding. 

The original query is a simple select from adventure works. 

select 
addressid, addressline1, addressline2, city, StateProvinceID, postalcode, modifieddate 
from AdventureWorks2012.Person.Address

These are a few of the conversions we used to get the outputs to fit as we needed to match an older method. 

select 
 dbo.padl(addressid,10,'0') as AddressID 
,left(addressline1 + space (40),40) as AddressLine1
,Case when addressline2 is null then '' else addressline2 end as AddressLine2 
,isnull(city,'No City Listed') as City
,dbo.padl(StateProvinceID,3,'0') as StateProvinceID 
,dbo.padr(convert(char(15),postalcode), 15, ' ') as ZipCode 
,convert(varchar,ModifiedDate,110) as Date 
,convert(varchar,ModifiedDate,108) as Time 
from AdventureWorks2012.Person.Address 
order by convert(varchar,ModifiedDate,112) desc, convert(varchar,ModifiedDate,108) desc



/****** Object:  UserDefinedFunction [dbo].[PADR]    Script Date: 01/26/2014 23:30:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
 -- PADL(), PADR(), PADC() User-Defined Functions
 -- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
 -- PADR similar to the Oracle function PL/SQL RPAD 
Create function [dbo].[PADR]  (@cString nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
     begin
       declare @length smallint, @lengthPadCharacter smallint
       select  @length  = datalength(@cString)/(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
       select  @lengthPadCharacter  = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

       if @length >= @nLen
          set  @cString = left(@cString, @nLen)
       else
          begin
             declare  @nRightLen smallint
             set @nRightLen  =  @nLen - @length -- Quantity of characters, added on the right
             set @cString =  @cString + left(replicate(@cPadCharacter, ceiling(@nRightLen/@lengthPadCharacter) + 2), @nRightLen)
 end

     return (@cString)
    end


/****** Object:  UserDefinedFunction [dbo].[PADL]    Script Date: 01/26/2014 23:30:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- Author:  Igor Nikiforov,  Montreal,  EMail: udfs@sympatico.ca   
 -- PADL(), PADR(), PADC() User-Defined Functions
 -- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
 -- PADL similar to the Oracle function PL/SQL  LPAD 
Create function [dbo].[PADL]  (@cString nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
      begin
        declare @length smallint, @lengthPadCharacter smallint
        select  @length = datalength(@cString)/(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode
        select  @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2  else 1 end) -- for unicode

        if @length >= @nLen
           set  @cString = left(@cString, @nLen)
        else
  begin
              declare @nLeftLen smallint,  @nRightLen smallint
              set @nLeftLen = @nLen - @length  -- Quantity of characters, added at the left
              set @cString = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cString
           end

    return (@cString)
   end

22.1.14

PowerShell Remote Commands

This is something I've found useful here recently when I had a server acting up. It's a simple way to send commands to a remote server such as 'shutdown -r'

Enable-PSRemoting -Force

Enables to you actually remote through PowerShell.


Test-WSMan Server1

Just tests that you can reach the server through this method.


Invoke-Command -ComputerName Server1 -ScriptBlock { Get-ChildItem C:\ } -credential Domain\UsrID

If you're only sending a single command, you'd replace the Get-ChildItem C:\" with what you're wanting to run.


Enter-PSSession -ComputerName Server1 -Credential Domain\UsrID

This command allows me to interact on a long term method. Basically if I'm sending more than a single command, this would work better.


I'm sure there are other methods. I like short concise code. How do you all connect?

20.1.14

Who Do You Rely On?

We tend to think of ourselves as self reliant. Sit and think for a second, how many people do you rely on?

Help Desk

Our angry callers hit them first. They let us know if something's down and our monitor doesn't see it. The more information we can give them, the easier they can make our lives. If you know you have a planned outage, give them a heads up. They will appreciate it immensely.

Developers

These are the people that build the applications your business is making it's money from. They connect customers with the data you're protecting. We need to get along with them. If we put ourselves on an even level and make sure we keep a good balance, we can get so much more done. Protect the data, but not at the cost of the business.

Network Admin

When you try and hit a server and can't because the routes were updated again, instead of just being mad at the Network Admin, consider this. All those times you had no problems? It's because they had it working. They're in the same boat as us. Noone notices how good of a job we're doing until it all catches fire.

System Admin

I haven't built a production system from the ground up in years. I love my Sys Admins. They get tasked to setup 50 new SQL boxes, 100 new IIS servers and half a dozen DC's for various projects. Once they do all the heavy lifting, we can use a single PS script to install SQL for us across all the new servers and just go to lunch. Give them credit for working on all the things they do so we can concentrate on what we actually want too.

Management

Good managers go to bat for you. They are the ones that argue for new budgets to get more tools, people, hardware and even that soda fountain in the break room. They're the ones trying to make sure no one calls you while you're on vacation or tries to get the SQL Saturday hotel room paid for when you sneak off on the weekends. The more you work for them, the more they'll work for you. (Bad managers excluded, terms and conditions apply, not available at all jobs, see supervisor for details)

Mend The Fence!

Take the time now. Think of some way you can make it easier to work with them and get the projects completed that needed to be done. The easier we make it to work together, the better we can make our companies. I hate seeing posts of an SA or NetAdmin trying to lock down the DBA just as much as I hate seeing the DBA lock out the Developers from even staging. There are times when it's required, but others it just simply isn't. I'm not saying give the DBA's Domain Admin or the password to the proxy servers, or even giving the Developers SA on production... But sometimes a little read only helps a lot when isolating a problem at 2 am on Christmas morning.

Final Thought

I'll end with this. Where I work now, our Network Admins are great. They go the extra mile to take care of us. Our first line support staff goes out of their way to get all the information they can. Our SA does what he can to make our lives easier and our Developers talk with us when they have a question. This may sound like everyone doing their job, but let's be honest. This is the exception. This helps raise retention. Get your people involved, make them feel equal and make sure to take a day and let them enjoy it. Maybe a little Dave and Busters with a long lunch. You'd be surprised what wonders that'll do for your staff.

15.1.14

Restore Master From Backup

There are a few posts running around talking about restoring master from backup. Thomas LaRock has a fantastic "How To: Restore The Master Database In SQL Server 2012" post. It's very well organized and has a lot of great examples and directions. SQLSkills also has a survey up referring to a survey about restoring or rebuilding master. I'm really curious what their post will entail.

I'm going to throw something in the hat here. It's not as detailed as what you'd see from SQLRockstar or SQLSkills. This is a document I put together about a year ago for my group so that when we had to restore from master, someone had a step by step on how to do so.

The word doc is located here.

This is not a training item or a deep dive into how the rebuilds are done and how you can map the stars with them. This is just simply a doc that you throw in your Disaster Recovery Plan folder and let it collect dust until something major happens. If your people know where you keep this... hopefully the instructions are simple enough that they can follow it. You may modify it some to fit your org better.

This is simply a run doc that sits and collects dust until a fire brews. Have your people do the training, have them read through SQLRockstar's post. It's great. Keep this document on the file share so if they forget ,you won't need to search through websites trying to remember something you've done once or twice a career.

13.1.14

What Tables Are Wasted?

Wasted space still seems to be on everyone's mind to some extent. I've wondered what tables do we use? We have our offenders, those are easy to track. What about that little database that's been around for 4 years that no one left even really knows what it does? I have a set of scripts that may help some with that. I'll include a download link as well.

It's listed in the comments, I'll mention it here as well. This list is only accurate to the last reboot of services. Do not base your delete decisions on this solely. If you track it over time, it should be reviewed to see about removing, then only remove it with a good backup so you can restore it if needed. Take caution any time you remove parts of your database.

You can download the full script Here. Any comments or suggestions are appreciated. Thanks again!

/*
Author Bill Barnes
Created 01/10/2014

Use: The purpose of this script is to show indexes and tables that have not been used
since the last restart of SQL services. This script can be quickly modified to show what
tables have been used and provide more useful data. This will ignore all system schema
based tables.

Note: an update or scan is counted per instance set not per scan. If you update a row once
you get one addition to update. If you update a table and change 50,000 records, that still
is only 1 update. Keep that in mind when reading the numbers provided.
*/

--This Version will only pull a list of tables that have shown no use.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
usage.user_lookups,usage.user_scans, usage.user_seeks, usage.user_updates, usage.system_lookups,
usage.system_scans, usage.system_seeks, usage.system_updates, usage.last_user_lookup, usage.last_user_scan,
usage.last_user_update, usage.last_system_scan, usage.last_system_seek, usage.last_system_update
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is null
and sch.schema_id <> 4
and obj.object_id is not null
order by obj.name

-- This version provides a list of all tables that are in use.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
usage.user_lookups,usage.user_scans, usage.user_seeks, usage.user_updates, usage.system_lookups,
usage.system_scans, usage.system_seeks, usage.system_updates, usage.last_user_lookup, usage.last_user_scan,
usage.last_user_update, usage.last_system_scan, usage.last_system_seek, usage.last_system_update
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is not null
and sch.schema_id <> 4
and obj.object_id is not null
order by obj.name

--This version shows a sum of all activity on these tables.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
sum(usage.user_lookups) UserLookups,sum(usage.user_scans) UserScans, sum(usage.user_seeks) UserSeeks,
sum(usage.user_updates) UserUpdates, sum(usage.system_lookups) SystemLookups,
sum (usage.system_scans) SystemScans, sum(usage.system_seeks) SystemSeeks, sum(usage.system_updates) SystemUpdates
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is not null
and sch.schema_id <> 4
and obj.object_id is not null
group by sch.name, obj.name, idx.name, obj.object_id
order by obj.name


9.1.14

New Baby Girl!

On the 7th of January, 2014 my baby girl Kaitlyn was born. She came in about a month early.


This is my mother and my wife using face time so that my sick grandmother can see the new grand baby. It wasn't an intentional 4 generation picture.

Sleep exists... but only for the one that cries the loudest. I swear she can cry and snore at the same time. She's personally adorable. She's got two older sisters who seem both interested and afraid of the "crying burrito". 

A lot of my friends have said that you cannot be prepared for a newborn, you can only try. I was also told to sleep any chance I got just in case. So far though... she's fussy a little on eating, but she's sleeping well and not overly crying.

This year is starting off rather well so far. She's healthy and happy. The wife is doing well now. We didn't get attacked by snowmagedon. Lot's of good things so far. I'll be back with more posts once things calm down. For now though, I'm going to take a short break. If she's realllly good, I'll be back next week. ^.^

6.1.14

Posts I've Found Interesting:

This is a question I had until I came across a great script by The Scripting Guys. The script below is unaltered and entirely their creation. I just found it useful enough to forward it along. This is a short post. Prepping for a baby on the way has cut into my sleep. ^.^


select 'table_name'=object_name(i.id) 
        ,i.indid 
        ,'index_name'=i.name 
        ,i.groupid 
        ,'filegroup'=f.name 
        ,'file_name'=d.physical_name 
        ,'dataspace'=s.name 
from    sys.sysindexes i 
        ,sys.filegroups f 
        ,sys.database_files d 
        ,sys.data_spaces s 
where objectproperty(i.id,'IsUserTable') = 1 
and f.data_space_id = i.groupid 
and f.data_space_id = d.data_space_id 
and f.data_space_id = s.data_space_id 
order by f.name,object_name(i.id),groupid 
go 

SQLSoldier wrote a post recently on how DMV's cost him his job by helping him find a better one. I found it rather interesting and thought I should forward it along. A similar situation happened when the company I was with showed no interest in providing better training, adjusting pay any, and was overall just difficult to get a hold of. I was a contractor working as a DBA. A good friend of mine had an opening where he worked and I ran for happier times. Some times the grass really is greener.

There's an older post (2007) about joins. Let me say first, I'm not a coder. I've worked with some Java, C++, VB, and obviously T-SQL. I always prefer a visual I can work with or semi working code to build off of. Coding Horror had a post explaining SQL joins visually. It's worth looking into.


Don't forget! SQL Saturday Albuquerque is coming up!


1.1.14

Final Giveaway And Some Stats

Today I'm honored to give the final prize in a month of free giveaways here, the book; Professional SQL Server 2012 Internals and Troubleshooting to Robert! I have been lucky enough to give away 4 months of PluralSight to Komal, Annapu Reddy Gayathri, Aadhar Joshi, and Daniel.

Things I've Learned Blogging

This is my 40th post so far. I've noticed that most of my audience is coming from India at almost a 2 to 1 ratio over America. I think that's amazing. I honestly thought it would take months or even a year for this to be read out of the states.

Most of my readers come on around 0600 Central time and again around 1400 Central time. I'm not sure if it's just that time of day or what. I normally schedule my post to show up at 0300 or 0500. I only post after that on days I'm announcing something such as a winner.

I don't think I can maintain a post a day and have anything worth reading. I Plan to do a Monday, Wednesday release with an optional Friday. I'm the sole poster on this site and that takes a tole. I know the wife is tired of hearing, "I plan on working some tonight". I'd like to get a second person who's interested in posting on here sooner or later. Maybe even guest posts. I wouldn't mind hosting a few posts for someone who wants to tell the world something interesting but doesn't want to start a blog.

Commentators and people in general are not that vicious. I was honestly nervous when this blog started getting posted to SQL Server Central. I was expecting some negative feed back such as my information was wrong, too low level, basic stuff, or even just not needed. Instead I've gotten a rather good response and helpful advice. Anyone who's considering writing... go for it.

Thank you all for your support. Next week I'll get back to more technical posts. I really appreciate the people who have come here, who have decided to start following the site and those that commented. Even if it was just to win something. ^.^