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

learn more… | top users | synonyms (3)

2
votes
2answers
1k views

sqlpackage.exe ignoring BlockOnPossibleDataLoss?

I have a dacpac that has set the BlockOnPossibleDataLoss set to false, however the deployment is blocked when I run it with sqlpackage.exe, telling me "The column [a] is being dropped, data loss could ...
1
vote
1answer
393 views

SQL Server 2008-R2/2012 with Failover Cluster - minimizing downtime of program updates

I have SQL Server 2008-R2 with Failover Cluster like this. Planning to upgrade to SQL Server 2012. Problem - The downtime of my program updates (not Microsoft updates/patches). Both of my cluster ...
1
vote
2answers
8k views

DBCC SHRINKDATABASE TRUNCATEONLY 2008R2 vs 2012

Im a bit confused was the TRUNCATEONLY parameter changed in 2012 or is the documentation wrong in 2008R2? 2008R2 Releases all free space at the end of the file to the operating system but does ...
4
votes
2answers
171 views

Central Management Server - can it manage itself?

I'm setting up a SQL Server 2012 lab on Windows 2008 R2 which has 5 SQL Servers. I registered SQL1 as my central management server and created a group under this server that includes all the servers ...
0
votes
1answer
55 views

SQL Server 2012 Intellisense Support for Extended Events

Using SQL Server 2012 with SP1 for some reason when try to use extended events programmatically using T-SQL. I am not able to see intellisense working? Just trying to check is this something on my end ...
0
votes
1answer
272 views

In Extended Events what is the difference between rpc_completed and sql_batch_completed? [duplicate]

I understand that rpc_completed is defined as the completion of a remote procedure call and sql_batch_completed is the completion of a t-sql batch. However can anyone explain in concrete terms what ...
8
votes
3answers
5k views

Can I create a user defined table type and use it in the same transaction?

When I execute the following (in management studio, GO will separate the commands into batches) use tempdb begin tran go CREATE TYPE dbo.IntIntSet AS TABLE( Value0 Int NOT NULL, Value1 Int ...
5
votes
3answers
9k views

What permissions are necessary for truncating a table?

I have a sql account with the following permissions on a database: The db_executor role you see this account being a member of was created by this script: CREATE ROLE [db_executor] AUTHORIZATION ...
1
vote
2answers
442 views

SQL Server and SharePoint time problem

I have created a SharePoint application which has a specific time zone. The Windows server where SQL Server and SharePoint are installed are using same time zone. When I create a new item in list ...
2
votes
1answer
112 views

Confirmation on query optimization and performance understanding (seek and scan)

I am following along with this video on SQL Server query optimization and I would like to check my understanding of the results I am seeing. I am using SQL Server 2012 with the AdventureWorks 2012 ...
2
votes
0answers
2k views

SQL Server intermittent error 40 Could not open a connection to SQL Server or The network path was not found

I am getting intermittent errors as per the below stack trace. I am using C# Entity Framework with SQL Server 2012 enterprise running on a separate server computer. I can connect and query the DB ...
1
vote
2answers
456 views

Cannot add an account [non domain account ] to SQL Server 2012

Background: I have a application pool [ABCDEMO] in IIS. The identity of the appool is "NetworkService". I am trying to add IIS APPPOOL\ABCDEMO to my SQL SERVER 2012 on a Windows SERVER 2012. When I ...
2
votes
2answers
86 views

How to revert multiple impersonations?

When there are nested impersonations (as in exec as user = 'user1'; exec as user = 'user2'; exec as user = 'user1';), is there a way to revert all of them without having to use the revert command ...
0
votes
1answer
2k views

SQL Server 2012 Service Master Key Regenerate

I have a new SQL Server 2012 instance and when I run the following command I get an error. ALTER SERVICE MASTER KEY REGENERATE Error: Msg 15329, Level 16, State 1, Line 1 The current master ...
0
votes
3answers
145 views

SQL Server Facets: “Linked Server” exist but “Registered Server” doesn't - why?

I'd like to monitor multiple SQL Server instances. Something similar to what is described in "Monitor Your SQL Server Linked Server Configurations with Policy Based Management" but instead of using ...
0
votes
1answer
333 views

Is the [NT Service\SQLSERVERAGENT] a supported type for SQL Server Agent service?

Reading and re-reading MSDN "Select an Account for the SQL Server Agent Service". NT Service\SQLSERVERAGENT is not listed in the "Supported Service Account Types" section. And, if not, what is it ...
4
votes
1answer
862 views

Any way to send notifications to different Emails on success/failure of SqlServerAgent job?

SQL Server 2012 R2 on Windows Server 2008R2 Is there any way to send notifications to different Emails: on success of an Sql Server Agent job to one Email address and on failure - to another ? ...
0
votes
1answer
75 views

Compiled version of stored procedure

If I have administrator rights, would it possible to view details on the compiled version of a stored procedure or a view? Is there a visual representation available?
0
votes
1answer
422 views

How i can create backup script from diagram in SQL Server?

I use SQL Server 2012 and SQL Server 2008 R2. I create a script from all object (tables / trigger / stored procedure / function ...) in my database. I generated this script from SQL Server ...
0
votes
0answers
65 views

All databases restoring in Suspect Mode in SQL Server 2012

I've just re-installed SQL Server 2012 (removed the evaluation copy and installed a licenced version) and now every database I try to restore, it fails to completely restore and the database sits in ...
0
votes
1answer
421 views

Setting Audit Events in SQL Server 2012

We have to do information assurance remediation on our SQL Server 2012 and came across the following finding: Check to see that all required events are being audited. So I ran these queries: select ...
3
votes
2answers
1k views

What's the point in SQL Server Central Management Server?

Online MSDN article "Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio)" (SQL Server 2012) states: "Note Before you execute the following procedures, ...
4
votes
1answer
1k views

How to specify a task in a SQL Server maintenance plan conditionally?

I have a weekly and nightly SQL Server Agent jobs executing a weekly and nightly maintenance plans, resp. These weekly (on Sunday) and nightly (on other 6 nights of the week) maintenance plans ...
1
vote
1answer
2k views

How to launch SQL Server Agent job on completion of another one?

An SQL Server Agent job (SSA) have to be executed after a few other SSA jobs. Is there any way to start SSA jobs on completion (or success, or failure) of another one? Currently, I do this on ...
4
votes
1answer
713 views

create linked server to vertica

I'm attempting to create a linked server from SQL Server to Vertica to pull over some data. I've attempted using the following ODBC found here. The code I'm currently trying is: EXEC ...
0
votes
1answer
200 views

Not exists/Where NOT in

Doing a pie chart in crystal. Items are Inquiries Referrals Followups A referral has to have an inquiry, A followup has to have a referral. I want to show pie chart of referrals to ...
5
votes
1answer
11k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. Following up on my previous ...
3
votes
1answer
167 views

join syntax / style performance consideration

Edit: I've added a follow up question based on comments by @ypercube about semantic equivalence and my findings of the query behavior changes when you have a mix of outer/inner nested joins. We ...
8
votes
5answers
9k views

Freeing Unused Space SQL Server Table

I have a table in SQL Server Express with a lot of unused space. I need to free up space in the database. | NAME | ROWS | RESERVED | DATA | INDEX_SIZE | UNUSED | ...
0
votes
2answers
656 views

Copy SQL Server database to same server & decrypt by symmetric key return null

I have a SQL Server 2012 database that I backed up and restored to the same server and instance. When I attempt to use DecryptByKey it always returns null. Since it is the same server, do I need to ...
1
vote
5answers
4k views

How to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job)?

Struggling to execute PowerShell commands in SqlServer Agent job of MS SQL Server 2012 R2 (Windows Server 2008R2, Windows 7 Prof) I need to delete (.bak files older, than, say, 7 days in remote ...
3
votes
1answer
4k views

SQL Server Service Account login or password is not valid error

I am trying to install SQL Server 2012 Express on a Windows 2012 Server for test purposes. This machine is part of our company domain. During installation I am trying to use one of domain account ...
0
votes
1answer
95 views

In sys.dm_os_memory_cache_entries view, what does original cost mean?

Looking at a couple of DMVs, and trying to see if I can programmatically figure out the cost of the cached plans in my SQL Server. I'm hoping that column represents the same concept of cost that is ...
1
vote
1answer
186 views

Configuring transactional replication with updatable subscription via SSMS 2012

Can I configure transactional replication with updatable subscription via SSMS 2012 (SQL Server 2012)? Is this removed from SSMS 2012 or I'm just missing something here?
1
vote
0answers
253 views

Alternative method of viewing a database diagram in SQL Server to see what tables have gone missing? [closed]

I have a database diagram for my database, but when I open it in SQL Server, I almost immediately get a message saying some permissions changed or tables in the diagram were dropped or renamed, and ...
4
votes
3answers
7k views

How do I move SQL Server database files?

I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server. How can I do this?
0
votes
2answers
231 views

Lot of log files in $FSLOG generating. How to stop?

I'm using SQL Server 2012 Express Edition and I have a problem with huge amount of files generated in: C:\Program Files\Microsoft SQL ...
5
votes
2answers
2k views

Error running Powershell Script in SQL Server Agent

I have a SQL job that runs via SQL Server Agent (Running SQL Server 2012 Enterprise). The last step of the job is to run an application located on a network share. Unfortunately I don't know the name ...
0
votes
1answer
1k views

Query that only uses index takes long time to execute [closed]

I have a table with ~69,000 rows. I have this very simple query: -- SET STATISTICS TIME ON SELECT ID, FirstName, LastName FROM Customers -- SQL Server Execution Times: CPU time = 31 ms, elapsed time ...
4
votes
2answers
3k views

Log sizes unexpectedly large: log_reuse_wait_desc == “log_backup”

I've been reading posts and manuals all day, I need help. My problem: transaction log sizes after data and transaction log backups will not shrink, and grew to be unexpectedly large. The system is ...
2
votes
1answer
2k views

SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

We've recently upgraded to SQL Server 2012 (from SQL Server 2008 R2) on Windows Server 2012 (from Windows Server 2008 R2), and a SQL Server Job Agent job that was previously running without error is ...
8
votes
2answers
1k views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
0
votes
1answer
489 views

dbcc checktable/checkdatabase errors after database restore

Following on from this question Error when creating an index in SQL Server 2012 we have been having problems restoring our live database back to test. Our current production database is SQL 2005 ...
2
votes
3answers
2k views

~2 Hours after a large insert SQL Server gets “I/O requests taking longer than 15 seconds to complete”

This problem has been haunting me for a while but I actually fixed the queries that caused the problems (after I finally found them) but i'm curious if it's an SQL Process that's the underlying ...
3
votes
0answers
839 views

Once more about SQL Server virtual accounts

Let me reboot my question on SQL Server virtual accounts permissions The (same) MSDN article on the same version 2012 of SQL Server "Configure Windows Service Accounts and Permissions" states: ...
2
votes
1answer
193 views

What are Objective Business Reasons to Prefer SQL Server 2008R2 over 2012?

Basically, I want to take this discussion -- What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2? -- and turn it on its head. We are a small business, and we are struggling ...
0
votes
0answers
153 views

What are the possible reasons for SQL Server errors on reading operations upon switching ALLOW_SNAPSHOT_ISOLATION` and `READ_COMMITTED_SNAPSHOT ON?

I have a client-server app running in Windows Server 2008R2 using/interacting SQL Server 2012. After reading app's documentation as well as feedbacks on internet from those who already accomplished ...
1
vote
2answers
352 views

Licensing SQL Server 2012 Standard Edition for development and test severs [closed]

Is there an economic way to license SQL Server standard edition on a test server? Given the following environment: live: SQL Server 2012 Standard Edition test: SQL Server 2012 Developer edition ...
3
votes
1answer
557 views

Error when creating an index in SQL Server 2012

I'm trying to create the following index on our test environment (SQL Server 2012): CREATE NONCLUSTERED INDEX [iTransaction-OriginatingTransaction] ON [dbo].[Transaction] ( ...
6
votes
1answer
18k views

SQL Server 2012 Restore backup to new DB Name

I seem to remember that, in 2008, you could restore a backup to a new copy of a DB, by changing the name in the "Destination Database" field from the restore-wizard. It would create a brand new DB, ...