Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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, ...