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
0answers
28 views

SQL Trigger fails retrieve new record in second query

Any advice on this will be appreciated This script created a table with new records. My script is failing to assign the "Inserted" ID to the query but manually assigning a value works (a) ...
1
vote
1answer
39 views

Group by every N records but remove incomplete group that has records less than N

I need to query a table to fetch data in groups of some size say N=5 but if the size is less than 5 then the last group should be ignored i.e every 5 records should be grouped ==> i.e first 5 ...
2
votes
0answers
29 views

Is it OK to restore master db backup, after changing WSFC clustered instance into non-clustered instance

I'm converting WSFC fail-over clustered SQL Server 2012 instance into non-clustered one. My question in short: Is it OK to restore a master database backup taken from the clustered instance, into the ...
0
votes
0answers
27 views

How do I debug an “Operator used tempDB to spill” warning when statistics appear to be ok

I have a section of a complex query plan where a Sort operator appears directly after an Index Seek and SSMS shows a "Operator used tempDB to spill" warning. It is reported to be taking 70% ...
1
vote
1answer
34 views

Can two SQL “Always on” nodes have different datafiles position on disk?

I have two Always on instances SQL server 2014 configured on two servers (primary read/write, secondary only sync). First instance has 13 db and the second has only one. I need to move all the ...
0
votes
1answer
57 views

TRY/CATCH only reports the last (and most useless) error

(This is SQL Server 2016 SP2-CU12.) When running this command interactively, I see the quite useful Msg 3035, Level 16, State 1. BACKUP DATABASE @DB TO DISK = @targetfile WITH DIFFERENTIAL, RETAINDAYS ...
0
votes
1answer
16 views

Database Design For reporting comparison results

I'm going to design an table architecture. Here I wanted to compare same data coming from different sources say Source_A and Source_B. I have to compare few attributes and identify Mismatches Data ...
0
votes
0answers
16 views

Optimal selection of predefined non-materialised data

Take the following result set as an example: Store Category Item Cost Stock Sold Ordered ABC Accessories Lock 344.31 281 141 225 DEF ...
3
votes
2answers
95 views

Purposefully fail select with no where

We have an evil query that reads every row from a table, but should not. To help the devs find the source of that query, I want to make SQL Server error out when any select against that table has no ...
0
votes
1answer
23 views

Replication types

How can I find out, if my current replication pattern is any of following or not? I am using SQL2008R2. 1)Peer to peer replication 2)Bidirectional transactional replication
-1
votes
0answers
24 views

Reading Data from Encrypted Table MsSQL [closed]

I have a database which is MsSQL 2008 and some of its columns are encrypted via symmetric key. I am new to C# and couldn't find any documentation about reading data from it without modifying my ...
1
vote
1answer
38 views

Why am I getting the An expression of non-boolean type specified in a context where a condition is expected, near ',' error [duplicate]

This is my code: SELECT * FROM mytable WHERE (Permit,Stampdate) IN (SELECT Permit,MAX(Stampdate) AS Stampdate FROM mytable GROUP BY Permit)
0
votes
1answer
30 views

SQL Server and ASP.NET - Backup Log database with Application Pool in Always Running

I have a database on SQL Server Express used for 3 Asp.net applications. The Recovery Model is Full. Every day I execute a backup database and log with this code BACKUP DATABASE @databaseName TO DISK =...
0
votes
1answer
28 views

SQL Server Alias won't work for my database instance

I have setup a SQL Server 2019 instance on my machine which has a computer name in a format like this: AB-ABC1234ABAB My SQL instance is named 'SQL2019', therefore I connect to my local SQL instance ...
0
votes
1answer
34 views

“To many rows being processed at one. Reduce and try again.” error in SQL Server Management Studio

I get this error when trying to update some rows from SQL Server Management Studio v18.5: Msg 50000, Level 16, State 1, Procedure UPDATElimit, Line 7 [Batch Start Line 0] To many rows being processed ...
1
vote
1answer
45 views

High CPU Usage on certain query when changing DB Compatibility Level

We migrated from SQL 2008R2 to SQL 2016 a few years ago and have been running on Compatibility Level 100 since that point. We are looking to make use of memory optimization introduced in SQL 2016 and ...
2
votes
3answers
413 views

Need help to understand tuning up a slow SQL server query [closed]

For one of our databases the query like below is extremely slow. Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below SELECT [...
0
votes
1answer
36 views

SQL server replication (publisher, subscriber, distributor) on top of AlwaysOn

I am assessing the migration from SQL 2008R2 to SQL 2017 AlwaysOn where we have replication (publisher, subscriber, distributor) setup on SQL2008R2. There are various restrictions if one wants to ...
0
votes
1answer
32 views

Cloning a SQL Server Virtual Machine with TDE

When a virtual machine with a SQL Server database that uses TDE is copied or cloned, will the database be readable on the new instance?
0
votes
0answers
32 views

View multiple Database Servers through single SSMS connection

Recently I've been handed the keys to an environment with several servers, each running multiple instances of SQL server. These span many version of SQL server over the years. I have documentation I'm ...
0
votes
1answer
56 views

Single user mode looses connection

So just a quick background, we are trying to update the database design, in a production environment. But we want to be sure, no users try to login during that time. So we started looking into single ...
0
votes
1answer
45 views

A way to process a CTE once?

I have a view that has serious performance issues, because I am linking it to a CTE that uses another view, with the same table references. WITH splits(ID, [NEW TEU]) AS (SELECT s.[DTL ID] AS ...
1
vote
1answer
34 views

SQL Server & SSDT : Error modeling database during Publish after adding CREATE AGGREGATE / EXTERNAL NAME

I am using SSDT to create a database containing CLR user defined aggregates. The aggregates are intended to sit in different schema. That is not possible with CLR objects inside SSDT, without using a ...
2
votes
2answers
81 views

How many threads being used while query execution?

I have AdventureWorks2014 database installed on my test SQL Server 2014. I am planning to execute the following query: SELECT * FROM Sales.SalesOrderDetail sod INNER JOIN Production.Product p ON sod....
0
votes
0answers
20 views

SQL Server System Objects and Intellisense

I have created two stored procedures that I want to be available from all databases on SQL Server 2017. I created them in the master database, used sp_ as the prefix for both of them (e.g. sp_MyProc1 ...
1
vote
1answer
27 views

TABLOCK for both truncate and subsequent bulk insert

I'd like to truncate a table and then run a bulk insert on it with some new data. I tried a query something like this: BEGIN TRANSACTION; TRUNCATE TABLE db.dbo.table; BULK INSERT db.dbo.table FROM ...
1
vote
1answer
30 views

Derive Date Spans from Start and End Dates in SQL Server table

I am using SQL Server 2016 I have a table that contains 1 row per month that a patient is assigned to a particular Provider. A patient can be assigned to multiple providers during the year. How can I ...
-2
votes
0answers
24 views

Rearrange Month Wise data in sql query

Currently I am retrieving data month wise from below query select month(EntryDate) as m, sum(QTY) as QTY,Sum(IWeight) as Weight from ConIssuance where delid is null group by month(EntryDate) Order ...
1
vote
1answer
37 views

How to find out the real time usage of SQL Server database by users [closed]

Please advise, can I somehow find out the real time usage of SQL Server database by users using sql profiler?
0
votes
2answers
45 views

New DB is not in the end user list

I created a user tom(SQL Login) in SQL Server 2019 Standard edition. Then I remove and reinstall the SQL Server 2019 standard edition(same version) and restored all the databases again with the same ...
0
votes
0answers
25 views

How can a subscriber of one publication also be a publisher of another publication in SQL?

How can a subscriber of one publication also be a publisher of another publication in SQL? We have a replication on our server which is not initializing because of the following error: "Cannot ...
-3
votes
1answer
73 views

SQL Server Integrity Check error

I’ve Implemented Ola's SQL Server Integrity Check on one of my SQL 2012 Servers and created a SQL Agent job to run it. The job fails every time I run it, but when I run the code below manually it ...
0
votes
2answers
45 views

Lock only a row in insert command sql server

I have this initial code below to create and insert some values on my table IF (OBJECT_ID('dbo.Teste_Nolock') IS NOT NULL) DROP TABLE dbo.Teste_Nolock CREATE TABLE dbo.Teste_Nolock ( Id INT ...
2
votes
1answer
61 views

Auto Update Statistics threshold SQL Server 2016

since SQL Server 2016 the default auto statistics update threshold changed in that way that it uses a smaller threshold depending on the table size. In our application we periodically update ...
0
votes
2answers
74 views

Materialized view in SQL Server

Have a bit of glitches from the time I was using SQL Server back in 2006 and the many other databases I played with (and which I have not touched since then). I need to create a materialized view to ...
0
votes
0answers
21 views

DB design for single Dependent table column to multiple Primary table

I have a scenario where records in the dependant table(say History) are created by two entities, one User and other Application. For which my purposed design is User ----------------- UserId (PK) ...
0
votes
1answer
26 views

SQL server Log Shipping

I have two node SQL server standard failover cluster. I need to setup single node DR using log Shipping method. Want to ask after switch over to DR how we can setup, plan so that application can ...
2
votes
1answer
79 views

Why is my recursive CTE so much slower on Azure SQL?

I have this simple recursive CTE for a hierarchy of folders and their paths: WITH paths AS ( SELECT Id, ParentId, Name AS [Path] FROM Folders WHERE ParentId IS NULL UNION ALL SELECT ...
0
votes
1answer
26 views

AG listener with MultiSubnetfailover True on Named Instance

I have an AG configuration with databases on a Named instance. I have enabled multisubnetfailover =true and registerallprovidersip =1 I just saw some blurb on the web that these settings are not ...
2
votes
1answer
55 views

Red Symbol on Server Roles

I was RDP to one of my servers (SQL Server 2012 (SP4) Std Ed) and saw that the Server Roles had a red symbol (not a red x), but when I connect to that server through a VPN using MSSQL that red symbol ...
1
vote
0answers
42 views

SQL Query fails when content of a single parameter change

Im very puzzled about this issue.. I have a SQL Server database with many records and it is used by my website. When the website user searches for a specific record, the SQL command gets a timeout. ...
0
votes
2answers
53 views

Why 2 Table Scans of Same Table Have Huge Differences in Elapsed Time SQL Server

This is driving me bananas. According to both the Execution Plan AND Statistics IO, both queries are doing a full table scan and nothing more, but in one case the query takes LESS THAN ONE SECOND and ...
1
vote
1answer
33 views

SqlPackage resolving table that doesn't exist

I'm trying to validate the integrity of one of our SQL Server 2016 databases by using sqlpackage.exe to extract the dacpac and validate the schema: ./sqlpackage.exe /DiagnosticsFile:"...
0
votes
1answer
31 views

How to stop all nodes on FCI SQL server?

My Setup: We have two nodes SQL setup with Active/Passive Windows Failover Cluster instance. My Goal: To stop the running SQL Server service on the active node and prevent failover from happening for ...
2
votes
1answer
39 views

how to mirror an azure storage account to a non-paired zone to enable fast file_snapshot backup/restore and database file access?

Wow, that title was a mouthful. Scenario My dev server is restoring a copy of production data daily, anonymizing it, and creating a dev-grade backup for my developers to restore locally. We're running ...
1
vote
1answer
40 views

Using aggregate function on data masked column returns zeroes

Recently we have implemented dynamic data masking in SQL Server 2019 database in order to hide sensitive information from developers. However, for testing purposes I'd like them to see close-to-real ...
0
votes
0answers
18 views

Removing a Windows Server Cluster Node and Then Adding Back - Do I need to remove the Availability Group replica?

We had a secondary server bite the dust... we were remarkably able to resurrect it. Everything is great and intact... except for the windows server cluster configuration. We basically need to evict it ...
1
vote
1answer
52 views

Problem tracing Error: 8623 with extended events

From time to time we are getting SQL Server error: Error: 8623, Severity: 16, State: 1 The query processor ran out of internal resources and could not produce a query plan. This is a rare event and ...
1
vote
3answers
54 views

Best way to choose which columns to index to improve performance

I use a MSSQL Database for my web application and now the user audit(USER_AUDIT) records in the application have crossed beyond 5 million records/rows. Every day the performance of the application is ...
0
votes
2answers
23 views

Default trace vs auditing in SQL Server

Currently I am working in an environment that has the default trace enabled and I would like to propose that we add SQL server auditing to our environment. I'm looking for some points to put forward ...