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
2answers
29 views

Subqueries in SQL Server

I am taking a SQL course and am completely stumped on this question. Using a subquery have a query that returns: CustomerID, EmailAddress, FirstName, LastName, '#3: Customers who have ordered than 1 ...
28
votes
7answers
46k views

Does SQL Server support GREATEST and LEAST, if not what is the common workaround?

Reviewing this question it seems like that's a lot of work that shouldn't be needed. They're trying to extend a range with a date. In other databases, you would just use greatest and least.. least(...
2
votes
2answers
40 views

How to make large “in” queries for an indexed field?

In mssql, how can I run effectively this query: select id from table where field in (gigantic list of ordered longs) Table schema: table: { id primary key field long } index findex (field) I ...
-2
votes
2answers
28 views

How to Cast today data as bigint to Data - 202104171830

I have got today's data as Bigint: 202104171830. How could I cast this Bigint value to today's date ? Please help. MSSQL
2
votes
1answer
685 views

Query from Postgres to SQL Server

I have this query in PostgreSQL and I am not familiar with PostgreSQL. I need to convert it to SQL Server. SELECT (applicant_majors.*)::applicant_majors FROM applicant_majors WHERE applicant_majors....
0
votes
1answer
111 views

Increasing tempdb data files on 2 node Availability Group

I am planning to create additional tempdb files for 2 nodes in a SQL 2012 Availability Group. Unfortunately we do not have Instant File Initialisation turned on so the growth of the tempdb data files ...
0
votes
1answer
1k views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
7
votes
2answers
1k views

What is the optimized way to pass multiple parameter from a single table to a function?

I have a function [dbo].[FN_CalcAerialDistance] which accepts 4 parameters and returns its result. Now that 4 parameters come from the same table, I do not want to hit Database multiple times to bring ...
0
votes
1answer
19 views

How to get a count of how often a name/string appears

I have a database of devices owned by users, I want see how many users own more than 2 devices, but it would suffice to just get the total count. Real query: Select tblAssetUserRelations.Username As '...
0
votes
2answers
273 views

Hint to force locks between 2 Select clauses

I have a process that run in parallel on several threads and also on several machines. In summary, the process has to 'lock' a row in a table named 'Queue' and then retrieves some columns from the ...
0
votes
1answer
18 views

Query Store System View JOINs

In SQL Server 2016, is there an optimal/correct way to JOIN sys.query_store_query_text and sys.query_store_query (joinable using query_text_id) with sys.query_store_runtime_stats? I don't see any ...
1
vote
1answer
22 views

Invalid Column Name on CONCAT_WS

Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue. I have two tables cases and legal_entity the data on the two tables looks like this legal_entity ...
2
votes
1answer
168 views

What's the difference between equality (=) and inequality (<>) on BIT field in SQL Server?

Is there difference between WHERE [BitField] = 1 and WHERE [BitField] <> 0? They seem logically equivalent. SQL Server, however, disagrees and creates slightly different execution plans. (Which ...
1
vote
2answers
5k views

How to use HTTP Connection Manager in Script Component? (Not Script Task)

I'm trying to perform the same scenario as in the following link; Create a SSIS Script Component as a Data Source that uses a pre-existing HTTP Connection Manager to retreive a page with GET and emit ...
4
votes
1answer
180 views

No parallelism in Express Edition of SQL Server

I have SQL Server 2019 Express Edition (CU8) on Windows Server 2019 (1809 version) and all of my queries go serial with NoParallelPlansInDesktopOrExpressEdition in NoparallelPlanReason property. Is it ...
0
votes
1answer
20 views

How to test user permissions

I have two server users, lets say admin and notadmin. I need to test notadmin's permissions. I need set and test VIEW SERVER STATE permission on notadmin, but usually I have access only to database ...
-1
votes
2answers
30 views

Migrate bak-file to older version of SQL server using command line [duplicate]

I want to copy a bak file from sql server 2019 and restore it to a sql server 13.0.5026.0. The conversion from one version to another that I have seen this far is using a wizard. However, I need to do ...
0
votes
0answers
26 views

Set SPN Service principle name for SQL Server Service account - Best practice

I am looking for guidance on Microsoft recommended best practice regarding setting the SPN for the service account that runs the SQL Server service (MSSSQLSERVER) When setting up a new server (or ...
0
votes
1answer
39 views

SQL Server Management Send Mail as Part of a Job

I have a SQL Job that I would like to send an email through, but for some reason I can't get any details on why the email is not sending. This code works as expected when executed in the query window, ...
1
vote
4answers
453 views

How do you determine the direction of a relationship?

Suppose I have the following relationship diagram: How would you describe the relationship determined by the red line? Reading from left to right you would say each order has only one customer so it ...
0
votes
0answers
20 views

SQl Server log shipping monitor security configuration and update problems

I'm setting up log shipping between instances A and B with the instance C as a monitor. The shipping itself is working fine but I'm somehow unable to make the monitoring work. The log shipping status ...
2
votes
1answer
170 views

Data files half empty in SQL Server. Is that a problem?

I have a multiple TB database and I have been doing some clean-up and dropped many tables. So now the data files are half empty. If I don't care about releasing the space to the operating system, is ...
22
votes
6answers
102k views

Could not find the Server Agent in SSMS

I am following the guide - http://www.sqlchicken.com/2009/07/how-to-create-a-server-side-trace-with-sql-profiler/ which is working until I reach Step 8 Now to schedule your newly created script. ...
1
vote
1answer
24 views

Reduce transaction log file utilisation

I have a SQL Server 2017 database that currently has an 80GB data file and a 90GB log file. I want to get the log file down to a more manageable size, because I am about to run some big scripts which ...
0
votes
1answer
18 views

What is the most performant way to group by and join high cardinality columns in column based table?

I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
0
votes
0answers
29 views

TempDB started constant disk IO

At around 10am yesterday I started seeing constant read/write activity to the TempDB database via our RedGate SQL monitor. This is unusual because usually our disk operations are <100kb/s however ...
1
vote
0answers
31 views

How do SSIS connection strings send passwords?

I have a SQL server that pulls data from another SQL server via SSIS. We recently converted the server targeted by SSIS to VM. Since the VM conversion I have been getting "login failed for user '(...
0
votes
1answer
30 views

Why is SELECT USER_NAME() returning my windows credentials and not the database user mapped to a windows security group that I am in?

I am trying to implement Row Level Security and have mapped an AD Security Group to a user in my test Database. When trying to select data from a table that I have granted access to I am not getting ...
-1
votes
0answers
20 views

How to estimate run time for for moving clustered index to new filegroup?

Need to move 3 semi-large (millions of rows) related tables with clustered indexes to a new filegroup on the SAN. Is there a way to estimate how long dropping / recreating clustered index will take? ...
0
votes
0answers
20 views

SSIS package run error indicates wrong credentials after VM migration

First, I am not a SQL DB admin. I inherited this system and working to migrate away from it...that said: I have an old MSServer2000 SQL DB we migrated to VM. That server offloads data to another SQL ...
2
votes
1answer
40 views

Key lock triggered by batch update

I'm doing a batch update operation,but it appears key lock I think I'm in trouble. I don't understand why the deadlock is happening. Environment: SQL Server 2016 deadlock graph detailed: <deadlock-...
0
votes
1answer
29 views

VM'd a SQL server, having trouble with external HDDs

First I am not a SQL DB. I inherited an old system and am trying to keep the wheels from falling off. We have an MS SQL 2000 server (running server Win Server 2000) that we performed a P2V migration ...
1
vote
1answer
49 views

Reason for failed query plan executions in SQL Server

We are using Azure SQL Database and recently we have experienced a severe performance degradation on our production database. When looking into Regressed queries in Query Store to find out the ...
0
votes
2answers
39 views

Online rebuild consuming lot of log space

why the online rebuild on a primary key clustered index in taking almost equal to the space of non clustered index size which is untouched. Details: We have a table with primary key clustered index (...
13
votes
7answers
7k views

Reasons for disabling statistics auto update?

I just learned that a client company I work for has decided to keep the auto update statistics options off for some of their SQL Servers, and the DBAs manually troubleshooting performance issues when ...
0
votes
1answer
21 views

OPENJSON running once per row of an INNER JOIN unnecessarily?

I have a table MP_User with 147 entries, and in order to query it, I am passing in JSON containing an array of "query objects", each of which has a unique ID and either an email address or a ...
0
votes
1answer
24 views

migration by cloning a Production SQL Server host into a Dev environment

We are doing some SQL Server migrations, various versions, by cloning (snapshot backups) the host servers to the new DC in a different domain. Work is still in planning stage, not yet implemented. For ...
-2
votes
1answer
29 views

SQL to Determine which Row Combinations have the Most Occurrences

I have a table InvoiceData VendorNum InvNum AU Acct Amt 12345 Invoice 1 9010 40006 459.00 12345 Invoice 2 9010 40006 425.50 12345 Invoice 2 9010 40007 425.50 12345 Invoice 3 9010 40006 33 12345 ...
1
vote
1answer
44 views

How do I CAST a date in a subquery? Currently getting an error: Conversion failed when converting date and/or time from character string

I have a column called Source in my table called TPR. The Source column is of datatype varchar and contains text like January 2020 and Q3 Completed. When I run a query that only extracts the date-like ...
3
votes
3answers
373 views

Permission error when using sys.dm_exec_input_buffer with @@SPID

When I run this blob of SQL on SQL Server 2016 or an Azure SQL DB: --hello world SELECT event_info FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req....
0
votes
0answers
21 views

Why does DTExecUI.exe run fine but DTExec.exe gives me an error?

When I try to execute an SSIS package with DTExec.exe with a batch file or in the command prompt it gives me error code 0xC0040019 Data Flow Task 1 (SSIS.Pipeline) data flow objects cannot be loaded. ...
5
votes
2answers
380 views

What happens when you change a heap into a clustered table in SQL Server?

As I understand, a heap is an unordered object. When you insert a record, SQL Server use IAM page to get the pages that belong to this heap, and use PFS page to find a particular one which has enough ...
3
votes
0answers
32 views

SQL Server Network Packet Size

During the analysis of network traffic from Citrix clients to our database server, we noticed that we had a lot of RETRANSMITs occurring during the transport of TCP packets. The packets were being ...
0
votes
2answers
59 views

SELECT DISTINCT in extra space data from a single column ( no duplicates )

Hello i am having problems with this column data cities Charlotte Cincinnati Cleves Columbus Fairfield Fresno Los Angeles -- HERE IS THE PROBLEM Los Angeles Madison Manhatttan Beach New York Orange ...
0
votes
1answer
127 views

SQL Server Backup Failure

This one has me stumped and I'm sure there is a simple explanation that I cannot fathom at present. A few informational pieces: vCentre Database - SIMPLE Recovery Model SQL Server 2008 R2 Standard ...
0
votes
1answer
48 views

Can Microsoft JDBC Driver 4.0 connect to SQL Server 2019 and perform DML without issue?

The SQL version compatibility BOL article advises that the minimum compatible JDBC driver version for SQL Server 2019 is 7.4. Can an older JDBC driver still connect to SQL Server 2019? Or can only the ...
0
votes
1answer
23 views

Order by starting from “the middle” in a windowing function

What I have is a table like this, where I need to do a running total of the values column for the upcoming months. The issue is that I would like to start from month 4 (shown in column 3) and go all ...
0
votes
1answer
55 views

Powershell script executed by SQL Server doesn't take effect

I'm doing some testing and I want to execute a PowerShell script through xp_cmdshell. Below is the PowerShell script (c:\temp\sqltotext.ps1). echo "haha" | Out-File "c:\temp\haha.txt&...
-4
votes
0answers
44 views

Update and Select in real time SQL Server [closed]

I am with SQL Server, and I need a solution to update several tables and, at a same time, select queries in real-time. I know this could be blocked by another user if you make one of this operations, ...
0
votes
1answer
46 views

SQL sql subquery with SUM for totals

I am a little stuck on getting a total value for a column using the SUM function. I want to get the total amount of items for an order from my database. The DB order is made up of several line items ...

1
2 3 4 5
598