how to rename a temp table

EXEC sp_rename '#customers', '#custs'
result with error :Invalid object name '#customers'.
how to rename a temp table?Sam
sp_rename
Changes the name of a user-created object (for example, table, column, or
user-defined data type) in the current database.
"Sam" <focus10@.zahav.net.il> wrote in message
news:ecE3BxurFHA.2008@.TK2MSFTNGP10.phx.gbl...
> EXEC sp_rename '#customers', '#custs'
> result with error :Invalid object name '#customers'.
> how to rename a temp table?
>
>|||Sam wrote:
> EXEC sp_rename '#customers', '#custs'
> result with error :Invalid object name '#customers'.
> how to rename a temp table?
I'm not sure you can rename a temporary table. Why do you need to do
this?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||so how can i rename the temp table?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OziOz4urFHA.240@.tk2msftngp13.phx.gbl...
> Sam
> sp_rename
> Changes the name of a user-created object (for example, table, column, or
> user-defined data type) in the current database.
>
>
>
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:ecE3BxurFHA.2008@.TK2MSFTNGP10.phx.gbl...
>|||I don't think there's a documented way to do it.
Why would you ever want to rename a temp table? This seems especially
pointless with a local temp table, which after all is intended
precisely to give you a locally-scoped name for the table. I'm sure if
you explain your requirement we can suggest a better solution to avoid
doing this.
David Portas
SQL Server MVP
--|||Uri, I believe sp_rename disallows renaming temp objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OziOz4urFHA.240@.tk2msftngp13.phx.gbl...
> Sam
> sp_rename
> Changes the name of a user-created object (for example, table, column, or
> user-defined data type) in the current database.
>
>
>
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:ecE3BxurFHA.2008@.TK2MSFTNGP10.phx.gbl...
>|||select * into #NewName from #OldName
drop table #OldName
--Brian
(Please reply to the newsgroups only.)
"Sam" <focus10@.zahav.net.il> wrote in message
news:uBSEB%23urFHA.1168@.TK2MSFTNGP11.phx.gbl...
> so how can i rename the temp table?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OziOz4urFHA.240@.tk2msftngp13.phx.gbl...
>|||sp_rename renames the table in the current database. a temp table is created
in tempdb database and tempdb doesnt have sp_rename stored procedure.
the table gets deleted immediately after the session is closed, so why do u
want to
rename it
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sam" wrote:

> EXEC sp_rename '#customers', '#custs'
> result with error :Invalid object name '#customers'.
> how to rename a temp table?
>
>
>|||Hi, Dan
That was exactly may point.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OpFv7%23urFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Uri, I believe sp_rename disallows renaming temp objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OziOz4urFHA.240@.tk2msftngp13.phx.gbl...
>|||> how to rename a temp table?
What would be the point?

How to rename a table ?

Hi all.

I'm porting an application from eVB-ADOCE to VB.Net-SQL CE

In the old application there are some SQL statements like this:

"ALTER TABLE OldTable TO NewTable"

Is there an equivalent instruction or a simple way to rename a table?

I was not able to find anything simple to do the same.

I really need NOT to do something like

SELECT * INTO NewTable FROM OldTable

because the resulting table could go outside memory and/or spend too much time in elaboration.

Please let me know what I can do.

Many thanks !

As far as I know, there is no such option from SQL. But you can rename a table (and a table column) using OLE DB:

QA: How do I rename a SQL CE table?

|||This is very interesting, and I read your article, but I'm so ignorant in C++ that I've not been able to translate your code in VB. |||

Don't worry - I'm working on an article that will allow you to do this from .NET CF on a device. I will post this article either today or tomorrow.

|||

Jo?o Paulo Figueira wrote:

Don't worry - I'm working on an article that will allow you to do this from .NET CF on a device. I will post this article either today or tomorrow.

That will be wonderful !

|||

As promised, here is the article:

Renaming a SQL CE Table From a .NET CF Application

|||

Joao Paulo,

I don't know how to say "thank you" for your kindness. I'm going to use your work just now.

If you come to Milano (Italy), please let me know ! I shall organize a good dinner for you

Thank you very much again !

How to rename a column or table on the desktop?

I am using the compact edition on a desktop using VS2005 as well as SQL Server Management Studio. None of those tools allow me to rename a column or rename a table. Can someone point me a tool the runs on the desktop (as opposed to running on a CE device) that allows me to do the renaming?

Thanks

A 3rd party tool from this company may be able to help you, please contact the company (I am not affiliated): http://www.primeworks-mobile.com/

|||

I have published an article on this subject that you can adapt to the desktop:

Renaming a SQL CE Table From a .NET CF Application

Although some of my products do that (I am affiliated to that company ), you don't have to purchase one just to rename a table or column name. The code I published on that article can be easily adapted to the desktop. If you have problems with it, just let me know.

|||Great tool! Instead of copying, MS should just buy your tool.

How to remove time from date?

Hello,
I have a table (T1) that has a field that holds a date with a time. In
another table (T2) I have records related to T1 on an ID field (one T1 to
many T2). T2 has a date field also, but this field does not have time (ie
the time part is midnight). I want to join the two tables on the ID and
Date but the time portions of the dates are not equal so they do not match.
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND T1.Date = T2.Date
Does anyone know how I can set the time portion of T1.Date to midnight in
the query (not in the table) so the join will work.
I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
want the time part dropped or midnight.
Thanks
EdmundHi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry|||I don't know why I did not think of this. It was staring right at me.
Thanks Barry. I've gone for CONVERT(CHAR(10),T1.Date, 120)) as it will
implicitly convert to DATETIME with date as midnight.
Edmund.
"Barry" <barry.oconnor@.manx.net> wrote in message
news:1149517628.941905.154220@.h76g2000cwa.googlegroups.com...
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry
>|||try this:
CONVERT(VARCHAR(10), T1.date, 126)
Barry wrote:
> Hi,
> Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
> (Varchar(13), T2.Date, 112)
>
> HTH
> Barry|||Edmund,
You might want to chk this article :: http://www.aspfaq.com/show.asp?id=2460
Extract from that article:
--
For example, to get today's date in YYYYMMDD format, you currently need to
call the following:
SELECT CONVERT(CHAR(8), GETDATE(), 112)
What does the 112 mean? Nothing. It's just an arbitrary number representing
this specific format.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Edmund" wrote:

> Hello,
> I have a table (T1) that has a field that holds a date with a time. In
> another table (T2) I have records related to T1 on an ID field (one T1 to
> many T2). T2 has a date field also, but this field does not have time (ie
> the time part is midnight). I want to join the two tables on the ID and
> Date but the time portions of the dates are not equal so they do not match
.
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND T1.Date = T2.Date
> Does anyone know how I can set the time portion of T1.Date to midnight in
> the query (not in the table) so the join will work.
> I've tried CONVERT(DATETIME, T1.Date, 106) but it keeps the time part. I
> want the time part dropped or midnight.
> Thanks
> Edmund
>
>|||Edmund,
To truly remove time from datetime and not just "hide" it:
SELECT CAST(DATEDIFF(DAY,0,getDate()) AS DATETIME)
Returns:
2006-06-06 00:00:00.000
Mark|||A slightly better way to do it than using the CONVERT() function calls
would be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d,
DATEDIFF(d, 0, T2.Date), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web
article about datetime data including a short section on stripping time
info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
*mike hodgson*
http://sqlnerd.blogspot.com
Barry wrote:

>Hi,
>Try this...
> SELECT T1.ID, T2.Date, T2.Value
> FROM T1
> JOIN T2
> ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
>(Varchar(13), T2.Date, 112)
>
>HTH
>Barry
>
>|||Mike, this is very good.
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:u4N4C8PiGHA.1612@.
TK2MSFTNGP04.phx.gbl...
A slightly better way to do it than using the CONVERT() function calls would
be:
SELECT T1.ID, T2.Date, T2.Value
FROM T1
INNER JOIN T2
ON T1.ID = T2.ID
AND DATEADD(d, DATEDIFF(d, 0, T1.Date), 0) = DATEADD(d, DATEDIFF(d, 0, T2.Da
te), 0)
If you want more info on it, Tibor Karaszi wrote a pretty good web article a
bout datetime data including a short section on stripping time info:
http://www.karaszi.com/SQLServer/in...idOfTimePortion
mike hodgson
http://sqlnerd.blogspot.com
Barry wrote:
Hi,
Try this...
SELECT T1.ID, T2.Date, T2.Value
FROM T1
JOIN T2
ON T1.ID = T2.ID AND Convert(varchar(13), T1.Date, 112) = Convert
(Varchar(13), T2.Date, 112)
HTH
Barry

How to remove rows where only part of the row is duplicated

Hi,

I've got a db table containing 5 columns(excluding id) consisting of
1.) First Half of a UK postcode
2.) Town name to which postcode belongs
3.) Latitude of Postcode
4.) Longitude of Postcode
5.) Second Part of the Postcode

I want to select columns 1,2,3 and 4, but once only. There are often
several entries where 1 and 2 are the same but 3 and 4 are different
i.e.
WA1Bewsey and Whitecross53.386492-2.596847
WA1Bewsey and Whitecross53.388203-2.590961
WA1Bewsey and Whitecross53.388875-2.598504
WA1Fairfield and Howley53.388455-2.581701
WA1Fairfield and Howley53.396117-2.571789

My current query is
SELECT DISTINCT Postcode, Town, latitude, longitude
FROM Postcode
WHERE Postcode.Postcode = 'wa1'
ORDER BY Postcode, Town

However as latitude and longitude differ on each line DISTINCT does
not do what I'm looking for.
Can anybody suggest a way changing the query to just give the first
instance of each Postcode/Town combo?
I.E.
WA1Bewsey and Whitecross53.386492-2.596847
WA1Fairfield and Howley53.388455-2.581701

Many thanks!
DrewThere isn't really any 'first' instance unless you
define your own ordering. However, assuming you have a unique ID
column
this should work

SELECT a.Postcode, a.Town, a.latitude, a.longitude
FROM Postcode a
WHERE NOT EXISTS (SELECT * FROM Postcode b
WHERE b.Postcode=a.Postcode
AND b.Town=a.Town
AND b.ID>a.ID)
ORDER BY a.Postcode, a.Town|||(andylole@.gmail.com) writes:

Quote:

Originally Posted by

I've got a db table containing 5 columns(excluding id) consisting of
1.) First Half of a UK postcode
2.) Town name to which postcode belongs
3.) Latitude of Postcode
4.) Longitude of Postcode
5.) Second Part of the Postcode
>
I want to select columns 1,2,3 and 4, but once only. There are often
several entries where 1 and 2 are the same but 3 and 4 are different
i.e.
WA1 Bewsey and Whitecross 53.386492 -2.596847
WA1 Bewsey and Whitecross 53.388203 -2.590961
WA1 Bewsey and Whitecross 53.388875 -2.598504
WA1 Fairfield and Howley 53.388455 -2.581701
WA1 Fairfield and Howley 53.396117 -2.571789
>
My current query is
SELECT DISTINCT Postcode, Town, latitude, longitude
FROM Postcode
WHERE Postcode.Postcode = 'wa1'
ORDER BY Postcode, Town
>
However as latitude and longitude differ on each line DISTINCT does
not do what I'm looking for.
Can anybody suggest a way changing the query to just give the first
instance of each Postcode/Town combo?

A simple way out would be:

SELECT Postcode, Town, AVG(latitude), AVG(longitude)
FROM Postcode
GROUP BY Postcode, Town

Of course, this yield data that is not in the table at all, but it's a
reasonable assumption that the different lat/long values are in the same
proximity. And if they are not, you have a much bigger problem anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

Your solution is perfect!
Many thanks to all for helping.
Cheers,
Andy.

How to remove replication?

i used to the sql built to remove replication,but after i remove all replication,operating table of the database still report having replication conflit.why and how to remove all replication clearly?
thanks.
If the database is no longer involved in replication as a publisher or a
subscriber then you can use sp_removedbreplication.
HTH,
Paul Ibison
|||Hello all,
I think we have the same or similar problem.
We have removed the replication by using the Enterprise Manager, but we are still not able to alter tables.
We also tried to use sp_removedbreplication, but received the same error message after trying to alter the table.
Is there any way to remove old replication information from the DB?
Regards,
Marc
/*
Tuesday, May 11, 2004 9:55:10 AM
User:
Server: XXXXXX
Database: meas
Application: MS SQLEM - Data Tools
*/
'tTestNode' table
- Unable to rename column from 'Name' to 'qName'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the table because it is published for replication.
|||There is a stored procedure to do this called sp_MSunmarkreplinfo which
takes a tablename as a parameter. Alternatively, setting replinfo to 0 in
sysobjects for the particular table should do it.
Regards,
Paul Ibison
|||you can safely issue a drop table statement to remove these tables. I normally do this
select 'drop table '+name from sysobjects where type='u' and name like '_onflict%' and status<0
the in the results portion I will have the drop table statements to drop these objects. I paste this into qa and run them.
Do this only if you have no active merge, queued or immediate updating publications in the database.

