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

Auto logging / tracking records changes at MS SQL [duplicate]

how can I set automatic recording all changes that were done at tables records (Updates) and to get some info / logging summery? thanks
-1
votes
2answers
54 views

Transaction log: small transactions vs large transactions for speed [closed]

I am trying to learn how the transaction log works and have been studying it for a couple of day. Some operations become faster when done as many small transactions, e.g. deleting many rows in small ...
-3
votes
1answer
37 views

Change Consecutive Row Value [closed]

I am trying to change value of Consecutive AA to first AA as AA1 and second one as AA2, if only one AA then it should be as AA1, its possible to change all AA to AA1 and then search for 2nd ...
0
votes
0answers
17 views

Authenticate mssql server using kerberos all running on local mac os

I have SQL Server running on docker and did the setup using the following : https://www.quackit.com/sql_server/mac/install_sql_server_on_a_mac.cfm Then I setup Kerberos on local using the following : ...
0
votes
0answers
28 views

Linked server anonymous login error

I am trying to access a database in my UAT server through a windows login credential. When I try to access the database, I get the error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' This ...
0
votes
0answers
32 views

Using gMSA account for SQL server reporting services

I am trying to configure SSRS on my VM:s via gMSA account which does not seem to work and unable to access the reporting url ( Web Portal URL) link to check configuration. The requirement is to have ...
0
votes
1answer
29 views

MS SQL Row-Level-Security out of a Citirx Store Front

I’m looking for either a solution, or a definitive answer that what I want to do can’t be done. My situation: I have a MS Access Front End that is located in a Citrix Store Front. The Front End ...
5
votes
4answers
509 views

Database events but not triggers

This is a question regarding general DB inner workings, not particular to an implementation or paradigm, though answers for certain technologies are welcome. I am asking if there is a way to listen to ...
0
votes
2answers
45 views

How to change the datatype of column without deleting it indexes; [closed]

I am looking for changing the datatype of a field from nvchar(20) to nvchar(MAX), but it retains the below message. my questions here: Deleting the index will affect on the data? Can I restore the ...
0
votes
3answers
68 views

Auditing of database connections in SQL Server 2008 R2

We are finally planning to close down our last SQL Server 2008 R2 server. Before we can proceed with this we need to track what users are connecting to which databases. We have tried different options ...
0
votes
1answer
29 views

Newly created index not appearing [closed]

Here's a weird thing: In SQL Server 2017 Standard, I created a new index on one of my tables which already had a few indexes on it, by running the following in SSMS: create unique index ...
0
votes
1answer
33 views

Running select filename from dbo.sysfiles returns short foldernames with tilde

I am running the following statement in SQL Server 2014(in Windows 2012R2): select filename from dbo.sysfiles where fileid = 1 However it returns: c:\progra~1\micros~1\mssql1~1.sql\mssql\data\master~...
0
votes
1answer
52 views

Why is the built-in distance function so much slower than my custom one?

I'd like to find the distance between 2 sets of coordinates. First I use the built in function: declare @lat1 real = 33.1, @lon1 real =-117.1, @lat1 real = 39.6, @lon1 real =-98.7 declare @source ...
0
votes
2answers
38 views

Optimize performance for query with lots of OR's

I have the following query select Id from Table1 where column1 = @inputparameter1 or column2 = @inputparameter1 or column3 = @inputparameter1 or column4 = @inputparameter1 or column5 = @...
0
votes
1answer
22 views

Does Sql Server caches parameterized anonymous blocks?

"Anonymous block" is a term from Oracle. But speaking of the thing for sql server Declare .... Begin begin trans . . . commit End If I send this SQL in parameterized form to ...
1
vote
1answer
126 views

Configure Connection String to Use Target Machine Credentials

I am trying to develop an SSIS package that will be run on several different machines. I am stuck because I do not understand how to create a connection string that doesn't explicitly point to one ...
0
votes
1answer
49 views

Log files in Always on Disaster Recovery Situation

In my Always on Avaliability group setup there are four nodes in the cluster in two datacenters, an avaliability group has for replicas, one on each node. Two replicas in the same datacenter have sync ...
0
votes
0answers
39 views

Service Broker error when ending a conversation

