sp_procoption not working
-
I've created the following stored procedure that archives the SQL Log. It uses xp_cmdshell to copy the errorlog.1 file to errorlog<datestring>.log. It works fine when I run it manually however when I use sp_procoption to have it run automatically it shows that it runs in the SQL Log but the file is never renamed.
Another oddity is that it runs against my ReportServer database rather than the master. (I have reporting services installed on that server which has it's own startup SP.)
Suggestions?
CREATE PROCEDURE usp_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 a temp table to store the SQL Server startup registry parameters
CREATE TABLE #Reg ( Value nvarchar( 255 ) , Data nvarchar( 255 ) )
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<datestring>.log
set @Cmd = 'copy "'+@LogString+'.2" "'+@LogString+@datestring+'.log"'
exec master.dbo.xp_cmdshell @Cmd
drop table #Reg
GO
exec sp_procoption N'usp_ArchiveSQLLog', N'startup', N'true'
GO
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek) -
This was removed by the editor as SPAM
-
Did you create it in the master db?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I tried it too. The proc executes but the statement:
INSERT #Reg
EXEC master..xp_regenumvalues N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
does not work on startup. I changed #reg to a permanent table and removed the drop table statement so I could check the contents. Odd.
I checked the registry and these values are there when SQL Server is stopped. As you said this statement works (the whole proc works) after startup..so why not during. Francis
-
In order to run extended stored procedure xp_regenumvalue, SQL Server has to load 'xpstar.dll' first because this xp is one of the module of 'xpstar.dll' but SQL Server launches startup procedure before it starts 'xpstart.dll'.
-
Great Allen, how can I find out at which point which library is loaded?
BTW, thought I'd mention that xp_regenumvalue is undocumented.
FWIW
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
How about building a pause into the stored procedure?
Have it run the proc which then pauses for a minute or so before executing the actual proc code. Not that I know off the top of my head how I'd do that.
Or I suppose I could put the proc into a job and have the sp_procoption stored procedure just schedule a the job for one minute into the future.
Anybody see a problem with this?
The only downside I can think of would be if some bonehead was booting the server multiple time over and over without pause in which case it might reboot before the job executes.
RE: Frank, unless there's a documented way to get the reg values then I say "yeah, yeah, yeah". (If there is, what is it? And why didn't you say so in your last post?
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek) -
You can start a job automatically when SQL Server Agent starts.
-
Ok, that's maybe a valid point! I don't know of an easy way to get reg values with documented procs. Well, there might be a way with the sp_OA* procs, but not even a better one. I only thought I'd drop this in because it is not documented for some reasons and you should carefully think it over before implementing in production code.
Just my $0.02 cents
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Allen: Well that makes life a little simpler doesn't it!
Frank: I'm not sure if you would necessarily call this production code. Yes, it will be on a production server. But, while I would personally like to be able to save my logs forever the most likely scenario would be that the archiving just wouldn't happen. In which case we'd be back to sql server default behavior which is acceptable.
Thanks for the help guys!
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek) -
I have altered the proc as per below.
The results,
The proc does fire. It also uses appr. DLL for xp_cmdshell and xp_regenumvalues , but still fails to create the copied file log file....
Alter PROCEDURE usp_ArchiveSQLLog
AS
print 'usp_ArchiveSQLLog -> start '
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 a temp table to store the SQL Server startup registry parameters
print 'usp_ArchiveSQLLog -> before master..xp_regenumvalues '
CREATE TABLE #Reg ( Value nvarchar( 255 ) , Data nvarchar( 255 ) )
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%'
print 'usp_ArchiveSQLLog -> before master..xp_cmdshell '
-- Create the DOS command to copy the errorlog.1 file to errorlog<datestring>.log
set @Cmd = 'copy "'+ @LogString +'.2" "'+ @LogString + @datestring + '.log"'
exec master.dbo.xp_cmdshell @Cmd
drop table #Reg
print 'usp_ArchiveSQLLog -> finish '
GO
exec sp_procoption N'usp_ArchiveSQLLog', N'startup', N'true'
GO
-
Adding a print @Cmd to above proc and running it as a startup displays
[autoexec] copy ".2" "200403031522.log"
So i guess only the xp_regenumvalues does not work in startup proc. (thats why it is uundocumented ???
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply