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

learn more… | top users | synonyms (2)

0
votes
1answer
644 views

Unable to re-install an instance using the original instance name

We are trying to uninstall and re-install a 2008R2 instance. We have uninstalled the database engine but when we tried to re-install it we got the error "Original instance id\name in use". The ...
1
vote
2answers
531 views

DB Locks - How would one handle long transactions and maintain high concurrency?

We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle ...
1
vote
1answer
882 views

Can I alter a view being referenced with schemabinding if the change fulfills the binding requirement?

In SQL Server 2008, I have views WITH SCHEMABINDING and I need to alter one. I am changing a column while not changing its alias so consumers will be unaffected. If I were to drop all my other views ...
1
vote
0answers
475 views

Timeout while using table valued parameter

I'm trying to find the cause for this error while calling a stored proc with a table valued parameter: Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target ...
3
votes
1answer
63 views

Single statement or batched delete?

I need to delete 6.3 million records from 25 tables in production. I have the following two queries, but I don't know which one will be best: delete jc from Jtable jc join J1Table j on j.JobId = jc....
3
votes
2answers
49 views

Is my Publisher database my actual source database or is it a new database?

I am setting up transactional replication for the first time and am working through my backup policies I need to set. Is my Publisher database my actual source database? Or is it a new database that ...
1
vote
1answer
86 views

Return value formatted similar to if casted as money

My client's system is very outdated, and there is nothing I can do about it. What I'm trying to achieve is to extract values always with at least 2 decimal places. E.g. From 1826.5695600000 ...
1
vote
3answers
2k views

Can SQL Agent job notifications be sent to one operator for success and another for failure?

We have a requirement that some people are sent notification on success, and other on failure. But the UI seems to only allow a combination of 1 operator + 1 condition. So I was wondering if we ...
8
votes
2answers
583 views

Should I nest dependent outer joins in SQL Server?

I have heard mixed information on this and am hoping for a canonical or expert opinion. If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them? For a contrived ...
3
votes
1answer
374 views

Turn Off Alerts

We have a SQL Server 2008 R2 database instance running on a VM. Nightly, between 10p and 12a, we are getting the following error message: DATE/TIME: 2/3/2014 10:16:37 PM DESCRIPTION: Database ...
2
votes
1answer
293 views

Copy FileGroup to Another FileGroup

we run Microsoft SQL Server 2008 R2 on production server. We need create new tables and load a big amount of data on them (roughly 1 TB!) I am wondering if it is possible to insert this dataset on ...
1
vote
1answer
576 views

Linked Server - login trying to access database that is not default

Trying to create a linked server from server A (2005) to B (2008 R2). Everything worked when created. The login on server B was mapped to two databases: DataBase1 and Database2. Database1 was the ...
6
votes
3answers
2k views

Cube processing either takes too long or fails

I have a SSAS Cube with 35 dimensions and 10 measures. Some of the dimensions are quite large in size. In almost all dimensions data either get updated and inserted. Measures have high volume of ...
5
votes
2answers
2k views

if-then-else gives a boolean headache

I have three statements to combine but I cannot find the correct solution for SQL Server 2008 R2: I need to concatenate three columns into one but in three different ways depending on the result in ...
3
votes
1answer
2k views

sql server service broker queue pass parameter to stored procedure

Stored Procedure Create PROCEDURE [dbo].[InitServiceLog] ( @name varchar(max)=null ) AS BEGIN SET NOCOUNT ON; insert into [AdventureWorks].[dbo].[ServiceBroker_Log](Service) values(@name)...
0
votes
1answer
54 views

SQL Server 2008 R2 Resource Managment

I have a server running Windows Server 2008 R2 Enterprise 64Bit, with 256 GB RAM. I am running SQL Server 2008 R2. With normal usage, SQL Server is using about 232 GB of RAM. Occasionally it will ...
3
votes
1answer
204 views

Jobs for cycling error logs

Couple of weeks ago I asked this question Error logs various questions and got the answer I needed. Based on the answer and some readings I want to create the following jobs: cycle the error log ...
1
vote
1answer
53 views

