Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
0
votes
3answers
21 views
Are the switches for the SQLCMD command case sensitive?
I have MS SQL Server 2014 Management Studio installed on two computers, both of which run on Windows 10. The switches for SQLCMD command are case-sensitive on one computer, but it is not on the other ...
2
votes
1answer
60 views
Troubleshooting a slow performing encrypted stored procedure
I have a stored procedure which is part of a vendor supplied system. The stored procedure is encrypted so we can't see the code.
The stored procedure has all of a sudden started to run slow.
The ...
0
votes
0answers
11 views
Log Shipping and Transactional Replication
Is it possible to have a Log Shipping Secondary database be a publication database for transactional replication?
In my research I discovered the the database for log shipping has to stay in no ...
0
votes
0answers
25 views
SQLServer JDBC Driver]Object has been closed
I had few queries in code which were running in loop. They all were wrapped inside a code function. There were frequent deadlock with the queries. Because I do not understand the logic too well, I was ...
0
votes
0answers
13 views
When I update end time of a tblSubTaskDetails, how to automatically update all tables with sum of ProjectID, TaskID and SubTaskID? [on hold]
When I update tblSubTaskDetails, sum of particular ProjectID, TaskID, SubTaskID should be inserted in the tblSubTask table in ActualHours.
Like wise it should update tblSubTask with sum of particular ...
7
votes
2answers
107 views
Increase Max Memory setting on a SQL Server Production
I have a production box with 5 instances on.
Version: SQL Server 2014 SP3 Enterprise.
I discovered that even though the machine has ~400GB memory assigned, the combined MAX MEMORY settings on all 5 ...
1
vote
1answer
34 views
Data Loss when forced failed over to secondary replica on Sql Server 2014
Setup:
2 Availability Groups
2 Replicas on Synchronous Commits.
1 Replica on Asynchronous Commit.
Automatic Failover configured for the two replicas on Synchronous Commits.
Manual Failover ...
-1
votes
1answer
28 views
Compare multiple rows and merge into one row
I have a table with 4 columns.I need to merge the rows into one record if the difference of the end_date and start_date of the next row is less than 120 days difference. This merge should be for each ...
6
votes
1answer
463 views
Substring join or additional table, which is faster?
I have a case that come up regularly at work. I have many tables that use a 4 character string as a foreign key : G191
The G is a sort of category, the 19 is the year, and the 1 is an instance.
...
1
vote
1answer
57 views
How can Excel communicate with a remote SQL Server that is disconnected from the Internet?
My setup involves three computers:
1. An application sits on an Internet-connected Windows Server in the Cloud.
2. This application is connected to a SQL Server instance on another Windows Server in ...
0
votes
0answers
8 views
Transactional Replication - Source/Dest table with different structure
I need to perform Int/BigInt migration on one of our large tables.
The Identity column needs to be changed from int to BigInt.
It's a very large table, hence I wanted to copy over all the data and ...
0
votes
1answer
15 views
MSX Job deployment: Not creating job on certain servers
I am deploying a new index maintenance jobs through MSX to about 100 servers, I am in the process of manually checking that each job has been created and I have already discovered that for some ...
0
votes
0answers
23 views
sys.dm_db_index_usage_stats showing incorrect figures for last_user_update
As the title suggests, I seem to be getting unexpected readings from the last_user_update column in the index_usage_stats DMV. It seems to update the datetime value everytime I run a script inserting ...
1
vote
1answer
49 views
Overlaps and Gap grouping in SQL Server
I am trying to find gaps in my data and then group those. The table I am working with looks like this:
runid|year |road|start |end
01010| 9 |2 |0.000 |0.585
01100| 9 |2 |0.585 ...
1
vote
0answers
14 views
How to log SSAS $system.discover_sessions to a SQL Server Database
We are running SQL Server 2014 and SSAS on the same box. Every now and then our SSAS service appears to freeze. When it does I check the msmdsrv.log file and it tells me that spid x is blocking ...
0
votes
1answer
80 views
Count Consecutive Codes
I'm searching for a query (cannot figure it out myself at the moment with the help of already found queries around here. The quest I have is the following one: I have a view that gives following ...
14
votes
3answers
521 views
Concatenate all values of the same XML element using XPath/XQuery
I have an XML value like this:
<R>
<I>A</I>
<I>B</I>
<I>C</I>
...
</R>
I want to concatenate all I values and return them as a single string: ...
0
votes
1answer
64 views
Why is sp_Blitz executing for more than 5 hours [closed]
Screenshot
I am running sp_Blitz on my SQL Server 2014 with 93 databases. It has been running for more than 5 hours and still won't complete. I am able to run sp_BlitzWho,sp_BlitzIndex,sp_BlitzFirst ...
0
votes
1answer
69 views
Max Workers available and yet pending task high
While exploring the data from system health extended events for event so server diagnostics I see for column query processing XML as below:
Max workers - 2944 ( based on 80 schedulers on my server)
...
5
votes
2answers
348 views
How encryption in SQL login authentication works
I am going through the below link
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server
and in that it says
When SQL Server logins are used, SQL Server ...
1
vote
1answer
77 views
Upgrade SQL Server 2014 Failover Cluster to SQL Server 2016 Availability Group on Windows Server 2016
In our current set up we have 2 node SQL failover cluster in data center DC1 with mirroring enabled. And similarly in DC 2 a 2 node SQL failover cluster. DC1 acts as primary via DB mirroring in one ...
1
vote
0answers
44 views
SQL Server Transaction Log Full affect on FileStream [closed]
These are general question I would like to receive some inputs from experts with BLOB SQL Data.
How will SQL Server Transaction Log (.ldf file) linked to SQL Server FileStream
Is it different from ...
12
votes
2answers
539 views
Can SQL Server create collisions in system generated constraint names?
I have an application which creates millions of tables in a SQL Server 2008 database (non clustered). I am looking to upgrade to SQL Server 2014 (clustered), but am hitting an error message when under ...
3
votes
2answers
95 views
Checking IFI enabled on SQL server below 2016
I am using in one of my reports to check for status of IFI being enabled on SQL server.
It works well when using dmv sys.dm_server_services for SQL2016
However, in SQL2014 and SQL2012 i see ...
5
votes
2answers
179 views
Run Powershell Script From Agent As Administrator
I need to recycle an AppPool in IIS on my server when the SQL Server Service starts.
The route I am taking is to have a startup stored procedure which runs an Agent job that has a Powershell job ...
1
vote
1answer
74 views
I want to change to Dynamic SQL Query instead of CASE statement [closed]
I have two tables as below and description is given in below
Employee table
id EmployeeID departmentid department1 department2 department3
1 300 100 IT Finance ...
1
vote
1answer
70 views
SQL Server TDE Vs SQL Server Database Backups
Can anyone please help me understand below case:
Kindly advise on below case:
SQL Server is on 2014 SP2 GDR version
I read Compression and TDE does not go hand in hand .
I have 2 PRD Environments:...
2
votes
1answer
48 views
Accurately simulate SQL Server work load with diskspd
We were building a new node in an alwayson availibility group and this new server was on a different storage than the other nodes in the cluster. Since it was a separate storage we wanted to make ...
1
vote
0answers
37 views
Current Host Server for Availability Groups [duplicate]
We have a 4 Node multi Subnet Availability Group. When i go to the Cluster Manager I could see the Current host server is the Node 4 whereas the AG Primary node is Node 1. Will the AG go down if Node ...
0
votes
0answers
40 views
The connection was recovered and rowcount in the first query is not available. Please execute another query to get a valid rowcount
I get the error message as appears in the title and seen below when running a query against one of our SQL databases. The error seems to be consistent with this specific query when trying to return ...
1
vote
1answer
38 views
Query does nothing except context switches
I run Adam Mechanic's WhoIsActive every so often on my server and log results to a table. I noticed a peculiar case while looking at some of the results and would like help interpreting what is ...
0
votes
0answers
26 views
Installing Anti Virus on availability group
Is it worth the risk to use Anti Virus on Availability group databases. Even with following the guidelines given at
https://support.microsoft.com/en-us/help/309422/choosing-antivirus-software-for-...
1
vote
2answers
57 views
Resetting a new archiving database
I have an archiving question.
We have a History DB used for archiving old records from our main system and the application knows to query it
However, it becomes too big.
I would like to ...
0
votes
0answers
22 views
SQL server 2014 new publication error “A generic failure occurred during Service Master Key encryption or decryption.”
I'm trying to create a new local publication (replication , Transactional Publcation) on my production data base but i'm getting an error
A generic failure occurred during Service Master Key ...
0
votes
0answers
35 views
Changing DB mode from restricted user to multiple user is taking time in taking effect
In my installer, I need to set the setup Maintenance Mode and when done, I have to reset the same. As implementation, I set the DB mode to restricted user mode. executed all my required queries in ...
2
votes
0answers
125 views
SQL Server buffer cache smaller after reboot with same workload [closed]
We are hosting a SQL Server 2014 SP2 CU16 in a Windows Server 2016 64-bit environment for a customer. Performance has been fine for a long while.
On March 31 at midnight, the database server ...
0
votes
0answers
31 views
Change Tracking seems to be generating enormous transaction logs
Comrades,
I'm troubleshooting a 24GB DB that is generating approx 2GB (compressed) of transaction logs/60min.
Using the following command I'm tried to examine the contents of the log.
SELECT ...
-1
votes
1answer
44 views
Accidentally disabled windows authentication SQL server 2014 [closed]
I disabled the windows authentication in SQL server 2014.
I don't know how to re-enable it.
I tried to google but couldn't find the solution.
It says Expand Security and then expand Login. Right-click ...
8
votes
4answers
2k views
MAXDOP Settings for SQL Server 2014
I know this question has been asked number of times and also has answers to it but, I still need a bit more guidance on this subject.
Below is the details of my CPU from SSMS:
Below is CPU tab from ...
1
vote
2answers
44 views
I have a beginning balance I need to calculate ending balance running total by month rows
I have been searching this site and others trying to find an answer to this. I tried various things but cannot get my head around an answer so here goes.
Here my data:
What I need is to start with ...
0
votes
1answer
26 views
Update Statement on one table takes locks on other tables
Given the following statement on the AdventureWorks database:
UPDATE Sales.SalesOrderDetail SET OrderQty = 100 WHERE ModifiedDate >= '2014-01-01'
If I open another query window and look at the ...
0
votes
1answer
34 views
Incorrect GrantedQueryMemory(KB) Value in SSMS Dashboard report
Just checking by to see if someone else has witnessed the same and tried a fix or has been fixed:
Issue:
I ran below query on stackoverflow2013 database
select
ParentId as [Post Link],
count(...
4
votes
2answers
173 views
Performance Improvement went wrong in Production, worked fine in Test
We have an application supported by vendor and there was a piece of code which was doing very heavy logical reads and time consuming inside a procedure, hence I proposed them to tweak the query a bit ...
1
vote
1answer
57 views
Assign Permissions to a SQL User
I have active directory on my company, I have some login users created at the level of the SQL Server (2014) that I need to propagate to all my databases on that server. I want to create a read only ...
2
votes
1answer
233 views
Fragmentation Level for Heaps
I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and ...
0
votes
1answer
144 views
Loading Data from CSV file or Staging Table to Multiple tables - SQL Server 2014
Hi i am trying to load data from CSV file to multiple sql server tables and some of the tables having Reference to parent tables so i can get the FK column value but trying to load via INSERT INTO ...
2
votes
1answer
60 views
Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan
Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan
-- create objects
CREATE DATABASE ConvertTest
GO
USE ConvertTest
GO
CREATE ...
3
votes
1answer
273 views
How to determine the cost of index creation?
I have a table that I want to create an index on to improve the performance of a couple of SELECT queries that use the table.
How can I test whether the index has had any significant detrimental ...
0
votes
0answers
22 views
Find when tables were deleted historically
We've had an issue where data tables have been deleted from a client applications SQL Server Database (Dynamics Navision). Unfortunately we had an outage and the SQL Servers were affected by this, ...
1
vote
1answer
164 views
JSON_VALUE in SQL-server 2014 [closed]
I am looking for an equivalent functionalities of JSON_VALUE in SQL Server 2014. Can any of you post an alternate of the below code which is supported in SQL Server 2014
SELECT JSON_VALUE(ERROR_MSG, '...
