MS SQL Server: Quick T-SQL to Delete All User Table Data

Posted on March 29, 2011 by Greg

Here’s a quick (hack) way to delete all user table data from a Microsoft SQL Server database using the undocumented procedure sp_MSforeachtable. As the name hints, this procedure repeats a SQL statement against all user tables within a database.

This example assumes all identity columns are seeded at 1. You may not be able to do that in your environment.

Also note, you could use TRUNCATE TABLE, but only if you no foreign key constraints on your tables.

EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'DELETE ?'
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'IF (OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1) DBCC CHECKIDENT(''?'',RESEED,1)'

Hope this helps!

Posted in MS SQL Server, T-SQL | Leave a comment

IT Toolbox: TechNet Magazine March 2011

Posted on March 25, 2011 by Greg

The March IT Toolbox column is up on the TechNet Magazine website.

This month I covered these two tools:

  • Fast Duplicate File Finder: Get rid of all those duplicates on your NAS
  • PowerGREP: Search your filesystem fast

Check it out and let me know what you think!

And, as always, if you have a utility or application you would like me to cover, please let me know.

Posted in Microsoft TechNet | Leave a comment

Creating a SQL CLR User Defined Function

Posted on March 7, 2011 by Greg

Starting with SQL Server 2005, Microsoft added the awesome ability to reference .Net assemblies from your T-SQL procedures.  Here’s a quick 5 step overview on how to get up and running with your code-based User Defined Function.

1) Create a project from the Visual C# SQL CLR Database Project template (just so you can get the correct references and get a test harness going).

2) Add a class containing your UDFs. This one does a basic Regular Expression comparison:

public class MyUdf
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
public static SqlBoolean FnRegExMatch(string source, string pattern)
{
Match m = Regex.Match(source, pattern,
RegexOptions.CultureInvariant |
RegexOptions.IgnoreCase |
RegexOptions.Singleline
);
return m.Success;
}
}

3) Enable the CLR on your SQL Server instance through the configuration options:

EXEC sp_configure 'show advanced options' , '1'
GO
RECONFIGURE
GO
EXEC sp_configure 'clr enabled' , '1'
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options' , '0';
GO

4) Add the assembly and then create a reference to your UDF:

CREATE ASSEMBLY [MyUdfs]
AUTHORIZATION [dbo]
FROM 'C:\SqlServerClr\MyUdf.dll'
WITH PERMISSION_SET = SAFE -- we only have "safe" calls in our lib
GO

CREATE FUNCTION [dbo].[FnRegExMatch](
@SOURCE nvarchar(4000),
@pattern nvarchar(4000)
)
RETURNS BIT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [MyUdf].[FnRegExMatch]
GO

5) Now you should be able to call your function:

SELECT dbo.FnRegExMatch('my test string', '\btest\b')

Of course this is just the barest of essentials to get you going with SQL CLR UDFs, but I hope it helps!

 

Posted in C#, MS SQL Server, T-SQL | Leave a comment

Windows 7 and Server 2008 R2: Make SP1 Permanent

Posted on March 2, 2011 by Greg

If you are pretty sure you aren’t going to be rolling back Service Pack 1 on your Windows Server 2008 R2 or Windows 7 machine, you can free up some space by making it permanent.  This will remove all the pre-SP components and uninstall information from your system:

%windir%\system32\dism.exe /Online /Cleanup-Image /spsuperseded

Hope this helps!

Posted in Windows 7, Windows Server 2008 R2 | Leave a comment

IT Toolbox: TechNet Magazine February 2011

Posted on February 22, 2011 by Greg

The February IT Toolbox column is up on the TechNet Magazine website.

This month I covered these two tools:

  • PowerWF Studio: Drag and drop PowerShell Workflows
  • /n Software’s SQL Server SSIS Tasks: Extend SSIS 2008

Check it out and let me know what you think!

And, as always, if you have a utility or application you would like me to cover, please let me know.

Posted in Microsoft TechNet | Leave a comment

WordPress on Windows with IIS7 and FastCGI PHP Upgrade Error

Posted on February 12, 2011 by Greg

If you are running WordPress on IIS7 and are having trouble updating your plugins and/or WordPress itself, you might have an impersonation setting problem.  Check your PHP error log (sometimes in C:\temp).  If you see and error message like:

“PHP Warning:  touch(): Unable to create file /tmp/wordpress-3.tmp because No such file or directory in…”

it probably means you have fastcgi.impersonate set to 1 in your php.ini and the anonymous user, IUSR, doesn’t have the rights to modify files in your site directory.

To fix this, right-click on the website directory and select Properties.  On the Security tab, add the IUSR with Modify permissions.  Be sure to click Advanced after and choose to replace permissions on the sub-folders.

Posted in IIS, WordPress | Leave a comment

IT Toolbox: TechNet Magazine January 2011

Posted on January 19, 2011 by Greg

The January posts for my IT Toolbox column are up on the TechNet Magazine website. This month I covered:

  • TortoiseSVN: Great Windows front-end to Subversion
  • SecureRDP: Enhance the security of remote connections to 2003 machines
  • ATTO Disk Benchmark: Baseline your hard disks, arrays, and SSDs

Check it out here: January IT Toolbox for TechNet Magazine

I am always looking for new products to cover, so if you have a suggestion, please, let me know!

Posted in Microsoft TechNet | Leave a comment

Visual Studio: Team Foundation Server: Workspace Already Exists Error

Posted on January 7, 2011 by Greg

If you ever have the joyous experience of having to revert back to Visual Studio 2005 and you are using Team Foundation Server as your source control system, you may encounter a workspace error message akin to “workspace already exists on computer”.

This is sometimes due to the cached settings on your system. To clean this up, try the following:

SET TFAppData=%USERPROFILE%\Local Settings\Application Data\Microsoft\Team Foundation
IF EXIST "%TFAppData%\1.0\Cache" rd /s /q "%AppDataTF%\1.0\Cache" > NUL
IF EXIST "%TFAppData%\2.0\Cache" rd /s /q "%AppDataTF%\2.0\Cache" > NUL
IF EXIST "%TFAppData%\3.0\Cache" rd /s /q "%AppDataTF%\3.0\Cache" > NUL
tf workspaces /s:http://mytfserver:8080/

Note: “tf.exe” must be in your path.  If not, just add the full path to the .exe.  Also, this currently shows cache paths for Visual Studio 2005, 2008, and 2010

Posted in Team Foundation Server, Visual Studio | Leave a comment

IT Toolbox: TechNet Magazine December 2010

Posted on December 17, 2010 by Greg

Holy moly the year is almost at its end!

The December posts for my IT Toolbox column are up on the TechNet Magazine website. This month I covered these email tools:

  • MailStore Server: Archive your email for all your users
  • PST Importer 2010: Convert PST files

Check it out here: December IT Toolbox for TechNet Magazine

And, as always, if you have a tool you would like to see covered, just let me know!

Posted in Microsoft TechNet | Leave a comment

IT Toolbox: TechNet Magazine November 2010

Posted on November 23, 2010 by Greg

It’s almost Turkey Time!  The November posts for my IT Toolbox column are up on the TechNet Magazine website.  This month I covered these 3 tools:

  • StarWind iSCSI SAN Software: Easy to set up, software-based SAN
  • Workshare Compare for Excel: Compare Excel files and CSVs
  • FastTrack Scripting Host: Automate administrative tasks

Check it out here: November IT Toolbox for TechNet Magazine

And, as always, if you have a tool you would like to see covered, just let me know!

Posted in Microsoft TechNet | Leave a comment