Saturday, September 21, 2013

[SQL Server 2008 issues] How to Update Column based on Due and collection as explained in post

[SQL Server 2008 issues] How to Update Column based on Due and collection as explained in post

How to Update Column based on Due and collection as explained in post

Posted: 19 Sep 2013 08:47 PM PDT

Create Table #temp ( Number Int, Princ_Due Int, Int_Due Int, Other_Due Int, Collectionn Int, Princ_Adj Int, Int_Adj Int ) Insert Into #Temp(Number,Princ_Due,Int_Due,Other_Due,Collectionn) Values(1,0,100,200,300), (2,100,200,300,800), (3,100,200,-100,200), (4,100,200,100,1000) Select * from #Temp /* Following is the column description, Princ_Due --> princple due( means amount yet to be collected) Int_Due --> Interest due Other_Due --> Other due Collectionn -- Total amount collected. My requirement is ,Collection Amount should be get adjusted according to priority,first Other,Interest and then Principle. For example in --> record 1 , Collection = 300, Other_Due = 200 Int_Due = 100 Princ_due = 0 So my Desired output should be, Int_Adj = 0 Princ_Adj = 0 -->record 2 , Collection = 800, Other_Due = 300 Int_Due = 200 Princ_due = 100 So my Desired output should be, Int_Adj = 0 Princ_Adj = -200 -->record 3 , Collection = 200, Other_Due = -100 (Negative means it is not due it is collected ) Int_Due = 200 Princ_due = 100 So my Desired output should be, Int_Adj = 0 Princ_Adj = 100 -->record 3 , Collection = 1000, Other_Due = 100 Int_Due = 200 Princ_due = 100 So my Desired output should be, Int_Adj = 0 Princ_Adj = -600 */ Please help me .Thanks in Advance!!

Varchar to numeric ???

Posted: 19 Sep 2013 10:18 PM PDT

Recently I was playing with different datatype conversions and saw one strange thing while converting one of the numeric value in varchar datatype to numeric datatype. Here is the code:declare @varchar varchar(50)select @varchar = '8E10'select @varchar -- Returns 0E10select isnumeric(@varchar) -- Returns 1select convert(numeric(28,10), @varchar) -- Error converting data type varchar to numeric.select cast(@varchar as numeric(28,10)) -- Error converting data type varchar to numeric.goAs you can see, '8E10' return 1 as a result of IsNumeric function but while using convert or cast, it gives error. Probably it's meant to but can anyone put light on this ?

Script to find Replicated column

Posted: 20 Sep 2013 08:54 AM PDT

It may sound pretty easy , still I can't figure I want to list all the replicated columns in database or publication . Iam sure somebody must have quick script in box.

Conditional Formatting in SSRS Based on a Range of Values

Posted: 20 Sep 2013 08:07 AM PDT

I am building a backup status report in SSRS 2008. Here is a screenshot of what I have so far:[img]http://skreebydba.files.wordpress.com/2013/09/backupstatus.png[/img]I want to change the font color of the Backup Status to red if the status is FAILED using this conditional logic:=IIF(Fields!backupstatus.Value <> "SUCCESS", "Red", "Black")What I want to do now is change the font color of the Instance Group value to red if any of the Backup Status values in that group are FAILED. So in the screenshot above, DEVSQL08 should be displaying in red as well in the left-hand column.Any assistance would be appreciated.Thanks,Frankblog [url=http://skreebydba.com]skreebydba.com[/url]twitter [url=https://twitter.com/skreebydba]@skreebydba[/url]

Considerations for location of Reporting Services databases

Posted: 20 Sep 2013 08:30 AM PDT

