SQL This!

I dunno if you've heard of Red-Gate. but so far they've created some very top notch software to help out with both webservices and Sql Server. I've personally tried both their Sql Data Compare and Sql Compare wich helps match both schema and data accross diffrent databases and servers. This has many benefits as you can easily work in a test environment and have the ability to work with some close to live data. and after you've completed your work you can propagate the changes to your test environment with out a hassle not to mention you can minimize the time it takes to roll changes to your live environment. But really what I wanted to talk about today was Sql Prompt. It's Intellisense for Sql Server. That's it. Well that's not just it. It loads a systray app in your system and hooks directly to your Query Analyzer. Off the cuff I found it to be extreamly effective. I started to write my Select statement and it was able to appropriately give me bac...

I cruised the net last week looking for a simle "Find and Replace" script. sorta like a split for VB only that I need the replace to occur at every n th character. Not finding what I wanted I ended up writing my own, the idea was that I'd start at the lenght that my string needs to be then crawl backwards until I find a space (or breakpoint) then replace it with my Replace string. There may be a better way.. if you have one feel free to comment. CREATE FUNCTION dbo.SplitAndReplace ( @String AS VARCHAR(8000), @SplitString CHAR(1), @ReplaceString VARCHAR(255), @BreakInterval AS INT ) RETURNS VARCHAR(8000) AS BEGIN /* SET @String = 'This is as very simple text that needs to be broken down for processing in the external system, the process should auto insert an exclamation-pound sign in lew of a space that is at 60 characters' SET @BreakInterval = 60 SET @ReplaceString = '!#' SET @SplitString = ' ' */ DECLARE @X AS INT DECLARE @Y AS INT SELECT...

Sometimes you just have to know How to Set Up a SQL Server Stress Test . SQLIOstress is a bit diffrent than SQLIO wich actually gives you back only IO resutls, whereas SQLIOstress will yeild results in the manner that SQL maintains files.

UPDATED! 11/11/11 Every DBA runs into the following topic from time to time in their career, How Do you go about Migrating Logins from One SQL Server to Another ? From time to time you'll want to move actual sql login users to another server be it an upgrade, or if you have a good development - test - production cycle, you might be more used to this. Or maybe just maybe you're starting this cycle out, initially just run the Sproc provided by Microsoft called sp_help_revlogin There are two methods posted on the Microsoft site: Method 1 is for Sql Server 2000 to 2000  KB 246133 Method 2 is for Sql Server 2000 to 2005  KB 246133 Method 3 is for Sql Server 2005/2008 KB 918992 You will need to use Query Analyzer for Sql Server 2000 or Management Studio for 2005/2008  Copy the results and paste them to the new query window Go to the source server and open up a new query window and be sure you are pointing to your source database check that your Management s...

Even tho this is a Sql Blog, the following actually is related... Many developers will often use Microsoft Access as a viable Front End to Sql Server instead of using VB or even VB dotNet. This is often a quick way to leverage rapid development. However if you've ever used Access you'll quickly find that it's not the friendly little IDE that it claims to be. When you attempt to leverage it's power as bound data application, you'll often find yourself wrestling within the clutches of it's Before Update, Current and After Update Events. I can say you haven't known how to really hate, until you've battled it out with these events. In a simple application it all works fine, in a typical Access MDB format, binding data to forms is common and relatively easy and painless. Try it with an ADP (Access Data Project). The Before Update event can easily be avoided and replaced with a database trigger. Trigger's to Sql Server are what Before Update...

Setting up xp_cmdshell securely can be a challenge. Generally only sysadmins can execute the xp_cmdshell. It is actually a very huge security hole if you choose to program your website w/ the sa account as you lay your database in the path for SQL injections. This can be typical if the originating server or application is one that you are taking over. However, there will be instances where you will need to grant mere mortal users access to the xp_cmdshell, this can be done via the sql agent proxy, this gives you the ability to assing a generic nt user the ability to execute a limited range of file object manipulation. The following Microsoft article goes into the details for setting up your SQL Server agent proxy. How to configure a SQL Server Agent proxy account to enable non-system administrators to execute the xp_cmdshell extended stored procedure in SQL Server 2000

When you need to quickly find out diffrences of data/fields between two tables, there is nothing easier than this script: Table Compare