Modify Existing Stored Procedure??

Dec 2, 2005

I have a stored procedure I created in SQL Server 2000 enterprise manager which I would like to modify in SQL Server 2005 Express Management Studio.  When I right click on the stored proc and select "Modify", the code opens - however any attempt to save creates a local .SQL file.  How can I save these changes to the stored procedure on the server?

View 1 Replies


ADVERTISEMENT

How To Modify An Existing Stored Procedure?

Apr 16, 2008

Hi all,
I have an Existing Stored Procedure on the database, and I want to modify it. However, when I changed it and saved it with the same name, the value,say 20 here I changed was not upgraded to the new value (I wanna it be 30). so anyone know how to solve that? Thanks in advance. 

ALTER PROCEDURE [dbo].[spr_getCompanyId]( @companyname as varchar(20), @companykeyword as varchar(20)   //i want to change 20 to 30
)

View 5 Replies View Related

How To Modify My Stored Procedure So That...

Aug 2, 2004

Hi,
Below is the SP that I want to modify it such that it does another thing apart from what its doing now. I want to to delete all the records corresponding to the BpDate value before this insert happens. by meaning deleting all the records, I meant to say delete all records where BpDate = @BpDate and SiteCode = @SiteCode.


CREATE PROC CabsSchedule_Save

@JulianDate smallint,
@SiteCode smallint,
@BpDate smallint,
@CalendarDay smallint,
@BillPeriod smallint,
@WorkDay smallint,
@CalDayBillRcvd varchar(30),
@Remarks varchar(50)

AS


INSERT INTO
CabsSchedule(JulianDate, SiteCode, BpDate, CalendarDay, BillPeriod, WorkDay, CalDayBillRcvd, Remarks)
VALUES
(@JulianDate, @SiteCode, @BpDate, @CalendarDay, @BillPeriod, @WorkDay, @CalDayBillRcvd, @Remarks)
GO




Any help will be appreciated.

Thanks,

View 1 Replies View Related

NEED HELP TO MODIFY STORED PROCEDURE CODE...

Apr 3, 2007

Pls check my code for the stored procedure which i created for the companydetails including companyid P.K. Not Null int(4),companyname Not Null varchar (20),address varchar(30) where companyid is the primary key and it should be autogenerate.I also want that it should check if the name exits or not.It should also check that the field is entered and not kept null.If it's null then should return the error message.I want to write the queries select,insert,update and delete in the single stored procedure.How can i differ all the query individually in a stored procedure.The select and insert query are done on the button click event whereas the update,delete queries are performed in the gridview link event. Pls help me and modify my code accordingly with ur suggestions who know
the stored procedure very well.First read what i want then give reply.waiting for the reply and with corrections.The coding is perfomed in sql server 2005 and asp.net with C# 2005, 1 ALTER PROCEDURE CompanyStoredProcedure2 @uspcompanyid int,3 @uspcompanyname varchar(20),4 @uspaddress1 varchar(30), 5 @frmErrorMessage as varchar(256) OUTPUT,6 @RETURNVALUE as int OUTPUT,7 @RETURNID as int OUTPUT8 AS9 declare
10 @companyid int,11 @companyname varchar(20),12 @address1 varchar(30) 13 14 BEGIN15 16 begin17 Select @RETURNVALUE = -918 RETURN -919 end20 21 begin22 Select @frmErrorMessage = 'The Operation Mode Has Not Been Specified'
23 return -924 end25 26 27 28 begin 29 --validation...
30 if (@uspcompanyname is Null or @uspcompanyname = '')31 begin32 Select @RETURNVALUE = -933 select @frmErrorMessage = 'Company Name is empty'
34 return -935 end 36
37 if exists (select companyid from companymaster 38 where upper(companyname) = upper(cast(@uspcompanyname as varchar(20))))39 begin40 select @companyid = companyid from companymaster 41 where upper(companyname)=upper(cast(@uspcompanyname as varchar(20) ) )42 end43 else 44 45 select @companyname= cast (@uspcompanynameas varchar(20))46 select @address1= cast(@uspaddress1 as varchar(30))47 select @companyid = isnull(max(companyid),0) + 1 from companymaster48 49 IF exists(SELECT * from companymaster where companyname=@companyname)50 begin51 Select @frmErrorMessage = 'Record With Company Name ' 52 + @companyname + ' is Already Exisiting For The Company Name ' 53 return -954 end 55 56 -- the following codes inserts
57 begin transaction58 INSERT INTO companymaster59 ( companyname, address1)60 VALUES (@companyname,@address1)61 commit transaction62 63 select @RETURNVALUE = 064 select @RETURNID = @companyid65 66 end67 68 69 -- the following codes edit/updates
70 begin71 UPDATE companymaster 72 SET companyname=@companyname,73 address1=@address1 74 WHERE companyid =cast(@uspcompanyid as int)75 76 select @RETURNVALUE = 077 select @RETURNID = cast(@uspcompanyid as int)78 end79 -- the following codes delete
80 begin81 DELETE companymaster WHERE (companyid = @companyid)82 end 83 84 END 85
  Pls help me and modify my code accordingly with ur suggestions who know the stored procedure very well.First read what i want then give reply.  