Hello experts,I'm working on designing a SQL Server 2008 R2 Reporting Services (SSRS) topology, and a couple of my colleagues asked to see if SSRS can have its databases installed remotely, that is, separately from the other SSRS components. It looks like this is possible, but the question was also raised as to whether we could install it on our main OLTP db server. 1. My instinct tells me that this wouldn't be advisable for performance reasons, but is this feasible depending on the expected load for the SSRS installation?2. Also, does it cost more in licenses to have SSRS host its own databases, or is the cost of an SSRS database instance for the ReportServer and ReportServerTempDB databases included in the license for the main OLTP db server that doesn't have SSRS on it? For example, according to this page, scaling out SSRS seems to increase the cost dramatically.[url=http://www.networkworld.com/community/node/43349]http://www.networkworld.com/community/node/43349[/url]3. That same article also suggests that it is best to separate the web server for SSRS as well, meaning at least one more server for that component. Is this really the necessary best practice across the board, or can it depend on the size of the expected user base of SSRS users (in our case, something like 20-30 users)?Thanks for any help - I am in the middle of reading up on the related documents myself (for example here [url=technet.microsoft.com/en-us/library/ms157293(v=sql.105).aspx]technet.microsoft.com/en-us/library/ms157293(v=sql.105).aspx[/url] and here [url=http://technet.microsoft.com/en-us/library/cc966418.aspx]http://technet.microsoft.com/en-us/library/cc966418.aspx[/url]), but thought I would put this question out there in case someone happens to provide information while I'm researching.Thanks again.- webrunner

How to add more partitions to existed table ?

Posted: 31 Dec 2011 02:54 AM PST

I have a table that contains records of transactions with ID column is primary keyI use partition follow ID column, each partition have 1 million records.CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?Thanks

Partitioning tables has made them much larger (triple in size)

Posted: 20 Sep 2013 03:43 AM PDT

Hi, we recently partitioned some large tables in a database and now the table sizes are 3 times what they used to be. It is rougly the same number of rows as before. we lost a terabyte of space after partitioning them with basically no new data to account for it.I analyzed one of the smaller tables that we partitioned, and the table that had occupied 80,000 pages now occupies over 200,000 pages at the leaf level on the clustered index for the same number of rows. I think we were getting 60 rows per page and now we are getting about 23 rows per 8kb page on average. There are only 4 new columns to this 45 column table and they are small like ints (no large new columns).The tables are clustered and have several non-clustered indexes. I checked fragmentation levels and they are defragmented, and over 80% page space is utilized per page. At most I would expect a 20% size increase due to the fullness of the pages. I rebulit the clustered index and the number of pages actually increased a bit, so its definitely not fragmentation.Is there anything else I can check? Is the table size expected to grow this big after partitioning for some reason?P.S. the table has about 40 partitions (1 per month of data).

SQL 2008 Audit Logs to Text

Posted: 19 Sep 2013 11:41 PM PDT

Is there a way to dump all SQL server 2008 audit logs to a flat text file?

Locking a non existent row

Posted: 20 Sep 2013 04:54 AM PDT

I have a stored procedure that takes a while to run that returns a single result. What I was finding was that this stored procedure was getting called a number of times with the exact same parameters. What I did was create a cache that would save the result. So the next time the SP was called with those parameters it would get the result from the cache instead of running the entire SP.That was working great. There is however 1 issue that I'm trying to resolve.Here are the basic steps the SP takes1. Check the cache2a. If there's a hit, return the result. 2b. If not in the cache, do the processing. 3. Write result to cacheThe problem happens when there are 2 identical calls (from different sessions) at the same time when there isn't a result in the cache. The first call will check the cache and see there's no hit and continue. The second call will check and see there's no hit and continue. The problem happens at step #3. The first session will write to the cache and then right behind it the second session will write to the cache. That second write to the cache fails because there's a unique constraint on the parameters. So what I'd like to do is when that first session makes the initial check in the cache to put a lock on the row it's going to create. That way when the second session makes the check to the cache it will wait until the first session has written to the cache.Any suggestions?Thanks!

testin

Posted: 20 Sep 2013 05:26 AM PDT

testin

Error Message

