Altering a table column from varchar to datetime is pretty straight-forward in the SQL Server Management Studio (SSMS), until you look at the tsql generated.  For many operations SSMS will generate a tsql script that will:

• create a temporary table
• drop all the foreign keys
• copy the data to the temporary table
• create the new table with the correct data type
• copy the data to the new table
• drop the temporary table
• add the foreign keys back

That is a lot of operations and on a really large table of millions of rows may take a very long time to complete.

SQLCricket comments that it is possible to change the options in SSMS to warn on table operations, i think this is only in sql 2K8.

SQLPuma comments that it is possible to change a varchar() to a datetime via tsql with an alter table alter column command.

In this particular case we were modifying a varchar(10) to a datetime.  All the data was in a valid format.  The easiest method is to alter the column, another method to complete this is to:

• rename the existing column (tmp_varchar etc)
• add a new column with the correct name
• update the new column (in batches if necessary)
• drop the original column (now with a tmp_name)

This is very quick, much safer operation and is much "nicer" to the database log file.  Example of tsql is below:

          
            CREATE
            TABLE dbo.testing
(test_id bigintNOTNULLIDENTITY (1,
1) primarykey,
some_dt varchar(10)
) GO insertinto testing
(some_dt) values ('2009-01-01') insertinto testing
(some_dt) values ('2009-01-02') insertinto testing
(some_dt) values ('2009-01-03') insertinto testing
(some_dt) values ('2009-01-04')
go EXECUTE sp_rename 'dbo.testing.some_dt',
    'Tmp_some_dt', 'COLUMN' GO AlterTable testing Add some_dt DateTimeDefault('1900-01-01') NOTNULL GO Update testing
    Set some_dt
= Convert(DateTime,Tmp_some_dt)
GO AlterTable testing
    DROPColumn Tmp_some_dt
GO select * from testing
go --drop
table testing
        

Nothing more annoying than something that should, but doesn't work.

Often we allow users to view jobs (not necessarily administer), but just the ability to go view them etc.

Generally this was granted through the TargetServerRole in the msdb database.  Works fine in sql server 2000 enterprise manager, but with SQL Server Management Studio connecting to a sql server 2000 Instance, it does not work.

Best we can tell this is related to a "Phantom" 208 error.  A 208 error is generated when one compiles a stored procedure which declares and uses a temporary table.

http://www.mcse.ms/printthread.php?threadid=1265640

Though if you run a trace profile everything completes fine, but some how ssms "sees" the 208 error and "throws" an error.

Unfortunately many users are not adept enough to run the commands through TSQL, they need a GUI.

So far the only solution was to install sql server 2000 client tools, which we were hoping to only have to install sql server 2005 client tools.

Recently I ran into an issue using SQL Server management Studio to edit a SQL Agent scheduled job.

Specifically the business requirement was to have a job run every 87 minutes.  When I went to edit the job, it would let me type in 87 minutes but when you tabbed off or saved, it changed to 60 minutes!

Ended up having to edit this using TSQL, disabling the current schedule and adding a new schedule that was set for 87 minutes, runs and works no problem, you just can't edit it through the GUI.

Thankyou SQL Server Management Studio.