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