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

learn more… | top users | synonyms (2)

1
vote
2answers
15 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
44 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 ...
3
votes
0answers
21 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
11 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 ...
1
vote
1answer
573 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
3
votes
2answers
202 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 ...
8
votes
5answers
15k views

Add article to transactional publication without generating new snapshot

Using SQL 2008 R2 transactional replication with pull subscribers, when we add an article, I'd like to avoid having to create an entire snapshot (the db is ~80 GB, so this takes hours). From this ...
1
vote
1answer
733 views

SQL Server connection failed for domain user from Mac OS machine

I have SQL Server 2008 R2 SP1. I am using connection string to connect to SQL Server. In connection string I am passing the driver path, server name, TCP port number, user name with which I want to ...
14
votes
1answer
598 views

Excessive compile blocking on sp_procedure_params_90_rowset

A resurgence of this questionon MSDN: Blocked-process-report: what is this waitresource "OBJECT: 32767:124607697:0 [COMPILE]" I have caught these statements in Profiler. They all have durations over ...
3
votes
1answer
27 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
70 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 ...
9
votes
1answer
129 views

How to reproduce “Could not continue scan with NOLOCK due to data movement”

I occasionally get "Could not continue scan with NOLOCK due to data movement" with some large jobs, which do have WITH (NOLOCK) on the select queries. I understand this has something to do with ...
2
votes
0answers
25 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 ...
1
vote
2answers
58 views

Cannot generate SSPI Context

When I try to connect to a SQL Server from my local machine I get the following error: Cannot connect to Computer1\Instance1. The target principal name is incorrect. Cannot generate SSPI ...
0
votes
1answer
111 views

SSRS CROSS Domain Reports Server Authentication

I have a pretty complicated situation at hand, let me try to explain. I have a SQL Server and a Web Server in a separate domain (lets call it domain A). SQL Server has the databases for Reporting ...
2
votes
3answers
22k views

OpenRowSet - How to configure OLE DB Provider to be used for for distributed queries

I have an Excel file with data and would like to update a table in database based on the data the Excel file contains. To do that I want to use OpenRowSet command. But I get the error below when even ...
6
votes
0answers
101 views

DBCC Check DB issue

I have database of 99GB on this database when I am running DBCC Checkdb('IssueDB') It gives error as: Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while ...
7
votes
3answers
740 views

Property Size is not available for database

I recently restored a database to the same instance it was backed up from (SQL Server 2008 R2 Enterprise) and found that I couldn't access the database properties. I have done the following: ...
2
votes
1answer
37 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
14 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 ...
2
votes
2answers
542 views

Duplicate login details per user in error log

Being able to view details of who successfully logs into a database and which user's login failed via the error logs is a good thing. However I have a scenario that I need help with: For some reason ...
5
votes
1answer
86 views

Monitor table permission changes

I need a solution to monitor table permission changes: how can I implement monitoring of every changes to granted permissions for a specific or any user on a specific table in SQL Server 2008 R2?
0
votes
2answers
21 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 ...
1
vote
0answers
371 views

SQL Server 2008 R2 Cluster Service pack 3 fail to failover node

I have a SQL Server R2 cluster on Windows 2008 R2 boxes, and today i was to install SP3. I installed it without any errors and rebooted the passive node. When i then try to failover to the passive ...
1
vote
1answer
531 views

SQL Server 2008 R2, error creating file for FileStream

I'm trying to create a file to add to a filestream file group and I get the error: Msg 5120, Level 16, State 106, Line 1 Unable to open the physical file "d:\data\filestreamstore.mdf". Operating ...
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 ...
2
votes
3answers
30 views

Suddenly cannot access SQL Server database

My SQL Server 2008 R2 database suddenly cannot be accessed. When I try to view it in SQL Server Management Studio, it appears something like below: It cannot be detached and attached. I need to ...
3
votes
0answers
50 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 ...
1
vote
2answers
90 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 ...
3
votes
1answer
197 views

Cannot reinstall SQL Server 2008 R2 after downgrade from Windows 10 to Windows 7

Scenario: using SQL Server 2008 R2 Express under Windows 7 without problems. Then I upgraded to Windows 10 without problems. Then I downgraded back to Windows 7 (due to problems with other ...
0
votes
1answer
3k views

SQL Server 2008 R2 Drops Connections when Anti-Virus Scan is running

I have a SQL Server 2008 R2 Enterprise (SP2) cluster (2 nodes Windows Server 2008 R2 SP1, NetApp Shared storage). There are 5 instances all running on this cluster in an active passive configuration ...
3
votes
1answer
67 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 ...
4
votes
0answers
62 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 ...
0
votes
0answers
41 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 ...
4
votes
1answer
46 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 ...
2
votes
2answers
626 views

SQL Server 2008 R2 Database Diagram Error Code

I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error ...
17
votes
5answers
4k views

SQL Server compatibility with New TLS Standards

The major browsers are moving beyond SSL3.0 and TLS1.0 . The PCI Security Council has declared an end-of-life date for these protocols to be considered sufficiently strong encryption. We need to ...
32
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 ...
0
votes
2answers
226 views

Analysis Services not allowing other/remote connections other than localhost

Analysis services not allowing any connections except localhost. Authentication box is disabled and only allow windows authentication. How can I connect to different remote analysis servers using ...
9
votes
1answer
357 views

How to properly troubleshoot Merge Replication False Conflicts

We have SQL Server 2008 R2 Merge Replication setup. Lately I started getting conflicts for some tables, and when I check the conflict viewer, I can see that the winner & the loser column values ...
4
votes
2answers
70 views

Does a covering nonclustered index help to answer Select * queries?

Our application developers run a lot of SELECT * queries against our database system, unfortunately. The missing index suggestions of sys.dm_db_missing_index contain a lot of cases that indicate to ...
4
votes
2answers
2k views

Is Information in sys.dm_db_index_usage_stats reliable

I'm archiving data from an old system that has no documentation. Lucky me... I'd like to find out when the tables were created, last accessed etc. Can I trust that this query will give me the correct ...
14
votes
4answers
12k views

SQL server service won’t start after disabling TLS 1.0 and SSL 3.0 [duplicate]

Because of security reasons, we disabled TLS 1.0 and older protocols on our windows, and enabled just TLS 1.1 and TLS 1.2 under the following registry path: ...
7
votes
3answers
356 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 ...
5
votes
2answers
66 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 ...
9
votes
3answers
12k views

SQL Server not starting at Windows startup but starts manually

My SQL Server instance's (SQL Server 2008 R2 Express) service has Startup Type = Automatic. The past couple of times I've restarted my computer the service has failed to start on its own, but it ...
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, ...
3
votes
1answer
2k views

SQL Server 2008 R2 cluster installation error [closed]

I was trying to install SQL Server Cluster (Launch wiard to install single node SQL Server 2008 R2 faliover cluster) .. The setup checks displayed no error and the installation started after all the ...
-2
votes
1answer
37 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 ...