SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (1)

0
votes
0answers
2 views

System Database Migration and Security - Log Shipping or DB Mirroring

I've setup log shipping between two servers (they used to be mirrored). What is the simplest or most efficient way to mirror the system databases between the primary and secondary? Is it just a ...
-1
votes
0answers
12 views

Stored procedure generating dynamic SQL is slow for some paramaeters

I'm having issue with a stored procedure that generates dynamic SQL, it performs ok for some parameters but very slow for some parameters. This has been an issue everyday and we are making some ...
0
votes
2answers
56 views

Update single table with mutually exclusive threads

I have 2 threads (for example 2 different sql sessions) reading rows of the same table in parallel. They will do some processing and update a column to inform the row has been processed. Thread id:...
2
votes
0answers
8 views

Remove Data From Memory when Using SSAS Tabular In-Memory Mode

I use SSAS Tabular in In-Memory mode. I also use partitions to divide my data. So I will have one partition per month. AFAIK, when using In-Memory mode, server keeps processed data in memory forever....
4
votes
1answer
52 views

Understanding SQL Server statistics better

I have a table with 5,000,000 records with the DateOfBirth column spread between 1950 and 2007. The statistics histogram for the index only has two RANGE_HI_KEYs. I feel like the histogram should have ...
1
vote
0answers
12 views

Get missing index statistics in SQL Server 2014 [duplicate]

For example consider a database that has many SP and many query execute on it using ORMs. It's very time consuming that check every SP or profile all queries that ORMs generate for performance and ...
1
vote
2answers
36 views

How can I hide a column using a Select statement?

declare @bid varchar(4) = '1007' select sr.release_date, sr.customer_id, sr.customer_name, sr.item_id, sr.item_desc, sr.order_no, sr.qty_on_hand, sr.release_qty, ...
1
vote
0answers
21 views

SQL Server Maintenance Plan Help

SQL Server Maintenance Plan Help I'm using SQL Server Maintenance and found a way to selectively turn on and off various features of the plan via it's associated job shown in Figure A. This can be ...
8
votes
2answers
355 views

Is there a way to make this select retrieve the same results with a single seek?

Is it possible to retrieve the same data as the following with a single seek or scan, either by modifying the query or influencing the optimizer's strategy? Code and schema similar to this are ...
1
vote
1answer
31 views

SUM() 3 columns with alias into a fourth one

This is one part of my procedure: select nome, --casos expedidos (- 3 meses) isnull((SELECT count(cod_rep) FROM tbReps where ...
0
votes
0answers
14 views

SQL Server 2014 Availability Group Listener only visible to primary node of AG

I'm having some trouble configuring the Availability Group Listener for SQL Server. Here's my current setup: I have one server, lets say server DC, which acts as the domain controller for AD DS with ...
1
vote
0answers
59 views

The query processor ran out of stack space during query optimization. Please simplify the query

I'm using SQL Server 2014. When running this query: DELETE FROM shuser WHERE datecreated > '6/21/2016' AND FkShUserCreatedBy = 1 GO I get an error: Msg 8621, Level 17, State 1, Line 1 ...
7
votes
0answers
135 views

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)

Consider these two functions: ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) As far as I understand, they produce exactly the same result. In other ...
1
vote
0answers
26 views

SQL Replication Monitor does NOT show publication properties - Transactional Replication

The setup I have is as follows: a) Version SQL Server 2014 SP1 CU6. b) publication is on a separate availability group [AG1] and subscriber is part of another AG [AG2]. c) Distribution is ...
0
votes
0answers
32 views

SQL Server Memory Configuration [duplicate]

My Server has Total RAM 200GB. I have set 80% of total RAM for SQL Server as it needs more memory. Just want to know if there is any fixed number or method to finalise amount of memory to be set for ...
0
votes
0answers
36 views

Temp DB configuration

My TempDB disk size is 100GB. Out of that I have made below split: 80GB/8 = 8 Data files. 15GB for Log file. Remaining 5GB I have left for autogrowth for last eight datafile and log file(...
0
votes
1answer
31 views

Bulk Update Based Off Values In A Table

In Sql Server 2014 I need to run an Update statement that will update NULL values with values found previously in the table. To better illustrate, here is a very small subset of my data Create Table ...
1
vote
1answer
36 views

Why does SQL Server Mangement Studio not script Indexes by default?

Recently I realized by default, scripting the database does not including indexes, and I have to change the option every time. Could anyone please explain the reason? Is there any insight about this ...
2
votes
2answers
74 views

Should I install SQL Server 2012 SP3 when I'm already running SQL Server 2014?

We've been running SQL Server 2014 for almost a year now, but SQL Server 2012 SP3 continues to appear in the Windows Update list (as an optional install). It seemed ridiculous to install a service ...
1
vote
0answers
30 views

SQL Server 2014 Express database size limitation exceeded, suddenly cannot be exceeded anymore?

I have a database in SQL Server 2012 Express. I inserted 14 million rows to test index performance and everything was working fine. The database's PRIMARY file group was at over 21 GB. I had ...
0
votes
0answers
17 views

Mix mode auth password,is it sa password?

During SQL Server 2014 Installation,I have set up Mixed Mode Authentication and given a password for it.My question is when I set Mix mode,did I set up password for SA login.So if want to use SQL ...
0
votes
1answer
28 views

Visual Studio 2010 Shell Error - is it Causing this SQL Server error?

Visual Studio 2010 Isolated Shell has the infamous "Invalid License Data. Reinstall" error. I have SSMS 2014. I have tried reinstalling several times, I installed Visual Studio 2013 but SSMS doesn't ...
0
votes
0answers
12 views

Replication issue SQL Server 2014

I set up a merge replication between one Publisher and a subscriber[ the same tables on both] and everything seems to be working fine and there was not a single conflict in the initial setup and ...
-1
votes
1answer
169 views

SQL query failing because of commented part of T-SQL query

I am executing below query but I am facing issue. Note :SQL 2008 R2 and SQL 2014 running with two diff serv accounts on Win 2008 R2 and Win 2012 R2. TCP Ports, Firewall ports are all enabled along ...
0
votes
3answers
74 views

Why does my hard-drive fill up when executing a query?

I have a very complex SQL query that I execute in SQL Server Management Studio. As the query is running, I notice that my harddisk is filling up, until the point it's completely full (I'm talking a 10+...
5
votes
1answer
87 views

When you switch Table A to Table B, does the index data get switched as well?

I currently have a rather large table (5-7 million rows). This table is rebuild regularly by a procedure which builds the data in a staging table, then switches the data into the production table ...
0
votes
1answer
15 views

Error while executing query using send attachment

when i am executing the below query, I am getting error as:- Msg 22050, Level 16, State 1, Line 4 Failed to initialize sqlcmd library with error number -2147467259. This is my query:- EXEC msdb.dbo....
0
votes
0answers
26 views

Run SQL Agent Jobs in Sequence

While there are other questions out there in forums, I notice that those are several years old, and I hope that things have changed by now. I am using SQL Server Management Studio 2014 and I have to ...
2
votes
4answers
76 views

Query To Return Data From MAX(ID)

I need to return only the 1 row of the MAX(ID), but my query is not returning such. This is the result set that I get returned id bbid indate st val 200817 16543 01/01/16 1 ...
0
votes
1answer
150 views

How to load entire database into memory?

I want to load my entire database into memory, but how can I do this? I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory. When I do select count(*) from ...
2
votes
1answer
62 views

How do I place an UpdateLock on a row with an index

Let's assume I have a table like this on a SQL Server 2014: CREATE TABLE Accounts ( Id int IDENTITY(1,1) NOT NULL, OwnerId int NOT NULL, Balance bigint NOT NULL CONSTRAINT PK_Account ...
0
votes
1answer
23 views

Group Data and show group as separte column [duplicate]

We have following Data ---------------------------- RollNo | Seat ---------------------------- A1234 | GN A1233 | GN A1235 | GNF A1238 | GN A1233 ...
1
vote
2answers
78 views

XML column performance in TSQL

I'm finding very limited reading on how a large xml column affects table performance in sql server 2014/6. For reference, it is a 36gig table and minus one xml column, it is 36megs. Some queries ...
2
votes
1answer
47 views

SQL Server 2014 Reporting Services issues

I have set up SSRS on MS SQL 2014 Dev Edition using the ReportServer built in account as the Service Account. My machine name is: POUYASAGER My local login is pyous I have given the ReportServer ...
20
votes
1answer
268 views

SQL Server 2014: any explanation for inconsistent self join cardinality estimate?

Consider the following query plan in SQL Server 2014: In the query plan, a self-join ar.fId = ar.fId yields an estimate of 1 row. However, this is a logically inconsistent estimate: ar has 20,608 ...
3
votes
3answers
103 views

Query Spatial Data for Nearest Neighbor for each group

I want to query spatial data for nearest neighbor. I am using this article and the following query works perfectly: SELECT TOP 1 [Location].STDistance(@Location) FROM [DS1] WHERE [Location]....
0
votes
0answers
113 views

Which is preferable With clause or formulas in from clause

Query with with clause with temp_table(fName,sCode,sDesc) as( select case left(shipping_info.ship_description, (len(shipping_info.ship_description) - 11)) when 'John Doe' then 'Johnathan'...
0
votes
0answers
33 views

TCP Provider Issue

Error : TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to ...
-1
votes
0answers
20 views

How to prevent database server from lagging when auto back up of data to backup DB is in progress?

We have a windows 2008 r2 server with MSSQL server running on it as our application's database server (we use it 24 hours). It has auto back up every midnight (as it is the time when less users are ...
-3
votes
1answer
52 views

Script out spaceused stored procedure to edit it

Is it possible to script out the sp_spaceused stored procedure so I can edit it to include log space information and sort of create my own custom stored procedure? I would like to put the results in ...
3
votes
2answers
42 views

Changing Database Transaction isolation level to SNAPSHOT for SQL Server replication “distribution” database

I have a couple transactional replication publications that make large changes (nearly 1 billion records) to a publication article. These transactions are captured by the log reader and stored in the ...
1
vote
0answers
13 views

What settings / statements are pushed to subscribers in transactional replication?

Version: SQL Server 2014 (Web edition) Replication: Transactional Are the following pushed to subscribers in transactional replication? SET_QUOTED_IDENTIFIER statement; SET ANSI_NULLS statement; ...
17
votes
1answer
215 views

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" ...
2
votes
0answers
50 views

“Access is denied.” for xp_cmdshell for network share

On a SQL 2014 SP 1 instance a SQL Agent job I'm using xp_cmdshell to return a list of files from a network share. This has recently stopped working and returns "Access is denied." The command is ...
11
votes
1answer
152 views

Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?

I need to provide real-time, or almost real-time, data for SSRS and Tableau reporting. I don't want the production OLTP system to be negatively impacted by long running queries. Will running a large ...
0
votes
1answer
37 views

SQL Server 2014 - AlwaysOn HA - Always Synchronizing

I have a Windows Server 2012 R2 cluster with SQL Server 2014 configured in a HA configuration. The secondary databases always say Synchronizing when the data movement is happening. Is this normal? ...
2
votes
0answers
49 views

SQLCMD -L Returns Bizarre Response Instead of Server List

I have a server with three installed SQL Server 2014 instances with Service Pack 1 (build number is 12.0.4213.0) installed. When I run SQLCMD -L on the server I am getting the bizarre response below. ...
6
votes
2answers
93 views

Restore database with a FileTable

We have a SQL Server 2014 (build 12.0.24457.0) database with two tables in, both are FileStream tables, the biggest has 2979466 rows, SUM(cached_file_size)= 40564905472. We have tried to restore this ...