JOBS

Hi All,
I am currently running a job that backs up a database using
the following code
BACKUP DATABASE [Leads] TO DISK = N'\\mymachine\d$\backup\leads.bak' WITH INIT , NOUNLOAD ,
NAME = N'Leads backup2', SKIP , STATS = 10, noFORMAT
this most of the time works fine but every now and again it
complete's fine but keeps the database locked open, is
there any way to fix this'
Thanks PhilWhat exactly do you mean by "Locked Open"?
--
Andrew J. Kelly SQL MVP
"Phil" <harlequintp@.blazemail.com> wrote in message
news:051801c3fabf$38fedc30$a101280a@.phx.gbl...
> Hi All,
> I am currently running a job that backs up a database using
> the following code
> BACKUP DATABASE [Leads] TO DISK => N'\\mymachine\d$\backup\leads.bak' WITH INIT , NOUNLOAD ,
> NAME = N'Leads backup2', SKIP , STATS = 10, noFORMAT
> this most of the time works fine but every now and again it
> complete's fine but keeps the database locked open, is
> there any way to fix this'
> Thanks Phil|||Sorry I should of been a little clearer, what I mean is
although the job says that it has completed, if you look
at the locks section in the SQL Enterprise Window, you
can still see a lock on the database showing the code
featured below.
Thanks again for your help!
Phil
>--Original Message--
>What exactly do you mean by "Locked Open"?
>--
>Andrew J. Kelly SQL MVP
>
>"Phil" <harlequintp@.blazemail.com> wrote in message
>news:051801c3fabf$38fedc30$a101280a@.phx.gbl...
>> Hi All,
>> I am currently running a job that backs up a database
using
>> the following code
>> BACKUP DATABASE [Leads] TO DISK =>> N'\\mymachine\d$\backup\leads.bak' WITH INIT ,
NOUNLOAD ,
>> NAME = N'Leads backup2', SKIP , STATS = 10,
noFORMAT
>> this most of the time works fine but every now and
again it
>> complete's fine but keeps the database locked open, is
>> there any way to fix this'
>> Thanks Phil
>
>.
>|||You have to be even clear than that<g>. What type of lock does it have?
Are these locks causing problems? It is normal for any connection to a db
to have at least a shared lock on the db. You also have to refresh EM in
order for most of the windows to show up to date information.
--
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:0bd801c3fb1f$b4c51a40$a401280a@.phx.gbl...
> Sorry I should of been a little clearer, what I mean is
> although the job says that it has completed, if you look
> at the locks section in the SQL Enterprise Window, you
> can still see a lock on the database showing the code
> featured below.
> Thanks again for your help!
> Phil
> >--Original Message--
> >What exactly do you mean by "Locked Open"?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Phil" <harlequintp@.blazemail.com> wrote in message
> >news:051801c3fabf$38fedc30$a101280a@.phx.gbl...
> >> Hi All,
> >>
> >> I am currently running a job that backs up a database
> using
> >> the following code
> >>
> >> BACKUP DATABASE [Leads] TO DISK => >> N'\\mymachine\d$\backup\leads.bak' WITH INIT ,
> NOUNLOAD ,
> >> NAME = N'Leads backup2', SKIP , STATS = 10,
> noFORMAT
> >>
> >> this most of the time works fine but every now and
> again it
> >> complete's fine but keeps the database locked open, is
> >> there any way to fix this'
> >>
> >> Thanks Phil
> >
> >
> >.
> >sql

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 script

Hi all,
I'm doing some scripts in a schedule job. Looks like I have some limitations
on the size of the code inside of the steps.
It's a way to fix this?
Tks
JFBPut the "scripts" into stored procedures, and call the stored procedures
from the job steps?
"JFB" <help@.jfb.com> wrote in message
news:OaGKFciFGHA.3588@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I'm doing some scripts in a schedule job. Looks like I have some
> limitations on the size of the code inside of the steps.
> It's a way to fix this?
> Tks
> JFB
>|||Why I didn't think about that? :)
Tks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23WbqzeiFGHA.216@.TK2MSFTNGP15.phx.gbl...
> Put the "scripts" into stored procedures, and call the stored procedures
> from the job steps?
>
> "JFB" <help@.jfb.com> wrote in message
> news:OaGKFciFGHA.3588@.TK2MSFTNGP10.phx.gbl...
>

Job NextRunDate using DMO always 0

Hi All,
I'm having a problem where whenever I add a new job (in code) with a
schedule to our server, the NextRunDate and NextRunTime properties are
always 0.
I have tried executing the sp_help_jobschedule stored procedure, but the
values returned are still 0.
I noticed that when i did a refresh on the job object itself when i was
using SQL Server 2000 SP4, the nextRunDate and time were calculated
correctly, however, we are not moving to SP4 and are staying on SP3a, so
that doesn't really help me.
Hope you can help.
Thanks,
BenHi
Just wondering if sp_help_job gives you the correct schedule?
John
"Ben Brearley" wrote:

> Hi All,
> I'm having a problem where whenever I add a new job (in code) with a
> schedule to our server, the NextRunDate and NextRunTime properties are
> always 0.
> I have tried executing the sp_help_jobschedule stored procedure, but the
> values returned are still 0.
> I noticed that when i did a refresh on the job object itself when i was
> using SQL Server 2000 SP4, the nextRunDate and time were calculated
> correctly, however, we are not moving to SP4 and are staying on SP3a, so
> that doesn't really help me.
> Hope you can help.
> Thanks,
> Ben
>
>|||Hi John,
Yes sp_help_job does give me the correct schedule.
I found out that the problem was fixed in SP4, and it involves Jobs not
starting unless they are owned by an admin user.
I tested this with a domain admin account, and the Next run dates and times
appear perfectly.
So until we go to the latest service pack, i guess this problem will be
unresolved, unless we calculate these Dates and times ourselves.
Ben
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:83BE8BBE-FC78-41B0-8B4C-CBF419ADF44C@.microsoft.com...
> Hi
> Just wondering if sp_help_job gives you the correct schedule?
> John
> "Ben Brearley" wrote:
>|||Hi Beni
If sp_help_job shows the correct shedule use profiler to see what it calls
before it runs sp_help_jobschedule or run sp_help_job and discard the result
before running sp_help_jobschedule.
John
"Ben Brearley" wrote:

> Hi John,
> Yes sp_help_job does give me the correct schedule.
> I found out that the problem was fixed in SP4, and it involves Jobs not
> starting unless they are owned by an admin user.
> I tested this with a domain admin account, and the Next run dates and time
s
> appear perfectly.
> So until we go to the latest service pack, i guess this problem will be
> unresolved, unless we calculate these Dates and times ourselves.
> Ben
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:83BE8BBE-FC78-41B0-8B4C-CBF419ADF44C@.microsoft.com...
>
>

JDBC with SQL Express

The java code :

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
"instance=SQLEXPRESS;databaseName=UPM;integratedSecurity=true;";
Connection con = DriverManager.getConnection(connectionUrl);

returns "connection refused" no matter what I do.

I've used the surface config tool to enable TCP, I've added sqlsrvr.exe to the firewall exceptions, I've tried a dozen or so variations on the connection string.

I can connect to the database using the Management Studio.

Any ideas what I'm missing ?

Thanks.

As you are using a trusted connection can your verify that you are using the correct user in both systems.

|||

I'm running into the same problem.

No problem connecting through VWD or SQL Management Studio...

The code:

String url =
"jdbc:sqlserver://localhost;" +
//"databaseName=TV;" +
"portNumber=1433;" +
"instanceName=./SQLEXPRESS;" +
"loginTimeout=0;" +
//"userName=Pierre;" +
//"serverName=PG" +
"integratedSecurity=true;";

con = DriverManager.getConnection( url );

Same bad result with all commented parameters.

The message:

The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect.

|||
This may be because by default MS SQL Server Express is configured to use dynamic TCP/IP ports, like named instances.

Go into the SQL SERVER CONFIGURATION MANAGER
open SQL SERVER 2005 Network Configuration
open Protocols for SQLEXPRESS
open TCP/IP properties
on the IP ADDRESSES tab, check at the bottom if "TCP Dynamic Ports" has a value.

if so, clear the value and leave that field blank. Then change "TCP Port" to 1433 or whatever port you decide.

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.