I'm writing an import utility that is using phone numbers as a unique key within the import. I need to check that the phone number does not already exist in my DB. The problem is that phone numbers in the DB could have things like dashes and parenthesis and possibly other things. I wrote a function to remove these things, the problem ...
If I have a table field named 'description', what would be the SQL (using MS SQL) to get a list of records of all distinct words used in this field. For example: If the table contains the following for the 'description' field: Record1 "The dog jumped over the fence." Record2 "The giant tripped on the fence." ... The SQL record outpu...
As a follow up to: http://stackoverflow.com/questions/105400/what-are-indexes-and-how-can-i-use-them-to-optimize-queries-in-my-database where I am attempting to learn about indexes, what columns are good index candidates?, specifically for an mssql database? After some googling, everything I have read suggests that columns that are gene...
We have a classic ASP application that simply works and we have been loathe to modify the code lest we invoke the wrath of some long-dead Greek gods. We recently had the requirement to add a feature to an application. The feature implementation is really just a database operation requires minimal change to the UI. I changed the UI and...
I want to add a column to an existing legacy database and write a procedure by which I can assign each record a different value. Something like add a column and autogenerate the data for it. Like, if I add a new column called "ID" (number) I want to then initialize a unique value to each of the records. So, my ID column will have record...
Assume a table structure of MyTable(KEY, datafield1, datafield2...) Often I want to either update an existing record, or insert a new record if it doesn't exist. essentially if (key exists) Run Update command ELSE run insert command What's the best performing way to write this? ...
Currently, I am developing a product that does fairly intensive calculations using MS SQL Server 2005. At a high level, the architecture of my product is based on the concept of "runs" where each time I do some analytics it gets stored in a series of run tables (~100 tables per run). The problem I'm having is that when the number of ru...
What is the best way (performance wise) to paginate results in SQL Server 2000, 2005, 2008 if you also want to get the total number of results (before paginating)? ...
I have just inherited a server application, however it seems that the only copy of the database is corrupt and the working version is gone, so is it possible to find what queries the application is running so I can try to rebuild the tables? Edit: I have some files with no extensions that I are named the same as the databases, IDK if th...
Obviously I can use BCP but here is the issue. If one of the records in a Batch have an invalid date I want to redirect that to a separate table/file/whatever, but keep the batch processing running. I don't think SSIS can be installed on the server which would have helped. ...
I'd like to be able to hook into a 3rd party application to see what SQL Statements are being executed. Specifically, it is a VB6 application running on SQL Server 2005. For example, when the application fills out a grid, I'd like to be able to see exactly what query produced that data. ...
I have an Interbase DB. How can I convert it to SQL Server? ...
Hi, I'm sorry if the question is long-winded and/or unclear. I will try and make it clearer with any feedback I get. I've got two tables: TableA ID, Name TableB ID, SomeColumn, TableA_ID (FK for TableA) The relationship is one row of TableA - many of TableB. Now, I want to see a result like this: ID Name SomeColumn 1....
Hi, I'm trying to create a UDF in SQL Server 2005 Express as below: CREATE FUNCTION [CombineValues] () RETURNS VARCHAR(8000) AS BEGIN DECLARE @CuisineList VARCHAR(8000); RETURN ( SELECT @CuisineList = COALESCE(@CuisineList + ', ', '') + CAST(Cuisine AS varchar(20)) FROM Cuisines ) END Cuisines has the str...
Is there a way to lock a row in the SQL 2005-2008 database without starting a transaction, so other processes cannot update the row until it is unlocked? ...
I am encountering very infrequent yet annoying SQL deadlocks on a .NET 2.0 webapp running on top of MS SQL Server 2005. In the past, we have been dealing with the SQL deadlocks in the very empirical way - basically tweaking the queries until it work. Yet, I found this approach very unsatisfactory: time consuming and unreliable. I would ...
Hi all, I have a table that contains tasks and I want to give these an explicit ordering based on the priority of the task. The only way I can think to do this is via an unique int column that indexes where the task is in term of the priority (i.e. 1 is top 1000 is low). The problem is that say I wanted to update task and set its prior...
The title says it all. I can connect to my SQL Server database via sqlcmd from a DOS command window, but not from a Cygwin window. From DOS: F:\Cygnus>sqlcmd -Q "select 'a test'" -S .\SQLEXPRESS a test (1 rows affected) F:\Cygnus> ==================================================== From Cygwin: $ sqlcmd -Q "select 'a...
I'm working on a database design for groups hierarchy used as the foundation of a larger system. Each group can contain other groups, and also 'devices' as leaf objects (nothing goes below device). The database being used is MS SQL 2005. (Though working in MS SQL 2000 would be a bonus; a solution requiring MS SQL 2008 is unfortunately n...
SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 ...