Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

How To Defrag A Table?

I have a large table with no PK but has other indexes. How to defrag the table (not the indexes)? Do I need to drop and re-create the table or something?


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Defrag Table With No Clustered Idx
Is there any easy way to defrag a table with no clustered idx other than bcp out then back in ? (SQL 2000)Actually I have a 100GB db that I deleted data from other tables, about 10GB worth, but access to the table in question (38GB) seems to have slowed down dramatically, Or does the entire disk need to be defragmented and a bcp out/in would be a waste of time on this table ?I run an index defrag every night, reindex weekly.Thanks.

View Replies !   View Related
Automating Table Defrag
I'd like to build a process that will identify all the tables in a database with a scan density less than 100% and generate 'dbcc dbreindex' statements for them. The 'dbcc showcontig' command displays the information I want but I don't know how to access this information from within a script. Any ideas out there?

View Replies !   View Related
Defrag Or Not To Defrag
I have been reading many things on the internet and I wanted to create a thread asking my question here.  We currently do all the re-indexing and show contig's etc to maintain my sql data and to ensure everything is good to go there.

My question is, what about the physical drive and data.  We house our mdf's on a raid 1_0 and our ldfs on raid 5.  I am wondering if I need to defrag these drives b/c if not am i impacting my I/O on that box.  If so should I stop the sql service so that it does not corrupt SQL data?  Any help on this topic would be great.



View Replies !   View Related
Database Defrag
I am working with a very large db (850+gig) that is in desperate need of defragging. I am familiar with DBCC Reindex and DBCC IndexDefrag but am concerned that these processes will take an extremely long time to run. Has anyone had any experience with any 3rd party tools that better/faster then DBCC? Or any ideas or suggestions how best to attack this? Among the many tables with large footprints and millions of rows, I have 3 tables with reserved space over 100g also 3 tables with over 500Mil rows (1 tbl with > 1bil rows). This is a 24x7 db.

Thanks in advance for any ideas and suggestions

View Replies !   View Related
Possible Defrag Needed?
Hi all,

I have started to look at the way our production DB has been defined and set up, with the view to improving performance.

The DB is now 11gb, and the original size was set up as 3000mb, the rest has been take in 10% additional extents.

Now, back in my DB2 DBA days, this was a bad thing to have any data spread across extents as they may not be contiguous. I am assuming that is the same with SQL Server. Can someone confirm/deny this?

If this is the case, how can I get the DB back into one primary partition?

Thanks in advance.


View Replies !   View Related

Our sql server machine is badly in need of a defrag however I'm a bit weary of doing this as I'm really not sure of the implications or indeed how to do it.

I'm really not a server type of person so any assistance would be gratefully received. If anyone can explain it to me like I'm a 5 year old then that would probably be best!


View Replies !   View Related
Defrag The Drive

Can I de-frag the Drive in which the Data files & Log Files of the SQl Server Exists ???

Please Advice.


View Replies !   View Related
Index Defrag
I am new to sql server and we have sql server 2000


Please somebody give me for sql server 2005 scripts for above.

View Replies !   View Related
Defrag / Reindex
I#ve been doing disaster recovery on a web box that died today.

So I thought I'd do some "downtime" maintenance on the DB server

I ran a BDREINDEX on all tables, all indexes. (I know this is the 2000 way, but I assume its as good as the proper 2005 way??).

5 minutes on a 10GB database. Not bad!

I checked the DEFRAG and UPDATE STATS processes that run overnight.

They are basically defragging only tables with SHOWCONTIG indicating fragmentation. And then doing an UPDATE STATISTICS WITH FULLSCAN on all tables

That is taking an average of 30 minutes ...

Is DBREINDEX the equivalent of an UPDATE STATISTICS WITH FULLSCAN, or is it in some way a smaller-sample version?

I'm wondering why I don't just lock the DB and do a REINDEX of everything in 5 minutes ...


View Replies !   View Related
Automating Defrag.exe
Isn't there any way to automate Windows defrag? Isn't there any undocumented parameters for DEFRAG.EXE?

Canada DBA

View Replies !   View Related
DEFRAG Disk Drive
SQL 2000We took SQL Server offline last night and defragged the SAN. Should wereindex or will be be okay ?Thanks,Craig

View Replies !   View Related
Does Index Defrag Get Logged?
I've noticed a huge transaction log size after having run an
index defragmentation. Does a defrag get written to the transaction
log really? (Assuming the full recovery model.)

View Replies !   View Related
How To Defrag SQL Server 2000
Hi everyone, I am fairly new with SQL Server and need a little bit of help in regards to boosting my SQL servers performance. I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before. Can someone please help guide me through the necessary steps to execute this task. Thanks in advance.

View Replies !   View Related
Benefits Of Win2000 Defrag
Is there a big performance advantage to using the standard defrag utility that comes with Win2000 for servers with SQL 2000 on them. I have seen the ads for buying a specific defrag product. Is there a big advantage to the commercially created products VS what's internal to Win2000?

View Replies !   View Related
Defrag Indexed Views?
How do we defrag indexed views? Can any one give me a query to loop thru all the indexed views in the database and find out the fragmentation levels and also defrag them?
Thanks in advance!

View Replies !   View Related
How To Defrag The System Tables.
I just ran some dbcc showcontig against some system tables in my production databases in sql 7.0 and found out these tables are heavily deframented. Is there a way to defrag these tables other that using dbcc dbreindex which will not work on system tables.


Jim Zhong

View Replies !   View Related
YADS -- Yet Another Defrag Script
This script was based on the original posted here:

I use the REORGANIZE option of ALTER INDEX. It's default is ONLINE, no matter what the engine edition and makes for simpler code.

I also make heavy use of my own logging tables, which are included at the bottom of the sp.

Please, feel free to comment. I like getting feedback about my scripts and it seems rarely that happens...

USE Admin

IF EXISTS (SELECT [name] FROM Admin.sys.objects WHERE [name] = 'usp_DB_DeFrag' AND TYPE = 'P')

CREATE PROCEDURE dbo.usp_DB_DeFrag (@DBName sysname, @Percentage float = 10)
-- EXEC Admin.dbo.usp_DB_Defrag @DBName = 'FooDB', @Percentage = 10

**Name: Admin.dbo.usp_DB_DeFrag.sql
**Description: Defragment indexes using REORGANIZE for online operation.
**Record historical fragmentation information to a permanant table
**for trend/history analysis.
**Depends on: SQL2005 >= SP2 due to object_name() usage. See BOL for details.
**Admin.dbo.Process_Log - Table
**Admin.dbo.FragTracking - Table
** TODO: Open to suggestions...
**Author: G. Rayburn <>
**Date: 10/02/2007
**Modification History
**Initial Creation: 10/02/2007 G. Rayburn <>

DECLARE @DynFragList varchar(1024)
, @DynDBAlter varchar(256)
, @DynDefragDriver varchar(max)
, @DynUpdateStats varchar(1024)
, @OrigRecoveryModel nvarchar(128)
, @Process_Name varchar(150)
, @Message varchar(256)
, @Error int

-- Cursor objects:
, @SchemaName sysname
, @ObjectName sysname
, @IndexName sysname
, @IndexType nvarchar(60)
, @AvgFrag int
, @PageCount int
, @RecordCount int
--, @GhostRecordCnt bigint
--, @Partition int

--SET @DBName = 'FooDB'
--SET @Percentage = 10;

SET @Process_Name = 'usp_DB_Defrag run on [' + @DBName + ']';

-- Ensure that @DBName is a valid db for db_id() usage.
IF (db_id(@DBName)) IS NULL
SET @Message = '[' + @DBName + '] is not a valid database on ' + @@SERVERNAME + ', please check your spelling and try again.'

INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', 9999, @Message)


-- Record startup message:
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, '[START] - usp_DB_Defrag @DBName = [' + @DBName + '], @Percent = ' + CONVERT(varchar(3),@Percentage) + '.')

-- Check & alter recovery model if neccessary:
SET @OrigRecoveryModel = (SELECT CONVERT(varchar(55),DATABASEPROPERTYEX(@DBName, 'Recovery')))

IF @OrigRecoveryModel = 'FULL'
SET @DynDBAlter = 'ALTER DATABASE [' + @DBName + ']

EXEC (@DynDBAlter);

IF @Error = 0
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), 'Successfully set database [' + @DBName + '] to BULK_LOGGED recovery model.')
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), 'Failed to set database [' + @DBName + '] to BULK_LOGGED recovery model.')
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, 'Database [' + @DBName + '] is in ' + @OrigRecoveryModel + ' recovery model so no need to change it.')

-- Temp table of initial DBCC results:
ObjectName varchar(100)
, [Object_ID] int
, Index_ID int
, Partition_Number int
, IndexType varchar(60)
, alloc_unit_type_desc nvarchar(60)
, avg_fragmentation_in_percent float
, avg_fragment_size_in_pages float
, avg_page_space_used_in_percent float
, fragment_count bigint
, page_count bigint
, record_count bigint
, forwarded_record_count bigint
, ghost_record_count bigint


LEFT(object_name([object_id], db_id(@DBName)),100)
, [object_id]
, index_id
, partition_number
, index_type_desc
, alloc_unit_type_desc
, avg_fragmentation_in_percent
, avg_fragment_size_in_pages
, avg_page_space_used_in_percent
, fragment_count
, page_count
, record_count
, forwarded_record_count
, ghost_record_count

FROM sys.dm_db_index_physical_stats (db_id(@DBName), NULL, NULL, NULL, 'DETAILED')

WHERE avg_fragmentation_in_percent >= @Percentage
AND index_id >= 1
AND page_count >= 1000

ORDER BY -- Ensure Clustered indexes are rebuilt first.
, index_id ASC;

CREATE INDEX IDX_ObjNameIndexID ON #_FragList (ObjectName, Index_id);

-- Historical tracking:
INSERT INTO Admin.dbo.FragTracking

, ObjectName
, [Object_ID]
, Index_ID
, Partition_Number
, IndexType
, alloc_unit_type_desc
, avg_fragmentation_in_percent
, avg_fragment_size_in_pages
, avg_page_space_used_in_percent
, fragment_count
, page_count
, record_count
, forwarded_record_count
, ghost_record_count
, getdate()

FROM #_FragList

ORDER BY [Object_ID]
, Index_ID ASC;

-- Create & populate Temp table to drive defrag operations from.
CREATE TABLE #_DefragDriver
IdentID int IDENTITY(1,1)
, SchemaName sysname
, ObjectName sysname
, IndexName sysname
, IndexType varchar(60)
, avg_fragmentation_in_percent float
, page_count int
, record_count int
, ghost_record_count bigint
, partition_number int

SET @DynDefragDriver = '
USE [' + @DBName + ']

INSERT INTO #_DefragDriver

SELECT schema_name(so.schema_id)
, fl.[ObjectName]
, si.[name]
, fl.IndexType
, fl.avg_fragmentation_in_percent
, fl.page_count
, fl.record_count
, fl.ghost_record_count
, fl.partition_number

FROM #_FragList fl
, [' + @DBName + '].sys.indexes si
, [' + @DBName + '].sys.objects so

WHERE object_id(fl.ObjectName) = si.object_id
AND fl.index_id = si.index_id
AND object_id(fl.objectname) = so.object_id
AND si.is_disabled = 0
AND si.allow_page_locks = 1

GROUP BY so.schema_id
, fl.[ObjectName]
, fl.[object_id]
, fl.index_id
, si.[name]
, fl.IndexType
, fl.avg_fragmentation_in_percent
, fl.page_count
, fl.record_count
, fl.ghost_record_count
, fl.partition_number

ORDER BY fl.[object_id]
, fl.index_id ASC; '

EXEC (@DynDefragDriver);

-- Do the defrag.

SELECT SchemaName
, ObjectName
, IndexName
, IndexType
, avg_fragmentation_in_percent
, page_count
, record_count
--, ghost_record_count
--, partition_number

FROM #_DefragDriver


OPEN curDBFrag

FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag, @PageCount, @RecordCount --, @GhostRecordCnt, @Partition
WHILE (@@fetch_status <> -1)
IF (@@fetch_status <> -2)

-- ALTER INDEX operations:
SET @Message = 'Table: [' + @ObjectName + '] with record count: ' + CONVERT(varchar(15),@RecordCount) + ' and page count: ' + CONVERT(varchar(15),@PageCount) + '. Index: [' + @IndexName + '] of type: ' + @IndexType + ' is ' + CONVERT(varchar(5),@AvgFrag) + '% fragmented.';

SET @DynFragList = 'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @ObjectName + '] REORGANIZE;'

EXEC (@DynFragList);

IF @Error = 0
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), @Message)
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), @Message)

-- UPDATE STATISTICS operations:
SET @Message = 'UPDATE STATISTICS [' + @SchemaName + '].[' + @ObjectName + '] [' + @Indexname + '];'

SET @DynUpdateStats = '
USE [' + @DBName + ']

UPDATE STATISTICS [' + @SchemaName + '].[' + @ObjectName + '] [' + @Indexname + ']; '

EXEC (@DynUpdateStats);

IF @Error = 0
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), @Message)
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), @Message)

-- Friendly WAITFOR operation:
WAITFOR DELAY '00:00:05.000'

FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag, @PageCount, @RecordCount --, @GhostRecordCnt, @Partition


-- Reset FULL recovery model.
IF @OrigRecoveryModel = 'FULL'
SET @DynDBAlter = 'ALTER DATABASE [' + @DBName + ']

EXEC (@DynDBAlter);

IF @Error = 0
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), 'Successfully reset database [' + @DBName + '] back to FULL recovery model.')
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), 'Failed to reset database [' + @DBName + '] back to FULL recovery model.')

-- Record complete message:
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, '[COMPLETE] - usp_DB_Defrag @DBName = [' + @DBName + '], @Percent = ' + CONVERT(varchar(3),@Percentage) + '.');

-- Cleanup:
DROP TABLE #_FragList;
DROP TABLE #_DefragDriver;

-- Dependancies:
----USE [Admin]
---- DROP TABLE Admin.dbo.FragTracking
----CREATE TABLE FragTracking
----, DBName sysname
----, ObjectName sysname
----, Object_ID int
----, Index_ID int
----, Partition_Number int
----, IndexType varchar(60)
----, alloc_unit_type_desc nvarchar(60)
----, avg_fragmentation_in_percent float
----, avg_fragment_size_in_pages float
----, avg_page_space_used_in_percent float
----, fragment_count bigint
----, page_count bigint
----, record_count bigint
----, forwarded_record_count bigint
----, ghost_record_count bigint
----, SnapDate datetime
----USE [Admin]
---- DROP TABLE Admin.dbo.Process_Log
----CREATE TABLE [dbo].[Process_Log](
----[MessageID] [int] IDENTITY(1,1) NOT NULL,
----[Date] [datetime] NOT NULL,
----[Process_Name] [varchar](150) NULL,
----[Severity] [varchar](15) NULL,
----[ErrorCode] [int] NULL,
----[Message] [varchar](255) NULL,
----[Date] ASC,
----[MessageID] ASC
----) ON [Admin_Data]

View Replies !   View Related
Index Rebuild Does Not Defrag

After issuing an index rebuild on a primary key index (and updating statistics), the index still shows a scan density of 12.5%!

Any ideas on why the rebuild doesn't seem to do anything on the fragmentation levels?

I'm using sql 2005


View Replies !   View Related
MS SQL Question Regarding Page/index Defrag.
I know the shortcut to 'defragging' the database to eliminate and
reclaim 'whitespace' in data and index pages is to setup a maintenance
plan. This doesn't work for me for two reasons:
1. I like to know how things work.
2. I can not create maintenance plans with my hosted provider.
I use the following T-SQL to rebuild all indexes on all tables:

-- exec sp_MSforeachtable "DBCC DBREINDEX ('?', ' ', 90)

But, does that not only address indexes? What about getting whitepace
back from data in tables themselves?
Am I over-thinking this?
When you're paying for SQL by the MB and generate a lot of data (with a
fair amount of churn) you want to keep it optimized and efficient,

View Replies !   View Related
Dbreindex Vs Index Defrag Question
Does anyone know if dbreindex and index defrag ideally perform the same function? I have been told that index defrag does not hold locks on a table when executed and dbreindex does. Other than this is there any difference between the two functions? My understanding was that dbreindex reindexes the data stored in a table for faster reads and index defrag removes purged data. Am I correct? I am currently running both functions on my SQL server and was advised that I really only need to run the index defrag job. Is this advise correct?

View Replies !   View Related
Turn Sql Serveice Off Before Performing Disk Defrag?
We have a Windows 2003 R2 server running Sql 2005. Should we stop the sql server engine prior to running the "Defrag" utiltiy that comes with the OS? (Maybe we should also disable the NIC card on that machine too....?)

View Replies !   View Related
What factors would make you decide to use DBCC DBREINDES instead of DBCC DEFRAG when you notice index defragmantation?



View Replies !   View Related
I Have Created A Table Table With Name As Varchar And Id As Int. Now I Have Started Inserting The Rows Like, Insert Into Table Values ('arun',20).
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem? 

View Replies !   View Related
Moving From One Table To Other Table Automatically For Every 3 Months By Checking The Paticular Value Of The Table Field
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into  other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
Please help me.

View Replies !   View Related
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.

View Replies !   View Related
Dbo.Table Of A Database In The .SQLEXPRESS Object Explorer: How To Copy The Dbo.Table To The Another Blank Dbo.Table?
Hi all,
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing:
      dbo.Order Details
But, I have these dbo.Tables in a different Database "xyzDatabase".  How can I  copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database? 
I right clicked on the dbo.Categories and I saw the following thing:
                   New Table...
                   Open Table
                   Script Table as |>   CREATYE To |>
                                                DROP To |>
                                                SELECT To |>
                                                INSERT To |>   New Query Editor Window
                                                UPDATE To |>
                                                DELETE to |>
From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired.    Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance,
Scott Chang
P. S.  I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.       

View Replies !   View Related
What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure
 which is more efficient...which takes less is the memory allocation done for both the types.

View Replies !   View Related
How To Search Multiple Table Which Table Name Is Store In Another Table And Join The Result Together?
I have one control table to store all related table name
 Table ID                   TableName
     1                           TableA
     2                           TableB
In Table A:
RecordID                Value
     1                         1
     2                         2
     3                         3
In Table B:
RecordID             Value
    1                         1
    2                         2
    3                         3
 How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!

View Replies !   View Related
Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View Replies !   View Related
ODBC-linked Table Update On Table ' Table Name ' Failed.
Hello All:Have a problem that I have never seen before and have not found anythingontechnet on how to resolve it. I have a SQL Server 2000 server that Ihavecreated a new database. I then used DTS to import data from a MS Access97database. Before making the import, I made changes to the MS Accessdatabaseand changed the autonumbers to integers. I also removed allrelationships.My front end is still in MS Access 97 and I am linking ODBC tables fromtheSQL Server. Here's the issue, I can not make any changes to any data inanytable. I get the following message:ODBC-linked table update on table 'Registry' failed.According the help for this, the database is in read-only mode. Icheckedthe MS Access front end and all my settings on the SQL Server. Nothingis inread-only. I did not use the Linked Table Manager to relink the tables,which can cause issues.After this message I get the following message:The text, ntext, and image data types cannot be compared or sorted,exceptwhen using IS NULL or LIKE operator.Which is error 306 and has a severity of 16.On the same SQL Server using Northwind I do not have any issues. Anyideaswould greatly be appreciated. Thanks.Katuil*** Sent via Developersdex ***Don't just participate in USENET...get rewarded for it!

View Replies !   View Related
Newbie-DELETE A Record In A Table A That Is Related To Table B, And Table B Related To Table A
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
      [EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
      [BranchFID] [int] NOT NULL,
      [FirstName] [varchar](50) NOT NULL,
      [MiddleName] [varchar](50)  NOT NULL,
      [LastName] [varchar](50)  NOT NULL,
ALTER TABLE [MyCompany].[Employee] 
REFERENCES [myCompany].[Branch] ([BranchGID])
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
      [BranchGID] [int] IDENTITY(1,1) NOT NULL,
      [BranchName] [varchar](50) NOT NULL,
      [City] [varchar](50)  NOT NULL,
      [ManagerFID] [int] NOT NULL,
ALTER TABLE [MyCompany].[Branch] 
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
ALTER TABLE [MyCompany].[Branch]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €śEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €śBranch€? entry, basically each entry in €śbranch€? and €śEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €śON DELETE CASCADE€? or something?

View Replies !   View Related
Is A Temp Table Or A Table Variable Used In UDF's Returning A Table?
In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?

View Replies !   View Related
Difference In Creating Temporary Table By #table And ##table
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
create table ##temp
name varchar(25),
roll int
insert into ##temp values('banti',1)
select * from ##temp
create table #temp
name varchar(25),
roll int
insert into #temp values('banti',1)
select * from #temp

both works fine , then what is the difference
waiting for ur reply

View Replies !   View Related
Delete Table And Immediately Crate Table, Error Occur &&"Table Already Exist&&"

Cmd.CommandText = "Drop Table Raj"



Cmd.CommandText = "Select * Into Raj From XXX"




This generates error that Table already exist.
If Wait 1 sec then execute statement then it works fine.

Thanks in Advance
Piyush Verma

View Replies !   View Related
How To Create A System Type Table/ Change User Table To System Table.
Is there any Posibility to change a User Table to System Table.

How to create one system table.

I am in Big mess that One of the Table I am using is in System Type.

I cant Index the same. Is there any Mistake we can change a user table to system table.....

View Replies !   View Related
The OLE DB Provider &"MSDAORA&" For Linked Server &"....&" Does Not Contain The Table &"COUNTRY&". The Table Either Does Not Exist Or The Current User Does Not Have Permissions On That Table.
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.

View Replies !   View Related
How Do I Enter NULL In A Table Cell In The Enterprise Manager UI For Table Data Entry?
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column.  Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit".  I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it.  I want to get back to <NULL>.Anybody know?Thank you.Tom

View Replies !   View Related
Table Names In Stored Procedures As String Variables And Temporary Table Question
How do I use table names stored in variables in stored procedures?

Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000

I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.

Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename

It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.

Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
where ColumnB = 003860
Group By ColumnA, ColumnB

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB

The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.


View Replies !   View Related
Importing Access Table Into SQL Server 2005 Express Table And Adding One Field
Hi all,

 Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table.  What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field?  Thanks a bunch for any asistance.


View Replies !   View Related
INSERT New Record Works OK In Local Table, BUT Not If The Target SS DB/table Is In A Different Physical Server

Hi...  I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update  local SS DB/table - new records writes OK in the table.  BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs.  AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple -  Add new records in a SS table using SSIS .  I only care for new rows and  not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER -  using SELECT stmt
2.  I have LoopUp component that will look for NEW records -  Directs all rows that don't find match and redirect rows (error output).
3.  Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4.  I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing.  And I do not see error in my package either.  It is not failing.
Thanks in advance!

View Replies !   View Related
SSIS Loading DWH Staging Area When Table Names Is Selected From Table List
Maybe anyone have done that before?
I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables.
i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.
I made such SSIS package. set ole db source data access mode to table or view name variable.
set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables)
but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.
how to solve that problem?

