When I need to have more info about a process, I run this kind of query:
SELECT PROGRAM_NAME FROM master..sysprocesses WHERE program_name LIKE
'SQLAgent - TSQL JobStep (Job %'
The result might be something like this:
SQLAgent - TSQL JobStep (Job 0x9BD0927CE9086241B582AAAAD7D3B86D : Step
1)
When I want to have more info about that particular job, I run this
query:
SELECT * FROM msdb..sysjobs WHERE job_id =
0x9BD0927CE9086241B582AAAAD7D3B86D
Now how do I combine these two queries? The first version you might
suggest is:
SELECT *
FROM msdb..sysjobs
WHERE job_id IN
(SELECT SUBSTRING(program_name,30,34)
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
This gives the result
Syntax error converting from a character string to uniqueidentifier.
Can you help me with this? How to convert the result of the subquery to
uniqueidentifier? I didn't succeed to use the usual convert function
CONVERT(UNIQUEIDENTIFIER,SUBSTRING(progr
am_name,30,34)).
MarkTry this (untested):
SELECT *
FROM msdb..sysjobs
WHERE cast(job_id as nvarchar(128)) IN
(SELECT cast(SUBSTRING(program_name,30,34) as nvarchar(128))
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:9FB96207-78A4-4566-9AEC-8294830AABAB@.microsoft.com...
> Try this (untested):
> SELECT *
> FROM msdb..sysjobs
> WHERE cast(job_id as nvarchar(128)) IN
> (SELECT cast(SUBSTRING(program_name,30,34) as nvarchar(128))
> FROM master..sysprocesses
> WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
>
> ML
Thanks, but that doesn't work either - I tested. But at least that doesn't
give any errors, just an empty result.
However, I found out, that this works. Quite ugly one, but it works.
SELECT *
FROM msdb..sysjobs
WHERE job_id IN
(SELECT SUBSTRING(program_name,38,2) +
SUBSTRING(program_name,36,2) +
SUBSTRING(program_name,34,2) +
SUBSTRING(program_name,32,2) + '-' +
SUBSTRING(program_name,42,2) +
SUBSTRING(program_name,40,2) + '-' +
SUBSTRING(program_name,46,2) +
SUBSTRING(program_name,44,2) + '-' +
SUBSTRING(program_name,48,4) + '-' +
SUBSTRING(program_name,52,12)
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
Mark
Showing posts with label master. Show all posts
Showing posts with label master. Show all posts
Tuesday, March 20, 2012
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
> >
>
>
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
> >
>
>
Subscribe to:
Posts (Atom)