Deleting records returned by datareader

I have a function that opens a connection to an SQL database, issues aSELECT command, and reads the records with an OleDbDataReader. As therecords are read, any that match certain criteria are deleted with aDELETE command. Simplified example code is shown below:
Dim dbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd.CommandText = "SELECT * FROM [User] ORDER BY UserID"
dbCmd.Connection.Open()
Dim reader as OleDb.OleDbDataReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
If reader("SomeColumn") = SomeCalculatedValue Then
Dim dbCmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd2.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd2.CommandText = "DELETE FROM [User] WHERE UserID = " + reader("UserID")
dbCmd2.Connection.Open()
dbCmd2.ExecuteNonQuery()
dbCmd2.Connection.Close()
End If
End While
reader.Close()
This code worked well with an MS Access database, but when I changed toSQL Server, I get a database timeout error when attempting to do theDELETE. I suspect the reason is that the connection the reader has openhas the record locked so it cannot be deleted.
The SQL connection string I am using is something like this:
UserDbConnString = "Provider=SQLOLEDB; Server=(Local); User ID=userid; Password=password; Database=dbname"
The connection string I used for MS Access included the property"Mode=Share Deny None". I wonder if there is some similar way to tellSQL Server to allow editing of records that are open for reading withan OleDbDataReader.
Any help would be appreciated.

Hi,

If you are using SQL Server, use SqlClient class.

The SqlClient classes use the native SQL Server drivers to access a database, while the OleDb classes use the generic OLE-DB interface

HTH

|||Thanks for the tip, HTH, but the application was originally written touse MS Access databases using the OleDb classes, and then modificationswere made to upgrade to SQL Server. While using the native SQL driversmay be more efficient, I'm not looking to re-write the application atthe moment - I just want to resolve the issue at hand.
Can anyone tell me how to set up an SQL Server connection so thatopening a connection for read by an OleDbDataReader doesn't lock therecords? I know that I could read all the records into a datagrid, andthen delete the required rows, or that I could add the records thatneed to be deleted to an arraylist for later deletion. But this seemsmuch more complicated than it should be.
All I really need to do is open a datareader, read through the recordsone at a time, and delete a few of the records that match certaincriteria. This was easily done with MS Access, but with SQL Server(using the coonnection string described in my original post) I get atimeout error when I try to delete a record. I am presuming that atimeout occurs because the record I am trying to delete is locked bythe datareader.
Someone surely has a simple answer to this problem.

|||Go to Enterprise Manager click on tools then Query Analyzer and click on tools again then Options then click on Connections there are a few options see if one could help. But I would also run the SQL statement timing out in the Query Analyzer and click on show plan before execution to see if a minor change in the statement will make a difference and use the Profiler to create a trace on statement start and statement close events. I would also run a search for Query Governor in the BOL(books online) see what adjustments you need. Hope this helps.

Deleting Records From Sql Table

I AM TRYING TO DELETE RECORDS FROM THE SQL SERVER TABLE,

I AM USING THE COMMAND BELOW TO DELETE THOSE 71 RECORDS BUT MY WHOLE TABLE GOT DELETED AND HAD TO RECOVER FROM BACKUP..
SO WHAT IS WRONG WITH THE SYNTAX GIVEN BELOW

