Deletion Of Data Other Than English Language
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."
View Complete Forum Thread with Replies
Related Forum Messages:
Importing French Language Data Into English SQL
Using DTS to import French language data from Excel 5.0 into English SQL code page 850 some of accented vowels change for example 'e acute' goes to 'U acute' although most characters are OK. Is there a way of dealing with this automatically. Jim
View Replies !
Support For Spanish And English Language
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 Replies !
SQL Server 7 - Mulriple Language Support (English & Russian)
I have several discussion forums that are powered by O`Reilly`s Webboard and SQL 7 sitting on a Windows 2000 Advanced Server. I am having difficulty configuring SQL to support both English & Russian discussion forums. I have configured the regional settings to support Russian, and added Russian to the language cache, but am still unable to have SQL recognize Russian characters. I can`t configure Russian as the default language because I need to support English, French and Spanish boards on the same database. Any advice that you have is greatly appreciated. Thank you in advance for your help.
View Replies !
Full Text Search With Language Other Than English(ex Chinese, Japanese)
I 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.
View Replies !
Alert On Data Deletion
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 Replies !
How To Compare Data Before Deletion
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.
View Replies !
Deletion Old Data In Replication Environment
Hi to allI have a question about deletion of amount of data:My production environment is this one:- one publisher with a database (historycal events)- 50 subscribers with the prev database in unidirectional replicationunidirectional (from subscribers to publisher)My target was capturing events from the subscribers to send them topublisher (later I can do reports on it).Once the data is on the server i don't need them any more in subscribers.Now I would like to delete the oldest data (year 2003) of some table on thepublisher (remember that replication is unidirectional S->P).The tables contain about 6-7 millions of records.I delete one month per time. The process is about 30 minutes long and themerge agent subscribers changes in retry state.Can I use these queries to make faster this process? Eventually what kind ofproblems can I have ?DELETE FROM mydb WITH (PAGLOCK) WHERE mydb.dbo.mydate Between date1 anddate2orDELETE FROM mydb WITH (ROWLOCK) WHERE mydb.dbo.mydate Between date1 anddate2Thank you very much for your support.Marco
View Replies !
Cannot Import Non-English Data In MS SQL
Dear All, I have an Excel file containing data in English and some in Korean text. I want to import this to a table in MS SQL. I created a new DB with collation korean_wansung_ci_as and created the table first with normal varchar datatype, but on import the Korean text shows up as question mark characters (???). Can anyone suggest what I should do ? If I do an import in another MS SQL DB not using the specific collation the DTS wizard loads the korean data column as nvarchar (I guess it uses unicode) and is visible. I want to load the data in the native format but it doesn't work even though I use the right collation. Any idea what I am missing ? Regards, Azhar.
View Replies !
Data Deletion On MS Access Table Hangs
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
View Replies !
Large Amount Data Deletion Blocking Other Operations
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
View Replies !
Chinese BIG5 Data Being Stored In English Database
I'm currently trying to update a database with chinese data in it to a unicode database using visual basic as the conversion process. The old database is set as US english codepage but is being written to by a webpage with Chinese encoding. The Chinese data is stored in the database as a string of random characters, which if for example is copied to a html page and then encoded views fine. However I am now trying to move this chinese data into a Unicode database, which rather than displaying the correct chinese characters is showing the symbols exactly as they are in the database, even when veiwed using a utf8 webpage. Is there any way that I can convert the ansi characters to BIG5 encoding and then to Unicode or even better go from the ansi characters straight to the correct Unicode characters for the symbols they are representing? Any help gratefully received. Aelfric
View Replies !
Unable To Display Chinese Data From SQL Server English Version
Hi, I am unable to display chinese characters on my Windows 2000 chinese verion web server. My database sql server 7 english version using standard code page and the data type is nvarchar. I am able to display the data properly on windows 2000 english version web server. Anyone got any idea on how to resolve this problem. Please help Thanks Guys
View Replies !
Data Mining Add-Ins Doesn't Work With Non-English Regional Settings
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
View Replies !
Other Language Data In Db
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!
View Replies !
Data Definition Language
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?
View Replies !
Get Data Of SQL Server 2005 With ASP Language
Hi all,Im a "newbie" on ASP language and i need help to make a ASP Page, if someone can help me i apreciate. So ... what i want?I want that in a page with a Object (MSN Chat Control) that i past you the source code:1 <html> 2 3 <head> 4 <meta name="GENERATOR" content="Microsoft FrontPage 5.0"> 5 <meta name="ProgId" content="FrontPage.Editor.Document"> 6 <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> 7 <title>New Page 1</title> 8 </head> 9 10 <body> 11 12 <p> 13 <object classid="clsid:F58E1CEF-A068-4C15-BA5E-587CAF3EE8C6" id="MSNChatFrame1"> 14 <param name="_cx" value="19368"> 15 <param name="_cy" value="12330"> 16 <param name="BackColor" value="0"> 17 <param name="BackHighlightColor" value="0"> 18 <param name="TopBackHighlightColor" value="0"> 19 <param name="HexRoomName" value> 20 <param name="NickName" value> 21 <param name="RoomName" value> 22 <param name="Server" value> 23 <param name="ChatMode" value="0"> 24 <param name="BaseURL" value> 25 <param name="URLBack" value> 26 <param name="Category" value> 27 <param name="Topic" value> 28 <param name="WelcomeMsg" value> 29 <param name="CreateRoom" value> 30 <param name="ChatHome" value> 31 <param name="Locale" value> 32 <param name="ResDLL" value> 33 <param name="ButtonTextColor" value="33554432"> 34 <param name="ButtonBackColor" value="49668298"> 35 <param name="ButtonFrameColor" value="50305382"> 36 <param name="InputBorderColor" value="0"> 37 <param name="Feature" value="0"> 38 <param name="MessageOfTheDay" value> 39 <param name="AuditMessage" value> 40 <param name="ChannelLanguage" value> 41 <param name="InvitationCode" value> 42 <param name="NicknameToInvite" value> 43 <param name="MSNREGCookie" value> 44 <param name="PassportTicket" value> 45 <param name="PassportProfile" value> 46 <param name="CreationModes" value> 47 <param name="MSNProfile" value> 48 <param name="Market" value> 49 <param name="WhisperContent" value> 50 <param name="UserRole" value> 51 <param name="SubscriberInfo" value> 52 <param name="UpsellURL" value> 53 </object> 54 </p> 55 56 </body> 57 58 </html> 59 the page get the variable of Nick or the authentification as you prefer at line 20 in the SQL Server that i have already some registers of users. And if user dont is registered or dont have loggen on that the page go to the page of login that i have in http://msnportugal.no-ip.com/Forums/login.aspx?ReturnUrl=/The_Page_With_the_Object_that_i_need_helpCan someone help me to make that?Thanks alot,Neo
View Replies !
Differant Language OS And Data Storage In SQL Server
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
View Replies !
Loading The Different Language Data From Excel File To The Single Table
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)
View Replies !
Deletion
Hi 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
View Replies !
Restrict Deletion
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.
View Replies !
Deletion Of Duplicate Row
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 Replies !
Replication Without Deletion
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
View Replies !
Deletion Problem
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
View Replies !
Deletion Query
Ok, 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?
View Replies !
User Deletion Log SQL
Im using SQL enterprise manager v8, a few days ago I got a report that a user account was deleted. I was wondering what logs would point this out. I've been through the event review and i am not seeing any usefull info.
View Replies !
How To Prevent Db Deletion
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.
View Replies !
Database Deletion
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 Replies !
DB Deletion Time
Is there an option to find out the deleted DBs on a server? ------------------------ I think, therefore I am - Rene Descartes
View Replies !
Deletion And Identity Reset
Obviously to delete all records from DB table is simple, however, I would like to make my whole Live DB pretty much empty. I've copied all my data from my test DB over to my live DB (didn't mean to but I did). I would like to remove all the data and the identity values, resetting them back at their original values. Is there a simple way or do I have to do it the hard way. That being going in and removing Identity, saving and then placing identity back on the DB Table.
View Replies !
Recovering From Transaction Log Deletion In 6.5
I was trying to relocate my transaction log to a bigger drive usingsp_movedevice but I made a mistake in the syntax of the second parameterand put only the path, not the path and the file name.Now my database is marked as "suspect" and I get an error message in my logupon database start up saying that the log file cannot be open.Is there a way to have MS SQL 6.5 "forget" all the logs of this database,create new ones and restart the database? The logs contained nothingimportant, I had truncated them an hour or so before I made my mistake. Ijust want to make sure the data are still usable.When I look at the devices with sp_helpdevice, I can see a log that existand is hopefully in pristine condition and the one that doesn't existanymore.I looked in the archives of various newsgroups but couldn't find somethingthat correspond closely to my situation. I saw something similar but withMS SQL 7.0(http://groups.google.com/groups?hl=...om %26rnum%3D4)using sp_attach_db/sp_detach_db. What would be the equivalent with version6.5?Thanks!Charles--Charles-E. Nadeau Ph.Dhttp://radio.weblogs.com/0111823/
View Replies !
Database Still 'exists' After Deletion
hi Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again, then it says the database still exists, even it is physically destroied. ------Here is the errors: System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists. at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea n breakConnection) ------The evidence that the database doesn't exist physically: Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed. ------The code: /* * C# code to programmically create * database and table. It also inserts * data into the table. */ using System; using System.Collections.Generic; using System.Text; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.IO; namespace riskWizard { public class RiskWizard { // Sql private string connectionString; private SqlConnection connection; private SqlCommand command; // Database private string databaseName; private string currDatabasePath; private string database_mdf; private string database_ldf; public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf) { this.databaseName = databaseName; this.currDatabasePath = currDatabasePath; this.database_mdf = database_mdf; this.database_ldf = database_ldf; } private void executeSql(string sql) { // Create a connection connection = new SqlConnection(connectionString); // Open the connection. if (connection.State == ConnectionState.Open) connection.Close(); connection.ConnectionString = connectionString; connection.Open(); command = new SqlCommand(sql, connection); try { command.ExecuteNonQuery(); } catch (SqlException e) { Console.WriteLine(e.ToString()); } } public void createDatabase() { string database_data = databaseName + "_data"; string database_log = databaseName + "_log"; connectionString = "Data Source=.\SQLExpress;Initial Catalog=;Integrated Security=SSPI;"; string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY" + "(name=" + database_data + ",filename=" + database_mdf + ",size=3," + "maxsize=5,filegrowth=10%)log on" + "(name=" + database_log + ",filename=" + database_ldf + ",size=3," + "maxsize=20,filegrowth=1)"; executeSql(sql); } public void dropDatabase() { connectionString = "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;"; string sql = "DROP DATABASE " + databaseName; executeSql(sql); } // Create table. public void createTable(string tableName) { connectionString = "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;"; string sql = "CREATE TABLE " + tableName + "(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," + "name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)"; executeSql(sql); } // Insert data public void insertData(string tableName) { string sql; connectionString = "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;"; sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " + "VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') "; executeSql(sql); sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " + "VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') "; executeSql(sql); sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " + "VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') "; executeSql(sql); sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " + "VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') "; executeSql(sql); } public static void Main(String[] argv) { string databaseName = "riskDatabase"; string currDatabasePath = "E:\liveProgrammes\cSharpWorkplace\riskWizard\A pp_Data"; // Need to be more flexible. string database_mdf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.mdf'"; string database_ldf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.ldf'"; RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf); riskWizard.createDatabase(); riskWizard.createTable("userTable"); riskWizard.insertData("userTable"); //riskWizard.dropDatabase(); } } }
View Replies !
For Deletion..trigger Is Not Working
Hi, I have this trigger, it is working fine when i add new data but it doesn't work when I delete data from the table? Any idea? Any help will be highly appreciated. CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item] FOR INSERT, UPDATE, DELETE AS DECLARE @var_DB_contract INTEGER, @var_CQE INTEGER, @var_PC INTEGER, @var_item VARCHAR(7), @var_AMT_PAID INTEGER, @var_AMT_RET INTEGER, @var_ITEM_NEW VARCHAR(1), @var_quant DECIMAL, @var_fiyr INTEGER, @var_amt_result INTEGER, @var_amt_ret_result INTEGER, @var_amt_old INTEGER, @var_amt_ret_old INTEGER, @var_quant_result INTEGER, @var_quant_new INTEGER, @var_quant_old INTEGER, @Item_new VARCHAR(7), @var_chk varchar(1) --If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted) set @var_db_contract =(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) IF @var_db_contract IS NOT NULL BEGIN SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) set @var_chk ="Y" END ELSE BEGIN SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) set @var_chk="N" END SET @var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_amt_result =ISNULL(@var_amt_paid,0) - ISNULL(@var_amt_old,0) SET @var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_amt_ret_result = isnull(@var_amt_ret,0) - isnull(@var_amt_ret_old,0) SET @var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_quant_result = isnull(@var_quant_new,0) - isnull(@var_quant_old,0) SELECT @item_new = new_item FROM VALID_ITEM WHERE DB_CONTRACT = @var_db_contract AND PC_CODE = @var_PC AND ITEM_NO = @var_ITEM UPDATE ae_contract set amt_paid_contr = isnull(amt_paid_contr,0) +@var_amt_result, amt_ret_contr = isnull(amt_ret_contr,0) + @var_amt_ret_result where db_contract = @var_db_contract IF @item_new = 'N' BEGIN update vendor set used_amt = isnull(used_amt,0) + @var_amt_result + @var_amt_ret_result where db_vendor = (select gen_contr from ae_contract where ae_contract.db_contract=@var_db_contract); END UPDATE enc_det set amt_paid_fy = isnull(amt_paid_fy,0) + @var_amt_result, amt_ret_fy = isnull(amt_ret_fy,0) + @var_amt_ret_result where db_contract = @var_db_contract and pc_code = @var_pc and fy = @var_fiyr UPDATE valid_item set tamt_ret_item = isnull(tamt_ret_item,0) + @var_amt_ret_result, tamt_paid_item = isnull(tamt_paid_item,0) + @var_amt_result, qtd = isnull(qtd,0) + @var_quant_result where db_contract = @var_db_contract and pc_code = @var_pc and item_no = @var_item
View Replies !
Daily Deletion Of Records
Ladys, Gentlement, I have table that grows anywhere from 200,000 to 1,000,000 records perday. Besides that I need to keep at least 6 months historical data from this same table. The transaction log was purged after each batch when testing data monthly. I'm looking for some way of deleting just one day's data if it meets a criteria. It must remain within the 6 months period of historical data. This is what I've come up with so far" select * FROM dbo.Temp_table WHERE datediff(day, DATE_TIME, getdate()) >= 180 If it meets this criteria I can change the select to a delete? Please Let me know what you think
View Replies !
Deletion Of Duplicate Values
hi, i am trying to delete rows where a particular column (hours) has the same value for the same member (primary key) but where the effective dates are different. i want to delete the duplicate(s) rows which have the most recent effective date(s). can you help?
View Replies !
Table Deletion Error
hi i am using sql server 2005 express edition , with asp.net i am trying to delete a table programmatically a button on a form , if the client clicked it , then a table should be dropped . but always i get an error message , that says "cannot drop table <table name> , becaust it does not exist or you do not have premissions to do that" could any body help plz thax ghassan
View Replies !
Deletion Problem In Sql Table
i am using this statement for deleting a single row in sql table. "DELETE FROM Random WHERE NewID= '" & strwinner & "'" where "strwinner" is the variable which contains the row to be deleted. the problem is that when i check the table in sql the row which was supposed to be deleted is sitll there.it does not give me any error statement or something. iam executing this statement by using ExecuteNonQuery in my .aspx page. please help
View Replies !
English Query
How good is SQL Server English Query on a scale of 1 to 10? Secondquestion how hard is it to setup? Third question is, Is it going to beavailable and better in the next version of SQL Server?Thanks
View Replies !
Non-English Characters
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 Replies !
Non English Characters
Hi,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 Replies !
British English
I am actually auditing an environment that has a number of databases with British English as language and others using English. What is the impact of having different languages for the databases ?
View Replies !
One SQL In Spanish Another In English
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
View Replies !
Non-English Char
Would the char or varchar datatype fieldname accept non-english letters like Japanees, Chinees, Russian..etc. Or should I use nchar or nvarchar? Thanks
View Replies !
|
TRACKER
