In MS SQL Server 2000 How To Find Last Used Stored Procedure ?

Jul 30, 2007

Hi

This is seshu.

Is there any way to find out the last used stored procedure.

Let me know about this one.

View 1 Replies


ADVERTISEMENT

Cannot Find Select Stored Procedure Wizard In SQL 2000

Jul 20, 2005

I was using the SQL 2000 stored procedure wizard to create storedprocedures. I can create insert, delete and update SPs however I amnot able to create the select SP. The wizard does not offer the optionof selecting "select" type SP. thank you for your replyscompraj

View 1 Replies View Related

How To Find Year From 1900 To 2000 Using Store Procedure In Sql Server

Nov 21, 2006

hi friends
i want to know how to calculate year ranging from 1900 to 2000 using store procedure in sql server

View 8 Replies View Related

SQL Server Express And C# 2.0: Cannot Find Stored Procedure...

Sep 5, 2006

Hi everybody,   I created several stored procedure in a local sql server 2005 express database, now when I call/execute them in the asp.net 2.0 web page, it returns an error message of "Cannot find the stored procedure '<proc_name>'", this is funny, when I execute the commandin a sql query window "exec sp_GetAllArticles", it also returns cannot find stored procedure, but when I execute the "Use <DBName>" and thenexecute the statement, it works. The sql connection used in codes is ok. So, what I have missed to set or where I made mistake?Code:Inside web.config, <add name="Connectstring" connectionString="Data Source=DENSIASQLExpress;Initial Catalog=DbName;Integrated Security=True" providerName="System.Data.SqlClient" /> inside Load event, DAL.ConnectionString = ConfigurationManager.ConnectionStrings["Connectstring"].ConnectionString; private void DisplayLatestResources()     {         DAL.CreateCommand("sp_GetLatestFiveTrainingResources");         DAL.ExecuteReader();         SqlDataReader dr = DAL.DataReader;       .....     }  Try supplying with user id and password, same result...also try using aspnet_regsql.exe, same result also... Thanks in advanced. den2005

View 1 Replies View Related

Can't Find The Stored Procedure I Wrote In SQL Server

Jan 9, 2008

This is my first forray into writing stored procedures and like the manual said created one in SQL Server 2005 parsed or tested it, executed it, tested it again succesfully,need to modify it but can't find it under stored procedures file where I initially right clicked and selected New Stored Procedure.
Any idea where it went?

View 3 Replies View Related

SQL Server 2012 :: Find All Tables Used In Any Stored Procedure

Feb 14, 2015

I have a table with the list of all TableNames in the database. I would like to query that table and find any tables used in any stored procedure in that DB.

Select * from dbo.MyTableList
where Table_Name in
(
Select Name
From sys.procedures
Where OBJECT_DEFINITION(object_id) LIKE '%MY_TABLE_NAME%'
Order by name
)

View 7 Replies View Related

SQL Server Admin 2014 :: Stored Procedure - Find The Cause For Blocking

Sep 23, 2015

I was trying to create stored proc

[code="create procedure dbo.sp_table1
@idint
as
begin
updatetable1
setisarchived = 1,

[Code] ....

But the query was continuously blocking the query below

updatetable1
setisarchived = 1,
modtime = getdate()
whereid = @id
andisarchived = 0

I was not sure, why the create procedure statement is blocking the update statement.

View 1 Replies View Related

SQL Server 2012 :: Stored Procedure - Find Invalid Combination

Sep 30, 2015

I have a scenario where I need to develop a stored proc to identify invalid input provided.

Following is a sample scenario

Create table product (ProductId varchar(10),SizeId int,ProductColor varchar(10));
insert into Product
select 'Prod1',10,'Black' union ALL
select 'Prod1',10,'BLue' union ALL
select 'Prod2',20,'Green' union ALL
select 'Prod2',10,'Black' ;

[Code] ....

In following TSql Code , Color and Size are optional. Both are provided as comma separated input. I have provided "bbc" as wrong color and "MM" as wrong size. I want to identify if color is invalid or size (MM is in valid for Black and Blue) and to set flag accordingly.

I had tried out join but it is not serving needs.

---===========================================
-- Sql
--============================================

DECLARE
@ProdId varchar(10),
@color varchar(max) = Null,
@size varchar(max) = Null
BEGIN
set @ProdId='Prod1';

[Code] .....

View 9 Replies View Related

SQL Server 2008 :: Find All Items That Call A Stored Procedure

Nov 2, 2015

We have a process that uses a stored procedure: sp_MysteryProcedure....

The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.

One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).

