Showing posts with label dll. Show all posts
Showing posts with label dll. Show all posts

Wednesday, March 7, 2012

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
> >
>
>