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
1 vote
1 answer
26 views

Adding a column to a table then updating the table

I have a code that looks something like this declare @query = ' Alter Table Temp Add NewColumn int update Temp set NewColumn = 100' exec (@query) It will give error NewColumn does not ...
user avatar
1 vote
2 answers
32 views

What queries should you run to debug what are the stored procedures that caused a deadlock and on what line I should look at on MSSQL?

What queries should you run to debug what are the stored procedures that caused a deadlock and on what line I should look at on MSSQL? I am trying to fix a recurring deadlock on MSSQL. The issue is I ...
user avatar
  • 11
0 votes
0 answers
22 views

Database Mail failing when using a view in the query

I am trying to setup a job to run a query and send the results as an email. When I query the tables it works and sends the email. However when I query a view it fails. I've been searching the web and ...
user avatar
3 votes
2 answers
314 views

In SQL Server / Azure SQL is there a way to see the locks a query produced or wants to produce?

I'm trying to investigate a lock situation. I know how to see the current locks on DB by querying sys.dm_tran_locks but what I want is to take a specific SQL Query and having a kind of 'analysis' on ...
user avatar
  • 147
0 votes
0 answers
43 views

Database Mail failure [closed]

I have an SQL Server 2019 on Windows Server 2019 (on prem) fully updated. I am trying to use the SQL Mail feature but it is not working. ( mail failure ) Outlook works normally on the server if i test ...
user avatar
0 votes
0 answers
17 views

no permission createobject

I have a script on sql that uses a vbscript to create an excel file It was working well on a server 2008R2 and sql 2008r2 with excel 2007 but now we upgraded to server 2019 and sql 2019 with excel ...
user avatar
2 votes
2 answers
291 views

how to find out the `Key Lookups` in the current query plan?

I am putting a query to list the key lookups that are present in the current requests being executed, I basically would like to tackle then and see if I could eliminate these key lookup from the ...
user avatar
1 vote
3 answers
513 views

Can setting max dop = number of cpu's cause situation where a single query blocks other queries?

I have sql server with 8 cpus. Max worker threads setting is say 850. Max dop is say 8. And cost thread hold for parallelism is 50. This means sql server will breakdown a query going above cost ...
user avatar
  • 2,111
2 votes
1 answer
33 views

temp DB file does not exist after moving

I am using SQL server 2019 AG. I am facing an issue after moving temp DB files from one drive to another after locating the file paths by this script: Use master GO SELECT name ...
user avatar
  • 31
0 votes
1 answer
29 views

Whether it is appropriate to use a single SAN storage pool or create separate pool for SQL server?

My storage admin says that our SAN has 1 storage pool (set of physical RAID drives) that is shared by various servers and end-user vms. I want to install a new SQL server instance on a volume created ...
user avatar
  • 2,111
7 votes
3 answers
2k views

What does SQL server do with the query output once it is read by the client?

Assuming a SELECT query with multi-table join is executed, which resulted in selection of 100000 rows. The join operation will be performed in memory and the result will also be held in memory until ...
user avatar
  • 2,111
0 votes
1 answer
36 views

What is the fundamental meaning of max worker threads in SQL server?

I'm using sql 2019 on 8cpu machine, and as per this link (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-...
user avatar
  • 2,111
0 votes
1 answer
85 views

Counting 1 million rows in SQL server takes 1 minute [closed]

I have a very simple table: ID (integer primary key, ID increment on) field1 (large text field about 4KB per record) field2 (large text field about 4KB per record) I have about 1 million rows in ...
user avatar
0 votes
0 answers
57 views

Can DST transitions cause "random" query timeouts?

I have encountered a query timeout ("Execution Timeout Expired") on a sub-second query on a connection set to 30 seconds timeouts. Client side logging indicated that 4 days elapsed between ...
user avatar
0 votes
0 answers
36 views

Excel header columns changes when export data from sql server to Excel using pandas?

I work on sql server 2017 I run script depend on python language . I run script on sql server 2017 to export data from sql server 2017 to Excel file already exist. header of excel file before export ...
user avatar
-3 votes
1 answer
31 views

How to estimate server(sql server) resources for a specific number of users [closed]

I need to be able to estimate server resources for sql server To be able to save money. For example, how many resources (CPU, memory and RAM) are needed for a database with 3,000 users. Is there a ...
user avatar
  • 11
1 vote
1 answer
37 views

Sql server agent job question - maximum run time

How long can a SQL server agent job can run in general? are there any maximum run time limit settings option while creating a job ?
user avatar
  • 11
0 votes
1 answer
76 views

How to decrypt zipped data in MSSQL table

We're trying to import (reverse engineer) some data from someone else's MS SQL database, without any vendor support. In the past the data has been stored in either plain text or RTF so easy to extract....
user avatar
0 votes
0 answers
41 views

How can I see the queries executed on day x? (So I can find the DB change that is impacting performance.)

Since Day X (which is inconveniently just outside my DB recovery plan), I have been experiencing a significant performance degredation on an overnight process that runs Stored Procedure A, executed ...
user avatar
0 votes
0 answers
39 views

How can i store execution plans for certain stored procs

Unfortunately we cannot use query store as we are on SQL 2014 and need to find a way to store execution plans of certain stored procedures. I saw XE like sp statement completed but unfortunately it ...
user avatar
1 vote
0 answers
37 views

Drop external table in Azure Managed Instance

We moved a database from on-prem to Azure Managed Instance. But now we can't use External Tables, so we're taking a different approach. Is there a way to drop the external tables so I can use the ...
user avatar
0 votes
0 answers
33 views

Is it secure to store connection password on Pentaho Spoon?

I'm gonna use Spoon to transfer data from Postgres to MSSQL. I see that when we create connection its data is stored and we don't need to type password anymore. But I can't find where it stores this ...
user avatar
  • 1,413
0 votes
1 answer
32 views

In Always on AG How can we setup jobs with a failover

In SQL Server 2019, We have primary and secondary replicas. if primary goes down then secondary will become the primary server, In such case how to handle the SQL jobs. Please help me to get it ...
user avatar
0 votes
0 answers
41 views

Is it possible to create a synonym dynamically?

I want to create a synonym for a lot of databases. Here's the synonym script: create synonym blog.Posts for [ClientA].contents.Posts The point is that the ClientA should be dynamic for each database. ...
user avatar
  • 31
1 vote
3 answers
50 views

How to speed up ALTER COLUMN when changing column size on big tables, leading to SUSPENDED state / PAGEIOLATCH_EX wait type?

I tried changing a column on a table with a few million records, changing a varchar column length from 100 to 250. After more than one and a half hours of execution time, whereupon monitoring the ...
user avatar
2 votes
1 answer
284 views

Trying to get a large amount of data (column type is "text") from an MS SQL Server

I want to get like 300MB of data (text column) out of an MS SQL Server, but with the library I'm using I'm getting an out-of-memory exception as soon as I try to access the field. Unfortunately, I'm ...
user avatar
  • 23
0 votes
2 answers
55 views

SQL Server - Finding Parent Source from T-SQL Snippet

The environments for which I'm responsible have some pretty substantial plan non-reuse challenges. I've run across the following query (h/t Brent Ozar blog commenter Michael J Swart) that does a fine ...
user avatar
0 votes
2 answers
52 views

UDF and built-in function behavior

Take the following queries: DECLARE @X VARCHAR(200) = '1,2,3,4' SELECT *, dbo.aUserDefinedScalarFunction(4) AS ScalarValue FROM MyTable T INNER JOIN dbo.aUserDefineTableFunction(@X) A ...
user avatar
  • 407
0 votes
1 answer
18 views

SQL Server Extended Events odd behavior trying to capture statements coming from SAS

The problem: the Extended Events (EE) session we created to capture queries against a certain table is not working when the request comes from SAS. The EE code: CREATE EVENT SESSION [...
user avatar
  • 31
3 votes
2 answers
66 views

How to Join two tables with SUM and GROUP BY in SQL server

I have 2 tables product id designation 1 coca 2 Pepsi 3 Fanta 4 Seven 5 Eight 2)sub_product product_id name quantity 1 sm 10 1 lg 10 1 xl 20 2 1L 10 2 2L 20 2 5L 20 3 Ty 10 3 Sa 20 4 ...
user avatar
6 votes
2 answers
513 views

DateTime2 and Page Life Expectancy (PLE)

As I understand it, when you define a column on a table you define its precision. This precision takes 1 byte and is stored at the column level. If you use a precision of 5 or more, then a DateTime2 ...
user avatar
  • 2,394
0 votes
0 answers
25 views

very simple amateur question. Build a table then do group_concat

I am very new to SQL and don't have much intuition for all this. I have lots of coding experience but nothing in databases. I have two scriptthat work in isolation, but I don't know how to stitch ...
user avatar
  • 1
0 votes
2 answers
32 views

How can I create a brand new alarm based on a verification not listed by default on SQL Server?

I want to create an alert like the ones from Brent Ozar's How to Configure SQL Server Agent Alerts, but I couldn't find a message related to the error I want to monitor on sys.messages as the ...
user avatar
  • 4,394
0 votes
1 answer
27 views

Setting up reporting and alerting in SQL Server through TSQL and Powershell

I am trying to automate monitoring on SQL Server through PowerShell and TSQL, to get alerts through mail whenever some issue is there. I have gone through many articles over the internet and tried the ...
user avatar
0 votes
1 answer
24 views

Microsoft SQL server management studio : insert label in SQL query results

I'm working with SQL server, using Microsoft SQL Management Studio. I'm writing quite some SQL queries, one after the other. This causes a list of results, which are quite difficult to distinguish, as ...
user avatar
  • 333
2 votes
2 answers
236 views

how can I convert this SQL statement with nested IN clauses to a JOIN format

Here are the very simplified versions of my three tables. Schools_Subjects Table SchoolId SubjectId 1 2 5 1 5 6 The above table contains only parent subject IDs. Subjects Table SubjectId Name ...
user avatar
  • 29
5 votes
1 answer
449 views

Sort order with numbers as strings

For demonstration purposes, I have a sample table with a varchar column called numberstring with the values: -4 32 0 4 16 8 -8 1024 When selecting using ORDER BY numberstring, I find that different ...
user avatar
  • 2,337
1 vote
0 answers
37 views

What is select[dbo].[SqlManagement](1) in MS Sql Server?

I often run this script to see whats taking up time and slowing down my server SELECT top 10 last_execution_time ,execution_count , total_worker_time , total_elapsed_time , Text FROM sys....
user avatar
0 votes
1 answer
46 views

SQL query to group results on matching record ID

I am having trouble designing a query. I have a list of PersonIds for records in a Persons table. I would like to have a single query that takes this list of PersonIds and returns other records in ...
user avatar
0 votes
1 answer
69 views

SQL Server login failed for user and all user accounts seem to be disabled

When I tray to login to SQL Server, all users are disabled and fail with error 18470. Even Windows Authentication. Login failed for user 'NT Service\SSISScaleOutMaster150'. Reason: Could not find a ...
user avatar
  • 1
-2 votes
2 answers
85 views

SQL Server 2017 transaction log does not shrink logfile after rebuild index [closed]

I have 10GB database and i have configured transaction log shipping. My rebuild indexes job executed indexes fillfactor changed from 100 to 70 and transaction logfile has growth 60GB. I have tried so ...
user avatar
1 vote
0 answers
89 views

Any benefit creating temp tables with random prefixes in SQL Server to control latch waits on tempdb database

Recently I came across reading in one of the blogs that if a temp table name is prefixed with different/random names (in different procedures) that could benefit in reducing the concurrency issues / ...
user avatar
-3 votes
0 answers
28 views

Total hours spent on working on each project using SQL Server (Azure Data Studio)

Question:: Create a new view called “Project Hours” which would show for every project how many total hours were spent working on it, and the number of employees working on the project. Query the view ...
user avatar
2 votes
2 answers
314 views

Need help tuning a complicated query

I've inherited a rather complex stored procedure that is causing timeouts in our Production environment. I've used SQL Sentry Plan Explorer to help me look at some of the issues. I've identified a ...
user avatar
3 votes
1 answer
73 views

SQL Server Data Tools -- Stopped connecting to Azure SQL DB

All updates are installed on VS 2017 (15.9.47) and 2019 (16.11.13) I am trying to perform a data comparison between two DB's via Microsoft’s tool , On Prem and Azure SQL managed instance Everything ...
user avatar
  • 31
0 votes
1 answer
27 views

Restore SQL Server replication subscriber

We have merge replication in our system and there is a case when we need to deploy our system with subscriber database and time is crutial. I wonder if the is possibility to take configured subscriber ...
user avatar
1 vote
1 answer
45 views

How do I generate the private key needed to restore the encrypted backup?

I'm simulating the situation where I take a backup from instance A and restore it on instance B. I followed the doc to Backup to Disk with Encryption. Since I'm using only my desktop to simulate the ...
user avatar
  • 4,394
-4 votes
0 answers
22 views

Hi, I am new to Dbeaver and I cannot create a table. Below you will find my code so you can have a better understanding & I hope you can help me

CREATE TABLE nasdaq( nasdaq trade varchar(50), symbol varchar(50), security name varchar(256), listing exchange varchar(50), market category varchar(50), etf varchar(50), round lot size varchar(50), ...
user avatar
  • 1
2 votes
1 answer
30 views

Error trying to add a login to Azure SQL Special Role 'dbmanager'

Upon running these commands on an Azure SQL instance: CREATE LOGIN test WITH PASSWORD=N'Passw0rd' GO ALTER ROLE dbmanager ADD MEMBER test GO ... I'm getting an error: Cannot add the server principal ...
user avatar
  • 429
1 vote
1 answer
45 views

Move Functions from Where Clause to Select Statement

I have a union query that runs abysmally slow I believe mostly because there are two functions in the where clause of each union. I am pretty sure that there is no getting around the unions, but ...
user avatar