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.
32,874
questions
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 ...
- 24.3k
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 ...
- 729
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 ...
user21491328
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 ...
- 29
-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 ...
- 21
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 ...
- 11
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 ...
- 99
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 ...
- 131
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 ...
Jacky.Chueh
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 ...
user20130987
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
--...
- 1
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 ...
- 1
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\...
- 897
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 ...
- 154
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