Posted: 20 Sep 2013 02:50 AM PDT

Hi All,Im moving csv file to oracle database and getting an error, even though the input columns i am convertingf to double-precision float r[8] = output column[Data Conversion [9564]] Error: Data conversion failed while converting column "KioskID" (9296) to column "KIOSKID" (12698). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (9564) failed with error code 0xC0209029 while processing input "Data Conversion Input" (9565). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.[Data Conversion [9564]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "KIOSKID" (12698)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "KIOSKID" (12698)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.Any Idea guysShuan

Why does this code only work if executed with other statements?

Posted: 20 Sep 2013 02:13 AM PDT

I'm trying to run a check for column existence prior to updating a column. My sample code is as follows:[code="sql"]-- DROP AND CREATE Source tableIF OBJECT_ID(N'dbo.DoesNotIncludeColumn') IS NOT NULL AND EXISTS (SELECT 1 FROM sys.objects WHERE name = 'DoesNotIncludeColumn' and TYPE = 'U' ) DROP TABLE dbo.DoesNotIncludeColumnGOCREATE TABLE DoesNotIncludeColumn ( Column1 INT PRIMARY KEY NOT NULL , Column2 nvarchar(4000) NULL )-- DROP AND CREATE Destination tableIF OBJECT_ID(N'dbo.DestinationTable') IS NOT NULL AND EXISTS (SELECT 1 FROM sys.objects WHERE name = 'DestinationTable' and TYPE = 'U' ) DROP TABLE dbo.DestinationTableGOCREATE TABLE DestinationTable ( SourceColumn1 INT PRIMARY KEY NOT NULL , SourceColumn2 nvarchar(4000) NULL )INSERT INTO DoesNotIncludeColumn ( Column1 , Column2 )VALUES ( 254 , 'Source table text string' )INSERT INTO DestinationTable ( SourceColumn1 , SourceColumn2 )VALUES ( 254 , 'Destination table text string' )IF EXISTS ( SELECT 1 FROM sys.objects objz INNER JOIN sys.columns colz ON objz.object_id = colz.object_id WHERE SCHEMA_NAME(objz.schema_id) = 'dbo' AND objz.name = N'DoesNotIncludeColumn' AND colz.name = N'ThisColumnDoesNotExist' ) BEGIN UPDATE e SET e.SourceColumn2 = t.ThisColumnDoesNotExist FROM DoesNotIncludeColumn t JOIN DestinationTable e ON t.Column1 = e.SourceColumn1 END [/code]As I've written it, the code works as expected.However, if I separately run the last part (from 'If Exists down) on its own, I get a message stating[i]Msg 207, Level 16, State 1, Line 14Invalid column name 'ThisColumnDoesNotExist'.[/i]Is there something I can do to run the last bit separately?

Parsing a summary / detail flat file

Posted: 13 May 2013 12:29 AM PDT

My team has a flat file from another system that we need to parse and import into 2 tables. We're currently importing this into a staging table then parsing the staging table RBAR style in WHILE loop. As you can imagine, this is causing us an extraordinary amount of pain. It's taking several days to process one file and the bigger they get...Each line on the file has a record id. There's 1 (the header), 2 (the vendor info), and 3 (the details). A vendor can have 1-N number of 3 records listed after it but (and here's the kicker) none of the detail records have any identifying information that connect it to the vendor info in record 2. The only way we know they are connect is by the order.Example Data:1MyFile051220132VendorID123 StartDate Terminated AgreementNum AnotherCol AnotherCol23ContractNum SaleDate Product Region Col1 Col2 Col3 2VendorID456 StartDate Terminated AgreementNum AnotherCol AnotherCol23ContractNum SaleDate Product Region Col1 Col2 Col3 3ContractNum SaleDate Product Region Col1 Col2 Col3 3ContractNum SaleDate Product Region Col1 Col2 Col3 3ContractNum SaleDate Product Region Col1 Col2 Col3 2VendorID789 StartDate Terminated AgreementNum AnotherCol AnotherCol23ContractNum SaleDate Product Region Col1 Col2 Col3 3ContractNum SaleDate Product Region Col1 Col2 Col3 So all the 3 records contain all the details of the vendor that are above it. But again, there's no identifying information between the 3 records and the 2 records. To add to this burden, the report is a rolling 3 month report of all records, so we can't trucate the tables and start over from scratch because it's not inception to date. Also, we have to delete out the current month (though I'm working to get that changed). Our Staging table looks like this:[code]CREATE TABLE [dbo].[Staging]( [ID] [int] IDENTITY(1,1) NOT NULL, [Extract_Record] [varchar](2000) NULL, [UNID] [int] NULL) ON [PRIMARY]GO[/code]Because we can't immediately tell what record is what and we don't want to mess up the order, we insert all the lines into the Extract_Record column and parse everything out later with substrings. We use UNID to create a unifying ID for all these records and insert them into their tables.Forgive me for scrubbing the heck out of all my column names, but I'm erroring on the side of protecting our business. Here's what our code looks like:[code]BEGIN DECLARE @i INT, @max INT, @rec SMALLINT, @newid INT, @TransactionID INT, @unid INT; --Below code sets unid to identify complete record sets SELECT @i=1,@max=MAX(id),@newid=0 FROM dbo.Staging; WHILE @i<=@max BEGIN SELECT @rec=CONVERT(SMALLINT,LEFT(extract_RI,1)) FROM dbo.Staging WHERE id=@i; IF @rec>1 AND @rec<4 BEGIN IF @rec=2 BEGIN SET @newid=@newid+1; UPDATE dbo.Staging SET unid=@newid WHERE id=@i; END ELSE BEGIN UPDATE dbo.Staging SET unid=@newid WHERE id=@i; END END SET @i=@i+1; END SELECT @i=1,@unid=NULL, @rec=NULL; WHILE @i<=@max BEGIN SELECT @unid=unid FROM dbo.Staging WHERE unid IS NOT NULL and id=@i; IF @unid IS NOT NULL BEGIN SELECT @rec=CONVERT(SMALLINT,LEFT(extract_RI,1)) FROM dbo.Staging WHERE id=@i; IF @rec=2 BEGIN --SELECT @RI=Substring(extract_RI, 2,10) --FROM dbo.Staging --WHERE id=@i; --Below code deletes current month data. Reporting team only requires previous months data WITH CurrentMonth AS(select * from dbo.StagingWHERE LTRIM(RTRIM(Substring(Extract_Record, 97,6))) = Substring(CONVERT(varchar,GETDATE(),112),1,6))DELETE FROM dbo.Staging FROM dbo.Staging serINNER JOIN CurrentMonth cmON ser.UNID = cm.UNID SELECT @TransactionID=@@IDENTITY; --Below code loads differential data from staging table to final reporting table INSERT INTO Summary (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9 ) SELECT LTRIM(RTRIM(Substring(Extract_Record, 2,4))) AS Col1, LTRIM(RTRIM(Substring(Extract_Record, 6,10))) AS Col2, LTRIM(RTRIM(Substring(Extract_Record, 16,3))) AS Col3, LTRIM(RTRIM(Substring(Extract_Record, 19,20))) AS Col4, LTRIM(RTRIM(Substring(Extract_Record, 39,8))) AS Col5, LTRIM(RTRIM(Substring(Extract_Record, 47,10))) AS Col6, LTRIM(RTRIM(Substring(Extract_Record, 57,10))) AS Col7, LTRIM(RTRIM(Substring(Extract_Record, 67,30))) AS Col8, LTRIM(RTRIM(Substring(Extract_Record, 97,8))) AS Col9 FROM dbo.Staging ri LEFT OUTER JOIN Summary rit ON LTRIM(RTRIM(Substring (ri.Extract_Record, 2,4))) = LTRIM(RTRIM(rit.Col1)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 6,10))) = LTRIM(RTRIM(rit.Col2)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 16,3))) = LTRIM(RTRIM(rit.Col3)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 19,20)))= LTRIM(RTRIM(rit.Col4)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 39,8))) = LTRIM(RTRIM(rit.Col5)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 47,10)))= LTRIM(RTRIM(rit.Col6)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 57,10)))= LTRIM(RTRIM(rit.Col7)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 67,30)))= LTRIM(RTRIM(rit.Col8)) AND LTRIM(RTRIM(Substring(ri.Extract_Record, 97,8)))= LTRIM(RTRIM(rit.Col9)) WHERE id=@i AND rit.Col1 IS NULL AND rit.Col2 IS NULL AND rit.Col3 IS NULL AND rit.Col4 IS NULL AND rit.Col5 IS NULL AND rit.Col6 IS NULL AND rit.Col7 IS NULL AND rit.Col8 IS NULL AND rit.Col9 IS NULL; SELECT @TransactionID=@@IDENTITY; END ELSE IF @rec=3 BEGIN --Below code loads differential data from staging table to final reporting table INSERT INTO Detail (TransactionID, Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16) SELECT @TransactionID, LTRIM(RTRIM(Substring(Extract_Record, 2,4))) AS Col1, LTRIM(RTRIM(Substring(Extract_Record, 6,4))) AS Col2, LTRIM(RTRIM(Substring(Extract_Record, 10,20))) AS Col3, LTRIM(RTRIM(Substring(Extract_Record, 30,8))) AS Col4, LTRIM(RTRIM(Substring(Extract_Record, 38,24))) AS Col5, LTRIM(RTRIM(Substring(Extract_Record, 62,8))) AS Col6, LTRIM(RTRIM(Substring(Extract_Record, 70,8))) AS Col7, LTRIM(RTRIM(Substring(Extract_Record, 78,24))) AS Col8, LTRIM(RTRIM(Substring(Extract_Record, 102,10))) AS Col9, LTRIM(RTRIM(Substring(Extract_Record, 112,4))) AS Col10, LTRIM(RTRIM(Substring(Extract_Record, 116,10))) AS Col11, LTRIM(RTRIM(Substring(Extract_Record, 126,4))) AS Col12, LTRIM(RTRIM(Substring(Extract_Record, 130,24))) AS Col13, LTRIM(RTRIM(Substring(Extract_Record, 154,3))) AS Col14, LTRIM(RTRIM(Substring(Extract_Record, 157,17))) AS Col15, LTRIM(RTRIM(Substring(Extract_Record, 174,5))) AS Col16 FROM dbo.Staging ri2 LEFT OUTER JOIN sap.tblRITransactionDetail rit2 ON LTRIM(RTRIM(Substring(ri2.Extract_Record, 2,4))) = LTRIM(RTRIM(rit2.Col1)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 6,4))) = LTRIM(RTRIM(rit2.Col2)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 10,20))) = LTRIM(RTRIM(rit2.Col3)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 30,8))) = LTRIM(RTRIM(rit2.Col4)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 38,24)))= LTRIM(RTRIM(rit2.Col5)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 62,8))) = LTRIM(RTRIM(rit2.Col6)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 70,8))) = LTRIM(RTRIM(rit2.Col7)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 78,24))) = LTRIM(RTRIM(rit2.Col8)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 102,10))) = LTRIM(RTRIM(rit2.Col9)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 112,4))) = LTRIM(RTRIM(rit2.Col10)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 116,10))) = LTRIM(RTRIM(rit2.Col11)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 126,4))) = LTRIM(RTRIM(rit2.Col12)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 130,24))) = LTRIM(RTRIM(rit2.Col13)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 154,3))) = LTRIM(RTRIM(rit2.Col14)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 157,17)))= LTRIM(RTRIM(rit2.Col15)) AND LTRIM(RTRIM(Substring(ri2.Extract_Record, 174,5))) = LTRIM(RTRIM(rit2.Col16)) WHERE id=@i AND rit2.Col1 IS NULL AND rit2.Col2 IS NULL AND rit2.Col3 IS NULL AND rit2.Col4 IS NULL AND rit2.Col5 IS NULL AND rit2.Col6 IS NULL AND rit2.Col7 IS NULL AND rit2.Col8 IS NULL AND rit2.Col9 IS NULL AND rit2.Col10 IS NULL AND rit2.Col11 IS NULL AND rit2.Col12 IS NULL AND rit2.Col13 IS NULL AND rit2.Col14 IS NULL AND rit2.Col15 IS NULL AND rit2.Col16 IS NULL; ENDEND SET @i=@i+1; ENDEND[/code]So, messy, slow and very very annoying. I've got a few thoughts on how we can start to fix it, but before I share I wanted to see what everyone else was thinking. I'm hoping someone has an ephiphany that can help.So, any ideas?Just an FYI: there's no fixing the input file in our immediate future. Just getting this much information was like pulling teeth and the other team takes months upon months (if even that soon) to work changes through their SDLC. So I have to do what I can to mitigate the load issue now with the file that I have.

