I'm having to do some data conversion, and I need to try to match up on names that are not a direct match on full name. I'd like to be able to take the full name field and break it up into first, middle and last name. The data does not include any prefixes or suffixes. The middle name is optional. The data is formatted 'First Middle La...
I'm working on a database that needs to represent computers and their users. Each computer can have multiple users and each user can be associated with multiple computers, so it's a classic many-to-many relationship. However, there also needs to be a concept of a "primary" user. I have to be able to join against the primary user to lis...
I have a SQL Server 2005 table like this: create table Taxonomy( CategoryId integer primary key, ParentCategoryId integer references Taxonomy(CategoryId), CategoryDescription varchar(50) ) with data looking like CategoryIdParentCategoryIdCategoryDescription 123nullfoo345123bar I'd like to query it into an xml document like this: ...
I need to create an ODBC link from an Access 2003 (Jet) database to a SQL Server hosted view which contains aliased field names containing periods such as: Seq.Group In the SQL source behind the view, the field names are encased in square brackets... SELECT Table._Group AS [Seq.Group] ...so SQL Server doesn't complain about creatin...
I have to write a component that re-creates SQL Server tables (structure and data) in an Oracle database. This component also has to take new data entered into the Oracle database and copy it back into SQL Server. Translating the data types from SQL Server to Oracle is not a problem. However, a critical difference between Oracle and S...
I recently had to rename a table (and a column and FK/PK contraints) in SQL Server 2000 without losing an data. There did not seem to be an obvious DDL T-SQL statements for performing this action, so I used sp_rename to directly fiddle with object names. Was this the only solution to the problem? (other, than give the table the correct ...
I know there is already a question about this but my issue is more oriented to remote scenarios. With net start/stop you can specify a /y parameter to bounce users off current sessions but you cannot start/stop remotely. With sc you can start/stop remotely but if the SQLServer instance is being used it won't let you stop the service (w...
The basic problem is like this: A subscriber has successfully replicated a row from the publisher, using transactional replication. Now, how do we keep track the time of this row being last successfully replicated? A friend has suggested the following solution, which he used for his SQL Server 2000: 1) Add a datetime column. 2) Change...
A query that is used to loop through 17 millions records to remove duplicates has been running now for about 16 hours and I wanted to know if the query is stopped right now if it will finalize the delete statements or if it has been deleting while running this query? Indeed, if I do stop it, does it finalize the deletes or rolls back? ...
I hit this error while my web application was trying to execute a SELECT INTO on a MSSQL 2005 database. I really have two questions: What does this error mean and how does this happen? Is there a way to prevent these errors by coding in a different way? ...
What level of CPU usage should be considered high for SQL Server? ie 80% 90% 100%? ...
This has been an adventure. I started with the looping duplicate query located in my previous question, but each loop would go over all 17 million records...meaning it would take weeks (just running *select count * from MyTable* takes my server 4:30 minutes => mssql 2005). I gleamed information from this site and at this post: http:/...
Hi why doesn't this work in SQL Server 2005? select HALID, count(HALID) as CH from Outages.FaultsInOutages where CH > 3 group by HALID I get invalid column name 'CH' ...
It's possible to create digrams in SQL Server 2000 that can be useful to show the relationships between tables. The problem we run into is that when somebody refreshes our development database, the diagrams get lost. We can load tables, stored procedures, views, etc. with SQL scripts, but we have to create the diagrams by hand. Is the...
I have a database with user 'dbo' that has a login name "domain\xzy". How do I change it from "domain\xzy" to "domain\abc". ...
I deleted millions of rows of old data from a production SQL database recently, and it didn't seem to shrink the size of the .MDF file much. We have a finite amount of disk space. I am wondering if there is anything else I can do to "tighten" the file (like something analogous to Access' Compact and Repair function)? ...
I have a number of stored procs which I would like to all run simultaneously on the server. Ideally all on the server without reliance on connections to an external client. What options are there to launch all these and have them run simultaneously (I don't even need to wait until all the processes are done to do additional work)? I h...
Does anyone know of a tool to migrate a Sqlite database to SQL Server(both the structure and data)? Thanks? ...
I have several databases where the transaction log (.LDF) is many times larger than the database file (.MDF). What can I do to automatically shrink these or keep them from getting so large? ...
In Oracle, I can re-create a view with a single statement, as shown here: CREATE OR REPLACE VIEW MY_VIEW AS SELECT SOME_FIELD FROM SOME_TABLE WHERE SOME_CONDITIONS As the syntax implies, this will drop the old view and re-create it with whatever definition I've given. Is there an equivalent in SQL Server 2005 that will do the same th...