how can I drop a propcedure or a view without error for MS SQL 2000/2500
for a table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[myTABLE]
and for a procedure
IF EXISTS ???
DROP PROCEDURE [dbo].[SP_myTABLE_Count]
thank you for helping
i am using vb.net and ms sql server 2005 express.....what is the syntax for dropping a table if existsi have used this but it says incorrect syntax near if Dim cmda As New SqlCommand("drop table " + test + " if exists", New SqlConnection(strdb)) cmda.Connection.Open() cmda.ExecuteNonQuery() cmda.Connection.Close()any solutions???? plz only answer in vb.net and sql server express
View 8 Replies View Relatedin mysql i can drop a table or db if it currently exists using
drop table if exists [table1] or
drop database if exists [db1]
is there an equalivant in ms sql
thanks
I'm trying to drop indexes if they already exist then recreate them as needed, however, I'm getting an error if the index does not exist and I thought that's what the "IF Exists" statement was for.
syntax:
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_acct_no') DROP INDEX accounts.idx_acct_no
go
create index [idx_acct_no] ON [dbo].[accounts] ([acct_no])
go
Error when index does not exist:
Server: Msg 3703, Level 11, State 7, Line 1
Cannot drop the index 'accounts.idx_acct_no', because it does not exist in the system catalog.
Is there anyway to not get this error?
Hi all,
I come from MySQL.
In sqlserverce
I'm looking for an equivalent command..to
"DROP INDEX IF EXISTS inventory.idxItems"
or somthing similar so that I do not get an error or crash the C# app when I try to drop a nonexisting index..
also for droping a table.
I need to drop a table if exist ?? how can i do that ??
thanx !
Hello,Using Enterprise Manager, I deleted from my database the only tablethat contained records (Right-Click on Table, Choose "Delete Table").My expectation was that the LARGE .mdf file would be reduced to minimalsize (at least as small as the Northwind MDF). However, it's still 4+Gig in size!! (Northwind is 2.62 MB). This is a problem, bc I deletedthe table in order to recapture hard drive space.NOTE: I already tried using Shrink Database in EM. This significantlyreduced the size of the .mdf file from its original (i.e. pre-delete)size of 38 Gig to present size of 4 Gig.What can I do to further reduce the size of the MDF file?Thank you.
View 1 Replies View RelatedWhat is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end
View 5 Replies View RelatedHello all.
Can anyone tell me how to go about establishing if a particular view exists on a table?
Thanks.
We have a table created by an application, and a view that joins the table with other tables.
For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.
However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.
Anyone know what could be the problem here?
hello,
im creating a sp that creates a view from a query then bcp to csv file.
my problem is that when i start the sp, it complains the sp already exists....yes it does, however, i don't if im going about this the wrong way...but i tried the following to no avail
DROP IF EXISTS v_participantTrades
now, does that syntax not work for views?
I am trying to check if a view exists on a linked server using sys.views. I tried to fully qualify it but that produces an error telling me the below, which both the database name is correct as well as the server name. Is it possible to obtain a list of views from a linked server connection? Msg 7314, Level 16, State 1, Line 321 The OLE DB provider "SQLNCLI10" for linked server "alpha" does not contain the table ""salesdata"."sys"."views"". The table either does not exist or the current user does not have permissions on that table.
SQL Server 2008 is the server I want to query from and sql server 2000 is the server I want to query even if I try to use this syntax it still produces the above said error
Code:
select
count(*)
from
alpha.salesdata.INFORMATION_SCHEMA.VIEWS
I also tried to qualify the views by using the below and still same error
Code:
select
count(*)
from
alpha.salesdata.INFORMATION_SCHEMA.VIEWS
where
table_schema = 'dbo'
Hi,
I increased one of my base tables column which is referenced in view
I noticed sql server didn't recognized this change and its still showing old field size in the view.
I can simply drop and create it again. But wanted to know if there is any way (command/sp) to recompile the view which will be easy to deploy in production as patch.
Thanks
- D
Is there any way around (or will there be) using the drop-down? It takes several minutes when running against an Oracle Apps database to populate that dropdown with the several hundreds of tables and views.
TIA.
I 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.
I am work on SQL Server 6.5
When I want to drop a view then appear error message :
"Msg 3701, Level 11, State 1 Cannot drop the view 'EUL_ODBC_SCHEMAS',
because it doesn't exist in the system catalogs."
Has anybody else experienced this, and do you know what is causing the warning?
Thanks in advance for any replies.
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
Louis writes "Is it possibble to write a SQL statement that returns "True" or "False" if a stored procedure exists. Basically write a SQL statement that checks whether or not the Stored Procedure exist in the database???
Thanks in advance"
Hi,In my stored procedure I would like to do something like :IF EXISTS(SELECT WebIdFROM WebsWHERE FormatDateId = @FormatDateId)BEGINSELECT WebIdFROM WebsWHERE FormatDateId = @FormatDateIdSET @RetVal = -1ENDIs there a nicer way to do this, so that we don't have to do therequest twice ?Regards
View 2 Replies View RelatedI am using the following code to insert records into a destination tablethat has a three column primary key i.e. (PupilID, TermID &SubjectGroup). The source table records all the pupils in a school with(amongst other things) a column (about 50) for each subject the pupilmight potentially sit. In these columns are recorded the study groupthat they belong to for those subjects. The destination table holds arecord per pupil per subject per term, against which the teacher willultimately record the pupils performance.The code as shown runs perfectly until the operator tries to insert aselection of records that include some that already exist. What I wouldlike it to do is, record those, which do not exist and discard theremainder. However, whenever a single duplicate occurs SQL rejects thewhole batch. I know that my solution will probably involve using the‘NOT EXISTS’ expression, but try as I might I cannot get it to work. Tofurther complicate things, the code is being run from within VBA usingthe RunSQL command.The variables ‘strFieldName’, ‘strGroup’ & ‘strTerm are declared at thestart of the procedure and originate from options selected on an Accessform.INSERT INTO dbo.yInterimReportData (PupilID, LastName, FirstName,TermID, SubjectGroup) SELECT PupilID, LastName, FirstName," & "'" &strTerm & "'" & "," & "'" & strGroup & "'" & "FROM dbo.Pupils WHERE (" &strFieldName & " = " & "'" & strGroup & "')Any Ideas?RegardsColin*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedHi,
I am a very new programmer hired to do stored procedures.
I want to drop stored procedures with like names..Can I do that with single command something like
Drop Proc where proc like 'XY%'
Other alternative is write a stored procedure which is a group of stored procedures.. selected from sys.objects.
Please help me ..
Thank you
Swapna
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.
Hi All.
Can anyone tell me what the syntax to check and see if a stored procedure exists and if it doesnt to create it is?
Thanks people.
When I created an SP in Enterprise Manager, I didn't manually type in the existence check at the inception, a la "if exists (select * from sysobjects...)". I just started with the CREATE PROC AS statement.
I noticed that if I generate a SQL script for the SP, SQL2000 automatically generates the existence check statement.
My question is, can I assume that when the SP is actually executed, SQL2000 does the exists check on its own? I EXEC'd the proc in Query Analyzer with no errors.
I just want to make sure that I don't need to enter the exists statement if it's already being done behind the scenes.
Trying to create a package that will copy all user stored procs from one database to another - on the same server.
I am using a Transfer SQL Server Objects Task to do this.
I am getting the error below.
Warning: 0x8002F356 at STORED PROCS, Transfer SQL Server Objects Task: Stored procedure "{0}" already exists at destination.
DropObjectsFirst is set to True
Can anyone help? Thank you in advance.
I'm using SQL Serv 6.5. I've got a stored procedure that is DEFINITELY not in use but i can't drop it because it's telling me it is ???
I don't want to stop and start SQL Serv. Any ideas ??
thanks
Paul
I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?
Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...
Thanks
I'm trying to put scripts to create our stored procedures under version control.
However I don't want these scripts to be run if the stored procedure already exists (we'll be using update scripts to alter existing stored procedure I think).
Anyway I tried :
Code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.getEnglandHotelsByATOPResort Script Date: 26/10/2005 10:40:01 ******/
if NOT EXISTS (SELECT object_id('procedureName','p'))
CREATE PROCEDURE [dbo].[procedureName]
@location char(2)
AS
...Procedure...
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I know that the if NOT EXISTS ... part works as doing the following returns the expected result :
Code:
IF NOT EXISTS (SELECT object_id('procedureName','p'))
SELECT 'FALSE'
ELSE
SELECT 'TRUE'
However when trying to do this before the CREATE PROCEDURE I get :
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'PROCEDURE'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@location'.
Any ideas how to do this?
-D
Hey,I'm writing some automated unit tests for a database app. The appcreates a database via script. I'm developing the system in atest-driven manner and need a way to determine if a parameter existswithin a stored procedure using SQL. Any ideas?Thanks,WillJoin Bytes!
View 3 Replies View Relatedhi there
I am using SCPTXFR.EXE to generate script of the database(for backup),using following command.
SCPTXFR.EXE /s abc /d aaa /P abc12345 /f D:HSE0607SCHEMA.sql /q/r/T
Now the script is generating fine, but what i find that there is no statment like
"if not exists(...) "
for the Stored Procedure( i had applied the /r parameter), for the Create Tables it is there.
Why there is no statment IF NOT EXISTS(...) for Stored Procedure?
I need this Line, how can i do this??
Regards,
Thanks.
Gurpreet S. Gill
I'm writing some code to create stored procedures in a database. In order to test it out, I deleted a stored procedure (right clicking in SQL Server 2008 and clicking on delete) and then ran my code to see if it would create it.
My code looks like this:
Code:
SqlCommand command = new SqlCommand();
SqlConnection conn = database.TypeLibraryDatabaseConnection;
command.Connection = conn;
// create the command to create the stored procedure
command.CommandText = database.GetProcedure(node.Name);
// create the stored proc in the database
try
{
command.ExecuteNonQuery();
}
catch
{
}
command.Dispose();
database.GetProcedure(node.name) basically gets a string containing the SQL script to create the stored procedure.
command.ExecuteNonQuery() throws an SqlException that says: "There is already an object named 'SecuritySession_DeleteSessionById' in the
database." But I deleted it! Why does it think it's still there?
How can i delete a stored procedure if it exist
like "Drop stored procedure 'sp1' if exist"
I have SQL SERVER 2005 with SP2 on windows XP professional.
When I execute DROP PROCEDURE manually,it works.
However when I execute inside a loop made using static cursors,
it generates following error
"Could not find server 'drop procedure dbo' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."
OR it gives this error "Could not find stored procedure"
any idea???