Category Archives: Microsoft SQL

MS SQL Clear Cache and Buffers

Posted on by
Reply

Use DBCC FREESYSTEMCACHE to release all unused cache entries from all caches.

DBCC FREESYSTEMCACHE ('ALL','default') WITH MARK_IN_USE_FOR_REMOVAL;

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

Insert or Update Data in MS SQL

Posted on by
Reply

I’ve been using MySQL for quite a while now and have enjoyed the easy to use “INSERT … ON DUPLICATE KEY UPDATE” syntax. My more recent project requires me to use MS SQL as the database and executing this same type of simple statement is a bit more involved.

After trial an error here is what I’ve found to be the sure fire method for getting the UPDATE or INSERT depending on the data your inserting.

DECLARE @err int;
DECLARE @rowcount int;

UPDATE tblData SET strYourCollumn = 'MyValue'
  WHERE lookupID = 10;

SET @rowcount = @@ROWCOUNT;
SET @err = @@ERROR;

IF(@err <> 0)
  BEGIN
    -- Handle error here
  END

IF(@rowcount = 0)
  BEGIN
    INSERT INTO tblData (strYourCollumn, lookupID) VALUES ('MyValue',10)
    -- Its a good idea to check for any insert errors here too
  END

Truncating log files in MS SQL

Posted on by
Reply

I recently ran into an issue where I wanted to truncate a bloated Microsoft SQL log file and really the reason was because I was running out of drive space. When you have no drive space you cant exactly conduct a full backup of the log file and then shrink it.

Here is a simple script that avoids having to backup the log (or write it to another location) prior to properly shrinking the file.

USE [YourDatabase]
GO
ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(YourDatabase_log, 1)
ALTER DATABASE [YourDatabase] SET RECOVERY FULL WITH NO_WAIT
GO

The script above is for Microsoft SQL 2008. To achive the same effect, older versions of MS SQL can use the following script, which does the same thing.

USE [YourDatabase]
GO
DBCC SHRINKFILE(YourDatabase_log, 1)
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(YourDatabase_log, 1)
GO

Now you can enjoy all the new free space you just cleared up.

Escaping MS SQL Single Quotes and Wildcards

Posted on by
1

Transforming data from one place to another using various tools tends to lead for the need to properly capture and escape special characters for the target systems. In this case Microsft SQL. The more common characters tend to be the single quote (‘), the percent sign (%), and the under score (_).

To escape single quotes simply add a second single quote to your string, turning O’Donald to O”Donald.

INSERT INTO tablename (columnname) VALUES ('O''Donald')

As you can see I’ve replaced the single quote with double quotes between the O and D.

Wildcard characters, % and _ are handled a little diffrently. Insted of adding another single quote we wrap the special character with square braces [] or defining an ESCAPE clause. This prevents the MS SQL engine from trying to conduct wildcard matches when compiling your query.

Using square braces is easy.

SELECT * FROM tablename WHERE columnname = '[%] CPU Utilization'

INSERT INTO tablename (columnname) VALUES ('My[_]Underscore[_]Value')

Another method to escape characters is by using the ESCAPE clause at the end of your WHERE clause like this:

SELECT * FROM tablename WHERE columnname LIKE 'My\_Underscore\_Value' ESCAPE '\'

In this example I’ve defined the back slash \ as the escape character using the ESCAPE keyword. Its worth noting however that you are limited to a single character when defining an escape character.

Create Comma Delimited Strings in SQL

Posted on by
Reply

Have you ever needed to build a comma delimited string on the fly based on results from query your using?

DECLARE @CommaString varchar(500);

SELECT  @CommaString = ISNULL(@CommaString + ', ', '') +  YourFieldValue
FROM    YourTableName
WHERE   YourSelectCriteria;

SELECT @CommaString;

Its worth noting this will work with one string at a time but if you drop it into a user-defined function, problem solved!

MS SQL 2008 R2 Allow Remote Connections

Posted on by
1

Recently I’ve been challenged at work and asked to use Microsoft SQL Server as the database, using Windows Server 2008 R2. I decided to take the standard install options, doing nothing special. During the install there was a warning about the need for additional Firewall configuration. Making note of this, I proceeded with the install without incident. Once the install was done, I ensured the server was running by verifying all processes were “Started” with the exception of a few that were not needed such as SQL Active Directory Helper, SQL Full-text Filter (beyond the scope of this document), SQL Server Agent (not going to do any scheduled jobs yet), and SQL Server Browser (no need to announce to the world I’m running a database).

My next test to make sure the MS SQL server was up and running as expected I logged into the server using the client tools installed with the server, Microsoft SQL Server Management Studio. I know you can install without them but it sure makes life easier on a development server when you have client tools set up locally when troubleshooting. To ensure the SQL Server was running I logged in using localhost as the Server name.

Once connected you will be able to brows your database objects such as Databases, Security, ect…

The server was up and running so the next logical step would be to enable remote connectivity by opening the MS SQL port to incoming traffic. In Windows 2008 the Windows Firewall with Advanced Security tool is fairly straight forward. To open this tool click on Start, Administrative Tools, Windows Firewall with Advanced Security. You could also take the shortcut and type “fire” in the Search tool and then click on the Windows Firewall program icon.

The tool is basically broken down into two main areas, Incoming and Outgoing Rules. For basic MS SQL connectivity we only need to add one Incoming rule for port 1433.

  1. On the left side, click on Inbound Rules.
  2. On the right side, click on New Rule…
  3. In the wizard, choose Port, click Next.
  4. Choose TCP and provide the value of 1433 after choosing the Specific local ports option then click Next.
  5. Click the Allow the connection option and click Next.
  6. Check all three boxes Domain, Private, and Public, depending on your desired network configuration.
  7. Provide a descriptive name like MS SQL Port 1433 Incoming and click Finish.

When your done the Windows Firewall should have a new Inbound rule.

This is where I ran into a few moments of hair pulling. Just because you’ve opened the port, I couldn’t get any remote clients to connect. I continually revived Connection Timeout issues. After a few port scans from both the localhost as well as a remote client I discovered that despite the Firewall rule, I still couldn’t access the server. At one point I completely turned off the Windows Firewall with the same frustrating results.

Evidently when you take the defaults (unless I missed it) the more recent installs of Microsoft SQL disable both Named Pipes and TCP/IP. To get remote connections working the TCP/IP is required to be enabled.

  1. Open the SQL Server Configuration Manager
    1. Provided you installed the client admin tools this program can be found by clicking on Start -> Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager. Its a standard snap-in so I bet you can open it just like you would any of your favorite snap-ins.
  2. Once the Configuration Manager is open, expand the SQL Server network Configuration and click on Protocols for MSSQLSERVER.
  3. On the right side you should see several protocols listed. The one we are interested in is the one named TCP/IP. If this protocol is set to Disabled, all attempts to connect to your SQL server will fail miserably.
  4. Double click on TCP/IP and change the Enable option from No to Yes.
  5. Click Apply, then click OK.

To make life easier, I also enabled the Named Pipes to prevent having to troubleshoot other client connectivity problems.

For these changes to take effect you will then have to restart the SQL Server (MSSQLSERVER) process. After a quick restart, all of your connectivity concerns should have evaporated.

To test this you can now open the SQL Server Client tool, using an IP and connect to the server.