Currently, user apple can exec sp_help_job (system stored procedure) in database msdb on Server/Instance name CALFI303 but it doesn€™t return any records. What permissions I need to give to the User apple ? If I sign on with sa, it returns the records with no problem.
I recently came across this built-in role in MSDB. It is not documented anywhere except sp_create role stuff. Does anyone know what this role is used for? Is this what gives users that are not dbo or sa access to see jobs?
View 1 Replies View Related
Can anybody help me. I have a job 1 , job 2 , job 3 and job 4. In job 1 i call job 2 in step 1 and job 3 in step 2 and job 4 in step 3. I call job2 , job 3 and job 3 by EXEC dbo.sp_start_job but before completion of job 2 it goes to job 3, so for job 4 also. i donot know how to use exec msdb.dbo.sp_help_job for job status completion.
Is there a way to calculate how long each job takes to run using sp_help_job or similar stored procedure? Is last_run_time the clock time or duration? I am getting values like 40000 and 50000 under last_run_time but don't know what that is.
Notalian
Hello,
Could someone give me a code sample of how to extract @execution_status into a variable from sp_help_job in t-sql?
Thank you
exec msdb.dbo.sp_help_job @job_name = 'job_name', @job_aspect='JOB'
I've been reading a lot of stuff about this lately, i've tried severals solutions but none worked.
The case is pretty much standard: from query analyzer/any other software it works if i execute it by hand. From my piece of software it fails miserably (managed code, .NET 2.0) with "A severe error occurred on the current command. The results, if any, should be discarded."
Here is the call stack:
Stack : at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at <my code which checks the status of the stored procedure is listed here>
The point is that if the SQL user is made sysadmin it works. As I understood from the "Books online" the SQL Server agent uses the account specified using xp_sqlagent_proxy_account when executing jobs or commands for users who are not members of the sysadmin fixed server role.
I tried that too, the xp_sqlagent_proxy_account didnt print out any error, and "exec master.dbo.xp_sqlagent_proxy_account N'GET' "returned no records. Obviously the execution of "sp_help_job" still fails with the above exception.
How can I run "sp_help_job" with an user which is not part of the sysadmin role?
Hi everyone,
I want to check if a job is currently running before calling sp_start_job. I just don't know how to read execution_status returned by sp_help_job in my query.
Any ideas
Thank you
I have 32-BIT MS SQL 2005 running on Windows 2003 R2 platform. The code is 100% bug-free and works fine on staging and production. Production has an issue AT RANDOM TIMES. Most of the time, it works fine...connections are pooled and reused. Out of nowhere (very randomly), it will start opening new connections for each request and keep doing that until the DB server crashes (could not open connection exception). If I restart IIS, it works fine again...all connections are being reused (no more than 6 connections). Just for fun, I restarted IIS again....it starts opening new connections for each request!! I restarted again, it now reuses existing connections. What's going on??? This has occurred 2 times on our production box. .NET ALSO has a SERIOUS bug where if you nest master pages or user controls, sometimes it will throw a compilation error on a LIVE site (microsoft admits that it's a bug in the engine and currently...there is NO FIX for it..there are patches...none of them work).
View 3 Replies View RelatedI get this error when I try to access one of my tables.
Msg 605, Level 21, State 1
Attempt to fetch logical page 4377 in database 'maillist' belongs to object '1340531809', not to object 'client'.
I know that when my database is restarted it will be marked suspect because of this error.
Does anybody know:
What causes this error?
Why I keep on getting it?
How do you fix it?
Is anybody even reading this?
Greetings,
I am having a big issue now. I made what I thought would be a simple change to our reporting services application, which has been running smoothly for about 2 years. The change has caused my "forms authentication" to start throwing an error that I rember from a while back while testing. Our rporting server is down, sad to say. Has anyone had this problem.
The error : Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:...
Changes made
1. Stoped RS web app in IIS
2. Changed the ASP.NET Config Settings(Report Server App)
a. Authentication Tab - cookie time out (changed to 60)
b. State Manegement Tab - Session Time Out (changed to 60)
3. Changed the ASP.NET Config Settings(Our aps.net app that displays the reports)
a. Authentication Tab - cookie time out (changed to 60)
b. State Manegement Tab - Session Time Out (changed to 60)
4. Changed the SessionState Timeout property in the web config file in our web app that displays the reports.
5. Restarted the Report Server app.
6. Recycled the app pools
Could not log in!
I am catching the above error in my web app's ReportExecution.LogonUser().
I rebooted the server and continue to get sqldump logs and this exception. This error occured when I was testing the redirect a while back but that issue was resolved. The only changes I have made are above.....
hi,
i had a query like this ,
source flat file containg row having length 803, frm that i parse the fields of diff lengths ,record type field is one of them,
after flat file i took derived column to parse the record types(i.e type1,type2, .......)
later i took conditional split and split those record types in to diff derived columns,
problem starts now ?
i got 500 records before conditional split and after condi..split.. they r 499,
i dont know wat happened to that 1 record.
plz help me regarding this issue........
Hi all of you,
Does anyone ever face this sort of error? We were launching a SSIS from our .Net console:
0x80070002 while loading package file "C:Documents and Settingsadminsql2k5Local SettingsApplication DataMicrosoftSQL ServerSmoInnerPackage.dtsx". The system cannot find the file specified.
InnerPackage.dtsx is a sort of template for SSIS??
Thanks indeed,
Dear all
I am a pretty new in the development world fresh from uni. I am doing development on a system that has a security database. Access to the data in that database is pretty important. So in case the main server where the database is stored for soem reason fails or gets compromised i need to have a second copy with the most recent data in that database and keep the application up and running. The data i have is stored in a SQL 2005 database. What are the recomended aproaches for acheiving this needed reliability?
Would running the SQL Agent every 2 minutes do the trick? And replicate the database to another server and then have asecondary deployment on that server running as a backup? Or are there any other means?
Any advice is apreciated.
Sincerely
Publc role has been grannted 'SELECT' privelege to syslogins and sysusers tables in the Master and GTSS database
The syslogins table contains all the logins that were created on the server. The sysusers table contains the users that are mapped to the database. Unauthorised access to these tables would reveal critical authentication info of other users
Restrictive permissions should be configured on critical database tables such as sysusers and syslogin.
How to set this.
Our SQL server keeps crashing with the following error. When it crashed it completely shut down the server. Could you please give me advice on how to stop this from happening again? I would like to thank in advance for your help.
A MS DTC component has encountered an internal error. The process is being terminated. Error Specifics: A non-MS DTC XA Library threw an exception in function olog
ntdll!KiFastSystemCallRet + 0x0
+ 0xd58c3c0
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Here is the information of our server
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name SQL2387
System Manufacturer Dell Computer Corporation
System Model PowerEdge 2850
System Type X86-based PC
Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz
Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz
Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz
Processor x86 Family 15 Model 4 Stepping 3 GenuineIntel ~3790 Mhz
BIOS Version/Date Dell Computer Corporation A04, 9/22/2005
SMBIOS Version 2.3
Windows Directory C:WINDOWS
System Directory C:WINDOWSsystem32
Boot Device DeviceHarddiskDmVolumesSQL2387Dg0Volume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone Eastern Standard Time
Total Physical Memory 4,095.08 MB
Available Physical Memory 1.75 GB
Total Virtual Memory 1.83 GB
Available Virtual Memory 3.81 GB
Page File Space 2.00 GB
Page File C:pagefile.sys
Hi all of you,
This post is related with this one, previously opened:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=620243&SiteID=1
Moreover, this post is related too (IMHO) with
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=430406&SiteID=17
Following with my research I've seen that DTS.DLL is installed on:
1)My root c:
2)%systemroot%system32
2)c:archivos de programa...90
3)c:archivos de programa...80
It seems that BIDS is trying to call, at first, to this COM CCW component.
So that, all has been deleted phsyically from my workstation and unregistered.
Sql Server 2005 full installation again and... the problem lives yet.
I'm desperate
Hi Friends,
We developed an application in the ASP.Net with C#.
We are going to do a task in the transaction.
It is working perfectly. But if two users comes simultaneously, then
It gives an exception that Transaction is already commited.
First transaction Commited Successfully.
When Second transaction goes to Commit()
Then it generate this error.
We are unable to find this problem Whether this is problom from SQL Server or DOTNET.
Pls give ur suggestions.
Thanks and waiting a helpful response.
Sachin
We need to apply the transactional log for business hours (9-5) everyday to a oracle instance which will be seeded with SQL Data for once. And then every day the transactional log would be applied.
We need to do this as eventually this server is moving to oracle.
At this point if somebody could help me to read the transactional log that would be great.
Thanks,
Please any one can help me ?I have multiuser application ( VB 6.0 ) on SQL Server 2000 running.
Last night my Sql Server was writting info on the Log file of one database when the power accidentally shut down. In fact, the server was executing one Microsoft Data Transformation Service ( DTS ) which transfers data from one table to another.
This morning when the users tried to work with this database everything went wrong... I've not been able to attach MDF AND LDF files to SQL Server 2000 Desktop Engine.
Looking for what happened I found those Info/Error LOG events:
A)Starting up database 'pcserveis'.
B)ex_raise2: Exception raised, major=34, minor=48, severity=21, attempting to create symptom dump
C) "The transaction ( Process Id 5 ) was left in "interblockade" in Lock
resources with another process and was chosen as subject of the "interblockade". Execute the transaction again." ( Sorry about wrong words , i'm using a translation tool from spanish )
D) Could not undo log record (5019077:221:133), for transaction ID (0:15254626), on page (1:1917), database 'pcserveis' (database ID 11). Page information: LSN = (5019072:426:5), type = 1. Log information: OpCode = 2, context 1.. ( Error: 3448, Severity: 21, State: 1)
E)Error while undoing logged operation in database 'pcserveis'. Error at log record ID (5019077:221:133).. (Error: 9004, Severity: 23, State: 7)
F)An error occurred while processing the log for database 'pcserveis'..
What can i do to restore my database? Thanks
Hi,
I'm upgrading a new dedicated sql server 2005 (64 bit) box that I just got to SP2. After I install SP2 there is 1 critical update.
Critical Update for SQL Server 2005 Service Pack 2 (KB 934458)
When I install this, it says the installation has failed. I have uninstalled it and tryed reinstalling but I get the same result.
It's listed in the control panel as a program I can remove, however in windows update history it says it has failed. Also in windows update I no longer have the option to install it again, as though it has installed properly.
Anybody have these issues before? I'm not sure if its installed OK or not.
Thanks!
mike123
On the re-boot required for critical patch KB824105 MS SQL Server failed torecgnize the log on for the account it is registered under, and all otheraccounts when manually cahnged.It seems to have completely lost all ability to recognize and logon.SQL is totally hooped - no ODBC at all at the service doesn't start. At allbecause of no recognition of any accounts.Any suggestions? I've been trying for a while.
View 1 Replies View RelatedHi all of you,
It seems that colleagues need our info stored in our sql25k cluster for FoxPro.
How could I extract them using SSIS?
Thanks in advance
We are a National Level Institutional Stock Broker in India providing trading platform to our Brokers to trade on Exchange nation-wide. We are using Compaq Proliant 8500 Serires computers in clusterd mode having RAM of 1.5 GB on each machine. We are using the Computer-To-Computer-Link (CTCL) solution / application for routing orders of brokers to the Stock Exchange. This application uses the SQL server 7.0 in clustered mode as back-end database on Windows NT Ver 4.0. On daily basis around 20,000 transactions are processed in the database.
Following are the Database Details
----------------------------------
1. Dat Size : 1890 MB
2. Log Size : 838 MB
------------------
Total Size: 2728 MB
3. File growth options: Automated file growth, By percent : 10
4. Maximum File Size : Unrestricted file growth
On April 25, 2001, we found that application was not getting initialize, we (technical team) checked all the procedures, which are getting triggered at that particular time and found all of them were ok, but application did not started.
We also suspected that one of the procedure might have been corrupted, so we re-compiled all procedures which get triggered before starting the application. After havind done so, the application was still not getting initalized / started.
We also executed following commands:
1. DBCC CHECKDB to check inconsistancy, if any but found none (log is available)
2. DUMP TRAN with no_log (To clear transation logs)
We also executed procedures one after the other sequentially, which were being executed before the application starts after binding once again.
After having done this, we also suspected that the problem might have been with "SQL Server 7.0 database engine" and therefore decided to re-start the computers, after which we found that the applicatio started without any hindrance / errors.
This has raised some issues as follows:
1. Has the database corrupted ? If yes, weather it is possible to run DBCC CHECKDB, SQL queries on a corrupted database ?
2. Was there any systems environment failure ?
If so, is it possible to run DBCC CHECKDB, SQL queries on a corrupted
database?
3. Is there any possiblity of Database Engine failure?
What can be the consequeces on Database Engine failure ?
4. Is it possible that Database Engine Failure is the cause for our case ?
Our some of the engineers are of the opinion that the SQL server need to be shutdown and re-started periodically.
1. Will this in any way avoid such re-currance in the future?
2. If this is true, we would like to have your technical feedback as to why this is necessary?
Should you require any further clarification, I shall be glad to furnish it to you in this regard.
I request you to give your techical feed back on an urgent basis as our applications are CRITICAL and needs UTMOST CARE, hence needs consistancy of database.
I shall be glad to receive your feedback at
my e-mail: gchavan@iseindia.com or (+91)(022-781 2389).
Govind Chavan, Mumbai, INDIA
SQL Server has stopped a critical task, with the following error log. I have executed the task again and the same symptons occur.
--
EXCEPTION_ACCESS_VIOLATION raised, attempting to create sympton dump
***BEGIN STACK TRACE***
0x0040DC1A in SQLSERVR.EXE, ubfree() + 0x009A
...
...
--
Has anyone any had a simliar experience or have any suggestions for investigation?
Cheers
Jasper
Good day everyone!
The issue I'm going to pose here has to do with SQL Server allocated memory BEING EXHAUSTED by an application (a simple data receiver from GPS units) when the number of units connecting to it is considerably increasing.
This happens to an extent that no more connections from the units are valid and therefore lots of data (Afterwards) is being lost.
This causes a real problem with the installations and the projects which are running the system.
I will hereby post all of the info which might be useful for any Database Experts among you, so you can perhaps help me figure out the source(s) of the mentioned problem, and possible solution(s) of the issue.
1- The Operating System used:
Windows 2003 Server (Ent. Edition)
2- The Database Server used:
Microsoft SQL 2000 Server
3- The amount of memory:
1 GB (but on some sites there are 4 GB and the same problem
is faced)
4- The conditions where the issue happens
When the amount of the received data - which gets written to
the SQL Server tables - exceeds a certain limit.
5- Number of applications used:
We are using two applications:
a- The receiver, which is a simple application that only
receives the data from the GPS units installed at the cars
of the clients' fleet(s), and writes them to the database
(Day and Night - running all the time), so the memory used
gets bigger and bigger till it floats the memory used by
MS SQL Server 2000. As a result of this the connections
made from the units furtherly to this point (to the receiver)
are not being received and written to the database.
b- the other application is quite heavy.. it does data
management on SQL Server 2000 and exhausts the CPU.
It reads from tables and makes analysis for GPS data...
This is done while "vehicle tracking".. so it makes
calculations on the received data and stores them into
output tables.
6- The programming language used in writing these 2 applications:
Delphi 7
7- What are we doing right now to handle this problem?
We need to flush the memory on periodical basis.
This is being done by stopping the SQL Server, or by
restarting the PC.
And it is not solving the problem entirely, especially that
there must be a way to handle this.
BTW, the memory is not being flushed when we turn the
application off.
8- What do we need to know?
a- We've found, after research, that there are "Temporary tables" or
objects in SQL Server 2000, that the DB system creates.. also page
files, and temp tables.
What are these for? And does handling them in a certain manner help
solve the problem?
b- Is there a way to monitor our applications to see where they are
increasing the usage of memory and how this is happening?
c- Is there a script that writes to the database that we can use to see if
the problem is from our applications? And in case it is, then is there a
command we should write or a function or script we should run in order
to flush the MS SQL 2000 Server periodically, so that it doesn't flood?
What comments can you have on this issue?
Are there solutions at hand?
I thank everyone for trying to help!
All the best :beer: :beer:
TD.
P.S. you can write to me at: database1980 (AT) hotmail (DOT) com
Hi
I have one query regarding backup
ecovery startegy
hi
I have Db with simple recovery model.. recently i updated my column without where clause now i need to recover all my original state of that column..i just have idea @ set DB recovery model to Full then take full back up and then take differantial back up and then take transactional log back up and restore it as full , diff and then most recent transactional log back up...but still i didnt get original state of that table column...
please guide me
T.I.A
Hi,
what is the difference between those 2 SQL server 2005 SP2 critical update?
the description of the two are the same but the builds are different.
what should i install?
1.
http://www.microsoft.com/downloads/details.aspx?familyid=A7C903FE-35A7-4BB2-8E73-932770099711&displaylang=en
2.
http://www.microsoft.com/downloads/details.aspx?familyid=D09CF5C4-FEC4-4322-9FEE-06A43401CF0C&displaylang=en
THX
Hi everyone,
I've got a serious problem with SSIS packages. We've built an application -done with Framework 2.0- which throw SSIS packages on demand -according a criteria on NTFS-
Everything's fine but those packages which own ADO .Net connections. When our application meet some of them appears in our log:
I attached you the snippet of code:
pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & "" & ObjSSIS.sSSISName, ObjSSIS.sServer, "usrSSIS", "ninot", EventsSSIS)
Till here, fine no throws any event.
sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)
'here throws OnError event when meet the task issued
If sResultDts = DTSExecResult.Success Then
...
No se pudo adquirir la conexión "SRVDESASQL2005.msdb.usrSSIS". Puede ser que la conexión no esté configurada correctamente o que no tenga los permisos adecuados en esta conexión.
Tranlate into english, more or less:
It doesn't acquire the connection "<server.database.user>." It could be that the connection is not fine customized or maybe permissions are not suitable for this connection.
At the same time we've verified that all data sources are available. In a fact that same packages from the server is launched without incidences (manually)
Please let me know any comment or further information or any link related with this problem. With OLE Db connections we haven't problems at all.
Thanks in advance,
Hi everyone,
I perfectly know that this post is bad allocated but it's a critical one. We've a cluster and there it don't get rows.
I'm trying this sentence: SELECT * FROM ::FN_VIRTUALFILESTATS and from our production server doesn't return results. Why the hell not?
However from other servers it returns data.
Thanks for your time,
Our company records live sales into an SQL Server database. The same tables that store the sale information are also used by a reporting interface to query sales figures, but occasionally a SELECT generated by the reports is so slow that it causes the INSERT operations to time out and fail.
We've tried increasing the CommandTimeout property in .NET of the application performing the inserts, but despite it being set to 90 seconds, it's still not enough to prevent the occasional sale from failing to record which is a big no-no for us. We've run the Database Tuning Advisor on the stored procedure that generates the SELECT for the reports, and it is now fully optimized but still this isn't enough. The tables are quite massive (millions of rows) and the SELECT requires JOINs to other tables, some of which are in a separate database on the same server.
Is there a solution to this problem, aside from increasing the CommandTimeout property to the point where no timeout errors occur? My concern is that doing this could increase the number of concurrent connections and we'd hit another limit, so it's not really solving the problem. Is there a way to configure SQL Server to always favour INSERTs over SELECTs? The reporting users won't really care if the reports are slower but it's critical to get these INSERTs up to 100% reliability.
I'm not a DBA (just a developer) so I don't have an intricate knowledge of databases and this problem is a bit beyond my level of expertise. Obviously we don't want to re-design our entire system, but we'll do whatever necessary to ensure we aren't failing to record sales.
One idea I had, which may be awful I don't know, is to submit these sales to an MSMQ and write some software that will read from the queue and insert the records from there instead. We could then deal with the timeout issue by just re-submitting the sale until it is accepted, then removing it from the queue.
1. I am looking data base solution for highly critical application. what type of HA/DR solution will suite for this.(always on, transactional , log shipping or mirroring). make sure all secondary nodes should be identical to production .
2. for any need to upgrade /patching /maintenance work, Â production should not be able to down. No downtime 100%.
Hello,
I'm working on the design of a system that will have two queues. The first queue will take requests to do something. The second queue will hold the results of having done something. Both queues won't necessarily be on the same physical server. Clients reading/writing to the queue will not be using SQL server. Web service/WCF interfaces will be developed to interrogate/manipulate queues.
My question is: which is better - MSMQ or Service Broker; if I want reliable queues. I can affort to loose items off the first queue, but cannot afford to loose any result data on the second queue.
Can anyone make any recommendations please?
Regards,
Jonathan.
We provide hardware and network support to a medical practice with 5 surgeons and 10 admin staff.
They are implementing a new medical practice software package which will hold all their business critical data including financials, patients, medical records etc. This runs on either SQL 2005 express or full SQL server.
The software vendor is proposing using SQL express only as the database will be << 4Gb
We are not application or database skilled and have concerns that the medical practice may not be getting the best advise from the software vendor.
Is SQL express just as robust and safe for critical data as the full SQL server database ? Technical €œgut feel€? is that it can€™t be or MS would not sell many licenses.
Comments would be appreciated. It is hard for us to suggest using a full SQL database unless there is a substantiated reason.