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
0
votes
1answer
22 views

Debug stored procedure that times out from year to year

We have a stored procedure that joins a couple of tables and returns json. We've added indexes and it returned the expected results without any issues for months. Btw, here's a snapshop of the live ...
0
votes
1answer
28 views

Passing a variable in OPENJSON WITH statement in SQL Server

Below script calls an API with base and other currency as input and gets foreign exchange(FX) value from its response. While reading the response from api we have to specify '$.rates.GBP' in openjason ...
-1
votes
1answer
33 views

Find rows with a column value based on a date range and type AND excluding them based on less than 90 days prior for another value from same column

So I am finding all rows within a date range that are of the BLUE type. SELECT * FROM table WHERE date_of_service BETWEEN CAST ('2020-01-01' AS DATE) AND CAST ('2020-09-30' AS DATE) AND type = 'BLUE' ...
1
vote
1answer
20 views

Can we create SQL Server Aliases in Linux clients?

Can we create "Server Aliases" in Linux clients (webservers etc.) so that you can change connections without touching the application's DB URL string? The official documentation seems to ...
3
votes
2answers
85 views

MS SQL Server - Increase allocated memory

My problem is that SQL Server takes a long time to ramp up it's memory usage for instances with TB worth of RAM, all the while we get intermittent waits of MEMORY_ALLOCATION_EXT that slow down our ...
0
votes
1answer
40 views

Best way to encrypt some selected rows

I know how to create a new column based on an old one , encrypt it after creating a master key/certificate and etc. I would like to know what would be the best approach to encrypt ceartin columns ...
0
votes
2answers
35 views

Optimize duplicated but negated subquery

