Need Noobie Help On Alter Procedure...simple Question
Jan 28, 2008
Hi all,
I need to get a better understanding of alter procedure. If I Create a Procedure in SQL Server 2005, and want to modify that to Alter Procedure statement instead of create procedure... How do I do that??
I'm not an SQL Server person.. I dont understand why my work place needs Alter proceudre all the time but all our procedures have Alter procedure statement. Once they are created they have to be changed to Alter procedure...
Do I just need to change the keyword CREATE to ALTER?? because when I try to save it ,,, it saves it as a .sql file... Not the script.
Immediate reply would be appreciated
Thanks
View 7 Replies
ADVERTISEMENT
Mar 20, 2008
I've got two tables:
CallLog (one)
Repository (many)
They both have the shared id of CallID. I'm trying to write a trigger that when one adds a record to the repository table, I want the CallLog table flagged with 'T'. My existing code is flagging everything thing in the CallLog table that has CallID match from the Repository table... How do I update the single row in the CallLog table? Driving me nuts!
USE [HEATPROD]
GO
/****** Object: Trigger [dbo].[WriteAttachmentFlagNew] Script Date: 03/19/2008 12:29:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[WriteAttachmentFlagNew]
ON [dbo].[Repository]
AFTER Insert
AS
BEGIN
UPDATE CallLog
SET AttachmentFlagNew = 'T'
FROM repository
WHERE calllog.CallID= repository.CallID
END
View 6 Replies
View Related
Apr 21, 2008
Hi all, I am trying to do a very basic ALTER Command and am trying to change its DEFAULT value. Code below is what I currently have:
Code Snippet
ALTER TABLE Table_1
ALTER COLUMN TEST VARCHAR(1000) NULL DEFAULT 2
Thanks, Onam.
*UPDATE* I found this code but are there alternative methods? Additionally, if I was to update its DEFAULT value again how would I go about doing that? Do I first have to remove the CONSTRAINT and then run the command?
Code Snippet
ALTER TABLE Table_1 ADD CONSTRAINT DF_Albumns_Comment DEFAULT 2 FOR TEST
View 8 Replies
View Related
Feb 19, 2004
I am getting this message when I try to open the database in the enterprise manager. In all other locations it is telling me that the database is running any thoughts on how to resolve this?
The SQL Server \CRISPINDB1 is not known to be running. Are you sure you wish to connect?
Anything at all will be a great help to me thanks in advance.
View 6 Replies
View Related
Apr 1, 2007
Keep in mind this is my first compiled SQL program Stored Procedure(SP), copied from a book by Frasier Visual C++.NET in Visual Studio2005 (Chap12). So far, so theory, except for one bug (feature?)below. At some point I'm sure I'll be able to laugh about this, akinto forgeting a semi-colon in C/C++, but right now it's frustrating(time to sleep on it for a while).Problem--For some reason I get the error when trying to save files where twotables (called Author and Content), linked by a single key, form arelationship.By simple comparison of the source code in the textbook and my program(below) I found the difference: instead of, like in the textbook, theStored Procedure (SP) starting with "CREATE PROCEDURE", it*automatically* is (was somehow) given the name of 'ALTER PROCEDURE'and I cannot change this to "CREATE PROCEDURE" (you get an error in MSVisual Studio 2005 Pro edition of "There is already an object namedXXX in the database", see *|* below). No matter what I do, the SP isalways changed by Visual Studio 2005 to 'ALTER PROCEDURE'!!!(otherwise it simply will not save)Anybody else have this happen? (See below, others have had this happenover the years but it's not clear what the workaround is)Keep in mind this is my first attempt and I have ordered somespecialized books on SQL, but if this is a common problem (and Isuspect it's some sort of bug or quirk in VS2005), please let me know.Frankly I think SQL as done by VS2005 is messed up.Here are two Usenet threads on this problem:(1) http://tinyurl.com/2o956m or,http://groups.google.com/group/micr...1454182ae77d409(2) http://tinyurl.com/2ovybv or,http://groups.google.com/group/micr...9e5428bf0525889The second thread implies this is a bug--any fix?Also this bug might be relate to the fact I've switched (and notrebooted) from Administrator to PowerUser after successfully changingthe permissions in the SQL Server Management Studio Express (see thisthread: http://tinyurl.com/2o5yqa )Regarding this problem I might try again tommorrow to see if rebootinghelps.BTW, in the event I can't get this to work, what other SQL editor/compiler should I use besides MS Visual Studio 2005 for ADO.NET andSQL dB development?RL// source files// error message:'Authors' table saved successfully'Content' table- Unable to create relationship 'FK_Content_Authors'.The ALTER TABLE statement conflicted with the FOREIGN KEY constraint"FK_Content_Authors". The conflict occurred in database "DCV_DB",table "dbo.Authors", column 'AuthorID'.// due to the below no doubt!--CREATE PROCEDURE dbo.InsertAuthor /* THIS IS CORRECT (what I want)'CREATE PROCEDURE' not 'ALTER PROCEDURE'*/(@LastName NVARCHAR(32) = NULL,@FirstName NVARCHAR(32) = NULL)AS/* SET NOCOUNT ON */INSERT INTO Authors (LastName, FirstName)VALUES(@LastName, @FirstName)RETURN--ALTER PROCEDURE dbo.InsertAuthor /* WRONG! I want 'CREATE PROCEDURE'not 'ALTER PROCEDURE' but VS2005 won't save it as such!!!*/(@LastName NVARCHAR(32) = NULL,@FirstName NVARCHAR(32) = NULL)AS/* SET NOCOUNT ON */INSERT INTO Authors (LastName, FirstName)VALUES(@LastName, @FirstName)RETURN--*|* Error message given: when trying to save CREATE PROCEDURE StoredProcedure: "There is already an object named 'InsertAuthor' in the dB
View 11 Replies
View Related
Jul 5, 2007
Hi
How can I set alter procedure rights for all procedures in a database for one user?
View 1 Replies
View Related
Nov 13, 2007
I want to use Create a procedure if not exisit and Alter it if exisit.
Can I use Create and Alter keyword together while writing a procedure in SQL Server 2005? If YES than HOW?
View 2 Replies
View Related
Dec 24, 2007
I want to alter a stored procedure from within my code base. I'm basically wanting to write an in house app that will update my stored procedures across many databases that we have. Any help would be appreciated. I'm having difficulty finding anything on this topic.
View 3 Replies
View Related
May 18, 2007
Hi all,
I use SQL2005 and I recently noticed this...
When I right click a stored procedure and select modify I get something like this
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxxxx]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
xxx xxx xxx'
instead of the usual alter procedure...
I think that this happened after I installed SP2 (which I cannot remove)
Why this is happening and how can I revert it to the old way of altering stored procs?
View 2 Replies
View Related
May 20, 2008
we use alter statements for our stored procedures when rolling out a new version of our app. We have noticed that when viewing the stored proc in production it looks like the altered one, but when the app runs it, the changes are not taking effect. Has any one else experienced anything like this? It's like the old version is still being held on too.
To get around the problem we have been dropping and recreating the procedures.
Any help would be appreciated. Thanks!
View 1 Replies
View Related
Sep 11, 2006
Hello
As I'm not an experienced stored procedure programmer I was wondering where I need to put the BEGIN and END statement in a ALTER Stored Procedure.
As I understand it's the proc that gets altered, not the statements in the proc and that I find confusing.
I need to change the name of a column like:
UPDATE table
SET
Fielddd1 = 'test'
and I wan to change it into
UPDATE table
SET
Field1 = 'test'
Quite simple no? Forget it! Even if I hit the execute command to modify it. It get's changed back to the old code behind my back. So I guess I need to put a BEGIN and an END statement but I tried that already.
Could someonegive me a pointer?
Many thanks!
Worf
View 3 Replies
View Related
Dec 23, 2007
I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.Here is a short snippet of my code, but it keeps erroring out on me.Try myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text myCommand.ExecuteNonQuery() myTran.Commit()Catch ex As Exception myTran.Rollback() Response.Write(ex.ToString())End Try The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
View 3 Replies
View Related
May 28, 2002
Hello.
I'm trying to create a batch sql script which first alters some existing tables via the ALTER TABLE command, I then want to alter some existing stored procedures via the ALTER PROCEDURE command within the same batch. I have found that I can encompas the alter table scripts within a conditional IF EXISTS (Begin/End) but not the alter procesdure scripts. I have looked in reference material and have found nothing to suggest this type of operation is not possible. Is this possible? Is this a know bug fixed by a service pack?
Thanks in advance for any replies.
David.
View 3 Replies
View Related
Jun 27, 2007
Is there any way to extend the Alter Procedure command or fire somekind of event when it is executed in SQL Server 2005?
Thanks,
David
View 9 Replies
View Related
May 13, 2008
Hi,
I am using SQL 2000( Version: 8.00.000)
I want to ask can I add View in the stored procedure like the following:
Create Procedure sp_createTblLogReasonColorView
@m int,
@y int
AS
BEGIN
ALTER View vw_tblLogReasonColor
AS
Select * From tblLogReasonColor where Year(n_Delivery_Date) = @y AND Month(n_Delivery_Date) = @m
END
Thanks for you reply.
View 1 Replies
View Related
Apr 27, 2006
Can the ALTER USER statement be used (without a hack like using EXEC) in a stored procedure? I know that the sp_password system stored procedure can not be. Additionally, it is being deprecated anyway. I guess what is boggling me about my attempts so far relate to the errors I am getting due to the user being specified not being in quotes in the syntax. All of the searching I have done so far have come up lame so far; the only examples I have found about it were in scripts that create other scripts for transferring users and other administrative tasks that would be run from the query window, but not from an application. To be complete as possible, here is an example of a script the returns errors:
ALTER PROC [dbo].[lbxChangePassword]
(
@loginid nvarchar(180),
@oldpassword nvarchar(40),
@newpassword nvarchar(40)
) AS BEGIN
IF @oldpassword = (SELECT password FROM contacts WHERE loginid = @loginid)
BEGIN
BEGIN TRANSACTION
UPDATE contacts
SET password = @newpassword
WHERE loginid = @loginid
ALTER LOGIN @loginid WITH PASSWORD=@newpassword OLD_PASSWORD=@oldpassword
END
ELSE
BEGIN
RAISERROR(N'The password you entered does not match your current password.', 16, 1)
RETURN
END
IF @@ERROR <> 0
BEGIN
RAISERROR(N'There was an error creating your new password.', 16, 1)
RETURN
END
COMMIT TRANSACTION
END
************
This returns:
Msg 102, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near '@loginid'.
Msg 319, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
************
If ALTER LOGIN isn't how to change the password, then please tell me what the correct practice of changing a password is. I want to use the CURRENT_USER keyword in my queries and want I can't finish setting that up until I have this resolved because users will need to change their own passwords through the application I am developing.
View 10 Replies
View Related
Jun 26, 2001
I'm running SQL server 2000 sp1. I created a stored procedure that (1) drops a table, (2) recreates it with a "select into" statement, (3) alters the table by adding a field, and then (4) updates that field.
The trouble I'm having is that when I execute the stored procedure I get an error stating that I have an "invalid column name" between steps (2) and (3). It seems as though when I drop the table in step (1), the entire procedure wants to re-compile and it can't get past step (4) because the table hasn't been altered yet.
I've noticed a similar problem in editing stored procedures when they refer to tables or fields that don't exist yet because WITHIN the procedure they are created/modified. I'm not able to get a successful syntax check and therefore not able to save my work.
I must be missing something. Any suggestions?
View 4 Replies
View Related
Aug 10, 2015
I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.
I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.
View 1 Replies
View Related
Feb 5, 2015
Version 2008 R2
The stored procedure has the dependency on the table that was altered.
View 4 Replies
View Related
Jul 23, 2005
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Dec 20, 2006
People I am not a SQL Server guy so bear with me please. This should be very simple. I am looking for a procedure; given a TABLE_NAME as input; will run the following three SQL statements for each COLUMN in that table:
select max(COLUMN_NAME) from TABLE_NAME
select count(0) from TABLE_NAME where COLUMN_NAME is null
select count (distinct COLUMN_NAME) from TABLE_NAME
It will then write the output in a readable fashion to a local file.
Thanks a lot
View 4 Replies
View Related
Mar 3, 2008
Hello Everyone,I am sure, for the knowing, that this is a simple question.I have a table called tblTickets and a table called tblProjects. I would like to make a stored procedure that does the followingCopy entire contents of tblTickets record to tblProjects record and also changes tblTickets.sDetails to contain some text like "This ticket has been escalated into a project click here to view the project"tblProjects.sDetails would remain the same as the original.tblProjects will have additional fields but they can remain blank for the time being.ThanksMatt
View 11 Replies
View Related
Jun 3, 2008
What I am doing:I am in the process of creating a "log" that records when I update a record. I would like a new row to be inserted into BookLog each time a book's information is updated. Below I have the "update" code and below that is the code that is suppose to be inserting the newely updated record in the BookLog table.
**What the problem is**: I've tested the insert code, everything works EXCEPT BookEntryDate. I do not want to update the BookEntryDate when I am inserting it into BookLog. I just want to keep the original entry date from the Books table thats already there. In the BooksLog table I have "BookEntryDate" to not allow nulls...when i go to test i get an error that says "Procedure 'BookUpdate' expects parameter '@BookEntryDate', which was not supplied." I've tried several things and I am stuck :-/ I need some ideas. Thanks for your help!Alter Procedure dbo.BookUpdate@BookEntryDate datetime,@BookSummary nvarchar(1000),@BookComment nvarchar(50),@UserID nvarchar(50),@BookID bigint AsDeclare @Now datetimeSet @Now = GetDate()Update BooksSet Books.BookSummary = @BookSummary,Books.UserID = @UserID,Books.BookComment = @BookCommentWhere Books.BookID = @BookIDINSERT INTO BookLog(BookID,BookEntryDate,BookSummary, UserID,BookCommentLogDate)VALUES (@BookID,@BookEntryDate, @BookSummary,@UserID, @BookComment,@Now)RETURN GO
View 4 Replies
View Related
Jun 4, 2008
hi, i have a sqlserver2005 table 'member' with the fields 'id','name','datecreated','datemodified' and 'memberlevel' (which the default value is 1).
Now i want to create a stored procedure that will run automatically once a day,
this procedure will reset to 0 the 'memberlevel' that are 1 if the 'datemodified' is greater than 3 months.
can anyone help me on this, thanks!
View 2 Replies
View Related
Jun 12, 2008
When I verify syntax, I get this error:
Msg 1038, Level 15, State 4, Procedure webservices_BENEFICIAL_USES_DM_SELECT, Line 8
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Here's the proc:
CREATE PROCEDURE webservices_BENEFICIAL_USES_DM_SELECT
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME varchar(40) = ""
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT*
FROMBENEFICIAL_USES_DM
END
GO
Thanks.
View 6 Replies
View Related
Mar 31, 2007
Hey all,
Can anyone tell me why this stored procedure doesn't work?
ALTER PROCEDURE [dbo].[RemoveLocation]
@Id int,
@Name varchar
AS
DELETE FROM Classifieds_Terminals
WHERE [Id] = @Id and [Name] = @Name
I try exeuting it like this:
USE [CLASSIFIEDSDB2.MDF]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[RemoveLocation]
@Id = 18,
@Name = N'Terminal A'
SELECT 'Return Value' = @return_value
GO
It returns 0 and nothing happens....???
Here is the table:
id | Name
18 Terminal A18 Terminal B18 Terminal C
View 2 Replies
View Related
Apr 2, 2007
Hi,
I got a problem of a sp. Did the sp can use over one GO script? It is because after I create the below scripts in the sp and checked no syntax error and then apply changed. I re-open this sp, it just save my scripts upto the first GO statement . The scripts from "IF EXISTS..." has not saved. But I try to run the script in analyzer is successful. How can I fix it in Enterprise manager?
Thanks.
Here is my test code:
CREATE PROCEDURE sp_Test AS--set nocount onSELECT id, name from MainGO--insert the record in SimpleReports tableIF EXISTS (select ID from Main where id=5)delete from Main where id=5GOINSERT INTO Main ( ID, Name, Quantity)VALUES ( '6', 'hij', '60')GO
View 4 Replies
View Related
Jan 16, 2008
I have what I hope is a simple question about a stored procedure. If this is not the right spot for this, I offer my apologies.
I am planning on calling a stored procedure from my ASP.Net site using C#. What I want to do is pass a variable to the procedure and using that varible, put together another variable that I will pass back.
I am very new to all but the most basic stored procedures so I was not shocked when my code didnt work. Can anyone toss me a hint? I am actively working on this and if I find anything on a website to assist and have a breakthru, I will be sure to update you all.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goCREATE PROCEDURE [dbo].[GetAttySectyData] (@ID as int) AS
declare @First as varchar(200)
declare @Middle as varchar(50)
declare @Last as varchar(200)
declare @Name as varchar(255)@First = SELECT value FROM dbo.text WHERE efield_id = 10741 AND employee_id = @ID
@Middle = SELECT value FROM dbo.text WHERE efield_id = 10906 AND employee_id = @ID
@Last = SELECT value FROM dbo.text WHERE efield_id =10740 AND employee_id = @ID
Select @First+' '+@Middle+' '+@Last from text AS @Name
RETURN
View 3 Replies
View Related
May 21, 2008
I am using SQL Server Express within visual studio and am needing to create a stored procedure. The proble is that every time I finish and close it then it asks me if I want to save the changes which of course I want to.
But then it always comes up with a message box that says "invalid object name" followed with the name of the stored procedure.
??? why? I try creating a stored procedure from scratch or copying the code of another stored procedure. It alway gives me that message and does not let me save the stored procedure. I tried changing the default name of "dbo.storedprocedure1" or even leaving it as is and always I get the message that says "invalid object name: dbo.storedprocedure1" and it does not let me save the procedure.
What am I doing wrong?
View 5 Replies
View Related
Jan 28, 2005
I am trying to figure out the syntax for an SQL statement I have in a stored procedure.CREATE PROCEDURE dbo.forum_post_add
@forumId integer,
@fpTopic char(100),
@fpBody text,
@fpPoster integer
AS
Insert into forumPost (forumId, fpTopic, fpBody, fpPoster) values (@forumId, @fpTopic, @fpBody, @fpPoster)
UPDATE forum SET forumThreads = forumThreads + 1 where forumId = @forumId
UPDATE forumGroup SET fgThreads = fgThreads + 1 where fgID = select forumGroup from forum where forumId = @forumId
GOThe statement in question is the second UPDATE. How do I create an UPDATE statement that selects that correct record based on the single result of a SELECT statement?
I am also not too sure about the 'fgThreads = fgThreads + 1' thing, is this the best way to increment a value?
I know I should also be using transactions and probably a few other bells and whistles but I'll worry about that later.
Thanks
Martin
View 4 Replies
View Related
Jul 14, 2005
I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0.
If I set it to 0 which is disabled, the user can still login.
Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login
what are mine doing wrong ?
Please help
CREATE PROCEDURE DBAuthenticate
(
@username Varchar( 100 ),
@password Varchar( 100 )
)
As
DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )
SELECT
@ID = IdentityCol,
@actualPassword = password
FROM CandidatesAccount
WHERE username = @username and Activate = 1
IF @ID IS NOT NULL
IF @password = @actualPassword
RETURN @ID
ELSE
RETURN - 2
ELSE
RETURN - 1
GO
View 5 Replies
View Related
Feb 13, 2007
If I put 5 or 6 stored procedures within another stored procedure will they be executed sequentially or all at once? Is there any method. I can manage this process with some code I was just wondering how query analyzer or an sp would handle this by default.
create dbo.storedprocedure as update_data
exec sp_1
exec sp_2
exec sp_3
etc.
View 12 Replies
View Related
