Blog » Entries from 1/14/2009
Here's a script I knocked up which gives you some insights into your Reports Catalog in Reporting Services.I got frustrated with the Reports Manager site and its inability to give you a wholistic view of subscriptions, and the script blew out a little from there as I had ...
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 bigint NOT NULL IDENTITY (1, 1) primary key, some_dt varchar(10) ) GO insert into testing (some_dt) values ('2009-01-01') insert into testing (some_dt) values ('2009-01-02') insert into testing (some_dt) values ('2009-01-03') insert into testing (some_dt) values ('2009-01-04') go EXECUTE sp_rename 'dbo.testing.some_dt', 'Tmp_some_dt', 'COLUMN' GO Alter Table testing Add some_dt DateTime Default('1900-01-01') NOT NULL GO Update testing Set some_dt = Convert(DateTime,Tmp_some_dt) GO Alter Table testing DROP Column Tmp_some_dt GO select * from testing go --drop table testing
I’ve always thought this was pretty slick… We’ve all gone into Query Analyzer or SSMS and ran select, insert, or update statements. You write the statement, hit execute, and it returns the data or performs the action. What we’re doing here is no different except that we ...
Well let me elaborate a bit.
The use case (which I’m now hating R.K. for bringing up cause I wont let it go now LOL ) where this applies is….
“I want to insert into a temp table the results of a dynamic sql statement”
The caveat being this ...
Get Syndicated on SQLServerPedia
Have you been blogging for six months or more? Want to see your own posts here on SQLServerPedia too? Follow these easy steps to increase your visibility in the community and attract more visitors to your blog.
Create a Blog Category for Syndicated Posts
Using your blogging software, create a new category for posts that should be syndicated on SQLServerPedia and add a few posts to it. (This allows you to keep off-topic posts from being syndicated.)
Find the URL to Your Syndicated Posts Feed
Most major blogging platforms will create a RSS or Atom feed for each category you create. On WordPress blogs, the feed can be accessed by appending
/feed/to the category page URL. On Blogger blogs, the feed for a label is at
.blogspot.com/ feeds/ posts/ default/-/ <categoryname>
Email Us with Your Feed URL, a Picture, and a Bio
Once you're ready to set up syndication, send us an email introducing yourself. Include the URL to your feed, a headshot of yourself, and a short professional bio.