Newest tsql Questions

Q&A for professional and enthusiast programmers

How to select the time a room is available given date and room in SQL Server?

I have 'Reservation' table with the following fields ReservationID int ReservationDateFrom datetime ReservationDateTo datetime ReservationRoom int I want to create an sp that can select ...

How to use string variable for SQL Server index hint?

I have a SQL Server 2012 database table with geometry columns and there is a single Spatial index on this table. I have a SQL query which I run to do some geospatial analysis (intersect/contains) and ...

SQL error not rolling back entire SqlCommand

I have been placing set xact_abort on into SQL command statements and noticed that it is not rolling back updates, inserts, etc in my C# SqlCommand on error. Taken from this post. The MSDN states that:...

TSQL - Insert Multiple datasets without join

I have a stored procedure I am working on and trying to think through this scenario. I allow a user to pick multiple documents that have a PK of ID. They can then select multiple names from a list ...

SQL Creating a function to get the sum based on column name

Im trying to write a simple function that will allow me to get the sum based off the value of a column. CREATE FUNCTION [GetSumOfColumnByCase](@column varchar(50), @case int) RETURNS INT AS BEGIN ...

Having some trouble creating a stored procedure (Incorrect syntax near 'end')

Update question Changed the stored procedure accordingly, but it's not returning any values for some reason, even though I added RETURN @UserID right above the last END. Update 2 Alright never mind,...

Which datatype choose?

If we have numbers like 25-135, 87-987, 25-135.115-15, 25-135-511 - which datatype is appropriate to use in the table? Please advise.

How to join most recent currency exchange date on a date which falls on a weekend or holiday?

I have a table with transactions with different currencies, and some of these dates fall on a weekend or holiday. Example: Date currency_code ammount 20-02-2017 EUR 55 18-02-...

Parameterise values for OFFSET and FETCH NEXT

Is there any way to do it ? I'd like to be able to run it as dynamic sql via EXEC sp_executesql At the moment - it's just a string concatenation like so: SET @sql += ' ORDER BY em.[Date] ' + IIF (@...

Compare two rows in same table (Duplicates)

I would like to compare rows where i have same name and surname and check if data is the same before i will delete the duplicate. All data is in one table client. Example: value name surname 2 ...

Find most recent record in a subquery (SQL Server)

I'm converting some code from Oracle to SQL Server (2012) and have run into an issue where this subquery is using a PARTITION/ORDER BY to retrieve the most recent record. The subquery runs fine on its ...

a transport level error has occurred when receiving results from the server semaphore timeout

I have this error on my system A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) And as I ...

Next Booking Record with Function and table

Now I am trying to find the next available booking for a patient with the help of UDF, as I am not sure I can do this in some other way. This is my UDF: CREATE FUNCTION [dbo].[...

Group by LastActivityDate and different notetext

I have a problem in SQL, Writing SQL where two result set like Select CustID, B, C, D, Max(LastActivityDate) as LastDateModified, NoteAdded from ( Select CustID,B,C,D, Max(...

Stored procedure to update table in SQL

I have a following issue... I have three variable, v1,v2,v2 (All are of type java.util.ArrayList). I want to write a stored procedure that will take this variable as an input and update the one table....

Update rows that have the same values in two columns in MSSQL

My table have the same values in two columns. I would like to update only one row where in column login and email are the same value and data modification is earlier. So, in this example below it must ...

How to create a Database without SSMS?

I have created an application in which I used the following code to Backup the Data but stuck on this error. Code: Dim connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;...

Transpose rows into columns in SQL without using aggregate functions like MAX, AVG etc

I have a table like this: CREATE TABLE MyDataTable ( [RollNo] varchar(8), [QuesBlock] int, [RespVal] varchar(2) ); INSERT INTO MyDataTable ([RollNo], [QuesBlock], [RespVal]) VALUES (...

Need explanation of how Stuff function and For XML Path works in this code

I inherited a SQL script from someone who left our company, and I'm not sure what it's doing. The first part selects a username and their location and puts the data into a temp table #Roster: ...

SQL delete statement deletes instead of error out

Can someone explain how this query even executes? The column credit_transaction_key does not exist in financial_transaction, but it does exist in financial_transaction_jnl. What I don't get is why ...

how to insert smalldatetime value to data base column

i have one from user table like : Id int Unchecked Name varchar(50) Checked EmailAddress varchar(200) Unchecked Host varchar(250) Unchecked Port int Unchecked POPHost varchar(250) ...

SQL Person by month Data

I have a data set with Name|ID|Start_DATE|END_DATE I would like to get a break out of active months. Example: Bob Smith|1001|2016-12-01|2017-02-18 would return all the months listed between ...

How To Handle DBNull When Pulling data from DataAdapter

I've imported a MSSQL dataset into the DataSet Designer of Visual Studio but I'm having trouble with non-nullable types. My database allows for null integers (and i'd like to keep it that way if ...

“Execute as” in stored procedure doesn't apply to “OPENROWSET”

I'm trying to open xlsx file in stored procedure. When I'm executing procedure in schema app, but with execute as dbo: ALTER PROCEDURE [app].[Compare] ( @p_CPN_ID int, @p_FILE_LOCATION ...

Merge T-SQL Issue/Alternatives

I have a situation where in a stored procedure, I have a merge statement and this statement makes sure that if the records are in a particular day then it updates but if it is in a different day then ...

Reduce SQL Server database backup size

I have a sample SQL Server database backup that is ~12gb. Too large for me to restore in SQL Server Express (10gb limit). I downloaded the Developer version of SQL Server (on my home PC) and was able ...

SQL Server error - Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

I write this stored procedure ALTER PROCEDURE [dbo].[CERRARVIAJE] ( @ViajeID INT, @Reloj MONEY, @Peaje MONEY, @Equipaje MONEY, @Adicional MONEY, @Plus MONEY, @...

SQL Server: Find records where XML is missing tag

I have table named: XMLIndex that contains a column named: XMLRec that holds the structure of an XML file and values. Some of these records are missing a tag named: <ISO></ISO> My ...

Combine two table and create new column in result based second table

I have two temp table, from that want to combine both of them like following.Is there any way to do this? In final result there should consist of new columns based on second tables Tax Type. The ...

Temporary table not created from dynamic query execution

If I run this dynamic query: declare @test nvarchar(1000) = 'select * into #tmp7 from bauser' execute(@test) and then try to query #tmp7 with: select * from #tmp7 error is thrown: Invalid ...

SQL Server Write Permission but not delete

I'm pretty sure the answer is that once I grant write/alter access it explicitly gives the person access to delete.

Delete rows older than 2 months and cascade down to 3 tables

I have the following SQL Scheme on a SQL Server 2014 database: create table dbo.Media ( Id int identity not null constraint PK_Media_Id primary key clustered (Id), Created datetime not null, ...
+50

how to make Global Mail counter for maximum relays with asp.net 4.0 with c#

i.m .net developer. i.m working with one news letter system. No i have to make one global counter when no of mails send from particular from user. here is table schema tbl_FromUser_master Id int ...

sql averaging rows on same resultset

I'm trying to accomplish something kind of weird, i have a table that has the flowing select output on a select * (I'm just putting 4 rows to simplify). each pair of rows on the table is information ...

get data from curor & set into another temp table

DECLARE @MyCursor CURSOR; DECLARE @MyField int; Create Table #DataHold ( CourseChapterLessonID int ) BEGIN SET @MyCursor = CURSOR FOR SELECT CourseChapterLessonID from ...

SQL Server filter string by regular expression [duplicate]

Using T-SQL how can I remove all characters from a string that do not form a certain pattern? For example, if the the valid pattern is: "one blank, at least one character from a to Z, one colon, at ...

SQL - Format US Postal Codes that have 4 digit extension into parent zip code (group them)

Using SQL or T-SQL, in a column containing US postal zip codes, how can I group US postal codes that have 4 digit extension into their respective parent group(s)? For example, zip codes of 84047-2345,...

Transaction Isolation Level issue

I have been reading up on Transaction Isolation Levels and I am not sure I understand everything correctly. I need help. Please consider the following Csharp pseudo code: Using Stored procedure 1, ...

SELECT statement with if ELSE in SQL Server

I have a table looks like this: ref doc ----------- ref001 3 ref001 3 ref002 1 ref002 4 ref002 1 I want to use SELECT with IF ELSE statement or any other conditional ...

SQL: Append result set of first result to back of second result?

first time actually posting here. Have learned alot from reading posts though! My problem: Trying to make a random Canadian SIN generator to automate some customer test account inserts. I don't want ...

SQL Server 2016, How to use CONTAINS() with multiple word search of database?

New to SQL for the most part so perhaps there is a better approach but I'm looking for a way to search through a database using a textbox which can take any number of words/numbers. Basically like a ...

T-SQL Pivot table with dates

Using SQL Server 2008 R2 (SP3), I have a table which looks like this; AuditDate ID ID2 NumberOfResults 2011-02-25 1 0 3099534 2011-02-25 1 1 769 2011-02-25 1 2 176949 I am trying ...

How to create a new table with the select query results

I want to convert the obtained query (below exposed, see:code) into a new table so I can compute and process the data more easily.. I know this question sounds stupid, but when I use: SELECT X.* ...

Divide COUNT Column By COUNT(DISTINCT(Column To give avg order size SQL SERVER

Ok I have a list of the months orders so that bit is easy. SELECT COUNT(*) AS ITEMS, For the next part easy to: COUNT(DISTINCT(PICKSET_NO))AS PICKSETS, Its the next part I cant work out: SUM(...

Find out who is executing the stored procedure from within said procedure

Am I able to find out who is executing the stored procedure from within that procedure? CREATE PROCEDURE AS BEGIN DECLARE @executor SET @executor = {query to find out} END

How to update all fields dynamically in Instead of UPDATE trigger SQL Server

I am implementing Instead of UPDATE trigger in SQL-server, want to update all the fields without being in need to cache the values from the INSERTED table and then set it to the fields in the UPDATE ...

Updating records depending on the intersection of dates

I have a table that looks like this: ID | Start | End | Flag1 | Flag2 -------------------------------------------- 1 | 1900-01-01 | 1900-01-01 | 0 | 1 1 | 1900-01-01 | 2000-...

PATH as variable in OPENJSON

I want to make a function, which adds value to json-array only if it's not present there. I write a function CREATE FUNCTION dbo.JSON_ARRAY_APPEND (@json nvarchar(max), @arr nvarchar(max), @value ...

SQL Server 2016 transpose certain data from row to column

I am using a code to transpose specific data from rows to columns, when I use it in a small sample it works fine but then for the original sample of approximately 3.4 millions of rows it replies this:...

Create table variable from pivot results

Is it possible to create table variable from pivot results? My pivot query: SELECT @QUERY = 'SELECT USER_KEY, ' + @COLDEPSUMMARY + ' FROM CAUSDE_TAS ...
Translating... 0%