Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2
votes
1answer
72 views
Refactoring subquery to JOIN and CROSS APPLY, get only row for each record in parent table
Given the following query:
SELECT
p.ProductName,
CASE
WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1)
THEN
1
ELSE
0
END
...
1
vote
1answer
24 views
Sql servr 2014 instant to sql server 2008 r2 instance linked servers query problem
I'm getting the following error on some of the linked server,when I try to query a table using a link server. The link server is created on SQL2014 instance to connect to an SQL2008r2 instance.
0
votes
0answers
22 views
I have a stored procedure running fine before 4PM every day. After that it runs like a dog [duplicate]
I have a stored procedure running fine before 4PM every day. After that it runs like a dog.
I identified which query inside the sp caused the problem. In a good run, the logical IO is about 12 ...
1
vote
1answer
59 views
Issue with a “weird” deadlock in SQL server
I'm working with a database that has the characteristics that follow:
GO
/****** Object: Table [dbo].[ADS_EVENT_TYPE_STR] Script Date: 1/17/2019 8:20:36 PM ******/
SET ANSI_NULLS ON
GO
SET ...
1
vote
1answer
33 views
Changing the owner of a read-only database which is configured with Log Shipping
Is there a way to change the owner of a read-only database which is configured with Log Shipping without actually re setting up LS?
Per this link it's not, but I'm checking if anything has changed ...
0
votes
2answers
74 views
Getting the part for string using sql server query
I have rows like below
10470_AMK-4799__OFS_BROWSERTC
6038_DMUSER_DM.READ.REPLACEMENT
6043_DMUSER2__OFS_DM.OFS.SRC.VAL
10194_AMK-TC037__OFS_BROWSERTC
64948_AMK-TABLETUSER__OFS_TWS
10010_AMK-0357
I ...
0
votes
1answer
33 views
Backup Structured Data and Unstructured Data seperately in SQL Server - Is it Possible?
Can I get some inputs on below:
SQL Server Version : 2014 SP2 GDR with AOAG environment.
Is it possible to backup Structured Data & Unstructured Data (FileStream) separately in SQL Server ?
For ...
0
votes
0answers
28 views
Recovery Pending Issue
one of my database is under recover pending issue since yesterday ... am trying to solve it but am unable to do so .. Please please help me
tried this
Exec Sp_resetstatus database;
Alter Database ...
0
votes
1answer
31 views
SQL Server Express Instance is not showing while connecting to server
I created a SQL Server instance and installed it in my system,
but the instance is not showing in server name while connecting.
-3
votes
1answer
49 views
DESC not working in SQL Server 2014 Management Studio
I want to execute DESCRIBE query to view database table, but i am not able to as it shows error 'Incorrect syntax the keyword DESC'
Query :
DESC 'table-name'
2
votes
2answers
47 views
SQL Server Replication with Historical Tables
Is it possible to replicate a SQL Server database and for the replica to have temporal/historical tables?
Details:
I have a production system, running SQL Server 2014. I want to have a replica ...
2
votes
0answers
58 views
Blue/Green via AlwaysOn [closed]
Full disclosure, definitely not a DBA. However, I've put together the following solution design to try to achieve a Blue/Green SQL Deployment.
See for Definition of Blue/Green: https://martinfowler....
2
votes
1answer
46 views
Enforcing distinct number of trailing spaces
I'm working on converting a SQL Anywhere (ASA) 5.5 based application and its database to MSSQL. Existing is a table with a primary key clustered on two columns, code_type char(4) and code char(30). I ...
0
votes
0answers
29 views
SQL Server 2014 Triggers and Merge Replication
I am trying to have a trigger fire when the Merge agent\user insert\delete\update data.
The trigger inserts data into a log table.
I don't want to use NFR (Not For Replication) because I need that the ...
1
vote
1answer
71 views
Who has SHOWPLAN permissions on a database
Is there a way I can see all users in a database who currently have access to view execution plans? That is, who has SHOWPLAN permissions
1
vote
1answer
15 views
Exist on table from linked Server giving Error 4104
I am comparing two tables on different Servers and the query I am running looks like this:
Select top 100 *
From [ServerA].[MyDB].[dbo].[Table1]
Where Exists( Select * from [ServerB].[TheirDB].[...
4
votes
0answers
73 views
Query Notification Internals
I'm looking into using Query Notifications as a light(er) weight way of identifying when data in a table changes. I know they use Service Broker, and (although I have difficulty explaining exactly why)...
6
votes
3answers
801 views
Do SQL Servers in a cluster need to have the same memory?
I have two servers in always on failover configuration with windows clustering underneath.
Do both servers need to have the same memory?
One of our DBs that uses a lot of memory is not in the AG, so ...
0
votes
1answer
92 views
SELECT shows different amount of rows [closed]
I have a job which each day (at 6:01pm) copies 516 rows from a table and seeds them with tomorrow's date
INSERT MYDB.MARKET_DATA(
MARKET_DATE,
CURRENCY_BASE,
...
0
votes
1answer
29 views
select distinct values from foreign key constarint
i am selecting values from two different table which have one to many relationship between them.during select its retrieving duplicates values even i use distinct and group by.the above table is ...
1
vote
2answers
37 views
Restore a single database backup (.bak) file into multiple filegroups
I have a SQL Server 2014 database DB01 (single filegroup - PRIMARY) and I took a backup of it into a DB01.bak file in SSMS.
I have another database DB02 with 1 primary filegroup (1 file) and one ...
1
vote
1answer
33 views
Triggers corrupt merge replication
I have merge replication (SQL server 2014) and a table that have 3 triggers that monitor delete, insert and update.
All of the triggers are after trigger not instead of.
I'm updating the value on one ...
2
votes
1answer
121 views
how to Enable these Windows Firewall rules using powershell?
I have a few servers where I see a blue question mark on the sql server service, as per the picture below.
This is the solution:
Enabling these Windows Firewall rules did the trick for me
...
1
vote
0answers
10 views
SUSDB appears to be in a different location than shown in SQL Studio
I Have WSUS installed on a 2012R2 server. I am using SQL, not the internal windows database.
I was looking at cleaning out some old files. One of the folders is named RestoreTemp (created to ...
1
vote
0answers
28 views
Mirroring issue with mirror server
I am running two Windows Servers 2012 R2 with SQL Server 2014 Standard Edition. I have strange issue on mirroring.
I configured mirroring on for 15 databases, no Witness server. The Principal server ...
2
votes
1answer
30 views
Migrating a SQL Server 2014 AG to a new 2017 AG
Our current production server runs on SQL Server 2014 AlwaysOn AG. We are planning to upgrade it to SQL Server 2017 AlwaysOn.
Due to the configuration issues with the present cluster, we can not use ...
1
vote
2answers
84 views
How to pass a column name using string_split - ms sql server?
I'm trying to pass column names using a string split because I need the values from that table to join with another table.
I've already achieved this in Postgresql where I get the values from these ...
0
votes
0answers
40 views
mirroring issue
Hi team,
I am facing strange issue on mirroring,and sql server details are SQL Server 2014 and Standard edition. Issue is ...
0
votes
1answer
45 views
msdb.dbo.sysjobhistory data gets delete whenever SQL Server services are restarted
I am facing a issue where my msdb.dbo.sysjobhistory data is getting deleted when ever my SQL Server services are restarted or fail over.
I am using SQL Server 2014 SP2 with cluster environment, with ...
0
votes
1answer
23 views
How to handle set up real time DML and DDL statements sync between two DB servers SQL Servers
We are looking to offload the data from a Production enviroment to a reporting environment. I currently have an SSIS package setup to extract and load data between the two servers.
However, now ...
0
votes
1answer
33 views
What happens when the same application from different server tries to connect to the always on primary server
In our environment we have an always on availability group and the reporting server is acting as a secondary replica with Synchronous Mode, manual failover. XSLT report editor that is a vendor ...
1
vote
2answers
40 views
How to Disable Windows Authentication in SQL Server
I would like to say that as per my Organization requirement they want to Disable Windows Authentication in SQL Server.
My Environment is as follows:-
1) Microsoft Azure
2) Windows Server 2016
3) ...
0
votes
1answer
23 views
Listener Port 1433 for Always on Availability Group
In our Availability group I have a reporting server where report writers will connect directly and also crystal reports will be used, If the listener port is 1433 and the clients also gets connected ...
0
votes
1answer
17 views
Syncing complete database with server
I am developing Point of sales software, which have multiple branches and one main branch.
Main branch is web based while all other branches (9 now, which may exceed) are desktop based.
Client ...
1
vote
2answers
151 views
Shrinking a SQL Server 2008 Standard database to move it into SQL Server 2014 Express
I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to ...
0
votes
1answer
13 views
Read only replica part of always on availability group for Reporting
As a DBA I would like to include a read only replica into the always on availability group. XSLT / Crystal Reports will only be used.
Please see below
1) Primary Replica (Same Data center) ...
2
votes
1answer
35 views
SSMS generated script failing to execute
So I am in a situation where I need to import a small DB (under 1GB) from a newer version of SQL Server (I am restoring to a 2014 SQL Server from a 2016 SQL server), without a direct connection ...
1
vote
1answer
71 views
Trace Flag impact to Mirrored Databases
I want to enable dead lock trace flag -t1222 on a production SQL Server 2014 instance. Many databases on this instance are configured with database mirroring. What (if any) impact to mirrored ...
1
vote
1answer
62 views
SQL Server Not Consuming all Memory - Insufficient System Memory in Resource Pool 'internal'
I am running SQL Server Standard 2014 with SP3. I am noticing a lot of activity on the log viewer around insufficient system memory in resource pool and memory Broker.
I have searched on this error ...
1
vote
1answer
29 views
Matching dates with conditions and filtering in a table
So I have a tables like this
id | date1 |date2 |value
1 | 1/1/2014|1/1/2004|100
1 | 1/1/2014|1/1/2004|100
1 | 1/1/2014|1/1/2009|200
1 | 1/1/2014|1/1/2009|200
1 | 1/1/2014|1/1/...
0
votes
1answer
80 views
SQL Server service start automatically only after Windows Shutdown, how can I make restart automatically without batch file? [duplicate]
I am using MS SQL Server 2014, When I restart my Pc the SQL Server Service does not start automatically, however, it does after Windows Shutdown and run again, how can I make it restart automatically ?...
5
votes
1answer
66 views
RCSI and index maintenance
I have a database that is RCSI enabled and I've been rebuilding my indexes online (SQL 2014 Enterprise) for as long as I can remember. My understanding is that if we were to rebuild the indexes ...
1
vote
1answer
47 views
Cluster IP's under Access Point for Administering the Cluster
When adding the cluster IPs under Access Point for Administering the Cluster, do we only include IPs for the public subnets, or do we also need to include an IP for the private subnet(s)? This ...
0
votes
2answers
39 views
SQL Server Agent - Do not show job step details and column headers in output file
I have a SQL Server Agent job which runs a select statement on a schedule and appends results to a CSV file
(Right click the job --> properties --> advanced and then adding a file path to the output ...
0
votes
0answers
37 views
SQL Server error 19456 when adding the listener
I have three replica same subnet both has a Private and public network. I was able to create the cluster and create the availability group . When I try to add the listener I get the below error
...
3
votes
1answer
73 views
SELECT from Clustered Columnstore results in LOB Reads
I am performing a SELECT from the following Clustered Columnstore Index which contains 5.2 Billion Records. When I review the output of STATISTICS IO, I am seeing LOB Logical reads.
My question is ...
4
votes
1answer
73 views
Eliminating a intra query deadlock, can I avoid restricting parallelism?
I have an update statement that is generating intra query parallelism deadlock. Based on this link I believe my two options to resolve this issue is to force SQL Server to avoid parallelism via an ...
-1
votes
0answers
33 views
Monitor packets received by cleint
Using wait stats, I've managed to prove that ASYNC_NETWORK_IO is the key bottleneck with our SQL Server, We have around an 80% wait. I would like to add additional monitoring on our SQL Servers to get ...
0
votes
0answers
23 views
Moving the FileStream Data Drive to New Location and enabling EFS
Can any of the MS FileStream experts help me on below:
I do have the 5 App DBs with FileStream enabled.
Current the size of **C: for FileStream Blob data is 5 TB**
All the DBs have below drive ...
2
votes
2answers
119 views
Memory issues: Tempdb using Almost all of the buffer cache
On one of the servers I am working on today, i see that almost all of the buffer cache is filled by tempdb.
As a result memory is very low on the server.
CPU
Tempdb:
Version:
Microsoft SQL ...
