Questions tagged [sql-server-2014]
SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
2,451
questions
0
votes
1answer
27 views
How to Grant A User Under A Sql Domain Account To Insert and Update Records
I have a sql domain account in sql server like DOMAIN\GroupName. There are many user accounts under that account like DOMAIN\user.name. I want to give insert and update permission only to a user under ...
0
votes
1answer
68 views
How to restart MS SQL server right, what is best practice to do that?
I`m quite new to DB administration.
I have a problem when I restart SQL server, one of the databases goes into Recovery mode and automatically repairs the DB in 30 min. I don't know why this happens....
2
votes
2answers
67 views
MS SQL running out of Disk Space - can FileGroups be used to free up some space?
Problem
We have a Sql Server 2014 with a 1TB attached disk (on Azure) that's running out of disk space. We have about 20GB's left (maybe a few weeks of space). As such, we need to move some data off ...
4
votes
1answer
55 views
Why should I apply a CU for SP2 that was released after the release of SP3?
Checking SQL Server 2014 build versions to identify the right update for my server I got a little confused as what update to use. Did some research and found out I should Apply the latest Service Pack ...
0
votes
1answer
43 views
How do cardinality estimates affect CPU and Reads in SQL Server?
Trying to understand a concept, not sure where to look for answers. I have been researching cardinality estimates and their affects on memory. In the process of optimizing a query to get better ...
2
votes
1answer
42 views
Finding stored procedures with XACT ABORT ON OR OFF
I am trying to troubleshoot blocking problems related to active open transaction with sleeping status and probably have found issues related to XACT ABORT option missing or as OFF read from many blog ...
0
votes
1answer
22 views
Copy all agent jobs to separate files based on date and keep five copies
Looking for a crazy script to run after database backup ...
Want to look at all agent jobs and if modified date is equal to today save it to a shared drive, if not skip. Also want to delete the ...
3
votes
1answer
60 views
Is Memory Grant size 'cached' for subsequent executions of a cached plan?
I hope the title of the question is clear. I am a DB developer as one of my job roles. I recently asked a question regarding index statistics and I have spent a lot of time reading about statistics, ...
0
votes
1answer
61 views
SQL Server take an unencrypted full backup
I have to take an unencrypted full backup of a database so that it can be sent over to other team after which they can restore it in their own environment. The other team has a different environment ...
3
votes
1answer
110 views
Exactly same query and plan but different duration and total reads. I Know parameter sniffing but
I know what is parameter sniffing but this seems different, I have this simple query:
select * from A400RDATA
This table has only a cluster index and approximately 25000 rows. The query usually ...
0
votes
3answers
94 views
How did you split strings before string_split()
I have some strings I need to split on a SQL Server 2014 instance.
I would normally do it something like this:
declare @bk nvarchar(1000)
set @bk = 'SIS5.0~person_id~696969'
select value from ...
2
votes
1answer
43 views
Querying a SQL Server's CNAME alias instead of the actual instance name
We don't have a DBA here and our infrastructure analyst is trying his best to give us a test instance on a server using a CNAME alias which neither he nor I have done before.
The actual database ...
0
votes
2answers
65 views
Deny a Sql Server user from a specific hostname
Is it possible to block a sql server user not to execute DDL and DML operations from a specific hostname (client)?
I have a CRM web application. That app connects to my database with a sql server ...
2
votes
0answers
38 views
sys.database_files DMV, max_size column
I was just modifying my script that I use to get size of database file and wanted to use max_size to display if a max size was set for a database file. As per Microsoft Docs, the column description ...
0
votes
1answer
47 views
AutoIncrement non-primary key or Make unique composite key?
I have a db with a table that has a rollover value such as OrderNumber and another column is year.
This is the primary key - a composite key - but there are other tables with which I need to use a ...
3
votes
3answers
161 views
Why does the concatenation order change with a User-Defined Table Type?
I've encountered what seems to me to be a strange ordering issue when using CONCAT with a User Defined Table Type.
I've put the SQL below that allows me to reproduce this:
I have the following user ...
0
votes
1answer
48 views
Index / Check DB Script question
I'm using Ola Hallengren's maintenance scripts and I wanted to see if I could create 2 tables so that it logs integrity checks and index optimizations to different tables.
0
votes
1answer
62 views
Where to put single quotes?
I am trying to execute the below statement which creates a database called service2019:
DECLARE @dbname VARCHAR(50);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));
EXEC (
'CREATE ...
1
vote
1answer
19 views
In place upgrade from Express 2005 SP 4 to Express 2014, does database size limitation follow?
We are currently attempting get around a file size limitation issue with our database. The database file size hovers around the 4GB mark (the database size limit is 4GB in SQL Express 2005). We always ...
0
votes
0answers
25 views
create_time in sys.dm_xe_sessions is in the future
I have run the following query which shows the running XE sessions on the server:
SELECT name,
create_time
FROM sys.dm_xe_sessions
and I get the results
+-------------------------------+...
0
votes
1answer
96 views
Columnstore Index maintenance and finding fragmentation
We are using clustered columnstore indexes and partitions in SQL Server 2014. Some tables have a lot of updates and we find ourselves with a lot of deleted rows for some partitions. In our tests, a ...
0
votes
0answers
16 views
Log Reader Agent is not starting
I have setup transactional replication on SQL SERVER 2014. It was working fine yesterday. Today, I am seeing below error. How to investigate what is the problem. Why log reader agent is not starting ...
5
votes
2answers
774 views
Compatibility level of SQL Server
Is there a way to know if the compatibility level has changed In my SQL SERVER DB 2014 ?
1
vote
2answers
33 views
restoring to new db putting old db into restore
The image below says it all. I need to get some data out of a back-up that's two weeks old, so I restored to a new db called ETLFix. However, when I started the restore my production DB, ETL went ...
2
votes
2answers
81 views
Backup files - Are they safe to delete?
I've got a back up process which initially writes to a local disk, then the disk gets backed up remotely. After each back up (log, diff and full) I'd like to delete all the files on the local disk. ...
0
votes
1answer
15 views
SSRS 2014 Report - looking up single row from dataset
I have a table that I'm trying to report out on in SSRS-2014.
A cut down version of the table:-
TABLE_NAME FEE_NAME FEE_VALUE
PDE 1st year 100.00
PDE 2nd year 120.00
PDE ...
0
votes
1answer
33 views
Why do I need DAC if I am able to connect to the server via sqlcmd?
DAC attracted my attention at the moment when I was unable to connect to the SQL Server using a traditional way (the reason was deleting logon trigger). I made some searches on the web and it turned ...
1
vote
0answers
30 views
Slight change of query causes CONVERT of illegal values to be done first, causing a conversion error [duplicate]
I have a table MyTable which has columns OldVal, NewVal, Nme which are all VARCHAR(MAX)
Wherever Nme = MyValue, the OldVal and NewVal values will be "integers" IE a numeric value value stored as a ...
1
vote
0answers
11 views
What is the impact of MAX_DISPATCH_LATENCY while configuring EXTENDED EVENT?
By default, MAX_DISPATCH_LATENCY is set to 30 seconds. I chose FILE as a target and write all captures there. I thought that if I write all captures into file, why would then I lower down ...
1
vote
1answer
48 views
Different Date format in predicate when comparing against a date or datetime
I have the following query:
SELECT CAST(MyDateTimeCol AS DATE) AS MyDate,
Col1,
Col2
FROM MyTable
WHERE MyDateTimeCol >= '2019-11-25' AND MyDateTimeCol < '2019-11-26'
...
0
votes
0answers
47 views
Problem with calculating an approximate space required for online index rebuild
I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes ...
1
vote
3answers
107 views
Execute a query but not return results to client
I am testing the performance a query and trying to fix a parameter sniffing issue. Based on the parameter sometimes it returns 3 rows and sometimes it returns over 5K rows. I want to test the impact ...
0
votes
1answer
12 views
Assigned filetype for backups not applying
I am working to migrate backups over to a new storage point. I figured the easiest thing to do was to duplicate the configuration over from the existing Maintenance Plan to a new one, just in case ...
3
votes
3answers
159 views
Only return recordset(s) from Stored Procedure if it has rows
I have a stored procedure that returns multiple recordsets for use in an application. Sometimes some of those recordsets are empty.
I'd like to reduce overhead and only return those which have 1 or ...
-2
votes
1answer
46 views
Modification in the SQL script
I want sysadmin logins which are not listed in the exceptionssysadmin table.
I'm receiving empty columns with the below script.
can someone suggest where am i going wrong ?
The below is the script
...
0
votes
1answer
11 views
Upgrading Microsoft SQL 2014 and Always On AG Configurations
I'm getting ready to perform an upgrade from SQL 2014 to 2017 on two servers that are configured for Always On. The concern on the upgrade is how to break Always On to stop replication and to keep any ...
1
vote
2answers
74 views
Find jobs that succeeded, but had steps that failed
Is it possible to find jobs that succeeded, but that had steps that failed like the desired output shows? I'm new to this side of development and I'm not sure about all of the system tables, etc. If ...
1
vote
1answer
28 views
Find all tables in a snapshot replication that have primary keys
I've been tasked with moving all tables in a single snapshot replication that have primary keys to the transaction replication.
I've tried to break it down into two steps, finding all tables in a ...
0
votes
0answers
22 views
How does DBCC CHECKDB use tempdb?
I would like to know the working principle of DBCC regarding the tempdb. I want to know why it uses tempdb for what purpose. I didn't find any advanced article about that so maybe someone can shortly ...
0
votes
0answers
18 views
what is the difference between replication options proc exec and definition only?
I understand proc exec option as for example when you run a procedure which inserts 1000 records into table, those insert afterwards will be replicated to the subscriber. When it comes to definition ...
0
votes
0answers
21 views
Is subscription database considered as read-only?
I am not sure about that because while users implement SELECT from subscription database, publisher replicates data to the subscription server meaning that INSERT and UPDATE also take place (which are ...
-1
votes
1answer
27 views
Sql Server 2014 - Rows to Columns convert Dynamically- [duplicate]
Below are my query and table details
create table #ReviewData
(
ProjectId int,
ReviewId int,
ReviewDataPointKey varchar(max),
ReviewDataPointValue varchar(max))
Insert into #ReviewData values (...
-1
votes
1answer
72 views
SQL Server Rows to Columns Dynamically
Below are my table and query details.
Table definition
create table #ReviewData
(
ProjectId int,
ReviewId int,
ReviewDataPointKey varchar(max),
ReviewDataPointValue varchar(max)
...
4
votes
1answer
211 views
How to remove SQL Server Error Logs
I have six error logs plus the current. They are taking up 10G of disk space on my hard drive on my VPS server. Thats about 25% of the server space. The largest file is 6G and growing.
How can I ...
1
vote
0answers
35 views
Linked server in Sql Server 2014 invalid path
I am trying to create a linked server to foxpro tables using vfpoledb, but it gives me the error of "Invalid path or file name" when trying to put as source \\xxx.xxx.x.x\same_names\abc (xxx.xxx.x.x ...
0
votes
1answer
28 views
Unable to Insert Casted XML Data into New Column in Temporary Table
Question
How can I insert CAST(event_data as XML) data into a newly added column in a temporary table with the following statement:
INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(...
1
vote
1answer
36 views
Cannot take backup into NAS storage at specific time
I have a job which runs every 30 minute and takes t-log backup of the database suing Ola's script. Every Thursday and Friday at 22:45 I get an error:
11/07/2019 22:45:00,Backup,Unknown,Error: 3041<...
0
votes
1answer
21 views
How to convert a VARCHAR DD/MM/YYYY H:MM:SS AM into datetime and then to Json date?
So I know this question is crazy. I just spent days trying to solve this but i think it's impossible.
I have a table with a CreatedDate as a VARCHAR field.
10/7/2019 7:12:59 AM
What I need to to ...
4
votes
1answer
256 views
Can SET IDENTITY_INSERT be allowed with less privileges than db_ddladmin?
I've inherited a system where the application is running under sysadmin account.
I limited this account to db_datareader + db_datawriter + EXECUTE on all database and set extended events session to ...
0
votes
0answers
29 views
Linked Server using Impersonation fails SSMS GUI Connection test but sp_testlinkedserver succeeds
I have a linked server (Server B) set up on Server A with permissions set as follows:
This means that there are five Windows Logins (of which my windows account is one) who can access the linked ...
