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.
2
votes
2answers
13 views
How to query against exact values in XML column set
I have a table that contains 80+ sparse columns along with a column set column, this is a brief example:
DROP TABLE IF EXISTS #ColumnSet
GO
CREATE TABLE #ColumnSet
(
Id INT NOT ...
0
votes
0answers
19 views
SSMS 17.8.1 Error: Accessing SQL Server logs
I am experiencing an error when I am trying to access the SQL Server Logs through SSMS 17.8.1 for SQL 2017.
I click to expand the SQL Server Log folder in SSMS and I get the following error.
How can ...
0
votes
1answer
18 views
How to get the consolidated ServiceID with the sum of File Size associated with over an year
Can anyone please help me on below to get the consolidated ServiceID with the sum of File Size associated with it over an year.
Complete Code (For reference)
If(OBJECT_ID('tempdb..#temp1') Is Not ...
2
votes
3answers
28 views
SQL Server replicating data to an archive
I have the following scenario:
Multiple SQL Servers where production machines log about 80GB of data (mostly images) daily in a database (each machine has a server with one database). Since these ...
0
votes
0answers
13 views
Process to Archive off DB Backups in Azure Blob Storage
I'm looking for ideas on how to start on Archiving some of our SQL Server Database Backups that are on the Azure Blob Storage. It's getting to an unmanageable state. I'd like to drive this using ...
0
votes
1answer
21 views
Disabling TDE on AlwaysOn database
Which are the correct steps to disable (not removing) TDE on a AlwaysOn database in an availability group?
I found steps for removing TDE but that involves removing the availability group and ...
3
votes
1answer
62 views
Bad row estimate following Compute Scalar operator in plan
I'm struggling to understand where a row estimate is coming from in an execution plan.
Paste the plan link
declare
@BatchKey INT = 1, @ParentBatchKey INT = 1,
@QuoteRef varchar(50) = 'Q00018249',
@...
2
votes
1answer
30 views
T-SQL DATEDIFF_BIG - 469ms diff with currentmills and documented start-end constraints incorrect?
(NB I don't count as a db admin... I'm just using one, so please assume zero other knowledge)
My reference date is '02/04/2017 18:00:00.124', and for no particular reason, whilst I need the time ...
1
vote
2answers
49 views
SUBSTRING and IF statement
I have varchar column, its name is column1 and has records like 111232435-0 and 1123443530. The last digit is not always 0; it can be 0-9.
Some of them are digits with (-), other are digits. I want ...
4
votes
1answer
357 views
Cannot Start SQL Server 2017?
I have been trying to start SQL Server 2017 and been getting
'Not enough storage is available to complete this operation. '
Full Error:
TDSSNIClient initialization failed with error 0xe, status ...
1
vote
0answers
17 views
Precision for average_disk_seconds_per_read?
In SQL Server Extended Events, event = perfobject_logicaldisk, what is the actual unit of time for average_disk_seconds_per_read and average_disk_seconds_per_write?
All of the documentation I've ...
0
votes
0answers
31 views
Split Excel row with sql
I'm storing some data with this SP from a xls file. The fields are separated by " \t " , but recently I have been receiving files without escape sequencers.
I need to find a new way to split my ...
0
votes
0answers
33 views
Data modeling with the goal to get the best performance in Oracle and SQL Server
I have a question about how I can model my stock database in order to get the best performance possible.
In SQL Server or in the Oracle, each update executed generates a little lock.
I'd like to ...
1
vote
1answer
40 views
Simple recursive CTE stuck in infinite loop
I have a need for a recursive query to find daisy chained related data. I have the following query
CREATE TABLE #source
(Parent varchar(10), Child varchar(10), Date datetime)
INSERT INTO #source
(...
-2
votes
0answers
18 views
Update column based on value in a different table
I am trying to update columns with counts based on values in another field. I have tried a couple of different things but am not getting the results I expected.
First, I have created an update query ...
1
vote
2answers
38 views
Creating Conditional Rules for Column Values
I am currently working on the following table in SQL Server
Table: order_status_logs
log_id INT NOT NULL IDENTITY(1,1)
order_id INT NOT NULL
status_id INT NOT NULL
log_date ...
-5
votes
1answer
56 views
Can data size be less than index size in sql database?
Recently I came across a question like , Can a SQL Database table contain index size greater than actual data size ? I have gone through few articles and studied bit on this and few experts ...
2
votes
0answers
20 views
SQL Server 2017 Failover Cluster on RHEL Linux
I am trying to create Failover Cluster on SQL Server 2017 on RHEL 7.3 Linux, following this document from Microsoft:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-shared-disk-cluster-...
-2
votes
3answers
62 views
What are multiple greater/less than in TSQL? [on hold]
I was reading an article at www.codemag.com/article/1208111 and saw cases in the WHERE clauses where columns were compared with
Column1 <<<>> Column2
And
Column1 <<<= ...
0
votes
2answers
38 views
how to run once a bunch of generated scripts with exception handling?
Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists.
So I created a script that calls a procedure that will save the contents of this table ...
1
vote
1answer
44 views
Generating maintenance database from maintenance tables currently in master and msdb
I want to create a maintenance database that i can clean up master and msdb, i am trying to collect all the table info from master and msdb that are not Microsoft tables, bearing in mind that some ...
3
votes
2answers
226 views
Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups
I am having trouble understanding what exactly to expect from the CleanupTime option in the Ola Hallengren Server Maintenance Solution. I'm finding some related questions, and elaborate answers, but ...
-3
votes
1answer
41 views
Updating a string field by concatenating 2 int columns
I have 2 columns, an int and a tiny int, and I want to update another column, a varchar one, with the value column1 + '-' + column2. Something like this pseudo code:
UPDATE COLUMN3 varchar = CONCAT (...
1
vote
2answers
448 views
Efficient way to move 170 GB table from production to another server
I have a 170 GB table that is no longer referenced.
I want to move that table to another server. I tried these options in staging:
select into 8 GB table to another db - 1.47 min
SSIS package - 6 ...
-1
votes
2answers
43 views
SQL Server, Error: Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259 [on hold]
I have the below query results to attach and email:
-----------------------------------------
EXEC [msdb]..sp_send_dbmail
@profile_name='SMTP',
@recipients='xxxx@yyy.com',
@subject='DB Mail',
@body='...
0
votes
1answer
13 views
SQL Server 2016 Availability Group on DPM 2016
I'm trying to backup my availability group in SQL Server 2016 using DPM 2016, but after I add the cluster in DPM, availability group show nothing to back up. I've set every secondary node to be ...
6
votes
1answer
548 views
What Happens to dirty pages if the system fails before the next checkpoint?
Assuming a database using full recovery model, when a record is written in SQL Server (by INSERT / UPDATE etc) write ahead logging will ensure the change is written to the log file before modifying ...
0
votes
0answers
31 views
Could not connect to SQL Server 2008 [on hold]
Tried doing this but could not solve my error
Go to SQL Server Configuration Manager
Start->all program->SQL Server 2008 R2->Configuration Tools->SQL Server Configuration Manager
Explore ...
-3
votes
0answers
36 views
Arabic question mark [on hold]
Kindly I have stored an Arabic language on my database , but it is showing as question mark, How can display clear text.
0
votes
1answer
42 views
Instead of C:\SQLDBA\ServerName\Instance rather C:\SQLDBA\ServerName_Instance
I am trying to create a folder based on the servername\instance to backup up to, i want the folder to create as servername_instance. Could you please point out where i am going wrong as it creates as ...
0
votes
1answer
11 views
Access Database Over Network (accde) [on hold]
I have been creating an access database for my assignment. I am using SQL SERVER 2014 for the datasource for the access tables. Now the problem is that according to the assignment
i need to make ...
1
vote
1answer
97 views
Split datetime records that fall between 10pm to 7am into new record [on hold]
I am very new to SQL Server. I am trying to split time in time out records that fall between night shifts from a employee shifts table but I am having a difficult time doing so. Night Shift runs from ...
1
vote
3answers
44 views
Cant restore database from A to B using any method in SSMS GUI
I have two databases. One is production (A), the other is development (B).
I use to be able to restore database from a backup.BAK file of A in B by using SSMS > right clicking databases > restore ...
-2
votes
0answers
15 views
maintenance plan updating table in different frequency shall not collide
Task is to update "main_table" with Content_A and Content_B. If both run at the same time they lock each other.
All queries work as expected and can be executed by maintenance plan (let's call them ...
0
votes
1answer
31 views
Ola Hallengren Index Maintenance with compressed indexes
If the Ola Hallengren index maintenance solution is installed onto a server which already has compression applied to several of the indexes, will the script respect the current compression level when ...
0
votes
3answers
46 views
Insert Values Into Table, Putting Duplicates In Another Table
So I posted this question yesterday. Some of the responses I got were helpful, however it seems my issue is a bit more complex than I originally thought.
After doing some looking the reason I was ...
1
vote
1answer
28 views
Is it good practice to have relationship between the tenant table and all other tables in a multi tenant application?
I am developing a multi tenant SaaS that uses Azure SQL for data storage. I make use of sp_set_session_context to make sure that any query only touches records relevant to the current tenant.
I ...
0
votes
0answers
35 views
Count() used in sql query giving #Error in SSRS report after report deployment on report server
I am newbie to SSRS and I am trying to display basic reports in SSRS. I have create a simple table with 1 column named "TotalNumber", whereTotalNumber display the count of total number of user in the ...
1
vote
1answer
44 views
Script SQL Server Agent jobs to file
I am busy making a backup of all Agent Jobs by scripting them all to file.`How would i rewrite the below to use BCP instead
BEGIN
SET @EchoCommand = 'ECHO ' + @EchoField + ' >> ...
5
votes
2answers
384 views
SQL Server: can dynamic data masking be safe when providing a database backup?
I have a SQL Server 2016 SP1 with data that I am ready to share with a partner (developer), except for a few columns, example:
email varchar(64) MASKED WITH (FUNCTION = 'email()') NULL
I give the ...
0
votes
1answer
45 views
Send email from SQL Server
I need to send email from SQL Server SP. Until now I used sp_send_cdosysmail. Is it better to use sp_send_dbmail? And the main question - which mail server is used by sp_send_dbmail - is it configured ...
1
vote
0answers
50 views
SQL Server was unable to communicate with the LaunchPad service
I have run the below code:
execute sp_configure 'external scripts enabled';
and it is giving me the output as:
name minimum maximum config_value run_value
external scripts ...
-3
votes
1answer
48 views
Data Structure transformation- MS server [on hold]
I kinda stuck at transforming the data structure, Please help!
This is what i have so far,
SELECT t0._id, t0.Category, MONTH(t0.TranDate) AS MONTHS, sum(t0.TranAmount) AS trans_total,
...
5
votes
1answer
117 views
Query is fast but becomes sluggish when created as a view
Execution Plan as Query
SELECT VP.Branch
, VP.ROUTE AS Route
, VP.SAPCustomerID
, S.CustomerID
, S.ProductID
, S.Date
-- Group by Customer
, CustomerQuantity = SUM(...
-1
votes
1answer
24 views
mssql : log locking attemps
I have an application with lot's of locking problems, I'd like to log detailed things related to locks in my mssql db. More precisely: I want to log not only deadlocks (I've found how to do that, it's ...
1
vote
2answers
67 views
Insert into Table ignoring duplicate values
I am having a brain fart on figuring this out. I have the following two Tables:
Table: parts
part_id INT IDENTITY(1,1) NOT NULL,
part_number VARCHAR(50) UNIQUE NOT NULL,
...
-2
votes
1answer
44 views
Some one please help with this script [on hold]
DELETE FROM Sql.dbo.Data DD
INNER JOIN
(
SELECT
TOP 100000 D.CLIENT, D.REFERENCE
FROM Sql.dbo.Data D
INNER JOIN Sql.dbo.Log P
ON D.CLIENT = P.CLIENT
AND D.REFERENCE = P.REF
WHERE P....
0
votes
1answer
46 views
Migrating SSRS from SQL Server 2012 to 2016
I have a requirement of migrating my whole SQL Server from SQL Server 2012 to 2016. I have already migrated the database engine part. Since I have not really worked on SSRS (reporting part), so need ...
33
votes
2answers
960 views
Why does changing the declared join column order introduce a sort?
I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique.
The test setup
Setup script, including some ...
2
votes
2answers
43 views
SQL Server benchmarking HammerDb TPM = 0
I'm trying to use HammerDb 3.0 to get a baseline to compare my servers.
I can create the Db, schema, data and items required no problem. I can execute the test and see that it's running.
When the ...
