All Questions
Tagged with sql-server-2017 t-sql
35 questions
4
votes
1
answer
4k
views
Assigning values to multiple @variables using SELECT
I've encountered some T-SQL code which seems to rely on behaviour I found unexpected.
When assigning values to multiple @variables in one SELECT statement, when one @variable is dependent on another, ...
- 856
0
votes
1
answer
1k
views
Grant permissions only if an object exists, but without mentioning the object twice
We have a number of database build scripts that grant permissions to specific objects (sprocs, functions, etc) only to specific roles. We have recently moved to an automated deployment model that has ...
- 101
-2
votes
1
answer
3k
views
Row_number to not increment if values on three columns are the same from the row before
Suppose I have a dataset that looks like the screenshot below and I need to add a count column for the rows based off the orderid, DocumentNo_, and CurrPackageTrackingNo with the following logic:
...
- 617
-3
votes
1
answer
90
views
How to write select statment that get data based on partid from last date to current date with filling gapes?
I work on sql server 2012 i face issue i can't make select statment get Partid from last month until current month march (3) .
based on last date exist per partid
and on same time if there are any ...
- 147
0
votes
1
answer
166
views
SQL Server query consolidating each database file data in to one row
I have this Dataset base of this query below
SELECT DB_NAME() AS dbname , size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', ...
- 617
0
votes
0
answers
70
views
Product Version for Analysis Services and Report Server using T SQL
How can I find the product version for both analysis services and report server using Transact-SQL?
I'm trying to put together a query that will get me the details of each SQL instance with their ...
- 131
0
votes
0
answers
320
views
Calculate Start and End of Fiscal Period Weeks
I have a calendar table established (thank you, Aaron Bertrand):
SET NOCOUNT ON
IF OBJECT_ID (N'dbo.Dim_Calendar', N'U') IS NOT NULL
DROP TABLE dbo.Dim_Calendar
SET DATEFIRST 7, LANGUAGE ...
- 55
0
votes
1
answer
3k
views
Select rows where result appears more than once
I have been looking all over StackExchange, testing various methods listed here, without any success.
I need to SELECT only users that have multiple sessions.
This is my beginning SELECT statement:
...
- 113
-2
votes
1
answer
91
views
Delete millions of rows on a badly designed table [duplicate]
I have identified Millions of duplicated rows on a table I have inherited by using this query:
SELECT COUNT(*) AS NumRecords, AccessID, LEFT(SQLTEXT, 5000)
FROM Table
WHERE AccessID=5012
GROUP BY ...
- 447
1
vote
1
answer
519
views
Create a Sequence with the calling user as the owner
Background
I have a SQL User that has the following permission:
GRANT CREATE SEQUENCE ON SCHEMA::seq TO SequenceCreator
GO
I would like to be able to have this user create a sequence that is owned ...
- 2,540
1
vote
1
answer
3k
views
Update Rows From Table If Table Exists Without Using sp_executesql
I have a situation where I need to run a query on a large number of similar databases. Most of them will have a table that I need to update some rows in but some of them will not have the table.
I ...
- 113
2
votes
1
answer
324
views
Basic alternative to fn_LocalTimeToUTC
My cloud provider has prevented me accessing fn_LocalTimeToUTC and fn_UTCToLocalTime.
How would I go about getting the same functionality that these two functions offer manually and directly in SQL ...
- 123
1
vote
1
answer
460
views
Eliminate filter operator before columnstore index scan operator
I have a large fact table with millions of rows called MyLargeFactTable,
and its a clustered column store table.
There is a composite primary key constraint on it there as well
(customer_id,...
- 265
0
votes
1
answer
465
views
OPENROWSET Bulk insert Text File NULL Columns
I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the ...
- 431
1
vote
1
answer
134
views
Break down calendar table based on custom dates in SQL Server 2017
I have a table that contains the following fiscal year definitions:
FiscalYear | Period | PeriodStartDate | PeriodEndDate
==================================================================...
- 55
3
votes
2
answers
59
views
Find order that has deliveries in multiple weeks
I thought I had a simple run of the mill problem, but I can't manage to figure out how to slice it. Assume I have a table of Deliveries that has the OrderNumber, the DeliveryNumber and the ...
- 165
1
vote
1
answer
610
views
Adding a date and the time portion of a datetime2 together [duplicate]
I have a date field [load_date] and a datetime2 field [load_time] that I want to combine into a datetime2. I want to combine load_date with the time portion of load_time and create a datetime2.
It's ...
- 455
2
votes
2
answers
4k
views
How to check AUTO_CREATE_STATISTICS/AUTO_UPDATE_STATISTICS is enabled for database
I have a project database that maintain different databases from different environnement (dev, test, prod, predprod...), I need to check if AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICSis set to ...
- 109
2
votes
3
answers
1k
views
Operator used tempdb to spill data . The estimated row are less than real rows
I have a query that takes around 32 seconds, I tried many ways to fix the performance problem.
The first thing I tried is adding a new column that contain date and time as an integer. After I added ...
- 109
1
vote
1
answer
126
views
Compatibility level of databases based on sql server version
We need to check for databases that are not in the correct compatibility level on a particular instance. For example, on a SQL Server 2016 instance, if there are databases that are not in ...
- 11
1
vote
3
answers
4k
views
Database Mirror - Host can not be reached or does not exist
I tried mirroring database with GUI and T-SQL, but I had problems in both cases.
Here is some information about infrastructure and SSMS:
INFRASTRUCTURE
Principal server with SQL Server 2017 ...
- 324
0
votes
1
answer
345
views
Excluding records from SQL Query
I have a table which I am trying to return only records of rendition_type Interactive and no other associated rendition_type. If a records has more than one rendition_type, I need to exclude it from ...
- 1,133
4
votes
1
answer
170
views
How to recreate an Index with the same settings?
I'm in a situation where I want to add another included column to an existing index, but keep the rest of the settings (eg. FILLFACTOR, ONLINE, SORT_IN_TEMPDB, etc.) the same.
I do not have the ...
- 5,512
1
vote
3
answers
2k
views
Slow query in production, bad execution plan or bad index?
I've just fixed a production performance issue by dropping an index and recreate it.
I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad.
Arguments ...
- 111
0
votes
1
answer
328
views
Is it possible to restore a database from corrupted .mdf file?
I know that the answer of the question may vary depending on what exactly is broken in each .mdf file - only looking for advice if there is another way to approach this.
So, I have received some bad ...
- 4,318
5
votes
1
answer
226
views
Grant dbcreator only for databases matching prefix
In Microsoft SQL Server 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.
Is it possible to do this at the ...
user184141
8
votes
3
answers
3k
views
Why does this query, missing a FROM clause, not error out?
So we have a query with a subquery that has a typo in it. It's missing the FROM clause. But when you run it, it doesnt error out! Why!?
SELECT
1
,r.id
,'0D4133BE-C1B5-4141-AFAD-...
- 153
1
vote
0
answers
45
views
Repivoting rows of pivoted data with an dynamic amount of rows
I am trying to create an overall sequential pivot of various rows of data, repeating each row as a new set of columns and using the sequence number as the trailing identifier in each column name. Here ...
- 11
2
votes
1
answer
72
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 ...
- 123
1
vote
1
answer
4k
views
Inner join with greater or equal VS where condition using a variable
Suppose I have a table with a unique column and this columns has only integer numbers.
Eg: ID column with rows from 1 to 999.
My intention is to return only rows when my ID is greater than 500, for ...
7
votes
1
answer
2k
views
Does lightweight pooling disable built-in CLR facilities?
Running SQL Server in fibre mode (lightweight pooling) disables the use of SQL CLR:
Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "...
- 90.2k
1
vote
1
answer
1k
views
Python code in T-SQL environment gives "access a socket in a way forbidden by its access permissions" Error
Help on the following please.
I have this code Python Code
import requests
r = requests.get("https://www.google.com")
r.status_code
print(r.status_code)
print("Python Rawks!!!!!!")
which ...
24
votes
4
answers
5k
views
If a database only ever has one insert, is it bad to index every possible column combination?
I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is ...
- 365
1
vote
1
answer
957
views
On Insert Trigger To Create Email
I am wanting to send an email through the SQL Server Agent once an insert has been made to a specific table. I tried the syntax below, but the email is not being sent, nor queued to send.
ALTER ...
- 960
0
votes
2
answers
2k
views
Join returns no results [closed]
In a trigger, I have:
SELECT TOP 1 t.Id
FROM deleted d
INNER JOIN Table t
ON t.Id = d.Id
SELECT TOP 1 t.Id
FROM Table t
WHERE t.Id = (SELECT TOP 1 d.Id FROM deleted d)
Table is a related table ...
- 391