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

learn more… | top users | synonyms (3)

0
votes
0answers
4 views

How to import one varbinary(max) column with bcp

bcp reacts with oversize message on importing a jpg-file. azure does not allow to use bulk operations. how to import a varbinary(max) with bcp?
0
votes
0answers
13 views

ERROR: Arithmetic overflow error converting float to data type numeric

I unfortunately have some bad data and need to work around it. I have a Parcel column in a spreadsheet with some parcels that have inadvertently been converted to floating point numbers. I posted a ...
1
vote
1answer
31 views

Copy primary keys/clustered indexes from Prod to DEv/Test env

We followed the best practices of creating Primary Keys/Clustered indexes on tables which are actively queried on prod. Now there is a scenario where we need to get the similar above changes done ...
0
votes
0answers
16 views

What is the max number of characters of a string column can be returned by a Stored Procedure in SQL Server 2012?

My SQL Server's version is Microsoft SQL Server 2012 - 11.0.5058.0 (X64). I have a table with a column "Token" which stores string of about 152 characters. I need a procedure which returns all the ...
-1
votes
1answer
21 views

How to list all the views and its base object which has unresolved table or other object referece in a paricular database

I want to list all the views which are made up of base objects. That base object is no longer available.Thanks
1
vote
1answer
36 views

How to search by text in SSIS packages? [duplicate]

Is there a way to search by text in SQL Server Integration Services packages using T-SQL statement (or other language)? I want to check if certain table is used in any package as I am going to drop ...
6
votes
3answers
935 views

What is the effect of having an opened transaction in MSSQL for too long?

I'm just wondering what happens if you begin a transaction in a DB and forgot to commit or rollback it. Will the server be down? Lets say you left it for 3 days. There are also users who are using it ...
-3
votes
0answers
13 views

Collect statistics for whole SQL

I am quite new to SQL Server. I would like to collect some SQL statistics for the SQL Server, including Compilation/sec, Recompilations/sec and Batch Requests/sec. However, I have no idea how to ...
1
vote
1answer
53 views

Contradiction in book related to variable scope in Stored Procedures

I was reading the 70-461 TSQL training book and the part about stored procedures said this: Variables declared in Proc1 and Proc1's parameters are not visible to any of the procedures called by ...
0
votes
1answer
81 views

Update single table with mutually exclusive threads

I have 2 threads (for example 2 different sql sessions) reading rows of the same table in parallel. They will do some processing and update a column to inform the row has been processed. Thread id:...
2
votes
1answer
29 views

Grant sysadmin permissions to 'NT AUTHORITY\SYSTEM'

As you all know, Microsoft SQL Server version 2012 and above are not enabled any more sysadmin role flag to Local System (NT AUTHORITY\SYSTEM) account. In my case it's becomes a problem: I have a WiX ...
1
vote
0answers
23 views

SQL Server Maintenance Plan Help

SQL Server Maintenance Plan Help I'm using SQL Server Maintenance and found a way to selectively turn on and off various features of the plan via it's associated job shown in Figure A. This can be ...
1
vote
1answer
17 views

Will Installation of Service Pack 3 on SQL Server 2012 Web Edition (existing production) require database restore activity?

We are currently running our Database Server on a RTM version: Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Standard Edition (...
0
votes
2answers
22 views

KILLED/ROLLBACK query doesn't stop running

I've tried to kill a query on ms sql server management studio. Now it has the status killed/rollback. When I use: kill 74 with statusonly it doesn't make any progress. It is still at 0%. How can I ...
-5
votes
0answers
50 views

Is it possible to take MS SQL Server 2012 backup in particular date range? [closed]

Is it possible to take MS SQL Server 2012 backup in particular date range?
0
votes
0answers
12 views

Restoring a backup of 1 database to another puts both databases into Restoring mode

We have a 2 databases- Staging Test At times, we refresh out Test environment with a copy of Staging. We do this by taking a full backup of the Staging database and restoring it over the Test ...
1
vote
1answer
57 views

SQL Server TempDB size issue

SQL Server reported that our tempdb is out of space. So I had a quick google search and found this on technet https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx Determining the ...
7
votes
1answer
961 views

Using Two LEFT JOIN or use AND with single LEFT JOIN?

Using two LEFT JOINs i.e. SELECT <some columns> FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON <condition1> LEFT JOIN Table2 AS t3 ON <condition2> is this same as using AND in ...
1
vote
0answers
29 views

Installing SQL Server Express from SQL Server Developer Installer

If I install SQL Server 2012 Express using the SQL Server 2012 developer installer, do I still have access to all features available from the Advanced Services and/or With Tools versions of SQL Server ...
1
vote
0answers
27 views

SQL Server 2012 Performance issue while data load from Biz Talk Server

All of sudden BizTalk load has stated failing in SQL Server which was running for years without any issues. BizTalk will compile an insert SQL statement from flat file & insert data into SQL ...
1
vote
2answers
63 views

Applying single quotes across multiple lines using ssms code snippet

further to Applying Quotes Across Multiple Lines Is it possible to create a "surround with" ssms code snippet to turn a list of values across multiple lines into an IN clause, so I could: select the ...
0
votes
2answers
72 views

Convert Function

I am working on converting a few dates to make them easier to read and when I call upon the convert function to do so, they simply don't convert. When I run the queries they look the same. The code is ...
1
vote
0answers
116 views

HASH JOIN hint doesn't work. but OPTION (HASH JOIN) does [closed]

I have a query involving 3 tables and 2 inner joins. The optimiser is choosing to join them together with LOOP joins, but I know HASH joins will be better. So I tried changing the joins to be INNER ...
0
votes
0answers
26 views

SQL Server - Copy Database fails due to duplicate user

In the "model" system database, I have a user (UserA). I have another database with UserA in its Security > Users that I'd like to make a copy of using the Tasks > Copy Database. When I try this I get ...
1
vote
2answers
50 views

Ensure a Row is unique on insert

SQL Server simple problem - ensure that only 1 batch is active at one time. Each batch has an assigned BatchID# - however that ID# might not be unique if a batch is re-run at some point. However, for ...
2
votes
2answers
76 views

Should I install SQL Server 2012 SP3 when I'm already running SQL Server 2014?

We've been running SQL Server 2014 for almost a year now, but SQL Server 2012 SP3 continues to appear in the Windows Update list (as an optional install). It seemed ridiculous to install a service ...
5
votes
5answers
367 views

SQL unused logins

is there a way to find out logins that have not been logged into the SQL Server or accessed the databases in the past 90 days ?
1
vote
1answer
20 views

To find out last activity/access date and date of expiration for all SQL Server 2012 User login

Apart from enabling the Audit on the SQL server, is there any other way we could find out the last activity/access date and date of expiration for all SQL Server User login ? Also using DMVs wont work ...
3
votes
1answer
72 views

Fatal error 3624 : iKey < m_statBlob.GetHeader()->GetKeyCount()

I am receiving errors when I run the following query: SELECT ( SELECT 1 FROM dbo.AccountEntry AE WHERE AE.Account = P.Account ) FROM dbo.Proposal P WHERE P.id = @Id ...
1
vote
0answers
12 views

TLS 1.0 disabled causing errors when requests come from SQL Server to a Webserver

The issue that I have at the moment is that when I disable TLS 1.0 (due to PCI Compliance) on a web server that hosts our external application, a stored procedure is called from a different server ...
0
votes
0answers
17 views

SQL Server Policy Management Escaping for LIKE/NOT LIKE conditions

I would like to post this question and answer it right away in case anyone else has wondered... After hours of research and even opening this issue http://connect.microsoft.com/SQLServer/feedback/...
12
votes
1answer
256 views

Clustered index not used in a delete statement

I have a SQL Server table defined as follow CREATE TABLE [dbo].[Production_Detail] ( [Id] [bigint] NOT NULL DEFAULT (NEXT VALUE FOR [dbo].[Production_Detail_Seq]), [Meta_Data_ID] INT NOT NULL ...
0
votes
1answer
40 views

How to enable TLS1.2 for port 1433

I have received vulnerability on Microsoft SQL server 2008 (RTM) [high] [1433/tcp/mssql] TLS Version 1.2 Protocol Not Enabled TLS v1.2 is not enabled on this port. Already enabled TLS 1.2 on ...
2
votes
0answers
14 views

Service broker internal spid hogging tempdb

We've recently decommissioned a rather badly implemented service broker setup after a number of near outages, however while doing my morning checks I've noticed that there are 2 Service Broker ...
-3
votes
1answer
145 views

How to achieve high performance management of a huge list of distinct strings [on hold]

I am trying to achieve high performance management of a huge list of distinct strings in SQL Server (but I'm open to suggestions of alternate platforms that might be more performant). My specific use ...
5
votes
1answer
373 views

SQL Query for cumulative sum

I am experiencing problems with formulating a (relatively) simple SQL Query (SQL Server 2012 is used). We have a database which counts something up for certain users. Therefore we have a really simple ...
0
votes
1answer
31 views

Migrate Database

I'm trying to migrate my database to another server, an older version. Currently my database is on SQL Server 2014, but I need to add migrate it to a SQL Server 2012 because thats the one my server ...
1
vote
2answers
42 views

Determine ANSI_DEFAULTS setting ON or OFF?

How can I determine if ANSI_DEFAULTS is enabled or not on my server. My server is using IMPLICIT transactions and one way from which it can affect it based on setting of ANSI_DEFAULTS. How can I ...
0
votes
0answers
20 views

SQL Server 2012 Availability Group (with Synchronous & Asynchronous Replicas)

I have an AG set up in SQL 2012 (sp3), where I have 2 replicas (with synchronous commit & automatic failover) which works very well. When I try to add a 3rd replica (for DR purpose, with ...
0
votes
1answer
75 views

How to detect bad execution plans automatically?

I want to automate a way to detect when a stored procedure plan changed for worse. I am planning to do it using a list of critical SPs and checking when their plans changed. Then compare the execution ...
0
votes
1answer
21 views

AlwaysOn Shared network location change requrest

We have successfully setup the Alwayson with different subnets and it was working fine. But unfortunately the existing NAS storage had a problem. Now our storage team provide new storage IP. We need ...
2
votes
1answer
35 views

SQL Server 2005 still running after in place upgrade to sql 2012

We upgraded a default instance from SQL Server 2005 to 2012 today. But Components of SQL Server 2005 are still on the server. These includes the MSQL1. folder (it has all the data and log files), Mgmt ...
1
vote
0answers
39 views

Where can I find information on what resources are consumed when connections are established with MSSQL 2012?

I'm trying to troubleshoot the following intermittent sql connection error: "DESCRIPTION: SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. ...
3
votes
1answer
52 views

How to script XML Schema collection in SQL Server 2012?

I need to make script of XML schema collections in my Database and then insert each xml inside a table field as string. I need to do this to compare current schema collections with the new schema ...
6
votes
2answers
93 views

SQL perf issue - configure server correctly first or troubleshoot issues one by one?

This is for SQL Server 2012. I am troubleshooting overall slow performance on a Dynamics AX environment. Everything is just slow - app itself, reports, ad-hoc, etc. Right off the bat, I can see ...
6
votes
1answer
144 views

Failed allocate pages: FAIL_PAGE_ALLOCATION 1

We are on SQL Server 2012 SP3 Enterprise Edition on Windows 2012 R2. I saw these errors in the sql logs: Failed allocate pages: FAIL_PAGE_ALLOCATION 1 2016-06-14 04:28:27.44 spid175 ...
0
votes
0answers
19 views

Replicate SCCM2012 DB to secondary read only server

We have a SCCM2012 DB that we're looking to replicate out to a SQL2012 or above server. The reason for the replication is for a new ITSM system to use ODBC to extract information about devices without ...
3
votes
3answers
104 views

Query Spatial Data for Nearest Neighbor for each group

I want to query spatial data for nearest neighbor. I am using this article and the following query works perfectly: SELECT TOP 1 [Location].STDistance(@Location) FROM [DS1] WHERE [Location]....
0
votes
0answers
41 views

How Can I move data among the datafiles

I have almost 1 tb of database which have 4 datafiles in primary file group that have data unevenly A-data file has 300 gigs B- has 430 gigs c- has 200 gigs D- has 12 gigs what should I do in order to ...
1
vote
0answers
40 views

Different sporadic SQL Server errors

First question on this SA but I feel my problem is more Database related than code related. I have multiple Delphi Datasnap servers connecting to SQL Server 2012. The SQL server has multiple ...