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

learn more… | top users | synonyms (1)

0
votes
0answers
11 views

Create update Trigger

I'm trying to create a trigger that whenever a software package is installed on a PC, the person who installed the software needs to provide data for pack, tagnum, and instdate, and the softcost field ...
0
votes
2answers
22 views

Using Select Statement to get column name and table name from the same table

I have a table containing 2 Columns : Column1 (table_name) and column2 (column_name) like this : (Column 1) (Column 2) |Column_Name | Table_Name | -------------|------------| ...
5
votes
1answer
465 views

Does a single rollback affect all transactions?

When I run the following script: begin transaction select XACT_STATE() select @@TRANCOUNT begin transaction select XACT_STATE() select @@TRANCOUNT begin transaction ...
1
vote
0answers
44 views

Deadlock in SQL SERVER 2014

I am experiencing sporadic deadlock behaviour when my application tries to execute any DROP TABLE statement against my database. I tried capturing the deadlock graph and this is the output from ...
1
vote
2answers
40 views

Join two tables using pivot combining two columns in one table

I have two tables in this format: Job_Skill ID_Skill Min_Job Idea_Job Max_Job ===================================== 1 0 0 1 2 0 1 1 7 1 ...
6
votes
3answers
97 views

Query wont compile/run

I have a query that won't run on the production server but it runs on the test server. It seems like the query plan won't compile or its compiling a very bad query plan. I have updated all statistics ...
3
votes
1answer
53 views

Inconsistent Execution Plan for Stored Procedure

We have a business-critical stored procedure that normally runs daily at 2am from a scheduled job (in an SSIS package) on the production DB server. The same procedure/package is called from a second ...
7
votes
0answers
73 views
+100

Keeping (Partial) Backups Small when using SQL Server FILESTREAM

I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the ...
1
vote
2answers
84 views

sys.dm_sql_referencing_entities not returning results

No doubt this is syntax or typo...but when I query sys.dm_sql_referencing_entities for a parent table, I get zero results. No error, just no results. USE [source]; GO SELECT * FROM ...
7
votes
2answers
260 views

SQL Server not using all memory

I have SQL Server 2014 with max memory set to 6GB (physical memory is 8GB). The Target Server Memory is sometimes 6GB and then drops back to Total Server Memory (approx 5.3GB, never reaches 6GB). I ...
0
votes
0answers
24 views

Missing database on a Linked Server

I have a MS SQL 2014 (web ed) that's on the Azure cloud (Server A). I have linked this server to an onsite SQL 2014 server (server B). On server A, I can see MOST of server B's databases but not all. ...
0
votes
0answers
28 views

best practice to remove leading zeros when writing data to a destination

I have a query like in my other question DBA Question with Source Code of Select statement And i want to remove the leading zeros of the customer Number (KUNDE) with the answer of this question ...
1
vote
2answers
37 views

I'm confused about High Availability/Always On

is High Availability something to describe Log Shipping and Always On ( replica )? is Always on something to describe Log Shipping and High Availability? I'm studying to get my certification 40-762 ...
5
votes
3answers
137 views

Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4. After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad ...
3
votes
1answer
71 views

TempDB uses a lot of space in buffer pool

I'm running a SQL Server 2014 with 2 cores and a max memory of 6GB. When I'm checking what's in the buffer pool, I see that TempDB uses 1GB of memory. That's the query I used: SELECT COUNT(*) AS ...
4
votes
1answer
37 views

How can a database parameter be used on `sp_addextendedproperty`?

I have found the MSDN definition and code examples of 'sp_addextendedproperty'. In Example A on that page, I see an example of how to add an extended property to a database. USE AdventureWorks2012; ...
0
votes
1answer
38 views

ODAC OLE DB provider “OraOLEDB.Oracle” for linked server supplied inconsistent metadata for a column

I am using SQL Server 2014 linked server to connect to an Oracle 11gR2 (11.2.0.3) database, using the ODAC 12.1.0.2.4 Oracle OLE DB Provider. I have a stored procedure that lives in a SQL 2014 ...
3
votes
1answer
39 views

Apply SQLServer Service Pack to a Single Node Cluster

I have to apply a SQL Server 2008 R2 Service Pack to a Single Node Cluster. The other node died and new hardware has been ordered. Is there anything weird to worry about? Normally we'd apply the ...
2
votes
1answer
46 views

Prevent SQL Job Agent from interpreting a quotation mark as apostrophe

I'm struggling with my SQL Job Agent interpreting a quotation mark " as an apostrophe '. I have a T-SQL query which works in SQL Server Management Studio but not when I insert the exact same ...
2
votes
2answers
27 views

Transactional replication subscriber vs AG secondary replica for reporting

I am trying to recommend my client a SQL server upgrade (from 2005-> 2014) which will allow the users to create record snapshot (via SSRS) from a read-only data source. Data latency should be kept to ...
0
votes
1answer
13 views

Enabling IPAII protocol disable remote conections. Why?

I have some servers with IPAII protocol enabled with a certain port: I'm using 1433 because a long story. but its working. We can use the default port 1433 AND IPAII ( for PHP developers ) with ...
3
votes
1answer
46 views

How does SSMS get a list of files in a backup?

When restoring a database that has multiple files, SSMS gives you the option to restore as other paths and file names. How does it retrieve the list of files from the backup? Can I do that in a SQL ...
10
votes
1answer
172 views

Where does SQL Server physically store the IDENTITY VALUE for a table?

I'm hoping someone can point me in the right direction on this one. Here's my workings out so far. SELECT * FROM sys.identity_columns is a system view which gives "last_value" but the definition for ...
3
votes
2answers
231 views

Identify the type of a SQL Server backup

Long story short, we had a hosting plan on www.hostforlife.eu (btw, terrible service even on more expensive packages, plus they lost ALL files on their servers and we had to restore our backups as ...
1
vote
1answer
62 views

SQL Server 2014 SP1 installation iso file question

I read a lot regarding the issues with the original SP1 slipstream file and if the file names to download can be clarified it will be very helpful. What files do I need to install SQL 2014 SP1 CU6? ...
1
vote
1answer
20 views

Is it possible to attach a description field to the records in INFORMATION_SCHEMA.TABLES?

In SQL Server, [database name].INFORMATION_SCHEMA.TABLES has the following columns: TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE This table has no additional fields, such as "DESCRIPTION" ...
4
votes
1answer
91 views

Update with default values in case clause

I have a single table like CREATE TABLE Mytable (ID int identity, Name nvarchar(10)); GO INSERT INTO MyTable (Name) VALUES ('test1'); INSERT INTO MyTable (Name) VALUES ('test2'); GO ALTER TABLE ...
1
vote
1answer
149 views

Is select case when null better than iif(exists)?

I try to reduce huge performance issues on our application database. We use SQL Server 2014 and we have a lot of views. I wonder if there is a significant impact of rewriting left joins to use ...
0
votes
1answer
705 views

Backup SQL Server to Google Drive

I have Microsoft SQL Server 2014 on an Azure VM (Windows Server 2012 R2) with one database. Is it possible to configure an automatic backup to my Google Drive folder? Maybe a special tool or ...
4
votes
1answer
112 views

Identical tables, identical query, completely different execution times

We have two tables with identical columns and indexing (No indexing at all, basically). We run the same query, which in case of the original table takes 5 seconds to run; in the case of the new table ...
1
vote
1answer
32 views

how to clear only the query plans from the current session?

I am working on tuning a query on a very busy server, where I don't want to clear the cache, using DBCC FREEPROCCACHE; I know it will cause at least a CPU spike, and in this case I don't want it. ...
-1
votes
1answer
23 views

Microsoft SQL Server Not Showing Server Name in object Explorer [duplicate]

I Recently Installed Microsoft SQL Server 2014 (SSMS) on my local machine for development learning purposes. I am running Windows 10 32 bit. When i try to connect, Server Name does not appear. Screen ...
0
votes
2answers
42 views

What are the SET options that affect Plan Reuse and how to get their values in T-SQL?

I'm troubleshooting the case when a stored procedure runs great from the management studio at any time, but the same stored procedure can run terribly badly in one of the webservers, even for the same ...
4
votes
5answers
134 views

Is there any best number for rows count in a table to start using indexing?

I'm a newbie in DBA. I want to know if there is any specified point in a table's rows count that when we reach that point, then we could use an index. I know there should be some dependency between ...
0
votes
0answers
25 views

How to merge multiple SQL Server DBs to one central DB

I am working on 25 identical DBs (SQL Server 2014) installed in 25 Branches. I need to merge the data of these DBs into one central DB, daily.I tested Replication (merge) method. As my servers are ...
2
votes
2answers
44 views

Separate license needed for Visual Studio 2012 SQL Server database projects? [closed]

We have SQL Server 2014 Standard Edition and would like to use Visual Studio 2012 Database Projects for version control. Do we need to purchase a license to use VS database projects? Or is it ...
1
vote
0answers
27 views

Synchronization report for uncontained objects across each of the replicas in HAG

I have seen How to transfer logins and passwords between instances of SQL Server , Synchronize Availability Group Logins and Jobs and SQL Server 2012 Availability Group Add-in to synchronize ...
7
votes
1answer
154 views

Query against sys.schemas and sys.synonyms runs very slow for one user

Scenario: SQL Server 2014 (v12.0.4100.1) .NET Service runs this query: SELECT name, base_object_name FROM sys.synonyms WHERE schema_id IN (SELECT schema_id FROM sys.schemas ...
1
vote
1answer
30 views

linked servers between availability group to a server

I had three servers(server1, server2, server3) in availability group. And the listener name is "Production". I have a server name "Acceptance". I need to run a script that which will copy the tables ...
3
votes
1answer
36 views

is there a way to find out who was spid228 at a given time in the past?

I have a script that reads the error log so that I can see what has been happening within the last day: --create the table CREATE TABLE #Radhe (logdate datetime not null default (getdate()), ...
3
votes
1answer
589 views

Is SYSUTCDATETIME a safe primary key?

I have a serially modified table of processed events. A utc time for when it was processed and an eventId. Is it safe to use SYSUTCDATETIME() as my primary key? I can easily use a surrogate key but ...
1
vote
0answers
31 views

Failed to allocate reserved pages: FAIL_RESERVED_PAGE_ALLOCATION

Last night I received the error on one of my servers of: Failed to allocate reserved pages: FAIL_RESERVED_PAGE_ALLOCATION 2047 I know that this has already been discussed on here some but all ...
6
votes
2answers
105 views

how to get estimated subtree cost?

If I have a query that returns a query_plan, for instance like this: SELECT TOP 1000 st.TEXT ,cp.size_in_bytes ,cp.plan_handle ,QP.query_plan FROM sys.dm_exec_cached_plans AS cp CROSS ...
4
votes
1answer
60 views

SQL Server: no search results caused by noise words

I've moved a database from SQL Server 2000 to SQL Server 2014 via an import/export through SQL Server 2008 to make it compatible to be imported in 2014. So far that has worked fine. I found out that ...
7
votes
1answer
87 views

how come a table variable is improving the performance of a query in this circumstance?

for this specific case, that I will try to explain below, using a table variable is performing better than not using a table variable. I would like to know why, and if possible, get rid of the table ...
4
votes
0answers
35 views

HADR high worker thread usage

Why would an availability group's number of worker threads in an HADR pool increase well beyond minimum usage of "typically, there are 3–10 shared threads" per replica? In one case we've observed ...
1
vote
0answers
23 views

Matching table (with sub-tables) to another table(with sub-tables) efficiently

I have a problem in an ETL process. I need to integrate data into a data warehouse. The data is associated with a "variant". A variant is defined as having a specific set of parameters (identified by ...
3
votes
1answer
100 views

Adding custom language to SQL Server

I would like to use SQL Server @@langid variable to return different data based on current connection language (set in connection string or with SET LANGUAGE). This works perfectly well for built-in ...
5
votes
2answers
422 views

Custom ID Numbering - fast way on insert?

As start: this is for an ETL stored procedure that is serialized, so paralellism is not of concern. I need to assign custom ID numbers for markers during loads. I can not use an identity field, ...
1
vote
1answer
79 views

Install SQL Server 2014 SP1 on VMWare workstation 12 player

I am not able to install SQL 2014 SP1 on VMWare workstation 12 player. Please review my post and give me some feedback on how to resolve the issue. Your input is greatly appreciated! Hardware ...