Sql Case sensitivity: job

Showing posts with label job. Show all posts

Tuesday, March 27, 2012

Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>>I just converted a SQL Server 2000 database to 2005. I have a job whose
>>only step does the following:
>> Declare @.Command char(240)
>> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
>> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
>> Replace([name],' ','~')
>> From msdb..sysjobs where job_id=[JOBID])
>> exec master..xp_cmdshell @.Command, NO_OUTPUT
>> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
>> was passed to the job step. Under 2005, I get an error when I run the job
>> saying 'JOBID' is undefined. Does anyone know what change I need to make?
>> Amos.
>sql

Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.
Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>
|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>

Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifie
r,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifie
r,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the j
ob
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>

Friday, February 24, 2012

CELCO - SQL For Smarties 2005 Ed.

Joe,
Kudos to you on a job well done. Will be adding your 2005 edition to my
personal SQL book library as well as a recommended SQL book for others.
JerryHurricane Celko...
I just purchased the "Joe Celko's SQL for Smarties: Advanced SQL
Programming."
Yosh
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23EZc5v5zFHA.2912@.TK2MSFTNGP10.phx.gbl...
> Joe,
> Kudos to you on a job well done. Will be adding your 2005 edition to my
> personal SQL book library as well as a recommended SQL book for others.
> Jerry
>|||I notice its still not been updated to the current ANSI 99 standard and is
16 years old now (ANSI 89).
Whilst it contains some good fundementals there are better books that taylor
to Microsoft SQL Server rather than a standard that isn't commonly used in
the wider community on a day to day basis.
Ken Henderson and Itzik Ben Gan have both done some really excellant
advanced t-SQL books that have immediate use to you as a SQL Server
developer/dba.
My 2 cents.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23EZc5v5zFHA.2912@.TK2MSFTNGP10.phx.gbl...
> Joe,
> Kudos to you on a job well done. Will be adding your 2005 edition to my
> personal SQL book library as well as a recommended SQL book for others.
> Jerry
>|||On Wed, 12 Oct 2005 19:15:24 -0700, "Jerry Spivey"
<jspivey@.vestas-awt.com> wrote:

>Joe,
>Kudos to you on a job well done. Will be adding your 2005 edition to my
>personal SQL book library as well as a recommended SQL book for others.
>Jerry
Jerry,
A basic question or two.
If you don't have the book already in your library how can you be sure
it's a job well done?
What do you think its strengths are? How relevant, for example, is
(what seems to be) a generic SQL book to a T-SQL context?
I am not sniping simply asking for clarification to better understand
the basis for your assessment.
Thanks.
Andrew Watt
MVP - InfoPath|||howd you get SQL into a packet of smarties anyway...?
"Jerry Spivey" wrote:

> Joe,
> Kudos to you on a job well done. Will be adding your 2005 edition to my
> personal SQL book library as well as a recommended SQL book for others.
> Jerry
>
>|||Tony,
Yes...Ken's book is one of the best and I like Itzik's as well. Would put
Ken and Kalen's as two of the top SQL Server books available.
Jerry
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:ubORBL9zFHA.2076@.TK2MSFTNGP14.phx.gbl...
>I notice its still not been updated to the current ANSI 99 standard and is
>16 years old now (ANSI 89).
> Whilst it contains some good fundementals there are better books that
> taylor to Microsoft SQL Server rather than a standard that isn't commonly
> used in the wider community on a day to day basis.
> Ken Henderson and Itzik Ben Gan have both done some really excellant
> advanced t-SQL books that have immediate use to you as a SQL Server
> developer/dba.
> My 2 cents.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23EZc5v5zFHA.2912@.TK2MSFTNGP10.phx.gbl...
>|||Hi Andrew,
I own about 40+ SQL/SQL Server books and I've taught SQL Server for 7 years.
I base my book recommendations on content, ease of readability and
relevancy. I do own Joe's 1999 edition. In fact I was just going through
it when I stopped by the book store and happend to notice his 2005 edition.
I was impressed with the additional content (coverage and examples). In
fact the book has increased in size by over 250 pages. While personally I
believe Ken and Kalen's books are the two best SQL Server books on the
market I feel Joe's book is one of the top SQL books on the market.
HTH
Jerry
"Andrew Watt [MVP - InfoPath]" <SVGDeveloper@.aol.com> wrote in message
news:7mask1toptlh9gsqqmbqr5vrg9vmpmaun0@.
4ax.com...
> On Wed, 12 Oct 2005 19:15:24 -0700, "Jerry Spivey"
> <jspivey@.vestas-awt.com> wrote:
>
> Jerry,
> A basic question or two.
> If you don't have the book already in your library how can you be sure
> it's a job well done?
> What do you think its strengths are? How relevant, for example, is
> (what seems to be) a generic SQL book to a T-SQL context?
> I am not sniping simply asking for clarification to better understand
> the basis for your assessment.
> Thanks.
> Andrew Watt
> MVP - InfoPath

Sunday, February 19, 2012

Cdosys

I am trying to send job status information using smtp via cdosys. I have it emailing but I want to pass the servername and jobname in the email. Can anyone assist?

***oops, here is the SP and the command ran from the job steps;;;

************************************************** *****

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@.From varchar(100) ,
@.To varchar(100) ,
@.Subject varchar(100)=" ",
@.Body varchar(4000) =" "
/************************************************** *******************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

************************************************** *********************/
AS
Declare @.iMsg int
Declare @.hr int
Declare @.source varchar(255)
Declare @.description varchar(500)
Declare @.output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @.hr = sp_OACreate 'CDO.Message', @.iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @.hr = sp_OASetProperty @.iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtpserver'

-- Save the configurations to the message object.
EXEC @.hr = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
EXEC @.hr = sp_OASetProperty @.iMsg, 'To', @.To
EXEC @.hr = sp_OASetProperty @.iMsg, 'From', @.From
EXEC @.hr = sp_OASetProperty @.iMsg, 'Subject', @.Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @.hr = sp_OASetProperty @.iMsg, 'TextBody', @.Body
EXEC @.hr = sp_OAMethod @.iMsg, 'Send', NULL

-- Sample error handling.
IF @.hr <>0
select @.hr
BEGIN
EXEC @.hr = sp_OAGetErrorInfo NULL, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @.hr = sp_OADestroy @.iMsg
GO
************************************************** ****

declare @.Body varchar(4000)
select @.Body = 'Job Succeeded'
exec sp_send_cdosysmail 'me@.you.com','you@.me.com','servername Jobname',@.Body

ThanksAnyone? Bueller?

Tuesday, March 27, 2012

Change in Job Execution - SQL Server 2005?

Change in Job Execution - SQL Server 2005?

Change in Job Execution - SQL Server 2005?

Change in Job Execution - SQL Server 2005?

Friday, February 24, 2012

CELCO - SQL For Smarties 2005 Ed.

Sunday, February 19, 2012

Cdosys

Sql Case sensitivity

Blog Archive

About Me