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.
29,397
questions
-2
votes
0answers
36 views
MICROSOFT SQL SERVER
I get this message when I am trying to run the query in Azure data studio
"Msg 8156, Level 16, State 1, Line 7 The column 'prod_cat_code' was specified multiple times for 'AA'."
What is ...
0
votes
1answer
20 views
SQL - Emptying Secondary Data File
We have two filegroup PRIMARY and SECONDARY. Objective is to merge SECONDARY partition with PRIMARY.
On running below command, following error is coming:
DBCC SHRINKFILE (N'ABC_Secondary', EMPTYFILE); ...
1
vote
2answers
18 views
Updating Database Email Profile for all notifications
I am creating an email profile for database email in a script that needs to be run on 200+ instances. The script will create a database email profile to send out alerts from agent jobs. Is there a way ...
2
votes
1answer
31 views
Can I change MSSQL interpretation of date order system wide?
I use Laravel with PHP from https://laravel.com/
MSSQL is a driver and works perfectly - with th exception of dates.
For some reason the project has decided that the follwoing is a universal date ...
1
vote
0answers
21 views
Is there a way to prevent SQL Server resetting itself back to read-write mode?
I'm working on an application which automatically attaches MDF files and connects to them in SQL Server LocalDB. I need to allow scenarios where each user has their own SQL Server LocalDB instance on ...
0
votes
1answer
40 views
Approaches to prevent database slowdown when another process is hitting it hard
I'm a developer not a dba, but this task was given to me...
One of our databases is hit hard by a process which reads and modifies hundreds of thousands records onece or twice a day, during the time ...
1
vote
2answers
84 views
Help with tuning this UPDATE Query or possible re-write
I have below SQL query which runs on avg. 40 secs and need some guidance where can i improve it or possibly re-write:
Declare @status_D INT,
Declare @StatusIP INT,
Declare @Status_F INT,...
-1
votes
1answer
21 views
Creating contained users for Azure SQL Database
What's the difference between these two commands?
CREATE USER abc FROM EXTERNAL PROVIDER
CREATE USER abc FOR EXTERNAL PROVIDER
2
votes
1answer
40 views
SQL Server 2014 only using 10 percent of its available memory? [duplicate]
Hoping someone can weigh in here. I have a 2014 developer edition box that doesn't seem to be performing well. I currently have 128 GBs of RAM allocated to the box and 120 GBs maximum allocated inside ...
-1
votes
0answers
34 views
SQL Server Error 945: database cannot be opened due to inaccessible files or insufficient memory or disk space
Machine: 2 Core 4 Gb RAM
Edition: SQL Server 2017 Express Edition
Error:
Error 945: database cannot be opened due to inaccessible files or insufficient memory or disk space
Disk Space: 45 GB Free
...
0
votes
0answers
9 views
How to restore SQL Server database to a particular time?
Is it possible for me to restore a SQL Server 2019 database up to a particular datetime? The database backup is full backup.
Thanks.
0
votes
1answer
15 views
MSSQL Logon Error in /var/opt/mssql/log/errorlog
Hello I checked in /var/opt/mssql/log/errorlog. then I get an error message like this:
The login packet used to open the connection is structurally invalid; the connection has bee
2021-01-22 16: 47: ...
1
vote
2answers
56 views
Query Cost when filtering with varchar vs nvarchar
Recently the application I'm working on encountered performance issue on a particular table. When I check the queries executing on this table, I noticed a warning about CONVERT_IMPLICIT on the ...
1
vote
1answer
20 views
What is VPS\example.com_web identity in SQL server?
I was facing connection failed for requested login in SQL server 2016 used for example.com domain on a VPS. I added the following logins to the database NT AUTHORITY\IUSR and NT AUTHORITY\NETWORK ...
0
votes
2answers
35 views
procedure not returning desired result
I am trying to generate a report from this procedure
create procedure [dbo].[hstatement]
(
@fromdate date,
@todate date,
@prd varchar(8)
)
as
begin
create table #tempdata
(
...
2
votes
2answers
75 views
cube and one example
if a data presented with 4-dimension in which each dimension is dependent to hierarchical 3-level aggregate like (country, city, street), then we can summarize it into 4096 ways !
we know for cube ...
-3
votes
0answers
41 views
using the “WITH … AS” function, how can I declare a variable? [closed]
I am trying to declare a date variable which will be reused a few times within the WITH... AS procedure:
DECLARE @DATA AS DATETIME
SELECT @DATA = 202101
WITH
TRANSACTIONS AS(...
1
vote
0answers
26 views
Linked Server to Hana DB
I am trying to create a linked server connection from sql-server to Hana DB. I have downloaded the Hana client and all the drivers along with it.
I have tried creating a system DSN and then adding the ...
0
votes
1answer
36 views
DB autogrowth issue
I have a database that will growth in 1 month time the autogrowth was set to 500MB but as I monitored it, the consumption is 1GB+ per day. Should I wait for it to auto grow or is it better to set the ...
0
votes
1answer
34 views
MSSQL - Insert Into adding data into the middle of the table? [duplicate]
A bit of a random one here... I've got a basic table that I'm using for temporary data inserts - and for that reason, I don't need or want a unique ID - however, I'm seeing odd behaviour! (or it feels ...
0
votes
0answers
10 views
AWS S3 IA as a filesystem for SQL Server VM [closed]
I want to archive billions of rows from a PROD db to a cheaper storage layer such as Amazon S3 Standard-Infrequent Access. I came up with the idea of just mounting the storage as a new volume and ...
-3
votes
1answer
36 views
while loop in sqlserver
my query is as below
@tdate as date='01-dec-2020'
while @tdate<'01-jan-2021'
begin
--logic
select * from (
select LBrCode,substring(PrdAcctId,1,8)+'000000000000000000000000' PrdAcctId,dt
--...
0
votes
1answer
24 views
One-directional database replication without delete on target database?
I have two databases: A small one where data is permanently added and a big one where the data is copied from the small one and is supposed to be stored long term. The small database deletes old data ...
0
votes
1answer
29 views
How to create a mapping table?
I have 2 tables as below
CITY_A
ID
CODE
CITY
001
90
PARIS
002
90
PARIS
003
85
PARIS
004
60
SYDNEY
005
75
SYDNEY
006
75
SYDNEY
CITY_B
ID
CODE
CITY
001
IN
PA
002
OUT
PA
003
OUT
PA
004
IN
...
0
votes
1answer
16 views
SSIS cannot deploy package to SQL server - An error occurred during decryption 15466
After recovering and re-importing a backup of a SQL Server 2019 instance none of my SSIS packages will deploy from Visual Studio.
Whenever I try and deploy I get the error
=============================...
0
votes
2answers
39 views
SQL INSERT INTO SELECT query for updating on same table with some modification
I want to update this table
Table name: Student
Name Department
Alex CSE
Bob EEE
Briyan ME
by copying all rows with some modification like this table:
Table name: ...
-1
votes
2answers
57 views
Purpose to use timestamp datatype in sql server? [closed]
What is the purpose to use timestamp datatype in sql server?
Do you have a concrete example of using timestamp in real business case?
When is it relevant to use timestampe datatype?
Thank you!
3
votes
0answers
84 views
Time to generate execution plan for select into from a view takes significantly more time than select
I have a view, vw_example, that is quite complicated and has multiple joins across multiple databases. This view has been causing significant delays when used as part of other queries, and we've ...
0
votes
1answer
41 views
upgrading SQL Server 2017 Standard to 2019 Enterprise [closed]
We are about to upgrade our SQL Servers from 2017 standard to 2019 enterprise. Does anyone have any real world experience on the best way to do this? We do not use HA, so our one prod server needs ...
1
vote
0answers
24 views
Every other SQL Server deployment fails due to access denied on Kubernetes [migrated]
I am deploying a SQL Server 2019 to Kubernetes with the following manifest:
apiVersion : apps/v1
kind: Deployment
metadata:
name: sql
spec:
selector:
matchLabels:
app: 'sql'
template:
...
0
votes
1answer
29 views
Importing files with a mix of decimal separators
Using SQL Server Management Studio 18.3 I want to import data (semicolon separated text file) that has some columns with dot as decimal separator, and some with comma as decimal separator. I can't ...
0
votes
1answer
40 views
Reasons For Slowness in SQL Server 2008 R2 Express Edition [closed]
We are observing intermittent slowness in our SQL Server. We ran sp_who2, sp_who3 stored procedures for checking CPU/IO usage but did not see any spikes/abnormal values. What else could be the ...
0
votes
1answer
41 views
SSMSBoost intellisense not working? [duplicate]
SSMS Version 17.9.1
It looks like the intellisense may not be working? Shouldn't it auto complete Select etc? Also, it looks like the intellisense is from SSMS itself (doesn't work that great)
I've ...
0
votes
1answer
40 views
ReFS vs NTFS - which is better for SQL Server 2019 on Windows Server 2019
We want to install SQL Server 2019 on Windows Server 2019; currently Admin has formatted Nimble Storage with ReFS. Is it really better than NFTS? I have seen other posts wherein people have mentioned ...
1
vote
1answer
59 views
Why does the log file grow larger than data file for an availability group database on the secondary replica?
I've got a SQL Server 2016 AG set up with only two replicas (primary and secondary). Both synchronous, non-readable secondary, and backup the primary only.
Transaction Log backups occur on the primary ...
4
votes
3answers
268 views
General Question on sp_recompile
For couple of scenarios, not many a times but few we have seen doing sp_recompile on a stored proc improving the performance.
Being a DBA i understand few of caveats involved when doing sp_recompile ...
-1
votes
0answers
44 views
SQL Querey Help
This querey was written, and I have to make it show the InvoiceFee.FeeAmount field. Currently, it does not show. It only populates Invoice.InvoiceDate, Invoice.InvoiceNumber, Customer.Name, and ...
0
votes
3answers
70 views
Maximize production of log records
I need to fill in transactional log file in possibly shortest amount of time.
I have tried a few variants of a loop of inserts and deletes on the same record. I fired it on separate tables in several ...
0
votes
1answer
28 views
sql server stored procedure - collection of data for fields as input [duplicate]
I have a stored procedure to add a collection of values to a field in table.
How I implement it now:
from my .net app, I create a string of comma separated values from an array( eg: resource1, ...
2
votes
3answers
259 views
Will SQL Server go offline if it loses network connectivity to SAN where master and msdb system databases reside?
I have a setup where 3 servers combined into Availability Group
All 3 servers have local directly attached SSD drives, and user database files are hosted on these drives
But the system databases (...
0
votes
1answer
39 views
Corruption on MS SQL Database due to 1 failed disk on RAID 6
Why is that when a single disk fails from a RAID 6 setup MS SQL 2014 database corrupts with the following error:
DESCRIPTION: SQL Server detected a logical consistency-based I/O error: torn page (...
0
votes
1answer
31 views
`Invoke-SqlCmd` does not display RAISERROR messages where `sqlcmd` does
I switched from sqlcmd to Invoke-SqlCmd, and suddenly my RAISERROR messages disappeared. When I run it with sqlcmd, they appear. Is there any way to make them visible with Invoke-SqlCmd?
RAISERROR (N'...
0
votes
0answers
32 views
Transaction was deadlocked on lock resources with another process and was chosen as the [closed]
I have table in SQL Server 2014. Please find the structure below:
CREATE TABLE [dbo].[ProductIMEISerialNoes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IMEI1] [nvarchar](max) NULL,
[IMEI2] [...
0
votes
2answers
45 views
The media family on device incorrectly formed for read only file group restore
Yes the databases (source and destination) are the same version. I have checked 3 times. Both are "Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64)"
The database ...
0
votes
1answer
19 views
How to load geometry point tables in sql spatial
Need help loading point data in sql spatial.
Similar to this post but I really dont know how to get XY data to be accessible by sql
I want to do what was done in the initial part of this post (...
0
votes
1answer
23 views
Creating row number for multiple columns?
I have the below data, and I want to only take one Location per Order ID ordered by the distance. This data set takes customer zips and compares to store zips and then returns the distance. I want to ...
0
votes
3answers
52 views
NOT EXISTS with two subquery fields that match 2 fields in main query
Background: Two different document types in a document management system.
Both Doc Type A and Doc Type B have a Ticket #, and a Ticket Date.
What we're looking for:
Doc Type A docs that don't have a ...
-3
votes
1answer
48 views
Arithmetic overflow error converting numeric to data type numeric (SQL Server). But the columns are big enough to contain data [closed]
We are receiving the following error when inserting our data into a SQL Server table:
Arithmetic overflow error converting numeric to data type numeric
The data we are inserting looks like this:
...
4
votes
1answer
180 views
SQL Server Long Running Transaction - WAITFOR(RECEIVE conversation…DatabaseMail)
I have recently implemented an Agent Job which checks SQL Server every 10mins for any long running queries and if detected it will send out a mail to recipients with the information. However since ...
5
votes
0answers
108 views
SQL Server 2019 service terminated unexpectedly at startup but manually starts
I've had a SQL Server 2019 server working great for the past few months, but it started stopping by itself since last Friday. This morning, I installed latest cumulative update on it.
Here is my ...
