Job Status Suspended

i am trying to code a proc that tests whether a job is running. i
understand how to get job status from the xp_sqlagent_enum_jobs via the
current execution status and i also understand that status = 4 means
the job is idle. what does status =5 (suspended) mean? The BOL
information seems to indicate that the job isnt running when status is
either 4 or 5 (status 0 = not idle and not suspended)
thanks
markHow about this bit of TSQL
select step_id FROM
msdb.dbo.sysjobhistory
WHERE instance_id =
(SELECT max(instance_id) FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id=j.job_id
WHERE j.[name] = 'Your Job Name')
This will extract the current step that a job is on. If a has completed, the
step_id will be zero. If it hasn't completed, the step_id will be non-zero.
"markfcook@.gmail.com" wrote:

> i am trying to code a proc that tests whether a job is running. i
> understand how to get job status from the xp_sqlagent_enum_jobs via the
> current execution status and i also understand that status = 4 means
> the job is idle. what does status =5 (suspended) mean? The BOL
> information seems to indicate that the job isnt running when status is
> either 4 or 5 (status 0 = not idle and not suspended)
> thanks
> mark
>

Job Aborting ??

I have a job created on my server which runs a stored procedure. The
SP uses a cursor on a recordset to insert rows into a table (via a
stored procedure - hence the cursor).
It is expected that some inserts will fail due to missing information
or that a record already exists for it. One of the main reasons for me
using the cursor is so that ALL the data is not rolled back when this
occurrs.
But it seems that when I run the job, the job is aborting after a few
error messages. When I execute the SP throught query analyzer, it
completes. I can tell this is happening because of the difference in
row numbers afterwards. Another indication that the job is aborting is
that a summary message does not show up in the job history.
What could be the cause of the job aborting? What other info do I need
to provide? (Me posting table schemas and sample data may not help
since I could not give you a large enough dataset to simulate.)
Job Output (from history)
--
Executed as user: LSP\sa. Based on security type [Future], the
underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
[SQLSTATE 42000] (Error 50000) Associated statement is not prepared
[SQLSTATE HY007] (Error 0) Based on security type [Future], the
underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
[SQLSTATE 42000] (Error 50000). The step failed.
Count after Job
--
1737
Query Analyzer Output (notice last line summary output)
--
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: GGZ3 9999/GGZ3 XNAS
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: GGZ3 9999/GGZ3 XNAS
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
9999/GGZ3
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
9999/GGZ3
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: ERH4 9999/ERH4 XNAS
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: ERZ3 9999/ERZ3 XNAS
...
... and so on
...
Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
Based on security type [Future], the underlying symbol field cannot be
null for: NQH4 9999/NQH4 XNAS
SecurityMaster load completed : 6345 success, 0 skipped, 0
unqualified, 97 failed
Count after Job
--
6342Jason,
That is just how Agent work for TSQL jobsteps. It will abort processing if any error. I suggest you
put your TSQL in a script file and use a CmdExec jobstep instead, and use OSQL.EXE with the script
as an input file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> I have a job created on my server which runs a stored procedure. The
> SP uses a cursor on a recordset to insert rows into a table (via a
> stored procedure - hence the cursor).
> It is expected that some inserts will fail due to missing information
> or that a record already exists for it. One of the main reasons for me
> using the cursor is so that ALL the data is not rolled back when this
> occurrs.
> But it seems that when I run the job, the job is aborting after a few
> error messages. When I execute the SP throught query analyzer, it
> completes. I can tell this is happening because of the difference in
> row numbers afterwards. Another indication that the job is aborting is
> that a summary message does not show up in the job history.
> What could be the cause of the job aborting? What other info do I need
> to provide? (Me posting table schemas and sample data may not help
> since I could not give you a large enough dataset to simulate.)
> Job Output (from history)
> --
> Executed as user: LSP\sa. Based on security type [Future], the
> underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> [SQLSTATE 42000] (Error 50000). The step failed.
> Count after Job
> --
> 1737
> Query Analyzer Output (notice last line summary output)
> --
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: GGZ3 9999/GGZ3 XNAS
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: GGZ3 9999/GGZ3 XNAS
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
> 9999/GGZ3
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
> 9999/GGZ3
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: ERH4 9999/ERH4 XNAS
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: ERZ3 9999/ERZ3 XNAS
> ...
> ... and so on
> ...
> Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> Based on security type [Future], the underlying symbol field cannot be
> null for: NQH4 9999/NQH4 XNAS
> SecurityMaster load completed : 6345 success, 0 skipped, 0
> unqualified, 97 failed
> Count after Job
> --
> 6342
>|||What you said makes sense but I am unable to prove it.
Given the following code, the print statement 'Got to here' should NOT print
when run as a job but it does.
USE TempDb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE [id] =object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spTest]
GO
CREATE PROCEDURE spTest
AS
SET NOCOUNT ON
RAISERROR('Test error', 15, 1)
PRINT 'Got to end'
GO
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Jason,
> That is just how Agent work for TSQL jobsteps. It will abort processing if
any error. I suggest you
> put your TSQL in a script file and use a CmdExec jobstep instead, and use
OSQL.EXE with the script
> as an input file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > I have a job created on my server which runs a stored procedure. The
> > SP uses a cursor on a recordset to insert rows into a table (via a
> > stored procedure - hence the cursor).
> >
> > It is expected that some inserts will fail due to missing information
> > or that a record already exists for it. One of the main reasons for me
> > using the cursor is so that ALL the data is not rolled back when this
> > occurrs.
> >
> > But it seems that when I run the job, the job is aborting after a few
> > error messages. When I execute the SP throught query analyzer, it
> > completes. I can tell this is happening because of the difference in
> > row numbers afterwards. Another indication that the job is aborting is
> > that a summary message does not show up in the job history.
> >
> > What could be the cause of the job aborting? What other info do I need
> > to provide? (Me posting table schemas and sample data may not help
> > since I could not give you a large enough dataset to simulate.)
> >
> > Job Output (from history)
> > --
> > Executed as user: LSP\sa. Based on security type [Future], the
> > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > [SQLSTATE 42000] (Error 50000). The step failed.
> >
> > Count after Job
> > --
> > 1737
> >
> > Query Analyzer Output (notice last line summary output)
> > --
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: GGZ3 9999/GGZ3 XNAS
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: GGZ3 9999/GGZ3 XNAS
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
> > 9999/GGZ3
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
> > 9999/GGZ3
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: ERH4 9999/ERH4 XNAS
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: ERZ3 9999/ERZ3 XNAS
> > ...
> > ... and so on
> > ...
> > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > Based on security type [Future], the underlying symbol field cannot be
> > null for: NQH4 9999/NQH4 XNAS
> > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > unqualified, 97 failed
> >
> > Count after Job
> > --
> > 6342
> >
> >
>|||I created a TSQL jobstep where I call the proc and an output file. I did not get the print in the
output file. Do you see anything else?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> What you said makes sense but I am unable to prove it.
> Given the following code, the print statement 'Got to here' should NOT print
> when run as a job but it does.
> USE TempDb
> GO
> IF EXISTS (SELECT * FROM sysobjects WHERE [id] => object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
> DROP PROCEDURE [dbo].[spTest]
> GO
> CREATE PROCEDURE spTest
> AS
> SET NOCOUNT ON
> RAISERROR('Test error', 15, 1)
> PRINT 'Got to end'
> GO
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Jason,
> >
> > That is just how Agent work for TSQL jobsteps. It will abort processing if
> any error. I suggest you
> > put your TSQL in a script file and use a CmdExec jobstep instead, and use
> OSQL.EXE with the script
> > as an input file.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > I have a job created on my server which runs a stored procedure. The
> > > SP uses a cursor on a recordset to insert rows into a table (via a
> > > stored procedure - hence the cursor).
> > >
> > > It is expected that some inserts will fail due to missing information
> > > or that a record already exists for it. One of the main reasons for me
> > > using the cursor is so that ALL the data is not rolled back when this
> > > occurrs.
> > >
> > > But it seems that when I run the job, the job is aborting after a few
> > > error messages. When I execute the SP throught query analyzer, it
> > > completes. I can tell this is happening because of the difference in
> > > row numbers afterwards. Another indication that the job is aborting is
> > > that a summary message does not show up in the job history.
> > >
> > > What could be the cause of the job aborting? What other info do I need
> > > to provide? (Me posting table schemas and sample data may not help
> > > since I could not give you a large enough dataset to simulate.)
> > >
> > > Job Output (from history)
> > > --
> > > Executed as user: LSP\sa. Based on security type [Future], the
> > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > [SQLSTATE 42000] (Error 50000). The step failed.
> > >
> > > Count after Job
> > > --
> > > 1737
> > >
> > > Query Analyzer Output (notice last line summary output)
> > > --
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: GGZ3 9999/GGZ3 XNAS
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: GGZ3 9999/GGZ3 XNAS
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P GGZ3
> > > 9999/GGZ3
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 95
> > > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C GGZ3
> > > 9999/GGZ3
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: ERH4 9999/ERH4 XNAS
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: ERZ3 9999/ERZ3 XNAS
> > > ...
> > > ... and so on
> > > ...
> > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line 46
> > > Based on security type [Future], the underlying symbol field cannot be
> > > null for: NQH4 9999/NQH4 XNAS
> > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > unqualified, 97 failed
> > >
> > > Count after Job
> > > --
> > > 6342
> > >
> > >
> >
> >
>|||This is the output from job history.
Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got to
end [SQLSTATE 01000] (Error 0). The step failed.
Could you post your code you I can try it on my system?
Thanks,
Jason
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> I created a TSQL jobstep where I call the proc and an output file. I did
not get the print in the
> output file. Do you see anything else?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > What you said makes sense but I am unable to prove it.
> >
> > Given the following code, the print statement 'Got to here' should NOT
print
> > when run as a job but it does.
> >
> > USE TempDb
> > GO
> >
> > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') =1)
> > DROP PROCEDURE [dbo].[spTest]
> > GO
> >
> > CREATE PROCEDURE spTest
> > AS
> > SET NOCOUNT ON
> >
> > RAISERROR('Test error', 15, 1)
> >
> > PRINT 'Got to end'
> > GO
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Jason,
> > >
> > > That is just how Agent work for TSQL jobsteps. It will abort
processing if
> > any error. I suggest you
> > > put your TSQL in a script file and use a CmdExec jobstep instead, and
use
> > OSQL.EXE with the script
> > > as an input file.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > I have a job created on my server which runs a stored procedure. The
> > > > SP uses a cursor on a recordset to insert rows into a table (via a
> > > > stored procedure - hence the cursor).
> > > >
> > > > It is expected that some inserts will fail due to missing
information
> > > > or that a record already exists for it. One of the main reasons for
me
> > > > using the cursor is so that ALL the data is not rolled back when
this
> > > > occurrs.
> > > >
> > > > But it seems that when I run the job, the job is aborting after a
few
> > > > error messages. When I execute the SP throught query analyzer, it
> > > > completes. I can tell this is happening because of the difference in
> > > > row numbers afterwards. Another indication that the job is aborting
is
> > > > that a summary message does not show up in the job history.
> > > >
> > > > What could be the cause of the job aborting? What other info do I
need
> > > > to provide? (Me posting table schemas and sample data may not help
> > > > since I could not give you a large enough dataset to simulate.)
> > > >
> > > > Job Output (from history)
> > > > --
> > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > >
> > > > Count after Job
> > > > --
> > > > 1737
> > > >
> > > > Query Analyzer Output (notice last line summary output)
> > > > --
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
95
> > > > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P
GGZ3
> > > > 9999/GGZ3
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
95
> > > > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C
GGZ3
> > > > 9999/GGZ3
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: ERH4 9999/ERH4 XNAS
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > ...
> > > > ... and so on
> > > > ...
> > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
46
> > > > Based on security type [Future], the underlying symbol field cannot
be
> > > > null for: NQH4 9999/NQH4 XNAS
> > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > unqualified, 97 failed
> > > >
> > > > Count after Job
> > > > --
> > > > 6342
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yes, indeed, you are right. I see that now when looking a bit closer (my class was about to start,
so I was a bit hasty when looking as the output earlier). So, it seems like Agent will stop
processing under *some* circumstances (evidently) and I guess one could do some research to find out
under what circumstances. Fact is, however, that Agent will stop processing under some circumstances
and a way around that is to execute the SQL code from OSQL instead of Agent. :-)
...
I tried one more thing. Adding a second PRINT after the proc call didn't make a difference. However,
when adding another batch (GO) that batch was not executed if the first had an error, while it was
executed if the first one was execute OK. I guess it all makes sense if you think about how batches
are handled in SQL Server.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:%23eBHumXxDHA.1576@.TK2MSFTNGP11.phx.gbl...
> This is the output from job history.
> Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got to
> end [SQLSTATE 01000] (Error 0). The step failed.
> Could you post your code you I can try it on my system?
> Thanks,
> Jason
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> > I created a TSQL jobstep where I call the proc and an output file. I did
> not get the print in the
> > output file. Do you see anything else?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > > What you said makes sense but I am unable to prove it.
> > >
> > > Given the following code, the print statement 'Got to here' should NOT
> print
> > > when run as a job but it does.
> > >
> > > USE TempDb
> > > GO
> > >
> > > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id], N'IsProcedure') => 1)
> > > DROP PROCEDURE [dbo].[spTest]
> > > GO
> > >
> > > CREATE PROCEDURE spTest
> > > AS
> > > SET NOCOUNT ON
> > >
> > > RAISERROR('Test error', 15, 1)
> > >
> > > PRINT 'Got to end'
> > > GO
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > Jason,
> > > >
> > > > That is just how Agent work for TSQL jobsteps. It will abort
> processing if
> > > any error. I suggest you
> > > > put your TSQL in a script file and use a CmdExec jobstep instead, and
> use
> > > OSQL.EXE with the script
> > > > as an input file.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > I have a job created on my server which runs a stored procedure. The
> > > > > SP uses a cursor on a recordset to insert rows into a table (via a
> > > > > stored procedure - hence the cursor).
> > > > >
> > > > > It is expected that some inserts will fail due to missing
> information
> > > > > or that a record already exists for it. One of the main reasons for
> me
> > > > > using the cursor is so that ALL the data is not rolled back when
> this
> > > > > occurrs.
> > > > >
> > > > > But it seems that when I run the job, the job is aborting after a
> few
> > > > > error messages. When I execute the SP throught query analyzer, it
> > > > > completes. I can tell this is happening because of the difference in
> > > > > row numbers afterwards. Another indication that the job is aborting
> is
> > > > > that a summary message does not show up in the job history.
> > > > >
> > > > > What could be the cause of the job aborting? What other info do I
> need
> > > > > to provide? (Me posting table schemas and sample data may not help
> > > > > since I could not give you a large enough dataset to simulate.)
> > > > >
> > > > > Job Output (from history)
> > > > > --
> > > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > > [SQLSTATE 42000] (Error 50000) Associated statement is not prepared
> > > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3 XNAS
> > > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > > >
> > > > > Count after Job
> > > > > --
> > > > > 1737
> > > > >
> > > > > Query Analyzer Output (notice last line summary output)
> > > > > --
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 95
> > > > > The underlying security does not exist: GGZ33DEC420P GGZ33DEC420P
> GGZ3
> > > > > 9999/GGZ3
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 95
> > > > > The underlying security does not exist: GGZ33DEC460C GGZ33DEC460C
> GGZ3
> > > > > 9999/GGZ3
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: ERH4 9999/ERH4 XNAS
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > > ...
> > > > > ... and so on
> > > > > ...
> > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity, Line
> 46
> > > > > Based on security type [Future], the underlying symbol field cannot
> be
> > > > > null for: NQH4 9999/NQH4 XNAS
> > > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > > unqualified, 97 failed
> > > > >
> > > > > Count after Job
> > > > > --
> > > > > 6342
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I modified my problem job to use osql and seems to be running as expected.
What is the difference in how Agent submits jobs and how OSQL does?
Thanks for all your help.
- Jason
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eDkPJwXxDHA.3416@.tk2msftngp13.phx.gbl...
> Yes, indeed, you are right. I see that now when looking a bit closer (my
class was about to start,
> so I was a bit hasty when looking as the output earlier). So, it seems
like Agent will stop
> processing under *some* circumstances (evidently) and I guess one could do
some research to find out
> under what circumstances. Fact is, however, that Agent will stop
processing under some circumstances
> and a way around that is to execute the SQL code from OSQL instead of
Agent. :-)
> ...
> I tried one more thing. Adding a second PRINT after the proc call didn't
make a difference. However,
> when adding another batch (GO) that batch was not executed if the first
had an error, while it was
> executed if the first one was execute OK. I guess it all makes sense if
you think about how batches
> are handled in SQL Server.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> news:%23eBHumXxDHA.1576@.TK2MSFTNGP11.phx.gbl...
> > This is the output from job history.
> >
> > Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got
to
> > end [SQLSTATE 01000] (Error 0). The step failed.
> >
> > Could you post your code you I can try it on my system?
> >
> > Thanks,
> > Jason
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> > > I created a TSQL jobstep where I call the proc and an output file. I
did
> > not get the print in the
> > > output file. Do you see anything else?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > > > What you said makes sense but I am unable to prove it.
> > > >
> > > > Given the following code, the print statement 'Got to here' should
NOT
> > print
> > > > when run as a job but it does.
> > > >
> > > > USE TempDb
> > > > GO
> > > >
> > > > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > > > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id],
N'IsProcedure') => > 1)
> > > > DROP PROCEDURE [dbo].[spTest]
> > > > GO
> > > >
> > > > CREATE PROCEDURE spTest
> > > > AS
> > > > SET NOCOUNT ON
> > > >
> > > > RAISERROR('Test error', 15, 1)
> > > >
> > > > PRINT 'Got to end'
> > > > GO
> > > >
> > > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > > Jason,
> > > > >
> > > > > That is just how Agent work for TSQL jobsteps. It will abort
> > processing if
> > > > any error. I suggest you
> > > > > put your TSQL in a script file and use a CmdExec jobstep instead,
and
> > use
> > > > OSQL.EXE with the script
> > > > > as an input file.
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > Archive at:
> > > >
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > >
> > > > >
> > > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > > I have a job created on my server which runs a stored procedure.
The
> > > > > > SP uses a cursor on a recordset to insert rows into a table (via
a
> > > > > > stored procedure - hence the cursor).
> > > > > >
> > > > > > It is expected that some inserts will fail due to missing
> > information
> > > > > > or that a record already exists for it. One of the main reasons
for
> > me
> > > > > > using the cursor is so that ALL the data is not rolled back when
> > this
> > > > > > occurrs.
> > > > > >
> > > > > > But it seems that when I run the job, the job is aborting after
a
> > few
> > > > > > error messages. When I execute the SP throught query analyzer,
it
> > > > > > completes. I can tell this is happening because of the
difference in
> > > > > > row numbers afterwards. Another indication that the job is
aborting
> > is
> > > > > > that a summary message does not show up in the job history.
> > > > > >
> > > > > > What could be the cause of the job aborting? What other info do
I
> > need
> > > > > > to provide? (Me posting table schemas and sample data may not
help
> > > > > > since I could not give you a large enough dataset to simulate.)
> > > > > >
> > > > > > Job Output (from history)
> > > > > > --
> > > > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
XNAS
> > > > > > [SQLSTATE 42000] (Error 50000) Associated statement is not
prepared
> > > > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
XNAS
> > > > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > > > >
> > > > > > Count after Job
> > > > > > --
> > > > > > 1737
> > > > > >
> > > > > > Query Analyzer Output (notice last line summary output)
> > > > > > --
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 95
> > > > > > The underlying security does not exist: GGZ33DEC420P
GGZ33DEC420P
> > GGZ3
> > > > > > 9999/GGZ3
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 95
> > > > > > The underlying security does not exist: GGZ33DEC460C
GGZ33DEC460C
> > GGZ3
> > > > > > 9999/GGZ3
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: ERH4 9999/ERH4 XNAS
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > > > ...
> > > > > > ... and so on
> > > > > > ...
> > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
Line
> > 46
> > > > > > Based on security type [Future], the underlying symbol field
cannot
> > be
> > > > > > null for: NQH4 9999/NQH4 XNAS
> > > > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > > > unqualified, 97 failed
> > > > > >
> > > > > > Count after Job
> > > > > > --
> > > > > > 6342
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> What is the difference in how Agent submits jobs and how OSQL does?
AFAIK, both use ODBC. Apart from that, I don't know of any technical details released on the
subject.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason Callas" <JayCallas@.hotmail.com> wrote in message
news:eBTMeDYxDHA.2712@.tk2msftngp13.phx.gbl...
> I modified my problem job to use osql and seems to be running as expected.
> What is the difference in how Agent submits jobs and how OSQL does?
> Thanks for all your help.
> - Jason
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eDkPJwXxDHA.3416@.tk2msftngp13.phx.gbl...
> > Yes, indeed, you are right. I see that now when looking a bit closer (my
> class was about to start,
> > so I was a bit hasty when looking as the output earlier). So, it seems
> like Agent will stop
> > processing under *some* circumstances (evidently) and I guess one could do
> some research to find out
> > under what circumstances. Fact is, however, that Agent will stop
> processing under some circumstances
> > and a way around that is to execute the SQL code from OSQL instead of
> Agent. :-)
> > ...
> >
> > I tried one more thing. Adding a second PRINT after the proc call didn't
> make a difference. However,
> > when adding another batch (GO) that batch was not executed if the first
> had an error, while it was
> > executed if the first one was execute OK. I guess it all makes sense if
> you think about how batches
> > are handled in SQL Server.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > news:%23eBHumXxDHA.1576@.TK2MSFTNGP11.phx.gbl...
> > > This is the output from job history.
> > >
> > > Executed as user: LSP\sa. Test Error [SQLSTATE 42000] (Error 50000) Got
> to
> > > end [SQLSTATE 01000] (Error 0). The step failed.
> > >
> > > Could you post your code you I can try it on my system?
> > >
> > > Thanks,
> > > Jason
> > >
> > > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:enWaMPXxDHA.556@.TK2MSFTNGP11.phx.gbl...
> > > > I created a TSQL jobstep where I call the proc and an output file. I
> did
> > > not get the print in the
> > > > output file. Do you see anything else?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > news:u5nKNCXxDHA.3428@.TK2MSFTNGP11.phx.gbl...
> > > > > What you said makes sense but I am unable to prove it.
> > > > >
> > > > > Given the following code, the print statement 'Got to here' should
> NOT
> > > print
> > > > > when run as a job but it does.
> > > > >
> > > > > USE TempDb
> > > > > GO
> > > > >
> > > > > IF EXISTS (SELECT * FROM sysobjects WHERE [id] => > > > > object_id(N'[dbo].[spTest]') AND OBJECTPROPERTY([id],
> N'IsProcedure') => > > 1)
> > > > > DROP PROCEDURE [dbo].[spTest]
> > > > > GO
> > > > >
> > > > > CREATE PROCEDURE spTest
> > > > > AS
> > > > > SET NOCOUNT ON
> > > > >
> > > > > RAISERROR('Test error', 15, 1)
> > > > >
> > > > > PRINT 'Got to end'
> > > > > GO
> > > > >
> > > > > "Tibor Karaszi"
> > > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > > > wrote in message news:eL96Q4UxDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > > > Jason,
> > > > > >
> > > > > > That is just how Agent work for TSQL jobsteps. It will abort
> > > processing if
> > > > > any error. I suggest you
> > > > > > put your TSQL in a script file and use a CmdExec jobstep instead,
> and
> > > use
> > > > > OSQL.EXE with the script
> > > > > > as an input file.
> > > > > >
> > > > > > --
> > > > > > Tibor Karaszi, SQL Server MVP
> > > > > > Archive at:
> > > > >
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > > >
> > > > > >
> > > > > > "Jason Callas" <JayCallas@.hotmail.com> wrote in message
> > > > > > news:%23ZaXSZOxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > > > I have a job created on my server which runs a stored procedure.
> The
> > > > > > > SP uses a cursor on a recordset to insert rows into a table (via
> a
> > > > > > > stored procedure - hence the cursor).
> > > > > > >
> > > > > > > It is expected that some inserts will fail due to missing
> > > information
> > > > > > > or that a record already exists for it. One of the main reasons
> for
> > > me
> > > > > > > using the cursor is so that ALL the data is not rolled back when
> > > this
> > > > > > > occurrs.
> > > > > > >
> > > > > > > But it seems that when I run the job, the job is aborting after
> a
> > > few
> > > > > > > error messages. When I execute the SP throught query analyzer,
> it
> > > > > > > completes. I can tell this is happening because of the
> difference in
> > > > > > > row numbers afterwards. Another indication that the job is
> aborting
> > > is
> > > > > > > that a summary message does not show up in the job history.
> > > > > > >
> > > > > > > What could be the cause of the job aborting? What other info do
> I
> > > need
> > > > > > > to provide? (Me posting table schemas and sample data may not
> help
> > > > > > > since I could not give you a large enough dataset to simulate.)
> > > > > > >
> > > > > > > Job Output (from history)
> > > > > > > --
> > > > > > > Executed as user: LSP\sa. Based on security type [Future], the
> > > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
> XNAS
> > > > > > > [SQLSTATE 42000] (Error 50000) Associated statement is not
> prepared
> > > > > > > [SQLSTATE HY007] (Error 0) Based on security type [Future], the
> > > > > > > underlying identity field cannot be null for: GGZ3 9999/GGZ3
> XNAS
> > > > > > > [SQLSTATE 42000] (Error 50000). The step failed.
> > > > > > >
> > > > > > > Count after Job
> > > > > > > --
> > > > > > > 1737
> > > > > > >
> > > > > > > Query Analyzer Output (notice last line summary output)
> > > > > > > --
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: GGZ3 9999/GGZ3 XNAS
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 95
> > > > > > > The underlying security does not exist: GGZ33DEC420P
> GGZ33DEC420P
> > > GGZ3
> > > > > > > 9999/GGZ3
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 95
> > > > > > > The underlying security does not exist: GGZ33DEC460C
> GGZ33DEC460C
> > > GGZ3
> > > > > > > 9999/GGZ3
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: ERH4 9999/ERH4 XNAS
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: ERZ3 9999/ERZ3 XNAS
> > > > > > > ...
> > > > > > > ... and so on
> > > > > > > ...
> > > > > > > Server: Msg 50000, Level 15, State 1, Procedure spAddSecurity,
> Line
> > > 46
> > > > > > > Based on security type [Future], the underlying symbol field
> cannot
> > > be
> > > > > > > null for: NQH4 9999/NQH4 XNAS
> > > > > > > SecurityMaster load completed : 6345 success, 0 skipped, 0
> > > > > > > unqualified, 97 failed
> > > > > > >
> > > > > > > Count after Job
> > > > > > > --
> > > > > > > 6342
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

