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

learn more… | top users | synonyms

3
votes
0answers
110 views

How to format T-SQL identifiers in camel case

A number of tools exist which are designed to facilitate formatting the T-SQL in an inherited script or stored procedure to match one's personal preferences. One feature I would like to see in such ...
2
votes
0answers
42 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
39 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
92 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
140 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
450 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
11 views

How can I return the full text of a document indexed in SQL Server Full-Text?

I'm using SQL Server 2014 with FileTables to store a large number of documents in different formats. The iFilters are working great, and everything is getting indexed with FTS + Semantic Search. Now ...
1
vote
0answers
20 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
73 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
69 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
34 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
28 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
28 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
65 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
47 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
169 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
318 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
310 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
198 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
3k 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
116 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
27 views

Shrink database or DB files not reducing size even after dropping biggest table

Our MDF file was originally 550GB in size (log is just 10MB), and the biggest table we had [HourlyCounters] was 140 million rows with 580 columns (varchar[50]). It's a table with a very inefficient ...
0
votes
0answers
18 views

SYNTAX ERROR: delete from multiple tables with inner join

Sorry, but I need syntax help :( This needs to run on SQL Server 2005 + DELETE SO, ASO, ASOV, AOV FROM SYS_OPTION SO INNER JOIN ADM_SYS_OPTION ASO on SO.OPTION_ID = ASO.OPTION_ID INNER JOIN ...
0
votes
0answers
16 views

How to Customized the Where Statement based on UDT

Goal: Display customized data based on customized where statement from UDT. The UDT is a parameter inside of a stored procedure. Problem: A parameter from a stored procedure is @communication ...
0
votes
0answers
36 views

Deep cloning a hierarchical database structure

I am trying to clone a hierarchical structure in my database, and I almost have it working; however I can't seem to get the output clause to access a column from a select statement. This is generally ...
0
votes
0answers
49 views

Rounding Casted Converting Error

The database I am working with has ntext datatypes which according to my understanding is evil. I used CAST to varchar to fix the issues in the below trimmed SQL Server query. Anyway, my question is ...
0
votes
0answers
68 views

How can I get a list all Databases in a Database Instance

I am getting an error with SQL Copy Database Wizard. It is finding a database that was dropped over a year ago and fails because it cannot be found. I cannot find any reference to the database and ...
0
votes
0answers
126 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 ...
0
votes
0answers
52 views

TSQL LAG equivalent

I am trying to understand how lag works, by converting the following code back to using self-join sql code without using lag. https://msdn.microsoft.com/en-us/library/hh231256.aspx USE ...
0
votes
0answers
38 views

Adding lot of new role assignment on different folders

On our local SSRS we are currently adding a big quantity of different users on different folders. The usual steps are like the ones detailed here SQL Server Reporting Services 2012 Permissions. Is ...
0
votes
0answers
40 views

Issues with Optimizing TSQL Script

I have a temp table that has a SecurityCode column. This SecurityCode column gets updated based off value from a CrossReference table. The value selected from the CrossReference table depends on how ...
0
votes
0answers
101 views

delete millions of rows from multiple tables

Hi we have to delete between 40 and 55% of records from several tables that have between 40 million and 100 million rows. Is it better to copy the rows we want to keep to a new table, drop the source ...
0
votes
0answers
136 views

Export a varbinary column to file system using existing authentication

I want to export a varbinary column to a file system. I can use BCP command to export data from a varbinary column, which requires username and password being sent along with BCP command. I can use ...
0
votes
0answers
29 views

Is it a good idea to use SSAS to predict hourly transactions?

So I've been working on this for quite some time but I run into so many obstacles that I am beginning to wonder if I am using the right tool for the job. My problem is pretty simple: I have several ...
0
votes
0answers
27 views

Question? Creating a website that has 2 sets of intermidate tables

I am creating a web site that uses 6 tables from one database in mysql (example) 2 sets of tables that connect to intermediate tables (using foreign keys to join the tables). 1) Category -> ...
0
votes
0answers
23 views

Cursor brings back Null when run

I'm trying to create a proc which assists in creating an XMLSitepath. I am trying to use a cursor but its not behaving as it should. Please could someone take a look and tell me where I am going ...
0
votes
0answers
197 views

Run a specific SQL Server job step from a stored procedure

I have a job with 5 steps that are scheduled to run after each other. I want to have a stored procedure where a user can execute the stored procedure and give the step number as a parameter. Only ...
0
votes
0answers
34 views

SSRS using select from (Select)

I want to report a script which contains a Select from (Select), when I just run the second select I have no problem but when I put the whole script it loads and nothing happens. Here's my code : ...
0
votes
0answers
52 views

Backup table is larger than original table

I am cleaning up my database by deleting unwanted tables such as temp tables, backup tables. While doing clean up I see that I have a backup table and the original table. The size of the backup table ...
0
votes
0answers
53 views

select random tuples but prefer old dates

With this table: CREATE TABLE T_TEST ( id int identity, somevalue varchar(256), lastupdate smalldatetime ); i want to select n random tuples which age is older than x days but prefer old tuples ...
0
votes
0answers
91 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" ...
0
votes
0answers
238 views

How to check if all the extendend stored procedures (xp_) are enabled or disabled?

These are the stored procedures that I want to check if they are enabled or disabled. xp_availablemedia xp_cmdshell xp_deletemail xp_dirtree xp_dropwebtask xp_enumerrorlogs xp_enumgroups ...
0
votes
0answers
148 views

SA Admin SQL-Server 2012 x64 keeps changing constantly

I know this is a newbie question, but really I can't find some precise info on this issue. I'm trying to figure out how to create a new user with admin server role on SQL Server 2012 x64. I mean, I ...
0
votes
0answers
16 views

Curious Regarding filtering behaviour and performance of where condition

So i have completed the clean up of SQL code that involves four set of queries that partially do the same filtering in where condition , while the rest of the part do their own task specific to each ...
0
votes
0answers
796 views

Row_Number() not working with OVER PARTITION BY

I have a query that I am "rolling up values". Lets say I have two rows that are the "same", save for one field: [CNumber] [PNumber] [date] [FullName] [Type] [P] [S] ...
0
votes
0answers
92 views

Name for SELECT equivalent of UPSERT

UPSERT means INSERT and\or UPDATE, is there a standard name for a sproc that will INSERT if necessary and then SELECT? e.g. I want to get the IS of a row that matches fields and give me the ID, if ...
0
votes
0answers
161 views

How can I get rid of the “No Join Predicate” warning

I have this fairly straight-forward snippet of a query: DECLARE @ClientGUID numeric(16) = 9000011916900200; SELECT ch.GUID, c.LastName, c.FirstName, mrn.ClientIDCode AS MRN FROM Client c INNER ...
0
votes
0answers
166 views

SQL Server and Entity Framework

We are currently experiencing a problem with Entity Framework which generates a sp_executesql statement. This statement is below. The statement runs fine in SQL Server however through the front end ...
0
votes
0answers
402 views

incorrect value from SUM Function

Has anyone seen this before? When doing a simple sum on a table: select sum(column) from table where column is not null I'm receiving the data 1558.39999999999 However, investigation of the ...
0
votes
0answers
266 views

Getting Request Timeout from Store Procedure?

Only sometimes, I get Request Time-out(means my SP is not responding within a specfied time-period which is 90 seconds). Here is my SP. ALTER PROCEDURE [dbo].[Insertorupdatedevicecatalog] ...