New answers tagged

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 ...


0

Here is one way, note that this effectively limits concurrency, so don't expect stellar performance at scale. INSERT dbo.TableName(col1, col2) SELECT @ClientName, COALESCE( (SELECT MAX(col2) + 1 FROM dbo.TableName WITH (UPDLOCK, HOLDLOCK) WHERE col1 = @ClientName), 200000); Of course if anyone ever deletes any rows from this table, you'll ...


0

Some of your question is kind of confusing to me so I am asking some clarifying questions. It looks like you tried to hide your server and instance names in some of the messages, but did not in other places. From the PowerShell error message it shows you were going after a server named 'serverr2' and the instance name of 'serverr2'. My first thought is you ...


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 ...


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 ...


0

Watch for the high activity again; when you see it, start a server side trace or if necessary use Profiler briefly to see what's going on. Alternately, use a packet sniffer like Wireshark to capture raw wire activity. Check dm_exec_cached_plans to see if that gives any idea what's going on. Watch dm_io_virtual_file_stats to see which files in particular, ...


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 ...


0

Try single quotes in your powershell script. I beat my head against this for an hour before i switched that out, and it worked like a charm.


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 ...


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 ...


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

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('') ...


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

Here is a very nice script by Pavel Pawlowski to do the job: http://www.pawlowski.cz/2011/03/cloning-user-rights-database/ Main advantage: script/copy Role Memberships script/copy Object Level Permissions script/copy Database Level Permissions I'm NOT the author of this script. The script is copy-pasted from this link on Pavel Pawlowski blog, see the ...


4

CONCAT was introduced in SQL Server 2012; there is no way to make it work in SQL Server 2008 R2. From the documentation: There's also no way to make it fail in 2012+, even with compatibility level. So have your people check SELECT @@VERSION; on both servers; you'll find that where CONCAT fails it is < 11. In order to make your code compatible with ...


2

Look for files on your drive called .mdf (database files) and .ldf (log files). The filenames don't neccessarily have to be the same as the name of the database. At a very minimum, there will be one of each type for any given database, but it's quite common to have multiple database files, often spread over different volumes. Move those files to their ...


2

I used this code to extract the FileName with Extension. UPDATE TableName set ColumnName= right(Image_Link, CHARINDEX('/', REVERSE(ColumnName) + '/') - 1) And this code to extract and update only FileName UPDATE TableName SET ColumnName= REVERSE(SUBSTRING(REVERSE(ColumnName),CHARINDEX('.', REVERSE(ColumnName)) + 1, 999))


-1

My company has just developed this new tool (free) that helps you to easily extract scripts for SQL databases, can do comparison, can launch WinMerge for quickly comparing scripts to live database, and can also synch differences both updating the scripts or applying the changes to the database (except for tables, which would involve more complexity and more ...


1

SQL Server 2014 support parallel DML in the form of SELECT INTO and SQL Server 2016 support parallel INSERTs into heap via INSERT SELECT providing the TABLOCK hint is used on the source table, this also works on clustered column stores, I am yet to try this out with clustered indexes though.


1

Thanks to DirtyPaws. SQL Statement with Powershell Syntax: #Credentials $SQLServer = "SQL ServerName/IP Address" $SQLDBName = "DatabaseName" $uid ="MySqlUser" $pwd = "MySqlUserPassword" #Establish SQL Connection $connectionString = "Server=$SQLServer;Database = $SQLDBName; User ID = $uid; Password = $pwd;" $connection = New-Object ...


-1

Do a modulo 0.5: Insert Into b (fv, fv1) Values (.8032, 0.8032 % 0.5); See fiddle


1

You can replace fv1 by a Computed Column: Create Table #b ( fv decimal(16,4) , fv1 AS ( CASE WHEN fv > 0.5 THEN fv - 0.5 ELSE fv END ) PERSISTED )


-1

You want to set the field fv1 to be 0.5 less than the value in fv? UPDATE #b SET fv1 = ISNULL(fv, 0.00000) - 0.5 You could also make fv1 be a computed column which would make it automatic.


2

This is not clear what output is needed. You are trying to convert text strings to int. This obviously does not work. Besides this type of formatting might fit better outside of SQL Server. If you want something like 5 Hours 15 Minutes you can try this query: [Time Spent(In hrs)] = CAST( SUM( DATEDIFF(hour, ...


4

A much better way to monitor is using sp_whoisactive - from Adam Machanic. I use sp_whoisactive with below parameters : You can even log the output to a physical table for future analysis. It allows you to capture query plan, blocking, etc as well. EXEC sp_WhoIsActive @filter = '', @filter_type = 'session', @not_filter = '', ...


2

IF you are referencing Activity Monitor for SQL 2008+ First make sure you have your requirements in place. Requirements to use Activity Monitor The permission necessary to view Activity Monitor is VIEW SERVER STATE To view the Data File I/O pane, besides VIEW SERVER STATE, the login must be granted CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY ...


-1

You can simply change the initial size of the files and accordingly shrink the file after that. The issue is that updated size is not reflecting in the file size and hence the available space is in negative. Hope this helps.


-1

I ran this query then pasted the results back into query window to drop all the tables: SELECT 'DROP TABLE ' + NAME from sys.tables ORDER BY NAME If you want to delete all tables but keep those with names that started with A, B, C or D: SELECT 'DROP TABLE ' + NAME from sys.tables WHERE NAME NOT LIKE '[ABCD]%' GROUP BY NAME


5

Alternate solution Instead of using xp_cmdshell, which comes with a lot of security risks, you could run something like this from the command prompt: osql >output_file.txt -S myServer\myInstance -E -Q "PRINT @@VERSION" The -S switch denotes the name of the server and instance, -E means Windows authentication (you could instead use -U and -P for ...


9

This command: ECHO 1>C:\Pharmsuite\Vercheck.txt Does not do what you think it should do because 1> has a special meaning. This problem can be fixed by adding a space before >: ECHO 1 >C:\Pharmsuite\Vercheck.txt Or in your case: SET @CMD = 'ECHO '+@Ver+' >'+@Vercheck It also works with this command (making sure there is a space before ECHO ...


5

The problem is, 1> has a specific meaning. It means "redirect standard output", whereas 2> would be "redirect standard error". > by itself is short-hand for 1>. You can try: SET @CMD = 'ECHO>'+@Vercheck + ' ' + @Ver Since you can perform redirection before the rest of the arguments to echo.


2

This should work: SELECT LEFT(fieldtosplit , CASE WHEN LEN(fieldtosplit) > 1000 THEN 1000 ELSE LEN(fieldtosplit) END ) , CASE WHEN LEN(fieldtosplit) <= 1000 THEN '' ELSE SUBSTRING(fieldtosplit, 1001 , CASE WHEN LEN(fieldtosplit) > 2000 THEN 1000 ELSE LEN(fieldtosplit)-1000 END ) ...


6

Assuming that the [PhoneField] is 10 digits, such as '1234567890' then you could use STUFF to put the proper values in. E.g. select STUFF(STUFF('1234567890',7,0,'-'),4,0,'-'); But please note that this only answers formatting a phone number for the 10-digit answers. If you are using internationally dialed numbers, you need to investigate the various ...


2

there isn't a function that will do it for you. You can cast the phone number as a string then then parse out the parts and add hyphen declare @phone int set @phone = '1232223333' select SUBSTRING(cast(@phone as varchar(10)),1,3)+'-'+SUBSTRING(cast(@phone as varchar(10)),4,3)+'-'+SUBSTRING(cast(@phone as varchar(10)),7,4)


1

You could try something like this query: Declare @d1 date, @d2 date, @numreturned int Set @begindate = '01/01/2015' Set @enddate = '01/12/2015' Set @numreturned = 0 SELECT @numreturned = COUNT(DISTINCT CAST(csdate as date)) FROM firsttabletocheck WHERE csdate >= @begindate and csdate < @enddate; IF DATEDIFF(day, @begindate, @enddate) > ...


0

If using excel to get the data , you can always depend on external data link function. Create new excel 2013 and click data ribbon. Click "from other sources", click "sql server" After that follow the wizard. It is better that you create a specific view to pump the data to excel as the wizard only recognize table and view. If you want to distribute the ...


0

From what i experienced using indexes in big table. It seems the sql engine will optimally used index in a set of columns. For example, your primary key consist of set of columns. But the index of that set will not be used in sql optimation unless the whole set of column is used in the query. That is why your original query did not use the index. So, ...


0

Based on your schema it looks like trans_seq can't be null so I would remove the isnull from the select as that would have a small amount of CPU overhead in just checking the value that is returned from the max function. SELECT max(trans_seq) +1 FROM inv_inventory_journal WHERE organization_id = @P0 AND wkstn_id = '600' Also if your query is ...


1

Could this be related to caching with Kerberos? (just a guess though might be unrelated) http://blogs.technet.com/b/tspring/archive/2014/06/23/viewing-and-purging-cached-kerberos-tickets.aspx


1

One more thing. If these tables are very larges even if you have good indexes, you should updates these tables rows in cycle, because the transaction log is growing every time and big transactions takes more times. Example: CREATE TABLE UpdateTable(id int IDENTITY(1,1), name varchar(200), namenew varchar(200)) INSERT INTO UpdateTable VALUES ...


1

You can query from sql cache for long time: SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS QueryText, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st You ...


1

As the other two answers correctly state, Assemblies are loaded into a particular database and are not system-wide (though I'm fairly certain that the assembly_id value is unique system-wide). This means that they are backed up, and restored, with each database that they are loaded into. Also, the enabled / disabled setting of CLR Integration (via ...


0

I am not near a computer with any database on it to try this idea; however looking at your "expected output", the invoice or sales number is an incrementing number (assuming invoice 208 is really a typo and should be invoice 108). Have you tried 'Order by InvoiceID ASC, Debit Desc'? It will keep payments for each invoice under the invoice. Another ...


0

Realizing this situation is long over I'm still going to put in my 2 cents for anyone else finding themselves in a similar situation. You have a fair amount of corruption in that table. Run a DBCC CHECKDB on the database ASAP. I hope you have a good & recent backup. That's going to be your best bet for recovery. If you don't have a recent backup ...


3

The main thing you need is to generate the required order sequence using row_number(). seq = row_number() over ( partition by t.CustID order by t.InvoiceID, t.Date, CASE WHEN t.S_Type = 'Receipt Voucher' THEN 1 ELSE 2 END ) than use it for calculation of the cumulative balance ; WITH cte AS ( SELECT CustID, ...


2

Below should work. You should keep some error handling in your code using try.. catch unless you are doing this for educational purpose only. Declare @numapproved int, @totalnumpending int, @nummanpending int, @numsopending int Create Table #madeupland ( RamaldaID int IDENTITY(1,1) PRIMARY KEY ,cofeetypes varchar(50) ,mangerapproved ...


4

The simplest way to read and increment a numeric value in a text file would be to not do it in SQL Server, though you can still use SQL Server to cause it to happen. You can create a CMD script as follows that accepts a single input parameter for the file name and then increments the value inside of that file by 1. If the file name does not exist, it will ...


2

@RayofCommand, has already given an appropriate answer, but will like to add more which is very helpful and easy to track for any corrupt databases using SQL Server Integrity Check from ola hallengren. Is the maintenance plan Check Database Integrity Task enough ? Well, for this should check some more details on this as explained by Kendra on DBCC ...


1

As already pointed out by Martin Smith, a CLR aggregate is probably your best bet. Again, storing your results in a temporary table is a good idea. Here is another possible T-SQL implementation which uses UNPIVOT/PIVOT: IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test; CREATE TABLE #test ( id int, name varchar(128), car ...



Top 50 recent answers are included