So my question is multi-part.....

1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?

View 9 Replies View Related

SQL Server 2008 :: Find List Of Stored Procedure Used By Crystal Reports

Jul 1, 2015

We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?

View 4 Replies View Related

SQL Server 2008 :: Find Out What Data Was Changed By A Stored Procedure After It Was Executed?

Jul 22, 2015

isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.

View 3 Replies View Related

SQL Server 2014 :: Find Out Last Executed Date For Any Stored Procedure In Database

Oct 6, 2015

Is it possible to find out the last executed date for any stored proc in the database using system tables or writing any other query.

View 2 Replies View Related

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

View 3 Replies View Related

(Could Not Find Stored Procedure ''.) When Calling A User Defined Procedure

Feb 4, 2008

Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d    

View 3 Replies View Related

Sql Server 2000 - Dts Can't 'see' Stored Procedure

Jul 20, 2005

I am trying to get the DTS Execute SQL task to run a simple sql server 2000no-parameter stored procedure.The procedure runs without error when using SQL Analyzer but DTS Execute SQLreports "could not find stored procedure "[dbo].[test]"The stored procedure is as follows:create procedure test asdeclare tnames_cursor CURSORforselect database_name, tmp_table_name from[DW_Dimensions].[dbo].[vw_dimension_temporary_tables_active]open tnames_cursordeclare @tablename sysnamedeclare @databasename sysnamewhile (@@FETCH_STATUS <> -1)beginselect @databasename = rtrim(@databasename)select @tablename = rtrim(@tablename)exec ("delete from " + @databasename + ".dbo." + @tablename)fetch next from tnames_cursor into @databasename, @tablenameendclose tnames_cursordeallocate tnames_cursorGOAny ideas?

View 1 Replies View Related

SQL Server 2000 Stored Procedure With ADO And VBA

Feb 1, 2008

I have a stored procedure that builds a sql statement and executes it. When run in Query Analyzer it runs successfully and displays records as it should. When I open a recordset with the stored procedure from VBA it executes the codes but doesn't open the recordset.

The store procedure is:
ALTER PROCEDURE dbo.spTest
AS
DECLARE @sColumns varchar(2000)
DECLARE @sFrom varchar(2000)
DECLARE @sWhere varchar(2000)
DECLARE @sSqlString AS nvarchar(2000)
SET @sColumns = 'dbo.tblDoctor.lastname '
SET @sFrom = 'FROM dbo.tbldoctor '
SET @sWhere = 'WHERE dbo.tbldoctor.doctorcode is not null '
SET @sSqlString = 'SELECT ' + @scolumns + ' ' + @sFrom + ' ' + @swhere PRINT '@scolumns ' + @scolumns

PRINT '@sfrom ' + @sfrom PRINT '@swhere ' + @swhere PRINT 'sqlstring ' + @sSqlstring
EXEC Sp_executesql @sSqlString

The VBA code is:
Private Sub Command0_Click()
Dim cn As Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
Set cn = CurrentProject.Connection
strsql = "exec spTest"
With rs

.Source = strsql
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
.Open
End With
Debug.Print rs.RecordCount & " records found"
End Sub
When it hits the line with rs.recordcount it displays the following error: "Operation is not allowed when object is closed" referring to the rs recordset never being opened.

Any suggestions?

View 4 Replies View Related

SQL Server 2000 And Stored Procedure Concurrency

Apr 1, 2005

I'm using Java to connect to a SQL Server 2000 database. I connect using the Driver Manager with Sun's odbc driver ( sun.jdbc.odbc.JdbcOdbcDriver ) or I can use the jdbc driver provided by Microsoft (com.microsoft.jdbc.sqlserver.SQLServerDriver)

The Java application makes 1 Connection.

Within the database there exists a stored procedure that updates 2 Tables. The tables have a fixed number of rows that get updated continuously by calls to this stored procedure.

The Java application has a thread pool of 15 threads that create 15 CallableStatements (1 per thread) using the same instance of the Connection object.

According the the Microsoft JDBC driver docs, 1 Connection with multiple calls to the Callable statements is how it's supposed to be done. The following is an excerpt from Microsoft's "SQL Server 2000 Driver for JDBC User’s Guide and Reference" (page 86) regarding Connection Managment:
Managing Connections

