Thursday, February 28, 2013

[T-SQL] converting Varchar details into nvarchar of my table

[T-SQL] converting Varchar details into nvarchar of my table

converting Varchar details into nvarchar of my table

Posted: 27 Feb 2013 09:29 PM PST

hai friends , i am create on table like create table wish( id int identity, name nvarchar(100), city varchar(100), comments nvarchar(max))insert into wish values('rama','chennai','happy birthdy')i ve inserted the wishes also in tamil telgu like hindhi unfortunately i m inserted value in web page in varchar mode enter the comments of tamil its diplay like ??????now i wanna retrive nvacrhar of tamil .....how to modify the data varchar data into nvarchar alrdy inserted

Grouping records by time interval

Posted: 27 Feb 2013 11:58 PM PST

Hi everyone,I have been searching for a few days now to try to solve a problem, but without success. What I did find on the net wasn't what I want.We have a large DB with a lot of tables. From those tables I have to use 2 tables in my query.The query has to group all the records from the table 'Tussen_Tickets' per time interval of 15 minutes based on the column Ticket_Closed_DateTime and group it also per Event_Channel which is a column in the table. The intervals can be found in the second table with the name 'Tijdsintervallen'. The result has also to represent all the intervals that can be found in the table 'Tijdsintervallen'. This means that per day all the 96 intervals have to be represented in the result. If no results are found for an interval in the table 'Tussen_Tickets', zero has to be filled in the columns DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold and Talk.The result has to have the following columns:Date (datetime), Interval from table Tijdsintervallen, and the columns Event_Channel, DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold and Talk from the table Tussen_tickets.I hope I'm clear and complete enough with my question and explanation.Who is willing to help me out with this query?In attachment you will find a text file to create the 2 test tables and fill them with data.Thank you very much for your help and assistance.Greetz,Geert

Is a doomed transaction inevitable if deadlock occurs in a trigger (SQL Server 2008 R2)?

Posted: 27 Feb 2013 01:37 AM PST

Hi all, Below are two scenarios, however, what I am trying to accomplish is to catch deadlock errors and re-try the DML that was chosen as the deadlock victim. Does anyone know of way to accomplish what I am trying to accomplish without a doomed transaction given the constraints? I have seen many posts that this appears to be a limitation of the dbengine, but am looking for confirmation. In both scenarios, I have a SProc - let's called it "X" - that is called mostly from within triggers (that performs some denormalized calculations and cannot be changed at this time) - in which deadlocks are often occurring. Scenario 1 •In SProc X, I want to catch any 1205 errors, and re-try the query that receives the 1205 error•However, whenever I re-try the query I receive an error that the transaction is "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction" Scenario 2 •In SProc Y, that runs DML statements that will fire triggers that call SProc X, I want to catch any 1205 errors, and re-try the query that receives the 1205 error•If SProc Y is called outside any transaction, I can begin a new transaction and rollback if a deadlock occurred and successfully retry the DML•However, if SProc Y is called inside a transaction, I need to perform a SAVE TRAN and rolback to the savepoint. Whenever I re-try the query I receive an error that the transaction is "The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.". For my logic to work, I cannot rollback the entire transaction, so SProc Y only performs as expected outside a transaction TIA, Dave

A complicated situation

Posted: 28 Feb 2013 12:07 AM PST

Hi,I am working on a project where the resistance to some antibiotics is studied. We have a database of patients checked for resistance. The result of test for each antibiotic comes back as either "1" for "not-resistant" and "2" for "resistant". A simplified table is something like this :[code="sql"]DECLARE @Test_TBL TABLE(ID VARCHAR(3), City VARCHAR(10),AntiBiotic VARCHAR(20), Result INT)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('1', 'Denver', 'AMP', 1)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('1', 'Denver', 'TET', 2)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('2', 'Denver', 'SPT', 2)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('3', 'New York', 'AMP', 2)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('3', 'New York', 'SPT', 2)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('4', 'Boston', 'AMP', 2)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('4', 'Boston', 'TET', 2)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('4', 'Boston', 'STR', 2)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('5', 'New York', 'AMP', 2)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('5', 'New York', 'STR', 2)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('6', 'Denver', 'TET', 2)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('6', 'Denver', 'SPT', 1)INSERT INTO @Test_TBL(ID,City,Antibiotic, Result) VALUES('6', 'Denver', 'STR', 2)[/code]Now, I need to make a report that looks like this :[img]http://apzec.ca/images/SQLServerCentral/capture.jpg[/img]I appreciate for any help.

How to concatenate group of rows

Posted: 27 Feb 2013 10:28 AM PST

Hi,I have a data structure as followed : [Name], [Task] and the data is :John, task1John, task2Joe, task1Joe, task3Joe, task4Jane, task1Jane, task4..........I wonder how to use the 'SELECT' to make the following result :John, task1;task2Joe, task1;task3;task4Jane, task1;task4.....Thanks for any help.

sql pivot problem with unknown number of records to columns.

Posted: 27 Feb 2013 06:23 PM PST

Hi Please help me out in this scenario.Table ID NAME 1 a 1 b 1 c 1 d 2 e 2 f 3 g 3 h 3 i 4 j 5 K 5 L 5 m 5 N 5 O 5 P required output id name1 name 2 name3 name4 name 5 name61 a b c 2 e f 3 g h i 4 j 5 k l m n o pi need a query which gives the out put

select rows into colums

Posted: 27 Feb 2013 10:19 PM PST

Dear T-sqlérs,I have a test table (see script below) with the following resultname lengthbalk1 7balk1 6balk1 9stof1 6stof2 6stof3 6stof4 6stof5 6stof5 7stof6 6stof7 6stof8 6stof9 6stof9 7stof10 6stof11 6stof12 6Now I would like the result to be like this:balk1 stof1 stof2 stof3 stof4 stof5 stof 6 stof 7 stof 8 stof 9 stof10 stof 11 stof 126 6 6 6 6 6 6 6 6 6 6 6 67 7 7 9 How should my query be?Thnx a lot in advance for your help :)[code]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[test]( [name] [varchar](50) NULL, [length] [int] NULL) ON [PRIMARY]GOinsert into test (name, length) values ('balk1', 7)insert into test (name, length) values ('balk1', 6)insert into test (name, length) values ('balk1', 9)insert into test (name, length) values ('stof1', 6)insert into test (name, length) values ('stof2', 6)insert into test (name, length) values ('stof3', 6)insert into test (name, length) values ('stof4', 6)insert into test (name, length) values ('stof5', 6)insert into test (name, length) values ('stof5', 7)insert into test (name, length) values ('stof6', 6)insert into test (name, length) values ('stof7', 6)insert into test (name, length) values ('stof8', 6)insert into test (name, length) values ('stof9', 6)insert into test (name, length) values ('stof9', 7)insert into test (name, length) values ('stof10', 6)insert into test (name, length) values ('stof11', 6)insert into test (name, length) values ('stof12', 6)select * from testSET ANSI_PADDING OFFGO[/code]

Calculation with aliases

Posted: 15 Feb 2013 12:35 AM PST

