Posted: 25 Jun 2013 11:17 PM PDT

Just found an odd piece of behavioral difference between SQL 2005 and SQL 2008R2. Any help would be appreciated.I have a tally table with numbers up to 8000. Then I have an Inline TVF to split strings.Prerequisites:[code="sql"]SET QUOTED_IDENTIFIER ONSET ANSI_NULLS ONGOIF EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'NUMBERS' AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE')BEGIN DROP TABLE dbo.NUMBERSENDGOCREATE TABLE dbo.NUMBERS( Number smallint IDENTITY(1, 1) PRIMARY KEY)GOSET NOCOUNT ONWHILE 1 = 1BEGIN INSERT INTO dbo.NUMBERS DEFAULT VALUES IF @@IDENTITY = 8000 BEGIN BREAK ENDENDSET NOCOUNT OFFGOIF OBJECT_ID('func_SPLIT_STRING') IS NOT NULLBEGIN DROP FUNCTION dbo.[func_SPLIT_STRING]ENDGOCREATE FUNCTION [dbo].[func_SPLIT_STRING]( @INPUT VARCHAR(MAX), @DELIMITER CHAR(1) = '|')RETURNS TABLE ASRETURN ( SELECT ROW_NUMBER() OVER (ORDER BY Number ASC) [ID] , SUBSTRING(@INPUT, Number, CHARINDEX(@DELIMITER, @INPUT + @DELIMITER, Number) - Number) [DATA] FROM dbo.NUMBERS WHERE Number <= CONVERT(INT, LEN(@INPUT)) AND SUBSTRING(@DELIMITER + @INPUT, Number, 1) = @DELIMITER )GO[/code]Then consider the following code:[code="sql"]DECLARE @VALUES VARCHAR(MAX)SET @VALUES = 'E62|E48|E47'SELECT CONVERT(INT, RIGHT(DATA, LEN(DATA) - 1)) , ISNUMERIC(RIGHT(DATA, LEN(DATA) - 1))FROM dbo.func_SPLIT_STRING(@VALUES, '|')WHERE ISNUMERIC(RIGHT(DATA, LEN(DATA) - 1)) > 0[/code]This code runs fine on SQL Server 2005, but raises the following error on SQL 2008R2 (SP1).[code]Msg 536, Level 16, State 4, Line 5Invalid length parameter passed to the RIGHT function.[/code]Please note that the database I run this on is running in 2005 compatibility level (level 90), but changing it to 100 doesn't have any effect.This is a function that we commonly use to split input. I can rewrite it as follows to avoid the error, but would prefer to find a solution in the func_SPLIT_STRING function to avoid having to rewrite a lot of SQL code.Workaround on the query side:[code="sql"]DECLARE @VALUES VARCHAR(MAX)SET @VALUES = 'E62|E48|E47'SELECT CONVERT(INT, RIGHT(DATA, LEN(DATA) - 1)) , ISNUMERIC(RIGHT(DATA, LEN(DATA) - 1))FROM dbo.func_SPLIT_STRING(@VALUES, '|')WHERE CASE WHEN LEN(DATA) > 0 THEN ISNUMERIC(RIGHT(DATA, LEN(DATA) - 1)) END > 0[/code]By this logic, it appears that in spite of the actual output from the inline TVF, SQL comes across a value where the LEN(DATA) is 0, leading to -1.Any help would be very much appreciated, or even an explanation as to why SQL 2008R2 misbehaves like this.

how to get reporting to with their name?

Posted: 25 Jun 2013 11:03 PM PDT

Hai friends, create table users(user_id varchar(10),username varchar(10),designation varchar(10),department varchar(10),reporting_to varchar(10))insert into users(user_id,username,designation,department,reporting_to)values('0010','A','salesman','Marketing','00005')insert into users(user_id,username,designation,department,reporting_to)values('0005','AA','Manager','Marketing','00001')insert into users(user_id,username,designation,department,reporting_to)values('0001','ABC','MD','Marketing','0001 ')my requirement is how to display all reporting_to peoples with their name.?example:if i choose reporting_to of "A" Users ll show like belowA is reporting_to==>'0005 : AA' like

Update linked server

Posted: 25 Jun 2013 06:51 PM PDT

Hii have linked server called TESTdatawith coloumn id which is varchar datatype that contain number .i want to update the last recourd with +1 .DECLARE @CR_num TABLE ( NUM varchar(50) ) declare @NUM as varchar(50)select @NUM=convert(int,[CCFNUM_LASTNUM_00N])+1 from [TEST_DATA].[***].[dbo].[CCFNUM] where [CCFNUM_LOCATION_00N]='9999'update [TEST_DATA].[***].[dbo].[CCFNUM] set [CCFNUM_LASTNUM_00N]=(select @NUM) where [CCFNUM_LOCATION_00N]='9999'but i get this error :OLE DB provider "*****" for linked server "TEST_DATA" returned message " Ambiguous table reference: (CCFNUM) UPDATE << Syntax Error >> CCFNUM SET CCFNUM_LASTNUM_00N = ? WHERE CCFNUM_LOCATION_00N = ? AND CCFNUM_LASTNUM_00N ".Msg 7343, Level 16, State 4, Line 5The OLE DB provider "****" for linked server "TEST_DATA" could not UPDATE table "[TEST_DATA].[****].[dbo].[CCFNUM]". ===========butwhen i update wit this command :update [TEST_DATA].[****].[dbo].[CCFNUM] set [CCFNUM_LASTNUM_00N]=1515 where [CCFNUM_LOCATION_00N]='9999'it run successfully ???!!!any suggesting ?regards,,,,

Need to pivot query results

Posted: 25 Jun 2013 06:27 AM PDT

Hello, I have the following query and I need the results in 1 row going across. Here is the query:[code]SELECT top(5) ap.apptDte, ap.begTime, lc.locName, ev.event, pm.fname + ' ' + pm.lnameFROMappts ap join loc_mstr lc on lc.locID = ap.locIDjoin events ev on ev.eventID = ap.eventIDjoin prov_mstr pm on pm.provID = ap.rend_provIDWHERE personID = '5D06AAE9-1B8D-461B-BAAB-633C1ED7ED43'AND appt_date > GETDATE()[/code]The results would have 25 columns on 1 row. Is this possible?Thank you.

best approach

Posted: 25 Jun 2013 06:31 AM PDT

I need to build a report from a table that holds seat assignments and capacity that looks like the below, however they are looking for the report to contain the cube number and blanks if capacity is not met in a cube so for cube 333 there should be 2 entries and for cube 701 there would be 3 entries but only 1 of them would have a value for ID.Looking for the best possible way to do this?Thanks! ID Cube Capacity-------------------------1 701 3 9 333 23 333 2

Sql samples, sql tips

Wednesday, June 26, 2013

[T-SQL] Different in behavior between 2005 and 2008R2 with RIGHT in the where clause

[T-SQL] Different in behavior between 2005 and 2008R2 with RIGHT in the where clause

No comments:

Post a Comment

Search This Blog