Connection management is important to application performance. Optimize your application by connecting once and using multiple statement objects, instead of performing multiple connections. Avoid connecting to a data source after establishing an initial connection.
This is precisely what I'm doing, but I do not know if the the stored procedures are being run concurrently, the documentation does not tell me.

So my question: What is happening inside SQL Server 2000?

View 2 Replies View Related

Problem With Stored Procedure In SQL Server 2000

May 19, 2004

Hi everyone,

I am new to the forum and I have a problem with a stored procedure in SQL Server 2000. I use a stored procedure that returns a string but the problem is that my variable is empty at least I think it's empty.

With the code below the variable is not empty:
set @Ssql = 'select Cust_Company_id, fix_price from GEC_FixPrice where priority > 0 and cust_company_id like ' + char(39) + @InvoiceLine_CustCompanyid + char(39) + ''

But when I use the code below it's empty

- set @sSql = 'select Cust_Company_id, fix_price from GEC_FixPrice where priority > 0 '
- set @sSql = @sSql + ' and cust_company_id like ' + char(39) + @InvoiceLine_CustCompanyid + char(39) + ''


I also tried this :

- set @Start = 'select Cust_Company_id, fix_price from GEC_FixPrice where priority > 0 '
- set @End = ' and cust_company_id like ' + char(39) + @InvoiceLine_CustCompanyid + char(39) + ''
- set @sSql = @Start + @End

This works also, so I don't get why the second example won't work and the second example is the one I want to use.

So does anyone has a clue :confused:

thx in advance,

grtz

Loki2600

View 2 Replies View Related

Possible To Lock A Row Within A Stored Procedure In SQL Server 2000?

Jul 20, 2005

Hi All,I have a table that holds pregenerated member IDs.This table is used to assign an available member id to web sitevisitors who choose to register with the siteSo, conceptually the process has been, from the site (in ASP), to:- select the top record from the members table where the assigned flag= 0- update the row with details about the new member and change theassigned flag to 1- return the selected member id to the web pageNow I'm dealing with the idea that there may be brief, high trafficperiods of registration, so I'm trying to build a method (storedprocedure?) that will ensure the same member id isn't returned by theselect statement if more than 1 request to register happens at thesame instant.So, my question is, is there a way, once a record has been selected,to exclude that record from other select requests, within the boundsof a stored procedure?ie:- select statement is executed and row is instantly locked; any otherselect statement running at that exact moment will receive a differentrow returned and sill similarly lock it, ad nauseum for as manysimultaneous select statements as take place- row is updated with details and flag is updated to indicate themember id is no longer unassigned- row is released for general purposes etcIf what I'm suggesting above isn't practical, can anyone help meidentify a different way of achieving the same result?Any help immensely, immensely appreciated!Much warmth,Murray

View 12 Replies View Related

Sql Server 2000 - Xp_sendmail Stored Procedure

Aug 15, 2007

I have no idea about how can I use xp_sendmail
when I try to use it this error displayed on the screen.
There is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail ...

View 3 Replies View Related

Extended Stored Procedure Using C# For SQL Server 2000

Mar 12, 2007



Can anyone of you help me in finding online - resources to write extended stored procedure for Sql Server 2000 using C#.

Thank you,



Andy Rogers

View 1 Replies View Related

Passing XML From Sql Server 2000 In Stored Procedure

Jul 23, 2007

Hi,
I m wanting a stored procedure to pass an XML string from a stored procedure to my application. Whats the best way to achieve this?

Hope you can help

Thanks

Paul

View 10 Replies View Related

Stored Procedure Vs Simple Query In SQL Server 2000?

May 19, 2007

I am developing a windows application in VB.Net 2005 and Database is SQl Server 2000.I want to insert, update and delete records from a master table which has 8 columns.So should I write a stored procedure for this or write three queries and execute them in code.I haven't used stored procedure before. What will be advantages of using stored procedures?And tell me how to write stored procedure to insert,update and delete. Then how to call it in VB.Net code.

View 7 Replies View Related

Need Help In Writing A Stored Procedure In MSSQL Server 2000

Mar 29, 2007

Hi Everybody,

I am trying to update a column Percentage in a table named Critical Doctors with a column named

PercentTime from tblPercent table, Where the column Doctor matches with any DoctorId from

tblPercent.

I am getting an error message for the following query.

Have Two tables

1.CriticalDoctors
2.tblPercent

