Stack Exchange Network

Stack Exchange network consists of 174 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.

1
vote
0answers
13 views

How to resolve error 15517 when DBO is set to SA?

Newbie DBA here, just getting settled into my first DBA job and trying to figure out how things work outside of a classroom environment. Your patience is appreciated. I'm trying to create diagrams ...
0
votes
1answer
34 views

SQL Server 2016 capped at specific CPU usage

I have a situation where my SQL server is capped at a constant CPU usage rate (13%) and only four cores are being utilized. There is no resource governors or anything like that in place. This runs ...
-1
votes
0answers
30 views

No matching network interface found for resource 'Cluster IP Address' IP address 'xx.xxx.245.140' (return code was '5035')

In a cluster of one node at each site, the cluster is showing this warning and error as Cluster resource 'Cluster IP Address' of type 'IP Address' in clustered role 'Cluster Group' failed. ...
1
vote
0answers
27 views

IntelliSense is enabled but not working in SQL Server 2016 with Azure SQL DB

I have installed SQL server 2016 whose details are as in below screenshot: My queries are pointing to Azure SQL DB. Below are the screenshot of some other settings that I have found over the ...
-1
votes
2answers
69 views

Data Warehouse: Auto Generate Slowly Changing Dimension Code For Any Table with Dynamic Sql

Is there any code to take OLTP/Stage data and transform it into Data Warehouse Kimball Slowly Changing Dimension Type 2? The code should be dynamic, and should be able to utilize any table as input. ...
-1
votes
0answers
36 views

Data Warehouse and Staging Database on Same Server instance [on hold]

What are the benefits/negatives of having Staging and Data Warehouse on the same server instance vs different server instance? 1) OLTP Source ---> 2) StagingDB --> 3) Data Warehouse I understand ...
-1
votes
1answer
30 views

intermittent High Volume of I/O Reads for a SP [on hold]

On my production SQL Server, there are incidents of 95-100% CPU usage almost everyday (1-2 times in a day), lasting for 5-10 mins. Although it recovers itself and no intervention is required. It is ...
0
votes
2answers
47 views

Compile Dynamic Sql before Running

I have a table, and want to test if dynamic sql will compile. Example: create Table dbo.Product ( ProductId int, ProductName varchar(255) ) This will succeed: Column Name exists declare ...
0
votes
2answers
43 views

First and Last of Month Calculations

This is probably a rudimentary question for most, but for some reason I'm not getting it right. I currently use two specific SET formulas for "1st day of last month" and "Last day of last month" 1st ...
-1
votes
0answers
24 views

In Place migration from SQL server 2014 to SQL Server 2016 [on hold]

please suggest how to proceed in place migrtion from sql server 2014 to sql server 2016 in active passive cluster. what are steps needs to taken
1
vote
0answers
23 views

Migrate Access tables to generic SQL Server tables [on hold]

I have the need to migrate about 800 or so Access database tables to one or more generic SQL Server database tables. The requirement is to have no more than 2 or 3 tables in the SQL Server database. ...
0
votes
1answer
49 views

SQL Server - drop user triggered silent revoke of privileges

I encountered following scenario: A user X granted a ton of permission in the SQL Server database. For example it granted SELECT permission on table Y to user Z. A user X was dropped with simple ...
0
votes
0answers
49 views

Kimball Modelling One Column Linking to Many Dimensions

We are conducting Quality inspections, Appraisals, and Ratings on furniture. Many different types of furniture exist, chairs (1), shelves (2), couches (3), table (4). The respective DimProductType is ...
2
votes
3answers
30 views

Linking Waits to a process/statement

I'm starting to take on more of a DBA role and trying to get tooled up with scripts and knowledge to help with this. I have been trying the sp_blizt procs in a dev environment. A key area I'm trying ...
-1
votes
3answers
160 views

Create Slowly Changing Dimension table from Repeated Data

I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. Many ...
0
votes
1answer
40 views

Find if Stored Procedure Error

Is there a method in T-SQL to see if error occurred in Stored Procedure, without the use of @Output or try catch? We have a lot of legacy code 200+ stored procedures. I have seen @Output method and ...
0
votes
0answers
30 views

CDC being dropped automatically

We have a strange situation and couldn't find any help online. So, seeking your help. Since the last few days, the CDC that was enabled on our database is being dropped automatically. We couldn't ...
0
votes
0answers
30 views

Clustered Index for Staging DataStore, identity int or datetime-int composite [closed]

We are deciding between clustered index at our company. Currently have a staging table and a data storage table. The stage table gets truncated every day, and new data is inserted. The ...
0
votes
2answers
37 views

How to find out the service accounts for each of my instances in the current server?

I have the following query that gives me one line for each sql server instance installed in the current server: --list the instances in the current server --marcello miorelli --16-sep-2016 ...
0
votes
0answers
28 views

Creating Synonyms both ways

I have a database project in visualstudio, Database A and Database B. Is there anyway to create synonyms both ways for tables. Example:Creating scripts so when in Database A, tables in database B can ...
1
vote
1answer
37 views

Print Column Names for Synonym for Table in Another Database

I am utilizing one database A. How do I print out the columns names for synonym for table in another database B? create table DatabaseB.dbo.Customer ( CustomerId int primary key, ...
0
votes
1answer
31 views

Print Column Names for Table in Another Database

I am in one database. How do I print out the columns names for table in another database? create table dbo.Customer ( CustomerId int primary key, CustomerName varchar(100), ZipCode ...
2
votes
2answers
99 views

Generate SQL Table DDL from a View

How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql (1) can remove primary key/constraint, (2) ...
-5
votes
0answers
38 views

Creating Master Slave Relationship on SQL Server 2017 (v14.x) using only SQL Server Management Studio [closed]

I have run into an issue where I have a database that I need to replicate on another server (readonly). I've never created a Master, Slave relationship. I don't have remote access to either server, is ...
1
vote
0answers
28 views

Adding Databases to Availability Group

I'm attempting to add databases to an existing availability group using automated seeding but I randomly receive this error: "Seeding Check Message Timeout". I'm pulling this error message from sys....
0
votes
0answers
30 views

SQL SMS: Use T-SQL Editor instead of Plain Text Editor

In SQL Server Management Studio, right clicking a table shows options to "Select..." or "Edit..." rows. I'm not sure if I have my terms correct, but choosing "Select..." opens what I believe is a "...
0
votes
1answer
21 views

Don't show local databases in SQL Always-On AG

We're installing a SQL Server Always On AG solution(SQL Server 2016) in a test-enviroment at our organisation. When testing the features we notice that all databases from a node are listed when we ...
0
votes
1answer
24 views

Is using indexed view with no expand on standard edition a must?

I have recently read the text below in 70-762 Developing SQL Databases (p. 67): The first benefit of using an indexed view is that when you use it Enterprise Edition of SQL Server, it uses the ...
0
votes
1answer
42 views

SQLServer Row level security - Enforce from within database

Have a multi-tenant app running on a single sql 2016 db, I have a RLS (row level security ) predicate on customer_id (which is unique for each tenant) and the column exists in all tables. CREATE ...
0
votes
2answers
39 views

Running SSIS jobs sequentially in T-SQL

We are loading ETL data in two parts, first with an SSIS package and then stored procedure. In the script below, Part2 stored procedure will run even before SSIS Part1 is complete. How do I get Part2 ...
0
votes
0answers
22 views

Availability Group being PARTIALLY_HEALTHY

I'm having a strange issue with a SQL server hosting 11 Availabilty Groups. All those groups have 1 or 2 databases in it and they are synchronous commit with the automatic failover. I noticed ...
1
vote
0answers
43 views

service broker - poison message - how to handle? how to enable poison messase handling?

I am getting constantly this error message on service broker: POISON MSSAGE - Message Removed from the queue to allow other messages to proceed - ApplicationID: 377411 select top 1000 * from ...
1
vote
2answers
40 views

Running Powershell Code embedded in SSIS or TSQL, not in external file

Is there a method to embed powershell code in SSIS or T-SQL, without saving the code in an external file *.ps1? We know SSIS/Tsql can run powershell, however the code always seems to be saved ...
0
votes
1answer
52 views

Powershell Run Stored Procedures in Parallel in Database

We want to run all stored procedures in parallel. The powershell script finds a list of stored procedures in the database (only have 5 right now), and runs them simultaneously. This code seems to be ...
1
vote
1answer
9 views

Insert String Value To Binary(64) Column

I am attempting to use a stored procedure to insert data into a table. The DDL of my table is like this: CREATE TABLE [dbo].[data1]( [ID] [int] IDENTITY(1,1) NOT NULL, [fname] [varchar](100) ...
0
votes
1answer
21 views

Full backup vs Simple TempDB usage [closed]

What is the difference in terms of I/O disk usage between Simple Backup and Full Backup? Does full backup incur more reads and writes? How does full backup mode impact reads and writes to TempDB's ...
1
vote
0answers
11 views

Filegroup per Table partition - remove filegroup and re-add filegroup

I've seaching all over the web for a solution, if it is even possible... I'm not a savvy in Partitioned tables in different filegroups... Our software is supported by SQL 2008 R2 SP2 to SQL 2017 for ...
0
votes
1answer
35 views

subtract missing Value from Last Value

Ok, so it's kind if difficult for me as a non-native english speaker to get this into writing, so please bear with me. I have a source-Table as following: CREATE TABLE [dbo].[Mailbox]( [...
3
votes
1answer
239 views

Can SQL Server disable IntelliSense on SSMS?

I am using SSMS 2016 (ver: 13.0.15000.23) and connecting to SQL Server database 2016 (ver: 13.0.5081.1). IntelliSense is not working on my SSMS. I tried the following: Tools >> Options >> Text ...
0
votes
0answers
25 views

Auto Publish Deploy same table to different Schema in Database Project

We have a table in stage schema called: staging.Customer, containing all the new incremental customers added recently in the system. The data gets inserted into table called dbo.Customer which has ...
0
votes
0answers
25 views

Two SQL Transaction log backups and one of them copy only

We have two different Transaction log backups and one of them copy only. Copy-only triggered from Veeam backup tool. Normal one from SQL native one. I would like to configure Log shipping for all the ...
0
votes
0answers
15 views

Publish Profile Database Items by Schema

Currently using VS SSDT, Does sql package.exe or Visual Studio SSDT have a publish profile option parameter for schemas? We only want to deploy and publish certain schemas. Eg, we have table dbo....
0
votes
0answers
24 views

Deploy one object in database without Whole Publish Profile

We are using SSDT Visual Studio Database projects. I modified single table (eg added column, etc), and want to deploy new table in two Dev/QA instances. I do Not want to publish other changes in the ...
0
votes
0answers
36 views

NT SERVICE accounts should be sysadmins? [duplicate]

I have the following query to get me a list of sysadmins in the current server: select 'ServerRole' = SUSER_NAME(rm.role_principal_id), 'MemberName' = lgn.name , 'Status'= Case when ...
1
vote
2answers
38 views

Upgrading SQL Server 2005 to 2016

I have a requirement to get rid of our old servers, however one of them includes a database server that is currently using SQL Server 2005. We have a new server running with 2016 so I wanted to know ...
-1
votes
2answers
41 views

sql server update single row taking very long time and not finishing

I am trying to update single record for my small database but it take for ever and not finishing but if I restart the SQL server it work fine for short time other tables work fine SQL SERVER 2016 ...
1
vote
0answers
57 views

Configure Always On for publisher

We have a pull transaction replication.Publisher runs MSSQL 2016. Distributer runs on separate server with MSSQL 2016. Is it possible to define Always On over publisher and another new server, so ...
0
votes
0answers
23 views

is there a way to find out what route and endpoint my service broker session is using?

I have been trying in many way, from sys.conversation_endpoints to find out which route and which endpoint my service broker session is using. SELECT conversation_handle, is_initiator, s.name as '...
1
vote
1answer
22 views

Enable Replication/CDC from High Availability Group

I am a newbie to this Replication concept and I am trying to build some scripts to automate our Build Process where we will be disabling/re-enabling Replication on a regular basis. Background: We ...
0
votes
0answers
23 views

when to use Simple or Forced Parameterization in my databases?

I have recently created a filtered index in one of my databases but it would neither be used nor accepted in an index hint in any query unless I added the option(recompile) hint in it. it turned out ...