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

learn more… | top users | synonyms (3)

9
votes
2answers
230 views

SQL Server sample Update of Statistics misses highest RANGE_HI_KEY on ascending key column

I'm trying to understand how Statistics sampling works and whether or not the below is expected behaviour on sampled statistics updates. We have a large table partitioned by date with a couple of ...
1
vote
2answers
65 views

Script/tool to use to check when the server is not being heavily used and configuring backup on Availbility Groups

If I were to backup 50 databases in SQL server, I want to make sure that backup is taking place when the server is not that busy. Is there any way or script/tool to use to check when the server is not ...
0
votes
1answer
92 views

Automate backup using Windows Task Scheduler

I want to run an automatic backup of SQL Server 2012 Express using Windows Task Scheduler. I have 3 user databases. I want to backup all 3 user databases through a single stored procedure and a ...
7
votes
3answers
154 views

How to improve estimate of 1 row in a View constrained by DateAdd() against an index

Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64). Given a table and index: create table [User].[Session] ( SessionId int identity(1, 1) not null primary key CreatedUtc ...
0
votes
2answers
57 views

Trying to query AD on a linked server error message is 7321

Trying to query AD on a linked server error message is 7321 error occurred while preparing the query. Any suggestions? Select Name, EmployeeID, Mail, SamAccountName FROM OPENQUERY(ADSI, 'SELECT Name,...
2
votes
2answers
37 views

How do I detect execute permission granted to a role (when no ON clause was used)

I just spent hours trying to figure out how an app had privilege to execute stored procedures. TIL from StackExchange: Turns out the ON clause is optional for GRANT, allowing a role to be granted ...
10
votes
3answers
349 views

Is total_elapsed_time in DMV sys.dm_exec_requests completely inaccurate?

I am running SQL Server 2012 and am trying to put some queries together for monitoring using the DMVs. However, when looking at the total_elapsed_time field in the sys.dm_exec_requests DMV, the ...
2
votes
1answer
64 views

Unable to set user/group as principal on a proxy

I'm organizing permissions on our SQL Server instance. I want to stop using a SQL Server account, and instead grant permissions to Active Directory groups, and then add user and service accounts to ...
0
votes
0answers
11 views

Send SQL Server Snapshot to External FTP Server

I am trying to set up Snapshot-Replication for SQL Server 2014. I need Server 1, the publisher and distributor, to create a Snapshot and FTP to an external FTP server i.e. FTP server is not hosted on ...
1
vote
1answer
84 views

Create Linked Server using Active Directory account

I was able to grant access in MSSQL to an Active Directory group and add a service account to that group. It's working fine, I'm able to use trusted connection to login on that MSSQL using both my ...
0
votes
1answer
33 views

is there a max number of rows in a SQL Server 2008/2012 and MySQL table? [closed]

Im evaluating a RDBMS for a bank app solution and i need to be sure about these performance parameter. There is a lot of info on internet that is confusing me :O. Max number of concurrent ...
2
votes
1answer
134 views

Is it possible to disable use of fast forward-only cursor on server side?

I have an application that is using ODBC to access a SQL Server 2012 database using SQLSetStmtAttr to set the SQL_SOPT_SS_CURSOR_OPTIONS option to SQL_CO_FFO. This means that its reads from the DB are ...
1
vote
2answers
75 views

Can I access the file stream shared folder using Listeners in case of AlwaysOn cluster environment?

One of the most effective ways to extend your SQL Server Integration Services (SSIS) control flow is to use a Script task to write custom code that perform tasks you cannot perform with the built-in ...
2
votes
1answer
90 views

Joining 2 queries to match Months side by side

I have 2 SQL statements that I want to join up. The first gets the actual data per month for Turnover accounts. The second query gets budgets (if any) for the same Turnover accounts. Query 1 Actuals ...
2
votes
2answers
71 views

SQL Server 2012 Replication - MSrepl_commands growing all set to in progress

I'm stuck looking at this problem that we've had since our last restart almost a week ago. The distribution database is growing and no longer pruning; it appears that everything in the ...
1
vote
1answer
81 views

Deny users permission to create tables in master

I have noticed that I can create tables, schemas, stored procedures etc. the master database. I don't know what is granting this permission. It's annoying, because when we open a *.sql file in SSMS, ...
4
votes
2answers
277 views

How can I find the Database ID of a deleted database?

Where can I view/find the database ID after a database was deleted? I work in software/hardware environment and we have locking unit (prototype) synced in our S/W with unique DB ID. In order to reset ...
3
votes
2answers
133 views

Grant Admin to an Active Directory account in SQL Server

I have a SQL Server 2012 instance, and an Active Directory group, which I can add Active Directory users to. I have my personal AD account (MyAccount) and a service account (ServiceAccount) in that ...
1
vote
0answers
21 views

Server error in reports application

I have a server with SQL Server 2005 installed with the latest service packs. SQL Server 2012 express was been installed on same machine and seems to have broken the Reporting services service. When I ...
3
votes
2answers
67 views

Strange Database Mirroring Problem

I am noticing a strange problem with DB mirroring on SQL Server 2012 (standard edition). The mirroring is set up, and successfully established, and working. There is no witness. Once principal is ...
3
votes
1answer
87 views

Don't see it : Integration Services Catalog, and open the [All Executions] report

I have SQL Server 2012 Enterprise Edition (64-bit) on Windows NT 6.2. I am not seeing the option to the "[All Executions] report" in the Integration Services Catalog . How can I solve it? Update: I ...
3
votes
2answers
110 views

Transaction in a stored procedure

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest ...
2
votes
1answer
37 views

querying sys.database_principals accross a linked server

I'm attempting to get a listing of the users and roles they are associated with on a remote server to be input into a database on the local server. But when I use the linked server sys....
0
votes
0answers
106 views

SSIS package fails, Odata connector to SharePoint Online

Using package developed in SSDT2012, deploying to SQL2012. I've worked through many errors/issues to get to this error: Package:Error: Failed to initialize SharePoint Client Runtime. Could ...
0
votes
1answer
24 views

Find all stats that were sampled

I am using Ola's scripts for stats updates and for one particular index I want to do full scans when updating stats (to solve for the Ascending Key problem in conjunction with trace flag 2389). We ...
1
vote
1answer
66 views

Combining 2 SQL queries

I have these 2 queries below that I use to calculate net profits from Jan to Dec. Query 1 /* Profit for the Period 13 */ SELECT * FROM ( SELECT MONTH(T0.RefDate) AS Month, '311999' AS 'SAP Code', '...
0
votes
1answer
44 views

Memory usage increases whenever I run stored procedures [duplicate]

I want to know why SQL Server memory use increases whenever I run stored procedures. After I stop and restart SQL Server, the memory used is just 1 GB. Each time I run stored procedures, the memory ...
0
votes
2answers
72 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
17 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
108 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
32 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
38 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
41 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
71 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
44 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
76 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
72 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
29 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
23 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
67 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
69 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
52 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
172 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
68 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
109 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
80 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
48 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
71 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. ...