Rollback Will Drop Created Tables And Drop Created Tables In Transaction..?
Dec 28, 1999
Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
View 1 Replies
ADVERTISEMENT
Jun 29, 2015
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
View 3 Replies
View Related
Oct 9, 2006
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies
View Related
Mar 2, 2008
Does anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies
View Related
May 7, 2008
This procedure runs and I see my GM names come up one at a time but I get no tables created.
Sorry about the sloppy code - I'm not a real pro.
declare @@GM Char(100)
declare @SQL VarChar (2000)
Declare spot cursor scroll for
select GM from BIM_Historical_Performance.dbo.Performance_Master
open spot
fetch first from spot
into @@GM
While @@Fetch_Status =0
BEGIN
set @SQL = 'select Comp, Billto, Cust_name as Customer, Branch, BAC, [MgMt_Type], BondValue as Bondbucks , BondValueAlloc as Allocbucks, c1 as Curcode, u1 as CurUnderP$, s1 as Cur3mthBIMsales, p1 as performance, I1 as Inside, [IS_since] as Insidesince, O1 as Outside, [OS_since] as Outsidesince, c2 as CodeM-1, c3 as CodeM-2, c4 as CodeM-3, c5 as CodeM-4 ,c6 as CodeM-5, GM into ' +@@GM + ' from BIM_Historical_Performance.dbo.Performance_Master where BIM_Historical_Performance.dbo.Performance_Master.gm = ' +@@GM
EXEC (@Sql)
Fetch NExt from spot
End
View 1 Replies
View Related
Oct 31, 2005
I want to know who had created the new tables,How can I achieve it?
Can I know it by using the sysobjects table?
View 7 Replies
View Related
Feb 20, 2008
Hi everyone.. I have a big problem.. :p
I have created a installer for my application and database.
When I run the installer it only creates the database but no tables created. But in some other workstation it works fine. I dont know what causes this problem....
Please guide me.
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
View 10 Replies
View Related
Nov 24, 2000
Can Query Analyzer deal with this or not???
Select FirstName into FirstNameTable From NameTable
Select Count(*) from FirstNameTable
Fails with FirstNameTable as invalid object.
TIA
View 5 Replies
View Related
Jan 31, 2008
Hi,
Is thr any query to drop around 20 tables at a time????
or shud i need to drop them individually one at a time???
View 1 Replies
View Related
Jan 21, 2002
Hi,
I want to create a nested SP which will dump out the results of All Temp Tables that are currently created in the session. The purpose of this is for createing a generic debugging SP which will write the contents of all temp tables to a debug table (when a certain flag is set).
I need to know how to:
- Get a list of all temp tables created
- Get a list from each temp table of the columns created.
Hope someone can help!
Cheers,
Andrew
View 1 Replies
View Related
Aug 25, 2006
Hi,
I have created a bunch of tables in the Master db by mistake.I want to drop those tables.Please tell me away to drop those.
Thanks!!
View 2 Replies
View Related
Feb 12, 2008
Are they unique to a user/session? Like if 2 users simultaneously run the stored procedure?
TIA!
View 13 Replies
View Related
Apr 25, 2007
what is the sql query to drop all tables in a database in sql server 2000
View 5 Replies
View Related
Mar 7, 1999
Hi,
I want to write a script that will drop all tables in a database that begin with BACKUP.
Is there an easy way of doing this?
Thanks
Phil
View 1 Replies
View Related
Feb 1, 2007
I have a list of 35 tables that need to drop the primary key index from in my database.
My problem is as follows for these 35 tables:
1. How can I get a list of all the primary keys for this subset of tables in my database
2. How can I drop just the PK for each of these tables?
I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.
Thanks
View 9 Replies
View Related
May 30, 2008
I need to drop multiple tables in an SP... all the tables starts with a string that I can use... please suggest me the best way to drop all these tables in an SP that has more than just this.. Thanks!
View 2 Replies
View Related
Dec 6, 2006
I need to create a file that removes (drops) all of your database objects.
View 12 Replies
View Related
Jun 15, 2007
Hello all, im using visual web developer btw. Im using the excellent tutorial here at http://www.asp.net/learn/videos/view.aspx?tabid=63&id=49 Works a treat. For my catalogue/database. i have 2 tables using the drop down menu - one is a "Buyers guide" (a list of the product) and an "application list" (this one is a list of the motorbike).
So in essence they are the same tables, but of course moved around with slightly different ways. What i would like to, is to some how make a refferenced link to each - Once one of the drop down menus has been linked to a product, theres a column that tells u what bike is being used....i therefore want to have a link so the seconmdary drop down menu - but i do not know how i can do this. Any ideas guys/gals? thx.
View 1 Replies
View Related
Mar 14, 2008
When I am initializing a transactional replication from SQL 2005 to Oracle 10g, SQL tries to drop tables that don't exist. The properties option for the articles specifically states "if the name is in use:". The name is not in use, yet SQL still tries to drop non-existent tables, which causes the replication to halt. Anyone seen this before, or have any ideas what to do about it?
Thanks!
View 10 Replies
View Related
May 24, 2008
Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose,
I have four data tables with the same structure but records may be
different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I
want to that two records by filtering all tables and sum of rates and
want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance.
View 2 Replies
View Related
Sep 11, 2014
I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?
View 1 Replies
View Related
Sep 10, 2015
I'm in the process of migrating over nearly 900 reports to a replicated server.
I have moved over 100 reports, stored procedures and their dependent objects so far.
I have two tables that are not being applied to the subscriber.
View 9 Replies
View Related
Jul 21, 2003
How to find out all the statistics from all the tables and drop them..any script anyone can help with?
When we are trying to make datatype changes in few related tables,it's giving error saying that some statistics are dependent onthe column blah blah...
Thanks,
Sheila.
View 1 Replies
View Related
Dec 11, 1998
I have two tables that will not drop in a Database. I'm running SQL 6.5 on a Compaq Proliant.
Symptoms: Both tables show up in sysobjects, sysindexes, and syscolumns. Both allow SELECT, sp_rename, and truncate, but just hang ISQL or the Enterprise Manager when I try to drop them. I ran a DBCC CheckTable on them with no errors reported. Any ideas??
View 2 Replies
View Related
Jul 20, 2005
SQL SERVER 2000System let's you alter the system tables and add indexes. However, it won'tlet you drop the index afterward.Anybody know how to drop an index on a system table?Thanks,Kevin
View 4 Replies
View Related
Oct 23, 2007
I have 1000's of tables. Some are of the form dbo.VT_2006-10-12. I'd like to drop all tables with the "VT" in the table name. How is the best done?
View 6 Replies
View Related
Apr 12, 2006
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
View 1 Replies
View Related
Sep 10, 2007
Hello,
I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5
Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:
declare @n as nvarchar(3), @table as nvarchar(10)
set @n = 1
while @n <= 5 begin
set @table = '#temp'+@n
if object_id('tempdb..#temp'+@n) is not null drop table @table
set @n = @n + 1
end
Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@table'.
I have also tried:
declare @n as nvarchar(3), @dropstmt as nvarchar(25)
set @n = 1
while @n <= 5 begin
set @dropstmt = 'drop table #temp'+@n
if object_id('tempdb..#temp'+@n) is not null @dropstmt
set @n = @n + 1
end
This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@dropstmt'.
Does anyone know how to get this to work?
Thanks.
View 8 Replies
View Related
Feb 1, 2008
Hey there,
Trying to build a temporary table based on a parent table such that:select * into #staging from tbl_parent where 1=0
The temp table (#staging) picks up any NOT NULL constraints from the parent (tbl_parent) table and frankly, it doesn't meet my needs.I also tried to build a view of the parent (tbl_parent) table from which to base my SELECT INTO and still, the constraints followed.
I'm thinking perhaps it possble to iterate though the temp table using syscolumns sysobjects and and set the column NULL properties on the temp table AFTER its been created but in this approach, I'm working to avoid directly referencing the columns by name.
Is this possible? A better answer perhaps?
Purpose of exercie is to populate a record in memory before it hits the table.I'll be constructing the record on the fly so that at first, I won't have all the fields to de-Null the columns.Also, I don't wish to lose the fact that some of my fields are Null - once I've fully populated my temp record, I'll insert into my 'real' table and will depend on constraints to throw the appropriate error. In this I won't have to built custom error checking in the stored procedure itself but instead depend on SQL SERVERS built capacity to the throw the error. This opens a bunch of possibilities for extensibility since if at a later date a constraint rule changes, one need only change the parent table definition rather than cracking open the stored procedure.
Thank you for reading and a big thanks to you for replying :)
Also,
View 11 Replies
View Related
Aug 28, 2015
The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.
query to find list of indexes on the tables on which the views has been created.
View 4 Replies
View Related
Jan 16, 2014
I used code below to drop one temporary table. How to make code to drop all temporary tables?
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
View 9 Replies
View Related
Apr 17, 2008
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
View 1 Replies
View Related
Jul 20, 2005
Just a quicky about temporarary tables. If using QA, when you create atemporary table, it gets dropped if you close the query. Otherwise youneed to state 'DROP TABLE myTable' so that you can re-run the querywithout the table being there.Sometimes, you can have quite lengthy SQL statements (in a series)with various drop table sections throughout the query. Ideally youwould put these all at the end, but sometimes you will need to dropsome part way through (for ease of reading and max temp tables etc...)However, what I was wondering is :Is there any way to quickly drop the temporary tables for the currentconnection without specifying all of the tables individually ? Whentesting/checking, you have to work your way through and run each droptable section individually. This can be time consuming, so beingnaturally lazy, is there a quick way of doing this ? When workingthrough the SQL, it's possible to do this quite a lot.ExampleSQL Statement with several parts, each uses a series of temporarytables to create a result set. At the end of a section, these worktables are no longer needed, so drop table commands are used. Thefinal result set brings back the combined results from each sectionand then drops those at the end.TIARyan
View 2 Replies
View Related