Questions tagged [sql-server-2016-localdb]

0

votes
0

answer
2

Views

TFS backup job hangs - how do i find the problem?

I have TFS backup job hangs for several hours without any errors. I have tried Restart of SQL server. On the monitoring page of TFS it looks ok without any errors. TFS 2018.1 SQL SERVER 2016 STD Windows Server 2012 R2 Backup was working good as scheduled for almost 1 year . Please Advise
galsi
0

votes
3

answer
18

Views

Join and get Last record In another table

Please consider this tables: FileSpec: id Name Extension ------------------------------- 1 Nima xlsx 2 Arian txt 3 Ava doc 4 Eli txt DownloadLog: Name UserName Date --------------------------------...
Arian
1

votes
1

answer
59

Views

Alternative to SQL Sub Query for Returning Prior Matching Records

SQL Azure Database V12 (SQL Server 2016) Given the following basic table structure: MyTable ============== Id int PK TheDate datetime2 not null TheValue varchar(50) not null -- other fields And the following SQL: select ( select count(*) from MyTable mt2 where mt2.TheValue = mt1.TheValue and m...
Metro Smurf
1

votes
1

answer
620

Views

Visual Styles-related operation resulted in an error because no visual style is currently active. (System.Windows.Forms)

I have installed the latest version of the SQL Server Management Studio 2016 in order to test the Always Encrypted feature. It is opened from this menu here: but always throw the following error: and then the studio stops working. Has anyone idea what is causing this behavior?
gotqn
1

votes
2

answer
624

Views

Running Distinct Count with a Partition

I'd like a running distinct count with a partition by year for the following data: DROP TABLE IF EXISTS #FACT; CREATE TABLE #FACT('Year' INT,'Month' INT, 'Acc' varchar(5)); INSERT INTO #FACT values (2015, 1, 'A'), (2015, 1, 'B'), (2015, 1, 'B'), (2015, 1, 'C'), (2015, 2, 'D'), (2015, 2, 'E'), (2015...
whytheq
1

votes
2

answer
1.5k

Views

Enable disable a security policy in SQL Server

I have created a Security Policy to implement Row Level Security (RLS) in SQL Server 2016. There is some specific time in a month when the security policy will be applicable. I am planning to write a job which will enable or disable the Security Policy, but I am not getting the SQL command to disabl...
PSK
1

votes
2

answer
5.1k

Views

How to configure encrypted SQL Server connections to prevent MITM attacks?

It has come to my surprise that SQL Server by default does nothing to prevent man-in-the-middle attacks when configuring your SQL Server to enforce connection encryption. Forcing encryption on your connections is dead-simple. You go to Sql Server Configuration Manager, expand SQL Server Network Conf...
Sam Rueby
1

votes
1

answer
231

Views

ColumnStore Index - Maximum Rows in a RowGroup - 1,048,576 OR 102,400?

I was trying to understand, the architecture of Columnstore Indexes - SQL Server 2016. Ref: https://msdn.microsoft.com/en-us/library/gg492088.aspx#Anchor_4 Here, the minimum rows in a rowgroup to move in to the column store is mentioned as 102400, But it is also mentioned, that 1,048,576 rows are u...
yasub jiruwala
1

votes
2

answer
40

Views

SQL Display conditionally on other row

I have a table that lists a batch number and then results from testing at each stage that this batch passes through. I want to display test results for each stage, but only on batches which have passed a certain stage in the last 7 days. So far I have in my WHERE the following: WHERE [DateTime] >= D...
coblenski
1

votes
2

answer
109

Views

MS SQL no warning when inserting DateTime to Date column

create table t1 (d date not null) insert into t2 (d) values ('1988-02-01 14:23:30'); // inserting whole dateTime, no warning or error select * from [Test].[dbo].[t1] // 1988-02-01 Where can I set that ms sql server would throw error in case we are inserting whole datetime to date column. I noticed t...
broadband
1

votes
3

answer
433

Views

Option Strict On and DBNull.Value

I've started converting an application of mine to use Option Strict On. I've been doing the CStr,Ctype etc, and it's been going well. SQLCommand.Parameters.AddWithValue('@TERMINATE', If(IsNothing(txtEarningsTerminated.DateValue), DBNull.Value, txtEarningsTerminated.DateValue)) Then I hit this. The t...
Kayot
1

votes
2

answer
270

Views

How to store the result of dynamic SQL in a variable?

I have the following theoretical statement which I would like to implement using dynamic SQL (on SQL Server 2016) and store the single output value in the variable (@output). @numericvar, @columnname, @tablename should be input parameters. Any help would be greatly appreciated. Many thanks. SELECT...
Kanarinox
1

votes
1

answer
827

Views

Can both a table and indexed view be columnstore?

I have a data warehouse fact table with millions of records and it is a clustered columnstore table. I want to have an Indexed View of the table which casts numeric values of a Text column to numbers and text values of the same column to blanks, for faster performance. If I create the View as a clus...
Arvind S
1

votes
1

answer
209

Views

Does Table-Valued Function (SQL) create table on each call? [performance]

Okay this might sound a noob question, but SQL isn't my really strength, so I am requesting some help here. I am trying to implement something, but I am concerned about performance issues. The problem I am trying to fix is something like this: I have a column with a lot of data separated by commas '...
TiagoM
1

votes
1

answer
2.3k

Views

SSIS deployment option missing - Visual Studio 2015

I'm trying to deploy an SSIS package (visual studio 2015) to our SQL 2016 server. However, the option to deploy is not being displayed. I have already tested it on a previous package as shown below: But the other project does not give me the option to do the same. Only to execute the package. It may...
Santelices
1

votes
2

answer
78

Views

A way to not repeat expressions in a SQL Server string manipulation query

I have the following sql: SUBSTRING(Location, CHARINDEX(' : ', Location) + 3, CHARINDEX(' ms - ', Location) - (CHARINDEX(' : ', Location) + 3)) (Location could be something like this: 1dasev : 232 ms - 323-asv3R and the substring would return 232) If I wrote this in C# I would not re-calculate CH...
Vaccano
1

votes
1

answer
98

Views

SQL Server 2016 with R Services in the cloud

I have developed a stored procedure that computes the best fitted curve that describes some data. In order to do that I am running a piece of R code through SQL Server using the nonlinear least squares function of R language (in case of interest the function is nls()). I have also developed a user i...
gkoul
1

votes
1

answer
460

Views

SSIS Data Transfer from Azure SQL to On-premises SQL Server 2016

Is there any way to transfer the large volume of data from Azure SQL to on-premises SQL Server 2016 Enterprise/Standard? The requirements prescribed as follows: Weekly full database transfer Daily delta transfer before midnight I read about SSIS for Azure Blob Storage but am not sure whether it is a...
Kyaw Thurein
1

votes
1

answer
1.6k

Views

Insert values into identity column manually and automatically in one query

I have a table in SQL Server with identity column. I need to insert N+M rows into it. For N rows, I need manually to insert the value of identity column. For other M rows, the value for identity column should be auto-generated. Each row of input data has an attribute (bit column) which defines wheth...
Anton
1

votes
1

answer
37

Views

SQL computed column evaluating numbers should return string

A similar question is here: Computed column should result to string However in my case, I cannot cast the numeric values to strings. I need to evaluate the numbers and based on them return a text description. Is that possible? ALTER TABLE dbo.test ADD classTest AS CASE WHEN speed > 10 THEN 'fast' E...
sclarky
1

votes
1

answer
46

Views

How to find all tables where trigger does not exist

The other day we found a table that had a ModifiedDate column but discovered there was no trigger in place to actually update that column. Now I'm trying to write a script to find all of the tables that have a ModifiedDate column but no trigger to update it. Here is what I have so far: SELECT so.nam...
Jonathan Porter
1

votes
1

answer
24

Views

Moving subtotal to columns

I believe my problem has to do with the grouping structure. This is my original report structure where there are detail rows and then a subtotal row: I need to move the subtotals to columns Hdr Qty. Ordered, Hdr Qty. Remaining, etc. but because it's in the same groupings as the rest of the data it s...
Jonathan Porter
1

votes
2

answer
413

Views

How can I delete in SSIS a FlatFile in ControlFlow when it has dynamic name?

I use SQL Server 2016 and I have a SSIS package in which I have created a FlatFile with a dynamic Name in one DataFlow in Loop. For creating the name I have used this pathern : 'SomeText' + DateTime + 'SomeText' I have created a varibale like below Image : This is Exprission in my variable: Then I...
Ardalan Shahgholi
1

votes
1

answer
547

Views

How to attach my Microsoft SQL Server 2016 database to installshield project in Visual Studio 2015?

I'm coding a C# Console Application that connects to a database and runs some queries where my database Engine is Microsoft SQL Server 2016 and I code in Visual Studio 2015. I have already installed and activated InstallShield Limited Edition on my visual studio and I'm able to make an installation(...
parisa
1

votes
2

answer
972

Views

SQL Trigger after insert not working

I tried to write a trigger that updates the data after an insert. I'm using the SQL Server 2016 Import Export Data tool to transfer my data from Excel into the database. After the import I need to run a few update statements. I can save my trigger, but it's not updating any data. Where is my fault?...
Leo
1

votes
2

answer
541

Views

Detect the cause of SQL Server update lock

Problem: A .NET application during business transaction executes a query like UPDATE Order SET Description = 'some new description` WHERE OrderId = @p1 AND RowVersion = @p2 This query hangs until timeout (several minutes) and then I get an exception: SqlException: Execution Timeout Expired. Th...
FireAlkazar
1

votes
1

answer
175

Views

Azure PolyBase external table from StorageTable

Can somebody explain to me why I can polybase reference an azure BlobStorage as external table but not an azure StorageTable? Did I missed it some where in the documentary? Its some kind of ironic for me if I don't missed it and it is really not possible. Related to @wBob answer I created a uservoic...
Steffen Mangold
1

votes
4

answer
211

Views

Find Comebacks in Football Database With Sql Query

I have 4 tables. Matches: | id | HomeTeamID | AwayTeamID | --------|-------------|------------ | 1 | 1 | 2 | 2 | 1 | 3 | 3 | 3 | 1 Goals: | id | MatchID | Minute | TeamID --------|-------------|---------- |--------- | 1 | 1 |...
YS1965
1

votes
2

answer
63

Views

Using IS NULL to convert NULL dates

I'm running a query to change some returned data around in SQL Server 2016, and I am getting a conversion error (Conversion failed when converting date and/or time from character string). SELECT jobs.name, CASE WHEN jact.start_execution_date IS NULL THEN 'No Execution' ELSE jact.start_exeuction_date...
brandont
1

votes
2

answer
576

Views

Query XML data in SQL Server 2016 database

I have XML data stored in a SQL Server 2016 database that I need to query a date range in a stored procedure around the PROJECT_END to return the application_ID's 3012448 D23 NU 1 01/01/1985 12/31/1985 01/01/1985 12/31/1987 I have the select part but can't seem to query based upon the date range usi...
user1314159
1

votes
1

answer
231

Views

What is bottleneck for query to perform slow in Azure

I have Azure SQL database in Standard tier, 10 DTU. How can i 'predict' performance on CPU intensive queries (as that seems to be reason for slowness)? To illustrate problem will use perf_test table, that can be populated like this (script could be improved a lot, but that is not a point here): CRE...
Jānis
1

votes
1

answer
138

Views

Balanced Data Distributor in BIML

Is there support for the balanced data distributor component in BIML? The documentation has this: https://varigence.com/Documentation/Language/Element/AstBalancedDataDistributorNode But when I use this element, I get the following error: Error 0 FlowFramework, Version=5.0.60425.0, Culture=neutral...
1

votes
1

answer
1.3k

Views

Select results from multiple array elements in Json document with Sql Server 2016's OPENJSON function

Is it possible to combine parts of a json document from multiple array elements into a single result in Sql Server 2016? Given this json: { 'fruit': { 'types': [ { 'possible': [ 'Apples', 'Bananas', 'Pears' ], 'category': 'Basic' }, { 'possible': [ 'Oranges', 'Grapefruit', 'Lemons', 'Limes' ], 'cate...
Dave Slinn
1

votes
1

answer
397

Views

Tool to convert queries with Non-ANSI syntax to ANSI syntax in SQL Sever 2008 R2

As a background, our system was working from SQL 2000 and was using the Non-ANSI syntax to write the queries. When we migrated to SQL 2008 R2, we have modified the '= * ' and ' * =' with RIGHT as well as LEFT joins. But now we are planning to migrate to SQL 2016. The Comma JOINs are deprecated. We c...
Gopakumar N.Kurup
1

votes
3

answer
47

Views

Update more than one column with single Case When logic - SQL Server

I need to create a following XML with Custom Element with attribute using Hard-code data. Table schema: StudentMark: CREATE TABLE [dbo].[StudentMark] ( [StudentMarkId] int IDENTITY(1,1) NOT NULL, [StudentId] uniqueidentifier NOT NULL, [SubjectId] uniqueidentifier NOT NULL, [Score] int NOT NULL, [Gen...
B.Balamanigandan
1

votes
1

answer
179

Views

Get DST Start and End Date for any Timezone

I see this question asked quite a bit, but don't see any actual answers (most solutions are for US time zone specifically). Is there a way in SQL Server to get the start and end date of daylight saving time, given a year and a time zone? To summarize, I'm looking to see if anyone has come up with a...
jpints14
1

votes
2

answer
43

Views

SQL Query - Pivot Grouping or any concept to get my result table

I have a table called #MyTable. MyTable ID Column Value 1 x1_abc 11 1 x1_def 23 1 x2_abc 9 1 x2_def 10 2 x1_abc 1 2 x1_def 55 2 x2_abc 42 2 x2_def 44 3 x1_abc 32 3 x1_def 33 3 x2_def 88 3 x2_def 90 My output should be like this ID Column x1 x2 1 abc 11...
Developer_29
1

votes
2

answer
243

Views

Setting collation for a column in SQL Server 2016

I am testing below code for collation. declare @table table ( col1 varchar(10) collate Latin1_General_CS_AS -- modified for column ) insert into @table values('abcd') declare @table1 table ( col2 varchar(10) --database default(collate SQL_Latin1_General_CP1_CI_AS) ) insert into @table1 values('ab...
omkar
1

votes
1

answer
721

Views

SQL LOOP Pass values from Temp Table as parameters to stored procedure

Using SQL Server 2016, I have a huge query for our Finance Dept that uses @Year and @FinPeriod to match up transactions with a period. And for reporting we normally pass a single value into the stored procedure. But now we are required to populate the underlying tables with the data that would norma...
user3735855
1

votes
1

answer
349

Views

SQL Server 2016 - Always Encrypted - Missing Option To Generate Powershell

I'm currently testing Always Encrypted feature of SQL Server 2016, and I was able to encrypt the column using default keys but I am unable to script this effort out via 'Generate PowerShell script to run later' option. Has anyone encountered and re-mediated this before?
Carl Ardiente

View additional questions