deleting rows based on nvarchar data

I have a table that contains rows that I would like to delete based on a field and it's contents.
What is the correct syntax to script the removal of these rows based field parameter?delete tableA where fieldB = ?

Is that what you mean?|||Kinda. I only have one table and want to delete specific rows from that table that have a specific data within a certain field.

Let be more specific. I have a table (tableA) with 10 fields. Field 3 has data that does not conform to a datetime format and I would like to remove it. The field is currently a nvarchar(50) type (2003-10-10).

I want to remove rows that contain data that is looks like this
(0020-10-10). Make sense?|||delete from table where field3 ='0020-10-10'|||Perhaps you can use the ISDATE() function, which returns 1 if a string can be converted to a valid date, and 0 if it cannot.

Try this query:

select *
from YourTable
where ISDATE([Column3]) = 0

If this returns the rows you want deleted, then change the query to a delete query:

delete
from YourTable
where ISDATE([Column3]) = 0|||right but I forgot to mention, there are all kinds of variation of that date.

I ran a script that reads as follows to help identify data within a field that does not fit a date format->

SELECT * FROM findet WHERE ISDATE(servfrom) = 0

This gave me a list of records that are not in proper date format. Now, I would like to remove them from my table. Can I use the same,

delete findet where ISDATE(servfrom)=0|||See previous post.|||That worked like a charm, thank you!!

Deleting records that get too old

I must admit I dont know all that much about SQL, which is why I hope someone can show me the light. I have a script almost finished, however I have no idea how to have it trim database entries that are older than, say, 90 days. Any ideas?WHERE date_file > DATEADD(dd,90,GETDATE())|||"Stay away from the light!" [Donkey from "Shrek"]|||"Stay away from the light!" [Donkey from "Shrek"]

Hey, I thought it was my job to handle the disconnected ramblings....

And btw, you do have a row added datetime column, right?

Of course you do.

(hey buddy, wanna buy a watch...cheap)|||Disconneted ramblings...

...Brett's job. Not yours.

...or rdjabarovs...

...<insert random Maragarita reference here>...

[sniped!]

...or connect the ramblings with a join, but not in WHERE clause.

WHERE disconnected_ramblings = ...

Not good.

Is it Friday yet?|||I'm confused.

Huh?

I think I lost my =|||Kansas?

There are only 2 things that come from Kansas boy.....

Steers...ok never mind

BUT, can you tell me the movie?

Did we scare him/her off...

And what about DB2

Damn...gotta run to THE city...

later|||Brett, someday some company will create a programming language that will allow you code in stream-of-consciousness, and you will be a happy man.|||WHERE date_file > DATEADD(dd,90,GETDATE())Wait a minute, shouldn't it be "WHERE date_file < DATEADD(dd,-90,GETDATE())"?|||Now WHERE did I put that backup...?|||Brett, someday some company will create a programming language that will allow you code in stream-of-consciousness, and you will be a happy man.

They did...it's called REXX

I love the INTERPRET keyword most of all scarecrow...

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can suggest
many thx
Assuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx
|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>
|||Hi,
Please find the script for the same:
------
--Name : s_RebuildIndices
--Author : Pallavi
--Description: Rebuilds all table indices
--Notes:
--Date: 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:

> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
>
>

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can sugges
t
many thxAssuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>|||Hi,
Please find the script for the same:
----
--
--Name : s_RebuildIndices
--Author : Pallavi
--Description : Rebuilds all table indices
--Notes :
--Date : 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:

> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
>
>

deleting multiple databases on 2000

Hi,
I need to delete approx 50 db on the same server. Instead of deleting one
at a time, is there a better way? Is there a script that someone can suggest
many thxAssuming you have only a couple of database that you want to keep, you could
create a script that uses a cursor to to select the databases
(master..sysdatabases) that you want to keep and then dynamically drop
eveything that is 'not in' your selected databases to keep.
Be warned!!! - make sure you eliminate the system databases.
If it were me, i'd just drop them manually just to be safe!
Immy
p.s. Ensure you have them all backed up! ;)
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> Hi,
> I need to delete approx 50 db on the same server. Instead of deleting one
> at a time, is there a better way? Is there a script that someone can
> suggest
> many thx|||you could also use the ms undocumented command sp_foreachDB
but would still need to check for system databases first.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> Assuming you have only a couple of database that you want to keep, you
> could create a script that uses a cursor to to select the databases
> (master..sysdatabases) that you want to keep and then dynamically drop
> eveything that is 'not in' your selected databases to keep.
> Be warned!!! - make sure you eliminate the system databases.
> If it were me, i'd just drop them manually just to be safe!
> Immy
> p.s. Ensure you have them all backed up! ;)
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
>> Hi,
>> I need to delete approx 50 db on the same server. Instead of deleting
>> one
>> at a time, is there a better way? Is there a script that someone can
>> suggest
>> many thx
>|||Hi,
Please find the script for the same:
------
--Name : s_RebuildIndices
--Author : Pallavi
--Description : Rebuilds all table indices
--Notes :
--Date : 28 March 2006
Create PROCEDURE dbo.s_DropUserdatabases
AS
SET NOCOUNT ON
-- declare all variables
DECLARE @.sTableName SYSNAME
DECLARE @.sSQL VARCHAR(50)
DECLARE @.iRowCount INT
DECLARE @.t_TableNames_Temp TABLE
(table_name SYSNAME)
INSERT @.t_TableNames_Temp
SELECT name
FROM SYSDATABASES
WHERE name not in ('master','msdb','model','tempdb')
ORDER BY name
--Getting row count from table
SELECT @.iRowCount = COUNT(*) FROM @.t_TableNames_Temp
WHILE @.iRowCount > 0
BEGIN
SELECT @.sTableName = table_name from @.t_TableNames_Temp
SELECT @.sSQL = 'DROP DATABASE '+@.sTableName
EXEC (@.sSQL)
DELETE FROM @.t_TableNames_Temp WHERE @.sTableName = table_name
SELECT @.iRowCount = @.iRowCount - 1
END
RETURN 0
SET NOCOUNT OFF
GO
"Mark Broadbent" wrote:
> you could also use the ms undocumented command sp_foreachDB
> but would still need to check for system databases first.
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%233QpAlPVHHA.192@.TK2MSFTNGP04.phx.gbl...
> > Assuming you have only a couple of database that you want to keep, you
> > could create a script that uses a cursor to to select the databases
> > (master..sysdatabases) that you want to keep and then dynamically drop
> > eveything that is 'not in' your selected databases to keep.
> >
> > Be warned!!! - make sure you eliminate the system databases.
> >
> > If it were me, i'd just drop them manually just to be safe!
> > Immy
> > p.s. Ensure you have them all backed up! ;)
> >
> >
> > "stoney" <stoney@.discussions.microsoft.com> wrote in message
> > news:B860DA2F-705A-446C-AEC0-11B16350F3DB@.microsoft.com...
> >> Hi,
> >>
> >> I need to delete approx 50 db on the same server. Instead of deleting
> >> one
> >> at a time, is there a better way? Is there a script that someone can
> >> suggest
> >>
> >> many thx
> >
> >
>
>

Deleting columns from table

in sql server 2000, I have a table that I have to write a script for to delete several columns in this table. I am finding that I have to use alter or drop keywords or a combination of the two but not sure because I have not done this before. I am googling this but finding all kinds of other information that I dont' need to know.

I dont have rights on this table so I cannot do this manually. I have to create the script and send it on to someone else.

If anyone can provide a good script example that I can use to delete unwanted columns it would be a great thing. Thanks.

You might want to check the scripts section @. SQLServerCentral.com.Here's onethat might work for you.

Deleting a UDF that does not exist...

I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
T-SQL command in a script:
GRANT EXEC on xxxyyyzzz to harry
problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
highlight this or any user on this database and try to click the Permissions
button, I get error:
--
Microsoft SQL-DMO
--
Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
UserDefinedFunctions collection. If the name is a qualified name, use [] to
separate various parts of the name, and try again.
how can I delete this "phantom" object? If I scroll through all the objects
that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
it.
Please help. Thanks.OK. Located it in the systemobjects table, but when I try to delet that row,
get an error message:
Ad hoc updates to the system catalogs are not enabled. The system
administrator must reconfigure SQL server to allow this.
Help again.
"Sam" wrote:
> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.|||Never mind - was able to delete it from the sysobjects table using:
sp_configure 'allow updates; ,1
reconfigure with overrride
all's well that ends well. no more error message
"Sam" wrote:
> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.

Deleting a column DEFAULT....

Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettaglio]
DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ALTER COLUMN [OreStraordinario] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ADD DEFAULT ('00.00') FOR [OreStraordinario]
GO

it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.

Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)Giacomo (no_spam@.grazie.it) writes:

Quote:

Originally Posted by

P.S.
We're using SQL Sever 2000 (version 8.00.194)

Before you do anything else, install SP4. What you have is the RTM version
which is five years old, and there are many bugs that have been fixed.
Not the least the hold that made the Slammer worm possible.

No, it will not address your problem at hand, and being at work now I don't
have the time to post the exact code. You will need to read the system
tables to get the name of the default and then use dynamic SQL to run
ALTER TABLE DROP CONSTRAINT. Best practice is to always name your defaults,
so that you don't run into this problem.

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

You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.

Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

EXEC sp_rename 'PlanningDettaglio.OreStraordinario', '__old', 'COLUMN'

ALTER TABLE PlanningDettaglio
ADD OreStraordinario DECIMAL(18,2) NULL -- or whatever the original
definition

UPDATE PlanningDettaglio
SET OreStraordinario = __old

ALTER TABLE PlanningDettaglio DROP COLUMN __old

COMMIT TRANSACTION

HTH,
Gert-Jan

Giacomo wrote:

Quote:

Originally Posted by

>
Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:
>
ALTER TABLE [dbo].[PlanningDettaglio]
DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
GO
>
ALTER TABLE [dbo].[PlanningDettaglio]
ALTER COLUMN [OreStraordinario] varchar(5)
GO
>
ALTER TABLE [dbo].[PlanningDettaglio]
ADD DEFAULT ('00.00') FOR [OreStraordinario]
GO
>
it works, but only locally.
I've tried with:
>
ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT
>
Err.: Incorrect syntax near the keyword 'DEFAULT'.
>
Can someone help me please?
>
Thanks in advance,
Giacomo
>
P.S.
We're using SQL Sever 2000 (version 8.00.194)

|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

Giacomo (no_spam@.grazie.it) writes:

Quote:

Originally Posted by

>P.S.
>We're using SQL Sever 2000 (version 8.00.194)

>
Before you do anything else, install SP4. What you have is the RTM version
which is five years old, and there are many bugs that have been fixed.
Not the least the hold that made the Slammer worm possible.
>
No, it will not address your problem at hand, and being at work now I
don't have the time to post the exact code. You will need to read the
system tables to get the name of the default and then use dynamic SQL to
run ALTER TABLE DROP CONSTRAINT. Best practice is to always name your
defaults, so that you don't run into this problem.

So this is how you do it:

DECLARE @.default sysname
SELECT @.default = object_name(cdefault)
FROM syscolumns
WHERE id = object_id('yourtable')
AND name = 'yourcolumn'
EXEC ('ALTER TABLE yourtable DROP CONSTRAINT ' + @.default)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you very much,
it works perfectly! :)

Giacomo|||Actually I m also facing the same problem. I had need of fetching data
from two tables for which we used Diagram for inner joining. Later I
found that in VB.Net when we update or save any new data in one of
those tables which were inner joined it was showing error, so we
deleted that diagram and updated the data without joining.
Now when we try to delete some data from one of those tables it shows
message Delete statement conflicted with COLUMN REFERENCE constraint
<constraint name >The conflict occured in database <databasename>,table
tablename,column <columnname>

Kindly give solution of this problem.
sonika.

Gert-Jan Strik wrote:

Quote:

Originally Posted by

Giacomo,
>
You must use ALTER TABLE ... DROP CONSTRAINT to drop the column default.
>
Another option (than the one posted by Erland) is to recreate the
column. Note that this will only work if there is no DRI on the column,
and no indexes. For example:
>
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
>
EXEC sp_rename 'PlanningDettaglio.OreStraordinario', '__old', 'COLUMN'
>
ALTER TABLE PlanningDettaglio
ADD OreStraordinario DECIMAL(18,2) NULL -- or whatever the original
definition
>
UPDATE PlanningDettaglio
SET OreStraordinario = __old
>
ALTER TABLE PlanningDettaglio DROP COLUMN __old
>
COMMIT TRANSACTION
>
HTH,
Gert-Jan
>
>
Giacomo wrote:

Quote:

Originally Posted by

Hi,
I've the following problem. I must delete a column DEFAULT from a table,
but I must do it with a script, independently from the server where it'll
be executed.
Locally I've tried with:

ALTER TABLE [dbo].[PlanningDettaglio]
DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ALTER COLUMN [OreStraordinario] varchar(5)
GO

ALTER TABLE [dbo].[PlanningDettaglio]
ADD DEFAULT ('00.00') FOR [OreStraordinario]
GO

it works, but only locally.
I've tried with:

ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULT

Err.: Incorrect syntax near the keyword 'DEFAULT'.

Can someone help me please?

Thanks in advance,
Giacomo

P.S.
We're using SQL Sever 2000 (version 8.00.194)

|||sonu (sonika4oct@.gmail.com) writes:

Quote:

Originally Posted by

Actually I m also facing the same problem. I had need of fetching data
from two tables for which we used Diagram for inner joining. Later I
found that in VB.Net when we update or save any new data in one of
those tables which were inner joined it was showing error, so we
deleted that diagram and updated the data without joining.
Now when we try to delete some data from one of those tables it shows
message Delete statement conflicted with COLUMN REFERENCE constraint
><constraint name >The conflict occured in database <databasename>,table
tablename,column <columnname>
>
Kindly give solution of this problem.

I'm sorry, but you will have to try again. To start with, your problem
appears to be completely different from what Giacomo had, so please start
a new thread.

As for your problem, you run a DELETE statement and a constraint fires.
I assume that this is a foreign key constraint. Usually foreign keys
are a good thing, as it prevents from you from doing bad things like
deleting a product which are listed on open order items. Then you talk
about diagrams, but it's impossible to understand what you are talking
about. Are you talking about database diagrams in an SQL tool, or is one
of your tables called Diagrams? Please post again, and make an effor to
explain your problem clearly. It helps if you includ the definition of
the involved tables, and the SQL you are having problem with.

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

Delete this post

hello all,

I have the following script that does not work in windows XP SP2

//Script

sub UpLoad (UploadFileName)

dim b1

b1=&H4000000

set objStream =CreateObject("ADODB.Stream")

set objRecord =CreateObject("ADODB.Record")

set cnn =CreateObject("ADODB.Connection")

cnn.Open "provider=MSDAIPP.DSO;data source= http://192.168.0.100"

objRecord.Open UploadFileName,cnn, 3,b1

objStream.Type = 1 'adTypeBinary

objStream.Open objRecord,3,4 /////// Error line //////

objStream.LoadFromFile "c:\"+UploadFileName

objStream.Flush

objStream.Close

objRecord.Close

Set objStream = Nothing

Set objRecord = Nothing

cnn.close

set cnn = nothing

end sub

Error Message is "No such interface supported"

Any idea whoud be appreciated highly

Would this post be served better at a different forum?|||

Hi admin,

this post is not in a good forum so please delete it for me.

thanks, mahsina

Delete rows from table

I need a script that will delete the first 100000 rows of a table. Is this possible?There are no 'first' rows because a relational database table is an
unordered set of rows. That said, below are a couple of methods that will
delete up to 100000 rows.

--primary key is MyKey
DELETE FROM MyTable
WHERE MyTable.MyKey
IN
(
SELECT TOP 100000 MyKey
FROM MyTable
ORDER BY MyKey
)

--no primary key
SET ROWCOUNT 100000
DELETE FROM MyTable
SET ROWCOUNT 0

--
Hope this helps.

Dan Guzman
SQL Server MVP

"C.P." <carmenpuccio@.yahoo.com> wrote in message
news:8b8faf60.0402061532.177b1ad0@.posting.google.c om...
> I need a script that will delete the first 100000 rows of a table. Is this
possible?|||>> I need a script that will delete the first 100000 rows of a table.
Is this possible? <<

This actually makes no sense. The basic idea of a table is that it is
an unordered set of values; there is no "first" or "next" or "last";
it is not anything like a sequential file.

Tye next basic idea is that rows are found by logic, not by counting
their **physical** position in **physical** storage.

Please read a book or take an RDSBMS course so you'll have some
foundations.|||joe.celko@.northface.edu (--CELKO--) wrote in message news:<a264e7ea.0402071301.30cd8600@.posting.google.com>...
> >> I need a script that will delete the first 100000 rows of a table.
> Is this possible? <<
> This actually makes no sense. The basic idea of a table is that it is
> an unordered set of values; there is no "first" or "next" or "last";
> it is not anything like a sequential file.
> Tye next basic idea is that rows are found by logic, not by counting
> their **physical** position in **physical** storage.
> Please read a book or take an RDSBMS course so you'll have some
> foundations.

Actually I meant to say the oldest rows and the script that was given
to me is exactly what I needed and I appreciate it. As for you, go to
h*ll and learn how to spell...

delete records in tables

Is there any built-in tool that can delete tabel
records/contents in sql server? I can run delete from
tablename script, but there are two many tables. is there
an easy way?Look at this...
Declare @.sql varchar(8000)
set @.sql=''
select @.sql=@.sql + ' Truncate Table ' + name from sysobjects where
xtype='U'
EXEC(@.SQL) -- Truncates all tables
set @.sql=''
Select @.sql=@.sql + ' DROP TABLE ' + name from sysobjects where xtype='U'
EXEC(@.SQL) -- Drops all tables
HTH
"Matt" <spam@.spam.com> escreveu na mensagem
news:108001c3a920$524706e0$a101280a@.phx.gbl...
> Is there any built-in tool that can delete tabel
> records/contents in sql server? I can run delete from
> tablename script, but there are two many tables. is there
> an easy way?
>|||If you run the following SQL it will create a DELETE script for every table
that can then be ran.
SELECT 'DELETE FROM ' + TABLE_SCHEMA+'.'+TABLE_NAME + char(10) + 'GO'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
--
HTH
Ryan Waight, MCDBA, MCSE
"Matt" <spam@.spam.com> wrote in message
news:108001c3a920$524706e0$a101280a@.phx.gbl...
> Is there any built-in tool that can delete tabel
> records/contents in sql server? I can run delete from
> tablename script, but there are two many tables. is there
> an easy way?
>|||Hi,
Truncate table command will not succeed if there is FK relation ship. The
parent table will fail with below error,
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'table name' because it is being referenced by a
FOREIGN KEY constraint.
In this case you have to use delete command. (delete the child table using
Truncate command and use delete command for Parent table)
Thanks
Hari
MCDBA
"Wandenkolk T. Neto" <wandenkolkneto@.hotmail.com> wrote in message
news:#u4uRLSqDHA.688@.TK2MSFTNGP10.phx.gbl...
> Look at this...
> Declare @.sql varchar(8000)
> set @.sql=''
> select @.sql=@.sql + ' Truncate Table ' + name from sysobjects where
> xtype='U'
> EXEC(@.SQL) -- Truncates all tables
> set @.sql=''
> Select @.sql=@.sql + ' DROP TABLE ' + name from sysobjects where
xtype='U'
> EXEC(@.SQL) -- Drops all tables
>
> HTH
> "Matt" <spam@.spam.com> escreveu na mensagem
> news:108001c3a920$524706e0$a101280a@.phx.gbl...
> > Is there any built-in tool that can delete tabel
> > records/contents in sql server? I can run delete from
> > tablename script, but there are two many tables. is there
> > an easy way?
> >
>