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.
0
votes
0answers
6 views
Dynamically Updating Multiple Columns - SQL Server Management Studio
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
15 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
0answers
14 views
Inserting into table depending on conditions
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
11 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
35 views
Deadlock with insert and delete
Deadlock graph:
<deadlock>
<victim-list>
<victimProcess id="process21881f1bc28" />
</victim-list>
<process-list>
<process id="process21881f1bc28" ...
1
vote
2answers
29 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,...
0
votes
2answers
39 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
24 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
1answer
38 views
How to export SQL Server database as single .sql query file
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
0
votes
0answers
6 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
34 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
31 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
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
34 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
42 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
18 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
56 views
How to make a trigger for create_view
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
129 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
51 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
35 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
24 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
48 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
35 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
45 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 ...
0
votes
1answer
32 views
Setting up Data Level Application Alerts in SQL Server [on hold]
Solutions are available for providing alerts when any job fails in SQL Server.
Is there any solution available for setting up data level alerts - for e.g.
if data hasn't updated in USER tables for ...
-1
votes
2answers
69 views
Data Warehouse: Auto Generate Slowly Changing Dimension Code For Any Table with Dynamic Sql
Is there any code to take OLTP/Stage data and transform it into Data Warehouse Kimball Slowly Changing Dimension Type 2? The code should be dynamic, and should be able to utilize any table as input.
...
-1
votes
0answers
36 views
Data Warehouse and Staging Database on Same Server instance [on hold]
What are the benefits/negatives of having Staging and Data Warehouse on the same server instance vs different server instance?
1) OLTP Source ---> 2) StagingDB --> 3) Data Warehouse
I understand ...
5
votes
1answer
54 views
RCSI and index maintenance
I have a database that is RCSI enabled and I've been rebuilding my indexes online (SQL 2014 Enterprise) for as long as I can remember. My understanding is that if we were to rebuild the indexes ...
-1
votes
1answer
30 views
intermittent High Volume of I/O Reads for a SP [on hold]
On my production SQL Server, there are incidents of 95-100% CPU usage almost everyday (1-2 times in a day), lasting for 5-10 mins. Although it recovers itself and no intervention is required. It is ...
0
votes
1answer
42 views
SQL Trigger not respecting where clause
I have a trigger:
CREATE TRIGGER [dbo].[UpdateTbl]
ON [dbo].[TableB]
AFTER INSERT
AS
BEGIN
DECLARE @role varchar(20)
select @role = u.[role] from inserted i join users u on ...
1
vote
0answers
40 views
User permission businness logic
This question will also be posted in the stackoverflow, since dba and programmers may have different opinions.
First of all sorry for the long question, but I wrote some extra code so that I can ...
0
votes
2answers
47 views
Compile Dynamic Sql before Running
I have a table, and want to test if dynamic sql will compile.
Example:
create Table dbo.Product
(
ProductId int,
ProductName varchar(255)
)
This will succeed: Column Name exists
declare ...
0
votes
2answers
43 views
First and Last of Month Calculations
This is probably a rudimentary question for most, but for some reason I'm not getting it right. I currently use two specific SET formulas for "1st day of last month" and "Last day of last month"
1st ...
0
votes
0answers
24 views
Add more partitions to partitioned table [duplicate]
How would I create more partitions to an existing partitioned table? At the moment, there's partitions set for every day for the remainder of 2018, and now I need to add more partitions for everyday ...
1
vote
1answer
37 views
Cluster IP's under Access Point for Administering the Cluster
When adding the cluster IPs under Access Point for Administering the Cluster, do we only include IPs for the public subnets, or do we also need to include an IP for the private subnet(s)? This ...
0
votes
1answer
33 views
SQL AlwaysON. keeping Agent Job in sync, but with same JobID?
Is there a way to specify Job ID when creating a job? Or is there a way to update the Job ID of a specific Agent Job that already exists?
I have a software from a vendor that uses the Job ID to ...
0
votes
0answers
36 views
Open transaction preventing log reuse?
I have a SQL servers in an availability group, set as synchronous commit.
Due to this setup, log backups only reclaim log space when a backup of the secondary has been processed.
I noticed my log ...
7
votes
2answers
105 views
Accidental DBA: Unsure why I have a broken log chain?
First of all, thanks for having a look at this - We've got a large database in our production environment (1.26 TB), it's got a few hundred corrupt pages in it and has done for months, so the same ...
1
vote
0answers
23 views
What are the requirements for SQL Replication on Azure SQL DB Managed Instance?
We are seeing if it is feasible to setup Azure SQL DB Managed Instance (SQL MI) and an Azure VM with SSMS installed for this replication scenario:
We have a vendor who needs access to our SQL Server ...
0
votes
2answers
16 views
SQL Server Agent - Do not show job step details and column headers in output file
I have a SQL Server Agent job which runs a select statement on a schedule and appends results to a CSV file
(Right click the job --> properties --> advanced and then adding a file path to the output ...
1
vote
2answers
52 views
Creating databases - no access to folder - MS SQL
I am merging a few instances together which are for separate customers. They need to be separated by account access so customer X can't view customer Y's databases.
I have created a login (we'll ...
4
votes
1answer
62 views
TLS 1.2 breaks SQL Server linked servers
As part of a recent security blitz we set up TLS 1.2 on all of our servers. Last night I flipped the "Force Encryption" flag on all of our SQL Servers and since then I've had a weird issue with linked ...
1
vote
2answers
46 views
How can I add “in” keyword in this code to accept more than one value in gender and age parameters using SQL Server 2012
I want the parameters in Age and Gender to accept more than one value
Create Procedure [Admission].[SelectPlayerData]
@MembershipNumber varchar(20),
@PlayerName varchar(70),
@PlayerNameEnglish ...
-1
votes
0answers
24 views
In Place migration from SQL server 2014 to SQL Server 2016 [on hold]
please suggest how to proceed in place migrtion from sql server 2014 to sql server 2016 in active passive cluster.
what are steps needs to taken
-1
votes
2answers
44 views
What permissions should be added to public users sql [on hold]
I have a SQL Server and am looking at making it public.
What I mean by this is: I can create a DB and give access to myself and friends etc.
I need to create a login which can only view databases it ...
0
votes
2answers
36 views
Copying all columns of a row from table into another?
I would like to keep the history of a table using SQL queries without triggers. The simplest way to do that is to fire a SELECT and INSERT into the history table like below:
INSERT INTO history (...
