Anil Chauhan writes "Hello experts,
I have a table in sql server 2000 which has over 94000 records.
I have to delete a record from table ,which record having a language other than english .
I need to clean the table by removing all the data which are in other language .
My main table has 12 fields .
Thanks in advance."
Hi,I have a database and want to store data in Spanish and English. Toaccompish this:1. Do i need to create separate tables for both the languages likeitems_en and items_sp?2. If I opt for the UTF16 charset what single collation setting can Iuse?Thanks and RegardsJackal Hunt
View 1 Replies View RelatedI have set up a full text search to handle multiple columns searching for chinese
But the result of the search isn't really what i have expected.
I have setup the catalog to have a chiense word break, and the columns in the tables are all nvachar
when i do something like
select * from dbo.Table_1 where contains(*, '"<chinese character>"',language 1082)
the search result is really inconsistent, especially with single characters.I have also checked that these characters are not in the noise filter file....
the search result is better when the input is more than a single characters, but still, somtimes it will not return any result at all.
so, I try to use the "like" statement instead of "contains" to do the search with the same inputs, and 100% of the time, it returns the correct result.
does anyone have any experience about things like that? coz I guess this is a more spcific issue with language. Is there any place that you guys know of, can offer me some help?
thank you in advance.
hye everyone,
i am new in reporting service and have question about :
if my table in database store the english words but want to display in chinese/ arabic on my report.
so..can the reporting service do like that..
any suggestion/tips or idea...
thanks in advance
thank you very much
We have an employee table that contains bank details and are experiencingproblems with account numbers being erased and lost. In order to track downwhy this is happening (either due to our application code or SQLreplication) we'd like to be able to prevent certain columns from beingdeleted if they already contain some data.Is it possible to setup a check constraint to prevent our ee_acct_no columnsfrom being set to NULL or blank strings if it contains an account number(i.e a 9 digit number)? We have setup the column to allow NULL's as we don'talways know employees bank details until later, so we do need to put them onour database without bank details initially.Also, if possible, can someone suggest a stored procedure or trigger i couldcreate that would fire a user-defined error message that would email anoperator if a bank account number changed?Many thanksDan Williams.
View 8 Replies View Related
SET identity_insert dbo.table1 on
GO
insert into dbo.table1(
PrimaryKeyCol,Col1, Col2 .....)
select
PrimaryKeyCol,Col1, Col2......
from [Sever].Database.dbo.table1 as ClientColumn
where not exists(
select * from dbo.table1 as ServerColumn
where ServerColumn.PrimaryKeyCol = ClientColumn.PrimaryKeyCol
)
DELETE FROM [Server].Database.dbo.table1
where exists(
where ServerColumn.PrimaryKeyCol = ClientColumn.PrimaryKeyCol
)
SET identity_insert dbo.table1 off
GO
I can't complie this code.. anybody see where I went wrong??
Thanks for all your help.
Could any one help me in
Writing code/query to export simple English text data from MSSQL Sever2000 to FoxPro2.6 dbf
aamir_net68@yahoo.com
We have been asked whether it is possible to completely delete sensitive data. When data is deleted it is not actually completely removed from the database but is marked as deleted. It is possible to zero out that data using sp_clean_db_free_space but this doesn't affect the transaction log.
We had hoped that CHECKPOINT would clear the data from the log but we are not completely certain of this. So the question becomes is there a built-in command or function in SQL Server 2008, or subsequent versions, that will completely remove deleted data, both from the transaction log and the database itself? Or is there a 3rd party tool to accomplish the same thing?
Hello,
I have not been able to locate information on the following problem. The first step I have in a packge (Execute SQL Command) is to delete the data from an MS Access database table. The package hangs at this step after all validation is complete. In the package, once the table data is deleted, it is repopulated in a later step. The deletion step and the repopulation step use the same connection manager.
There is no information in the log about an error. At the time the package ran, there was a lock file on the database with about six users connected. I'm not sure what version of Access the database was created in, but I have 2003 on my machine, and I cannot open the database.
Any ideas?
Thank you for your help!
cdun2
I tried Data Mining Add-Ins for Office 2007 - CTP December 2006.
Test settings: Windows XP SP2 english with Italian regional settings, Office 2007 english (RTM), SQL Server 2005 Developer (with SP2 CTP Dec06) and Data Mining Add-ins for Office 2007 (CTP Dec06).
If I keep regional settings in Italian, I get error like this:
Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
If I change regional settings to English, the Add-in works.
I found this description as the possible cause of the problem: http://msdn2.microsoft.com/en-us/library/ms178780(vs.80).aspx - if this is the issue, it would be necessary to change the ExcelLocale1033Attribute on the component.
Is there another workaround other than to install the Office 2007 MUI?
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Hi Experts:
We have several database linked via merge replications. Due to business requirements, we need to delete 5M rows in one table, we did it on one subscriber. However, the publisher kept uploading the deletion operations from the subscriber and blocked any downloading operation from publisher to subscriber. How can we acceralte the replications now as this has already operated in 2 days, and will continue 1-2 days? Is it possible to set the publisher take the downloading before uploading? How to speed up large amount data deletion operations in replication environment?
Thanks in advance!
Ron
Hi all,
i need to store russian data in sql server db but i found the russain data appeared as odd character in the table, can sql server support it or is there other solution for it? thanks!
During work with creating, updating or deleting objects in a database,
we use explicit name of the objects, for example
DROP PROCEDURE myProc.
By what the method can I avoid using the explicit name and get something like this:
DROP PROCEDURE @myProc or DROP PROCEDURE id_myProc?
Dear Friends,
I am using SQL server 7 with ASP. I have two working environment means one is korean and second it english.
- one Korean OS server have SQL server 7.0 and it is my database server
- second Korean OS server is only webserver
- English OS is win2k and it is only Web server.
1) When i used both Korean server as my webserver + database server then there is no problem to add Korean Data to SQL server On korean OS.
2) But when I try to user English OS server as my webserver and Korean Os server as my database server then I am not able to store Korean Data in Database server insted of it stored some mis/junk/acssi characters in database.
-- I allready try with Korean version of MDAC of English os
-- I also try with OEM feature in SQL server client network utility
-- When I am use CODEPAGE in my .ASP page then data storage work fine .. but at the time of getting it back there is problem.
If u need any more information about problem then let me know.
So please help me in this regards.
Thanx in advance
Anis Vora
Partner
Global SoftWeb Solutions
www.globalsoftweb.com
Hi
I have transfer data from Oracle to SQL Server the data contains english and some german charactor and the data in oracle has UTF-8 unicode format
Please tell me how i can import the data as SQL Server 2005 is not supporting UTF-8
Regards
S.S.Satyanarayana
can anyone help me to solve this problem
i have created a ssis package to load the data from excel file to the table, but we are getting the data in different language ie in french,english and in china after loading the data when we view the data it is showing as junk characters for chinese data but we are able to see other language data ie french and english.
so please tell me how to solve that
reply to my mail id(sandeep_shetty@mindtree.com)
Would the char or varchar datatype fieldname accept non-english letters like Japanees, Chinees, Russian..etc.
Or should I use nchar or nvarchar?
Thanks
Hi, Im installing a software which use SQL Server 2000 in english, but currently I have in all computers another program that use a ODBC for SQL 2000 in Spanish.If I install the components for the client of SQL in English then the another application got an error ODBC somehting related with the driver.
So there are any way to have a client for use SQL in spanish and english at the same time.So I mean I got two server one in spanish one in english, and just one computer client with twwo applications.
regards
and thank you
Hello all,We are developing an add-on for GoldMine, however my problem is ageneric SQL Server problem. The situation is this; we have a database,with collation set to SQL Latin. When we connect to database viaGoldmine, Enterprise Manager or Delphi (through ADO) we cannot seeTurkish characters. When I set SQL Server machine's "Language ForNon-Unicode Programs" setting to Turkish, GoldMine works fine, howeverthere is still problem in EM and Delphi.Does anybody know how to solve this issue?
View 7 Replies View RelatedHi,We are developing a small web interface to a local ERP software, whichuses SQL Server 2000 as database. The database uses SQL_Latin1_CP1collation, and the fields are varchar (not nvarchar), however, the mainprogram inserts and reads non-English (Turkish) characters into thesecolumns. However, when we connect to database with ADO.NET, thesecharacters are not read correctly. (The situation is same when I checktables with Enterprise Manager and Query Analyzer)In a past situation (which was about a Win32 application), I have heardabout character conversion behaviour of ADO (and many other DBlibraries) and solved that problem using BDE instead of ADO, so thatthe connection is made via DB-Library instead of OLEDB.But this way cannot be applied to my ASP.NET situation, and there is NOway to change database collation. Must I use a ADO.NET property, or useanother provider, or maybe another library? Any advices? Thanks...
View 4 Replies View RelatedHi all,
I have a table in xyz database and there is no column in table like creation_date or modified_date.
The problem is I want to delete records which has been added in the table before 1st jan 2007.
The size of table is 85 GB
Immediate help would be appriciable.
Regards,
Frozen
I have developed a tool to allow project developers to easily re-create the entire schema for our base product. The current issue involves setting the correct collation for the customers' region. Our brother company in Germany uses the same db creation tool and scripts, and we here in the US also have customers in South America. My ultimate question is "what subset of collation names would be necessary to provide the project developer?" I could query the database to get all the collation names, but I think it was around 1000 names. Can I query to get a smaller subset of the most relevant collation names?
Ed
Hi All,
I have this dumb problem with dates in my mobile application. The problem is when saving a short date in English format ex 20/08/2006 the sql server 200 windows CE Edition display an error stating
THERE IS AN ERROR IN THE DATEPART FORMAT. [,,,Expression,,]
When changing the date in American format the problem is solved ex 08/20/2006
The problem is that I require the dates in English Format only!
Is there anyway of solving my problem ?
Thanks
Matt
Hi,
I have purchased windows Srv Ent 2003 R2 English MVL license is it 32 bit or 64 bit? will it run on HP DL580 G4/(1) Dual-Core 64-bit Intel� Xeon�� 7110M Processor (2.60 GHz, 95 Watts, 800MHz FSB) ?
Pls reply urgently.
Thanks
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?
Thank you in advance.
It is an option to set deletion without getting logged since I have problem to delete two years historical data and would like to keep this year data on my 80MB rows. Actually I create a new table to get copy one-year data and I truncated the old table. I am wondering if there is other better way to do this task.
TIA,
Stella Liu
Hello there,
We are currently setting up out production server to the following requirements:
1. Every month, delete records that haven't been changed in the last 90 days.
2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse.
The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too.
Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem.
Thanks for your time,
Ulrik Rasmussen
While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.
View 1 Replies View RelatedIs there an option to find out the deleted DBs on a server?
------------------------
I think, therefore I am - Rene Descartes
table1 has a PK_tbl1 which matches FK_tbl1 in table2 and the relationship is one to one. Like this:
table1 has columns of UnitId, Code ...
table2 has columns of ItemID, UnitId ...
So if I need remove an item from table1 I must remove the matched one in table2 first.
delete from table2 where UnitId = xxx
delete from table1 where UnitId = xxx
Now I need create a trigger on table1 for deletion:
CREATE Trigger [dbo].[Table1_Delete]
ON [dbo].[Table1]
FOR DELETE
As
[code]....
Hi
I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.
Any help/direction here would be appreciated.
Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard
View 2 Replies View RelatedOk, so I have an issue, was wondering if anybody else has any suggestions.
I have a table that is pretty large, in all regards. It is a "message" table that holds text messages that users send to each other.
1. Has some data fields, integers, dates, some bit columns, a message subject field (varchar(250)), and a message body field (field type = text)
2. Table contains about 70 million records
3. Table has 6 indexes associated to it
4. Table has 2 views associated to it.
5. Table has 8 foreign keys associated to it.
I need to delete, oh, about 90,000 records out of this 70 million record table. I am able to disable the foreign keys to this table for deletion, but that does not seem to mitigate the problem. I think the issue lies with having to update the indexes as well as the views.
When I execute the select statement to retrieve the records I need to delete, it executes pretty quickly, no problems there that I can see.
The issue comes when I try to delete the records, it takes way too long, and we know it. We let it run for an hour and it didn't really get anywhere. This is in a server environment, some pretty decent hardware, 8gig memory, fast SCSI drives, 8 core processors, i don't know the exact specifics, but they're not bad.
Here's a DBCC SHOWCONTIG on our table
DBCC SHOWCONTIG scanning 'message' table...
Table: 'message' (1448040590); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 51602
- Extents Scanned..............................: 6486
- Extent Switches..............................: 6948
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 92.83% [6451:6949]
- Logical Scan Fragmentation ..................: 0.54%
- Extent Scan Fragmentation ...................: 0.93%
- Avg. Bytes Free per Page.....................: 93.5
- Avg. Page Density (full).....................: 98.85%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This is from our dev environment which is but a portion of our production db- but I presume our production environment will have similar percentages (not necessarily the pages scanned)
Any suggestions on how to delete records efficiently?