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
4
votes
2answers
405 views

Why is there an aggregate in this UPDATE plan?

Given this declare @Data table (id int, fact char(1)); declare @Summary table (id int, collected varchar(99)); insert @Data(id, fact) values (1, 'a'), (1, 'b'), (2, 'c'), (2, 'd'), ...
0
votes
0answers
27 views

SQL Server Replication with Temporal Tables

After a bit of advise. I have an existing database that extensively uses Temporal tables (i.e. System versioned tables). We are looking at an environment where we have a near to real time replica of ...
0
votes
1answer
27 views

Index Rebuild Operation

I'm planning to do an index rebuild operation for over 30+ indexes. Some index rebuild require almost >30min because the Table Record is around 60,000,000. Command used for this table is ALTER ...
0
votes
0answers
34 views

Design Instructional Columns [closed]

I am creating a table that will provide instructions to an application. Based on the call to action the application will use data from different tables. For example: Say you have an eCommerce website ...
0
votes
0answers
33 views

SSRS ReportServer blocking from a CREATE PROCEDURE

We have a PowerBI Report Server that will just continue to load on the homepage (http://reportserver/reports) and just times out after certain minutes. When we investigated the ReportServer database, ...
0
votes
1answer
35 views

sp_configure set at code execution - Pro/con's of turning on/off

The right answer of course is 'it depends', but on the surface it seems to make sense to turn on/off some options when needed as the code sample below for Ole Automation Procedures would do. Could ...
0
votes
0answers
30 views

SSMS 2018 How to create query that display tables with DBA defined columns and tables that are not empty

I am a power bi user, my current task was to do custom "views" on Microsoft SQL database so that those "views" can be used in doing power bi report later. Now there's one problem that i have ...
3
votes
1answer
43 views

Why does TLS1.2 break connections to SQL Server?

NOTE: Requested to move the question from StackOverflow to dba Don't know if this is really a SQL Server problem or not. Client moved to TLS 1.2, it broke an app that connects to SQL Server 2017 and ...
1
vote
1answer
25 views

script insert SQL Server 2005 from SQL Server 2014 Express

I have a database in SQL Server 2014 Express, and I want to import it to SQL Server 2005. I have generated the scripts to generate the database and to insert the data. All the script generated to be ...
1
vote
0answers
48 views

SQL server 2012 audit caused blocking

When I enabled Audit on the SQL server, it blocked all other transactions also Activity monitor was not able to show anything. DPA could not collect any data during Audit time. The Audit was for ...
2
votes
2answers
120 views

How to install SQL Server 2017 Express in “Quiet Simple” mode without an extraction folder?

I need to install SQL Server 2017 Express with as little user interaction as possible. I am using the SQLEXPR_x64_ENU.exe setup file that I found a Microsoft download for. The installer I am currently ...
0
votes
2answers
55 views

Restore database stuck at 100% progress

We are seeing restore taking more than 25 minutes for a simple 4 mb file .Restore progresses to 100%,but it wont update any status from there When checked in error log ,we could see below error... ...
0
votes
0answers
46 views

Inline function inside CASE statement

I have a scalar function which was getting used inside several queries. Scalar function was called inside WHERE and INSIDE CASE statement. This created huge IO and CPU performance issue due to the ...
0
votes
1answer
31 views

Mirroring sql server , how to change the port?

I have two servers on the same cluster, in fact my goal is to set up "high availability", but the databases that I have integrated are not synchronized, so I think it is " mirroring "that I created ...
0
votes
1answer
29 views

Rows to EAV structure with row identifier

Would you help me with this, please? Imagine any SQL table or query results like… +-----------+----------+-----+ | FirstName | LastName | Age | +-----------+----------+-----+ | Peter | Whatever |...
0
votes
2answers
55 views

Granting Permission on Databases and Schemas

I have a Security permissions script i have inherited that i'd like to improve. The script grants permissions on the user databases and Schemas. I'd like to improve the script by iterating through ...
0
votes
1answer
26 views

Query FROM Indexed View queries underlying table in Execution plan

I have a largish table (>126 million rows), and a need to optimize a specialized set of queries, so I created an Indexed view on the table, and coded a procedure that would select from the Indexed ...
0
votes
1answer
65 views

How to resolve MDF and LDF files remaining in use?

I am attempting to copy off approximately 220 gigs of database MDF and LDF files from a MS SQL server 2000 install in order to transfer ownership to a third party. The error I get when I try to stop ...
0
votes
1answer
45 views

How should JOINed columns be included in an index?

We are using SQL Server SE. Using a simple query, I would like to determine which index setup is best. The simple query is: SELECT COUNT(*) FROM (PS LEFT OUTER JOIN LIS ON (PS_ID = ...
0
votes
0answers
35 views

ODBC in Access working but not in SQL Server

I have a postgres database running on a remote server. I'd like to be able to query it in SQL Server. I downloaded the ODBC Driver for postgres and created a system DSN which successfully runs a ...
0
votes
2answers
46 views

MS SQL Server cycle calculation

Dears, I have an problems with a calculation of this formula: Column "A" has always the same value for a group of SubIDs. In the "Result" column I need formula (function) like in the example columns ...
0
votes
0answers
28 views

Is it possible to have SQL server look for entities under other schemas than DBO when the schema name isn't specified in the query? [duplicate]

Not sure if this is a thing, and I understand it's better practice to write your queries using the two-part naming convention, but it would be cool if SQL Server had a way to setup a schema priority ...
0
votes
1answer
39 views

SQL Server - Defining and populating a new temp table column fails only once

How can I add a new column to a temp table dynamically in a stored procedure and populate it with some values, without resorting to dynamic SQL? I am getting an error only on the first time I run the ...
0
votes
1answer
23 views

SSMS18 - Default File Location (Changed ssms.Application.pkgdef)

I have changed the DefaultProjectsLocation (SSMS18) from the default (My Documents) to a folder on my network. But when I run SSMS18 and open Projects it is still looking at the default location. What ...
1
vote
2answers
25 views

Insert with function result - prevent duplicates

I have a MS Sql function which has some logic to process the next ID of an element (concats letters with numbers based on some business logic, also based on a select count()). Somehow, during the ...
0
votes
1answer
44 views

SQL Statement Layer Filter Queries Limit Access

Goal: Want to create a so called "SQL Statement Layer" which checks/filters all incoming queries. If incoming query is invalid, should be denied. Use-case: Limit database access and avoid ...
0
votes
0answers
12 views

SSMA for Access - Migration Assistant Wizard remove password prompt after linking table

Hello used SSMA to create a linked table using SQL. Is there a way to remove this password prompt after opening the table in Access ? I tried adding the username and password in the connection string ...
1
vote
1answer
86 views

Select * from View is taking a SUPER long time [closed]

I have created a view which gets data from 5 tables, 3 of them containing 200k+ rows,340k+ rows and 2.1mill+ rows. The view contains group by clause, case statements and CTEs. When I do a simple ...
0
votes
3answers
84 views

Log backup vs differential backups

We're trying to define a backup policy. We were going to apply following: Weekly full backup, retained for 30 days. Nightly differential backups, retained for 7 days But we're also exploring other ...
9
votes
4answers
2k views

Increased memory to server, but do I need to change page-file size?

I'm running SQL Server 2017 on Windows Server 2012 R2 and have increased the memory from 256 GB to 512 GB. I've noticed the following configuration for Virtual Memory: C: [OS] = 2 GB G: [DATA] = ...
0
votes
2answers
48 views

If I manually failover my AlwaysOn AG primary replica to the secondary and take the primary database offline will it auto-sync when back online?

I have an AlwaysOn AG setup between Database A (primary replica) on Server 1 and Database B (secondary replica) on Server 2. If I force a manual failover from my primary replica on Server 1 to the ...
0
votes
1answer
42 views

What steps do I need to take to move my SQL instance to a different drive on the same server but with the same drive letter?

I need to move my SQL instance to a different drive on the same server but plan to rename the drive to the same drive letter as the original drive the instance was on (after renaming the old drive). ...
0
votes
1answer
28 views

dynamic data masking read uncommited

I have read about Dynamic Data Masking and one question is still in my mind. I am wondering what will happen is such scenario: 1. Dynamic Data Masking is set up on table 2. ETL process is loading new ...
3
votes
1answer
78 views

When were there changes in the memory settings of SQL Server

Is there a log, query or any other way to see when there was a change made to the memory settings of SQL Server?
1
vote
1answer
16 views

Will the source dataset for a cursor update on each iteration if within the cursor loop I'm updating one of the tables that dataset references?

In this example: DECLARE @KeyField INT, @ValueField INT DECLARE CursorName CURSOR FAST_FORWARD FOR SELECT T1.KeyField, T1.ValueField FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.ValueField = ...
2
votes
1answer
33 views

Sql Server - Build a json array of integer from an aggregate

I want to use an aggregate to build a simple JSON array like this LuckyNumbers array : [{ "id": 1, "name": "Josian", "LuckyNumbers": [581, 777] }, { "id": 2, "name": "Paul", "...
0
votes
1answer
34 views

Is it possible to get the name of a procedure by it's plan handle or query plan hash if the execution plan is already cleared from the plan cache?

I ran a home-brew trace for the length of 1 day that captured a few things that were running. The trace includes the specific query text, the parent entity text of that query, the Plan Handle, and the ...
0
votes
1answer
47 views

List users permissions to tables and schemas on MSSQL 2016

List all permissions for a given role? has a nice query for listing users and their roles on a DB. How could we list all tables and users with their permissions on these tables?
0
votes
4answers
55 views

What role is able to grant role permissions on MSSQL 2016?

Unfortunately, we'll have to remove db_owner role from not admin users, because people were abusing it. We'll split roles permissions in 3 groups: 1) readonly (SELECT) with specific write (INSERT,...
1
vote
0answers
52 views

SQL Server 2016 table corrupt

I have an SQL Server 2016 v13.0.5101.9 hosting Sharepoint databases. The weekly DBCC CHECKDB job discovered database corruption (or inconsistency): Msg 8928, and Msg 8965 popping up. I narrowed it ...
0
votes
0answers
23 views

Always On Availability Groups and MSDTC for SQL Server 2012

I have been told that since MSDTC is not supported for SQL Server 2012 Always On Availability Group and should be turned off. what specific settings do I need to change to turn it off? This is what ...
0
votes
2answers
36 views

Database mail in Azure VM with SQL Server installed on it

We migrated 4 servers with SQL Server instances to Azure VM around 2-3 months ago. We configured "database mail" the same way we did for the on-prem servers. Mail worked fine immediately. All of a ...
0
votes
1answer
30 views

Query to get dates NOT in table

I am building a booking app and part of this involves reserving a courtesy car. Courtesy cars can be booked out for a period, e.g. 2nd April - 11 April 12 April - 19 April etc... These dates are ...
0
votes
1answer
31 views

Can a trigger with an “insert into” statement cause Intext Exclusive locks for small insert operations?

We have a trigger that runs AFTER an insert in a table (Nodes) that creates entries in another table (NodeClosures) that selects entries (6 on average) from the NodeClosures table and inserts them as ...
0
votes
2answers
30 views

Does MSSQL Server need more space than the size of the data itself for importing? How much more?

Does MSSQL Server need more space than the size of the data itself for importing? How much more? How to tell? Recently tried copying data into MSSQL server (2012) Microsoft SQL Server Management ...
0
votes
1answer
19 views

Update table with data from table in a separate database

TableA ID Address Location Country 1 NULL Region1 Austrailia 2 NULL Region2 Antartica 3 NULL Region3 Egypt TableB ID Address Name 1 test avenue james 2 test ...
0
votes
1answer
54 views

Compare two tables in SQL Server

I want to compare two table in SQL server and find difference. I want Warehouse and Product combination and see the difference. I am expecting to combine and display both table together and write ...
-1
votes
1answer
31 views

How to add query hint inside a view

I have a view that contains multiple joins, and this makes the query run really slow. It takes more than 3 hours to execute this query via the VIEW. I the copied the SELECT ... part of the statement ...
0
votes
0answers
26 views

Meaning of different icons in the SSMS Object Explorer [duplicate]

I have noticed that the Database symbol to the left of the Server name in the object explorer tends to differ without any obvious reason. Two servers have a database symbol with a Green arrow on them ...
0
votes
1answer
27 views

How to use variable in Exec statement [closed]

I'm trying to add my target server to the Multi Server SQ LAgent Job (MSX). We have multiple versions of SQL Agent jobs and I'm trying to automate adding only related targets according to SQL Server ...