SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

3
votes
2answers
82 views

Return values from Left Table Only Once

This is my table structure and a sample query. However, my query returns these results: ID event1 Exists 1 cycle No 1 cycle Yes 2 run Yes 3 walk No As you can see ID 1 is ...
1
vote
2answers
76 views

Slow query that times-out my application, any advice?

I have a query that it seems taking about 50 seconds to execute from SQL Management Studio, but when a user tries to run it as a report from a front end application (that was built in VB), the ...
1
vote
0answers
22 views

SQL Server 2008 R2 Service - large memory consumption [duplicate]

I have a SQL Server instance where the service is consuming 173 GB, the max server memory is set at 60 GB. How do I determine where that memory is or if I have a memory leak?
1
vote
0answers
37 views

Query doesn't respond after specific data timestamp [closed]

I am doing SSRS reports which include multiple queries. Most of the time I am selecting from a view which has three columns: Tag Timestamp Value This view retrieves data from multiple tables ...
1
vote
1answer
37 views

Differential backups are 2/3 the size of the full backup

I was investigating why my differential backups were taking almost as long as my full backups and I noticed that the size of the first differential backup after the daily full backup was 2/3 the size ...
0
votes
0answers
38 views

SQL Server 2008 R2: Could not connect to TCP error code 10061

I am having the problem that I'm simply not able to connect to a running SQL Server using Microsoft SQL Server Management Studio. Every time I try to connect to the server I am getting: Could ...
1
vote
1answer
38 views

Does @@FETCH_STATUS have (or need) an equivalent to SCOPE_IDENTITY?

MSDN advises that @@FETCH_STATUS be used with care because it's global: Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully. After a FETCH statement is ...
2
votes
1answer
39 views

Query XML without namespace

I can query XML, which has defined xmlns tag with common table expression DECLARE @XML XML SET @XML = '<NodeA xmlns="https://XYZ.xsd"> <NodeB verzePis=""> <NodeC1 attrA="Hello" ...
5
votes
1answer
176 views

Cost Threshold for Parallelism with Maxdop = 1

Been debating with my company's app vendor regarding parallelism. They have maintained that Maxdop = 1 has significant performance improvements for the app/db but they have provided absolutely no ...
1
vote
2answers
41 views

Need Script to find SP Invoked in SQL

I'm unable to track a stored procedure that is executing every morning 7 AM. Is there is any script to check that which job invokes that particular SP?
0
votes
0answers
32 views

Changing MaximumErrorCount when executing maintenance plan

I have defined a Maintenance Plan in Microsoft SQL Server 2012. It doesn't execute and it says DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) ...
1
vote
1answer
33 views

Full text search error: Invalid locale ID was specified

Query involving full text search is generating the following error message after restoring the database to a new server: Invalid locale ID was specified. Please verify that the locale ID is ...
3
votes
2answers
86 views

SQL Server 2008 R2 returning wrong record

I have a SQL Server 2008 R2 hosting a database with which my software works through the ODBC API. The database has a table, called Users, which has a number of columns, but there are 3 columns (3 ...
0
votes
0answers
12 views

Enabling the use of OPENROWSET in Sql Server [duplicate]

I am trying to make my DEV Server allow the use of OPENROWSET. Though I can use it on my production but not in my DEV. I cannot use the Bulk insert because the excel file is dynamic (Column changes ...
0
votes
0answers
27 views

SQLAgent Job Step- Database field blank, but not

In looking at a historical SQL Agent job on my employers server I noted a job with 4 steps. The Databasefield was set as master for the step 1 and not set at all for the other 3 steps. Yet each of ...
0
votes
0answers
69 views

Equalize datafile size

I have a DB (Microsoft SQL server 2008 R2 (10.50.1600.1)) that has 28 datafiles with all different filesize. Some are 10GB, some 15GB, some 27 GB, etc. The difference in filesize is a historical fact. ...
3
votes
1answer
61 views

SSMS: Import from flat file (.csv)

In SQL Server Management Studio (SSMS) 2008 R2, I wish to import a not-quite-properly formatted CSV into a database table. I was trying to use the wizard at <database name>->Tasks->Import Data. ...
0
votes
1answer
33 views

Partitioned view with dedicated “routing” column

I have an application (Microsoft Dynamics NAV to be exact) that splits up data for different companies in tables with the exact same structure. I can not change that behavior, so I built VIEWs that ...
3
votes
1answer
54 views

Howto query xml

My source data in a SQL Server table has XML structured data. For each of the source rows I need to query the following sample XML structure in a way that I get a calculated value of all A sub ...
0
votes
1answer
24 views

Perform Sum In Update Statement [closed]

I am trying to run this update statement, but it keeps giving me the error of (3 row(s) affected) Msg 157, Level 15, State 1, Line 9 An aggregate may not appear in the set list of an UPDATE ...
2
votes
1answer
25 views

Getting Dates In Range Into Table

I have a calendar table that shows all dates from 01/01/2015 to 12/31/2016, essentially the calendar shows the date and isworkday. I need to be able to pass a begindate and enddate to a stored ...
4
votes
1answer
53 views

SQL Server 2008 R2 services not coming online

I am working for a critical account and yesterday I got a weird issue after applying cumulative update package 14 on top of SQL Server 2008 R2 Standard edition. Before applying CU-14 package on the ...
1
vote
2answers
78 views

Query to send mail to multiple users with their corresponding tickets

I have a table which has a list of ticket owners and and tickets which are assigned to them which are of high priority. For example: I need to send mail from SQL Server using sp_send_dbmail to ...
5
votes
1answer
47 views

How to change default port for T-SQL Debugger

The default port for T-SQL Debugger is 135, it is possible to change this port ? In my company this port is on special list of banned ports, therefore we need to open that service on another port ...
0
votes
1answer
38 views

Require to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2

I need to send an e-mail from SQL Server in the below format, as requested by the client: Dear colleague, See attached file with your current list of high priority ticket notifications. Following ...
3
votes
2answers
225 views

Stored procedure returns -4 after catching exception

In a stored procedure, I am finding that if it successfully inserts a record, it returns 0. If it fails on a primary key violation (which is caught and ignored), it returns -4. Where is the -4 coming ...
5
votes
1answer
52 views

Why isn't transaction log free space always reused?

After monitoring the Transaction log files for a while I noticed that some of them sometimes behave in a way I did not expect. Here are two samples for comparison. All data is based on EXEC('DBCC ...
3
votes
1answer
35 views

Redefine Transaction log size restricion fails

I am trying to set the transaction log's max size restriction to UNRESTRICTED using SSMS using the following dialogue: The actual setting is set to 2.097.152 MB - a nonsense value that exceeds the ...
3
votes
1answer
143 views

Aggregate data from multiple rows into single row

In my table I have rows, and each row has some data and its version. When column is not-null, it overrides an old value otherwise nothing happens. Pseudocode (in C#) var resultTable = new ...
3
votes
1answer
41 views

Keeping service up while taking drive offline in clustered instance

I have clustered instance with two (2) data drives on it. Drive J has around 50 databases on it and drive K has about 15. One of the system administrators was trying to increase the size on K by 20 GB ...
0
votes
0answers
40 views

SQL Server doesn't send intermediate SSL certificates

I am connecting to SQL Server (2008 R2) over open Internet and properly encrypting the connection is essential. I have COMODO certificate with two intermediate certificates between root CA and server ...
0
votes
2answers
39 views

SSDT table column order

I realized that the behaviour of SQLPACKAGE.EXE changes depending on what changes have been made to a table schema: Adding a new column at the end of the table: "Table is changed" Removing a column ...
0
votes
1answer
27 views

Howto remind and restore recovery mode during SSDT deployment?

Due to deployment of a SSDT database project I would like to be able to switch the target database's recovery mode from FULL to BULK LOGGED and back after the deployment finished in order to do any ...
3
votes
0answers
68 views

LOG BACKUP is TRUNCATING in Log Shipping

I had configured Log shipping from INSTANCE1 to INSTANCE2. For the past few days, am able to find that the log backup is truncating only at the midnight. Due to this, LSRestore Job in INSTANCE2 got ...
3
votes
1answer
71 views

Where clause from OUTER APPLY using LEFT function

What would be a good alternative to improve my where clause that currently uses the LEFT function? Should I create a persisted computed column and an index to it? SELECT --columns-- FROM ...
6
votes
1answer
102 views

How to solve blocking on tempdb

I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2). Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all ...
1
vote
0answers
44 views

Why does the first query scan and the second seek?

Please could somebody explain why the first query below scans on every JOIN to the REFERENCE_VALUES table but the second query seeks on the same JOINs. The first query does have other JOINs but I ...
7
votes
3answers
435 views

Risks involved in upgrading to SQL Server 2008 R2

We have quite a number of SQL Servers that need to be upgraded from version 2005 to 2008 R2. Work is planned before the middle of the year as Microsoft is ending its support for the same. The 2005 ...
1
vote
0answers
53 views

Query with join to a table on a different database need an index

I'm creating a query that one of its joins is made to a table from a different database. The query analyzer is suggesting the creation of an index, however I don't have permission to add objects to ...
2
votes
1answer
26 views

Migrating log files should i copy file/directory permissions?

Im planning to move the logfiles for the following databases ReportServer, ReportServerTempDB, tempDB, msdb to a new drive. But it seems that permissions aren´t copied with the directories and files, ...
5
votes
2answers
84 views

How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?

Recently, I was asked to refresh a couple of databases on a test server from production. Always wanting to follow best practices, I performed the backup using the WITH CHECKSUM option, and eventually ...
36
votes
5answers
4k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
2
votes
2answers
209 views

Use Distinct With Sum?

I want to sum a few values using case statements. My problem is compiler complains if I do not add a group by to the end of the query, which then that negates my Distinct, hah. For example, in my ...
4
votes
1answer
92 views

Why are transactions running as distributed (DTCXact)?

I am actually trying to analyze and solve some regularly occurring deadlocks that all include at least one transaction named "DTCXact", this makes me ask. I repetitively run the following query ...
2
votes
2answers
91 views

DBCC Usage on production system

We have a system running SQL Server 2008 R2 on a Hyper-V VM (64GB RAM and 16 virtual cores). Another VM (the application server) within the system accesses the db server quite intensively for both ...
-2
votes
1answer
57 views

Can Log Shipping be recovered and resumed by scripts after DR?

Server A has DB1 log shipping to Server B as DB1_LS in a DR environment. Before the company DR exercise, I scripted out the log shipping configuration from DB1 properties window so I basically got one ...
4
votes
1answer
77 views

Why is my @@Version unchanged after upgrading?

I have SQL Server 2008 R2. I installed SQL Server 2014 with the option: Upgrade from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012 and the process ended correctly. But ...
5
votes
3answers
587 views

Who is the person using a SQL login?

Is it possible to find out who used a SQL-Server login? Let's assume we suspect a person to abuse a SQL login that is supposed to be used by an application only to logon to SQL-Server. Is the domain ...
1
vote
2answers
193 views

Stored procedure working when run manually, not running in SQL Agent Job

When I execute a Stored Procedure through a SQL Agent Job, I get the following error message: Executed as user: NT AUTHORITY\SYSTEM. XML parsing: line 10, character 33, unexpected end of ...
10
votes
1answer
171 views

Does CREATE TABLE SomeSchema.#TempTableName have a bug?

Simple test-bed: USE tempdb; GO /* This DROP TABLE should not be necessary, since the DROP SCHEMA should drop the table if it is contained within the schema, as I'd expect it to be. */ ...