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
60 views

How to grant permissions to allow anything inside a SQL Server schema?

I have a schema called training. I have a new role called training_modify. Here are the desired permissions for users placed in that role: Select, update, insert, and delete permissions on any object ...
1
vote
1answer
33 views

Recursive Parent-Child Location Relationship Cache as View

Here is my data structure: CREATE TABLE Locations( LocID int IDENTITY(1,1) NOT NULL, LocationTypeID int NOT NULL, ParentID int NULL, LocNum varchar(50) NULL, LocName varchar(250) ...
0
votes
1answer
4 views

SQL Server Audit Changes in user permission

Is there any way to know that which privileges user previously had? Previously SQL Server user had rights on some views in a given database. But now these have been revoked, is there any way to get ...
2
votes
1answer
23 views

Cannot find DReplay.exe for SQL Server 2019

I have been working on SQL Server 2019 version and trying to setup a load test environment using SQL Server Distributed Reply Controller - Client, however I cannot find the DReplay.exe. I thought the ...
1
vote
3answers
65 views

How to calculate end_date from start_date and duration in SQL Server Query

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date. I am providing the start_date and duration as the input to calculate end-date. While calculating the end_date the ...
2
votes
1answer
26 views

Should “Long Running with Low CPU” be expected for an SPROC that performs millions of INSERTS?

Brent Ozar's sp_BlitzCache flags the sproc below as the most "expensive" one in my system ("Frequent Execution, Long Running With Low CPU"). The sproc just checks a table for a row corresponding to a ...
1
vote
1answer
31 views

SQL Server Grant CRUD to Users on One Schema

I have a schema called training. I want users who will be trained to have CRUD permissions on any object on that schema. I do not want them to have any other permissions on any other object outside ...
0
votes
0answers
22 views

Stream output messages to application console [migrated]

I have a procedure that takes a while to run and I would love to be able to run this procedure from a Kotlin application and see output messages live within the Kotlin application console. Having a ...
4
votes
1answer
162 views

How can I log which queries are in a distributed transaction on an MS SQL server?

I am looking at migrating a database from a self-hosted cluster to Microsoft Azure SQL. I am aware that there are a few distributed transactions involved, which isn't supported on Azure SQL. Is there ...
-1
votes
1answer
13 views

Find closest follower in a rank list

On my database I have the table named Rank_List_T: Rank Name Time 1 John 110 2 David 120 3 Bob 121 4 Hans 124 5 Claus 126 6 Gustav 140 I am interested in the person, who ...
0
votes
1answer
23 views

SQL Server Management Studio cannot Connect to Always On Availability Listener

I am trying to connect to the AG Listener in SSMS. I choose [Connect] Then [Database Engine] I type in the name of the listener I created. SQLListener [Windows Authentication] Click [Connect] ...
0
votes
1answer
23 views

Should I install SQL Server 64 bit or 32 bit

Sorry if this question is lame. For production purpose, i wanted to know when to install SQL Server 64 bit package and when to install 32 bit package when the client's PC has: - 32 bit OS and 64 bit ...
0
votes
1answer
28 views

only last row is inserting in table when having “instead of insert” trigger on that table [duplicate]

I have this trigger CREATE TRIGGER [dbo].[intrigger] ON [dbo].[test] instead of insert AS begin declare @name nvarchar (50),@city nvarchar (11), @tdate datetime , @fid int,@fopd datetime; select @...
0
votes
1answer
27 views

SQL Server AlwaysOn Availability Group - Creating an Index Online in SSMS

I am running a 2-node SQL Server 2016 AlwaysOn Availability Group. I would like to create a non-clustered index online in SSMS for one of the availability databases in the AlwaysOn Availability Group....
-4
votes
1answer
50 views

How to shrink a Tempdb without restart the server

I want to shrink my tempdb without restart. I tried all possibilities available in Internet like 1opentransaction(there is no open transaction) 2.
0
votes
1answer
36 views

SQL-SERVER: how to get the maximum length of a variable of VARCHAR help

Hello, in this question im trying to get the maximum length of a name but i cnat seem to figure out what im doing wrong, i've search the web for answer but find none. i tried to put the MAX function ...
3
votes
1answer
57 views

Is it possible to see blocker's host name or machine name using sp_WhoIsActive?

I am using sp_WhoIsActive. When blocking is occurred on a server I want to quickly identify who is the culprit. Column login_name does not give me much info about blocker because there are hundreds ...
0
votes
1answer
20 views

Best way to refresh some of the tables (non primary key) on a daily basis SQL server [closed]

Best way to refresh some of the tables on a daily basis SQL server apart from using Trn Replication as some tables do not have primary key
2
votes
1answer
52 views

Microsoft SQL Server - Permission to create a Snapshot

I've developed a set of SSIS packages to load a data warehouse. In order to manage any issue that may arise during package execution, each package starts by creating a snapshot of the data warehouse. ...
0
votes
1answer
41 views

How can I identify all large tables that have no index?

Is there a way I can identify all objects with over 10K rows and no index?
10
votes
2answers
560 views

Break the link between a Database User and SQL Server Login

When restoring database from once instance to another one sometimes has to relink SQL Server Logins to the Database User. This is normally achieved with one of the following procedures. Deprecated ...
2
votes
1answer
52 views

SQL Server 2019 Core - Always on Availability Group - Availability replica is disconnected

all. (I have read through all the other topics related to this one, and I cannot find anything to is similar to this issue, unless there is one hidden that I have missed.) OK. SQL Server Always On ...
0
votes
1answer
29 views

How to get a breakdown of CPU usage at each point in time?

I have a SQL Server RDS instance, running on a db.m5.xlarge (4 vCPU, 16GB RAM) with Multi AZ enabled. I am trying to determine the cause of a constant 25%-30% CPU load on the instance at a time when ...
1
vote
2answers
66 views

SQL Server order of execution and query plan

I am aware that the order of execution of a query in SQL Server is (excluding TOP, etc): FROM, JOIN WHERE GROUP BY HAVING SELECT ORDER BY However, I cannot understand how in the query plan the sort (...
3
votes
2answers
61 views

Referential Integrity - Indirect Foreign Key In “Depth”

Question What is the best practice for ensuring a Key from a "Grand-Parent" or "Great Grand-Parent" table is maintained when a "Child" or "Grand-Child" table is created from multiple relationship ...
1
vote
1answer
26 views

Best-practice use of Resource Governor in SQL Server 2017 [closed]

I am working on a server that houses both an OLTP workload and a data warehousing/reporting workload. The OLTP system requires sub-second (in the milliseconds) response time while the reporting ...
7
votes
1answer
210 views

GROUPING SETS returning unexpected results with calculated expression

Here we have two similar queries using grouping sets where the SELECT clause contains some expressions calculated in aggregation: SELECT RN10, RN10 / 10, COUNT(*) FROM ( SELECT RN, RN/10 AS ...
0
votes
1answer
51 views

Determine the source of user permissions in a database

I have a developer that just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment. How can I track down what security ...
0
votes
1answer
31 views

SQL Replication [duplicate]

I'm attempting to set up SQL Server replication for the first time for two servers on two different domains. Server A is the primary server which is running MS SQL Server 2014 whereas Server B is ...
4
votes
2answers
1k views

Memory utilization by SQL Server

I m using SQL server 2012. I have a stored procedure that will eventually be tuned, but I have one question. My server memory is 32 GB and max memory assigned in SQL Server is 20GB. When I execute ...
1
vote
0answers
32 views

SSIS 2017 - Parse flat file with multiple columns and headers on multiple lines

I receive a daily CSV file that contains 600+ company employee positions, each of which are formatted as follows: Position,Description SUP1015,Shipping Supervisor Day Work UOM:,Hours,Active:,Yes,...
6
votes
4answers
488 views

How can I configure Ola Hallengren IndexOptimize script to run against a subset of tables

I'm using Ola Hallengren IndexOptimize script against a SQL 2016 database that is 7 TB in size, with over 300,000 tables. I only have a 6 hour window each night to manage indexes. I'm using the ...
1
vote
1answer
31 views

Speed— Restoring from Azure Blob to local database

We're being encouraged to move our database backups to Azure blobs, which we access through the Microsoft Azure Storage Explorer. We're running SQL Server 2017. Our backup times are fine-- 20 minutes ...
1
vote
0answers
26 views

Index Exists, data is present, but statistics are empty. Why? [duplicate]

I’ve created a dump of all statistics on all tables in a Production database (based on the three sets returned by DBCC SHOW_STATISTICS). The database has been up and running for several months, and is ...
2
votes
2answers
58 views

Database cannot be opened. It is in the middle of a restore

I have executed these two commands: USE master GO RESTORE DATABASE test FROM Kingston WITH NORECOVERY GO USE master GO RESTORE LOG test FROM Kingston WITH STOPAT = '2020-05-13 14:05:25', RECOVERY; ...
0
votes
1answer
33 views

odd OS memory consumption by two nearly-identical SQL 2008 R2 instances

Instances are legacy, something we have to carry for the next 3 years, 2008 R2 64-bit, both SQL Server and Windows Server OS. Two copies on two nodes. Both servers are set to consume "Maximum server ...
1
vote
0answers
36 views

Issue: Data synchronization state of availability database is not healthy

I have a sqlserver 2017 cluster (sql server 14.0.3026.27) with two secondary nodes set with Availability Mode = Synchronous commit. The server has 8 databases that are replicated on the secondary ...
0
votes
2answers
46 views

Deny certain user from updating certain rows [duplicate]

Say I have a table like this: canModify | name | age ------------------------ 1 | John | 24 0 | Nick | 21 I want to grant update access to a specific user only where canModify=1 I ...
0
votes
1answer
32 views

Logon failed for login due to trigger execution / LOGON TRIGGER

I have this situation. I created a logon script to block any session for specific logins. These logins are in a table. So the behaviour of my script is simple. Get the login from the table, compare it ...
0
votes
1answer
33 views

dbmail failing to send intermittently [duplicate]

We have a SQL Agent job that runs daily that sends emais out to our drivers with their schedules for the next day. For 85% of drivers, the emails are not getting out of the SQL server. The other 15% ...
0
votes
1answer
30 views

High Availability with Transactional Replication

We are using SQL 2017 Enterprise and setting up a new DR site. One of our servers (Server B) is used for reporting and has many databases, one of which has tables populated through Transactional ...
0
votes
3answers
49 views

Problem with IF statement [closed]

I am having an issue with this stored procedure. When the user selects "Yes", it should do what it says in the if statement for the "Yes" but it does not do anything, it does not insert anything into ...
0
votes
1answer
45 views

Pivot Multiple Columns with a Cross Apply

I am trying get the following result: At the moment, my query (and hopefully a guide to table structure) resembles: WITH CoreGateway AS ( SELECT g.GatewayTitle , g.GatewayOrder , ...
1
vote
1answer
33 views

Change collation in easy way

I'm a developer and sometime I face with restoring a DB with a different collation than the server. Our application uses the tempdb too and as result some joins fail due of the different collation. So ...
1
vote
1answer
60 views

Lost SQL credentials and unable to connect to SQL via SQLCMD

I have a machine running SQL 2014 but have neither the windows authentication or sa authentication details due to mismanagement of a previous support team. Having tried logging in to SQL Management ...
-2
votes
1answer
27 views

Function to convert time difference between two dates into timer format

Does anyone have a function that converts date difference between two dates into timer format ? For example: select udf_DateDiffTimer ('2020-05-12 14:12:34','2020-05-12 14:12:35') The result should ...
0
votes
1answer
46 views

Is it bad to use NULL in foreign key column to mean something (e.g. this is the default data)?

I have tried searching for good guidance on this already, but without much luck. Still, apologies in advance if this is duplicated elsewhere. The Problem In a nutshell, we pay external contractors ...
0
votes
0answers
56 views

SQL Server Full-Text Search Order by another Column Performance

I have two tables, Entity and EntityDetails. Entity table has columns EntityId, Col1, Col2, Col3, LastUpdated (smalldatetime). EntityDetails table has columns EntityId, Details1, Details2. Where ...
0
votes
0answers
5 views

Calling a SSAS DAX Evaluate command in Java

I have a tabular database in SSAS 2019, where the data source is SQL Server 2019. I also have a Java application, and I need to retrieve data from SSAS using DAX, preferably with REST API. When I ...
0
votes
1answer
31 views

Multi-Subnet Always On with Availability Group for DR [closed]

I researched a lot of mutli-geographical DR - Disaster Recoververy solution architectures for MSSQL and most of the proposed solutions I came across recommends HA using 2 nodes Failover clustering in ...