Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







SQL Db Size Will Not Decrease After Deleting Data


Hi

I installed " Web Wiz Forum ASP SQL 2000 DB "

it work fine but
when i added some data in the forum for example my db size is 1.45 MB

after i delete those data the db size will not decrease

is there any code that i must enter on the sql server setup file

Excuse me i asked this question in the web wix forum site but they don`t
answer me

if know what i must to do plz tell me


Thanks


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How To Decrease The Size Of Data File ?
Hello Guys,

Just wondering if someone can help me decrease the size of mdf and ldf files. In the past production database "NewUniverse" had been allocated space of 100 GB for mdf file and 8 GB of ldf file. However the data file has only used 30 GB of data. But now due to disk space related reason, I tried to decrease the datafile size from 100 GB to 40 GB. But I am not able to do it.

Any help in this regard would be appreciated.

View Replies !   View Related
How To Decrease Log File Size
Dear all,

I wanna ask you how to decrease log file size..?
For example i have database file with data file size is 122,166 Kb
and the database log file is 6,330,176 KB

Thanks,

Oko Sakti Banget

View Replies !   View Related
How To Decrease The Size Of The Transaction Log Daily?
 

 
 
I have a database that the transaction log grows about 1 GB per day. I would like this size was decreased daily. Does anyone have any suggestions? 
Some friends told me that after the Full backup that is done daily, I should perform a backup of transanction log with option to truncate and after, make a shrink in the database. That is exactly what should be done?

Thank you,
 
Best Regards,
 
Ralph Haddad

View Replies !   View Related
How To Decrease The Database File Size
Hi all,

 

How to derease the database file size in Primary filegroup.

 

Thanks,

View Replies !   View Related
Does DB Size Decrease When I Delete A Huge Table ??
Hi,
My DB size (Right click on DB Name, Data Files tab, Space Allocated field) was 10914 MB.

I delete a huge table (1.2 million records * 15 columns).
I checked the db size again. It didnt change.
Shouldn't it decrease because I delete a huge table ??

View Replies !   View Related
Reduce Table Size Without Deleting Data
We are using SQL Server 2000 Standard ed, sp4 on Server 2000 Advanced.We have one table that is three times as large as the rest of the database.Most of the data is static after approximately 3-6 months, but we arerequired to keep it for 8 years. I would like to archive this table (A), butthere are complications.1. the only way to access the data is through the application (they areimages produced by the application-built on Power-Builder)2. there are multiple tables refrencing this table and vise-versa3. we restore the entire db to two other servers for testing and trainingregularly4. there might be more complications that have not been thought ofCurrently, our only plan is to setup a seperate server with a copy of this dbon it and the application. Leave only the tables necessary to access the data,and if this 'archive' works, remove from production the data from the table Aand all references to the table A from rows on the other tables.I mentioned #3 because someone mentioned a third party tool that may be ableto pull the data from the table, archive it elsewhere, and at the same time,place a 'pointer' in the table to the new storage location. The tool theymentioned only works on Oracle and we have not explored beyond that yet.I am ready to explore ideas and suggestions; I am still new to the DBA world,I am out of ideas.Thank you!--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200607/1

View Replies !   View Related
Is It Possible For A Table To Increase Size When Deleting Records?
No transaction log involved, only the table itself.

 

Use sp_spaceused "table_name" to check the space used.

 

It seems the table size actually increased from the beginning to the middle of deletion, at the end of deletion, its size decreased.

 

Recovery mode set to be simple, autoshrink turned on.

 

The tables tested are about 50MB ~ several GB in size, all have the same behavior. The size increased about 5%~10%.

 

Since the deletion is called from another software, I want to know if it is possible for SQL Server to have this behavior or it is absolutely the 3rd party software's issue

 

Thanks!

View Replies !   View Related
Database Space Size Increased On Deleting Records
Hi,

I was running out of space and thus deleted some rows from a table. To my surprise the db size increased. I then shrunk it to bring it back to what it was earlier.

When i deleted some 5000 rows, some space must have been released. Where did the space go and why did the db size increase after deleting the records?

I thght it might be log files..but db is set to Simple Recovery which does not utilize a Log File.

Any reasons?

View Replies !   View Related
SQL2005 Data Import Error, Unicode Data Is Odd Byte Size For Column &&<n&&>. Should Be Even Byte Size
Hi, I have a problem importing data from SQL Server 2000 'text' columns to SQL Server 2005 nvarchar(max) columns.  I get the following error when encountering a transfer of any column that matches the above.
The error is copied below,

Any help on this greatly appreciated...

ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Unicode data is odd byte size for column 3. Should be even byte size.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)


Many thanks

View Replies !   View Related
Encrypted Data Size By Original Size, Algorithm ?
I want to know encrypted data's size for designing database field size.

For example, cardnumber varchar(20)  Encrypted by Triple_DES and PassPhrase, How match size does need to encrypted data store field.

I think the size does not depend to PassPhrase char length.

Regards,
Yoshihiro Kawabata

View Replies !   View Related
Best Practice For Data Size/Log Size Ratio
Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com

View Replies !   View Related
Estimating Data Size And Log Size In A Database
Question 1:
I wonder if anyone could help me on estimating the acutal data size and its log size for MS-SQL Server version 7. I know that on-line book specifies the procedure of the data size but I have some data items with attribute of data type as "Text".
In order to include the data type "Text" how many bytes of overhead and fill factor to be considered for data size calculations?

Question 2:
Also, I want to know that if anyone can help me with log size calculation for a table. It's just not good enough if the on-line book says to take from 25 to 50 % or something....it is too vague and I can not take the answer.

Here are the example of the table and if anyone can include the answers to the questions above. It will be great.

(Example:)
Can anyone help me to figure out the data and log size respectively based on the conditions below:
----------------------------------------------
1. Table Name: A_TBL
2. Table Format:
Item Data Type Length
--------------------------------------------------------
EmployeeNumber Char 20
EmployeeName Char 50
EmployeeURL TEXT 16 -----pointing---> 256
EmployeeMemo TEXT 16 -----pointing--->1000
EmployeeTitleNo Int 4

3. DB(Table) access frequency per transaction:
Insert = 1 time/transaction
Update= 2 times/tranaction
Select = 6 times/tranaction

4. Transaction occurred: 10 times, therefore; 10 INSERT, 20 UPDATE, 60 SELECT statements are issued.
(I guess 60 SELECT statements are not subject to be in the log so forget the SELECT statement)

5. Fill Factor 100 for all the data type.

6. Non-Clustered index is set for the above.

I really want to know about it, please help me on this.


Regards,

View Replies !   View Related
Performance Decrease After OS Upgrade
We were running a 4 processor server with 8 GB RAM and 800GB hard diskspace on Windows 2000 server and SQL Server 2000.Recently we have upgraded the server to 8 processor and added another800GB. The OS was upgradded to Windows 2003 enterprise edition.All the processor are 2.5GHz Xeon preocessors.After upgradation the performance of the server has gone down from whatit was giving before the upgrade.It seems that multiprocessing is not ocurring.The model of the server is HP DL740The OS is installed in a built in array(5i controller) of the serverand the SQL server is installed in a external array(6400 controller).I will really aprecite if anyone can give any clue to improve theperformance.Thanks in advance.Taw.

View Replies !   View Related
Decrease A Number In Column By One
A want to user a procedure to decrease a number in a col. by 1 each day.

I need some type of operation

then a job has to run the procedure to decrease a number once a day.

I don't know how to go about the operation? I am going to the SQL Query Anylizer to help me run the script to test it. I have in a col. of 201 numbers, and I will subtract 1 from each day from all recorders. I guess you could call this a count down or timer. Does this code work for me?

Thank You,

Insert into newusers (Timer-1)

Select Timer

From newuses


GO

View Replies !   View Related
Use Datatableadapter To Decrease Item Quantity
 my friend said that to decrease quantity of item must use store procedure or trigger.. is there can use datatableadapter to decrease quantity? if yes.., so which the easy to use? procedure or trigger or tableadapter?ok, thx....

View Replies !   View Related
Archive Data Instead Of Deleting It To Prevent 4GB Data Limit
We are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data.

 

Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days.

 

We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok.

 

Any other solutions that would allow us to archive data instead of deleting it? Thanks.

View Replies !   View Related
Deleting Existing Data Before Loading New Data
I have a package which loads data from a flat file (csv) to 4 tables in a database.
Now, the load is incremental.

I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this?
Iam using 4 Oledb Destinations, 1 multicast, 1 source component to do this.
Also can it happen like a transaction? because if it deletes the existing data and couldnt load new data there will be a problem!.how to avoid this?

View Replies !   View Related
Deleting Data From DB
I have edited the aspnet_Users_CreateUser stored procedure so that the UserId that is created when a new user is created is copied to a UserId field in another table. However, when I use the website administration tool to delete users that have been created, it gives me an error saying the delete statement conflicted with the reference constraint. I then added the following code in the aspnet_Users_DeleteUser procedure....
IF ((@TablesToDeleteFrom & 16) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_tt') AND (type = 'V'))))
BEGIN
DELETE FROM dbo.userclassset WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1    hdhd
END
This code was then added to the function at the end which deletes the data from the aspnet_Users table when everything else has been removed
(@TablesToDeleteFrom & 16) <> 0 AND
Now when I delete a user in the website admin tool, it "deletes" (with no error) the user from the list but doesnt actually physically delete it from the database.
Any ideas?
 

View Replies !   View Related
Deleting All Data
Is there a way to delete all data from a database - all tables and alltables excluding system tables?sqlserver 2000?Thanks,Tmuld

View Replies !   View Related
Deleting The Master Table Withour Deleting The Child Tables
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this,  parent table has relation with the child table.
regards
vinod.t.v

View Replies !   View Related
Deleting Data From Database
Edited by SomeNewKid. Please post code between <code> and </code> tags.


I have added a delete button to my datagrid, and put in what I think is the code to delete a member from the database at their Member ID, however when I have called a members details and the delete button is pressed, nothing happens!!??
Any ideas?
Here is the code:

<%@ Page Language="VB" %>
<script runat="server">

' Insert page code here
'
Function GetMember(ByVal iD As Integer) As System.Data.SqlClient.SqlDataReader
Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT [oga].* FROM [oga] WHERE ([oga].[ID] = @ID)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

sqlCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = iD

sqlConnection.Open
Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function

Sub dgMember_Delete(sender as Object, e as DataGridCommandEventArgs)
dgMember.EditItemIndex = -1

dgMember.DataSource = GetMember(memberID.Text)
dgMember.DataBind()

End Sub


Sub btnView_Click(sender As Object, e As EventArgs)
dgMember.DataSource = GetMember(memberID.Text)
dgMember.DataBind()
End Sub

Sub dgMember_SelectedIndexChanged(sender As Object, e As EventArgs)

End Sub
Function DeleteMember(ByVal iD As Integer) As Integer
Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "DELETE FROM [oga] WHERE ([oga].[ID] = @ID)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)

sqlCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = iD

Dim rowsAffected As Integer = 0
sqlConnection.Open
Try
rowsAffected = sqlCommand.ExecuteNonQuery
Finally
sqlConnection.Close
End Try

Return rowsAffected
End Function

</script>
<html>
<head>
</head>
<body id="dgMemberInfo">
<font face="arial">
<h1><img src="thlogotop.gif" align="left" /> <img src="thlogotop.gif" align="right" />
<br />
<center><font color="red">T</font>albot <font color="red">H</font>eath <font color="red">O</font>ld <font color="red">G</font>irls <font color="red">A</font>ssociation
</center>
</h1>
<h2 align="center">Delete a Member
</h2>
<asp:HyperLink id="HyperLink1" runat="server" Width="94px" NavigateUrl="Default.aspx">Main Menu</asp:HyperLink>
<hr />
<br />
<br />
<br />
<p align="center">
</p>
<form runat="server">
<br />
<div align="center">Please Enter Member ID :
<asp:TextBox id="memberID" runat="server"></asp:TextBox>
</div>
<br />
<br />
<br />
<br />
<div align="center">
<asp:Button id="btnView" onclick="btnView_Click" runat="server" Width="204px" Text="View Member Details"></asp:Button>
</div>
<br />
<br />
<p align="center">
<asp:DataGrid id="dgMember" runat="server" BorderColor="Black" OnSelectedIndexChanged="dgMember_SelectedIndexChanged" AutoGenerateColumns="False" OnDeleteCommand="dgMember_Delete">
<Columns>
<asp:ButtonColumn Text="Delete" ButtonType="PushButton" CommandName="Delete"></asp:ButtonColumn>
<asp:BoundColumn DataField="ID" HeaderText="ID">
<HeaderStyle font-bold="True" horizontalalign="Center" verticalalign="Middle"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Member No" HeaderText="Member No">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Surname" HeaderText="Surname">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Forenames" HeaderText="Forenames">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Known as" HeaderText="Known As">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Title" HeaderText="Title">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Address" HeaderText="Address">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Home Tel" HeaderText="Home Tel">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Email" HeaderText="Email">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="DOB" HeaderText="DOB">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="StartDate" HeaderText="StartDate">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="LeaveDate" HeaderText="LeaveDate">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="clubsoc" HeaderText="Club/Society">
<HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle>
</asp:BoundColumn>
</Columns>
</asp:DataGrid>
</p>
<p align="center">
</p>
<p align="left">
<br />
<br />
&nbsp;
</p>
<!-- Insert content here -->
</form>
</font>
</body>
</html>

View Replies !   View Related
Deleting Data After Replication.
I am looking for an opinion on the best way to delete all data from a table after it has been replicated.

The simple use case would be:



Data is replicated from production server to archive server.
Data is deleted from production serverThanks in advance...

View Replies !   View Related
I Have A Problem For Deleting Data
 

my row in my data base consists of 12 coulmns, i have a  duplicate values in 6 coulmns  of it , i use select distinct for all it doesnt work for it it doesnot remove dupliacte , i cant make primary key or any constraint on this 6 coulmns together cause it contains duplicate values even if i choose ignore duplicate it doesnot work
 
example
id      name       adress     telephone        job      gender    deprtment
1       john          dd            123             doctor     m         1st deprtment
1       john         dd             123             doctor     m            2nd deprtment
 
so there is duplicate on id, name,telephone, job , gender and different in department so how i make a primary key on id , name , adress , telephone , job , gender together
 
also how to delete duplicate from it although i used distinct function and it doesnit remove duplicate
 
thanks in advance

View Replies !   View Related
SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.
 
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?

ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "

 

And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.

 

Any help with this process?

View Replies !   View Related
Deleting Data By Comparing To Another Table
I have an entry form allowing customers to enter up to 15 skus (productid) at a time, so they can make a multiple order, instead of enteringone sku, then submitting it, then returing to the form to submit thesecond one, and so forth.From time to time, the sku they enter will be wrong, or discontiued, soit will not submit an order.Therefore, when they are done submitting their 15 skus through the orderform, I want a list showing them all of those skus that came back blank,or were not found in the database.I'm doing this by creating two tables. A shopping cart, which holds allthe skus that were returned, and a holding table, that holds all theskus that were submitted. I want to then delete all the skus in theholding page that match the skus in teh cart (because they are goodskus) which will then leave the unmatched skus in the holding table.I'll then scroll out the contents of the holding table, to show them theskus that were not found in the database.(confused yet?)So what I want to do is have some sql that will delete from the holdingtable where the sku = the sku in the cart. I've tried writing this, butit dosn't work.I tiried this delete from holding_table where sku = cart.skuI was hoping this would work, but it dosn't. Is there a way for me to dothis?Thanks!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !   View Related
Trouble Deleting Data In Table
I have a table where I want to delete some data from but I get this error.

You might have a record that has a foreign key value related to it, or you might have violated a check constraint.

What to do????

View Replies !   View Related
Data From The Table Deleting Automaticaly.
Hi gurus,

The data is automaticaly deleting from one perticular table at every night from last week onwords. I have created a delete trigger to find it out. But Nothing was recorded. There is no jobs except maintainance plans. Nothing in event viewer too. The database recovery model is simple. How can i solve this problem
Please advise me to solve this problem

Thanks
Krishna.

View Replies !   View Related
Deleting Orphaned Data - Maintenance
Hi,

I really don't know how frowned upon my approach is here, but it was the only way I have been able I've been able to do it.

On my application, when users delete their account, it sometimes brings the db server to a COMPLETE crawl. The reason is some users who delete have many years of related data, and when the data deletes with them, its very slow.

To avoid this I've taken off many contstraints, and I do have some sprocs that deleted orphaned data at night.

thoughts on this approach ?

View Replies !   View Related
Problem Deleting Duplicate Data
I have a table that contains more than 10,000 rows of
duplicate data.  The script below copies the data to a temp table then
deletes from the original table.  My problem is that after it runs, I now
have 122 rows of triplicate data (but dups are gone). If I rerun the script, it doesn't see the
triplicate data and returns 0 rows.  I've use three different versions of
delete dup row scripts with the same result.  There are no triggers or
constraints on the table, not even a primary key. What am I missing?-------------------------------------------------------------------

/**********************************************
Delete Duplicate Data
**********************************************/

--Create temp table to hold duplicate data
CREATE TABLE #tempduplicatedata
(
    [student_test_uniq] [bigint] NULL,
    [test_uniq] [int] NULL,
    [concept_id] [smallint] NULL,
    [test_id] [varchar](12) NULL,
    [questions_correct] [smallint] NULL,
    [questions_count] [smallint] NULL,
    [percentage_correct] [decimal](6, 3) NULL,
    [concept_response_count] [smallint] NULL
)

--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT * FROM crt_concept_score
GROUP BY student_test_uniq,
        test_uniq,
        concept_id,
        test_id,
        questions_correct,
        questions_count,
        percentage_correct,
        concept_response_count
HAVING COUNT(*) > 1

--Confirm number of dup rows
SELECT @@ROWCOUNT AS 'Number of Duplicate Rows'

--Delete dup from original table
DELETE FROM crt_concept_score
FROM crt_concept_score
INNER JOIN #tempduplicatedata
ON  crt_concept_score.student_test_uniq = #tempduplicatedata.student_test_uniq
AND crt_concept_score.test_uniq = #tempduplicatedata.test_uniq
AND crt_concept_score.concept_id = #tempduplicatedata.concept_id
AND crt_concept_score.test_id = #tempduplicatedata.test_id
AND crt_concept_score.questions_correct = #tempduplicatedata.questions_correct
AND crt_concept_score.questions_count = #tempduplicatedata.questions_count
AND crt_concept_score.percentage_correct = #tempduplicatedata.percentage_correct
AND crt_concept_score.concept_response_count = #tempduplicatedata.concept_response_count

--Insert the delete data back
INSERT INTO crt_concept_score
SELECT * FROM #tempduplicatedata

--Check for dup data.
SELECT * FROM crt_concept_score
GROUP BY student_test_uniq,
        test_uniq,
        concept_id,
        test_id,
        questions_correct,
        questions_count,
        percentage_correct,
        concept_response_count
HAVING COUNT(*) > 1

--Check table
-- SELECT * FROM crt_concept_score

--Drop temp table
DROP TABLE #tempduplicatedata
GO

View Replies !   View Related
Deleting Data Using Table Prefix
 

I can run a select to retrieve data using a prefix 'a' for the specific table involved. However when I try to run a delete using the same criteria it fails telling me

Msg 102, Level 15, State 1,.......Line 1

Incorrect syntax near 'a'

 

The Select statement looks like:

select count(*) from schema.table a where a.customer_id=1234

The Delete looks like:

delete from schema.table a where a.customer_id=1234

What am I doing wrong here? and how can I prefix the table, because the command I want to run is much more complicated than the example above and it needs the prefix

View Replies !   View Related
Deleting Data From Primary Table
 

Hi Everybody,
 
Kindly let me know if there is a way of deleting data from primary table without deleting data from its corresponding foreign key table.
 
Thanks & Regards
 
 

View Replies !   View Related
Deleting Data From Production Sever
 

Hi All,
I have to delete 135 Million records from our production server, keeping only last three months data & the table doesnot have any index.
 
Can you please suggest the best possible approach to perform this activity.
 
Approach i am thinking :-
 

1)I will rename the existing table & create a new table with the same name (By this my application wont be interrupted)
2)then i am planning to create a nonclustered index on the date column sort order desc so that i can get the last three months data.
3)Once the index is created i can transfer the required records to the new table created in step 1.
 
Please sugeest your valuable feedback or suggestions to perform the task ASAP.
 
Thanks
 
 
 

View Replies !   View Related
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). 

View Replies !   View Related
Deleting Using SqlDataAdapter Via A Data Access Layer
I've a management module (managing Products) currently being displayed on the aspx page using ObjectDataSource and GridView control.The datasource is taken from a class residing in my Data Access layer with custom methods such as getProducts() and deleteProduct(int productID)I'm currently using SqlDataAdapter as well as Datasets to manipulate the data and have no big problems so far.However, my issue is this, each time i delete a product using the deleteProduct method, I would need to refill the dataset to fill the dataset before i can proceed to delete the product. But since I already filled the dataset using the getProducts() method, is it possible to just use that dataset again so that I dont have to make it refill again? I need to know this cos my data might be alot in the future and refilling the dataset might slow down the performance. 1 public int deleteCompany(Object companyId)
2 {
3 SqlCommand deleteCommand = new SqlCommand("DELETE FROM pg_Company WHERE CompanyId = @companyId", getSqlConnection());
4
5 SqlParameter p1 = new SqlParameter("@companyId", SqlDbType.UniqueIdentifier);
6 p1.Value = (Guid)companyId;
7 p1.Direction = ParameterDirection.Input;
8
9 deleteCommand.Parameters.Add(p1);
10 dataAdapter.DeleteCommand = deleteCommand;
11
12 companyDS = getCompanies(); // <--- I need to refill this before I can delete, I would be iterating an empty ds.
13
14 try
15 {
16 foreach (DataRow row in companyDS.Tables["pg_Company"].Select(@"companyId = '" + companyId + "'"))
17 {
18 row.Delete();
19 }
20 return dataAdapter.Update(companyDS.Tables["pg_Company"]);
21 }
22 catch
23 {
24 return 0;
25 }
26 finally { }
27 }
I thank you in advance for any help here.

View Replies !   View Related
Stored Procedures For Inserting And Deleting Data
Hi, am new to sql server. Please some one send me some introduction abt stored procedures and some coding exammples to update and fetch the data from datasourece.
thanks.

View Replies !   View Related
Deleting Data By Calling The Stored Procedure In The .NET
Hi, does anyone know how to delete data from the SQL database by
calling the stored procedure in the Visual Basic.NET? Because I did the
Delete hyperlink bounded inside a datagrid. I have already displayed
the appointment date, time in the datagrid so I do not have to input
any values inside it. These are my stored procedures code for deleting:

ALTER PROCEDURE spCancelReservation(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9))
AS

BEGIN
IF NOT EXISTS
    (SELECT MemNRIC, AppDate, AppTime
    FROM DasAppointment
    WHERE (MemNRIC = @MemNRIC) AND (AppDate = @AppDate) AND (AppTime = @AppTime))
    RETURN -400

ELSE IF EXISTS
     (SELECT MemNRIC
    FROM DasAppointment   
    WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 10))
    RETURN -401
ELSE
    DELETE FROM DasAppointment
    WHERE MemNRIC = @MemNRIC

END

IF @@ERROR <> 0
    RETURN @@ERROR

RETURN

DECLARE @status int
EXEC @status = spCancelReservation '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @status

Can someone pls help? Thanks!

View Replies !   View Related
Deleting Extra Tempdb Log And Data Files
We had someone create an extra data file and log file for tempdb. Sowe currently have two data files and two log files. Is it possible todelete the newly created data and log files? If I just delete thephysical files, I assume they'll get created as soon as SQL Servergets started back up. Any help would be great, since a single dataand log file for tempdb is my goal.Thanks much.sean

View Replies !   View Related
Urgent! Please Help! Deleting Data From A Huge Table
I have a huge table with 4 primary keys on it. I need to delete the data from this table ( approx. 5.6 millions records to be deleted). It takes a hell lot of time to delete it by normal query.
Can someone please suggest me a better way?
Any help will be appreciated.

View Replies !   View Related
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?

View Replies !   View Related
Deleting Large Amount Of Data From The Table......
I need to delete data from a particular table which has more than half a million records. The data needs to be deleted is more than 200,000 records from the table. What is the best way to delete the data from the table other than importing into a temporary table and performing the same operation?

Let me know if the strategy to be followed is okay.

1. Drop all the triggers
2. Drop all the indexes
3. Write a procedure with a loop setting ROWCOUNT to 1000 and delete the records. ( since if I try to delete all the rows it will give timeout error )
The above procedure will delete 1000 records for each batch inside the loop till it wipes out all the data for the specified condition.
4. Recreate Indexes and Triggers.

Please let me know if there are any other optimal solution.

Thanx,
Zombie

View Replies !   View Related
Deleting A Data Viewer Causes BI Studio To Crash
Is there any way I can remove the data viewer (or even the whole data flow task) so that BIDS doesn't crash. Whenever I delete the data viewer, or the objects that the flow is linking to, or the data flow task, or even the sequence the data flow task is contained in, BIDs crashes.

thanks

View Replies !   View Related
Script To Check Free Data Size And Free Log Size In MS SQL 6.5
Dear all,

Is there any sql script to get the szie for free data space and free log space in MS SQL 6.5?

sp_spaceused and dbcc sqlperf (logspace) doesn't seem to provide the answer.

Thanks for yr help.

Rgds
Ung

View Replies !   View Related
BIDS/VS Shutdown While Deleting Breakpoints/data Viewers
Does anyone else have problems either deleting breakpoints or data viewers?

Every time I do this, BIDS shuts down. 

Thanks

BobP

View Replies !   View Related
Deleting Data In A Linked Table Is Not Supported By This ISAM.
Hi,
 
I am trying to delete a row in excel [Sheet1$], where this data in that row is used in a pivot table in same excel [Sheet2$] which should also get deleted, when i try to delete that row using "delete ... from [Sheet1$] " it is throwing an error message "Deleting data in a linked table is not supported by this ISAM. (Microsoft Office Access Database Engine)"
Can you please guide me in overcoming this error...........
 
Thanks in advance,
 
Warm Regards,
gchanduu

View Replies !   View Related
Deleting Data From Sql 2005 Table But No Resource Released
 

I'm Working in a Simple picture Gallery  On My web site.
When I add my pictures To the table Using Binary Writer and Delete
]

DELETE FROM [Photos]

WHERE [PhotoID] = @PhotoID
 
 It From My Table this Transact Delete the Pictures
but After some work I found That My database File size is increassing  day To day I'm very confused
so please tell me where is the problem ?

View Replies !   View Related
&&"can Decrease To Accomodate Contents&&" Not Working For Subsequent Pages?
This is an example of my data:
 
Col1                Col2                       Col3
1          LONGDESCRIPTION1        111
1          LONGDESCRIPTION1        222
1          LONGDESCRIPTION1        333
1          LONGDESCRIPTION1        444
2          LONGDESCRIPTION2        XXX
2          LONGDESCRIPTION2        YYY
2          LONGDESCRIPTION2        XYZ
2          LONGDESCRIPTION2        999
 
This is an example of what I want my report to look like (ill put row numbers to help identify)
 
Col1                Col2                       Col3              Row Num
1          LONGDESCRIPTION1        111                  1
                                                    222                  2
                                                    333                 3
                                                    444                  4
Subtotal                                       Col3Sum                                             (PAGEBREAK)
2          LONGDESCRIPTION2        XXX                  5
                                                    YYY                6
                                                    XYZ                  7
                                                    999                 8
Subtotal                                      Col3Sum
 
The Settings:
I'm currently using a single TABLE DETAIL ROW with HIDE DUPLICATES in Col1 and Col2.
The LONGDESCRIPTION field can be expected to take 2 row heights worth of space because it wont fit.
I've also got CAN INCREASE TO ACCOMODATE CONTENTS and CAN DECREASE TO ACCOMODATE CONTENTS selected.
 
The Problem:
When Printing/PrintPreviewing Page1, the Height of Rows 2/3/4 ARE THE CORRECT SIZE (only 1 row high, meaning that can decrease to accomodate contents works).
BUT when Printing/PrintPreviewing Page2, the Height of Rows 6/7/8 FOLLOW THE HEIGHT of ROW 5 (two rows high, meaning that can decrease to accomodate contents DOES NOT WORK).
 
Rows 2/3/4 has the same Row Height as Row 1 ONLY ON PAGES after Page1.
Rows 6/7/8 has the same Row Height as Row 5 ONLY ON PAGES after Page1.
 
Can decrease to accomodate contents does not seem to be working for Pages after page 1.
 
 
 

View Replies !   View Related
Data Used Bigger Than Data Size
One of our databases has the following sizes:

Data used : 4017.14MB (191.29%)
Indexes 35.48 mb (1.69%)
Unused : 5.27 MB (0.25 %)
Unreserved: -1957.89 mb (- 93.23 %)
Total Size 2100MB

looking at this the data size is 2100Mb and the data used is more at 4014.14MB.

How can I sort this out, do I simply need to create another device and expand the database or do I need to do something else?

Cheers

View Replies !   View Related
Data Used Bigger Than Data Size
One of our databases has the following sizes:

Data used : 4017.14MB (191.29%)
Indexes 35.48 mb (1.69%)
Unused : 5.27 MB (0.25 %)
Unreserved: -1957.89 mb (- 93.23 %)
Total Size 2100MB

looking at this the data size is 2100Mb and the data used is more at 4014.14MB.

How can I sort this out, do I simply need to create another device and expand the database or do I need to do something else?

Cheers

View Replies !   View Related
Sql Data Size Max
hi
in sql server i set the datastype en size of the column messagetext to nvarchar and max
but how do i set max in the code below cause i only can set an int in the place where the datasize has to be
objCmd.Parameters.Add("@MessageText", SqlDbType.NVarChar, 50).Value = txtbericht.Text
 
tnx
 

View Replies !   View Related
Original Data And Log Size
How can I find out the original size of the Data and Log files?TIARob

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved