Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server-2016]

SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.

0
votes
0answers
37 views

Logon failed for login <<user name>> due to trigger execution

I have the following issue for a SQL Server 2016 instance. Usually everything works fine but form time to time we are getting Error 17892 followed by error 18056. This is a selection form the error ...
2
votes
0answers
39 views

Stored Procedure: How remove Comments, spaces, and extra lines, or companies binaries?

We are trying to compare stored procedure codes. Example, these two stored procedure codes produce same result. (1) Is there method to convert stored procedures into binaries to validate they ...
0
votes
2answers
19 views

Add missing column to SSIS, data not extracted to staging table

I've inherited an environment that extracts data from a Dynamics AX 2012 ERP to a data warehouse for reporting. The original extraction set up by consultants did not include the proper date column ...
0
votes
1answer
48 views

Setting up AlwaysONAG in SQL server 2016 on windows 2016

I am going to set up AG's for the very first time, so with little or no experience, please guide me on below understanding: If i am setting up 4 node AOAG with below scenario: Node1-NY Data Center - ...
0
votes
1answer
32 views

Difference ReadOnly connections through sqlcmd vs SSMS

I configured my HA SQL Server to have a readonly. When I entered the following command sqlcmd -S servername -E -d db_name -K ReadOnly It points correctly to the secondary server. Then in SSMS I ...
2
votes
1answer
58 views

Issue with adding a database to an Availability Group

I am having an issue with a particular AG where I am adding a DB (test123 below) to the AG and on primary it will show everything OK as follows: However, on secondary it shows with an exclamation ...
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(...
1
vote
1answer
62 views

Query Index question

I am trying to figure out how I can index this query below. The table is 155 columns. It has a clustered index on the column which is not in the query. The query is dynamically created from out of the ...
0
votes
1answer
22 views

Intellisense Not Working in SSMS 2016

I am working with an Azure SQL Server database in SSMS 2016. I have Intellisense enabled in Tools -> Options -> Text Editor -> Transact-SQL -> Intellisense, however I have never had Intellisense ...
4
votes
2answers
592 views

Collect SQL Server Query Execution time in seconds

I'm trying to find a way of collecting SQL server query response times over say for the last two days. Is there a way to achieve this? I know there are the query stats DMV's but I dont seem to be ...
0
votes
1answer
30 views

Multicondition Subquery Max Date and Item Number

The following is a query to get latest Sales date for a customer. select pt.CustomerId, pt.SalesDate, pt.* from dbo.PurchaseTransactions pt where pt.SalesDate = ( select max(SalesDate) from ...
6
votes
1answer
200 views

How to Disable and Drop all Temporal Tables from a database

I'm working on a task where I need to backup and restore a database in another instance. But, when I restore that database, I need to Drop all the History Tables associated with Temporal Tables. Is ...
0
votes
1answer
37 views

Adding another Secondary Log Shipping location

I've seen a few variations of this question, but no clear cut answers to my situation. I have an existing log shipping setup, and want to add a secondary server. Here is how I understand what will ...
2
votes
2answers
53 views

Add Constraint to check value exists in a non-unique composite key column

I have the following table, which has a composite primary key: CREATE TABLE dbo.Seqs( SequenceKey nvarchar(10) NOT NULL, UsageSequence smallint NOT NULL, col1 bit NOT NULL, col2 ...
2
votes
0answers
98 views

Transaction rollback hanging from past two months(MSSQL 2016)

I executed a query using SQLCMD on 2019-01-30, and the query caused ldf file to bloat exponentially and it became 2TB in size, then my MSSQL started rollback of transaction at 2019-02-01. I used KILL ...
0
votes
0answers
37 views

Time Interval Partitioning in SQL Server

I have a table with time stamp (UTC) and for several reasons, like purging from this table (done at later point of ETL), I am trying to find out the feasibility of partitioning the table by 5 minutes ...
0
votes
1answer
30 views

Try_Convert Datetime showing '1/1/1900', want to show space

How do I get try_convert datetime to display NULL when the input is space? I am getting two different results here. My intention is to show 'space' number query as Null also, just like first query. ...
1
vote
0answers
27 views

Record the duration of a stored procedure with multiple parameters

Is there a way to automatically record the duration and row count of the results of a stored procedure execution and save the plan? I have a procedure i'm troubleshooting for possible parameter ...
1
vote
0answers
42 views

How do I parse Address into Street Number, Street Name, Type, Unit Number?

How do I parse Address into Street Number, Name, Type, Unit Number? Is there a function which will conduct this? 2280 MEYERS AVE ALDERGROVE AVE & S ...
-4
votes
1answer
49 views

PRODUCTION DATABASE RUNNING OUT OF SPACE [closed]

My database has 9556.97MB of space left. I do have about 2TB free disk space. The database is in full recovery mode and doing full backup everyday, also TLOG backup every 24 minutes. I used to just ...
3
votes
1answer
59 views

Parameterized Plan Flushed

I cannot find documentation anywhere on what would trigger this recompilation reason. We are investigating a sudden query performance drop and the only thing that I can think of is that the plan got ...
0
votes
1answer
45 views

Getting data grouped by weekday and hour

I have a table: CREATE TABLE BolusData ( [BolusKey] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [BolusDate] [datetime2](7) NOT NULL, [BolusSource] [nvarchar](50) NULL, [BolusVolDelivered]...
1
vote
1answer
46 views

SQL Server Query Store - Force Plan does not work

Here is my situation: I have a simple SP (containing giant joins and a few temp tables), which essentially returns a resultset, works in 15 seconds in 2008 compatibility level, but takes 3 hours when ...
0
votes
0answers
48 views

SQL Server 2016: Run xp_cmdshell as a different sysadmin user aside from the SQL Server service account

I been trying to research this for hours and can't seem to find anything. Basically what I am trying to do is run a xp_cmdshell command as a currently signed in sysadmin user To give some background, ...
0
votes
1answer
38 views

Security user audit

This has probably been asked a million times already and for this i apologise, but is there a way or any T-SQL I can run that will tell me, the server, the database, users login name, what permissions ...
2
votes
1answer
50 views

Calculate difference between current value and next value without CTE/ inline table

I have a table that has userid and his visit dates. I have created a CTE with adding a next visit column and using this to calculate day difference between visits. With Visits AS ( SELECT [uid],[...
6
votes
1answer
77 views

SQL Server 2016 Collation and Internal table Glitch?

My Steps: installed SQL 2016 with collation SQL_Latin1_General_CP850_CI_AS Restored Database to Stage instances with same collation as Database.(original Backup from 2012) Ran > "C:\Program Files\...
3
votes
1answer
152 views

Adding an additional “order by” column gives me a much worse plan

in other words, how can I get rid of the sort operator on the picture below? the picture above shows the execution plan of the following 2 selects together: SELECT TOP 1 so.OrgType,...
2
votes
2answers
61 views

What Triggers This Warning: Type Conversion in Expression May Affect “CardinalityEstimate” in Query Plan Choice

Using SQL Server 2016 I'm trying to understand why I receive this warning while running this T-SQL statement (I chose the table variable for ease of testing. This happens with DB tables that have ...
1
vote
1answer
23 views

How can I get a stored procedure to create 2 temporary tables and send 2 emails from the separate tables?

I am writing a stored procedure which creates 1 temporary table populates and then emails the results through if it is above a certain threshold. I am wanting to alter this so that if the results are ...
0
votes
0answers
32 views

Compare Validate TSQL Data Warehouse to SSAS

How do I compare Data in T-SQL DataWarehouse to SSAS Cube? I would like to conduct this from SSMS (SQL Server Studio Management Studio). Example Select CustomerName from dbo.DimCustomer where ...
2
votes
1answer
30 views

Error when upgrading SQL 2016 to SP2 - “The given key was not present in the dictionary.”

I am trying to patch one node in a SQL Server 2016 Standard FCI. I removed the node from the cluster, and ran the installer, but it failed. The summary file contained the following exit code / ...
7
votes
2answers
389 views

SQL Server: Covering indexes including all columns?

Our team has inherited an application and associated database. The previous developers appear to have enforced a rule where every index, on every table, has an INCLUDE clause to always add every ...
0
votes
1answer
21 views

SQL Server Connection property applicationIntent

I have a doubt in ApplicationIntent connection property of SQL Server. The doc say it has two values ReadWrite and ReadOnly. If I set the value as ReadOnly, will it allow only read queries or will it ...
0
votes
1answer
39 views

how to get complementary information from sp_readerrorlog

when auditing what are the failing logins in one of my server, using the sp_readerrorlog procedure I get the following result, when running the following query: EXEC sp_readerrorlog 0, 1, 'Login ...
3
votes
0answers
43 views

how does CXPACKETS relates to CXCONSUMER wait types?

when running a delete query to a table that has many foreign keys I get the following waits. I noticed that I have both CXPACKETS and CXCONSUMER wait types at the same time during of the execution ...
0
votes
1answer
23 views

How to encrypt/secure single columns ins SQL Server?

I would like to secure some columns in my data warehouse. People who are generally allowed to query the database should get returned the columns but encoded. For example: SELECT PERSON_ID FROM PERSON ...
2
votes
0answers
81 views

Aggressive Tempdb spill process

We are having an issue with aggressive tempdb spills that significantly effect overall server performance and, in rare cases, cause AlwaysOn failures. Tested on SQL2016 and SQL2017 Enterprise (recent ...
0
votes
0answers
26 views

How to Test Multiple parameters in SSDT SQL Server Unit Testing?

I want to validate if a sproc can insert given multiple parameters. Instead of writing the unit test multiple times, how can I give multiple parameters? In XUnit/Nunit, I can do something like this : ...
1
vote
0answers
37 views

Query Store- force plan not working as expected

Have similar issue as was ask in other question. In my case none of cases from microsoft docs seems to apply Can something else be the reason? Schema changes not happening, but after index ...
3
votes
1answer
66 views

Non-clustered indexes for columns defined within a composite Primary Key

I have a multi-tenant database where I am utilizing an AccountId column within all tables as a part of the composite Primary Key for tenant isolation. Is it beneficial to create additional non-...
0
votes
0answers
42 views

SQL Server Unit Testing, always set Scalar Value to 1 or Assert Statement

In Sql Server Unit Testing, SSDT, is there anyway to set a default unit test to have scalar value to 1 in Property window? Or write an Assert statement like in Nunit/Xunit? Assert.AreEqual(...). ...
4
votes
1answer
140 views

What does @RC mean in SSDT SQL Server Unit Testing?

In Sql Server Unit Testing, SSDT, what does @RC stand for? Does it mean Row Count, or Row Column? Team wants to understand, we have been using it for long time, good tool which works, just curious.
0
votes
0answers
56 views

SQL Server 2016 Availability Group (Primary Unable to Read/Write)

We have a 3 replica SQL Server 2016 availability group. When the secondary synchronous goes unhealthy the database that resides on the primary becomes unreadable / unwritable, at this time the primary ...
1
vote
0answers
29 views

sql server connectivity broke down to server after its IP address has moved to a subnet. How to restore its connectivity?

I a server called Vermont on a different domain, with a trust relationship. I can ping the server there. But when I try to connect to it, from my SSMS I get the following error: I used to connect ...
0
votes
0answers
26 views

SQL Full-text Filter Daemon Launcher failing to start with error “Access is Denied”

When attempting to start the SQL Full-text Filter Daemon Launcher service through the SQL Server Configuration Manager, it attempts to launch, but then fails. In the event log we get the following ...
2
votes
1answer
56 views

When to Use Functions over Stored Procedures

What advantages/circumstances do functions have over stored procedures? Want to justify use for function. It seems stored procedures benefit in every way, from transaction handling, input, multiple ...
2
votes
1answer
23 views

Return json list of values in SQL Server 2016 using data from 2 columns

I have a database with 2 columns A B -- -- X 1995 Y 2005 C 1962 D 2003 I'm trying to create a SQL statement that will take a string of comma delimited values and return a json ...
2
votes
1answer
87 views

SQL Create Script DDL Table from View

How do I script a Create Table DDL for a View? Example View: create view dbo.CustomerTransaction as select cust.CustomerId, -- this is int cust.CustomerName, -- varchar(25) tr....
1
vote
2answers
26 views

Upgrade SQL Server 2014 AG to SQL Server 2017 AG

Is it possible to migrate SQL Server 2014 AG (Windows 2012, SQL Server 2014) to SQL Server 2017 AG (Windows 2016, SQL Server 2017) using rolling upgrade method? What I mean is Build 2 SQL Server ...