how to remove reference of a table from all stored procs

Hello All
I am working on SQL SERVER 2000 DATABASE.
Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference from all stored procs(about 100 Stored Proc's)
Pls help me asap.
Regards,
EktaI'm afraid not. You need to work through your procedures and see which referenced the table and
adjust the proc code for each to your liking. You can get some help in finding which procedures
referencing your tables, for instance:
SELECT object_name(id) FROM syscomments WHERE text like '%procname%'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ekta" <enahar@.hotmail.com> wrote in message news:e02qRhY2HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hello All
> I am working on SQL SERVER 2000 DATABASE.
> Is there any quick way to Drop the table say TABLE1 from the Database and remove its reference
> from all stored procs(about 100 Stored Proc's)
> Pls help me asap.
> Regards,
> Ekta
>

how to remove reference of a table from all stored procs

Hello All
I am working on SQL SERVER 2000 DATABASE.
Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference from all stored procs(about 100 Stored Proc's)
Pls help me asap.
Regards,
EktaI'm afraid not. You need to work through your procedures and see which refer
enced the table and
adjust the proc code for each to your liking. You can get some help in findi
ng which procedures
referencing your tables, for instance:
SELECT object_name(id) FROM syscomments WHERE text like '%procname%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ekta" <enahar@.hotmail.com> wrote in message news:e02qRhY2HHA.5980@.TK2MSFTNGP04.phx.gbl...[v
bcol=seagreen]
> Hello All
> I am working on SQL SERVER 2000 DATABASE.
> Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference
> from all stored procs(about 100 Stored Proc's)
> Pls help me asap.
> Regards,
> Ekta
>[/vbcol]

How to remove page breaks when you have multiple groups

Hi,
When you have more than one sub groups in a report, table keeptogether is not
functioning properly. It is always breaking into multiple pages.
I would appreciate if anybody give a fix for this problelm
Thanks,
vamsi.This problem has been a major irritant for me. Suggestions for a workaround
gratefully appreciated!

how to remove index

hi I am new to sql server and I want to remove an index in a table in the northwinds practice database. Can I do it in enterprise manager or is there a script to run in query analyzer? thanks!
You can do it from EM. But... it's always best to learn the real syntax if
you can. Read in Books Online about the DROP INDEX command. It's very easy
to use...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!
|||Fanny,
In Enterprise Manager, right click on the table --> All Tasks --> Manage
indexes.Highlight the index and click 'delete'.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!

how to remove index

hi I am new to sql server and I want to remove an index in a table in the no
rthwinds practice database. Can I do it in enterprise manager or is there a
script to run in query analyzer? thanks!You can do it from EM. But... it's always best to learn the real syntax if
you can. Read in Books Online about the DROP INDEX command. It's very easy
to use...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!|||Fanny,
In Enterprise Manager, right click on the table --> All Tasks --> Manage
indexes.Highlight the index and click 'delete'.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"fanny" <anonymous@.discussions.microsoft.com> wrote in message
news:19EFB062-883D-41C6-AE3C-B82741DC56AA@.microsoft.com...
> hi I am new to sql server and I want to remove an index in a table in the
northwinds practice database. Can I do it in enterprise manager or is there
a script to run in query analyzer? thanks!

How to remove Duplicated data in SQL Table?

Hi

I want to know, how to remove duplicated data in Sql Table using a SQL query? Im using SQL Management Studio Express Edition to connect to database.

Your help will be highly appreciated.

See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1425992&SiteID=1

Or these articles:

Duplicates –Delete
http://www.sql-server-performance.com/rd_delete_duplicates.asp

Duplicates –Find
http://www.aspfaq.com/2431

Duplicates –How do I remove duplicates from a table?
http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html

Duplicates –How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444

How to remove a table after merge replication

I want to remove a table after merge replication, and i want to change the
identity range for replication
How can I modify the table or remove it from the Publication so that it can
be modified?
Regards
Srinivasan K
drop the subscriptions and run sp_dropmergearticle to drop the article.
If you are using automatic identity range management you will have to issue
the following commands.
sp_changemergearticle 'northwind','categories','pub_identity_range',300
go
sp_changemergearticle 'northwind','categories','identity_range',300
go
sp_changemergearticle 'northwind','categories','threshold',90
go
If you are not you can issue dbcc checkident to reseed the identity ranges
on both sides of the equation(s).
ie
dbcc checkident('categories',reseed,300)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Remove a table after merge replication" <Remove a table after merge
replication@.discussions.microsoft.com> wrote in message
news:F692A05B-5D94-46C7-96F0-6D7B06E07BBE@.microsoft.com...
> I want to remove a table after merge replication, and i want to change the
> identity range for replication
> How can I modify the table or remove it from the Publication so that it
can
> be modified?
>
> Regards
> Srinivasan K
>
>

How to Remove a charater from a SQL Data.

Dear Frineds

I have a hyperlink datatype column in Access database.I import that column into SQL Table.

Now my column in sql table say 'imp_cl 'have all data from Acces table.
But all record are prefix with"#" and at the end of value also have "#".
that means it imported in following way

eg. #//Server/image/img1.pdf#

Now I want to remove this # from both side.As we have around 80,000 record of same type,it is very difficult to do it record by record.

I would like to know aay fuction ,method or programme to remove this "#" from all record.

Thannk you

gracesonDECLARE @.c NVARCHAR(200)

SET @.c = '#//Server/image/img1.pdf#'

SELECT DataLength(@.c), @.c, SubString(@.c, 2, Datalength(@.c) / 2 - 2)
-PatP

How to remove 1:1 Relationships

I have an asset schema problem which I'm not too sure how to resolve...

I have an asset table which stores values common to all asset types. I have created seperate asset type tables(VehicleAssetDetail, PropertyAssetDetail, InvestmentAssetDetail) to store their specific details.

This in turn creates 1:1 relationships between "Asset" table and the respective detail tables.

If I were to store all the details in the "Asset" table it would cause a lot of redundancy bearing in my mind there are about four other asset type details I have excluded from the schema.

Are the 1:1 relationships bad? If they are, how do I resolve them without throwing all the columns from the asset details tables into the Asset table?

Attached is a 15Kb gif image of the schema. I'd really appreciate any help with this... :)
Thanks.A lot of your assertions are fals.

Why would a 1:1 create redundant data?

Only reason I would use a 1:1 is if the row length is too long, then I would catergorize the data into appropriate entities.

But remember, the best join, is none|||Hi Brett...
You may have misunderstood my statement...
If all the columns of each Detail table would be placed in the "Asset" table and the Details tables dropped, most of the columns in the "Asset" table would be null, except for the columns which relate to the specific asset which is inserted.

A 1:1 relationship would not create redundancy.
So back to my original question... :)|||This was raised in another thread which may be worth a read

http://www.dbforums.com/showthread.php?t=1622324

Read the last 3 or 4 posts or so and you'll get the answer ;)

How to release Table unused space

Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
JuanDo you have Text or image columns? Did you drop any recently?
--
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:
> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> > Hi all
> >
> > I am having some disk space issue, and I notice that I have some tables
> > with
> > a lot of GB of unused space.
> >
> > Is it a way to claim and release that unused space?
> >
> > To give you a better picture, I have just one table with below information
> > Rows = 131977895
> > Reserved =146270344 KB
> > Index_Size = 2767616 KB
> > Data = 70372568 KB
> > Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> >
> >
> > This table is reindexed every sunday (yesterday was the last reindex) and
> > it
> > does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> > get these results:
> > DBCC SHOWCONTIG scanning 'Sales' table...
> > Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> > TABLE level scan performed.
> > - Pages Scanned........................: 8796978
> > - Extents Scanned.......................: 1101956
> > - Extent Switches.......................: 1101956
> > - Avg. Pages per Extent..................: 8.0
> > - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> > - Logical Scan Fragmentation ..............: 0.04%
> > - Extent Scan Fragmentation ...............: 9.80%
> > - Avg. Bytes Free per Page................: 647.0
> > - Avg. Page Density (full)................: 92.01%
> > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> >
> >
> > Could someone give me an idea on how to release the 70 GB of unused space?
> >
> > Thanks a lot
> >
> > Juan
> >
>
>

How to release Table unused space

Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
Juan
Do you have Text or image columns? Did you drop any recently?
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>
|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>
|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:

> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
>
>

How to release Table unused space

Hi all
I am having some disk space issue, and I notice that I have some tables with
a lot of GB of unused space.
Is it a way to claim and release that unused space?
To give you a better picture, I have just one table with below information
Rows = 131977895
Reserved =146270344 KB
Index_Size = 2767616 KB
Data = 70372568 KB
Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
This table is reindexed every sunday (yesterday was the last reindex) and it
does not look to have fragmentation issues, when I ran a DBCC Showcontig I
get these results:
DBCC SHOWCONTIG scanning 'Sales' table...
Table: 'Sales' (1228791685); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned........................: 8796978
- Extents Scanned.......................: 1101956
- Extent Switches.......................: 1101956
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.79% [1099623:110
1957]
- Logical Scan Fragmentation ..............: 0.04%
- Extent Scan Fragmentation ...............: 9.80%
- Avg. Bytes Free per Page................: 647.0
- Avg. Page Density (full)................: 92.01%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Could someone give me an idea on how to release the 70 GB of unused space?
Thanks a lot
JuanDo you have Text or image columns? Did you drop any recently?
Andrew J. Kelly SQL MVP
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Juan,
Your numbers don't match.
The first data you mention is 132 million rows, and 50% unused space.
But then the DBCC SHOWCONTIG output reports 1,228 millions rows and 10%
unused space.
Gert-Jan
Juan wrote:
> Hi all
> I am having some disk space issue, and I notice that I have some tables wi
th
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
> This table is reindexed every sunday (yesterday was the last reindex) and
it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan|||Did you get your numbers below from sp_spaceused? You cannot rely on this
for accurate information. DBCC updateusage will help.
Note that from showcontig output your pages are mostly full --> not much
unused space.
TheSQLGuru
President
Indicium Resources, Inc.
"Juan" <Juan@.discussions.microsoft.com> wrote in message
news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
> Hi all
> I am having some disk space issue, and I notice that I have some tables
> with
> a lot of GB of unused space.
> Is it a way to claim and release that unused space?
> To give you a better picture, I have just one table with below information
> Rows = 131977895
> Reserved =146270344 KB
> Index_Size = 2767616 KB
> Data = 70372568 KB
> Unused = 73130160 KB <-- here is my concernd almost 70 GB unused !!!
>
> This table is reindexed every sunday (yesterday was the last reindex) and
> it
> does not look to have fragmentation issues, when I ran a DBCC Showcontig I
> get these results:
> DBCC SHOWCONTIG scanning 'Sales' table...
> Table: 'Sales' (1228791685); index ID: 1, database ID: 16
> TABLE level scan performed.
> - Pages Scanned........................: 8796978
> - Extents Scanned.......................: 1101956
> - Extent Switches.......................: 1101956
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.79% [1099623:1
101957]
> - Logical Scan Fragmentation ..............: 0.04%
> - Extent Scan Fragmentation ...............: 9.80%
> - Avg. Bytes Free per Page................: 647.0
> - Avg. Page Density (full)................: 92.01%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Could someone give me an idea on how to release the 70 GB of unused space?
> Thanks a lot
> Juan
>|||Ey, you are right, I ran a DBCC updateusage and it fix the numbers, I am
getting now just 300 KB of unused space, this makes sense now.
Thanks a lot, I'll keep on mind to run a DBCC UpdateUsage next time I check
the sizes of my tables.
Juan
"TheSQLGuru" wrote:

> Did you get your numbers below from sp_spaceused? You cannot rely on this
> for accurate information. DBCC updateusage will help.
> Note that from showcontig output your pages are mostly full --> not much
> unused space.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juan" <Juan@.discussions.microsoft.com> wrote in message
> news:A6F92E41-49D3-4D64-9123-63FE8740E18E@.microsoft.com...
>
>

How to reinitialize a table in transactional publication.

I have set-up replication using transactional publication and I have
configured subscription to be near continuous.
I have this issue. When setting up subscription I have specified that the
subscription database already had data. So when the snapshot agent is
initialized it does not recreate the tables on the subscription database.
This is ok. But, now if I was to re-initialize only one of the tables, how
do I achieve this without breaking replication.
-Nags
You can't really do this.
If you start with a no-sync, you can't re-initialize. Only subscriptions
that were set for automatic synchronization can be re-initialized.
What I do in situations like this is to drop the article, and then recreate
it in a new publication.
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eQMRViYIEHA.828@.TK2MSFTNGP10.phx.gbl...
> I have set-up replication using transactional publication and I have
> configured subscription to be near continuous.
> I have this issue. When setting up subscription I have specified that the
> subscription database already had data. So when the snapshot agent is
> initialized it does not recreate the tables on the subscription database.
> This is ok. But, now if I was to re-initialize only one of the tables,
how
> do I achieve this without breaking replication.
> -Nags
>
|||Can we drop an article once the tables are published and also subscribed ?
I thought that once there is a subscription on a publication (at least in
transactional publication) we cannot drop the article.
-Nags
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OIi$glcIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> You can't really do this.
> If you start with a no-sync, you can't re-initialize. Only subscriptions
> that were set for automatic synchronization can be re-initialized.
> What I do in situations like this is to drop the article, and then
recreate[vbcol=seagreen]
> it in a new publication.
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eQMRViYIEHA.828@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
database.
> how
>
|||You will have to drop the subscription for both automatic and nosync
subscriptions, then you can drop the article.
"Nags" <nags@.DontSpamMe.com> wrote in message
news:uSGXCLhIEHA.3440@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Can we drop an article once the tables are published and also subscribed ?
> I thought that once there is a subscription on a publication (at least in
> transactional publication) we cannot drop the article.
> -Nags
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OIi$glcIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> recreate
> the
> database.
tables,
>
|||But, I do not want to break replication
-Nags
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eg$wjYkIEHA.3536@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You will have to drop the subscription for both automatic and nosync
> subscriptions, then you can drop the article.
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:uSGXCLhIEHA.3440@.TK2MSFTNGP09.phx.gbl...
?[vbcol=seagreen]
in[vbcol=seagreen]
subscriptions[vbcol=seagreen]
that[vbcol=seagreen]
is
> tables,
>
|||Nags wrote:
> I have set-up replication using transactional publication and I have
> configured subscription to be near continuous.
> I have this issue. When setting up subscription I have specified that the
> subscription database already had data. So when the snapshot agent is
> initialized it does not recreate the tables on the subscription database.
> This is ok. But, now if I was to re-initialize only one of the tables, how
> do I achieve this without breaking replication.
> -Nags
>
You are not very well..
However, there are several tricks you can try:
a) you can afford having no transaction on the source table for a
certain time
=> put a trigger for insert / update / delete on the table which
always raise error and rollback
=> wait the log reader agent and the distribution agent have purged
any pending transaction on this table
=> bulk copy your table from publisher to subscriber
=> remove the trigger
b) you can also create a new publication on the table ( I never tried to
publish twice a table on the same database, but you can always create a
"mirror" table on the publishing database that you keep in sync with
triggers )
On the subscriber, subscribe to this new publication and wait for
synchronisation
Now, here is the trick:
On the publisher you have articles A and A2 which you know are in sync
On the subscriber you have tables Arep and A2rep and you know that A2rep
is in sync
So, stop the log reader agent.
Wait long enough so that the distributing agents tell "no more transactions"
Copy A2rep into Arep
restart log reader agent.
Now Arep is sync'ed and you can drop all the A2 stuff.
If you are not familiar with these operations, you might make a
rehearsal on a test server before trying your production server!

how to register a table

I was advised to use a table in addition to the standard aspnet_ tables for keeping extra data and to register it with the rest of the database. However, when I tried to register it, I couldn't even see a database to choose from then received an error. All the tables, including the ones I created, work when I run my VSE 2005 project. If someone can give me some A-B-C guidance, I'd appreciate it.

ur query is not clear, give some of ur working example...

|||

I'm not sure how I'd give a working example since the question has to do with a database and there's not much I can show. I run aspnet_regsql so I can add a table to the standard aspnetdb.mdf. When I try to see what databases are available from the wizard that runs, I get the error "Connection failed to query a list of database names from the SQL server." On the other hand, when I use the <default> database shown in the wizard, I get the message "Setup failed. Exception: Unable to connect to SQL Server database"

This problem is quite common, I'm sure, and I don't know what other information I can give, except that I obviously need to somewhere insert a missing step that I've omitted. Do I need to run aspnet_regiis or something? I do this and only get a list of all the different options.

|||

OK, I think I'm good now. Upon reading through some more posts regarding this problem, I picked up that I needed to write \sqlexpress after the server name. Problem solved.