Hi,I have the following query:[code="sql']USE ICP;GODECLARE @beginDatum DatetimeDECLARE @eindDatum DatetimeSELECT T.Ticket_ID, UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime, DATEDIFF(second, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime) AS Tijdsduur, SUM(CONVERT(DECIMAL(4, 3), TA.Ticket_Action_Description)) AS WrapUp, SUM(CONVERT(DECIMAL(4, 3), TAA.Ticket_Action_Description)) AS OnHoldFROM Tickets AS T LEFT OUTER JOIN Application_Useraccounts AS UA ON T.Ticket_Accepted_Useraccount_ID = UA.Useraccount_ID LEFT OUTER JOIN Tickets_Actions AS TA ON T.Ticket_ID = TA.Ticket_ID AND TA.Ticket_Action_Type_ID = 3 LEFT OUTER JOIN Tickets_Actions AS TAA ON T.Ticket_ID = TAA.Ticket_ID AND TAA.Ticket_Action_Type_ID = 4 INNER JOIN Authorities AS A ON T.Authority_ID = A.Authority_IDWHERE (T.Ticket_Closed_DateTime BETWEEN @beginDatum AND DATEADD(day, 1, @eindDatum))GROUP BY T.Ticket_ID, UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTimeORDER BY UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime[/code]Now I need to add another column in the result based on a calculation using other aliases:Tijdsduur - WrapUp - OnHold AS TalkWhat's the best way to accomplish this?I tried following query, but that does not work:[code="sq'l"]USE ICP;GODECLARE @beginDatum DatetimeDECLARE @eindDatum DatetimeSELECT Tijdsduur, WrapUp, OnHold, Tijdsduur - WrapUp - OnHold AS Talk, T.Ticket_ID, UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTimeFROM (SELECT T.Ticket_ID, UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime, DATEDIFF(second, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime) AS Tijdsduur, SUM(CONVERT(DECIMAL(4, 3), TA.Ticket_Action_Description)) AS WrapUp, SUM(CONVERT(DECIMAL(4, 3), TAA.Ticket_Action_Description)) AS OnHold FROM Tickets AS T LEFT OUTER JOIN Application_Useraccounts AS UA ON T.Ticket_Accepted_Useraccount_ID = UA.Useraccount_ID LEFT OUTER JOIN Tickets_Actions AS TA ON T.Ticket_ID = TA.Ticket_ID AND TA.Ticket_Action_Type_ID = 3 LEFT OUTER JOIN Tickets_Actions AS TAA ON T.Ticket_ID = TAA.Ticket_ID AND TAA.Ticket_Action_Type_ID = 4 INNER JOIN Authorities AS A ON T.Authority_ID = A.Authority_ID) AS dtSumsWHERE (T.Ticket_Closed_DateTime BETWEEN @beginDatum AND DATEADD(day, 1, @eindDatum))GROUP BY T.Ticket_ID, UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTimeORDER BY UA.Useraccount_First_Name, UA.Useraccount_Last_Name, A.Description, T.Ticket_Accepted_DateTime, T.Ticket_Closed_DateTime[/code]

How to display Columns based on other table values

Posted: 27 Feb 2013 04:28 AM PST

i have below tables1) student (sno,name,subject,cost,city), 2) studentPT (sno,Ptname,EnrollDate)3) StudentDrill (Sno, DrillName,EnrollDate)now the requirement is want to display sno,name,cost,PTCol (if record exist(based on sno) in studentPt table then 'Yes' + EnrollDate otherwise 'Not Enrolled'), DrillCol (if record exist(based on sno) in studentDrill table then 'Yes' + EnrollDate otherwise 'Not Enrolled')how can i display those last two columsn based on rulesplease kindly help meThank you Very Much in AdvanceAsittii

How compare a list to a table

Posted: 27 Feb 2013 12:54 AM PST

Hi,I have a list of colors like this {White,Black,Green} and I have a table with this structure [Name];[Color];[IDX]. The data inside my table is :item1;Black;1item2;Green;2item3;Yellow;3item4;White;4........I wonder if it is possible to write a "SELECT" command to compare each row to the list and if the color is not included in the list, return a word like "Other". so the returned row from above will be like this :item1;Black;1item2;Green;2item3;Other;3item4;White;4........Currently, I fetch through table and compare each row to the list items and if the color is not in the list update the filed and replace it with "Other" however this procedure takes long and as my table record number is increasing, I need to find another way.Any help will be appreciated.Thanks
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog