Category Archives: SQL

Creating a Database Backup and Restoring it in Code

Posted on March 27, 2012 by Tom Becker

When running regression tests many times you have to rely on very specific bits of data to be present in your database for the correct results to come out reliably.  This can typically be handled by a few simple inserts and deletes to massage the data that you need.  Other times there is just too much data to handle in that way.  So you need to just start from scratch and re-create your database.  
Read more...

Posted in .Net, ASP.NET, SQL | Tagged , , | Leave a comment

SSIS: Use Dates in Dynamic SQL Statements

Posted on March 1, 2012 by Kevin Carter

I have seen lots of posts on how to build a date string for expressions that set the SQL Statement that will be executed in Data Pumps for example. You might need to do that if you are working with a data source that does not like date manipulation functions in the SQL statement. The solutions I have seen seem a bit complicated, but I figured out a way to do it simply. This might not be new, but I have not seen it, so here goes. First, I create a variable that is a string to hold the date.  
Read more...

Posted in Business Intelligence, SQL, SSIS | Leave a comment

Automate your test data with Red Gate SQL Data Generator

Posted on November 29, 2011 by Everett Comstock

Just about every developer out there has encountered a scenario where they need to load test the application they are creating, but simply cannot come up with enough load to make it happen. I’m not going to go down the rabbit hole that is load testing an application in this post, but I do want to talk about one component of load testing, which is working with a lot of data. I seem to always run into the situation where I’m developing an application for a client, and when it comes time to test the application in a real world scenario and need real world data, the only viable solution is to back up data from a production server and massage it to “fit” into my applications database. Bad idea. Yes, there are times when this is unavoidable, but in general, do you really want to make multiple copies of sensitive data? Also, in most cases I cannot just copy and paste the data into my database. In the past I’ve created a number of data migration utilities, data population scripts, and used a number of tools just to populate my application with data that I can use to test it. All of this takes time, and effort.  
Read more...

Posted in SQL | Tagged , , | Leave a comment

Transactional Replication Filters

Posted on November 28, 2011 by Rick Matherly

I recently needed to replicate a small portion of a database to a separate server hosting SharePoint. Only a small amount of our total user base would be able to access this site, so we decided to use Transactional replication to replicate only the users with the proper roles. This presented a few unexpected challanges, as the filters did not work as you might expect.  
Read more...

Posted in Replication, SQL | Tagged , , | Leave a comment

Use Task Name as Stored Procedure Argument in SSIS

Posted on July 5, 2011 by Kevin Carter

This is something that probably doesn’t come up that often, but I had a situation recently where a routine was called repetitively. The client didn’t want a For/Loop type of solution, they wanted to manage it in the package, so I created the stored procedure to execute the routine and it accepted the name of the task from the SSIS package as an argument to direct the function. Doing it this way, meant the client only had to copy the task and rename it to change its function. They did not have to edit the internal call for the task.  
Read more...

Posted in Business Intelligence, SQL | Leave a comment

Cleaning up the Error Logs – SQL Error 18456

Posted on June 10, 2011 by Brian Beasley

So, yesterday I was trying to resolve one issue that led to another, pretty much a daily occurrence right? This SQL issue was littering the application logs every minute with Event ID 18456, as you can see in the screenshot.  
Read more...

Posted in SQL | Leave a comment