Undocumented stored procedure for looping through all databases on a server

If need to do something to all your databases there is a stored proc for you.  Its called sp_msforeachdb.
This will loop though all your databases and perform a command for you.  It’s similar to the foreach table sp I wrote about here.
There is one parameter @command.

Let’s get right to an example.

EXECUTE sp_msforeachdb ‘USE ? IF DB_NAME() NOT IN(”master”,”msdb”,”tempdb”,”model”) BACKUP DATABASE ? TO DISK = ”G:?.bak, WITH INIT”’

The ? gets replaced with the database name.
So that will backup any user database to the the G: drive.

This can simplify any looping code you have.

[tweetmeme only_single=”false”]

Windows 7 GodMode (or 32bit Vista)

Windows 7 (or 32bit Vista) have a God Mode, which gives you all the settings of control panel in one folder (without having to browse around in control panel).

WARNING: THIS DOES NOT WORK ON 64bit Vista OR 64bit WINDOWS SERVER 2008.  DO NOT FOLLOW THESE INSTRUCTIONS IF YOU ARE RUNNING 64bit Vista OR 64bit WINDOWS SERVER 2008.  IF YOU DO EXPLORER.EXE WILL REBOOT OVER AND OVER.
Here are the instructions for removing GodMode, https://chrisbarba.wordpress.com/2010/01/06/how-to-remove-godmode-from-vista-64bit/

GodMode

Here are the instructions for adding a GodMode folder.
Right click on the desktop and add a new folder.
Name it the following GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}

After that you will have this folder on your desktop.

Entity Framework Model won’t update

So you have a Entity Framework model all laid out and you make major changes to a table (rename fields and add fields).

Now you need to update your model (you don’t want to delete it and recreate it because you have added stored procs, etc).

If you delete your table off the model and try updating your model from database.  There are some scenarios where it won’t see that you need to add back the table you deleted (don’t ask me what they are).  The table won’t show in the designer, but you won’t be able to add it back.

To get around this problem, right click on the edmx file and open with xml editor.(It will ask you if you want to close it, if you have it open, click yes)

Warning: I would recommend you back up your files before editing your xml.

Now search for your table name and delete any reference to it.  Save the xml and close it.  When you open your edmx file you will not having any trouble adding back you changed table.

Entity Framework model won’t open

The entity framework creates a model of your database, a .edmx file.  If you open the file and close it, then you can’t open it again until you close visual studio and reopen it.  That no fun.

Here’s a work around, right click you edmx file and choose Open With…
image
You will get a choice of what you want to open the model with.  If you choose XML Editor you will be able to open just the xml.  (If may warn you that your model is already open and will ask if it can close it, choose yes).
Now it’s open, but you see all the XML.
Right click on the edmx file again and choose Open.

Your model will open back in the familiar view that your used to.

Grant execute permissions to all stored procs easily

Granting execute permissions can be stored procedures can be quite a pain, especially when you have dropped and recreated your stored procedures when moving between environments.

You can go to each stored procedure and right click each one and change the permissions.  That takes forever.

A quick way to resolve the issue (in SQL 2005 and SQL 2008) is to create a role and give it execute permissions at that database level.

Here is the SQL to run:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

 

This will create a SQL role and anyone you put in it will will have execute permissions to all stored procedures in the database.

This doesn’t work if you only want to grant execute to a subset of stored procedures.

Technorati Tags:

SQL Server 2008, can’t save changes to tables

When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the management tools will not allow you to save the changes.
You will get an error stating, “You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table be re-created.”

What a pesky problem.  It prevents you from making progress when you making database changes. It’s a good thing you can turn it off.
This is caused when you make one of the following changes:

  • You change the Allow Nulls setting for a column.
  • You reorder columns in the table.
  • You change the column data type.
  • You add a new column.

Here’s how you fix it.
In Management Studio, go to Tools –> Options –> Designers –> Tables and Designers and uncheck the Prevent Saving Changes that require table re-creation option.
image

Now Management studio will work like expected.

 

WARNING:
Microsoft recommends you don’t turn this option off and that you use T-SQL to make changes to your tables.
Turning off this option will conflict if you have Change Tracking feature turned on.  If you turn off this option and make a change to table with change tracking on all the tracking changes  for that table will also be deleted.
To check if you have change tracking turned on, right click on your table and go to properties, look for the option Change Tracking.

image

Here is the kb article from microsoft: http://support.microsoft.com/default.aspx/kb/956176

SharePoint WSP right click add solution

For anyone doing SharePoint development I have one of the best tips I would recommend to anyone (a coworker of mine, Bo Cherry, found this tip).  You can add a solution with just right clicking on a WSP.  No more stsadm commands (mostly).  You have to edit your registry (don’t forget to back it up first), but it is well worth it.  From what I have seen, if you use the right click option, you have less trouble deploying solutions  than just using stsadm to deploy.

Just open notepad, add the following code and save as a .reg file.
Now double click the new file and there you go.  The add solution choice will only come up when you right click on a WSP file.

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\.wsp]
@=”wspfile”

[HKEY_CLASSES_ROOT\wspfile\shell]

[HKEY_CLASSES_ROOT\wspfile\shell\Add Solution]

[HKEY_CLASSES_ROOT\wspfile\shell\Add Solution\command]
@=”C:\\Program Files\\Common Files\\Microsoft Shared\\web server extensions\\12\\BIN\\stsadm.exe -o addsolution -filename \”%1\””

 

I have tried changing the code to add a deploy solution to the context menu, but it was very flaky.  Sometimes it worked and sometimes it didn’t.  Not worth the trouble.  So I just use the UI to deploy the solution.