ScanCount Difference

Posted: 19 Sep 2013 11:27 PM PDT

See below query, i have read this scenario at many place but couldnt collect it .Please explain Why the ScanCount is different [code="sql"]CREATE TABLE ScanCount (Id INT IDENTITY(1,1),Value CHAR(1))INSERT INTO ScanCount (Value ) VALUES ('A') ,('B'),('C'),('D'), ('E') , ('F') CREATE UNIQUE CLUSTERED INDEX ix_ScanCount ON ScanCount(Id)SET STATISTICS IO ON--Unique clustered Index used to search multiple valueSELECT * FROM ScanCount WHERE Id IN(1,2,3,4,5,6)--Unique clustered Index used to search multiple valueSELECT * FROM ScanCount WHERE Id BETWEEN 1 AND 6drop table ScanCount[/code][quote](6 row(s) affected)Table 'ScanCount'. Scan count 6, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(6 row(s) affected)Table 'ScanCount'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.[/quote]

Function returns table from multiple selects

Posted: 19 Sep 2013 10:40 PM PDT

Hi All,OK I have a function that is working exactly as I had wanted it to, but I want to be sure that what I have done has the correctly syntax and that I will not causing performance problems later. The function searches the PersonTable with a list of family names. By using the left and right ID's of the PersonTable it can determine all of the family and sub family members. (e.g. provide the grandparents' sir name(s) and the function will return all of the generations below the grandparents. So the function parses an input string of names into multiple strings of names and then runs individual queries using each parsed name. For each query I insert the results into a table and then return that table from the function.My question is, based on the function below is there anything that I am doing completely wrong by inserting the sub queries into the return table like I have done?Execute the function like this:[code="sql"]SELECT * FROM [fnGetPersons] ('Name 1|Name 2','|') order by name[/code]The function definition[code="sql"]CREATE FUNCTION [fnGetPersons]( @sPersonNames nvarchar(MAX), @sParseChar varchar)RETURNS @PersonList TABLE( ID int, name nvarchar(255), description nvarchar(255))AS BEGIN DECLARE @sPersonName varchar(255) = NULL WHILE LEN(@sPersonNames) > 0 BEGIN IF PATINDEX('%' + @sParseChar + '%',@sPersonNames) > 0 BEGIN SET @sPersonName = SUBSTRING(@sPersonNames, 0, PATINDEX('%' + @sParseChar + '%',@sPersonNames)) INSERT INTO @PersonList SELECT ID, name, description FROM PersonTable WITH (nolock) WHERE ( left_ID BETWEEN ( SELECT left_ID FROM PersonTable WITH (nolock) WHERE name = @sPersonName ) AND ( SELECT right_ID FROM PersonTable WITH (nolock) WHERE name = @sPersonName ) ) SET @sPersonNames = SUBSTRING(@sPersonNames, LEN(@sPersonName + @sParseChar) + 1, LEN(@sPersonNames)) END ELSE BEGIN SET @sPersonName = @sPersonNames SET @sPersonNames = NULL INSERT INTO @PersonList SELECT ID, name, description FROM PersonTable WITH (nolock) WHERE ( left_ID BETWEEN ( SELECT left_ID FROM PersonTable WITH (nolock) WHERE name = @sPersonName ) AND ( SELECT right_ID FROM PersonTable WITH (nolock) WHERE name = @sPersonName ) ) END END RETURNEND[/code]