I have what I call a 'Hamlet' query in the form: select * from stuff a left join otherStuff b on (a.x=b.x) -- lots of joins ommited where (b.ToBe = 1 and a.Being in (select Being from stuffThatIs ...
1
vote
1answer
41 views

SQLServer Query Store not saving failed queries run by SP_ExecuteSQL

We are on SQL Server 2016 Query Store is activated on the database in question. We have had some timeout problems reported on a nightly batchjob, and we have located the query in question. The query ...
0
votes
0answers
37 views

Login to SQL Server cross domain AD Group user

Our MS SQL Server is on another trusted domain (Trusted Domain C). We created a Universal Group within Domain A with users from different forest domains and added the AD group onto the SQL Server. ...
1
vote
1answer
45 views

Batch Delete with checkpoint still causes log growth

I have a large table (600 million rows, 60GB data) that I need to migrate to another table. This is for a few reasons: The current table is a heap and I want to convert it to having a clustered ...
1
vote
0answers
48 views

Copying a dependency structure

I'm looking for performing a copy of a "dependency structure". What I mean is: there is a tree structure representing a product and its sub components. Each of this products may have one or ...
-3
votes
1answer
27 views

How to use substring and replace in SQL? [closed]

I have a table record and have two fields definitions namely: TotalActivity datatype DateTime on SQL query, LastActivity datatype decimal My problem is how do I calculate these on that record? I ...
-1
votes
1answer
50 views

Get records where no column is null [duplicate]

I've a table with 100+ (nullable) columns and 10,000+ rows. I'd like to fetch rows that have no columns null. id|f001|f002|f003|....f100 1 |null|aa | ss |sada 2 |sdad|aa | ss |sada 3 |as |aa | ss |...
0
votes
0answers
11 views

AlwaysOn Availability listener access issue

Good morning all , Who has any idea how to do create an SQL alias on a listen alwayson which uses a port other than 1433 thanks
2
votes
1answer
46 views

How does Automatic Seeding work - Always On Availability Group

I found only these two statements from BOL regarding this Initial Data Synchronization method: Link 1 Automatic seeding uses the log stream transport to stream the backup using VDI to the secondary ...
-3
votes
1answer
46 views

SQL Server Stored procedure performance issue [closed]

I have the complicated stored procedure which reads the data from Oracle database and writes into SQL Server tables. The procedure working time was increase more than 3 times. Number of processed ...
0
votes
2answers
75 views

PAGEIOLATCH wait and Page Life Expectancy very low

Training for SQL Server, has following scenario: SQL Server on Azure VM, DS-series machine, it has 32 GB of RAM Server has below performance conditions: - very high PAGELATCH_IO waits - average Page ...
0
votes
0answers
30 views

Replication of Table? [duplicate]

Goal: *Same database to have 2 similiar table named test1 an test2. *Table test2 is only used for query (read only) and table test1 is only used for command (delete, create and update). Background: *...
-1
votes
0answers
32 views

Microsoft SQL for Linux - Paid Edition [closed]

I've installed Microsoft SQL Server on Linux, and for fun, I chose the PAID edition. As Microsoft's web site states, it did not ask to input the license. I selected option 5 (Standard Edition). That ...
0
votes
0answers
24 views

Upload Excel data to Microsoft SQL Server [migrated]

I am trying to read excel data and insert it into a SQL Server database. I am using npm package mssql to do the same. Also using XLSX to read Excel data. However I keep getting the same error ...
0
votes
1answer
54 views

Flag Rows Occurring 2 Hours After Previous Flag

I have some messy RFID data due to over sensitive antenna's. There is a physical process that tracks an RFID tag moving through different stations in a cycle. An item with an RFID tag can move through ...
-2
votes
1answer
42 views

Create a view in MSSQL where one column in a table depends on the value of another column in the same table

How can I create a view in MSSQL where one column in a table depends on the value of another column in the same table? Example: I have 4 tables: placeType, schools, shops and hospitals and deliveries. ...
-2
votes
0answers
40 views

Simple alternative to a crud application?

I have a relational star database consisting of about 9 datasets - 700mb. When I connect it to powerbi / qlikview / tableau, I can slice and dice it until I get to a resulting table of values. Now I ...
0
votes
1answer
32 views

Timeout on CPU usage?

Is there any way, in SQL Server, to cause a batch to time out if its CPU usage exceeds some value specified in the batch? I need this to occur at the batch, and not the connection level.
0
votes
1answer
16 views

Set Trigger on ALTER_SERVER_ROLE

I've tried to add a trigger to the ALTER_SERVER_ROLE command using: CREATE OR ALTER TRIGGER XXX ON ALL SERVER FOR ALTER_SERVER_ROLE AS It creates fine but doesn't fire when using the ALTER ...
2
votes
0answers
14 views

startup ask failed for service MSSQLServerOLAPService

I can't install SSAS (SQL Server analysis services) in multidimensional mode : I get an error at the end of the installation : startup ask failed for service MSSQLServerOLAPService. I was able at the ...
0
votes
1answer
51 views

Are multiple column index used in single column where clause

I have a table with a defined non clustered index on 3 columns. CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable] ( [Col1] ASC, [Col2] ASC, [Col3] ASC ) Will it be used if i do a ...
1
vote
2answers
61 views

Why is my database still fragmented after I reorganized and rebuilt it, and updated the statistics in SQL Server?

I created a Maintenace Plan, first i reorganize the indexes, then rebuild, and final update the statistics every Saturday. I ran this script for verification: SELECT S.name as 'Schema', T....
0
votes
1answer
53 views

Decrease Data IO operation on query to lower the DTU cost on Azure

We have a FunctionApp in Azure that triggers on a timer and does some DB operations every 5 minutes. These operation are taking a very long time to complete. When I run the same query on my computer ...
-2
votes
0answers
29 views

How to setup SQL Server on VMWare for best performance [duplicate]

We have Dell r940 server with 64 CPUs. Processor Type: Intel(R) Xeon(R) Platinum 8253 CPU @ 2.20GHz 4 Sockets + 16 Cores per Socket and 128 Logical processors Memory 767 GB We set the server up on a ...
0
votes
1answer
28 views

Building a custom SQL Server 2017/2019 container on a windows server without internet

I am in bit of a predicament,and would appreciate any help to move forward from my current situation I have been given a VM running on Windows Server 2016 with Docker Enterprise installed. My ...
0
votes
1answer
30 views

Only a sysadmin can specify '@output_file_name'

I have developed an async batch system using the Agent Jobs in SQL Server 2017. It is working really well. I have one issue with setting up the operations staff to run this. We get the following error ...
0
votes
2answers
44 views

Copy on Write for SQL Server

I have 20 users that each keep a copy of a database on their local computer (technology other than MSSQL). Their copy (we call it a sandbox) is updated each morning and their previous changes are ...
0
votes
1answer
23 views

Another question about finding the last time a value changed

I know there is a simple answer to this question but just can't get my head round it. I have a table with datetime and int columns. | TimeStartTz | RowNumber | RateID | |--------------...
0
votes
2answers
47 views

Transaction log backup after full backup is too big

Our current production database performs a full backup daily at 7AM. The transaction log backup takes place every 15 minutes (24/7). Database version: Microsoft SQL Server 2014 - 12.0.4100.1 (X64) ...
2
votes
1answer
245 views

Select everything besides the Max(date) for each group

I have a table where I would like to pull everything besides the max dates for each groupID. Is there an easy way to do it? here is what i am trying to do: SELECT groupId, Types, MAX(Dates) as date ...
0
votes
1answer
52 views

log_reuse_wait_desc - AVAILABILITY_REPLICA Unable to truncate transaction log

I have just 1 secondary replica and just one database in my availability group. Both Primary and Secondary replicas are in synchronous-commit availability mode. I inserted 1000 records into the table ...
-3
votes
1answer
49 views

Is there a different (better) way to do this? [closed]

I have one parent table(p) with primary key (id) and two child tables (c1, c2) with foreign keys (pid), i want to return all the records including no matches where id = 1. SELECT p.id, c1.data1, c2....
0
votes
0answers
11 views

Powershell to fetch MDW reports

We have some SQL Servers where MDW is configured. we need to pull MDW reports every week and send it to customer. Is there any way we can automate it using powershell..?, i googled it and i didn't get ...
0
votes
1answer
51 views

Analyse differential backup to see why it is so big

We quickly have very big diff files. Although the database is a highly transactional database, there is also a lot of data at rest. 1 or 2 days after a full backup was taken, the diff backup is bigger ...
0
votes
1answer
24 views

Join to get most specific record for tenant without PIVOT

I have a Settings table and a Tenant table. There is a hierarchy for that an Account can have 1 or many Companies and a Company can have 1 or many `Facilities'. Account 1 ---> Company 1 ...
0
votes
0answers
18 views

Issue with Scripting out Subscriptions using Powershell

I am trying to script out the replication objects via Powershell using Microsoft.SqlServer.Rmo.dll. The replication type is transactional with push subscriptions. I have been able to script out ...
1
vote
0answers
54 views

Converting time zones

My SQL Server is located in US Mountain Standard Time where the Daylight Savings is not observed. My office is in US Central time zone. All schedules are kept in Central time by business. I am trying ...
0
votes
2answers
38 views

database design storing historical information

I have this database table Templates and I need to keep historical information. So in code what I am doing is inserting a new row with the updated data and then setting the IsCurrentVersion flag to ...
0
votes
1answer
48 views

migrating to a bigger server - how to copy over differences

We have a very large database, one table (logs) contains the majority of the data (around 13 billion rows) total database size is approx 6.5TB and I have 900gb of space remaining before server is full....
6
votes
1answer
140 views

How to return several arrays of doubles as a table in C# SQLCLR function

What I have is a CLR SqlFunction that produces 3 arrays of doubles. I would like this function to return something appropriate so that the FillRowMethod can output it for me as a table in T-SQL. It ...
4
votes
2answers
411 views

Multiple volumes vs single volumes for SQL Server CHECKDB performance

We have a ~10TB database with dozens of data files on SQL Server 2017 Enterprise CU21. We're seeing slower CHECKDB performance when all of the files are on a single volume as opposed to multiple ...
0
votes
1answer
23 views

Combine two child tables based on parent

Suppose I have 3 tables parent, child1 and child2. The parent table may look as follows: +--------+-----------+-----------+ | Column | Child1_Id | Child2_Id | +--------+-----------+-----------+ | ...
0
votes
1answer
64 views

Turning “Set Statistics IO” for stored procedure doesn't show all tables used in stored procedure - why?

I'm reviewing a stored procedure that's been a performance problem. And I just realized that some of the tables which are used in the stored procedure don't even show up AT ALL in the results from Set ...
1
vote
1answer
59 views

Why does my execution plan show a Clustered Index Scan is occurring when using an index hint that specifically uses a Nonclustered Index?

The Backstory: I have a table with the following structure: CREATE TABLE WideTable1 ( BoringColumn1, BoringColumn2, CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see ...