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

learn more… | top users | synonyms (3)

0
votes
1answer
39 views

Need help with a query for a helpdesk application

I have a helpdesk application I'm modifying. I need to pull data into an ASP.Net DataGrid. In the problems table, there is a field for employeeID, which is the employee id of the person entering ...
3
votes
1answer
95 views

Grant access on Secondary but not on primary in SQL Server logshipping [duplicate]

I have a strange situation wherein users need permission on log shipped DB on secondary server but not on primary. The problem is, the login has access on many other DBs on primary server apart from ...
0
votes
0answers
154 views

PREEMPTIVE_OS_FILEOPS when querying Filestream

I've have a table which contains a Filestream column, when I query the table as follows: SELECT ID, FileContent --, * FROM dbo.Files AS f WHERE FileContent IS NOT NULL AND ID = ...
4
votes
1answer
568 views

Scope_Identity() returns null after inserting first row in table

I have a stored procedure that is used for inserting values in two tables. The tables have a parent-child relationship i.e. the first table has an identity column, and the second table references the ...
1
vote
2answers
519 views

Getting error “Time-out occurred while waiting for buffer latch type 2 for page (1:3564879), database ID 7.” But a database with Id 7 DOES NOT exist

I have a testing environment where I have scheduled jobs running restore tests as well as DBCC checks. These jobs run to: 1) copy database back up from the prod. 2) DROP all existing databases except ...
5
votes
0answers
118 views

Always On Availability Group, Always redirect user to read only instance

We have an Always On availability group with a primary and a read-enabled secondary. We have a user for the implementation team who uses the database to check the correctness of the data that they are ...
5
votes
2answers
169 views

How can I efficiently downsize some Unicode fields?

We have a SQL Server 2012 Enterprise live transactional database that is now growing over 1G per month and is becoming a size problem for us. It is currently at 23G. Character type fields are all ...
1
vote
0answers
221 views

iSeries Linked-Server SQL 2012 performance issue

I have a seen a serious performance dive when moving from SQL2008R2 to SQL2012 on my linked server queries. I have set the linked server on the SQL2012 server as a Microsoft OLE DB Provider for ODBC ...
0
votes
2answers
46 views

Granting Select access to all tables within a specific Database in MS SQL

I have a server that has multiple databases that are contained within that same server/project. I am using MS SQL Server 2012. I have a special role set up with 3 users that are apart of that role. I ...
2
votes
1answer
204 views

SQL Server 2012 Always On Setup with two nodes

I have AlwaysOn setup with two data nodes and one witness. I faced an issue where my PRIMARY Server restarted abruptly. Here're some issues I faced during this timeframe. Due to abrupt restart of ...
0
votes
0answers
22 views

Same query takes even 100 times more ran from IIS app vs Management Studio

Sometimes (after a new deploy but which doesn't have any db updates) a specific query takes even 100 times more time to be executed than standard (or Management Studio). A few details: The query is ...
2
votes
3answers
190 views

Excluding clustered key columns from non-clustered indexes definitions

As I've read the bookmark or the reference of the non-clustered index to the table clustered index is the clustered index key itself. On the SQL Saturday event I've attended, one of the lecturers said ...
7
votes
1answer
184 views

Does SQL Server 2012/2014 Standard support Hot Add memory on a Hyper-V guest?

In this support article: https://support.microsoft.com/en-us/kb/956893 The answer to Q7 says: Hyper-V Dynamic Memory is fully supported with SQL Server. Only SQL Server versions and editions ...
3
votes
1answer
590 views

The multi-part identifier “d.dept_name” could not be bound

I want to create a table with department name as column name and no. of employess for each department as its row. Here dept_name is coming from Dept table and count of emp.ID is coming from emp ...
1
vote
2answers
323 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 ...
0
votes
0answers
45 views

SQL Server 2012 - Query Governor Not Working Properly

Our production instance has been experiencing long running queries, in an attempt to keep them from running long, we decided to implement the Query Governor. I started by testing in our development ...
1
vote
1answer
33 views

Batch process to delete table from SQL Server backup files

I have a set of full backups from a same SQL Server database saved in different files. For example: 01.my-database.bak 02.my-database.bak 03.my-database.bak I want to delete a specific table from ...
5
votes
2answers
108 views

Help optimising delete statement

Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate ...
1
vote
2answers
94 views

SQL Server compatibility level

I inherited a database server with SQL Server 2012 Enterprise Edition. All the databases (except for the system databases) are at the 90 compatibility level. How do I justify updating them to 110 ...
1
vote
1answer
54 views

Using RowLock to enforce Once-And-Only-Once

I have a Web Service that can fire off a chain of events for an order. It does this when it gets a message telling it to (max of about 2 per second). The problem I have is that I have more than one ...
5
votes
2answers
292 views

Import XML files into SQL Server 2012

Every Friday I have to import a couple (sometimes more than 300) of XML files into 2 tables. The structure of one of the tables, R000000, looks like this: R00000010 | R00000020 | R00000030 | ...
4
votes
1answer
855 views

DB Engine Stolen Server Memory is too high

I get error from System Center Operations Manager (SCOM). How to solve this error? SQL DB 2012 Engine Stolen Server Memory is too high. I run query and get this counters: ...
0
votes
0answers
47 views

FreeTds with 2 sql server instances

I have python scripts on a Linux machine that connect to Microsoft sql server to get data. The server was initially 2008 version. Now it has both 2008 and 2012. How can I point the tds to connect ...
4
votes
1answer
110 views

Scenario of huge SQL Server Database backup

I use SQL Server and have a huge database with large compressed backup size (about 250 GB). The backup scenario of this database is: Create full backup at the first day of each month. Create ...
6
votes
2answers
163 views

sys.dm_sql_referenced_entities fails to provide used columns list when joining with temp table

I think I need some help as I run out of ideas. In my database I have a simple table like this: CREATE TABLE dbo.Invoices ( CashDiscounts MONEY, Cost MONEY ) GO and a stored ...
0
votes
1answer
143 views

“Unspecified Error” when executing SSIS Package

I am executing an "Initial Load" SSIS package, which truncates all ETL tables first, then re-populates them by pulling data from a remote server. I get the dreaded "Unspecified Error" at a different ...
1
vote
2answers
92 views

How many deadlocks am i really getting? system health or deadlocks/sec?

I'm trying to look at deadlocks on a troublesome server, and when I look at the system_health xel file, I see 80 records for "xml_deadlock_report" in about 16 hours. I assume these are all different ...
0
votes
1answer
166 views

SQL Server 2012: Users cannot see database properties

I migrated a database from SQL Server 2008 to 2012 and also the users. Now the weird thing is that if the user connects to SSMS on the same server as the SQL Server engine, he can display database ...
4
votes
2answers
198 views

Help with query

I have following data. I am trying to get the number of days the project stayed in a particular status. Example below of raw data, I need to be able to get the result shown. Can I get some ...
0
votes
1answer
87 views

DENY VIEW DEFINITION TO PUBLIC

I have a SQL account with no permissions other than having the public role. I have run the following command to deny permissions to the system views: use master DENY VIEW ANY DEFINITION TO ...
0
votes
1answer
86 views

Exploring async DB IO - what is the cause for “The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement”?

Please, observe the following code: static async Task<Result> IOAsync(string duration) { using (var conn = new SqlConnection("server=localhost;database=abc;uid=xyz;pwd=123;Max Pool ...
5
votes
1answer
67 views

Online clustered index rebuild using more log space than expected

I have a table TableA ID PK BIGINT TrxID BIGINT CardBalance INT AccountBalance INT SP_SPACEUSED 'TableA' output: name rows reserved data index_size unused TableA 904054184 ...
2
votes
2answers
917 views

AppDomain 2 (mssqlsystemresource.dbo[runtime].1) is marked for unload due to memory pressure message in SQL Server Log

From last two days I can see following in SQL Server errorlog. AppDomain 2 (mssqlsystemresource.dbo[runtime].1) unloaded. & AppDomain 2 (mssqlsystemresource.dbo[runtime].1) is marked for unload ...
1
vote
0answers
37 views

Disable the Task action button

How can I disable the "Tasks ->" button in SQL Server Management Studio? I need to prevent users scripting objects.
1
vote
0answers
36 views

Date picker is dynamic in SSDT but not in SSRS

Running SQL Server 2012 SP1 and SSDT from same distribution. (VS2010) When I display a report in SSDT the date selector on a date parameter is dynamic or responsive. When you click on the date ...
1
vote
2answers
31 views

Selecting Values That Differ From Section Average

I'm attempting to select data from a table that's already been made containing records of file size as taken every twelve hours. The table looks something like this: surrogate_id | partition_id | ...
2
votes
1answer
72 views

Generate Database Creation Script Within A Stored Procedure

I'm looking to automate a wipe of our test environment to bring it up to be in-line with production at the end of every sprint. As it stands this is currently being completed by the test enviroment ...
0
votes
2answers
645 views

How to replicate data between two SQL Server 2012?

I have two SQL Server 2012 databases running on two different Windows Server instances, I want the Server A (DB1) to be in full sync with the new Server B (DB2). How can I make this connection ...
1
vote
1answer
52 views

storing as smallint, querying as int

A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're ...
2
votes
0answers
79 views

Every time I run a stored procedure it takes longer to complete until I restart the SQL Server [closed]

I need to optimize a critical stored procedure running on SQL Server 2012. The idea was to measure how long it normally took to run this procedure, make a few changes, measure the improvement and ...
1
vote
0answers
48 views

Long Transaction On File Upload Locks Strategies

I need to upload files to some SFTP or FTP servers (using libcurl) and register them in database. For this I have several tables -- locations to store files - some SFTP and FTP servers CREATE TABLE ...
4
votes
1answer
117 views

transaction across multiple databases - what is the overhead?

I run the following update, which is one single transaction across 2 databases. Please note I have not committed the transaction. --================================================================ ...
0
votes
1answer
57 views

Sql Server 2012 Failover Replica in Synchronous Mode between Primary-Secondary on VPN is very slow

I have installed SQL Server 2012 Clustered HA with Replica in Sync mode between two nodes connected by an high-speed VPN. The DB is encrypted. Everything works fine, but to do the replication, the HA ...
2
votes
1answer
101 views

Should you encrypt the system databases?

A finding from a STIG evaluation, CCE-19832-5 reports that the [master] database should be encrypted with a Database Master Key using an encrypted password that meets DoD standards. This a result of ...
5
votes
1answer
390 views

CDC system tables missing from backup

I am migrating a database that uses CDC from SQL Server 2008 R2 to SQL Server 2012. There are CDC-related tables under the "System Tables" branch in Object Explorer: cdc.ddl_history ...
0
votes
1answer
138 views

how to calculate the next time a job will run - by each schedule

I have a script that shows me when it is the next time a job(s) will run, and other things, the dates are formatted, etc. the script is here, and it works fine: USE msdb Go set transaction ...
1
vote
1answer
38 views

putting together contents of XML column - from all rows of a table - how to get it done without a “while”?

I have a table called sizeguide with the definition you can see below. I am interested in the SizeGuideXML column, which is type XML. I had a mission the objective was, for each SizeGuideDesc, get ...
9
votes
1answer
1k views

“truncate log on checkpoint” option in SQL Server

Long story but our long term consultants (former employees) wrote a custom script years back (2006 or so) to interface with Tivoli Storage Manager and it appears to be checking for a SQL Server DB ...
3
votes
1answer
162 views

Always fetch 3 records in a particular order

I have a query which gets 3 books (ABC1,ABC2,ABC3) and their details. I need them in a particular order for pivoting so I have a Row number column for the same. People read none/some/all books. ...
1
vote
0answers
79 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: ...