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)

0
votes
0answers
7 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 ...
1
vote
0answers
12 views

Table partitioning based on values from another table

I've 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 AccountYear ...
2
votes
0answers
12 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 ...
1
vote
2answers
32 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 ...
0
votes
0answers
12 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 ...
0
votes
2answers
18 views

SQL With Multiple Condition and Column

I have 3 tables: Employee +----+--------------+--------------------+----------------+ | Id | EmployeeName | EmployeePermission | Position | ...
6
votes
5answers
257 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
21 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
0answers
36 views

Slow application performance

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 ...
4
votes
4answers
396 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 ...
0
votes
0answers
17 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 ...
5
votes
3answers
148 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 ...
3
votes
1answer
53 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 ...
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 ...
2
votes
0answers
23 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
28 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 ...
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 ...
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
1answer
38 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
30 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
673 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 ...
16
votes
1answer
117 views

Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

I am looking for practical guidance for setting the values for the BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE of the BACKUP command. I have done a bit of research (see below), I have done a bit of ...
4
votes
4answers
181 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 ...
5
votes
2answers
61 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 ...
0
votes
0answers
15 views

Check for impersonated users using Policy Management

All: I apologize in advance for my poorly worded question, but I'm very new to using policy management and was wondering if there is a way that you can check for impersonated users using Policy-based ...
4
votes
1answer
33 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 ...
0
votes
0answers
25 views

SQL Server Transactional Replication Not Being Created

I'm trying to set up transactional replication between two instances of SQL Server 2014. The publisher is a named instance running SQL Server 2014 Standard Edition. The subscriber is an instance of ...
0
votes
1answer
24 views

EXEC master.dbo.xp_cmdshell 'The user name or password is incorrect'

I am trying to move a file from a network drive to another server and when I execute this statement. I get an output of The user name or password is incorrect DECLARE @SourceFile AS ...
-7
votes
1answer
131 views

How can I intentionally grow my transaction log to 500GB for testing purposes? [on hold]

I have been tasked with creating a 500GB transaction log with the intention of developing an automated transaction-log shrinker. I need to be able to record the time take for this task. How can I ...
1
vote
2answers
49 views

Query to send mail to multiple users with their corresponding tickets

I have a table which has a list of ticket owners and and tickets which are assigned to them which are of high priority. For example: I need to send mail from SQL Server using sp_send_dbmail to ...
4
votes
1answer
53 views

Advice on how to improve the efficiency of a stored procedure using lots of joins

I have a stored procedure that is returning search results for items in a database grouped by customers who own them. The user performing the search is restricted as to what items they can see by ...
4
votes
0answers
27 views

How to change default port for T-SQL Debugger

The default port for T-SQL Debugger is 135, it is possible to change this port ? In my company this port is on special list of banned ports, therefore we need to open that service on another port ...
-1
votes
1answer
40 views

SQL Server installation location [on hold]

What is the best practice recommendations for installing SQL Server 2012 or 2014 with regards the installation files location. Can the installation files location resides on the system drive or should ...
0
votes
1answer
29 views

Reduce the allocated size of a DB file [duplicate]

Is it possible to reduce the initial log-file size of a DB. See the screen-shoot below, the log-file shouldn't be more than 500 MB with an autogrowth of 100 MB. I dont want to shrink the file, i want ...
1
vote
2answers
26 views

Detached DB showing up in back-up history

I found a script to show me the databases that are not backed-up in the last 24 hours: Script to retrieve SQL Server database backup history and no backups SELECT CONVERT(CHAR(100), ...
2
votes
0answers
24 views

SQL AlwaysOn & System Center Configuration Manager TP 4 1601

Recently Microsoft released Technical Preview 4 for Configuration Manager (1601). Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the site ...
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 ...
3
votes
2answers
212 views

Stored procedure returns -4 after catching exception

In a stored procedure, I am finding that if it successfully inserts a record, it returns 0. If it fails on a primary key violation (which is caught and ignored), it returns -4. Where is the -4 coming ...
4
votes
2answers
36 views

SSIS OLEDB Command not firing triggers

Got a bit of a strange one that I can't seem to work out. We have a vendor database ( so we have no control over the table structure, foreign keys, etc...) that's on SQL Server 2012 SP2. We have a ...
2
votes
1answer
26 views

How can I have flexible PIVOT column names?

Following the example in https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/, I have a PIVOT that works but is not very flexible. It is basically SELECT * FROM ...
1
vote
2answers
94 views

Which data type should I use to store a 11 digit number [on hold]

Which data type I should use to store an 11 digit number? It must store '0' as the first digit. For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows ...
6
votes
2answers
229 views

Add [Is Primary Key Column] to this query

I made this query, to list tables, its columns, data type and etc: select t.name as 'Table', c.name 'Column', ty.name 'Data_Type', c.max_length as 'Max_Lenght', ...
2
votes
0answers
39 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 ...
0
votes
0answers
31 views

Unable to create availability group

While trying to create an availability group, I am getting the below error. Can someone find the root cause of this? I even granted "create computer objects" permission, but it still did not work. ...
0
votes
1answer
17 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 ...
0
votes
0answers
36 views

SSAS MD dimension joins to same table twice (but processing fails as attribute has 2 names for same ID)

My cube has a product dimension, and each product has an "availability" status attribute. In the database, SellableItemAvailabilityId in DimSellableItemVersion (which contains products) joins to Id in ...
2
votes
1answer
34 views

Do Sequences Have the Same Replication Range Limits As Identities?

With merge replication, if the subscriber runs out of identities for a range when inserts occur into it, the subscriber will throw an error on the insert into the table, because it is the publication ...
0
votes
0answers
34 views

Headers and footers for Crystal Reports [on hold]

I have three tables: Employee [EMPNAME, EMPID] ProductList [ProductName, ProductCode] Orders [EMPNAME, ProductCode, ProductName, DeliveryDate, OrderDate, TOTAMT] I am trying to ...
3
votes
3answers
214 views

Can I run a CLR Stored Procedure on a different server than the database instance?

I want to add a trigger whenever a row is inserted into a certain table. The trigger will fire off a CLR stored procedure which will do things other than database work, such as write a JSON string to ...
6
votes
2answers
94 views

Unable to create restore plan due to break in LSN chain

My backup plan is: Full backup - once a day Differential backup - every four hours Transaction Log backup - every 30 minutes. All database backup are stored on my computer. I add some wrong ...