This isn’t a task I perform often but yesterday, I had to compare a table from one of the DBs w/ the actual file to try and ID the differences because I was getting alert stating: datetime field overflow. Quickest way would be to BCP out the table and then compare it using Beyond Compare. [...]
Archive for March, 2007
disable & enable triggers
I’m not sure if this is a new feature in sql 2005 or not but I don’t recall ever seeing it in SQL 2000. I saw it this morning in one of my co-workers .sql file. In the past, I had to drop the triggers and recreate them DISABLE TRIGGER { [ schema . ] [...]
quotename fn
I just saw this new function being utilized in one of our script: quotename. It is fairly simply to use. Now you don’t have to write anything like the following: declare @database_name varchar(50) set @database_name = db_name() set @database_name = ‘[' + @database_name + ']‘ + ‘;’ select ‘use ‘ + @database_name or declare @database_name [...]
insert select
quick and easy way of adding multiple rows into a table using one INSERT INTO statement insert into @table (a,b,c) select ‘who’, ‘what’,'when’ union all select ‘why’,'how’,'mom’
database mirroring
When setting up database mirroring, if you happen to RESTORE LOG, you will need to recreate the mirroring endpoint.
tail-log backups
I backed up a database on serverA running Yukon (SQL 2005) today and tried to restored it to serverB. I got the err The tail of the log for the database “dbName” has not been backed up. Fix is to backup the log file using the WITH RECOVERY option, run your backup, and then restore [...]
xp_cmdshell
At my old job, the developers would use xp_cmdshell but I never cared for or looked at it until now … it is pretty damn useful: declare @sourceDir varchar(500), @archiveDir(500) exec xp_cmdshell = ‘move /Y ‘ + @sourceDir + ‘\*.dat ‘ + @archiveDir And you can do many things with the output, parse it for [...]
determine job duration
here’s a script that was provided to me, it determines how much longer a job will run before its completed.
moving sql2005 sys dbs
Okay so, sql 2005 book online has a great article on how to move the system databases. Last week, I was able to test drive their documentation. Everything looks good until I ran into some permission problem. When you attempt to bring up the master database in single-recovery mode using: net start mssql$instance /f /T3608 [...]
Error 14258
I got this error a while back: Error 14258: cannot perform this operation while sql server agent is starting… Long story short: This problem was caused by the MAPI client. Reconfigure Outlook client