Convert SQL 2000 Script to SQL Server 2005

Help me convert this script to SQL Server 2005:
use master
go
exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
go
grant all on dbo.xp_getsequence to public
go
EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
RETURNS INT AS
BEGIN
DECLARE @.newid INT
SELECT @.seqname = db_name() + '..' + @.seqname
EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
RETURN(@.newid)
END
go
grant all on system_function_schema.fn_getsequence to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(50)
IF @.inputvalue is null
SELECT @.rtnvalue='.'
ELSE
BEGIN
IF LTRIM(RTRIM(@.inputvalue))=''
SELECT @.rtnvalue='.'
ELSE
SELECT @.rtnvalue=@.inputvalue
END
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_blank2dot to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(50)
IF LTRIM(RTRIM(@.inputvalue))='.'
SELECT @.rtnvalue=''
ELSE
SELECT @.rtnvalue=@.inputvalue
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_dot2blank to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
VARCHAR(500))
RETURNS VARCHAR(500) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(500)
SELECT @.rtnvalue=@.str1 + @.str2
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_concat2 to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
VARCHAR(500),@.str3 VARCHAR(500))
RETURNS VARCHAR(500) AS
BEGIN
DECLARE @.rtnvalue as VARCHAR(500)
SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
RETURN (@.rtnvalue)
END
go
grant all on system_function_schema.fn_concat3 to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
sql_variant)
RETURNS sql_variant AS
BEGIN
RETURN (isnull(@.inputvalue,@.replacement))
END
go
grant all on system_function_schema.fn_nvl to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_tochar(@.inputvalue sql_variant)
RETURNS VARCHAR(500) AS
BEGIN
RETURN CAST(@.inputvalue AS VARCHAR)
END
go
grant all on system_function_schema.fn_tochar to public
go
USE MASTER
GO
CREATE FUNCTION
system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
VARCHAR(10),@.length INT)
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.newseqid VARCHAR(50)
SELECT @.prefix = coalesce(@.prefix, '')
SELECT @.length = coalesce(@.length, 0)
SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
- len(@.newseqid)) + @.newseqid
RETURN(@.newseqid)
END
go
grant all on system_function_schema.fn_getfullseq to public
go
CREATE PROCEDURE ReloadG
AS
EXEC xp_cmdshell 'START Command ', no_output
go
grant execute on master..ReloadG to public
goM P
> system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> VARCHAR(500))
What if the user provides NULL for one of the parameters?
Visit www.projectdmx.com/tsql/
"M P" <mponteres@.gmail.com> wrote in message
news:1191225325.605094.195820@.o80g2000hse.googlegroups.com...
> Help me convert this script to SQL Server 2005:
> use master
> go
> exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
> go
> grant all on dbo.xp_getsequence to public
> go
> EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
> RECONFIGURE WITH OVERRIDE
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
> RETURNS INT AS
> BEGIN
> DECLARE @.newid INT
> SELECT @.seqname = db_name() + '..' + @.seqname
> EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
> RETURN(@.newid)
> END
> go
> grant all on system_function_schema.fn_getsequence to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(50)
> IF @.inputvalue is null
> SELECT @.rtnvalue='.'
> ELSE
> BEGIN
> IF LTRIM(RTRIM(@.inputvalue))=''
> SELECT @.rtnvalue='.'
> ELSE
> SELECT @.rtnvalue=@.inputvalue
> END
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_blank2dot to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(50)
> IF LTRIM(RTRIM(@.inputvalue))='.'
> SELECT @.rtnvalue=''
> ELSE
> SELECT @.rtnvalue=@.inputvalue
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_dot2blank to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> VARCHAR(500))
> RETURNS VARCHAR(500) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(500)
> SELECT @.rtnvalue=@.str1 + @.str2
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_concat2 to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
> VARCHAR(500),@.str3 VARCHAR(500))
> RETURNS VARCHAR(500) AS
> BEGIN
> DECLARE @.rtnvalue as VARCHAR(500)
> SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
> RETURN (@.rtnvalue)
> END
> go
> grant all on system_function_schema.fn_concat3 to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
> sql_variant)
> RETURNS sql_variant AS
> BEGIN
> RETURN (isnull(@.inputvalue,@.replacement))
> END
> go
> grant all on system_function_schema.fn_nvl to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_tochar(@.inputvalue sql_variant)
> RETURNS VARCHAR(500) AS
> BEGIN
> RETURN CAST(@.inputvalue AS VARCHAR)
> END
> go
> grant all on system_function_schema.fn_tochar to public
> go
> USE MASTER
> GO
> CREATE FUNCTION
> system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
> VARCHAR(10),@.length INT)
> RETURNS VARCHAR(50) AS
> BEGIN
> DECLARE @.newseqid VARCHAR(50)
> SELECT @.prefix = coalesce(@.prefix, '')
> SELECT @.length = coalesce(@.length, 0)
> SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
> SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
> - len(@.newseqid)) + @.newseqid
> RETURN(@.newseqid)
> END
> go
> grant all on system_function_schema.fn_getfullseq to public
> go
> CREATE PROCEDURE ReloadG
> AS
> EXEC xp_cmdshell 'START Command ', no_output
> go
> grant execute on master..ReloadG to public
> go
>|||I am not familiar with the script. I have a task in checking for possibility
of this script to be used on SQL Server 2005. Can you help?
"Uri Dimant" wrote:
> M P
> > system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500))
> What if the user provides NULL for one of the parameters?
> Visit www.projectdmx.com/tsql/
>
> "M P" <mponteres@.gmail.com> wrote in message
> news:1191225325.605094.195820@.o80g2000hse.googlegroups.com...
> > Help me convert this script to SQL Server 2005:
> >
> > use master
> > go
> > exec sp_addextendedproc 'xp_getsequence', 'xp_sqllib.dll'
> > go
> > grant all on dbo.xp_getsequence to public
> > go
> >
> > EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
> > RECONFIGURE WITH OVERRIDE
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_getsequence(@.seqname VARCHAR(50))
> > RETURNS INT AS
> > BEGIN
> > DECLARE @.newid INT
> > SELECT @.seqname = db_name() + '..' + @.seqname
> > EXEC master.dbo.xp_getsequence @.seqname, @.newid OUTPUT
> > RETURN(@.newid)
> > END
> > go
> > grant all on system_function_schema.fn_getsequence to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_blank2dot(@.inputvalue VARCHAR(50))
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(50)
> > IF @.inputvalue is null
> > SELECT @.rtnvalue='.'
> > ELSE
> > BEGIN
> > IF LTRIM(RTRIM(@.inputvalue))=''
> > SELECT @.rtnvalue='.'
> > ELSE
> > SELECT @.rtnvalue=@.inputvalue
> > END
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_blank2dot to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_dot2blank(@.inputvalue VARCHAR(50))
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(50)
> > IF LTRIM(RTRIM(@.inputvalue))='.'
> > SELECT @.rtnvalue=''
> > ELSE
> > SELECT @.rtnvalue=@.inputvalue
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_dot2blank to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_concat2(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500))
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(500)
> > SELECT @.rtnvalue=@.str1 + @.str2
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_concat2 to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_concat3(@.str1 VARCHAR(500),@.str2
> > VARCHAR(500),@.str3 VARCHAR(500))
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > DECLARE @.rtnvalue as VARCHAR(500)
> > SELECT @.rtnvalue=@.str1 + @.str2 + @.str3
> > RETURN (@.rtnvalue)
> > END
> > go
> > grant all on system_function_schema.fn_concat3 to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_nvl(@.inputvalue sql_variant, @.replacement
> > sql_variant)
> > RETURNS sql_variant AS
> > BEGIN
> > RETURN (isnull(@.inputvalue,@.replacement))
> > END
> > go
> > grant all on system_function_schema.fn_nvl to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_tochar(@.inputvalue sql_variant)
> > RETURNS VARCHAR(500) AS
> > BEGIN
> > RETURN CAST(@.inputvalue AS VARCHAR)
> > END
> > go
> > grant all on system_function_schema.fn_tochar to public
> > go
> >
> > USE MASTER
> > GO
> > CREATE FUNCTION
> > system_function_schema.fn_getfullseq(@.seqname VARCHAR(50),@.prefix
> > VARCHAR(10),@.length INT)
> > RETURNS VARCHAR(50) AS
> > BEGIN
> > DECLARE @.newseqid VARCHAR(50)
> > SELECT @.prefix = coalesce(@.prefix, '')
> > SELECT @.length = coalesce(@.length, 0)
> > SELECT @.newseqid = CAST(fn_getsequence(@.seqname) AS VARCHAR)
> > SELECT @.newseqid = @.prefix + replicate('0',@.length - len(@.prefix)
> > - len(@.newseqid)) + @.newseqid
> >
> > RETURN(@.newseqid)
> > END
> > go
> > grant all on system_function_schema.fn_getfullseq to public
> > go
> >
> > CREATE PROCEDURE ReloadG
> > AS
> > EXEC xp_cmdshell 'START Command ', no_output
> > go
> > grant execute on master..ReloadG to public
> > go
> >
>
>