I have an error trapping routine within a proc that uses cursors. The routine works but if I run the stored proc from Query analyzer more than once it complains the cursor has already been declared and is still open. Should I close and deallocate as part of my error routine?
View 3 RepliesHi guys.
is there anyway I can find a cursor that is open
so that i can close it?
I have a procedure running daily (across servers). that stopped suddenly with this error.
A cursor with the name 'xyz' already exists.
I tried closing and deallocating on destination server. I am getting the error like "cursor doesnot exist"
I need to run this procedure. i dont want to recycle the destination server.
any ideas?
-MAK
Dear Sir,
When we run DBCC CHECKDB, we got [Error 831, Severity 20, State 1 - "Unable to deallocate a kept page"... ]
We just found one about this err http://support.microsoft.com/kb/949199/en-us, but we didn't enabled SNAPSHOT isolation level.
Could someone tell us what can we do next step??
**DB Info.**
1. Version : Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86) Mar 23 2007 16:15:11 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2. snapshot_isolation_state : 0
3. snapshot_isolation_state_desc FF
Best Regards,
W2
We are using sql server 2005 Enterprise Edition with service pack1
I got the following error messages in the SQL log
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000090000 in file '....mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
fcb::close-flush: Operating system error 21(The device is not ready.) encountered.
I got these errors for about 2 hrs and after that I see these messages in the sql log
Starting up database ' '
1 transactions rolled forward in database '' (). This is an informational message only. No user action is required.
0 transactions rolled back in database ' ' (). This is an informational message only. No user action is required.
Recovery is writing a checkpoint in database ' ' ( ). This is an informational message only. No user action is required.
CHECKDB for database '' finished without errors on (local time). This is an informational message only; no user action is required.
Can anyone please help me in troubleshooting this issue. Why this migh have happened.
any help would be appreciated.
Thanks
when i try to deploy a packages in integration service catalog i am getting the below error
.NET Framework error occurred during execution of user-defined routine or aggregate
"deploy_project_internal":
System.ComponentModel.Win32Exception: A required privilege is not held by the client
System.ComponentModel.Win32Excepbon:
at Microsoft. SqlServer. IntegrationServices.Server.ISServerProcess.StartProcess(Soolean
bSuspendThread)
at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectlnternal(SqlInt64 deployld,
SqIInt64 versionld, SqlInt64 projectld, SqlString projectName)
. (Microsoft SQL Server, Error: 6522)
Hi,
My question is if I close the sqlreader i am using. will that close the connection it uses or the connection will still remain open?
Syed
We are running a nant build to recreate our databases from scripts. I am getting this error "Unable to deallocate a kept page"
I read on another site this should be fixed in SP2 however I am on SP2 (SQL Server 2005 9.00.3054)
Any help would be appreciated.
Jason
I have the next code in my proyect:
If CurrentProject.IsConnected Then
MsgBox "Base ya Conectada.Procedemos a cerrarla primero", vbOKOnly
If SQLConexion.State = adStateOpen Then SQLConexion.Close
SQLConexion.Close
End If
SQLConexion.Open SQLSentencia
AbreConexionSQLSERVER = 0
If I detect that the connection is opened while I am initializazing varriables, I want to close always the connection and then begins my aplicattion always opening the connection, but it always returns a error 91..
suggestions?
Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SqlServerProject1.StoredProcedures.insertlogin(SqlString log, SqlString pas).No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[insertlogin]. ***************************************************************all i am trying to do is : creating a SP in VS using managed code and then trying to execute it. But every time i get the above error. If you can tell me how to edit connection string in this that would be very helpful. At present i am using : Using conn As New SqlConnection("context connection=true") I tried to do "" ALTER ASSEMBLY SqlServerProject1 WITH PERMISSION_SET=EXTERNAL_ACCESS""but i get this error "" Msg 10327, Level 14, State 1, Line 1ALTER ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.""" *********************************************************************Plz help
View 13 Replies View RelatedDoes anyone know how to decrypt a PGP encrypted file in a DTS routine?
Thanks.
Danielle
I am loading in packed data (ie, from an IBM MVS mainframe - packed decimal data). Every 8 bits represents 2 digits of a number and the last 4 bits represents if it is a negative or positive number.
The system uses bcp to load the data into sql server.
The idea is to use a VB routine they wrote to transform the data into Char() based numbers. (And then transform the Char(12) to an Int.)
It is possible (and how) to incorporate a VB sub-routine into a stored procedure?
Bobby
bobby@teamcomfluent.com
I'm running vb 2088 express with SQL express. I have limited VB and very limited SQL knowledge.
I have a VB app that inserts candidate data into the SQL table (called front) all works fine.
I now have a second VB app (separate from the above VB app) that runs at the same time; it does some extra work on the candidate data.
My question is when a new candidate is added to the SQL table, how I can get the second VB app to automatically know that a new record has been inserted (and the record key) and then start the VB subroutine.
I'm unsure if I should look at triggers or stored procedures (or something else) for this task.
Any help or pointers to additional resources would be appreciated.
Thank you!
TJ
I need to write a stored procedure for seach routine.
ALTER PROCEDURE dbo.Search_Select
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(100) = NULL,
@Address VARCHAR(50) = NULL,
AS
DECLARE @Sql varchar(500)
DECLARE @Where varchar(500)
SET @Sql = 'SELECT * FROM employee WHERE '
HOW DO I WRITE SET@Where = ???? Taking care of all input parameters.
SET @Where = '(@FirstName LIKE ''' + @FirstName + '% AND'')' + '(@LastName LIKE ''' + @LastName + '% AND'')' .........
Is it right?
Hello all,
I'm stumped on how to solve this question so I figured i'd ask the community. As a warning i'm not sure best how to describe my situation so i'll try and give as much detail as I can.
First in table A, I have two columns that already have data in them that are numeric (Col1, Col2). Also, in table A I have two more columns that are going to derive their data based of a complicated data retrieveal routine (Col3, Col4). So my table structure looks something like this:
Code:
Table A
Col1 Col2 Col3 Col4
20 20 NULL NULL
(Where Col3, and Col4 are going to be populated based off the routine)
The Data for Col3 and Col4 is in an excel spreadsheet that i'd like to convert into a table for MSSQL. However i'm not sure how to do this because in the spreadsheet there is a lookup routine (that i'm trying to copy to MSSQL code, i'll show that in a minute) that generates its values based off data in the X / Y columns, so something like this:
Code:
_|1__|2___|3__
0|0 |0 |0
1|1 |15 |25
So when 2 is met, and 1 is met, they would equal '15'. No arithmetic involved, just simply matching up the X / Y and pulling the data.
My question is, how do I create tables out of this, so my lookup routines can get the values as a result of matching X / Y? (2, 1 = 15)???
The excel routine is this:
=IF(VLOOKUP(F66,'Appendix A'!A5:K56,MATCH(F68,'Appendix A'!A4:K4,1),TRUE)>F46/12*0.125,F46/12*0.125,(VLOOKUP(F66,'Appendix A'!A5:K56,MATCH(F68,'Appendix A'!A4:K4,1),TRUE)))
Thanks!
I need a function or a routine in sql for making all possible combinations of strings with given letters.
means if I give a string 'ab' the function should return
'a,b,ab,ba' if we give 'abc' it should return 'a,b,c,ab,ac,ba,bc,ca,cb,abc,acb,bac,bca,cab,cba'.
return data can be a single list or the list elements can be printed to screen one by one.
I need to use this routine for inserting bulk sample values for a few tables.
Also have limited time to make one by myself.
Please help.
Hi, I am new to ASP.NET and Visual Web Developer 2005 Express, which I run on Windows Vista. I am currently going through some of the tutorial videos on http://www.asp.net/learn/videos/ . I encountered an error in Lesson 04 and I would have ignored it but it keeps haunting me and now I cannot proceed with the lesson 09 all because of the same error. I would really appreciate it if somebody could help me resolve this because it.
General error text
The database filename can not contain the following 3 characters: [ (open square brace), ] (close square brace) and ' (single quote)
Lesson 4 circumstances
Web.Configis modified to contain the following code just before the closing </system.web> tag:<anonymousIdentification enabled="true"/><profile enabled="true"> <properties> <add name="MyNewProperty" allowAnonymous="true"/> </properties></profile>
I have an application that manage an sql srever compact edition database it worked OK until I got the following problem:
Only when debuging the application ("Start Debugging" from visual studio). In the first call to an sql server routine I get an exception "Unspecified error [ sqlceqp30.dll ]"
My code:
this.usersTableAdapter.Fill(this.nessDBDataSet.Users);
The line with the exception:
[global:ystem.Diagnostics.DebuggerNonUserCodeAttribute()]
[global:ystem.ComponentModel.DataObjectMethodAttribute(global:ystem.ComponentModel.DataObjectMethodType.Fill, true)]
public virtual int Fill(NessDBDataSet.UsersDataTable dataTable) {
this.Adapter.SelectCommand = this.CommandCollection[0];
if ((this.ClearBeforeFill == true)) {
dataTable.Clear();
}
int returnValue = this.Adapter.Fill(dataTable); //Here I get the exception
return returnValue;
}
The Exception:
Unspecified error [ sqlceqp30.dll ]
Again if I do "Run Without Debugging" Evrything is working.
Can any one help me with this
Thanks
Ofer
We have a load-balanced Reporting Services installation that has two front-end machines that run IIS and the reporting services windows service, and a clustered Report Server database server. We have the front ends load balanced behind an F5. If we stop IIS on one of these servers, this traffic is easily and transparently directed to the other server.
My question is what happens if the Windows service on one of these servers is stopped? When Reporting Services is triggering subscription processing is it calling out to all machines it believes are a part of the scale-out deployment? So, if we install windows updates that require a reboot, is there a possibility that some of our subscription processing will be lost, or is RS smart enough to direct processing to the currently active node?
HiWe have need for an SSIS package that would perform routine purging of the growing data in some of the tables used to support notification services. While running Sql in a regular job would seem to suffice, an SSIS package would be more in line with the other processes for the alerts in terms of manageability. The SSIS package should follow the following guidelines:Deletes records from a given number of tables, based on a specified date column for each table and a specified number of days for each table, or other conditions. SystemAlertQueue: 30 days old based on the SubmitTimestamp column. SystemAlertChron: 30 days old based on the EventTimestamp column. SystemAlertNotifChron: 30 days old based on the NotifTimestamp column. WMICheck, related tables (based on WMICheckID, see WMIAlerts database diagram in SqlServer) 15 days old based on BeginCheckDate column. Each table€™s deletion routine should be distinguishable in the package. Does any body know how to do this.. Please help meRegardsDeepu M.I
View 1 Replies View RelatedHi Friends ,
When i am trying to store a table under a database , it gives me an error that
'Invalid Cursor Status ' and it does not allow me to store that table .
The server is SQL Server 7.0 with Service pack 3.
Can somebody help me in getting this resolved please ?
Thanks
Anita .
We are getting an error while running a stored procedure,
This is not the exact error but it is roughly:
'cannot complete cursor operation because table schema changed after cursor declaration'
Any ideas as to what causes this?
Thanks,
Eddie
hello,
I have several tables that have guids as their primary keys and the tables are related as follows:
Table1 - primary key = ServiceNo (Guid), Filter Key = CampaignNo
Table2 - primary key = CostBasisNo (Guid), Foreign Key = ServiceNo (from Table1)
Table3 - primary key = UserId, Foreign Key = ServiceNo (from table1)
Table4 - primary key = SourceServiceNo (Foreign Key from Table1), MemberServiceNo(Foreign Key from Table1)
what I need to do is copy all records from Table1 where CampaignNo = @CampaignNo and insert them into table1, this I can do easily but I will generate a new ServiceNo for each one and associated a new CampaigNo which is fine.
The problem comes in that I need to also copy the contents of Table2 = Table3 for all ServiceNos that have been copied from Table1 but insert the new Guid that will have been created when copying the rows in Table1
This is further compounded when I need to do the same to Table4 but this time I need to insert the newid's for SourceServiceNo and the related MemberServiceNo which all would have changed.
I haven't the first clue where to start with this task, do I need to use temporary tables, cursors? any help gratefully received, even if it's a pointer to the most efficient approach.
regards
I use Microsof Visual Studio 2005 to write a c# aplication for my compact device (Dell Axim).
I also use Sql Server Compact Edition to handle my database.
Everything worked OK but suddenly I get an exception on the first call to fill command of a table in my database (Any table) This exception is happenning only when I debug the application if i run the application without debug everything is workink OK.
The exception details are :
System.Data.SqlServerCe.SqlCeException was unhandled
Message="Unspecified error [ sqlceqp30.dll ]"
HResult=-2147467259
NativeError=25123
Source="SQL Server Compact Edition ADO.NET Data Provider"
StackTrace:
at System.Data.SqlServerCe.SqlCeConnection.ProcessResults()
at System.Data.SqlServerCe.SqlCeConnection.Open()
at System.Data.SqlServerCe.SqlCeConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at Ness300.NessDBDataSetTableAdapters.UsersTableAdapter.Fill()
at Ness300.LoginPanel.DoLogin()
at Ness300.LoginPanel.OKButton_Click()
at NL300Controls.BmpButton.L300Button_MouseUp()
at System.Windows.Forms.Control.OnMouseUp()
at System.Windows.Forms.Control.WnProc()
at System.Windows.Forms.ContainerControl.WnProc()
at System.Windows.Forms.Control._InternalWnProc()
at Microsoft.AGL.Forms.EVL.EnterMainLoop()
at System.Windows.Forms.Application.Run()
at Ness300.Program.Main()
I tried to run the application from the mobile device in order to connect to it doing "Attch to running process" from visual studio.
But just running the application on the device with the resgistry value:
HKEY_LOCAL_MACHINESOFTWAREMicrosoft.NETCompactFrameworkManaged DebuggerAttachEnabled
set to 1 caused the crash when I set it back to 0 everything works fine.
It worked for me before I do not know what happened.
Is it a security problem ?
Can any one helpi me with this because this problem do not give me a way to debug my application.
I am currently re-writing an overnight index defrag procedure and would like to audit indexes in my database - logging the before defrag action" avg fragmentation value and "after defrag action" frag value in an audit table. Â This will be for all databases on the server. Â I have completed the vast majority of it (cycling though all the databases, detecting which indexes need reorganising or rebuilding and inserting the information into a table) but I cannot get the audit values working properly. Â For example, a sample row in my audit table would look like this:
ID Name DB Table frag_before frag_after
1 Index2 DB1 Table6 70.33456 0.03
2 Index7 DB1 Table9 45.98 1.2567etc
I have been using dynamic sql in an inner cursor and I get an error about the fetched variable of the outer cursor. How can I fix it?
DECLARE A1 CURSOR GLOBAL FOR
SELECT Iid, Server, Dbname
FROM@OuterTable;
[code]...
Hello group
I need some help and gaining knowledge per the SQL Standards.
I have a Stor Procedue which Cursor through say MASTER. This table have information/ Counm that guides me to multiple say two different table.
i.e, Col1 in MASTER table have the information on what table I need to work on COL2 in MASTER Table give me what Field I have to work on and accordingly move the data
Example :
MASTER
ID COL1 COL2 COL3
1 Address Street Nichols Rd.
1 Address Zip 90001
2 Address Street Michigan Ave
2 Address Zip 90002
3 Person FName James
3 Person LName Bond
4 Person FName Bill
4 Person LName Gates
Address
ID Street Zip
1 Nichols Rd. 90001
2 Michigan Ave 90002
Person
ID FName LName
3 James Bond
4 Bill Gates
For doing this I created a Store Procedure which Cursor thru MASTER table which for each row goes to Entity on which it has to work (MASTER Table : Col1 Value) and in that entity Its goes to the Field(MASTER Table: Col2 Value) and places the value of the Col3 in it.
It is working fine and superb. Now, I try to introduce Error Handling such that incase in order to see if there is any error while doing this INSERT and UPDATE.
SAMPLE QUERY: (Structure is Same but modified for readability)
UPDATE @ENTITY SET @FIELD = @VALUE WHERE ID = @KEYID
IF @@ERROR <> 0
BEGIN
INSERT INTO dbo.ErrorLog VALUES( @KEYID + ' , '+ @ENTITY + ' , ' + @FIELD +' , ' + @VALUE )
END
This query is working fine . If there is any error and for some reason if its not able to insert/update the data Its populates the ErrorLog Table and instead of failing the store proc goes for the next row and do teh necessary action
Finally SProc does what it has too
1) Get the data in teh respective tabel and colunm
2) if it cannot insert/ update go for the next row and populate the error log table with what it was n't able to update/ insert
SO Whats the problem? Good question , here is the issue or topic of discussion
1) What is the standard way of Error Handling. Did I atleast follow teh minimum Error Handling criteria.
2) I can see from teh table that, as required, its skipping the row into Error Log table whenevr its not able to do the required functions and going for next row
3) I introduce a Error and when I execute the Sproc it gives me Mesaage:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Also I see that "Query Completed with Errors" well I said whenever teh SPROC see error do some thing else, then why would it count it as error and give me this message? Why is the SPROC failing.
What Should I do such that it counts or give when ever there is a genuine error. I mean it should not fail at all because I am saying there whenever there is an incapability of Insert/ Delete just simply skip the row and Insert that row in Error Log Table.
Am I missing something in this Proc?
If it will do that then is tehre any way or query or anything I have to do that handles the issue that
Whennver its not able to do whatever I am asking it to do (Ex: If I am asking it to insert INTEGER in Character Field) then an error is raised then just skipp that row insert that row in erro log and go for next row and dont fail the SPROC.
THANKS FOR THE PATIENCE FOR READING AND REPLYING.
I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).
When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).
The problem thus cannot be with the function since it works just fine if I do like this:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001
And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).
But when I do this it crashes:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)
For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.
Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.
The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).
Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).
Is there a certain tweak I can do to make things work out, or should I code things differently?
Thanks!
One of the people I have to support gets an error message when they are trying to use a SQL based application that was developed in house:
an error occured..microsoft odbc sql driver cursor type change. location frn main, load user () source is odbc
Unfortunately, the developer is not available to troubleshoot the issue and I do not understand it. I thought ODBC settings were static, so how could they cause a cursor to change?
Hi;I am trying to write a rountine ( below ) that will go into a colum oftext data type ( fae.pmcommnt ) locate the word "to" and replace it.I have the routine below. I get no error messages, but it also seemsto do nothing :).Any clues would be greatly appreciated.ThanksSteve================================================== =============declare @ptrP intSELECT @ptrP = PATINDEX('%to%', pmcommnt)from fae where projid ='00013'declare @ptrPC binary(16)select @ptrPC = TEXTPTR(pmcommnt)from faeif( TEXTVALID ('fae.pmcommnt', @ptrPC ) > 0 )print 'works'print @ptrPUPDATETEXT fae.pmcommnt @ptrPC @ptrP 2 'JJ'select projid, pmcommnt from fae
View 2 Replies View RelatedWe have found that it is common for Visual Studio 2005 to crash when editing or running SSIS packages -- from CTP versions through beta versions and including the release version.
Of course we kept hoping that newer releases would become more stable, or at least more robust -- and now I'm hoping there will be a service pack, which might make it more robust?
I get the following errors while running a SQL procedure.
[ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]The cursor is already open
[ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'qst_Ruler_AllExpenses' already exists
I am creating, opening and deallocating the cursor.
Advice please.
Hello:
We have an old 16-bit app that runs fine in SQL 7.0 but on our test SQL 2000 box some reports generate the following error "System Message 10202 - The application fetch buffer size is less than the cursor select statement requires". Any ideas what this means, I am not much of a coder. The MS Knowledge base does not list much on this.
Thanks,
I'm trying to run a stored proc on a SQL 2005 SP1 box to return info to a SQL 2000 SP4 box, as a linked server. Both boxes have the latest service packs, and run Windows 2003 Server, again with the latest service packs. The error I get is: OLE DB provider "SQLNCLI" for linked server "192.168.0.126" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".Msg 16955, Level 16, State 2, Line 1Could not create an acceptable cursor. The full script I am running is: CREATE procedure [dbo].[proc_AuditServer] as /* ** Auditing Script for SQL Servers. ** ** D Maxwell, June 2007 ** ** This script takes configuration and job status information ** and writes it to a designated logging server. I'll describe ** each section in detail, below. We write to the local box first, ** Then upload everything to the logging server. ** ** This is the SQL 2005 version. */ /* ** We want to know exactly what server this is, so ** we get the server name, instance name, as well as ** SQL Version, Edition, and Service Pack level. */ truncate table admin.dbo.sql_servers insert into admin.dbo.sql_servers select convert(varchar(15), serverproperty('ServerName')), convert(varchar(25), serverproperty('InstanceName')), convert(char(9), serverproperty('ProductVersion')), convert(varchar(4), serverproperty('ProductLevel')), convert(varchar(20), serverproperty('Edition')), getdate() /* ** Now, having that, we get the list of databases, ** as well as thier creation dates and file names. */ truncate table admin.dbo.databases insert into admin.dbo.databases select convert(varchar(15), serverproperty('ServerName')), dbid, name, crdate, filename from master..sysdatabases where dbid > 4 order by dbid /* ** We need to know how the server is configured, so we ** can compare it to a list of preferred configuration ** values, as well as the defaults. I cut this out of ** sp_configure. */ truncate table admin.dbo.server_config insert into admin.dbo.server_config select convert(varchar(15), serverproperty('ServerName')), name, config_value = c.value, run_value = master.dbo.syscurconfigs.value from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs where type = 'C' and number = c.config and number = master.dbo.syscurconfigs.config and ((c.status & 2 <> 0 ) OR (c.status & 2 = 0) ) order by lower(name) /* ** The next configuration item we want to get is the ** list of jobs that run on the server. We're looking ** specifically for backup and other maintenance jobs. ** (Which will hopefully be named appropriately...) ** We use Neil Boyle's job report script for this. ** My comments and changes prefaced by a 'DM:' */ truncate table admin.dbo.jobs insert into admin.dbo.jobs select convert(varchar(15), serverproperty('ServerName')), --DM: Needed since we'll have lots of servers reporting j.job_id, -- DM: More unique than a name. convert(varchar(22), j.name) as job_name, case freq_type -- Daily, weekly, Monthly when 1 then 'Once' when 4 then 'Daily' when 8 then 'Wk ' -- For weekly, add in the days of the week + case freq_interval & 2 when 2 then 'M' else '' end -- Monday + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday + case freq_interval & 8 when 8 then 'W' else '' end -- etc + case freq_interval & 16 when 16 then 'Th' else '' end + case freq_interval & 32 when 32 then 'F' else '' end + case freq_interval & 64 when 64 then 'Sa' else '' end + case freq_interval & 1 when 1 then 'Su' else '' end when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day when 32 then 'Mthly ' -- The most complicated one, "every third Friday of the month" for example + case freq_relative_interval when 1 then 'Every First ' when 2 then 'Every Second ' when 4 then 'Every Third ' when 8 then 'Every Fourth ' when 16 then 'Every Last ' end + case freq_interval when 1 then 'Sunday' when 2 then 'Monday' when 3 then 'Tuesday' when 4 then 'Wednesday' when 5 then 'Thursday' when 6 then 'Friday' when 7 then 'Saturday' when 8 then 'Day' when 9 then 'Week day' when 10 then 'Weekend day' end when 64 then 'Startup' -- When SQL Server starts when 128 then 'Idle' -- Whenever SQL Server gets bored else 'Err' -- This should never happen end as schedule , case freq_subday_type -- FOr when a job funs every few seconds, minutes or hours when 1 then 'Runs once at:' when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds' when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes' when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours' end as frequency -- All the subsrings are because the times are stored as an integer with no leading zeroes -- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00) , substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at ,case freq_subday_type when 1 then NULL -- Ignore the end time if not a recurring job else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2) + ':' + substring ( right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at from msdb.dbo.sysjobs j, msdb.dbo.sysJobSchedules s, msdb.dbo.sysschedules c where j.job_id = s.job_id and s.schedule_id = c.schedule_id order by j.name, start_at /* ** Now that we know what jobs we have, let's find out ** how they did recently. */ truncate table job_status insert into job_status select convert(varchar(15), serverproperty('ServerName')), job_id, run_status, run_date, run_time, run_duration from msdb..sysjobhistory where step_name = '(job outcome)' -- The last 90 days' worth. and run_date > (select replace(convert(varchar(10), (getdate() - 90), 120), '-', '')) order by run_date desc /* ** If this server is already known to the audit server, ** we need to remove the existing data from the audit ** tables. */ declare @known bit set @known = (select count(*) from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('servername')))) /* ** Now we remove the existing information from the audit tables, ** if need be. */ if @known = 1 begin delete from [192.168.0.126].AUDITDB.dbo.sql_servers where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.databases where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.server_config where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.jobs where server_name = (select convert(varchar(15), serverproperty('ServerName'))) delete from [192.168.0.126].AUDITDB.dbo.job_status where server_name = (select convert(varchar(15), serverproperty('ServerName'))) end /* ** Finally, we upload the new info from here to the audit server. */ insert into [192.168.0.126].AUDITDB.dbo.sql_servers select * from admin.dbo.sql_servers insert into [192.168.0.126].AUDITDB.dbo.server_config select * from admin.dbo.server_config insert into [192.168.0.126].AUDITDB.dbo.databases select * from admin.dbo.databases insert into [192.168.0.126].AUDITDB.dbo.jobs select * from admin.dbo.jobs insert into [192.168.0.126].AUDITDB.dbo.job_status select * from admin.dbo.job_status This works fine for other boxes of the same service pack levels. I've already read KB302477, which doesn't appear to apply, since I'm already several revisions beyond that. I'm unable to duplicate this in test. Any ideas as to what I should look at next? Thanks. -D.
View 2 Replies View Related