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

learn more… | top users | synonyms (1)

4
votes
0answers
118 views

How can we improve this PIVOT operation? ( SQL Server 2008 R2 )

We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query. select top 100 * from ( select codCliente ...
3
votes
0answers
80 views

Export/import data from Excel into a table with queries

I'm a newbie interested in learning and implementing things I learn. I've been working on SQL Server 2014 these days and I'm loving it. I've come across some scenario's where I would have an Excel ...
3
votes
0answers
44 views

How do I load data from a single table to multiple tables & keep referential integrity

Using SQL Server 2008R2. I have a single table with a bunch of columns. I have built a new database with multiple tables to copy the data too. How do I copy the data, and still keep the ...
3
votes
0answers
1k views

SQL DatabaseMail smtp error 5.7.57 at one site, email works at another with the same details

I have several SQL databases on different servers. I have used a script to set up Database mail. EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Emailer' ,@description = 'Email out ...
3
votes
0answers
85 views

Trigger/Event that fires when a Transaction finishes

I have a query that pulls from about 15 different tables. I am looking to materialize this into a table that stores it in xml/json. (To improve performance.) The problem I have is that these tables ...
2
votes
0answers
53 views

TSQL Generic Row number theoretical question

To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next) ...
2
votes
0answers
60 views

What is the correct way to rename/swap a table in production

I have two tables, tblCode and tblCodeAlternative tblCode has about 4100 rows and is used in production. tblCodeAlternative has about 4200 rows and is in the production database but not used ...
2
votes
0answers
46 views

Cross database - Table valued Stored Procedure

I have the same table type in two databases (DB1 and DB2), DB1 has an stored proc (sp1) which has the above type as an input parameter. Now, I want invoke this SP from DB2. But, I am getting this ...
2
votes
0answers
155 views

How do I provide a failsafe for retrieval of data from excel to SQL

So say there is a process in place for importing data from excel spreadsheet to SQL using SSIS. And there are two tables, one with new data and one with data from its previous run. I sometimes have ...
2
votes
0answers
30 views

restore script variable date and time

Goodafternoon, i am looking for a variable for the date and time. I have several backups in one directory and want to restore the latest to a another server. Is there a variable i can use in my ...
2
votes
0answers
76 views

Techniques for moving database from Enterprise to Standard Editiion

I have a SQL Server 2012 Enterprise edition and a Model database. On a virtual machine I have installed SQL Server 2012 Standard Edition and want to create a copy of the Model database on it. I have ...
2
votes
0answers
46 views

Code required to make column not nullable

I have a table where I mistakenly allowed NULL values in a column: CREATE TABLE EXAMPLE ( EXAMPLE_ID INT IDENTITY(1, 1) NOT NULL, FOO_ID INT, CREATED_ON DATETIME DEFAULT ...
2
votes
0answers
111 views

Quirk update for cumulative sum of chances for a raffle. What can go wrong?

I need to make a raffle where users have different chances. | USER | CHANCES | |------|---------| | A | 10 | | B | 25 | | Z | 15 | Looking in this answer: drawing a riffle ...
2
votes
0answers
240 views

Avoiding Functions() in the Where clause

Okay, while analyzing queries against a SQL Server 2008 R2 database, I happened upon the following query: (@p__linq__0 nvarchar(4000)) SELECT TOP (1) [Extent1].[OrderSOId] AS [OrderSOId], [Extent1] ...
2
votes
0answers
472 views

SQL Server : where with cast(value as varbinary) vs cast(value as varbinary(300))

Are there any theoretical performance differences between these queries in the scenarios below. Scenarios are: MyTable has Value nvarchar(300) MyTable has Value nvarchar(max) Q1: declare @p ...
1
vote
0answers
23 views

Matching table (with sub-tables) to another table(with sub-tables) efficiently

I have a problem in an ETL process. I need to integrate data into a data warehouse. The data is associated with a "variant". A variant is defined as having a specific set of parameters (identified by ...
1
vote
0answers
25 views

Compare Strings After Character

I need to check email addresses against an internal known bad domain database. My internal database has domains in the form of myfreepaysite.com just no @ sign. I have been using this syntax to ...
1
vote
0answers
29 views

What's the deal with transactions in a stored procedure called by an SSRS report?

Consider the following: sp_a contains an sp_executesql call to exec sp_b sp_b begins and commits a transaction, then immediately logs @@TRANCOUNT. When I call sp_a manually from within SSMS, the ...
1
vote
0answers
28 views

How to properly re-run a deadlocked trasaction in TSQL

In TSQL (SQL Server 2012), is this a proper way of re-running a transaction that was killed due to deadlocked - it was chosen as the deadlock victim. EX: "Transaction (Process ID 118) was deadlocked ...
1
vote
0answers
28 views

Populate ID Field by Converting Group Name Values

This is a bit tricky to explain, but here goes. We have a System Rights database that loads system users and system rights or access levels into two different tables. The database is already built ...
1
vote
0answers
101 views

xp_delete_file not working

I am running a query window under my own account and the following command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\backup\sql', 'bak', '2015-11-24T23:30:02' ...
1
vote
0answers
97 views

How Can I Increase Performance of This MERGE Statement?

I have a INSERT INTO/MERGE statement that is part of a stored procedure that needs to be really efficient. The execution plan shows that 90% of the procedure is due to the following statement shown ...
1
vote
0answers
63 views

Migration script fail and revert on first error

I have a db migration script about 4000 thousand lines. I would like to compose it when it reverts all changes on first error. What is the proper way to do that? Is wraping with try/catch is the ...
1
vote
0answers
43 views

Window function vs group by method: find value for MAX ID

I'm trying to understand the performance issue with Window function when i select value for MAX ID --Old group By method SELECT tj.MainProcedure , tj.StartTime , tj.EndTime , ...
1
vote
0answers
27 views

Changing Action on Delete to Cascade

We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table. We now have ...
1
vote
0answers
14 views

Snapshot replication error two days in a row

We have a mature replication system to move data from OLTP to BI. It's been working for years with relatively few troubles. This week we've run into the same problem two days in a row with snapshot ...
1
vote
0answers
15 views

Opponent Winrate Over another opponent sql

I am trying to find the most efficient way to build a counter warehouse. shortened the tables just to needed info for question Match Table MatchId, version, type MatchParticipants matchId, ...
1
vote
0answers
30 views

How to parse and display row data as Column

I'm trying to display the row data from tbl_cte as a column. The result of column1 is alphanumeric e.g : "Data1.2.3". I need to parse it and display the "Data" as the name and "1.2.3" as the version ...
1
vote
0answers
89 views

Unable to insert explicit value in table due to IDENTITY_INSERT is ON Error

I am trying to insert explicit values in one of my Real time Audit Production database. There are more than 80 columns, I have just taken here 5 columns. I am writing syntax is as below: use ...
1
vote
0answers
85 views

Using :Connect I keep getting msg 911 - database [dbname] does not exist

I have a two part T-SQL script, the first part runs against a database on a different server, the second part is supposed to run on the local PC. I'm using SQLCMD mode and the :Connect keyword but ...
1
vote
0answers
222 views

How to speed up for huge tables in SQL select query?

I have the following huge tables. Table_1 with 500000 (0.5m) rows Table_8 with 20000000 (20m) rows Table_13 with 4000000 (4m) rows Table_6 with 500000 (0.5m) rows Table_15 with 200000 (0.2m) rows ...
1
vote
0answers
128 views

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= ...
1
vote
0answers
54 views

Can I Parse and/or include “And” in my SQL Statement for a full text index?

So I have a query that looks at a full text index like this: SELECT * FROM Table1 q INNER JOIN CONTAINSTABLE(Table1,TableField, @Keywords) search ON q.Key = search.[KEY] Now @Keywords ...
1
vote
0answers
42 views

Passing Table Values into a TVF

I have a TVF that takes a userID parameter. I have another table that contains a list of these userIDs. I want to pass all these userID values into the TVF. I have tried this code... FROM ...
1
vote
0answers
266 views

Using loops inside Openrowset

I'm trying to execute a query across around 200 servers using openrowset function. This function needs to execute in a loop - the query is sucessfull in SQL Server 2012 environments, but failing in ...
1
vote
0answers
32 views

TSQL Stored Procedure - Granting general user access to specific sproc

I'm attempting to alter a sproc that needs the line added : grant exec on dbo.sproc_name to exec_proc. I believe the admin setup the exec_proc as a group. I added this grant line following the final ...
1
vote
0answers
68 views

Non clustered indexes

I have a questions on Index Our Application is throwing the following SQL eroor Index 'INDEXNAME' on table 'dbo.TABLENAME' (specified in the FROM clause) does not exist. Error: Unknown Error ...
1
vote
0answers
93 views

Export hierarchy tree in a specific way

I need a big help. I have two tables (SubCells and Codes) and Visually it looks like this (We can count an order) And i need to get this final result (with SQL or even a C#) How can i do ...
1
vote
0answers
358 views

Exporting to excel from OPENROWSET with cell formatting

I am trying to generate a report from SSMS to Excel, using OpenRowSet. All is working fine, still I have to export a specific column with a barcode font. I already have the column calculated from ...
1
vote
0answers
141 views

What is the bare minimum access needed in the public role?

I need to lock down access to a specific database for a user - they should only have access to execute a couple of stored procedures. However, by default the user gets everything in the "public" ...
1
vote
0answers
482 views

SQL command in stored procedure continue on error

To perform Checkdb on all database of server i am using a stored procedure which loops over a view which contains list of databases in server and the stored procedure performs checkdb on all databases ...
1
vote
0answers
419 views

Sql Execution Plan - Hash Match

I'm currently working on a Sql Query that's having performance issues. I ran the execution plan and saw the Hash Match has 50% cost and also, doing a index scan. I'm starting to learning about the ...
1
vote
0answers
247 views

UPLOCK to avoid multiple reads

Given the following table structure... Orders ====== OrderId int PK OrderEvent =========== OrderEventId int PK OrderId int FK EventTypeId int FK I've got multiple concurrent processes SELECT-ing ...
1
vote
0answers
4k views

DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'

I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
1
vote
0answers
121 views

TSQL Trigger Incorrect Syntax

Im a SQL noob but here goes - I have a trigger in SQL which is giving me an incorrect syntax error E.G: INSERT INTO aTable ( tTypeId, opId, inserted.Id ) SELECT 3, 0, inserted.Id ...
0
votes
0answers
22 views

Any table type that persists across connections

Probably not possible,but I'm going to ask this anyway: I don't have OPENROWSET privileges on server B. I would like to import data while connected to server A (where I do have that privilege) and ...
0
votes
0answers
29 views

NEXT VALUE FOR SEQUENCE inside a CASE Statement

I want to use a new SEQUENCE each year (See here: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year ) However you can not use NEXT VALUE FOR inside a CASE statement. ...
0
votes
0answers
31 views

Run Select Statement Based Off Data In Field

Below is sample DDL of what I am trying to accomplish. What I need to happen is if region = 1 only pull data for PP or if region = 2 then only pull data for CC How would I set that up in my query? ...
0
votes
0answers
39 views

Search All Views On A Database

We have about 800 views on a SQL Server 2000 database, about once a week I run a procedure to give me information so I can see which stores have LIKE '%Camo%' in there inventory so I know who to send ...
0
votes
0answers
47 views

Issues with the @query parameter in sp_send_dbmail

When I use the following: select * from table A where repID in ( select repID from table B where id = 5 ) as the string I'm assigning to the @Query parameter. ...