I have a filed address in my database in MS Access. There are 8000 recods in the table. I want to remove all the city names from address field, as i have city name in city field. How can i delete these city names through SQL? *** Sent via Developersdex http://www.developersdex.com ...more >>

This is my 3rd posting on the subject. I'm trying to delete a Primary Key in a SQL database. Seems that there is a constraint blocking the deletion. I can't find any way to delete the key or the constraint? The following code works for all normal, unconstrained indexes: Imports smoIdx =...more >>

Hello all, We have a parent table (say ABC) and its child table(say XYZ). The sturctures are simple as shown below. ABC 1. abc_id 2. abc_name XYZ 1. xyz_id 2. xyz_name 3. abc_id_fk (foreign key relation with abc_id of abc table) So there is a foreign key in XYZ table. ON DELETE...more >>

Let's say I have two tables: TABLE1 (T1) pkID Field1 Field2 Field3 TABLE2 (T2) pkID fkID Field1 Field2 EffDate Now, what I want to SELECT is T1.Field1, T1.Field2, T1.Field3, MAX(T2.EffDate). In other words, I want to pull back the 3 Fields from T1 and then JOIN ...more >>

I am facing the following problem with deadlocks: I have written a set of stored procedures which invoices orders in a database. First some general explanation: There exists a table for orders and a table for order positions referencing the order table. So each order consists of one or many...more >>

My SQL knowlesge is fairly limited and although the folowing procedure returns the data i require in the format i need, there is a lot of duplicate code between the IF statements, these statements are only used as i need different WHERE clauses in the SELECT statement. Is there a simle was of ...more >>

Hi all, I have a SQL2K multi-table database in which I ignorantly used smallmoney as the datatype for various currency-related columns. I want to purify my soul of this mess. Three questions for the panel: 1. What should I convert to? Decimal(10,4)? 2.. Will I see a performance boost in s...more >>

I have two tables, each with a primary key. The primary key of the first table is set as an identity integer, so it will auto-assign a number. Together, the two primary keys define the one-to-one relationship that the tables have with one another. I'd like to build an EDITABLE view with fi...more >>

I have a few textboxes that take an address, city, state, zip. Once submitted the address gets geocoded and checked against a database of addresses in lat, long format. If the distance between addresses is less than a range given in another textbox then that address gets returned. I have code...more >>

Is it possible to do two aggregate calculations in a query using the same column twice? Here's my query: SELECT DealerCode, Year, Month, gvw_cd, COUNT(*) as TotalRegistrations FROM table1 WHERE gvw_cd > 4 GROUP BY DealerCode, Year, Month, gvw_cd ORDER BY DealerCode, Year DESC, Month DESC, ...more >>

I have the following convert statement in SQL 2000: Select CaseStudy = CONVERT(varchar(20), ROUND(CONVERT(decimal, DATEDIFF(day, StartDt, EndDt))/7, 1)) From tblCase And I am getting the following error message: Syntax error converting the varchar value '2.300000' to a column of data t...more >>

Hi, I want to implement the following scenario in one stored procedure, and I am wondering should I use Transaction in this: scenario: If found the same record (with same 'ID') in the table, it should just update that record; if no record are found, insert a new record. (n...more >>

Im getting the following error on an SQL query. if someone could help i'd appreciate it. These are the lines which seem to be causing the problem: WHERE Orders.OrderStatus = 'Ready to Ship' AND (Orders.PaymentAmount) <=249 AND Orders.ShippingMethodID <> 500 AND (OrderDetails.ProductWeight*Or...more >>

I ran sp_who and it show same spid with multiple updates. can any one explain what it means? BTW i am using execs spid ecid status dbname command 10 0 suspended DB100 UPDATE 10 3 runnable DB100 UPDATE thx ...more >>

Could someone show me how I can determine if a database is set to Trustworthy using T-SQL (programatically)? Thanks - Amos. ...more >>

Apologies in advance, this should be simple, but I'm frazzled... Here's the scenario: Employee tracking database, using transactions to show assignments with effective dates. I've got one who had an assignment to change locations, say on date 5/1/06. Then, on 6/1/06 she was reassigned /ba...more >>

Hi reposting, hoping to get a response, I am helping rewrite a process to help optimize a process that is causing deadlocks and is taking a long time. Let say you have a table like the following. Table1 ID ParentID field1 field2 field3 field4 field5 ..... field15 1 null ...more >>

Hi all, Where can I find a list of breaking changes from sql2000 sp4 to sql2005 sp1? I just installed SQL2005 SP1 and backed up my SQL2000 database and restored it to SQL2005. Everything went through fine. Now it would be nice if I could reference a document that will tell me what kinds of ...more >>

USE Master GO IF EXISTS ( SELECT * FROM SysDatabases WHERE Name = 'Imex' ) DROP DATABASE Imex GO CREATE DATABASE Imex GO USE Imex GO -- Create lots of tables / indexes, constraints, relationships, defaults, etc. -- Create lots and lots of stored procedures. -...more >>

Hello, the following statement is the model that I need to emulate for retrieving only one row from groups of duplicate rows (dedup statement): SELECT * FROM tbl1 t1 WHERE EXISTS (SELECT * FROM (SELECT TOP 1 * FROM tbl1 t2 WHERE t2.RecordId = t1.RecordId) x WHERE x.rownum = t1.rownum) ...more >>

Hi, is there a function that determines if a number is odd or even? thanks Ken ...more >>

In context of SQL Server 2000 As a developer, I LOVE this thing that Visual Studio calls a Database Project, where I can keep all the scripts that maintain objects in my database, and then they all plug into Visual SourceSafe, so I can keep version control history on say, a CREATE PROCEDURE...more >>

Hi, While I can run the following stmt. explicitly: --start DECLARE @iMiscItemId INT DECLARE @iPaymentItemId INT SELECT @iMiscItemId = TEMPLATE_ITEM_ID FROM T_GL_TEMPLATE_ITEM WHERE ITEM_NAME = 'MISC' SELECT @iPaymentItemId = TEMPLATE_ITEM_ID FROM T_GL_TEMPLATE_ITEM WHERE ITEM_NAME =...more >>

Hi! I am in the process of tuning a stored procedure and came accross something rather unusual. Basically, the number of logical reads against a table keeps on changing before and after I run Dbcc drop cleanbuffers even though the execution plan is completly identical. I understand that physica...more >>

Hi, I created a view which joins several tables on their id to get the name value in the view. For example, I have the « browser » table with its id and the name of the browser. The name of the browser appears in the view. I have many queries that search that view grouping by those ...more >>

Hi, This is driving me nuts, I have created a function which I can clearly see listed but when I call it from within a Stored Procedure it keeps telling me that it's an "Invalid Object Name", does anyone know why this is? I am using SQL Server 2000 Thanks PD...more >>

If someone has the time, can they verify that this is a well optimized Query. I am just looking for peer review. TIA -Matt- /*********************************************************** Step 1: Create the Main table. ***********************************************************/ IF OBJECT...more >>

i want to give password to mssql 7 database password because when i distrubuted mssql database that does not open by other computer use even though he use attach method plz tell me how i give security to my database thanks yogesh ...more >>

i've a SQL 2005 Everywhere table with and index that i cannot drop. this index is unique and non-clustered, but if i try to remove with an ALTER TABLE i get this error The foreign key constraint does not exist i tried with ALTER TABLE accesslog DROP CONSTRAINT pk_accesslog ALTER TABLE a...more >>

In SQL 2000 I can secure an underlying table by putting a view on top and granting select permissions on the view rather than the underlying table. This works as long as they objects are owned by the same user. Can that view be in a different physical database as long as it shares the same owner...more >>

Hi, Hope someone can help me out here, I am working with a database that someone else has desighedn and I have a field called (workingdays) and this is populated with a Bit Value i.e. 1 = Friday, 2 = Thursday, 4 = Wednesday, 8 = Tuesday, 16 = Monday, 32 = Saturday, 64 = Sunday or a total of...more >>

Hi, Does T-SQL allow using numbers to represent columns in the select list in the group by clause? For Eg. Select Col1, Col2, count(*) from Table1 group by Col1, Col2; Is it ok to write the above query as shown below? Select Col1, Col2, count(*) from Table1 group by 1,2; Thank...more >>

Hi. I've been looking for a way to access meta data of my SQL server 2000. But I can't seem to find certain information such as : - Database file size and location - Logfile size and location - Nbr of records in specific tables (select count(*) is too slow for my needs) The metadata availa...more >>

Tables: Encounter EncounterProvider Provider ProviderType EnounterProvider is the junction table. Having EncounterID,ProviderID, and ProviderTypeID. ProviderType could really be named better. But it has to do with the role the provider is playing at some point in that encounter. So we...more >>

NULL + ' ' + string as combinedstring the result would be NULL can NULL + ' ' + string as combinedstring = string ? cheers mark ...more >>

i know vb script got an function call instr. However, what function I should use in Stored Procedure ? thanks a lot ...more >>

Hi all. I need to create at physical table (and drop it at the end of the SP) during som calculations and use of Exec (sSQL) statement. I don't have "Create table permissions" on the user and I don't want to grant this. Is there anyway I can tell SQLServer that it is OK to create this table a...more >>