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
6
votes
1answer
473 views

SQL Server table query with pagination performance tuning, understand the current solution

as stated in the title i start a performance tuning of a table query with pagination generated by a legacy program that use Linq To SQL as ORM. I have found this resource in which is highly ...
0
votes
1answer
45 views

SQL Server SSMS - Edit 200 rows functionality - is it possible for two or more people to alter each other's work using this functionality?

Let's consider a scenario: Two or more people are working on a table that stores configuration data for an app. There are multiple configurations in that table, so each person can work on it's own ...
0
votes
1answer
77 views

SQL Server RAM usage - how to find out where it is going?

Short Version SQL Server is using 34 GB of RAM. But when query the Memory Consumption Report, the buffer pool size, and the ad-hoc query size, it only adds up to around 2 GB. What is the other 32 GB ...
0
votes
1answer
36 views

Deleting old snapshots SQL

This may be a silly or dumb question but... We just changed the snapshot path and I was wondering if the cleanup job also clears up the old snapshots from the previous folder path or is it something ...
-2
votes
0answers
39 views

SSRS in Availability Group does not fail over correctly

I am trying to get my SSRS reports to work with an Availability Group setup. Currently it works on Node 1, but any time there is a failover to Node 2, the reports stop working until I fail back to ...
5
votes
2answers
181 views

Computing the set difference of tables of intervals

I often run into the following problem. I have two tables of intervals. They are bounded by dates (with no time component). Within each table the intervals do not overlap. StartTs EndTs 2015-01-03 ...
0
votes
1answer
71 views

Restore SQL Server incremental backup with full backup copy only

I need to restore an incremental backup of SQL server but when I tried it, it gave me an error and I realized that the full copy has been done in copy-only mode for more than a year and now it does ...
1
vote
1answer
17 views

How to create a SQL Server linked Server with Local SQL Server Authentication User to a remote Windows Authentication

I need to create a Linked Server from Server A that uses a SQL Server Authentication to a Server B that uses a Windows Authentication. Is the "impersonation" possible? If "YES" ...
0
votes
1answer
25 views

SSIS detecting sequential duplicates

SSIS/Sql Server/Visual Studio 2019. I've got a big csv file that has a lot of iffy data. One thing I wanted to do was at least try to recognize sequential lines that are the same and kick the ...
4
votes
2answers
187 views

How to retrieve the maximum value and its corresponding date in a table

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value. Example Data CREATE TABLE ...
-3
votes
0answers
36 views

SQL Server failed install [closed]

It's a developer mode basic install Unable to install SQL server Exit code (Decimal): -2147467259Error description: The service did not respond to the start or control request in a timely fashion. [...
-2
votes
1answer
55 views

SQL TempDB swells to unmanageable size

We're using SQL server 2014 SP4. Out of several stored procedures, there's a particular one that creates about 30 very large temp tables (wasn't created by myself). The process is run via daily SQL ...
-1
votes
1answer
32 views

database transaction log file is not growing up after database mirroring with High-Performance Mode

i made a mirror (High-Performance Mode) for sql database, after few days i noticed that the transaction log file(ldf) of database is not growing up. i did not know way. can anyone explain why that is ...
0
votes
0answers
42 views

SQL Filtering on multiple properties

I have a Database with a table Books: Id IsVisible Slug UserId Name CreationDate Description IsAdult BirthDate City Siren 1 1 julien 56D1C3BE-7674-4CF0-6940-08D9D09EC5A8 Julien 2022-01-05 22:57:39....
0
votes
0answers
31 views

SQL Update with sum, inner join, and group condition

I'm trying to update with some condition: UPDATE a SET a.FLEX_ERROR = CASE WHEN ((a.NUM = 1 OR A.CODE = 'D') AND SUM(a.POINT) <> b.TOTAL_VALUE) ...
0
votes
0answers
18 views

SSIS convert string with no 0's to date format

I'm working trying to convert a string data type to a date that does not contain 0's in the string of the month and day part and need to convert it into a date format yyyy-MM-dd, anything that is a ...
0
votes
3answers
29 views

Unable to grant membership to public role

I'm trying to refresh my dev db with a snapshot from prod. I've restored the production db from the prod server to my dev server just named as development. I'm trying to grant access to the dev db to ...
0
votes
1answer
24 views

Giving read access to all tables for an user and giving write acess to all tables for another user in MSSQL

Just a question. I created a login through the wizard and then associated an user with the login for a database; however, I have several database, do I have to create an user for all of them manually? ...
0
votes
1answer
46 views

How can I improve the run time of a query that groups / display same date data in one row?

I currently have a view that contains about 1k rows of user information. currently i have a query that groups all the same user information by date into one row. Currently this query takes between 20 ...
1
vote
0answers
85 views

Snapshot isolation transaction aborted due to update conflict

This question has been moved here and been revised as more logging has been added. This is the structure of the SQL database: a) Table called tblPPObject which has a bigint column called InsertionID ...
0
votes
0answers
42 views

