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

Get New Database Created Then Running Script To Created Tables / Relationships

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

Drop All Indexes In A Table, How To Drop All For User Tables In Database

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

How To Drop An Identity Column From All Tables Tables In A Database

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

Why Are My Tables Not Being Created?

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

HELP:I Want To Know Who Had Created The New Tables

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

No Tables Created....

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

Analyzer And Created Tables

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

Drop Tables...

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

Dynamically Listing Created Temp Tables

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

Dropping User Created Tables From Master Db

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

Temp Tables Created In Stored Procedures

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

Drop All Tables In Database

Apr 25, 2007

what is the sql query to drop all tables in a database in sql server 2000

View 5 Replies View Related

Drop Multiple Tables

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

How To Drop All PKs On Tables In Database?

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

Drop Multiple Tables

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

File To Drop Tables

Dec 6, 2006

I need to create a file that removes (drops) all of your database objects.

View 12 Replies View Related

Linkage From 2 Drop Down Tables?

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

SQL Tries To Drop Tables That Don't Exist

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

Filter Dynamically Created Data Tables Using Sql Query

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

SQL 2012 :: Generate Triggers For Newly Created Tables

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

SQL 2012 :: 2 Replicated Objects (tables) Not Being Created On Subscriber

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

How To Find Out All The Statistics From All The Tables And Drop Them

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

Tables Exist But Wont Drop

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

Drop Index On System Tables

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

Drop All Tables In Db That Have Specific Name Convention

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

Task To Create And Drop Tables

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

Looping To Drop Temp Tables

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

Tables Created By SELECT INTO Inherit Parent Table Constraints!

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

DB Engine :: How To Find List Of Indexes On Tables On Which Views Has Been Created

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

SQL 2012 :: One Script To Drop All Temporary Tables

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

Script To Create/drop Tables, Indeces, FK ..etc

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

Drop Temporary Tables Whilst Connected ?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved