Let's say I have the following table in SQL Server 2005: id | Date | Letter ----------------------------- 01 | 5/1/2009 | W 02 | 5/1/2009 | X 03 | 5/1/2009 | Y 04 | 5/1/2009 | Z 05 | 5/1/2009 | A 06 | 5/1/2009 | B 07 | 5/1/2009 | D 08 | 5/1/2009 | E 09 | 5/2/2009 | W 10 | 5/2/2009 | X 11 | 5/2/2009 | Y 12 | 5/2/2009 | Z 13 | 5/2/2009 | ...
I have a table with a column that I want to extract out and put into a separate table. For example, lets say I have a table named Contacts. Contacts has a column named Name which stores a string. Now I want to pull out the names into another table named Name and link the Contact.Name column to the Id of the Name table. I can only use ...
Consider a SQL Server 2005 database with a bunch of data on a lot of people, a large chunk of whom are married. To track relationships among people, a Relationship table exists that serves to link the ID of one person to the ID of another. A Relationship Type on each Relationship record serves to indicates the type of relationship exists...
I have some data id ref == ========== 1 3536757616 1 3536757617 1 3536757618 and want to get the result 1 3536757616/7/8 so essentially the data is aggregated on id, with the refs concatenated together, separated by a slash '/', but with any common prefix removed so if the data was like id ref == ========== 2...
I am going to create a DDL trigger to all databases in SQL Server instance. I'd like to do it in one run instead of many runs for each database. Below are the two T-SQL statements I need to execute: -- Create table use <dbname> GO CREATE TABLE dbo.ChangeAttempt (EventData xml NOT NULL, AttemptDate datetime NOT NULL DEFAULT GETDATE(),...
So I have a table that looks like this: Name ID TaskID HoursAssigned ---------------------------------------------------------------- John Smith 4592 A01 40 Matthew Jones 2863 A01 20 Jake Adams 1182 A01 100 Matth...
I created a batch script that calls a T-SQL file and output it to text file. The T-SQL file contains select statement of a table to get the information of column with XML as datatype. The file will then be sent with either contents as body of email or as an attachment. However, when I open the text file, I see a long list of information ...
I am working with a table where there are multiple rows that I need pivoted into columns. So the pivot is the perfect solution for this, and works well when all I need is one field. I am needing to return several fields based upon the pivot. Here is the pseudo code with specifics stripped out: SELECT field1, [1], [2], [3], [4] F...
What is Index Maintenance and how do I do it? How frequently do I have to do it? What are the benefits? This is related to a transaction table which is subject to frequently modifications; all DML operations will be run on that table. ...
In MSSQL I have a set of tasks which have start and end times. What I wish to do is collapse sequential tasks together. So my definition of sequential is TaskEndDate equals start of next TaskStartDate, there is no gap in time. In the below dataset, 21:00 to 21:40 is one sequence, then 22:00 to 22:20 & 23:20 to 00:00. TaskStartDate ...
Hi, I am having problems with a stored procedure that I am writing for SQL Server. I am calling a function within the stored procedure which returns a 2 column table with a value. This value I want to assign to a VARCHAR variable to be used throught the rest of my store procedure. DECLARE @TEAM VARCHAR(100) SET @TEAM = (SELECT DISTI...
Does anyone here know how i can determine the version of SQL running on my linked server through use of TSQL statements? I am running SQL2005 my linked servers are running a mix of sql2000, 2005 and 2008. ...
The code posted here is 'example' code, it's not production code. I've done this to make the problem I'm explaining readable / concise. Using code similar to that below, we're coming across a strange bug. After every INSERT the WHILE loop is stopped. table containst 100 rows, when the insert is done after 50 rows then the cursor sto...
I have the following function ALTER FUNCTION [dbo].[ActualWeightDIMS] ( -- Add the parameters for the function here @ActualWeight int, @Actual_Dims_Lenght int, @Actual_Dims_Width int, @Actual_Dims_Height int ) RETURNS varchar(50) AS BEGIN DECLARE @ActualWeightDIMS varchar(50); --Actual Weight IF (@ActualWeight...
I would like to replace (or remove) a newline character in a TSQL-string. Any Ideas? (UPDATE) The obvious REPLACE(@string,CHAR(13),'') just won't do it... ...
I've got the following tables (only key columns shown): Order OrderItem OrderItemDoc Document ======= =========== ============ ========== OrderId OrderItemId OrderItemId DocumentId --etc-- OrderId DocumentId --etc-- --etc-- I'm writing a stored procedure to 'clone' an Order (takes an exist...
How can you do a full outer join in sqlserver 2005? Seems like there is full outer join in sqlserver 2008 but I need to do this in sqlserver 2005. In other words, I am merging two views based on the ACCTNUM col in both views (The views show aggregates so there is at most one ACCTNUM record in each view for each account) and I would ...
As you can see, this sucks big time. Any alternative? I've tried using the column alias in the group by clause to no avail. select count(callid) , case when callDuration > 0 and callDuration < 30 then 1 when callDuration >= 30 and callDuration < 60 then 2 when callDuration >= 60 and callDuration < 120 then 3 ...
I know, I quite dislike the catch-all survey type questions, but I couldn't think of a better way to find out what I think I need to know. I'm very green in the world of database development, having only worked on a small number of projects that merely interacted with the database rather than having to actually create a new one from scra...
I have a temp table, that isn't going away. I want to see what is in the table to determine what perhaps bad data might be in there. How can I view the data in the temp table? I can see it in tempdb. I ran SELECT * FROM tempdb.dbo.sysobjects WHERE Name LIKE '#Return_Records%' To get the name of the table. I can see it's columns ...