Scripting Jobs in SQL Server

Have you every wanted to save your SQL Server jobs to a SQL script file?  It's not straight-forward from the interface, but is easily done.  Just follow these steps:

1. In SQL Enterprise Manager, open the list of Jobs by opening the Object Explorer tree to SQL Server Agent -> Jobs
2. Next, press F7 to open the "Object Explorer Details" (Can also use the menu View -> Object Explorer Details)
3. You should see all your jobs listed.  If not,  you might need to navigate through the list and find them.
4. Select the jobs you want to backup (You can use Shift and Ctrl)
5. Right click and select Script Job As -> Create To -> File
6. Finally enter a filename and press Save

I hope this makes your life easier.  Now get to backing up those jobs!

Regenerating XSD Dataset After Change

I find myself constantly having to maintain projects built with XSD datasets.  The thing I always *strongly disliked* about XSD's is their tendency to be fragile.  Whenever I make a change everything breaks and I'd end up fooling around for a 1/2 day getting it to work again.

Well, I think I've finally figured out how to make changes to an XSD, then regenerate the schema correctly.  Here are the steps that are working well for me now:

Open the XSD and make whatever changes you need.  In this sample, I'm just adding a new column to a SQL Server stored procedure.  Once I've made the change in the stored procedure, it's time to synchronize the XSD.

Go to the XSD view in Visual Studio, right-click on the object you need to change and click "Configure."

Go through all the steps of configuring that item...

You will notice that there is a file called "...Designer.cs".  This is the file auto-generated by the DataSetGenerator tool (or at least it's supposed to be auto-generated).

If this file isn't being changed, you can force it to make a new one.  First, delete the Designer.cs file (Right click => Delete).

Then right-click on the .XSD file and select "Run Custom Tool".  This will regenerate the Designer.cs file and you should be off and running.

What?  You don't have a menu item that says "Run Custom Tool"?  Then you need to add it to the XSD file type.  You do that by left-clicking on the XSD file, then go to the properties.

Under custom tool, make sure it says MSDataSetGenerator.  If it doesn't, add it.

Happy coding!

How to Fix Orphaned SQL Server Users

No, not this kind of Orphan -- too
bad we can't fix this issues as easily!

Just a quick entry this time.  However, it happens all the time: you restore a database to a new server and can't login to the database with the original usernames.  It happens because the usernames have become "Orphaned Users."

It's now easy to fix, as of SQL 2000 sp3.  It used to be REALLY hard before.

First find all your orphaned users, by running this command:

EXEC sp_change_users_login 'Report'

It will yield something like this:

jobean 0x2BB8F8255C002E47B232FB331B1698BA

From this, I know 'jobean' is an orphaned user.

So, run this command to fix it:

EXEC sp_change_users_login 'Auto_Fix', 'jobean'

The row for user 'jobean' will be fixed by updating its login link to a login already in existence.

The number of orphaned users fixed by updating users was 1.

The number of orphaned users fixed by adding new logins and then updating users was 0.

This hooks up the jobean user of the database to the jobean of the database server.  Did this fail (probably because you don't have a jobean user in the database server?)  Then issue this command to make a new user and wire it up to the jobean orphaned user.

EXEC sp_change_users_login 'Auto_Fix', 'jobean', 'login', 'password'

[Where login and password are those you want assigned to the new user]

Easy as can be.

Restoring SQL Server Database: Exclusive access could not be obtained because the database is in use

Here is the scenario - you are in a rush to restore a down SQL database.  Then you get the dreaded:

Exclusive access could not be obtained because 
the database is in use.

Don't panic.  You can do two things.

Option 1

If you are using the SQL Manager Tool, you select "Options" from the side panel.  On there you will find a checkbox to go into single-user mode for the Restore.  This will do the trick.

If this option is grayed out, cancel the restore operation, then bring it up again.  Check this box first before doing anything else.  It should just work.

Option 2

If you are using the command line, use this command:

USE master
GO
ALTER DATABASE DATABASE_NAME
SET SINGLE_USER

Now do your restore as normal.

Afterward

Don't forget to make sure you are in multi-user mode!

USE master
GO
ALTER DATABASE DATABASE_NAME
SET MULTI_USER

Finding a Dropped Table

I recently needed to find a dumped table that was somewhere in backups, but where??? Obviously, the db stores this in the transaction logs, but its really hard to find if you don't know where to look.

A great query for finding this dropped table is this:

SELECT * FROM fn_dblog (NULL, NULL), (SELECT [Transaction ID] AS [tid] FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] LIKE '%DROPOBJ%') [fd] WHERE [Transaction ID] = [fd].[tid];

This will return back everything you need to get this table off of your backup:

[Current LSN], 3 [Operation], 4 [Context], 5 [Transaction ID], 6 [Description]

Removing Spaces From SQL Server Data

Have you ever found yourself with spaces in SQL fields that just shouldn’t have spaces, such as usernames.  Well, this is an easy problem to fix with the SQL command "CHARINDEX".  CHARINDEX will return the first occurrence of an expression (such as a space) in a field.

So, you might say:

SELECT CHARINDEX(' ','Rats live on no evil star')

and what is returned is: 5 — the index of the first space in this string (a Palindrome, by the way.)  So, you can use this, along with SUBSTRING, to give you a final string with no spaces in it.  Here is an example:

SELECT UserName,
SUBSTRING(username, 1,CHARINDEX(' ',username)-1)+SUBSTRING(username,CHARINDEX(' ',username)+1,LEN(username)) AS NoSpaceUsername
FROM users
WHERE  CHARINDEX(' ',username)>0

This will yield results, such as: 
UserName       NoSpaceUsername
DE SILVA       DESILVA
DEL REY        DELREY
DEL TORO       DELTORO

I hope this is helpful!