I love this tool. It’s a free add-on if you own SQL Prompt, RedGate’s version of intellisense, which is better, in my eyes, than Microsoft’s. It uses SQL Prompt’s FTS collection to search for any piece of string in any DB object. It’s crazy fast. What I like the most though, is the ability to right click on a result, and move the focus to appropriate DB object in the explorer panel. Hit CTRL+ALT+D to trigger the search screen and start typing away.

I can’t say how many various problems I’ve had connecting to SQL Server. I’ve lost count at this point. This was the last one I had, which I’ve encountered a number of times and always successful to fix it.

Cannot connect to x.x.x.x.

Additional Information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server….

Now usually when I encounter this error, I do something suggested here at Pinal Dave’s site. This time nothing mentioned there worked. What did work, was me explicitly selecting the Network protocol, and not leaving it as the :

Still wondering why I have to do this, as I’ve never had to explicitly select the protocol before, and nothing on the server has changed in years. Will continue to investigate.

I needed to delete 30,000 files that took about 60 GB. I know traditionally it takes forever to delete it in Windows. Even using the “del” command in the console, it’s always been slow for me. Enter Robocopy. I never gave it a chance until today. The way it can delete is by syncing directory against an empty one which was just what I needed. It was able to do it in about 50 seconds. Awesome.

You’ll have to download Robocopy as part of the Windows Server 2003 Resource Kit Tools.

What I used to delete this big directory:

robocopy /MIR c:\empty-directory c:\my-files-to-delete

Currently I use RedGate’s SQL Data Compare to see differences in data across databases. It’s a great product. One thing I love about RedGate is the usability of their products. All of them are intuitive and easy to use. What if you don’t have the cash to do this though?

You could use the EXCEPT (introduced in SQL Server 2005) clause to do your comparison. For example:

  SELECT  *  
  FROM    #table2 -- returns 30 records
 
  EXCEPT
 
  SELECT  *  
  FROM    #table1 -- returns 10 records.

You get a result set of 20 records if those 10 records in the table1 statement are in table2. You can think of it as subtracting records from table2 that are in table1. You will get the end result of 20 records.

The following statement builds on that taking the end result and putting it back into table1. This is useful if you want to sync an outdated table (in this case table1) with another table that has additional records (table2).

INSERT INTO #table1
  SELECT  *  
  FROM    #table2 -- latest data table (we'll be copying from here)
 
  EXCEPT
 
  SELECT  *
  FROM    #table1 -- outdated table (has old data)

Other tools to compare data across tables: CompareData may let you do what you want, and its table compare is free unlimited, I believe, for the evaluation version. The great thing about this tool is also that it will check results of two SQL statements / stored procs. Useful when unit testing.

Also you can use TableDiff.exe in your “C:\Program Files\Microsoft SQL Server\90\COM” directory. Learn more about it at the SQL team website.

Here’s a quick and simple way to do this. I often have to show and hide set of panels based on whether something is checked or not. jQuery code is pretty straightforward and it comes in handy.

$( function() {    
 
  function toggleCheckbox( trigger, hidethese )
  {
    if ( $( trigger ).attr("checked") )
    {
      $( hidethese ).hide();
    }
    else
    {
      $( hidethese ).show();
    }
  }
 
  // jQuery object that has your checkbox
  var triggerCheckbox = $( "#showPreonDescription" );
 
  // jQuery object for collection of elements you want to hide
  // For this example, I could've also just done #panel by itself
  // to hide everything
  var panelsToHide = $("#subpanel,#maincontent,#nav");  
 
  // Call the function on click.
  triggerCheckbox.click(
    function ()
    {
      toggleCheckbox( triggerCheckbox, panelsToHide );
    }
  );
});

I have the demo if you want the source as well.

Whipped up this UDF while working on a project. It checks to see if the list is valid to use in a TSQL WHERE IN Clause. I wanted to check for a list of valid positive integers (including 0), ignoring spaces.

<cffunction name="isNumericList" returntype="boolean" hint="Check for either single positive number or a set of positive numbers. Spaces ignored." >
 
  <!--- Useful when inserting into an "IN" TSQL list in the WHERE clause.  --->
 
  <cfargument name="list" required="true">
 
  <cfargument name="delimiter" required="false" default=",">   
 
  <cfset var isNumericList = false />
 
  <cfif REFind( "^(\d+)$|^(([\d\s]+#Arguments.delimiter#)+\s*\d+)$", Trim(Arguments.list) ) >
 
    <cfreturn true >
 
  </cfif>
 
  <cfreturn isNumericList >  
 
</cffunction>

Now let’s test it!

<!--- Dummy Data --->
<cfset dataList = ArrayNew(1) />
<cfset dataList[1] = "456c" />
<cfset dataList[2] = "456" />
<cfset dataList[3] = "45c," />
<cfset dataList[4] = "5,,," />
<cfset dataList[5] = "565,651,34,643232,45" />
<cfset dataList[6] = "454,c,45,5454,32" />
<cfset dataList[7] = "121.45,43,565,1,1,2" />
<cfset dataList[8] = "43,54,65,1," />
<cfset dataList[9] = "67,54,73,436," />
<cfset dataList[10] = ",6565,656,77,32,3" />
<cfset dataList[11] = "" />
<cfset dataList[12] = ",43656" />
<cfset dataList[13] = "4365,  55,31,24,   5,   5  ,1,      34" />

Now let’s use it!

<!--- Use it! --->
<cfoutput>
   <cfloop array="#dataList#" index="i">  
      [#i#] => #isNumericList(i)#<br />
   </cfloop>
</cfoutput>

Results!

[456c] => false
 
[456] => true
 
[45c,] => false
 
[5,,,] => false
 
[565,651,34,643232,45] => true
 
[454,c,45,5454,32] => false
 
[121.45,43,565,1,1,2] => false
 
[43,54,65,1,] => false
 
[67,54,73,436,] => false
 
[,6565,656,77,32,3] => false
 
[] => false
 
[,43656] => false
 
[4365,  55,31,24,   5,   5  ,1,      34] => true

Winrar is a great tool used for compressing files. It also has a command line tool called “rar.exe” to compress files, in case you want to batch it up. Here’s an example of the most common switches I use.

"C:\Program Files\WinRAR\rar.exe" a -m5 -r -ep1 "C:\temp\Work\Upgrades\2010-02-06\111.rar" "C:\temp\Work\Upgrades\2010-02-06\Post-Upgrade\"

First path is the location of the rar file to create. The second path is either the location of the file(s) or directory to compress.

options used
 
  -ep1  Exclude base directory from names
  a     Add files to archive
  -r    Recurse subdirectories (will compress entire directories)
  -m5   Set compression level to maximum

You can even assign a password to it using the -p switch

One handy trick that I used to do was use the function OBJECT_DEFINITION() to view the code of a sproc. It’s a handy little function that doesn’t get much love as OBJECT_ID or OBJECT_NAME(). Let’s say we want to get the code of the sproc dbo.uspGetBillOfMaterials from DB AdventureWorks. We do like so:

SELECT OBJECT_DEFINITION( OBJECT_ID('dbo.uspGetBillOfMaterials') )

You get a result that may not be too pretty. In this case, use PRINT rather than SELECT, since SSMS strips out line breaks to squeeze it in the cell.

Now let’s search all available user sprocs in the database to search for the string “%GetBillOf% :

SELECT  OBJECT_DEFINITION( p.[OBJECT_ID] ) AS code 
FROM    sys.procedures p
WHERE   OBJECT_DEFINITION( p.[OBJECT_ID] ) LIKE '%GetBillOf%'

Now, I do longer search this way since every day I have a job that writes out all DB objects (tables, sprocs, etc.) to a individual files and they get indexed via Copernic Desktop Search. I script them out using SQL Compare.

Unfortunately, I don’t know why, OBJECT_DEFINITION() doesn’t work on tables. It’s been a wanted feature by the community. If you want to learn more about this function, check out:

• How to script all stored procedures in a database
• Script Table definitions using TSQL

Here’s the requirements document template (the “Product Definition”) I created for use when I was a project manager. Feel free to use and change as needed. It serves as a basis for functional and non-functional requirements. To check out what other documents may be needed in a project, check out my Project Documents post.

Download the Word document.

Why do this?

In case you don’t have shell access to your server from your hosting provider. Also if the provider doesn’t let you writing files locally from a script.

If you want to backup from a remote machine to your local machine:

(Don’t use the < or > symbols when you type it, except the last > that redirects to a file.)

mysqldump --opt -Q -h <www.narutorp.net> -u <username> --password=<chocolatepizza> <database_name> > <C:\temp\mysql\backupdb1.sql>

If you want to backup from a remote machine to that same remote machine.

mysqldump --opt -Q -h <127.0.0.1> -u <username> -p <password> > <filename.sql>

You can also pipe that to gzip to compress, and schedule it as needed.