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,886
questions
0
votes
2
answers
17
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 ...
- 30.5k
-1
votes
0
answers
13
views
Count daily worktime and group per day (whole month)
how can I query a table with working times, so that it is possible to count worktime in hours (rounded to 2 decimal positions) on daily basis for one user.
The output is a calendar day per day the ...
- 975
8
votes
1
answer
2k
views
Change Tracking across several Tables
I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables.
Right now, we have triggers on each of ...
CommunityBot
- 1
1
vote
1
answer
902
views
SQL Server Linked Servers not connecting
I have configured linked servers with "connections will be made using the login's current security context", but can only make use of them when logged on to the server, not from another machine...
...
CommunityBot
- 1
3
votes
1
answer
418
views
Does ordinal position of columns matter for SQL Server replication
I am setting up replication on SQL Server 2017. Transaction replication with push subscriptions such that many smaller database are replicated into a single larger database for reporting.
The ...
CommunityBot
- 1
0
votes
4
answers
15k
views
How to take SQL Server databases backup using .bat script file with time stamp
I am using SQL Server Express edition, so there is no features for backup plan, I want to take databases backup using bat script, and create a scheduler to be run everyday. I have created .bat file ...
- 483
0
votes
1
answer
26
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 ...
- 161
0
votes
1
answer
1k
views
SQL Install Fail - 2012 on Win11 [duplicate]
Cannot use file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Template Data\master.mdf' because it was originally formatted with sector size 4096 and is now on a volume with sector ...
- 78.7k
0
votes
2
answers
30
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 ...
5
votes
3
answers
511
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, ...
- 78.7k
1
vote
1
answer
2k
views
SQL Server is terminating this session. c0000005 EXCEPTION_ACCESS_VIOLATION
I Got Error:
A user request from the session with SPID 183 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log ...
CommunityBot
- 1
0
votes
1
answer
606
views
.Net Core connect to MS SQL Server Database using AD user creds
I'm trying to connect a .Net Core application running in a Linux container to a SQL Server database. Our DBA's require us to use AD users to connect to SQL Server. Another team is using NodeJS and ...
CommunityBot
- 1
5
votes
1
answer
583
views
Reinitialize Table Values in SQL SSDT Unit Testing
I am creating SQL Server Unit Tests. We are testing various stored procedures.
In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (...
CommunityBot
- 1
0
votes
2
answers
258
views
Backup on Distributed AG setup
I have setup a distibruted availability group and trying to understand behaviour of backups on it. While I can take backup from primary replica of Primary AG, I can not take backup from primary ...
CommunityBot
- 1
3
votes
2
answers
914
views
SQL Audit and AlwaysOn Replicas
We would like to enable SQL Audit on a SQL Server 2014 database. This database is replicated to a read replica via AlwaysOn. We don't need, or want, the replica audited. If we enable audit on the main ...
5
votes
2
answers
4k
views
SQL Server database forum design sample
I am doing a design for a database using SQL Server 2008 R2, basically it is a simple database design for a forum (just like games fans forums). Fortunately I am struggling with it as I didn't find ...
CommunityBot
- 1
0
votes
1
answer
467
views
how to install mssql on to ubuntu
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1' : file not found.
pugazhl@pugazhl-LIFEBOOK-LH532:~$ # sqlcmd -S ...
CommunityBot
- 1
0
votes
2
answers
234
views
SQL Server effectiveness of mirroring partial database
Our database stores 200 TB of data. We've setup clustering options of two VM nodes with one storage but due to limited resources, we cannot replicate the entire database for disaster recovery (only ...
CommunityBot
- 1
3
votes
1
answer
741
views
Avoiding a full re-initialization when making changes to transactional replication
When I use the GUI to make any changes to transactional replication on SQL server, I live in fear of the "the changes you have made require a reinitialization of all articles" dialog.
I have had ...
CommunityBot
- 1
7
votes
1
answer
3k
views
SQLPackage : Import or Export SQL Azure BACPAC without users
I know how to use SQLpackage.exe. I can export from SQL Azure and create a bacpac. I can also Import to SQL Server via SQLPackage.exe.
I want to export or import without the users in the database. The ...
0
votes
1
answer
18
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 ...
- 19
1
vote
1
answer
6k
views
How to generate a self-signed SSL certificate for MS SQL server 2008 R2 using OpenSSL?
Using OpenSSL, I'd like to generate a self-signed certificate for usage with Microsoft SQL server 2008 R2. This might be unusual, but I'd like to learn what's going on behind the scenes.
I have ...
2
votes
3
answers
15k
views
SQL Server select columns which contain data
I'm working for a client to import data out of a generic off the shelf application database. If you can imagine a database that is designed by software developers that have no concept of database ...
3
votes
1
answer
1k
views
Trace flag 2861 and what a 'zero-cost' plan actually means
I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries?
The blurb says:
SQL Server typically won't cache plans for these trivial queries because the cost ...
- 68.5k
-2
votes
0
answers
28
views
SSMS 19 usage issues
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
2
answers
30
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-...
- 30.5k
0
votes
0
answers
21
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... ...
-1
votes
0
answers
28
views
Create linked server encrypted from Sql Server 2019 to 2008R2 using Microsoft OLE DB Driver 19
I have some SQL Server 2008R2 that I want to connect to.
I have force encryption set to NO, and some expired certificates.
From new SQL Server 2019, I want to make a new linked server connection.
...
- 36.7k
0
votes
0
answers
22
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
0
answers
9
views
Unable To Find Object Within SSMS Highlighted With Data Migration Assistant
There's a breaking change being highlighted in the 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 ...
- 11
-1
votes
1
answer
32
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. ...
- 68.5k
0
votes
0
answers
29
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
1
answer
2k
views
Data mismatch on Subscriber Database in SQL server transactional replication
We have Transactional replication set up between an OLTP Database server 'A' and reporting DB server 'B', where the Data from OLTP DB(Size 400 GB) "AR" is getting replicated on the server B as ...
- 2,171
0
votes
2
answers
687
views
What causes disk space growth from Informatica load onto SQL Server?
We've been dealing with huge disk growth on SQL Server from Informatica. After the load, the database grows to 2.4TB. After the database shrink, it goes to 1.05TB. What could likely cause this to ...
- 2,171
5
votes
3
answers
27k
views
Single DB shared across multiple servers
I've planned to host many servers (50+) each will have more than 20K users stay connected. There is lot of shared data amongst users (e.g. a user needs to refer data prepared by another user).
If I ...
- 2,171
1
vote
1
answer
291
views
SSIS failure flow error. The decision after a sequence container failure does not proceed with the correct flow
I have an SSIS sequence container that extracts data from individual source database tables into a Stage layer. However, I need to know when a given calculation crashes in order for the given error to ...
CommunityBot
- 1
1
vote
1
answer
5k
views
Adding a domain user in SQL Server Management Studio
Before I ask my question, let me mention that my SQL Server is installed on a member server of a domain (it belongs to the domain but is not an AD server).
I am having trouble adding a network user in ...
CommunityBot
- 1
0
votes
1
answer
266
views
How can I apply the same logic to multiple tables that have the same number of columns and data types?
I have multiple tables with identical number of fields (and data types).
Here are two examples (DDL to create these tables at the bottom):
Animal
AnimalGroup Name
----------- ---------...
CommunityBot
- 1
6
votes
1
answer
259
views
SQL Server Replication
We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table. Replication converted that to 7.5 million individual update statements. When my alerting ...
CommunityBot
- 1
2
votes
2
answers
54
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 ...
- 78.7k
0
votes
2
answers
789
views
SQL Server 2016 - TDSSNIClient initialization failed with error 0xffffffff, status code 0x80
After a windows server restart SQL instance does not start with following errors:
2021-11-19 05:43:39.72 spid10s Error: 17182, Severity: 16, State: 1.
2021-11-19 05:43:39.72 spid10s TDSSNIClient ...
CommunityBot
- 1
0
votes
1
answer
33
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, ...
- 139
1
vote
0
answers
37
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
2
votes
1
answer
5k
views
SQL Server - allocation unit size in Windows 2019
I'm about to install a SQL Server 2019 instance on a Windows 2019 machine and found out that there are new NTFS allocation unit sizes available - up to 2M.
I used to format partitions using 64k unit ...
CommunityBot
- 1
7
votes
1
answer
98
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 (...
- 78.7k
-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
0
votes
0
answers
62
views
Unable to install SQL Server 2019 on Windows 11
****I would really appreciate if someone can help me to sort this issue, really frustrating.
Action required:
Use the following information to resolve the error, and then try the setup process again.
...
- 1,459
2
votes
1
answer
91
views
Improving performance in a query with median
I have the following query that I would like to speed up, I ran it through the Tuner but it didnt suggest any new indexes so I am looking for some help to improve it. (using sql server 2019)
Actual ...
- 1,333
2
votes
1
answer
203
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
40
views
Delete query slow
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 ...
