Job Execution Information Not Written To System Tables
Jul 3, 2007
SS 2005 64Bit SP2
Hello Chaps
Intermittent problem with the SQL Agent job history not getting written to the history table. Background:
Today we noticed the account SQL Agent runs under cropping up in sp_who2. A quick check of the activity monitor said nothing was running. We ran a trace and, based on the SQL being executed, had a word with one of the developers who confirmed they had manually executed one of the jobs.
There was no record anywhere that the job had run. There has been an issue with this particular job, when executed by this user, not showing up in history before but, as mentioned, this had been intermittent and we thought that a restart of the service had sorted it.
Stuff run to try to track the job:
EXEC sp_help_jobactivity @job_name = 'MyJob'
EXEC sp_help_jobhistory @job_name = 'MyJob'
SELECT *
FROM dbo.sysjobhistory
WHERE job_id = 'MyJob GUID'
The first returned a row with no details in the columns indicating activity (e.g. last_executed_step_date and other columns were null).
sp_help_jobhistory had some historical records but nothing since mid last month.
sysjobhistory correlated with sp_help_jobhistory as you would expect.
Right clicking the job in SSMS and viewing history correlated with sp_help_jobhistory (i.e. some records but nothing since mid-June).
We edited the SQL in the job step and got the developer to rerun the job and, typically, everything appeared as it should in all the above result sets.
Obviously this is tricky to track down since it has been intermittent but does anyone recognise anything that I have described above? I have of course googled but there doesn't really seem to be anything about it.
We have considered there may be a problem in MSDB and may try running CHECKDB to see if anything comes up but somehow I doubt it will.
ok. the problem: some tables are empty, so i can't be sure why some are updating at the DB and some arent. I have manually picked thru every line of the xml that i'm reading into the dataset here, and it is fine, data is all valid and everything. the tables i'm most worried about are bulletins and surveys, but they all have to be imported from my upload, the code steps thru just fine and I've been thru it a million times. Did I miss something in my dataadapter configuration?. 'daBulletin ' Me.daBulletin.ContinueUpdateOnError = True Me.daBulletin.DeleteCommand = Me.SqlDeleteCommand17 Me.daBulletin.InsertCommand = Me.SqlInsertCommand17 Me.daBulletin.SelectCommand = Me.SqlSelectCommand25 Me.daBulletin.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "tblBulletin", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("BulletinID", "BulletinID"), New System.Data.Common.DataColumnMapping("ContractID", "ContractID"), New System.Data.Common.DataColumnMapping("Msg_Type", "Msg_Type"), New System.Data.Common.DataColumnMapping("DatePosted", "DatePosted"), New System.Data.Common.DataColumnMapping("Subject", "Subject"), New System.Data.Common.DataColumnMapping("B_Body", "B_Body"), New System.Data.Common.DataColumnMapping("I_Read_It", "I_Read_It"), New System.Data.Common.DataColumnMapping("DateRead", "DateRead")})}) Me.daBulletin.UpdateCommand = Me.SqlUpdateCommand16 here is my merge function: Private Function Merge(ByVal sFilename As String, ByVal User As String) As String Dim connMerge As New SqlConnection(ConnectionString) Dim dsNew As New dsBeetleTracks Dim dsExisting As New dsBeetleTracks Dim strResult As String SetConnections(connMerge) Dim idc As New System.Security.Principal.GenericIdentity(User) Dim currentUser As BeetleUser = bu
dsNew.ReadXml(sFilename)
If currentUser.IsInRole("Admin") Or currentUser.IsInRole("QA") Then If dsNew.tblBulletin.Count > 0 Then daBulletin.Fill(dsExisting.tblBulletin) dsExisting.Merge(dsNew.tblBulletin) strResult += daHelipads.Update(dsExisting.tblBulletin).ToString + " Bulletins updated<br>" End If End If
If dsNew.tblHours.Count > 0 And (currentUser.IsInRole("Survey") Or currentUser.IsInRole("Admin")) Then daHours.Fill(dsExisting.tblHours) dsExisting.Merge(dsNew.tblHours)
strResult += daHours.Update(dsExisting.tblHours).ToString + " hours updated<br>" End If
If dsNew.tblHeliPads.Count > 0 Then daHelipads.Fill(dsExisting.tblHeliPads) dsExisting.Merge(dsNew.tblHeliPads) strResult += daHelipads.Update(dsExisting.tblHeliPads).ToString & " helipads updated " End If
If dsNew.tblExpenses.Count > 0 Then daExpenses.Fill(dsExisting.tblExpenses) dsExisting.Merge(dsNew.tblExpenses)
strResult += daExpenses.Update(dsExisting.tblExpenses).ToString + " expenses updated<br>" End If
If dsNew.tblPersons.Count > 0 And (currentUser.IsInRole("Survey") Or currentUser.IsInRole("FB") Or currentUser.IsInRole("Heli-burn")) Then daPersons.Fill(dsExisting.tblPersons) dsExisting.Merge(dsNew.tblPersons) strResult += daPersons.Update(dsExisting.tblPersons).ToString + " persons updated<br>" End If
If currentUser.IsInRole("Field") Then daSurveys.SelectCommand.CommandText = "exec Surveys_Field_Select" daSurveys.InsertCommand.CommandText = "exec Surveys_Field_Insert" daSurveys.UpdateCommand.CommandText = "exec Surveys_Field_Update" End If
If dsNew.tblSurveys.Count > 0 And (currentUser.IsInRole("Survey") Or currentUser.IsInRole("Field")) Then ' Or CurrentUser.IsInRole("Admin")) Then
strResult += daSurveys.Update(dsExisting.tblSurveys).ToString + " surveys updated<br>" End If
If dsNew.tblSurveyChecks.Count > 0 And (currentUser.IsInRole("QA") Or currentUser.IsInRole("Admin")) Then daSurveyChecks.Fill(dsExisting.tblSurveyChecks) dsExisting.Merge(dsNew.tblSurveyChecks)
strResult += daSurveyChecks.Update(dsExisting.tblSurveyChecks).ToString + " survey checks updated<br>" End If
If dsNew.tblTreatments.Count > 0 And (currentUser.IsInRole("FB") Or currentUser.IsInRole("Heli-burn")) Then ' Or CurrentUser.IsInRole("Admin")) Then daTreatments.Fill(dsExisting.tblTreatments) dsExisting.Merge(dsNew.tblTreatments)
strResult += daTreatments.Update(dsExisting.tblTreatments).ToString + " treatments updated<br>" End If
If dsNew.tblInternalQC.Count > 0 And (currentUser.IsInRole("FB") Or currentUser.IsInRole("Heli-burn") Or currentUser.IsInRole("Survey")) Then ' Or CurrentUser.IsInRole("Admin")) Then daInternalQC.Fill(dsExisting.tblInternalQC) dsExisting.Merge(dsNew.tblInternalQC)
strResult += daInternalQC.Update(dsExisting.tblInternalQC).ToString + " internalqc updated<br>" End If
If dsNew.tblTreatmentChecks.Count > 0 And (currentUser.IsInRole("QA") Or currentUser.IsInRole("Admin")) Then Try daTreatmentChecks.Fill(dsExisting.tblTreatmentChecks) dsExisting.Merge(dsNew.tblTreatmentChecks) strResult += daTreatmentChecks.Update(dsExisting.tblTreatmentChecks).ToString + " treatment checks updated<br>" Catch dbex As DBConcurrencyException strResult += vbCrLf & dbex.Message For x As Integer = 0 To dbex.Row.Table.Columns.Count - 1 strResult += vbCrLf & dbex.Row.GetColumnError(x) Next End Try End If
If dsNew.tblHeliPiles.Count > 0 And (currentUser.IsInRole("Heli-burn")) Then ' Or CurrentUser.IsInRole("Planner")CurrentUser.IsInRole("QA") Or CurrentUser.IsInRole("Admin") Or daHeliPiles.Fill(dsExisting.tblHeliPiles) dsExisting.Merge(dsNew.tblHeliPiles)
strResult += daHeliPiles.Update(dsExisting.tblHeliPiles).ToString + " piles updated<br>" End If
If dsNew.tblHeliCycles.Count > 0 And (currentUser.IsInRole("Heli-burn")) Then ' CurrentUser.IsInRole("Planner") Or Or CurrentUser.IsInRole("Admin")) Then daHeliCycles.Fill(dsExisting.tblHeliCycles) dsExisting.Merge(dsNew.tblHeliCycles)
strResult += daHeliCycles.Update(dsExisting.tblHeliCycles).ToString + " cycles updated<br>" End If
If dsNew.tblHeliTurns.Count > 0 And (currentUser.IsInRole("Heli-burn")) Then 'CurrentUser.IsInRole("Admin") Or CurrentUser.IsInRole("Planner") Or daHeliTurns.Fill(dsExisting.tblHeliTurns) dsExisting.Merge(dsNew.tblHeliTurns)
strResult += daHeliTurns.Update(dsExisting.tblHeliTurns).ToString + " turns updated<br>" End If
If dsExisting.HasChanges Then dsExisting.Merge(dsNew) End If dsExisting.AcceptChanges()
'duh. 'If dsNew.HasChanges Then ' dsNew.AcceptChanges() 'End If If dsExisting.HasErrors Then Dim bolError As Boolean Dim tempDataTable As DataTable bolError = True
strResult += "<br>"
For Each tempDataTable In dsExisting.Tables If (tempDataTable.HasErrors) Then strResult += PrintRowErrs(tempDataTable) End If Next End If
dsNew.Dispose() dsExisting.Dispose() connMerge.Close() 'edebugging will only track strresult Dim fsError As New FileStream(Server.MapPath("./incoming/error.txt"), FileMode.Create, FileAccess.Write) Dim swError As New StreamWriter(fsError) swError.WriteLine("--==ERROR LOG==--") swError.WriteLine(Now.Date.ToShortDateString) swError.WriteLine("-----------------") swError.WriteLine(strResult) swError.Close() fsError.Close()
Hi all, Im not really sure if this is a PHP or and SQL problem but here goes.
Im using MSSQL and have developed a webpage that enables users to run various PACKAGES manually, however I need to display if the package has been successfully run.
Is there a system table that logs package information or is there a PHP function that I can use.
Thanks
P.s I know there are some system tables with the information for jobs but I do not want to create a job for each package.
I have created and deployed my first report. It renders fine for me and the other database admin. When others attempt to view it, we get the error
Query execution failed for data set 'periods'. (rsErrorExecutingCommand), For more information about this error navigate to the report server on the local server machine, or enable remote errors
Initially, We created a local group on the machine that hosts both the database and webserver and added the individuals to that group. Then, within SRS Report manager, we added that group to the Browswer role of the report. The error message was slightly different, in that it couldn't even open the Datasource.
We then added an individual to the database as dbreader, and got the above message. It apprently is starting to render, and when it encounters the first query (dataset "periods", which populates a drop down list for a parameter), it chokes. BTW, the Periods dataset executes a stored procedure dbo.Period_List that has no parameters. It returns a list of reporting periods.
I could not figure out how to "enable remote errors" or find an error log on the server. The C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles Log files did not appear to record any errors.
Hi,I have a few things on my databases which seem to be neither true systemobjects or user objects - notably a table called 'dtproperties' (createdby Enterprise manager as I understand, relating to relationship graphingor something) and some stored procs begining with "dt_" (some kind ofsource control stuff, possible visual studio related). These show up whenI use"exec sp_help 'databaseName'"but not in Ent. Mgr. or in Query Analyzer's object browser, and also notin a third party tool I use called AdeptSQL. I am wondering how thosetools know to differentiate between these types of quasi-system objects,and my real user data. (This is for the purpose of a customized schemagenerator I am writing). I'd prefer to determine this info with systemstored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into thesystem tables if needed.Thanks,Dave
Is there a system variable to indicate a package run successfully or failed? I have a task in Event Handlers to log the process into a table, but I still need extra information like execution status, more likely, if package successfully executed, I want to get a specific values from SQL server (like in DTS, execution_status = 4 when success), Any suggestions are welcome.
I'm trying to access data from one table by matching ids pulled from another table. I can't quite get the syntax though if anybody could help I would appericate it.
Here is where I'm at
select * from table1 where variable 1 = (select distinct(variable 2) from table 2 where variable 3 = variable 4)
Hi there, I need an easy solution for the following problem:
I want to join two tables. The first table contains an internal system customer ID and the official customer ID. The second table contains parts of the internal customer ID from table 1 plus the receivers of the invoices for each customer, If necessary. A parent child hierarchy If you'll call it like that. Now, how do I get a result table that puts out the customer ID plus the customer ID of the invoice receiver in a seperate row, like shown in the example result table?
Batch execution is terminated because of debugger request. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Batch execution is terminated because of debugger request any solution or cause of this exception please let me know freindz
I am trying to retrieve tables name for a database diagram within SQL database 2000 using T-SQL .I did some search Found that using dtproperties table I can get the diagrams name. but there is no information about the tables within these diagrams.
I am trying to retrieve tables name for a database diagram within SQL database 2000 using T-SQL .I did some search Found that using dtproperties table I can get the diagrams name. but there is no information about the tables within these diagrams.
where name comes from the info table, and number of items comes from the store table.
Query something like:
show the total number of items for all those users (in the fashion Name, Number of items)from the store table, for all those whose level=something and base=something in the auth table.
For example,
if there are 10 users whom match level=something and base=something in the auth table,
What I want displayed is: ------------------------ Name Number of Items ------------------------ user1 10 user2 15 user3 20 .... user10 30 -----------------------
which if broken up is: select name from info where aid=X select count(*) from store where aid=X ..
for all those whose base=something and level=something from auth.
Dear list,I am trying to get the names of the tables and the column names from thecode below for a database but it is not working. When I run the querybelow the column titles are delivered but there is no data. I think thismight be a premissions issue. Has anyone run into this before?Thanks in advance.Use Test_db/* Provides Table Name, Column Name, Extened Description */Select a.name as tbl_name, b.name as column_name, d.name as data_type,d.length as length, d.xprec as prec, d.scale as scale, b.usertype,b.scale, c.valuefrom sysobjects as a inner join syscolumns as b on a.id=b.id inner joinsysproperties as c on b.colid=c.smallid and a.id=c.idinner join systypes as d on b.xtype=d.xtype
Till recently we were using the following code to retreive schema information of columns of tables
Dim schemaTable = connection.GetOleDbSchemaTable( _ System.Data.OleDb.OleDbSchemaGuid.Columns, _ New Object() {Nothing, Nothing, tableName, Nothing})
Now instead of getting the name of table (which i was using as param for filtering) i'm going to receive a sql-query. Now my question is if I were to get a query like the following :
I am playing with DBCC command to check the contsrainst on a perticular table (DBCC CHECKCONSTRAINTS ('myTable') WITH ALL_CONSTRAINTS), it always gives the following result:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
please kindly help me for making a database My problem is that firstly when we create databse through enterprise manager then the second step is i want to create a tables.
when i am creating any one table in data base after this process when execuet the Query/show the table then the message is displayed
" the query cannot executed because some files are either missingor not register Run setup files again to make the required files are register."
how to handle this problem what can i do ?:shocked: :shocked:
I'm running a master package executing 8 child packages.
Each package contains the same connection managers and each package is stored within the MSDB database. The master package executes the packages stored in the MSDB using the 'execute package task'.
When running the master package from either MSDB or visual studio directly the odd thing happens that some tables will be filled, but after package execution I notice each table to be empty. There's no rollback 'procedure 'specified within the package and each package executes successfully because of error row handling. Anybody any hints how to solve this one?
Having some issues with our apps. We are trying to get our applications to work with sql2005.
Ive got the databases "setup", and all our apps run fine... ...except for when queries are made without the owner of the table being specified in the query.
The connection is opened with the username that is associated with that owner. And it fails in Manager as well. Is there something im missing, because you should be able to do this.
eg: select * from <table_name>
Gives the error:
Msg 208, Level 16, State 1, Line 1
Invalid object name '<table_name>'.
However if i were to query like this: select * from <owner>.<table_name>
I want to know where I can find Object permission detailes, For example I have table1 and user "abc", I have given permission to user "abc" for table1 to "SELECT" "INSERT", "UPDATE", I want to know where this detail is stored in system table. I was looking at "SYSUSERS","SYSLOGINS", "SYSPERMISSIONS", "SYSMEMBERS", "SYSOBJECTS" I couldn't find anything, If some one can post your answers that will be great.
I have succesffuly BCP out system tables(sysdatabases, sysdevices, syslogins, and sysusages). Now I want to BCP those same tables IN to create devices, databases and so on using the BCP utility. I run the same command used to BCP out and only changes the OUT to IN but it did not work. Can somebody whose done this before tell me how to do it.
Where can I find a discussion of how to interpret the keys1 and keys2 columns of the sysindexes table? These columns are binary(255) and are supposed to contain a description of the columns to which the index applies. I also need to know how to interpret the status column of the sysconstraints column.
I've one Sybase DBA asking this question, Is this really true, Pls help me.
Due to architectural changes in SQL Server 7.0, you cannot dump the transaction log if a database's system tables are inaccessible (due to media failure, for instance)in 7.0 and later versions. Microsoft recommends that separate devices be used for system tables and user tables to allow a final log backup to take place in the event the data device is inaccessible. The additional exposure incurred by not doing this is possible loss of data for the interval between the last transaction dump and the point of failure.
