The Old Inability To Toggle/change/switch Between ALTER PROCEDURE &<---&> CREATE PROCEDURE Bug (or Is It A Feature?)
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 Studio
2005 (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, akin
to 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 two
tables (called Author and Content), linked by a single key, form a
relationship.
By simple comparison of the source code in the textbook and my program
(below) I found the difference: instead of, like in the textbook, the
Stored 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 MS
Visual Studio 2005 Pro edition of "There is already an object named
XXX in the database", see *|* below). No matter what I do, the SP is
always 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 happen
over the years but it's not clear what the workaround is)
Keep in mind this is my first attempt and I have ordered some
specialized books on SQL, but if this is a common problem (and I
suspect 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.
Also this bug might be relate to the fact I've switched (and not
rebooted) from Administrator to PowerUser after successfully changing
the permissions in the SQL Server Management Studio Express (see this
thread: http://tinyurl.com/2o5yqa )
Regarding this problem I might try again tommorrow to see if rebooting
helps.
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 and
SQL 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 Stored
Procedure: "There is already an object named 'InsertAuthor' in the dB
help i need to create stored procedures that run once month change value (UPDATE) once a month in one table the values from 1,2,3,4 next month 2,3,4,1 ..... 3,4,2,1 .......4,1,2,3 ..... 1,2,3,4 i have this code
Code Snippet SELECT empID, location, ISNULL(NULLIF (( location + DATEDIFF(mm, location_date, GETDATE())) % 4, 0), 4) AS new_location FROM dbo.empList
like this the employee go from one location once amonth to new location
table on this month (5) empid location location_date --------------------------------------------------------------
1111 1 222 2 333 3 444 4
5555 3 666 2 777 3 888 4
table on next month (6) (after a month) empid location location_date -------------------------------------------------------------------
Any One Tell me how i can use N'Switch in SQL Server Stored Procedure . When I use Such a this syntax i am encountered and stoped with a error. the is below(Stoerd Prdocedure Code) ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- CREATE proc sp_check_userpass(@username nvarchar(50),@password nvarchar(50),@first nvarchar(50),@last nvarchar(50),@email nvarchar(50),@stage int )as declare @cnt int begin select @cnt=count(*) from member_info where (@username=username and @password=password) if(@cnt=0) begin insert into atable(first,last,username,password,stage) values(@first,@last,@username,@password,@stage) end else return @cnt end GO ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- Other thing is that when exec this store proc it must be return a value return back to the @cnt but it is return only This( The command(s) Completed succefully). Any One guide me how i can must be do . i must clear it that i am using this query in ASP.NET SqlClient Object To Execute Query. Thanx In Advance. ArQa
I am having problem when performing the alter table switch. Both tables are identical and have pk.
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresults' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part' .
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON TResultsScheme (testresult_id) ) ON TResultsScheme (testresult_id)
I have performed the function on other tables successfully, but this is the first that has an identity column involved. Is there anything special that needs to be done?
Working on partitioning a few large tables. One of the tables included a text column and the €śTEXTIMAGE_ON [PRIMARY]€? clause which would prevent the partitioning of this table. After some research we found that the data was legacy and no longer used. We updated the column on the affected rows to NULLS and altered the column to a VARCHAR(20) When I attempted to run the ALTER TABLE SWITCH I encountered the error Msg 4947, Level 16, State 1, Line 1 ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresultsjoe' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part'. After a lot of grief and testing I determined that the message was bogus and the real issue is that the 'sys.tables' still has €ślob_data_space_id€? with a value of 1 for this table. I created a copy of the table with the text column altered to varchar and one with just the varchar to begin with. After copying data from the original table, I tried to run the alter switch. It failed once again for the text column altered to varchar table, but it worked for the varchar from the start.
Since it appears that this value is causing my issues, is there anyway to update the table in place. I know I can BCP the data out, but that would take too long and would defeat the advantage of using the alter switch method.
BOL States:
The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. This means we cannot update the table manually.
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.
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.
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.
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!!!
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?
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.
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
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 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.
Greetings, I have five rows in a group header. The top row is a true header row with details that will always be there and will always be visible. The bottom four rows have their Visibility.Hidden property set to 'TRUE'. All four of the bottom rows are assigned the same Visibility.ToggleItem. Of the four bottom rows, any or all of them may not have data at any one time. I want to find a way of allowing the rows to toggle from hidden to visible, but if one or all of the rows have a field item whose value is 0 then that row must remain invisible regardless of whether it is toggled.
I am still working on this puzzle, but if someone can provide me with an easy solution while I struggle, I would greatly appreciate it.
A customer wants to implement table partitioning on a replicated table.
They want to hold 13 months of data in the table and roll off the earliest/oldest month to an identical archive table. The table has a date field and partitioning by month makes sense all around.
So SWITCH PARTITION is the obvious solution to this, except for the fact that the table is replicated (transactional w/no subscriber updates).
What are his architectural or practical solutions to using table partitioning and replication?
I work at a medical billing office. We provide billing services for a number of clients. The primary billing software runs on a big AIX system, but for reporting and a few other things we get a daily dump into sql server. The way the dump happens each client get's their own database in sql server. This works great for reports that go to individual clients, but it can be a real pain for internal reports that cover all clients. We end up with sql code that looks something like this: SQL Code:
Original - SQL Code
ALTER PROCEDURE ProceName AS BEGIN CREATE TABLE #Temp ( Client varchar(5), FieldName varchar(20), OtherField int )
DECLARE @Client varchar(5) DECLARE @SQL varchar(8000) DECLARE curAll cursor FOR /*SQL Code to get Client list here */ OPEN curAll FETCH NEXT FROM curAll INTO @Client WHILE @@FETCH_STATUS = 0 BEGIN Set @SQL = 'SELECT ''' + @Client + ''', *' + ' FROM dump_' + @Client + '.dbo.TableName t' + ' WHERE t.FieldName=''somevalue'''
INSERT INTO #Temp Exec(@SQL)
FETCH NEXT FROM curAll INTO @Client END
--send results to application SELECT * FROM #Temp
--clean up CLOSE curAll DEALLOCATE curAll DROP Table #Temp END
ALTER PROCEDURE ProceNameASBEGIN CREATE TABLE #Temp ( Client varchar(5), FieldName varchar(20), OtherField int ) DECLARE @Client varchar(5) DECLARE @SQL varchar(8000) DECLARE curAll CURSOR FOR /*SQL Code to get Client list here */ OPEN curAll FETCH NEXT FROM curAll INTO @Client WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'SELECT ''' + @Client + ''', *' + ' FROM dump_' + @Client + '.dbo.TableName t' + ' WHERE t.FieldName=''somevalue''' INSERT INTO #Temp Exec(@SQL) FETCH NEXT FROM curAll INTO @Client END --send results to application SELECT * FROM #Temp --clean up CLOSE curAll DEALLOCATE curAll DROP TABLE #TempEND
As you can imagine, this is a real pain. There must be someway to dynamically change the current database in the procedure and run the code there directly rather than creating a string and calling out to exec 50+ times. Any ideas?
Hi, I have a schedule task which call one of my stored procedure, In this stored procedure, I need to change db owner of one of database, but I find sp_changedbowner do not allow me to specify db name,it only change current db,so I have to open a db before call sp_changedbowner,but it is invalid..
CREATE PROC demo as begin ...
use demo_db //it is invalid exec sp_changedbowner 'scott'
Hello. I am trying to change the owner of a stored procedure from a user ID to dbo. How do i do this. I have tried sp_changeobjectowner but I get an error message. Can I do this from Enterprise Manager? btw, I am using SQL 7.0. Any help would be appreciated. Thanx
Can someone explain to me why the following doesn't work?declare @oname sysnameselect @oname=name from sysobjects where name like"df__mytable__mycol%"alter table mytable drop constraint @oname
I am using a web application using asp.net 1.1 vs 2003. i am using sql server authentication where users are using their sql server user id and sql server saved passwords. how can i change their passwords inside the sql server? is there a system sp that allows one to pass one's user id and then change password. I will need to call that sp from a user defined store dprocedure and pass the parameters and that will change the password on the sql server. thanks
My scenario is that I want to change the default SQL server format in my stored procedure more preferably only during the course of stored procedure execution (not permanent changes does any one have idea that how will I able to achieve this simple task...
I built a database by using a generated script from the originaldatabase. It built the System Store Procedures as User type. How do Ichange them back to System type?
