Hi all!
I have a SQL Server database with a secondary datafile (*.ndf). I would like to be able to delete that file and only use the *.mdf to store data. Of cource I also have a transaction log file.
Is it possible to move data from the ndf to the mdf and the delete the ndf'
Regards
/FredrikHi,
SQL Server spans data across all data files within a filegroup.So all files
fill will be used for all DMLs. to remove a file , you must first have the
data moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHEINKFILE command.
This will empty the file and mark it as unavailable. After that execute
REMOVE FILE in ALTER DATABASE command to drop the NDF file.
See DBCC SHRINKFILE and ALTER database in books online for syntax.
--
Thanks
Hari
MCDBA
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:5DCC35E5-43A0-45B7-A3F6-81353A62F09E@.microsoft.com...
> Hi all!
> I have a SQL Server database with a secondary datafile (*.ndf). I would
like to be able to delete that file and only use the *.mdf to store data. Of
cource I also have a transaction log file.
> Is it possible to move data from the ndf to the mdf and the delete the
ndf'
> Regards
> /Fredrik
Friday, March 30, 2012
Remove inactive entries from transaction log and log shipping
When I try to take a Transaction log backup of a database (Recovery model
set to Full), I get an option to truncate the transaction log by checking
"Remove inactive entries from transaction log" check box in the backup
dialog. This basically causes the inactive portion of the transaction log
(where transaction is completes, either rolled back are committed) to be
truncated. So this potentially leads to saving of huge amount of hard disk
space.
Now, when I setup log shipping is there any similar option I can set on the
primary database? After every transaction log backup I want inactive entries
from the transaction log to be removed. Is there any way to do this? If yes,
how?. If no, why is that so? Once the backup is taken inactive log entries
on the primary database are of no use to log ship, right?
Rathna RajThe option in the backup dialog is very misleading. It basically means that
EM does *not* send the WITH NO_TRUNCATE option. This option is designed for
doing an "emergency" backup in case the database is corrupted, and I have
communicated to MS that the dialog shouldn't be misleading in this way. The
normal way to do log backup is by not specifying this option (having the
checkbox checked (I believe), the default). If the log shipping was written
by a sane individual ;-), then it should not specify this option, I.e., you
already have the desired behavior. To be certain, use a Profiler trace to
see what is going on.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rathna Raj" <RathnaRajT@.icode.com> wrote in message
news:%2365JEWJyDHA.1272@.TK2MSFTNGP12.phx.gbl...
> When I try to take a Transaction log backup of a database (Recovery model
> set to Full), I get an option to truncate the transaction log by checking
> "Remove inactive entries from transaction log" check box in the backup
> dialog. This basically causes the inactive portion of the transaction log
> (where transaction is completes, either rolled back are committed) to be
> truncated. So this potentially leads to saving of huge amount of hard disk
> space.
> Now, when I setup log shipping is there any similar option I can set on
the
> primary database? After every transaction log backup I want inactive
entries
> from the transaction log to be removed. Is there any way to do this? If
yes,
> how?. If no, why is that so? Once the backup is taken inactive log entries
> on the primary database are of no use to log ship, right?
> Rathna Raj
>|||Hi Rathna,
Thanks for your post. Based on my research, log shipping does done backup
log part, so transaction log should not be large. Therefore, it seems that
truncating the transaction log of the primary server manually in log
shipping is not necessary.
For more information regarding log shipping, please refer to the following
article on SQL Server Books Online. Also, the example in this article will
help describe how the log shipping works.
Topic; "Log Shipping"
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.sql
set to Full), I get an option to truncate the transaction log by checking
"Remove inactive entries from transaction log" check box in the backup
dialog. This basically causes the inactive portion of the transaction log
(where transaction is completes, either rolled back are committed) to be
truncated. So this potentially leads to saving of huge amount of hard disk
space.
Now, when I setup log shipping is there any similar option I can set on the
primary database? After every transaction log backup I want inactive entries
from the transaction log to be removed. Is there any way to do this? If yes,
how?. If no, why is that so? Once the backup is taken inactive log entries
on the primary database are of no use to log ship, right?
Rathna RajThe option in the backup dialog is very misleading. It basically means that
EM does *not* send the WITH NO_TRUNCATE option. This option is designed for
doing an "emergency" backup in case the database is corrupted, and I have
communicated to MS that the dialog shouldn't be misleading in this way. The
normal way to do log backup is by not specifying this option (having the
checkbox checked (I believe), the default). If the log shipping was written
by a sane individual ;-), then it should not specify this option, I.e., you
already have the desired behavior. To be certain, use a Profiler trace to
see what is going on.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rathna Raj" <RathnaRajT@.icode.com> wrote in message
news:%2365JEWJyDHA.1272@.TK2MSFTNGP12.phx.gbl...
> When I try to take a Transaction log backup of a database (Recovery model
> set to Full), I get an option to truncate the transaction log by checking
> "Remove inactive entries from transaction log" check box in the backup
> dialog. This basically causes the inactive portion of the transaction log
> (where transaction is completes, either rolled back are committed) to be
> truncated. So this potentially leads to saving of huge amount of hard disk
> space.
> Now, when I setup log shipping is there any similar option I can set on
the
> primary database? After every transaction log backup I want inactive
entries
> from the transaction log to be removed. Is there any way to do this? If
yes,
> how?. If no, why is that so? Once the backup is taken inactive log entries
> on the primary database are of no use to log ship, right?
> Rathna Raj
>|||Hi Rathna,
Thanks for your post. Based on my research, log shipping does done backup
log part, so transaction log should not be large. Therefore, it seems that
truncating the transaction log of the primary server manually in log
shipping is not necessary.
For more information regarding log shipping, please refer to the following
article on SQL Server Books Online. Also, the example in this article will
help describe how the log shipping works.
Topic; "Log Shipping"
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.sql
remove ignored words - full text catalog
hey
when i try to search an "noise word" it drop me an ERROR
what i have done :
- stop full text service
- remove the words i want ,from noise.enu , noise.eng , noise.dat (@.@.language = u.s...) the files is in system32 and in program files\...\config .
- start full text service
-rebuild and after that re populate the catalog
and it still drop me an ERROR !!! how can i resolve this prob ?
tnx tnx .
Why are you storing SQL Server Full Text file in System 32, you don't create the part SQL Server creates the part. You just create the Text and NText datatype and upload the file SQL Server creates an Arithmetic pointer to the file and I know it cannot be in System 32. System 32 is in Windows folder SQL Server will not get a file from Windows folder. Run a search for Full Text, Microsoft Search Service, Micorsoft Catalog and CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE in SQL Server BOL(books online). Hope this helps.|||no , i don't store the catalog in system 32 , the noise.* files are in library system32 and in ...\config .
when u install sql server , it copy the noise.* files to those librarys .
and correct , i use CONTAINS , i doesn't allways get this error , only when i search "ignored word"
Try this ' "ignored word" ' or ' "ignored Wor*" ' it may make a difference. Hope this helps.
|||hyesooo it doesn't give me an error ,BUT it doesn't give the "correct" answer exp' :
the phrase : "windows cd"
----------------
the search phrase : "cd"
it return "windows cd" <-CORRECT
----------------
----------------
the search phrase : "*c*"
it return "windows cd" <-NOT CORRECT
----------------
i dont need to search in part of the word .
so how can i search "c" that won't give me "ignored words" error & will search like all other words (that won't search part of the word , like the exm')
tnx a_r .
|||There are noise words try this link for better queries and more about noise words. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/3454281|||tnx .
but how can i remove those "noise words" ? that it won't give me the error .|||You did not read the aritcle it shows you how to edit that file. Hope this helps.|||hey , i read the article & and i did more that what the article intracte to do . see the first post .
Remove Identity seed w/ T-SQL
Does anyone know how to remove the Identity attribute of a column using T-SQL. I have created the Identity attribute with the following. I know I can do it with a script created through Enterprise Manager (which is really long and hard to re-create with Dynamic SQL), but I am looking for a one line opposite to the following statement:
IF (SELECT COL_LENGTH('[dbo].tablename', 'UserID')) IS NULL
ALTER TABLE [dbo].tablename ADD UserID INT NOT NULL IDENTITY (1, 1)
something like:
ALTER TABLE [dbo].tablename ALTER COLUMN UserID"REMOVE IDENTITY"
Thanks,
JoshThere is not a simple way to accomplish this. You need basically to drop the column altogether and then add it back to the table. as a non-identity column.
So you could add a new non-identity column to the table (UserIDNEW), copy your UserID data into it, drop the UserID column, then rename your UserIDNEW column back to UserID.
Wish I had better news.
I looked upthis article to explain more concretely what I am talking about.
Terri|||Terri,
I too found the same article. I didn't think there was a simple way to do it, but I thought I would ask.
Thanks,
Josh
Remove identity property of a primary key
Hi,
I have a table with a column named ID as primary key, this column has
the identity property. This ID is referenced by some other tables as foreign
key.
Is there a way, I can use "alter table alter ID int not null...." TSQL
to remove this identity property?
Thanks!
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy1. drop FK
2. sp_rename table with identity to some other name
3. create table new with the same name without identity
4. insert new select * from old
5. create FK
6 drop old
"Hardy Wang" <hardywang@.hotmail.com> wrote in message
news:O7AqRnXrFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as
> foreign key.
> Is there a way, I can use "alter table alter ID int not null...." TSQL
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>|||The only way is dropping the column. Try using EM if you really want to do
this. In EM, before saving changes, press button "save change script", third
from left to right in the tool bar. You will see what really EM does in orde
r
to accomplish this tak.
Example: (from northwind.orders)
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
DECLARE @.v sql_variant
SET @.v = N''
EXECUTE sp_addextendedproperty N'MS_Description', @.v, N'user', N'dbo',
N'table', N'Tmp_Orders', N'column', N'OrderID'
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID,
OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry FROM dbo.Orders (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT
AMB
"Hardy Wang" wrote:
> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as forei
gn
> key.
> Is there a way, I can use "alter table alter ID int not null...." TSQ
L
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>
>
I have a table with a column named ID as primary key, this column has
the identity property. This ID is referenced by some other tables as foreign
key.
Is there a way, I can use "alter table alter ID int not null...." TSQL
to remove this identity property?
Thanks!
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy1. drop FK
2. sp_rename table with identity to some other name
3. create table new with the same name without identity
4. insert new select * from old
5. create FK
6 drop old
"Hardy Wang" <hardywang@.hotmail.com> wrote in message
news:O7AqRnXrFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as
> foreign key.
> Is there a way, I can use "alter table alter ID int not null...." TSQL
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>|||The only way is dropping the column. Try using EM if you really want to do
this. In EM, before saving changes, press button "save change script", third
from left to right in the tool bar. You will see what really EM does in orde
r
to accomplish this tak.
Example: (from northwind.orders)
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
DECLARE @.v sql_variant
SET @.v = N''
EXECUTE sp_addextendedproperty N'MS_Description', @.v, N'user', N'dbo',
N'table', N'Tmp_Orders', N'column', N'OrderID'
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID,
OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry FROM dbo.Orders (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT
AMB
"Hardy Wang" wrote:
> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as forei
gn
> key.
> Is there a way, I can use "alter table alter ID int not null...." TSQ
L
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>
>
remove IDENTITY in sql server
suppose my table is
create table TEST (
id NUMERIC(10,0) IDENTITY(1,1) NOT NULL)
I want to remove IDENTITY(1,1) from id without dropping tableHi ankur
I suspect you are planning on sorting out your own no-gaps identity field here. This is a very common request but rarely is a good choice. Before you carry on I would really recommend you just carry for a little longer in this thread:
http://www.dbforums.com/showthread.php?t=1620216
It could save you a heck of a lot of wasted time if it turns out this is not the best way to procede.|||I am toatally new to sql server
so
initailly our project manager has ordered me to put auto incrment
but
now
PM has said to remove auto increment
I am in bit trouble dont know to remove auto increment
so
I asked help from you
help me please|||Look up the ALTER TABLE command in BOL. You will want to pay attention to the DROP COLUMN option.|||beh.
just tell him you removed it, but don't. PM shouldn't be making such low level design decisions!|||why don't you want to drop the table?
create a new one, copy over the numbers (after all, the table has only one column), then drop the old one and rename the new one
something about this whole scenario does not smell right
the project manager is actually you, isn't she|||Think bigger picture
If you still need to retain the id, what is the PM's plan to manage new id's?
And do you have a natural key?|||table is too large but I have taken a small example
there large data in I want not to drop
if there is any query are way to remove IDENTITY(1,1) without effecting
data|||table is too large but I have taken a small example
there large data in I want not to drop
if there is any query are way to remove IDENTITY(1,1) without effecting
data
No, but what's big? ALTER does not allow you to drop an IDENTITY Property on a Column...
As a matter of fact, do you know how save a script in Enterprise Mangler?
go create a test table with an identity column
Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do
Well I can tell you..it it will copy the data to another table where the column will be defined as int, drop the old table and rename the new table to the old table...but go look for yourself
Personally, I'd bdp the data out in native format, create a new table with the definition you want, bcp the data in, rename the old table to something else like table_old, the rename the new table to the old
Again, how many rows are we talking about
Oh...you can disable the identity column in your stored procedures...you are using sprocs as data access only right...and then do
SET IDENTITY_INSERT ON
But that's a hack|||And do you have a natural key?as if this would make a difference to the yes-identity-no-wait-a-sec-no-identity PM|||I want to remove IDENTITY(1,1) from id without dropping tableDropping the identity property can not be done directly, but you can acheive similar effects in more than one way. The problem is that there are many reasons for not wanting to drop the table, and without understanding exactly what you are trying to do or avoid I can't give you clear instructions.
The short answer is to simply recreate a working copy of the table without the identity property, drop the original table, then rename the working copy so it has the same name as the original. This process is easy, safe, and relatively foolproof.
There are a number of other solutions, but without understanding your requirements I have no idea which solution or solutions to suggest.
-PatP|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?Not naive at all George - that's the way I would do it (though with decimal(10) so you can insert half a million records a day for 6 years). There is a downside to that though:
http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html|||Ahh I see!
Thanks Poots :)
I guess as long as you're aware of the potential problems you can use your best judgement per case.|||Yes I got my answer to remove IDENTITY from column
see attachment set IDENTITY to NO|||see attachment set IDENTITY to NOJust curioius, but do you have any clue what setting IDENTITY to "NO" on this screen will actually do? It will produce exactly the result you have requested, using exactly the method that you wanted to avoid.
As long as you are happy with what you've found, that's fine. It just isn't what you requested! ;)
-PatP|||Yes I got my answer to remove IDENTITY from column
see attachment set IDENTITY to NO
To quote a Guiness Stout Commercial...
"Brilliant!"|||go create a test table with an identity column
Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do
That sounds oddly familiar
create table TEST (
id NUMERIC(10,0) IDENTITY(1,1) NOT NULL)
I want to remove IDENTITY(1,1) from id without dropping tableHi ankur
I suspect you are planning on sorting out your own no-gaps identity field here. This is a very common request but rarely is a good choice. Before you carry on I would really recommend you just carry for a little longer in this thread:
http://www.dbforums.com/showthread.php?t=1620216
It could save you a heck of a lot of wasted time if it turns out this is not the best way to procede.|||I am toatally new to sql server
so
initailly our project manager has ordered me to put auto incrment
but
now
PM has said to remove auto increment
I am in bit trouble dont know to remove auto increment
so
I asked help from you
help me please|||Look up the ALTER TABLE command in BOL. You will want to pay attention to the DROP COLUMN option.|||beh.
just tell him you removed it, but don't. PM shouldn't be making such low level design decisions!|||why don't you want to drop the table?
create a new one, copy over the numbers (after all, the table has only one column), then drop the old one and rename the new one
something about this whole scenario does not smell right
the project manager is actually you, isn't she|||Think bigger picture
If you still need to retain the id, what is the PM's plan to manage new id's?
And do you have a natural key?|||table is too large but I have taken a small example
there large data in I want not to drop
if there is any query are way to remove IDENTITY(1,1) without effecting
data|||table is too large but I have taken a small example
there large data in I want not to drop
if there is any query are way to remove IDENTITY(1,1) without effecting
data
No, but what's big? ALTER does not allow you to drop an IDENTITY Property on a Column...
As a matter of fact, do you know how save a script in Enterprise Mangler?
go create a test table with an identity column
Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do
Well I can tell you..it it will copy the data to another table where the column will be defined as int, drop the old table and rename the new table to the old table...but go look for yourself
Personally, I'd bdp the data out in native format, create a new table with the definition you want, bcp the data in, rename the old table to something else like table_old, the rename the new table to the old
Again, how many rows are we talking about
Oh...you can disable the identity column in your stored procedures...you are using sprocs as data access only right...and then do
SET IDENTITY_INSERT ON
But that's a hack|||And do you have a natural key?as if this would make a difference to the yes-identity-no-wait-a-sec-no-identity PM|||I want to remove IDENTITY(1,1) from id without dropping tableDropping the identity property can not be done directly, but you can acheive similar effects in more than one way. The problem is that there are many reasons for not wanting to drop the table, and without understanding exactly what you are trying to do or avoid I can't give you clear instructions.
The short answer is to simply recreate a working copy of the table without the identity property, drop the original table, then rename the working copy so it has the same name as the original. This process is easy, safe, and relatively foolproof.
There are a number of other solutions, but without understanding your requirements I have no idea which solution or solutions to suggest.
-PatP|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?|||This may just be naivety on my part but why can't you add a new int column and copy the identity values into the new column, then drop the identity column?Not naive at all George - that's the way I would do it (though with decimal(10) so you can insert half a million records a day for 6 years). There is a downside to that though:
http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html|||Ahh I see!
Thanks Poots :)
I guess as long as you're aware of the potential problems you can use your best judgement per case.|||Yes I got my answer to remove IDENTITY from column
see attachment set IDENTITY to NO|||see attachment set IDENTITY to NOJust curioius, but do you have any clue what setting IDENTITY to "NO" on this screen will actually do? It will produce exactly the result you have requested, using exactly the method that you wanted to avoid.
As long as you are happy with what you've found, that's fine. It just isn't what you requested! ;)
-PatP|||Yes I got my answer to remove IDENTITY from column
see attachment set IDENTITY to NO
To quote a Guiness Stout Commercial...
"Brilliant!"|||go create a test table with an identity column
Then go into EM and remove the identity column and save it...it wil ask you if you want to save the script SQL Server used to make the change...you'll see what SQL Server has to do
That sounds oddly familiar
Remove Identity column constraint/mgmt
Hello...
We have some tables that had been using Identity columns as a Primary
Key...but we abandoned that approach a few weeks ago and adopted GUIDs
instead.
These tables are included in Publications that were originally on SQL
2000...but we upgraded to SQL 2005.
Is there a way I can remove the Identity constraint from the server
pub...does the Not for Replication handle this?
Or...would be better to Drop the column and reinitialize the subscribers?
thanks for any help
- will
You would be best to drop the identity column, NFR will not drop the
identity column but will not enforce the identity property if the insert is
caused by a replication process.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"dw" <dw@.discussions.microsoft.com> wrote in message
news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
> Hello...
> We have some tables that had been using Identity columns as a Primary
> Key...but we abandoned that approach a few weeks ago and adopted GUIDs
> instead.
> These tables are included in Publications that were originally on SQL
> 2000...but we upgraded to SQL 2005.
> Is there a way I can remove the Identity constraint from the server
> pub...does the Not for Replication handle this?
> Or...would be better to Drop the column and reinitialize the subscribers?
> thanks for any help
> - will
|||Thanks for the help. I may test out going the NFR route...just so I don't
have to mess with the table schema too much. Will changing to NFR force a
Re-Init of for the subscribers?
"Hilary Cotter" wrote:
> You would be best to drop the identity column, NFR will not drop the
> identity column but will not enforce the identity property if the insert is
> caused by a replication process.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
>
>
|||This is something you do on the subscriber - so for transactional
replication it will have no impact on reinitialization. For merge and
updateable subscribers it will.
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
"dw" <dw@.discussions.microsoft.com> wrote in message
news:C109A5B7-07DE-4E85-88C5-6BA71CBCCEFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the help. I may test out going the NFR route...just so I
> don't
> have to mess with the table schema too much. Will changing to NFR force a
> Re-Init of for the subscribers?
> "Hilary Cotter" wrote:
sql
We have some tables that had been using Identity columns as a Primary
Key...but we abandoned that approach a few weeks ago and adopted GUIDs
instead.
These tables are included in Publications that were originally on SQL
2000...but we upgraded to SQL 2005.
Is there a way I can remove the Identity constraint from the server
pub...does the Not for Replication handle this?
Or...would be better to Drop the column and reinitialize the subscribers?
thanks for any help
- will
You would be best to drop the identity column, NFR will not drop the
identity column but will not enforce the identity property if the insert is
caused by a replication process.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"dw" <dw@.discussions.microsoft.com> wrote in message
news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
> Hello...
> We have some tables that had been using Identity columns as a Primary
> Key...but we abandoned that approach a few weeks ago and adopted GUIDs
> instead.
> These tables are included in Publications that were originally on SQL
> 2000...but we upgraded to SQL 2005.
> Is there a way I can remove the Identity constraint from the server
> pub...does the Not for Replication handle this?
> Or...would be better to Drop the column and reinitialize the subscribers?
> thanks for any help
> - will
|||Thanks for the help. I may test out going the NFR route...just so I don't
have to mess with the table schema too much. Will changing to NFR force a
Re-Init of for the subscribers?
"Hilary Cotter" wrote:
> You would be best to drop the identity column, NFR will not drop the
> identity column but will not enforce the identity property if the insert is
> caused by a replication process.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
>
>
|||This is something you do on the subscriber - so for transactional
replication it will have no impact on reinitialization. For merge and
updateable subscribers it will.
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
"dw" <dw@.discussions.microsoft.com> wrote in message
news:C109A5B7-07DE-4E85-88C5-6BA71CBCCEFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the help. I may test out going the NFR route...just so I
> don't
> have to mess with the table schema too much. Will changing to NFR force a
> Re-Init of for the subscribers?
> "Hilary Cotter" wrote:
sql
Remove Identity
I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
ToeenHi.
You can use something as this:
Create tha table with Identity...
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
[f_city_desc] [varchar] (50) DEFAULT ('')
) ON [PRIMARY]
GO
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NULL
Hermilson T.
****************************************
**********************
I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
Toeen|||> You can use something as this:
> Create tha table with Identity...
> CREATE TABLE [City]
> ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> [f_city_desc] [varchar] (50) DEFAULT ('')
> ) ON [PRIMARY]
> GO
> And to Modify the column, erasing the property of the Identity.
> Alter TABLE City
> Alter column f_city_id [int] NOT NULL
You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> You can't clear the IDENTITY property from the table column using the
ALTER
> TABLE statement. You have to re-create the database and move the data, for
> example:
> BEGIN TRANSACTION
> CREATE TABLE dbo.TemporaryTable
> (
> id int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.TemporaryTable(id)
> SELECT id FROM dbo.YourTable TABLOCKX')
> GO
> DROP TABLE dbo.YourTable
> GO
> EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> GO
> COMMIT
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing
set identity insert <tablename> on
Then you can insert your values. When you are done, set identity insert to
off.
Eric
"Toeen" <masha@.brain.net.pk> wrote in message
news:uMU5qUJ8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> I have created an identity column in a table.Now i want to remove the
> identity property from the column by using T-SQL script.
> Can anyone tell How?
> Thanking you!
> Toeen
>|||> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.
SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
Hope this helps.
Dan Guzman
SQL Server MVP
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402aaa65$0$13253$ba620e4c@.news.skynet.be...
> Or you can use a select into statement
>
> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
> exec sp_rename dbo.TemporaryTable', 'YourTable'
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> ALTER
for
> rights.
>
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
ToeenHi.
You can use something as this:
Create tha table with Identity...
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
[f_city_desc] [varchar] (50) DEFAULT ('')
) ON [PRIMARY]
GO
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NULL
Hermilson T.
****************************************
**********************
I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
Toeen|||> You can use something as this:
> Create tha table with Identity...
> CREATE TABLE [City]
> ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> [f_city_desc] [varchar] (50) DEFAULT ('')
> ) ON [PRIMARY]
> GO
> And to Modify the column, erasing the property of the Identity.
> Alter TABLE City
> Alter column f_city_id [int] NOT NULL
You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> You can't clear the IDENTITY property from the table column using the
ALTER
> TABLE statement. You have to re-create the database and move the data, for
> example:
> BEGIN TRANSACTION
> CREATE TABLE dbo.TemporaryTable
> (
> id int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.TemporaryTable(id)
> SELECT id FROM dbo.YourTable TABLOCKX')
> GO
> DROP TABLE dbo.YourTable
> GO
> EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> GO
> COMMIT
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing
set identity insert <tablename> on
Then you can insert your values. When you are done, set identity insert to
off.
Eric
"Toeen" <masha@.brain.net.pk> wrote in message
news:uMU5qUJ8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> I have created an identity column in a table.Now i want to remove the
> identity property from the column by using T-SQL script.
> Can anyone tell How?
> Thanking you!
> Toeen
>|||> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.
SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
Hope this helps.
Dan Guzman
SQL Server MVP
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402aaa65$0$13253$ba620e4c@.news.skynet.be...
> Or you can use a select into statement
>
> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
> exec sp_rename dbo.TemporaryTable', 'YourTable'
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> ALTER
for
> rights.
>
Remove Identity
I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
ToeenHi
You can use something as this
Create tha table with Identity...
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL
[f_city_desc] [varchar] (50) DEFAULT (''
) ON [PRIMARY
G
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NUL
Hermilson T
*************************************************************
I have created an identity column in a table.Now i want to remove th
identity property from the column by using T-SQL script
Can anyone tell How
Thanking you
Toee|||> You can use something as this:
> Create tha table with Identity...
> CREATE TABLE [City]
> ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> [f_city_desc] [varchar] (50) DEFAULT ('')
> ) ON [PRIMARY]
> GO
> And to Modify the column, erasing the property of the Identity.
> Alter TABLE City
> Alter column f_city_id [int] NOT NULL
You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > You can use something as this:
> >
> > Create tha table with Identity...
> >
> > CREATE TABLE [City]
> > ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> > [f_city_desc] [varchar] (50) DEFAULT ('')
> > ) ON [PRIMARY]
> > GO
> >
> > And to Modify the column, erasing the property of the Identity.
> >
> > Alter TABLE City
> > Alter column f_city_id [int] NOT NULL
> You can't clear the IDENTITY property from the table column using the
ALTER
> TABLE statement. You have to re-create the database and move the data, for
> example:
> BEGIN TRANSACTION
> CREATE TABLE dbo.TemporaryTable
> (
> id int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.TemporaryTable(id)
> SELECT id FROM dbo.YourTable TABLOCKX')
> GO
> DROP TABLE dbo.YourTable
> GO
> EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> GO
> COMMIT
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing
set identity insert <tablename> on
Then you can insert your values. When you are done, set identity insert to
off.
Eric
"Toeen" <masha@.brain.net.pk> wrote in message
news:uMU5qUJ8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> I have created an identity column in a table.Now i want to remove the
> identity property from the column by using T-SQL script.
> Can anyone tell How?
> Thanking you!
> Toeen
>|||> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.
SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402aaa65$0$13253$ba620e4c@.news.skynet.be...
> Or you can use a select into statement
>
> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
> exec sp_rename dbo.TemporaryTable', 'YourTable'
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > > You can use something as this:
> > >
> > > Create tha table with Identity...
> > >
> > > CREATE TABLE [City]
> > > ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> > > [f_city_desc] [varchar] (50) DEFAULT ('')
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > And to Modify the column, erasing the property of the Identity.
> > >
> > > Alter TABLE City
> > > Alter column f_city_id [int] NOT NULL
> >
> > You can't clear the IDENTITY property from the table column using the
> ALTER
> > TABLE statement. You have to re-create the database and move the data,
for
> > example:
> >
> > BEGIN TRANSACTION
> > CREATE TABLE dbo.TemporaryTable
> > (
> > id int NOT NULL
> > ) ON [PRIMARY]
> > GO
> > IF EXISTS(SELECT * FROM dbo.YourTable)
> > EXEC('INSERT INTO dbo.TemporaryTable(id)
> > SELECT id FROM dbo.YourTable TABLOCKX')
> > GO
> > DROP TABLE dbo.YourTable
> > GO
> > EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> > GO
> > COMMIT
> >
> > sincerely,
> > --
> > Sebastian K. Zaklada
> > Skilled Software
> > http://www.skilledsoftware.com
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
>
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
ToeenHi
You can use something as this
Create tha table with Identity...
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL
[f_city_desc] [varchar] (50) DEFAULT (''
) ON [PRIMARY
G
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NUL
Hermilson T
*************************************************************
I have created an identity column in a table.Now i want to remove th
identity property from the column by using T-SQL script
Can anyone tell How
Thanking you
Toee|||> You can use something as this:
> Create tha table with Identity...
> CREATE TABLE [City]
> ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> [f_city_desc] [varchar] (50) DEFAULT ('')
> ) ON [PRIMARY]
> GO
> And to Modify the column, erasing the property of the Identity.
> Alter TABLE City
> Alter column f_city_id [int] NOT NULL
You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > You can use something as this:
> >
> > Create tha table with Identity...
> >
> > CREATE TABLE [City]
> > ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> > [f_city_desc] [varchar] (50) DEFAULT ('')
> > ) ON [PRIMARY]
> > GO
> >
> > And to Modify the column, erasing the property of the Identity.
> >
> > Alter TABLE City
> > Alter column f_city_id [int] NOT NULL
> You can't clear the IDENTITY property from the table column using the
ALTER
> TABLE statement. You have to re-create the database and move the data, for
> example:
> BEGIN TRANSACTION
> CREATE TABLE dbo.TemporaryTable
> (
> id int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.TemporaryTable(id)
> SELECT id FROM dbo.YourTable TABLOCKX')
> GO
> DROP TABLE dbo.YourTable
> GO
> EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> GO
> COMMIT
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing
set identity insert <tablename> on
Then you can insert your values. When you are done, set identity insert to
off.
Eric
"Toeen" <masha@.brain.net.pk> wrote in message
news:uMU5qUJ8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> I have created an identity column in a table.Now i want to remove the
> identity property from the column by using T-SQL script.
> Can anyone tell How?
> Thanking you!
> Toeen
>|||> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.
SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402aaa65$0$13253$ba620e4c@.news.skynet.be...
> Or you can use a select into statement
>
> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
> exec sp_rename dbo.TemporaryTable', 'YourTable'
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > > You can use something as this:
> > >
> > > Create tha table with Identity...
> > >
> > > CREATE TABLE [City]
> > > ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> > > [f_city_desc] [varchar] (50) DEFAULT ('')
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > And to Modify the column, erasing the property of the Identity.
> > >
> > > Alter TABLE City
> > > Alter column f_city_id [int] NOT NULL
> >
> > You can't clear the IDENTITY property from the table column using the
> ALTER
> > TABLE statement. You have to re-create the database and move the data,
for
> > example:
> >
> > BEGIN TRANSACTION
> > CREATE TABLE dbo.TemporaryTable
> > (
> > id int NOT NULL
> > ) ON [PRIMARY]
> > GO
> > IF EXISTS(SELECT * FROM dbo.YourTable)
> > EXEC('INSERT INTO dbo.TemporaryTable(id)
> > SELECT id FROM dbo.YourTable TABLOCKX')
> > GO
> > DROP TABLE dbo.YourTable
> > GO
> > EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> > GO
> > COMMIT
> >
> > sincerely,
> > --
> > Sebastian K. Zaklada
> > Skilled Software
> > http://www.skilledsoftware.com
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
>
Remove identity
Hi all,
In the SQL Server Management Studio Express, when I right click in table, and I choice the modify option, bellow appear the column properties where I set the Identity Specification to No or Yes.
Is there a way to remove the identity from a column
by using alter table?
thanx
Hi,
identity is no property, you have to create another column, copya the values to the new column and drop the old one.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Hi,In the SQL Server Management Studio Express, when I right click in table, and I choice the modify option, bellow appear the column properties where I set the Identity Specification to No or Yes.
This option disables/enable the identity?
But is there a way to disable the identity via command line?
thanx|||
Hi,
"disabling" the property does the things behind the scenes I mentioned in my earlier post. In SQL 2k (I didn't check that in SQL2k5 right now) it did a creation of a new table, copy the data to the new table, and renamed the table vice versa to the new / old name.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Remove hyperlinks when exporting
Hi
Is there any way to remove any hyperlinks in a report when they are exported
to another format like Excel.
ThanksHello,
Based on my scope, there is no built in method in Excel rendering extension
to achieve this goal. The possible solution is writing your own Excel
rendering extension though it is time-consuming.
Microsoft offers several ways for you to send comments or suggestions about
Microsoft products. If you have suggestions for product enhancements that
you would like to see in future versions of Microsoft products, please
contact us using one of the methods listed below.
World Wide Web - To send a comment or suggestion via the Web, use one of
the following methods:
- In Internet Explorer 6, click Send Feedback on the Help menu and then
click the link in the Product Suggestion section of the page that appears.
- Visit the following Microsoft Product Feedback Web site
http://register.microsoft.com/mswish/suggestion.asp
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "l.holmes" <enate@.newsgroups.nospam>
| Subject: Remove hyperlinks when exporting
| Date: Wed, 12 Oct 2005 10:44:39 +0100
| Lines: 8
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#HwMSGxzFHA.904@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 213-232-82-176.dsl.prodigynet.co.uk 213.232.82.176
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp1
3.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:61049
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi
|
| Is there any way to remove any hyperlinks in a report when they are
exported
| to another format like Excel.
|
| Thanks
|
|
||||Hi
Thanks for the reply. Hopefully there will be a way to remove them in RS
2005 or a means to get around this problem.
Regards
Lewis Holmes
eNate
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:3blWar7zFHA.2352@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> Based on my scope, there is no built in method in Excel rendering
> extension
> to achieve this goal. The possible solution is writing your own Excel
> rendering extension though it is time-consuming.
> Microsoft offers several ways for you to send comments or suggestions
> about
> Microsoft products. If you have suggestions for product enhancements that
> you would like to see in future versions of Microsoft products, please
> contact us using one of the methods listed below.
> World Wide Web - To send a comment or suggestion via the Web, use one of
> the following methods:
> - In Internet Explorer 6, click Send Feedback on the Help menu and then
> click the link in the Product Suggestion section of the page that appears.
> - Visit the following Microsoft Product Feedback Web site
> http://register.microsoft.com/mswish/suggestion.asp
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> | From: "l.holmes" <enate@.newsgroups.nospam>
> | Subject: Remove hyperlinks when exporting
> | Date: Wed, 12 Oct 2005 10:44:39 +0100
> | Lines: 8
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <#HwMSGxzFHA.904@.tk2msftngp13.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 213-232-82-176.dsl.prodigynet.co.uk 213.232.82.176
> | Path:
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp1
> 3.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:61049
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi
> |
> | Is there any way to remove any hyperlinks in a report when they are
> exported
> | to another format like Excel.
> |
> | Thanks
> |
> |
> |
>
Is there any way to remove any hyperlinks in a report when they are exported
to another format like Excel.
ThanksHello,
Based on my scope, there is no built in method in Excel rendering extension
to achieve this goal. The possible solution is writing your own Excel
rendering extension though it is time-consuming.
Microsoft offers several ways for you to send comments or suggestions about
Microsoft products. If you have suggestions for product enhancements that
you would like to see in future versions of Microsoft products, please
contact us using one of the methods listed below.
World Wide Web - To send a comment or suggestion via the Web, use one of
the following methods:
- In Internet Explorer 6, click Send Feedback on the Help menu and then
click the link in the Product Suggestion section of the page that appears.
- Visit the following Microsoft Product Feedback Web site
http://register.microsoft.com/mswish/suggestion.asp
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "l.holmes" <enate@.newsgroups.nospam>
| Subject: Remove hyperlinks when exporting
| Date: Wed, 12 Oct 2005 10:44:39 +0100
| Lines: 8
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#HwMSGxzFHA.904@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 213-232-82-176.dsl.prodigynet.co.uk 213.232.82.176
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp1
3.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:61049
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi
|
| Is there any way to remove any hyperlinks in a report when they are
exported
| to another format like Excel.
|
| Thanks
|
|
||||Hi
Thanks for the reply. Hopefully there will be a way to remove them in RS
2005 or a means to get around this problem.
Regards
Lewis Holmes
eNate
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:3blWar7zFHA.2352@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> Based on my scope, there is no built in method in Excel rendering
> extension
> to achieve this goal. The possible solution is writing your own Excel
> rendering extension though it is time-consuming.
> Microsoft offers several ways for you to send comments or suggestions
> about
> Microsoft products. If you have suggestions for product enhancements that
> you would like to see in future versions of Microsoft products, please
> contact us using one of the methods listed below.
> World Wide Web - To send a comment or suggestion via the Web, use one of
> the following methods:
> - In Internet Explorer 6, click Send Feedback on the Help menu and then
> click the link in the Product Suggestion section of the page that appears.
> - Visit the following Microsoft Product Feedback Web site
> http://register.microsoft.com/mswish/suggestion.asp
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> | From: "l.holmes" <enate@.newsgroups.nospam>
> | Subject: Remove hyperlinks when exporting
> | Date: Wed, 12 Oct 2005 10:44:39 +0100
> | Lines: 8
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <#HwMSGxzFHA.904@.tk2msftngp13.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 213-232-82-176.dsl.prodigynet.co.uk 213.232.82.176
> | Path:
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp1
> 3.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:61049
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi
> |
> | Is there any way to remove any hyperlinks in a report when they are
> exported
> | to another format like Excel.
> |
> | Thanks
> |
> |
> |
>
Remove hyperlink when exporting to Excel
How do I remove hyperlinks from reports that are exported to Excel?Help, I have the same problem. I added actions to all my text fields to
allow me to filter the report when a user click a cell. I had no idea this
would totally ruine the report when exporting to Excel (each cell is now a
live hyperlink to the live report - FROM EXCEL!!) Te sheet has grown from a
couple hundred K to 22 MB!!! How can I have these textboxes revert to just
plain text when exported to excel. This is an emergency!|||Essentially, you'll write an expression for:
Navigation:
=IIF (Parameters!render_format.value <> "html", nothing, "[report name]")
Text Decoration (to underline if a hyperlink):
=IIF(Parameters!render_format..Value = "HTML4.0","Underline","None")
Color:
=IIF(Parameters!render_format..Value = "HTML4.0","Blue","Black")
Scott
"Estella Kang" wrote:
> How do I remove hyperlinks from reports that are exported to Excel?sql
allow me to filter the report when a user click a cell. I had no idea this
would totally ruine the report when exporting to Excel (each cell is now a
live hyperlink to the live report - FROM EXCEL!!) Te sheet has grown from a
couple hundred K to 22 MB!!! How can I have these textboxes revert to just
plain text when exported to excel. This is an emergency!|||Essentially, you'll write an expression for:
Navigation:
=IIF (Parameters!render_format.value <> "html", nothing, "[report name]")
Text Decoration (to underline if a hyperlink):
=IIF(Parameters!render_format..Value = "HTML4.0","Underline","None")
Color:
=IIF(Parameters!render_format..Value = "HTML4.0","Blue","Black")
Scott
"Estella Kang" wrote:
> How do I remove hyperlinks from reports that are exported to Excel?sql
Remove hex from database
I have someone who altered all of the data in our database [22 million lines
]
that added in a line feed and carriage return in one of the fields. It needs
to
be removed. How do I remove hex values OD OA from a database field?Try something similar to:
declare @.crlf varchar(2)
set @.crlf = char(13) + char(10)
update mytable
set myfield = replace(myfield, @.crlf, '')
TEST ON A DEVELOPMENT DATABASE FIRST !!!!!!
Mike
"jeff fisher" <jeff@.fisher.com> wrote in message
news:OvUhzMCTFHA.2096@.TK2MSFTNGP14.phx.gbl...
> I have someone who altered all of the data in our database [22 million
lines]
> that added in a line feed and carriage return in one of the fields. It
needs to
> be removed. How do I remove hex values OD OA from a database field?
]
that added in a line feed and carriage return in one of the fields. It needs
to
be removed. How do I remove hex values OD OA from a database field?Try something similar to:
declare @.crlf varchar(2)
set @.crlf = char(13) + char(10)
update mytable
set myfield = replace(myfield, @.crlf, '')
TEST ON A DEVELOPMENT DATABASE FIRST !!!!!!
Mike
"jeff fisher" <jeff@.fisher.com> wrote in message
news:OvUhzMCTFHA.2096@.TK2MSFTNGP14.phx.gbl...
> I have someone who altered all of the data in our database [22 million
lines]
> that added in a line feed and carriage return in one of the fields. It
needs to
> be removed. How do I remove hex values OD OA from a database field?
Remove Help icon from the Toolbar
Hi,
I am trying to hide the help icon from the Toolbar, and i used a stylesheet to control it.
.ToolbarHelp
{
display: none;
}
The strange thing is that when i call the report in my asp.net page directly in the server machine, the help icon is removed, but when i view it in another machine, it doesn;t seem to work.
Any idea what went wrong?
Did you verify the path to the style sheet?
I am trying to hide the help icon from the Toolbar, and i used a stylesheet to control it.
.ToolbarHelp
{
display: none;
}
The strange thing is that when i call the report in my asp.net page directly in the server machine, the help icon is removed, but when i view it in another machine, it doesn;t seem to work.
Any idea what went wrong?
Did you verify the path to the style sheet?
Remove headers in SQL Query analyzer when making a query
Hi Friends...
I need to know how can i remove the colum headers when making a query with
sql query analizer, the query is:
select succeeded from sysdbmaintplan_history
i need that the field header 'succeeded' dissapear!!!
thanks alluse empty space as a column name
select succeeded as ' ' from sysdbmaintplan_history
--
Programmer
"Tinchos" wrote:
> Hi Friends...
> I need to know how can i remove the colum headers when making a query with
> sql query analizer, the query is:
> select succeeded from sysdbmaintplan_history
> i need that the field header 'succeeded' dissapear!!!
> thanks all|||Click on Tools/Options menu. Goto results tab. You will see the option for
column headers - just uncheck it.
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:0FD3406C-E8B0-4CE6-A9DE-9E7109213380@.microsoft.com...
> Hi Friends...
> I need to know how can i remove the colum headers when making a query with
> sql query analizer, the query is:
> select succeeded from sysdbmaintplan_history
> i need that the field header 'succeeded' dissapear!!!
> thanks all
I need to know how can i remove the colum headers when making a query with
sql query analizer, the query is:
select succeeded from sysdbmaintplan_history
i need that the field header 'succeeded' dissapear!!!
thanks alluse empty space as a column name
select succeeded as ' ' from sysdbmaintplan_history
--
Programmer
"Tinchos" wrote:
> Hi Friends...
> I need to know how can i remove the colum headers when making a query with
> sql query analizer, the query is:
> select succeeded from sysdbmaintplan_history
> i need that the field header 'succeeded' dissapear!!!
> thanks all|||Click on Tools/Options menu. Goto results tab. You will see the option for
column headers - just uncheck it.
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:0FD3406C-E8B0-4CE6-A9DE-9E7109213380@.microsoft.com...
> Hi Friends...
> I need to know how can i remove the colum headers when making a query with
> sql query analizer, the query is:
> select succeeded from sysdbmaintplan_history
> i need that the field header 'succeeded' dissapear!!!
> thanks all
Remove header using URL access
I have a report that has parameters. The report is called from a webpage using URL access. I know how to remove the toolbar or parameters with "&rc:Toolbar=false or &rc
Fred
For this type of customization of report manager, you will probably need to create your own application in Visual Studio 2005 and use a report viewer.
Report manager is not very customizable.
|||How difficult would it be to write a program to display the parameters like Reporting Services does and to use the selected parameter to refresh the report like Reporting Services does? Any samples?
Fred
|||You can do this by linking to the report via the reportserver virtual directory rather than the report manager.
Try navigating to http://<your_server>/reportserver and then click through to your report. You will see that the report parameters and toolbar are still visible. Copy the URL and get your application to link to that instead.
|||Adam,
Yes, this displays the parameters and toolbar, but it also displays the header information which is above the parameter section. I do not want my endusers to see this information ( as mentioned in the first post in this series). This information allows the user to navigate Report Manager which will confuse them. I only want them to see the report with the parameters and toolbar.
Fred
|||Fred,
Can I suggest that you actually try the solution or read the post properly before commenting!
My suggestetion is based on a live system and I assure you it delivers exactly what you have desribed.
When I navigate to my report using the following URL https://servername/reportserver?%2fReports%2fReport+1&rs:Command=Render
I see this:
If I add &rc: parameters=collapsed to the URL I get this:
I popup my window from a piece of javascript where I use the following code:
Code Snippet
function openReport(name, parameters)
{
if(typeof(parameters) == "undefined") parameters = "";
var encodedName = name;
while(encodedName.indexOf(" ") != -1)
{
encodedName = encodedName.replace(" ", "+");
}
encodedName = escape(encodedName);
var URL = "/reportserver?%2fReports%2f"
+ encodedName
+ "&rs:Command=Render&rc:parameters=collapsed"
+ parameters;
var features = "resizable=yes"
+ ",location=no"
+ ",menubar=no"
+ ",status=no"
+ ",toolbar=no"
+ ",width=" + screen.width
+ ",height=" + screen.height
+ ",top=0"
+ ",left=0";
window.open(URL, "", features).focus();
return true;
}
|||Adam,
I apologize. I did not read your post correctly. I was using Report Manager and navigated to my report from the home page. Once I used the ReportServer directory I saw that it was exactly what I wanted.
Thank you,
Fred
Remove header using URL access
I have a report that has parameters. The report is called from a webpage using URL access. I know how to remove the toolbar or parameters with "&rc:Toolbar=false or &rc
Fred
For this type of customization of report manager, you will probably need to create your own application in Visual Studio 2005 and use a report viewer.
Report manager is not very customizable.
|||How difficult would it be to write a program to display the parameters like Reporting Services does and to use the selected parameter to refresh the report like Reporting Services does? Any samples?
Fred
|||You can do this by linking to the report via the reportserver virtual directory rather than the report manager.
Try navigating to http://<your_server>/reportserver and then click through to your report. You will see that the report parameters and toolbar are still visible. Copy the URL and get your application to link to that instead.
|||Adam,
Yes, this displays the parameters and toolbar, but it also displays the header information which is above the parameter section. I do not want my endusers to see this information ( as mentioned in the first post in this series). This information allows the user to navigate Report Manager which will confuse them. I only want them to see the report with the parameters and toolbar.
Fred
|||Fred,
Can I suggest that you actually try the solution or read the post properly before commenting!
My suggestetion is based on a live system and I assure you it delivers exactly what you have desribed.
When I navigate to my report using the following URL https://servername/reportserver?%2fReports%2fReport+1&rs:Command=Render
I see this:
If I add &rc: parameters=collapsed to the URL I get this:
I popup my window from a piece of javascript where I use the following code:
Code Snippet
function openReport(name, parameters)
{
if(typeof(parameters) == "undefined") parameters = "";
var encodedName = name;
while(encodedName.indexOf(" ") != -1)
{
encodedName = encodedName.replace(" ", "+");
}
encodedName = escape(encodedName);
var URL = "/reportserver?%2fReports%2f"
+ encodedName
+ "&rs:Command=Render&rc:parameters=collapsed"
+ parameters;
var features = "resizable=yes"
+ ",location=no"
+ ",menubar=no"
+ ",status=no"
+ ",toolbar=no"
+ ",width=" + screen.width
+ ",height=" + screen.height
+ ",top=0"
+ ",left=0";
window.open(URL, "", features).focus();
return true;
}
|||Adam,
I apologize. I did not read your post correctly. I was using Report Manager and navigated to my report from the home page. Once I used the ReportServer directory I saw that it was exactly what I wanted.
Thank you,
Fred
sqlremove guest user in msdb
In according of what I have found on various document I removed to user 'guest' from the msdb database. But now the SQL_Server_Agent don't start.
I run this service from a normal user NT account. In the log error of SQL_Server_agent i found this items:
1) [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
2) [298] SQLServer Error: 4060, Impossibile aprire il database richiesto nell'account di accesso 'msdb'. L'accesso avrX esito negativo. [SQLSTATE 42000]
The only solution I have found is to add the NT account to the sysadmin role but I don't want to do this!
Anyone can help me ?
I run SQL 2K SP3 on WIN2K Server SP4
Thanks
The login that SQL Agent uses MUST be a SQL Server Admin account or the
agent will not start.
So first do that.
Then if it still does not start, add the login as a valid user to the MSDB
database, then see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Pasqualini" <Roberto Pasqualini@.discussions.microsoft.com> wrote in
message news:7206619A-0EB0-480C-84C0-9E99C2BF4751@.microsoft.com...
> In according of what I have found on various document I removed to user
'guest' from the msdb database. But now the SQL_Server_Agent don't start.
> I run this service from a normal user NT account. In the log error of
SQL_Server_agent i found this items:
> 1) [000] Unable to connect to server '(local)'; SQLServerAgent cannot
start
> 2) [298] SQLServer Error: 4060, Impossibile aprire il database richiesto
nell'account di accesso 'msdb'. L'accesso avr esito negativo. [SQLSTATE
42000]
> The only solution I have found is to add the NT account to the sysadmin
role but I don't want to do this!
> Anyone can help me ?
> I run SQL 2K SP3 on WIN2K Server SP4
> Thanks
|||Many thanks for your promplty response.
I haven't understand this.
So I have another question: in your your opinion while is the best security choice ?
1) Start the SQL_SERVER_AGENT with an NT administrator account, that have sysamind role
2) Assing the sysadmin role to an NT normal user account and use this to start the SQL_SERVER_AGENT.
The Microsoft security baseline analizer suggest that no have more than 2 sysadmin user.
Thanks
"Wayne Snyder" wrote:
> The login that SQL Agent uses MUST be a SQL Server Admin account or the
> agent will not start.
> So first do that.
> Then if it still does not start, add the login as a valid user to the MSDB
> database, then see.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Roberto Pasqualini" <Roberto Pasqualini@.discussions.microsoft.com> wrote in
> message news:7206619A-0EB0-480C-84C0-9E99C2BF4751@.microsoft.com...
> 'guest' from the msdb database. But now the SQL_Server_Agent don't start.
> SQL_Server_agent i found this items:
> start
> nell'account di accesso 'msdb'. L'accesso avrX esito negativo. [SQLSTATE
> 42000]
> role but I don't want to do this!
>
>
I run this service from a normal user NT account. In the log error of SQL_Server_agent i found this items:
1) [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
2) [298] SQLServer Error: 4060, Impossibile aprire il database richiesto nell'account di accesso 'msdb'. L'accesso avrX esito negativo. [SQLSTATE 42000]
The only solution I have found is to add the NT account to the sysadmin role but I don't want to do this!
Anyone can help me ?
I run SQL 2K SP3 on WIN2K Server SP4
Thanks
The login that SQL Agent uses MUST be a SQL Server Admin account or the
agent will not start.
So first do that.
Then if it still does not start, add the login as a valid user to the MSDB
database, then see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Pasqualini" <Roberto Pasqualini@.discussions.microsoft.com> wrote in
message news:7206619A-0EB0-480C-84C0-9E99C2BF4751@.microsoft.com...
> In according of what I have found on various document I removed to user
'guest' from the msdb database. But now the SQL_Server_Agent don't start.
> I run this service from a normal user NT account. In the log error of
SQL_Server_agent i found this items:
> 1) [000] Unable to connect to server '(local)'; SQLServerAgent cannot
start
> 2) [298] SQLServer Error: 4060, Impossibile aprire il database richiesto
nell'account di accesso 'msdb'. L'accesso avr esito negativo. [SQLSTATE
42000]
> The only solution I have found is to add the NT account to the sysadmin
role but I don't want to do this!
> Anyone can help me ?
> I run SQL 2K SP3 on WIN2K Server SP4
> Thanks
|||Many thanks for your promplty response.
I haven't understand this.
So I have another question: in your your opinion while is the best security choice ?
1) Start the SQL_SERVER_AGENT with an NT administrator account, that have sysamind role
2) Assing the sysadmin role to an NT normal user account and use this to start the SQL_SERVER_AGENT.
The Microsoft security baseline analizer suggest that no have more than 2 sysadmin user.
Thanks
"Wayne Snyder" wrote:
> The login that SQL Agent uses MUST be a SQL Server Admin account or the
> agent will not start.
> So first do that.
> Then if it still does not start, add the login as a valid user to the MSDB
> database, then see.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Roberto Pasqualini" <Roberto Pasqualini@.discussions.microsoft.com> wrote in
> message news:7206619A-0EB0-480C-84C0-9E99C2BF4751@.microsoft.com...
> 'guest' from the msdb database. But now the SQL_Server_Agent don't start.
> SQL_Server_agent i found this items:
> start
> nell'account di accesso 'msdb'. L'accesso avrX esito negativo. [SQLSTATE
> 42000]
> role but I don't want to do this!
>
>
remove guest user in msdb
In according of what I have found on various document I removed to user 'gue
st' from the msdb database. But now the SQL_Server_Agent don't start.
I run this service from a normal user NT account. In the log error of SQL_Se
rver_agent i found this items:
1) [000] Unable to connect to server '(local)'; SQLServerAgent cannot st
art
2) [298] SQLServer Error: 4060, Impossibile aprire il database richiesto
nell'account di accesso 'msdb'. L'accesso avr_ esito negativo. [SQLSTA
TE 42000]
The only solution I have found is to add the NT account to the sysadmin role
but I don't want to do this!
Anyone can help me ?
I run SQL 2K SP3 on WIN2K Server SP4
ThanksThe login that SQL Agent uses MUST be a SQL Server Admin account or the
agent will not start.
So first do that.
Then if it still does not start, add the login as a valid user to the MSDB
database, then see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Pasqualini" <Roberto Pasqualini@.discussions.microsoft.com> wrote in
message news:7206619A-0EB0-480C-84C0-9E99C2BF4751@.microsoft.com...
> In according of what I have found on various document I removed to user
'guest' from the msdb database. But now the SQL_Server_Agent don't start.
> I run this service from a normal user NT account. In the log error of
SQL_Server_agent i found this items:
> 1) [000] Unable to connect to server '(local)'; SQLServerAgent cannot
start
> 2) [298] SQLServer Error: 4060, Impossibile aprire il database richiesto[/vbco
l]
nell'account di accesso 'msdb'. L'accesso avr esito negativo. [SQLSTATE
42000][vbcol=seagreen]
> The only solution I have found is to add the NT account to the sysadmin
role but I don't want to do this!
> Anyone can help me ?
> I run SQL 2K SP3 on WIN2K Server SP4
> Thanks|||Many thanks for your promplty response.
I haven't understand this.
So I have another question: in your your opinion while is the best security
choice ?
1) Start the SQL_SERVER_AGENT with an NT administrator account, that have sy
samind role
2) Assing the sysadmin role to an NT normal user account and use this to sta
rt the SQL_SERVER_AGENT.
The Microsoft security baseline analizer suggest that no have more than 2 sy
sadmin user.
Thanks
"Wayne Snyder" wrote:
> The login that SQL Agent uses MUST be a SQL Server Admin account or the
> agent will not start.
> So first do that.
> Then if it still does not start, add the login as a valid user to the MSDB
> database, then see.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Roberto Pasqualini" <Roberto Pasqualini@.discussions.microsoft.com> wrote
in
> message news:7206619A-0EB0-480C-84C0-9E99C2BF4751@.microsoft.com...
> 'guest' from the msdb database. But now the SQL_Server_Agent don't start.
> SQL_Server_agent i found this items:
> start
> nell'account di accesso 'msdb'. L'accesso avr_ esito negativo. [SQLST
ATE
> 42000]
> role but I don't want to do this!
>
>
st' from the msdb database. But now the SQL_Server_Agent don't start.
I run this service from a normal user NT account. In the log error of SQL_Se
rver_agent i found this items:
1) [000] Unable to connect to server '(local)'; SQLServerAgent cannot st
art
2) [298] SQLServer Error: 4060, Impossibile aprire il database richiesto
nell'account di accesso 'msdb'. L'accesso avr_ esito negativo. [SQLSTA
TE 42000]
The only solution I have found is to add the NT account to the sysadmin role
but I don't want to do this!
Anyone can help me ?
I run SQL 2K SP3 on WIN2K Server SP4
ThanksThe login that SQL Agent uses MUST be a SQL Server Admin account or the
agent will not start.
So first do that.
Then if it still does not start, add the login as a valid user to the MSDB
database, then see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Roberto Pasqualini" <Roberto Pasqualini@.discussions.microsoft.com> wrote in
message news:7206619A-0EB0-480C-84C0-9E99C2BF4751@.microsoft.com...
> In according of what I have found on various document I removed to user
'guest' from the msdb database. But now the SQL_Server_Agent don't start.
> I run this service from a normal user NT account. In the log error of
SQL_Server_agent i found this items:
> 1) [000] Unable to connect to server '(local)'; SQLServerAgent cannot
start
> 2) [298] SQLServer Error: 4060, Impossibile aprire il database richiesto[/vbco
l]
nell'account di accesso 'msdb'. L'accesso avr esito negativo. [SQLSTATE
42000][vbcol=seagreen]
> The only solution I have found is to add the NT account to the sysadmin
role but I don't want to do this!
> Anyone can help me ?
> I run SQL 2K SP3 on WIN2K Server SP4
> Thanks|||Many thanks for your promplty response.
I haven't understand this.
So I have another question: in your your opinion while is the best security
choice ?
1) Start the SQL_SERVER_AGENT with an NT administrator account, that have sy
samind role
2) Assing the sysadmin role to an NT normal user account and use this to sta
rt the SQL_SERVER_AGENT.
The Microsoft security baseline analizer suggest that no have more than 2 sy
sadmin user.
Thanks
"Wayne Snyder" wrote:
> The login that SQL Agent uses MUST be a SQL Server Admin account or the
> agent will not start.
> So first do that.
> Then if it still does not start, add the login as a valid user to the MSDB
> database, then see.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Roberto Pasqualini" <Roberto Pasqualini@.discussions.microsoft.com> wrote
in
> message news:7206619A-0EB0-480C-84C0-9E99C2BF4751@.microsoft.com...
> 'guest' from the msdb database. But now the SQL_Server_Agent don't start.
> SQL_Server_agent i found this items:
> start
> nell'account di accesso 'msdb'. L'accesso avr_ esito negativo. [SQLST
ATE
> 42000]
> role but I don't want to do this!
>
>
Remove Grand Total in Pivot Table issue using asp.net
Hi,
I am trying to remove the grad total amount from the pivot table list with asp.net,programing. The data I called from cube, I have rows,columns and data axis values, when I place my measure in to data axis it autometically created sub total and grand totals, I want remove those. I could not found any property or method to remove that.
Please help me on this.
Hi Please help, I still not got any answer.
Remove from noise list
how I can remove "c" from my noise word and how I rebuilt my catalog
to search on c#, c+, and c.
For SQL 2000 go to c:\Program Files\Microsoft SQL Server\MSSQL\Ftdata or
c:\Program Files\Microsoft SQL Server\MSSQL$InstanceName\Ftdata
For SQL 2005 go to
c:\Program Files\Microsoft SQL Server\MSSQL.X\MSSQL\Ftdata
Where X is your instance ID.
Edit the noise word list for your language, stop MSSearch (MSFTESQL in SQL
2005) save your changes and restart MSSearch or MSFTESQL.
Now you will be able to search on C#, C++ correctly, but no c++, or c#. It
must be uppercase in your content and queries to work.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Cenk FIRAT" <CenkFIRAT@.discussions.microsoft.com> wrote in message
news:4357CA5D-6A74-4161-B3E4-A007268B6DDA@.microsoft.com...
> how I can remove "c" from my noise word and how I rebuilt my catalog
> to search on c#, c+, and c.
>
to search on c#, c+, and c.
For SQL 2000 go to c:\Program Files\Microsoft SQL Server\MSSQL\Ftdata or
c:\Program Files\Microsoft SQL Server\MSSQL$InstanceName\Ftdata
For SQL 2005 go to
c:\Program Files\Microsoft SQL Server\MSSQL.X\MSSQL\Ftdata
Where X is your instance ID.
Edit the noise word list for your language, stop MSSearch (MSFTESQL in SQL
2005) save your changes and restart MSSearch or MSFTESQL.
Now you will be able to search on C#, C++ correctly, but no c++, or c#. It
must be uppercase in your content and queries to work.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Cenk FIRAT" <CenkFIRAT@.discussions.microsoft.com> wrote in message
news:4357CA5D-6A74-4161-B3E4-A007268B6DDA@.microsoft.com...
> how I can remove "c" from my noise word and how I rebuilt my catalog
> to search on c#, c+, and c.
>
Remove from Cache
I want to remove all the procedures from the cache... I used DBCC
FREEPROCCACHE but doesn't effect.
Any idea?
Thanks in advance.
Joh wrote:
> I want to remove all the procedures from the cache... I used DBCC
> FREEPROCCACHE but doesn't effect.
> Any idea?
> Thanks in advance.
How did you determine that the statement is not working as expected?
Maybe you are looking for "DBCC DROPCLEANBUFFERS" to clear data from the
cache.
David Gugick
Imceda Software
www.imceda.com
|||I ran stored procedure first time which took around 2 mins to retrieve the
data and at the second time it tooks 1 min and third time it took 30
seconds... when I used DBCC DROPCLEANBUFFERS command the stored procedure
took same 30 seconds why not 2 mins if it remove from the buffer?
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#1uJWbvWFHA.3540@.TK2MSFTNGP15.phx.gbl...
> Joh wrote:
> How did you determine that the statement is not working as expected?
> Maybe you are looking for "DBCC DROPCLEANBUFFERS" to clear data from the
> cache.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Joh wrote:
> I ran stored procedure first time which took around 2 mins to
> retrieve the data and at the second time it tooks 1 min and third
> time it took 30 seconds... when I used DBCC DROPCLEANBUFFERS command
> the stored procedure took same 30 seconds why not 2 mins if it remove
> from the buffer?
>
Maybe there's a cache outside SQL Server that's at play or maybe there
was another process keeping the disks/CPUs busy the first execution. In
any case, you're much better off using Profiler to see the performance
of a query. The duration is important, but CPU is more telling as high
CPU always translates into longer durations. Also have a look at your
execution plans and make sure the queries are full-optimized.
David Gugick
Imceda Software
www.imceda.com
|||Yes but my question is that at the first time when procedure take 2 mins
then how come it take 1 min in the next execution .... and when I restart
my system then again it take the same process ... I want that when I execute
that procedure so it should take 2 mins. in every execution ... that 's why
I used DBCC DROPCLEANBUFFERS...
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uwMRjGzWFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Joh wrote:
> Maybe there's a cache outside SQL Server that's at play or maybe there
> was another process keeping the disks/CPUs busy the first execution. In
> any case, you're much better off using Profiler to see the performance
> of a query. The duration is important, but CPU is more telling as high
> CPU always translates into longer durations. Also have a look at your
> execution plans and make sure the queries are full-optimized.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
sql
FREEPROCCACHE but doesn't effect.
Any idea?
Thanks in advance.
Joh wrote:
> I want to remove all the procedures from the cache... I used DBCC
> FREEPROCCACHE but doesn't effect.
> Any idea?
> Thanks in advance.
How did you determine that the statement is not working as expected?
Maybe you are looking for "DBCC DROPCLEANBUFFERS" to clear data from the
cache.
David Gugick
Imceda Software
www.imceda.com
|||I ran stored procedure first time which took around 2 mins to retrieve the
data and at the second time it tooks 1 min and third time it took 30
seconds... when I used DBCC DROPCLEANBUFFERS command the stored procedure
took same 30 seconds why not 2 mins if it remove from the buffer?
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#1uJWbvWFHA.3540@.TK2MSFTNGP15.phx.gbl...
> Joh wrote:
> How did you determine that the statement is not working as expected?
> Maybe you are looking for "DBCC DROPCLEANBUFFERS" to clear data from the
> cache.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Joh wrote:
> I ran stored procedure first time which took around 2 mins to
> retrieve the data and at the second time it tooks 1 min and third
> time it took 30 seconds... when I used DBCC DROPCLEANBUFFERS command
> the stored procedure took same 30 seconds why not 2 mins if it remove
> from the buffer?
>
Maybe there's a cache outside SQL Server that's at play or maybe there
was another process keeping the disks/CPUs busy the first execution. In
any case, you're much better off using Profiler to see the performance
of a query. The duration is important, but CPU is more telling as high
CPU always translates into longer durations. Also have a look at your
execution plans and make sure the queries are full-optimized.
David Gugick
Imceda Software
www.imceda.com
|||Yes but my question is that at the first time when procedure take 2 mins
then how come it take 1 min in the next execution .... and when I restart
my system then again it take the same process ... I want that when I execute
that procedure so it should take 2 mins. in every execution ... that 's why
I used DBCC DROPCLEANBUFFERS...
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uwMRjGzWFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Joh wrote:
> Maybe there's a cache outside SQL Server that's at play or maybe there
> was another process keeping the disks/CPUs busy the first execution. In
> any case, you're much better off using Profiler to see the performance
> of a query. The duration is important, but CPU is more telling as high
> CPU always translates into longer durations. Also have a look at your
> execution plans and make sure the queries are full-optimized.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
sql
Remove foreign key
Hi
Can anyone tell me how I can remove a foreign key in a SQL database?
Thanks in advanceDo you mean from the structure of the table, or from one of the data rows? Or remove the relationship entirely?
Don|||remove the relationship entirely.|||Two basic ways. If you have it, open SQL Server's Enterprise Manager and open or create a database diagram. Delete the relationship there.
With T-SQL, use the ALTER TABLE statement to drop the foreign key constraint, something like
ALTER TABLE tableName DROP CONSTRAINT foreignKeyConstraintName
You'll need the constraint name, which you can get from sp_helpconstraint.
Don|||Thanks
Remove files from databases
Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:
1- Drop the table - OK
2- Drop the filegroup - OK
3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.
When I query a catalog view with this query:
Select*FromSys.Database_Files
I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.
This is the code I use:
Use [TESTING ]
Go
-- Add a FileGroup to the Database
AlterDatabase TESTING AddFileGroup FG01
Go
-- Add a file to a FileGroup
AlterDatabase TESTING AddFile(
NAME= TESTING_DATA01,
FILENAME='D:\Sql Server\Data\Testing_Data01.ndf',
SIZE= 1 MB,
MAXSIZE= 10 MB,
FILEGROWTH= 1 MB
)ToFileGroup FG01
Go
-- Create the table using a specific FileGroup
CreateTable TABLE1 (
Id IntNotNull,
FirstName VarChar(30)NotNull,
LastName VarChar(30)NotNull,
BirthDate SmallDateTime
)
On FG01
So far everything works ok, but in the next code there is an error:
-- Delete the table
Drop Table TABLE1
Go
-- Remove a FileGroup from the Database
AlterDatabase TESTING Remove FileGroup FG01
Go
-- Try to remove the file
AlterDatabase TESTING Remove File TESTING_DATA01
When I try to remove the file there is an error:
"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"
Remove FileGroup
I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty
any idea...
Thanks
Ranga
Hi
I've encountered the same issue.
Hi guys, is there a solution for this problem.
Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1
thanks,
Larry
Hi Ranga,
Maybe a partition scheme is still mapped to the filegroups.
Try dropping the partition scheme before removing the filegroups
|||We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.
Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?
Thanks.
Remove FileGroup
I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty
any idea...
Thanks
Ranga
Hi
I've encountered the same issue.
Hi guys, is there a solution for this problem.
Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1
thanks,
Larry
Hi Ranga,
Maybe a partition scheme is still mapped to the filegroups.
Try dropping the partition scheme before removing the filegroups
|||We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.
Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?
Thanks.
sqlRemove FileGroup
I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty
any idea...
Thanks
Ranga
Hi
I've encountered the same issue.
Hi guys, is there a solution for this problem.
Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1
thanks,
Larry
Hi Ranga,
Maybe a partition scheme is still mapped to the filegroups.
Try dropping the partition scheme before removing the filegroups
|||We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.
Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?
Thanks.
Remove Fields Programatically
Is it possible to remove fields programatically from a report file at run time?
I have too many years worth or data being returned and it exceeds the width of an A4 page - So I want to allow the users to select the years that they wish to view via a checkedlistbox - but each time I remove the Columns from the dataTable that populates the report files I get an error as noted below
I'm using VS 2005 and CR shipped with it...
<error>
The field name is not known.
Error in Filed C:\documents and settings\<insert file path here>\rpt_siz_xty {EBEF8981-654C-42C7-9DDA-3AC4184771C8}.RPT:
Error in formula <sizYr01>.
If IsNull({sp_rpt_siz_xty;1.sizYr01}) Then space(12) Else Cstr({sp_rpt_siz_xty;1.sizYr01})
The field name is not known.
</error>
I've dome a fair amount of searching and can't seem to find any remove methods for the field definitions...
Anyone?I think the field u r deleting is used in a formula in report. U need to delete that formula also.|||Why do you want to do it at runtime?
I have too many years worth or data being returned and it exceeds the width of an A4 page - So I want to allow the users to select the years that they wish to view via a checkedlistbox - but each time I remove the Columns from the dataTable that populates the report files I get an error as noted below
I'm using VS 2005 and CR shipped with it...
<error>
The field name is not known.
Error in Filed C:\documents and settings\<insert file path here>\rpt_siz_xty {EBEF8981-654C-42C7-9DDA-3AC4184771C8}.RPT:
Error in formula <sizYr01>.
If IsNull({sp_rpt_siz_xty;1.sizYr01}) Then space(12) Else Cstr({sp_rpt_siz_xty;1.sizYr01})
The field name is not known.
</error>
I've dome a fair amount of searching and can't seem to find any remove methods for the field definitions...
Anyone?I think the field u r deleting is used in a formula in report. U need to delete that formula also.|||Why do you want to do it at runtime?
remove extra characters
I have the following sql statement:
SELECT FTE_CLASS_GROUP_NBR_DSCR
FROM dbo.DLIST_FTE_CLASS_GROUP
WHERE FTE_GRP_ID IS NOT NULL
This is an example of the result returned:
9999/00 Some lenghty text is displayed after the numbers
I want to trim everything after 9999/00
Is there a way to use rtrim to remove the characters after the numbers or another method?
Thanks,
-D-Please disregard...I found the solution. Thanks.
SELECT FTE_CLASS_GROUP_NBR_DSCR
FROM dbo.DLIST_FTE_CLASS_GROUP
WHERE FTE_GRP_ID IS NOT NULL
This is an example of the result returned:
9999/00 Some lenghty text is displayed after the numbers
I want to trim everything after 9999/00
Is there a way to use rtrim to remove the characters after the numbers or another method?
Thanks,
-D-Please disregard...I found the solution. Thanks.
Remove export options
I only want to provide my report users the ability to output to PDF. I tried setting toolbar=False and adding my own rendering hyperlink to PDF on my web page, but when I set toolbar to false I do not get a horizontal scrollbar for a wide report so I had to turn toolbar=true back on.
Is there a way to not provide any Export options except PDF in the standard Export drop-down?See previous post on this question:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=adca0a75-0f75-48f1-bf52-ed68869ddfc2
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:61F4003D-5988-470A-9EA6-9DDA303DB67B@.microsoft.com...
> I only want to provide my report users the ability to output to PDF. I
tried setting toolbar=False and adding my own rendering hyperlink to PDF on
my web page, but when I set toolbar to false I do not get a horizontal
scrollbar for a wide report so I had to turn toolbar=true back on.
> Is there a way to not provide any Export options except PDF in the
standard Export drop-down?sql
Is there a way to not provide any Export options except PDF in the standard Export drop-down?See previous post on this question:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=adca0a75-0f75-48f1-bf52-ed68869ddfc2
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:61F4003D-5988-470A-9EA6-9DDA303DB67B@.microsoft.com...
> I only want to provide my report users the ability to output to PDF. I
tried setting toolbar=False and adding my own rendering hyperlink to PDF on
my web page, but when I set toolbar to false I do not get a horizontal
scrollbar for a wide report so I had to turn toolbar=true back on.
> Is there a way to not provide any Export options except PDF in the
standard Export drop-down?sql
Remove Enter from Address Field
Dear Friends,
Hi, I am facing very big problem but i think it's not big problem for expert. my problem is below,
i want remove all enter from my address filed and it type is TEXT
Thank you,
Qainan IdrisHi Qainan ,
Do you want to remove the e-mail address from the address book and type it in yourself?when do you want to do this ?whilst setting up an e-mail operator? all you need to type is the e-mail address for example , where it ask you for the operators e-mail address all then you can type e.g me@.me.com adn then test it should work , it worked for me.
I am not sure if this is what you meant , i hope this helps , if it doesnt pls reply ..
Regards
Burner|||Depending on whether you use char(13) + char(10) or just char(13) for the [Enter] -- can't you just UPDATE using the Left function and the Len(text)-2 or Len(text)-1 ?|||Use the replace function.|||Unfortunaly, function replace does not work with text field...
Try this one...
create table address(id int,address text null)
insert address
select 3,'12345678
Red Drive
Bigtown
NY
dr
45098'
It needs to fire couple times this batch to replace all 'Enters':
declare @.rt varchar(4)
set @.rt='%'+char(13)+char(10)+'%'
update address
set address=ltrim(substring(address,1,patindex(@.rt,add ress)-1)+' '+
substring(address,patindex(@.rt,address)+2,DATALENG TH(address)))|||snail: Not quite - that is why we have the cast/convert functions.
qainan: The replace function works fine but you will have to nest a cast/convert function with your text field
Hi, I am facing very big problem but i think it's not big problem for expert. my problem is below,
i want remove all enter from my address filed and it type is TEXT
Thank you,
Qainan IdrisHi Qainan ,
Do you want to remove the e-mail address from the address book and type it in yourself?when do you want to do this ?whilst setting up an e-mail operator? all you need to type is the e-mail address for example , where it ask you for the operators e-mail address all then you can type e.g me@.me.com adn then test it should work , it worked for me.
I am not sure if this is what you meant , i hope this helps , if it doesnt pls reply ..
Regards
Burner|||Depending on whether you use char(13) + char(10) or just char(13) for the [Enter] -- can't you just UPDATE using the Left function and the Len(text)-2 or Len(text)-1 ?|||Use the replace function.|||Unfortunaly, function replace does not work with text field...
Try this one...
create table address(id int,address text null)
insert address
select 3,'12345678
Red Drive
Bigtown
NY
dr
45098'
It needs to fire couple times this batch to replace all 'Enters':
declare @.rt varchar(4)
set @.rt='%'+char(13)+char(10)+'%'
update address
set address=ltrim(substring(address,1,patindex(@.rt,add ress)-1)+' '+
substring(address,patindex(@.rt,address)+2,DATALENG TH(address)))|||snail: Not quite - that is why we have the cast/convert functions.
qainan: The replace function works fine but you will have to nest a cast/convert function with your text field
Remove Duplication
Hi
Is there a way to remove duplicated records
Also if there is a way what about records that are the same except the key
field, is there a way to remove them?
Thank you in advance,
Shmuel Shulman
SBS Technologies LTDRather than removing duplicates they should be prevented. The designer shoul
d
be spanked, at least. :)
Seriously:
First of all identify duplicates (Qry#1):
select <column_list>
from <table>
group by <column_list>
having (count(*) > 1)
(Only include columns that aren't actually unique in the table).
Then decide which one's to drop (Qry#2):
select <unique_column>
from <table>
inner join (
..put Qry#1 here
) Duplicates
on Duplicates.<Col1> = <table>.<Col1>
and ... <-- put the rest of the columns
as additional conditions
Then find the keys in all foreign tables and decide whether to drop foreign
rows or update them to reference the keys of the rows you intend to keep.
I'd suggest you post DDL adn sample data for a more accurate solution.
ML
http://milambda.blogspot.com/|||Believe it or not, but "adn" is another way to spell "and". It'll be a big
hit in 2006. ;)
ML
http://milambda.blogspot.com/
"ML" wrote:
> Rather than removing duplicates they should be prevented. The designer sho
uld
> be spanked, at least. :)
> Seriously:
> First of all identify duplicates (Qry#1):
> select <column_list>
> from <table>
> group by <column_list>
> having (count(*) > 1)
> (Only include columns that aren't actually unique in the table).
> Then decide which one's to drop (Qry#2):
> select <unique_column>
> from <table>
> inner join (
> ...put Qry#1 here
> ) Duplicates
> on Duplicates.<Col1> = <table>.<Col1>
> and ... <-- put the rest of the columns
> as additional conditions
> Then find the keys in all foreign tables and decide whether to drop foreig
n
> rows or update them to reference the keys of the rows you intend to keep.
> I'd suggest you post DDL adn sample data for a more accurate solution.
>
> ML
> --
> http://milambda.blogspot.com/
>|||>> Is there a way to remove duplicated records [sic] <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
The right way to always declare tables with a key. In fact, if there
is no key, then by definition that thing is NOT a table at all. Fire
the moron who did not provide a key.
If this is the result of the aforesaid moron using IDENTITY in a table,
then run over him in the parking lot so that the quality of databases
is improved everywhere.
DELETE FROM StinkingNonTable
WHERE <pseudo_key_column>
IN (SELECT MAX (<pseudo_key_column> ),
<non-key column list>
FROM StinkingNonTable
GROUP BY <non-key column list>
HAVING COUNT(*) > 1);
Run this a few times until zero rows are dropped, then fix the table
and hunt down the moron.
Is there a way to remove duplicated records
Also if there is a way what about records that are the same except the key
field, is there a way to remove them?
Thank you in advance,
Shmuel Shulman
SBS Technologies LTDRather than removing duplicates they should be prevented. The designer shoul
d
be spanked, at least. :)
Seriously:
First of all identify duplicates (Qry#1):
select <column_list>
from <table>
group by <column_list>
having (count(*) > 1)
(Only include columns that aren't actually unique in the table).
Then decide which one's to drop (Qry#2):
select <unique_column>
from <table>
inner join (
..put Qry#1 here
) Duplicates
on Duplicates.<Col1> = <table>.<Col1>
and ... <-- put the rest of the columns
as additional conditions
Then find the keys in all foreign tables and decide whether to drop foreign
rows or update them to reference the keys of the rows you intend to keep.
I'd suggest you post DDL adn sample data for a more accurate solution.
ML
http://milambda.blogspot.com/|||Believe it or not, but "adn" is another way to spell "and". It'll be a big
hit in 2006. ;)
ML
http://milambda.blogspot.com/
"ML" wrote:
> Rather than removing duplicates they should be prevented. The designer sho
uld
> be spanked, at least. :)
> Seriously:
> First of all identify duplicates (Qry#1):
> select <column_list>
> from <table>
> group by <column_list>
> having (count(*) > 1)
> (Only include columns that aren't actually unique in the table).
> Then decide which one's to drop (Qry#2):
> select <unique_column>
> from <table>
> inner join (
> ...put Qry#1 here
> ) Duplicates
> on Duplicates.<Col1> = <table>.<Col1>
> and ... <-- put the rest of the columns
> as additional conditions
> Then find the keys in all foreign tables and decide whether to drop foreig
n
> rows or update them to reference the keys of the rows you intend to keep.
> I'd suggest you post DDL adn sample data for a more accurate solution.
>
> ML
> --
> http://milambda.blogspot.com/
>|||>> Is there a way to remove duplicated records [sic] <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
The right way to always declare tables with a key. In fact, if there
is no key, then by definition that thing is NOT a table at all. Fire
the moron who did not provide a key.
If this is the result of the aforesaid moron using IDENTITY in a table,
then run over him in the parking lot so that the quality of databases
is improved everywhere.
DELETE FROM StinkingNonTable
WHERE <pseudo_key_column>
IN (SELECT MAX (<pseudo_key_column> ),
<non-key column list>
FROM StinkingNonTable
GROUP BY <non-key column list>
HAVING COUNT(*) > 1);
Run this a few times until zero rows are dropped, then fix the table
and hunt down the moron.
remove duplicates, add PK..need help
I am currently cleaning up a "loose" database, by adding primary keys on a
few particular tables that currently have none.
The Primary key will contain 2 fields, but before I add the pk, I need to
delete any duplicates. There should be none or very few that snuck by the
application, so deleting them is not a concern.
Here is a sample of the CURRENT table format:
CREATE TABLE OrderSalesReps(
OrderID int NOT NULL,
SalesRepID int NOT NULL,
Revenue_1 decimal(14,2) NOT NULL,
Revenue_2 decimal(14,2) NOT NULL
)
So the new Primary Key will be on [OrderID and SalesRepID]
but there may be duplicates that exist currently.
What is a clean query to delete dups before I create the Primary Key.
Thanks in advance.
[Please note: OrderID in this table is a Foreign Key which links to
Orders.OrderID - doesn't matter for this case, but thought I'd mention]Hi Chris,
Which version of SQL Server are you working with?
In SQL Server 2005 the solution is pretty simple and fast:
WITH Dups AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY OrderID, SalesRepID
ORDER BY OrderID, SalesRepID) AS RowNum
FROM OrderSalesReps
)
DELETE FROM Dups WHERE RowNum > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Chris" <rooster575@.hotmail.com> wrote in message
news:utBY9xXhGHA.3904@.TK2MSFTNGP02.phx.gbl...
>I am currently cleaning up a "loose" database, by adding primary keys on a
>few particular tables that currently have none.
> The Primary key will contain 2 fields, but before I add the pk, I need to
> delete any duplicates. There should be none or very few that snuck by the
> application, so deleting them is not a concern.
> Here is a sample of the CURRENT table format:
> CREATE TABLE OrderSalesReps(
> OrderID int NOT NULL,
> SalesRepID int NOT NULL,
> Revenue_1 decimal(14,2) NOT NULL,
> Revenue_2 decimal(14,2) NOT NULL
> )
> So the new Primary Key will be on [OrderID and SalesRepID]
> but there may be duplicates that exist currently.
> What is a clean query to delete dups before I create the Primary Key.
> Thanks in advance.
> [Please note: OrderID in this table is a Foreign Key which links to
> Orders.OrderID - doesn't matter for this case, but thought I'd mention]
>
>|||Unfortunately, this solution has to work with SQL Server 2000 and 2005.
Thanks
-Chris
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:eMVD1ZYhGHA.4144@.TK2MSFTNGP02.phx.gbl...
> Hi Chris,
> Which version of SQL Server are you working with?
> In SQL Server 2005 the solution is pretty simple and fast:
> WITH Dups AS
> (
> SELECT *,
> ROW_NUMBER() OVER(PARTITION BY OrderID, SalesRepID
> ORDER BY OrderID, SalesRepID) AS RowNum
> FROM OrderSalesReps
> )
> DELETE FROM Dups WHERE RowNum > 1;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:utBY9xXhGHA.3904@.TK2MSFTNGP02.phx.gbl...
>|||First you need to decide on what your rules are for deciding which
duplicate to keep. If you're talking about exact duplicates (i.e., all
column values are identical) then you can select the unique records
into a temporary table, truncate the original table, add the primary
key, then insert the unique records back into the original table (you
could insert back then recreate the PK if you wanted as well). For
example:
SELECT DISTINCT OrderID, SalesRepID, Revenue_1, Revenue_2
INTO tmp_OrderSalesReps
TRUNCATE TABLE OrderSalesReps
ALTER TABLE OrderSalesReps
ADD CONSTRAINT PK_OrderSalesReps PRIMARY KEY CLUSTERED (OrderID,
SalesRepID)
INSERT OrderSalesReps (OrderID, SalesRepID, Revenue_1, Revenue_2)
SELECT OrderID, SalesRepID, Revenue_1, Revenue_2
FROM tmp_OrderSalesReps
--
Of course, if you end up with two rows with identical OrderIDs and
SalesRepIDs, but different Revenues then you need to decide which one
to keep. If it's really only a few rows then you could manually remove
them from your work table before inserting back to the original table.
If you wanted to use the highest revenue values found for an order/rep
(for example) then you might change your SELECT...INTO to something
like:
SELECT OrderID, SalesRepID, MAX(Revenue_1), MAX(Revenue_2)
INTO tmp_OrderSalesReps
FROM OrderSalesReps
GROUP BY OrderID, SalesRepID
HTH,
-Tom.
Chris wrote:
> I am currently cleaning up a "loose" database, by adding primary keys on a
> few particular tables that currently have none.
> The Primary key will contain 2 fields, but before I add the pk, I need to
> delete any duplicates. There should be none or very few that snuck by the
> application, so deleting them is not a concern.
> Here is a sample of the CURRENT table format:
> CREATE TABLE OrderSalesReps(
> OrderID int NOT NULL,
> SalesRepID int NOT NULL,
> Revenue_1 decimal(14,2) NOT NULL,
> Revenue_2 decimal(14,2) NOT NULL
> )
> So the new Primary Key will be on [OrderID and SalesRepID]
> but there may be duplicates that exist currently.
> What is a clean query to delete dups before I create the Primary Key.
> Thanks in advance.
> [Please note: OrderID in this table is a Foreign Key which links to
> Orders.OrderID - doesn't matter for this case, but thought I'd mention]
few particular tables that currently have none.
The Primary key will contain 2 fields, but before I add the pk, I need to
delete any duplicates. There should be none or very few that snuck by the
application, so deleting them is not a concern.
Here is a sample of the CURRENT table format:
CREATE TABLE OrderSalesReps(
OrderID int NOT NULL,
SalesRepID int NOT NULL,
Revenue_1 decimal(14,2) NOT NULL,
Revenue_2 decimal(14,2) NOT NULL
)
So the new Primary Key will be on [OrderID and SalesRepID]
but there may be duplicates that exist currently.
What is a clean query to delete dups before I create the Primary Key.
Thanks in advance.
[Please note: OrderID in this table is a Foreign Key which links to
Orders.OrderID - doesn't matter for this case, but thought I'd mention]Hi Chris,
Which version of SQL Server are you working with?
In SQL Server 2005 the solution is pretty simple and fast:
WITH Dups AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY OrderID, SalesRepID
ORDER BY OrderID, SalesRepID) AS RowNum
FROM OrderSalesReps
)
DELETE FROM Dups WHERE RowNum > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Chris" <rooster575@.hotmail.com> wrote in message
news:utBY9xXhGHA.3904@.TK2MSFTNGP02.phx.gbl...
>I am currently cleaning up a "loose" database, by adding primary keys on a
>few particular tables that currently have none.
> The Primary key will contain 2 fields, but before I add the pk, I need to
> delete any duplicates. There should be none or very few that snuck by the
> application, so deleting them is not a concern.
> Here is a sample of the CURRENT table format:
> CREATE TABLE OrderSalesReps(
> OrderID int NOT NULL,
> SalesRepID int NOT NULL,
> Revenue_1 decimal(14,2) NOT NULL,
> Revenue_2 decimal(14,2) NOT NULL
> )
> So the new Primary Key will be on [OrderID and SalesRepID]
> but there may be duplicates that exist currently.
> What is a clean query to delete dups before I create the Primary Key.
> Thanks in advance.
> [Please note: OrderID in this table is a Foreign Key which links to
> Orders.OrderID - doesn't matter for this case, but thought I'd mention]
>
>|||Unfortunately, this solution has to work with SQL Server 2000 and 2005.
Thanks
-Chris
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:eMVD1ZYhGHA.4144@.TK2MSFTNGP02.phx.gbl...
> Hi Chris,
> Which version of SQL Server are you working with?
> In SQL Server 2005 the solution is pretty simple and fast:
> WITH Dups AS
> (
> SELECT *,
> ROW_NUMBER() OVER(PARTITION BY OrderID, SalesRepID
> ORDER BY OrderID, SalesRepID) AS RowNum
> FROM OrderSalesReps
> )
> DELETE FROM Dups WHERE RowNum > 1;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:utBY9xXhGHA.3904@.TK2MSFTNGP02.phx.gbl...
>|||First you need to decide on what your rules are for deciding which
duplicate to keep. If you're talking about exact duplicates (i.e., all
column values are identical) then you can select the unique records
into a temporary table, truncate the original table, add the primary
key, then insert the unique records back into the original table (you
could insert back then recreate the PK if you wanted as well). For
example:
SELECT DISTINCT OrderID, SalesRepID, Revenue_1, Revenue_2
INTO tmp_OrderSalesReps
TRUNCATE TABLE OrderSalesReps
ALTER TABLE OrderSalesReps
ADD CONSTRAINT PK_OrderSalesReps PRIMARY KEY CLUSTERED (OrderID,
SalesRepID)
INSERT OrderSalesReps (OrderID, SalesRepID, Revenue_1, Revenue_2)
SELECT OrderID, SalesRepID, Revenue_1, Revenue_2
FROM tmp_OrderSalesReps
--
Of course, if you end up with two rows with identical OrderIDs and
SalesRepIDs, but different Revenues then you need to decide which one
to keep. If it's really only a few rows then you could manually remove
them from your work table before inserting back to the original table.
If you wanted to use the highest revenue values found for an order/rep
(for example) then you might change your SELECT...INTO to something
like:
SELECT OrderID, SalesRepID, MAX(Revenue_1), MAX(Revenue_2)
INTO tmp_OrderSalesReps
FROM OrderSalesReps
GROUP BY OrderID, SalesRepID
HTH,
-Tom.
Chris wrote:
> I am currently cleaning up a "loose" database, by adding primary keys on a
> few particular tables that currently have none.
> The Primary key will contain 2 fields, but before I add the pk, I need to
> delete any duplicates. There should be none or very few that snuck by the
> application, so deleting them is not a concern.
> Here is a sample of the CURRENT table format:
> CREATE TABLE OrderSalesReps(
> OrderID int NOT NULL,
> SalesRepID int NOT NULL,
> Revenue_1 decimal(14,2) NOT NULL,
> Revenue_2 decimal(14,2) NOT NULL
> )
> So the new Primary Key will be on [OrderID and SalesRepID]
> but there may be duplicates that exist currently.
> What is a clean query to delete dups before I create the Primary Key.
> Thanks in advance.
> [Please note: OrderID in this table is a Foreign Key which links to
> Orders.OrderID - doesn't matter for this case, but thought I'd mention]
Remove duplicates within pipeline
I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.
Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.
Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.
If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.
Thanks
Kory
Never mind... I think I found exactly what I needed: The Sort Transform.
-Kory
|||The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.
Rafael Salas
|||Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.
I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.
I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.
SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.
Any other ideas out there?
Thanks
Kory
|||Hi I'm having the same problem
does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?
|||What version of ExtraSort are you using and what platform is it running on?KoryS wrote:
I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.
Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.
Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.
If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.
Thanks
Kory
Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.
By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.
Remove duplicates within pipeline
I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.
Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.
Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.
If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.
Thanks
Kory
Never mind... I think I found exactly what I needed: The Sort Transform.
-Kory
|||The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.
Rafael Salas
|||Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.
I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.
I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.
SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.
Any other ideas out there?
Thanks
Kory
|||Hi I'm having the same problem
does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?
|||What version of ExtraSort are you using and what platform is it running on?KoryS wrote:
I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.
Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.
Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.
If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.
Thanks
Kory
Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.
By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.
Remove duplicates within pipeline
I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.
Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.
Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.
If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.
Thanks
Kory
Never mind... I think I found exactly what I needed: The Sort Transform.
-Kory
|||The only way I can think is to use the sort or aggregate transformations. Have you explore those? Notice that those are full blcoking transformation, so memory usage and performance are things you may want to check.
Rafael Salas
|||Yes, I thought the sort transform would do the trick- and it did for small files. Files with < 500,000 rows sorted immediately, within 5-10 seconds. Files > 500,000 or so just hung. Looking at task manager, the DTSDebugHost.exe kept climbing and my overall memory consumption was > 5G and I only have 3G total on the server.
I would have thought the performance was linearly decrease, and not go from 10 seconds to indefinite for just 200K rows more.
I've downloaded and installed the Extrasort component, but get an error when I try to put it on the design surface. It complains that it wasn't installed correctly. I've uninstalled and reinstalled it twice. I know NSort is another option, but I really am not needing sorting functionality, just removing duplicates.
SSIS comes with a sample solution that builds a component for removing duplicates, but as far as I can tell, the fields to pick to determine duplicates are the only fields that it passes through the pipeline. I need to remove dups based on 3 fields, but pass through the rest of the fields, like the sort component does.
Any other ideas out there?
Thanks
Kory
|||Hi I'm having the same problem
does the ssis have the capabilities of seperating the duplicate records ? or still i use the query? can you give me some advice on this ?
|||What version of ExtraSort are you using and what platform is it running on?I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.
Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.
Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.
If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.
Thanks
Kory
Had no problems running ExtraSort file version 1.0.0.3 (98,304 bytes) on a 32 bit dev platform Win XP SP2 as well as Win2k3 SP1. The SQL Server Build on both is 2153 , which "everyone" running IS should be on at this point. Have been unable to get ExtraSort to run natively on x64.
By default, the component installs to C:\Program Files\Ivolva Digital\ExtraSort Component\ExtraSort.dll.
Subscribe to:
Posts (Atom)