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.
28,126
questions
0
votes
2answers
7 views
How to Pivot Multiple Columns in SQL Server
I've read many posts about using pivot to get the data in the format I want, but the more I read the more confused I get.
I have this data:
That I'm trying to get into a format similar to this:
For ...
-3
votes
2answers
18 views
Need help with the following SQL query
I need help with the following output. Tried case, Distinct, Group By, failed. Join might help, but still couldn't write a query with join.
-4
votes
0answers
17 views
SQL Help needed [closed]
I need help with the query of the following output. Have tried Case, Distinct, Group By. So far not successful. Join might help, but still couldn't make any query with join.
1
vote
1answer
19 views
Foreign keys to primary tables or nested table
In this hypothetical example should the foreign key constraint setup for the ProductId and UserId columns in the ProductUserCommentAction table be referencing the Product/User tables as shown in the ...
0
votes
0answers
8 views
SQL Server 2019 Always On using Distributed Network Name
We're running a couple SQL servers in Azure that are set up with an Always On availability group and Windows Failover Clustering. The servers are Windows 2019 and we're running SQL Server 2019. When ...
1
vote
0answers
14 views
mssql suddenly cannot access physical file
SQL server 2019 on Centos 7 x86_64. It was running fine for quite long time, then got broken.
I was not doing anything special, neither update or restart or whatever. At some point the database went ...
0
votes
2answers
22 views
sum several results
DB Source
how can I add the result of 4 sum, to get this
I tried this query but the total is wrong, the total include eth05 colunm.
Any idea how to fix that?
select router, count(*) ...
1
vote
1answer
39 views
Select the first row returned from the join query based on a condition
I have 2 tables PRODUCT and MANUFACTURER
Table1: PRODUCT, Columns: PRODUCT_ID,PRODUCT_NAME
Table2: MANUFACTURER, Columns: PRODUCT_ID,MANF_ID,ITEM_NO,DEFAULT_MANF
I have to select the default ...
-1
votes
0answers
14 views
SQL 2000 Trace Querys [duplicate]
I'm trying to capture all querys and stored procedure in real time (or log file) for SQL 2000, i try with SQL Pofiler but it is not possible with such an old version.
-2
votes
1answer
28 views
Getting average in the where clause using alias subqueries in sqlserver
Hello I'm new at SQL and I've been asked to get the rent duration where rent duration is more than the average rent duration, but I can't seem to do it correctly. What am I doing wrong?
This is the ...
0
votes
1answer
28 views
Specific format/formula when changing column datatype
Can one modify a column's datatype using a very specific conversion format?
For example, when converting from datetime to varchar I'd like to use the yyyy-MM-dd hh:mm:ss format, not the system ...
0
votes
1answer
13 views
Getting the exact and closest Search using CONTAINS/CONTAINSTABLE in SQL
I have a table that contains 30 000+ records named as ProductCodes that contains a column 'Value'.
I have made a query that'll return the records matching and containing a passed String using ...
0
votes
2answers
31 views
query to find incorrect data
A table contains valid data and invalid data. Require query to find invalid data.
Valid Data: One locatorCode can contain multiple countyCode and when even one record with serviceEndDate = 12/31/9999 ...
0
votes
0answers
8 views
CMS Maintenance Plans not copying to TSX
I recently setup an SQL 2017 instance to act as a central management server.
I've deployed a number of dynamic jobs w/o issues, but when trying to deploy a standard maintenance plan, the plan never ...
-2
votes
1answer
44 views
SQL Profiler for SQL 2000
I need to use SQL Profiler for a Database running on SQL 2000. I currently use SQL Profiler 18 but when I attempt to connect I receive this error:
Client unable to establish connection
SQL Server ...
0
votes
0answers
13 views
MSSQL - Retrieving the top 5 time series within a query
I'm utilizing the Grafana site with MSSQL for my querying of time series and attempting to write 5 time series to a graph, my current query is
SELECT FLOOR(DATEDIFF(second, '1970-01-01', date)/1800)*...
-6
votes
0answers
22 views
This is an ER diagram of Sports Club Management System Of Cricket. Help me to make the schema diagram for this [closed]
This is The ER diagram. Need the Schema Diagram
0
votes
0answers
16 views
SQL Server: Cannot connect remotely after changing server name successfully
My local computer's operating system is Windows 10 Professional.
We have a remote server named XXXXAD01. Its operating system is Windows Server 2016 Standard. It is part of a Domain ISYYYY.ZZZZZ. ...
0
votes
1answer
18 views
Cannot drop the user because it does not exist or you do not have permission
I am getting this message for some users when executing sp_revokedbaccess procedure for database users. Users I am trying to remove are selected from sys.database_principals with type = S, W or G (sql ...
1
vote
2answers
22 views
SQL Server Express Edition feature and upgrade concerns
I want to install and learn T-SQL with SQL Server. The lightest edition that I can find is the Express Edition 2017.
Will the express edition support procedures, triggers, dynamic sql, PIVOT, ...
0
votes
0answers
9 views
In SQL Server, data returned by sys.dm_os_performance_counters for “SQLServer:Broker Statistics” is not changing over time
In SQL Server [Microsoft SQL Server 2016 (SP2-CU10) (KB4524334) - 13.0.5492.2 (X64)], I'm trying to determine just how much activity our Service Broker queues are processing. One tactic is to use sys....
0
votes
1answer
13 views
Chaining remote SQL Server Agent Jobs with success/failure feedback
I have 2 Microsoft SQL Server 2014 (SP3-CU4) instances, residing on different servers.
ServerA has a linked server, pointing to ServerB\InstanceB.
Both instances have a job, and JobA triggers the ...
-2
votes
1answer
30 views
Memory management intricacies for physical server
There are 2 purpose of posting this question:
Finding a process to determine the best possible value that can be used for setting MAX and MIN memory of SQL Server on bare metal?
Note: I know the ...
0
votes
0answers
17 views
Average Waiting Tasks Count / Resource Waits
I'm trying to help troubleshoot a sudden performance problem with SQL Server 2014 SP2 (12.0.5659.1). I don't have details on what exactly the scheduled stored procedures are doing, but I know that the ...
0
votes
1answer
25 views
Trouble with differential backup - full backup not being recognized
I have recently implemented backups using https://ola.hallengren.com/.
Backups
EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -%Archive', @Directory = N'H:\sqlbackup', @BackupType = '...
0
votes
1answer
18 views
SQL Server - SQL script to restore database backup file into a new database
I am trying to create a SQL script to restore a database backup file into a new database in SQL Server.
I can do it manually without problems:
Right click on Databases => Restore Database
Tick on ...
0
votes
2answers
19 views
Multi-instance 2-node SQL Cluster - do instances need their own disks?
I currently have a single default instance of SQL2017 Standard on a 2-node cluster.
Against my preference, I need to setup a second instance on the cluster (the new database requires some users to ...
0
votes
1answer
17 views
MS SQL Management Studio - Script of INSERT statements leaves empty chars for shorter than limit NVARCHAR?
I have just observed in MS Management Studio that if we want to get the script for table's inserted values the output includes the unused free data space which is in excess for the e.g. NVARCHAR data ...
0
votes
1answer
22 views
quoted identifiers on filtered indices
IĀ“ve 2 questions. I am using a T-SQL Server 2014.
When creating a table with a filtered index on a T-SQL server, it is a must-have to set QUOTED_IDENTIFIER to ON. Why is this so?
IĀ“ve some SPs which ...
1
vote
1answer
40 views
T-SQL query - how to AVG() the SUM() of columns' rows to a new row column?
What is asked is to find the average marks for each student comprised by the total of the marks divided by their count.
How should the query look like to achieve this, since the AVG function is an ...
3
votes
2answers
468 views
Index: Does 0% fragmentation imply a B-tree?
I am currently dealing with Microsoft SQL Server and its index structures. The data structures for this are trees.
When I reorganize a table index (e.g. the primary key), the result afterwards is 0% ...
2
votes
1answer
27 views
SQL Server 2019 performance worse than 2012… am I missing something?
We have a SQL Server 2012 server which far outperforms a SQL Server 2019 database on (as far as I can see) the same infrastructure. We are hosting both databases on a cloud platform with the same SLAs....
0
votes
1answer
7 views
WMI error: 0x800700a4 - The @wmi_query could not be executed in the @wmi_namespace provided
When trying to create a WMI alert in SQL Server, I received this error on 7 of the ~50 instances I was applying the change to:
SQLServerAgent Error: WMI error: 0x800700a4.
Msg 14511, Level 16, State ...
1
vote
0answers
20 views
Compatibility mode change does not reflect on secondaries
I was of belief that changing compatibility mode on primary database in database mirroring would make the change for mirror database.
But it proved to be wrong. I queried using sys.databases on ...
0
votes
0answers
22 views
TSQL Materiaized/Indexed View Cluster index with subquery
I have two tables: Listings and Media and Media is a child of Listings. I am trying to create a indexed view that will include the first record from Media. I would also like to index my view so that ...
0
votes
1answer
24 views
Question response time on StackOverflow
I thought that this was fun to share. I was wondering when should I give up all hope that my question would ever be answered and begin to start crying. So, I wrote a query for the StackOverflow DB ...
-1
votes
1answer
33 views
What value to set for MAX and MIN memory of SQL Server hosted on Bare Metal [duplicate]
Within AWS, Many of our Database servers are being migrated from EC2 Instance to Bare Metal. (Correct me, If I am wrong) In Bare metal, there are two memory limits available for the VM hosted on Bare ...
0
votes
2answers
46 views
Why is it important for every table to have a primary key?
So I have some tables in Sql Server that are essentially a list of sales, things like:
ProductID
SalesOrderID
ProductFamilyID
ProductCost
ProductSource
and so on. In this case, none of the ...
1
vote
0answers
22 views
DBCC CHECKTABLE - 2570 error
Running DBCC Checktable on one of my tables causes several errors:
Msg 2570, Level 16, State 3, Line 1 errors, Page (1:442), slot 1 in
object ID 565577053, index ID 0, partition ID ...
0
votes
0answers
20 views
Need help with JOIN I should be using as values are in columns are changing to find difference in tables
how can i compare text info using joins here as parentid & id of node will be the identifying factor for difference and also the text ?
columns are
RID ConfigFileID ID(xml child node) ...
0
votes
1answer
20 views
Querying an enormous table (~150 million rows) with multiple joins - SQL Server
First off, I don't have SHOWPLAN permission on this server or permission to add indexes, etc. I am not a database administrator by trade; I'm just trying to query a database. There is already indexes ...
0
votes
0answers
11 views
Ola Hallengren's Maintenance Solution - Huge Gaps of Time Where Nothing Happens [duplicate]
I have implemented Ola Hallengren's maintenance solution for index maintenance only. When I run it there are large gaps of time between index rebuilds/reorgs.
It runs for several minutes before the ...
1
vote
1answer
36 views
SQL Server infrastructure planning [closed]
Is there any general official Microsoft guide for planning SQL Server hardware? For instance, if you plan to have 500 concurrent Ńonnections you should have minimum 4 cores, 256GB of RAM, 15K HDD.
...
0
votes
0answers
12 views
SqlServer 2008 R2 LinkServer issue on Server A when inser data on Server B
I have two Server A and Server B.
Inserting data through Server B using trigger on particular table.
But showing below error:-
The operation could not be performed because OLE DB provider "...
-2
votes
1answer
16 views
ALTER DATABASE MODIFY FILE fails - I want to alter data file sizes using size = @variable - not working
Example:
DECLARE @sql NVARCHAR(255)
DECLARE @DBname NVARCHAR(128), @FileName NVARCHAR(128), @NewSizeMB INT
SET @DBname = 'AGTestDB'
SET @FileName = 'AGTestDB'
SET @NewSizeMB = 1024
SET @sql = N'...
0
votes
1answer
8 views
Snapshot Replication where Publisher is SQL Server 2008 and Subscriber is SQL Server 2019 not working
āWhen we are performing Snapshot replication where Publisher is SQL Server 2008 and Subscriber is SQL Server 2019. We are receiving below mentioned errors.
Error messages: The process could not bulk ...
0
votes
1answer
39 views
Same query but different plan on different server
I have recursive CTE query, it was taking 30s to execute, after investigation I found out that a column (Name) with datatype nvarchar(MAX) was the problem so I changed it to nvarchar(1024). After ...
0
votes
0answers
29 views
T-SQL Merge not updating in explicit transaction
We have the following code in a stored procedure which has a merge statement and an update statement to a different table in an explicit transaction. The merge update does not update any data in the ...
0
votes
1answer
23 views
can't find column stats for newly created table
I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached.
database current settings are:
I started with ...
0
votes
1answer
20 views
Azure SQL: Should Manual Indexes be Applied with automatic tuning?
Should Manual Indexes on tables be applied in AzureSQL Cloud Database? We read that AzureSQL does automatic tuning, so are manual indexes even required? We read, there can be cases where AzureSQL ...
