Enter 2001 April/EnterCD4.iso/Update/SQL Server SP3/sql70sp3i.exe/INSTALL/xpstar.sql

Text File | 1999-04-04 | 18.0 KB | 607 lines

/********************************************************************************************/
/* XPSTAR.SQL - Extended Stored Procedures for the SQL Server Enterprise Components         */
/********************************************************************************************/
use master
go

/********************************************************************************************/
/* DROP EXISTING XP's first                                                                 */
/********************************************************************************************/
if exists (select * from master.dbo.sysobjects where name = 'xp_regread')
  EXEC sp_dropextendedproc 'xp_regread'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_regwrite')
  EXEC sp_dropextendedproc 'xp_regwrite'
go
if exists (select * from master.dbo.sysobjects where name = 'xp_regdeletevalue')
  EXEC sp_dropextendedproc 'xp_regdeletevalue'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_regdeletekey')
  EXEC sp_dropextendedproc 'xp_regdeletekey'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_regaddmultistring')
  EXEC sp_dropextendedproc 'xp_regaddmultistring'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_regremovemultistring')
  EXEC sp_dropextendedproc 'xp_regremovemultistring'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_regenumvalues')
  EXEC sp_dropextendedproc 'xp_regenumvalues'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_readerrorlog')
  EXEC sp_dropextendedproc 'xp_readerrorlog'
go

if exists (select * from master.dbo.sysobjects where name = 'sp_readerrorlog')
  drop procedure sp_readerrorlog
go

if exists (select * from master.dbo.sysobjects where name = 'xp_enumerrorlogs')
  EXEC sp_dropextendedproc 'xp_enumerrorlogs'
go

if exists (select * from master.dbo.sysobjects where name = 'sp_enumerrorlogs')
  drop procedure sp_enumerrorlogs
go

if exists (select * from master.dbo.sysobjects where name = 'xp_getfiledetails')
  EXEC sp_dropextendedproc 'xp_getfiledetails'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_perfsample')
  EXEC sp_dropextendedproc 'xp_perfsample'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_perfstart')
  EXEC sp_dropextendedproc 'xp_perfstart'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_perfend')
  EXEC sp_dropextendedproc 'xp_perfend'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_perfmonitor')
  EXEC sp_dropextendedproc 'xp_perfmonitor'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_servicecontrol')
  EXEC sp_dropextendedproc 'xp_servicecontrol'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_availablemedia')
  EXEC sp_dropextendedproc 'xp_availablemedia'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_dirtree')
  EXEC sp_dropextendedproc 'xp_dirtree'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_eventlog')
  EXEC sp_dropextendedproc 'xp_eventlog'
go

if exists (select * from master.dbo.sysobjects where name = 'sp_eventlog')
  drop procedure sp_eventlog
go


if exists (select * from master.dbo.sysobjects where name = 'xp_fixeddrives')
  EXEC sp_dropextendedproc 'xp_fixeddrives'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_subdirs')
  EXEC sp_dropextendedproc 'xp_subdirs'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_getnetname')
  EXEC sp_dropextendedproc 'xp_getnetname'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_snmp_getstate')
  EXEC sp_dropextendedproc 'xp_snmp_getstate'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_snmp_raisetrap')
  EXEC sp_dropextendedproc 'xp_snmp_raisetrap'
go

if exists(select * from master.dbo.sysobjects where type = 'X' and name = 'xp_sqltrace')
  EXEC sp_dropextendedproc 'xp_sqltrace'
go

if exists(select * from master.dbo.sysobjects where type = 'X' and name = 'sp_IsMBCSLeadByte')
  EXEC sp_dropextendedproc 'sp_IsMBCSLeadByte'
go

