How To Extract @execution_status From Sp_help_job
Sep 6, 2007Hello,
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
ADVERTISEMENT
Check Execution_status In Sp_help_job
Nov 7, 2007Hi 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
Help Using Sp_help_job
Oct 5, 2007
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.
Sp_help_job Stored Procedure
May 29, 2007Is 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
Sp_help_job (SQLServer 2000)
Mar 13, 2008exec 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?
Very Critical Sp_help_job Questions And What Is Targetserverrole ?
Feb 7, 2008
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.
Extract A Value From XML
Feb 17, 2015I have the following xml:
<MachineRepostitoryDS xmlns="http://Testing.com/Deployment/MachineRepostitoryDS.xsd">
<Components>
<Environment>QA400</Environment>
<HostName>HostTesting</HostName>
<Component>EventProcessorService</Component>
<CompStatus>OK. 17 Testing assemblies found</CompStatus>
<Version>4.0.24.0</Version>
<Path>D:TestingEventProcessor.WinService</Path>
</Components>
I would like to extract the version value from it (in the case above it's- 4.0.24.0).
Hoe Do I Extract?
Mar 15, 2007Hello All,
I have a table that has the first name and last name together in one field (stupid I know). I want to extract the first name from the field and copy it into another field names, well, firstname. I figure it might be possible since there is a comma after the last name. But how can I do this?
So for:
STRNAME
-------------
adams, john
-------------
washington, michael
-------------
I want it to be like this
STRNAME | STRFIRSTNAME
---------------------------
adams | john
---------------------------
washington | michael
So I guess there would be two parts to the statement:
1. First I need to select or trim the strname field to the comma. BUT I don't want to just trim it- I want to copy it.
2. The next thing I need to do is to copy the lastname (the part that I trimmed) into a new field which I created called lastname.
Any help would be greatly appreciated!
Extract First Name From Last Name
Nov 14, 2007I posted this a while ago with no luck and I'm going to try it again:
I have a column called strlastname with contains the firstname and last name of a client. I want to separate out the first name and last name into into two separate fields. I figured maybe I can do it easier if I copied the column into the table. So now I have a column called strfirstname that contains the first name and the last name and a colmn called strlastname that contains the first name and last name. The lastname is seperated from the first name with a comma like this:
doe, john
any guesses?
How To Extract One Name?
Apr 17, 2008
Hello I have seperated 1 name 2 name and 3 names. useing conditional split searching spaces in between names.
but while taking only one name in dervied colum what should i enter to get it.
i tried the same expression that i used in 2 namees and 3 names for first_name.
but its giving me error is there adifferent way to get one name.
le tme know
Extract INT From String?
Feb 19, 2014I have a table which has important information stored within a text field as nText. The fields look like this
{B:P:8}
{B:D:18}
{A:P:821}
{E:D:38}
{A:D:9}
{B:D:18}
The integer after the last colon & before the curly brace is the one I need to extract. Is there a way to do this in SQL?
I tried this at first, but the problem is that it's not very tolerant to the varying lengths of the integer.
,RIGHT(LEFT(CAST(bN.Note AS NVARCHAR(50)),6),2)
I use Microsoft SQL 2008
How To Extract Row Corresponding To First Occurrence Of ID
Mar 4, 2014I have a sample table as follows:
IDITEM_EFFECTIVE_DTITEM_TERMAMOUNT
106738118-Jan-142141-200
106738102-Feb-142141-274.82
108766810-Jan-1421411238
108766810-Jan-142141-1238
108766810-Jan-142141309.5
108766810-Jan-142141-309.5
109754020-Jan-142141-3814.16
109754025-Feb-142141-100
110337727-Feb-142141-200.6
110337731-Jan-142141-19.24
I need to find rows from the above table which has the first occurence of the corresponding ID. This the output will contain the rows that will have first occurence of the following ids.
1067381, 1087668, 109754, 1103377.
How does one handle this. I have not been able to come up with the sql yet.
Extract Data From DB2
Jan 22, 2008I have a package which establishes connection with DB2 server.I dont have any db2 application where i can format query for db2.This is my query in access/sql.
can someone help me converting it into a db2 query.i tryed to google and use the functions but i failed and even the error that ssis gives don't help asthey are same for any type of error.
Thanks in ADV
SELECT table1.YYYY & table1.MM as MO_YR,
table2.CNTRYCD AS CNTRY_CD,
Sum(table1.AMT) AS [VALUE]
FROM table1
INNER JOIN table2
ON (table1.MM = table2.MM)
AND (table1.YYYY = table2.YYYY)
WHERE (table1.YYYY BETWEEN YEAR(DATE()) AND YEAR(DATEADD("m",-3,DATE())))
AND (table1.MM BETWEEN MONTH(DATE()) AND MONTH(DATEADD("m",-3,DATE())))
GROUP BY table2.CNTRYCD, table1.YYYY, table1.MM
Extract SQL Queries
Feb 6, 2008Hi All,
I am looking to extract all the queries from a table in sql to either excel or office.
I have used SELECT Qstring FROM OUQR to list them but now i have to copy each one individually and the paste them to another document.
Is there a better way to go about doing this....
Thanks for the help....
Cheers
Extract Data From SQL 6.5
Jul 23, 2005I need to get some data from an enormous, creaky old SQL 6.5 database.I know nothing about either the data schema (though I believe some sortof documentation exists), nor 6.5 for that matter, having come to SQLServer at 7.0.My clients need the data in comma delimited format.Please, can anyone suggest any possibilities? One thing that occurredto me might be to create an Access application, use an ODBC link to theSQL DB, and then leverage Access' not inconsiderable functionality toget the data out.Does anyone foresee any problems with this, or any better ways?Forever in your debt.Edward--The reading group's reading group:http://www.bookgroup.org.uk
View 2 Replies View RelatedExtract Data
Jan 10, 2008I have the following data example,
Street
Forest Ridge Dr
Whites St
Alba Rd
I wish to achieve the following,
Street StreetSuffix
Forest Ridge Dr
Whites St
Alba Rd
I have the following code
INSERT Addr2 (AddressID, LotNo, FlatNumber, HouseNum, Street, StreetSuffix, Locality,
PostCode, [State])
SELECT LotNo, FlatNumber, HouseNum, SUBSTRING(Street, 1, (PATINDEX('% %', Street))),
LTRIM(SUBSTRING(Street, (PATINDEX('% %', Street)), 20)), Locality, PostCode, [State]
FROM Addr1
But i get the following result which is no good!
Street StreetSuffix
Forest Forest
Whites Whites
Alba Alba
What am I doing wrong in the Street & StreetSuffix Functions above?
I have this working with a cursor but I'm trying to work out a set based solution as the cursor
takes way to long to complete.
In the Cursor I'm performing the code below per row of AddressID
SET @SpaceLength = (SELECT PATINDEX('% %', @Street))
SET @Suffix = (SELECT SUBSTRING(@Street, @SpaceLength, 20))
SET @Street = (SELECT SUBSTRING(@Street, 1, @SpaceLength))
Extract Value From A Column That Contains XML?
Aug 4, 2015'm trying to get the value from a column in a SQL Server table that contains XML but the type of the column is not XML it is TEXT.
I tried this:
SELECT
[Id],
[Request]
FROM
[Tracker]
WHERE
[Request].value('/Credit[1]/Loan[1]/LoanApp[1]/Applicant[1]/Personal[1]/Individals[1]/Individual[1]/GivenName[1]/FirstName[1]', 'nvarchar(50)') = 'Tom'
but I get this error :
Cannot find either column "Request" or the user-defined function or aggregate "Request.value", or the name is ambiguous.
I tried to cast the column like this:
select
CAST(CAST(Request AS NTEXT) AS XML).value('(/Credit[1]/Loan[1]/LoanApp[1]/Applicant[1]/Personal[1]/Individuals[1]/Individual[1]/GivenName[1]/FirstName[1]', 'nvarchar(50)')
from Tracker but with this I get this error:
XML parsing: line 1, character 15, A string literal was expected
HOW TO EXTRACT THE FOURTH NAME
Apr 15, 2008
Hello,
How do we extract the FOURTH STRING OF NAME FROM A FULL NAME SEPERATED BY COMMAS
John, Baker, Mike, Joe
FIRST NAME --- SUBSTRING(AuthRequestor,1,FINDSTRING(AuthRequestor," ",1) - 1)
SECOND/MIDDLE---- SUBSTRING(AuthRequestor,FINDSTRING(AuthRequestor," ",1),FINDSTRING(AuthRequestor," ",2) - FINDSTRING(AuthRequestor," ",1))
LAST NAME---- SUBSTRING(AuthRequestor,FINDSTRING(AuthRequestor," ",2),LEN(AuthRequestor))
HOW TO GET FOURTH NAME---- ?????? JOE
How To Extract Zip File
May 8, 2007Is there a way to extract the zip file and use the textfile within it to transform data.
Please let me know
How To Extract Rule
Nov 3, 2006Hi,
How do I extract rules and it's value from a database?
I can extract the rules through view(sys.objects) but where can I get it's content?
Regards
Marcelo Gamba
Extract SQL Select Statement
Nov 29, 2006Hi,
View 1 Replies View RelatedExtract The Month Between Two Given Dates
Dec 4, 2007hii all
i am havie a database with given fileds like projectid,projectcode,projectname,startdat,enddate,etc....
my problem is to show all months and year between each projects startdate and enddate in a datagrd..and the table dont have month, year field..
i need to use sql quiery..
if anybody can help me in this matter.. it will be greatful..
thanks in advance..binoy
Data Extract Question
Aug 5, 2005Hi, I'm hoping someone has an idea or two on this topic.
Basically I have three tables of data say tContact, tQuestion, tAnswer
tContact
-----------
ContactID
Email
Name
tQuestion
------------
QuestionID
Question
tAnswer
------------
QuestionID
ContactID
Answer
I need to extract the data for the client and they would like to see
the data with one line per contact, but showing every answer to every
question... they would like the data formatted like this:
ContactID, Email, Name, Question1Answer, Question2Answer, Question3Answer, Question4Answer, etc........
Obviously to get the data I cansimply do an outerjoin to get all
contact data then all questions, and answers that exist... but that
will obviously return tabular data with one row per each
answer... Does anyone have any ideas on how to do this using just
SQL? I can pull the data and write a function that spits it out
to text using the Stringbuilder class and some logic, but I'm thinking
this must be possible in SQL natively... any help would be more than
appreciated. Thanks in advance.
-e
Extract Value From Middle Of String
Sep 9, 2005Lets say I have a column of type varchar and need to extract an integer value from the middle of it. The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?
View 2 Replies View RelatedBest Way To Extract Part Of A Db And Deploy It
Sep 24, 2005Hi,I'm using MSSQL2k and I have some customers in a customer table. I want to write a webapp and offer them the possibility to teach them using the app while having telephone contact with them. They can use the app on my server for some time and add data to it.After a while when someone wants to buy the app I would like to distribute the app as an Intranet app to their own server and let them have the data they already have wrote into the db on my server.Because there will be some customers with much data I'd like some guidance in how to plan the database to easily extract their own data and create a new db polutated with their data.Kenneth
View 1 Replies View RelatedExtract Value From A Lookup Table
Feb 23, 2006Hi,
I'm inserting records into a Table1 with the following fields :
tabID int 4ClientID int 4..etc etc.
In the insertitem template, I want the user to enter a Clientcode into textbox1. Then I want that client code validated against a lookup table, ClientTable and if valid, get the ClientId value from that ClientTable, so that I can populate the ClientID field in Table1.
I've got a mental block and I cannot see how I can do that !
Any ideas most gratefully appreciated.
Thanks
Sunil
Anyone Know How To Extract Datetime From Timestamp?
May 8, 2007Anyone know how to convert a timestamp value to datetime that I can read?
When I run this:
Code:
select * from trans
where convert(datetime , time_stamp) >= getdate()-1
--'time_stamp' is the name of the column
I get:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
(0 row(s) affected)
How To Extract Data After The Comma
Oct 5, 2005hi there
i have a field name(fil_srt_cond) with the values of
cmpnt_name,ASC,1,2
cmpnt_stuff,DESC,2,3
i used the char index:-
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +1 ,3) ord,
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +5 ,1) len,
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +7 ,1) str
to display them in their specific fields like
NAME STAR_CHAR LENGTH ORDER
Tag 1 2 ASC
however for cmpnt_stuff,DESC,2,3 i'm getting
NAME STAR_CHAR LENGTH ORDER
Stuff , , DES
therefore i'm not getting the required values 2 and 3.
can u pls help me to find a way how to get the data after the comma. thank you in advance
How To Extract Only 1 Record For Each Staff?
Oct 30, 2005Hi,
I have more than 1 record for each staff. This is the sample of record,
StaffNo Field1 Field2 Field3
50424 Data1 Data2 Data3
80336 Data1 Data2 Data3
50424 Data4 Data5 Data6
50424 Data7 Data8 Data9
80336 Data4 Data5 Data6
Is it possible for me to extract only 1 record for each staff? The result should be like this..
StaffNo Field1 Field2 Field3
50424 Data1 Data2 Data3
80336 Data1 Data2 Data3
Any idea? I used DISTINCT in sql statement but it's not working because the record is unique.
Thanx in advance..
Extract And Compare Hour
Jan 22, 2007Hi all:
As I can extract the hour values and minute of a field of type datetime to compare it with the values of a field of type smalldatetime of another table
Thanks.:confused:
SQL Query To Extract DB2data
Jan 23, 2008Hi i have a query
select 12 * int( "SQLUSER"."BTT120".yyyy) + int("SQLUSER"."BTT120".mm) as actual,
12*year(CURRENT DATE) + month(CURRENT DATE) as ending,
12*year(CURRENT DATE) + month(CURRENT DATE)-3 as starting
from "SQLUSER"."BTT120"
where "SQLUSER"."BTT120".yyyy ='2007' and "SQLUSER"."BTT120".mm in ('10','11','12')
which gives me ourput
Actual ending Starting
24094 24097 24094
My actual requirment is to get the data for the rolling 3 months for which i used the same logic as above in the WHERE clause.
SELECT "SQLUSER"."BTT120".YYYY CONCAT "SQLUSER"."BTT120".MM AS MO_YR
FROM "SQLUSER"."BTT120"
where (12 * int( "SQLUSER"."BTT120".yyyy)) + int("SQLUSER"."BTT120".mm) between
(12*year(CURRENT DATE)) + month(CURRENT DATE)-3 and
(12*year(CURRENT DATE)) + month(CURRENT DATE)
everything looks good but iam not getting output, there is data for the date range
(12 * int( "SQLUSER"."BTT120".yyyy)) + int("SQLUSER"."BTT120".mm) = 24094
(12*year(CURRENT DATE)) + month(CURRENT DATE)-3 = 24094
(12*year(CURRENT DATE)) + month(CURRENT DATE) = 24097
can anyone help me if iam wrong in syntax. new to DB2
Thanks
How To Extract Content From Email
Feb 22, 2012I want some way to extract email content from email that we send/receive in account.....
This question is in context to Text Analytic
Extract Substring In Statement
Feb 12, 2015I tried to extract the information in a field
Samples of the data:
"Current Date updated ( 12/31/2015 -> 12/31/2016 )"
"Status updated ( Open -> Should be Closed )"
From example 1 ==> need outputs of " 12/13/2015" and "12/31/2016";
example 2 --> need outputs of "Open" and "Should be Closed"
These are what all records look like:
1. It contains the word, "updated".
2. There is a space after the last character of updated. Then, there is a space before open parenthesis. Then, there is a space before getting the required word. Example: "updated ( xxxxx" (xxxxx is a required extract word.)
3. The entire word(s) should be captured before the "->" sign (after the open parenthesis)
4. The last part is to captured everything after the "->" but before the close parenthesis ")"