Hot answers tagged

10

I would suggest storing the number as a BIGINT, and simply displaying the number the way you want it. DECLARE @Num BIGINT; SET @Num = 2421402015; SELECT CONVERT(VARCHAR(11), REPLICATE('0', 11 - LEN(@Num))) + CONVERT(VARCHAR(11), @Num); As pointed out by Aaron Bertrand in his comment, it is better performance-wise to not calculate the length of the ...


5

To demonstrate that what you're saying in your question is not true: DECLARE @x TABLE(y VARCHAR(11)); INSERT @x(y) VALUES('02421402015'); SELECT y FROM @x; Results: y ----------- 02421402015 You need to tell us more details about what is going on so we can understand your problem better. But most importantly, you need to consider separating storage ...


5

I would approach this differently. XACT_ABORT_ON is a sledge hammer, you can use a more refined approach, see Exception handling and nested transactions: create procedure [usp_my_procedure_name] as begin set nocount on; declare @trancount int; set @trancount = @@trancount; begin try if @trancount = 0 begin transaction ...


4

Defensive programing requires you to write code that handles as many known states as possible, thereby reducing the possibility of bugs. Checking XACT_STATE() to determine if a rollback can be executed is simply a good practice. Blindly attempting a rollback means you may inadvertently cause an error inside your TRY...CATCH. One way a rollback might fail ...


3

TL;DR / Executive Summary: Regarding this part of the Question: I don't see in what cases the control can be passed inside CATCH with a transaction that can be committed when XACT_ABORT is set to ON. I have done quite a bit of testing on this now and I cannot find any cases where XACT_STATE() returns 1 inside of a CATCH block when @@TRANCOUNT > 0 ...


2

As Aaron has already said, this is due to a bug in SQL Server 2008 / 2008 R2. The bug has been marked as fixed in a later version. It will not be fixed in SQL 2008 / 2008 R2. I have a PowerShell script that I wrote to pull deadlocks from the event buffer, add the missing information if possible, and write the deadlock graphs out as .xdl files. The .xdl ...


2

You can use FOR XML PATH() clause to generate the desired output. I'm starting from your result set and generate the output. DECLARE @Table1 TABLE ([ID] int, [Name] varchar(8), [Examiners Name] varchar(6)) ; INSERT INTO @Table1 ([ID], [Name], [Examiners Name]) VALUES (1290, 'Jun', 'Aron'), (1290, 'Jun', 'Mark'), (1292, 'Takahiro', ...


1

You can concatenate string using FOR XML for each [Name]. Query: SELECT ID, Name, [Examiner Names] = LEFT(Names, LEN(Names) - 1) FROM ( SELECT DISTINCT ID, Name , Names = ( SELECT [ExaminerName] + ',' AS 'data()' FROM names WHERE ID = x.ID ORDER BY [ExaminerName] FOR XML PATH('') ...


1

Since you are getting the error when saving and not executing the package, you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012. Your user's login most likely doesn't have access to the SSISDB catalog (even though he might have access to SQL Server). You need to map the login to an SSISDB user and configure ...


1

I recently experienced this same issue which brought me to this page. @MartinSmith was on to something when he recommended updating your statistics and explain plan. I will like to add that you should also try to ensure you take a look at running jobs/queries which may create locks and thereby slow down response time. In my case the culprit was the job ...



Only top voted, non community-wiki answers of a minimum length are eligible