Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.
-1
votes
2answers
11 views
Can a non-clustered index be transformed to a covering index in SQL Server?
I have a non-clustered index which includes one column. Now I want to alter the index and add additional two columns as covering columns. Is it possible? If yes, then can you, please, provide the T-...
0
votes
1answer
13 views
How to access objects created by a different user
User A created few objects with schema "UserAPriv", but now he is either leaving the company or changing the department.
Now we need to restrict his access to his previously created objects and make ...
0
votes
2answers
12 views
Does MSSQL Log shipping truncate the transaction log?
I have setup log shipping on a database in Microsoft SQL Server 2012.
I was having issues with my secondary server and I found that this was probably due to a scheduled backup job messing up the log ...
0
votes
2answers
15 views
How to connect Microsoft SQL Database with Active Directory Password Authentication (AD Login)
I have web application and I wanted to use Active Directory Password Authentication for Database login.
I already setup user login for Active Directory, but it show the error like below.
Help me! ...
0
votes
0answers
14 views
SQL Server Allways On Availability Groups - HA & DR
I am trying to build an architecture where we get automatic fail-over for both HA and DR for our DBs using 2016 AlwaysOn Availibilty Groups. When I tested this using only one Availability Group the ...
2
votes
1answer
17 views
database_xml_deadlock_report or xml_deadlock_report
Is [database_xml_deadlock_report] the same as [xml_deadlock_report] just with extra fields?
I'm creating a basic deadlock and blocking capture and see both. Most examples online show [...
1
vote
1answer
20 views
SQL Server - How to copy database daily
I have 2 separate SQL Server instances, for the purpose of the question, we will call them Source and Target.
I have an ever changing database on Source called MyDatabase, this database is populated ...
0
votes
2answers
25 views
How to aggregate the number of indexes on a table and columns per index
I have a simple query that returns every table, index and the columns of the index in a database. I'd like to add two aggregate columns that return the number of indexes on a table and then the amount ...
0
votes
0answers
19 views
What does “_localserver_view” mean after an MSDB Database table
What is equal to the database table
[msdb]. [dbo]. [sysschedules_localserver_view], which contains the suffix _localserver_view?
I am having a permissions problem with a [msdb].[Dbo].[...
2
votes
1answer
15 views
Can I control which algorithm SQL Server uses to hash symmetric key passphrases
I will start by describing the issue / cause before asking my question:
If you are using a symmetric key in SQL to encrypt data.
This can be set up as follows:
CREATE MASTER KEY ENCRYPTION BY ...
-1
votes
1answer
27 views
SQL Server 2012 Enforce Password Policy
If i would like to change the password of my SQL login,
How much time i can't use with the same password?
0
votes
0answers
34 views
CPU and Hard Drive Spike for 20-30 Minutes
I'm collecting data on the performance of our Microsoft SQL 2008 server in preparation for moving it to Azure. It's a very low load server (currently max concurrent transactions max out at 3), ...
1
vote
0answers
36 views
Correct way to group by month and year?
I need to group by month (and year) and I was thinking about:
GROUP BY CAST(YEAR(tDate) AS NVARCHAR(4)) + '-' + CAST(MONTH(tDate) AS NVARCHAR(2))
But I found in the web something like:
GROUP BY ...
0
votes
0answers
8 views
Read Excel Files wich are matching to the given template Through SSIS
Hope you are doing well .
I have developed a SSIS Package with which I'm reading different Excel Files with different structures and Inserting it into different tables .
I have a Table in SQL-Server ...
0
votes
0answers
39 views
How can I stop a long running query in SQL Azure Database
I triggered a very inefficient query against a Azure SQL Server instance that has been running now for hours. I have tried looking at the active spid and trying to kill the process that started it but ...
3
votes
1answer
158 views
Unique filtered index and surprising estimation
When I view the estimated execution plan for this query...
SELECT *
FROM tabela2 x
LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod
... the Estimated Number of Rows property for [table1]...
0
votes
2answers
42 views
Select fields from sub query , but I face field could not be bounded
SELECT
ROW_NUMBER() OVER(ORDER BY c.FullName ASC) AS No#,
a.AcctBranchName,
c.FullName,
c.BirthDate,
c.Age,
a.StartDate as 'Account Start Date',
c.CustStartDate as '...
0
votes
1answer
38 views
Partition SQL Server Table by Year, 35 days rolling period
We are trying to partition SQL Server tables by year (2015, 2016, 2017, 2018) and we want to create one more partition which hold current 35 days period data always.
Partitions: Part_2015, Part_2016, ...
0
votes
0answers
16 views
SQL Server Log Reader Agent while trying to add node in p2p Replication
I am trying to add one more node in peer-to-peer cluster.
In current state, I have 3 servers A, B and C.
I need to add a node D with node A(only).
For this, I used following steps:
Took full backup ...
0
votes
1answer
34 views
Join to a Same Combination of Tables
I want to join a first table to same combination of joins: X,Y,Z. The first table can be different table types, but they all join on same guid column. Table A may have 8 columns of different data ...
0
votes
1answer
30 views
Full name as a Primary Key (PK) is my preferred choice; is there another way?
I'm new to DB management and I'm trying to create an ER Schema Diagram to best understand how to build my database for a project.
My project is to list every speaker (i.e. an individual person) that ...
-1
votes
0answers
20 views
MS SQL Always On with Zero Downtime and Automatic failover ( DC-DR Scenario) [on hold]
The requirement here is, we need to achieve MS SQL automatic failover between DC (Mumbai) & DR (Chennai) without any manual intervene.
Please find below detailed architecture:
1. I am using MS ...
0
votes
0answers
24 views
Dynamically Updating Multiple Columns - SQL Server Management Studio [on hold]
I would like to pass a statement like the below into SQL Server Management Studio (through Pyodbc in Python):
UPDATE tab SET (column, ...) = (val, ...)
which I found from this forum. However, this ...
1
vote
1answer
33 views
Indexing for a table with many large subsets
In SqlServer 2017 we have a table with millions of rows. The table has a few dozen columns. One of the columns is a varchar(50) with a well defined range of allowed values, the value of this column is ...
0
votes
1answer
27 views
Inserting into table depending on conditions [on hold]
I'm trying to insert data into table but depending on the content it is supposed to be done different ways. My question is whether there exists such function that will work like follows:
WHEN ...
-1
votes
0answers
13 views
SQL Log File to Table Insert/Update
I have a emp table which contains three columns. Lets say EmpNo, EmpName and Age. Empno is a primary key column. I am getting data from logs or text file. If there is no particular Empno then row ...
2
votes
1answer
48 views
Deadlock with insert and delete
Deadlock graph:
<deadlock>
<victim-list>
<victimProcess id="process21881f1bc28" />
</victim-list>
<process-list>
<process id="process21881f1bc28" ...
1
vote
2answers
39 views
SQL Server Error Log - Is It / Can It Be Encrypted?
Is it possible for the SQL Server Error Logs to be encrypted?
Our SQL Server recently had a memory dump which it threw into the Error Log. While the dump was definitely useful to identify the issue,...
1
vote
2answers
72 views
Workaround to implement Minimum Degree of Parallelism
Please correct me if I am mistaken but as I understand it, when a query is being scheduled for execution, the engine takes into account the number of available free threads and will adjust the query ...
0
votes
1answer
28 views
Always on basic question
I've tried to find this on Google but no love. Usually that means it's so basic everyone else knows it. But I would like an answer for my own peace of mind.
If I make a change to one server in an ...
0
votes
3answers
64 views
How to export SQL Server database as single .sql query file [on hold]
How to export a SQL Server database into single .sql file, so that I can execute the query any system and SSMS, without carrying the .mdf file
1
vote
0answers
8 views
SQL Server in Azure VM + Azure AD + SSIS - how to authenticate?
Our configuration:
Windows Server 2016 VM in Azure, running SQL Server (same as on-prem version)
Azure Active Directory with users etc.
Local Windows 10 PCs are joined to the AAD domain
SQL Server ...
0
votes
1answer
39 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 ...
0
votes
1answer
37 views
Create Copy of all rows where foreign key matches specified and then replace foreign key
I'm new to database administration (this is my first time) and have been tasked with creating a database with functionality that allows users to select a "Model" as a current state which can then be ...
-1
votes
0answers
13 views
sql server 2008 r2 differential backup [duplicate]
Can sql server take daily incremental differential backup based on previous day? For example,
day 1: full backup.
day 2: differential from day 1
day 3: differential from day 2
day 4:...
-1
votes
1answer
23 views
Time difference between 2 consecutive lines [duplicate]
I am using SQL Server v18.0
I have the folowing table:
The table represents the time use for the test equipment.
The character C_ represents connect operation and the D_ represents disconnect ...
0
votes
2answers
39 views
How to choose which backup files to restore when identical timestamps exist for diff and log backups
I can see from an earlier question that log file backup may be performed simultaneously with either full or differential data file backups.
Imagine for a moment that full backups run during a window ...
0
votes
1answer
46 views
Changing SQL Server (MSSQLSERVER) Service Account
I am sorry if this has already been answered in the past.
We have SQL Server 2016 which is our Dynamics NAV Database. Currently all backups are stored on local drive of the server. I have been given ...
0
votes
1answer
19 views
Duplicate Ips for cluster nodes causing backup issues
We have a four node cluster (SQL Server 2014 on Windows 12) and we recently setup heart beat network on the nodes following a best practices source after experiencing some cluster instability issues. ...
0
votes
1answer
61 views
How to make a trigger for create_view [on hold]
I need to make a trigger about who and when created a view. I have some groundwork about it.. So, when some user is running a script for create view, trigger is crushing because this user doesn't have ...
6
votes
2answers
144 views
Why does changing the sort order return a different number of results? Is it a bug?
I have a query that contains COALESCE in the select list. If I change the order by clause of the query I get a different number of results.
-- 225 rows - with order by desc
select coalesce(tdo....
1
vote
1answer
53 views
SQL Server Not Consuming all Memory - Insufficient System Memory in Resource Pool 'internal'
I am running SQL Server Standard 2014 with SP3. I am noticing a lot of activity on the log viewer around insufficient system memory in resource pool and memory Broker.
I have searched on this error ...
1
vote
1answer
27 views
Matching dates with conditions and filtering in a table
So I have a tables like this
id | date1 |date2 |value
1 | 1/1/2014|1/1/2004|100
1 | 1/1/2014|1/1/2004|100
1 | 1/1/2014|1/1/2009|200
1 | 1/1/2014|1/1/2009|200
1 | 1/1/2014|1/1/...
-3
votes
2answers
36 views
how can update part of the value in SQL
i have this sample:
i wanted to change the value of '&' into 'C:\' having trouble composing a script.
0
votes
1answer
26 views
Prevent duplicate rows with many columns
Here is the shortened version of my table:
CREATE TABLE [dbo].[Ability_Main](
[Ability_ID] [smallint] NOT NULL,
[Ability_Text] [nvarchar](150) NOT NULL,
[Flag_Conditional] [bit] NOT ...
0
votes
1answer
51 views
SQL Server service start automatically only after Windows Shutdown, how can I make restart automatically without batch file? [duplicate]
I am using MS SQL Server 2014, When I restart my Pc the SQL Server Service does not start automatically, however, it does after Windows Shutdown and run again, how can I make it restart automatically ?...
0
votes
0answers
35 views
How to Create a Database from File Names and Details [on hold]
I've downloaded a bunch of files (45,000+) from a collection of Go/Baduk/Weiqi games and would like to create a SQL DB for them, using their file names, dates, description, and, if possible (I'll ...
-2
votes
1answer
36 views
Looking for help with this shipping report! The Case Syntax is throwing me for a loop
So, the goal of this report is to get all orders that have been shipped in a month (October here). I was initially just using the 'PickedUpOn' field but soon found that there were instances where this ...
-1
votes
1answer
44 views
How do I access a stored procedure on a different database on the same server
Here is my code
ALTER PROCEDURE [admin].[UpdateMiddleSchoolTranscriptsGPA_weight]
AS
BEGIN
SET NOCOUNT ON;
--EXEC webapp.sa.[MMSD].[UpdateMiddleSchoolTranscriptsGPA_weight]
EXEC MMSD....
0
votes
1answer
46 views
powerful VPS but slow MS SQL- why? [on hold]
VPS (Contabo) : 10 cores (Xeon E5 2.2GHz), 60 GB RAM, SSD, MS SQL Web Edition (configured to use 10 CPUs), Windows Server 2016
When running SQL takes ~32GB of RAM,
When I execute SQL a stored ...