Rebuild the Fulltextcatalog

I have a Database (80GB) that is able to store attachments. These attachments are full text indexed. For some reasons I had to rebuild the fulltext catalog and this 30 hours. Our business users ...
0
votes
0answers
123 views

Replication with no network connectivity

I have SQL Servers on two isolated networks that cannot be connected directly. However, there is a need to keep data in sync between the two systems. Obviously SQL Server replication won't work. It ...
0
votes
1answer
445 views

linked server hanging

We've been using a linked server to an access database for close to a year now with little issue. Now when I run a select query against the linked server the query sits there and never finishes. ...
2
votes
2answers
8k views

Upgrading to SQL Server 2012 from SQL Server 2008

I have upgraded my SQL Server 2008 R2 Enterprise edition to SQL Server 2012. Even after upgrade I can see & use SQL Server 2008 R2 instance without any issue. Did my upgrade went fine? I can ...
2
votes
1answer
2k views

Issue while upgrading SQL Server 2012

When I tried to upgrade SQL Server 2008 R2 to SQL Server 2012 everything went well except below issue How do I fix it? Do I need to uninstall SQL Server 2012 again? EDIT Feature: ...
6
votes
1answer
2k views

How is SQL Server returning both a new value and old value during an UPDATE?

We've had issues, during high concurrency, of queries returning non-sensical results - results the violate the logic of the queries being issued. It took a while to reproduce the issue. I've managed ...
5
votes
1answer
371 views

Numeric data type with variable significant digits

I need to implement a data type which should preserve the last zeros in the decimal points in SQL Server 2008 R2. Example of the data are 1.002 1.210 0.00100 0.1401 My clients get lab measurements ...
1
vote
1answer
6k views

Cannot Start SQL Server Reporting Service [closed]

That's the first time I use SSRS so my apologies if I'm asking something stupid. I have a SQL Server 2008 R2 already installed with SSRS. When I try to start the SQL Server Reporting Service I ...
4
votes
1answer
154 views

In READ COMMITTED isolation, is it possible to read the old value after the update?

I'm trying to solve an issue with SQL Server 2008 R2 and am grasping at straws. Given that data is housed in leaf-nodes of the B-tree, and is also exists in indexes, that someone could read multiple ...
3
votes
2answers
57 views

Calulating savings between two schemes

I've normalized a single table to reduce the number of nullable columns from 3 down to 0. The idea was to save a little drive space but I'm seeing a 45% decrease in space needed to store the same ...
1
vote
2answers
105 views

SSIS via Import Wizard SQL Server 2008 R2

I have a flat file i need to import into SQL. For a lot of boring reasons, I curently don't have access to use BIDS, only the import wizard in SSMS. The flat file looks sort of like: A 2 B ...
1
vote
1answer
664 views

Use @@servername and db_name() on notify operator task

I'm setting up a maintenance plan and on error I added a "Notify operator task" with some text. But I want to be able to add something like: Backup failed on MyDatabase\Instance_name...blablabla ...
2
votes
1answer
3k views

Could I realistically use SQL Server Express as a distributor database for transactional replication?

We have a group of users within my company that are running reports directly against our production database. It has gotten to the point where they are hammering it enough to cause performance issues ...
1
vote
1answer
1k views

Does MAXDOP affect the number of tempdb data files I should create?

From what I have read, best practices for configuring tempdb includes creating multiple data files. The rule of thumb as I understand it is if you should create the same number of data files as you ...
1
vote
1answer
30 views

Change A Database Name When Restoring

I'm going to be moving a database from one server to another. Right now the name makes no sense and I'd like to change it. I found a answer here that makes me think it might be relatively simple. My ...
3
votes
2answers
411 views

How can I most closely monitor this table in production to find why rows are disappearing?

