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

Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

1
vote
1answer
30 views

Converting a string to an object reference

Sometimes I end up in a position where I have a string version of my object reference like so: 'server.database.schema.table' And I need to use it as an actual object reference: 'server.database....
0
votes
2answers
38 views

Insert into a table selecting data from a sub query [on hold]

I would like to insert a few data into a table selecting data from a subquery having multiple joins. I tried the following way: insert into mytable (columns) select * from (select columns from ...
2
votes
0answers
42 views

Best Way to Index Multiple Effective Date Columns

I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an ...
1
vote
1answer
51 views

Last character in column shift to next column

I have a unique situation I have to fix. Our vendor gave us a data map and things are not mapping out as they said it should. My situation is, starting with a particular column, I need to drop the ...
0
votes
1answer
36 views

Trigger to decrypt the Encrypted data in a column while it's being inserted [on hold]

I have a table TestTableDestination(SerialNumber (varchar) , Name(varchar)) When I want to insert in this table , I'm inserting the encrypted data. What I want to do is to write a trigger with ...
2
votes
1answer
20 views

Created Check Constraint WITH NOCHECK - still trusted

I needed to alter the conditions of existing constraint - so I dropped it and recreated with the new conditions. This failed, because there were 800k+ rows, that didn't meet the condition. That ...
1
vote
1answer
30 views

Log bad call to stored procedure

I have an external application making a stored procedure call into a SQL Server 2017 database, via a normal database connection (PHP library (PDO?) of some form using the server, database, user, ...
0
votes
2answers
65 views

SQL Work Hours Only

I am working on a report, one of the requirements is that I find the turnaround time between two dates (columns) - I have done that using a DATEPART function, however, the hours are exaggerated, I ...
0
votes
1answer
35 views

Query performance diffrence between equals and between

I need to know why the correct answer is D ? what is the diffrence between D and A. Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores ...
0
votes
1answer
29 views

Database Reader Role with the ability to Create, View and execute stored procedure's and view's in sql server

I will need to allow report writers on the Subscription database to be able to view the table, create alter and delete stored procedure and views. Since it is a transactional replication they cannot ...
-2
votes
0answers
48 views

create a job which will run once a day at night and check for new 'Y' values in a table

I have a table inventory_receipts_line_ud inside which there is a field item_mast_uid which is a key and also a field named prop65 which is a 'Y'/'N' field. I also have a table inv_mast_ud inside ...
1
vote
2answers
64 views

Syntax error in SQL Server while making Dynamic SQL

Scenario: I have a table with 100M+ rows of data so I have created an index with something as: CREATE NONCLUSTERED INDEX FIBaseVoterWithDeletedAt ON dbo.base_voter (name_voter,...
0
votes
2answers
58 views

string_split into multiple columns

Sorry if this question is very basic, but I just can't figure it out, and couldn't find any good answers. I have a very long list of files, folders and Sizes. I need to Seperate the folders to ...
0
votes
1answer
40 views

DBCC CLONEDATABASE missing schemas

I have used DBCC CLONEDATABASE to create a copy of a database without any data. I have now found that some schemas were not created, thus some objects have no schema. Its hard to reproduce this as it ...
1
vote
3answers
42 views

Order implicit groups

Given this set... Site, Date 4 10/10/18 3 10/10/18 4 07/10/18 5 12/10/18 3 11/10/18 5 13/11/18 I want to order it such that the rows are ordered by site and the site groups are ordered ...
-1
votes
0answers
13 views

How to select desired columns from Excel file using OPENROWSET when headers are in persian Form (Farsi)

I have an Excel File with Persian (Farsi) headers and I'm using OPENROWSET to read the data from the excel file. The problem is that when I mention the name of the columns , the function does not ...
-2
votes
1answer
25 views

The result from Print (@SQLquery) differs from stored procedure exact result set

This is my query ALTER Procedure [dbo].[OpenExcel] @FileName Varchar(2000), @ExcelName Varchar(2000), @SheetName Varchar(100) , @Header Varchar(50) , @...
-1
votes
1answer
26 views

How to acquire a table result set from “Table - valued function” when the result set has variable structures?

I need to write a Table - valued function from which I can acquire a table result set with different structure each time. another thing is that I need to declare variables inside my function . I've ...
0
votes
1answer
60 views

Help to decrese query time in huge table of 100M+ rows on Ms-SQL Server

I am trying to get data using offset limit concept as follows: CREATE PROCEDURE BaseUserIndex @Offset int, @Limit int, @SortCol int, @SortDir nvarchar(10), @Search nvarchar(255) = ...
1
vote
1answer
22 views

Using “Where clause” In OPENROWSET as variable

I need to have a where clause in OPENROWSET but it is not always fix. It might be null sometimes. I have a procedure as you can see here : ALTER PROCEDURE [dbo].[OpenExcel] @TableName VARCHAR(2000) ...
0
votes
0answers
16 views

What is the best value for MAX DOP and Cost Threshold for Parallelism in a Datawarehouse [duplicate]

We are having much troubles on our ETL project in SQL Server 2016. The project keeps hanging on different tasks. After searching for possible solutions in the web I found that, in order to increase ...
11
votes
5answers
1k views

How do I limit a SQL stored procedure to be run by one person at a time?

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time. While this ...
1
vote
1answer
45 views

Get a list of Databases which were not modified with in a date range - SQL server [closed]

can we get an output for list of databases which were not modified for last two months in a server and based on that we have to delete all of them. I have tried below is the query: --identify when a ...
0
votes
2answers
28 views

Getting @@ROWCOUNT when there is an insert query in SQL-SERVER?

Hope you are doing well. I have a procedure as you can see below : ALTER Procedure [dbo].[OpenExcel] @TableName varchar(2000), @FileName varchar(2000), @ResultOpenExcel tinyint output AS ...
0
votes
1answer
36 views

DOWS meaning in T-SQL

I am reading a book which shows this example SELECT wait_type , SUM(wait_time_ms / 1000) AS [wait_time_s] FROM sys.dm_os_wait_stats DOWS WHERE wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP', '...
0
votes
0answers
24 views

Connecting SQL Server on Windows to a Samba share on Linux without Active Directory?

I'm testing a new setup that we're hoping to deploy in our small shop: using SQL Server on Windows, but connecting the database to a Linux SMB share. I'm using the latest SQL Server 2017 Developer ...
0
votes
1answer
17 views

Short circuit count query

I have a stored procedure that searches for rows given a long list of parameters. Some of the queries result in extremely slow count queries. Is it possible to stop counting after, say, one hundred ...
0
votes
0answers
49 views

Error in writing Dynamic stored procedure

I'm trying to write a Dynamic query to pass file name and table name to the procedure dynamically. what is wrong with this query? ALTER Procedure [dbo].[OpenExcel] @FileName nvarchar(2000), @...
0
votes
1answer
28 views

Receiving error while creating stored procedure to insert Excel files into a table

I was wondering if you could tell me what is wrong with my query? The parameter @TableName is an input parameter. Why do I have to declare it again? CREATE PROCEDURE [dbo].[OpenExcel] @FileName ...
0
votes
0answers
17 views

How to insert Excel files with different structures into different tables in database using SSIS and OpenRowset query

Hope you are doing well. I need to insert data from different Excel files With different structures Into different tables in my database. So according to this scenario I can not use 'Data flow task' ...
0
votes
1answer
54 views

Truncating a table with a foreign key constraint - SQL Server

I have two tables in a SQL Server Database, StudentAttendance and Attendance with the following structure: Attendance AttendanceID TimeTableID AttendanceDate UserName StudentAttendance ...
4
votes
1answer
282 views

What are the rules for using COLLATE in a query?

I am putting a script together, that gives me the permissions on database objects. SELECT permission_order=710 ,permission_type = 'Object Level Permissions' ,db = db_name(), login_=...
0
votes
2answers
72 views

Why is dbo the only grantor in Microsoft SQL Server?

I can't understand why is dbo the only grantor in SQL Server? Or am I wrong? I mean it as follow: No matter as which user I am currently loged in in SQL Server Management Studio, when I go to ...
0
votes
1answer
71 views

When duplicate date values exist in a column, change the value of the oldest

I have a query that returns records with duplicate Month and Year in a date field. I need to identify the records with duplicate Month and Year, then identify the oldest based upon the full date and ...
0
votes
2answers
54 views

Where do I use T-sql for SQL Server? [closed]

I have used SQL Server a fair amount but always with SSMS. I would like to be able to use all the capabilities and understand the environment better but I do not know where I use T-sql. I have tried ...
2
votes
2answers
159 views

How to group items by path (graph structure) and leave only those with highest score using t-sql?

I have a table with records and their scores (table 'Offers'): OfferId OfferScore ----------- ----------- 1 1 2 20 3 3 4 4 5 5 6 6 7 ...
-1
votes
1answer
36 views

Separating Numeric Values from string

I've looked at this link, but it does not really answer my question. Developing a Function for this would be ideal. I developed a trigger to insert the following into a table using one column: ...
-3
votes
1answer
50 views

T-SQL: how to use SUM() with GROUP BY

I have a very basic question: I have this code which works and returns me 23 rows: select Issue_ID ,sum(HoursSpent) as TimeSpent --,DateClosed from SixMonthsReview where StaffAssigned = 'Teddy ...
1
vote
2answers
291 views

How do I rewrite the below Oracle trigger so it will work on SQL Server?

I am migrating from Oracle to SQL Server using SSMA and I get thrown an unparsed SQL error on the below code. Can anyone help me convert this to T-SQL? Thanks in advance. CREATE OR REPLACE TRIGGER "...
-3
votes
1answer
43 views

How can I generate a column which is a combination of values from different columns

I have table with following structure. and I want to generate a row per day and a column per combination of first and second level category. The output should be like: Can anybody help me out in ...
-5
votes
1answer
40 views

please help me tune the below SQL Server query..its running forever [closed]

Declare @bst varchar(1000), @pst varchar(1000), @ost varchar(1000), @t_cnt int = 1; WHILE @t_cnt <= (SELECT count(*) FROM t_temp) BEGIN SELECT @bst = g_Search_Text FROM t_temp WHERE id = ...
2
votes
1answer
28 views

Finding the first occurrence where id don't match

I’m trying to find the first occurrence of SNum where the VerNum don’t match. In this case it should point to SNum = 300 as VerNum 12 is not present. DROP TABLE #temp1 DROP TABLE #temp2 CREATE TABLE ...
0
votes
1answer
40 views

Percentage calculation with case not working as expected

I've been racking my brain on this since yesterday afternoon, I figure it is time to call on those smarter than I. I have some data represented by the following. Sorry I have to use an image, I cannot ...
0
votes
1answer
61 views

INSERT/SELECT xml column from one table to another

First off I apologize for asking a question I'm sure is well answered, however I cannot quite understand what I'm reading. I have an existing table with an xml column. I have a second table with the ...
4
votes
1answer
67 views

Why we need to optimize CONTAINED IN clause of FOR_SYSTEM TIME using check constraint?

Using temporal tables one is able to use the FOR_SYSTEM TIME clause with combinations with other options to get valid rows for specific time intervals. For example, we have: FOR SYSTEM_TIME ALL FOR ...
0
votes
1answer
35 views

SQL Server: Pass table name into table valued function as a parameter

All of my tables have those two columns: [Id] [uniqueidentifier] NOT NULL [Revision] [bigint] IDENTITY(1, 1) NOT NULL Every other time I write a query, I want to eliminate some of the rows. The ...
1
vote
2answers
42 views

sp_spaceused reveals 0 rows but 72 KB of reserved space

When I run sp_spaceused dummybizo I get the following results: name rows reserved data index_size unused dummybizo 0 72 KB 8 KB 8 KB 56 KB I am expecting the ...
2
votes
2answers
91 views

Why is x <> 1 false when x is null?

I have a query where a row is selected when field1 <> 10. When field1 is null, this predicate is false! According to my primitive maths, if null <> 10 = false, then 10 = null. What is ...
3
votes
1answer
48 views

Replace all Numeric Values with Single Character

I have a value in a SQL table like the below: 80400365_SwapOpenTrd_20180831.csv I want to replace the numeric values so it is returned as this: %_SwapOpenTrd_%.csv I tried the below code but there ...
3
votes
1answer
49 views

Forced serialization on inline table valued function

Apologies if this is a stupid question. We are attempting to convert our multi-statement table valued functions (MSTVFs) to inline table valued functions (ITVFs), to avoid the forced serialization of ...