This tag is for SQL Server 2012, major build version 11.00.xxxx.

learn more… | top users | synonyms (2)

2
votes
0answers
59 views

Syntax for restoring SQL Server backup from non-disk source [duplicate]

I have backed up and restored many a SQL Server database using T-SQL script, but always FROM DISK or TO DISK. I am not a provisioning-concerned database administrator, though, so don't have as much ...
2
votes
2answers
3k views

Should I join datetime to a date using cast or range?

This question is a take-off from the excellent one posed here: Cast to date is sargable but is it a good idea? In my case, I am not concerned with the WHERE clause but in joining to an events table ...
3
votes
3answers
178 views

Inserting two rows from single row

I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind ...
3
votes
1answer
790 views

Using AD groups vs roles in SQL Server

What is best practice for sql server security? Using AD groups that are given rights on schema objects or should I add the AD groups to a database role which is given rights on schema objects ? If the ...
0
votes
1answer
226 views

Can this query be optimized further?

I have 100K records in my Documents table. This is my query which gives a page of 50 records in roughly 750 ms. I would like to further bring it down if possible. I am using Row_Number function to ...
4
votes
0answers
132 views

ENABLE_BROKER vs NORECOVERY

I have two nodes with MSSQL2012 which work with AlwaysOn High Availability After some crash I had to remove db from availability group on the one of the nodes. For restoration I've made backup ...
3
votes
2answers
6k views

How to script all permissions on a schema

SQL management studio allows to create scripts for all db objects, however I so far couldn't find a way to correctly script a schema or user. The permissions of a user on a schema are not included in ...
2
votes
2answers
1k views

SQL Server 2008 upgrade to SQL Server 2012 on a cluster

I have a Windows 2008R2 2 node cluster with SQL Server 2008 clustered. How do I prevent the cluster from failing over while I am applying the SQL Server 2012 to the alternate node first? Any pointers ...
1
vote
1answer
557 views

Run a program on a remote computer after a SQL job has completed

We have a SQL Agent Job that performs a few tasks every night (schedulled to run using SQL Agent). The database server is SQL 2012 if that makes a difference. We have another process (a .net program) ...
3
votes
0answers
133 views

SQL Server 2012 Resource Governor and memory used by CLR

In SQL 2012 memory used by CLR is now part of what is controlled by "MAX Server Memory" a) Does Resource Governor in SQL 2012 Enterprise (MAX_MEMORY_PERCENT for resource pool) governs CLR memory? ...
1
vote
1answer
282 views

Error deleting FileTable - HRESULT = '0x80070490'

When trying to delete a FileTable, the following error occurs: Message 33405, Level 16, State 7, Line 1 Error during operation on an object table Discard FileTable. (HRESULT = '0 x80070490 ').
2
votes
1answer
487 views

Script out XMLA for all databases

I have several SSAS databases. I only want to script out the XMLA for them on a nightly basis. This will be a second tier backup in addition to the regular backups we have. How do I auto-generate ...
1
vote
1answer
991 views

Feature SQL Server 2012 - Filetable - The location of the file can not be opened

When trying to access the files available on the virtual directory created for FileTable, you receive the following error message: "The location of the file can not be opened. Access is not ...
0
votes
0answers
174 views

The remote copy of database “<DBNAME>” is not related to the local copy of the database

I'm trying to join a secondary DB to my SQL Server 2012 Availability Group following this article: Join a Secondary Database to an Availability Group (SQL Server) When I attempt this the local SQL ...
0
votes
2answers
164 views

Query to return fields of distinct values per key

In the process of building a data warehouse we are creating some aggregation tables for a reporting engine. We would like to build a single aggregation table from the fact table that can exist as list ...
3
votes
1answer
923 views

How to remove diacritics in computed persisted columns? COLLATE is non-deterministic and cannot be used

I have a function that includes: SELECT @pString = CAST(@pString AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS This is useful, for example, to remove accents in French; for example: ...
3
votes
2answers
274 views

continuously extending a table in SQL Server

We have a large data warehouse database where we continuously get new rows inserted in 5 different tables, at the left-hand side of the b-tree (=at end of the table) This cause a lot of page splits. ...
1
vote
1answer
821 views

Is MSDTC required for SQL Server 2012 Fail Over Cluster?

I am making a 2 node SQL Server 2012 Fail Over Cluster, do I need to install MSDTC also? If yes then can both be installed on single shared disk?
1
vote
0answers
755 views

Microsoft SQL server 2012 error 18456 with domain credentials [closed]

I have a SQL server 2012 setup to use windows authentication. Remotely I can connect to "Server A" no problem with domain credentials but when I try to locally connect to "Server A" with SSMS it ...
1
vote
2answers
146 views

Single disk for Data, System, User, Temp and Backup when installing Fail Over Cluster?

I have a Windows cluster with two nodes. I am trying to make Fail Over Cluster using SQL Server 2012. On both nodes there is 2 TB storage available which I can access as: ...
2
votes
2answers
803 views

Unattended SQL install on a remote machine

My product installation does an unattended install of SQL Server (SQL Server Express by default). I use command line arguments rather than a configuration file, so I'm quite familiar with this ...
2
votes
1answer
387 views

Backup and restore FileTable files

I'm preparing to use the FileTable feature in SQL Server 2012, but I'm unclear about how backups work with this feature: Does a full database backup include FileTable files? How would I perform the ...
8
votes
2answers
821 views

Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

I'm a fan of surrogate keys. There is a risk my findings are confirmation biased. Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys based ...
0
votes
1answer
52 views

Warn about table recreation on table change

By default, if you try to change a table using the designer making any of the following changes, you get an error message: You change the Allow Nulls setting for a column. You reorder columns in the ...
0
votes
1answer
288 views

Eliminate duplicates in different order from all combinations, can I order returned columns alphatically?

this should actually be not a too hard task, but it seems to be a little too early for me to see the solution: I have 4 different string values. I want to have all combinations of those 4 values ...
1
vote
2answers
199 views

What's the official successor for sp_resetstatus in case of a SUSPECT database?

I have a database that has been marked SUSPECT because the volume that both the data and the log file were on suddenly disappeared (an accident - this is a developer workstation). I tried bringing the ...
2
votes
3answers
677 views

How to DROP multiple tables from databases?

I have situation where someone mistakenly execute script for creating tables under wrong database. Let's say we have database DB1 which is some test database, and we have to create other database ...
1
vote
2answers
340 views

Understanding disk requirement to make fail over cluster

Please take a look at the following screenshot. I am trying to make SQL Server 2012 Fail Over Cluster using two nodes. As you know we need to have a shared SAN which will be used by both nodes to ...
1
vote
0answers
370 views

Using Mirroring to upgrade SQL Server 2008 R2 to SQL Server 2012

I am attempting to mirror a SQL Server 2008 R2 database currently in a fail over with the purpose of doing an in place upgrade. I have installed SQL Server 2012 on another machine to serve as my ...
1
vote
1answer
536 views

SQL Server 2012 - Full Text Search [Break word - Underscore]

Let's say I want to search for a list containing 'PS3': SELECT * FROM [TABLE] WHERE CONTAINS(Title, 'PS3') When the Title is proceeded by an underscore it will not return it. Oblivion-PS3 -> Good ...
0
votes
1answer
995 views

AdventureWorks2012: “The FILE SIZE property is incorrect” error while trying to attach sample database “Adventure Works for SQL Server 2012”

I am trying to attach the sample databases, for example, AdventureWorks2012_Data.mdf from codeplex "Adventure Works for SQL Server 2012" into a MS SQL Server 2012 (default) instances on a a virtual ...
2
votes
1answer
588 views

SQL Server 2012 Page Compression - Determine compression ratio?

We're using SQL Server 2012 and have page compression turned on for a number of our larger tables. Is there a way we can determine the compression ratio we're getting? We'd like to be able to ...
1
vote
0answers
83 views

Displaying SSIS variabe values in Integration Services Dashboard

I have an SSIS package that executes stored procedures which return output parameters into ssis variables. How can I easily display these variables content in the Integration Services Dashboard ? ...
2
votes
2answers
312 views

Scheduling duplication of two tables across instances

In my SQL Server 2012 database I have two tables: [tbl_Customers] - about 30 rows [tbl_Users] - about 10,000 rows I need to schedule the two tables above to be copied to a new instance, say every ...
3
votes
2answers
5k views

Restore of database failed [duplicate]

I wanna restore my database and i use sql server 2012 express edition but when i restore my backup file in sql server management studio, i take this error: System.Data.SqlClient.SqlError: There is ...
0
votes
0answers
288 views

How to transfer logins from sql server 2012 to 2008?

I need to transfer dynamically logins from SQL Server 2012 to SQL Server 2008 and I can't because when I get the hashed password on 2012 does not have a valid format to be used on 2008. I know that ...
1
vote
0answers
304 views

Collecting parameter values from Extended event session

I'm using extended events to trace the statements used by our application. I have created a session that will collect the information that I want, except that I would also like to know the actual ...
4
votes
2answers
4k views

Understanding two NICs requirement for SQL fail over cluster

I am trying to make an SQL Server 2013 fail over cluster. I have two DB machines. I understand that both machines need 2 NICs each. In my organization the IP scheme assigned is something like ...
1
vote
1answer
327 views

RBS and Fail Over Cluster with SQL Server 2012

I am trying to install one SQL Server 2012 cluster with two nodes. I will also be enabling Remote Blob Storage (RBS) on server so I was wondering if I need to take something in account while ...
1
vote
0answers
140 views

Automatic Version increment on Data-Tier Applications

In the project settings of a database project I can set the version number form my data-tier application, however setting this manually is a bit inefficient. I would like to automatically increment ...
0
votes
3answers
2k views

Connecting to SQL Server instance through C# [closed]

I made a program, using c# and sql server with sql authentication. It works properly in my device, but after making the program setup file .exe extension using Innosetup, I cannot connect to my ...
5
votes
2answers
9k views

SQL Server not using all CPU cores/threads

After upgrading our SQL Server's hardware, we noticed in the Windows Task Manager that the SQL instance is only using half of the threads available to it: . The server has the following hardware ...
1
vote
2answers
1k views

SQL Server: Additional cpus slow down batch

I'm running SQL Server 2012 in Windows 2008R2 in a virtualized environment. I've observed the following under both VMware Workstation 9 and Hyper-V 2012R1 and I don't know how to address it. I've ...
2
votes
1answer
167 views

How do I access the full text of an error in SSIS GUI?

I have an Excel task which is providing error text through the GUI, but I don't know how to access the error task without the GUI and the error disappears if I move the cursor from the X I have the ...
1
vote
2answers
1k views

What is best way to storing geo coordinates related to planned route?

Here is my scenario: Application "A" produces lat and long points that correspond to planned route. Depending on route distance it could have several thousands of points. I know of Geography and ...
0
votes
0answers
197 views

Cannot set ArithAbort to Off on SQL Server

I have 2 SQL 2012 servers. The settings are exactly the same, but on one I can set ARITHABORT to Off but no matter how I try I can't do the same on the second server. The only difference I could find ...
2
votes
1answer
780 views

Troubleshooting Mirroring Configuration

I am configuring database mirroring on SQL Server 2012. After configuration I am getting the following error when attempting to start the mirroring session: tcp server cannot be reached or does ...
2
votes
2answers
1k views

Partitioning a Table Referenced by a Foreign Key

I have one table: CREATE TABLE [dbo].[entry] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [EntryDate] [datetime] NOT NULL, [createddate] [datetime] NOT NULL, CONSTRAINT [PK_Entry_ID] ...
1
vote
1answer
349 views

SQL Server 2012 SQL Log “the log file is not using Unicode format”:

On only one server of many, the SQL log is blank and shows a message "the log file is not using Unicode Format" when viewed via SSMS. The log is in the right location as specified in the service ...
2
votes
1answer
125 views

Query does not use index when selecting all records [closed]

This is my first time handling a large database, and I was wondering why the two queries below are different in terms of execution plan. I have setup an index on a specific table: CREATE ...