I use Service Broker infrastructure to develop data push approach between multiple servers ( many sources and one destination ). I stick Remus Rusanu recommendations about how reuse or end ...
0
votes
0answers
40 views

Trying to connect two sql server databases

I have been trying to connect to a SQL Server instance. When I use the IP address, it connects. But when I use the server name, it doesn't. The main reason why I need to use the name is because I want ...
0
votes
2answers
47 views

Transaction log space used in percent - how to clear or empty

I am trying to learn more about the transaction log, which I think is the bottleneck of many Stored Procedures (used for reporting) which do data modifications. So I tried below: select * from sys....
0
votes
1answer
41 views

Cannot find sqlserver service

I installed Sql Server 2019 in Windows PC. In Sql Server Configuration Manager into SQL Server Service I stopped all the instances. But still I can loggin to Sql Server via SSMS.why is that? Where is ...
0
votes
1answer
47 views

How to avoid implicit conversion when using XQuery value() method in TSQL?

I have the following xml that I am parsing using TSQL - XQuery value method. <Root> <FooAudit> <BarIntField>..</BarIntField> <BarDateTimeField>..</...
2
votes
0answers
66 views

Some SQL Server memory consumers keep stealing memory from “Total Server Memory” ? It keep decreasing without any external memory pressure

Problem Description : SQL Server Memory Usage keep increasing while Total Server Memory keep decreasing till ERROR: fail_page_allocation accoured and instance crash Version : SQL Server 2017(RTM-CU21) ...
1
vote
0answers
44 views

Confounding NUMA configuration in SQL Server for 24 vcore system in Azure

I need some help understanding how SQL Server takes a 24 core VM and displays it as 11 NUMA nodes under properties. At startup, this is the key message about hardware SQL Server found: SQL Server ...
1
vote
1answer
48 views

Login to local db with ip

I install sqlserver and login with SSMS with windows auth. and server = DESKTOP-XXXXX\MSSQLSERVER01 When I tried to connect with 127.0.0.1 ot 127.0.0.1:1433 I can't I put into TCP/IP but still I can'...
0
votes
1answer
49 views

How to fix CRC error when doing a SQL Server database backup?

I'm in a pickle and any help would be greatly appreciated. I'm running our office's ERP Software (Acumatica) on a local server with Windows Server 2016 and SQL Server 2017. Acumatica is currently ...
1
vote
1answer
27 views

SQL Server Transactional Replication between SQL server 2019 Standard and SQL server 2016 Web Edition

We have SQL Server 2016 Web Edition on production currently. Since this edition supports replication as Subscriber only, I set up a new server with SQL Server 2019 Standard edition. I want to ...
1
vote
2answers
19 views

Alternative to cross server query in SSIS

I need to do a cross-server query in SSIS and have been having a hard time figuring out how to do it. Here is what I am trying to achieve: SELECT Name, Email FROM Server1.dbo.table1 WHERE UniqueID IN (...
2
votes
0answers
32 views

MSSQL 2012 SP4 11.0.7493 periodically unresponsive until cluster failed over

I have a production MSSQL 2012 Standard SP4 (11.0.7493) database that periodically (non-deterministically, but anywhere from every few days to every few weeks) becomes unresponsive to queries made by ...
0
votes
2answers
49 views

Can't rely on INFORMATION_SCHEMA.view_definition?

I've built application that compares objects in the databases and generates scripts to replicate objects in the different DB. What I've noticed while I was using this to retrieve view code select * ...
0
votes
0answers
30 views

Manual SCSU 'compression' in SQL Server 2017 for NVARCHAR(MAX)

SQL Server has the wonderful SCSU "compression" gem that works on NVARCHAR(1-4000) types. The usage of SCSU effectively means that the physical storage space used is approximately that of ...
1
vote
1answer
25 views

Foreign Key Constraint into an different database

An SQL server hosts all the data for multiple entities (e.g sub-companies or organizational units OU). An "enterprise" database holds the "enterprise" data such as configuration ...
0
votes
0answers
27 views

How to migrate SQL2014 to Azure SQL in short time (scripting)? [duplicate]

Context: The new application is ready to be used (Azure SQL). The old application is still used (MS SQL 2014). The switch from the old to the new is to be done during one night. The size of data is ...
0
votes
0answers
28 views

Joining without a unique identifier

Payments PaymentRef Product PaymentAmount MerchantRef P01 ABC 100 MR01 P02 ABC 200 MR02 P03 XYZ 200 MR03 Refunds ReundRef Product PaymentAmount MerchantRef R01 ABC -10 MR04 R02 ABC -100 MR05 ...
0
votes
2answers
49 views

Clarifying delayed durability

I'm considering applying delayed durability in a system I work on and want to make sure I have the correct mental model in order to avoid data loss. My understanding is it basically means COMMIT ...
2
votes
1answer
99 views

<> does partition elimination?

We have a partitioned tables partitioned on STATUS Column using below partitioning function. So a table will have total two partitions and both are going to different filegroups as per partition ...
0
votes
1answer
21 views

Filter a SQL replication

I'm trying to replicate a SQL database but I want to apply a filter based on date. The database has more than 6000 tables and all of them have a datetime column (stamp) with the same name "...
3
votes
2answers
154 views

How can I check if one specific login has any database users mapped on it?

I've create some logins and database users. So I would like to accomplish the following. Drop user from database: USE [dbname] GO IF EXISTS (SELECT 1 FROM sysusers WHERE NAME = 'username') DROP ...
5
votes
1answer
91 views

Merge with multiple records at once

There is a piece of code, from a previous dev, that is doing a merge statement with variables, and processing one record at time. I'm sure the merge can deal with multiple records at one. create table ...
1
vote
1answer
49 views

Sum multiple columns, based on distinct values in different Columns

I have the following table, in an Azure SQL DB that has duplicate values that I'm trying to Sum. Here is the Logic: If the PaymentID is unique, then Sum Payment, If the creditID is unique, then sum ...
0
votes
1answer
28 views

Data Factory keeps getting “untrusted connection” error when logging into on prem SQL Server via IR on VM

We have: A local SQL Server database A VM with the Integration Runtime installed on it A Data Factory sharing this IR, working (according to the ADF interface) Logins stored in Key Vault for SQL ...
1
vote
1answer
32 views

How do I copy SQL Server Data Discovery and Classification rules and suppressed recommendations to another database?

We have hundreds of SQL Server databases, all of which share a common schema. We are implementing Data Discovery and Classification policies. We wish to create one rule set and copy it to all like ...
0
votes
1answer
38 views

Logical reads time 8KB more than memory on instance

I am working on an instance of SQL Server and was using Redgate SQL Monitor. I saw a query in the top 10 list with 1 execution which had logical reads more than the memory for the instance by a factor ...
0
votes
1answer
20 views

Setting Up Multi-Server Administration---SQL Server Account

I am trying to set up Multi Server Administration so that I can easily update multiple instances of the same database across our network with ease. Where I am getting lost is how to set up the SQL ...
0
votes
3answers
86 views

Safe and secure implementation of logon trigger in SQL Server 2014 Express edition?

I've to implement the following requirement: Access to SQL Server instance shall be allowed only from a C# application. Users shall not be able to access any database (even those in which they have ...
2
votes
0answers
37 views

SQL Server Failover Cluster — How to Fail Over Storage?

The picture helps explain what I am trying to do. I know how to setup an SQL failover cluster, did it and it works perfectly. Here is my situation : Node1 and Storage A is in one location (city) and ...
2
votes
1answer
98 views

Execute SQL conditionally based on database Compatibility Level

The Problem I have a script that I need to execute on both SQL Server 2016 and SQL Server 2019. If executing on Server 2019+, I want to use syntax that is only supported on that version (specifically, ...
2
votes
5answers
61 views

CREATE Windows User in database for not existing domain

Is it possible to create a Windows User in the database when the domain is not accessible? I am preparing database at my dev environment outside of the targeted domain. Created user is not expected to ...
4
votes
2answers
352 views

Delete rows older than x days without locking table

we have a quite big MS SQL database with millions of rows. I created a simple script to delete rows older than 1 month, but this seems to lock the table and creates trouble for the application. The ...
0
votes
1answer
18 views

Setting up SQL server multiple Names but within the same Database

Can we create different SQL Name (so will not change the previous connection strings) in a single SQL Server with the same content of the Database and log-in credentials?