All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

3
votes
1answer
31 views

Can't get databases or its objects with powershell

I have Powershell installed both in my server and development pc. In my dev machine apparently have a most recent version of PowerShell installed or some its components of SQL Server 'sqlps'. My ...
2
votes
2answers
11 views

SSIS - How to parse extensions from path-filename strings

I'm importing a large set (126M rows) of file data from CSVs. Import works fine, except I'm trying to derive a column as I import rather than trying to script it out afterward (due to the number of ...
0
votes
0answers
4 views

How to implement SQL Server Service Broker with external activator

I am trying to execute an external app using SQL Server Service Broker and the External Activator provided by Microsoft. [https://www.microsoft.com/en-us/download/confirmation.aspx?id=29065][1] I ...
0
votes
0answers
11 views

Query to Group By for a Specific Substring returning Only One Result

I have the following query: Select count(*) as countForType, SUM(typeTable.amount) as totalForType, case when ( charindex('[', typeTable.source) > 0 and ...
-1
votes
0answers
9 views

How do we implement SQL Server IF ( SELECT in MySQL 5.6?

How do we implement this SQL Server syntax IF(SELECT MAX(DateAdded) FROM RestoreDetectors " + " WHERE CONVERT(VARCHAR,DateAdded,101) = CONVERT(VARCHAR,GETDATE(), 101) " + " AND DATEDIFF(SECOND, ...
0
votes
1answer
30 views

DBCC CHECKIDENT behaving inconsistently

I have a script that sometimes needs to be run on a fresh database that's just had all the tables created and sometimes needs to be run on an existing database. I have a script to wipe out the tables ...
1
vote
2answers
247 views

Is it possible to set stand-by database to active, do changes and then continue restore chain?

Basically, I'm using a custom log shipping method on a sql 2012, where the transaction files are manually copied to an offsite location. The files are then restored using something similar: RESTORE ...
-2
votes
0answers
14 views

SQL server Database Consolidation [on hold]

What are the best practices for consolidatig multiple SQL Server databases into a single SQL Server machine? I have gone through many of different solutions but I'm unable to find the best solution.
0
votes
1answer
23 views

Altering failed job email

I'm looking for a simple solution: if you have a job with multiple steps and one step fails but the job is set to continue, is there a way for the SQL Agent email to say which step(s) in the process ...
0
votes
1answer
20 views

How do I view operators in a Notify Operator task in a Maintenance Plan?

In the past, we have added each DBA to a Notify Operator task on failure in Maintenance Plans ranging from scheduled backups to indexing and occasionally one-off operations. One of our DBAs recently ...
1
vote
1answer
35 views

Partitioning a table on a column in another table

I have a fact table called FactAccount which has the following columns: AccountId (identity) AccountMonthId - FK referring to DimMonth AccountYear The DBA has done the partitioning on the ...
2
votes
0answers
34 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) ...
1
vote
2answers
45 views

SQL Server Concurrency Issue

We have a SQL server concurrency issue we are facing and would like some ideas on how to solve it. There are several procedures we have written the contain INSERT/UPDATE statements on tables in a Set ...
6
votes
3answers
7k views

How do I add a MySQL linked server to SQL Server?

I am trying to add a linked server on SQL Server to MySQL Server. Note the SQL Server is installed on a separate machine with a different domain name. The SQL Server is 10.0.1.1 on old_domain.com. ...
2
votes
0answers
421 views

SQL Server 2014 Avaliability Group DbMgrPartnerCommitPolicy

I have an environment with 2 Windows Server 2012 R2 servers, both with SQL Server 2014 Expensive edition. Whenever a change happens in the AGs (such as: AlwaysOn Availability Groups connection with ...
7
votes
5answers
604 views

Fast reads without paying the price on writes

We have a large append-only table containing financial transactions. On average 1000 transactions are inserted per minute. Since there are now more and more usecases where we actually want to read, ...
2
votes
2answers
25 views

Modify linked server definition (Oracle) in SQL Server 2012

I have created a linked Oracle server in SQL Server 2012 using Oracle Provider for OLE DB and TNSNAMES.ORA (the Oracle database is clustered and, as far as I know, no other connection method works ...
0
votes
2answers
21 views

SQL With Multiple Condition and Column

I have 3 tables: Employee +----+--------------+--------------------+----------------+ | Id | EmployeeName | EmployeePermission | Position | ...
0
votes
2answers
234 views

SQL Server vs SOLR (Or any document db)

I have my "customer" data in a normalized sql server database. Getting out the customer data in my app is taking too long. This is because I have to go to 10+ tables to get all the data I need. My ...
1
vote
0answers
18 views

How to Replicate data between a hosted database and a local database

I have a Cloud hosted Database, maintained by a different company. Here is my scenario: Cloud Hosted Database SQL2012 - Need to connect through a VPN. We setup a local Server with with Dual NIC ...
1
vote
1answer
255 views

Why are SELECT queries in sleeping status holding open transactions

Using sp_WhoIsActive, I see that I have a lot of processes in a "sleeping" status, several for hours. I am not sure of the cause and am still troubleshooting (timeout? bad code with uncommitted ...
4
votes
4answers
515 views

Delete into new table (like select into)

The select into syntax will create a new table to insert data into (I know that types may not be consistent etc. but it gives you a rough copy). Is it possible for a delete output into statement to ...
5
votes
3answers
156 views

In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?

Edit After getting first answers to this question I realised that I should narrow it down and changed the title of the question. Sorry for confusion. I've been reading MSDN about TRY...CATCH and ...
0
votes
0answers
39 views

Slow application performance [on hold]

We are experiencing slow application performance on a couple of apps. We are on SQL Server 2012 Standard Edition 64 GB. This is installed on a Windows 2012 R2 server. The database is without mirroring ...
3
votes
1answer
55 views

Having hard time understanding expressions in below query plan

I was going through Temporal tables new features in SQL 2016,one of the examples was to query tables with as of clause which returned below query plan Rows Executes StmtText StmtId NodeId ...
0
votes
0answers
19 views

Normalizing a database [on hold]

I have been developing a social network . I am in the part of normalizing the database.. All I need is to know what transitive dependency really means. Most sources do not explain it as well as they ...
2
votes
2answers
2k views

How to create login and logout trigger for client audit?

I need a table to put login and logout information for client not sysadmin,then i can use date to statistic user login/off ration... Can anyone offer me a simple method to create login and logout ...
1
vote
2answers
289 views

Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014

I am about to setup a SQL Server 2014 on a Windows Server 2012. Starting from best performance on the drive I formatted the drive with 64k allocation unit size. Now I am stuck using Diskpart to set ...
4
votes
4answers
185 views

Add autoincrement to existing PK

I created a table in a DB that already exists in another DB. It was initially populated with the old DB data. The table's PK had to receive the values that already exist on those records, so it ...
0
votes
2answers
7k views

How to copy backup files to remote share in SQL Server Agent job without AD/domain accounts involvement?

MS SQL Server 2012... with nightly databases backups to the same/local as SQL Server machine... I am trying to add another SQL Server Agent Job to copy the .bak files to remote (non-windows, i.e. ...
0
votes
2answers
8 views

Create new instance MS SQL Server 2014

I have installed MS SQL Server 2014 , but after installation there is no instance created. There is only the default instance. How make a new instance in existing MS Sql Server ? I found a lot of ...
32
votes
5answers
4k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
7
votes
2answers
285 views

SUM of DATALENGTHs not matching table size from sys.allocation_units

I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken? SELECT SUM(DATALENGTH(Field1)) ...
0
votes
1answer
39 views

Select Into removes IDENTITY property from target table

I am doing an exercise which removes the IDENTITY property from the target table when using a select into statement. source table name: Sales.Orders column name: orderid column property: int ...
3
votes
1answer
129 views

SQL Server query on server taking longer than laptop

Our program uses an SQL server database as its back-end (SQL server 2008-r2 - 2014). The program is OK for performance but the Database seems to slow down a bit too much. As part of the nightly ...
5
votes
2answers
675 views

Maximum SQL Server memory set to 12 GB, but it still goes over 12 GB. How do I prevent SQL Server going over max memory limit?

User 'mrdenny' wrote that after you set SQL Server maximum memory... Now this isn't going to limit all aspects of SQL Server to that amount of memory. This only controls the buffer pool and the ...
1
vote
1answer
106 views

What is the relationship between the SSIS Package Store and Catalog?

I have a server with a working installation of the Sql Server 2012 SSIS Catalog. I need to set up an additional instance including the SSIS Package Store service as an interim step while the packages ...
3
votes
1answer
34 views

Fill Factor: Will the fill factor change automatically after it detects the changes in the index pages?

So lets say that the default fill factor is left at 0 initially. Data has been loaded up into the server and one fine weekend, the index rebuild job is run. Now if it detects any new data which must ...
1
vote
1answer
37 views

Readable secondary

I set my readable secondary to read intent only and I ran my application without application intent = readonly and start a trace on the secondary node to make sure we are not reading from the ...
2
votes
0answers
25 views

Blocking Extended events

I have very basic table CREATE TABLE [dbo].[Table_1]( [Test] [int] NULL) sp_configure'block process threshold', 1 --> 1 sec go To create blocking, in 1 session i executed Begin tran Update ...
3
votes
0answers
31 views

How to deal with Users Needing to Enter Mixed Data Types in MS SQL Server

I have a form in a C# application that I can't change with a data table bound to a query. The user enters a mark for a student's task or 'A' for absent. However, the person who designed this had the ...
4
votes
1answer
234 views

Find all tables that are related to a view recursively sql server

I have a view-A that is comprised of other views (View-B View-C) and I would like to list all the tables that used by View-A (which would include the tables used in View-B and View-C). When I use ...
4
votes
1answer
50 views

How can I select all of the parents of rows that exist in another table?

I have two tables that share a many to many relationship. The relationships are stored in a third table. CREATE TABLE Person ( Id int NOT NULL, Name varchar(255) NOT NULL, PRIMARY KEY ...
2
votes
1answer
43 views

Error 909 restoring a database on Standard Edition

I ran into a problem with SQL Server 2012 Standard Edition (64-bit). I have rebuilt a system with the exact same specifications for software as it had been built previously (Windows 2012, SQL Server ...
0
votes
0answers
38 views

Retrieving the latest forecast

I am writing an application which stores weather forecasts for different cities, and each day there is a new forecast for each city. I want to be able to provide daily forecasts, and also the latest ...
4
votes
1answer
34 views

SQL Server 2008 R2 services not coming online

I am working for a critical account and yesterday I got a weird issue after applying cumulative update package 14 on top of SQL Server 2008 R2 Standard edition. Before applying CU-14 package on the ...
3
votes
1answer
31 views

Installation of SQL Server 2014 fails on Windows Server 2012 R2

I am attempting to run a silent installation of SQL Server 2014 with SP1 on Windows Server 2012 R2 core install, although it also fails when using UIMODE="EnableUIOnServerCore": (01) 2016-02-05 ...
5
votes
2answers
63 views

Transaction Log Backups Serial or Parallel?

We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance. This question ...
1
vote
2answers
103 views

Transforming multiple rows into a single row

In my company, I need to join table A to table B: Table A is the sources table Table B is the empty table I want to know that how can I do it and the result like table B? Source table A: ID ...
1
vote
1answer
129 views

Best way to transfer a few tables from reserve server to production

We have two MS SQL Servers (2012 Standard Edition on Windows 2012 Server) which are located in the same subnet. One is production server. Another is reserve server. Currently there is a ...