InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 13:32:09 GMT20http://www.sqlservercentral.com/Forums/Topic1306041-392-1.aspxOk... this is just friggin wierd. We have a field called "ISDELETED". If the record is current the field is Null (or 0). When we query the table we use....SELECT *FROM TABLE_1 T (nolocks)WHERE ISNULL(ISDELETED,0) = 0Now... I didn't make the rules here... but... to eliminate the No Locks hint, and the ISDELETED predicate, they created a view so now I just query:SELECT *FROM vw_TABLE_1Very simple... However, I've been migrated some of the procedures to the view instead of being table table based and (GASP) I get more records. WTF(x) (that stands for What the Function)....Ok... to test the view I did this:SELECT count(*)FROM TABLE_1 T (nolocks)WHERE ISNULL(ISDELETED,0) = 0SELECT COUNT(*)FROM vw_TABLE_1answer, SAME NUMBER OF RECORDS!!!Ok.. so what if the count is the same, but the records aren't the same. I can see how that MIGHT cause a different result. So I did this:SELECT *FROM TABLE_1 T (nolocks)WHERE ISNULL(ISDELETED,0) = 0EXCEPTSELECT *FROM vw_TABLE_1RESULT... Empty recordset. So in other words, the results set from the view exactly matches the result set from the table. In the main query, I can switch back and form from the table to the view and the results are considtently different in the the same 6 records keep showing up for the View and not the table.OMG this is frustrating... Please, someone tell this simple geek what the Function is going on!ThanksCrusty.Thu, 24 May 2012 12:01:21 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1305108-392-1.aspxI need Status Update column like :1) All (both completed and pending i.e which went to Enrollment Analyst step’2) Pending 3) Complete Have Main table:Create Table [dbo].[tbl_doc_activity] (Acivity_id int not null identity(1,1),---identity columnEntry_id int ,Eventy_type_id int,Event_value varchar(1023), Event_time datetime,Username Varchar(200))-------And have EVENT LOOK UP TABLE...just giving this table for understanding--- CREATE TABLE [dbo].[event_look_up]( [Event_Description] [varchar](50) NULL, [Event_Type_Id] [int] NOT NULL) Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)values(1,'STATUS')Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)values(2,'FOLDERNAME') Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)values(3,'sys_Routing_Action') Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)values(4,'sys_WkfStep_Previous') Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)values(5,'sys_WkfStep_Current') Insert into [dbo].[event_look_up]( [Event_Type_Id], Event_description)values(6,'Pending ')---------------------------getting all records with current step is Enrollment Analyst-------------select et.Event_Description, da.* from dbo.tbl_doc_activity dainner join [dbo].[event_look_up] eton da.Event_Type_Id = et.Event_Type_Idwhere da.Event_Type_Id=5 and da.Event_Value like 'Enrollment Analyst'------Incomplete since it has no additional current_step after "Enrollment_analsyt" : so it is in pending-----Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)VALUES(107,1,'Imported','2012-05-12 13:49:03.740','ABD') Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)VALUES(107,5,'Enrollment Analyst','2012-05-12 13:49:03.757','ABD2') Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)VALUES(107,1,'Routing','2012-05-12 13:49:07.927','ABD3') Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)VALUES(107,3,'ENROLLMENT TECHNICIAN','2012-05-12 13:49:07.943','ABD4'--These are completed "Enrollment_analsyt_step as their previous step is "enrollment analyst"or we can say that it has additional current_step after "Current_step"=Enrollment_analsyt------select et.Event_Description, da.* from dbo.tbl_doc_activity dainner join [dbo].[event_look_up] eton da.Event_Type_Id = et.Event_Type_Idwhere da.Event_Type_Id=4 and da.Event_Value like 'Enrollment Analyst'Example:------Completed Enrollment_step since entry has previous step =EA------------------------Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)VALUES(108,1,'Routing','2012-05-18 02:03:51.887','ABD') Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)VALUES(108,5,'Enrollment Analyst','2012-05-18 02:03:55.560','ABD2') Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)VALUES(108,5,'Intake','2012-05-18 02:03:46.373','ABD3') Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)VALUES(108,3,'INDEXING','2012-05-18 10:29:01.463','ABD4') Insert INTO [dbo].[tbl_doc_activity](Entry_id,Eventy_type_id ,Event_value ,Event_time,Username)VALUES(108,4,'Enrollment Analyst','2012-05-18 10:53:06.597','ABD5')------I need to Create some status table like below in which i have: status which tells: --1) records which came out of enrollment_analyst step(completed) or whose previous_Step=enrollmet analsyt --2) records still in enrollment analsyt i.e they have no current_step after current_step=enrollment_analsyt-----------create Table #Status_of_Enrollment( Entry_id int, event_type_id int, Event_value varchar(30), Event_time varchar(30), last_updated_user varchar(40), Status varchar(40))My Final table should look like :Entry_id Event_type_id Event_value Event_time username Status108 4 Enrollment Analyst 2012-05-18 10:53:06.597 ABD5 Completed107 5 Enrollment Analyst 2012-05-18 02:03:55.560 ABD2 pendingI know what to do , but don’t know how to do???Any help help is appreciated.Wed, 23 May 2012 10:16:56 GMTkomal145http://www.sqlservercentral.com/Forums/Topic1297913-392-1.aspxHi,I have a query where the last left join is killing the performance. Someone suggested me to replace the last corelated subquery with a CTE but being a DBA and limited skills in T-SQL, I request help in this forum.Here is the query:CREATE VIEW [dbo].[vw_ScheduledReceipts] AS SELECT term.StateCode AS OriginState ,dest_ad .[State] AS DestinationState ,ImpExp = CASE WHEN term.StateCode=ord.DestState THEN 0 ELSE 1 END ,Carrier# = CASE WHEN ord.CarrierID >= 2000 THEN ord.CarrierID ELSE 999 END ,car.CarrierCode AS CarrierName ,car.Descr AS CarrierDesc ,vend2.FederalID AS CarrierFederalID ,term.Descr AS Origin ,LTRIM(RTRIM(dest_ad.[City]))+ ', ' + LTRIM(RTRIM(dest_ad.[state])) AS Destination ,sup.SupplierID AS Supplier# ,sup.Descr AS SupplierDesc ,vend.FederalID AS SupplierFederalID ,SupplierName = CASE WHEN vend.Descr IS NULL THEN sup.Descr ELSE vend.Descr END ,CAST(CONVERT(VARCHAR,ord.PostedDate,101) AS SMALLDATETIME) AS PostedDate ,MONTH(ord.PostedDate) AS [Month] ,DATEPART(Q,ord.PostedDate) AS [Qtr] ,YEAR(ord.PostedDate) AS [Year] ,ordproditm.BOL AS BOL# ,prod.ProdCategory AS ProductCategory ,[Type] = CASE WHEN prod.ProdCategory = 'X' THEN prod.ProdCategory + '-' + prod.ProdCode WHEN prod.ProdCategory = 'K' AND prod.ProdCode = '11' AND ord.DestState <> 'OH' THEN 'D' WHEN prod.ProdCategory = 'K' AND prod.ProdCode = '11' AND ord.DestState= 'OH' THEN 'K' WHEN prod.ProdCategory = 'D' AND prod.ProdCode = '87' THEN 'X-97' ELSE prod.ProdCategory END ,prod.ProdCode AS ProductCode ,prod.Descr AS ProductDesc ,shpprod.TaxExemptReason AS CustomerType ,supterm.PayFlag AS PayFlag ,SUM(CAST(ordproditm.GrossGallons AS DECIMAL(17,6))) AS SumOfGrossGallons ,SUM(CAST(ordproditm.NetGallons AS DECIMAL(17,6))) AS SumOfNetGallons ,SumOfBilledGallons = CASE WHEN ordprod.BillCode = 'N' THEN SUM(CAST(ordproditm.NetGallons AS DECIMAL(17,6))) WHEN ordprod.BillCode = 'G' THEN SUM(CAST(ordproditm.GrossGallons AS DECIMAL(17,6))) ELSE 0 END ,ordprod.BillCode AS BillCode ,cust.CustNum AS Customer# ,cust.CustName AS CustomerName ,term.TermCode AS TerminalCode ,dest_ad.County AS County ,cust.FEIN AS [CustomerFederalIdentification#] ,TerminalControl# = CASE WHEN ISNULL(tcn.TerminalNumber,'')='' THEN '' ELSE tcn.TerminalNumber END ,shpprod.StateBillByGN AS StateTaxGrossorNet , BillFreight = CASE WHEN CAST(shpprod.BillFreight AS INT)=1 THEN 'Y' ELSE 'N' END ,ord.DeliveryTicketNo AS DeliveryTicket# ,co.oaid as COA_CompanyID ,co.Name as CompanyName ,co.Descr as CompanyDescription ,co.Companyid as CompanyID FROM Orders ord INNER JOIN MOC_Replication.dbo.OrderProduct ordprod ON ordprod.OrderID = ord.OrderID INNER JOIN MOC_Replication.dbo.ShipToProduct shpprod ON shpprod.ShipToProdID = ordprod.ShipToProdID INNER JOIN MOC_Replication.dbo.ShipTo shpto ON shpto.ShipToID = ShpProd.ShipToID INNER JOIN MOC_Replication.dbo.Carrier car ON car.CarrierID = ord.CarrierID INNER JOIN MOC_Replication.dbo.OrderProdItem ordproditm ON ordproditm.OrderProdID = ordprod.OrderProdID INNER JOIN MOC_Replication.dbo.ShipToItem shpitm ON shpitm.ShipToProdID=ShpProd.ShipToProdID AND ordproditm.ShipToItemID = shpitm.ShipToItemID INNER JOIN MOC_Replication.dbo.Product prod ON prod.ProductID = shpitm.ProductID INNER JOIN MOC_Replication.dbo.Terminals term ON term.TerminalID = ordproditm.TerminalID INNER JOIN MOC_Replication.dbo.Customer cust ON cust.CustomerID = shpto.CustomerID INNER JOIN MOC_Replication.dbo.Company co ON co.Companyid = ord.CompanyID LEFT JOIN MOC_Replication.dbo.[Address] dest_ad ON dest_ad.addressid = shpto.addressid INNER JOIN MOC_Replication.dbo.Supplier sup ON sup.SupplierID = ordproditm.SupplierID LEFT JOIN MOC_Replication.dbo.SupplierTerminal supterm ON supterm.SupplierID=ordproditm.SupplierID AND supterm.TerminalID = ordproditm.TerminalID INNER JOIN MOC_Replication.dbo.Vendor vend ON vend.VendorID = sup.VendorID INNER JOIN MOC_Replication.dbo.vendor vend2 ON vend2.VendorID = car.VendorID LEFT OUTER JOIN MOC_DT.dbo.TCN_Xref tcn ON tcn.recid = (SELECT MIN(recid) FROM MOC_DT.dbo.TCN_Xref WHERE DsLocationCode = CAST(term.TerminalID AS VARCHAR) AND (sup.SupplierCode LIKE DsSupcode + '-%') ) WHERE (sup.SupplierID NOT BETWEEN 9993 AND 9999) AND ord.OrderStatus = 'P' GROUP BY term.StateCode ,dest_ad .[State] ,ord.DestState ,ord.CarrierID ,car.CarrierCode ,car.Descr ,vend2.FederalID ,term.Descr ,dest_ad.[City] ,dest_ad.[state] ,sup.SupplierID ,sup.Descr ,vend.FederalID ,vend.Descr ,ord.PostedDate ,ordproditm.BOL ,prod.ProdCategory ,prod.ProdCode ,prod.Descr ,shpprod.TaxExemptReason ,supterm.PayFlag ,ordprod.BillCode ,cust.CustNum ,cust.CustName ,term.TermCode ,dest_ad.county ,cust.FEIN ,tcn.TerminalNumber ,shpprod.StateBillByGN ,shpprod.BillFreight ,ord.DeliveryTicketNo ,co.oaid ,co.Name ,co.Descr ,co.Companyid Kindly let me know if it is possible to get rid of the last subquery. Forgive me if my question is incomplete.Thanks in advance!ChandanThu, 10 May 2012 08:24:47 GMTchandan_jha18http://www.sqlservercentral.com/Forums/Topic1305998-392-1.aspxI have a procedure on a server which can be called from several different linked servers. (To be precise, the server on which the procedure exists has been set up as a linked server on the various source servers.) Whenever it's called, I want to identify the calling system in a log without it being passed as a parameter (no spoofing allowed). @@REMSERVER appeared to be the answer to a maiden's prayer, but it has apparently been deprecated. Five minutes of searching MSDN and I can't find any sort of alternative. Suggestions?Thu, 24 May 2012 10:38:36 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic1305043-392-1.aspxPlease run below code ----------------------------set nocount on declare @role varchar(255),@dbname varchar(255) declare @rowid int declare @user nvarchar(255) declare @cmd varchar(8000)set @dbname = 'Mydatabasedashboardaaaaaaaaa1111'set @role = 'db_owner' -- Change database role name as appropriate set @user = 'domain\devgroup'select @cmd1 = 'USE '+@dbname+char(13)+'if exists ( select [name] from sys.database_principals where [name] = '''+@user+''')'+char(13)+'drop user '+'['+@user+']'+char(13)+'CREATE USER ['+@user+'] FOR LOGIN ['+@user+']'+char(13)+ 'EXEC sp_addrolemember '''+@role+''','''+@user+''''+char(13) from sys.server_principals where 1 =1select @cmd-------------------------Output: /*USE Mydatabasedashboardaaaaaaaaa1111if exists ( select [name] from sys.database_principals where [name] = 'domain\devgroup')drop user [domain\devgroup]CREATE USER [domain\devgroup] FOR LOGIN [domain\devgroup][b]EXEC sp_addrolemember 'db_owner','domain\de[/b] The output is being truncated in last line even after declaring @cmd with varchar(8000) and nvarchar(max).I might be doing something stupid ,Can you please help me with this issue?*/Wed, 23 May 2012 09:25:02 GMTsqlbee19http://www.sqlservercentral.com/Forums/Topic1305988-392-1.aspxNot a very succint title, wan't sure how to put it ... basically, our monitoring team are putting together a module to scrape the event log for SQL errors I have a list of the error numbers I want to look for from sysmessages and have confirmed that they would get written to the event log.However, they need an example of each message I want to scrape for, to be raised as an error in the event log in order that they can configure whatever it is that they're making!Is there a way I can force each of the selected events in sysmessages to write itself out to the error log? specifics like database name are not necessary. I think I need to look at RaiseError, but not too sure where to start :-PThu, 24 May 2012 10:14:40 GMTN.Dhttp://www.sqlservercentral.com/Forums/Topic1304665-392-1.aspx This function gets the score up to now for students (we are using categories and some of the categories have deletelowest to specify drop lowest of assessments in this category. Thanks in advanceALTER FUNCTION [dbo].[fn_UpToNowPercentage_dropL_table] ( @SectionID int, @StudentID int) RETURNS tableAS return( select SUM( (catweight*totalscore*1.0)/(case when TotalMaxPoints =0 then 1 else TotalMaxPoints end))*100.0/SUM(catweight) as perc1 from ( select catweight,assessmentcategory, sum(score) as totalscore,sum( (case when gradestatus='C' then 0 else maxpoint end)) as totalmaxpoints from (select AP.AssessmentID, GradeStatus, A.points as maxpoint, AP.points as score, ROW_NUMBER() over( partition by studentid,A.sectionid,Assessmentcategory order by (ap.points*1.0/a.points)) as rownumber , assessmentcategory, CatWeight, deletelowestfrom GradeBook.Assessment A inner join GradeBook.AssessmentPoint AP on A.AssessmentID=AP.assessmentid inner join GradeBook.GBCategory GC on A.AssessmentCategory=GC.CategoryID where A.SectionID=@sectionid and studentid=@studentid and a.points >0 and ap.points is not null and a.deleted=0 and ap.points >=0 )S where S.rownumber >deletelowest group by assessmentcategory,catweight) T Tue, 22 May 2012 21:32:35 GMTSarsourahttp://www.sqlservercentral.com/Forums/Topic1305904-392-1.aspxI have two queries I execute on a table. For Query1, the index would be a combination of Columns - A, B, C, D, E, F, GFor Query2, the index would be a combination of Columns - A, B, C, D, E, H, IWould like to know the optimised way of creating indexes on the table which hold for both queries. How should it be done?ThanksThu, 24 May 2012 08:33:13 GMTadit04http://www.sqlservercentral.com/Forums/Topic1303701-392-1.aspxHi,I found this code made by someone else in the company (he does not work here anymore) and it seems to me it has some unnecesary elements. Here is the code:[code="sql"]SELECT CAST('1900-01-01' AS smalldatetime) AS RecordAdded, 'Import New Submissions' AS LabelUNIONSELECT RecordAdded, LabelFROM (SELECT DISTINCT TOP 100 PERCENT RecordAdded, CAST(RecordAdded AS varchar(20)) AS Label FROM Mytable ORDER BY RecordAdded DESC) AS derivedtbl_1[/code]And I figured that it would be better to rewrite it as this:[code="sql"]SELECT CAST('1900-01-01' AS smalldatetime) AS RecordAdded, 'Import New Submissions' AS LabelUNIONSELECT DISTINCT RecordAdded, CAST(RecordAdded AS varchar(20)) AS LabelFROM MytableORDER BY RecordAdded DESC[/code]Is there any reason I should leave the subquery and the top 100 percent? It just seems to me that it's worthless and resource consuming.Note: RecordAdded is a nullable smalldatetime column that shows when was the record added to the table.Mon, 21 May 2012 14:03:17 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1304765-392-1.aspxHi, I am working on a GPS related issue. There is a table where we keep geographical information of vehicles tracked using GPS devices. This devise will be sending data to the server every second. So we have a huge dump of data in the tables. There is a table for each day. Table structure is :TabsenPK IntDateTimestamp BigInt ( We keep in UTC format which is managed by JAVA)Longitude Decimal(16,9)Lattitude Decimal(16,9)Field1 IntField2 IntField3 IntField4 Int ( Field1 to Field4 are used to keep few details )I need to delete all records from this table where DateTimeStamp is prior to a passed date. This will come to millions of records. What is the best method to delete this records with least performance impact ? How can I implement a batch based removal ?Wed, 23 May 2012 02:07:44 GMTsanujsshttp://www.sqlservercentral.com/Forums/Topic1305137-392-1.aspxGooood day experts!Can anyone help me with this case statement please? The part I'm having trouble with is the very last one when the parameter @isActive = 9.Sample[code="sql"]CREATE TABLE #tmp( [Id] [varchar](15) NOT NULL, [Order] [int] NOT NULL, [IsActive] [bit] NOT NULL, [Cnt] [int] NULL)INSERT INTO #tmp VALUES ('WAG-3218-UK',1,0,1)INSERT INTO #tmp VALUES ('WAG-3218-UK',2,0,2)INSERT INTO #tmp VALUES ('WAG-3218-UK',3,1,6)INSERT INTO #tmp VALUES ('WAG-3218-UK',4,1,6)INSERT INTO #tmp VALUES ('WAG-3218-UK',5,0,9)INSERT INTO #tmp VALUES ('WAG-3218-UK',6,0,7)INSERT INTO #tmp VALUES ('WAG-3218-UK',7,1,3)INSERT INTO #tmp VALUES ('WAG-3218-UK',8,0,3)INSERT INTO #tmp VALUES ('WAG-3218-UK',9,1,8)INSERT INTO #tmp VALUES ('WAG-3218-UK',10,0,6)[/code]Expected[code="sql"]-- If param @isActive = 0SELECT 'WAG-3218-UK' as [Id] ,1 as [Order], 0 AS [IsActive], 1 AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,2 as [Order], 0 AS [IsActive], 2 AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,5 as [Order], 0 AS [IsActive], 9 AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,6 as [Order], 0 AS [IsActive], 7 AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,8 as [Order], 0 AS [IsActive], 3 AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,10 as [Order], 0 AS [IsActive], 6 AS InactiveCnt, 'N/A' AS ActiveCnt-- If param @isActive = 1SELECT 'WAG-3218-UK' as [Id] ,3 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, 6 AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,4 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, 6 AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,7 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, 3 AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,9 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, 8 AS ActiveCnt-- If param @isActive = 9SELECT 'WAG-3218-UK' as [Id] ,1 as [Order], 0 AS [IsActive], '1' AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,2 as [Order], 0 AS [IsActive], '2' AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,5 as [Order], 0 AS [IsActive], '9' AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,6 as [Order], 0 AS [IsActive], '7' AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,8 as [Order], 0 AS [IsActive], '3' AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,10 as [Order], 0 AS [IsActive], '6' AS InactiveCnt, 'N/A' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,3 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, '6' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,4 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, '6' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,7 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, '3' AS ActiveCntUNIONSELECT 'WAG-3218-UK' as [Id] ,9 as [Order], 1 AS [IsActive], 'N/A' AS InactiveCnt, '8' AS ActiveCnt[/code]cheers.Wed, 23 May 2012 10:36:34 GMTMr. Holiohttp://www.sqlservercentral.com/Forums/Topic1305474-392-1.aspx500+ posts and I finally get to start my second thread! Hopefully I'll get a better response on this one that I did on my first.Fumbling around as I am wont to do with XML the other day, I ran across some XML that I simply couldn't shred the way I wanted to.Here's the setup data:[code="sql"]DECLARE @t TABLE (ID INT IDENTITY, key1 CHAR(10), val1 VARCHAR(50) ,val2 VARCHAR(50), val3 VARCHAR(50))INSERT INTO @tSELECT 'REC01', 'VALUE 01A', 'VALUE 02A', 'VALUE 03A'UNION ALL SELECT 'REC02', 'VALUE 01B', 'VALUE 02B', 'VALUE 03B'UNION ALL SELECT 'REC03', 'VALUE 01C', 'VALUE 02C', 'VALUE 03C'UNION ALL SELECT 'REC04', 'VALUE 01D', 'VALUE 02D', 'VALUE 03D'DECLARE @xml1 XML, @xml2 XMLSELECT @xml1 = (SELECT * FROM @t FOR XML PATH(''))SELECT @xml1[/code]This produces XML that looks as follows:[code="plain"]<ID>1</ID><key1>REC01 </key1><val1>VALUE 01A</val1><val2>VALUE 02A</val2><val3>VALUE 03A</val3><ID>2</ID><key1>REC02 </key1><val1>VALUE 01B</val1><val2>VALUE 02B</val2><val3>VALUE 03B</val3><ID>3</ID><key1>REC03 </key1><val1>VALUE 01C</val1><val2>VALUE 02C</val2><val3>VALUE 03C</val3><ID>4</ID><key1>REC04 </key1><val1>VALUE 01D</val1><val2>VALUE 02D</val2><val3>VALUE 03D</val3>[/code]I am able to extract any of the records individually as follows:[code="sql"]-- Read first record SELECT row.value('ID[1]', 'INT') as ID ,row.value('key1[1]', 'CHAR(10)') as key1 ,row.value('val1[1]', 'VARCHAR(50)') as var1 ,row.value('val2[1]', 'VARCHAR(50)') as var2 ,row.value('val3[1]', 'VARCHAR(50)') as var3 FROM (SELECT @xml1 AS XML1) x CROSS APPLY XML1.nodes('.') t(row)-- Read second recordSELECT row.value('ID[2]', 'INT') as ID ,row.value('key1[2]', 'CHAR(10)') as key1 ,row.value('val1[2]', 'VARCHAR(50)') as var1 ,row.value('val2[2]', 'VARCHAR(50)') as var2 ,row.value('val3[2]', 'VARCHAR(50)') as var3 FROM (SELECT @xml1 AS XML1) x CROSS APPLY XML1.nodes('.') t(row)[/code]I am not able to extract all 4 records into my result set. Of course, I can if I format the XML differently, for example like this:[code="sql"]SELECT @xml2 = (SELECT * FROM @t FOR XML PATH('IDS'))--SELECT @xml2 = CAST('<IDS>' + CAST(@xml1 AS VARCHAR(MAX)) + '</IDS>' AS XML)SELECT @xml2SELECT row.value('ID[1]', 'INT') as ID ,row.value('key1[1]', 'CHAR(10)') as key1 ,row.value('val1[1]', 'VARCHAR(50)') as var1 ,row.value('val2[1]', 'VARCHAR(50)') as var2 ,row.value('val3[1]', 'VARCHAR(50)') as var3 FROM (SELECT @xml2 AS XML2) x CROSS APPLY XML2.nodes('/IDS') t(row)[/code]Any XML experts out there with a clue to this one?Wed, 23 May 2012 19:37:15 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1305446-392-1.aspxHello All,I have a vendor provided stored procedure that is essentially a calculation engine. I can pass this procedure two parameters: an identifier and a month-end date. The calculation generated is an annual (12 month) calculation describing the previous year prior to the month-end date parameter. I need to extract historical data out of this system so I intend to pass a data set to a cursor and then do the INSERT INTO EXEC myProc thing.My companies have a start date. For each company I want to generate a series of records starting with the previous month-end date (based on current date) and going back to one year after the companies start date.Example. Say my start date is 1/3/2011. The first full month after the start date would be February 2011. I would want to generate three dates: 4/30/2012, 3/31/2012 and 2/29/2012.I would not want 1/31/2012 because subtracting one year from 1/31/2012 would give me 1/31/2011 which is before the first full month after the start date.I hope this is clear. None of my logic in this project is holiday or weekend based and I don't have a calendar so I'd prefer a solution without oneThanks if you want to help.CREATE TABLE #Companies(Company varchar(1),StartDate datetime)INSERT INTO #CompaniesSELECT 'A',' 4/3/2009' UNIONSELECT 'B',' 3/31/2010' UNION SELECT 'C',' 1/1/2009'SELECT * FROM #CompaniesDROP TABLE #CompaniesExpected results:A 5/31/2010A 6/30/2010A 7/31/2010A 8/31/2010A 9/30/2010A 10/31/2010A 11/30/2010A 12/31/2010A 1/31/2011A 2/28/2011A 3/31/2011A 4/30/2011A 5/31/2011A 6/30/2011A 7/31/2011A 8/31/2011A 9/30/2011A 10/31/2011A 11/30/2011A 12/31/2011A 1/31/2012A 2/29/2012A 3/31/2012A 4/30/2012B 4/30/2011B 5/31/2011B 6/30/2011B 7/31/2011B 8/31/2011B 9/30/2011B 10/31/2011B 11/30/2011B 12/31/2011B 1/31/2012B 2/29/2012B 3/31/2012B 4/30/2012C 2/28/2010C 3/31/2010C 4/30/2010C 5/31/2010C 6/30/2010C 7/31/2010C 8/31/2010C 9/30/2010C 10/31/2010C 11/30/2010C 12/31/2010C 1/31/2011C 2/28/2011C 3/31/2011C 4/30/2011C 5/31/2011C 6/30/2011C 7/31/2011C 8/31/2011C 9/30/2011C 10/31/2011C 11/30/2011C 12/31/2011C 1/31/2012C 2/29/2012C 3/31/2012C 4/30/2012 Wed, 23 May 2012 17:27:06 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic1305419-392-1.aspxOK, I've been poking at this for days and can't figure out why its not working.SQL Server 2008 R2I have a number of examples where I use Insert Into with an EXEC, e.g. [code="sql"] INSERT INTO @jobs EXEC master.dbo.xp_cmdshell @root_dir[/code]This works perfectly. Rows are inserted into @jobs. Never fails.However, when trying to do this with a particular procedure that I've created, I never get records returned. Nor do I get an error message. If I just call it, it returns two rows, never fails, no errors.[code]EXEC Work.dbo.P_UTIL_LIST_FILES @root_dir = 'C:\DataFiles' , @filename_filter = 'AccountFile'[/code]This, however, inserts zero rows in #Files. No error, just no data.[code="sql"]INSERT INTO #files (FileFullName, FileSize, DateCreated)EXEC Work.dbo.P_UTIL_LIST_FILES @root_dir = 'C:\DataFiles' , @filename_filter = 'AccountFile'[/code]The return from Work.dbo.P_UTIL_LIST_FILESis a select * from the table below:[code="sql"]DECLARE @files TABLE ( FileFullName varchar(200), FileSize varchar (20), DateCreated datetime)[/code]The destination table in the proc I'm executing the Exec Into is created as follows:[code="sql"]CREATE TABLE #files ( FileFullName varchar(200), FileSize varchar (20), DateCreated datetime)[/code]As you can see, column names and data types match. But clearly something is going wrong since I get no data inserted.Anyone bumped into this recently and have an idea of why this isn't working?Many thanks!!!Wed, 23 May 2012 15:47:47 GMTFly Girlhttp://www.sqlservercentral.com/Forums/Topic1305032-392-1.aspxOK, I'm trying and failing (to an extent) to get my head around set-based iteration.As a sometime dev who is used to loops (go VBScript go!) and more 'traditional' programming, I decided this was a topic that I really needed to learn something about.So I'm wondering if anyone can help with this example.Here's me creating a table and populating it with data using a loop-based method:[code="sql"]CREATE TABLE fooTable (data VARCHAR(8000) )DECLARE @loopCounter INTSET @loopCounter = 10000WHILE @loopCounter > 0 BEGIN INSERT INTO fooTable SELECT name from sys.databases SET @loopCounter = @loopCounter - 1 END[/code]So far, so good. I should have the name of every database on my server, duplicated 10000 times. 5 databases, 50000 rows. This query took about 10 seconds to execute (I didn't time it accurately).Now I'm struggling to render this as a set-based approach.From what I understand, I should be using a function based approach to populate the data rather than a looping one. I.e. if I was using an INT value or similar I could probably specify a range or other mathematical function - I chose a VARCHAR to make it a bit difficult.Would be grateful for any help in understanding this topic. Thanks. Wed, 23 May 2012 09:15:11 GMTderek.colleyhttp://www.sqlservercentral.com/Forums/Topic1305716-392-1.aspxHi, I need to manipulate string as below. Example : set @str1 = 'Lokesh'set @str2 = 'Gowda'Result Should be:@str1 = Lok***@str2 = Gow**Please assist without using any loops. ThanksThu, 24 May 2012 05:13:41 GMTlokesha.bhttp://www.sqlservercentral.com/Forums/Topic1304858-392-1.aspxHow can I merge 2 SQL statements below to render --> TODAY's date - 13 months --> in the format: MM/DD/YYYY for example: If today is June 1, 2012 -- I need the following: 05/01/2012here is TODAY -13 month calculation:----- SELECT DATEADD(dd, DATEDIFF(dd,0,DATEADD(MONTH,-13,GETDATE())), 0)here is the MM/DD/YYYY format I need:----- SELECT CONVERT(VARCHAR, GETDATE(),101)thanks in advance !Wed, 23 May 2012 05:41:17 GMTExpress12http://www.sqlservercentral.com/Forums/Topic1305517-392-1.aspxHi Experts,Can you please provide me a solution regarding below mentioned problem?I have a table with some data like[b][u]Product[/u][/b][b][u]Total Sales[/u][/b][b][u]Year[/u][/b][b][u]Period[/u][/b]Product A 75 2012 10Product B 80 2012 10Product C 32 2012 10Product D 56 2012 10Product B 25 2012 11Product C 90 2012 11Product A 65 2012 12Product C 50 2012 12Product D 45 2012 12Now I want to create a view like this[b][u]Product[/u] [/b] [b][u]Total Sales (Current Period)[/u][/b] [b][u]Total Sales (Last Period)[/u][/b] [b][u]Year[/u][/b] [b][u]Period[/u][/b]Product A 75 0 2012 12Product B 80 25 2012 12Product C 32 90 2012 12Product D 56 0 2012 12Product A 0 65 2012 11Product B 25 0 2012 11Product C 90 50 2012 11Product D 0 45 2012 11Product A 65 0 2012 10Product B 0 0 2012 10Product C 50 0 2012 10Product D 45 0 2012 10Please help me.Thanks and Regards,P PaulWed, 23 May 2012 22:46:36 GMTprasanta.paul 88490http://www.sqlservercentral.com/Forums/Topic1305649-392-1.aspxI'm having one of those days...I want to create a StoredProc that runs an Insert query into a table.My main data table contains details around appts for customers. ApptID, ApptDate, CustomerNo, Customer Details etcWe use this to send text messages out daily to remind of up coming appts. The customer has the ability to text back if cant make it.I want to extract back the appts that are cancelled.I need to create a StoredProc that Inserts ApptID, CustomerNo and the datetime now into another table.This is my StoredProc, when it executes I get the error, StoredProc has no parameters and arguments were supplied.Where have I gone wrong?[code]ALTER PROCEDURE dbo.RunInsertCanxApptASDECLARE @ApptID intDECLARE @CustNumber varchar(15)INSERT INTO dbo.ApptsCanx (CustNumber, ApptID, CanxDate)VALUES ((SELECT CustID FROM dbo.Data WHERE CustID = @CustNumber), (SELECT ApptID FROM dbo.Data WHERE ApptID = @ApptID), GETDATE()[/code]My execute statment[code]EXEC dbo.RunInsertCanxAppt 53954, 123456[/code]Thu, 24 May 2012 03:48:48 GMTjez.lislehttp://www.sqlservercentral.com/Forums/Topic1303273-392-1.aspxHi, I need to know if a word is avaiable in a comma seperatred textsuppose a value in table1 is 'ram'i need to know if 'ram' is available in column2 of table2. the values of column2 will be like ram,sham,ravi---- Drop table #basetableCreate table #basetable(Id int identity,Component varchar(256),)----Drop table #dailytableCreate table #dailytable(ID Int,FILENAME Char(50),PROCESS Char(50),)insert into #basetable Values( 'ravi,raja ')insert into #basetable Values( 'raja,jim,sham')insert into #basetable Values( 'prem,john,pal,ram')Insert into #dailytable values(1, 'In' ,'Ram ')Insert into #dailytable values(2, 'out' ,'prem')Insert into #dailytable values(3, 'In-out ', 'Ravi' )Select * from #basetableSelect * from #dailytableMon, 21 May 2012 04:39:04 GMTcarthikhttp://www.sqlservercentral.com/Forums/Topic1305183-392-1.aspxHi All, i have this query: [code="sql"]SELECT B.BUSINESS_UNIT, B.INV_ITEM_ID, C.DESCR, B.DESCR, B.UNIT_MEASURE_STD, B.INV_PROD_FAM_CD, B.INV_ITEM_GROUP, B.CATEGORY_CD, B.CUMULATIVE_QTY, B.CM_UNIT_COST, B.TOTAL_COST, D.DESCR, B.DESCR60,C.SETID,C.INV_PROD_FAM_CD,(CONVERT(CHAR(10),C.EFFDT,121)),D.SETID,D.INV_ITEM_GROUP,(CONVERT(CHAR(10),D.EFFDT,121)) FROM PS_CO_RUN_STKVAL A, ((PS_CO_IN_STCK_C_VW B LEFT OUTER JOIN PS_INV_ITEM_FAM C ON C.INV_PROD_FAM_CD = B.INV_PROD_FAM_CD AND C.SETID = 'COSAP' ) LEFT OUTER JOIN PS_INV_ITEM_GROUP D ON B.INV_ITEM_GROUP = D.INV_ITEM_GROUP AND D.SETID = 'COSAP' ) WHERE A.OPRID = 'CO883362' AND ( (B.BUSINESS_UNIT = A.BUSINESS_UNIT OR A.BUSINESS_UNIT = '')AND A.OPRID = 'CO883362' AND A.RUNCNTLID = '1'AND (( A.CO_IN_ITEM_OPT = '00' AND ( B.BUSINESS_UNIT = A.BUSINESS_UNIT OR A.BUSINESS_UNIT = '')) OR ( A.CO_IN_ITEM_OPT = '01' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID = A.INV_ITEM_ID) OR ( A.CO_IN_ITEM_OPT = '02' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID IN (SELECT E.INV_ITEM_ID FROM PS_MASTER_ITEM_TBL E WHERE E.SETID = 'COSAP' AND E.INV_ITEM_GROUP = A.INV_ITEM_GROUP)) OR ( A.CO_IN_ITEM_OPT = '03' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID IN (SELECT F.INV_ITEM_ID FROM PS_MASTER_ITEM_TBL F WHERE F.SETID = 'COSAP' AND F.INV_PROD_FAM_CD = A.INV_PROD_FAM_CD)) OR ( A.CO_IN_ITEM_OPT = '04' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.INV_ITEM_ID IN (SELECT G.INV_ITEM_ID FROM PS_MASTER_ITEM_TBL G, PS_MS_ITM_INV_VW H WHERE G.SETID = H.SETID AND G.INV_ITEM_ID = H.INV_ITEM_ID AND G.SETID = 'COSAP' AND H.CATEGORY_CD = A.CATEGORY_CD))) AND B.DT_TIMESTAMP = (SELECT MAX( I.DT_TIMESTAMP) FROM PS_CO_IN_KARDEX I WHERE (I.BUSINESS_UNIT = B.BUSINESS_UNIT) AND I.INV_ITEM_ID = B.INV_ITEM_ID AND I.TRANSACTION_DATE <= '10/05/2012') AND B.SEQ_NBR = (SELECT MAX( J.SEQ_NBR) FROM PS_CO_IN_KARDEX J HERE (J.BUSINESS_UNIT = B.BUSINESS_UNIT) AND J.INV_ITEM_ID = B.INV_ITEM_ID AND J.DT_TIMESTAMP = B.DT_TIMESTAMP) )[/code]The delay is on the table PS_CO_IN_KARDEX where there are millions of records Can anyone Help me please.Wed, 23 May 2012 11:09:26 GMTxRafohttp://www.sqlservercentral.com/Forums/Topic1303878-392-1.aspxI have a table in which there are comma delimited fields, I want to update them based on conditionsif my additional_procedure location field contains null or not 1 and 2 i need to make it 1I am using following method 1) spiliting into rows 2) checking those values where ever there is null, not 1 and not 2, set it to 13) again making those rows into comma delimited field 4) I want to put that new comma delimited field into table [b]here is temp table [/b][code="sql"] drop Table #temp Create Table #temp (MRN nchar(10) null, additional_procedure varchar(max), additional_procedure_Location varchar(max)) insert into #temp (MRN,additional_procedure,additional_procedure_Location) values('1','9772800,9773100,9773200', '1,2,3'),('2','9773300, 9773400, 9773500, 9773600', '2,1,,1'),('3','9774300,9774400,9774500,9774600','1,2,2,4'),('4','9775300,9775400,9776100','4,,2'),('5','9776500,9776800,9776900,9777100','2,1,,1')Select * from #temp[/code]I wrote 2 table valued functions first one to spilit them into rows and than another to find out if there is null or not 1 and not 2 and for setting value to 1I also made a procedure in which I am passing two parameters and getting the new comnplete field in comma delimited format , however I am unable toput this new comma delimited field into the table and need help how to update it, your help would be highly appreciated my functions are as below[b]First Function to spilit these comma delimited fields into rows [/b][code="sql"]GO/****** Object: UserDefinedFunction [dbo].[Validate_2_DelimitedFields_Id] Script Date: 05/22/2012 15:27:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[Validate_2_DelimitedFields_Id]( @delimited1 nvarchar(max), @delimited2 nvarchar(max), @delimiter1 nvarchar(100), @delimiter2 nvarchar(100), @StartFieldNum int, @EndFieldNum int) RETURNS @t TABLE( id int, val1 nvarchar(max), val2 nvarchar(max))ASBEGIN insert into @t(id,val1,val2) select a.id,a.val,b.val from [HealthValidator].[dbo].[ValidateDelimitedField] (@delimited1,',',1,50) as a full outer join [HealthValidator].[dbo].[ValidateDelimitedField] (@delimited2,',',1,50) as b on a.Id = b.Id RETURNENDGO[/code][b]Second funtion to find where is not 1 not 2 and null values and setting it to 1 [/b][code="sql"]CREATE FUNCTION [dbo].[UpdateProcedureLocation] ( -- Add the parameters for the function here @ap varchar(max), @apl varchar(max) )RETURNS @ReturnTable TABLE ( -- Add the column definitions for the TABLE variable here [id] int NULL, [ap] [varchar](max) NULL, [apl] [varchar](max) NULL )ASBEGIN INSERT INTO @ReturnTable(id,ap,apl) Select * from dbo.Validate_2_DelimitedFields_id(@ap, @apl ,',',',',1,26) Update @ReturnTable Set apl = '1' where Id in (Select id from dbo.Validate_2_DelimitedFields_id(@ap, @apl ,',',',',1,26) where ((nullif(val1,'') is not null ) and nullif(val2,'') is null) or (nullif(val1,'') is not null ) and (val2 <> '2' and val2 <> '1')) RETURN END[/code][b]and third procedure in which I am passing these two and getting a new field in the same comma delimited format [/b][code="sql"]create proc [dbo].[Sp_UpdateProcedureLocation] @ap varchar(Max), @apl varchar(Max), @newapl varchar(Max) output as declare @TEMPTable TABLE (id int, ap varchar(20), apl varchar(20)) Insert into @TEMPTable(id,ap,apl) Select * from dbo.[UpdateProcedureLocation](@ap, @apl) DECLARE @NewProcLoc VARCHAR(MAX) SELECT @NewProcLoc = COALESCE(@NewProcLoc+',' ,'') + apl FROM @temptable SELECT @NewProcLoc Set @newapl = @NewProcLoc GO[/code]My required output is [code="sql"] drop Table #temp Create Table #temp (MRN nchar(10) null, additional_procedure varchar(max), additional_procedure_Location varchar(max)) insert into #temp (MRN,additional_procedure,additional_procedure_Location) values('1','9772800,9773100,9773200', '1,2,1'),('2','9773300, 9773400, 9773500, 9773600', '2,1,1,1'),('3','9774300,9774400,9774500,9774600','1,2,2,1'),('4','9775300,9775400,9776100','1,1,2'),('5','9776500,9776800,9776900,9777100','2,1,1,1')Select * from #temp[/code]Tue, 22 May 2012 00:10:29 GMTumar.memonhttp://www.sqlservercentral.com/Forums/Topic1305443-392-1.aspxHi All,I run SQL Server 2012 and Sharepoint 2010. While I do not expect any Sharepoint pros on here, Sharepoint 2010 requires a certain collation set at the server level (not sure if it is really required, but mentioned as a requisite step), but unfortunately, at the server level, I have a different collation set. However, Sharepoint also recommends certain collations at the web application database level (each web app has its own database), but as these databases are created by SP2010, they have the correct collation. Am I right in thinking that the database collation takes precedence from the server, and is it safe to change the server collation level? I've seen the command line to rebuild SQL Server to change the server collation and I am considering running this. However, does the revlogin sp work in backing up/restoring the users and user permissions?ThanksWed, 23 May 2012 17:10:42 GMTinfo-849042http://www.sqlservercentral.com/Forums/Topic1305185-392-1.aspxPlease see attachment PlanCat.docx.Wed, 23 May 2012 11:11:00 GMTgary.moreyhttp://www.sqlservercentral.com/Forums/Topic1305006-392-1.aspxHi AllConsider the following 2 Tables:[code="sql"]CREATE TABLE [dbo].[T1]( [Col1] [int] NOT NULL, [Col2] [int] NOT NULL)GOINSERT [dbo].[T1] ([Col1], [Col2]) VALUES (1, 2)INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (2, 2)INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (3, 2)INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (4, 2)INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (5, 2)INSERT [dbo].[T1] ([Col1], [Col2]) VALUES (22, 44)GOCREATE TABLE [dbo].[T2]( [Col1] [int] NOT NULL, [Col2] [int] NOT NULL) GOINSERT [dbo].[T2] ([Col1], [Col2]) VALUES (1, 2)INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (2, 2)INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (3, 2)INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (4, 2)INSERT [dbo].[T2] ([Col1], [Col2]) VALUES (5, 2)[/code]Then consider the following query:[code="sql"]select * from T1 TTwhere exists (select Col1 from T2 TTT where TTT.Col1 = TT.Col1)[/code]I understood that when using EXISTS, SQL checks if any rows are returned by the sub-query, if anything is returned then the outer query will return everything(*) - If not, then nothing will be returned.I always understood that it works with true/false..When you execute [code="sql"]select * from T1 TTwhere exists (select Col1 from T2 TTT where TTT.Col1 = TT.Col1)[/code]It doesn't return everything in the T1.Why is this?ThanksWed, 23 May 2012 08:35:57 GMTderekr 43208http://www.sqlservercentral.com/Forums/Topic1304936-392-1.aspxHiFollowing is the code[code="sql"]DECLARE @ProductOption TABLE(ProductOptionID int,Name varchar(100),ProductID int)INSERT INTO @ProductOption (ProductOptionID,Name,ProductID) SELECT '1','Wifi','1' UNION ALL SELECT '2','Broadband','1' DECLARE @ProductOptionRate TABLE(ProductOptionID int, FromDate date,Rate numeric(19,5))INSERT INTO @ProductOptionRate (ProductOptionID,FromDate,Rate) SELECT '1','2012-05-01','100' UNION ALL SELECT '1','2012-05-15','200' UNION ALL SELECT '1','2012-05-25','1200'UNION ALL SELECT '2','2012-05-01','500' UNION ALL SELECT '2','2012-05-15','800' UNION ALL SELECT '2','2012-05-25','1800'DECLARE @FromDate date = '2012-05-18'DECLARE @ProductID int = 1SELECT * FROM ( SELECT ROW_NUMBER()OVER(PARTITION BY AO.ProductOptionID,FromDate ORDER BY AO.ProductOptionID,FromDate) AS Rownum, AO.ProductOptionID, FromDate, Rate FROM @ProductOptionRate AS AO JOIN @ProductOption AS PO ON AO.ProductOptionID = PO.ProductOptionID WHERE (PO.ProductID = @ProductID) AND (FromDate <= @FromDate) ) AS D WHERE D.Rownum = 1[/code]Here DECLARE @FromDate date = '2012-05-18' So it should display only data which has FromDate = '2012-05-15' If @FromDate date is Set date between this range from '2012-05-01' to '2012-05-14' it should display only data which has FromDate = '2012-05-01' If @FromDate date is Set date between this range from '2012-05-15' to '2012-05-24' it should display only data which has FromDate = '2012-05-15'Wed, 23 May 2012 07:30:12 GMTdilipd006http://www.sqlservercentral.com/Forums/Topic1300879-392-1.aspx[code="sql"]select 'A' as [value], 'B' as [value]for xml path('test'), type[/code]This outputs the following:[code="xml"]<test> <value>AB</value></test>[/code]However, what I wanted is this:[code="xml"]<test> <value>A</value> <value>B</value></test>[/code]I can do a lot with for xml path(), but such a basic thing is making me go crazy.... I know something like this:[code="sql"]select 'A' as [text()]union all select 'B' as [text()]for xml path('value'), root('test'), type[/code]will return the expected result, but that's not the point. In real life I need to construct a much more complex xml document, where potentially multiple elements with the same name can follow each other, and I need those values to be in multiple elements, not their concatenated values in one element?So, how do I make for xml path() return the both values as 2 elements, even if they have the same name?Wed, 16 May 2012 03:26:04 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1304990-392-1.aspxHi, I need to write an Instead of Insert trigger on a table that logs proxy information. There are a large amount of 'anonymous' records logged to this table and we need the trigger to only insert the non 'anonymous' records. I also want to keep track of how many 'anonymous' records it is NOT inserting to the table , so I have a counter in a another table called NumAnonymous.This is what I have so far:Create Trigger [dbo].[TrgInstdofInsert] on dbo.ProxyInstead of insertas SET NOCOUNT ON BEGIN IF Exists (SELECT * FROM inserted WHERE ClientUserName = 'Anonymous') BEGIN UPDATE dbo.NumAnonymous SET NumAnonymous = NumAnonymous + 1; END ELSE Insert INTO dbo.Proxy select * from inserted endGOI have tested this on a test DB and it seems to work, however if I apply it to production, I see the counter running but no records at inserted to the DB at all. Can anyone tell me what I am doing wrong? Wed, 23 May 2012 08:16:25 GMTsdodiahttp://www.sqlservercentral.com/Forums/Topic1304977-392-1.aspxI need to export the data from a query(select col1,col2,col3 from table where col1 is not null) to generate excel file on different machine and after successful export to excel alert an email.this process needs to run every week.Please help..Thanks for your help in advanceWed, 23 May 2012 08:03:29 GMTMvs2k11http://www.sqlservercentral.com/Forums/Topic1305013-392-1.aspxHi,Hope it's the right forum!With PDW AU3, I know stored procedures are supported but some functions in the SMP SQL Server product have not been implemented in SQL Server PDW,Transact-SQL compatibility with SQL Server SMP is not yet fully complete andI'm looking for a documentation or something in order to help me.Who knows where I can find info. about what is supported or not ?Thank youWed, 23 May 2012 08:45:31 GMThtiteufhttp://www.sqlservercentral.com/Forums/Topic1304720-392-1.aspxHi All,I have a more than 3 lacs emailId in my customer table. I want to extract domain name from emailid and wanted to know who are from the same domain. For Exa. there are 4 emailId. 1). xyz[b]@sqlservercentral.com[/b] 2). abc[b]@sqlservercentral.com[/b] 3). kkr[b]@yahoo.com[/b] 4). pqr[b]@gmail.com[/b]Here 2 emailId are from sqlservercentral.com domain, 1 from yahoo.com and 1 from gmail.com. So i want the output like this.Domain Count-------------------- -------sqlservercentral.com 2yahoo.com 1gmail.com 1----------------------Although my requirement is, i have to ignore those emailIds who are from Yahoo, Gmail, Hotmail, Reddifmail...etc..Here is what i have so far..[code="sql"]Select count(EmailId), Stuff(Ltrim(Rtrim(Emailid)), 1, charindex('@',Ltrim(Rtrim(Emailid))), '') from dbo.Customermaster Where Stuff(Ltrim(Rtrim(Emailid)), 1, charindex('@',Ltrim(Rtrim(Emailid))), '') not in ('gmail.com', 'yahoo.com', 'yahoo.co.in', 'msn.com', 'hotmail.com', 'hotmail.co.in', 'rediffmail.com', 'rediff.com', 'ymail.com', 'indiatimes.com', 'yaghoo.com', 'localmail.com', 'yahoo.co.uk', 'yahoo.in', 'reidffmail.com', 'yahoo.com ', 'rediffmai.com')Group by Stuff(Ltrim(Rtrim(Emailid)), 1, charindex('@',Ltrim(Rtrim(Emailid))), '')[/code]Is there any better way rather than this ????need your helpWed, 23 May 2012 00:42:29 GMTKrishnrajhttp://www.sqlservercentral.com/Forums/Topic1303783-392-1.aspxHello, I'm trying to drop a table if exists then create it as part of a dynamic query but it keeps returning syntax error msg: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'tmp05212012MemBuyers'. I think the problem may be when I use the if exists drop table statement because if I don't use it and run the scrip it creates the table successfully. I have to create the table like this because if needs to include the present date when the job runs. Both examples are below, first the section that creates the table succesfully, then the crapped up section. Any light as to where the syntax/errors are will be greatly appreciated.section 1:-- drop table tmp05212012InfoReqDeclare @SQLExec2 varchar(250), @BackName2 varchar(8)Set @BackName2 = replace(convert(varchar,getdate(),101),'/','')-- select @BackNameSet @SQLExec2 = 'Select * Into tmp' + @BackName2 + 'InfoReq From #InfoReq'Exec (@SQLExec2)Section 2:Declare @SQLExec varchar(1000), @BackName varchar(8)Set @BackName = replace(convert(varchar,getdate(),101),'/','')-- select @BackNameSet @SQLExec = 'if exists (select * from dbo.sysobjects where id = object_id(N' + ' tmp' + @BackName + 'MemBuyers' + ') and OBJECTPROPERTY(id, N' + ' IsUserTable' + ') = 1)' + ' drop table dbo.tmp' + @BackName + 'MemBuyers' +' Select * Into dbo.tmp' + @BackName + 'MemBuyers From #membbuyers'Exec (@SQLExec)Mon, 21 May 2012 16:31:31 GMTlatingntlmanhttp://www.sqlservercentral.com/Forums/Topic1304872-392-1.aspxI am setting up a sql server 2008 r2 database with the associated tables, views and stored procedures for one main customer only. I receive files from this customer daily. My company says that I just need to change as they change.Basically I am trying to decide what to do with the size of a transaction code field. Up until last week, the field was a number that was a size of 3. For future expansion, I was thinking of making the field a numeric field that was size of 10. As of last week, I started to receive values that was 1 alpha character and 2 numbers. Thus I am trying to decide how to define the field size. I am thinking of using varchar(10) over char(10) or some smaller sized field? What size of field would you use and why?Wed, 23 May 2012 06:07:30 GMTwendy elizabethhttp://www.sqlservercentral.com/Forums/Topic1304819-392-1.aspxI found a neat trick that I have not seen applied anywhere, nor did I find any documentation for it. Yet it works on SQL server 2008, SQL Server 2008 R2 and SQL server 2005. I currently don't have the time to write up an article on this, but I still want to share it with all of you, so I'll just post it now so I can hear your ideas on it.The problem I often face is that I have to read an xml document having some parent-child structure in it and I need to insert both the parent and the child nodes into tables, plus the relationship between the two into another table. All solutions I've encountered and could come up with so far involved either reading the xml multiple times (once to find the parents, once to find the children per parent plus once more to find the relationships) or reading the parents into a temp table, including for each parent an xml fragment containing the child nodes for that parent. Each of these methods is very labor intensive (for me and for sql server). I've now found that on the window functions (row_number() etc) you can also partition and order by on the name of the nodes() output.Like this:[code="sql"]declare @xml xml = N'<parent name="p1"> <child name="c1" /> <child name="c2" /> <child name="c3" /></parent><parent name="p2"> <child name="c4" /> <child name="c5" /></parent>';select dense_rank() over (order by p.p) as parentID, p.p.value('@name','varchar(10)') as parentName, row_number() over (partition by p.p order by c.c) as childID, c.c.value('@name','varchar(10)') as childNamefrom @xml.nodes('/parent') p(p) cross apply p.p.nodes('child') c(c)[/code]Which produces:[code="other"]parentID parentName childID childName1 p1 1 c11 p1 2 c21 p1 3 c32 p2 1 c42 p2 2 c5[/code]Each parent node is assigned a unique number -based on the node's location in the xml file- and every child node is also assigned a unique number within it's parent node -again based on the child's location within the parent node-.As I said: I did not find any documentation describing this feature yet, so use it at your own risk. But I thought it was so cool that I still wanted to share this with you.Wed, 23 May 2012 04:11:40 GMTR.P.Rozemahttp://www.sqlservercentral.com/Forums/Topic1304736-392-1.aspxplease tell any one , how i see the query which is used for creating a tableWed, 23 May 2012 01:19:21 GMTshubhamagrawal.107http://www.sqlservercentral.com/Forums/Topic1303794-392-1.aspxI haven’t worked with data islands and gaps much in the past, and have been given a task to determine how many times a device interface was at > 95% utilization for at least 10 minutes continuously. The data I'm querying is performance data from network devices such as routers. Every minute thousands of device interfaces are polled and we get statistics back like bandwidth utilization %.Utilization can spike up and down pretty significantly so it’s only continuous high utilization longer than 10 minutes I need to count. A device could have high utilization for hours and hours possibly, and that would count as one high utilization event. So I guess the “end” of a high utilization event would be when the next record after a > 95% record is the first <= 95%.I’m assuming I’d have to use something like row_number over() but I haven’t been able to work it out. o_OHere's a sample set of records to help visualize the data:TheDateTime InterfaceName UtilizationPercent12/1/2010 6:00 WAN Interface 9912/1/2010 6:01 WAN Interface 9912/1/2010 6:02 WAN Interface 9712/1/2010 6:03 WAN Interface 9612/1/2010 6:04 WAN Interface 9912/1/2010 6:05 WAN Interface 9312/1/2010 6:06 WAN Interface 9312/1/2010 6:07 WAN Interface 8712/1/2010 6:08 WAN Interface 8512/1/2010 6:09 WAN Interface 8512/1/2010 6:10 WAN Interface 8612/1/2010 6:11 WAN Interface 8712/1/2010 6:12 WAN Interface 8712/1/2010 6:13 WAN Interface 8712/1/2010 6:00 LAN Interface 9912/1/2010 6:01 LAN Interface 9912/1/2010 6:02 LAN Interface 9712/1/2010 6:03 LAN Interface 9612/1/2010 6:04 LAN Interface 9912/1/2010 6:05 LAN Interface 9812/1/2010 6:06 LAN Interface 9812/1/2010 6:07 LAN Interface 9912/1/2010 6:08 LAN Interface 9712/1/2010 6:09 LAN Interface 9612/1/2010 6:10 LAN Interface 9812/1/2010 6:11 LAN Interface 9912/1/2010 6:12 LAN Interface 9912/1/2010 6:13 LAN Interface 93So I need to take the above data (could be millions of records with thousands of different interfaces) and come up with a result set that looks like this:Interface Name # of Times device was continously at > 95% for > 10 minutes WAN Interface 0 LAN Interface 1While "WAN Interface" was at greater than 95% for a few polls, it didn't last for 10 minutes. The "LAN interface" was at > 95% for longer than ten minutes until a 93% poll happened. Hope that makes sense. Thanks! SMon, 21 May 2012 18:12:47 GMTshaunnahttp://www.sqlservercentral.com/Forums/Topic1299019-392-1.aspxI have a view something like:Create VIEW SomeViewASSELECT * from Retail.dbo.EmployeesBut now since I have a RetailDev, RetailQA and Retail, I can read a table to get the databasename and then use it. But I can't seem to figure out how to do it in a view.DECLARE @Sql varchar(100)SET @Sql = 'SELECT * FROM ' + (Select DBName FROM CurrenDB) + ''EXEC (@Sql)Don' think that works.I can't pass the database in as you can't pass parameters in. Is this possible with a view?Thanks,TomFri, 11 May 2012 19:23:40 GMTtshadhttp://www.sqlservercentral.com/Forums/Topic1303495-392-1.aspxI am working with the data from msdb.dbo.sysjobhistory and want to take the average duration of each job. If you are not familiar with the table, here is a simplistic model:[code="sql"]create table #jobhistory( jobId uniqueidentifier ,run_duration int)[/code]These are the only columns necessary for my example.run_duration is in the format of hhmmss, and if the job only took 3 seconds the value would be '3'.Average of course doesn't return valid data. So I am trying to find the best way to find the average. Here is what I have so far.[code="sql"]create table #jobhistory( jobId int /* really uniqueidentifier, but using int for simplicity */ ,run_duration int)insert into #jobhistory select 1,3 union all select 1,31003 union all select 1,233 union all select 2,5 union all select 2,101 select jobid, RIGHT('00'+CONVERT(VARCHAR,AVG((SUBSTRING(right('000000'+convert(varchar,run_duration),6),1,2) * 60 * 60)+ (SUBSTRING(right('000000'+convert(varchar,run_duration),6),3,2) * 60)+ (SUBSTRING(right('000000'+convert(varchar,run_duration),6),5,2))) / (60*60)),2) + ':'+ RIGHT('00'+CONVERT(VARCHAR,AVG((SUBSTRING(right('000000'+convert(varchar,run_duration),6),1,2) * 60 * 60)+ (SUBSTRING(right('000000'+convert(varchar,run_duration),6),3,2) * 60)+ (SUBSTRING(right('000000'+convert(varchar,run_duration),6),5,2))) % (60*60) / 60),2) + ':'+ RIGHT('00'+CONVERT(VARCHAR,(AVG((SUBSTRING(right('000000'+convert(varchar,run_duration),6),1,2) * 60 * 60)+ (SUBSTRING(right('000000'+convert(varchar,run_duration),6),3,2) * 60)+ (SUBSTRING(right('000000'+convert(varchar,run_duration),6),5,2))) % (60))),2)from #jobhistoryGROUP BY jobId drop table #jobhistory [/code]I want the output to be in the format of HH:MM:SS.Thanks for any input!Mon, 21 May 2012 10:08:10 GMTdkschillhttp://www.sqlservercentral.com/Forums/Topic1303145-392-1.aspxI have a pivot table that displays studentid and a list of assessments as the columnsthe cells are the scores of those students in those assessmentsI would like to add a column which is the total scores for each studentdeclare @Sectionid int=1984DECLARE @Assessments nvarchar(max), @Total nvarchar(max)SELECT @Assessments = STUFF( ( select ',' + quotename(cast(AssessmentID as nvarchar(15))) from GradeBook.Assessment as A where SectionID=@SectionID and A.Deleted=0 for xml path('') ), 1,1,'' ) DECLARE @mySQL nvarchar(4000) SELECT @mySQL = N'select *from (SELECT AP.Points, Ap.StudentID,U.lastname +'+''', '''+ '+U.firstname as displayname,'+'+ cast(A.AssessmentID as nvarchar(15)) as Assessment FROM GradeBook.AssessmentPoint AS AP INNER JOINGradeBook.Assessment AS A ON A.AssessmentID = AP.AssessmentID INNER JOINSP.[User] UON AP.StudentID = U.UserID) DataPIVOT (sum(Points)FOR Assessment IN (' + @Assessments + ')) PivotTableorder by displayname'exec (@mysql)thanks in advanceSun, 20 May 2012 20:34:17 GMTSarsourahttp://www.sqlservercentral.com/Forums/Topic1304326-392-1.aspxHello,I have a the following table:Time NumCase Code15h00 - 16h00 1 4115h00 - 16h00 1 4216h00 - 17h00 3 4317h00 - 18h00 1 4413h00 - 14h00 2 4514h00 - 15h00 1 4613h00 - 14h00 1 47Want to query the table to have:Time Period 41 42 43 44 45 46 4713h00 - 14h00 0 0 0 0 2 0 114h00 - 15h00 0 0 0 0 0 1 015h00 - 16h00 1 1 0 0 0 0 016h00 - 17h00 0 0 3 0 0 0 017h00 - 18h00 0 0 0 1 0 0 0Total 1 1 3 1 2 1 1 1Any suggestions ?Thank youTue, 22 May 2012 11:01:00 GMTBeginner2012