Sp_start_job Not In The List

I'm not able to find sp_start_job under system stored procedures in msdb. If I just run sp_start_job I get execute permission denied. If I run sp_helptext sp_start_job I get the below error
The object 'sp_start_job' does not exist in database 'msdb' or is invalid for this operation

I am not sure, what is missing, permission or the system procedure itself is not there?


View Replies



I'm using sp_start_job to run a job from a stored procedure. This is fine but I want to know the result ie. success or failure. I'm not having much luck with @output_flag. I can't get it to return anything.

View Replies View Related


I need to give permissions for an user to start a job on the SQL server using msdb.dbo.sp_start_job stored procedure,what kind of permissions she will need on the server? Any help is appreciated.

View Replies View Related

How Does Sp_start_job Works?

I would like to use sp_start_job to execute a SSIS Package but I am not sure how it works. If there are two requests run the job two times simultaneously or sequentially?

View Replies View Related

Sp_start_job And Control Flow

I have a sql 7 scheduled job that has several steps. Each step uses the TSQL msdb..sp_start_job procedure to start a job.

How can I make each step wait for the previous step to complete before starting? I know the sp_start_job does its job and starts a job, but I want to know when the job it started finishes!

Post and email your reply please (mac_turner@hotmail.com)


View Replies View Related

Execute Sp_start_job From Stored Procedure

I need to disable and move orphaned computer objects in my Active Directory. The SQL Agent has permission to do this. I have created a stored procedure for the task with intentions of executing it with sp_start_job. However, I cannot execute it in SQL 2005. How can I grant permission to this (login) to execute sp_start_job?  This is all run from a web page and NOT the Query Window.

View Replies View Related

Sp_start_job Does Not Evaluate "@server_name" Parameter

We want to start a job remotely on another SQL7 server.

innocently we thought that should be no problem with sp_start_job.

We used the command listed below:

use msdb
EXEC sp_start_job @job_name='MyJobName', @server_name='blablabla'

The strange thing is, that sp_start_job ignores the @server_name at all.

Even if we enter complete nonsense for @server_name the job gets started as long it is present on the local sqlserver instance.
sp_start_job seems to search locally for the given job_name, only.
Consequently if we enter the remote server's name the job fails as the
jobname is not present on the local sqlserver.

Any ideas what's going wrong here.

Greetings from Mannheim, Germany

View Replies View Related

Monitor SSIS Job Started With Sp_start_job From .NET App

The following article has a good code sample for starting a SSIS job using SQLCommand from within a .NET application:


This runs a non-local job.

Two problems:

1. - it starts the job, and returns a result code (success or failure), but does not monitor the job and report when it completes.  This is supposedly the task of sp_help_job; however, I have been unable to get this sp to return the execution_status value - it comes back with '0' even when the job is idle (4) or executing (1).  Consequently a timed loop calling this sp never ends, as 0 means 'not idle or disabled'.

Here is the code I used to return the execution_status parameter:

Dim jobConnection As SqlConnection
Dim jobCommand As SqlCommand
Dim jobReturnValue As SqlParameter
Dim jobParameter As SqlParameter
Dim jobResult As Integer = 0
Dim stJob as String = "Test Job"

jobConnection = New SqlConnection(My.Settings.connMaster)
jobCommand = New SqlCommand("sp_help_job", jobConnection)
jobCommand.CommandType = CommandType.StoredProcedure


do while jobResult = 0

jobParameter = New SqlParameter("@job_name", SqlDbType.VarChar)
jobParameter.Direction = ParameterDirection.Input
jobParameter.Value = stJob

jobReturnValue = New SqlParameter("@execution_status", SqlDbType.Int)
jobReturnValue.Direction = ParameterDirection.ReturnValue

jobResult = jobCommand.Parameters("@execution_status").Value



The second problem is that if more than one instance of the job is running, how can the sp_help_job determine which instance it is supposed to monitor?  You would also need the session ID as well as the Job Name.  So how do you get this from sp_start_job, and how do you pass it to sp_help_job?

Right now I am getting by with getting a timestamp from the SQL Agent right before running the sp_start_job, and using this and the job name against the run_requested_date in dbo.sysjobactivity to determine when the stop_execution_date ceases to be NULL (which suggests, but does not confirm, that the job finished):

Dim tmStamp As DateTime

tmStamp = GetTimeStamp(My.Settings.connSysDB)

Dim strSQL As String = " select * from dbo.sysjobactivity sjh inner join sysjobs sj on sj.job_id = sjh.job_id" & _

" where sj.name = '" & stJob & "' and sjh.run_requested_date >= '" & tmStamp & "' and sjh.stop_execution_date IS NULL"

jobResult = GetCount(My.Settings.connSysDB, strSQL)

Public Function GetTimeStamp(ByVal strConnection As String) As DateTime

Dim x As DateTime
Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset

RS = New ADODB.Recordset
Conn = New ADODB.Connection
Conn.ConnectionString = strConnection


