Exporting VARCHAR MAX and NVARCHAR MAX to CSV
Learn how to deal with commas and new lines in CSV files that are destined for Excel. Use the REPLACE command to clean up your outfiles.
Read articleTopic 01
SQL Server & Windows
Database administration, T-SQL, PowerShell, and the systems around them.
When Excel will not convert a date to a date
When importing data from CSVs into Excel, some dates are not recognized as dates. Learn how to convert these dates to real Excel dates.
Read articleDisplay Virtual Machine IPs using PowerShell
Find the IP addresses of running Hyper-V virtual machines from a single PowerShell command.
Read articleUseful SQL Server Date Formats
Here are a few date formats that I've found useful over the years.
Read articleFinding the SSRS Log File
Discover how to find and examine the SQL Server Reporting Services log file.
Read articleT-SQL Tuesday March 2019 – Dipping into the Cookie Jar
This month's TSQL Tuesday is hosted by Shane O’Neill (B/T). The challenge this month is to write about “Dipping back into the cookie jar” or using the memories of a past challenge or…
Read articleT-SQL Tuesday July 2018 – Working with Dates
This snippet is so nice, I'm posting it again! I've utilized the script below at least once a week, for either reports, or working with large data sets.
Read articleList all Active Directory Groups for users in an Active Directory group
List all active directory groups for members of a target active directory group via Powershell script.
Read articleGenerate fresh data for WideWorldImporters
See how to use built in stored procedures to grow the WorldWideImporters SQL Server database with new, current data.
Read articleLoop over dates in SQL Server
I've written the script below at least once a month, for either reports, or working with large data sets.
Read articleStored Procedure Results in an SSIS Message Box
I was recently asked if it is possible to display the results of a query / stored procedure in an SSIS Message box. The answer is Yes, with a bit of C#.
Read articleRecover the missing Report Data tab in SSRS
Learn how to recover the Report Data tab in SQL Server Reporting Services (SSRS)
Read articleView SSIS Variables via Message Box
You can use a VB or C# message box to view the current value of a variable. No more guessing!
Read articleFind and count words in a string via SQL.
For all the power of SQL Server, some things a still missing.
Read articleEnable CLR on SQL Server
Learn how to enable .NET CLR on SQL Server.
Read articleIncorrect Syntax Near go revisited
Incorrect Syntax near go. We've all gotten this error before, but why does this happen? Watch a video explaining why you cannot use GO inside of sp_executesql
Read articleFind your SQL Server Profiler Template Path
Learn where SQL Server Profiler saves your Templates.
Read articleDisplay a Page Count in SSRS
Learn how to add a date printed and a page count in the footer of your SSRS report. Even if you have errors with Globals!OverallTotalPages.
Read articleDisplay results Horizontally in SQL Server
Learn how to use SQL Server's CTEs to display results in a horizontal format instead of the standard vertical format.
Read articleFind numbers within a string in Excel
Learn how to use excel to extract numbers from strings.
Read articleWorking with Seconds in Excel
I recently had to use back end data from SQL Server to deliver several reports in excel.
Read articleUsing datediff to display results as HH:MM:SS
DateDiff is a great tool in SQL Server. But you have to extend DateDiff a bit to get things in a nicer format, such as HH:MM:SS.
Read articleLong TRANSACTION Identifier is Long
If you're like me, you like using descriptive variable, procedure and function names.
Read articleReset an Identity Column in SQL Server
Reset and verify IDENTITY values in SQL Server using DBCC.
Read articleDetermine Tables Sizes in SQL Server
Everyone is worried about their database size. Even though storage is relatively cheap, if your database is growing linearly or exponentially, you're going to run into issues down the road.
Read articleUse SQL Server DMVs to find a table’s IDENTITY Column
Use SQL Server DMVs to find a table's IDENTITY Column. The SQL Server DMVs sys.objects and sys.columns contain valuable information that can identify IDENTITY Columns.
Read articleIncorrect syntax near ‘GO’ when creating Dynamic SQL
Learn about line breaks when creating dynamic SQL.
Read articleRemote Desktop won’t go back to Full Screen after Minimization in Windows 7
Learn how to get your remote desktop back to full screen after Minimization
Read articleUsing Windows Media Center on a Dual Screen Setup with Windows 7 x64
If your PC came with Windows 7, chances are you already have Windows Media Center installed.
Read articleQuerying an Oracle linked server fails
The other day, I needed to pull some data from an Oracle server on our network.
Read articleGreat Circles in SQL Server 2005 and SQL Server 2000
Recently, I had a request to calculate the distance between 2 GPS coordinates.
Read articleAutomatic Index Rebuilding
While working on a large project, I found that the queries were running slower and slower as the days went on.
Read articleKill all Connections to a Database in SQL Server
One of the databases I work with runs a ton of updates overnight and in the early morning.
Read articleImporting Data into SQL Server – Using Excel Shortcuts
I'll be the first to say, the switch from DTS to SSIS was painful.
Read articleImporting Data into SQL Server – Using the INSERT statement
Sometimes, the easiest and quickest way to get data into SQL Server is to simply use an INSERT statement.
Read articleImporting Data into SQL Server – Using the Import Export Wizard
Today begins the series on importing data into SQL Server 2005/2008 using the various built in tools.
Read articleStoring Computed Columns in SQL Server
From time to time you will run into a case where you need to store computed columns in your SQL Server tables.
Read articleCould not install the custom actions
Learn how to fix the error “Could not install the custom actions” message
Read articleView All Permissions for Objects in SQL Server 2008
There are times when you need to audit user permissions to ensure that things aren't getting inserted, over-written, or deleted.
Read articleOverwrite files in a DTS using ActiveX
Several of my DTS jobs require processing a file and then archiving the file.
Read articleDelete old DTS packages in SQL Server 2008
Learn how to delete DTS packges using TSQL
Read articleCommon Table Expressions (CTE) in SQL Server 2005 and Up
Thanks to the team at TechRepublic.com and BOL, I have used CTEs to delete duplicates with ease in SQL Server 2005 and higher.
Read articleUsing ActiveX to check the age of a file in SQL Server
Now, you can use ActiveX to check the age of a file before proceeding. The code assumes that you have already assigned the file location elsewhere in the DTS Package
Read articleUsing ActiveX to Dynamically set a Variable in an SQL Server DTS Package
From time to time, you will need to dynamically set a variable in the middle of your DTS packages.
Read articleUsing ActiveX to set query dynamically in SQL Server
Learn how to use ActiveX to dynamically change a query.
Read articleUsing NOCOUNT, ANSI Warnings and Temp Tables in SQL Server
Here are a few useful code fragments that I use all the time.
Read articleAdding Checks after a table is created in SQL Server
Did you create a table, only to find that you need to add a check constraint?
Read articleAdding Foreign Keys after a table is created in SQL Server
Well, you knew exactly what you needed when you created a table.
Read articleChange a Date Format Using ActiveX and SQL DTS Transformations
Let's say you are importing data from a flat file to SQL Server via DTS.
Read article