Tag Info

Hot answers tagged

11

This construction is not currently supported in SQL Server. Applying one of the workarounds listed in the Connect item reporting this deficiency, your query could be rewritten as: WITH UpdateSet AS ( SELECT AgentID, RuleID, Received, Calc = SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER ( PARTITION BY ...


7

I realize this doesn't answer the question "why does the log file have multiple entries for 'Login succeeded...'", however since you also seem to be concerned about the other log entries being "drowned-out" by these low-importance login messages, I thought I'd add the following as an answer. Instead of using SQL Server Management Studio's built-in interface ...


5

This is probably caused by a blocking process. 3-5 times a day this is blocked by some update or other write that takes ~40 seconds. Your SELECT blocks until it can continue. If this is indeed the case, the problem can be identified using the Blocked Process Report Event. You will have to enable this event and set the threshold to ~35 seconds. See blocked ...


5

Here's a better script. It doesn't use the awful INFORMATION_SCHEMA views. Among other things, these views do not expose foreign keys against unique constraints; only against explicit primary key constraints. It doesn't place GO inside T-SQL, which won't work if you execute the command dynamically (since GO is a batch separator for interactive tools like ...


4

I appreciate that you are looking for anecdotes about the experience of using mirroring; however this site is not really the place for opinion. Having said that, you may find the following excerpt from Microsoft.com regarding database mirroring helpful: Database mirroring is a simple strategy that offers the following benefits: Increases availability ...


3

Restarting the server should be enough - those worktables should clear out. But I'd probably start it up in single user mode (-m) to prevent other processes from creating worktables before you successfully remove those files. Then redefine the files required for tempdb; perhaps deleting unnecessary files, changing sizes, etc. You should also ensure you have ...


2

Unfortunately, there is no way directly from BCP to get the header as well as the rows out in a pretty format, even with the out parameter HOWEVER, there is a workaround. You can do this: SELECT 'HeaderCol1Name', 'HeadCol2Name' UNION ALL SELECT CAST(C.Col1 AS VARCHAR(255)), CAST(C.Col2 AS VARCHAR(255)) ... FROM (SELECT <Your original query>) AS C ...


1

The reason your function takes ages is because you have empty values for actual in UTF8Encoding. The patindex expression returns 1 when you check for an empty actual so you never exit the inner loop. You can fix that by adding and actual <> '' to the query against UTF8Encoding. Next issue is where you use @expected as parameter to nchar(). The ...


1

Try Agent Tokens instead, eg sqlcmd -S .\sql2012 -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "c:\temp\sqlcmd$(ESCAPE_NONE(DATE)).csv" This worked for me. Just a reminder, bcp is a more appropriate tool for this kind of extract; you won't have the header and "row(s) affected" problems which you're not dealing with here.


1

To be certain you are configuring tempdb file sizes as intended, you should use T-SQL instead of the SSMS user interface. To resize files, you can use something like this: ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, SIZE=1GB, GROWTH=1GB); You'd need to execute this once for each logical file in tempdb. NAME=tempdev_2, NAME=tempdev_3 etc. I'd ...


1

Okay I found the solution after searching and testing many things. The issue was in the Metadata (the connection between the Source Query and the Data Conversion), the length of the field was not matching the one from the source query anymore, which is to me, very odd as I thought that the Metadata would be automatically refreshed when you save and compile, ...



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