View 4 Replies View Related

How Do I Edit/modify A Stored Procedure?

Nov 12, 2007

Hi,

I'm from Argentina. I'm not an expert at all in SQL, I know very little about it. I've read in MSDN Library that in order to edit a stored procedure I must right-click the procedure to modify, and then click Design.

I'm using SQL Server Management Studio (not Express) and I don't see any "design" option. I do see a "modify" option. I clicked there and modified just a number I wanted to. Once modified I clicked on the X button to shut the file hoping the system would ask me if I wanted to save the changes made. When I clicked "yes" the file saved into "my documents > sql management studio > proyects".

I checked the original Store Procedure file inside "Programmability > Stored Procedures" and obviously it wasn't altered. I have now a file called "SQLQuery31" in "my documents > sql management studio > proyects" that seems to have the modification made.

Why do I have it there (inside My Documents) instead of modifying the original one? What must I do to get that stored procedure modified?

Thanks from Argentina !!

View 4 Replies View Related

Modify Table Structure Via Stored Procedure(?)

Jun 9, 2008

Our ERP vendor is helping us migrate from one version of their product to another. In the new version, they have added some columns and changed a few data types in several existing tables.

They sent us a list of the modifications that were necessary, which we did (painstakingly) by hand. But, for other reasons the update failed and we have to start anew.

This got me thinking: is there any way to do this kind of update via a script/stored procedure? I don't have their exact changes handy, but it was stuff like the table "inmast" needs a column of varchar(50) called "lotc" added to it, and the "intime" table needs the datatype of columns "Mon" "Tue" "Wed" changed from text to varchar(50), stuff like that.

Is it possible to do stuff like that in a SP?

View 1 Replies View Related

DB Engine :: How To Optimize Stored Procedure Without Modify

Nov 25, 2015

How to optimize stored procedure without modify?Is there is any way to forcing index without using FORCESEEK hint?

View 3 Replies View Related

Insert Delete Modify In A Single Stored Procedure

Jun 13, 2008

 hi guys,I am using SQL server 2005, i created a table of 4 columns. I want to create a stored procedure which will insert delete and modify the table using flag. all the three insert, delete and update must work in a single stored procedure. pls help me out. Thank You.  

View 1 Replies View Related

SQL Server 2012 :: Modify Stored Procedure Opens Does Not Open For Modifications As Earlier

Jan 5, 2015

when i click on the modify option on a stored procedure it is not opening for editing as earlier. it opens as if for scripting.for example i clicked modify procedure on stored proc. "text" and the result is as follows.

/****** Object: StoredProcedure [dbo].[test] Script Date: 05/01/2015 7:50:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'P', N'PC'))

[code]....

View 4 Replies View Related

Can't Modify Or Delete An Existing Job

Aug 20, 2004

Folks, i have rebuilt my old server machine and i've restored the MSDB over the new machine. Now when i try to edit or delete any of the jobs that come from the RESTORE; i get the following error message:


I never setup multiserver administration.
However i can create and delete new jobs.
How do i remove these older jobs.

Howdy!

View 5 Replies View Related

Modify Existing Report

Apr 26, 2007

Hello:



How do I go about bringing a report down from the report server to modify it? What kind of security access will I need? The report is in production and it was created by someone who recently left the company.



Thanks in advance for your assistenance.

View 3 Replies View Related

Dataset With Existing Stored Procedure

Mar 11, 2008

I am trying to use a dataset for the first time and I've run into a roadblock early.  I added the dataset to the AppCode folder, set the connection string, and selected 'use existing stored procedures' in the configuration wizard.  The problem is that there are three input parameters on this procedure and they're not showing up in the 'Set select procedure parameters' box.  I went through several of the stored procedures and this is the case for all of them.  The weird thing is that if I select the same procedure as an insert procedure then the parameters do show up.  Very frustrating, any thoughts?
Thanks in advance,
N

View 6 Replies View Related

How To Encrypt All Existing Stored Procedure?

Aug 18, 2006

I know that we can CREATE PROCEDURE procedure_name WITH ENCRYPTION.

But how about if I want encrypt existing stored procedures?

Which command should I use ?

View 4 Replies View Related

Cannot Creat Stored Procedure On Existing Database

May 15, 2008

Hi, I already got a database and I need to add another stored procedure on it in order to call it from the SSIS package.
But when I execute the sql file to create the procedure, there is a error message popping out like:

"Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'logit'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists."

And I have tried it on another computer, there is the same error. Could you please tell me how to solve the problem. Thanks very very much.

View 4 Replies View Related

Power Pivot :: How To Modify Existing Workbook Data Connection String

Oct 29, 2015

Trying to modify the workbook connection string but it is greyed out and unable to change the Provider= from SQLNCL10 to SQLOLEDB.  I am able to change the PowerPivot Data Connections connection string but not the workbook connection string.  On the forum only see where people have asked the question but it seems like the people who've asked ended up recreating the data models.

View 5 Replies View Related

Help Need To Modify Procedure

Jul 20, 2002

Have a script that should extract data if :-

1) Mothly :-
-payday is equal to @today
-pay frequency is = 1
-payday between 1-31

2) Weekly
-payday is between 1-5 ( 1 =Monday,2 =Tuesday,3=Weds,4=Thurs,5=Friday)
-payfrequency is = 2

3) Fortnightly
-after two weeks
-payfrequency =3


How do l modify this procedure to be able to extract using the listed conditions ? Need help

CREATE Procedure Collections_Cats
AS
BEGIN

Declare @today int
Set @today = (SELECT Day(GETDATE()))

DECLARE Collections_Cats_Cursor
CURSOR
FOR
SELECT distinct
n.loan_No AS Loan_No,
n.customer_No AS Customer_No,
c.first_name AS First_name,
c.second_name AS Second_name,
c.surname AS Surname,
c.initials AS Initials,
b.Bank_name AS Bank_name,
br.branch_code AS Branch_code,
d.bank_acc_type AS Bank_acc_type,
pay_sheet.pay_frequency AS Pay_Frequency,
n.monthly_Payment AS monthly_Payment,
pay_sheet.payday AS payday
FROM Transaction_Record tr

INNER JOIN
Loan n ON tr.loan_No = n.loan_No
INNER JOIN
Customer c ON n.customer_No = c.customer_no
INNER JOIN
Bank_detail d ON c.customer_no = d.customer_no
INNER JOIN
Branch br ON d.Branch = br.Branch
INNER JOIN
Bank b ON br.Bank = b.Bank
INNER JOIN
pay_sheet ON c.customer_no = pay_sheet.customer_no
WHERE Pay_sheet.Payday = @today AND pay_sheet.pay_frequency =1

Order by l.loan_No

OPEN Collections_Cats_Cursor

-- Perform the first fetch.
FETCH NEXT FROM Collections_Cats_Cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Collections_Cats_Cursor
END

CLOSE Collections_Cats_Cursor
DEALLOCATE Collections_Cats_Cursor
END;
GO

View 1 Replies View Related

Is There A Way To Find The Last Procedure Execution Time If Procedure Updates The Existing Table And There Is No Flags/triggers?

Aug 21, 2007

View 8 Replies View Related

T-SQL (SS2K8) :: How To Modify Procedure For Input Is Null Or Zero

Apr 30, 2014

I am creating web application for state and dist wise map. I've the tables like

create table ind_state
(
ind_stat_id int,
ind_state_name varchar(50)
)
insert into ind_state values ('1','Pondi')

[Code] .....

My output is depends on the dist selection so made procedure

alter procedure LAT_TEST_DYNAM0
@dist_id int
as
begin
create table #temp (pincode varchar(10) ,latitude numeric(18,10),longitude numeric(18,10) ,descp varchar(5000))
if @dist_id!=0

[Code] ....

Myself doubt is when @dist_id is null or "0" how to show default value ?

Otherwise above my code is correct?

View 4 Replies View Related

Modify System Stored Procedures

Oct 31, 2002

I am trying to edit a system stored(sp_add_dtspackage) procedure and cannot for the life of me find where to edit. This procedure does a check to keep users from saving changes to a package they do not own. I need users to be able to change the the packages when they are not always the one who created.

View 2 Replies View Related

Can't Modify Stored Proc Which Is In A Publication

May 22, 2006

Hi

SQL 2005 sp1 - merge replication - HTTPS.

We have 2 publications for the database - one which has subscription.SyncType = SubscriptionSyncType.Automatic
and another with SyncType = SubscriptionSyncType.None.

The first publication is there so we can add new stored procs etc, the second contains the initial schema and the data.


When we try to modify a proc which is in the publication with SyncType = Automatic, the query never returns.

This is most urgent - thanks for your help.

Bruce

View 16 Replies View Related

Do I Have The Right To Modify Third-party Application's Stored Procedures?

Jan 26, 2004

Our current concern deals with stored procedures from a third-party application that were modified in order to correct future data inconsistency that was being generated. Since the stored procedures were not encrypted, I was able to modify them and correct the problem. At the same time, we developed a small in-house application to correct the current data inconsistency and we created new stored procedures in the same database. Now I'm concern about if I had the right to modify those stored procedures and additionally, created new ones inside this database? Am I restricted somehow to use our full version of MS SQL Server with a scenario like this?

View 14 Replies View Related

Permission To Modify Stored Procedures In SERVER

May 8, 2015

finding all users that have permission to modify stored procedures in SQL SERVER.

View 0 Replies View Related

Create/modify File With A System Stored Procedire

Nov 22, 2005

Does anybody know how to create/modify a file without using one of the system stored procedures?

Thanks

View 1 Replies View Related

Make Sql Function To Modify Data Like Sql Procedure To Improve Code Structure And Readability.

Aug 27, 2007

Now Sql Function can not modify data as Sql Procedure like other database, it's very troublesome at most case!
in most case, Sql Function is used to improve code structure then it can be maintanced easy! BUT only because it can not modify data, there are 2 troublesome way:

1. Make all callers in the path from Sql Functions to Sql Procedure. and the coder will cry, the code will become very
confusional , and very difficult to maintance.

2. Divide the Sql Function into a thin CLR wrapper to call a Sql Procedure, can only use another connection, BUT can not be in the same transaction context And the code is ugly and slow.

You should not give limitation to Sql Function, should just limit the using context of Sql Function!

The sql code is more difficult to read and maintance than norm code(C#), then the improving code structure and readability should be one of your most important task, that's why microsoft!


View 6 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi 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?
 

View 1 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi 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.




View 11 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I 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.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

Existing Stored Procedures

Dec 13, 2007

hi,
is there someway I can use to copy an existing stored procedures to another database?
Thanks!

View 5 Replies View Related

Existing Storeed Procedure And Wizard Report Generator

May 9, 2008

I have an existing (working) SP that when I try to drop into the reoirt wizard gives me errors. The procedure runs in SQL management Studio, works in an existing rdl, even returns data in the repor wizard but also returns errors and therefore I can't continue. My work around is to drop it into an existing rdl; then I lose the Report Wizard features of generating a slick, quick repport.

View 1 Replies View Related

Quick Question About Checking For Existing Procedure Before Creation.

Feb 27, 2008

Hey everyone,

I'm getting a rather bizarre (but probably simple) error when trying to check for the existence of a procedure before creating it. I know I could just drop it, but I'm really curious as to why this code doesn't work right.



Code SnippetIF OBJECT_ID(N'dbo.spCoverageLog') IS NULL
CREATE PROCEDURE [dbo].[spCoverageLog] ( @spName VARCHAR(100) )
AS
BEGIN
UPDATE dbo.CodeCoverage
SET CreationDate = GETDATE(),ProcedureName = @spName;
END

GO
It's generating a syntax error at PROCEDURE.

Anyone know why this might happen?

The solution was to run the CREATE PROCEDURE statement with EXEC, but it shouldn't be.




Thanks in advance for any suggestions!

View 5 Replies View Related

Documenting Existing Stored Procedures

Sep 8, 2004

Does anyone have any scripts, or know of any tools, that can scan through all of the SPs that I have inherited, documenting details?

Thx

View 3 Replies View Related







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