How To Check Stored Procedure Status
Dec 18, 2003How to check status(valid/invalid) for all stored procedure in my DB...
View 14 Replies
ADVERTISEMENT
Stored Procedure Status Suspend
May 23, 2007Hello,
A call to a stored procedure via the SQLCommand.ExecuteReader() is made. When the stored procedure goes into suspend state the ExecuteReader() call returns. The stored procedure is not yet finished, after the suspend state it goes into the runnable state and performs some calculations.
Is it possible to set a configuration that the ExecuteReader doesn't return when the stored procedure goes into suspend, or must an other method be used?
Tx
Stored Procedure Execution Status
Dec 2, 2005Hi. When SqlServer executes a procedure (any type: select, update, insert) after it´s executed can I get a default status for this executed procedure, like a return bool value from SqlServer as true for successfull and false for failed to execute?
»»» Ken.A
Long Running Stored Procedure Status
Mar 24, 2008I have a stored procedure in SQL 2005 that purges data, and may take a few minutes to run. I'd like to report back to the client with status messages as the sp executes, using PRINT statements or something similar. I imagine something similar to BACKUP DATABASE, where it reports on percentage complete as the backup is executing.
I can't seem to find any information on how to do this. All posts on this subject state that it's not possible; that PRINT data is returned after the procedure executes. However it would seem possible since BACKUP DATABASE, for example, does this.
Is there any way to send status type messages to the client as the sp is executing??
Thanks.
How To Check Whether Stored Procedure Is Being Used
Apr 14, 2008Hi,
I'm using some security scanning software which looks for vulnerabilities in the database. It tells me that some stored procedure are should not be given public permission.
How do i know whether the stored procedures are being used by someone or last used on which date? Is there any way to find out?
At the same time, how do i check the permission of stored procedure on ms sql 2005? Thanks
How To Check Stored Procedure?
May 2, 2004Hello, everyone:
I have some stored procedures that run on SQL2K(SP3) /WIN2K. Sometimes I modify them using ALTER PROCEDURE statements. How to check if they are changed after ALTER statements run? Thanks.
ZYT
Stored Procedure To Check Different Criterias
Aug 6, 2007I've a stored procedure which retrieves based on different criterias. I added one more critieria - to display a column based on a range of values. The values are @OriginalMin and @OriginalMax. I declared the variables and gave the conditions. But still set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[USP_Account_Search_Mod]@OriginalMin DECIMAL=0
,@OriginalMax DECIMAL=0
AS
DECLARE
@CRI8 VARCHAR(500)SELECT
@CRI1=''
SET @CRI8='AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax'
SELECT @Criteria = ......+ @CRI8
When I execute this stored procedure, I get the following error message.
SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMax UNION SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(3)) AND Customer = '00001'AND OriginalBalance >=@OriginalMin AND OriginalBalance<=@OriginalMaxORDER BY NAME ASC
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OriginalMin'.
Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OriginalMin
Could someone tell what's wrong with the procedure? For convenience, I've included only the latest critieria I added.
How To Check For Nulls With-in A Stored Procedure
May 24, 2008I have a Stored Procedure as followsUSE [MyDataBase]
GO
/****** Object: StoredProcedure [dbo].[SPLogins] Script Date: 05/24/2008 21:58:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SPLogins]
(
@LoginName varchar(50), @LoginD int output,
)
AS
Select @LoginID = LoginID From MyDatabase Where (LoginName Is not Null) and @LoginName = LoginName
I am trying to check for a null value in the Stored Procedure. I dont' get an error, but it doesn't catch the "null" and gives an error when there is a null value
What Is the correct wayt to go about it.
Check If A Stored Procedure Exists
Mar 14, 2007Hi All.
Can anyone tell me what the syntax to check and see if a stored procedure exists and if it doesnt to create it is?
Thanks people.
Stored Procedure To Check Date Range
Nov 10, 2006Hi guys,I have written a stored procedure to check for date range, say if the user enters a value for 'city-from' , 'city-to', 'start-date' and end-date, this stored procedure should verify these 2 dates against the dates stored in the database. If these 2 dates had already existed for the cities that they input, the stored procedure should return 1 for the PIsExists parameter. Below's how I constructed the queries: 1 ALTER PROCEDURE dbo.DateCheck
2 @PID INTEGER = -1 OUTPUT,
3 @PCityFrom Char(3) = '',
4 @PCityTo Char(3) = '',
5 @PDateFrom DATETIME = '31 Dec 9999',
6 @PDateTo DATETIME = '31 Dec 9999',
7 @PIsExists BIT = 1 OUTPUT
8 AS
9
10 CREATE TABLE #TmpControlRequst
11 (
12 IDINTEGER,
13 IsExistsCHAR(1)
14 )
15 /*###Pseudo
16 1. Check the Date From and Date To
17 -- select all the value equal to parameter cityFrom and cityTo
18 -- insert the selection records into tmp table
19 --*/
20 INSERT INTO #TmpControlRequst
21 (ID, IsExists)
22 SELECT ID,
23 IsExists = CASE WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
24 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
25 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
26 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
27 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
28 AND @PDateTo >= DateFrom AND @PDateTo <= DateTo THEN 1
29 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
30 AND @PDateFrom >= DateFrom AND @PDateFrom <= DateTo
31 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
32 WHEN DateFrom <> '31 Dec 9999' AND DateTo <> '31 Dec 9999'
33 AND @PDateFrom <= DateFrom AND @PDateFrom <= DateTo
34 AND @PDateTo >= DateFrom AND @PDateTo >= DateTo THEN 1
35 ELSE 0 END
36 FROM RequestTable
37 WHERE ID <> @PID
38 AND CityFrom = @PCityFrom
39 AND CityTo = @PCityTo
40
41 --======== FINAL RESULT
42 -- For tmp table:-
43 -- isExists = 1 ==> date lapse
44 -- isExists = 0 ==> date ok
45 -- if count for (isExists = 1) in tmp table is > 0 then return 1 and data not allow for posting
46 SELECT @PIsExists = CASE WHEN COUNT(*) > 0 THEN 1
47 ELSE 0 END
48 FROM #TmpControlRequst
49 WHEREIsExists = 1
50
51 SELECT @PIsExists
52 --=========
53
54 DROP TABLE #TmpControlRequst
55
56 --=========
57 RETURN(0)However, when I run this stored procedure, 'PIsExists' would always return -1. I am positive that the values that I passed in, had already existed in the database. Any idea what might be causing this problem? Thanks in advance
Select And Check A Boolean Value With Stored Procedure?
Apr 10, 2008Hi all,How do you select and check a boolean value within a stored procedure? I don't know if this is correct or not:DECLARE @CheckStatus bitSELECT @CheckStatus = PREF_STATUS FROM tblPrefsWHERE [PREF_ID] = @PREF_IDIF (@CheckStatus IS FALSE) -- DO SOMETHINGIF (@CheckStatus IS TRUE) -- DO SOMETHING But I'm not sure if what I've used (IS FALSE/TRUE) is the correct way? "PREF_STATUS" is a bit column in my table.Thanks.
View 4 Replies View RelatedSQL 2012 :: Check If Particular Stored Procedure Is Recompiling Or Not
Mar 23, 2014I want to know, Is it possible to check a particular stored procedure is recompiling or not?
View 7 Replies View RelatedHow To Check When A Stored Procedure Was Last Called/executed
Jul 23, 2005HiOur SQL server has a lot of stored procedures and we want to get somecleaning up to be done. We want to delete the ones that have been notrun for like 2-3 months. How exactly will i find out which ones todelete. Enterprise manager only seesm to give the "Create Date"How exactly can I find the last called date ! I guess you could write aquery for that ! but how ???P.S I dont want to run a trace for 1 months and see what storedprocedures are not being used.
View 7 Replies View RelatedQuery - Check For A String In Stored Procedure
Jul 23, 2005Hi,I would like to check if a string value exist in a string in sqlserver stored procedure, e.g.set @testString = 'this is my test document.'if (@testString contains 'test')begin.....endHow do I do this in sql server stored procedure?Thanks,June...
View 1 Replies View RelatedCheck The Status Of SQL Agent
Jan 3, 2007Hi all
in my project, I need to access SQL job to finish something. But sometimes, the status of SQL Agent is not running, which needs me to check the status first. I am wonder are there some functions or some ways to check the status. If you know, please response me.
I appreciate your response !
Check Query Status
Sep 26, 2007Is it possible to create an SSIS package that checks for a running Query on my SQL db?
I need to some how check my SQL server and see if there is a query running, if its running I need to set an indicator in my table for my app. This job needs to be scheduled and run nightly (which I can do). But how can I query SQL and see if the query is still running?
Check File Status With Datetime?
Mar 25, 2008We have a really annoying job here that relies on a particular file to be created before several imports run. An old file may already exist, but if it isn't recent, we don't want the import to run. This job can't delete it, since other jobs use that file. What we'd like to do is to be able to check the creation date of the file, and if it is after a certain time of day, run the import, else, delete the file. I know of xp_fileexists. Is there anything similar in SQL that can return file information or am I stuck parsing the output from xp_cmdshell 'dir F:ftpcoreinputready.txt'? Any help or hints are appreciated. Let me know if you need more info. Thanks. -D.
View 14 Replies View RelatedCheck SQL Import Data Status/log
Mar 1, 2008So far, I only deal with Import & Export data from one server to another.
Earlier today, I initiated a table import, from SQL Server AAA to SQL Server BBB.
I can access Windows Server BBB (and SQL Server BBB) from Computer CCC through Remote Desktop.
My question: Is there any way to check if the import was successful or not, from another computer? Is there a log that I can see? I open the Management>>SQL Server Log, but the import/export is not recorded.
I can see the new table that I imported on Server BBB, but not sure if the import was successful or stopped in the middle since I initiated the import from Computer/Server AAA (I don't have access to Server AAA at this moment). I guess, I need to see the time when it's all done/completely imported. I checked the table properties, but it listed the time of creation. I don't see the time of transfer completed.
Please help,
Thanks.
Using SQL Server 2005 / Windows Server 2003.
How To Check Status Of Incremental Population?
Jun 1, 2008Hi,
I have a full text index created on a table with PK, text column and timestamp column. The table has 10 million rows. I tried one time full population and CPU spiked so after couple of hours i stopped full population.
Now since i have a timestamp column in the table I want to do a incremental population.
But when I run a select
SELECT * FROM sys.fulltext_indexes
The incremental_timestamp column is showing value 0x0000000000000000
How do I find how long will it take for incremental population to complete?
Thanks,
Yogesh
Insert Stored Procedure With Error Check And Transaction Function
Jul 21, 2004Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?
thanks
My contract table DDL:
************************************************** ***
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);
My insert stored procedure is:
************************************************** *****
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as
if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end
if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’
commit transaction
return 0
go
T-SQL Code To Check The Status Of Sql Job On SQL Server 2000
Jan 7, 2008How to query the database to see if a job is still running?
View 1 Replies View RelatedSQL Server 2008 :: Check DB Mail Status On All Servers
Apr 23, 2015i have 70 SQL database servers and i setup DB Mail on the 70 Servers, i want to know is there a way to find the status of all the jobs which i assigned the DB Mail and if its working/failing... is there a script i can run on powershell or SQL to find out that information
View 1 Replies View RelatedSQL Server 2012 :: Check Completion Status Of Recovery?
Aug 14, 2015I have a database In Recovery.
Where do I check the completion status of the recovery?
Multiple-Step Operation Cannot Be Generated Check Each Status Value Error
Jan 22, 2007Hi All,
I have a field 'Rowguid' of type uniqueidentifier in a table. This field is the last field in the table. In this case if I update a record through the application I don't get any error. Suppose if there are additional fields after the field Rowguid I get the error "Multiple-Step operation cannot be generated Check each status value"
For your reference I have used the following statement to add the RowGuid field
Alter table <tablename>
Add RowGuid uniqueidentifier ROWGUIDCOL NOT NULL Default (newid())
Can anyone please help me.
Thanks
Sathesh
How To Check Matrix Row Group's Visiblity Or Expand/Collapse Status In Program
Aug 7, 2007Hi,
How do I programmatically check a row group's Visibility or Expand/Collapse flag in a matrix table? For example, I have a matrix table contains the following groups:
Row groups: Facility --> Category Type --> Category
Column groups --> year, quarter, month
I want to be able to programmatically update the table content if Category rows are not visible (Category Type row group is collapsed).
Thanks.
SouBee
Urgent Multiple-step OLE DB Operation Generated Errors. Check Each OLE DB Status Value
Nov 30, 2007I am using execute sql task and my sql statement contains
EXEC test ?
procedure test has 2 parameters. I added parameters using parametermapping tab. When I run i get an error message
Multiple-step OLE DB operation generated errors. Check each OLE DB status value
Export From Excel To SQL.. Multiple-step OLE DB Operation Generated Errors. Check Each OLE DB Status Value, If Available.
Nov 9, 2007Hi all,
We have Windows 2003 64 sp2 Xeon, 2005 EE SP2 64 bit...
Trying to do conversion from DTS sql 2000..One package use load from excel to sql..So I tried to create same thing by myself.. Hell, so many issues.. So I used wizard, package created, I changed Run64bit to False, tried to run package, once - completed in debug mode.. Now it's time to create deployment utility and deploy package..During execution of manifest file got error:
Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
In BIDS, open up solution and tried to rerun package again - no way,".. cannot acquire connection from connection manager blah blah blah.."
Even tried to fire package without debugging, it fires 32 bid execution utility, so no question about 64 bit mode.. package failed..
Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785}
Message: ExternalRequest_post: 'IDataInitialize::GetDataSource failed'. The
external request has completed.
Start Time: 2007-11-09 09:41:25
End Time: 2007-11-09 09:41:25
End Log
Error: 2007-11-09 09:41:25.95
Code: 0xC0202009
Source: Package_name loader Connection manager "SourceConnectionExcel"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H
result: 0x80040E21 Description: "Multiple-step OLE DB operation generated error
s. Check each OLE DB status value, if available. No work was done.".
End Error
Log:
Name: OnError
Source Name: Data Flow Task
Source GUID: {2A373E56-8AAF-40E9-B9EF-4B2BB40175F0}
Execution GUID: {CE11CF95-A25E-4285-A8B0-9E28E51A6785}
Message: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
. The AcquireConnection method call to the connection manager "SourceConnection
Excel" failed with error code 0xC0202009. There may be error messages posted be
fore this with more information on why the AcquireConnection method call failed.
Any help..?
(Item Lookup Error! Hr=80040e21, HrDesc=Multiple-step OLE DB Operation Generated Errors. Check Each OLE DB Status Value, If Av
Jan 31, 2007Hi,
I am using ATL COM library application. It is using sql data base for fetching the records. Some times, i get the following error. could you please let me know, why this happens? This is not reproduceble every time.
(Error! hr=80040e21, hrDesc=Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work
Here is the code to connect to database which i am using.
CDataSource db;
CDBPropSet dbinit(DBPROPSET_DBINIT);
dbinit.AddProperty(DBPROP_AUTH_INTEGRATED, OLESTR("SSPI"));
dbinit.AddProperty(DBPROP_INIT_CATALOG, (const char *)bDatabase);
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, (const char *)bServer);
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
dbinit.AddProperty(DBPROP_INIT_TIMEOUT, (short)150);
hr = db.Open(_T("SQLOLEDB.1"), &dbinit);
if (FatalError(hr, "db.Open", buf))
{
*iErrorCode = hr;
return S_OK;
}
Any help, appreciated.
Thanks,
Satish
SQL Server Procedure Execution Status
Feb 18, 2008I am running a SQL Server procedure from ADO in visual C++, sometimes I get return -6 as return code from procedure.
From SQL Server documentation:
"SQL Server currently uses return values 0 through -14 to indicate the execution status of stored procedures. Values from -15 through -99 are reserved for future use."
I cannot find a description for the returns codes from SQL Server for error conditions (from -1 to -14), does anyone here knows where can I find this information?
Best regards,
Pedro Chau
Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Store Procedure/scripts For Monitoring Replication Status And Performance
Jan 8, 2007Hi guys , may I know is there any examples of store procedure/scripts for monitoring replication status and performance ? I just know about sp_replmonitorhelppublisher. Thx for the assistance.
From,
Hans
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure
Nov 14, 2014I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.