May 6, 2004 at 8:11 am
I am running the following SP. Once against SQL2K on WIN2K and it works just fine. I am running the same SP against another server running SQL2K on WINDOWS 2003 and it fails ! The reason it is failing is because the date convertion is outputing spaces i.e. the file name is "R:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG2004 05 06 14 36.log", whereas on the successful machine it is
"R:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG200405061436.log"
Is there a setting on SQL2K that I need to know about ?
Can anyone please help ?
thanks
CREATE PROCEDURE AT_ArchiveSQLLog
AS
declare @LogString nvarchar(255)
declare @Cmd nvarchar(500)
declare @datestring nvarchar(16)
/*Create a datestring to concatinate with the errorlog filename.*/
select @datestring = ltrim(rtrim(replace(replace(replace(convert(nvarchar(16), getdate(), 120), '-', ''), ':', ''), ' ', '')))
CREATE TABLE #Reg ( Value nvarchar( 255 ) , Data nvarchar( 255 ) )
/* Create a temp table to store the SQL Server startup registry parameters. */
INSERT #Reg
EXEC master..xp_regenumvalues N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
/* Select the string for the errorlog location. */
SELECT @LogString = replace(data, '-e', '') FROM #Reg
WHERE Data LIKE '-e%'
/* Create the DOS command to copy the errorlog.1 file to errorlog.log */
set @Cmd = 'copy "'+@LogString+'.2" "'+@LogString+@datestring+'.log"'
exec master.dbo.xp_cmdshell @Cmd
drop table #Reg
GO
May 7, 2004 at 2:58 am
Check the compatibility levels for the two databases. Level 80 gives the result you are looking for. Level 65 treats empty strings differently and the ltrim/rtrim functions behave differently. See BOL for compatibility differences.
cheers
Tony
May 7, 2004 at 6:12 am
yip, to check compatibility :
exec sp_dbcmptlevel , 'databasename'
to change
exec sp_dbcmptlevel , 'databasename', *
* to the desired level
May 11, 2004 at 3:42 am
checked compatibility and coth are running at 80. Any more ideas ?
May 12, 2004 at 2:26 am
SORRY IT WAS A COMPATIBILITY PROBLEM. IT HELPS WHEN YOU CHECK THE CORRECT DATABASE !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply