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

learn more… | top users | synonyms (3)

0
votes
0answers
18 views

How to align restore strategy with backup history cleanup? [duplicate]

I was curious about how that particular problem is tackled. 1) Full backups performed on saturdays. 2) Differentials every other day. 3) Tlog backups every 15 minutes. When trying to restore a ...
0
votes
0answers
29 views

How to shrink the .ldf(log) file of the particular live production database? [duplicate]

I would like to say that as one of my production database (.ldf) log file is growing larger than databse size. The size of database file (.mdf) and their log file (.ldf) is as follows:- 1)database ...
1
vote
1answer
16 views

No priviligies to create table in custom database

I'm using locally installed express edition of SQL server. CURRENT_USER = dbo, and I used win authentication. The database was successfully created this way: CREATE DATABASE [online_banking] ON ...
2
votes
1answer
295 views

SQL Bulk Insert Impersonation issue

I am running Microsoft SQL Server 2012 SP on a Windows Server 2008 R2 Standard SP1 box. The SQL Server service is running as a simple windows domain user (nothing special, no admin rights, etc.) I am ...
1
vote
0answers
139 views

xp_delete_file not working

I am running a query window under my own account and the following command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\backup\sql', 'bak', '2015-11-24T23:30:02' ...
7
votes
1answer
535 views

Efficiently storing sets of key-value pairs with wildly different keys

I inherited an application that associates many different types of activities with a site. There are roughly 100 different activity types, and each one has differing set of 3-10 fields. However, all ...
4
votes
1answer
115 views

Getting Unavailable dates for renting a product that has stocks

Database queries, normally so simple, yet sometimes so difficult. (brain trainer) So I have products, stocks and rentStockOrders. These products can be rented for a set of days. The stocks also have ...
0
votes
1answer
51 views

Dropping a column generates an error: SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'

When I run the following statement ALTER TABLE MyTable DROP COLUMN MyIdentityColumn I receive the following error message: SELECT failed because the following SET options have incorrect settings:...
1
vote
2answers
79 views

Locked TempDB Log

So we're running into an issues where TempDB's log is currently locked up. After checking log_reuse_wait_desc, we see that it is locked from an active transaction. Now the weird part is that when we ...
1
vote
1answer
120 views

Cursor with Begin and End Transaction

I've got the following script that keeps hanging on the 2nd iteration through the loop. BEGIN BEGIN TRY BEGIN TRANSACTION DECLARE @itemID int DECLARE LoopCursor CURSOR FOR ...
1
vote
1answer
223 views

Is it possible to configure multiple database mail id in single account name in sql server 2012?

I would like to say that as i want to configure multiple database mail in single account name in sql server 2012. This is SQL-Cluster-DB , so as per direction from head, If any sql job will fail , ...
4
votes
2answers
2k views

How to assign CTRL+F1 to sp_whoisactive in ssms 2012?

When I go to Tools > Options > Environment > Keyboard and Type in Query.Sp_whoisactive Press shortcut keys: Ctrl+F1 Click Assign. When I press Ctrl+F1, It still calls Help.
4
votes
1answer
6k views

SQL Server performance is slow when migrated from SQL Server 2012 to SQL Server 2014 [duplicate]

We are trying to migrate from SQL Server 2012 to SQL Server 2014 on Windows 2012 R2 OS. After the upgrade, we are doing performance testing and observed huge difference in running a simple query from ...
2
votes
1answer
154 views

Change port number for SQL 2012 Cluster

I have been trying to change the port number of a SQL 2012 cluster with little success. I had thought that I could just do this the same way as normal through configuration manager however, even with ...
4
votes
2answers
215 views

Contained DB Collation error

When changing a database to partially contained I am getting the following error: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in ...
0
votes
1answer
57 views

Change database used for SQL Server Integration Services logging

The SQL Server Best Practices Analyzer noted that my Integration Services' logging table is in a system database. It recommends that I move this to a non-system database. (See here) How do I make ...
0
votes
1answer
32 views

SQL Server Merge mandate

Does the target table need to be identical to source table schematically? Specifically: 1) do they need to have the same number of columns and do the column types must be the same?, 2) do the column ...
2
votes
2answers
92 views

CTE To Use Alternate Select When Select Returns No Rows

I have created a involved CTE, no recursion, with multiple selects being done as it processes the business logic of the requirement. But I have been advised that the logic needed for the scenario ...
1
vote
1answer
34 views

How to determine how much storage to store backups?

I was thinking about this question ( Here where I work, we have a lot of space so backups are dayli ok ), and i'm thinking about if I was hired in another company, and see that they don't have backups,...
7
votes
3answers
5k views

How to connect once for all queries in SQL Server Management Studio?

SSMS asks me to connect for every query in my project. Is there a way to connect only once per connection for all of the queries using it in my project? The answers below (March 2014) don't answer ...
5
votes
2answers
10k views

I cannot get SQL Server 2012 to install the database engine

SQL Server 2012 installation on a standard Windows 2008 R2 server. It refuses to install the database engine or sql replication. During the installation I got no errors saying that it wasn't ...
1
vote
1answer
497 views

Trying to use SMO Transfer Object to transfer a database from one instance to another

The schema tables are owned by dbo and another schema name. The code below is not moving over any tables. Is there something else that I need to add to make it transfer the tables? There are no stored ...
1
vote
0answers
376 views

Sudden PRIMARY KEY violation on IDENTITY column

I have a log table with an ID identity column. Everything works fine for years; then, yesterday, I see the following error in the logs: The statement has been terminated. Violation of PRIMARY ...
-2
votes
1answer
44 views

Design for table

I have a table: TABLE ( A int, B int, ..., H int, DataValue float ); All of my queries do aggregate operations, with A and some other columns (several combinations) in the WHERE clause (A is ...
1
vote
3answers
658 views

How to decrease RAM usage of SQL Server 2012 without restarting Service

Is there a way of decreasing RAM usage of SQL Server 2012 without restarting it? Setting up MAX Memory usage is not an option as it's quickly building up to limit I have set and then app based on ...
2
votes
2answers
2k views

Fix Linked Server Without Restarting SQL Server Instance

Problem: Occasionally a nightly job will fail, and when I look into it, it is the result of a query across a linked server failing. But for some reason that query seems to remain 'open' in some way ...
0
votes
2answers
121 views

Stored procedure optimization

In my application I am using the below stored procedure, but it is taking a long time to execute. Both tables contain millions of records. Please guide me to optimize this procedure. I have searched a ...
2
votes
1answer
114 views

Question about new behavior of current connection combobox in SQL Server 2012

I have a question about the new behavior of the database selection combobox in SQL Server 2012 (Press Ctrl + U to move your focus to it). It seems that, unlike in SQL Server 2008 R2, clicking in the ...
0
votes
1answer
47 views

Application service down due to SQL Server error

I'm using SQL Server 2012 where in I found an error in my SQL Server error logs which says: Logon,Unknown,The login packet used to open the connection is structurally invalid; the connection has ...
0
votes
4answers
75 views

Expected Performance of MSSQL [closed]

We have a client who has been having performance issues. One of the developers is saying that a select * from a table is taking ~50 seconds to perform the query (no joins just straight select with no ...
1
vote
1answer
19 views

Restore a specific file group to a test environment without restoring the Primary file group (Error 3154)

Scenario. I have a filegroup that we need to restore to get data back that has been removed from the database after the last backup I cannot touch The production systems because they are running ...
0
votes
2answers
200 views

How to install SQL Server 2012 on Windows 10

Currently this has been a huge pain for me. I have a fresh Windows 10 VM. I Installed SQL Server 2012. I open up the SQL Server Studio 2012, and there is no server name that I can choose from in the "...
6
votes
1answer
4k views

Kerberos authentication not working with Linked Servers in SQL Server 2012

I am setting up a DEV/TEST environment using 2 SQL Servers running SQL Server 2012 on Windows Server 2012. We are moving from SQL Server 2005 on Windows Server 2008, where we already have this up-and-...
1
vote
2answers
695 views

Format column length in output - SSMS

SQL Server 2012. Sample query at bottom. I'm trying to create a simple report for when a given database was last backed up. When executing the query at bottom with output to text in SSMS, the ...
3
votes
1answer
506 views

SQL Server 2012 Transparent Data Encryption and indexes

Does SQL Server TDE allow a query like this: select * from Patient where lastname = 'Smith' to occur without a full-table scan if the lastname column is indexed?
2
votes
1answer
47 views

Combine 2 tables

I am wanting to combine 2 tables. until recently the column names were different , and one had fewer columns than the other, however it was deemed unnecessary to have them as two tables. I have ...
5
votes
4answers
2k views

Create a secondary read-only copy of DB in a different server

We have users that query our prod database (mostly ODBC through MS-Access) to create some customs reports. We want to copy our data to a second server (replicate / log shipping / ???) so the load on ...
3
votes
1answer
137 views

AlwaysOn Secondary Replica DB-size

I just noticed something peculiar. We're using AlwaysOn availability in one of our environments with a read-only secondary replica. However, checking the .mdf / .ldf files for the secondary replica, ...
5
votes
4answers
13k views

SQL Server 2012 copy database failed

I want to make a copy of database in the same SqlServer . so, When i using the Copy Database Wizard, it throw error: ( I did this steps with a test DB and it works fine !!!!) config: sa user ...
4
votes
1answer
559 views

ADSI Linked Server: Why can I query AD in VBA, but not as a Linked Server?

After all the reading and research I've done, this seems like the most logical place to post this question: Why can I query Active Directory using VBA, but not a Linked Server in SQL Management ...
11
votes
3answers
12k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
2
votes
3answers
308 views

Show all stored procedure definitions (content) for editing

Apologies if this has been asked before, the suggested 'related' questions weren't relevant and my own search turned up very little. I need to list the definitions (code) of all my stored procedures, ...
3
votes
3answers
194 views

Simple yet problematic update query

I have a rather simple update/query, that has been causing me a lot of grief over the years. in simplest form it is : update VillageSemaphore set TimeStamp = getdate() where VillageID in (...
1
vote
1answer
146 views

Significance of has_unchecked_assembly_data due to a persisted computed column after changing compatibility level?

This database has a persisted computed column, which is computed by calling a .NET function. When changing compatibility from MSSQL 2005 to 2012, I was presented with error 10334, which looks more ...
1
vote
0answers
44 views

Restoring SQL Server 2012 Enterprise to Web Edition not possible due to full text partitioning

I have an SQL Server 2012 Enterprise database I’m trying to restore to SQL Server 2012 Web Edition instance. I’ve used sys.dm_db_persisted_sku_features to find features to remove before a backup/...
8
votes
1answer
2k views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
2
votes
1answer
165 views

Deterministic function for getting today's date

I am trying to create an indexed view using the following code (so that I can publish it to replication it as a table): CREATE VIEW lc.vw_dates WITH SCHEMABINDING AS SELECT DATEADD(day, DATEDIFF(day,...
0
votes
2answers
251 views

Force Sql server to use index seek in merge join

I am testing Merge join in sql server. I have an INNER JOIN and force the optimizer to do MERGE JOIN: ID in Personal Table is a Primary key ID in Abteilung Table is a primary key There is no other ...
2
votes
2answers
11k views

SQL Server 2012 setup fails immediately (Error result: -2067529723)

I am trying to install SQL Server Enterprise Core 2012 w SP1 64bit (as downloaded from the Microsoft Partner Program site) on a 64-bit Windows 2008 R2 SP1 server. When simply right-clicking on SETUP....
2
votes
2answers
299 views

Parsing Typed XML with a default namespace and inconsistent root node

I have a structured, typed xml file. <WebService xmlns="http://www.orbis-software.com/WebSvcCon"> <NewLeads> <OutputSchema> <root xmlns="" type="array"> <item type="...