sql cluster installtion failed

Posted: 20 Sep 2013 12:54 AM PDT

Hi All,When i am installing sql cluster 2008 r2 on win 2008 r2 ,the installtion failed with below error.Overall summary: Final result: Failed: see details below Exit code (Decimal): -2067791871 Exit facility code: 1216 Exit error code: 1 Exit message: Failed: see details below Start time: 2013-09-20 14:21:27 End time: 2013-09-20 14:54:53 Requested action: InstallFailoverClusterDetailed results: Feature: Database Engine Services Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0x1C2074D8@1216@1 Configuration error description: The cluster resource 'SQL Server (xxxxx)' could not be brought online. Error: The resource failed to come online due to the failure of one or more provider resources. (Exception from HRESULT: 0x80071736) Configuration log: C:\Program Files\Microsoft SQL Server\100\xxxxxxxxxxxxxxxxxxxxxxxxxx Feature: SQL Client Connectivity SDK Status: Passed MSI status: Passed Configuration status: Passed Feature: SQL Server Replication Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0x1C2074D8@1216@1Configuration error description: The cluster resource 'SQL Server (xxxxx)' could not be brought online. Error: The resource failed to come online due to the failure of one or more provider resources. (Exception from HRESULT: 0x80071736) Configuration log: C:\Program Files\Microsoft SQL Server\100\xxxxxxxxxxxxxxxxxxxxxxxxxx Feature: Full-Text Search Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0x1C2074D8@1216@1 Configuration error description: The cluster resource 'SQL Server (xxxxx)' could not be brought online. Error: The resource failed to come online due to the failure of one or more provider resources. (Exception from HRESULT: 0x80071736) Configuration log: C:\Program Files\Microsoft SQL Server\100\xxxxxxxxxxxxxxxxxxxxxxxxxx Feature: Integration Services Status: Passed MSI status: Passed Configuration status: Passed Feature: Client Tools Connectivity Status: Passed MSI status: Passed Configuration status: Passed Feature: Management Tools - Complete Status: Passed MSI status: Passed Configuration status: Passed Feature: Management Tools - Basic Status: Passed MSI status: Passed Configuration status: Passed Feature: Client Tools SDK Status: Passed MSI status: Passed Configuration status: Passed Feature: Client Tools Backwards Compatibility Status: Passed MSI status: Passed Configuration status: Passed Feature: Microsoft Sync Framework Status: Passed MSI status: Passed Configuration status: Passed""IN EVENTvwr system log i could see the errors below:"Cluster network name resource 'SQL Network Name (xxxx01)' failed to create its associated computer object in domain 'xxxxx.xxxx.net' for the following reason: Unable to create computer account.The text for the associated error code is: Your computer could not be joined to the domain. You have exceeded the maximum number of computer accounts you are allowed to create in this domain. Contact your system administrator to have this limit reset or increased.Please work with your domain administrator to ensure that:- The cluster identity 'xxxxxxxxo1$' can create computer objects. By default all computer objects are created in the 'Computers' container; consult the domain administrator if this location has been changed.- The quota for computer objects has not been reached.- If there is an existing computer object, verify the Cluster Identity 'xxxxxxx$' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool.Could any one help me out here....THanks in advance
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog