My sad but true Geeky adventures
Have you ever wondered how to get the current time and date within SQL Server? Well I am going to show you two easy methods of getting this information.
The first method is my preferred method as it’s slightly shorter than the second method.
SELECT CURRENT_TIMESTAMP
Originally published at https://parvtheitgeek.com on February 9, 2014.
Have you ever had the above error message? Wondered how to sort it out? Well I am going to show you how.
Today I was confronted by the above error when a test migration database which was setup with replication was loaded with simulated data. The migration source environment was not replicated so I did a check of the length of the data using LEN but thought this was inappropriate as we could solve the problem for a short period before facing the same problem again in testing. I thought it would be best to note the current configuration and then put the max value and note that we would have to change this configuration when it was loaded with data from production.
I checked the current configuration using the below script and screen captured it for future reference.
USE InsertyourDBname;
GO
EXEC sp_configure ‘show advanced options’, 1 ;
RECONFIGURE ;
GO
EXEC sp_configure ‘max text repl size’;
GO
I then decided that I would give the maximum value until later in the project when I could more fine tune this value.
USE InsertyourDBname;
GO
EXEC sp_configure ‘show advanced options’, 1 ;
RECONFIGURE ;
GO
EXEC sp_configure ‘max text repl size’, -1 ;
GO
RECONFIGURE;
GO
This can be done easily via SSMS by going on the server > Properties > Advanced > Max text replication Size > change value to -1.
Originally published at https://parvtheitgeek.com on February 6, 2014.
Have you ever wanted to search for new commands or have difficulty remember syntax? Well I recently found the wildcard search function within the help system and I am going to show you how to use it.
You could use the commands feature which is below and can be accessed via Add-ons > Commands
Or use the wildcard search in the command line. This is easily done using an asterix in front of, after or around the command or word you’re trying to look for.
For example if I was looking for SQL command iI would use
help *sql*
Or if I was looking for all commands which started with ‘remo’ I would use
help remo*
I would play about with the wildcard search system and see what cool new things you can find.
Originally published at https://parvtheitgeek.com on February 5, 2014.
A quick snippet for some newbie PowerShell developers whilst they start getting their teeth into the ISE is there are two easy ways of clearing the screen. I ahdnt thought about sharing this information but I saw a developer simply closing the ISE so that he could start afresh. I asked him why and he didn’t know any other way and hadn’t spent any time exploring the ISE or learning many PowerShell commands.
If you ever have a screen which you want to clear like below.
You can either type cls which will clear the screen or if you forget that syntax use the clear console pane button highlighted below.
Originally published at https://parvtheitgeek.com on February 3, 2014.
Have you ever wanted to be able to easily read through the help system in PowerShell? Wanted to take advantage of your second screen whilst developing in PowerShell? Well I am going to show you how.
Whenever I have used PowerShell I had a love-hate relationship with the help system. Whilst it helps me out a lot by making it easy to understand and use PowerShell commands it also is a pain having to scroll up and down to understand the syntax and go through the notes.
However, I Just found an excellent new feature ( at least to me ) which opens PowerShell into another window using the PowerShell command -ShowWindow which is in the example below.
help Get-Host -ShowWindow
That then opens the below screen which is a much more user friendly Help box.
Originally published at https://parvtheitgeek.com on February 3, 2014.
Have you ever wondered how to update the PowerShell help system? Wondered how to update it more than once in a 24 hour period? Needed to update the help file of a system not connected to the internet? Well I am going to show you how.
Whenever I jump onto any new server the first thing I do is check what PowerShell version is on the box via $psversiontable or Get-Host. Then I usually would ask the Windows Admins to upgrade to the latest version.
The second thing which I do is update the Help system. In version 2.0 this involves downloading the latest Help file. In PowerShell 3.0 they have made it super easy and all you have to do is run the command update-help
You will then get the below screen running whilst update runs.
This can be run once every 24 hours but if you do have a need to update more frequently you will need to run the below command.
update-help -Force
Whilst this seems easy this is only good for those Servers which are connected to the internet. If you are looking to do it on a Server which is not connected to the internet then you will need to use the save-help command. This saves a copy of the help file which can be copied to other servers.
Save-Help -Force -DestinationPath C:PS
Please note I only used the -Force command for illustration purposes as I wanted to do the update more than twice in a 24 hour period for the purposes of this article.
Originally published at https://parvtheitgeek.com on February 2, 2014.
Have you ever wondered what PowerShell version your machine was running? Needed to quickly check the PowerShell version to see whether it had successfully upgraded? Well I am going to show you how.
Today I was on one of my creaky old SQL Servers when I started doing some inventory checks to see what level the SQL 2000 and Windows Server 2003 were patched to and do some other inventory checks . Usually I would use some PowerShell scripts but as this Server exists in an old secured environment I could not us them and wondered whether it would be worth going through the motion of getting the scripts screened and approved. Before that I thought I should at least check what version of PowerShell was on the box and having never used PowerShell with Server 2003 or SQL 2000 thought this would be a good bit of fun.
I saw the PowerShell icon and felt uplifted and started it. I then typed $psversiontable and got an error. I had no access to google so it took me a few minutes to remember that the command ‘get-host’ had the information required. When it returned version 1.0 I decided to just stick with the current methods as I didn’t think it would be worth my time checking if my scripts would work in 1.0 when everything I had developed on version 2.0 and higher.
So in the future I would recommend using either of the two below commands to get the version number.
Get-host
$psversiontable
Originally published at https://parvtheitgeek.com on January 30, 2014.
Have you ever just been given a column name and not known which table it was in? Needed to check whether a column existed within a database?…
Well today I was just given a column name and told the information I needed was in there but alas they didn’t know which table it was in.
I could have gone through each table but that is very inefficient and I can more effectively harness the power of SQL to get the answer quickly and easily.
I knew that the INFORMATION_SCHEMA.COLUMNS would be a great place to start and did the below query.
FROM INFORMATION_SCHEMA.COLUMNS
The next step was to add the COLUMN_NAME in with a WHERE clause.
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = ‘max_history_rows’
Originally published at https://parvtheitgeek.com on January 29, 2014.
Have you ever been baffled when people talk about SQL joins? Mystified when you hear inner, outer, cross or self? Ran away in fear at the thought of joining two tables?
Well I am about to help you out and face down those fears and explain to you what joins are, the different types and what they are used for.
What is a join?
A join in SQL is a clause which combines two or more tables together within a database. The join is done on matching predicates between two or more tables and the result is a new data set.
Simply this means that it will take two tables and join them on a column within each table which has data that contains similar data.
What is an inner join?
An inner join will return only values where there is match between both corresponding columns in both tables.
Simply it means where the two columns match the rows will be returned in the new table.
What is an outer join? Left? Right? Full?
An outer join is different to an inner join as it does not require the data to match. A Left join would return all record from the left table and only the matching record from the right.
A Right join would return all record from the right table and only the matching record from the left.
A Full outer join returns all unmatched and matched data. Simply it is returning the results of a Left & Right join
What is a self-join?
A self-join is a table which is joined to itself.
What is a cross join
A cross join is often referred to as a Cartesian product and returns all possible combinations of rows.
Originally published at https://parvtheitgeek.com on January 28, 2014.
Have you ever wondered how many DMVs are in SQL? Wondered whether a DMV exists which help diagnose your problems? Well I am going to show you how to list all the DMVs and DMFs so that you can do a deeper dive into them or await my series where I will be exploring each every DMV and DMF.
Before you run the script it is important to understand that Dynamic Management Views and Functions are used to return information about the status of SQL Server so that you can performance tune issues, check and diagnose for problems.
SELECT *
FROM SYS.ALL_OBJECTS
WHERE [name] LIKE '%dm_%'
AND [type] IN ('V', 'TF', 'IF')
ORDER BY [name]
This script will only run in SQL2005 ( SQL 2000 does not include these features) and above.
In SQL 2008R2 there are 141 DMVs and DMFs and in 2012 there are 171.
Originally published at https://parvtheitgeek.com on January 25, 2014.
Parv The IT Geek 