Scenario: We write online banking software and for the time being (due to legacy design decisions) are using Quartz 2.2 right now as a queue system to perform a handful of operatoins long term. [Let'...
4
votes
1answer
7k views

SQL Server 2008 R2 Restore Database From File - “failed: 38(failed to retrieve text for this error. Reason: 15105)”

I'm running a database restore on a backup file that was created up on a clients server, the backup file has been moved to our server (same SQL version 2008 R2) and I'm getting the following error ...
3
votes
3answers
2k views

Is there a way to execute sql statement in sql server based on sql server version?

I would like to create computed index on a table if sql server is 2008 or newer and a simple index if sql server is 2005 or older: -- check for sql server version if (select cast(left(cast(...
2
votes
2answers
706 views

Scripting all of available databases to .sql files containing schema+data+users with single command

I have a SQL 2008 Express with 80 databases in it. I want to move these databases completely to another server which is hosting a SQL 2008R2 Enterprise instance. As i'm told it is better to script ...
0
votes
0answers
43 views

unclear SQL Server Issue

I'm attempting to determine root cause for an issue that caused an outage for my company. Essentially the site connections to our database server were timing out after not receiving a response for ...
6
votes
1answer
210 views

2008R2 Cannot Disable Non-Clustered Index

We have identified a handful of superfluous non-clustered indexes and I would like to disable them for a few days before straight dropping them. However when using the GUI in SSMS (right click > ...
1
vote
0answers
118 views

Differential backup job is working but not the Full daily backup

I have a full backup job for my database that runs once daily, I see it posted daily in the folder that it should go to, but when I check the "View History" for this job in SQL Server Agent, there are ...
1
vote
0answers
897 views

SSRS 2008 report fast in test, slow in production: parameter sniffing problem?

We recently had an issue with reports running in an integrated SharePoint environment where the report runs fine in development, test and staging, but very slow (times out) in production. Running ...
2
votes
1answer
63 views

Can we tell dbcc to ignore subnormal real values?

Currently dbcc DATA_PURITY complains about subnormal real values in some of my columns, such as 7.561407E-42. I would rather keep them in my tables, and ask dbcc to ignore them. I am OK with the ...
0
votes
2answers
571 views

Slow insert - 300+ Million Records

The following query takes over an hour on the table to insert. There are no indexes. There was one which was deleted but still takes an hour after deletion. Any pointers on improving this ...
3
votes
1answer
36 views

Single user mode when was configured

I'm trying to find when a database on our instance was configured as SINGLE USER MODE but can't find that information. Either using tsql or the GUI. Maybe it is not possible to know that information? ...
4
votes
1answer
1k views

SQL Server 2008 R2 Restore constantly hanging

I have a SQL Server 2008 R2 SP2 test server that I have to refresh the databases on fairly often, but every time I have to do a restore on, it will hang on me. I use a custom script to check the ...
1
vote
1answer
5k views

Cannot read property Collation.This property is not available on SQL Server 7.0

I'm not able to add administrator role in SQL Server 2008 R2. When I try to add new user it throws me the following error: Cannot read property Collation.This property is not available on SQL ...
6
votes
1answer
25k views

“Database is in transition” Error

Today I was trying to restore a database over an already existing database, I simply right clicked the database in SSMS --> Tasks --> Take Offline so I could restore the database. A small pop up ...
3
votes
1answer
197 views

Searching all Stored Procedures for an update of a specific column

I'm looking at an audit table created by a trigger, and we were smart enough to save the SUSER_NAME(); we have a column called LastUpdateRef, but not all old code has been updated to use it (it's a ...
2
votes
1answer
74 views

Database Design guidance required

I have scenario where there is Table T with columns PK,A, B, C,D TABLE t ( PK, A, B, C, D ) i have to insert data from different sources ie columns A,B,C,D are calculated separately.. and they same ...
2
votes
1answer
150 views

Error Logs various questions

I'm slowly collecting and trying to analyze lot of information on all of our instances (2008R2 SP1). I'm now going through the Error Logs and I've couple of question. There is a lot about error log ...
4
votes
1answer
5k views

Why does SQL Server run a subquery for each row of the table it's qualifying?

This query runs in ~21 seconds (execution plan): select a.month , count(*) from SubqueryTest a where a.year = (select max(b.year) from SubqueryTest b) group by a.month When the subquery ...