SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (3)

2
votes
1answer
66 views

SQLCLR assembly throws error when multiple queries run simultaneously

I have been using SimMetrics to implement the QGramsDistance search in SQL Server. The SimMetrics is used as a SQL CLR assembly. The search results are promising and everything was well, until of late,...
6
votes
2answers
180 views

list of batch aborting errors in SQL server

In SQL Server, if XACT_ABORT is off then some errors will terminate the current statement (for example supplying the incorrect number of parameters to a stored procedure that takes some parameters) ...
2
votes
1answer
117 views

Why SQL Server Express 2012 take automatically backup in “Virtual Device” without virtual device configuration?

I have noticed that after migration of SQL Server 2012 Enterprise to SQL Server 2012 Express, every day it shows "spurious" virtual backup after taking manual backup of database. When I execute this ...
3
votes
1answer
69 views

Is replication a suitable method to move a SQL Server database to new hardware?

I need to move a live database with about 100 GB of data to a new machine. As I understand the easiest way to do that would be to take it offline, move the files and bring it back online. But: The ...
0
votes
0answers
24 views

Optimizing the UPDATE For MSdistribution_history

One of the major headaches in my replication is the following MS procedure (over 55% usage), updating the distribution history. It currently has one index on columns agent_id, timestamp, runstatus, ...
1
vote
2answers
86 views

Ordering of a dimension in sql server datawarehouse

Built and deployed a simple cube with a date dimension. The dimension has things like day of month. How do i sort it? it always shows in the reports as 1, 10, 11, 12, ... 19, 2, 20, 21 Design. ...
3
votes
1answer
73 views

Kick a single user out of the database

I've seen multiple answers about kicking all of the other users but is there a way to stop the process id and kick just one user out of the database. Eg. user A B and C are connected to the database ...
-1
votes
2answers
45 views

because of Date format im not able to get few records [closed]

For example declare @string varchar(30) = 'dec. 24, 2015' select substring(@string, 10, 4) declare @output date select @output = case when substring(@string, 1, 3) = 'Dec' ...
1
vote
1answer
232 views

SQL Server Management Studio 2012 Application Cannot Start

I need someone's help. I'm trying to install SQL Server Management Studio on my Laptop Operating System: Windows 7 Service Pack 1 But I got an error message saying: The application cannot start. ...
0
votes
1answer
83 views

SQL Agent Proxy - writing to logs is only allowed to jobs that are owned by sysadmin

I have a Windows 2012 RS and SQL Server 2012 Enterprise Edition. I have SQL Server maintenance jobs from ola.hallengren.com and the owner of the jobs were sa. The security team decided to change that ...
5
votes
2answers
3k views

Check if a user exists in a SQL Server database

I'm working with SQL Server 2012. I want to check if a user exists before adding it to a database. This is what I have tested: USE [MyDatabase] GO IF NOT EXISTS (SELECT name FROM [...
3
votes
1answer
93 views

Is it possible to extract the multiple variable parts of data into different columns without a procedure?

I'm currently trying to do an analsis of a table containing a couple thousand rows of data. I have managed to restrict the data to what I really need, but I'm not sure if I can split it up further ...
-1
votes
1answer
33 views

SQL Server view query generating too much temp

We have been noticing when a month end job which basically queries a view is generating too much temp data. The database size is 50GB (in simple mode) and the view uses two tables which are not more ...
5
votes
1answer
239 views

How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

I asked a question about validation of XML using XSD schema inside SQL Server 2012 (see link). I understand (as I suspected) that I need to use CLR Function. The function will get XSD schema text and ...
0
votes
1answer
202 views

Instance name is null - SQL Server [closed]

I recently checked this in my SQL server instances: SQL Server 2012 EE: -- get current SQL Server name\instance name SELECT @@SERVERNAME -- get current machine name and instance name SELECT ...
3
votes
1answer
43 views

MAXDOP query for SQL Server 2012

I have a 8 core VM machine running SQL Server 2012. The current setting of MAXDOP is set as 5. There is recommendation from my colleagues to change the MAXDOP to 5 for better performance as there has ...
1
vote
0answers
119 views

Errors while accessing Excel file using openrowset

I'm seeing the below error message while running the following T-SQL statement. select * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=123.xls', 'SELECT * FROM [Sheet1$]') ...
1
vote
1answer
28 views

Why is Log File Auto Grow sometimes performed by my application user and sometimes by sa?

Many database tasks (including Data File Auto Grow, Log File Auto Shrink, and Data File Auto Shrink) are completed only by the sa user, but Log File Auto Grow seems to be associated with both sa and ...
1
vote
0answers
57 views

Collecting DB Used Space (MB) from SCOM on an Always-On DB

My client has asked for database sizing for capacity planning. I am trying to use System Center Operations Manager (SCOM), however when I get to the Always-On Cluster, it doesn't seem to display the ...
3
votes
1answer
173 views

Alternative for WHERE clause in SQL query

We have SQL Server 2012 Enterprise Edition running following query via a stored procedure: declare @TopX int = 1000 declare @stores Table (Store varchar(5), LastDate datetime, LastId int, RangeEnd ...
5
votes
2answers
119 views

Can SQL Server (2012-2014) be configured to run “on-demand?”

I am very, very new to SQL, and am having difficulty configuring the server appropriately. All I have to work with for now is a small copy of the database schema of the company I work for, with ...
0
votes
2answers
58 views

SQL temporary variable query

Not sure what am I doing wrong in this query, there seem to be a syntax error in the subquery AND typeId = (SELECT @id = typeId FROM EmpType, near @id =. I'm looking at getting the typeid in a ...
2
votes
1answer
77 views

What Host Name is my application connecting to SQL Server on

We have this 1 node broken cluster that I have been given the joy of fixing. Wont go into the reasons why its only got one node but I am planning on migrating this off onto a newly built cluster. So ...
3
votes
1answer
108 views

Starting SSIS Package through SSIS Catalog

I've developed an SSIS package to load files but it is behaving very strangely. I can kick off the package running start_execution on the SSISDB only when I log directly onto the server. Kicking it ...
16
votes
2answers
946 views

Max number of user connections

In SQL Server 2012 Standard edition, I know that the max number of user connections is 32,767. What should I do as a DBA if I am heading towards this number? Currently there are 30,000 user ...
7
votes
1answer
300 views

Log Shipping SQL Server 2012

I'm a developer at a small shop that doesn't have a DBA and i'm trying to get log shipping with sql server 2012 working. I'm trying to off load reporting from the transaction system to a new data ...
6
votes
1answer
261 views

How to Start Express SQL Server instance in Single user mode?

I want to start my SQL Server Express instance in single user mode to do some troubleshooting such as restoring the Master database. I am unable to find the "Startup Parameter" options in SQL Server ...
0
votes
1answer
152 views

update statements shows update count but not updating the table

I am facing a strange issue which I have never faced before. I have a update query like below Update A Set A.Col10 = E.Col10, A.Col11 = E.Col11 FROM TableA A INNER JOIN TableB B on A.Col1 = B.Col1 ...
6
votes
2answers
167 views

How to collect NTFS file properties and insert into SQL Server table

I have to provide reports on file system usage. I'm collecting statistics on file server usage down to individual file level so we can see who is using what files/folders, how much storage they're ...
3
votes
3answers
215 views

Selecting only one duplicate

I have a database of purchase orders and stockroom checkouts, and it seems that the stockroom checkouts tends to get duplicates inserted (we source this data from an older system nightly). What would ...
4
votes
1answer
173 views

Validate XML using XSD inside TSQL function in SQL Server 2012?

I need to validate XML using XSD inside TSQL. To make this task semi automated I need to do this inside TSQL function. To make validation I have to set value to XML and if it is not valid I will ...
-1
votes
2answers
67 views

Database BackupSize has Changed after migration from SQL Server 2012 Exterprise to SQL Server 2012 Express [closed]

I would like to say that after migration of MS SQL server 2012 Enterprise to MS SQL Server 2012 Express. I have seen there is some subtle diffence in DB backupsize as well as in "LSN" of all databases....
6
votes
1answer
86 views

SQL Server Edition for MS 70-463

I just started the MS 70-463 Implementation of warehouse, on the video course the tutor is directing towards SQL Server 2012 Evaluation Edition, but I have already have a SQL Server 2012 Express. Can ...
-1
votes
2answers
100 views

SQL query execution differs each and every time in SSMS

I have used below query to fetch maximum of 6000 records in SQL Server 2012. Select A.Date, A.City From dbo.TBL_Auction (NoLock) A Inner Join dbo.TBL_PLACE (NoLock) B On A.Place_Fk_Id = B.Place_Pk_Id ...
7
votes
2answers
319 views

8k row overflow error when updating row of size 5k

I'm trying to update a target table which has one row of size 5k to a row of size 5k also. Since it's one row it's easy to know the row actual size: select * from sys.dm_db_index_physical_stats(...
3
votes
2answers
559 views

Availability Group Listener

I'm looking at AlwaysOn availability groups. The more I look at it the more it appears that the availability listener group is a single point of failure. Where exactly does the listener actually run? ...
1
vote
2answers
101 views

How can I narrow down the cause of a deadlock after the fact?

We're using a .Net 4.5 web application with SQL Server 2012 on the same machine. We will sporadically see a cluster of "Wait operation timed out" exceptions while using the site. Occasionally we will ...
8
votes
3answers
434 views

Updating a WHERE clause to check if a value is NOT in a separate table

I have a query which uses a WHERE clause, and I happen to use the exact same WHERE clause in many queries on this table (et al). The query is: SELECT DATENAME(DW, [AtDateTime]) AS [Day of Week] ...
0
votes
1answer
89 views

SMB 3.0 file share storage limitation

What is/are the limitation/s to move from SAN storage to SMB 3.0 on windows Server 2012 - SQL server 2012 enterprise production environment? I want to know the benefit of using SAN over SMB 3.0 as ...
1
vote
0answers
143 views

SQL Server Transactional Replication Distribution Database not keeping up

We have following setup at the moment: 8 Cluster servers (SQL Server 2012 Standard Edition) talking to 2,000 store servers in the field via Merge Replication. The 8 servers are replicating to a ...
1
vote
1answer
136 views

CREATE TABLE AS SELECT creates non-nullable columns

I am using the CREATE TABLE AS SELECT statement in Oracle 11g to get data from SQL Server 2012 via a database link. Oracle creates all these tables with non-nullable columns and that causes me ...
1
vote
0answers
40 views

Trans Replication and Always On Listener Group Name

We have currently (SQL Server 2012 STD) which is continuously replicating from 8 different database server for reporting purpose to server which is on SQL 2008R2. We would like to create an Always On ...
14
votes
3answers
1k views

Why would SQL Server ignore an index?

I have a table, CustPassMaster with 16 columns in it, one of which is CustNum varchar(8), and I created an index IX_dbo_CustPassMaster_CustNum. When I run my SELECT statement: SELECT * FROM dbo....
-2
votes
1answer
71 views

Backup file from SQL Server 2012 to 2008 [duplicate]

I have a backup file of SQL Server version 2012 but I want to restore that backup on SQL Server 2008. Question: Is this possible ?
5
votes
1answer
96 views

How can I restrict the version of the target DB instance in a SSIS package?

How can I restrict an SSIS Package to only execute on SQL Server 2012? Is there a built-in way to do this (metadata?), or do I need to do a query? (Possibly belongs in a new question): I can get ...
4
votes
1answer
356 views

SQL Server 2012 Express high Commit (KB) (low virtual memory condition)

I have a customer running SQL Server 2012 Express Edition: Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Express Edition (64-bit) on ...
-1
votes
4answers
228 views

What MAXDOP setting should be used for MS SQL Server 2012? [closed]

I am getting a performance degradation error in one of my cluster production server. The error is like that A significant part of sql server process memory has been paged out. This may result in ...
7
votes
1answer
172 views

What is most efficient way to compare two large record sets in SQL Server 2012

Current advice for the most efficient way to compare two large record sets seems to be to use EXCEPT. This self contained SQL script below gets very inefficient as record sizes increase (change @last ...
4
votes
1answer
47 views

Is it possible to connect MS SQL Server Express 2012 through DAC Connection?

Is it possible to Connect MS SQL Server 2012 Express Through DAC. After moving from SQL Server 2012 Enterprise to MS SQL Server 2012 Express, some performance problem were there. When i have checked ...
3
votes
1answer
493 views

SSAS cube process time increased after making fact and dimension small

I have a cube of 55 GB size which takes around 2 Hour to process full. As I have data for last 4 years but our business wants only 2 years of data. For that, I have changed dimension and Fact views to ...