if exists(select * from master.dbo.sysobjects where type = 'X' and name = 'sp_GetMBCSCharLen')
  EXEC sp_dropextendedproc 'sp_GetMBCSCharLen'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_check_query_results')
  EXEC sp_dropextendedproc 'xp_check_query_results'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_enum_activescriptengines')
  EXEC sp_dropextendedproc 'xp_enum_activescriptengines'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_sqlagent_monitor')
  EXEC sp_dropextendedproc 'xp_sqlagent_monitor'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_sqlagent_notify')
  EXEC sp_dropextendedproc 'xp_sqlagent_notify'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_sqlagent_enum_jobs')
  EXEC sp_dropextendedproc 'xp_sqlagent_enum_jobs'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_sqlagent_is_starting')
  EXEC sp_dropextendedproc 'xp_sqlagent_is_starting'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_fileexist')
  EXEC sp_dropextendedproc 'xp_fileexist'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_ntsec_enumdomains')
  EXEC sp_dropextendedproc 'xp_ntsec_enumdomains'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_ntsec_enumusers')
  EXEC sp_dropextendedproc 'xp_ntsec_enumusers'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_ntsec_enumgroups')
  EXEC sp_dropextendedproc 'xp_ntsec_enumgroups'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_terminate_process')
  EXEC sp_dropextendedproc 'xp_terminate_process'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_msx_enlist')
  EXEC sp_dropextendedproc 'xp_msx_enlist'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_sqlmaint')
  EXEC sp_dropextendedproc 'xp_sqlmaint'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_get_mapi_profiles')
  EXEC sp_dropextendedproc 'xp_get_mapi_profiles'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_test_mapi_profile')
  EXEC sp_dropextendedproc 'xp_test_mapi_profile'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_get_mapi_default_profile')
  EXEC sp_dropextendedproc 'xp_get_mapi_default_profile'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_get_tape_devices')
  EXEC sp_dropextendedproc 'xp_get_tape_devices'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_enum_oledb_providers')
  EXEC sp_dropextendedproc 'xp_enum_oledb_providers'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_prop_oledb_provider')
  EXEC sp_dropextendedproc 'xp_prop_oledb_provider'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_updateFTSSQLAccount')
  EXEC sp_dropextendedproc 'xp_updateFTSSQLAccount'
go

if exists(select * from sysobjects where type = 'X' and name = 'xp_sqlregister')
  EXEC sp_dropextendedproc 'xp_sqlregister'
go

if exists (select * from sysobjects where type = 'P' and name = 'sp_sqlregister')
  drop procedure sp_sqlregister
go

if exists(select * from sysobjects where type = 'X' and name = 'xp_sqlinventory')
  EXEC sp_dropextendedproc 'xp_sqlinventory'
go

if exists (select * from master.dbo.sysobjects where name = 'xp_writesqlinfo')
  EXEC sp_dropextendedproc 'xp_writesqlinfo'
go

/********************************************************************************************/
/* If sp_MSgetversion exists as SP drop it otherwise drop the extended proc                 */
/********************************************************************************************/
if exists (select * from master.dbo.sysobjects where type = 'P' and name = 'sp_MSgetversion')
        drop procedure sp_MSgetversion
else if exists (select * from master.dbo.sysobjects where type = 'X' and name = 'sp_MSgetversion')
  EXEC sp_dropextendedproc 'sp_MSgetversion'
go

/********************************************************************************************/
exec sp_MS_upd_sysobj_category 1
go
/********************************************************************************************/

/********************************************************************************************/
/* Create XP's                                                                              */
/********************************************************************************************/
print 'Creating extended stored procedure xp_regread'
go
sp_addextendedproc 'xp_regread','xpstar.dll'
go
grant execute on xp_regread to public
go

print 'Creating extended stored procedure xp_regwrite'
go
sp_addextendedproc 'xp_regwrite','xpstar.dll'
go

print 'Creating extended stored procedure xp_regdeletevalue'
go
sp_addextendedproc 'xp_regdeletevalue','xpstar.dll'
go

print 'Creating extended stored procedure xp_regaddmultistring'
go
sp_addextendedproc 'xp_regaddmultistring','xpstar.dll'
go

print 'Creating extended stored procedure xp_regremovemultistring'
go
sp_addextendedproc 'xp_regremovemultistring','xpstar.dll'
go

print 'Creating extended stored procedure xp_regenumvalues'
go
sp_addextendedproc 'xp_regenumvalues','xpstar.dll'
go

print 'Creating extended stored procedure xp_readerrorlog'
go
sp_addextendedproc 'xp_readerrorlog','xpstar.dll'
go

print 'Creating stored procedure sp_readerrorlog'
go
create proc sp_readerrorlog(
    @p1        int = 0,
    @p2        varchar(255) = NULL,
    @p3        varchar(255) = NULL,
    @p4        varchar(255) = NULL)
as
begin

    IF (not is_srvrolemember('securityadmin') = 1)
    begin
       raiserror(15003,-1,-1, 'securityadmin')
       return (1)
    end
    if (@p1 = 0)
        exec xp_readerrorlog
    else if (@p2 is NULL)
        exec xp_readerrorlog @p1
    else
        exec xp_readerrorlog @p1,@p2,@p3,@p4
end
go
grant execute on sp_readerrorlog to public
go

print 'Creating extended stored procedure xp_enumerrorlogs'
go
sp_addextendedproc 'xp_enumerrorlogs','xpstar.dll'
go

print 'Creating stored procedure sp_enumerrorlogs'
go
create proc sp_enumerrorlogs
as
begin

    IF (not is_srvrolemember('securityadmin') = 1)
    begin
       raiserror(15003,-1,-1, 'securityadmin')
       return (1)
    end
    exec xp_enumerrorlogs
end
go
grant execute on sp_enumerrorlogs to public
go

print 'Creating extended stored procedure xp_getfiledetails'
go
sp_addextendedproc 'xp_getfiledetails','xpstar.dll'
go
grant execute on xp_getfiledetails to public
go

print 'Creating extended stored procedure xp_perfsample'
go
sp_addextendedproc 'xp_perfsample','xpstar.dll'
go

print 'Creating extended stored procedure xp_perfstart'
go
sp_addextendedproc 'xp_perfstart','xpstar.dll'
go

print 'Creating extended stored procedure xp_perfend'
go
sp_addextendedproc 'xp_perfend','xpstar.dll'
go

print 'Creating extended stored procedure xp_perfmonitor'
go
sp_addextendedproc 'xp_perfmonitor','xpstar.dll'
go

print 'Creating extended stored procedure xp_servicecontrol'
go
sp_addextendedproc 'xp_servicecontrol','xpstar.dll'
go

print 'Creating extended stored procedure xp_availablemedia'
go
sp_addextendedproc 'xp_availablemedia','xpstar.dll'
go

print 'Creating extended stored procedure xp_dirtree'
go
sp_addextendedproc 'xp_dirtree','xpstar.dll'
go
grant exec on xp_dirtree to public
go

print 'Creating extended stored procedure xp_eventlog'
go
sp_addextendedproc 'xp_eventlog','xpstar.dll'
go

print 'Creating stored procedure sp_eventlog'
go
create proc sp_eventlog(
    @p1        varchar(255) = NULL,
    @p2        int = NULL,
    @p3        int = NULL,
    @p4        int = NULL )
as
begin

    if (not is_srvrolemember('securityadmin') = 1)
    begin
       raiserror(15003,-1,-1, 'securityadmin')
       return (1)
    end
    exec xp_eventlog @p1,@p2,@p3,@p3
end
go
grant execute on sp_eventlog to public
go


print 'Creating extended stored procedure xp_fixeddrives'
go
sp_addextendedproc 'xp_fixeddrives','xpstar.dll'
go
grant exec on xp_fixeddrives to public
go

print 'Creating extended stored procedure xp_subdirs'
go
sp_addextendedproc 'xp_subdirs','xpstar.dll'
go

print 'Creating extended stored procedure xp_sqltrace'
go
exec sp_addextendedproc 'xp_sqltrace', 'sqltrace.dll'
go

print 'Creating extended stored procedure xp_getnetname'
go
sp_addextendedproc 'xp_getnetname','xpstar.dll'
go
grant execute on xp_getnetname to public
go

print 'Creating extended stored procedure sp_IsMBCSLeadByte'
go
sp_addextendedproc 'sp_IsMBCSLeadByte','xpstar.dll'
go
grant execute on sp_IsMBCSLeadByte to public
go

print 'Creating extended stored procedure sp_GetMBCSCharLen'
go
sp_addextendedproc 'sp_GetMBCSCharLen','xpstar.dll'
go
grant execute on sp_GetMBCSCharLen to public
go

print 'Creating extended stored procedure xp_regdeletekey'
go
sp_addextendedproc 'xp_regdeletekey','xpstar.dll'
go

print 'Creating extended stored procedure xp_check_query_results'
go
sp_addextendedproc 'xp_check_query_results','xpstar.dll'
go

print 'Creating extended stored procedure xp_sqlagent_monitor'
go
sp_addextendedproc 'xp_sqlagent_monitor','xpstar.dll'
go

print 'Creating extended stored procedure xp_sqlagent_notify'
go
sp_addextendedproc 'xp_sqlagent_notify','xpstar.dll'
go

print 'Creating extended stored procedure xp_sqlagent_enum_jobs'
go
sp_addextendedproc 'xp_sqlagent_enum_jobs','xpstar.dll'
go

print 'Creating extended stored procedure xp_sqlagent_is_starting'
go
sp_addextendedproc 'xp_sqlagent_is_starting','xpstar.dll'
go

