Questions tagged [sql-server-2008-r2]
SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.
304
questions with no upvoted or accepted answers
9
votes
1
answer
2k
views
Change Tracking across several Tables
I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables.
Right now, we have triggers on each of ...
- 433
6
votes
0
answers
1k
views
sp_send_dbmail causes session to hang
We had an issue in production (2008R2 SP3) yesterday, where a developer was running a piece of code in SSMS that was executing sp_send_dbmail inside a transaction, and including an attachment from a ...
- 16.1k
4
votes
1
answer
75
views
How to increase performance in Cross Database operations?
We currently have a SQL Server 2008R2 being accessed by a legacy ASP application.
Among different processes, one of them essentially SELECTs data from one database and INSERTs (or UPDATEs) them into ...
- 143
4
votes
0
answers
403
views
How to copy SQL Server log shipping files to AWS EC2 instance
I am using SQL Server 2008 R2 and would like to log ship from my local data center SQL Server instance to a log shipping target SQL Server running in AWS EC2.
What is the best way to copy the log ...
- 141
4
votes
0
answers
826
views
Allow developers to run database maintenance
We use the database solution by Ola Hallengren. We're trying to give our developers a way to run the maintenance stored procedures after they do an ETL. Our developers have locked down permissions. We'...
- 705
4
votes
0
answers
2k
views
Auditing access to a linked server
I have a linked server with SQL Server configured with the security option "For a login not defined in the above list, connections will: Be made using the login's current security context".
What I ...
- 1,434
4
votes
1
answer
264
views
CONVERT_IMPLICIT when inserting a Geography datatype into a Geography datatype column on Temp Table
@@Version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)
If I try to insert data into a geography datatype in a temp table I get a CONVERT_IMPLICIT, converting a Geography datatype to a ...
- 793
4
votes
1
answer
401
views
Read/Write Deadlock in SQL Server
We are facing deadlocks when a stored procedure is concurrently executed. We are using Microsoft SQL Server 2008 R2, concurrent execution is done with java executing multiple request of the stored ...
- 41
3
votes
1
answer
64
views
Ensuring Uniqueness Across Columns
I'll lead with this: Perhaps the database design is wrong, so I will accept a re-design as a solution!
Consider the following table:
ID | TypeA | RefA | TypeB | RefB
-- + ----- + ---- + ----- + ----
...
- 437
3
votes
0
answers
79
views
database restore with filestream on Windows Server 2016 renames data file
I have a database in SQL Server 2008 R2 sp2 that has filestream enabled. Restore of the database on a Win10 machine works as it should.
However, on Windows Server 2016, the data directory (file) for ...
- 31
3
votes
0
answers
407
views
How do I change a processor count on a stopped SQL Server instance?
I have restored an image of a production SQL Server 2008 R2 to a Virtual Machine to test out some changes.
When I try to start SQL Server to recreate tempdb (in partition that was not restored), it ...
- 31
3
votes
0
answers
701
views
Merge replication subscriptions are repeating the same actions endlessly
We have a merge publication set up on SQL Server 2008R2, with several subscribers. Synchronisation is done through the internet.
Over the last few days, some subscribers are unable to synchronise.
In ...
- 245
3
votes
0
answers
202
views
Slowly changing dimensions - data integrity
When using SCD is it possible to still enforce FK contraints. Consider the following table structure:
customer (data_version_id PK, customer_id PK, name, address1,...etc)
order(order_id PK, ...
3
votes
0
answers
679
views
SQL Server Management Studio creates 18456 login error using registered server group
I have been using "Local Server Groups" registration for quite a while now with SQL Server 2008 Management Studio.
I have created a group with 5 registered servers. They are registered using ...
- 4,757
3
votes
0
answers
2k
views
"The schema script could not be propagated to the subscriber" error has SQL code instead of file name
This type of error is unfortunately not uncommon. One report of many is this one on Stack Overflow: https://stackoverflow.com/questions/11360331/sql-server-merge-replication-error-the-schema-script-...
- 245
3
votes
0
answers
103
views
why some one will use approximate data types for sales
I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
- 31
3
votes
0
answers
372
views
VSS Writer : RESTORE VERIFYONLY FROM VIRTUAL_DEVICE breaks log shipping
Log shipping has been working for a very long time, but now and then it breaks. I found it sometimes breaks (causes the transaction log to become corrupt and I get the message : "media family on ...
- 1,365
3
votes
0
answers
2k
views
SQL Server replication - error when creating a subscription - The article '(null)' does not exist
When trying to add a subscription to an existing publication
use [Product]
exec sp_addsubscription @publication = N'Product',
@subscriber = N'REPLON1',
@destination_db = N'Product',...
- 15.8k
3
votes
0
answers
449
views
SQL Job Step created via wizard does not correctly set server & database_name values
I've recently been tasked with migrating a database from SQL Server 2005 to 2008R2. This has all been accomplished without any issues, but one part of the system requires a Snapshot Replication to be ...
- 31
3
votes
0
answers
292
views
Is it possible to have different authentication mode for SQL Server Database Engine and corresponding SQL Server instance?
I have installed the x64 SQL Server 2008 R2 Express with default settings and run MBSA 2.3 Microsoft analizer (using default settings too). Tried on clean Windows 8 installation (not 8.1) and Windows ...
- 31
3
votes
0
answers
9k
views
SQL Server 2008 R2 Service pack 3 won't recognize all instances
I have two instances on one of my servers, and when installing service pack 3 the GUI installer does not recognize one of the instances. Server is Windows Server 2008R2 Enterprise x64 SP1
Both ...
- 1,538
3
votes
0
answers
311
views
Unable to Update on SQL Server 2008 R2
This is a bit strange so bear with me please.
After installing a new SQL Server 2008 R2 on a new server (Windows 2012 Standard) just like the production one, I tried to connect my web aplication (...
3
votes
0
answers
416
views
Using Import Wizard with Access - No null values
I am trying to import a table from Access to SSMS using the Import Wizard but when I do the resulting table has a lot of empty strings instead of the NULL values that I am expecting.
How do I make ...
- 31
3
votes
0
answers
247
views
SQL Server replication - recommendations for stability
We have two SQL Server 2008R2 Standard servers, and we've set up replication of a single database between them. The replication is about as simple as it can get - it's a transactional replication from ...
- 303
3
votes
0
answers
283
views
Find Most active databases that can be offloaded from a busy server
I am in the process of identifying the most active databases on the server and will be moving the databases off the current server to alleviate the load.
The reason to do it is that our servers are ...
- 62.1k
3
votes
1
answer
5k
views
How can we improve this PIVOT operation? ( SQL Server 2008 R2 )
We have this query, that runs in 1:10. We need to improve this. This is just a test query, but we will implement this upgrade on our real query.
select top 100 *
from (
select codCliente
,...
- 7,374
3
votes
1
answer
54
views
remove a paritition table
I need to remove a partition table for an application upgrade. It is SQL Server 2008 R2.
The partition column is on column col1 data type smallint, null. Col1 has length 10 and precision 5. As you ...
- 31
3
votes
2
answers
107
views
Concerns regarding changing virtual NIC on SQL Server 2008 R2 / Windows Server 2008 R2
I have SQL Server 2008 R2 running on Windows Server 2008 R2 in a virtual environment (ESXI 5.5). Client has been advised to switch the existing E1000 adapter with a VMXNET 3 adapter because they were ...
- 35
3
votes
1
answer
1k
views
Remote access to SQL Server 2008 R2 on Windows Server 2012 R2?
I have SQL Server 2008 R2 (named instance) on a Windows Server 2012 R2:
Enabled remote access to SQL Server
Enabled shared memory & TCP/IP protocols & named pipes for the SQL Server instance....
- 139
2
votes
0
answers
475
views
How do I fix the Failed to retrieve data for attaching mdf file is which not primary database file
I'm using SQL Server 2019 in Windows 10 Enterprise and Administrator user
I have an MDF file and I want to attach it, and it has full access in the form of Everyone Full Control , when I try to attach ...
- 33
2
votes
0
answers
426
views
SSIS: How to export more than 4000 characters to Excel
I have the following basic setup in SQL 2008 R2. This does not work however as I always get the errors
Error 1 Validation error. SQL to XLS: SQL to XLS: Columns "Original" and "...
- 194
2
votes
0
answers
960
views
SQL Server: Read Only replica of production server
I have a production database which is being used by my primary application. Another department has created a new application for which I need to provide subset of my data from my database. The new ...
- 21
2
votes
2
answers
227
views
Log error restoring old database to SQL Server 2017
I am trying to migrate a database from an old SQL Server 2008 R2 to SQL Server 2017. The database was originally 2000 and thus has a compatibility level of 80. Initially I took a full database backup ...
- 21
2
votes
2
answers
4k
views
How to bulk convert ISO8859 text in Text columns to UTF8 in nvarchar(max) columns?
I've been struggling for days with a problem on a MS SQL database. Here is the situation: I have an old Open Source PHP application (Limesurvey version 1.92) running on a Windows 2008R2 server, with ...
- 21
2
votes
0
answers
51
views
TEMP Database is not showing Encrypted in TDE in SQL Server 2008R2
As per my knowledge, when we encrypt any user database in SQL Server through TDE, the TEMP database automatically gets encrypted.
I have done TDE in SQL Server 2016, and when I check through SELECT ...
- 397
2
votes
0
answers
506
views
fn_trace_gettable empty default trace rollover files
For some time now I've been using fn_trace_gettable to automatically collect and report certain events on the server.
Function is being called with following parameters:
::fn_trace_gettable('C:\...
- 405
2
votes
0
answers
239
views
why is update from a join containing zero records taking a minute and 20 seconds?
SQL Server 2008R2. I have a simple join that returns zero rows in less than a second. When I try to run an update on that join, it should also take less than a second but it is taking one minute and ...
- 131
2
votes
0
answers
83
views
SQL Sever contains not finding specifics values
In my database i have a column named index_description_column in the table products, and i've a record with "2205607 GRAMPO FIX. ROLDANA 7/8" (1X9)" data.
When i do the following query it brings de ...
2
votes
0
answers
258
views
Unexplainable behavior of SQL Server under Windows Authentication
The versions affected are SQL Server 2005, 2008, 2008 R2.
If we grant CONNECT SQL to public server role, any AD account can log in to server.
So we'll have all the AD members on the server without ...
- 10.6k
2
votes
0
answers
9k
views
BCP Error SQLState = S1000, NativeError = 0 , Unable to open BCP host data-file
I am trying to execute the below BCP query in my TEST Server, where BCP has a connection string to the production DB, so my destination folder is in my Test Server and I am getting an error, I think ...
2
votes
0
answers
722
views
Extended Events not displaying today's deadlock information
I am using the following query to retrieve deadlock xml.
select create_time,XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph
FROM
(select s.create_time,CAST(target_data as ...
- 591
2
votes
0
answers
992
views
Stuck ghost cleanup
Looking for a little advice. This is all on 2008 R2 SP1.
I have a large table (around half a terabyte) that a ghost cleanup is holding locks on, more specifically exclusive locks on two pages, which ...
- 21
2
votes
0
answers
199
views
Alternate of TimeStamp for row versioning and Peer to Peer Replication
Scenario: As per our findings the table cannot be used in peer to peer replication if it contains column with datatype timestamp.
MSDN:https://msdn.microsoft.com/en-us/library/ms151196(v=sql.105)....
- 2,895
2
votes
1
answer
1k
views
"Login failed for user..." with cross-instance access on same server in SSIS package
I have 2 SQL Server instances running on the same server.
Lets call the instances Instance_A and Instance_B and the server Svr0045.
Both server use the same service accounts (a service account is ...
2
votes
0
answers
228
views
SQL Server 2008R2 Linked Server to SQL Server 2000 showing incorrect server
I have 2 servers and 4 instances
Server 1 (SQL Server 2008R2) has instance a,b,c
Server 2 (SQL Server 2000) has instance d
I created a linked server from server 1 instance c to server 2 instance d. ...
- 121
2
votes
0
answers
266
views
SQL Server Service Broker with DML triggers
What I want to do:
Client database (C) is SQL Server 2008, has schema X
My database (D) is PostgreSQL 9.1, has schema Y
I want to transfer data from specific tables in C to D in near real time.
My ...
- 121
2
votes
0
answers
316
views
SQL SERVER 2008 R2 - Merge Replication - Identity Seed issue
The server I am running has identity Management set to automatic for most of the tables I am replicating (A hand full have been set to manual). When the replication goes to change the range it ...
2
votes
0
answers
165
views
How to fix the relationship between MSSQL database and associated FILESTREAM files
TL;DR: How can I fix the relationship between an MSSQL database and the associated FILESTREAM files for a database that was recovered from failed disks?
Background
A server hosting a FILESTREAM-...
- 121
2
votes
0
answers
109
views
Select Query with S lock wins on Insert with IX lock in deadlock
Select Query with S lock wins on Insert with IX lock in deadlock in Sql Server 2008R2.
Attaching the deadlock xml file.Please help!
http://pastebin.com/nY9EXbS8
This is from the error log by ...
- 89
2
votes
0
answers
827
views
Does HOBT shared lock prevent writes
I am attempting to execute long running reads against a table which is also the target of insert bulk queries from sqlbulkcopy. In order to achieve this I have added the with(nolock) to the query ...
- 121