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
votes
0answers
12 views

ODBC XML Mapping

i am trying to map a GE Proficy Historian ODBC Collector but I am unsure as to how i would go about mapping the following sections. my database has a column 'tagname' under which lies the pressure/...
2
votes
1answer
220 views

SQL rolling back all iterations of a while loop when each iteration has its own explicit transaction

For the past few days I've been trying to write a query for an automated process that will delete records from a log table within a specified date range (a month) to another table, so they can then be ...
0
votes
0answers
28 views

SQL Server Snapshot replication - Failing to start the Publisher snapshot agent

I am trying to set up a Snapshot replication using an Azure SQL Managed Instance. While trying to see the Snapshot Agent status, I see this error. Failed to connect to Azure Storage '' with OS error: ...
0
votes
0answers
13 views

Sql Server Fulltext catalog gets truncated

We're facing an interesting issue which we can't reproduce. On our Sql 2012 from time to time our Fulltext catalog gets truncated, We read MSDN but we didn't find any information if there is such an ...
-4
votes
1answer
30 views

How can i remove the duplicate values while using inner join [closed]

I get duplicate AccountIDs if I use the below code. Select Distinct(BE.AccountID), SUM(BB.Balance) AS BonusBalance, [Freebet] = CASE WHEN be.Freebet = 1 THEN 'FREESPINS' ELSE 'BONUSDEPOSITED' END -...
0
votes
1answer
25 views

Comparing running total with group by

working with Two scenarios CREATE TABLE [dbo].[Test]( [LineID] [int] NULL, [BusinessUnit] [nvarchar](50) NULL, [Value] [nvarchar](50) NULL, [Amount] [money] NULL ) ON [PRIMARY] GO ...
-1
votes
1answer
25 views

Dynamic sql query with top count sqlserver [closed]

My Requirement I need to set\get a variable's value using the dynamic query I need to write a dynamic sql query to select top (@topCount) The @topCount will need to be a calculated value using the ...
1
vote
1answer
26 views

Merging a .bak to an existing SQL database

I have a SQL Server database with data from 1990-2018 in a .mdf file. I recently downloaded data for 2018-2020 and it's in a .bak file. When I went to "Restore" the database, it wiped out ...
0
votes
1answer
38 views

How to identify queries that cause high Logical I/O?

I need some help to identify SQL queries that could suddenly increase the SQL Server Logical I/O. Can someone please help to provide some advice? This was from SQL Server 2017
5
votes
1answer
89 views

FORMAT 'G17' vs 'G'

I have a question about this fiddle: DECLARE @TestVal AS float = 8.88; SELECT flt = @TestVal , xml = (SELECT Value = @TestVal FOR XML PATH(''), TYPE) , fmt17 = FORMAT(@TestVal, 'G17') , ...
0
votes
0answers
36 views

Log File keeps on growing with every Restore [closed]

We are restoring a Database from a Microsoft SQL Server to another SQL Server on a nightly basis, overwriting the existing database every time with the new one. It appears that the log file on the ...
-2
votes
0answers
16 views

ArangoDB, Node4j or Sql Server for a startup project [closed]

I am developing a startup project and I am exploring some new approaches for storing data. My current model is relatevely small, but I dont know how it is going to develop in time. Here is my model: ...
4
votes
0answers
90 views

hash join hint returning error when using local variable

DECLARE @Id INT = 18471 SELECT c.UserId FROM dbo.Comments AS c INNER HASH JOIN dbo.Users AS u ON c.UserId = u.Id AND c.UserId = @Id The query above using Stackoverflow 2010 database with hash join ...
1
vote
0answers
49 views

Window Function - SUM() OVER (PARTITION BY … ORDER BY …)

i wish you all stay healthy and safe in this unprecedented circumstance. i am a newbie and asking my very first question in here. i have following dataset. green: original data. yellow: values that i ...
0
votes
0answers
48 views

Troubleshooting an automatic failover in 2 node multi subnet AG

I am trying to troubleshoot an automatic fail over that happened on our below infra- both of below running on HYPERV VMs Windows version 2019 SQL1 - 8 cores 64 GB RAM - No load running (Primary) ...
0
votes
2answers
16 views

Does “allow remote connections to this server” in sql server impact SSIS packages?

I'm wondering if anyone has experience with this setting "allow remote connections to this server" in sql server. I am partly responsible for a few hundred sql servers which run SSIS in ...
1
vote
2answers
24 views

SQL Server on azure virtual machines default instance

We have two ways of provisioning SQL Server on an Azure virtual machine, one is creating the VM and then upload the binaries of SQL server and do the installation manually, the second one is using SQL ...
0
votes
2answers
22 views

What is the difference between SET NOEXEC ON and SET PARSEONLY ON?

Currently, SQL server supports the following statements: SET NOEXEC and SET PARSEONLY. From the documentation: SET NOEXEC ON: When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL ...
0
votes
1answer
14 views

Find Unique Index that does not have Unique Constraint

I need to find list of unique indices that does not have unique constraint on that column. How to achieve this? I know, following query will give me unique constraints. SELECT * FROM ...
0
votes
1answer
30 views

SQL server row count

I had an activity for restoring a full database in SQL server 2008. I had to restore the database will norecovery since I had to restore the differential backups as well which will be shared everyday. ...
0
votes
1answer
13 views

Connect to Azure KeyVault via SQL Connect failed with Error code 3113

I have a task to enable SQL Server database encryption with an Azure Key Vault. I follow the steps described in Set up SQL Server TDE Extensible Key Management by using Azure Key Vault. More details ...
0
votes
2answers
27 views

SQL Server sharing drive location

DBA newbie here. Did some searching online but cannot find a definite answer on this. Is it acceptable to share the drive where .MDF files reside with other non-SQL Server files such as applications ...
14
votes
7answers
1k views

How to deny writes in all SQL Server databases (including new ones & restored ones)

I want to create a login that is denied the ability to write to any databases - not just databases that exist today, but any newly created databases or databases that are restored from other servers. ...
1
vote
1answer
90 views

Index or query tuning for complex predicates

I'm struggling to identify effective indexes (or rewrite the query) to improve a query with the following confounding predicates: JOIN on a date from one table being in range - between two date ...
0
votes
2answers
64 views

Can you change recovery mode from simple to full with users working on the server?

Can you change recovery mode from simple to full with users working on the server? While the server is changing the mode is the work of the users interrupted? If so, for how long. Version: SQL server ...
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
38 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
28 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
32 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
54 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
90 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
37 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
26 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
44 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
407 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
35 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
31 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
30 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
33 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
77 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
19 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
2 3 4 5
567