print 'Creating extended stored procedure xp_enum_activescriptengines'
go
sp_addextendedproc 'xp_enum_activescriptengines','xpstar.dll'
go
grant execute on xp_enum_activescriptengines to public
go

print 'Creating extended stored procedure xp_fileexist'
go
sp_addextendedproc 'xp_fileexist','xpstar.dll'
go
grant execute on xp_fileexist to public
go

print 'Creating extended stored procedure xp_ntsec_enumdomains'
go
sp_addextendedproc 'xp_ntsec_enumdomains','xpstar.dll'
go
grant execute on xp_ntsec_enumdomains to public
go

print 'Creating extended stored procedure xp_ntsec_enumusers'
go
sp_addextendedproc 'xp_ntsec_enumusers','xpstar.dll'
go
grant execute on xp_ntsec_enumusers to public
go

print 'Creating extended stored procedure xp_ntsec_enumgroups'
go
sp_addextendedproc 'xp_ntsec_enumgroups','xpstar.dll'
go
grant execute on xp_ntsec_enumgroups to public
go

print 'Creating extended stored procedure xp_terminate_process'
go
sp_addextendedproc 'xp_terminate_process','xpstar.dll'
go

print 'Creating extended stored procedure xp_msx_enlist'
go
sp_addextendedproc 'xp_msx_enlist','xpstar.dll'
go

print 'Creating extended stored procedure xp_sqlmaint'
go
sp_addextendedproc 'xp_sqlmaint','xpstar.dll'
go

print 'Creating extended stored procedure xp_get_mapi_profiles'
go
sp_addextendedproc 'xp_get_mapi_profiles','xpstar.dll'
go

print 'Creating extended stored procedure xp_get_mapi_default_profile'
go
sp_addextendedproc 'xp_get_mapi_default_profile','xpstar.dll'
go

print 'Creating extended stored procedure xp_test_mapi_profile'
go
sp_addextendedproc 'xp_test_mapi_profile','xpstar.dll'
go

print 'Creating extended stored procedure xp_get_tape_devices'
go
sp_addextendedproc 'xp_get_tape_devices','xpstar.dll'
go

print 'Creating extended stored procedure xp_enum_oledb_providers'
go
sp_addextendedproc 'xp_enum_oledb_providers','xpstar.dll'
go

print 'Creating extended stored procedure xp_prop_oledb_provider'
go
sp_addextendedproc 'xp_prop_oledb_provider','xpstar.dll'
go

print 'Creating extended stored procedure xp_sqlregister'
go
exec sp_addextendedproc 'xp_sqlregister', 'xpstar.dll'
go
grant exec on xp_sqlregister to public
go

print 'Creating extended stored procedure xp_sqlinventory'
go
exec sp_addextendedproc 'xp_sqlinventory', 'xpstar.dll'
go

print 'Creating stored procedure sp_sqlregister'
go
-- We create this thin wrapper proc so that we can make xp_sqlregister run at startup
create proc sp_sqlregister as
begin
  exec master.dbo.xp_sqlregister
end
go

grant exec on sp_sqlregister to public
go

print 'Creating extended stored procedure xp_writesqlinfo'
go
sp_addextendedproc 'xp_writesqlinfo','xpstar.dll'
go

print 'Creating extended stored procedure xp_updateFTSSQLAccount'
go
sp_addextendedproc 'xp_updateFTSSQLAccount','xpstar.dll'
go

print 'Creating extended stored procedure sp_MSgetversion'
go
sp_addextendedproc 'sp_MSgetversion','xpstar.dll'
go
grant execute on sp_MSgetversion to public
go

/********************************************************************************************/
/* enable sp_sqlregister for startup on SQL Server Standard, Enterprise and Desktop         */
/********************************************************************************************/
-- create table #t1(c1 varchar(20) not null, c2 int not null, c3 int not null)
-- insert into #t1 exec master.dbo.sp_MSgetversion
-- if exists (select * from #t1 where c3 = 2 or c3 = 3) exec sp_procoption 'sp_sqlregister', 'startup', 'true'
-- go
/********************************************************************************************/

/********************************************************************************************/
exec sp_MS_upd_sysobj_category 2
go
/********************************************************************************************/

/********************************************************************************************/
/* Save SQL Server config info to the registry */
-- exec xp_writesqlinfo
go
/********************************************************************************************/

/********************************************************************************************/
/* EOF XPSTAR.SQL                                                                           */
/********************************************************************************************/