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

learn more… | top users | synonyms (3)

0
votes
0answers
522 views

SQL Server Replication Monitor Throwing “Specified cast is not valid. (ReplicationMonitor)” Error

The publisher is running SQL Server 2008 R2 on a Server 2008 R2 box. The subscriber is running SQL Server 2012 on a Server 2012 box. I have tried launching the replication monitor from both servers ...
2
votes
1answer
228 views

MAXTRANSFERSIZE setting causes restore to fail

I have a database that seems to be functioning fine with no apparent errors except that any full backup taken is broken - attempting a restore fails with an error "RESTORE detected an error on page ...
1
vote
2answers
54 views

Should I include as much information as possbile to help the optimiser?

Reading the article "Dynamic Search Conditions in T‑SQL" I saw the following statement in the "Using sp_executesql" section. Double Feature You may wonder why there are two conditions on the ...
0
votes
2answers
2k views

Query to check if all replication articles are created at Subscriber SQL Server 2012

I am creating an automated build and test tool for our databases. In general there are two stages: - install databases in version A, install transactional replication - drop replication, update ...
1
vote
1answer
75 views

How to deal with multiple error columns in data flow

I'm writing a simple package to load data into tables and a I have multiple destinations within the same package. I'm attempting to first attempt batch inserts but want to fall back to row by row in ...
0
votes
2answers
176 views

Cannot Copy Database [closed]

I am working with SQL server 2012. I want to copy a database along with all the data. I just want to create a dev DB. I know SQL Server 2012 has a Copy Database... wizard. But when I run that wizard, ...
1
vote
2answers
4k views

Detecting changes in a SQL Server table

In my application, with a DB running on SQL Server 2012, I've got a job (scheduled task) that periodically executes an expensive query and writes the results to a table that can later be queried by ...
1
vote
1answer
874 views

Return every instance of a string contains search

I have this sample table and data CREATE TABLE [dbo].[SampleTable]( [Id] [int] IDENTITY(1,1) NOT NULL, [TextColumn] [nvarchar](1000) NOT NULL, CONSTRAINT [PK_SampleTable] PRIMARY KEY ...
2
votes
1answer
325 views

Import 10 Billion Rows, Daily [closed]

I need to import 10 billion rows into a database, daily. SSIS, doesn't seem like its cutting it, and I'm starting to think that writing some kind of custom C/C++ code might be an option (I don't much ...
0
votes
0answers
59 views

How control 27 locations and 27 databases? [closed]

We have 27 locations and 27 databases (all are SQL Server 2012 R2) All of them are the same (same tables,views,...), with different data for each location and separated from each other (in each town) ...
4
votes
2answers
296 views

Replication with FileStream

I need to setup database replication (for high availability) on a database that uses FileStream. This is on SQL Server 2012 Standard Edition, so Always On or Peer-to-Peer replication is not an option. ...
3
votes
1answer
3k views

How can I create a Foreign Key to a Compound Primary Key with a single column?

I am working with a 'lookup' style table that is already in place, which has a compound primary key (not my design) in Microsoft SQL Server 2012. I want to add a foreign key to it in another table, ...
3
votes
1answer
293 views

Is it possible to return a custom error for a logon trigger?

I'm trying to get a logon trigger to return a custom error. Thus far everything I have tried causes it to return the standard error: My trigger looks like this: CREATE TRIGGER Test_Logon_Trigger ...
5
votes
3answers
7k views

SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012

Yesterday we migrated a SQL Server 2005 database to SQL Server 2012. We made a full backup and restored it on the new server. Problem now is that the database on SQL Server 2012 is much slower than ...
3
votes
1answer
124 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
0
votes
0answers
44 views

Accidentally deleted records [duplicate]

I would like to know if it is possible to restore a deleted record directly from the transaction log without doing a DB restore to a test database to retrieve the deleted records? I could be wrong ...
1
vote
1answer
273 views

How to reinstall SQL Server Integration Services I've deinstalled from a Production server by mistake

In deinstalling a named instance of SQL Server 2012 from a Production server, inadvertantly, I selected SSIS as a product to deinstall. Now, I need to reinstall it with the least disruption possible. ...
1
vote
0answers
110 views

Bind web service/Report Manager based on IP address AND hostname

I'm trying to get Apache installed on our main database/application server, and I'd like to give it its own IP address on a secondary NIC so I don't have to use a nonstandard port. This requires that ...
4
votes
2answers
1k views

How do I delete unique rows from a table with duplicate keys?

I have the following data: road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ...
3
votes
2answers
181 views

Differential databases (not backups)

When working with virtual machines often it can be very useful to have a base read-only VM then have several smaller VM's that use that larger VM as a base and write their changes to their own ...
0
votes
0answers
165 views

How can I get rid of the “No Join Predicate” warning

I have this fairly straight-forward snippet of a query: DECLARE @ClientGUID numeric(16) = 9000011916900200; SELECT ch.GUID, c.LastName, c.FirstName, mrn.ClientIDCode AS MRN FROM Client c INNER ...
1
vote
0answers
133 views

What type of fact table / loading solution for a reservation system?

I am designing a Data Warehouse with SQL Server 2012 and SSIS. The source system handles hotel reservations. The reservations are split between two tables, header and header line item. The ...
1
vote
3answers
5k views

What could cause an auto increment primary key to skip numbers? [duplicate]

I have a table created with the following T-SQL: CREATE TABLE [dbo].[TableName]( [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, ...
3
votes
2answers
404 views

Altering Multiple Tables

I have database with 170+ tables in mssql 2012. Some of them contain the field "UserID", varchar(9) while others do not. Due to an application redesign, I need to alter all tables in the database to ...
1
vote
1answer
68 views

Connecting to Active Active Cluster Services

I have a two node active active cluster setup as below: NODE 1 - SQLINST1 ( with DTC1 ) NODE 2 - SQLINST2 ( with DTC2 ) I wish to now have the application connect to SQLINST2 whenever ...
1
vote
2answers
3k views

Free up disk space on sql server

Assuming I have a sql database server with disk space as follows: D Drive: 10GB (For the MDF file) E Drive: 10GB (For the LDF file) And I have only one database on the server with the following ...
1
vote
1answer
2k views

How to connect client applications to SQL SERVER 2012 cluster?

Following are the environment Details : Database Server : SQL SERVER 2012 Operating System : Windows Server 2012 Single instance of SQL SERVER 2012 is installed in cluster configuration with 2 ...
2
votes
1answer
840 views

Database Backup from 2008 R2, Restore on 2012 Failover Cluster Instance

I have a production database server having sql server 2008r2. This is an old server and considering our load, we are moving to a better hardware configuration, Now the new environment is a cluster ...
1
vote
1answer
3k views

stored procedure with multiple statements does not execute all statements when run from job

** see updates below I have a stored procedure (SQL Server 2012) that deletes old records from a table, then inserts new records from a view to synchronize the table with the view. When I execute the ...
1
vote
1answer
530 views

How to aggregate pairs of id values into groups

I have a table with two id columns id1 and id2. Each row shows that 2 id values have been linked together. My actual data has both id columns as varchar(20) but I have used integers in the example ...
1
vote
1answer
305 views

Delayed availability of historical data when using CDC

I am using the SQL Change Data Capture feature to retrieve historical data from my tables. During testing, I noticed that when I insert/update a row and query the change data right afterwards (see my ...
0
votes
0answers
78 views

SQL Server consistency I/O base error

I have received this error while performing queries on a database (SQL Server 2012) msg 824, level 24, state 2, line 1 sql server detected a logical consistency-based i/o error. Incorrect page ID ...
2
votes
2answers
2k views

How to do partition on a non clustered index column?

I have table ORDERS .Where there are Million rows in it .I need to add Pationed Index on Order to store data in one of two files based on the new IsLocked field.But when i checked table there is a Non ...
1
vote
1answer
142 views

Difference between sql agent account logged in as NTLM and UPN?

Can someone please tell the difference between a sql agent account logged in as NTLM and UPN? What is the difference if the sql agent account is logged in as: domain\username and username@domain.com? ...
10
votes
1answer
1k views

Why would you use a managed service account rather than a virtual account in SQL Server 2012?

In SQL Server 2012, service accounts are created as virtual accounts (VAs), as described here, as opposed to managed service accounts (MSAs). The important differences I can see for these, based on ...
8
votes
2answers
31k views

Installing SQL Server 2012 Express fails with error “ Could not find the Database Engine startup handle.”

I'm attempting to install SQL Server Express 2012 w/ SP1 x64 on Windows 7 SP1 x64. This machine has previously had SQL Server Express 2012 installed. This instance was upgraded to SQL Server 2012 ...
3
votes
2answers
135 views

SQL Server Master Data Services database history by snapshots

I'm involved in a project where there is SQL Server 2012 Master Data Services used. There's a requirement to save a version of the database contents in certain point of time, say, every month or a ...
0
votes
1answer
371 views

SQL Server sp_addrolemember in stored procedure for different database

I have a stored procedure to restore the latest backup of a prod database to an acceptance database. Once it's restored I need to grant a user read access to the database. Use statements are not ...
0
votes
4answers
121 views

T-SQL: Complete Database in one table

at a relational data model every logical relation is a single table with values and so on. I know someone who is this too much work and he wants to create only one table which contains all. This could ...
3
votes
1answer
354 views

SHOWPLAN does not display a warning but “Include Execution Plan” does for the same query

I was running the following query in SSMS and it showed me a warning in the "Include Execution Plan" window select CLIENT_GUID, PMT_AMOUNT, DATE_COMPLETED from ( select payment.CLIENT_GUID, ...
4
votes
1answer
1k views

AlwaysOn Availability Group Forced Failover

May I request your input. I setup SQL 2012 with always on availability group for my databases in sync and secondary readable mode. I completed failover tests with sync mode and it is functioning as ...
4
votes
1answer
3k views

SQL Server 2012: Security_error_ring_buffer_recorded: ImpersonateSecurityContext

a couple servers I manage are recording a lot of events in the system_health XE session. security_error_ring_buffer_recorded api_name ImpersonateSecurityContext calling_api_name ...
1
vote
0answers
102 views

How to take advantage of multiple spatial indices?

All, I'm building a view which uses a spatial intersect (STIntersect in MSSQL 2012) between points (student addresses with address information) and polygons (school boundary polygons with school ...
4
votes
1answer
668 views

Putting a condition related to a table in JOIN is better?

I have seen this a lot that people are putting table related condition on JOIN clause. See this example, SELECT i.Name FROM sys.TABLES AS tbl INNER JOIN sys.indexes AS i ON (i.index_id > 0 ...
25
votes
3answers
3k views

At what point does one database per client become unfeasible?

For one of our systems, we have sensitive client data and store each client's data in a separate database. We have about 10-15 clients for that system. However, we're developing a new system that ...
2
votes
0answers
375 views

Can I recover a single table from a SQL Server 2012 database backup [duplicate]

I have accidentally dropped a specific table and I don’t want to restore whole database (it is huge) but instead I want to recover only that table from a backup? What would be most simplest ...
4
votes
1answer
365 views

A clarification of MERGE best practices

I just wanted to ask a clarification about something on the MSDN from the "Optimizing MERGE Statement Performance" page. I am working with a data warehouse that takes in records from many different ...
2
votes
1answer
79 views

How can I automatically have a node become primary in an Availability Group?

In my environment, I have Db01 and Db02 setup with SQL 2012 AlwaysOn. Db01 is the preferred master as that is where all of the backup jobs reside. Typically, the only time that Db01 is unavailable is ...
2
votes
0answers
103 views

SQL Server 2012 High availability Groups for OLTP and Reporting environments

I would like to seek your advice in designing a high availability architecture at my current work place. We are moving to SQL Server 2012 platform and we currently have a set of OLTP databases ...
2
votes
0answers
34 views

TFS 2010 and SSMS2012

i was trying to use TFS 2010 in SSMS 2012 and, even if i installed Team Foundation Server MSSCCI Provider 2012 64-bit and Visual Studio 2010 Team Explorer, there's no way to see TFS under the Current ...