Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.
0
votes
1answer
18 views
How to use JSON path wildcards in JSON_QUERY?
For a minimal working example say I have a column storing JSON with a structure similar to the following.
DECLARE @json NVARCHAR(4000) =
'{"Dims":
[
{"Name":"Apple", "Baking": ["Pie",...
0
votes
0answers
10 views
Integration Services Catalog folder permissions changed
Question: Do any of the SQL Server systems tables in either SSISDB or MSDB contain information that would allow me to discovered which users are making changes to folder permissions in the Integration ...
0
votes
1answer
34 views
Can't connect to MS SQL Server via IP address anymore
I'm using a PLC connected to a laptop (over EthernetIP). The development software for programming the PLC has a built in settings page for connecting it to various database engines (MySQL, PostgreSQL, ...
-3
votes
0answers
30 views
Simple query to find null objectid in SQL [on hold]
I need assistance with finding null object id's on our Database.
or doc id's that have no associated object id which ever will provide results.
trying to estimate data loss
I have tried
SELECT ...
2
votes
1answer
72 views
Refactoring subquery to JOIN and CROSS APPLY, get only row for each record in parent table
Given the following query:
SELECT
p.ProductName,
CASE
WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1)
THEN
1
ELSE
0
END
...
0
votes
1answer
36 views
Difference in duration of SP:Completed and sum(SP:StmtCompleted)
I've got a stored procedure, which some time show a big difference in duration of overall execution time and execution time each of its statements. Procedure consist of 4 statement and maximum run ...
1
vote
0answers
17 views
Querying columns on large dataset using XML causes sporadic missing data using relative Xpath selectors
We have a tool that serves our clients live events, the tool is used to place orders and over a few days processes hundreds of thousands items, so whilst its large, nothing unusual.
We have a ...
-3
votes
1answer
27 views
Need custom SQL Script to identify sql job unusual ran [on hold]
I have a requirement
like I have a SQL job i.e. DailysalesDetails.
This job baseline duration is 5 mins, today I am running that job, it took 10 mins.
Now, when job execution time crosses the ...
0
votes
1answer
27 views
Database Restoration Issue Msg 3156, Level 16, State 6
Recently while restoring a database with memory optimized filegroup I start facing an issue with below error message:-
Msg 3156, Level 16, State 3, Line 2 File 'XYZ' cannot be restored to
'D:\...
1
vote
1answer
29 views
SQL Server Mirror: Witness Server(s)
Inherited a new SQL estate that has never been touched by a full time DBA. Mirroring has been setup and removed on multiple servers but not fully, old witness servers are still active.
Is there ...
2
votes
1answer
37 views
Error publishing SQLCLR C# Function using Visual Studio 2017
I'm trying to (learn) publish a SQLCLR function using Visual Studio 2017. (It's a simple function that sends an email.)
As a reference I used this articles on CodeProject and MSSQLTips:
Create, Run, ...
0
votes
2answers
43 views
Does an OFFLINE Database use space? [on hold]
I took my SQL Server Database OFFLINE. Is it still occupying the same space as when it was ONLINE?
Should I keep it OFFLINE or is it better to BACKUP the database and DROP it?
-1
votes
0answers
34 views
Sox auditors, unreasonable conclusions [on hold]
We are going through our annual SOX audit. We are a small company and our servers are hosted at our parent companies datacentre. Our parent company is public and since they have to be sox complaint ...
1
vote
1answer
27 views
SQL Server 2014 Failover cluster - Nodes on different patch levels
As part of trouble shooting a performance issue with a SQL Server cluster I found out that the nodes haven't been patched properly to the same level. It is a 2 node active / passive cluster. SQL ...
1
vote
1answer
18 views
MSSQL database physical design: RAID or spreading filegroups across different physical drives?
I'm going to build a Microsoft SQL server database which could end up being more than 40 TB in size. There are some quite large tables in the DB with more about 1010 .. 11 records. Every table has ...
1
vote
1answer
29 views
xp_delete_file - error message when no files for deleting
I have a job that deletes transaction log backups older than 3 days.
if for some reason there are no files to delete, it comes out with the following error message:
Msg 22049, Sev 16, State 1: ...
1
vote
3answers
34 views
Setting a parameter from a statement that starts with EXEC
I am trying to get the Server Name from a LinkedServer and assign it to a variable.
From this question (Get @@SERVERNAME from linked server), I found that either of these return the Server Name.
...
0
votes
1answer
13 views
SQL Server 2012 installation error on windows server 2012 cluster
I have a 3 node Windows cluster. When I install SQL Server failover cluster, I'm facing an issue:
SQL Server 2012 installation error failed to get full path for folder
c:\document and settings \...
2
votes
2answers
71 views
Enlighten me about INCLUDE on indexes
Well, After reading Erik Darling's post Filtered Indexes: Just Add Includes I'm really curious and sad about how I use INCLUDES:
He gives some examples and etc. about INCLUDE, but I'm confused about ...
0
votes
1answer
38 views
How to identify columns order in a table, bis
So I'm asking a question similar to many but actually what I'm specifically asking hasn't been addressed. This one is the closest (How to identify columns order in a table) but not quite.
I have a ...
2
votes
1answer
52 views
Attach ldf more current than mdf
Is it possible to create a database where the ldf is more current then the mdf? Or to attach the older (more mature) ldf to the younger mdf (less mature)?
I am working on Steve Stedman's Week 3 ...
1
vote
1answer
55 views
SQL Server Backup failing for just 1 DB (“The system cannot find the path specified.”)
For the last few mornings, I have had a backup of our ReportServerTempDB fail. We are not using SSRS on this server yet, but will in the next month, so the DB is the default 8mb .mdf and 8mb .ldf (i.e....
1
vote
2answers
40 views
Load balancing Availability groups with SQL Server Standard
So I have the current scenario, and it looks to be working fantastically, but I just want to get some input on the configuration.
Is it smart? Are there any issues I am not thinking of?
We have SQL ...
0
votes
0answers
38 views
Cannot set up replication between two servers
I am trying to subscribe a 2016 (sp2, cu1) server to another 2017 server. I am getting the following error message:
For merge publications, the version of the Subscriber must not exceed the version ...
2
votes
2answers
33 views
When is the modify_date of an object updated?
As part of the daily checks I'm setting up, I'm restoring the nightly full backups to our test server and checking the modified date of objects. This is not for any kind of precise change tracking or ...
1
vote
1answer
35 views
How to copy all firewall rules and apply them on a new server?
I often install new SQL Server Servers. Be them production, test or development.
There is one thing I still have not automated and it is time consuming: the firewall settings.
I know how to ...
0
votes
2answers
53 views
Will frequent creation and deletion of TABLE (not indexes) cause fragmentation in database?
Does frequent creation and deletion of physical table in database causes fragmentation? I am frequently creating and deleting database for loading data warehouse load. I am using these table to hold ...
10
votes
2answers
1k views
Why is this faster and is it safe to use? (WHERE first letter is in the alphabet)
Long story short, we're updating small tables of people with values from a very large table of people. In a recent test, this update takes ca 5 minutes to run.
We stumbled upon what seems like the ...
-2
votes
0answers
16 views
Connection to SQL Db from Linux server [on hold]
How can we establish connection from a Linux machine to a SQL DB to execute a batch script
0
votes
1answer
22 views
MSSQL Agent Task Failing - Stored Procedure
I have a stored procedure that is giving me some issues as an Agent Job
When running the task, I get the following error:
Executed as user: SQLUserName. Access to the remote server is denied because ...
0
votes
0answers
17 views
DDLTrigger_Audit error “The user does not have permission to perform this action”
I have a DDLTrigger_Audit enabled in my database when I try executing a stored procedure having Drop and Create index statement in it fails with the below error.
database "XXX" failed on Jan 17 2019 ...
0
votes
2answers
34 views
Get data from both tables what join should I use
I have T1 as table 1
----------------
field1 | field2
---------------
val1 | val2
I have T2 as table 2
----------------
field1 | field2
---------------
val11 | val22
I want to write a query ...
-2
votes
0answers
30 views
How Sql query for tree from two tables [on hold]
table : Group
id parentId name
----------------
1 0 Category1
2 1 Category1_2
table : Depart_Group
id parentId name Group_Id
----------------
1 0 Name1 1
2 1 Name2 ...
0
votes
0answers
18 views
RING_BUFFER_CONNECTIVITY - LoginTimers - Error 17830 logs when releasing unused database connection in MS SQL
Good day, can anyone please help me with my problem?
I have this program that uses QLOLEDB (also tried using the MSOLEDBSQL) to connect to local sql server express 2014 (ver 12.0.2). The program ...
1
vote
1answer
19 views
How to set Parent field according to another hierarchy field
I need some help to set a parent field according to hierarchy field like this:
declare @Chart table([ChartId] int NOT NULL identity(1,1), [ParentId] int, [Number] varchar(5),
[Serial] varchar(5),...
0
votes
0answers
28 views
CHECKDB run times issue
When using the PHYSICAL_ONLY option and NOINDEX with CHECKDB, my run times are longer! But, on the weekend, it is 1/4 the time without those options. Yes, there are more data changes during the week ...
1
vote
1answer
34 views
Using LIKE for a Range of Numbers
In my WHERE clause, I'm trying to use LIKE for a range of numbers. For example:
WHERE rr.sequence LIKE '05600%'
OR rr.sequence LIKE '05601%'
OR rr.sequence LIKE '05602%'
OR rr.sequence ...
1
vote
2answers
51 views
Recovering a SQL Server database
In a series of unfortunate mistakes, I managed to lose 4 months of data and rapid database modifications. I’m not a database administrator, I’m a programmer that also designs and maintains a database. ...
0
votes
1answer
33 views
Rebuild index error: “The job was stopped prior to completion by User”
I have a rebuild index job that is set to run every night. The job used to run fine, but last night for some reason the SQL Server Agent Job was stopped. The rebuild index job was running on 9 ...
1
vote
1answer
59 views
Issue with a “weird” deadlock in SQL server
I'm working with a database that has the characteristics that follow:
GO
/****** Object: Table [dbo].[ADS_EVENT_TYPE_STR] Script Date: 1/17/2019 8:20:36 PM ******/
SET ANSI_NULLS ON
GO
SET ...
9
votes
1answer
117 views
Why can you only drop a SQL Server view in the current database?
Just noticed this and was having a hard time coming up with a technical reason for it.
Tables can be dropped using three part names, but views are restricted to two part names.
What is the ...
1
vote
2answers
52 views
Poor SQL Server Performance With Nested “OR” Conditions In LEFT OUTER JOIN
I have a tricky bit of SQL that's giving me a bit of trouble. I'd like to introduce several new columns to a key table whose components may or may not be used based on user configuration (Dimension's ...
5
votes
0answers
48 views
SHRINKFILE Failure - Why does increasing file size resolve it?
I am running some SHRINKFILE operations to clean up a bunch of tiny, unnecessary files in a filegroup. For one of the shrinks, the command below results in an error:
DBCC SHRINKFILE (N'myfile' , ...
1
vote
2answers
33 views
Reverse SQL Server Listener?
The company that I work for runs our DMS database in an HA group with Always On. So there is a listener that we connect any applications to.
We also have SSIS on the secondary replica of the HA ...
1
vote
0answers
29 views
Log shipping large database
I have Question on log shipping:
I am migrating a VLDB (12TB) SQL database from a physical environment (server A) to virtual environment (server B), and from version SQL 2008 R2 to SQL 2014.
The ...
2
votes
2answers
29 views
Storage pool configuration for SQL Server
We are in the middle of building a new SQL Server 2017 always-on cluster in Azure VM. The data disk layout of VM are as follows
TempDb : 2 Azure Disk Read Cache
TempDBLog : 1 ...
-1
votes
1answer
57 views
What does the error “Ambiguous column name 'id'” mean? [on hold]
I have an error in my database when I am using right join in my query.
my table has a column name 'id'.
SELECT TOP 6 id
FROM dbo.tbldata
RIGHT JOIN tblimg
ON tbldata.id = ...
1
vote
1answer
112 views
Find the OverLapped (Common) Date Range from Group of Date Ranges
How can I find the Overlapped (common date range) from a group of Date Ranges given?
The following table contains events and programmes (EID and PID respectively)
CREATE TABLE #EventsTBL
(
PID ...
0
votes
1answer
29 views
Combination Identifier
I want to identify in one column those who are present in the data but there is limit to only 10 nests. Is there easier or some other way for me to get the result that I want?
I hope you could help ...
0
votes
1answer
35 views
Employee Shift Check - Nested Cursor
I have a query that returns the availability of an employee (only days they are available):
SELECT
emp_id,
emp_number,
area,
day_date,
shift_type,
start_time,
...
