July 18, 2006 at 5:15 am
I want to generate the log file on the different location of the same server other than C DRIVE. On different machine of the same network. On different machine which is in different network.
July 18, 2006 at 6:50 am
in order to do that, the account used to run SQL server would need to have network rights...so instead of the default of running as system, it would need to be changed to say a network administrator, who would have access to the other machines/mapped drives etc.
one the account is changed, simply change your script to use the other location.
pasted below is a contribution someone made for writing to a file, in case you do not have a current solution for that portion.
CREATE PROC usp_CreateSysObjectsReport
AS
/***
* Date: 4/18/2002
* Author: <mikemcw@4segway.biz>
* Project: Just for fun!
* Location: Any database
* Permissions: PUBLIC EXECUTE
*
* Description: Creates an HTML table from SYSOBJECTS
*
* Restrictions: some permissions may need to be set
*
***/
BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON
SELECT '<TABLE>'
UNION ALL
SELECT '<tr><td><CODE>' + name + '</CODE></td></tr>' FROM sysobjects
UNION ALL
SELECT '</TABLE>'
END
GO
GRANT EXECUTE ON usp_CreateSysObjectsReport TO PUBLIC
GO
CREATE PROC usp_writeSysObjectReport(@outfile VARCHAR(255))
AS
/***
* Date: 4/18/2002
* Author: <mikemcw@4segway.biz>
* Project: Just for fun!
* Location: Any database
* Permissions: PUBLIC EXECUTE
*
* Description: Writes the SYSOBJECTS report to specified @outfile
*
* Restrictions: some permissions may need to be set
*
* TODO!!!!! CHANGE MYDATABASE TO YOUR DATABASE NAME!!!
*
***/
BEGIN
DECLARE @strCommand VARCHAR(255)
DECLARE @lret INT
SET @strCommand = 'bcp "EXECUTE MYDATABASE..usp_CreateSysObjectsReport"'
+ ' QUERYOUT "' + @outfile + '" -T -S' + LOWER(@@SERVERNAME) + ' -c'
--BCP the HTML file
PRINT 'EXEC master..xp_cmdshell ''' + @strCommand + ''''
EXEC @lRet = master..xp_cmdshell @strCommand, NO_OUTPUT
IF @lret = 0
PRINT 'File Created'
ELSE
PRINT 'Error: ' + str(@lret)
END
GO
GRANT EXECUTE ON usp_writeSysObjectReport TO PUBLIC
GO
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply