Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,301
questions
0
votes
2answers
51 views
Implications of adding a trigger on production database
I am trying to add a trigger on a production database table which includes millions of rows and is used very heavily. There are around 70k-100k inserts per day.
When I try to add a trigger to this ...
0
votes
0answers
9 views
How to make replication continue to work after repairing?
I would like to share with you a problem I faced today and will be glad if you can help me to tackle that issue. The issue related to transactional replication. So, let's go step-by-step to know how ...
-3
votes
1answer
71 views
Sql Server consumes CPU but no one is using the server
In my server I have a SQL process that consumes CPU (~10%) but no one is using the server.
In query select * from master.dbo.sysprocesses order by cpu desc I see at least one suspect process:
spid: 9
...
1
vote
0answers
26 views
Can oscillating number of fragment decrease FTS query performance?
I've got a FTS Catalog on a table containing ~106 row. It used to work like a charm, but lately, for an unknown reason it started to get very bad (query >30 sec) performances randomly.
Reading ...
0
votes
1answer
54 views
lost all my databases after reinstalling sql server management studio
SQL server management studio was not working so, after googling this issue, I found that I have to reinstall it. So, after re installation, I lost my database and I didn't get a backup of it. So, is ...
0
votes
2answers
83 views
split one column into two columns based on it's value using t/sql
Hope you're doing well.
I have a table as you can see below :
ACNTHEADER(DDATE DATE ,ACNTCODE INT,VALUE NUMERIC(17,2))
the data in the table :
DDATE ACNTCODE VALUE
6/1/2013 1174 ...
2
votes
1answer
59 views
Separate a column into its components based on another table
I'm struggling with a very difficult and complicated query here and I need your help.
I have two tables as you can see below:
1) ACCOUNT1:(ATYPCODE int, ATYPDESC varchar(50), ATLASTFLAG varchar(50))
...
6
votes
1answer
400 views
Why SQL does not use the indexed view?
I have the following version of SQL Server:
Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
I created ...
1
vote
1answer
49 views
UPDATE to linked server (Pervasive Database) is failing using OPENQUERY
I have 2 database servers:
ServerA : Windows Server 2012 R2 Standard, SQL Server 2014 (SP3) (KB4022619) Standard Edition.
ServerB : Windows Server 2016 Datacenter, SQL Server 2016 (SP2-CU6) (...
1
vote
1answer
30 views
PowerBI Service Account granting duplicate execute permissions on stored procedure almost constantly
I have PowerBi Server (Jan 2019) installed on my server. I can see from the default trace that that the PowerBi Service account NT SERVICE\PowerBIReportServer is granting execute permissions on a ...
6
votes
1answer
380 views
SQL Server - TRY/CATCH does not work in certain cases
How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?
For example, this does not work:
Define a ...
0
votes
1answer
25 views
How to create two additional columns in a results set to act as axis for a heatmap
I have a set of results from a WSUS report that consists of computername and the status level of patching on that computer.
I have been asked to display this as a heatmap e.g each computer is a ...
1
vote
1answer
29 views
Understanding the recreation of index in Table partitioning
I need your help. I have[AdventureWorks2014] database and I wanted to perform partitioning on [Production].[TransactionHistoryArchive] table based on [TransactionDate] column. [Production].[...
0
votes
0answers
9 views
SQL Server 2014 Replication Subscription Sync
I have SQL Server 2014 Replication set up.
It is my understanding that when you add a new subscription that it will truncate the table and re-insert/re-initialize the tables you are subscribing to.
...
0
votes
1answer
32 views
Get INSERT/UPDATE/DELETE statements executed at specified time
We are investigating some issues with our production database where some suspicious records inserted/updated in our database. I just wanna know how we will be able to get these TSQL statements ...
-1
votes
1answer
26 views
SQL Server upgrade
Can we upgrade SQL server 2014 Enterprise Core edition to SQL server 2017 Enterprise edition ?
I saw some documentation but don't see anything with regard to upgrade from enterprise core to ...
3
votes
2answers
62 views
Find accounts with passwords that are not complex
I can see all the SQL Login passwords that enforce the password policy with this command
SELECT * FROM sys.sql_logins WHERE is_policy_checked = 1
However, it seems that when the enforce password ...
1
vote
0answers
49 views
Finding which queries used an index
I'm being asked to provide historical proof before I can maintain indexes - which is fine - over the past year I've kept a table full of index usage statistics for all the tables on the server.
I ...
0
votes
1answer
25 views
XQquery for specific attribute of all specific elements in XML
I am, looking for a query to get all attribute values "NetAmount" of Element "PRODUCT" from the following samle XML:
<ROOT>
<CATEGORY name="A">
<PRODUCT NetAmount="10.50"/>
<...
0
votes
0answers
26 views
DBCC UPDATEUSAGE versus data inaccuracies [duplicate]
SQL Server 2014 SP2 with Availability groups
3 Node Windows Cluster for AOAG (2 Sync & 1 Async Replicas)
NOTE : Databases are with FileStream Enabled
Recently we applied Encrypting File System (...
-4
votes
1answer
37 views
Subtracting sql queries using the same column [closed]
I have a sql query where I want to subtract 2 queries based on location:
select
(select location, sum(amount) from table1
where COA_0 = 'MAR' and SNS_0 = '1'
and ACCDAT_0 between '01-04-2018' and '...
4
votes
2answers
89 views
Indexes getting highly fragmented during normal usage of system
I am using MS SQL Server 2014 Standard Edition in my project and recently I have encountered an issue with index fragmentation getting high during normal usage of the system by the users.
Currently ...
1
vote
1answer
29 views
Legacy data migration to Always on Availability group database
In our application there is a data conversion process that brings all the legacy data to the production database.
Since our database is on availability group and the migration might bring 100GB + ...
0
votes
0answers
22 views
Republishing data in merge replication SQL Server
srv1 is distributer and publisher
srv2 also is distributer and publisher
srv3 subscriber of srv2.
I create merge replication and data successfully replicated from srv2 to srv3
However, when ...
1
vote
1answer
45 views
Using PIVOT to convert multiple rows and columns
So my current query output is as follows:
I would like to consolidate all of these rows to just one long row like so:
MyID Customer relc_rate relc_consumption relc_daysofservice ...
7
votes
1answer
277 views
.NET executes a SQL query and Active Monitor shows multiple rows blocking each other
I use .NET to execute SQL operations on SQL Server 2014, here's the code used:
using(SqlConnection conn = new SqlConnection(connectionString)){
//https://stackoverflow.com/questions/1880471/...
1
vote
1answer
50 views
Purging of unreferenced data via unconditional DELETE, by leaning on foreign key constraints
I'm designing a database from which old data has to be purged regularly for legal reasons, and I'm trying to figure out the best way to organise the purging.
Dealing with the main tables is trivial, ...
0
votes
0answers
19 views
Failed Login on Registered Server When Opening Connection
I have a number of servers registered in the Registered Servers pane in SQL Server Management Studio.
When I right click a server group and select "New Query", a window opens in which I can execute a ...
7
votes
1answer
110 views
Update Intersect hangs
We have this really weird behavior we just started experiencing with our update intersect statements. These were working fine, but now we are ingesting a pretty wide data source in terms of columns ...
1
vote
1answer
16 views
Auditing of modification of change to encryption keys
Is it possible to audit/log any attempts or actual changes to the keys used in the encryption hierarchy?
We are using 2014 Enterprise edition and have recently enabled TDE across our databases and ...
4
votes
1answer
62 views
Is it possible to use just one shared folder for log shipping?
I wonder if it is possible to use one shared folder(only in source database) instead of two (one in source db and another in destination db). That's Server1 will store its t-log backups there and ...
0
votes
1answer
18 views
What kind of changes does snapshot file store?
Assume that I created a database called [DBS] and inside that database I created a table called [products].
Then I inserted 4 rows into [products].
Afterwards I took a snapshot of [DBS]. As a result,...
0
votes
0answers
18 views
SQL Server tempDB data file is growing very fast. 120gb
My tempDB is now over 120GB. It has grown 65GB in a couple of hours. It is not the log but the actual DB that is growing. Reports show 99% is unused space. I tried restarting the server that SQL ...
2
votes
2answers
66 views
What is the best way to automate a migration of multiple SQL Databases to a different hard disk?
We have a SQL Server with about 50 databases. Of those databases, about 25 of them need to have their .mdf and .ldf files transferred from drive D: to drive E:. It is critical that the database names ...
2
votes
1answer
81 views
Does T-SQL have a Schema search path?
In T-Sql, a user has a single default schema. Is there a concept of schema search path similar to a postgres search_path?
The question behind the question is, if I use schemas as namespaces for ...
3
votes
1answer
51 views
Check if partition elimination works
I implemented partitions on one of the tables our DEV/TEST environment, and modified some of the procedures to use the partition key when accessing this table.
How can I verify that partition ...
3
votes
1answer
42 views
Understanding Stolen and foreign memory usage between 2 app database servers
Below is more for my understanding on how to interpret the data from my perfmon counters for 2 database servers with diff app structure but sort of similar workload
Server A has total of 1 TB RAM ...
2
votes
1answer
46 views
Error: 468 trying to connect to SQL server with SQL Server management studio
I'm trying to connect to SQL Server 12 with SQL Server management studio 2014.
The connection is succesful, but when I try to see properties of a specific database with mouse right button I get this ...
2
votes
2answers
62 views
What am I doing wrong with subquery
I cannot work this out, how do I get the subquery to return the lowest course weight for each lecturer, it currently only returns the lowest and is my subquery wrong or outer query?
Question: Show ...
6
votes
1answer
236 views
Slide Partition from Rowstore to Columnstore
I have a rowstore table which contains ~10 billion records. The table is partitioned on an INT field and has about 1,400 active partitions. I also have a clustered columnstore table built on the same ...
2
votes
2answers
44 views
SQL Transaction - use values before commit?
So I am creating a stored procedure in SQL2014. Towards the start it has:
Begin Transaction
Then it does an insert into a table, which has an auto-incrementing primary key:
Insert into tNotice.
...
0
votes
0answers
13 views
Where is transaction log truncated to if full dump is taken while txn log dump is running?System info: MS SQL 2014 Enterprise
We have a very large database with a very large (over 1.5 TB) log. A transaction log dump was started, and while it is running, a full DB backup was run (successfully). The log dump is still running. ...
0
votes
2answers
55 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
76 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
13 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
40 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 ...
7
votes
2answers
147 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
52 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
29 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
491 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.
...