View Replies !   View Related
Snapshot Agent Fails On Table With XML Datatype : Script Failed For Table 'dbo.TableName'
SQL Server 2005 SP2 Error: Script failed for Table 'dbo.TableName'

I keep getting this error when trying to generate a snapshot for transact replication.
Here is the publishing table schema:

CREATE TABLE [dbo].[viMediaPlaylist](
    [MemberID] [bigint] NOT NULL,
    [xmlPlaylist] [xml](CONTENT [dbo].[viMediaPlaylistCollectionSchema]) NOT NULL,
    [MemberID] ASC

I tried the following:
#1)Create table schema at the subscriber manually, and change package to Keep object as is. Got the same error during snapshot.

#2Filtered replication to NOT include XML column. This worked, and no error was generated, replication was up and running.

So my question is, what is the problem with XML column being replicated?
Any ideas how i can make this work? At this point i'm not sure what the problem is with the xml column, but it seems like ti tries to run schema script, but i'm not asking it to create the schema i already did it myself. Thanks!

Here is a log dump from the distributor:

2007-09-11 16:40:14.56 SQL Command dump
2007-09-11 16:40:14.56 ================
2007-09-11 16:40:14.56 Server: SQL02
2007-09-11 16:40:14.56 Database: Video
2007-09-11 16:40:14.56 Command Text: sys.sp_releaseapplock
2007-09-11 16:40:14.56 Parameters:
2007-09-11 16:40:14.56     @Resource = SQL02-Video_viMediaPlaylis-71
2007-09-11 16:40:14.56     @LockOwner = Session
2007-09-11 16:40:14.56     @DbPrincipal = db_owner
2007-09-11 16:40:15.17 [0%] The replication agent had encountered an exception.
2007-09-11 16:40:15.17 Source: Unknown
2007-09-11 16:40:15.17 Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException
2007-09-11 16:40:15.17 Exception Message: Script failed for Table 'dbo.viMediaPlaylist'.
2007-09-11 16:40:15.17 Message Code: Not Applicable
2007-09-11 16:40:15.17 
2007-09-11 16:40:15.17 Call Stack:
2007-09-11 16:40:15.17 Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Table 'dbo.viMediaPlaylist'.  ---> Microsoft.SqlServer.Management.Smo.UnsupportedVersionException: Either the object or one of its properties is not supported on the target server version.
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.GetTypeDefinitionScript(ScriptingOptions so, SqlSmoObject oObj, String sTypeNameProperty, Boolean bSquareBraketsForNative)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.AppendScriptTypeDefinition(StringBuilder sb, ScriptingOptions so, SqlSmoObject oObj, SqlDataType sqlDataType)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingOptions so)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingOptions so)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingOptions so, StringBuilder sb, ColumnCollection columns, IndexCollection indexes)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingOptions so, StringBuilder sb)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17    --- End of inner exception stack trace ---
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
2007-09-11 16:40:15.17    at Microsoft.SqlServer.Replication.AgentCore.Run()

View Replies !   View Related

Copyright © 2005-08, All rights reserved