Questions tagged [t-sql]

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

Filter by
Sorted by
Tagged with
4
votes
1answer
144 views

SQL Server 2016 suddenly produces Error:402,State:1,Class:16 updating a sproc with DATETIME/DATE/TIME arithmetic, that was fine before

In one of stored procs I need to capture the duration of a live event. It is guaranteed to never exceed 24 hours, so the datatype for the result column is chosen as TIME(3) (mapping to TimeSpan on ...
3
votes
1answer
428 views

What are the .BCP files inside a .bacpak file?

Diving on dba.stackexchange.com I found a nice answer that teach me how to open a .bacpac file with File Explorer. I gave it a try with the database AdventureWorks2008R2 and all I did was: Save the ...
1
vote
1answer
55 views

Query to divide each column value to two categories: [Common , Not_Common]

I have a table with below structure and data: create table PTEST ( col_name VARCHAR(50), col_value VARCHAR(50) ) COL_NAME COL_VALUE ----------------------- first apple ...
1
vote
1answer
45 views

Separate Specific Indexes VS Single Inclusive Index

I have four tables, all of which have around the same large number of rows (except Customer): Shipping (PackNumber, PackLine, OrderNumber, OrderLine, CustomerID) Orders (OrderNumber, OrderLine) ...
0
votes
1answer
71 views

MSSQL Join to put rows into columns column variable as column name

I have two tables TABLE 1 PRODUCTID TITLE 001 Product1 002 Product2 TABLE 2 ID PRODUCTID VARNAME VARVALUE 1 001 url https://... 2 001 length 30m 3 001 somethingelse somevalue I am trying to ...
-2
votes
1answer
48 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 ...
1
vote
1answer
60 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 ...
0
votes
1answer
33 views

Calling function that takes user defined table type as input, in a select query, with data in the table type from the table selected from

Hopefully the title makes some amount of sense. Basically I have a scalar function that takes a user defined table as input, f_doCalc(@MyUserDefinedTableType). The table type has 2 columns, theValue ...
0
votes
0answers
149 views

Permissions to run SQL Server Agent Job

I am getting the following error message: Message Executed as user: fin\jdoe. The server principal "fin\jdoe" is not able to access the database "abc" under the current security ...
1
vote
1answer
178 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 ...
4
votes
5answers
792 views

Handling optional parameters in stored procedure

I have a stored procedure, say "Test" with an optional parameter @Param1 int = NULL. In the procedure, the value of @Param1 is used to update the value of some column in some table, if the ...
1
vote
1answer
41 views

Is there any way to monitor if all columns in an article is published in replication

In replication, if we add a column in Publisher then it doesnt replicate to Subscriber unless it is done explicitly. I am looking for a way to track or monitor this. I am not able to find any system ...
1
vote
2answers
55 views

INSERT or UPDATE the values from a table A into the columns of table B based on table A's value in its row?

I have a table which contains attribute names with there values but each row is a attribute with its value, ItemId ItemAttributeName ItemAtributeValue 405 Application High AmpacityConnections 405 ...
1
vote
2answers
96 views

Understanding the floor performance of a SQL Server snapshot restore

I'm currently working on understanding the performance of snapshot restores in my company's test suite. I've read a lot of material online about how the time it takes to perform a restore is directly ...
0
votes
1answer
52 views

What does this statement mean: SELECT ... COLLATE SQL_Latin1_General_CP1_CI_AS AS [-‌- Logins To Be Created --]

What does this statement mean? SELECT ... COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --] It is part of a script I'm trying to adapt for listing out the SQL Server logins in an ...
0
votes
1answer
29 views

Help in querying hash PK and FK

I was trying to get the name of the PK and FK. However, this is the output that I get: PK: PK__adm_lib___3214EC067E4F7CCA FK: FK__adm_lib_S__fk_Co__7093AB15
0
votes
1answer
59 views

How to modify my existing T-SQL query to work for a linked server?

I am using SQL Server 2014 and I need to modify my existing T-SQL query so that it works for a linked server. My existing codes are as follows: USE [MyDatabase] SELECT name FROM sysobjects WHERE id ...
-1
votes
1answer
65 views

Please can I get help with using T-SQL to loop through all user databases in a SQL server instance [duplicate]

Please can anyone help me with how to loop through all the User databases in an instance executing the below query for each one of them? The script returns a list of users with their individual ...
4
votes
1answer
152 views

Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow

I've updated this post with some test data. I'm creating a report for my Movies database where I'd like for the end-user to be able to select movies of a certain genre. However, some movies have ...
0
votes
1answer
171 views

Run T-SQL Script Using Powershell via SQL Agent Job

Tried to think of the best subject title; however having a problem getting this done. I'll give my preferred way, but open to any best method to get this done. I have a tsql script that is done, ...
0
votes
1answer
500 views

An expression of non-boolean type specified in a context where a condition is expected, near 'AND' [closed]

ALTER PROCEDURE [dbo].[usp_BaseScan_CycleCount_Report] @StartDate varchar(10), @EndDate varchar(10) AS BEGIN IF @StartDate AND @EndDate IS NOT NULL SELECT * FROM dbo....
0
votes
2answers
48 views

Adding Dates for Each Day of Stay

Let's say we have a small inn. People make reservations online. I receive a reservation number, the date they will arrive and the length of the stay. I would like not just the number of days of their ...
-1
votes
1answer
82 views

How to execute join statement dynamically without using static group id? [closed]

I work on SQL server 2014 I need to run this statement below dynamically without using group id static because I don't know maximum group id using may be 10,15,20 so How to run query below dynamically ...
0
votes
0answers
28 views

TSQL - Take a single row query and transpose based on field value

I'm trying to create a fact table with data I was given. We have a monthly goal number, but we want to be able to figure out how many weeks are in that month and divide the goal number by the weeks in ...
0
votes
2answers
121 views

Create Date Spans from Contiguous and Non-Contiguous Date Spans

I am trying to create a query that will return a series of dates based off of the 'BEG_DT' and 'END_DT'. The results should show a series dates based off of the 'BEG_DT' and with a span between each ...
0
votes
1answer
19 views

A column of database type "geometry" results in two extra columns hidden from ssms

If I create a table like create table test2 (foo geometry) foo will show up in the table designer as expected but when I query sys.columns for column information... select t.name as TABLE_NAME, c....
-2
votes
2answers
61 views

Refresh database list [duplicate]

Is it possible to perform a database list refresh in T-SQL in the same way as in the SSMS right context menu Refresh option? I've a 2014 Express instance with 197 databases inside. In my C# app I have ...
1
vote
1answer
63 views

When tempdb allocations are released?

In a Service Broker queue, when one message is proceed over time, we are executing a stored procedure which is performing some heavy operations using temporary tables. I guess that the sp_WhoIsActive ...
0
votes
2answers
47 views

Backup TO URL WITH NOINIT fails because file already exists

There's a similar question here but I cannot work out the solution because the circumstances are slightly different, so please bear with me; I've been wresting with the official help docs all day. My ...
0
votes
1answer
113 views

Comma separated value string and compare against other values

Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything. Here is my query: DECLARE @ATID AS VARCHAR(3) = 12 DECLARE @...
0
votes
0answers
25 views

Report server (SSRS) email delivery delayed for 10 hours

Weird thing happened the other day. Around 2 am there are lots of subscriptions fires on report server that sends emails. Users received some of them, but not all. Then around noon, all of the sudden ...
-1
votes
1answer
46 views

Microsoft SQL Server: TSQL Formatting of Results Columns

I have a result set of data that has duplicates because certain companies are tied to multiple banks is there a way to combine the values in the BankName column either into a single column. So the ...
0
votes
0answers
41 views

Dynamic SQL and AO AG - SQL Server

I have 3 servers with an SQL Server instance (S1\PROD, S1\PROD, S3\PROD) in my cluster and I have a couple of DBs hosted on these with AG AO. I have a lot of DBs and they have 2 replicas (1 primary ...
0
votes
1answer
514 views

Create an Azure Synapse temp table from a SELECT with ORDER BY

I have the following statement that works when I omit the ORDER BY clause, or if I run it as a select and omit the create table part, but I need both to ensure my generated key is sequenced correctly ...
4
votes
2answers
94 views

Multiple Set Quoted_identifier within one script takes the last one?

I'm using SQL Server 2019 and just found a weird behaviour. Research has not gotten me anywhere further. Can someone please explain this behaviour? SET QUOTED_IDENTIFIER ON; if ((256 & @@options) ...
0
votes
1answer
330 views

Database design for one payment and monthly payments

I'm working on leasing shops system and here is my database for now: I have contracts these contracts have 2 types, lease for a duration (year) and the payment will be once, and the other type is ...
1
vote
3answers
391 views

Tsql help - doing a large update from CSV file to a table in ssms

I have been given a csv file with just ids in there and those ids are in a table in ssms with columns that require updating. Out of 90,000 ids in that table, only 83,000 of them require updating from ...
0
votes
1answer
34 views

How can I apply the same logic to multiple tables that have the same number of columns and data types?

I have multiple tables with identical number of fields (and data types). Here are two examples (DDL to create these tables at the bottom): Animal AnimalGroup Name ----------- ---------...
0
votes
0answers
39 views

Understanding CPU time to measure workload

I want to understand the workload of my stored procedures, which run on an instance with many other databases. Among other things I would like to know if I had my own server how many CPU core would I ...
0
votes
2answers
73 views

Query a list of objects in each database

I have a list of about 300 Views spread out across several databases. Some of the tables used in the DDL of the views have been dropped or renamed. What I want to do is try to query all 300 views in ...
0
votes
1answer
47 views

Getting data from Postgres through Linked server of SQL

I am trying to get the data from PostgreSql db through linked server of the SQL Server. But getting the following error on running this query select * from openquery(SALESFORCE,'select closeddate from ...
1
vote
3answers
209 views

How can parse column names using ordinal position (not name) from XML data in SQL Server?

I have several hundred tables in a database that have the same structure: SomeId, Pos, Varying Number of Other Fields So, for example, one table may look like this: PersonId, Pos, Hobby, Degree 12345, ...
0
votes
2answers
66 views

Azure SQL Database Elastic Pools - TSQL to get current Elastic Pool

I would like to be able to fetch the name of the elastic pool that the current database belongs to using TSQL but the only DMV I can find with a column containing elastic is sys....
0
votes
1answer
42 views

CREATE FUNCTION inside BEGIN TRY and END TRY

I want to use CREATE FUNCTION in a block that will "swallow" errors, but this BEGIN TRY CREATE FUNCTION test (@ID int) RETURNS int AS BEGIN RETURN(2 * @ID) END END TRY BEGIN CATCH END ...
3
votes
1answer
860 views

Azure SQL Database throwing error 1132 "The elastic pool has reached its storage limit" error, even if it's not full

I'm trying to execute the following (sample) batch in an Azure SQL Database inside an Elastic Pool: drop table if exists [dbo].[InsertTest]; create table [dbo].[InsertTest] ( [id] uniqueidentifier,...
4
votes
4answers
846 views

Why changing the column in the ORDER BY section of window function "MAX() OVER()" affects the final result?

I have a table with below structure and it's data : create table test_table ( Item_index int, Item_name varchar(50) ) insert into test_table (Item_index,Item_name) values (0,'A') insert into ...
-1
votes
1answer
51 views

Check if contract conflict with another contract query [closed]

I have a database with these tables as the bellow picture showed: The user can create a new contract each contract may have one or more shops and these shops status changes depending on the contracts,...
1
vote
1answer
207 views

Sql Server pivot string results into columns

I am trying to transpose a dataset of string values, into columns, depending on the value of an integer example: create table imgText ( rowindex int, imgName varchar(50) ) insert into imgText (...
0
votes
0answers
21 views

Track usage of views and set up logging

I set up a database with some views. I want to log who accessed which view and when. The target-table looks like this: [schema], [table], [user], [timestamp] dbo, access, someuser, 2021-04-13 12:07:...
1
vote
1answer
33 views

Select distance between cities in two directions

I have a distance table and a cities table. I have joined them in a way where i now have: cityA_id,city_A,cityB_id,cityB, distance in km i need to get the distance betweens cityA and CITY B and then ...

1
2
3 4 5
58