Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.
1
vote
0answers
7 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 ...
-2
votes
3answers
59 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
0answers
14 views
Affected rows after update with OpenQuery
Is there a way to get the affected row count, when updating through an connection server?
I'm using MSSQL connected to MySQL with ODBC.
UPDATE OPENQUERY (MyConnection, 'UPDATE recordid, status FROM ...
-2
votes
0answers
43 views
TSQL compare the values of the same column cells without knowing their values [on hold]
I have a table which contains just two columns, it is X and Y type of int.
Column | Type
X | int
Y | int
This are coordinates. I should write a procedure now while I have no access to ...
2
votes
1answer
43 views
Stored procedure inserts 2 columns into a table with 3 columns that are not null, with no defaults on the table [on hold]
I struggled with how to ask this question. This is from a piece of software that we have used for well over a decade. For the life of me, I can't figure out why it works. There are 3 objects in ...
1
vote
1answer
65 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 ...
0
votes
1answer
55 views
Need help figuring out a daisy chain issue [on hold]
We have a goofy process (don't we all?) that daisy chains (or cascades) on a table that needs flattening out for our data warehouse.
The field "alt_key" is an identifier field. It ends up being the ...
1
vote
1answer
59 views
What permissions does HAS_DBACCESS check?
I assume that
SELECT HAS_DBACCESS('database name')
is equivalent to
SELECT HAS_PERMS_BY_NAME('database name', 'DATABASE', 'permission name')
for some permission (or set of permissions, the results ...
0
votes
1answer
70 views
Validate data between two large tables
I have this scenario. Data files are transferred from multiple stores (transactions) to the head office as they happen. For each store the table in question is pretty big in size. At the head office, ...
1
vote
0answers
16 views
File Extensions are missing from new SSMS 2012
One of my colleagues SSMS recently got updated to 2012 version from SSMS 2008 R2. After that they are view only a few editors in SSMS for adding different File Extensions
Tools ==> Options ==>Text ...
0
votes
0answers
26 views
Select Candidate Tables for File Restructuring / Migration
I have a VLDB with a default setup: a single MDF file in the PRIMARY file group. I want to create a new file group which will contain a few of the most high-capacity or highly trafficked tables found ...
0
votes
1answer
50 views
What to consider when generating rows via cross joins vs recursive unions? [closed]
These two queries return the same result (generating a sequence of integers):
1
2
3
4
5
...
16
What performance/other considerations should be taken into account when choosing one or the other?
A (...
2
votes
3answers
68 views
Eval Function TSQL
Is there a TSQL function that allows to validate an operation? Example:
Select (1 = 0) should return false while Select (1 = 1) should return true
or
Select ('a' = 'b') should return false while ...
2
votes
1answer
49 views
Why does join syntax with stacked on clauses work?
I have three versions of the same select query (SQL Fiddle here):
CREATE TABLE MainTable(ID INT)
CREATE TABLE SubTable(ID INT, Type CHAR(2))
CREATE TABLE SubTableType(Type CHAR(2))
INSERT MainTable ...
2
votes
1answer
39 views
How to determine index needed to remove hash match from exec plan
Sql Server Database on Azure (Sql Server 2014/12.0.2000.8)
I have numerous stored procedures that all had the same basic select properties and joins, so I decided to generate a view that encompassed ...
7
votes
1answer
346 views
T-SQL trigger to update columns
I've been working with SQL queries for a couple of weeks and I'm trying to learn and understand from my mistakes. I have a table with two columns IsInvisible (bit) and ShortName (string) and when one ...
1
vote
0answers
37 views
Creating 'temporary' memory optimised tables with only read/write roles
I'm currently trying to get an application to use memory optimised tables instead of temporary tables, but have hit an issue.
The issue is related to permissions, as users of the application are only ...
0
votes
1answer
38 views
Restore Log and Differential Backups Simultaneously in MS SQL Server
I have the full backup of a database, also one differential backup and one log backup. Is it possible to restore this 3 backups together? SSMS lets only full backup and only one of them. Is there a ...
0
votes
2answers
56 views
Getting only the necessary row
I am working on a query for a project and sometimes the results have multiple rows for the same entity. What I need to do is:
If the results look like this:
Type | Entity
----------------...
0
votes
0answers
28 views
2 Address ID fields in a table drawing from 1 Address Table
I am new to SQL world and I am creating a query in which I want to show SHIP_TO_ADDRESS as well VENDOR_ADDRESS. The driving table ORDER contains ADDRESS_ID for both.
All the addresses are stored in ...
0
votes
1answer
30 views
TSQL - Finding Similar Values
I have a static table containing filenames (StaticFilesTbl).
Each day a file is sent in that is stored in DailyFilesTbl.
The filenames in DailyFilesTbl will be different from StaticFilesTbl in that ...
0
votes
1answer
35 views
Is there a way to identify the object behind the database synonym?
I have a script which uses a synonym. I have to alter that code and the problem is I can't understand which object that synonym refers to. Is there a way to find out? I am using the original database ...
2
votes
1answer
60 views
Okay to run sp_refreshview against all views on a regular basis?
Microsoft SQL Server has a system stored procedure called sp_refreshview which updates the metadata of the view that's passed in.
Are there any downsides to running this procedure regularly on all of ...
3
votes
2answers
438 views
What are exact benefits of using R services on Enterprise vs Standard?
I am currently using SQL Server 2016 SP standard edition and want to install R services. In the documentation is said that a stand alone installation is allowed only on Enterprise (Machine Learning ...
0
votes
2answers
65 views
SELECT on column value = X but if no rows available then any column value [closed]
In MSSQL 2016 enterprise edition I have a table column which can contain an int. I'd like to query for 1000 rows where this column equal X, but if no rows then column may contain any value.
Eg:
...
-3
votes
0answers
39 views
Getting columns from more than two tables
I need to get a result showing each NetworkService that is connect to a Subscription with a number provided in a list and also that is of a NetworkServiceType that has a description that includes the ...
-2
votes
2answers
228 views
How to query system specs (cpu, memory, data disks, free space) using t-sql [closed]
Is there an ability to query below system specifications using t-sql ?
1. cpu (show number of processors / cores / frequency)
2. memory (show total amount of RAM on a machine; show allocated RAM; ...
2
votes
0answers
41 views
Is there a setting in SQL Server to keep triggers from firing SELECT @@IDENTITY
This is a SQL Server 2014 Server and we have application code the does a Select @@Scope_Identity after an insert into a table.
Recently other developers added triggers to this table and ever since, ...
1
vote
3answers
401 views
How to ensure data isn't inserted into a table where the data already exists?
I've created a query which I want to insert the results into another table. It is going to be ran as a job every day, and data imported will still be kept in the result set. I don't want the data to ...
2
votes
0answers
48 views
Elegantly handling 'enum-like' magic numbers in T-SQL code
We run Dynamics GP, and anybody that's dealt with GP will be familiar with all of its "magic number" columns representing the various enum values inside the application. For example, a reporting query ...
4
votes
1answer
215 views
Cannot create SP in Azure SQL Database
I am trying to create a stored procedure on my Azure SQL Database, which has joins to other database table in Azure SQL Database.
I am getting the following error while creating the Stored Procedure:
...
4
votes
4answers
582 views
Using IF statement after a WITH clause
I want to create an alias for a SELECT statement, but then I want to limit the resultset with a TOP clause. If the procedure TopCount parameter is bigger than 0, I want to put limit result set with ...
1
vote
0answers
18 views
Viewing directory of a mapped drive
I'm using the following query to view files/folders in a given directory. It works locally, but when trying to view a mapped drive I get...
"The system cannot find the path specified."
I have the ...
5
votes
1answer
74 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: "...
2
votes
2answers
379 views
Help With Merge Statement
I am wanting to compare the Distinct sn from HMI_Temp to sn in my table HMI -> if the sn exists, I want to update the values - got this syntax CHECK! - if the sn does NOT exist, I want to insert the ...
1
vote
0answers
38 views
How to list PDF Files names and path in a SQL Server Database
I need help on script to list PDF Files names and path in a SQL Server Database.
DECLARE @full_path VARCHAR(500)
SET @full_path = '\some\path\here'
DECLARE @FindFile TABLE
(FileNames nvarchar(...
1
vote
2answers
105 views
TSQL Remove Characters from Text Value
I have been using the REPLACE function to remove characters from a text value in a table. It has just grown over time and now the query is quite messy.
What can I do to make this more efficient? Is ...
1
vote
1answer
84 views
Increment each row according to condition in SQL C#
I have a value of 100 from a textBox and I want to increment that value in the stock_out column but only till stock_out column is equal to stock_in column, if first row value is 40 than first row ...
0
votes
0answers
38 views
Executing IF Exists condition
I have a sproc that scripts out permissions and in the process, I add a check to see if the object exists before dumping the results into a table. Then, I have another re-assign permissions sproc that ...
1
vote
1answer
57 views
Redundant data or UNION
I couldn't come up with better title.
I'm preparing data structure for GDPR and I ran into one problem.
I have two types of legal basis for storing the personal data:
Law
User specific (consent)
...
0
votes
2answers
41 views
Difference in Value between latest two rows?
I have a table as follows:
Id | Name | Value | RegisterDate
--------------------------------------------------
1 | name1 | 11 | 2018-01-01
2 | name2 | 12 | ...
1
vote
1answer
58 views
LOOP in a stored procedure to calculate sum
I'm trying to write a stored procedure that does the sum calculation on a field using parameters ID and date.
For this purpose I have two tables. My table that contains data
ID CAF SDATE
1 ...
0
votes
0answers
49 views
Extracting Service name from stored procedure
I have the following procedure
USE ServiceBrokerTest;
GO
CREATE PROCEDURE PublishNotification
@NotificationData VARCHAR(1000)
AS
DECLARE @InitDlgHandle UNIQUEIDENTIFIER
BEGIN ...
0
votes
2answers
35 views
Dynamically choosing column for WHERE clause
I've got a table that holds columns named "Day1" through to "Day31".
Is there a way I can dynamically choose the correct column to put in a WHERE clause?
I can get the current day, that's no ...
1
vote
0answers
24 views
Encrypt through Java and Decrypt through SQL Server AES 256
Am trying to insert some value through JAVA Application with AES Algorithm and need to decrypt the data from the SQL Procedure and need to process a data.
Which is the best practice to do so?
2
votes
1answer
33 views
PIVOT with SQL Server
I have SQL Server table data returning the below data. (Green text is a note on the value of each cell in 'Outcome' column)
I want to PIVOT that query result so that it uses the latest 'ReceivedTime' ...
2
votes
1answer
45 views
Use SPLIT_STRING with OPENROWSET
Is there a way to pass the result from OPENROWSET BULK into a variable so i can use STRING_SPLIT to divide it into rows?
Something like:
DECLARE @MyString = FROM OPENROWSET (BULK 'C:\Temp\File....
0
votes
1answer
44 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 ...
0
votes
1answer
186 views
Always Encrypted: How to insert or update encrypted columns?
I have configured 'Always Encrypted' on one of the columns of a table of my SQL Server database. I am able to select and view data from client SSMS after passing the 'Column Encryption Setting = ...
1
vote
3answers
110 views
If Exists - Select Prevent Repeating Code
We receive an order list, and validate it starts with certain prefixes, example if OrderNumber does not start with ABC, than display order list to user as error. We use exists, to search for first ...
