Hello, In my SQL 2008 database table, I have one column name AUTHOR that contains XML data. The XML is not well formed and has data like below <Author> <ID>172-32-1176</ID> <LastName>White</LastName> <FirstName>Johnson</FirstName> <Address> <Street>10932 Bigge Rd.</Street> <City>Menlo Park</City> <State>CA</State> </Address> </Author> ...
Hi, In a stored procedure in SQL Server 2008, I need to parse strings like "12M" and return 12 * 30 days as an int. So, I am basically parsing and calculating the number of days the string represents. I am not sure how this can be done. I am thinking to do a while loop over each character in the string. Any suggestion? Edit (not aut...
Hello, I have one table contains field named source with varchar(max). That field has following value <OutPatientMedication DateFormat="MM-dd-yyyy" MedicationName="lisinopril 10 mg oral tablet" Instructions="2 cap(s) orally once a day " Status="Active" Quantity="0" Refills="0" Prescrip...
My source is as follows OSPID OSPNAME RELATEDOSPID 100004 LEVEL4 100003 100003 LEVEL3 100002 100002 LEVEL2 100001 100001 LEVEL1 0 100009 LEVEL4 100008 100008 LEVEL2 100007 I need the result as L4OSPID L4OSPNAME L3RELATEDID L3OSPNAME L2RELATEDID L2OSPNAME L1RELATEDID L2OSPNAME ROOTNODE 100004 LEVEL4 100...
Sample Query: CREATE PROCEDURE dbo.Test (@p varchar(10)) AS DECLARE @param varchar(10) SET @param = @p + '%' SELECT * FROM table1 t1 INNER JOIN table2 t2 on t1.id = tr.id WHERE t2.desc LIKE @param I've a query which is similar to one above and when i use this in stored procedure it runs indefinitely without giving any output. But if ...
I have a column named Address in a table. I have values such as 12-15 Hardley Street 2A-C Hardley Street A-2c Hardley Street A-B Hardley Street I am required to keep the hyphen(-) intact in the first three rows. i.e. If there is a number in the word that contains the hyphen(-), I should keep it, else I should replace with...
I am new to this site, so sorry if I sound like a newb. I have found some good answers to stuff on this site, and I like it! Here is my current dilemma: I am storing a bigint value (for file sizes) in a table. I need to group on one column and for the filesizes (which are in bytes) I would like to have a column showing them by GB. T...
I work with data that comes from multiple sources that I have no control over. These sources tend to have duplicates in the "key" values. I need to keep any of these duplicate values form matching in a join. Using the following data T1 | ID | FirstKey | SecondKey | ThirdKey | AdditionalColumns | +----+----------+-----------+-------...
If you do a join that looks like this SELECT T1.KeyField1, T1.KeyField2, T2.Field3 FROM T1 JOIN T2 ON T1.KeyField1 = T2.KeyField1 AND T1.KeyField2 = T2.KeyField2 Is there a way to not allow NULLS to match similar to the results this query would return SELECT T1.KeyField1, T1.KeyField2, T2.Field3 FROM T1 JOIN T2 ON T1.KeyField1 = T2....
I have column with values that have wrong character ?. Now I want to change it to character b. For this I am using this statement: SELECT REPLACE(name,'?','b') from contacts; But when I do this nothing's happening, it return value with ?. What I am doing wrong? How I can replace this? ...
I am having problems returning a VARCHAR out of a derived column. Below are extremely simplified code examples. I have been able to do this before: SELECT *, message = CASE WHEN (status = 0) THEN 'aaa' END FROM products But when I introduce a Common Table Expression or Derived Table: WITH CTE_products AS (SELECT * from products) SE...
I have some T-SQL that generates a nice report giving a summary some stuff by month. I have 2 questions, is there a way to get the to sort the months by calendar order, not by alpha? And, what i would like to do is add a total line for each year, and a total line for the whole report? SELECT CASE WHEN tmpActivity.Year IS NULL THEN...
Hi, I'm trying to set a variable from a SQL query: declare @ModelID uniqueidentifer Select @ModelID = select modelid from models where areaid = 'South Coast' Obviously I'm not doing this right as it doesn't work. Can somebody suggest a solution? Thanks! ...
Hi, I have a table with column isprocessed bit null. I have created an index(non clustered) on this column. Before applying index query **`SELECT top 5000 * From Request Where IsProcessed Is Null`** took 30 to 40 seconds. Remember this is very huge table with rows over 10 million. Now after creating index on isprocessed column same q...
Hi. I have a SQL table Events (ID int, Event int, StartTime datetime, Duration int). Event is event code (1=system running, 2=break) Duration is the amount of seconds that the event was active. I'd like to get the amount of seconds that event 1 was active, but subtract the duration of event 2. E.g. event 1 was from 1:00 to 6:00, even...
Given that I have to use log shipping and have a lot of databases that need to be failed over does anyone have any T-SQL/Powershell code for MS SQL Server 2005 that can automate that failover process as much as possible? These are the steps it would need to cover (or as many as possible): Attempt to backup the transaction log one last...
I have two tables, lets say Table1(Col1,Col2,Col3,Col4) and Table2(Col1). I want to update some cols in Table1. For each row in Table1 where Col1 ends with '001' (assume all values are at least length 4) I want to: 1.place a random number from the set (10,20,30,40,50,60,70,80,90) in Col2. 2.place a random 9 digit number in Col3. ...
Hi all, How can i kill or rollback, uncommitted transaction? I am listing my active transactions with the following sql: SELECT * FROM sys.dm_tran_session_transactions My result is: session_id transaction_id transaction_descriptor enlist_count is_user_transaction is_local is_enlisted is_bound -------------------------------...
hi there Imagine I have this table BirthDay |Name 1-10-2010 | 'Joe' 2-10-2010 | 'Bob' 2-10-2010 | 'Alice' How can I get a result like this BirthDay |Name 1-10-2010 | 'Joe' 2-10-2010 | 'Bob', 'Alice tks ...
How do you make it so that all calculations in the DB compute to a pre-specified # of decimal places? Say I have three tables with the following fields Table1 A int B decimal(18, 3) Table2 A int B decimal (18, 2) C decimal (18, 3) Table3 A int Precision int Now I need to change it so that all my calculations are based on w...