Wednesday, April 10, 2013

[T-SQL] Need advice on this string manipulate scalar UDF

[T-SQL] Need advice on this string manipulate scalar UDF

Need advice on this string manipulate scalar UDF

Posted: 09 Apr 2013 11:44 PM PDT

Dear all,[b]Can you please comment on this UDF, is there any better way to do it?For example CLF scalar UDF or inline table UDF?[/b]There is around 50 Million rows in TableABC and it will be around 1GB in size if it is exported as plain text .we want to use this function in this way:[code="sql"]SELECT 'D' AS RECORDTYPECODE, a.[Id],a.[Col1],a.[Col2],a.[Col3],%DBNAME%.ReplaceSplVarcharsBICD(a.[Col4]),%DBNAME%.ReplaceSplVarcharsBICD(a.[Col5]),%DBNAME%.ReplaceSplVarcharsBICD(a.[Col6]),a.[genID] FROM %DBNAME%.TableABC a[/code]The function is shown below:[code="sql"]USE [BPSUAT_BICD]GO/****** Object: UserDefinedFunction [dbo].[ReplaceSplVarcharsBICD] Script Date: 04/10/2013 16:08:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE function [dbo].[ReplaceSplVarcharsBICD](@value varchar(8000))RETURNS varchar(8000)asbegindeclare @result varchar(8000);-- This function is used to Replace some Special characters with space for varchar and char datatypes.-- Replacing for Enter, newlineselect @result = REPLACE(REPLACE(REPLACE(@value, CHAR(10) + CHAR(13), ' '),CHAR(10),' '), CHAR(13),' ');--Replacing other non printable characters from Hex 00 to Hex'1F' and Hex'7F'select @result = REPLACE(@result, CHAR(0),' ');select @result = REPLACE(@result, CHAR(1),' ');select @result = REPLACE(@result, CHAR(2),' ');select @result = REPLACE(@result, CHAR(3),' ');select @result = REPLACE(@result, CHAR(4),' ');select @result = REPLACE(@result, CHAR(5),' ');select @result = REPLACE(@result, CHAR(6),' ');select @result = REPLACE(@result, CHAR(7),' ');select @result = REPLACE(@result, CHAR(8),' ');select @result = REPLACE(@result, CHAR(9),' ');--select @result = REPLACE(@result, CHAR(10),' ');select @result = REPLACE(@result, CHAR(11),' ');select @result = REPLACE(@result, CHAR(12),' ');--select @result = REPLACE(@result, CHAR(13),' ');select @result = REPLACE(@result, CHAR(14),' ');select @result = REPLACE(@result, CHAR(15),' ');select @result = REPLACE(@result, CHAR(16),' ');select @result = REPLACE(@result, CHAR(17),' ');select @result = REPLACE(@result, CHAR(18),' ');select @result = REPLACE(@result, CHAR(19),' ');select @result = REPLACE(@result, CHAR(20),' ');select @result = REPLACE(@result, CHAR(21),' ');select @result = REPLACE(@result, CHAR(22),' ');select @result = REPLACE(@result, CHAR(23),' ');select @result = REPLACE(@result, CHAR(24),' ');select @result = REPLACE(@result, CHAR(25),' ');select @result = REPLACE(@result, CHAR(26),' ');select @result = REPLACE(@result, CHAR(27),' ');select @result = REPLACE(@result, CHAR(28),' ');select @result = REPLACE(@result, CHAR(29),' ');select @result = REPLACE(@result, CHAR(30),' ');select @result = REPLACE(@result, CHAR(31),' ');select @result = REPLACE(@result, CHAR(127),' ');--Replacing the pipe symbol select @result = REPLACE(@result, CHAR(124),' ');--Repalcing the NULsselect @result = Nullif(@result,'');--Removing the Right Spaces select @result = RTRIM(@result);--Removing the Left Spacesselect @result = LTRIM(@result);return @result;end[/code]or should we use CLF scalar UDF like this[code="VB"] using System; using System.Data.Sql; using System.Data.SqlTypes; using System.Collections.Generic; using Microsoft.SqlServer.Server; using System.Text; using System.Text.RegularExpressions; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString ReplaceSplVarcharsBICD(SqlString s){ if (s.IsNull) return String.Empty; //Removing the Right Spaces and Left Spaces string s1 = s.ToString().Trim(); if (s1.Length == 0) return String.Empty; StringBuilder tmpS = new StringBuilder(s1.Length); //striping out the "control characters" //Control characters are non-printing and formatting characters, such as ACK, BEL, CR, FF, LF, and VT. The Unicode standard assigns the following code points to control //characters: from \U0000 to \U001F, \U007F, and from \U0080 to \U009F. According to the Unicode standard, these values are to be interpreted as control characters unless //their use is otherwise defined by an application. Valid control characters are members of the UnicodeCategory.Control category. //Replacing for Enter, newline 0D0A or \x000D (13) or \x000A (10) //Replacing other non printable characters from Hex 00 to Hex'1F' and Hex'7F' subset of control characters //Replacing the pipe symbol \x007C (124) //Repalcing the NULs 00 for (int i = 0; i <= s1.Length; i++) { if (!Char.IsControl(s1[i]) & !s1[i] =='\x000D' & !s1[i] =='\x000A' & !s1[i] =='\x007C') { tmpS.Append(s1[i]); } else { tmpS.Append(' '); } } string result = tmpS.ToString().Trim(); return result; } }[/code]

CTE and Spool operators

Posted: 09 Apr 2013 08:28 AM PDT

I commonly find CTE using spool operators in the execution plan. According to Microsoft the spool operator uses the temp table."The Table Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database and existing only for the lifetime of the query. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input."So this leads to believe its possible that CTE's are using tempdb? so are the results stored in memory or tempdb? or does is DEPEND?

Initial size of the database file using T-SQL

Posted: 09 Apr 2013 04:19 PM PDT

Hi,Please let me know how to get the initial size of the database file using T-SQL. sys.master_files, sys.database_files, sysfiles, sysaltfiles --> gives only the current size and not the initial size.Thanks,Karthik R

The query processor could not start the necessary thread resources for parallel query execution.

Posted: 09 Apr 2013 04:50 PM PDT

I'm getting below error while running this query. [code="sql"]The query processor could not start the necessary thread resources for parallel query execution.[/code] As per my understanding, # of worker threads is automatically decided by SQL Engine. then how can I avoiud this error. does changing the Max worker threads help in this case? Please suggest. [code="sql"]DECLARE @OMSSubscriptionIdentifier AS NVARCHAR(50), @CTPSubscriptionIdentifier AS NVARCHAR(50), @PromotionCodeIdentifier AS NVARCHAR(50), @EDWSourceSystemID AS INT = 2780; SELECT @OMSSubscriptionIdentifier = dbo.fnIDFIdentifierDefault(), @CTPSubscriptionIdentifier = dbo.fnIDFIdentifierDefault(), @PromotionCodeIdentifier = dbo.fnIDFIdentifierDefault(); SELECT __$Operation, OnlineServiceSubscriptionIdentifier, PromotionCodeIdentifier as OnlineServicePromotionCode, EDWSourceSystemID, EDWIsDeletedFlag, LNKSID FROM (SELECT NULL as __$Operation, CASE WHEN SUB.OMSSubscriptionIdentifier <> @OMSSubscriptionIdentifier THEN CAST(SUB.OMSSubscriptionIdentifier AS NVARCHAR(50)) ELSE CAST(SUB.CTPSubscriptionIdentifier AS NVARCHAR(50)) END AS OnlineServiceSubscriptionIdentifier, prom.PromotionCodeIdentifier, @EDWSourceSystemID AS EDWSourceSystemID, CAST(0 AS bit) AS EDWIsDeletedFlag, NULL AS LNKSID, CASE WHEN SUB.OMSSubscriptionIdentifier <> @OMSSubscriptionIdentifier THEN SUB.OMSSubscriptionIdentifier_OrderBy ELSE SUB.CTPSubscriptionIdentifier_OrderBy END AS OnlineServiceSubscriptionIdentifier_OrderBy, prom.PromotionCodeIdentifier_OrderBy FROM stg.IDFOnlineServiceSubscriptionPromotion Prom JOIN stg.IDFOnlineServiceSubscription Sub ON Prom.SubscriptionKey = Sub.OnlineServiceSubscriptionSourceKey WHERE CASE WHEN SUB.OMSSubscriptionIdentifier <> @OMSSubscriptionIdentifier THEN 2770 ELSE 2780 END = @EDWSourceSystemID ) A WHERE A.PromotionCodeIdentifier <> @PromotionCodeIdentifierORDER BY UPPER(OnlineServiceSubscriptionIdentifier_OrderBy) , UPPER(PromotionCodeIdentifier_OrderBy) ][/code]

T-SQL Help

Posted: 09 Apr 2013 06:54 AM PDT

This seems like a very simple question but for some reason the answer is eluding me.Given the following table data (Customers) how do I select all the records excluding the duplicates with an earlier Actual Date?[code][u]Next[/u] [u]CustId[/u] [u]Customer[/u] [u]Scheduled Date[/u] [u]Actual Date[/u] [u]Last[/u]2013 101 Cust1 2011-04-08 2011-04-08 20112013 202 Cust2 2011-03-31 2011-03-31 20112013 202 Cust2 2010-12-02 2010-12-02 20112013 303 Cust3 2011-04-13 2011-04-13 20112013 404 Cust4 2010-12-29 2010-12-29 20122013 505 Cust5 2011-06-20 2011-06-20 20112013 505 Cust5 2011-01-25 2011-01-25 20112013 606 Cust6 2010-10-07 2010-10-07 20112013 707 Cust7 2010-07-20 2010-07-20 2011[/code]Would like to get:[code][u]Next[/u] [u]CustId[/u] [u]Customer[/u] [u]Scheduled Date[/u] [u]Actual Date[/u] [u]Last[/u]2013 101 Cust1 2011-04-08 2011-04-08 20112013 202 Cust2 2011-03-31 2011-03-31 20112013 303 Cust3 2011-04-13 2011-04-13 20112013 404 Cust4 2010-12-29 2010-12-29 20122013 505 Cust5 2011-06-20 2011-06-20 20112013 606 Cust6 2010-10-07 2010-10-07 20112013 707 Cust7 2010-07-20 2010-07-20 2011[/code]Thanks,Dave

Broadcast calender generator

Posted: 21 Mar 2013 03:10 AM PDT

Hi guys,Did anyone ever have to create a function/stored procedure that would take a date as an input and generate the values for the broadcast calender like:- Media_Week_Start_Date- Media_Week- Media_Month- Media_MonthName- Media_Quarter- Media_YearThanks in advance!

Column name or number of supplied values does not match table definition

Posted: 09 Apr 2013 09:36 AM PDT

Greetings,I have a vendor provided procedure that returns two result sets. I am trying to insert the first result set into a table. I believe this should work since I can disregard the second result set.--This worksCREATE TABLE #Test(a float,b float,c float,d float,e float,f int,g float,h DECIMAL(22,2),i DECIMAL(22,2),j DECIMAL(22,0),k DECIMAL(22,2),l float,m float)INSERT INTO #TestSELECT 3.58118449079261E+98, 9.6404720198456E+97, 8.59568543065516E+98, 2.36898164801007E+61, 1.58071941607988E+67, 103, 1.30623880297128E+97, 171334797.19, 131638.64, 404716, 566951665.25, 5504385.09951457, 4.09327353277521E+98SELECT * FROM #TestDROP TABLE #TestGO --I then create some procsCREATE PROCEDURE TestProc1ASSELECT 3.58118449079261E+98, 9.6404720198456E+97, 8.59568543065516E+98, 2.36898164801007E+61, 1.58071941607988E+67, 103, 1.30623880297128E+97, 171334797.19, 131638.64, 404716, 566951665.25, 5504385.09951457, 4.09327353277521E+98GO CREATE PROCEDURE TestProc2 --two return setsASSELECT 3.58118449079261E+98, 9.6404720198456E+97, 8.59568543065516E+98, 2.36898164801007E+61, 1.58071941607988E+67, 103, 1.30623880297128E+97, 171334797.19, 131638.64, 404716, 566951665.25, 5504385.09951457, 4.09327353277521E+98 SELECT 233, '2012-12-31 00:00:00', 'ABC', 2625859.36, 1.40291573314828E+77, 2773929.43, 2625859.36, NULL, 3.89158923999005E+83, 1.67548882141181E+197, 0.00507868627580116GO--This worksCREATE TABLE #Test(a float,b float,c float,d float,e float,f int,g float,h DECIMAL(22,2),i DECIMAL(22,2),j DECIMAL(22,0),k DECIMAL(22,2),l float,m float)INSERT INTO #TestEXEC TestProc1SELECT * FROM #TestDROP TABLE #Test--but this doesn't and I thought it wouldCREATE TABLE #Test(a float,b float,c float,d float,e float,f int,g float,h DECIMAL(22,2),i DECIMAL(22,2),j DECIMAL(22,0),k DECIMAL(22,2),l float,m float)INSERT INTO #TestEXEC TestProc2SELECT * FROM #TestDROP TABLE #TestYour comments are welcome.

Exclude weekends from DateDiff

Posted: 09 Apr 2013 03:16 AM PDT

Dear Friends,I need an urgent help-"How to Exclude Weekends from DateDiff function?"Kind RegardsDhananjay

TSQL help

Posted: 09 Apr 2013 04:55 AM PDT

Hi , I have a table from where I want to filter out some records as given below.CREATE TABLE #MissingTransactions (Code VARCHAR(10), Location VARCHAR(10), PrevDate DATE, PrevTime INT, NextDate DATE, NextTime INT, PrevTrans INT, NextTrans INT,NextSite VARCHAR(20),NextCompany VARCHAR(20))INSERT INTO #MissingTransactionsSELECT 'Code1','01','1/17/2013',1711,'1/18/2013',1859,74174,74209,'Site1','Comp1'SELECT 'Code1','26','1/14/2013',1124,'1/14/2013',1432,1,10,'Site2','Comp1'SELECT 'Code1','26','1/14/2013',1128,'1/14/2013',1533,2,20,'Site2','Comp1'SELECT 'Code1','26','1/14/2013',1358,'1/15/2013',722,9,22,'Site2','Comp1'SELECT 'Code1','26','1/16/2013',1611,'1/19/2013',819,70,133,'Site2','Comp1'SELECT 'Code1','26','2/24/2013',2055,'2/25/2013',1130,975,1000,'Site2','Comp1'SELECT 'Code1','26','2/25/2013',1034,'2/26/2013',717,999,1006,'Site2','Comp1'While doing a a Final Select in my report from #missingTransactionsI do not want to display PrevTrans IN (1,2,9) since Prev Trans(2) is in between Prev Trans (1) and Next Trans (10) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTarns = 2 is equal to NextDate(1/14/2013) where PrevTrans = 1AND Prev Trans(9) is in between Prev Trans (1) and Next Trans (10) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTrans = 9 is equal to NextDate(1/14/2013) where PrevTrans = 1 OR Prev Trans(9) is in between Prev Trans (2) and Next Trans (20) for the same code1 and Site2 and PrevDate(1/14/2013) where PrevTrans = 9 is equal to NextDate(1/14/2013) where PrevTrans = 2 Also I want to omit PrevTrans 975 and 999 with the same conditions like above.The only ones I want to display is PrevTrans 74174 and 70.Please help .

small case but stuck here now

Posted: 09 Apr 2013 01:53 AM PDT

I've never noticed or had the issue before:I have a sp with some datetime parameters:[code="sql"]ALTER PROCEDURE [dbo].[spUpdateDocInfoBySysID](@SysID int, @DocID int, @SelectedDocTitle varchar(255), @NewDocType int, @DocStatus varchar(10), @NewDateFirstIssued smalldatetime, @NewPreviousReviewDate smalldatetime, @NewNextReviewDate smalldatetime, @Note varchar(1000),@Contact varchar(50),@Phone varchar(20),@RiskRating int)[/code]exec [dbo].[spUpdateDocInfoBySysID] 2730, 1578, 'asdfasdfasdfasdf', 2, 'Active', CONVERT(datetime, '2011-10-30',103),CONVERT(datetime, '2013-01-22',103),CONVERT(datetime, '2222-02-22', 103)This threw me:[code="other"]Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'CONVERT'.[/code]exec [dbo].[spUpdateDocInfoBySysID] 2730, 1578, 'asdfasdfasdfasdf', 2, 'Active', '2011-10-30','2013-01-22','2222-02-22'This threw me:[code="other"]Msg 8114, Level 16, State 5, Procedure spUpdateDocInfoBySysID, Line 0Error converting data type varchar to smalldatetime.[/code]Can anyone point to me what's wrong with it? it has been working fine for quite a long time
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog