Another record locking issue...

Hi all,

I'm working on a Planning Databse for a factory that allows jobs to be allocated to machines and to be given a priority. This info. is displayed on an Access 2000 form e.g.

MACHINE ONE MACHINE TWO
Priority Job Number Priority Job Number
1 J111 1 J654
2 J562 2 J865
3 j231 3 J821

Both the Production Manager and the Factory Manager have this form open at all times. The problem is this, if for example the Factory Manager changes the priority of a job on his PC, the change is not shown on the Production Managers PC (cos' his screen has not been refreshed with update) The Production Manager therefore has old data on his screen and could mistakenly think that the job priority had not changed at all...
I have managed to write some VBA code to get round this but I was wondering if there was any way of 'pushing ' changes to the front-end when they are made or of using record-locking etc. to achieve the same affect...

Thanks
MThat situation shouldn't happen. However, review optimistic and pessimistic locking. Pessimisitic should definitely work if you're having problems with optimistic.|||Give them a refresh button?|||I have already implemented a refresh button but every now and again the users forget to press it and this causes problems. Also tried using the Access Timer Interval to refersh every n seconds but that caused other problems that I won't go into here.

Sorry I didn't reply sooner as I was off ill yesterday :-(|||The only way that I can see that would resolve your problem is implementing some kind of refresh interval on the front end, as the refresh button aproach apparently didn't work!

The problem with "forcing new entries" is basically that you actually retrieve data from the db, and thus it's kind of difficult to force a re-fetch from the db side...|||Listen, I can send you some monkeys that can click the refresh button for you...|||Hey guys,

I've just realised that i never mentioned the back-end is a SQL Server 2000 database!!! Basically, I just don't understand record locking well enough and will have to go and do some research...thanks for all your inpute.

Angry user #2

I am also angry and frustrated with SQL 2005 Management Studio - here are some of the problems I'm having ...

1. When I look at the jobs on the summary page and click details I see only the name and status of the job - With Enterprise Manager I can see the name, status, category, last run date and next run date. Where's the rest of the info?

2. Occasionally I get a blank summary page with nothing on it - don't ask me how I managed to do it. Without it, I could not select multiple objects in object explorer for scripting for example - and why is that the case?

3. Under Management Studio how do I get access to Integration services and reporting services? Or do I use Visual Studio for those? Or should I be using Visual Studio for writing and debugging stored procs? I thought it was all integrated under Management Studio - but is Management Studio just for querying SQL databases and Business Intelligence Studio for that and other data sources? I'm confused by all the tools.

4. I would like to find a good tutorial for using this interface - the books online tutorial is sparse - but I never felt that I really needed one with EM.

5. On the positive side I like the new interface for modifying individual SQL objects - it's groups of objects that are giving me the most trouble.

regards Richard

1. Limited information on the summary page.
We've heard this from several different sources. The "Job Steps Execution History" report provides some of the information you listed. Other information requires you to select properties for the particular job.

2. Blank summary page.
I haven't heard of this happening. Can you consistently repro this or is it totally hit or miss?

3. Access to SSIS, SSAS, and SSRS from within SSMS
Connections are server specific. From the Object Explorer click on Connect and the drop down will list the different server types (AS, RS, IS, etc) you can connect to. From here the connection dialog will be displayed and you can choose the physical server to connect to. BI Studio is for developing BI applications not for managing the servers.

4. Good tutorial for SSMS
An update to the tutorials will be posted shortly but I don't know if this will address your needs. I haven't done an inventory of third party tutorials, but I'm sure you can find some out on SQL Server sites like SQL Junkies.

5. Groups of objects are giving me the most trouble
Can you elaborate on this?

Cheers,
Dan

|||

Dan -

Thanks for the reply just came back from the holidays

1. Thanks I eventually figured this out - thats ok.

2. Cant repro this.

3. I eventually found the Registered Servers windows which helped me greatly with managing multiple servers.

4. OK.

5. On the groups of objects - this isn't that big of deal - it's just surprising that I cannot highlight groups of objects in the Object Explorer - I must use the summary page for that. Thanks R

|||

#1, please use job activity monitor node under SQLServer Agent node

# 2. I am not sure the sceanrio you are refering to. For eg. If you are selecting Alerts node and there are no alerts, then the summary page is expected to be empty.

# 3. You can connect to Inetgration services and Reporting services from SQL Server Management studio(SSMS). For editing the packages you will need to use SQL BIDS(Business Intelligence Development Studio). VS can be used for debugging, it is not integrated in SSMS.

# 4. BOL has all the answers to your questions you have posted. If there is some "area" that you are looking for does not have answer, please let us know.

Thanks,

Gops Dwarak

Angry user #2

I am also angry and frustrated with SQL 2005 Management Studio - here are some of the problems I'm having ...

1. When I look at the jobs on the summary page and click details I see only the name and status of the job - With Enterprise Manager I can see the name, status, category, last run date and next run date. Where's the rest of the info?

2. Occasionally I get a blank summary page with nothing on it - don't ask me how I managed to do it. Without it, I could not select multiple objects in object explorer for scripting for example - and why is that the case?

3. Under Management Studio how do I get access to Integration services and reporting services? Or do I use Visual Studio for those? Or should I be using Visual Studio for writing and debugging stored procs? I thought it was all integrated under Management Studio - but is Management Studio just for querying SQL databases and Business Intelligence Studio for that and other data sources? I'm confused by all the tools.

4. I would like to find a good tutorial for using this interface - the books online tutorial is sparse - but I never felt that I really needed one with EM.

5. On the positive side I like the new interface for modifying individual SQL objects - it's groups of objects that are giving me the most trouble.

regards Richard

1. Limited information on the summary page.
We've heard this from several different sources. The "Job Steps Execution History" report provides some of the information you listed. Other information requires you to select properties for the particular job.

2. Blank summary page.
I haven't heard of this happening. Can you consistently repro this or is it totally hit or miss?

3. Access to SSIS, SSAS, and SSRS from within SSMS
Connections are server specific. From the Object Explorer click on Connect and the drop down will list the different server types (AS, RS, IS, etc) you can connect to. From here the connection dialog will be displayed and you can choose the physical server to connect to. BI Studio is for developing BI applications not for managing the servers.

4. Good tutorial for SSMS
An update to the tutorials will be posted shortly but I don't know if this will address your needs. I haven't done an inventory of third party tutorials, but I'm sure you can find some out on SQL Server sites like SQL Junkies.

5. Groups of objects are giving me the most trouble
Can you elaborate on this?

Cheers,
Dan

|||

Dan -

Thanks for the reply just came back from the holidays

1. Thanks I eventually figured this out - thats ok.

2. Cant repro this.

3. I eventually found the Registered Servers windows which helped me greatly with managing multiple servers.

4. OK.

5. On the groups of objects - this isn't that big of deal - it's just surprising that I cannot highlight groups of objects in the Object Explorer - I must use the summary page for that. Thanks R

|||

#1, please use job activity monitor node under SQLServer Agent node

# 2. I am not sure the sceanrio you are refering to. For eg. If you are selecting Alerts node and there are no alerts, then the summary page is expected to be empty.

# 3. You can connect to Inetgration services and Reporting services from SQL Server Management studio(SSMS). For editing the packages you will need to use SQL BIDS(Business Intelligence Development Studio). VS can be used for debugging, it is not integrated in SSMS.

# 4. BOL has all the answers to your questions you have posted. If there is some "area" that you are looking for does not have answer, please let us know.

Thanks,

Gops Dwarak

Analyzing sysjobhistory

Has anyone written routines to analyze sysjobhistory? I'm looking for a tool/routine to analyze jobs for failures, trends (such as constantly increasing run times) and other information. It's not as straighforward as I had originally hoped it would be.

I am specifically prohibited from using any 3rd-party tools (too expensive per mgmt).

I would be grateful for any input/insight.

Regards,

hmscottIts advised not to query system tables directly when system supplied stored procedures are availble, have you looked into SP_HELP_JOBHISTRY and other corresponding SPs. Refer to books online for more information.|||Satya,

Yes, I had looked at that SP. The basic issue I had with it was that the Date and time stamps are configured as INTs and are not combined into an integrated date/time field.

Your reminder is a good one, however; I am nearly finished with working on a centralized repository for job execution history. I will look again and try to avoid querying the system tables directly.

Regards,

hmscott