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
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
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
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?
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 have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View Related
Hello World,
I'm new to SSIS and would like a little assistance getting started, if possible...
Here is what I want to do:
Check if file exist (C:DTS UpgradeFilexxx.txt) --->
Archive file (C:DTS UpgradeArchive) --->
Check if file has data (true or false)
AND/OR
If there are any good website that have good direction, let me know
Thanks in advance for your help!!!
I need a job run page to fire a job on a sql server if the job is not already running. How do I check if the job is running on the MSSQL server.
Can I use the sp_job_help as it does return 4 data sets with the first having the data I need, but as yet I have not mastered a multi data set return.
How can I make sure that I have SQL Server 2000 sp3 or sp3a installed?
Thanks you,
http://thelushed.com/forum/showthread.php?t=138
View 1 Replies View RelatedDECLARE @Temp int
DECLARE @FullQry varchar(50)
set @FullQry='select @Temp=Emp_ID from Employee where.....'
Exec(@FullQry)
select @@ROWCOUNT
My Employee table has 3 records and this query sholud return me @@ROWCOUNT=1
but it will return 0 why this i am not able to find out.Exec function return ROWCOUNT or not?
insert into OPENROWSET('Excel 8.0;Database=D: esting.xls;',
'SELECT * FROM [testing$]')
getting errore
Incorrect syntax near ')'.
Hi,
Is there any way to tell me how many users connecting to specific database / who are using it in the management studio? we are use the windows authentication mode.
Thanks!
I want to built a table to a form....
I have some check boxes in this form....
what is the script line for this check box ?
I know it is suppose to work 1 or 0 ? for false or true .....
How I suppose to do this ?
Hello,I Have a code:<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [shop_clients] ([ID], [IMIE]) VALUES (@ID, @IMIE)"> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="IMIE" Type="String" /> </InsertParameters> </asp:SqlDataSource> SO this code It will allow me insert to database SQL textbox - name ?If yes How I can:If I click the button My textbox - name, insert to databasePlease me help :)
View 1 Replies View RelatedAperently I could not insert a text field to another table from INSERTED in a trigger.It seems th follwoing is working, do you see any problem joining INSERTED to the mySrcTable which is the table that has this trigger
INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText) SELECT i.myTrID, i.myFirstName, p.myBigText FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID WHERE (i.myTrType = 'In') Thanks,
Hi I was wodering how to add an OR statment right in the Check Constraint expression.
This is what I am starting with in the database
([zip] like '[0-9][0-9][0-9][0-9][0-9]')
and what I want well not exact but this would answer my question
([zip] like '[0-9][0-9][0-9][0-9][0-9] || [A-Z][A-Z][A-Z][A-Z][A-Z]')
Thanks for any help
Hi,
I have two web pages in one web page i have 5 check boxes. For example if the user checks the Checkbox1, checkbox2 and clicks on button.
On the button click I am storing the selected checkboxes value in database lke the following:
Year Options
xxx 1
xxx 2
in the above format( user selectes checbox1, check box 2).
And in the Second Web page I am showing the 5 checkboxes but in this web page I need to check the first and second checkboxes on the page load because user selectes those two check boxes in the first web page.
my select query returning the results like this:
Options
1
2
based on options I have to check those corresponding check boxes in the second web page.
How to achive this.
Thanks in advance
I have a pretty standard form that inserts users name, office, and team. It generates a random 10 digit ID for them. How would i got about checking the table to make sure that ID doesn't exist?
Here's my insert code.
string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnection);
string usercode = GenPassWithCap(9);
String insertCmd = "INSERT into users (ID, firstname, lastname, office, team) values (@id, @firstname, @lastname, @office, @team)"; SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);
myCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar, 10)); myCommand.Parameters["@id"].Value = usercode;
myCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50)); myCommand.Parameters["@firstname"].Value = txtFirstName.Text;
myCommand.Parameters.Add(new SqlParameter("@lastname", SqlDbType.VarChar, 50)); myCommand.Parameters["@lastname"].Value = txtLastName.Text;
myCommand.Parameters.Add(new SqlParameter("@office", SqlDbType.VarChar, 75)); myCommand.Parameters["@office"].Value = dwnOffice.SelectedValue;
myCommand.Parameters.Add(new SqlParameter("@team", SqlDbType.VarChar, 20)); myCommand.Parameters["@team"].Value = dwnTeam.SelectedValue;
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
Do I run a completey different select command before hand and try to match that field?
Hi,
I'm trying to populate a table in one database with details from a identical table in another database. How do I check to see if the row exsits before I insert the data because at the moment I'm getting a violation of a primary key error.my codes something like the below
insert into db2.table1(values)
select *
from db1.table1, db1.table2 where t1.id = t2.id
Cheers Dave
How can I check if the ( SqlCommand ) return empty values
Can some one write code for this, I want know it is return Null values or not
thanx ....
Hi
I have two tables. one MasterDetail and second is countrydetail
In master detail i have MasterDetailId (Primary) and countryId.
In CountryDetail table I have Countrid (Primary),CountryName.
I don't want to give relationship because i can insert null value in countryid in MasterDetail table.
So i have wriiten query like this
-> select c.CountryName,m.MasterDetailId from MasterDetail m,CountryDetail c where c.CountryId=m.CountryId and m.MasterDetailId= '2'
If In MasterDetail table if CountryId is null then it will not show me any record. So I want that record and its value with this query and checking null values. Help me about solving this query.
Thank You
Hi,I have two databases called DB1 and DB2. DB1 has a table called table1 and DB2 has table2.I want to write one SP into the DB1, that SP will check whether table2 into the DB2 is exists or not, how do I do it? Any help?I know if it is into the same database then,IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[table2]') and OBJECTPROPERTY(ID, N'IsTable') = 1)-- then do some thingI tried replacing "dbo.sysobjects" with "DB2.dbo.sysobjects", but no luck.Any Help???
View 2 Replies View Relatedhi, I want to implement a constraint on a talbe for two fields
phone numbers should b (###)###-####
and ss# should be ###-##-####
How can I create such constraint. I tried, but got an error message and could not save the table with the new changes.
Thanks
Ahmed
Can anyone tell me what is wrong with this line of code..
I am missing someting on both line of code
exec ("print 'DBCC INPUTBUFFER FOR SPID " + @spid + "'")
exec ("dbcc inputbuffer (" + @spid + ")")
Hi !
How do I check when my database was last used ? I have two old databases that I think is not in use, but I want to check to make sure before I delete them or take them "offline".
TIA MAttias
How do i create a check constraints on column a so it dose not accept $ character? syntax pls.
Thanks
Is there a way to check if a sql 2000 server was installed on the license of "per server" or "per # concurrent users"?
Can't find from server property.
thanks
-D
Hi,
I am developing a database for my company in SQL server 2000 and I have some
few problems.
Firstly.
I have a customers table and orders table in my DB:
Customers Orders
--------- ------
CustID (primary key) ----------------< CustID
. ^ ProductID
. | Quantity
. | .
. | .
etc. | etc.
|
relationship
(one to many)
What I want to do is:
1) to be able to delete a Customer and automaticaly SQL server delete all the
orders that this customer done from the Orders table.
2) If for some reason the CustomerID changes, SQL should be able to
automaticaly update the necessary fields with the new values in the Orders
table.
Finally, 3) I want to be able to insert a new customer that has an order
and update both the Customers table and Orders table automaticaly. e.g
CustID Name Address ProductID Quantity etc.
------ ---- ------- --------- -------- ----
10-003 John London 33-25 2 ...
Such a kind of insert should add automaticaly the following entries in the
two tables:
Customers Orders
--------- ------
CustID (10-003) CustID (10-003)
Name (John) ProductID (33-25)
Address(London) Quantity (2)
. .
. .
etc. etc.
A friend of mine told me that this can be done using Foreign Check constraints
in SQL server. But I do not know what to do.
Can anybody help me please?
Thank you very much.
Efthymios Kalyviotis
ekalyviotis@comerclub.gr
I have a question regarding the use of check constraints. I see how to set it up to make sure a value entered is one of a list using this syntax:
([eft_vc] = 'No' or [eft_vc] = 'Yes')
However, rather than have to hard code the allowable values, I'd like to have them read off another table. The logic would be as follows:
([eft_vc] in (select * from eft_t))
When I try to put this in, I get a message saying that constraints do not support subqueries. Does anyone know of a way around this?
thank you,
Darell
Hi,
i have following problem:
I have a DB with 4 tables in MSSQL6.5. In those tables write an NT service (4 threads for each table) 24h/day. I want to know somehow when i have no write in one of those tables for more than 1-2 hours (i dont want to querry those tables everytimes). MSSQL6.5 offer this kind of facilities? Or I can do this using stored procedures or something else?
Thank you very much,
Sebastian Bologescu
Can someone help me with this check constraint. I'm trying to get it runned on MS SQL Server but it seems the syntax isn't correct
check((Derived_Val=0) or ((select count(*) from Stage_One where Task=U_Id and Quantity is null)=0))
Thanx
j2dizzo