JET Anonymous Merge: Can It Be Done?

Is it possible to set up
Publisher: SQL Server 2000
Subscriber: Access 97
Merge replication
Anonymous pull subscriptions
Over the Internet via VPN
I've read the BOL and other on-line artices, but don't quite get it.
Links to specific examples?
Thanks
Nope, you might be able to do a push if you could map a drive. The problem
is the linked server definition to enable access as a database you need to
use if you are replicating to a access database. Review the sample chapter
download in the nwsu link for my book for more info on how to replicate to
access.
The problem is in your term anonymous pull. For a pull to work you must have
SQL Server or MSDE installed on the remote machine.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<richerwin@.ubs-europe.org> wrote in message
news:1107726821.588526.272280@.l41g2000cwc.googlegr oups.com...
> Is it possible to set up
> Publisher: SQL Server 2000
> Subscriber: Access 97
> Merge replication
> Anonymous pull subscriptions
> Over the Internet via VPN
> I've read the BOL and other on-line artices, but don't quite get it.
> Links to specific examples?
> Thanks
>

jdbc-sql 2000 error

Hi,
I'm having trouble connecting to Sql Server 2000 via Java 2 (5.0).
I'm on Windows Server 2003 and trying to connect locally from the
server. I've downloaded the jar files (mssqlserver.jar,msutil.jar,
msbase.jar) containing the jdbc driver from microsoft and placed them in
the same directory as my java file, below is a snippet of my code to
test the connection:
public void openTest() throws Exception
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver" );
Connection m_Conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://local:1433", "", "");
}
I compile using the following command there are no errors:
javac -classpath ".;./mssqlserver.jar;./msbase.jar;./msutil.jar"
Test.java
At runtime i get the following error,
Exception in thread "main" java.lang.ClassNotFoundException:
com.microsoft.jdbc.
sqlserver.SQLServerDriver
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at Hello.openTest(HelloPrint.java:30)
at HelloPrint.main(HelloPrint.java:11)
Can anyone help? Many thanks in advance.
InderHi
It's been a while since I used the JDBC driver!
microsoft.public.sqlserver.jdbcdriver may be a better newsgroup to post to.
You may want to make sure the .jar files are on the classpath and you may
want to re-run the setup just in case there is some corruption.
John
"inder" wrote:

> Hi,
> I'm having trouble connecting to Sql Server 2000 via Java 2 (5.0).
> I'm on Windows Server 2003 and trying to connect locally from the
> server. I've downloaded the jar files (mssqlserver.jar,msutil.jar,
> msbase.jar) containing the jdbc driver from microsoft and placed them in
> the same directory as my java file, below is a snippet of my code to
> test the connection:
> public void openTest() throws Exception
> {
> Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver" );
> Connection m_Conn = DriverManager.getConnection
> ("jdbc:microsoft:sqlserver://local:1433", "", "");
> }
>
> I compile using the following command there are no errors:
> javac -classpath ".;./mssqlserver.jar;./msbase.jar;./msutil.jar"
> Test.java
> At runtime i get the following error,
>
> Exception in thread "main" java.lang.ClassNotFoundException:
> com.microsoft.jdbc.
> sqlserver.SQLServerDriver
> at java.net.URLClassLoader$1.run(Unknown Source)
> at java.security.AccessController.doPrivileged(Native Method)
> at java.net.URLClassLoader.findClass(Unknown Source)
> at java.lang.ClassLoader.loadClass(Unknown Source)
> at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
> at java.lang.ClassLoader.loadClass(Unknown Source)
> at java.lang.ClassLoader.loadClassInternal(Unknown Source)
> at java.lang.Class.forName0(Native Method)
> at java.lang.Class.forName(Unknown Source)
> at Hello.openTest(HelloPrint.java:30)
> at HelloPrint.main(HelloPrint.java:11)
>
> Can anyone help? Many thanks in advance.
> Inder
>|||Hi John,
I've got that error fixed; you were right, it was a classpath
problem. Now i have another error,
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
when i run the following,
Connection m_Conn =
DriverManager.getConnection("jdbc:microsoft:sqlserver://servername:1433;
DatabaseName=Northwind","","");
The problem is that my 'servername' is the same as my windows server
2003 domain and when i setup my sql server 2000 i set it to use the
windows authentication, so now i'm not sure what to put for the username
and password in the above string.
Many thanks again,
Inderjit
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
This may help a bit.
http://support.microsoft.com/defaul...kb;en-us;313100
AFIK the JDBC driver does not support Windows Authentication as it is
not a type 4 driver. You may want to look at
http://www.jnetdirect.com/products.php?op=jsqlconnect
John

jdbc with mdx

Our reports are currently running off a data warehouse. The data is accessed via JDBC. We want to move all of our data to SSAS. However, as far as I can tell JDBC does not interface with MDX, only SQL and stored procedures. How can I access my SSAS data via JDBC?

Thanks

It is not possible to access data in SSAS with JDBC directly. It is possible to set up linked server in SQL Server to SSAS, and then access that linked server through JDBC. But this is very non-optimal solution (if it will even work).

JDBC sample code using named pipe and SQLServer 2005 driver?

Hi,

Does anybody have a working Java code sample that connects to an SQLServer 2005 database on a remote host, via the default named pipe, from a client using the SQLServer 2005 JDBC driver? Could you post it, or a pointer to it?

I've gotten java.sql DriverManager.getConnection() to work fine with TCP/IP connections before. But I'm a newbie with named pipes, and unclear on how the connection string/properties are different. I've tried to piece it together from multiple docs and threads, but haven't found sample code that quite fits my situation. I think a simple working example would best clarify the syntax.

The server is not using SQL Express. Most SQLServer configuration options are defaults; the named pipes protocol is enabled.

Thanks

Hi Dan,

The Microsoft SQL Server 2005 JDBC Driver does not support connections over named pipes. It only supports TCP/IP connections.

--David Olix

|||David,

Thanks for the info and your quick response.

I suggest mentioning this in the JDBC docs. I couldn't find it in my searching.

- Dan N.

jdbc refuses connection with 'user not associated with a trusted SQL Server connection' messag

When using jdbc with IntegratedSecurity, I run into this problem when the machine is not part of a domain & gets its IP address via dhcp. Is this expected behavior or a bug in the jdbc driver.

The SQLServer and client application are installed on the same machine and a local admin is logged in, running the client app.

If I change one of the two parameters mentioned above, the connection can be established leading me to believe this may be intentional for security issues. Am I correct?

To clarify, "not part of domain" means you disconnected from the domain or you machine is part of workgroup? Do you mean that "IP address works but server name dosen't or otherwise around.|||That is correct. If I have the scenario where the machine is part of a workgroup and I am using dhcp, the connection gets refused. If I either add the machine to a domain or assign a static IP address to the NIC), the connection succeeds. So, what I meant by 'using dhcp' is that the NIC is configured to use dhcp in the TCP/IP settings of the Network Connection properties. I have not tried an environment where one NIC is dhcp and another is static.

Also, if I join a domain, the connection will succeed with the NIC configured for dhcp.

Hopefully that is clearer.

|||

To help us understand the problem and narrow down where the problem might be, please provide us with the SQL Server version and the JDBC driver version?

Please also provide the JDBC connection URL (minus any sensitive data).

Can you verify that another driver (say SNAC / MDAC) properly connects to SQL Server in the same configuration to make sure it is a JDBC driver issue versus some machine configuration issue.

If your SQL Server is SQL Server 2005, you can find the "sqlcmd.exe" tool in the "Microsoft SQL Server\90\Tools\Binn\" install directory. You can connect with Integrated Authentication to the local SQL Server using sqlcmd.exe with the following syntax: sqlcmd -Hlocalhost -E

Alternatively, if you have "osql.exe" you can perform the same test "osql -Hlocalhost -E". If you do not have access to either of these tools, you can also use a "Universal Data Link" file. Please see http://msdn2.microsoft.com/en-us/library/e38h511e(vs.71).aspx and http://support.microsoft.com/kb/274536 for additional information.

This will help narrow down if there is an issue with machine configuration or JDBC driver.

Jimmy

|||SQL Version: SQL Server 2005 Express Edition (tried both SP1 and SP2)
JDBC Version: 1.1

Since I am using SQL Server 2005, I did run both sqlcmd and osql and was able to execute the following statement

SELECT convert(varchar(15), SERVERPROPERTY('productversion')), convert(varchar(5), SERVERPROPERTY('productlevel')), convert(varchar(20), SERVERPROPERTY('edition'))

I got a row back with the version information I expected so it looks like I was able to connect. In addition, SQL Server Management Studio is able to run and connect with Windows Authentication.

Here is the code that I am trying to execute. The intention of this snippet it to connect to SQL Server so that I can create a database. The user running the application had admin rights.

Code Snippet

try
(
String address = "1.1.1.1";
String port = "1433";
String db_name = "MyAppDB";
Connection my_connection;
String url;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
url = "jdbc:sqlserver://" + address + ":" + port + ";integratedSecurity=true;";
my_connection = DriverManager.getConnection(url);

Statement create_stmt = conn.createStatement();
create_stmt.execute("CREATE DATABASE " + db_name);
}
catch (SQLException e)
{
//WriteErrorMsg("Error establishing JDBC connection (" + e.getErrorCode() + ") " + e.getMessage());
//return OPERATION_ERROR;
}
catch (ClassNotFoundException e)
{
//WriteErrorMsg("Class error creating " + db_name + ": " + e.getMessage());
//return EXCEPTION_CAUGHT;
}

Let me know if there is any other info I can get you.
Thanks for the help
john
|||

In the code snippet provided you are attempting to connect to an IP address of 1.1.1.1 and port 1433. If your machine has DHCP enabled, does it actually get 1.1.1.1 as the IP address?

If you only plan to run the client application on the same machine as SQL Server, then I would recommend either specifying "localhost" as the server name or the actual server name in the connection URL.

example: url = "jdbcqlserver://localhost:1433;integratedSecurity=true;"

Also, typically SQL Server Express is not installed to use the fixed port 1433, did you configure the instance to the fixed 1433 port?

If not, I would recommend enabling the SQL Browser service and update the connection URL to something like:

url = "jdbcqlserver://localhost;instanceName=SQLEXPRESS;integratedSecurity=true;"

HTH,

Jimmy

|||That was not the real address. The snippet is part of function where the address is passed in as a paramenter. It would either be set to an address(ie 10.47.20.46) or localhost. I just put that in as more of placeholder. Bad choice of documenting, sorry for any confusion. The SQL server may or may not be on the same machine. In the case where SQL is local on another machine, I would not use integrated security and provide credentials for SQL authentication.

Yes, SQL is configured to configured for port 1433 in this case.

thanks

jdbc refuses connection with ''user not associated with a trusted SQL Server connection'' me

When using jdbc with IntegratedSecurity, I run into this problem when the machine is not part of a domain & gets its IP address via dhcp. Is this expected behavior or a bug in the jdbc driver.

The SQLServer and client application are installed on the same machine and a local admin is logged in, running the client app.

If I change one of the two parameters mentioned above, the connection can be established leading me to believe this may be intentional for security issues. Am I correct?

To clarify, "not part of domain" means you disconnected from the domain or you machine is part of workgroup? Do you mean that "IP address works but server name dosen't or otherwise around.|||That is correct. If I have the scenario where the machine is part of a workgroup and I am using dhcp, the connection gets refused. If I either add the machine to a domain or assign a static IP address to the NIC), the connection succeeds. So, what I meant by 'using dhcp' is that the NIC is configured to use dhcp in the TCP/IP settings of the Network Connection properties. I have not tried an environment where one NIC is dhcp and another is static.

Also, if I join a domain, the connection will succeed with the NIC configured for dhcp.

Hopefully that is clearer.

|||

To help us understand the problem and narrow down where the problem might be, please provide us with the SQL Server version and the JDBC driver version?

Please also provide the JDBC connection URL (minus any sensitive data).

Can you verify that another driver (say SNAC / MDAC) properly connects to SQL Server in the same configuration to make sure it is a JDBC driver issue versus some machine configuration issue.

If your SQL Server is SQL Server 2005, you can find the "sqlcmd.exe" tool in the "Microsoft SQL Server\90\Tools\Binn\" install directory. You can connect with Integrated Authentication to the local SQL Server using sqlcmd.exe with the following syntax: sqlcmd -Hlocalhost -E

Alternatively, if you have "osql.exe" you can perform the same test "osql -Hlocalhost -E". If you do not have access to either of these tools, you can also use a "Universal Data Link" file. Please see http://msdn2.microsoft.com/en-us/library/e38h511e(vs.71).aspx and http://support.microsoft.com/kb/274536 for additional information.

This will help narrow down if there is an issue with machine configuration or JDBC driver.

Jimmy

|||SQL Version: SQL Server 2005 Express Edition (tried both SP1 and SP2)
JDBC Version: 1.1

Since I am using SQL Server 2005, I did run both sqlcmd and osql and was able to execute the following statement

SELECT convert(varchar(15), SERVERPROPERTY('productversion')), convert(varchar(5), SERVERPROPERTY('productlevel')), convert(varchar(20), SERVERPROPERTY('edition'))

I got a row back with the version information I expected so it looks like I was able to connect. In addition, SQL Server Management Studio is able to run and connect with Windows Authentication.

Here is the code that I am trying to execute. The intention of this snippet it to connect to SQL Server so that I can create a database. The user running the application had admin rights.

Code Snippet

try
(
String address = "1.1.1.1";
String port = "1433";
String db_name = "MyAppDB";
Connection my_connection;
String url;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
url = "jdbc:sqlserver://" + address + ":" + port + ";integratedSecurity=true;";
my_connection = DriverManager.getConnection(url);

Statement create_stmt = conn.createStatement();
create_stmt.execute("CREATE DATABASE " + db_name);
}
catch (SQLException e)
{
//WriteErrorMsg("Error establishing JDBC connection (" + e.getErrorCode() + ") " + e.getMessage());
//return OPERATION_ERROR;
}
catch (ClassNotFoundException e)
{
//WriteErrorMsg("Class error creating " + db_name + ": " + e.getMessage());
//return EXCEPTION_CAUGHT;
}

Let me know if there is any other info I can get you.
Thanks for the help
john
|||

In the code snippet provided you are attempting to connect to an IP address of 1.1.1.1 and port 1433. If your machine has DHCP enabled, does it actually get 1.1.1.1 as the IP address?

If you only plan to run the client application on the same machine as SQL Server, then I would recommend either specifying "localhost" as the server name or the actual server name in the connection URL.

example: url = "jdbcqlserver://localhost:1433;integratedSecurity=true;"

Also, typically SQL Server Express is not installed to use the fixed port 1433, did you configure the instance to the fixed 1433 port?

If not, I would recommend enabling the SQL Browser service and update the connection URL to something like:

url = "jdbcqlserver://localhost;instanceName=SQLEXPRESS;integratedSecurity=true;"

HTH,

Jimmy

|||That was not the real address. The snippet is part of function where the address is passed in as a paramenter. It would either be set to an address(ie 10.47.20.46) or localhost. I just put that in as more of placeholder. Bad choice of documenting, sorry for any confusion. The SQL server may or may not be on the same machine. In the case where SQL is local on another machine, I would not use integrated security and provide credentials for SQL authentication.

Yes, SQL is configured to configured for port 1433 in this case.

thanks

JDBC for Sql Server Analysis Services 2005

Is there any way to access Sql Server Analysis Services 2005 via JDBC?I do not know of anyway of doing this with our JDBC driver.

JDBC Driver for Windows 2003 Server

I have a java application in Win2000 Server connecting to
MS SQL Server 2000 via the JDBC Driver Service Pack 2. I
upgraded my OS to Windows 2003 Server, but when i try to
connect to MS SQL Server via the same JDBC Driver, it
fails, i get error "Microsoft SQL Server 2000 Driver for
JDBC: Error establishing to socket".
What driver should i use to correct this? I dont see any
updated drivers available in microsoft.com.
thanks a lot!
tk wrote:

> I have a java application in Win2000 Server connecting to
> MS SQL Server 2000 via the JDBC Driver Service Pack 2. I
> upgraded my OS to Windows 2003 Server, but when i try to
> connect to MS SQL Server via the same JDBC Driver, it
> fails, i get error "Microsoft SQL Server 2000 Driver for
> JDBC: Error establishing to socket".
> What driver should i use to correct this? I dont see any
> updated drivers available in microsoft.com.
> thanks a lot!
Hi. It's not the driver. It's probably the DBMS not yet being configured
to listen in mixed-mode (listen for TCPIP connections). Got to setup and
check that.
Joe Weinstein at BEA
|||Hi,
I have a application in java using JDBC driver, but the connection is
correct and i have access to information.
In some cases a process of a user blocks the others, and until it is not
eliminated, they cannot continue the others. I would like to know as the
mixed-mode is configurated.
Thanks.
LuisJaimeG.
"Joe Weinstein" <joeNOSPAM@.bea.com> escribi en el mensaje
news:uzpgY9%23REHA.2876@.TK2MSFTNGP09.phx.gbl...
>
> tk wrote:
>
> Hi. It's not the driver. It's probably the DBMS not yet being configured
> to listen in mixed-mode (listen for TCPIP connections). Got to setup and
> check that.
> Joe Weinstein at BEA
>
|||solution please
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||| From: "Luis J." <ljgutierrez@.avansoft.com>
| References: <166b701c447c7$0cff79f0$a101280a@.phx.gbl>
<uzpgY9#REHA.2876@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: JDBC Driver for Windows 2003 Server
| Date: Mon, 14 Jun 2004 23:48:57 -0500
| Lines: 40
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <#fP4VPpUEHA.2844@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: 200.124.170.10
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP12
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6102
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi,
|
| I have a application in java using JDBC driver, but the connection is
| correct and i have access to information.
|
| In some cases a process of a user blocks the others, and until it is not
| eliminated, they cannot continue the others. I would like to know as the
| mixed-mode is configurated.
|
| Thanks.
|
| LuisJaimeG.
Hi Luis,
Mixed mode authentication only matters when you are making your connection
to SQL Server. Once you're authenticated and connected, it no longer
applies. You can troubleshoot the blocking problem using the SQL Server
blocking script in conjunction with SQL Profiler.
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
If you already know the SPID that is causing the blocking, you can simply
run "DBCC INPUTBUFFER(<spid>)" in Query Analyzer, where <spid> is your SPID
number. This will give you the SQL statement that is running at the time.
You can run "sp_lock" in Query Analyzer to see the specific locks that are
held by the blocking SPID. Once you identify which query is causing the
blocking, you can take steps to optimize the query (adding indexes,
recoding the query, etc). The faster the query executes, the less blocking
you will see. If the query is part of a transaction, you want to minimize
the size of your transactions to improve their speed and ultimately reduce
the blocking time.
Coding Efficient Transactions
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_md_06_3eba.asp
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.
|||tk wrote:
> I have a java application in Win2000 Server connecting to
> MS SQL Server 2000 via the JDBC Driver Service Pack 2. I
> upgraded my OS to Windows 2003 Server, but when i try to
> connect to MS SQL Server via the same JDBC Driver, it
> fails, i get error "Microsoft SQL Server 2000 Driver for
> JDBC: Error establishing to socket".
> What driver should i use to correct this? I dont see any
> updated drivers available in microsoft.com.
> thanks a lot!
Use netstat on the server to determine wether anyone is listening on the
SQL Server port.
You propably did not install SQL Server Service Pack 3.
On Windows 2003 Server TCP/IP is disabled for SQL Server without SP 3
due to security issues. Look at the eventlog, you will propably find a
message stating something in that direction.
Install SP 3, reboot and you should be fine.
Daniel Hagen
|||tk wrote:
> I have a java application in Win2000 Server connecting to
> MS SQL Server 2000 via the JDBC Driver Service Pack 2. I
> upgraded my OS to Windows 2003 Server, but when i try to
> connect to MS SQL Server via the same JDBC Driver, it
> fails, i get error "Microsoft SQL Server 2000 Driver for
> JDBC: Error establishing to socket".
> What driver should i use to correct this? I dont see any
> updated drivers available in microsoft.com.
> thanks a lot!
Use netstat on the server to determine wether anyone is listening on the
SQL Server port.
You propably did not install SQL Server Service Pack 3.
On Windows 2003 Server TCP/IP is disabled for SQL Server without SP 3
due to security issues. Look at the eventlog, you will propably find a
message stating something in that direction.
Install SP 3, reboot and you should be fine.
Daniel Hagen
|||I also upgraded from 2000 sever to 2003 server. My application was working fine with sql server until the upgrade. As a last resort I downloaded the Microsoft SQL Server 2000 Service Pack 3a and it did the trick. No more Error establishing socket connecti
on errors!
you can get the Service pack at:
http://www.microsoft.com/downloads/d...displaylang=en
I only installed the sql2ksp3.exe file.
Good Luck!
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.