/********************************************************************************************/
/* Create SP's */
/********************************************************************************************/
--====================================================================================
-- Add extended stored procedures for Web Server support.
--
-- sp_makewebtask: Creates and defines the Web Page Task
CREATE PROCEDURE sys.sp_makewebtask
@outputfile nvarchar(255),
@query ntext,
@fixedfont tinyint = 1, -- 0/1
@bold tinyint = 0, -- 0/1
@italic tinyint = 0, -- 0/1
@colheaders tinyint = 1, -- 0/1
@lastupdated tinyint = 1, -- 0/1
@HTMLheader tinyint = 1, -- 1-6
@username nvarchar(128) = NULL,
@dbname nvarchar(128) = NULL,
@templatefile nvarchar(255) = NULL,
@webpagetitle nvarchar(255) = NULL,
@resultstitle nvarchar(255) = NULL,
@URL nvarchar(255) = NULL,
@reftext nvarchar(255) = NULL,
@table_urls tinyint = 0, -- 0/1; 1=use table of URLs
@url_query nvarchar(255) = NULL,
@whentype tinyint = 1, -- 1=now, 2=later, 3=every xday
-- 4=every n units of time
@targetdate int = 0, -- yyyymmdd as int
@targettime int = 0, -- hhnnss as int
@dayflags tinyint = 1, -- powers of 2 for days of week
@numunits tinyint = 1,
@unittype tinyint = 1, -- 1=hours, 2=days, 3=weeks, 4=minutes
@procname nvarchar(128) = NULL, -- name to use when making the
-- task and the wrapper/condenser
-- stored procs
@maketask int = 2, -- 0=create unencrypted sproc, no task
-- 1=encrypted sproc and task
-- 2=unencrypted sproc and task
@rowcnt int = 0, -- max no of rows to display
@tabborder tinyint = 1, -- borders around the results table
@singlerow tinyint = 0, -- Single row per page
@blobfmt ntext = NULL, -- Formatting for text and image fields
@nrowsperpage int = 0, -- Results displayed in multiple pages of n rows per page
@datachg ntext = NULL, -- Table and column names for a trigger
@charset nvarchar(25) = N'utf-8', -- Universal character set is the default
@codepage int = 65001 -- utf-8 (universal) code page is the default
AS
BEGIN
DECLARE @suid smallint
DECLARE @yearchar nvarchar(4)
DECLARE @monthchar nvarchar(2)
DECLARE @daychar nvarchar(2)
DECLARE @hourchar nvarchar(2)
DECLARE @minchar nvarchar(2)
DECLARE @secchar nvarchar(2)
DECLARE @currdate datetime
DECLARE @retval int
-- Check for valid @dbname if supplied
IF (@dbname is NOT NULL)
IF (NOT(exists(SELECT * FROM master..sysdatabases WHERE name = @dbname)))
BEGIN
RAISERROR(16854,11,1)
RETURN (9)
END
-- Make sure that it's the SA executing this.
IF ( NOT ( is_srvrolemember('sysadmin') = 1 ) )
BEGIN
RAISERROR( 15003, -1, -1, 'sysadmin' )
RETURN(1)
END
-- IF not supplied, determine the user executing this procedure
IF (@username is NULL)
BEGIN
SET @username = suser_sname()
IF ( (charindex ('\',@username) > 0) OR (@username is NULL) OR (@username = 'sa') )
BEGIN
SELECT @username = N'dbo'
END
END
-- If not supplied, determine the database currently active
IF (@dbname is NULL)
BEGIN
SELECT @dbname = d.name FROM
master..sysdatabases d, master..sysprocesses p
WHERE d.dbid = p.dbid AND spid = @@spid
END
-- Generate @procname if not supplied
IF (@procname is NULL)
BEGIN
SET @currdate = getdate()
SET @yearchar = convert(nvarchar(4),year(@currdate))
SET @monthchar = right('0'+ rtrim(convert(nvarchar(2),month(@currdate))),2)
SET @daychar = right('0'+rtrim(convert(nvarchar(2),day(@currdate))),2)
SET @hourchar = right('0'+rtrim(convert(nvarchar(2),datepart(hh,@currdate))),2)
SET @minchar = right('0'+rtrim(convert(nvarchar(2),datepart(mi,@currdate))),2)
SET @secchar = right('0'+rtrim(convert(nvarchar(2),datepart(ss,@currdate))),2)
-- Get default procname if not supplied
SET @procname = N'web_'+convert(nchar(14),@yearchar+@monthchar+@daychar+@hourchar+@minchar+@secchar)+convert(nvarchar(20),@@spid)+right(rtrim(convert( VARCHAR(25),RAND() )),4)
END
SET @retval = 0
-- Create the Web task
EXECUTE @retval = sys.xp_makewebtask @outputfile, @query, @username, @procname, @dbname,
@fixedfont, @bold, @italic, @colheaders, @lastupdated, @HTMLheader,
@templatefile, @webpagetitle, @resultstitle, @URL, @reftext,
@table_urls, @url_query, @whentype, @targetdate, @targettime,
@dayflags, @numunits, @unittype, @rowcnt, @maketask, @tabborder,
@singlerow, @blobfmt, @nrowsperpage, @datachg, @charset, @codepage
IF (@retval <> 0)
BEGIN
SET @procname = 'xp_makewebtask'
RAISERROR(@retval, 11, 1, @procname)
END
RETURN @retval
END
/* Create SP's */
/********************************************************************************************/
--====================================================================================
-- Add extended stored procedures for Web Server support.
--
-- sp_makewebtask: Creates and defines the Web Page Task
CREATE PROCEDURE sys.sp_makewebtask
@outputfile nvarchar(255),
@query ntext,
@fixedfont tinyint = 1, -- 0/1
@bold tinyint = 0, -- 0/1
@italic tinyint = 0, -- 0/1
@colheaders tinyint = 1, -- 0/1
@lastupdated tinyint = 1, -- 0/1
@HTMLheader tinyint = 1, -- 1-6
@username nvarchar(128) = NULL,
@dbname nvarchar(128) = NULL,
@templatefile nvarchar(255) = NULL,
@webpagetitle nvarchar(255) = NULL,
@resultstitle nvarchar(255) = NULL,
@URL nvarchar(255) = NULL,
@reftext nvarchar(255) = NULL,
@table_urls tinyint = 0, -- 0/1; 1=use table of URLs
@url_query nvarchar(255) = NULL,
@whentype tinyint = 1, -- 1=now, 2=later, 3=every xday
-- 4=every n units of time
@targetdate int = 0, -- yyyymmdd as int
@targettime int = 0, -- hhnnss as int
@dayflags tinyint = 1, -- powers of 2 for days of week
@numunits tinyint = 1,
@unittype tinyint = 1, -- 1=hours, 2=days, 3=weeks, 4=minutes
@procname nvarchar(128) = NULL, -- name to use when making the
-- task and the wrapper/condenser
-- stored procs
@maketask int = 2, -- 0=create unencrypted sproc, no task
-- 1=encrypted sproc and task
-- 2=unencrypted sproc and task
@rowcnt int = 0, -- max no of rows to display
@tabborder tinyint = 1, -- borders around the results table
@singlerow tinyint = 0, -- Single row per page
@blobfmt ntext = NULL, -- Formatting for text and image fields
@nrowsperpage int = 0, -- Results displayed in multiple pages of n rows per page
@datachg ntext = NULL, -- Table and column names for a trigger
@charset nvarchar(25) = N'utf-8', -- Universal character set is the default
@codepage int = 65001 -- utf-8 (universal) code page is the default
AS
BEGIN
DECLARE @suid smallint
DECLARE @yearchar nvarchar(4)
DECLARE @monthchar nvarchar(2)
DECLARE @daychar nvarchar(2)
DECLARE @hourchar nvarchar(2)
DECLARE @minchar nvarchar(2)
DECLARE @secchar nvarchar(2)
DECLARE @currdate datetime
DECLARE @retval int
-- Check for valid @dbname if supplied
IF (@dbname is NOT NULL)
IF (NOT(exists(SELECT * FROM master..sysdatabases WHERE name = @dbname)))
BEGIN
RAISERROR(16854,11,1)
RETURN (9)
END
-- Make sure that it's the SA executing this.
IF ( NOT ( is_srvrolemember('sysadmin') = 1 ) )
BEGIN
RAISERROR( 15003, -1, -1, 'sysadmin' )
RETURN(1)
END
-- IF not supplied, determine the user executing this procedure
IF (@username is NULL)
BEGIN
SET @username = suser_sname()
IF ( (charindex ('\',@username) > 0) OR (@username is NULL) OR (@username = 'sa') )
BEGIN
SELECT @username = N'dbo'
END
END
-- If not supplied, determine the database currently active
IF (@dbname is NULL)
BEGIN
SELECT @dbname = d.name FROM
master..sysdatabases d, master..sysprocesses p
WHERE d.dbid = p.dbid AND spid = @@spid
END
-- Generate @procname if not supplied
IF (@procname is NULL)
BEGIN
SET @currdate = getdate()
SET @yearchar = convert(nvarchar(4),year(@currdate))
SET @monthchar = right('0'+ rtrim(convert(nvarchar(2),month(@currdate))),2)
SET @daychar = right('0'+rtrim(convert(nvarchar(2),day(@currdate))),2)
SET @hourchar = right('0'+rtrim(convert(nvarchar(2),datepart(hh,@currdate))),2)
SET @minchar = right('0'+rtrim(convert(nvarchar(2),datepart(mi,@currdate))),2)
SET @secchar = right('0'+rtrim(convert(nvarchar(2),datepart(ss,@currdate))),2)
-- Get default procname if not supplied
SET @procname = N'web_'+convert(nchar(14),@yearchar+@monthchar+@daychar+@hourchar+@minchar+@secchar)+convert(nvarchar(20),@@spid)+right(rtrim(convert( VARCHAR(25),RAND() )),4)
END
SET @retval = 0
-- Create the Web task
EXECUTE @retval = sys.xp_makewebtask @outputfile, @query, @username, @procname, @dbname,
@fixedfont, @bold, @italic, @colheaders, @lastupdated, @HTMLheader,
@templatefile, @webpagetitle, @resultstitle, @URL, @reftext,
@table_urls, @url_query, @whentype, @targetdate, @targettime,
@dayflags, @numunits, @unittype, @rowcnt, @maketask, @tabborder,
@singlerow, @blobfmt, @nrowsperpage, @datachg, @charset, @codepage
IF (@retval <> 0)
BEGIN
SET @procname = 'xp_makewebtask'
RAISERROR(@retval, 11, 1, @procname)
END
RETURN @retval
END
CREATE PROCEDURE sys.sp_makewebtask 去掉try
mssql:安全级别
数据库.dbo.表
schema_name是已经存在的架构。楼主数据库里有sys这个架构吗?没有的话可以使用dbo
sys是SQLSERVER系统架构,应该是允许使用的。
修改一下:
sys是SQLSERVER系统架构,应该是不允许使用的。
把 sys.变成 dbo.就行了因为 sys.用户组是Oracle的, dbo.是MSSQL的
从LZ的SQL语法来看应该是MSSQL的如果想把这个存储过程的权限仅限于一个用户或用户组, 可以先定义一个用户(如 LZ)
再用这个用户登录后创建存储过程 LZ.sp_makewebtask
sys是SQLSERVER系统架构,是不允许使用的。
试了一下想获取sys架构修改权限但是没有做到
可以把sys变为dbo
无法在 sysdepends 中添加当前存储过程所对应的行,因为缺少该存储过程所依赖的对象 'dbo.sp_makewebtask'。仍将创建该存储过程。
我的问题已解决。希望能给大家一点帮助。