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

learn more… | top users | synonyms (3)

1
vote
1answer
56 views

Same query plan, different data set, very different query duration SQL Server 2012

This may be a terrible question, because I'm not sure how information I can include to help. We have data segregated by customer. One customer apparently has higher volume of data. The same query ran ...
0
votes
1answer
24 views

SQL Query for searching table 'C' records only if records from table 'A' has specific values

I Have Three Tables, TBL-A, TBL-B, TBL-C, TBL-D TBL A has records for userstatus RECID USRID USERSTAT USRNAME 1 1 1 JOHN 2 5 1 HARRY 3 ...
3
votes
1answer
68 views

Can this SQL Query be improved

Related to one of my previous questions: How to select a subset from a query result I now have this Query: CREATE TABLE #JourneyLogs ( JourneyStartId INT, JourneyEndId INT, ...
1
vote
1answer
58 views

Can you provide rights to a snapshot without providing rights to the database?

Is it possible to give a user or group rights to a snapshot without giving them rights to the database if the database and snapshot both reside on the same instance?
0
votes
0answers
41 views

Cluster network name resource 'XYZ' cannot be brought online

I have given wrong name for listener while configuring the always on in SQL Server management studio. One option I can see is deleting and re-creating the listener, But is it right way of doing ...
2
votes
1answer
171 views

Query Time is doubled because of a column in select

I have a Stored Procedure which is calculating some data and inserting it into a Temp Table. Then I am selecting the Data from the Temp Table. Select AssetId = iAssetId, ..... SpeedKM = ...
2
votes
2answers
64 views

Why am I able to query for more than 1024 columns but I can not make a view of the same query?

My boss is requesting a view with greater than 1024 columns where in he will only select a few columns he wants when he queries the view so that the actual returned columns are less than 1024. Why am ...
9
votes
1answer
103 views

Why is the MSDB database TRUSTWORTHY?

The TRUSTWORTHY setting can be rather dangerous if you aren't careful and except for specific circumstances the recommendation is to keep it turned off. However by default the MSDB database has ...
-1
votes
1answer
69 views

Filter first then join or join first then filter?

Consider 5 tables with more than 1 billion records each. A query needs to join them. And I know that less than 10% of their records will be require. Say they all have a Date dimension, and only data ...
-1
votes
1answer
42 views

Data warehouse backup strategy

I have a data warehouse development environment. Just as the production environment. it loads data every night from test OLTP databases. I want a backup every time the load process finishes. What ...
2
votes
1answer
68 views

Hold exclusive lock on a whole DB

As I explained in Set a filegroup to readonly, I wanna set some tables readonly, to have better performance. These tables are only written by an ETL that runs daily, then they are only read. ...
3
votes
2answers
53 views

Is there a way to validate XML using variable as schema?

Is there a way to declare XSD schema as variable inside a construct: DECLARE @xml XML(Books)? I need to make quick validation of XML using XSD schema which is declared as variable. I suppose there is ...
0
votes
0answers
34 views

SP Behaves Differently on Production In Case Of Joins

I have this stored procedure in my database SELECT up.[Id] AS UserId, up.[DisplayName], up.[IsPublic], details.[Notes] AS LastNotes, details.[OriginDt] AS LastDate, ...
2
votes
2answers
129 views

Using Powershell Variables in a File-Stored Query with SQL Server

I'm attempting to run some SQL through Powershell, using a wrapper function in PS to run the SQL command. As well, my SQL is in its own independent file and not hard-coded inline to the Powershell ...
2
votes
3answers
94 views

How can I configure maximum number of concurrent connections WITHOUT SSMS?

I'm having an issue in a production environment and I was trying to simulate it in my local SQL Server Express instance. I used SSMS to change the "maximum number of concurrent connections" from "0" ...
1
vote
1answer
42 views

SQL Server 2012 AlwaysOn Availability Group active/passive

I have one Primary and one secondary replica on WSFC and AAG configuration. Both nodes are physical machines and sync with automatic fail over. The secondary replica is readable and we run ...
2
votes
1answer
49 views

Set a filegroup to readonly

I have a set of dimensions which are updated only during specific times. I have moved these tables to a specific filegroup named Dimensions. I wanna keep this filegroup as readonly. When ETL runs, it ...
0
votes
2answers
49 views

Notify when database is accessed in any manner

I'm using SQL Server 2012. Is there a way to receive a notification (email/log etc..) if a database or table in a database is touched in anyway (e.g. CRUD via Stored Procs or tables opened via ...
1
vote
0answers
63 views

A significant part of SQL Server process memory has been paged out

Around 7:30pm every evening we receive the following message and the server experiences an outage of 2 minutes. A significant part of sql server process memory has been paged out. This may result ...
3
votes
1answer
33 views

Separate differential backup from one restored baseline

I work on a team that customises software, part of this customisation is stored as records in a database, part is customisations to the schema itself and there are often some specific records in the ...
1
vote
1answer
20 views

Checks to be done, before/after changing the windows login password? [duplicate]

Here we have a scenario where a Service account , added under security as sysadmins for windows login, going to have its password change as per windows policy. Since windows team, server admins are ...
0
votes
1answer
29 views

Primary server 'XYZ' can not write to shared drive during availability group configuration

I am in a process of adding databases to availability group. In one of the steps it was asking "data synchronization prefferences" as shown below I have put the mapped drive location path, which ...
0
votes
1answer
14 views

Catalog of LinkedServer2 from Server1 shows databases on Server1

I'm on Server1 and set up a linked server to LinkedServer2, but the catalog shows the databases on Server1! I am sure I've done something bone-headed or am overlooking something horrendously obvious, ...
0
votes
1answer
14 views

Select records that fit criteria in another record

I have a procedure that I want to return only when a second procedure was selected. Id Name Procedure 1 Jim Smith Procedure1 2 Jim Smith Procedure2 I would like the first record ...
0
votes
1answer
28 views

I do not see SQL Server instance name under roles section of Fail over cluster manager screen! How to setup?

I have created 2 Virtual machines in azure and configured Windows Cluster using fail over feature. I have enabled the "AlwaysOn" feature in SQL Server Configuration Manager. But I do not anything ...
4
votes
1answer
57 views

How to create a 'keywords' table from a column's values

I have a table with 8 million+ rows in it. It stores jobs and their characteristics. There is a column in it that holds job titles. I am planning to create a keyword table based on the job titles from ...
8
votes
2answers
161 views

Backing up multiple databases to the same point in time

Usually when we start the backup we do not allow changes to commit or database will be not be accessible. I mean database will be in single user mode but I want to start the backup and release the db ...
1
vote
1answer
67 views

Shared network location in Availability Groups configuration

I am configuring Availability Groups feature in azure virtual machines nodes. In the below screen wizard, it's asking me for shared location. where and how I need to create this shared location? Do ...
1
vote
1answer
85 views

Restore accidentally truncated table

I truncated the wrong table, but I have a backup made an hour before the incident. I am trying to restore but at 30% I get the error: Restore of database 'msdb1740' failed. ...
2
votes
1answer
50 views

Best Practice : Schema design for Large Number of rows with few columns

I have about a data set that each entry is about 750,000 rows under it with each row having four fields. Each entry is in its' own CSV file currently and I want to get it into a database. Currently I ...
7
votes
4answers
465 views

How to get response from stored procedure before it finishes?

I need to return partial result (as simple select) from a Stored procedure before it is finished. Is it possible to do that? If yes, how to do that? If not, any workaround? EDIT: I have several ...
1
vote
1answer
42 views

Packaging SQL Server Express 2012 install won't install due to user rights

Is there a way force the above permission window to elevate the user permissions?
0
votes
1answer
39 views

Flagging duplicates

I have a huge table with a couple of duplicates: I want to add a new column that shows some value when duplicates occur, something like: case when fist.firstname = last.firstname then 'yes' else ...
0
votes
2answers
23 views

Source of network_name for distributor

So, I have a really weird case. Was doing a migration from sql server 2005 to 2012. Built the server, and at the last minute we found that the server was called abc/xyz. We just wanted abc, so the DBA ...
0
votes
0answers
32 views

Is it possible, when using LPIM, for one database to allocate the majority of the memory?

Windows Server 2012 R2 SQL Server 2012 Enterprise (maximum server memory: 200,000MB) 224GB memory LPIM enabled physical_memory_in_use_kb 204703788 large_page_allocations_kb 1128448 ...
1
vote
0answers
27 views

Joining tables and seeing Duplicate Records

I Use the query below to produce values from a table (BSCV1), based on results from another table (MshiftV1) using an inner Join on names and timestamps. I call the output "Correlation". WITH ...
0
votes
1answer
18 views

How do I allow users to add members to a specific database role?

I want to GRANT permissions to a new database role so that defined users can add and remove members from that role WITHOUT granting ALTER ANY ROLE permissions. Here's how my query runs/dies: CREATE ...
5
votes
1answer
50 views

T-SQL (SQL Server 2012) Update based on lookup table

I have the following tables related to this question: CREATE TABLE tblTitan( titanId int IDENTITY (15483,1) PRIMARY KEY, userId int UNIQUE NOT NULL, titanName varchar(15) NOT NULL, titanExperience ...
0
votes
1answer
86 views

SQL Server BACKUP WITH FORMAT

When doing a SQL Server database backup using the T-SQL BACKUP statement, does WITH FORMAT only wipe out the specified Media Set or does it wipe out all Media Sets 'associated' with the database? ...
4
votes
0answers
40 views

Large Variation in Bulk Insert time

So I have a simple Bulk Insert process to take data from our staging table and move it into our datamart. The process is a simple data flow task with default settings for "Rows per batch" and the ...
0
votes
2answers
72 views

Linked Server Error on SQL Agent Job

I get the below error from a SQL Server Agent Job. However, if I run the query in the job manually it works fine. Why is this? Is it just SQL Agent that requires MSDTC? I don't believe it is ...
0
votes
0answers
42 views

Importing Legacy Files directly into SQL Server database

I have a bunch of flat files, mostly Excel files, which I am trying to import directly into a SQL Server 2012 database. Is there a way to do this? I assume first the columns in the Excel files must ...
1
vote
0answers
42 views

Backup fails unless compression is enabled

I'm trying to back up a DB to a network share. The backup fails every time with the same error: BackupIoRequest::ReportIoError: write failure on backup device '\\share_name\Backups\filename.bak'. ...
1
vote
1answer
66 views

SSMS connects, Linked server fails

There's a SQL Server 2012 machine which I have login and password. It's a SQL Server login, not an AD one. I'm able to login using SSMS. But when I configure a linked server in my SQL Server I get ...
2
votes
0answers
46 views

where to add a column with low cardinality, decimal type into an index?

I got a question regarding the best way to put a bad column for an index in an index, so that I can satisfy my query below, and similar others that follow the same pattern. on the query below, all ...
0
votes
0answers
82 views

SQL Server could not retrieve the list of publications for 'XYZ'

First of all please note the following carefully: I have a source database named S_DB on one SQL Server hosted on a machine named S_SRV, on a domain named S_DOM. On a different domain named D_DOM, ...
0
votes
1answer
48 views

SQL Server Backup - move to new server - Media Failure

I've got a server in the UK with SQL Server 2008. It's very locked down firewall-wise. We want a local duplicate of the DB to test and build a new report. Backing up the relevant DB, and then ...
1
vote
0answers
16 views

I only have the original files of database but no .bak file, how do I restore the database? [duplicate]

We were upgrading a server from SQL Server 2012 to 2014 at work and it was giving us trouble so I passed it off to someone else who, when backing up the SQL Server database, didn't create a .bak file. ...
1
vote
0answers
46 views

Multiple conditional counts in the select clause - what's the best way?

(Bit of a long one, sorry) To set the scene: N.b. This is on SQL SERVER 2012 We have a table [CardLog] in an access control system database. Every time an ID card is printed, a row is added to this ...
1
vote
2answers
46 views

Find times when table is least frequently used

I would like to analyse performance of one of the big tables in the database. There are only two types of operations on it. Inserts and reads. Once data is entered it is never updated. I need to ...