Thinking

Tuesday, February 22, 2011

SQL Server [Version and Edition Upgrades ] And [Backward Compatibility]

SQL Server Version and Edition Upgrades
http://msdn.microsoft.com/en-us/library/ms143393(v=SQL.100).aspx

SQL Server Backward Compatibility
http://msdn.microsoft.com/en-us/library/cc707787(SQL.105).aspx

Tuesday, February 15, 2011

Copy file using T-SQL in SQL Server 2005

sp_configure 'Ole Automation Procedures',1
go
reconfigure

DECLARE @hr int
DECLARE @ole_FileSystem int
DECLARE @True int
DECLARE @src varchar(250), @desc varchar(2000)
DECLARE @source varchar(255), @dest varchar(255)

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @ole_FileSystem OUT
if @hr <> 0
begin
exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
raiserror('Object Creation Failed 0x%x, %s, %s',16,1,@hr,@src,@desc)
return
end

SET @source = 'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG.1'
SET @dest = 'D:\Backups\ErrorLogs\'+ Convert(varchar(16), Getdate(), 106)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4) +'ERRORLOG.txt'

EXEC @hr = sp_OAMethod @ole_FileSystem, 'CopyFile',null, @source, @dest
if @hr <> 0
begin
exec sp_OAGetErrorInfo @ole_FileSystem, @src OUT, @desc OUT
exec sp_OADestroy @ole_FileSystem
raiserror('Method Failed 0x%x, %s, %s',16,1,@hr,@src,@desc)
return
end

Monday, February 14, 2011

Update SQL Server Agent Error Log location in SQL Server 2005

USE [msdb]

sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
sp_configure

GO

EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT'

GO