Questions tagged [sql-server-2014]

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

Filter by
Sorted by
Tagged with
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. ...