Is it possible to run a query for EVERY DDL change that occurs in a server?

I want to run a query on each and every database that is change in whatsoever DDL change possible. For example, if I have 10 databases, A, and B, and ... in my SQL Server instance, and if a developer ...
0
votes
0answers
32 views

Linked server to Postgresql

I'm trying to create linked server in MS SQL 2019 to Postresql that is located on another machine. I've followed the below instruction, succesfully connected to Postgresql through odbc as well as ...
-1
votes
1answer
28 views

Connect to Remote SQL Server using powershell or cmd

I want to connect to Remote Sql server using powershell with username and prompt for password and when connected i need to to run a query to retrieve data from the sql server and display as output ...
0
votes
0answers
22 views

How to clean up old publications on the distributor?

When I use the following script on the distributor, it shows me current and a few old publications that no longer exist. I've gone to the publication server and run EXEC sp_droppublication @...
-2
votes
0answers
54 views

I'm getting error when I try and restore database but I'm running a newer version can anyone explain? [duplicate]

Restoring database I recieve the following error. It seems I'm running a newer version but I'm still getting error. Please explain... TITLE: Microsoft SQL Server Management Studio Restore of ...
-1
votes
2answers
149 views

WHEN run query on SQL Server, it is very slow. How to solve execution plan issues?

I work with SQL Server and face an issue that a query is very slow. INSERT INTO [ExtractReports].[dbo].[TradeCodesDelete] (PartID, Code, ...
-1
votes
1answer
67 views

Fresh SQL Server 2019 install, a simple select takes a minute to run each time

We recently performed a fresh install of SQL Server 2019 on an existing EC2 in AWS, once that we used for our last installation of SQL Server 2019. We then did a restore of some databases via backups (...
3
votes
2answers
267 views

How can Temp Table creation time be reduced in terms of CPU time?

Inside a frequently executed stored procedure (sometimes up to 10-15 times per second), there are two particular statements that show up in Top 10 heaviest queries by cumulative CPU impact These are ...
-3
votes
0answers
25 views

Alter Datatype for partitioned column

Have a large table which is partitioned on a datetime column. Would like to change this column to a date datatype. Can this be done? Edit - the partitioned datetime column is the clustered key.
-2
votes
1answer
74 views

Named parameters in queries for SQL Server

ALL, I'm trying to find a proper syntax to do the following: SELECT a, b, c FROM foo WHERE foo.id = :id; (the query above is written as in SQLite). The "normal" query would be written as: ...
3
votes
2answers
87 views

Extended Events, sp_reset_connection and sql_text

I have extended event sessions running on 3 different servers. I am looking for long running or excessively called stored procedures and in-line queries (ORMs, primarily nHibernate). The output for ...
0
votes
1answer
62 views

DatabaseBackup backs up the same database twice?

I use the Ola Hallengren DatabaseBackup procedure to run backups. I have two jobs configured so the backups can run parallel as I have one large database that takes 45 minutes to back up and many ...
0
votes
1answer
30 views

Order of execution in Ola Index maintenance and Stats collection

I was thinking of performing The index maintenance and stats collection jobs in the below order for all databases in an Instance. step 1.Perform all index maintenance activity for all required Indexes(...
0
votes
1answer
28 views

Scheduling Next Run date for weekly agent jobs

On SQL Server I used maintenance plan wizard to create three jobs: Job Schedule Job 1 Every 3 week on Sunday Job 2 Every 3 week on Sunday Job 3 Every 3 week on Sunday I want the three jobs to run ...
1
vote
2answers
79 views

Copy many large tables to another database

What I have to do I need to copy the data of a lot of tables (over 500) from a production database to a pre-production database, to better test our systems before deployment. A lot of those tables ...
0
votes
1answer
52 views

Invoke-DbaQuery not working on new server, Cannot find an overload for "SqlCommand"

I've ran the install script both without and with the -Force parameter from the Getting Started doc on github Install-Module dbatools -Scope CurrentUser Install-Module dbatools -Scope CurrentUser -...
1
vote
1answer
61 views

json_modify on condition

As in my previous question, I am working on migrating my XML based procedures to JSON ones. My newest struggle is with json_modify I'm used to using .modify with XPATH to be able to modify XMLs on the ...
0
votes
1answer
49 views

SQL Server on Linux caps the memorylimitmb setting

I have a 64GiB Ubuntu machine with SQL Server 2017 installed. Setting memory.memorylimitmb to anything higher than 63695 megabytes is ignored. Setting it to 67000 megabytes (which is 62.3 GiB roughly) ...
1
vote
2answers
44 views

Invalid Replication Server Name (IP Address) SQL Server 2012

I have a server in production (SQL Server 2012) that someone tried to set up replication (distributor), but they used an IP address instead of the name. I can see that the installation must have ...
6
votes
2answers
810 views

How does use of sp_executesql with parameters protect against SQL injection?

The following is a dynamic filtering solution that uses sp_executesql IF OBJECT_ID(N'dbo.GetOrders', N'P') IS NOT NULL DROP PROC dbo.GetOrders; GO CREATE PROC dbo.GetOrders @orderid AS INT = NULL, @...
0
votes
0answers
36 views

CDC Error Log Scan process failed in processing log records

I have an error in my CDC in SQL Server 2017: error_Number: 22859 error_severity: 16 error_Message: Log Scan process failed in processing log records. Refer to previous errors in the current session ...
4
votes
2answers
196 views

Suppressing SQL Server Management Studio's 'Messages' from output of SQLCMD via SQL Server Agent

I've created a script that outputs data in an XML format, and was using SQL Server Agent (Job Step of Type 'Operating System (cmdexec)') to run a job monthly/quarterly to output the data to a specific ...
0
votes
0answers
57 views

Weird utf-8 encoded column issue

We're looking to upgrade our old sql servers so I got a test db on 2019 and started playing with some of the new features. I loaded up some tables with SSIS packages from a csv file, then I changed ...
0
votes
1answer
48 views

MSSQL. Is it possible to execute bulk MERGE ignoring failures?

Suppose we have a simple relationship between two tables (Products and Categories) And they contain following data: And I want to do a SQL MERGE operation on the dbo.Products table from a temporary ...
0
votes
1answer
27 views

understanding Azure Sql Databse Billed VCores

trying to migrate from sql server on-prem to azure sql database and i cant understand Duration and CPU Used and Memory Used of Billed vCores
0
votes
1answer
61 views

Can not enable lock page in memory in SQL Server Express edition

For enabling Lock Page In Memory, I tried to do this: first of all I find my SQL Server account name with this code: SELECT service_account FROM sys.dm_server_services This code returns: NT Service\...
0
votes
0answers
25 views

Getting "User does not have permission to perform this action" error in sp_execute_remote execution

I have created the sample SP and External Data Source for My Database. CREATE OR ALTER PROCEDURE [dbo].[sp_DB1] @param1 VARCHAR(16),@outparam INT OUTPUT AS BEGIN SELECT @outparam=0 ...
0
votes
0answers
36 views

MS SQL: Out of memory happened while accessing a critical resource

How do I go about resolving "Out of memory happened while accessing a critical resource" errors? I have sufficient RAM (1.82TB) on a VMWare Win 2016 Std; MS SQL 2019 CU12 Ent 32 cores with ...
7
votes
2answers
115 views

Intellisense automatically disabling with "WITH Inline = OFF"?

In my SSMS (I'm using 18.10) project, I've been frustrated by the fact that Intellisense is not functioning in my files. I had conjectured that it was due to the size of the codebase, but in fact, I ...
10
votes
2answers
2k views

SQL Server is using a lot more RAM than it should

I have a SQL Server 2012 instance using more RAM that it should. The SQL Server process is using about 22.5GB RAM: The instance is configured to use a maximum of 10GB: Which is way more then ...