RS.ActiveConnection = Conn

RS.Open("SELECT CURRENT_TIMESTAMP as 'timestamp'", Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)

If RS.RecordCount > 0 Then
x = RS.Fields(0).Value
End If


Return x

End Function

This is not a great solution because it does not know what instance, if there is more than one, of the job it is reporting on.

What is a better way of finding out when a specific instance of a specific unscheduled job is finished running?


Steve Jensen

View Replies View Related

Permission Problem Calling The Sp_start_job Proc

Code Snippet
ALTER procedure [dbo].[sp_MyProc]
EXEC msdb.dbo.sp_start_job @job_name = 'MyJob'


I'm trying to write a procedure that calls a job. If I execute it (calling it from adp) as a user that is not db_owner (I guess), I get the error: The Execute permission was denied on the object sp_start_job, database 'msdb', schema 'dbo'.
How can I resolve this problem?

View Replies View Related

Getting “EXECUTE Permission Denied On Object 'sp_start_job'� From Activation Procedure.

Hi guys, please see if you can help me with this...
I have an activation stored procedure that starts a SQL Agent Job which executes a SSIS package.  However when the stored procedure runs it fails with the error EXECUTE permission denied on object 'sp_start_job'. The message queue was created under the €˜sa€™ account, and I have tried setting the activation procedure to run as SELF, OWNER as well as creating a user account with sysadmin rights and running it under that account, all with the same  result.  When I run the stored procedure manually (under pretty much any of the accounts I have set up) it executes without any errors and kicks off the job it is meant to.  The error only occurs when the stored procedure is activated via the service broker message queue. 
I changed the stored proc to write out system_user and current_user to a table so that I could see what it was running as and as it turns out it appears to be running as the correct user (which is €˜sa€™ when set to SELF) but not inheriting the correct permissions.
Is this a bug, and if so is there some work-around for it?

View Replies View Related

Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)


I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for.  I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom).  I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?

View Replies View Related

If You Can Export List Of Users In Server Could You Import This List

Hello everybody.
Sql server has option to export list of users from Sql server to text file
Could we use this file to import users to specific database using T-Sql or
Enterprise Manager ?


View Replies View Related

Insert Value List Doest Not Match Column List



I need to do a simple task but it's difficult to a newbie on ssis..


i have two tables...


first one has an identity column and the second has fk to the first...


to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!


i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"


here is the script


INSERT INTO CustomerAddress(
TypeDescription) VALUES(


what's the problem ??

View Replies View Related

Select List Contains More Items Than Insert List

I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?

Insert Query:
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,

CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier

tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)

View Replies View Related

Insert Only New Items From A List But Get ID's For New And Existing In The List.

I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words.  But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)

Code Snippet
CREATE TABLE [dbo].[Tokens](
 [TokenID] [int] IDENTITY(1,1) NOT NULL,
 [Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [TokenID] ASC

 [Token] ASC
CREATE FUNCTION [dbo].[SplitTokenList]
 @TokenList varchar(max)
@ParsedList table
 Token varchar(255)
 DECLARE @Token varchar(50), @Pos int
 SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
 SET @Pos = CHARINDEX(',', @TokenList , 1)
 IF REPLACE(@TokenList , ',', '') <> ''
  WHILE @Pos > 0
   SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
   IF @Token <> ''
    INSERT INTO @ParsedList (Token)
    VALUES (@Token) --Use Appropriate conversion
   SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
   SET @Pos = CHARINDEX(',', @TokenList, 1)
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
 @TokenList varchar(max)
 create table #Tokens (TokenID int default 0, Token varchar(50))
 create clustered index Tind on #T (Token)
 DECLARE @NewTokens table
  TokenID int default 0,
  Token varchar(50)

 --Split ID's into a table
 INSERT INTO #Tokens(Token)
 SELECT Token FROM SplitTokenList(@TokenList)
 --get ID's for any existing tokens
 UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0) 
 FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token

 INSERT INTO Tokens(Token)

 return the list with id for new and old
 SELECT TokenID, Token FROM #Tokens
 WHERE TokenID <> 0
 SELECT TokenID, Token FROM @Tokens
 DECLARE @er nvarchar(max)
 RAISERROR(@er, 14,1);


View Replies View Related

ERROR: A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List.

I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.

From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername" 

Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)

I "sometimes" get the following error:

OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Help would be appreciated!

I have seen other posts on this but, not able to relate the solution to my scenario.

View Replies View Related

A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List

Hi All,


I have seen a few other people have this error.

Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)

The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")

A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.

I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.


The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.

Any ideas?





Error log:

OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.

OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.

OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.

OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.

OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.

OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified.  Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.

OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified.  Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.

OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified.  Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.

OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified.  Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.

OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".

OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".

OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".

OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".


View Replies View Related

Items In List A That Don't Appear In List B (was &"Simple Query...I Think&")

Ok, I want to write a stored procedure / query that says the following:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true

In pseudo-SQL, I want to write a clause like this


(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
Return True

Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.

I may have to achieve this with some kind of logical loop but I don't know how to do that.

Can anyone help?

View Replies View Related

SELECT WHERE (any Value In Comma Delimited List) IN (comma Delimited List)

I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist.

Here is an approach I am trying:

TABLE Events(EventID int, Categories varchar(200))

EventID Catetories
1           ‘6,8,9’
2           ‘2,3’

PROCEDURE ListFilteredEvents
   @FilterList varchar(200)    -- contains ‘3,5’
WHERE (any value in Categories) IN @FilterList



How can I select all records where any value in the Categories column
matches a value in @FilterList. In this example, record 2 would be
selected since it belongs to category 3, which is also in @FilterList.

I’ve looked at the table of numbers approach, which works when
selecting records where a column value is in the parameter list, but I
can’t see how to make this work when the column itself also contains a
comma delimited list.

Can someone suggest an approach?

Any examples would be greatly appreciated!

View Replies View Related

Run Down A List In SQL

I have tilted and need some help.

I have a table with columns:

ArtNo, WareNo, State + a few others

Sample data:

111, 1, 3
111, 2, null
111, 4, 10
222, 1, 8
222, 2, 3
222, 1, 3
and so on

Each artNo is in several WareNo and has a state
Each WareNo has several ArtNo

I want to run through the table and get * for each combination of ArtNo&WareNo that does not have any post in the table with State = 3.

Best regards


View Replies View Related

List() UDF

Amol writes "I wish to write a UDF that will concatenate values from each row of a column for a select query. for each invocation of the UDF it should check the callee query to see if its the next row of the current query and concatenate the value or if its a new query start from null.

eg. : select list(name) from employee

will return a "single" list of all names in the employee table as a comma delimited string.

TIA, Amol."

View Replies View Related

List In List?

Hello everybody,
I am new to the whole reporting World and I was wondering if their is a solution for my problem. I searched for one and a half hour and I am getting desperate.
I need to repeat a report. So I thought: I can use a list for that. But when I placed all my objects in that list and linked it to a data source it gives an error at every object. I had this problem before and I made a work around so I did not solve it. I need a list in a list or a matrix in a list. I get the following errors:
Error    1          [rsDataRegionInDetailList] The list €˜list3€™ is in a list that has no group expressions defined for it.  To use a data region in a list, the list must have group expressions.         c:projectencitrix estcitrixusagedb
eportCitrixUsage.rdl            0          0         
Error    2          [rsFieldReference] The Value expression for the textbox €˜textbox16€™ refers to the field €˜USERNAME€™.  Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.        c:projectencitrix estcitrixusagedb
eportCitrixUsage.rdl       0          0         
I hope you can help me, thanks in advance!

View Replies View Related

Select Where 'in' A List

I have done queries before where you have something like this:
 Select name
From people
Where uid in (Select UID from employees where salary > 500000)
 as an example.
I have to use stored procedures, I can't connect directly...
I have a datatable of records that I select from a database. In the database table there is a flag 'checkedOut' (bit field) wich I want to set to true (1) for each of these records.
Since I have to use stored procedures I think I am limited to passing in parameters. I wrote a function using a stringbuilder to concatonate the key fields into a comma delimited list "1, 55, 98" etc. and thought to use it as the clause in the 'in' sample ie:Update ServiceRequests set
CheckedOut = 1
Where UID in (@UIDList)

 I have tried a few different things, none works.
Any ideas how you might accomplish this with stored procedures?
The records are passed to a thread to be dealt with and it's possible the next time it runs it will pick up the same records for processing if the first thread isn't through. I needed the 'CheckedOut' to ensure I don't accidentally do that.

View Replies View Related

How Can I Get A List Of The Last Payments

How can I get a list of the last payment for each client?
SELECT ClientID,PaymentDate,PaymentAmount FROM tblClients INNER JOIN tblPayments ON tblPayments.ClientID = tblClients.ClientID

View Replies View Related

List Of Countries

Hello All,
I need to download the table containing the list of the database in sql server 2005, where can this be downloaded

View Replies View Related

How I Can Get List Of Tables?

Hi friends,
How I can get list of tables and list of fields within those tables in SQL server.
Thnak a lot.

View Replies View Related

Please Help Me About List Of Table

i need to give list all table in my database plaese give me qurity?
thanks a lot

View Replies View Related

How Do I Get A List Of Tables In T-SQL

Is there anything equivalent to Oracle's Select * from tab in MS SQL.

View Replies View Related

Matching On From A List


say I have a list from an sql statement (results list)
this list contains 10 items

In another table, in one particular column - there is a match for one of these items from the initial list.

SO... this may be the list

in the other table there is a match...
but just for one item on that list.
3 <------ match

How do I find that match with my sql statement?

View Replies View Related

List Of Logins

I want to retrieve the list of all logins in the sql server. can anyonehelp me in that?

View Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved