Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

Filter by
Sorted by
Tagged with
1
vote
2answers
61 views

T-SQL Stored Procedure Returns Before Completing

We are running into a very curious error. We have two production SQL Servers. We call a stored procedure on one (call it Server_A) that calls a stored procedure on the other (Server_B). The stored ...
0
votes
1answer
456 views

TLS 1.2 Implementation Validation

I have a validation question regarding implementation of TLS 1.2. What tools, scripts, processes, etc.. can be used to validate that TLS 1.2 is working when clients, webserver, and SQL Servers are ...
0
votes
1answer
446 views

SSIS OnError propagating when it shouldn't

I have an SSIS package with a data flow task that uses a script component as its data source. The script component retrieves its data from a web service. The call to the web service sometimes fails, ...
0
votes
1answer
15 views

How to have MS Analysis server setup of both modes: multi dimension and tabular

Ref: Is there a way to install both tabular and multidimensional modes? The answer was .."You answered your own question. You simply install another instance of SSAS.".. My MS SQL 2014 has a ...
1
vote
2answers
131 views

How to improve query performance?

I have a query that executes very often on our system and there is no option to change the query text. Are there any suggestions on what could be done? Any help would be appreciated. The query: ...
40
votes
8answers
4k views

Asked to Not Use Transactions and to Use A Workaround to Simulate One

I've been developing T-SQL for several years and am always digging in further, continuing to learn all I can about all aspects of the language. I recently started working at a new company and have ...
0
votes
0answers
25 views

SQL Express Backup and Restore to Different Server

I have been translating an old ADP file format to a more current VB front end using SQL Express Back-end. In order to do this project the owners had me use either VPN or Team-Viewer in to a ...
0
votes
2answers
30 views

Mssql database old bakup restored on production server

I am using mssql server 2016, I have accidentally restored the 24 hours old database backup file on production server, so I have lost the data for today, means I have lost the data of 24 hours, is ...
-1
votes
1answer
1k views

Cannot find Database Mail option in My SQL Server 2014

I could not find the Database Mail option in my SQL server 2014 under SQL server instance -> Management. Where can I enable it?
2
votes
1answer
370 views

msdb.dbo.backupset.compressed_backup_size is inaccurate?

I thought the value in msdb.dbo.backupset.compressed_backup_size represented the size, in bytes, of the full, differential, or log backup file (that is, the BAK, DIFF, or TRN file). However, although ...
0
votes
1answer
680 views

Can't connect with UDL file to SQL Server 2016 but with SSMS it works without problems

I have a strange issue using a UDL file. I can't connect with the UDL file to SQL Server 2016 with a specific user (login) but when I'm trying to connect with the same user via SSMS, it works without ...
0
votes
0answers
25 views

More Efficient 3-Period Rolling Average Calculation

I have a table of numeric answers to survey questions. The goal is to call a stored procedure, with a parameter for PeriodType ("Quarter", "Month", or "Year") that returns this output: Year Period ...
0
votes
1answer
26 views

Find records size in kb/mb/gb for one client from multiple table in one database

I have multiple client using same database, i need to find client wise size utilized.
0
votes
1answer
28 views

Is this Database Model for university is correct

The education system in our country is a little bit different from other countries. We take subjects all through the year and finish the year with the same subject. We have 4 main exams through the ...
7
votes
1answer
46 views

Using XQuery to filter out some children, but preserve parents

SET NOCOUNT ON; DECLARE @xml AS Xml = '<a><b>bbb</b><c>ccc</c><d>ddd</d></a>'; SELECT @xml; SELECT @xml.query('/a/*[self::b or self::c]'); SET @xml....
-2
votes
1answer
64 views

Send by SQL sales report for each seller [on hold]

How can I send a report to each seller about their sales? I can already generate the report, but I cannot send it individually, this is an example of my data this is my script, i hope someone can ...
7
votes
3answers
884 views

Foreign Key with a Constant

Let's say I have a table A, that has two columns: one is an ID for ThingA, and one is an ID for ThingB. The primary key is (ThingA, ThingB). Next, I have a second table, but this time it's restricted ...
0
votes
1answer
107 views

Update a set of SQL statements

dbo.MatchFirstName(@FirstName) and dbo.MatchLastName(@LastName) are table Value Functions(TVFs). Problem: In my Update statement i.e Update #Temp after Insert, I want only those rows to be ...
0
votes
2answers
335 views

Subtracting sum of previous rows

I want to create total field in this table. But in SQL server 2008 can't use 'order by' in OVER clause. I have same PO and difference INV. I want to Qty field minus Quantity field and next row Total ...
36
votes
2answers
13k views

Why is using a table variable more than twice as fast as a #temp table in this specific case?

I was looking at the article here Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
4
votes
2answers
53 views

Fast alter column NVARCHAR(4000) to NVARCHAR(260)

I have a performance problem with very large memory grants handling this table with a couple of NVARCHAR(4000) columns. Thing is these columns are never larger than NVARCHAR(260). Using ALTER TABLE [...
1
vote
0answers
21 views

SQL Server : Memory pressure,Spiked user connections and CPU usage

I have the below sql server environment. SQL Server 2016 standard edition. 128 GB RAM 96 GB alllocated to SQL Server 8 tempdb's with 1 GB each. Have a major database with 2.5 TB size. Reporting also ...
3
votes
1answer
40 views

Seting MAXDOP for SQLserver 2016 and above

I was going through MS documentation reg: setting MAXDOP starting SQL2016 as that Automatic soft NUMA enabled by default and getting confused on how to make the changes to current SQ2014 and SQL2012 ...
4
votes
1answer
91 views

Constant Scan joining

In preparation of my previous Constant Scan question I was experimenting with VALUES in various ways and came across the thing regarding joining of VALUES that looks odd to me. Setup is simple ...
8
votes
2answers
312 views

Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

I am using a recursive CTE on a tree structure to list all the descendants of a particular node in the tree. If I write a literal node value in my WHERE clause, SQL Server seems to actually apply the ...
0
votes
2answers
45 views

Is it safe to move GPT formatted mdf file to MBR formatted disk?

From our current SQL Server, I've copied a .mdf file to another disk (GPT to MBR). It seems to create fault in whole disk, made MSSQLSERVICE offline. Additionally, no error logs were unavailable ...
1
vote
1answer
42 views

SERVERPROPERTY('HadrManagerStatus') equating to 1 even if Always On is not configured

We have a SQL Server Enterprise 2017. I would like to get insights as to why our SERVERPROPERTY('HadrManagerStatus')returns 1 even if Always On is not configured in this instance. I have a local ...
1
vote
1answer
38 views

Cannot choose Storage option on a table in SSMS for standard edition

I am looking into partitioning of tables for SQL Server on two different machines. One machine runs Microsoft SQL Server (RTM) 2016 Developer Edition, and the other is running Microsoft SQL Server ...
2
votes
3answers
2k views

SQL Server Cluster: There is no SQL Server failover cluster available to join

I have set up a SQL Server 2016 Standard cluster (Cluster-SQL) on node 1 (Cluster-Node-1) which is appearing in Windows Failover Cluster manager without issue. When I attempt to add a second node (...
4
votes
2answers
107 views

Ms sql MERGE INTO locks whole table for updates

I have a table for statistic values, it holds millions of records, which is defined like this: CREATE TABLE [dbo].[Statistic] ( [Id] [INT] IDENTITY(1, 1) NOT NULL , [EntityId] [INT] NULL , [...
-1
votes
1answer
29 views

Update Column Based on a Filter of Another Column - Returns NULL [on hold]

I have a table2 which i am inserting head count value based on where condition on table1 and group by. And based on another where condition from table1 in need to update value for another column in ...
0
votes
1answer
49 views

Can SQL Server be forced to use a cached plan for executing a SP or SQL script?

Can we somehow force SQL Server to use a cached plan for executing a SP or sql query? We have a parallel plan in prod which estimates data size in GB's and estimates number of rows to rows equal to ...
5
votes
0answers
164 views

How do I identify the source of the 17886 errors (“server will drop the connection”) in my SQL errorlog?

I have a SQL 2012 server that is throwing the following error every 5 minutes or so: Error: 17886, Severity: 20, State: 1. The server will drop the connection, because the client driver has ...
23
votes
1answer
2k views

Why doesn't this query use an index spool?

I'm asking this question in order to better understand the optimizer's behavior and to understand the limits around index spools. Suppose that I put integers from 1 to 10000 into a heap: CREATE TABLE ...
2
votes
3answers
64 views

What is SQL Server doing in the background once a Backup/Restore is complete?

When I run a backup or restore through the SSMS GUI, the dialog shows Executing with 20% increments rather than every 1% tick. To get round this, we can use DMV's. Thing is, when the query shows 100% ...
1
vote
1answer
36 views

Multiple availability groups--can each availability group have its own listener?

I have created two availability groups containing multiple databases on the same SQL Server instance. Can I have two different listeners for these two availability groups?
4
votes
3answers
163 views

Is there a limit to how many backups can be appended to a single file?

A recent question got me looking at MS Documents and wondering. Is there a limit to how many backups can be appended to a single file? By default SQL Server uses NOINIT to append new backups to the ...
0
votes
0answers
25 views

Collect a column as CSV in GroupBy

I have this schema: Result Story: BILL_INFO has all bill headers. There is a business process called Deposit that occurs once a day: BILL_INFO are updated with a DEPOSIT_ID from DEPOSIT_INFO table ...
0
votes
0answers
22 views

Transaction Replication - re-initialization the subscription after changing an article's structure

I'm using SQL Server 2014 SP3CU2 Enterprise Edition. I have a transaction replication on a DB with some very large tables. I needed to change the structure of a column on one of the tables that were ...
-3
votes
1answer
150 views

Can I update my Microsoft Enterprise SQL Server? [closed]

I just have a quick question about SQL Server and the various tools that come with it. I updated my SQL Server Management Studio to the latest version because I wanted to use the STRING_AGG function,...
-5
votes
2answers
142 views

In the following inventory system, is it possible to force row locks to avoid deadlock and ensure it works as intended? [closed]

I want to atomically reserve inventory for N objects for M users. I have two inventory tracking tables, one for global inventory and one for personal inventory. Global Inventory Table Columns: ...
1
vote
1answer
48 views

Restore ALL databases with sqlcmd in SQL Server (Express, Docker, 2017)

These documentation articles https://docs.microsoft.com/en-us/sql/linux/tutorial-restore-backup-in-sql-server-container?view=sql-server-2017 and https://docs.microsoft.com/en-us/sql/linux/sql-...
1
vote
1answer
176 views

Configuring Availability group between Two SQL Server Failover Cluster Instances

I am having two Windows clusters. Two SQL cluster instances are there in each two node cluster ( Data centre 1 :Windows Cluster 1-SQL2016CLUS1 Node 1 Node 2 SQL 2016 failover cluster instance1 -...
0
votes
2answers
43 views

Does restoring a database in Microsoft SQL Server add objects to Resource Database?

As far as I know the resource database contains information on all objects in its server instance, so I would think it's only natural that when you restore a database from a backup file, part of the ...
0
votes
1answer
100 views

Installing MS SQL Server on Centos

Has anyone been able to install SQL Server on Centos 6? I am running Centos 6.8, and I can only find information on Centos 7. I know that SQL Server on Linux is still in its early stages. I’m curious ...
0
votes
2answers
757 views

Get Date Limitation

So what I need is a limitation for the user of the funded contracts. So this is what I am trying to implement. I am trying to let the user enter dates within the year 2014, which will bring up how ...
2
votes
2answers
216 views

Lots of short term blockings on SQL Server 2008-R2 database

Seeking experts advise on short term blocking which we are seeing for almost a week now. DB is 15 TB and this Table B where we have lots of blocking - almost 300 in no is 2 TB. While using ...
0
votes
1answer
90 views

Fill in missing dates with data value from previous populated date

Given a table with date and ramp%. I would like to add the missing dates with previous ramp% Table and sample data: +------------+-------+ | date | ramp% | +------------+-------+ | 01-01-2016 |...
3
votes
2answers
2k views

After Restore Log_reuse_wait_desc of Replication

I'm wondering if anybody has ran into this issue before and has any suggestions on what's causing it/how to fix it. We are restoring a database into lower Dev environments and after the restore the ...
0
votes
1answer
24 views

User guest does not have permission to run DBCC checkprimaryfile (SMSS)

I am trying to attach an mdf file to a sql server using SMSS, and I got a message that says: User guest does not have permission to run DBCC checkprimaryfile Any clues about how can I solve that ...