Questions tagged [sql-server-2008-r2]

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

347 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
2answers
37k views

The INSERT/UPDATE/DELETE permission was denied on the object 'TheTable', database 'TheDb', schema 'dbo'

I have two SQL Server user accounts on a database that behave differently on INSERT / UPDATE / DELETE. SQL Server version is 2008 R2 (10.50.4042, 64 bits). UserA can do these commands (on any table). ...
7
votes
1answer
1k 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 ...
6
votes
0answers
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 ...
4
votes
1answer
65 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 ...
4
votes
0answers
388 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 ...
4
votes
0answers
1k 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 ...
4
votes
1answer
159 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 ...
3
votes
1answer
56 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 -- + ----- + ---- + ----- + ---- ...
3
votes
0answers
75 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 ...
3
votes
0answers
264 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 ...
3
votes
0answers
536 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 ...
3
votes
0answers
189 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
0answers
623 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 ...
3
votes
0answers
317 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 ...
3
votes
0answers
1k 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-...
3
votes
0answers
79 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 ...
3
votes
0answers
294 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 ...
3
votes
0answers
1k 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',...
3
votes
0answers
375 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 ...
3
votes
0answers
283 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 ...
3
votes
0answers
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 ...
3
votes
0answers
306 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
0answers
345 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 ...
3
votes
0answers
224 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 ...
3
votes
0answers
270 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 ...
3
votes
0answers
781 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'...
3
votes
2answers
399 views

Cross database reference select access with disabled login for DB user

We have two databases (DB1, DB2) with the following view structure. DB1: T1, T2, T3, ..... Tn (Tables) V1 (View consuming {T1, T2, T3 ..... Tn }) Inside DB2 we have to create a view V2 which will ...
3
votes
1answer
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 ,...
3
votes
1answer
47 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 ...
3
votes
2answers
77 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 ...
3
votes
1answer
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....
2
votes
0answers
385 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 ...
2
votes
2answers
147 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 ...
2
votes
2answers
2k 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 ...
2
votes
0answers
48 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 ...
2
votes
0answers
295 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:\...
2
votes
0answers
177 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 ...
2
votes
0answers
82 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
0answers
252 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 ...
2
votes
0answers
7k 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
0answers
662 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 ...
2
votes
0answers
170 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
votes
0answers
204 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. ...
2
votes
0answers
221 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 ...
2
votes
0answers
288 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
0answers
159 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-...
2
votes
0answers
108 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 ...
2
votes
0answers
727 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 ...
2
votes
1answer
250 views

Identity management in Transaction replication with updatable subscription

I have migrated my publisher DB to new server yesterday. For migration what I did was removed subscriptions and publication completely backed up DB on which publication was configured restored DB to ...
2
votes
0answers
157 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
2 3 4 5
7