Here’s a short video on using some basic MS SQL Functions
History Graph
In this tutorial, I’ll show you how to draw a histo-graph using your computer’s CPU usage as the values.
Alter Schema
Schema’s are use to help isolate users to a specific schema, ETL purposes, reusing a table name that already exists, and etc. reasons. In this video, I’ll show you how to import data into a table. Once the data is ready an Alter Schema is performed to prevent any downtime of the table.
OraOLEDB.Oracle Linked Server
Do you want to use Oracle’s driver to directly connect to an Oracle database instead of using ODBC (DSN)? Is it any faster? You be the judge!
Step 1
Make sure you have Oracle 12g drivers installed on the server. This could be any version of Oracle
Step 2
Make a note as to where your Oracle installation path is. You’ll need a few information from the TNSADMIN.ora file. For example, my path is C:\Oracle\Ora12\Network\Admin\tnsadmin.ora
Inside tnsadmin.ora, you’ll need the HOST, PORT, and SERVICE_NAME
Step 3
In SSMS, navigate to the OraOLEDB.Oracle providers and open up the properties
Step 4
Create a Linked Server
- Linked Server: Call this Linked Server whatever you want
- Provider: Select Oracle Provider for OLEDB
- Product name: Call this whatever you want
- Data source: This information comes from Step 2 in this format HOST:PORT/SERVICE_NAME i.e.(My_Host.afterowl.com:1571/My_Service_Name.afterowl.com)
- Click on the Security tab on the left and use Be made using the security context. Then click OK to create the Linked Server
Step 5
This is how you would use your new shiny Oracle Linked Server
SELECT * FROM OPENQUERY(“ORACLE”, ‘SELECT * FROM MyTable’) AS MyTable
In Recovery Status
Sometimes a very large database (VLF) stalls and or takes forever and a day to perform a 2 minutes task. In these fun times we restart the server. It gets funner when the server is back online and your database is IN RECOVERY.
This generally happens prior to the server restart, you still have large transactions running (i.e. millions/billions or records being DELETE/UPDATE/INSERT).
To check on the status of your database simply run
EXEC sys.sp_readerrorlog 0, 1, ‘Your_Database_MDF_Name’
At this point, you can’t use sp_helpfile because your database is inaccessible therefore navigate to where the mdf file is located. Most of the time, the mdf file is the same name as your database.
When you run the command above, in your message tab you’ll see the percentage of completion and how many seconds to go.
WAITFOR it …
There are times when you want to delay a script such as sending yourself a reminders or to run a job/stored procedure/etc.
Wait by Time
BEGIN
WAITFOR TIME ’01:10′;
PRINT ‘This message will print at 1:10 am’;
END;
Wait by Delay
BEGIN
WAITFOR DELAY ’01:00′;
PRINT ‘This message will print in one hour’;
END;
SQL Back Door – DAC (Dedicated Administrator Connection)
There will be a time or more in your life where SQL Server becomes unresponsive and stops working. You can try to remote desktop into the server with no luck– or take the Rambo route and hard restart the server or have IT do it. This is where DAC (Dedicated Administrator Connection) comes into play. DAC allows one back door SA connection into the SQL Server. To use DAC, you first have to turn it on and here’s how you do it.
GUI Method
First open up SQL Management Studio
Step 1. Right click on your server
Step 2. Click on Facets
Once the Facets windows show up do the following
Step 1. Under the Facet drop-down-list, pick Surface Area Configuration
Step 2. Select RemoteDacEnabled and from the drop-down-list pick True
Step 3. Click OK
That’s all to it
If you prefer the T-SQL method
USE master
GO
sp_configure ‘remote admin connections’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
How to log onto the server using DAC
Step 1: Open up SQL Server Management Studio (SSMS)
Step 2: On the tool bar, click on Database Engine Query
Step 3: In the Server Name box, type in ADMIN: before your server name.
Step 4: Click Connect
Once connected, you can then troubleshoot your SQL Services. If you can’t log in, it means two thing.
- You don’t belong in the SysAdmin group
- A SysAdmin is already logged onto the server
As always, I hope this helps.
Add Email to SSIS for troubleshooting
When your SSIS package fails, wouldn’t it be nice to receive an email with as much details as possible. Here are steps to setup this hidden gem.
Step 1. Setup the Event Handlers
- Click on the Event Handlers tab.
- Under the Event handler, select OnError
- Under the Executable drop-down-list, select the top most from the tree
- Click on the link
Step 2. Add the Send Mail Task
- From the SSIS Toolbox, drag the Send Mail Task into the Even Handlers canvas
Step 3. Setup the Email connection
- From the Connection Manager, right click in the white section and click on New Connection
Select SMTP and click Add…
Fill in the Name and SMTP server and click OK.
Step 2. Open up the Send Mail Task by double clicking on it
- Click on the Mail tab
- SmtpConnection: Click on the drop-down-list and select the connection you previously created
- Fill in the From, To fields with your emails. You can also use Expression for this but in this example, we’re going to hard code the emails
Click on the Expressions tab
You’ll want to do this step for the Message Source and Subject
Copy and Paste these to the respective Expression. When you’re done, click OK.
Message Source
“Machine Name: ” + (DT_STR, 2000,1252) @[System::MachineName] + “\r\n” +
“Package Name: ” + (DT_STR, 2000,1252) @[System::PackageName] + “\r\n” +
“User Name: ” + (DT_STR, 2000,1252) @[System::UserName] + “\r\n” +
“Source Name: ” + (DT_STR, 2000,1252) @[System::SourceName] + “\r\n” +
“Source Description: ” + (DT_STR, 2000,1252) @[System::SourceDescription] + “\r\n” +
“Error Code: “+ (DT_STR, 2000,1252) @[System::ErrorCode] + “\r\n” +
“Error Description: ” + (DT_STR, 2000,1252) @[System::ErrorDescription] + “\r\n”
Subject
“** Error Msg from OnError Event Handler *** ” + ” SSIS Package Name: [” + @[System::PackageName] + “] ” + “; Machine Name: ” + @[System::MachineName]
Proof is in the pudding!
Here’s a look at how it looks like when an error occurs. You’ll get an email telling you which Package and Task failed. Keep in mind that you no longer need to go to SQL Job History, SSIS Catalog, Event Viewer to view the errors. The errors will come to you via email! Sweet Jesus!
As always, I hope this helps.
Fail-Safe
There are times when you’re on vacation, kids soccer games, family outing, church, etc where you’re inaccessible. Thanks to Murphy’s law something bad will happen to the database server and alerts will fire off and you won’t receive those notifications because you’re enjoying life for once. This is where you want to enable Fail-Safe– a.k.a. your backup buddy.
As TechNet puts it, “when things go wrong with SQL Server notifications, operators are not notified and problems might not be corrected in a timely manner. As a precaution, you might want to designate a fail-safe operator. A fail-safe operator is notified when a SQL Server Agent cannot access system tables in the msdb database (which is where operator definitions and notification lists are stored), when all pager notifications to designated operators have failed, or when the designated operators are off duty (as defined in the pager schedule). ”
The steps to enable this tucked away feature is as follows.
Open up Management Studio -> Right Click on SQL Server Agent -> Click on Properties
Click on “Alert System”
Tick the “Enable mail profile” as you’ll need this for the Fail-Safe to work.
I’m assuming you already have a Mail Profile setup so at this point, select your Mail System/Profile from the drop-down-list. Once this is done, the Fail-Safe operator will highlight.
Tick on “Enable fail-safe operator” and select your Operator from the drop-down-list. Then Tick on how you want that operator to receive the messages and then Click OK
As always, I hope this helps.
You must be logged in to post a comment.