SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (1)

2
votes
0answers
16 views

TempDB contentions

Recently I have faced a strange issue. We have an active OLTP 40GB database on SQL Server 2014 SP1. Queries are found slow with IO_Completion wait and Disk Queue Length rise to 900 and SQL Server stop ...
0
votes
1answer
11 views

Why is my query using parallelism, even with MAXDOP = 0?

I'm having some headaches with a procedure. Long story short, I'm using now a hint to force an index ( now, the query runs in 0.1 secs, after this, it was running in 2min ) but the question is, why ...
1
vote
3answers
36 views

How do I handle multiple rows with variables defined by a query?

I am trying to write a query that uses variables to feed into a sub query. Query: DECLARE @Code VARCHAR(5) DECLARE @Sys INT DECLARE @Key INT SET @Key = 12345 SET @Code = (SELECT Code FROM ...
6
votes
4answers
210 views

Progress of SELECT INTO statement

Our ETL flow has a long-running SELECT INTO-statement, that's creating a table on the fly, and populating it with several hundred million records. The statement looks something like SELECT ... INTO ...
0
votes
0answers
16 views

SQL-Server Full-Text Index Auto-Population Stops Working

From time to time auto population stops working (SQL Server 2014 11.0.6518). I have to use ALTER FULLTEXT INDEX ON [table] RESUME POPULATION to make it work again. In the SQLFT*.LOG there is no ...
3
votes
3answers
112 views

Install Express/Standard side-by-side

I've reviewed these two existing questions: Install SQL Server 2008 Express on same machine as normal SQL Server 2008 install? Installing SQL Server Express in parallel of Standard ...and I ...
0
votes
0answers
28 views

How to update large tables in real-time without hindering performance in SQL Server 2014?

We are a SQL Server shop, currently on SQL Server 2014 Enterprise. We have an issue with updating production tables in real-time. The boss thinks mirroring is the answer, but I am not so sure. ...
0
votes
1answer
15 views

“Unable to Open BCP host data-file” using xp_cmdshell from SSMS but command line works

I have a stored procedure that runs on a job every once in a while to BCP some files left by an application I've written. I noticed the files were piling up and BCP wasn't picking them up so I ...
4
votes
2answers
131 views

How to optimize this tricky SQL self-join?

I want to perform a complex self-join on a table. I know that this could in theory be done very efficiently (see below), but I have trouble getting SQL (on Microsoft SQL Server) to do so. My question ...
3
votes
1answer
38 views

Why does Veritas backup take longer through CMDEXEC than cmd?

"c:\...\dbbackex.exe" -f "C:\...\backup.bch" -p policy_name -s server_name -np The above command, when run from command line takes something like 30sec to finish for a small db or transaction logs. ...
3
votes
2answers
88 views

Ordering groups/partition windows by other column values

I have a sample from a database I'm using in SQL server 2014 you can use here http://sqlfiddle.com/#!6/0d98e/1 It looks like this ...
0
votes
1answer
22 views

Practicing AlwaysOn on SQL Server 2014 - Windows 10 Home

TL;DR: What possible solution(s) are there to practice AlwaysOn feature on SQL server 2014 installed on Windows 10 Home edition? I am learning Database Administration on my own and have installed ...
0
votes
1answer
33 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 ...
3
votes
1answer
41 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 ...
7
votes
3answers
80 views

Find the source of a recurrent mass SQL edit on a server

I'll try to explain my problem as clear as possible. The server of a company I support runs many websites. This server runs Windows Server 2012 with Microsoft SQL Server 2014. Almost all of the ...
0
votes
0answers
30 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 ...
-1
votes
1answer
41 views

SQL Server installation location [closed]

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

Managed backup to Azure does not delete backup after retention period

I did set up SQL Server managed backup for our databases. I did define retention days to 4, but i just checked the azure storage account where the managed backups are and found that there is still all ...
1
vote
1answer
20 views

get number of events per day from profiler trace

I am trying to do something that I thought would be simple but turns out it is not (for me). I have 5 days of profiler trace files that I have imported to a single table. I want to query the table and ...
6
votes
2answers
241 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', ...
0
votes
0answers
36 views

Headers and footers for Crystal Reports

I have three tables: Employee [EMPNAME, EMPID] ProductList [ProductName, ProductCode] Orders [EMPNAME, ProductCode, ProductName, DeliveryDate, OrderDate, TOTAMT] I am trying to ...
1
vote
2answers
8 views

Most restricting permissions for querying view with linked server

I've created view to query another server via linked server(SQL Server 2014). I need to grant permissions to developers to use this view. Granting only select is not enough and they got the following ...
1
vote
1answer
38 views

Compare Timestamp Difference Between Consecutive Records

How do I compare Timestamp difference between 2 consecutive records? This is my example: DECLARE @x TABLE(ProductID INT, SingleRunFailure bit, SampleDate date); INSERT @x VALUES (101, 1, ...
-1
votes
1answer
40 views

How can you allow only the database owner and system administrator to use a database? [closed]

I am on SQL Server Management Studio 2014. I need to allow me, the database owner and the system administrator to use a database. Also, can both users use the database at the same time?
1
vote
0answers
44 views

Can I create a referential integrity constraint in SQL Server that prevents time overlaps? [duplicate]

It is trivial to create a Constraint on uniqueness for an integer value, but I am wondering if it is possible to create a constraint on overlap/intersection of a range of values. For example, my ...
1
vote
1answer
38 views

SQL Server Import Export Wizard Error - Server throw Exception

I am trying to move all database tables from one db to another as a nightly job. The first (export) db is running sql server 2008 - SQL Server Authentication - I connect with SQL Server Native Client ...
8
votes
2answers
84 views

SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index

I'm running a 350GB database on my PC with ~40 million rows. SQL Server 2014, Win7, AMD 8350 @ 4.8GHZ, 16 GB of RAM, and a 500 GB SSD (database is hosted on it's own 500 GB SSD, with a ...
1
vote
1answer
20 views

Minimum permissions required for mail attachment in DbMail

I know that SQL DbMail requires for attaching, a windows login or a sysadmin privilege is required. This is somewhat similar to minimum dbMail permissions which was asked by me at an earlier point. ...
2
votes
1answer
61 views

SQL Server 2014 Minimum Memory configuration - VMware

I am taking over a SQL Server 2014 environment which has been built and configured by a consultant. They have set a minimum value for memory which I usually set to 0. According to the consultant ...
3
votes
0answers
30 views

How to PUSH data between a Local DB Server to Cloud DB Server?

I have an implementation doubt regarding replicating data between SQL Server 2014 database servers (Replicating data from Local Premises SQL Server to Cloud DB Server, which are not in same domain or ...
0
votes
1answer
40 views

Copy A Database

Is it possible to copy a SQL Server 2008 Express without launching SSMS? Little more back-story: my internal drive was removed and is now used as a USB drive (upgraded to a larger hard drive) and ...
0
votes
1answer
31 views

Restore from SQL Server 2014 Enterprise to BI

Is it possible to restore a SQL Server 2014 Enterprise database with table partitioning (Production) to a SQL Server 2014 BI Version (Test/Development) ? Of course I don't expect to use the table ...
-1
votes
0answers
23 views

Service Broker needs to access the master key in the database

How do i resolve this error? I am using SQL Server 2014 and the database is trust worthy. The master key in the database 'database_name'. Error code:32. The master key has to exist and the service ...
1
vote
1answer
36 views

Return Distinct Records by Category Based Multiple Column Values

ID MODEL MODELSTATUS CONTROL 11 100 GOOD XN 24 100 TRENDING BF 33 101 GOOD XN 46 102 BAD BF 50 103 BAD ...
3
votes
2answers
68 views

How can I extract the perfmon counter “Memory: Pages/Sec” using t-sql?

I've googled this and so far, no luck so turning to all you awesome gurus for help. I'm trying to figure out if there's a way to get the perfmon counter "Memory: Pages/Sec" using t-sql as I'm trying ...
1
vote
1answer
55 views

Compare strings from two tables SQL Server 2014

I'm using these two tables in a SQL Server 2014 database: CREATE TABLE #FORMULAS ( [ID] [int] IDENTITY(1,1) NOT NULL, [DataSourceAddress] [nvarchar](255) NULL, [Description] ...
0
votes
0answers
25 views

Backup plan fails with error code 0x80004003

We have a maintenance plan that backups logs every 10 minutes at primary AlwaysOn group(don't sure that this is relevant info). Once in a while this plan's job fails with error: Code: 0xC0024104 ...
2
votes
1answer
44 views

Database in SIMPLE recovery model full using SSIS bulk insert task

I have a database in SIMPLE recovery model with 10GB free. The file I am trying to load through SSISs bulk load is 1GB. The table is empty (truncated prior to each loading), is a heap and has no ...
5
votes
1answer
77 views

Multi-Datacenter design for SQL Server

I'm not a DBA and perhaps I can't explain myself properly. I'm looking for some fresh ideas about how, and what I need to deploy, to get a full SQL Server database available across different ...
5
votes
0answers
58 views

Restored DB has data, DBCC CHECKDB is clean, but SELECT TOP 1 returns zero rows

Having restored a client's SQL 2014 EE DB onto our Developer Edition instance, without issue, then running a DBCC CHECKDB that found zero errors, and a clustered columnstore index alleging over 1Bn ...
1
vote
1answer
37 views

Return Bit Value based on previous records by Category

How would I return TRUE or FALSE if four of the last six records failed for a given MAKEID? So there has to be 4 failures (or FALSE records) in the last six MAKEID FAILURES CREATEDATE 101 ...
4
votes
2answers
71 views

Get 2 Most Recent Records By Product

I am stumped. I need to return a TRUE or FALSE by ProductId if the 2 most recent records created are 'pass' and the LEVEL values are different. PRODUCTID CREATEDATE STATUS LEVEL ABC ...
0
votes
3answers
22 views

Set Value Based on Number of Records with Certain Values using SQL Server

I am stuck on how to return a bit value based on records with certain values. Query needs to return TRUE or FALSE if there are three different Levels of the same Control: Example data: PRODUCT ...
0
votes
1answer
23 views

Scenarios in which you might need to restore large number of databases at once?

I've seen several online sources mentioning that you should always have a restore script automated for the possible need to restore a large number of databases at once and quickly get back up and ...
0
votes
0answers
36 views

Connecting to SQL Server 2014 with sqlcmd

I am using the version of sqlcmd that comes with the Microsoft ODBC Driver 11 for Sql Server to connect to a remote 2014 instance. I can create a DSN connection using the SQL Server Native Client ...
0
votes
1answer
49 views

Restored database from .bkp file in SQL Server 2014 generates some strange errors [closed]

I have restored the well-known Northwind database and executed a trivial query: Notice the strange error "Invalid object name..." although the query has been executed successfully and both tables ...
0
votes
1answer
18 views

SQL Server 2014 - Disabled All Admin Users [duplicate]

I made the most ridiculous thing. I accidentally disabled both remote logins and database accesses for the Administrator accounts (including the one with Windows Authentication (Administrator account) ...
1
vote
0answers
24 views

Change default value for Job Activity Monitor

Is there a way for me to programmatically (some configuration setting in a table / registry entry etc, I'm hoping) change the default value in the Auto Refresh setting to 5 seconds instead of 60, and ...
1
vote
2answers
44 views

How to turn this query into a Pivot?

If theres something I can't learn, is PIVOT. People are trying to teach me this, I'm reading blogs about this but I just can't understand the logic. How can I turn this query , into a pivot? The ...
2
votes
1answer
60 views

What does Error: 1204, Severity: 19, State: 4 mean?

I've found this error in our SQL log: "The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask ...