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.
24,917 questions
0
votes
0answers
17 views
One SPID has deadlocked itself - how? [duplicate]
We had some Deadlock alerts raised today - on further investigation I have found that it seems to all be the same SPID and related to parallelism events.
How has this happened? I didn't think ...
0
votes
2answers
23 views
How to find if difference between all row data for a specific columns are same
I have a table with column with following data,
Column1
0.50
1.00
1.50
2.00
2.50
....
10000.00
Is there a way to validate the pattern that each data row has a difference of 0.50 ?
I tried googling ...
0
votes
2answers
27 views
Do I need full recovery mode when I have multiple daily backup?
I have created 5 maintenance plans on my SQL server (say 50 !) which generate databse backup 5 times daily. As I read in this Q/A the full recovery is good when I need Point-in-time recovery.
My ...
1
vote
1answer
15 views
How to get the SQL-server login name of an integrated security account?
Let's say I'm a user in windows domain DC (DataCenter).
My user-name is DC\Somebody
I'm in group DC\Developers
Now SQL-Server has the login group called DC\Developers, and with that, I can login on ...
1
vote
1answer
9 views
Add replica from Node1 can't find node2 while installing SQL AlwaysON AG lab on virtual machines
I am trying to set up a SQL AlwaysON AG lab on virtual machines, with windows server 2016 and MS SQL 2017.
While creating a new Availability Group on node1 by the wizard, I meet difficult that the ...
0
votes
0answers
21 views
Understanding MAXDOP behaviour for queries going parallel
For my current server as below
4 Sockets with 10 cores per socket and 20 logical processors per socket.
Total 80 per SQL licensing: 4 NUMA nodes with HT enabled
I've set CTOP 50 and MAXDOP 8 on ...
1
vote
1answer
23 views
How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement?
How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT of an UPDATE statement? Neither @@IDENTITY nor SCOPE_IDENTITY() appears to be set properly.
...
1
vote
1answer
12 views
SSAS Tabular Model or SQL Relational Database for new Datawarehouse? Is SSAS still a good to learn technology?
I am in the process of creating a data warehouse using SQL Server for my company.
I have created a POC with a simple SQL Relational database with a few(about 10) stored Procs for the ETL process.
Now ...
-1
votes
1answer
20 views
How to check if a start and end date interval is within the range of another start and end date interval
As the title says, I need to check if a start and end date period is within the range of another start and end date period. I don't know if this needs to be done in a trigger or a stored procedure.
...
6
votes
1answer
383 views
Mysterious procedure calls without parameters - but no exceptions generated
I've been tracing a handful of stored procedures with EventClass RPC:Completed that are intermittently called from .Net SqlClient Data Provider without required parameters - just "exec SprocName".
...
1
vote
1answer
30 views
Can the LOG file fill up from index rebuilds where the indexes are not fragmented?
Working on SQL Server 2014.
I have noticed that a log file (60GB) on a database has filled up within the past day. To my knowledge, I am the only developer using this database and I have not been ...
1
vote
0answers
13 views
Asynchronous availability groups not synchronizing on SQL Server on Ubuntu
I installed SQL Server 2017 on Ubuntu and setup an availability group with cluster type = "None".
I set the "Availability" Mode to "Synchronous commit" and tested it. It works fine. All transactions ...
1
vote
0answers
37 views
How avoid updating unnecessary columns in Update statement?
I have an Update statement which update several columns. Some of those columns are nvarchar(max) type and not always need to be updated.
I would like to avoid updating unnecessary columns, so, the ...
2
votes
0answers
12 views
SQL Server Audit File Metrics Information
I'm currently working in an environment that requires we enable SQL Server Audit, enterprise-wide. We generate .sqlaudit files as we have retention and access guidelines, and I'm finding that we need ...
0
votes
0answers
24 views
SQL Server - CASE for multiple tables
hope I can explain myself right in this question.
I have three (3) tables from which I have to build a report, this is the data selection process I need to do in order to correctly output the data to ...
0
votes
1answer
34 views
SQL Server 2017 Configuration Problems
On my Development Server I have two instances configured; the default and one for our ERP system testing. I can connect to the ERP instance remotely with no problems. However, I cannot connect to the ...
3
votes
2answers
80 views
why it is doing index seek
I created a sample table as below
CREATE TABLE [dbo].[StatisticsDemo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL
) ON [PRIMARY]
Then I inserted below data as below:
...
0
votes
0answers
10 views
R Studio Error '/ not defined for Date Objects '
Bit of a newbie to R. Get this error running a regular report that other guys in my team run ok. Went through and compared librarys were ok ?
Customers1$date <- Sys.Date()
Customers1$MonthsLive &...
0
votes
0answers
36 views
Alter view after table drop
I want to alter the view for the drop tables.
For an example:
View:
create view View1 as
select * from table1
union all
select * from table2
union all
select * from table3
union all
select * from ...
0
votes
0answers
18 views
[SSISDB].[internal].[cleanup_server_retention_window] Foreign key Violation
I have setup a job to cleanup SSISDB with predefined storeprocedure "[SSISDB].[internal].[cleanup_server_retention_window]" but sometimes i get following error. After that i have to do manual cleanup ...
0
votes
1answer
62 views
Is there a way to have a system generated report of list of DBA in SQL Server? [on hold]
I'm an auditor, and I want to ask if there is a list of system generated report of DBA users in SQL Server? Per my audit client, there is no way to generate it.
However, I'm thinking that this is a ...
2
votes
2answers
39 views
Stored Procedure - Reseed Identity
I have a bunch of LU tables that have values added and deleted all the time. Therefore, I figured writing a quick stored procedure would serve me well and then can call the stored procedure on another ...
5
votes
1answer
40 views
Find which databases have a particular service in SQL Server Service Broker
From what I currently understand, the names of services are global to the instance and you can refer to them without knowing which database contains the service and associated queue etc. In addition, ...
0
votes
0answers
37 views
Using @@PROCID to return name of stored procedure
We are migrating from Sybase to MSSQL 2016, and have triggers on some Sybase tables which we have ported from SYB to MSSQL. These triggers are really useful in SYB because they tell us the proc wrote ...
6
votes
1answer
50 views
Query/Table Hints for the History Table when Using the FOR SYSTEM_TIME Clause
Probably a pretty quick question, but is there any way to specify Query/Table hints for the History Table when using the FOR SYSTEM_TIME statement when querying temporal tables? I suspect not, but I ...
2
votes
1answer
31 views
How to find dropped or ghosted columns in Azure SQL DB?
In SQL Server, dropping a column is a metadata only change; the data remains in the index until the index is rebuilt.
On prem, I use system_internals_partition_columns to determine what indexes have ...
4
votes
3answers
654 views
How to determine if a hyphen (-) exists inside a column
In a CASE expression, I'm trying to search inside a text column to identify a hyphen (-):
CASE
WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7')
AND al.NEW_ADDRESS CONTAINS '-'
...
-3
votes
0answers
53 views
Can anyone help me with this hideous query plan? [on hold]
Here's the plan
https://www.brentozar.com/pastetheplan/?id=SJxeroW6N
This is our number one throughput SP, I'm just trying to determine what I can do to improve it.
0
votes
0answers
30 views
Values for maintenance solution [on hold]
I am working on the SQL Server maintenance script. The job will execute when you enter date and time. What date and time format will you use for this task?
0
votes
0answers
47 views
Is a SQL Server DB worth shrinking? [duplicate]
I purged about 100 GB on 200 GB SQL Server v11 DB. The MDF file still show about 200 GB in the file explorer.
Everything looks fine, first tests shows 50% time treatments increase and the backup's ...
0
votes
3answers
34 views
Get distinct category columns with multiple subcategory columns mapped in table
I have 2 tables, category and subcategory, this query
select C.Description,s.Description
from category C
inner join SubCategory S on C.CategoryID =S.CategoryID
where C.IsActive=1 and S.IsActive=1
...
0
votes
2answers
34 views
Example of MS Access SQL IIF to T-SQL
Given the syntax rules shown below
Access: IF(Condition, ReturnIfTrue, ReturnIfValue)
T-SQL: CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END
What would the below be formatted to T-SQL ...
4
votes
1answer
49 views
Execute Permissions for a Store Procedure that creates databases
I have a Stored Procedure that creates a Database for a specific project which needs to run every month by one of our Data Analysts. The question is how do I structure this to enable the Analyst to ...
-1
votes
0answers
25 views
Multiple rows with identical IDs to different columns - do I need to pivot? [on hold]
There are multiple records in my database for a single ID. I want to get only a single value from duplicate data and single different value in the next column with the same ID. Do I need to use PIVOT ...
1
vote
2answers
24 views
Adding a Far DR secondary server Log shipping on already configured SQL server
We have a setup where there are 3 SQL Server instances in which 1 primary,1 Near Secondary,1 Far Secondary.
So the there is already log shipping configured between Primary and Near DR Secondary.
Now ...
-5
votes
0answers
39 views
script - iterate through all databases (SQL) [on hold]
i need script who iterate through all databases on the requested instance of the MSSQL server and for each individual database, finds all the tables and their associated indexes (if any) and ...
0
votes
0answers
12 views
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing
When I try to process my cube and specifically the Employee_DIM, I get the following error :
Errors in the OLAP storage engine: A duplicate attribute key has been
found when processing: Table: '...
0
votes
1answer
26 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 |...
0
votes
0answers
18 views
Adding a database to availability group is case insensitive and cause vss backup exclusions
I was using a vss solution to take backup of my SQL availability group databases where I encountered a scenario when I have a database name like "TestDB" and when i added database to an availability ...
2
votes
3answers
38 views
SQL Server Index Maintenance-Conditional Rebuilds
I have a question about index maintenance. I have a weekly process that does all of my database checks, etc. and a weekly rebuilding of indexes. Those rebuilds have been causing some locking pressure ...
4
votes
0answers
41 views
How to know which DMVs relate to each other?
Is there any way to know which DMVs/DMFs are related to each other?
Some of them are well known or obvious, like sys.dm_exec_connections JOIN sys.dm_exec_sessions ON session_id = session_id, or the ...
0
votes
1answer
21 views
Get not matching record as null while joining two queries
I have two queries having same columns, 1 returning 3 rows and other returning 2 rows. While I join this two query I am able to get only 2 rows. How can I get three rows with not matching record as ...
2
votes
1answer
121 views
SSMS refuses to start
I've recently installed SQL Server 2017 Express and SSMS v18.0 on my home computer (Windows 10) and at first this worked fine. However, after a reboot, SSMS flat out refuses to start. No error message,...
0
votes
0answers
39 views
+100
Differences Between Setting Up Transaction Log Shipping through Wizard vs Scripts
There appears to be a fairly significant difference after setting up transaction log shipping (in Standby/ Read-Only mode) through the SSMS wizard, which generates jobs that then call on outside .exe ...
0
votes
0answers
22 views
How to test a master key regeneration?
Following up after a situation on an important LIVE server -
where I have the master key backed up,
but the decryption password for that master key backup file is missing.
I can't open the master ...
0
votes
0answers
20 views
Update local SQL Server database table with data from remote SQL Server database table
I have two SQL Server Databases (Version 14.0), one on AWS and other on local in house server. I want to update some of the tables from the AWS server to local server in following two ways:
Update a ...
2
votes
2answers
201 views
'Select @VAR =' and 'Set @VAR =' behaving unexpectedly
Working on a database i didn't setup myself i was digging through some stored procedures to find out what they were doing.
In here i found numerous variables being set through a SELECT @var =, I ...
13
votes
1answer
448 views
Physical only checkdb is failing, but full one is completed successfully
I am executing checkdb with physical_only option and it fails with multiple errors like below:
Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1557580587,
index ID 1, partition ID ...
0
votes
1answer
31 views
Is it possible to exclude rows from a select based on conditions of another row
My data set is quite simple;
Ref Value Voucher No
------------ ---------- ----------
M019922 -154.80 100081968
M019922A 154.80 100081968
M019922B -154.80 ...
0
votes
0answers
16 views
Recursive triggers on update MariaDB
I have two virtual servers.
On both servers, I have a table "users".
I have an update trigger on the table "users" on both servers so when one server table is updated, it should be updated on the ...
