Job Outcome Constants (SQLDMO_JOBOUTCOME_TYPE)

How can i write a simple procedure as below, call it from a job, and because
@.m = 2 it will fail the job step and so show me the red x beside the job nam
e
in ent mgr?
create procedure marc1 as
declare @.m int
set @.m = 2
if @.m = 1
begin
print '2'
end
else
begin
print '2'
return -1
endmarcmc wrote:
> How can i write a simple procedure as below, call it from a job, and becau
se
> @.m = 2 it will fail the job step and so show me the red x beside the job n
ame
> in ent mgr?
> create procedure marc1 as
> declare @.m int
> set @.m = 2
> if @.m = 1
> begin
> print '2'
> end
> else
> begin
> print '2'
> return -1
> end
Use RAISERROR:
e.g. RAISERROR('My error message', 16, 1)

Job fails to communicate with SQL Server Agent

Hi,

Just need some help on a job issue.

The job in question gives the below mentioned error when run

Error 22022: Unable to post notification to SQL
ServerAgent (reason: OpenFileMapping()returned error
2, 'The system cannot find the file specified')

This seems to have started after I changed the owner for the Agent and MSSQL service from local service to a domain ID.

FYI, I have also changed the job owner from sa to the domain ID and have tried restarting the agent service in the hope that the Job can speak to the agent .. but it doesnt :(

Microsoft suggests to change some registry entries but I am not sure if that'd work
http://support.microsoft.com/default.aspx?scid=kb;en-us;911841&sd=rss&spid=2852

The server on which this job is hosted is a production box .. so am a litle apprehensive about making any registry changes

Any Suggestions ?

Thanks.

Warm Regards,
Ranjit.Hi people ...

Getting no instant fixes to this issue, I changed the service owner back to local system ... But on doing so .. things took a bad turn and the agent got hung .. we had no option but to call for an emergency reboot ..

That fixed the issue as the agents were set back to their usual settings.

I am still hunting for a permanant solution on this.. so would appreciate any help on this.

Thanks,
Ranjit.|||How did you change the accounts under which Agent & Server services are running? You must do it only via Enterpr. manager, not Windows services.

Job fails due to domian acccont

I have job running under a domian\userraccont.. It use to work fine..
suddenly it stoppped working..below is the error message
"""""The job failed. Unable to determine if the owner
(HO_PDOMAIN\cyresextractprod) of job Cypress Reserves Extract Processing has
server access (reason: Could not obtain information about Windows NT
group/user 'HO_PDOMAIN\cyresextractprod'. [SQLSTATE 42000] (Error 819"""
I could not even get the user login info by isuing xp_logininfo command..
I will be gald if any 1 responseCould be
PRB: 8198 Error Message Returned from Job Owned by Windows NT Authenticated
User
http://support.microsoft.com/defaul...b;en-us;q241643
Since we don't allow users to create/own jobs we set all our jobs to be
owned by sa to avoid this issue. This may not be applicable in your
situation but it does mean we never run into this issue :-)
Also have a look at the KB articles returned by this search (watch for URL
wrapping)
%26CDID%3DEN-US-KB%26PRODLISTSRC%3DON&withinResults=&QuerySource=gASr_Query&Produc
t=sql2k&Queryc=8198&Query=8198&KeywordType=ALL&maxResults=25&Titles=false&numDays=
&InCC=on" target="_blank">http://support.microsoft.com/search...br />
&InCC=on
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Babu" <Babu@.discussions.microsoft.com> wrote in message
news:47299136-8FE8-4573-8C70-561EE1C27C04@.microsoft.com...
>
> I have job running under a domian\userraccont.. It use to work fine..
> suddenly it stoppped working..below is the error message
> """""The job failed. Unable to determine if the owner
> (HO_PDOMAIN\cyresextractprod) of job Cypress Reserves Extract Processing
> has
> server access (reason: Could not obtain information about Windows NT
> group/user 'HO_PDOMAIN\cyresextractprod'. [SQLSTATE 42000] (Error 819"
""
> I could not even get the user login info by isuing xp_logininfo command..
> I will be gald if any 1 response

JDBC Driver Settings

Hello,
I saw the below in one of settings..where do i find them and where are they
defined..
see lot of select statements with sp_cursoropen with thousands of reads in
the
profiler but if i take the query and run it in the QA it will be 1/4 of
those reads .I
am wondering we will have to tweak somethings like you mentioned below.Could
you help me to find out where i could find those settings like fetchsize and
selectmode..
If you do decide to use cursor mode with ResultSets defined as
TYPE_FORWARD_ONLY and either CONCUR_READONLY or CONCUR_UPDATABLE, then you,
you can additionally test various values for the fetch size to tune the
performance. Otherwise, your fetch size will only be 1.
| Thread-Topic: JDBC Driver Settings
| thread-index: AcVKCoOrRDJWhsIWSTSRvlg4RlTrKQ==
| X-WBNR-Posting-Host: 12.214.173.82
| From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@.discussions.microsoft.com>
| Subject: JDBC Driver Settings
| Date: Mon, 25 Apr 2005 19:49:06 -0700
| Lines: 20
| Message-ID: <7959E226-7B55-4C1E-BD44-F74307B59BF2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:1049
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hello,
| I saw the below in one of settings..where do i find them and where are
they
| defined..
|
|
| see lot of select statements with sp_cursoropen with thousands of reads
in
| the
| profiler but if i take the query and run it in the QA it will be 1/4 of
| those reads .I
| am wondering we will have to tweak somethings like you mentioned
below.Could
| you help me to find out where i could find those settings like fetchsize
and
| selectmode..
|
|
| If you do decide to use cursor mode with ResultSets defined as
| TYPE_FORWARD_ONLY and either CONCUR_READONLY or CONCUR_UPDATABLE, then
you,
| you can additionally test various values for the fetch size to tune the
| performance. Otherwise, your fetch size will only be 1.
|
|
|
You can set the fetch size using the setFetchSize() method from your
Statement/PreparedStatement/CallableStatement object. SelectMethod can be
set to either Direct or Cursor, and this is done in the connection string:
SelectMethod=cursor
or
SelectMethod=direct
The default is Direct.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Carb,
But somebody is saying this..
Is this correct..
Microsoft JDBC driver doesn't support 'DIRECT' select mode for XADatasources
(XA database connections). So we have to use cursor method.
When I tried to switch to Direct, App didn't start with the error that
'direct' select method can not be used for XA Connections.
thanks,
""Carb Simien [MSFT]"" wrote:

> --
> | Thread-Topic: JDBC Driver Settings
> | thread-index: AcVKCoOrRDJWhsIWSTSRvlg4RlTrKQ==
> | X-WBNR-Posting-Host: 12.214.173.82
> | From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@.discussions.microsoft.com>
> | Subject: JDBC Driver Settings
> | Date: Mon, 25 Apr 2005 19:49:06 -0700
> | Lines: 20
> | Message-ID: <7959E226-7B55-4C1E-BD44-F74307B59BF2@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:1049
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | Hello,
> | I saw the below in one of settings..where do i find them and where are
> they
> | defined..
> |
> |
> | see lot of select statements with sp_cursoropen with thousands of reads
> in
> | the
> | profiler but if i take the query and run it in the QA it will be 1/4 of
> | those reads .I
> | am wondering we will have to tweak somethings like you mentioned
> below.Could
> | you help me to find out where i could find those settings like fetchsize
> and
> | selectmode..
> |
> |
> | If you do decide to use cursor mode with ResultSets defined as
> | TYPE_FORWARD_ONLY and either CONCUR_READONLY or CONCUR_UPDATABLE, then
> you,
> | you can additionally test various values for the fetch size to tune the
> | performance. Otherwise, your fetch size will only be 1.
> |
> |
> |
> You can set the fetch size using the setFetchSize() method from your
> Statement/PreparedStatement/CallableStatement object. SelectMethod can be
> set to either Direct or Cursor, and this is done in the connection string:
> SelectMethod=cursor
> or
> SelectMethod=direct
> The default is Direct.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
|||Chinn:
Yes, this is correct -- see below a quote from the Connection String
Properties section of the docs.
===
Direct-The direct method sends the complete result set in one
request to the driver. It is useful for queries that only produce a
small amount of data that you fetch completely. You should avoid
using direct when executing queries that produce a large amount
of data, as the result set is cached completely on the client and
constrains memory. In this mode, each statement requires its own
connection to the database. This is accomplished by "cloning"
connections. Cloned connections use the same connection
properties as the original connection; however, because
transactions must occur on a single connection, auto commit
mode is required. Due to this, JTA is not supported in direct mode.
In addition, some operations, such as updating an insensitive
result set, are not supported in direct mode because the driver
must create a second statement internally. Exceptions generated
due to the creation of cloned statements usually return an error
message similar to "Cannot start a cloned connection while in
manual transaction mode."
===
-shelby
Shelby Goerlitz
Microsoft, SQL Server
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:BDF3530D-CF92-43E6-85B2-9E0D0DAFF400@.microsoft.com...[vbcol=seagreen]
> Carb,
> But somebody is saying this..
> Is this correct..
> Microsoft JDBC driver doesn't support 'DIRECT' select mode for
> XADatasources
> (XA database connections). So we have to use cursor method.
> When I tried to switch to Direct, App didn't start with the error that
> 'direct' select method can not be used for XA Connections.
> thanks,
> ""Carb Simien [MSFT]"" wrote:
|||Shelby
Thanks Very Much For Explaining that.
Are there any settings we can tweak when using SelectMethod=Cursor..IF so
what are good and bad
Thanks,
"Shelby Goerlitz [MSFT]" wrote:

> Chinn:
> Yes, this is correct -- see below a quote from the Connection String
> Properties section of the docs.
> ===
> Direct-The direct method sends the complete result set in one
> request to the driver. It is useful for queries that only produce a
> small amount of data that you fetch completely. You should avoid
> using direct when executing queries that produce a large amount
> of data, as the result set is cached completely on the client and
> constrains memory. In this mode, each statement requires its own
> connection to the database. This is accomplished by "cloning"
> connections. Cloned connections use the same connection
> properties as the original connection; however, because
> transactions must occur on a single connection, auto commit
> mode is required. Due to this, JTA is not supported in direct mode.
> In addition, some operations, such as updating an insensitive
> result set, are not supported in direct mode because the driver
> must create a second statement internally. Exceptions generated
> due to the creation of cloned statements usually return an error
> message similar to "Cannot start a cloned connection while in
> manual transaction mode."
> ===
> -shelby
> Shelby Goerlitz
> Microsoft, SQL Server
>
> "chinn" <chinn@.discussions.microsoft.com> wrote in message
> news:BDF3530D-CF92-43E6-85B2-9E0D0DAFF400@.microsoft.com...
>
>
|||Chinn:
The main tweak w/ cursor mode is the setFetchSize setting discussed below..
-shelby
Shelby Goerlitz
Microsoft, SQL Server
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:93F68045-55C3-4276-AA2D-70B47877A26A@.microsoft.com...[vbcol=seagreen]
> Shelby
> Thanks Very Much For Explaining that.
> Are there any settings we can tweak when using SelectMethod=Cursor..IF so
> what are good and bad
>
> Thanks,
> "Shelby Goerlitz [MSFT]" wrote:
|||Hi Shelby..
Thanks For Your Pateince..
Can you tell me what do i set the value of fetchsize and what are the pro's
and con's
of that or is there a link where i can look up this information ..would be
great
if you can help me with this..
Thx
"Shelby Goerlitz [MSFT]" wrote:

> Chinn:
> The main tweak w/ cursor mode is the setFetchSize setting discussed below..
> -shelby
> Shelby Goerlitz
> Microsoft, SQL Server
> "chinn" <chinn@.discussions.microsoft.com> wrote in message
> news:93F68045-55C3-4276-AA2D-70B47877A26A@.microsoft.com...
>
>
|||Hello Chinn,
The setFetchSize setting allows you to fetch more rows at a time. Setting
it to a non-default value (larger than 1) will result in fewer calls to
sp_cursorfetch. You can reduce the number of round-trips to the database by
doing so. If you will be fetching a lot of rows this could significantly
improve the performance of your queries.
You will probably have to experiment with the right setting for you but,
for example, if your application typically works with 10 rows at a time,
you might want to set it to 10, etc.
Thanks,
Kamil
Kamil Sykora
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/securXity.
| From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@.discussions.microsoft.com>
| Subject: Re: JDBC Driver Settings
| Date: Wed, 4 May 2005 18:49:13 -0700
| Lines: 195
|
| Hi Shelby..
|
| Thanks For Your Pateince..
| Can you tell me what do i set the value of fetchsize and what are the
pro's
| and con's
| of that or is there a link where i can look up this information ..would
be
| great
| if you can help me with this..
|
| Thx
|
| "Shelby Goerlitz [MSFT]" wrote:
|
| > Chinn:
| >
| > The main tweak w/ cursor mode is the setFetchSize setting discussed
below..
| >
| > -shelby
| >
| > Shelby Goerlitz
| > Microsoft, SQL Server
| >
| > "chinn" <chinn@.discussions.microsoft.com> wrote in message
| > news:93F68045-55C3-4276-AA2D-70B47877A26A@.microsoft.com...
| > > Shelby
| > >
| > > Thanks Very Much For Explaining that.
| > > Are there any settings we can tweak when using
SelectMethod=Cursor..IF so
| > > what are good and bad
| > >
| > >
| > > Thanks,
| > >
| > > "Shelby Goerlitz [MSFT]" wrote:
| > >
| > >> Chinn:
| > >>
| > >> Yes, this is correct -- see below a quote from the Connection String
| > >> Properties section of the docs.
| > >>
| > >> ===
| > >> Direct-The direct method sends the complete result set in one
| > >>
| > >> request to the driver. It is useful for queries that only produce a
| > >>
| > >> small amount of data that you fetch completely. You should avoid
| > >>
| > >> using direct when executing queries that produce a large amount
| > >>
| > >> of data, as the result set is cached completely on the client and
| > >>
| > >> constrains memory. In this mode, each statement requires its own
| > >>
| > >> connection to the database. This is accomplished by "cloning"
| > >>
| > >> connections. Cloned connections use the same connection
| > >>
| > >> properties as the original connection; however, because
| > >>
| > >> transactions must occur on a single connection, auto commit
| > >>
| > >> mode is required. Due to this, JTA is not supported in direct mode.
| > >>
| > >> In addition, some operations, such as updating an insensitive
| > >>
| > >> result set, are not supported in direct mode because the driver
| > >>
| > >> must create a second statement internally. Exceptions generated
| > >>
| > >> due to the creation of cloned statements usually return an error
| > >>
| > >> message similar to "Cannot start a cloned connection while in
| > >>
| > >> manual transaction mode."
| > >>
| > >> ===
| > >>
| > >> -shelby
| > >>
| > >> Shelby Goerlitz
| > >> Microsoft, SQL Server
| > >>
| > >>
| > >>
| > >> "chinn" <chinn@.discussions.microsoft.com> wrote in message
| > >> news:BDF3530D-CF92-43E6-85B2-9E0D0DAFF400@.microsoft.com...
| > >> > Carb,
| > >> >
| > >> > But somebody is saying this..
| > >> >
| > >> > Is this correct..
| > >> >
| > >> > Microsoft JDBC driver doesn't support 'DIRECT' select mode for
| > >> > XADatasources
| > >> > (XA database connections). So we have to use cursor method.
| > >> > When I tried to switch to Direct, App didn't start with the error
that
| > >> > 'direct' select method can not be used for XA Connections.
| > >> >
| > >> > thanks,
| > >> >
| > >> > ""Carb Simien [MSFT]"" wrote:
| > >> >
| > >> >>
| > >> >> --
| > >> >> | Thread-Topic: JDBC Driver Settings
| > >> >> | thread-index: AcVKCoOrRDJWhsIWSTSRvlg4RlTrKQ==
| > >> >> | X-WBNR-Posting-Host: 12.214.173.82
| > >> >> | From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@.discussions.microsoft.com>
| > >> >> | Subject: JDBC Driver Settings
| > >> >> | Date: Mon, 25 Apr 2005 19:49:06 -0700
| > >> >> | Lines: 20
| > >> >> | Message-ID: <7959E226-7B55-4C1E-BD44-F74307B59BF2@.microsoft.com>
| > >> >> | MIME-Version: 1.0
| > >> >> | Content-Type: text/plain;
| > >> >> | charset="Utf-8"
| > >> >> | Content-Transfer-Encoding: 7bit
| > >> >> | X-Newsreader: Microsoft CDO for Windows 2000
| > >> >> | Content-Class: urn:content-classes:message
| > >> >> | Importance: normal
| > >> >> | Priority: normal
| > >> >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > >> >> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
| > >> >> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > >> >> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > >> >> | Xref: TK2MSFTNGXA01.phx.gbl
| > >> >> microsoft.public.sqlserver.jdbcdriver:1049
| > >> >> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
| > >> >> |
| > >> >> | Hello,
| > >> >> | I saw the below in one of settings..where do i find them and
where
| > >> >> are
| > >> >> they
| > >> >> | defined..
| > >> >> |
| > >> >> |
| > >> >> | see lot of select statements with sp_cursoropen with thousands
of
| > >> >> reads
| > >> >> in
| > >> >> | the
| > >> >> | profiler but if i take the query and run it in the QA it will
be 1/4
| > >> >> of
| > >> >> | those reads .I
| > >> >> | am wondering we will have to tweak somethings like you mentioned
| > >> >> below.Could
| > >> >> | you help me to find out where i could find those settings like
| > >> >> fetchsize
| > >> >> and
| > >> >> | selectmode..
| > >> >> |
| > >> >> |
| > >> >> | If you do decide to use cursor mode with ResultSets defined as
| > >> >> | TYPE_FORWARD_ONLY and either CONCUR_READONLY or
CONCUR_UPDATABLE,
| > >> >> then
| > >> >> you,
| > >> >> | you can additionally test various values for the fetch size to
tune
| > >> >> the
| > >> >> | performance. Otherwise, your fetch size will only be 1.
| > >> >> |
| > >> >> |
| > >> >> |
| > >> >>
| > >> >> You can set the fetch size using the setFetchSize() method from
your
| > >> >> Statement/PreparedStatement/CallableStatement object.
SelectMethod
| > >> >> can
| > >> >> be
| > >> >> set to either Direct or Cursor, and this is done in the connection
| > >> >> string:
| > >> >>
| > >> >> SelectMethod=cursor
| > >> >>
| > >> >> or
| > >> >>
| > >> >> SelectMethod=direct
| > >> >>
| > >> >> The default is Direct.
| > >> >>
| > >> >> Carb Simien, MCSE MCDBA MCAD
| > >> >> Microsoft Developer Support - Web Data
| > >> >>
| > >> >> Please reply only to the newsgroups.
| > >> >> This posting is provided "AS IS" with no warranties, and confers
no
| > >> >> rights.
| > >> >>
| > >> >> Are you secure? For information about the Strategic Technology
| > >> >> Protection
| > >> >> Program and to order your FREE Security Tool Kit, please visit
| > >> >> http://www.microsoft.com/security.
| > >> >>
| > >> >>
| > >>
| > >>
| > >>
| >
| >
| >
|