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
3
votes
3answers
149 views

Error 297, Severity 19 trying to create new table as sysadmin

I am completely scratching my head right now. I have two database instances that I am trying to add a table to, and both give me the same error: The user does not have permission to perform this ...
0
votes
1answer
128 views

How to troubleshoot “Encryption not supported on the client OLE DB provider ”SQLNCLI11“ for linked server” error

We are using LocalDb for integration/unit testing of a .NET Core application. I have been using this solution for months and recently started getting sporadic errors. It seems to happen the first time ...
-2
votes
2answers
56 views

Cost of reading and sorting 10GB table at cloud providers [closed]

Say I have a 10 GB table and I need to read it and then sort it by some column. How much does such an operation cost at cloud providers, say AWS or Azure if the pricing is variable? I just need a very ...
0
votes
1answer
39 views

Delayed durability for reporting database

I have a small database which basically run a couple of reporting SPs. Data gets extracted from an OLTP database and refreshed everyday and most tables in this database get truncated everyday. The ...
1
vote
1answer
335 views

Log Shipping Backup grey in Transaction Log Shipping Status

I have slow network that makes configure log shipping via GUI always timeout, so I generate script and run it in both primary server and secondary server. The scripts work fine but the primary ...
0
votes
1answer
36 views

Find if and when a Query was run?

I am trying to get to the root of a problem and need to see if and when a specific Delete Query was run on my MS SQL Server. The command would have come from my .NET website. My Server is Windows 2012 ...
1
vote
2answers
2k views

Unable to create a self signed Certificate for SQL Server 2017(14.x.xxxx)

Using SQL Server 2017 Developer Edition The command I'm using is: New-SelfSignedCertificate -CertStoreLocation Cert:\LocalMachine\My -DnsName host.doman.com -KeySpec KeyExchange -FriendlyName ...
45
votes
3answers
48k views

How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?

I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.
0
votes
0answers
82 views

Improving the run time of a DW query

I have a user running query which is sometimes under 10 seconds while other time going over 30 minutes. This is not a store proc , he is doing it manually from his machine to get data and later after ...
2
votes
1answer
73 views

SQL Server 2019 running 100% on only 1-2 cores with MAXDOP 8 to 14

Here is the background on this issue. I am inserting slightly over 1 million records into a table from within a C++ program using ADO. One row at a time is read in from flat file and converted info a ...
3
votes
1answer
3k views

SQL Server 2016 Linked Server OPENQUERY Hangs

I've been trying to figure out this linked server problem for about a week now with no luck. The set up I have is a linked server from an instance of SQL SERVER 2016 to an instance of SQL SERVER 2016 ...
2
votes
1answer
104 views

Allow user to GRANT certain permissions to others

I have tried googling this but may well be using the wrong terms... permissions and the like aren't my strong point. What I want to do is first create a role - eg 'Snr_Analyst' - with certain ...
0
votes
1answer
342 views

sp_BlitzIndex only results in one row (with priority -1) [closed]

Not sure why, but when I run sp_BlitzIndex (v4.5), it always results in only one row (even if set @GetAllDatabases = 1). This is the "header" row that shows Priority = -1. What am I doing wrong? I ...
0
votes
2answers
39 views

Extract from DB2 to SQL Server - where is the data in between the two servers?

I am trying to understand what happens when I extract data from DB2 from a SSIS package and load it into SQL Server. My question is: where is the data when it is between DB2 and SQL Server? On my ...
0
votes
1answer
26 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 ...
3
votes
1answer
53 views

Why is log reserve negative in the SQL Server compensation log when using fn_dblog command

I was using the fn_dblog command and was not able to get why the log reserve was negative.
0
votes
1answer
99 views

Error using SQL Server 2019 Import and Export Data (64-bit)

I'm getting an error immediately upon starting the SQL Server 2019 Import and Export (64-bit) wizard, and I'm looking for ideas. The error is: The SSIS Data Flow Task could not be created. Verify ...
0
votes
0answers
24 views

How To Allocate 2+ Balances Across 2+ Timeslots, Without A Cursor

Please refer to the following schema at SQL Fiddle when reading the use case below. In a timekeeping application I'm working with, there is a business process where individuals are entitled to a ...
0
votes
2answers
45 views

Merge two databases in SQL Server

I have some databases on an instance used for reporting. I want to merge them into one. There are many cross-database dependencies in the stored procedures (e.g. an SP can use tables from different ...
6
votes
2answers
1k views

Should we create a schema per “project”?

This is a database where users create a 'project' and work on it. We have a table for project and various other tables that contain different attributes for project (multiple rows for each project ...
1
vote
1answer
102 views

Can UPDATE lead to deadlocks in SQL SERVER?

Can we experience deadlock if run the same update query concurrently? Let's assume there's a table with millions of records and we need to update several thousand records at one shot. UPDATE TABLEX ...
2
votes
1answer
390 views

I can't create relationship in my database sql server

I have designed a database for my website. It has 3 tables: user, topic and comment. A user has many topics (or articles) A topic (or article) has many comments A user has many comments I ...
2
votes
2answers
5k views

Error 18456 Serverity 14, State 38

I have an application that logs into SQL Server 2014 that uses a single login to access the database when a user opens up the application. In other words I have 6 users using the same application ...
1
vote
1answer
275 views

Category entity relationship / subtype relationships design

I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to ...
-1
votes
1answer
22 views

Referencing a Column in the same table [duplicate]

Keeps telling me that I'm referencing SubTotal and I'm not sure how to fix that. The error message I'm getting is "Column CHECK constraint for column 'Total' references another column, table 'Job'...
1
vote
1answer
95 views

How to restore TDE encrypted DB on AWS (with certificate not stored in master)?

The way Amazon RDS enable TDE feature is to generate a certificate stored in master. However, I need to restore a database that is encrypted by another certificate. I have the cert file, private key ...
0
votes
1answer
29 views

Can an AD service account host SPNs for multiple SQL Instances?

Trying to set up Kerberos Authentication between two linked server to get past the double-hop issue. Server A is in a domain with several other SQL Servers. All SQL instances on all servers in this ...
0
votes
1answer
30 views

how to implement read-only rows or snapshots of rows

In my application I have objects with 3 levels of hierarchy. To model this in SQL Server, I have a ParentObject table with an Id column, ChildObject table with an Id and a foreign key ParentObjectId, ...
0
votes
1answer
21 views

mssql PERCENTILE_CONT group by year

I am trying to prepare a dataset for box chart which requires MAX, MIN, MEDIAN and quartiles grouped by years. This is my attempt, however I could not group it by years: SELECT years, MAX(...
0
votes
0answers
28 views

Auto running SQL script with Azure database server

I am trying to automate a report that is generated from the database on Azure database server. I am using below script in the batch file but I get the error. Script: osql -U firstname.lastname@company....
2
votes
1answer
36 views

Why do SQL Server auto-created statistics sometimes include the primary key?

I had always understood that SQL Server auto-created statistics only include one column. In other words, they are not multi-column statistics. However, today I came across some auto-created ...
2
votes
2answers
40 views

How to allow a certain domain user to create databases on a remote SQL instance?

I want to allow for a certain domain user to be able to create databases on a remote SQL instance. What steps are necessary and is it possible to perform those steps programmatically in a C# ...
0
votes
1answer
44 views

How to identify which connection is causing error 17886?

I need a way to identify which connection is causing error 17886. I've researched and there are solutions to fix the error, but only if you know which connection is causing the issue. I want to log ...
-2
votes
0answers
11 views

New member, can not post or up-vote solotions [migrated]

I've frequented these forums for over a decade, and decided i should finally participate. However the points system prevents me from posting anywhere but in this forum. Is there a particular way i ...
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 ...
-1
votes
1answer
30 views

How to checkdb automatically in AWS RDS?

I'm pretty new to Amazon AWS and RDS. I have migrated my SQL Database to RDS and I have it up and running. I'm now looking to automate maintenance tasks. Anyone with experience with RDS can help me ...
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 ...
3
votes
3answers
510 views

How to Exclude Foreign Keys during Publish action in sqlpackage

I am trying to publish a .dacpac extract to a target database, I want to exclude just the foreign key constraints in all the tables. After going through the ExcludeObjectTypes options I do not find a ...
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
5
votes
3answers
4k views

Port number change

We are planning on setting a static port for SQL Server 2005/2008 failover clustering. Any guide on what port to choose/select for 4 nodes of each cluster?(Active/Active) I am also thinking some ...
-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 ...
4
votes
2answers
351 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
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
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
1answer
265 views

azure db export to blob storage failed

i am trying to simply export BACPAC file from azure db to azure blob storage as mentioned here : https://docs.microsoft.com/en-us/azure/azure-sql/database/database-export but it is failed with error : ...
6
votes
2answers
9k views

Given the same input, is the HASHBYTES function guaranteed to return the same output?

If I use the SQL SERVER HASHBYTES() function in a script on my machine, is it guaranteed to produce the same output if run on another machine/instance? For example, will SELECT HASHBYTES('SHA1', '...
0
votes
2answers
44 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
1answer
466 views

SQLServer - Multiple PIVOT on same column

I have a table data like the below with Type Regular/Irregular from January to December. I need a query to do this data like the below table: January with below divided into two-jRegular & ...
25
votes
5answers
30k views

How to make SSMS upper case keywords

I recently started using Management Studio 2012. When using MySQL Workbench, a handy feature was that I could stay all in lower case and any reserved word (like SELECT, INSERT) would convert to upper ...