update CriticalDoctors set Percentage =
(select PercentTime from tblPercent)
where CriticalDoctors.Doctor = (select DoctorId from tblPercent)

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=

, >, >= or when the subquery is used as an expression.
The statement has been terminated.

Pls give me reply on how to write a stored procedure so that I can equate the percentage column

with one value and also check the condition with one value.

Thanking you guys in advance.

madhav

View 4 Replies View Related

SQL Server 2000 - Send A Mail By Stored Procedure

Mar 3, 2008

can I send a mail by stored procedure?

View 5 Replies View Related

Database Output Problem For A SQL Server 2000 Stored Procedure

Oct 27, 2006

Hi,  I have a problem with "database output"  window in executing a select-sp in vs 2005 standard. The db is a sql server 2000 one.When I execute the sp within VS, the database output correctly displays the execution information:No rows affected.(1 row(s) returned)@RETURN_VALUE = 3 but I can't see the returned rows (3 rows are returned); I only see the column names and no data.Running [dbo].[spBkm_GetList] ( @IDUser = <DEFAULT>, [.........]).IDBkm UIBkm IDUser --------------------- -------------------------------------- ---------
No rows affected. If I run the same sp in Sql Server Manager Studio Express it correctly shows the data of the 3 rows returned.  The sp uses  EXEC sp_executesql @Sql, @ParamList, ...... for executing the sql statement and the last line of sp is  RETURN @@ROWCOUNTI've tried removing the "RETURN @@ROWCOUNT" with no success.  The problem affects only one sp, the others, which are absolutely similar, work properly . I don't know what is the problem...Any idea?Thanks in advance 

View 1 Replies View Related

Help!! SQL Server 2000 Extended Stored Procedure Hangs In Windows 98

Jul 20, 2005

I am trying to run xp_cmdshell from the Query Analyzer using SQLServer 2000 running on Windows 98.It seems like it should be simple - I'm typingxp_cmdshell 'dir *.exe'in the Query Analyzer in the Master db. I'm logged in as sa.The timer starts running and never stops. No error message.Can anyone PLEASE help me with this? Any suggestions would beappreciated. Are SQL Server 2000 extended stored procedures notsupported in Windows 98? I've tried searching the Knowledge Base butcan't find anything.Thanks!

View 1 Replies View Related

SQL Server 2000 - Create An Excel File By Stored Procedure..

Mar 3, 2008

Can I create an excel file by stored procedure?

View 2 Replies View Related

SQL Server 2005 Stored Procedure Is Very Slow Vs. SQL Server 2000

Apr 18, 2008

Hi there,

I was wondering if someone can point out the error or the thing I shouldn't be doing in a stored procedure on SQL Server 2005. I want to switch from SQL Server 2000 to SQL Server 2005 which all seems to work just fine, but one stored procedure is causing me headache.

I could pin the problem down to this query:


DECLARE @Package_ID bigint

DECLARE @Email varchar(80)

DECLARE @Customer_ID bigint

DECLARE @Payment_Type tinyint

DECLARE @Payment_Status tinyint

DECLARE @Booking_Type tinyint

SELECT @Package_ID = NULL

SELECT @Email = NULL

SELECT @Customer_ID = NULL

SELECT @Payment_Type = NULL

SELECT @Payment_Status = NULL

SELECT @Booking_Type = NULL

CREATE TABLE #TempTable(

PACKAGE_ID bigint,

PRIMARY KEY (PACKAGE_ID))

INSERT INTO

#TempTable

SELECT

PACKAGE.PACKAGE_ID

FROM

PACKAGE (nolock) LEFT JOIN BOOKING ON PACKAGE.PACKAGE_ID = BOOKING.PACKAGE_ID

LEFT JOIN CUSTOMER (nolock) ON PACKAGE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID

LEFT JOIN ADDRESS_LINK (nolock) ON ADDRESS_LINK.SOURCE_TYPE = 1 AND ADDRESS_LINK.SOURCE_ID = CUSTOMER.CUSTOMER_ID

LEFT JOIN ADDRESS (nolock) ON ADDRESS_LINK.ADDRESS_ID = ADDRESS.ADDRESS_ID

WHERE

PACKAGE.PACKAGE_ID = ISNULL(@Package_ID,PACKAGE.PACKAGE_ID)

AND PACKAGE.CUSTOMER_ID = ISNULL(@Customer_ID,PACKAGE.CUSTOMER_ID)

AND PACKAGE.PAYMENT_TYPE = ISNULL(@Payment_Type,PACKAGE.PAYMENT_TYPE)

AND PACKAGE.PAYMENT_STATUS = ISNULL(@Payment_Status,PACKAGE.PAYMENT_STATUS)

AND BOOKING.BOOKING_TYPE = ISNULL(@Booking_Type,BOOKING.BOOKING_TYPE)

-- If this line below is included the request will take about 90 seconds whereas it takes 1 second if it is outcommented

--AND ADDRESS.EMAIl LIKE '%' + ISNULL(@Email, ADDRESS.EMAIL) + '%'

GROUP BY

PACKAGE.PACKAGE_ID

DROP TABLE #TempTable


The request is performing quite well on the SQL Server 2000 but on the SQL Server 2005 it takes much longer. I already installed the SP2 x64, I'm running the SQL Server 2005 on a x64 environment.
As I stated in the comment in the query it takes 90 seconds to finish with the line included, but if I exclude the line it takes 1 second.
I think there must be something wrong with the join's or something else which has maybe changed in SQL Server 2005. All the tables joined have a primary key.
Maybe you folks can spot the error / mistake / wrong type of doing things easily.
I would appreciate any help you can offer me to solve this problem.

On the web I saw that there is a Cumulative Update 4 for the SP2 which fixes the following:





942659 (http://support.microsoft.com/kb/942659/)
FIX: The query performance is slower when you run the query in SQL Server 2005 than when you run the query in SQL Server 2000

Anyhow I think the problem is something else, I haven't tried out the cumulative update yet, as I think it is something different, more general why this query takes ages to process.

Thanks again for any help

Best regards,
Pascal

View 9 Replies View Related

Stored Procedure From SQL Server 2000 To SQL Server 2005

Oct 31, 2007



I had few stored procedures which were working in SQL server 2000. I upgraded SQL server to 2005 and one stored procedure does not work. It gives the Error Msg 102. "Syntax error near ',' "
I already tried set quoted identifiers ON & OFFAny help would be appriciated.

View 10 Replies View Related

Cannot Find Stored Procedure

Mar 11, 2005

I developed a ASP.net web application with a MSDE database backend on my laptop(vs.net 2003 XP Pro), then I transferred the website onto a server(Windows Server 2003) and generated a SQL Server 2000 database from the scripts I exported from MSDE(web administrator). The problem I am having is that it can't find any stored procedures. I keep getting errors when logging on, 'Could not find stored procedure "_myProc" '.
Any one with any clues what might be the problem?
Yes I have changed the connection strings.
Thanks in advance
P

View 2 Replies View Related

Could Not Find Stored Procedure

Jan 5, 2006

I use the following code in ASP.NET 2.0 to update the database:
Dim myConnection As New SqlClient.SqlConnection("server=local);uid=sa;pwd=xxx;database=Northwind")Dim myCommand As New SqlClient.SqlCommand("dbo.spTralen_customer_save 'CACTU'", myConnection)myCommand.CommandType = CommandType.StoredProceduremyConnection.Open()myCommand.ExecuteReader(CommandBehavior.CloseConnection)
I get the following error message: "Could not find stored procedure..."
The sp is in the database and dbo is the owner of the sp and I'm logged in as sa as you can see above. It doesn't matter if I remove the "dbo." from the sql command, it still doesn't work. If I remove the parameter value 'CACTU' above I get an error message saying that the sp expects the parameter so the sp is obviously in the database.
Can someone please help me as soon as possible!// Gato Gris
 
 
 
 

View 2 Replies View Related

Could Not Find Stored Procedure

Apr 29, 2008

Not sure if this question belongs here or in a .NET forum.
But Im going to give it a shoot. The problem is that Im getting the following error: "Could not find stored procedure 'xxx'".

Ive never used stored procedures before, so what I am wondering is there anything basic that Im forgetting?

I have this simple stored Procedure:

ALTER PROCEDURE Person_info
@FirstN varchar(128),
@LastN varchar(128)
AS
SELECT FName, LName
FROM Person
WHERE (FName = @FirstN) AND (LName = @LastN)

and each time I call this procedure I get the prior stated error.

returnValue = sqlcmd.ExecuteReader(); //crashes when this line executes.

Ive found some people talking about this and it might be caused due to the "initial catalog=<database name>" in the connection string is missing. I tried that but didnt work.

View 18 Replies View Related







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