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
-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
56 views

SQL TempDB swells to unmanageable size [closed]

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
33 views

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

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
33 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
24 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
33 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
47 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
89 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
33 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
32 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
23 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
56 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
70 views

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

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
271 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
26 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
78 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
93 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
68 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
31 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
29 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
83 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
63 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
50 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
45 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
816 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
39 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
200 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
28 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
26 views

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

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
118 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 ...
3
votes
2answers
199 views

Backup files are lost while backing up to Azure URL from SQL Server 2016

I have a job script like this which should backup SQL Server 2016 dbs to Azure blob storage with storage key credential. Usually it works fine, but sometimes a few backups are lost (I mean there is no ...
2
votes
1answer
59 views

How to find last processed time of dimension in SSAS

I have a list of around 100+ dimensions. Is there any way to check the last processing time of each dimension using DMV's in SSAS?. Thanks in advance
0
votes
0answers
74 views

How to connect to SQL managed instance on private endpoint on different VNET

I can't connect to SQL managed instance from Azure VM in different VNET. VM network VNET address space:10.0.0.0/16 VM subnet: 10.0.1.0/24 SQL instance VNET address space:10.0.0.0/16 SQL instance ...
-2
votes
1answer
50 views

Count rows from multiple tables with similar names

I want to have 2 columns: Table Name and Row Count When I run this, I get 12 table names in my database. How do I then count the rows in each one without having to union 12 count queries together: ...
1
vote
0answers
75 views

SQL Server : sp_spaceused unallocated space

I checked SQL Server database size by sp_spaceused. Even though there seems to be enough unallocated space, some database issued the file expanding error. -- sp_spaceused result use db_01; EXEC ...
1
vote
0answers
85 views

SQL Server 2016 SP2, unable to shrink database, even after emptying primary file

We are running SQL Server 2016 SP2 CU15. For years we have set up test environments by pulling down a production backup and removing a lot of the data, then shrinking the database. For some reason ...
0
votes
0answers
30 views

Error when replicating to Azure SQL Database

I've configured transactional replication between my on-prem SQL 2017 instance and an Azure SQL Database. The publisher, distributor, subscriber have all been set up. The snapshot agent has generated ...
1
vote
1answer
64 views

Having the same primary key as the parent table in one to one relationship

Building two tables as one-to-one relationship, I would like to consider which one is a better design ? Having the same primary key for the parent table (person) as a primary key also for the child ...
1
vote
1answer
68 views

How does SQL Server decide which trn file to restore?

I want to restore my database using the backup files in the backup folder. The SQL Server is running on Windows. When I want to restore I do right-click on the databases and choose "Restore",...
0
votes
1answer
31 views

Table partitioning on an existing table and using different filegroups

I have an existing table on the primary filegroup, which I want to partition. The partition key is on year, which is a calculated column. I want to partition the table in such a way that in the end ...