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

learn more… | top users | synonyms (3)

0
votes
3answers
52 views

Find how many SQL Server instances are installed [closed]

On a SQL Server 2012 instance, how can I get the names of SQL Server instances using a SQL query? I want to show instance names currently installed in SQL Server.
0
votes
0answers
14 views

Any potential issues with changing the owner of a symmetric key?

We're doing a security audit on a database I support, and there's one old, obsolete user who can't be removed because he owns a symmetric key in the database. The symmetric key is being used to ...
3
votes
1answer
74 views

How to resolve this 'XML Validation: Declaration not found for element' error?

I'm new to SQL Server and I have this task to complete (for learning purposes), yet I'm unable to figure out this error due to inexperience with XML Schemas (I think) Here's what I'm trying to do - ...
0
votes
1answer
29 views

sql agent job timeout auto restart

we have a DWH load job that sometimes hangs. We have not been able to determine why this is happening it seems to be random and network/hardware related (which we have no control over). We are ...
2
votes
2answers
31 views

Trying to drop database only if it does not exist by using the db_id function fails

When the database foo is not yet created and I I run this IF db_id(N'foo') IS NOT NULL BEGIN ALTER DATABASE [foo] SET OFFLINE GO DROP DATABASE [foo] GO END I get a message Cannot drop ...
1
vote
1answer
31 views

Difference between Generate Script option at database level and backup and restore option

When doing that generate scripts on the database level what difference does it do from doing a backup and restore on the database? It seems to me that they both provide the same purpose. What would be ...
2
votes
3answers
38 views

Cannot create SSRS subscription for a report using SSAS as datasource

My questions is : how should I config SSRS subscription a report, which consumes SSAS datasource. I already googled and lookup stackoverflow a lot and still couldn't make it work. Problems: I ...
3
votes
1answer
41 views

Help with SQL Server and XML columns

I am writing a query to run against the SCOM 2012 OperationsManager database. SQL instance is Microsoft SQL Server 2012 - 11.0.5058.0 (X64). Within the database is a table called Monitors. The schema ...
2
votes
1answer
58 views

How to automatically fix Orphaned Users with PowerShell and SQLCMD

I'm trying to properly capture output from SQLCMD in SQL Server 2012. Here is the PS script: $dbname = "MyDB" $InstanceTarget = "localhost\SQLEXPRESS" $FixOrphansSQL=@" WITH login_CTE (name, type, ...
0
votes
2answers
40 views

After trying to restart moved database: Unable to open the physical file “D:\Data\MYDBNAME.MDF”

So the default location for databases on our server is c:\program files\microsoft sql server\mssql11.mssqlserver\mssql\DATA but due to space limitations we instead use d:\data I have now since ...
0
votes
1answer
28 views

Returning only the row with maximum value from related rows

In the example below how do I get the rows where the swap id is the maximum value in relation to the reservationId? I am currently joining multiple tables to get customer, reservation, room etc ...
0
votes
1answer
16 views

Problem with Processing Dimensions with Multiple Facts

I have a cube built in SSAS that uses 3 facts and 8 dimensions. Some of the dimensions are shared across 3 facts. When I process one of the dimension, it tries to process the underlying measure ...
1
vote
1answer
46 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
65 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
56 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
31 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
166 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
62 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
94 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
61 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
40 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
64 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
46 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
103 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
67 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
37 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
46 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
41 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
57 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
32 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
27 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
11 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
13 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
24 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
56 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
151 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
58 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
81 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
40 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
464 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
31 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
26 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
47 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 ...