Drop Create Vs Alter In Regards To Stored Procedures
May 17, 2007Anyone have any compelling arguements for using one over the other?
Thanks
Mercy
ADVERTISEMENT
SQL Security :: Deny Alter And Drop Permissions On ONLY Stored Procedures
Aug 19, 2015We have a generic sql login "prduser". Applications use this login. We want the login NOT to have ALTER PROCEDURE and DROP PROCEDURE permissions only on the stored procedures(there are thousands of them).
View 17 Replies View RelatedThoughts On ALTER Vs. DROP & CREATE.
Nov 8, 2006Thought I should post in the newbie forum for a while, instead. :-)
I have a couple of scripts that I've generated that drop a couple of system stored procedures and recreate them. I'm not sure why I did it in the first place, but I think it was that it wouldn't let me run an ALTER statement on them. Specifically, I'm now looking at sp_add_operator. I changed it to a 500 character email field instead of whatever it was (100, I think.)
/* Explanation: Why did I do that? SQL Mail is prohibited here, so I'm using CDO_Sysmail to email myself and the developers if a job fails. The list of people to email is determined by the owner of the database, who is also an operator in SQL. I get the list of emails from the email field of the operator properties. Hence, I need a bigger email field. Yes, I now know it would most likely be better to create an ADMIN database on each server for this kind of stuff. (Thanks to Tara for that blogged suggestion.) */
While I will probably go back to the default stored procedure, this got me to thinking: when would it be better to use an ALTER statement on a SProc rather than to do a DROP and CREATE?
Your thoughts, oh SQL gurus?
Stored Proceedures - How To Create/drop Them
Oct 10, 2006Hi What is the SQL syntax to create and destroy a stored proceedure with t-sql? Is there any good tutorials?ThanksPWP
View 5 Replies View RelatedSQL 7 And Can't Create Stored Procedures
May 11, 2007I am studying VC++6 and am involved in a database learning project. I installed a SQL 7 server on this W2K Pro machine a long time ago and applied SP4 to the server. The instructions are to create a stored procedure by right-click on the Stored Procedures folder icon in the Data View and selecting New Stored Procedure. There is no New Stored Procedure option displayed when I do this (Docking View, Hide, Properties). What must I do to get to ability to create a stored procedure. The sample database being used is the Pubs database.
View 1 Replies View RelatedCreate A File In Stored Procedures
Mar 1, 2000Can anyone tell me whether i can create/open a file inside a stored procedures??
I need to retrieve data from my database and then send these data to other users by
using xp_sendmail. I plan to arrange the data in a text file format.
Thanks for any reply.
Create Views In Stored Procedures?
Sep 25, 2007Can you create views within a stored procedure?
How To Create CLR Based Stored Procedures
May 3, 2006I need to create CLR Based Stored Procedures using VS2005 Standard Edition in Sql Server 2005 Standard Edition. Unfortunately, when I create a new project in VS2005. I can not find the template to create a Sql server project using C#. I re-install sql server and Vs2005, the problem still there. Does Vs2005 standard edition support create sql server project? Thanks a lot.
View 1 Replies View RelatedCannot Alter Or Drop Fields
May 15, 2006I must change replication schema from:
Publisher - SQL2000(8.0.2039), Disrtibuter/Subscriber - SQL2000
to:
Publisher - SQL2000(8.0.2039)(The same server), Disrtibuter/Subscriber - SQL2005(9.0.2047)(new server)
I have removed all subscription, publications, distributer and publisher. So there should not be now any information about replication.
Now I want to make some changes with tables and i try to alter and drop columns on publisher database. But it wont do... I always receive messages that tables and fields are being used in replication:
ALTER TABLE Clients DROP COLUMN ABCCode
Msg 4932, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because 'ABCCode' is currently replicated.
I tried to use sp_MSunmarkreplinfo and sp_removedbreplication but it did not help. I also tried to drop column with sp_repldropcolumn but got this message: Invalid object name 'sysmergearticles'.
Looks like server (publisher) still has information about old replication. But how to delete this information? And how can I alter and drop fields?
Thanks
Using The SQLDMO Reference To Create SQL Stored Procedures On-The-Fly And Execute
Nov 6, 2007I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object.
Here are some clipits of what you need.
Imports SQLDMOPrivate myServer As New SQLServer
Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString()
Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString()
Dim storedProcedure As New StoredProcedure
Dim qresults As SQLDMO.QueryResults
Example Stored Procedure
Try
myServer.Connect(serverName, userName, password)
storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
Label1.Text = "Creation of stored procedure successful"Catch ex As Exception
Label1.Text = "Creation of stored procedure failed"
Finally
myServer.DisConnect()End Try
Run Stored Procedure and Obtain Results
myServer.Connect(serverName, userName, password)
qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows
MemberRowNo = qresults.GetColumnString(num, 1)
Next
myServer.DisConnect()
Create Extended Stored Procedures In Visual Basic
Mar 9, 2001You know this sample ODS dll project:
$80oolsdevtoolssamplesodsxp_hello
I need to find a template like this in Visual Basic.
On this site I read through the xp_Encrypt project which was developed in VB. I did not see source code or a downloadable project file.
My searching on the internet hasn't yielded any practicle results, all examples are either how to use an extended stored procedure or are a MSVC++ project.
Anyone find VB related resources, anywhere? Books, TV, magazines????
Create Report With Complex And Nested Stored Procedures
Apr 16, 2007Hi all,
I had developed all my reports using CRXI and stored procedures of MSSQL 2000. Now I am migrating from CRXI to SSRS. But i have a problem because of my report stored procedures. These stored procedures are complex and nested. When i try to use procedures in a report, it gives a error. When i comment out my EXEC line in my procedures, it goes OK.
Please help me to do this ?
Using Alter Table To Drop Columns
Nov 5, 2000I am dropping 60 out of 133 columns on a user table, but am finding that the spaceused by the table ( as shown by sysindexes.dpages ) is not being freed up.
I have dropped and recreated all indexes on the table, with no effect on the dpages value, as well as running dbcc updateusage for the table.
Is it not possible to free up the space used by using this method ?
Is recreating and repopulating the table with only the required columns the correct / only way to do this ?
thanks,
manoj
Why Variables In A Alter Or Drop Does Not Work.
Oct 26, 1999I am writing a procedure to drop a specific user's schema only. I select all his/her table name and foreign key in a temp table first and then use a cursor to get the table name and foreign key, and begin drop foreign keys and drop table. However, sql think it is wrong with the syntax like "alter @table_name". Does anyone have a clue, what is wrong with my syntax. Or someone know how to drop one specific user's schema only (Other user may have same table in the database, and you don't want to drop that.
Attached is my scripts: please correct it. Thanks.
CREATE procedure sp_drop_schema @user sysname
as
select o.name table_names, o.id table_id, s.parent_obj Key_id, s.name key_name, u.name users_name
into #temp1
from sysobjects o inner
join sysobjects s on o.id=s.parent_obj
join sysusers u on o.uid=u.uid
where s.type='f' and u.name=@user
declare cur_f cursor
for select table_names, key_name
declare @table_name varchar (80), @key_name varchar (80)
open cur_f
fetch next from cur_f into @table_name, @key_name
while @@fetch_status=0 begin
alter table @table_name
drop constraint @key_name
fetch next from cur_f into @table_name, @key_name
end
close cur_f
deallocate cur_f
drop table #temp1
select o.name table_name, o.id table_id, o.uid users_id, u.name users_name
into #temp2
from sysobjects o
inner join sysusers u on o.uid=u.uid
where type='u' and u.name=@user
declare cur_temp cursor
for
select table_name
from #temp2
declare @tab_name varchar (80)
open cur_temp
fetch next from cur_temp into @tab_name
while @@fetch_status=0 begin
drop table @tab_name
fetch next from cur_temp into @tab_name
end
close cur_temp
deallocate cur_temp
drop table #temp2
When Do I Need To Drop Column Statistics When Doing An Alter?
Aug 30, 2007
I need to know definitively when I need to drop and recreate column statistics (histogram) when altering a column. Empirically, it seems I can lengthen a varchar or change a not null column to a nullable one, and then existing statistics dont' cause
Msg 5074, Level 16, State 1, Line 1
The statistics 'c1' is dependent on column 'c1'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN c1 failed because one or more objects access this column.
But, if I change a column to not nullable or shorten a column, I get the error.
Is this the complete description of when I need to drop and recreate column statistics and when I don't?
Alter Column Drop Identity
Apr 18, 2008Is there any way to remove the IDENTITY property of a column? I originally used it, but it caused so many headaches with ADO.NET that I've decided to just increment the darn things manually. So I want to turn it into just a normal integer column.
If there's no direct way, can someone suggest a quick way to create a new column, copy all the old values into the new column, change the primary key to the new column, and drop the old column?
Thanks.
Alter Table Drop Column Error
Apr 11, 20011. We have user defined type (Sql type = bit ) with rule setting it to 0
2. New column(isVIP) was created using this user defined type
3. sp_unbindrule was used to unbind rule from new column (isVIP)
4. Alter table Client drop Column isVIP !!!! (invalid syntax )
Any ideas ?
Thanks
How To Alter Table Drop Constraint @variable?
Jun 12, 2003I need to run the alter table to drop a default. However, the default name is kind of 'dynamic' from around 1000 databases, thus I need to run the following sql to get the name to a variable. Now, it looks the alter table statement does not like to drop a vaiable, is there a solution about it?
declare @radius_default varchar(40)
select @radius_default = (select sobj.name from sysobjects sobj
inner join syscolumns scolumn
on sobj.ID = scolumn.cdefault
where scolumn.name = 'radius' and sobj.name like '%LandMarks%')
print 'Need To Drop @radius_default: ' + @radius_default
--====================================
alter table LandMarks drop constraint @radius_default
thanks
David
Is There A Alter Table Xxxx Drop Default Yyy ?
May 11, 2005Hi:
I need to change a column's datatype from tinyint to int as follows:
alter table tableName
alter column column1 int
but with error <<'DF__LandMarks__color__6A50C1DA' is depending on it.>>
However, this old default is not part of the constraint. Thus, the only way is to delete it from sysobjects.
unfortunately, the following code I have to commented since they could only be executed line by line, not within a begin...end block.
--exec master.dbo.sp_configure 'allow updates', 1
--reconfigure with override
--delete from sysobjects
-- where name = 'DF__LandMarks__color__6A50C1DA'
-- and type = 'D'
--exec master.dbo.sp_configure 'allow updates', 0
--reconfigure with override
I need to update 10 server around 2500 databases with this change.
I have looked INFORMATION_SCHEMA related views, but not found default related, maybe I missed something.
thanks
David
SQL 2012 :: How To Drop Columns From Alter View
Mar 20, 2015I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.
Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .
The view is called TEMP_EXPORT_1
I can either use the code below to return a list of columns that I want removed:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'
Or I can use the code below to return the list of columns that I want to keep:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'
Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...
Therefore I tried the following but I'm not sure of the syntax:
ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1
Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Jun 15, 2006Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedSQL Server 2012 :: Stored Procedures Compiles Even When There Is No CREATE TABLE For A Temp Table
Feb 11, 2015i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
Oracle Stored Procedures VERSUS SQL Server Stored Procedures
Jul 23, 2005I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedQuestion About Procedures To Create Procedures In A Different Database
Jul 23, 2005I'm trying to write a procedure that having created a new database,will then create within that new database all the tables andprocedures that go with it.In doing this I'm hitting the problem that you can't issue a USEcommand within a procedure.So my question is either- how do I get around this?- if I can't, how can I create procedures etc in a *different*(i.e. the newly created) databaseor- is there a better way to do all this (*)I have SQL files that do this currently, but I need to edit in thename of the database each time before execution, so I thought aprocedure would be better. Also I'd like eventually to expose someof this functionality via a web interface.Although I'm a newbie, I feel I'm diving in the deep end. Any goodpointers to all the issues involved in this aspect of databasemanagement would be appreciated.(*) One thought that occurs to me is to have a "template" database,and to then somehow copy all procedures, tables, view etc from that.--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
View 10 Replies View RelatedALTER TABLE DROP COLUMN LastUpdated Failed Because One Or More Objects Access This Column.
Mar 7, 2008Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
Create Or Alter Procedure
Nov 13, 2007I 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?
Drop And Again Create SP Will That Help???
Jun 5, 2008Hi
I am wana to know why we every time drop the procedure and again create it as below...will that impact on performance or compilation of SP???
Use Northwind
GO
IF OBJECT_ID('dbo.ListCustomersByCity') IS NOT NULL
DROP PROC dbo.ListCustomersByCity
GO
CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%'
AS
SELECT City, COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country LIKE @Country
GROUP BY City
GO
T.I.A
Stored Procedures 2005 Vs Stored Procedures 2000
Sep 30, 2006Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Using Alter Statements To Create Indexes?
Apr 19, 2006I am new to writing SQL code and I read that you can use ALTER statements to create an index for a table. How would I go about doing that? Everything that I have tried in Query Analyzer comes up with an error.
Any help is appreciated.
CREATE, ALTER, Why Wont It Save?
May 11, 2007
when i create a new stored proc, i say "CREATE PROCEDURE [sprocName]"
but the next time i edit the stored proc, it hasnt changed to "ALTER ..."
I manually change it to ALTER, and run it, and close it, and it reverts back to "CREATE..."
Is there some sort of privelage i have to enable? Has anyone encountered this before?
Help guys, please :-S
Also, if you know of a better place where i can post this thread, please let me know
Drop And Re-create Table
Jul 27, 2006
What kind of problems may I see if a process drop and re-create a set of tables every night?
Drop And Create FK On A Table - 6.5
Oct 19, 2001Hello,
What's the command for dropping an existing Foreign key on a table and reacreating it in SQL Server 6.5
Thanks
Sri
Drop-create Table
Mar 17, 2000Hi,
I had a question and did not obtain an answer. So I am trying to rephrase and ask again, in case I was not clear the first time.
When we drop and recreate a table, do we always have to recompile the stored procedures that reference the table? Or is only under certain scenarios that we need to do so- like if an index on the table is changed
Please let me know
Thanks in advance
Kiran