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

learn more… | top users | synonyms (2)

0
votes
0answers
4 views

Database Server Daily Errors Check script

Could anyone point me to script that I can run daily and report on the previous day's errors and warning coming from the SQL Server error logs including failed jobs and probably also windows event ...
1
vote
1answer
9 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
19 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
23 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 ...
1
vote
1answer
26 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
139 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
24 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
15 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) ...
0
votes
0answers
11 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
49 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
23 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
45 views

Equalize datafile size [on hold]

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. ...
2
votes
1answer
22 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
26 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
36 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
18 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
24 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 ...
-1
votes
0answers
26 views

windows 10 home, 64 bit… trouble running a certain software requiring 2k8r2, would like to run these ideas by you [closed]

I need to run a programming software that requires 2008r2, I cant finish installing the software because it wants to install its own instance of 2k8r2. Can I install sql first then run the software? ...
4
votes
1answer
35 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
54 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 ...
4
votes
0answers
33 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
21 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
214 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 ...
4
votes
0answers
32 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
29 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
80 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
39 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
18 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
23 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
18 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
52 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
68 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 ...
5
votes
0answers
70 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
42 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
368 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
52 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
25 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
72 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 ...
33
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
186 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
49 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
51 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
39 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
63 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 ...
3
votes
3answers
552 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 ...
0
votes
0answers
17 views

Diagnose stalled transaction

I've built a PHP web application for a third-party powered by SQL Server 2008 Web Edition and hosted in a (supposedly) managed server that belongs to client. Application becomes unusable now and then ...
1
vote
2answers
100 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
153 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. */ ...
4
votes
1answer
47 views

SQL Server Memory Determination

We will be modifying a SQL Deployment in our Hosted Environment. We have a solution which is currently configured to use one Application (and not SQL) System Database (which holds Security and System ...