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

learn more… | top users | synonyms (3)

-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' ...
6
votes
2answers
189 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) ...
3
votes
2answers
949 views

Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". I'm not looking for a debate over NULLs and ...
5
votes
1answer
244 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
0answers
25 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, ...
22
votes
4answers
2k views

Copying (hundreds of) tables from one server to another (with SSMS)

I have several hundred (currently 466, but ever growing) tables I have to copy from one server to another. I have never had to do this before, so I'm not sure at all on how to approach it. All the ...
7
votes
3answers
19k views

Can SQL Server Management Studio Express be installed standalone?

I was wondering if the SQL Server Management Studio Express (mainly for Microsoft SQL Server) be installed on a standalone machine (without any of the SQL Services and stuff) to connect to a remote ...
7
votes
3answers
31k views

Management Studio System.OutOfMemoryException

I'm using Microsoft SQL Server 2012 and trying to run a simple query against it within Management Studio. I'm getting the following error: An error occurred while executing batch. Error message is:...
7
votes
1answer
2k views

How does SQL Server generate a query execution plan that adds up to 6,000%? [duplicate]

Today I was on The Heap and was looking at a query plan for something I thought could be improved. However, it created something that shook my belief in the SQL Server query optimizer. Can I still ...
0
votes
3answers
14k views

Cant see SQL Server Agent in SSMS [duplicate]

I have SSMS 2012 installed on my laptop, and SQL Server 2012 on my hosting, but when i try to schedule a sql job i didn't find "SQL Server Agent" in the left side list!!?
2
votes
2answers
3k views

Error creating database in SQL Server Management Studio. (Could not load file or assembly 'Microsoft.SqlServer.Sqm)

I'm trying to create a new database in SSMS, but I get this error: Could not load file or assembly 'Microsoft.SqlServer.Sqm, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or ...
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 ...
3
votes
1answer
110 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 ...
10
votes
2answers
12k views

Check progress of alter index reorganize / rebuild

How can I check the progress / status when I submit an alter index reorganize / rebuild ?
2
votes
1answer
5k views

How to do unattended install of SQL Server 2012 Express from the Enterprise media?

I have a requirement to install SQL Server 2012 Express on some machines, and SQL Server Enterprise on some others, and I'm attempting to avoid using separate packages to do so. It's pretty simple to ...
3
votes
2answers
565 views

Targeting MSX with job on Multi-Server Administration

I am using Multi-Server Administration to execute jobs on multiple targets. This works well, but I am not able to enlist the master as a target. I receive the following error when attempting to enlist ...
0
votes
1answer
85 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 ...
0
votes
1answer
213 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
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
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 [...
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 ...
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
vote
1answer
188 views

What is the fastest way to compare polygon geometry data?

We have two tables with polygons stored in geometry type columns. I want to fetch the polygons present in one table that are not present in another table. As of now I am doing a left outer join ...
-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 ...
6
votes
2answers
170 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 ...
-1
votes
1answer
1k views

can exec permissions be granted to a role in SQL Server 2008 / 2012

Can exec permissions (e.g. on a SP) be granted to a role in SQL Server 2008, so that all role members then have the permission; and if the 2008 database were backed up and restored into SQL Server ...
4
votes
1answer
180 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 ...
3
votes
1answer
45 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
2answers
3k views

Intellisense in SSMS 2012 not working for 2008 server

I did most of the solutions provided here. I know about enabling IntelliSense clearing the cache connecting to a server >= SQL Server 2008 using the latest SQL Server Service Pack and it works ...
1
vote
2answers
102 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 ...
1
vote
0answers
59 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 ...
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
121 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$]') ...
7
votes
1answer
302 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 ...
2
votes
1answer
78 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
857 views

SQL Server 2012 - Large backup file size

We have the following setup: Once a week (Sunday at midnight), a full database backup is performed. A new BAK file is created for this purpose, let's call it Backup1.bak. From then, a transactional ...
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 ...
16
votes
2answers
968 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 ...
6
votes
1answer
264 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
158 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 ...
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
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....
3
votes
3answers
216 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 ...
0
votes
1answer
337 views

SQL Server job runs forever after server restart

One of our ETL servers had to be restarted and a job was in the middle of execution. It was on the 2nd of 6 steps and now the job agent shows that it has been running for 15 hours back on step 1. ...
11
votes
4answers
33k views

Is there a way to generate table create script in TSQL?

Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a ...
3
votes
1answer
86 views

Can SQL Server 2012 FCI have multiple names?

Is it possible to have multiple names for the same SQL Server Failover Cluster Instance (virtual server)? IT has a naming convention they like, but it hard codes the Windows cluster name into the SQL ...
1
vote
3answers
2k views

SQL Server express - Management studio installation hangs at native images installation

I've been trying to install SQL server express edition (2014) with management studio and consistently failing to do so because installation stalls at step 'Install_sql_common_core_Cpu64_Action : ...
1
vote
1answer
302 views

non clustered index on same table but the order is different?

I have a table with 5 columns col1...col5 . I need to create a non clustered index on col2,col3,col4. I did, then my boss is asking me to create one more non clustered index on col4,col3,col2. It ...
-1
votes
2answers
101 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 ...
10
votes
1answer
122 views

Job not running on Schedule

So I have a basic SQL agent job that runs a Robocopy script to move all files from one folder to another. Job is a pretty basic set up. With a pretty basic schedule. And yet it has yet to run. ...