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

learn more… | top users | synonyms (3)

3
votes
0answers
75 views

Unable To Log In Through Windows Authentication

See this question for details of troubleshooting I've been stepping through (I did answer 2, which didn't work - it was a corrupt master file). In configuration manager, I changed to a Built-in ...
6
votes
1answer
84 views

How do I replicate a database over 2 geographical sites?

Disclaimer: I have no idea what I'm talking about here, or if what I'm doing is the correct thing to do. Long story short, my superior has asked me if it's possible to replicate our internal database ...
3
votes
1answer
113 views

Create table in Oracle as select from SQL Server - varchar size

I am trying to create table in Oracle 11g as select from a table in SQL Server 2012 via database link. The table in SQL Server contains column: hslakkis varchar(3) In Oracle it created the table ...
5
votes
2answers
110 views

Is execution plan cached “better” for stored procedures than for a non-dynamic query?

Reading different explanations about execution plan caching by Microsoft SQL Server, I'm confused about the benefits of using stored procedures instead of non-dynamic queries. By a non-dynamic query ...
0
votes
2answers
86 views

SQL server 2012 service pack installation

which service pack I should apply first after installing sql server 2012 enterprise edition either sp1 or sp2 ? Thanks in advance.
9
votes
3answers
338 views

What is the use of restore headeronly when performing a backup?

When I monitor my backups with the following query: SELECT command, percent_complete, 'elapsed' = total_elapsed_time / 60000.0, 'remaining' = estimated_completion_time / ...
6
votes
1answer
41 views

Update an Indexed View “Online”

Say I have an indexed view that looks like this: ALTER VIEW dbo.MyIndexedView WITH SCHEMABINDING AS SELECT ord.SomeColumn, COUNT_BIG(*) AS Count FROM dbo.Ordered ord WHERE ord....
6
votes
1answer
47 views

Indexed (Materialized) View for Parent and Newest Child

Say I have parent->child tables setup. Call them Shipment and Widget. Is there anyway to create an Indexed view that will have the PK of the parent (ShipmentId) and the created when of the newest ...
4
votes
2answers
118 views

Kill all sessions for ONE user in MS SQL

So we have a bad external application that connects to the database, executes a stored procedure and gets an answer. It never releases the database connection. I need a script that either kills all ...
4
votes
2answers
278 views

db_datareader, db_datawriter and db_owner permissions [duplicate]

First of all, sry because I am not a DBA and I'm not familiar with all the permissions. My question is, if I give a user db_owner permissions on one database. Do I need to give also db_datareader and ...
2
votes
1answer
105 views

Script file execution through Power shell

We are trying to automate few things, one of them is to deploy patch in one go on selected DBs of SQL Server, as per my knowledge Powershell is an option. But I have zero taste buds of Power shell. ...
1
vote
1answer
49 views

On an idle instance, why does setting max memory to 256mb cause %CPU to jump

Five 2012 instances on a 2-node cluster using AVGs. Two of them doing nothing yet, no user databases or users. Customer insists on specific max mem settings while waiting for additional memory ...
0
votes
1answer
38 views

turn off (synchronized) database

I just found a database, and I have no Idea what to do. I can't drop, update, or anything. The database is in this state: How can i turn it off? Because, it's already off ! I tried to restore ...
8
votes
1answer
166 views

What does the “reads” column in sys.dm_exec_sessions actually indicate?

This may seem like a very basic question, and indeed it should be. However, as a fan of the scientific method, I like to create a hypothesis, then test it to see if I'm correct. In this case, I'm ...
7
votes
1answer
182 views

Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?

I have this database table that is suppose to keep the full-text index up-to-date. However I am not seeing it happening at all (no error in the log because the last log I see was when I triggered it ...
1
vote
0answers
232 views

What does 'Could not find a log backup file that could be applied' means on log shipping?

Skipped Log Backup File ..... Could not find a log backup file that could be applied to secondary Database What does this mean? I've configured a log shipping. To test, I created 2 tables, put ...
2
votes
1answer
78 views

SQL Server is running slow after a certain amount of time [closed]

We've been experiencing some issues with our SQL server recently. I have noticed that some of our SQL jobs are running longer than normal. One job normally runs in under 3 minutes will take over a ...
5
votes
3answers
148 views

What are the SQL Server 2012 versions of FIRST() and LAST()?

I have a table with a value column. I want to calculate the last row minus the first row, as shown here: id value 1 10 2 45 3 65 4 95 . . . . ....
0
votes
2answers
55 views

Update row count on corrupt table

I have a corrupt table, 1 dodgy page about 1.6 million rows into a 2.9 million row table. I want to attempt this: ALTER DATABASE [CorruptDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DBCC CHECKDB (...
3
votes
1answer
140 views

IS-A relationship with only single sub entity

Hi I'm trying to design a database for a dictionary. The words has several fields like meaning, an example, and maybe a picture. but the words that are 'verbs' has three more special fields that the ...
3
votes
2answers
203 views

How to import blanks as nulls instead of zeros while importing txt using wizard

I'm using the Import Wizard to load a text file and need blanks in integer fields to be nulls, but only zeros are inserted. How to import it properly?
1
vote
1answer
483 views

Installing SQL Server 2012 error

I am installing SQL Server 2012 on Windows 7 Enterprise on a company laptop. I am getting SQL Server Setup failure An invalid return code was encountered waiting for a program to execute. ...
3
votes
1answer
78 views

Can SQL Server 2012 FCI have multiple names?

Is it possible to have multiple names for the same SQL Server Failover Cluster Instance (virtual server)? IT has a naming convention they like, but it hard codes the Windows cluster name into the SQL ...
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
201 views

Microsoft.ACE.OLEDB.12.0 Error when querying Excel

I am running the following query: SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=D:\GSS.xlsx;HDR=YES', '...
5
votes
2answers
239 views

How to name table-variable function unique constraint?

I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as ...
0
votes
2answers
149 views

What is the alternate to SA account in SQL Server? [closed]

My client is currently using SQL Server for their app. At this time they are using SA account everywhere. For security reasons we need to disable the SA account. The solution I've is create another ...
2
votes
1answer
366 views

SSIS package fails intermittently with: Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0

I have an SSIS package deployed to the SSIS catalog on my Data warehouse which has run successfully for months. The package is executed by a SQL agent job. Now it fails intermittently with the ...
0
votes
0answers
28 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 ...
0
votes
1answer
59 views

Availability Group failover stuck in Resolving State

I am setting up a lab for testing automatic failover for SQL Server I have a 2 node WSFC (SQL-CLUSTER-TEST-01) with the nodes SQL-TEST-01 SQL-TEST-02 A listener SQL-HA-GRP-01 My initial testing ...
1
vote
0answers
123 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' ...
4
votes
1answer
387 views

Getting a proxy account to backup to a share using Ola Hallengrens backup script

I have a SQL Server 2012 Standard install where the Engine and Agent are running under the default NT Service accounts Engine - NT Service\MSSQLSERVER Agent - NT Service\SQLSERVERAGENT I am trying ...
4
votes
1answer
110 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 ...
1
vote
1answer
90 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
2answers
77 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 ...
2
votes
1answer
120 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 ...
0
votes
1answer
54 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 ...
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,...
1
vote
1answer
187 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 , ...
2
votes
2answers
78 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 ...
22
votes
4answers
2k views

Copying (hundreds of) tables from one server to another (with SSMS)

I have several hundred (currently 466, but ever growing) tables I have to copy from one server to another. I have never had to do this before, so I'm not sure at all on how to approach it. All the ...
3
votes
0answers
91 views

Rebuild index online without risk to other requests

We have a SQL Server 2012 Enterprise Edition database whose indices fragment very quickly. We use a maintenance plan that reorganizes those indexes daily, and rebuilds them weekly. The rebuild ...
-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
1answer
17 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
120 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 ...
1
vote
0answers
339 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 ...
0
votes
2answers
164 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 "...
4
votes
2answers
191 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 ...