Help on the last updated date

Hi,
I'm trying to retrieve data from the last updated date. For example, when
job runs after long holiday, it will look for the last updated date and
retrieve the data. how would I achieve this?
I tried Max(invoicedate), the query is always timed out.
Please help,
Thanks,
SarahSELECT TOP 1 columns FROM table ORDER BY invoicedate DESC
Do you have an index on invoicedate? If not, then on a large table I really
wouldn't be surprised to see a timeout in either case.
"SG" <sguo@.coopervision.ca> wrote in message
news:u3bACnb%23FHA.3496@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to retrieve data from the last updated date. For example, when
> job runs after long holiday, it will look for the last updated date and
> retrieve the data. how would I achieve this?
> I tried Max(invoicedate), the query is always timed out.
> Please help,
> Thanks,
> Sarah
>|||Hi Aaron,
Thanks for your quick response. Actually what I need to do is that I like to
have this max updated date to be a critiria to retrieve data from a few
tables, can I do this?
Thanks,
Sarah
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Vbyd9b%23FHA.1248@.TK2MSFTNGP14.phx.gbl...
> SELECT TOP 1 columns FROM table ORDER BY invoicedate DESC
> Do you have an index on invoicedate? If not, then on a large table I
> really wouldn't be surprised to see a timeout in either case.
>
> "SG" <sguo@.coopervision.ca> wrote in message
> news:u3bACnb%23FHA.3496@.TK2MSFTNGP15.phx.gbl...
>|||> Thanks for your quick response. Actually what I need to do is that I like
> to have this max updated date to be a critiria to retrieve data from a few
> tables, can I do this?
Probably, but without better requirements, I can't tell you how.
http://www.aspfaq.com/5006|||Hi Aaron,
I have a table has sales history, it will be updated every day. I like to
get last day's sales on my report. I used DATEADD(d, -1, GETDATE()), it
works when there is no long wend or holiday. But I like to get a solution
to refine this to look for the last updated date. For example: Last day
sales was on Dec 23, 2005, and when I come back from holiday on Dec 28,2005,
I like my program to run to retrieve the data of Dec 23, not Dec 27.
Select * from sales where invoicedate=DATE(d,-1,GETDATE()).
Can I do this?
Thanks,
Sarah
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OIFlDRc%23FHA.1484@.tk2msftngp13.phx.gbl...
> Probably, but without better requirements, I can't tell you how.
> http://www.aspfaq.com/5006
>|||SELECT * --specify your columns; avoid the use of * in production
FROM sales
WHERE invoicedate = (SELECT MAX(InvoiceDate) FROM sales)
Be careful, though. If you get one order on the last day, and all of
your other orders show up on the day before, you'll have an awful empty
report.
Stu

Help needed with trigger

Hi All,

I have this trigger wich runs good!

CREATE trigger trUpdateGEOData
on dbo.BK_Machine
after insert, update
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID,
BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine
inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
and BK_Machine.LOC_Doornumber >= GEO_Postcode.van
and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
inner join inserted on BK_Machine.MachineID = Inserted.MachineID

Now the thing is that a machine not neccesarily needs a location which mean that if postalcode or doornumber is NULL this trigger should clear the street and city columns.

Does someone have an Idea?

Cheers WimLOC_Street = case when insrted.PostalCode is Null or
inserted.DoorNumber is Null then '' else GEO_Postcode.STraatID end

Change your UPDATE Statment to include this. Do something similar with Loc_City.

Help Needed With Job Scheduling

Normally scheduling a job is a very elementary operation but for some
hidden reason I've been unable to schedule a job

which runs on a 28 day cycle, even though I have at least 16 other jobs
seperately scheduled to run on a 28 day cycle with

different start dates/times.

My production SQL server is running SQL Server Enterprise Edition
8.00.760(SP3) on a 2 Processor(4 virtual) Microsoft

Windows 2000 Server 5.0.2195

After creating my job through the New Job Wizard in Enterprise Manager,
which at this point is 43 steps, the first step of

which is a Transact-SQL Script, and the remaining 42 all Operating
System Command steps, I try to create the schedule by

clicking on the Schedules tab. I click the New Schedule command
button, type in a schedule name, keep the option button

schedule type default as Recurring, and then click the change command
button. In the Edit Recurring Job Schedule popup

screen I selected the Daily option button in the Occurs section, typed
in 28 in the Daily section so it reads Every 28

days, and in the Daily Frequency section set it to Occurs once at
10:00PM. In the Duration section set the start date to

5/23/2005 with No End Date. Clicked OK several times to complete the
job setup.

When I went to see the Job under the SQL Server Agent - Jobs node, it
shows next run date as todays date, regardless of

what day I set up the job. I learned the hard way that when I set this
job up with a (Future) start date of 3/28/2005 that

it not only ran on 3/28/2005, but it ran every day 10PM since then
until I noticed it and disabled the job. Since then I

have set up and scheduled several other jobs to run on a 28 day cycle
in a similar way, both through the DTS job scheduling

wizard and scheduling tab when creating new jobs, and they all ran on
the desired schedule.

I've been trying to manipulate this job schedule in various ways, even
deleting and recreating a new schedule for this job

through the GUI in Enterprise Manager and still encounter this problem.
Now when I set up this job with the (already past)

start date of 5/23/2005 I would expect it to indicate that the next run
date would be 6/20/2005 10PM, 28 days after

5/23/2005 10PM. The run duration of this job is between 7 and 8 hours
depending on processor load. But still it always

says the next run date is the day that I set up the schedule.

I've created one step dummy jobs in hopes that I can give it the
desired schedule and then use this schedule for the real

job, but I cannot even give this dummy job the desired schedule.

I compared this job schedule with the other 28-day jobs that run as
desired by first looking in the sysjobschedules table.

Aside from the different start date/times the only difference was that
the freq_relative_interval was set to 0 instead of 1

like all the others. Changing it to 1 through a query did not fix the
problem, so I tried deleting the old schedule with

sp_delete_jobschedule and creating a new schedule using
sp_add_jobschedule but got the same results. Next run date was

still today. I tried modifying an existing schedule with
sp_update_jobschedule so that freq_relative_interval in

sysjobschedules was correct, but still the next run date was today
10PM.

I noticed in the sp_update_jobschedule there is some code which is
commented " Notify SQLServerAgent of the change, but

only if we know the job has been cached", which checks if the job is
cached? and then calls sp_sqlagent_notify. I even

called sp_sqlagent_notify seperately from sp_update_jobschedule and the
job still shows today 10PM as the next run date.

The sp_update_jobschedule checks the sysjobservers table for this job,
so I deleted that row from the table and tried

rescheduling the job again through the various methods described above.
The row in sysjobservers was created by default

during this process, but the next run date of my job still shows todays
date (I hit refresh every time I've checked).

The only differences I can tell you between all the 28-day jobs that
work, and this one is that this job will run for 7-8

hours while others take minutes or seconds to run. Are there any other
system tables to check besides sysjobschedules,

sysjobs, or sysjobservers which are failing to be updated that are
causing my job schedule to be cached? Is it the Run

Duration of this job which does not allow it to be on the 28-day cycle?

I've exhausted all options. Any help would be greatly appreciated.

Thanks,

Mike Orlando
CAMP SystemsMike (mjorlando@.campsystems.com) writes:
> I've been trying to manipulate this job schedule in various ways, even
> deleting and recreating a new schedule for this job through the GUI in
> Enterprise Manager and still encounter this problem. Now when I set up
> this job with the (already past) start date of 5/23/2005 I would expect
> it to indicate that the next run date would be 6/20/2005 10PM, 28 days
> after 5/23/2005 10PM. The run duration of this job is between 7 and 8
> hours depending on processor load. But still it always says the next run
> date is the day that I set up the schedule.

An MVP colleague says that this is to be expected, when your date is
in the past, although I'm not really sure that I agree. When I test
to set up a job with a 28-day schedule, and with start date of
2005-05-23, I get a next run date of 2005-06-26, that is 28 days from
today. And the job does not run today.

It might be that it is not possible to use the start date, to specify
when the job should run the first time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Mike,

The short answer to your question is this: to create an
every-28-day schedule, specify when you want the job to run
first as the start date and start time in the wizard. This
must be in the future, since it is not possible to decide now
to run a job in the past.

Do not specify a start date and start time in the past, despite
your belief that this caused the problem you had back in
March. Nothing you report here is unexpected, except for
the problem back in March, for which you give few details. If
that problem occurs again, post another thread here, and include
the sysjobschedules row for that schedule (which I assume is now
unavailable for the March job that ran every day).

To elaborate on what Erland reported, based on a few more experiments,
it looks like a new job's next_run_date is determined as follows (at
least for daily recurring jobs). I may not have checked enough cases,
but it seems plausible, if very strange. Assume a 28-day recurrence
for this description.

1. If the specified start date is "tomorrow" or later, the
initial next_run_date is the specified start date. The
job again runs 28 days later, 56 days later, etc.

2. If the specified start_date is "today", the initial
next_run_date depends on the start_time.

2A. If the start_date is today, and the start_time has passed,
the initial next_run_date is 28 days from today, at the scheduled
start_time.
2B. If the start_date is today, and the start_time has not passed,
the initial next_run_date is today, at the scheduled start_time.

So far it's reasonable, but it gets weirder.

3. If the start_date is "yesterday" or earlier, the initial
next_run_date *still* depends on the start_time.

3A. If the start_date is yesterday or earlier, and the start_time
has passed on today's clock, the initial next_run_date is 28 days
from today, at the scheduled start_time.
3B. If the start_date is yesterday or earlier, and the start_time
has not passed on today's clock, the initial next_run_date is today,
at the scheduled start_time.

As far as I can tell, this is all consistent with what you have
found, except for one case in the past, where you think that choosing
a start date in the future caused a job to run daily when it should not
have. I don't know what happened then, but I believe that setting a
start date in the future is the correct way to schedule a job.

I think that an error should be raised if a job is added where the
first scheduled job run is in the past, because Agent cannot fulfill
such a request.

Given the behavior I observed, my guess is that when these routines
were programmed, no one paid much attention to the "correct" way to
start a job in the past, perhaps because it never occurred to the
programmers that someone would try to use SQL Server to change
history.

Steve Kass
Drew University

Mike wrote:

> Normally scheduling a job is a very elementary operation but for some
> hidden reason I've been unable to schedule a job
> which runs on a 28 day cycle, even though I have at least 16 other jobs
> seperately scheduled to run on a 28 day cycle with
> different start dates/times.
> My production SQL server is running SQL Server Enterprise Edition
> 8.00.760(SP3) on a 2 Processor(4 virtual) Microsoft
> Windows 2000 Server 5.0.2195
> After creating my job through the New Job Wizard in Enterprise Manager,
> which at this point is 43 steps, the first step of
> which is a Transact-SQL Script, and the remaining 42 all Operating
> System Command steps, I try to create the schedule by
> clicking on the Schedules tab. I click the New Schedule command
> button, type in a schedule name, keep the option button
> schedule type default as Recurring, and then click the change command
> button. In the Edit Recurring Job Schedule popup
> screen I selected the Daily option button in the Occurs section, typed
> in 28 in the Daily section so it reads Every 28
> days, and in the Daily Frequency section set it to Occurs once at
> 10:00PM. In the Duration section set the start date to
> 5/23/2005 with No End Date. Clicked OK several times to complete the
> job setup.
> When I went to see the Job under the SQL Server Agent - Jobs node, it
> shows next run date as todays date, regardless of
> what day I set up the job. I learned the hard way that when I set this
> job up with a (Future) start date of 3/28/2005 that
> it not only ran on 3/28/2005, but it ran every day 10PM since then
> until I noticed it and disabled the job. Since then I
> have set up and scheduled several other jobs to run on a 28 day cycle
> in a similar way, both through the DTS job scheduling
> wizard and scheduling tab when creating new jobs, and they all ran on
> the desired schedule.
> I've been trying to manipulate this job schedule in various ways, even
> deleting and recreating a new schedule for this job
> through the GUI in Enterprise Manager and still encounter this problem.
> Now when I set up this job with the (already past)
> start date of 5/23/2005 I would expect it to indicate that the next run
> date would be 6/20/2005 10PM, 28 days after
> 5/23/2005 10PM. The run duration of this job is between 7 and 8 hours
> depending on processor load. But still it always
> says the next run date is the day that I set up the schedule.
> I've created one step dummy jobs in hopes that I can give it the
> desired schedule and then use this schedule for the real
> job, but I cannot even give this dummy job the desired schedule.
> I compared this job schedule with the other 28-day jobs that run as
> desired by first looking in the sysjobschedules table.
> Aside from the different start date/times the only difference was that
> the freq_relative_interval was set to 0 instead of 1
> like all the others. Changing it to 1 through a query did not fix the
> problem, so I tried deleting the old schedule with
> sp_delete_jobschedule and creating a new schedule using
> sp_add_jobschedule but got the same results. Next run date was
> still today. I tried modifying an existing schedule with
> sp_update_jobschedule so that freq_relative_interval in
> sysjobschedules was correct, but still the next run date was today
> 10PM.
> I noticed in the sp_update_jobschedule there is some code which is
> commented " Notify SQLServerAgent of the change, but
> only if we know the job has been cached", which checks if the job is
> cached? and then calls sp_sqlagent_notify. I even
> called sp_sqlagent_notify seperately from sp_update_jobschedule and the
> job still shows today 10PM as the next run date.
> The sp_update_jobschedule checks the sysjobservers table for this job,
> so I deleted that row from the table and tried
> rescheduling the job again through the various methods described above.
> The row in sysjobservers was created by default
> during this process, but the next run date of my job still shows todays
> date (I hit refresh every time I've checked).
> The only differences I can tell you between all the 28-day jobs that
> work, and this one is that this job will run for 7-8
> hours while others take minutes or seconds to run. Are there any other
> system tables to check besides sysjobschedules,
> sysjobs, or sysjobservers which are failing to be updated that are
> causing my job schedule to be cached? Is it the Run
> Duration of this job which does not allow it to be on the 28-day cycle?
> I've exhausted all options. Any help would be greatly appreciated.
> Thanks,
> Mike Orlando
> CAMP Systems|||Steve Kass (skass@.drew.edu) writes:
> Given the behavior I observed, my guess is that when these routines
> were programmed, no one paid much attention to the "correct" way to
> start a job in the past, perhaps because it never occurred to the
> programmers that someone would try to use SQL Server to change
> history.

Yet, it's simply a matter of interface. I have this job that is to
run every 28th day, and on the 23rd I ran it manually. Now I want SQL
Agent to run it for me in the future. Since I am lazy, I want SQL Agent
to do the job for me. After all, isn't that why we have computers?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your responses Erland and Steve. I used some of your ideas
and did a few more tests of scheduling my dummy job by changing

the existing schedule (328).

Scenario 1:

Date: 6/2/2005
Time: 9:24AM

Changed schedule 328 to run every 28 days at 9:00AM starting 5/23/2005.
The job shows next run date as 6/30/2005 at 9:00AM. Thats 28

days from today, 6/2/2005, the day I edited the schedule.

The following is the row in sysjobschedules:

schedule_id job_id name

enabled freq_type
freq_interval freq_subday_type freq_subday_interval

freq_relative_interval freq_recurrence_factor active_start_date
active_end_date active_start_time active_end_time next_run_date

next_run_time date_created

328C2FA68BE-E6A8-43CA-A427-B9ADDEF2D8FDSchedName1428100020050523

9999123190000235959002005-05-27 10:43:54.763

The next_run_date and next_run_time are 0.

Scenario 2:

Date: 6/2/2005
Time: 9:45AM

Changed schedule 328 by making start time 10:00AM, and keeping start
date 5/23/2005. Now the schedule says it will run next today

6/2/2005 at 10AM.

The following is the row in sysjobschedules:

schedule_id job_id name

enabled freq_type
freq_interval freq_subday_type freq_subday_interval

freq_relative_interval freq_recurrence_factor active_start_date
active_end_date active_start_time active_end_time next_run_date

next_run_time date_created

328C2FA68BE-E6A8-43CA-A427-B9ADDEF2D8FDSchedName1428100020050523

99991231100000235959002005-05-27 10:43:54.763

The next_run_date and next_run_time are still 0. I let the dummy job
run to see what the next run date would be after it runs and it

shows me it will be: 6/30/2005 10:00AM.

After this job ran the row in sysjobschedules is as follows:

328C2FA68BE-E6A8-43CA-A427-B9ADDEF2D8FDSchedName1428100020050523

99991231100000235959200506301000002005-05-27 10:43:54.763

The next_run_date and next_run_time are 20050630 and 100000
respectively.

Scenario 3:

Date: 6/2/2005
Time: 10:45AM

Changed schedule 328 by making start time 11:00AM, and start date
6/3/2005. Now the schedule says it will run next 6/3/2005 at 11:00AM.

The following is the row in sysjobschedules:

schedule_id job_id name

enabled freq_type
freq_interval freq_subday_type freq_subday_interval

freq_relative_interval freq_recurrence_factor active_start_date
active_end_date active_start_time active_end_time next_run_date

next_run_time date_created

328C2FA68BE-E6A8-43CA-A427-B9ADDEF2D8FDSchedName1428100020050603

99991231110000235959002005-05-27 10:43:54.763

The next_run_date and next_run_time are 0. The job ran as desired on
6/3/2005 11:00AM. The job has a next run date of 7/1/2005 11:00AM,

exactly what was desired.

Scenario 4:

Date: 6/3/2005
Time: 11:59AM

Changed schedule 328 by making start time 11:00PM, and start date
6/3/2005. Now the schedule says it will run next 6/3/2005 at 11:00PM.

The following is the row in sysjobschedules:

schedule_id job_id name

enabled freq_type
freq_interval freq_subday_type freq_subday_interval

freq_relative_interval freq_recurrence_factor active_start_date
active_end_date active_start_time active_end_time next_run_date

next_run_time date_created

328C2FA68BE-E6A8-43CA-A427-B9ADDEF2D8FDSchedName1428100020050603

99991231230000235959200506032300002005-05-27 10:43:54.763

The job ran as desired. The next run date is 7/1/2005 11:00PM, exactly
what is desired.

So I will wait until June 20, 2005 after noon and schedule my real job
to run every 28 days starting on June 20, 2005 at 10:00PM.

Judging from these recent tests I expect that my 28 day schedule will
execute as desired. I will let you know if I still have a problem.

Thanks,

Mike|||Mike (mjorlando@.campsystems.com) writes:
> Judging from these recent tests I expect that my 28 day schedule will
> execute as desired. I will let you know if I still have a problem.

Great to hear that you believe have things under control. And big
thanks for reporting back!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Scenario 5 (STILL HAVING THE SAME PROBLEM):

Date: June 20, 2005
Time: 12:30PM

Deleted schedule 328 from my job and created a new 28-day schedule
starting tonight 6/20/2005 at 10:00PM.

The following is the row in sysjobschedules:

schedule_id job_id name

enabled freq_type freq_interval
freq_subday_type freq_subday_interval freq_relative_interval
freq_recurrence_factor active_start_date active_end_date
active_start_time active_end_time next_run_date next_run_time
date_created
---- -----------
---------------------------------------
---- ---- ---- ------
------- ------- -------
------ ----- ------ -----
---- ----
----------------
329 D16909F8-6C6D-4567-BEE4-535440999A4E TwentyEight

1 4 28
1 0 0 0
20050620 99991231 220000 235959
0 0 2005-06-20 13:41:07.653

The freq_relative_interval was set to 0 as opposed to the other 28 day
jobs which are set to 1. The next_run_date and next_run_time were
also set to 0 respectively.

The job ran as desired however the next run date was NOT 28 days from
June 20, 2005 10:00PM. The next run date is June 21, 2005 at 10PM.
Back to the same problem, but this time I have a before and after row
in sysjobschedules. After the job ran the row in sysjobschedules looks
like this:

schedule_id job_id name

enabled freq_type freq_interval
freq_subday_type freq_subday_interval freq_relative_interval
freq_recurrence_factor active_start_date active_end_date
active_start_time active_end_time next_run_date next_run_time
date_created
---- -----------
---------------------------------------
---- ---- ---- ------
------- ------- -------
------ ----- ------ -----
---- ----
----------------
329 D16909F8-6C6D-4567-BEE4-535440999A4E TwentyEight

1 4 28
1 0 0 0
20050620 99991231 220000 235959
20050621 220000 2005-06-20 13:41:07.653

I thought I had this problem figured out by creating the schedule after
noon on the day it was supposed to run. Appearently this job still
wants to run every day. This is very frustrating since I waited almost
a month to schedule this job at a specific time so that the schedule
works on a 28-day interval. What the @.##%^#@. is wrong with SQL Server?
Are there any patches that can be run to fix this?|||Mike (mjorlando@.campsystems.com) writes:
> Scenario 5 (STILL HAVING THE SAME PROBLEM):
> Date: June 20, 2005
> Time: 12:30PM
> Deleted schedule 328 from my job and created a new 28-day schedule
> starting tonight 6/20/2005 at 10:00PM.
>...
> The job ran as desired however the next run date was NOT 28 days from
> June 20, 2005 10:00PM. The next run date is June 21, 2005 at 10PM.
> Back to the same problem, but this time I have a before and after row
> in sysjobschedules. After the job ran the row in sysjobschedules looks
> like this:
>...
> I thought I had this problem figured out by creating the schedule after
> noon on the day it was supposed to run. Appearently this job still
> wants to run every day. This is very frustrating since I waited almost
> a month to schedule this job at a specific time so that the schedule
> works on a 28-day interval. What the @.##%^#@. is wrong with SQL Server?
> Are there any patches that can be run to fix this?

Well, the first step to find a patch is that the problem can be recreated.
This far I have not been successful. Some 45 minutes ago I created a job
to run every 28th day at 23:15 starting today. The job ran, and is now
scheduled to run 2005-07-19 next time.

But I recalled that your job was a long-running job, so I've now scheduled
a job that will start at 23:45 and run for six hours. We'll see tomorrow
when this will be scheduled to run the next time.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> But I recalled that your job was a long-running job, so I've now scheduled
> a job that will start at 23:45 and run for six hours. We'll see tomorrow
> when this will be scheduled to run the next time.

That will be tonight at 23:45. Which is incorrect. So now at least I have
something I can bring up with Microsoft.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I represent a company called Vinzant, Inc. We develop and market a
product called Global ECS which is a job scheduling and batch
processing solution for Windows, Linux, most flavors of Unix, AS/400
and MPE/ix. We can schedule your tasks very easily to run in your SQL
Server environment. Check us out at http://www.globalecs.com . Feel
free to call us at 800.355.3443 and we can set up a live demo to show
your our solution. You can also download a free evaluation version of
Global ECS so that you may try it in your environment.

Thanks!

Scott
smockler@.vinsoft.com
219.942.9544|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Erland Sommarskog (esquel@.sommarskog.se) writes:
>> But I recalled that your job was a long-running job, so I've now
scheduled
>> a job that will start at 23:45 and run for six hours. We'll see tomorrow
>> when this will be scheduled to run the next time.
> That will be tonight at 23:45. Which is incorrect. So now at least I have
> something I can bring up with Microsoft.

I've now tested the case on SQL 2005. The problem exists there as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, I think you are on the right track. I think it has to do with
the job ending on a different day than the start date. All my other
28-day jobs ran as desired this month and they ended on the same day as
the start date.|||Mike (mjorlando@.campsystems.com) writes:
> Erland, I think you are on the right track. I think it has to do with
> the job ending on a different day than the start date. All my other
> 28-day jobs ran as desired this month and they ended on the same day as
> the start date.

So that brings us to a possible workaround. Create your job, but don't
schedule it. Instead schedule another job, which has the sole duty to
start the real job.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Simplicity is genius! Thank you, I'll let you know if there are any
problems.

help needed on merge replication

i have a merge replication. it runs well, the subscriber disconnect from the
publisher about 5 days, then i reconnect it to publisher. theoretically
speaking, the changes made during these 5 days made on both publisher and
subscriber will merge into one copy. but customer complain about data missing
on the subscriber. I run Red Gate data compare on publisher and subscriber
database, and find there are different records, missing records and
additional records. Any one know why this happened, and what is the way to
synchronize?
Thanks a lot.
Has the merge agent been run after the 5 days of disconnect?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"green hand" <green hand@.discussions.microsoft.com> wrote in message
news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>i have a merge replication. it runs well, the subscriber disconnect from
>the
> publisher about 5 days, then i reconnect it to publisher. theoretically
> speaking, the changes made during these 5 days made on both publisher and
> subscriber will merge into one copy. but customer complain about data
> missing
> on the subscriber. I run Red Gate data compare on publisher and subscriber
> database, and find there are different records, missing records and
> additional records. Any one know why this happened, and what is the way to
> synchronize?
> Thanks a lot.
|||Thanks Mahesh.
The problem is the merge agent was restarted after the 5 days of
discounnect. And has been alway connected till now.
"Mahesh [MSFT]" wrote:

> Has the merge agent been run after the 5 days of disconnect?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "green hand" <green hand@.discussions.microsoft.com> wrote in message
> news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>
>
|||Is the merge agent failing or succeeding?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:A0E71256-C581-45A3-A888-8E181D4D6A7E@.microsoft.com...[vbcol=seagreen]
> Thanks Mahesh.
> The problem is the merge agent was restarted after the 5 days of
> discounnect. And has been alway connected till now.
> "Mahesh [MSFT]" wrote:
|||Check to see if there are conflicts in the conflict viewer. Also check to
see if you have compenstate_for_errors set to false. This setting will not
resolve some conflicts, but rather leave dml which originates on the
conflict loser in place. For example a pk violation will not be resolved
with the winning publisher's pk value, but the subscriber's one.
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
"green hand" <green hand@.discussions.microsoft.com> wrote in message
news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>i have a merge replication. it runs well, the subscriber disconnect from
>the
> publisher about 5 days, then i reconnect it to publisher. theoretically
> speaking, the changes made during these 5 days made on both publisher and
> subscriber will merge into one copy. but customer complain about data
> missing
> on the subscriber. I run Red Gate data compare on publisher and subscriber
> database, and find there are different records, missing records and
> additional records. Any one know why this happened, and what is the way to
> synchronize?
> Thanks a lot.
|||Thanks Hilary,
I checked the conflict viewer, there is a conflict and the publish win. Does
these explain the data inconsistence between publisher and discriber? I
failed to find where compenstate_for_errors is, for I am an new DBA, could u
give me some hint? should I set it to false or true?
"Hilary Cotter" wrote:

> Check to see if there are conflicts in the conflict viewer. Also check to
> see if you have compenstate_for_errors set to false. This setting will not
> resolve some conflicts, but rather leave dml which originates on the
> conflict loser in place. For example a pk violation will not be resolved
> with the winning publisher's pk value, but the subscriber's one.
> --
> 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
>
> "green hand" <green hand@.discussions.microsoft.com> wrote in message
> news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>
>
|||It might. If compenstate for errors is set to true all databases will be
consistent. If not they won't. The compenstate for errors parameter is part
of sp_addmergearticle. Use sp_helpmergearticle to verify if this is set to
true or false.
Also consult http://support.microsoft.com/kb/828637 for more info.
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
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:8CB5F530-B61D-404B-AC8B-3888509B13CE@.microsoft.com...[vbcol=seagreen]
> Thanks Hilary,
> I checked the conflict viewer, there is a conflict and the publish win.
> Does
> these explain the data inconsistence between publisher and discriber? I
> failed to find where compenstate_for_errors is, for I am an new DBA, could
> u
> give me some hint? should I set it to false or true?
> "Hilary Cotter" wrote:
|||Hilary,
Thanks very much for help!
I found compensate_for_errors in a property of sp4. currently the publisher
and two subscribes are all under sp3. It seams the only way is to apply sp4
on all the 3 box. and then set it to ture. Am I right?
Thanks
"Hilary Cotter" wrote:

> It might. If compenstate for errors is set to true all databases will be
> consistent. If not they won't. The compenstate for errors parameter is part
> of sp_addmergearticle. Use sp_helpmergearticle to verify if this is set to
> true or false.
> Also consult http://support.microsoft.com/kb/828637 for more info.
> --
> 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
>
> "green hand" <greenhand@.discussions.microsoft.com> wrote in message
> news:8CB5F530-B61D-404B-AC8B-3888509B13CE@.microsoft.com...
>
>
|||That is correct.
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
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:A5E1B1DF-36A1-43FD-8F7A-87B46CB2E66A@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thanks very much for help!
> I found compensate_for_errors in a property of sp4. currently the
> publisher
> and two subscribes are all under sp3. It seams the only way is to apply
> sp4
> on all the 3 box. and then set it to ture. Am I right?
> Thanks
> "Hilary Cotter" wrote:

help me Please!

Hi There!!

I have a code that runs from a different machine and a different site and i am facing a problem that i can not log in to the database.I installed SQL Server on my machine and I created a user "fadila" and SQL Server Authentication and the password is "fadil1977" and the database is "otters" and it is installed as tables and stored procedures but there is not data on these tables.

I used the method provided in the code so, i only change the "connStr" in one place rather than in 20 places and my code is as below. Can you please Help me to connect to the database. I Really..Really appreciate it if you help me to solve it as it causeing me a big head-ache and still get the error message "SQL Server does not exist or access denied" .. please help!!!!

 Friend Shared ReadOnly Property connStr() As String
 Get
 Return String.Format( _
 "Data Source={0};Initial Catalog=Otters;User ID=fadila;Password=fadil1977", _
 DatabaseMachine)
 End Get
 End Property

your help is appreciated!!!!!!Sure you can connect to the sql server?

Possible reasons:

* Firewall in between.
* Stupid admin. One who did not read that syou need a special service pack (SP3?) to run on 2003, and Winwodows Server 2003 actively blocks SQL Server ports unless this sp is installewd (at least).
* Bad setup for the network, not allowing name resolution.
* Bad database machine name?

Do you homework - there is nothing we can do here to help you, simply because we have no clue what the error is, simply because you do not provide enough info.|||Hi Thona!!

Thank you very much for your help and answering my question. You just said there is no enough information provided, can you please tell me what information is needed so i can post up for you. I just want to make one point, I used before in the previous project the dataset and connect to the database without any problem so, is that what you told me, it does not apply to the dataset and SqlAdaptor as it is a different way toconnect so, i will not have this problem.

I will speak to the IT admin about the you told me and get back to you so, please keep looking to this thread until you solve it for me or you get fed up :)

Thanks again

Help me get MSDE SP3a installed on Win Server 2003

I've tried every which way I can from the docs and but I can't seem to get MSDE to install correctly. Found out that Win2003 only runs SP3a, downloaded and tried it.
tried these different commands from the prompt:
setup SAPWD="xxxxx" INSTANCENAME="xxxxx" TARGETDIR="xxxx" (from MSDE2000 only)
setup /upgradesp sqlrun INSTANCENAME="xxxxx" DISABLENETWORKPROTOCOLS=1 (after installing MSDE2000 first)
setup INSTANCENAME="xxxxx" SAPWD="xxxxx" (straight from MSDE2000 SP3a)
Services shows that the SQL agent and the MSSQL$xxxx running. MSSQLServerADHelper is not running. The Service Manager isn't showing any services in the tray nor does it have the little green arrow. I can't connect to anything with Enterprise Manager eithe
r.
What do I need to do? Please note, I'm a totally complete noob with SQL admin and set up...
Thanks...
PS: and note that I'm in the correct directory with the installer and the xxxx are the correct instances and pw's
addition: This is Win 2003 Server Standard Edition...