DELETE FROM dbo.Payment_Placement_AIMS
WHERE EXISTS
(select * from Payment_Placement_AIMS INNER JOIN
TESTING ON Payment_Placement_AIMS.JC_ID = TESTING.JC_ID
WHERE Payment_Placement_AIMS.Date_Stamp > '10/21/2007 12:00:00 AM') AND (Payment_Placement_AIMS.EMP_TYPE = 'First

employment'));since you are using exists in the where clause it is returning true value and the condition is satisfied in result the whole table is deleted (no specific record is mentioned)

following query will help you

DELETE FROM dbo.Payment_Placement_AIMS
WHERE Payment_Placement_AIMS.JC_ID in
(select Payment_Placement_AIMS.JC_ID from Payment_Placement_AIMS INNER JOIN
TESTING ON Payment_Placement_AIMS.JC_ID = TESTING.JC_ID
WHERE Payment_Placement_AIMS.Date_Stamp > '10/21/2007 12:00:00 AM') AND (Payment_Placement_AIMS.EMP_TYPE = 'First

employment'))sql

Deleting empty rows from table

Please can someone provide a T-SQL command that will delete empty rows from a
table
ThanksHuh? A row cannot exist if it is empty.
By definition a row is made up of data.
Perhaps you want to delete all rows where a specific column is empty (NULL).
If that is the case this should get you started:
/*
Identify the rows that you are about to delete
*/
SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
/*
Delete the rows
DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
*/
--
Keith
"airforce1" <airforce1@.discussions.microsoft.com> wrote in message
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thanks|||Hi,Keith
I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> Huh? A row cannot exist if it is empty.
> By definition a row is made up of data.
> Perhaps you want to delete all rows where a specific column is empty
(NULL).
> If that is the case this should get you started:
> /*
> Identify the rows that you are about to delete
> */
> SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS
NULL
> /*
> Delete the rows
> DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
> */
> --
> Keith
>
> "airforce1" <airforce1@.discussions.microsoft.com> wrote in message
> news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> > Please can someone provide a T-SQL command that will delete empty rows
> from a
> > table
> >
> > Thanks
>|||The original poster did not post a very well written question, so it is
difficult to provide a good answer.
<quote>
Please can someone provide a T-SQL command that will delete empty rows from
a
table
Thanks
</quote>
--
Keith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%239Md5vu1EHA.804@.TK2MSFTNGP12.phx.gbl...
> Hi,Keith
> I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
>
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> > Huh? A row cannot exist if it is empty.
> > By definition a row is made up of data.
> >
> > Perhaps you want to delete all rows where a specific column is empty
> (NULL).
> >
> > If that is the case this should get you started:
> >
> > /*
> > Identify the rows that you are about to delete
> > */
> > SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS
> NULL
> >
> > /*
> > Delete the rows
> > DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS
NULL
> > */
> >
> > --
> > Keith
> >
> >
> > "airforce1" <airforce1@.discussions.microsoft.com> wrote in message
> > news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> > > Please can someone provide a T-SQL command that will delete empty rows
> > from a
> > > table
> > >
> > > Thanks
> >
>|||I suppose you can identify rows you want to delete. Maybe you want to delete
*identical* rows which can't be deleted though an error message (because the
database can't delete identical rows).
If it's the problem you are asking for, so add an autoincrement field in
your table and then repeat rows deletion.
In other case specify details ...
Vlastik
"airforce1" <airforce1@.discussions.microsoft.com> pí¹e v diskusním pøíspìvku
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thankssql

Deleting empty rows from table

Please can someone provide a T-SQL command that will delete empty rows from a
table
Thanks
Huh? A row cannot exist if it is empty.
By definition a row is made up of data.
Perhaps you want to delete all rows where a specific column is empty (NULL).
If that is the case this should get you started:
/*
Identify the rows that you are about to delete
*/
SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
/*
Delete the rows
DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
*/
Keith
"airforce1" <airforce1@.discussions.microsoft.com> wrote in message
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thanks
|||Hi,Keith
I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> Huh? A row cannot exist if it is empty.
> By definition a row is made up of data.
> Perhaps you want to delete all rows where a specific column is empty
(NULL).
> If that is the case this should get you started:
> /*
> Identify the rows that you are about to delete
> */
> SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS
NULL
> /*
> Delete the rows
> DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
> */
> --
> Keith
>
> "airforce1" <airforce1@.discussions.microsoft.com> wrote in message
> news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> from a
>
|||The original poster did not post a very well written question, so it is
difficult to provide a good answer.
<quote>
Please can someone provide a T-SQL command that will delete empty rows from
a
table
Thanks
</quote>
Keith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%239Md5vu1EHA.804@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,Keith
> I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
>
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> (NULL).
> NULL
NULL
>
|||I suppose you can identify rows you want to delete. Maybe you want to delete
*identical* rows which can't be deleted though an error message (because the
database can't delete identical rows).
If it's the problem you are asking for, so add an autoincrement field in
your table and then repeat rows deletion.
In other case specify details ...
Vlastik
"airforce1" <airforce1@.discussions.microsoft.com> pe v diskusnm pspvku
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thanks

Deleting empty rows from table

Please can someone provide a T-SQL command that will delete empty rows from
a
table
ThanksHuh? A row cannot exist if it is empty.
By definition a row is made up of data.
Perhaps you want to delete all rows where a specific column is empty (NULL).
If that is the case this should get you started:
/*
Identify the rows that you are about to delete
*/
SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
/*
Delete the rows
DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
*/
Keith
"airforce1" <airforce1@.discussions.microsoft.com> wrote in message
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thanks|||Hi,Keith
I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> Huh? A row cannot exist if it is empty.
> By definition a row is made up of data.
> Perhaps you want to delete all rows where a specific column is empty
(NULL).
> If that is the case this should get you started:
> /*
> Identify the rows that you are about to delete
> */
> SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS
NULL
> /*
> Delete the rows
> DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
> */
> --
> Keith
>
> "airforce1" <airforce1@.discussions.microsoft.com> wrote in message
> news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> from a
>|||The original poster did not post a very well written question, so it is
difficult to provide a good answer.
<quote>
Please can someone provide a T-SQL command that will delete empty rows from
a
table
Thanks
</quote>
Keith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%239Md5vu1EHA.804@.TK2MSFTNGP12.phx.gbl...
> Hi,Keith
> I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
>
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> (NULL).
> NULL
NULL[vbcol=seagreen]
>|||I suppose you can identify rows you want to delete. Maybe you want to delete
*identical* rows which can't be deleted though an error message (because the
database can't delete identical rows).
If it's the problem you are asking for, so add an autoincrement field in
your table and then repeat rows deletion.
In other case specify details ...
Vlastik
"airforce1" <airforce1@.discussions.microsoft.com> pe v diskusnm pspvku
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thanks

Deleting data from a column

What is the command to delete data from certain named colums but
otherwise leave the column intact and also leave the rows and unnamed
columns intact?The following syntax will replace the existing data in the specified columns
with NULL. Without a WHERE clause it will update every row in the table.
If you only want to modify some subset of rows, you'll need to specify the
appropriate WHERE clause.
UPDATE table
SET column_name1 = NULL, column_name2 = NULL, etc.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"RocketMan" <ImaChessNut@.gmail.com> wrote in message
news:1181246690.348694.107840@.i38g2000prf.googlegroups.com...
> What is the command to delete data from certain named colums but
> otherwise leave the column intact and also leave the rows and unnamed
> columns intact?
>|||Hi
Its the UPDATE command
e.g
update table set column=null where x=1
see BOL for more info
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"RocketMan" <ImaChessNut@.gmail.com> wrote in message
news:1181246690.348694.107840@.i38g2000prf.googlegroups.com...
> What is the command to delete data from certain named colums but
> otherwise leave the column intact and also leave the rows and unnamed
> columns intact?
>

Deleting data from a column

What is the command to delete data from certain named colums but
otherwise leave the column intact and also leave the rows and unnamed
columns intact?
The following syntax will replace the existing data in the specified columns
with NULL. Without a WHERE clause it will update every row in the table.
If you only want to modify some subset of rows, you'll need to specify the
appropriate WHERE clause.
UPDATE table
SET column_name1 = NULL, column_name2 = NULL, etc.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"RocketMan" <ImaChessNut@.gmail.com> wrote in message
news:1181246690.348694.107840@.i38g2000prf.googlegr oups.com...
> What is the command to delete data from certain named colums but
> otherwise leave the column intact and also leave the rows and unnamed
> columns intact?
>

Deleting data from a column

What is the command to delete data from certain named colums but
otherwise leave the column intact and also leave the rows and unnamed
columns intact?The following syntax will replace the existing data in the specified columns
with NULL. Without a WHERE clause it will update every row in the table.
If you only want to modify some subset of rows, you'll need to specify the
appropriate WHERE clause.
UPDATE table
SET column_name1 = NULL, column_name2 = NULL, etc.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"RocketMan" <ImaChessNut@.gmail.com> wrote in message
news:1181246690.348694.107840@.i38g2000prf.googlegroups.com...
> What is the command to delete data from certain named colums but
> otherwise leave the column intact and also leave the rows and unnamed
> columns intact?
>|||Hi
Its the UPDATE command
e.g
update table set column=null where x=1
see BOL for more info
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"RocketMan" <ImaChessNut@.gmail.com> wrote in message
news:1181246690.348694.107840@.i38g2000prf.googlegroups.com...
> What is the command to delete data from certain named colums but
> otherwise leave the column intact and also leave the rows and unnamed
> columns intact?
>

Deleting data bloats log file

When I delete substantial amounts of data using the SQL DELETE command, the database size apparently remains the same (702 mb) and the log file goes from about 17 mb to over 1 gig. I was expecting for the overall size to decrease drastically, but got just the opposite.

Is this typical? Can I do something to slim it down? As I am just trying to decrease the overall size to make it easier to work with when creating my application in VB, I am not worried about restoring the db (I have secure copies).

The deletes are recorded in the Transaction Log, so that in case of needing to rebuild a 'crashed' database, you have a complete record or every action.

However, if you are just trying to clean out tables, you 'might' be able to use the TRUNCATE command.

TRUNCATE TABLE MyTable

If you are just removing part of the data, then you could change the database recovery type to 'Simple', and the log will be truncated as soon as the deletes are complete.

|||Hi Arnie,

I just want to create a lightweight version of the DB to make the VB application load and run faster during the early stages of development (and take up less drive space). I want the structure to remain the same so that when the application code is in good shape I will use the full data version. So I want to remove most of the data and keep the file size (especially log) as low as possible.

It sounds like I need to change the recovery type to 'Simple' but don't know how. Have checked out the menus and search through Books Online without luck.
|||

One consideration many of us make is to create a script of the database, perhaps including scripted data inserts for Lookup tables, etc.

Then when there is a schema change, you add the schema change to the script, and then you can regularly drop the development database and run the script to re-create a 'clean' copy. You can script out the objects by right-clicking on the database, select [Tasks...], and then [Generate Scripts...]. Follow the prompts. There are also third party tools for this purpose. Below are some resources that will help with scripting out data.

To change the Recovery Model, using Object Explorer, right-click on the database, select [Properties], then [Options]. You can change the Recovery model on that screen. Be sure to make a note to return the Recovery Model to [FULL] when you move the database to production. You may need to 'shrink' the log file since it has grown so large.

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

DDL –Script Database to File using SMO (VB.NET)
http://msdn2.microsoft.com/en-us/library/ms162138.aspx

DDL –Script Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

|||Thanks for the info.

I set the Recovery Model to simple.

I deleted about 95% of the data.

I ran DBCC SHRINKFILE and shrunk the log file to 2 mb (FANTASTIC! - NO PROBLEMS)

But the file size for the DB still show 702 mb in Windows Explorer. So I looked at the Disk Usage where it shows 702 mb for SPACE RESERVED but only 4.3 mb for SPACE USED.

I wonder if this is because the field with the most data is ntext datatype (which I believe reserves a certain amount of disk space regardless of what is used. The db was created several years ago in Access and migrated to SQL Server 2000. I have considered changing the datatype to nvarchar (MAX) which I believe only reserves what is actually needed. Also, when I create a full-text index, I get an alert that the ntext datatype may prevent some features of WITH CHANGE_TRACKING AUTO;

Do you think the ntext datatype is keeping the file size so large? If so, is it simply a matter of changing datatype on that column? Or could there be other factors keeping the db file size so large?|||

First up the warning about ntext and fulltext search. Full Text change tracking does not support updates made via writetext or updatetext which is probably why you are getting that error. As you are using SQL2005 (?) i'd suggest changing the column type to nvarchar(max). Text, Ntext and image have been superceded but are supported for backward compatability.

As for the size of the database. When you delete data, the size of the datafile will not shrink but the pages within that will be marked as free. Generally speaking its a good idea to have your datafile at a size bigger than the current database size as this allows for growth of data without the (expensive) operation of growing the datafile.

In saying this, you are able to shrink the data file in a similar way to the transaction log.

HTH!

|||Thanks Rich,

I shrunk the db as well and it really does make a big difference while I developing in VB. I'm learning LINQ so there is alot of trial and error, so this really saves me some time.

Now to the question of what to do with the database when I am ready for full size (when I get my VB code worked out) since there is clearly some redundant data that will need to be deleted. What do you mean by "its a good idea to have your datafile at a size bigger than the current databse size as this allows for growth of data without the expensive operation of growing the datafile"?

I didn't realize that you could directly determine the size of the data other than choosing column datatypes that limit or extend size (e.g. nvarchar(10)). This was one of the concerns about the ntext datatype (besides being outdated). I had read that it used up a certain amount of space regardless of the data whereas nvarchar(max) is flexible in allocating space depending upon the amount of data, hence a smaller file size customized to the requirements of the data.

For this particular database, the size should not increase significantly once it is finished since users will not be able to add or delete data. From time to time corrections (typos) may need to be made but should not add substantiallly to the size.

|||

Sorry if my wording was a bit confusing.

When you create a database you can specify the size of the data and log files which essentially allocates space for your data to be stored.

If you set your files too small and you have a lot of database write activity, the files will physically need to grow to store the data. The physical growth of a file is expensive in terms of resources and you want to minimise this, so set the files at a size that reflects how big you predict your data to grow.

The size of the your columns etc is how you control how much data is stored within the datafile and this is more likely to be a factor in performance rather than the physical file size.

HTH!

|||Thanks for the helpful explanation. I will keep that in mind as I alter this db and create new ones.

As a followup to the datatype question, I have been trying to change the datatype ntext on the SectionText column to nvarchar(max), but am having a problem. My sql is:

ALTER TABLE FullDocuments MODIFY SectionText nvarchar(Max) not null;

I am getting this error message:

Incorrect syntax near 'MODIFY'.

Any thought on what I am doing wrong and how to fix it?|||

Thats not the correct syntax for that command:

Try:

Code Snippet

ALTER TABLE FullDocuments
ALTER COLUMN SectionText NVARCHAR(Max) NOT NULL;

Good luck!|||

Let me expand upon Rich's explanation.

When a database runs out of space, and if 'AutoGrow' is set to TRUE, it will automatically acquire additional disk space from the OS. Consider the analogy of a notebook. When SQL Server acquires disk space from the OS, it is like the notebook gets paper, and then that paper has to be divided into pages, lines drawn on the pages, and they have to be numbered before they are ready to use. Once done, it is quick and efficient for SQL Server to use the pages. But when the pages fill up, and SQL Server has to acquire additional space from the OS, it is a 'costly' operation to have to wait until all of the new pages are ready to use before the operation that needed to store data can continue. So it is best to 'size' the database large enough to contain the expected data growth over a good period of time. Then it is a 'Best Practice' to have a scheduled task that will periodically assess the need for additional space, and acquire that space during 'non-peak' times. (AutoGrow could fire off at the worst moment for server load.)

Perhaps these resources will help:

FileSize -Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512

FileSize -DB Shrink Issues
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

|||Thanks again.

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 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 delet
e
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 Permissio
ns
> 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
1;] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the object
s
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't del
ete
> 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 Permissio
ns
> 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
1;] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the object
s
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't del
ete
> it.
> Please help. Thanks.

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 datbase.

Can anyone help me with this ?
I was wondering if there is a command I can run that deletes a database? I know this can be done in Enterprise Manager.
Have tried a few sites and searches to no avail...
Any help appreciated...
Maith agat,It's as easy as DROP DATABASE.

PS It's in the books online;)

Deleting a database

I would like to delete a database from Analysis Services from the command line, but I have been unable to find a way to accomplish this. Is there a utility or a script that can accomplish this?

Hi

In MSAS 2k ,You can a write a VBSCRIPT using DSO object in DTS for deletion of OLAPDB.Then run this DTS from DTSRUN "DTS Package name" from commnad line.

Thx and regards

Pankaj

|||

And if you are using Analysis Services 2005, you can write this C# application based on AMO (the management object model for AS2005, successor of DSO from AS2000):

(to reference AMO, use Microsoft.AnalysisServices.DLL from "%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies")

using Microsoft.AnalysisServices;

...

Server s = new Server();

s.Connect("localhost");

try

{

s.Databases.GetByName("My Database").Drop();

}

finally

{

s.Disconnect();

}

Deleting a Database

I am using ADO to execute a DROP DATABASE command. Before that I run some
query against the DB to do some checks. However I get the meesage "Cannot
drop the database XXX because it is currently in use". If I do not run the
query before the DROP works. I have made sure that all connections to the DB
are closed.
I run the SQL Profiler and noticed that there is an "Audit Login" line added
before a command and a corresponding "Audit Logout" afterwards. Now I
noticed that the "Audit Logout" command takes some time to appear in the
trace window. I put a message box before my DROP command and after my query
and waited till the "Audit Logout" appeared. Then the DROP worked. So it
seems that there is some kind of delay before the DB is "released" after my
query is run.
Is there some expalnation/solution to that?
Thanks
YannisYannis wrote on Wed, 17 May 2006 10:30:01 +0300:

> I am using ADO to execute a DROP DATABASE command. Before that I run some
> query against the DB to do some checks. However I get the meesage "Cannot
> drop the database XXX because it is currently in use". If I do not run the
> query before the DROP works. I have made sure that all connections to the
> DB are closed.
> I run the SQL Profiler and noticed that there is an "Audit Login" line
> added before a command and a corresponding "Audit Logout" afterwards. Now
> I noticed that the "Audit Logout" command takes some time to appear in the
> trace window. I put a message box before my DROP command and after my
> query and waited till the "Audit Logout" appeared. Then the DROP worked.
> So it seems that there is some kind of delay before the DB is "released"
> after my query is run.
> Is there some expalnation/solution to that?
> Thanks
> Yannis
Do you have connection pooling enabled? Connections which are "closed" by
the application will be passed to the pool so they can be reused by another
request, rather than starting a new connection from scratch. The delay will
be due to the connection pool holding the connection open and then releasing
it after the set timeout.
Dan|||Thank you Daniel,
I don't do anything relating to connection pooling.
Any idea where that may occur in ADO and what I can do to overcome it?
Yannis
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:%23GWlEFZeGHA.380@.TK2MSFTNGP04.phx.gbl...
> Yannis wrote on Wed, 17 May 2006 10:30:01 +0300:
>
> Do you have connection pooling enabled? Connections which are "closed" by
> the application will be passed to the pool so they can be reused by
> another request, rather than starting a new connection from scratch. The
> delay will be due to the connection pool holding the connection open and
> then releasing it after the set timeout.
> Dan
>|||You must prevent other connections to access the database:
alter database <db name>
set single_user
with rollback immediate;
use master;
drop database <db name>;
ML
http://milambda.blogspot.com/|||If you're using ODBC drivers, you'll need to make a change to your ODBC
settings in Windows. If you're not, you'll have to dig around for how to
change this for OLE DB or whatever connection type you're using.
Dan
Yannis wrote on Wed, 17 May 2006 15:34:14 +0300:
> Thank you Daniel,
> I don't do anything relating to connection pooling.
> Any idea where that may occur in ADO and what I can do to overcome it?
> Yannis
> "Daniel Crichton" <msnews@.worldofspack.com> wrote in message news:%23GWlEF
ZeGHA.380@.TK2MSFTNGP04.phx.gbl...

Deleted entire DB by accident!

Gentlemen, Can anyone save my life at the moment!
I need to restore a DB that I accidentally erased using
the Delete command in the Enterprise admin. I was on the
wrong server and the DB names are the same.
(No Backups were on this box since it's a dev box.)
Can anyone tell me a way to get my DB back?
Thanks,
BradTake your last production backup and restore it to your dev server.
Or were you fibbing and delete a production database you had no backups for?
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> Gentlemen, Can anyone save my life at the moment!
> I need to restore a DB that I accidentally erased using
> the Delete command in the Enterprise admin. I was on the
> wrong server and the DB names are the same.
> (No Backups were on this box since it's a dev box.)
> Can anyone tell me a way to get my DB back?
> Thanks,
> Brad|||The concept of Dev in our dept is that it it's Dev before
it goes to production. So, it's never actually seen
production. There is no actual restore .BAK file and
the .MDF file is missing.
Any ideas?
>--Original Message--
>Take your last production backup and restore it to your
dev server.
>Or were you fibbing and delete a production database you
had no backups for?
>"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
message
>news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
>> Gentlemen, Can anyone save my life at the moment!
>> I need to restore a DB that I accidentally erased using
>> the Delete command in the Enterprise admin. I was on
the
>> wrong server and the DB names are the same.
>> (No Backups were on this box since it's a dev box.)
>> Can anyone tell me a way to get my DB back?
>> Thanks,
>> Brad
>
>.
>|||Turn off the sql server and start searching for a file restore utility (I
don't have one or know of one in particular) that will work on your OS. If
the filespace is overwritten you will lose your data.
Just in case, though, did you select to delete the filegroups? If not they
will still be there as .ndf, .mdf, .ldf files.
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:2774501c38f69$d9323470$a601280a@.phx.gbl...
> The concept of Dev in our dept is that it it's Dev before
> it goes to production. So, it's never actually seen
> production. There is no actual restore .BAK file and
> the .MDF file is missing.
> Any ideas?
>
> >--Original Message--
> >Take your last production backup and restore it to your
> dev server.
> >
> >Or were you fibbing and delete a production database you
> had no backups for?
> >
> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> message
> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> >> Gentlemen, Can anyone save my life at the moment!
> >> I need to restore a DB that I accidentally erased using
> >> the Delete command in the Enterprise admin. I was on
> the
> >> wrong server and the DB names are the same.
> >> (No Backups were on this box since it's a dev box.)
> >> Can anyone tell me a way to get my DB back?
> >>
> >> Thanks,
> >>
> >> Brad
> >
> >
> >.
> >|||Thanks for the help, unfortunately I've been trying to
recover the MDF files with recovery utilities but so far
I've had no luck. Is it even possible to track down an
MDF file once it's been deleted with Enterprise Manager?
>--Original Message--
>Turn off the sql server and start searching for a file
restore utility (I
>don't have one or know of one in particular) that will
work on your OS. If
>the filespace is overwritten you will lose your data.
>Just in case, though, did you select to delete the
filegroups? If not they
>will still be there as .ndf, .mdf, .ldf files.
>
>
>
>"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
message
>news:2774501c38f69$d9323470$a601280a@.phx.gbl...
>> The concept of Dev in our dept is that it it's Dev
before
>> it goes to production. So, it's never actually seen
>> production. There is no actual restore .BAK file and
>> the .MDF file is missing.
>> Any ideas?
>>
>> >--Original Message--
>> >Take your last production backup and restore it to your
>> dev server.
>> >
>> >Or were you fibbing and delete a production database
you
>> had no backups for?
>> >
>> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
>> message
>> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
>> >> Gentlemen, Can anyone save my life at the moment!
>> >> I need to restore a DB that I accidentally erased
using
>> >> the Delete command in the Enterprise admin. I was on
>> the
>> >> wrong server and the DB names are the same.
>> >> (No Backups were on this box since it's a dev box.)
>> >> Can anyone tell me a way to get my DB back?
>> >>
>> >> Thanks,
>> >>
>> >> Brad
>> >
>> >
>> >.
>> >
>
>.
>|||Deleting with EM means that EM executes the DROP DATABASE command. For the DROP DATABASE command,
SQL Server will delete the file physically. Working on the OS level is your last resort. And, if
keeping the data from the dev environment is crucial, make sure you have backup for the dev
environment next time. :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:036001c38f72$ef2e2b90$a401280a@.phx.gbl...
> Thanks for the help, unfortunately I've been trying to
> recover the MDF files with recovery utilities but so far
> I've had no luck. Is it even possible to track down an
> MDF file once it's been deleted with Enterprise Manager?
> >--Original Message--
> >Turn off the sql server and start searching for a file
> restore utility (I
> >don't have one or know of one in particular) that will
> work on your OS. If
> >the filespace is overwritten you will lose your data.
> >
> >Just in case, though, did you select to delete the
> filegroups? If not they
> >will still be there as .ndf, .mdf, .ldf files.
> >
> >
> >
> >
> >
> >
> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> message
> >news:2774501c38f69$d9323470$a601280a@.phx.gbl...
> >> The concept of Dev in our dept is that it it's Dev
> before
> >> it goes to production. So, it's never actually seen
> >> production. There is no actual restore .BAK file and
> >> the .MDF file is missing.
> >>
> >> Any ideas?
> >>
> >>
> >> >--Original Message--
> >> >Take your last production backup and restore it to your
> >> dev server.
> >> >
> >> >Or were you fibbing and delete a production database
> you
> >> had no backups for?
> >> >
> >> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> >> message
> >> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> >> >> Gentlemen, Can anyone save my life at the moment!
> >> >> I need to restore a DB that I accidentally erased
> using
> >> >> the Delete command in the Enterprise admin. I was on
> >> the
> >> >> wrong server and the DB names are the same.
> >> >> (No Backups were on this box since it's a dev box.)
> >> >> Can anyone tell me a way to get my DB back?
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Brad
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Delete records from several tables at once

Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
Jorge
JotaO wrote:
> Hi!
> Is there any SQL command that allow me to delete records from a list of
> tables?
> Something like this: "Delete From Table1, Table2"
> TIA
> Jorge
Answered in microsoft.public.sqlserver.programming
Please do not multi-post!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Delete records from several tables at once

Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
JorgeJotaO wrote:
> Hi!
> Is there any SQL command that allow me to delete records from a list of
> tables?
> Something like this: "Delete From Table1, Table2"
> TIA
> Jorge
Answered in microsoft.public.sqlserver.programming
Please do not multi-post!
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Delete records from several tables at once

Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
JorgeJotaO wrote:
> Hi!
> Is there any SQL command that allow me to delete records from a list of
> tables?
> Something like this: "Delete From Table1, Table2"
> TIA
> Jorge
Answered in microsoft.public.sqlserver.programming
Please do not multi-post!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Delete records from serveral tables at once

Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
JorgeNo. However, it's possible to speed up the process by using TRUNCATE
TABLE - as long as no foreign keys refer to these tables.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"JotaO" <antispam@.antispam.com> wrote in message
news:%23YKsGzLUGHA.1444@.TK2MSFTNGP11.phx.gbl...
Hi!
Is there any SQL command that allow me to delete records from a list of
tables?
Something like this: "Delete From Table1, Table2"
TIA
Jorge|||Hi
If there is a foreign key in Table2 referencing Table1 then you can enable
cascading deletes.
John
"JotaO" <antispam@.antispam.com> wrote in message
news:%23YKsGzLUGHA.1444@.TK2MSFTNGP11.phx.gbl...
> Hi!
> Is there any SQL command that allow me to delete records from a list of
> tables?
> Something like this: "Delete From Table1, Table2"
> TIA
> Jorge
>