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
Hi
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
What kind of problems may I see if a process drop and re-create a set of tables every night?
Hello,
What's the command for dropping an existing Foreign key on a table and reacreating it in SQL Server 6.5
Thanks
Sri
Hi,
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
Hi all,
I have 2 questions:
1. How can I drop /remove a complete db from MSDE desktop engine?
2. After exporting my db onto MSDE server and get connected those db tables in FE (adp), how can I create a new table in the same back end db?
I know these are basic questions but since I am new to MSDE I hope the forum would bear me.
With kind regards,
Ashfaque
Hi,
How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how....
The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":
Dim db as ADODB.Connection
'... open connection to database
Dim strCmd As String
strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"
db.Execute strCmd
Thank you in advance
Hi, all,
I want to re-create an index on a production table.
I got an error 644 "could not find index entry...".
The DBCC CHECKDB and CHECKTABLE gave me this:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 37575172, index ID 6: Page (1:939782) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 37575172, index ID 6, page (1:939782). Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 7 and 36.
DBCC results for 'Mfg_DFSFNSF'.
There are 1142314 rows in 326143 pages for object 'Mfg_DFSFNSF'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Mfg_DFSFNSF' (object ID 37575172).
The table script for the index is like this:
CREATE UNIQUE INDEX [Mfg_ITMDH_MbrIdx] ON [dbo].[Mfg_DFSFNSF]([_ITMDH_OwnRow], [_ITMDH_MbrKey], [RECTYPE]) WITH FILLFACTOR = 70 ON [PRIMARY]
My question is that can I drop it and run above create it to fix the problem in live mode?
I know the other option will be:
DBCC CHECKTABLE (FSDBMR.dbo.Mfg_DFSFNSF, repair_allow_data_loss)
But that has to put the db under single user mode.
Thanks!
Hi 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 RelatedIf I rebuild the clustered index using this option (Drop Existing) without specifying the Fill Factor, would it it retain the original Fill Factor?
Thank you
How to SQL server save information about create/drop objects action. How can I get this? Example for, a lot of objects(sp) in my database has been dropped, how I know who was dropped them (user login & time)?
View 1 Replies View RelatedHi All,
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
Thanks,
Srinivas
Thought 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?
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
Hi All,
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
Thanks,
Srinivas
I have a package that i want to move between enviroments. Therefor i need to create a package that creates all my tables on the new server.
like
-- "if exists (select * from dbo.sysobjects where id = object_id(N'[table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [table]
GO"
The case is that i have around 30 tables, and my question is therefore
I know that you can create the drop and create script for one table at the time in the SQL Server Management Studio, but is there an easier way to do this
When I drag a GridView from the toolbox onto a Webform, and then configure its DataSource is a true DataSet created that I can access in the code behind? When my results return I want to be able to access individual rows and cells, taking their values, assiging them to variables and then using the newly equated variable to perform calculations.
Thank you,
Greetings
I am thinking of creating a script that generates another script for creating/dropping tables, indeces, FK, views, stored procedures..etc in the right order in a specific database. I accept the fact this script is dangerous (like if I ran it on production server). But it sure will be very helpful when moving our SQL objects from development to qa and then to production. Is this worth it? Haev you see anything that generates such a thing?
Thanks
Greetings
I was directed to post this here. I am thinking of creating a script that generates another script for creating/dropping tables, indeces, FK, views, stored procedures..etc in the correct sequence in a specific database. I accept the fact this script is dangerous (like if I ran it on production server). But it sure will be very helpful when moving our SQL objects from development to qa and then to production. Is this worth it? Have you see anything that generates such a thing using sys.objects,sys.indexes,sys.foreign_keys etc?
Thanks
what are the minimum permissions to allow a user to view, create, & drop tables within a DB (SQL 2008)?
View 4 Replies View RelatedAnyone have any compelling arguements for using one over the other?
Thanks
Mercy
I need a way to push create index statements drop to the subscribers. If I can use replication to distribute index creation that would be great. We don't allow our clients to make any schema changes directly. We are using transaction and merge replication.
View 4 Replies View RelatedHow to script all the PK/FK/constrainnts/indexes with create and drop statements?
As you know, we can't script 'drop statements' for primary keys etc..
Any help in giving me a script which does all the above is greatley appreciated..
Thanks,
Di.
hello everyone,we dropped the clustered & nonclustered indeces on a table, thenrebuilt them. logical fragmentation is near zero, but extentfragmentation is about 40%. how can this be if the indeces are brandnew?
View 2 Replies View RelatedHow do I allow a user (or group of users) permission to create/drop a table?
I have found the 'GRANT CREATE TABLE TO username' command, which will (I assume) allow a user to create a table, but how to I allow a user to 'DROP' the created table as well?
'GRANT DROP TABLE TO username' doesn't work?
and I want the users to be able to DROP/DELETE this table (temporary table created just for printing purposes) as well.
thanks
A heavily-selected database will be in an inconsistent state for several hours during a batch process. For that time, a database snapshot is created and accessed instead. To allow constant client read access to the database, a database that only contains synonyms exists. Those synonyms point to the main database except during the batch process, at which time they point to the database snapshot.
To switch the synonyms, each synonym is dropped and then created pointing to the database snapshot (after its creation, of course). The drop/create occurs inside a transaction. Roughly, the SQL looks like this:
SET XACT_ABORT ON;
BEGIN TRANSACTION;
DROP SYNONYM [dbo].[some_proc];
CREATE SYNONYM [dbo].[some_proc] FOR [snapshot_db].[dbo].[some_proc];
GRANT EXECUTE, SELECT ON [dbo].[some_proc] TO public;
COMMIT TRANSACTION;
When the batch update is completed, the process is reversed with "snapshot_db" replaced with "regular_db". The SQL snippet above is dynamic SQL. What I've pasted is the dynamic SQL that is executed as a single batch.
While this switch is happening, clients are accessing the procedures through the synonyms, potentially at a high request rate. Testing reveals that clients can get the error:
Error=-2147217900, Id=0, Meaning=IDispatch error #3092,
Source=Microsoft OLE DB Provider for ODBC Drivers,
Description=[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'dbo.some_proc'.
This error only occurs once. If the same SPID retries its request and the transaction has not completed (for testing, a delay was added), then it blocks until the transaction completes.
Any way to prevent it besides a client-side retry?
I am splitting data from SQL table and sending it to excel file but everytime i rerun the package ,it appends the existing data in excel file ..I tried using execute sql task with excel connection and write drop table `tablename` and then one more execute sql task with create table `tablename` (`Id` int ,`fname` varchar(100)) ....But it does not seem to work.
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
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
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
hi 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
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???