SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (3)

1
vote
1answer
297 views

Is there a way I can shorten the below query into a single query?

I have the below query. IS there a way I can put this into one single query? If not, can I shrink it further anyway? Please advice. DECLARE @highRegion TABLE(regionId INT, countR INT) DECLARE ...
3
votes
2answers
72 views

Need to convert 21:45 to 21.75

I have a datetime column with a time portion of 21:45; I need to convert it to 21.75. I tried: CONVERT(VARCHAR(5), DATE,108) This converts it to 21:45, now the client wants this be 21.75.
1
vote
1answer
47 views

'cmd' Field Definitions sysprocesses MS SQL Server

I am looking at doing some profiling of connections types and volumes across the day. I am using the sys.sysprocesses to gather the information I require, looking specifically at the 'cmd' field to ...
3
votes
2answers
152 views

Find out what columns are in a trace file

while tackling and unexpected CPU spike that was happening every 10 min one of my colleagues prepared a server side trace as follows: -- Create a Queue declare @rc int declare @TraceID int declare ...
40
votes
5answers
1k views

Date range rolling sum using window functions

I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need: SELECT TH.ProductID, ...
0
votes
0answers
38 views

SQLAzureMW 2012 throws UnknownPropertyException

I'm running SQLAzureMW 2012 to import a SQL Azure Database into my local SQL Server 2012 instance. The process imports a few tables and then throws the following error immediately after line: BCP ...
1
vote
0answers
31 views

SSMS 2012 intellisense is not working on one of the servers?

I dont know if this question is asked - I found similar question - How to enable intellisense, but my case is little different. We have 2 SQL Servers 2012 - one is in the other room, other is in our ...
2
votes
1answer
50 views

what are the costs of enabling extended events?

I have been dealing with deadlocks, specifically getting their information, as you can see on this post here. On this same post Shanky recommended "You must now rely on extended events trace to ...
5
votes
1answer
433 views

A possible infinite recompile was detected for SQLHANDLE

I have been finding strange error messages on the sql error log: Bocss: same deadlock taking place every hour – needs investigating Also lots of recompiles are listed in the error log for other ...
5
votes
1answer
94 views

SQL Server database level audit filtering does not work

I have SQL Server 2014 Enterprise Edition installed. Due to privacy considerations, SELECT statements on private data need to be logged, except for a specific domain user, used by a service which ...
3
votes
0answers
162 views

SSMS Client Error: Internal connection fatal error

I am running SSMS 11.0.5646.0 on a Windows 7.0 Pro desktop connected by UTP directly to same network switch as my development server (Windows 2008R2 Standard, SQL 2008R2 SE with SP3). All within the ...
4
votes
1answer
111 views

missing indexes on distributor database

Looking for missing indexes on my distributor databases, I surprisingly find too many. the tables that need to be indexed are generally 3: MSrepl_commands MSlogreader_history ...
1
vote
0answers
37 views

Queries running slower on production (failover cluster) than on homologation env [duplicate]

I have a Windows Server 2012 cluster with two nodes running SQL Server 2012 for production and a Windows 2012 single server with SQL Server 2012 for homologation. I just restored the production ...
2
votes
0answers
58 views

PAGEIOLATCH_SH and WRITELOG waits

We have a slow overnight process which I was asked to look at with the top waits being PAGEIOLATCH_SH and WRITELOG I have been collecting information from sys.dm_io_virtual_file_stats and can see ...
5
votes
3answers
137 views

Charindex, Getting specific data from a string

Hello I am very new to charindex and have tried to create a query that would extract my needed information but am running into an error. I searched for similar problems on stackexchange as well as a ...
2
votes
2answers
76 views

Query to know who paid more than 10k

I have a table with payment informations, like this: ID COSTUMER_ID DATEPAID AMOUNT 1 1 2012-01-01 50 2 1 2012-02-01 100 3 1 2012-03-01 70 4 2 ...
4
votes
2answers
135 views

Why would SQL Server remain unresponsive after a slow query completes?

I've just finished debugging a problematic SQL Server 2012 table-valued function. It was declared to use a unique column in the table: CREATE FUNCTION [dbo].[myApp_getUserIdsFromXml] ( ...
0
votes
1answer
65 views

List of role permissions including other roles - how can I find out the roles that belong to other roles?

I can see the permissions of a role in a database using the scripts in List all permissions for a given role? However, It does not show me when the role belongs to a another role. For ...
1
vote
2answers
76 views

Partition of a table - how to minimize the time offline? How to check if all the statistics were updated?

As you ca see on the picture, I have a table called "tblsapdispatch" that has 55,707,259 records. Last sunday 30-aug-2015 they run a big update on this table, and I can see that the statistics were ...
1
vote
0answers
72 views

Log shipping restore delay of a minute between iterations

On a test database running Log Shipping jobs the initial Restores to catch up performed the first time I run the Restore job seem to have a 1 minute or so Wait time built in. I would have expected ...
2
votes
1answer
107 views

SQL Server 2012 Always On AG - DBEngine Service account locking

I am having a strange issue and am hoping you guys might be able to help! Problem: I have a 2 node SQL Server 2012 Availability Group Cluster utilising a FSW. Both nodes are using the same DBEngine ...
3
votes
1answer
155 views

SQL Server: dm_exec_requests.wait_time vs. dm_exec_sessions.last_request_end_time

We have a third-party Microsoft Access application that uses SQL Server 2012 as its data store. It is a well known issue that Access often does not tell SQL Server that it has received the results of ...
8
votes
2answers
180 views

Identity column in columnstore index

I have an extremely large table IMO (~137 million rows) with lots of repeated data, lots of NULL columns, and such. I am considering exploring this using a table with a COLUMNSTORE INDEX and I have ...
3
votes
1answer
63 views

Unable to uninstall Sql Server 2012 Express (With SP1)

My Stack: . Windows 10 Enterprise . SQL Server 2012 Express (with SP1 bundled). ISSUE: . Installed SQL to test for backward compatibility. . When I tried to Un-Install the following prompt is ...
2
votes
3answers
48 views

SQL Server 2012 window function

I've the following table which holds status of jobs in a history table. JobHistory jobOrderId | dateAdded | Status -----------|-------------------------|------- 4909 | 2015-08-26 ...
8
votes
3answers
847 views

NOLOCK hint changes order of records returned

There is a clustered index on a table Client field LastName. When I simply dump all records from the table, they appear in the alphabetical order unless (nolock) hint is used as in the query in ...
2
votes
1answer
133 views

Management Studio licensing issues

We have a licensed SQL Server 2012 Standard Edition instance, that for some reason keeps getting license expiration messages in Management Studio. Sometimes it gets this message: Your Microsoft ...
-1
votes
1answer
101 views

How to give a user “create any database” permission in SQL Server? [closed]

Is it different from the dbcreator permission? Can anybody explain the process?
1
vote
0answers
53 views

Migrating SQL 2012 Analysis Services to another server

We have a SQL Server 2012 installation that was built on...let's say a compromised Windows installation. It's had so many installs/uninstalls done on it that nothing runs consistently, and we're ...
1
vote
2answers
63 views

Index planning for many columns with different usage

I've a table with 80 columns and that is a base table for most of the application. Daily load inserts almost 8,000 records and update upto 2,000 records. This table is now having more than 5 million ...
0
votes
0answers
52 views

Error while opening Registered Servers in SQL Server

I just installed SQL Server Management Studio 2012 and I'm trying to open the Registered Server tab to import my xml file with all saved servers, but I'm getting the error described below: "SQL ...
0
votes
1answer
95 views

Aggregation by positive and negative values

I have a table called "PROD_COST" with 5 columns: +----+----------+------+-----------+-------------+ | ID | Duration | Cost | COST_NEXT | COST_CHANGE | ...
2
votes
1answer
297 views

RAID 10 array configuration for SQL Server

I have a question regarding RAID 10 and SQL Server. My plan is to purchase an HP DL380 G9 with the 24 drive config. The question is how to break up the array. The drives will be divided between the 3 ...
2
votes
1answer
64 views

SQL Server 2012 Mirror in a Disconnected state - how do I fix it?

We had a hardware failure last night and we got our servers up and running. After logging into SSMS I find all the mirrored databases in a disconnected state. I've done some searching with ...
1
vote
0answers
43 views

How to trace an SSIS package using Extended Events?

I'm trying to figure out how to trace only SSIS packages accessing a database. I think one way (albeit probably not perfect) to find these queries using SQL are using sys.dm_exec_sessions and checking ...
4
votes
4answers
235 views

Routing data from one SQL Server to another

Is there a way in SQL Server 2012 to have data being written to one table routed to another SQL Server on another host? To clarify, we are using Hibernate Envars to write audit logs. Hibernate Envars ...
1
vote
1answer
111 views

How is an idle SQL Server connection causing blocking?

I have a busy, transactional SQL Server (2012) which is having intermittent problems with blocking (every couple of days or so). I've been using extended events to capture blocked-process reports, but ...
1
vote
2answers
76 views

Defragmenting Indexes Bloats Log Files

I'm having difficulty "optimizing" indexes as a minimally logged operation. Before the index maintenance is performed, the database recovery model is switched from FULL to BULK LOGGED. Depending on ...
4
votes
3answers
225 views

Setting maxdop 1 vs setting the threshold for parallelism high

I am looking at a possible fringe case for parallelism settings. I've long since followed the credence that maxdop 1 is a viable option for Microsoft Dynamics CRM databases. As the nature of the ...
2
votes
1answer
148 views

how to get the information about sleeping processes that are causing blocking?

I have a procedure that allows me to see what is currently running in my sql server. This procedure does not use sys.sysprocess because it has been deprecated. However, I am struggling to find ...
7
votes
1answer
161 views

Update queries slower after enabling SQL Server Full-Text Index

I have an asp.net website with many insert, update, and delete queries running against my database. A few days ago I create a Full-Text Index on two columns of one of tables. After that I realized ...
1
vote
3answers
81 views

SQL Server 2012 - Can't use AlwaysOn HA. What other options do I have?

I have 2 SQL Server 2012 machines. One is production and the other will be for backup. I was looking at using the AoHA but it requires SQL Server 2012 Enterprise for server OS an AD domain I ...
4
votes
1answer
312 views

Count the nodes in a binary tree structure

I need to count the left and right nodes in a binary tree structure (output grouped by joiningDate), given a particular starting node parent id (pid). The tree is stored in the table shown below: ...
2
votes
1answer
93 views

Converting old SQL Server outer join syntax

I'm a Power Builder (PB) developer. I've migrated PB from 10 .5 to 12.5, and now I need to migrate the stored procedures from SQL Server 2005 to 2012 as well. I get a invalid expression error when I ...
0
votes
0answers
40 views

Overwrite TEST db results in PROD db changing to “restoring” state - SQL Server 2012

Using Studio, I've backed up a database from a SQL Server 2008R2 machine that we intend to move to a SQL Server 2012 cluster. The data is from a database that I will call PROD. Using the backup from ...
5
votes
2answers
161 views

Using Left or substring with wildcard in SQL

I am looking to retrieve information from a field, but am having troubles getting exactly what I want. The information I want has no set order and I am also looking for two different things. Here is ...
0
votes
1answer
61 views

SQL Server 2012 Exclude a value

I'm teaching myself SQL, and I ran into a scenario that I cannot seem to solve. Please any help. I have a table called tblRecord with a column called BuyerID, in this case there are two BuyerIDS, and ...
1
vote
0answers
357 views

Change Root Directory on server properties page in SQL Server 2014 SSMS

We did in inplace upgrade from SQL 2012 to SQL 2014 (I know it wasn't the best choice, and it won't happen again, so no need to beat that horse). I have both the MSSQL11.MSSQLSERVER and ...
1
vote
1answer
51 views

Show the error message, after I accidentally clicked “Yes” on “don't show error again”

I was trying to alter an existing Maintenance Plan, but received an error on doing so. Before actually reading it I clicked it away, too late realizing that the text said "Do you wish to hide this ...
1
vote
1answer
154 views

Why is my Target Server Memory slightly higher than my Total Server Memory?

I'm doing a performance test on a high traffic (16000+ users, estimated 1000+ concurrent at peak) Microsoft Dynamics database. The server has gone on line a few months ago, and I'm doing some tests to ...