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

Get only column names from csv to ssms

DECLARE @sql NVARCHAR(MAX) DECLARE @filePath NVARCHAR(MAX) = 'C:/SomeFolder/yourImportFile.csv' DECLARE @tableName NVARCHAR(MAX) = 'yourTableName' DECLARE @colString NVARCHAR(MAX) SET @sql = 'SELECT @...
0 votes
0 answers
21 views

Linked server query occasionally returns results slowly

I have an unfortunate situation where I need to pull a next-number from a table from both a local and a linked server. To do so, I've created a stored procedure to return the result. When I execute ...
  • 294
0 votes
1 answer
18 views

Is it possible to update an existing table in a single statement, using the output of a MERGE statement? [closed]

We're splitting an old, wide table into two narrower tables, a parent and a child: create table WorkTable ( ParentId int null , ChildId int not null , ...
  • 334
0 votes
2 answers
62 views

SQL Server Connection String - MultiSubnetFailover True or Yes?

The MultiSubnetFailover option in database connection strings controls how the client attempts to connect to a database server, especially when multiple IPs are available. There is conflicting ...
  • 1,831
7 votes
3 answers
511 views

What is the stride of a GAM interval

For interest, I was reading about the internal structures of a Microsoft SQL Server file. It's fairly obvious how IAM pages are connected. However, it is not clear to me where later GAM pages are to ...
5 votes
1 answer
59 views

Stuffing a datetime value into a datetime2 column

Today I saw the following statement in a code review: ALTER TABLE dbo.MyTable ADD CreateDate DATETIME2(0) NOT NULL DEFAULT GETUTCDATE() The GETUTCDATE() function returns a datetime and is then ...
0 votes
0 answers
47 views

help with windows authentication on remote client accessing sql server 2022 with ssms

I have installed SQL Server 2022 on Windows Server 2019 Datacenter, and applied the March SQL Server 2022 Cumulative Updates. Also installed on the server is SSMS 18.12.1 and 19.0.2. On a remote ...
4 votes
1 answer
226 views

Do I really need a backup when using REPAIR_ALLOW_DATA_LOSS?

Reluctant DBA here. Do I really need a backup when using REPAIR_ALLOW_DATA_LOSS? I am well aware MS recommends doing a backup. What's the worse that could happen if I run a DBCC CHECKTABLE using ...
  • 141
0 votes
0 answers
49 views

Preventing SQL Server database users to modify other users or roles

I think this should be simple enough to do, but for the life of me I am struggling a lot to find a solution. SQL Server 2019. This is a lab and I am using the AdvertureWorks2019 database. I created a ...
2 votes
1 answer
166 views

Explicitly define a column collation even if it's the database default

SQL Server seems to go out of its way to remove a COLLATE restriction on a nvarchar column if the collation is the DB default. My DB default is SQL_Latin1_General_CP1_CI_AS. If I create a table thus:...
  • 673
4 votes
1 answer
132 views

Unable to restore database with datetime2(0) in Partition Function

I have a SQL-Server database with large table that partition by a datetime2(2) column. Some (old) filegroups marked as READ_ONLY. Periodically I make the backup with READ_WRITE_FILEGROUPS option. I ...
  • 43
-1 votes
2 answers
45 views

Remote Access to SSMS using SQL Server Authentication [closed]

I am having an issue logging remotely into SSMS (SQL Server Management Studio) using SQL Server authentication. (I am using SQL Server 2016) I am able to login locally using SQL Server authentications....
  • 205
0 votes
1 answer
22 views

SSISDB - Create Catalog Greyed out

We are in the process of migrating our dbs from SQL 2016 to SQL 2019. I was having issues restoring the SSISDB on a dev server so decided to start fresh and deploy packages on new server instead. I ...
0 votes
0 answers
25 views

How to prevent log_reuse_wait_desc REPLICATION stage and log growth

Not a unique problem here. I'm more so asking for advice on how to prevent this issue. We're using a nightly snapshot replication job to offload data to our data warehouse. This job has been setup and ...
  • 309
0 votes
2 answers
39 views

Find if AD group login is in use or not

Is there a way to find out if AD group login is in use or not? Basically I am trying to find out which logins are used and decommission ones not in use. Note that it isn't possible to decomm them by ...
  • 1,810
0 votes
0 answers
28 views

two servers have linked to the same server - one connection is encrypted and second not - why?

I have some old SQL Server 2008r2 instances that do not have client certificate set up and force encryption is set to FALSE. It is on Windows 2008 r2 and really hard to upgrade - for now it must stay ...
  • 101
0 votes
2 answers
36 views

merge or delete then insert or delete then append when the database is unstable

What are the best practices when it comes to modifying a huge (more than 500 M records) table in Azure SQL server in deployment. By modification I mean the table gets updated for past few days (let's ...
  • 103
0 votes
2 answers
51 views

T-SQL How to retrieve metadata from SQL proc containing temp tables

I've a T-SQL stored proc running in a Microsoft SQL Server 2019 environment. The proc contains temp tables, dynamic SQL and is 2000+ lines. The proc's output has been verified and tested for ...
  • 1
1 vote
2 answers
42 views

Sql Server Differential restore

Our network team is going to restore a server (ie the entire vm) with sql 2014 on it from a backup taken on Monday. This would include all database files. Out last full DB backup was taken the ...
user avatar
5 votes
3 answers
564 views

Regretting an identity: Is there a way to force inserts to specify the identity column?

To prevent an X-Y problem here's the actual problem we're trying to solve: The Problem: We have a bunch of lookup tables that were unfortunately created with an identity column on the Primary Key, ...
  • 235
0 votes
1 answer
22 views

SQL code that runs on Azure via GUI fails silently via pyodbc

Background: I am building a complex data analysis tool, and part of the process is to run some pretty deep and complex SQL from python. I am using pyodbc to connect to a SQL server instance hosted on ...
-3 votes
0 answers
36 views

SSMS 19 usage issues [closed]

SSMS 19 is really bad. When I've already connected to a MI's DB Engine, it prompts for credentials when I right-click and modify a stored proc. It doesn't do this for an on-prem server. Also, when I ...
  • 1
0 votes
1 answer
36 views

Restore a .bak in azure managed Database SQL

I have a database in azure bucket (.bak) that comes daily from another system and I want to create a stored procedure to restore a database. I have seen many guides about the command BACKUP RESTORE... ...
0 votes
0 answers
38 views

Why does `Get-ChildItem` require a call to `invoke-sqlcmd` to see `SQL SERVER:\SQL\...`?

PowerShell has a cool module for interacting with SQL Server. One of the features is the ability to navigate database objects like file system objects. For example: get-childitem -Path "SQLSERVER:...
  • 483
1 vote
1 answer
21 views

Unable To Find Object Within SSMS Highlighted With Data Migration Assistant

There's a breaking change being highlighted in the Data Migration Assistant tool Syntax issue on the source server about the syntax of an object however I am unable to find this object. I've searched ...
1 vote
1 answer
14 views

Always On Cluster, Network Problem, make failover or disconnections for nodes

have a nice day, I have a cluster sharing the same quorum resource, this quorum is a server with 2 share folders, one for SQL Always on Cluster, and other for SAP Always On Cluster, I have lot´s of ...
0 votes
0 answers
31 views

Tool to Run Sql from a table - with some bells and whistles [closed]

I’m looking for Sql Server tsql code that will let me run dynamic sql from a table. I need to generate and then run a large number of sql statements, which will extract data, clean it, and then apply ...
  • 109
0 votes
2 answers
46 views

Convert hex string representation to hex number in SQL Server T-SQL

I have been given a dataset where hexadecimal numbers have been stored as a quasi-hex string representation intended to be human-friendly, e.g. "16A1". Is there a conversion function in T-...
  • 371
1 vote
1 answer
45 views

How can I limit storage space taken up by `wpr_bucket_table`?

TL;DR: What is the wpr_bucket_table object in a SQL Server database, and how can we reduce the space it takes up? We have an Azure SQL Database that takes up significantly more storage than expected. ...
  • 21
0 votes
1 answer
36 views

Does Sqlserver store number of relevant decimals in a float field

I have a table in sql server containing a float field. This field displays a different number of decimals from row to row. I know for a fact that a float field CAN NOT contain the exact value 3173.77, ...
  • 1
-1 votes
0 answers
25 views

Script for IF EXITCODE ms sql

I'm trying to find a script that does a check at the end of a piece of code in the stored procedure, that script needs to check after all the query execution that everything was successful and ...
  • 21
2 votes
1 answer
213 views

Problem with IF NOT EXISTS query in ms sql

I want to script query to validate if not have existing column test to add it after check condition. But in those two ways it is not working. Way 1: IF [dbo].[TEST].TEST NOT EXISTS BEGIN ALTER TABLE [...
  • 21
-1 votes
0 answers
42 views

Delete query slow [closed]

I have a somewhat strange situation that I cannot figure out. I do have a msSql 2019 server, with a table that has 5 columns, the first being ID. It has about 1 million rows. There is a little .net ...
0 votes
1 answer
22 views

SSRS - Backup Encryption Key from SSMS

I have a SQL Server with SSRS installed. Note that SQL runs as NT Service\MSSQLSERVER SSRS runs as NT SERVICE\SQLServerReportingServices Version: SQL Server 2017 I am trying to automate the backing up ...
2 votes
2 answers
64 views

SQL Server 2012 Standard Edition rebuild index

We use SQL Server 2012 Standard Edition. Is there a way to rebuild the index, trying not to cause data table locking, and reducing the impact on performance? When we rebuild the index, it causes the ...
user avatar
0 votes
0 answers
47 views

How can I reset the Change Tracking Version of a SQL Server database to 0?

Is there a documented or undocumented way to reset the change tracking version in a SQL Server database to 0 ? Can someone share in which internal table or structure is the change tracking version ...
user avatar
0 votes
0 answers
31 views

SQLPackage Revoking and Granting Permission on Database Roles

I have a fairly niche question, but I am hoping someone may know a solution. I am using Visual Studio SQL database projects to source control our azure SQL DB's. As part of the project definition, we ...
-1 votes
1 answer
59 views

Creating a hierarchy from text

I'm trying to create a hierarchy from a text field I have in my table. Visually it is relatively easy to see who is parent and child. For example: (see a screenshot of the table results) 1M -----1M5 --...
0 votes
1 answer
63 views

Using "sp_execute_external_script" as alternative of "sp_invoke_external_rest_endpoint" in SQL Server

We have a business requirement to be able to request an HTTP end-point when certain events occur in the database (an SQL Server database). We have a catalog of end-points which we control. We use both ...
  • 3,721
0 votes
1 answer
41 views

Best BULK DELETE and Empty Space Recovery Options

What is the best option to recover empty space after running a bulk delete? DBCC Shrinkfile is useful to truncate the space. DBCC Shrinkdb is used to shrink the db files, but it leads to fragmentation....
0 votes
0 answers
43 views

MSSQL replication snapshot agent fails

I'm using MS SQL server 2019 on Linux. I'm trying to create a transactional replication. The problem is that when I create a publication, the snapshot agent can not start. Basically, I'm getting this ...
  • 1
1 vote
1 answer
52 views

How to move mdf file to other disk drive when it is "Standby / Read-Only" mode?

I am trying to change the location of mdf and ldf file from one disk drive to other disk drive due to storage limit. Currently, the database that I am trying to move to other drive is in "Standby ...
  • 205
0 votes
1 answer
48 views

How to change from "Read-Only" mode to "Standby / Read-Only" mode?

I changed the database (foo) mode from "Standby / Read-Only" to Active by using the following statement: restore database foo with recovery After that, I changed the mode back to Active by ...
  • 205
0 votes
2 answers
49 views

Why does (In Recovery) indication show up?

I just noticed that this database is currently in "In Recovery" mode. How do I change back to normal mode (without (In Recovery))? This is very active database, and I am trying to be very ...
  • 205
7 votes
1 answer
113 views

SQL Server Primary Key Column Statistics Histogram Suggests Duplicate Values

I have a statistic on a Primary Key column in a table. When I update the statistic with the default options: UPDATE STATISTICS dbo.MyTable PK__MyTable__CB394B3946083350 I get a histogram as follows (...
  • 1,809
0 votes
0 answers
23 views

Cache plan removal

I'm having an application which generates SQL queries from EF library. When looking into the query plan cache on the test server I see that plans are getting frequently removed from there. Plan ...
0 votes
0 answers
12 views

Does SQL Server 2016 GDR 13.0.6419.1 include 13.0.6404.1 Hotfix? [duplicate]

I know the GDR updates include all the security updates, but does it include the hotfix updates too? Should I update the hotfix and then the GDR or can I update the GDR right away?
2 votes
2 answers
356 views

Which SQL Server Manager should I use: C:\Windows\System32 or C:\Windows\SysWOW64?

I'm using SQL Server and I need to access the SQL Server Manager. However, I've noticed that there are two different paths to access it: C:\Windows\System32\SQLServerManager16.msc and C:\Windows\...
0 votes
0 answers
28 views

How to get the full query involved in a deadlock?

I have the following deadlock graph: The problem is that when I look into the queries involved, they seem to be two select statements that shouldn't be getting exclusive locks. After reading this ...
9 votes
4 answers
1k views

Does creating an index and then deleting it return the database to the pre-index creation state?

I'm testing some indexes and want to do a bit of A-B testing on which indexes perform better (primarily the order of the fields in the index and additional fields specified). I have a backup of the ...
  • 193

1
2 3 4 5
658