This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx

learn more… | top users | synonyms

7
votes
1answer
171 views

Error in SSMS when attempting to restore a database

We have over a dozen SQL servers, one of which hosts a couple of hundred sage databases, when i right-click on the Databases node in SSMS there is a long delay then i get the following error (and the ...
3
votes
1answer
367 views

SQL Server 2008 R2 Express password overwrite

I'm using a program that requires SA log-in to SQL Server. We had issues where some 3rd party support numpty changed the SA password and never informed us, so the program couldn't log-in. I've ...
1
vote
1answer
518 views

Replication Error when adding small table as new article

I have just added a small (200 row) table to my current replication which has now broken my entire replication scenario as it seems to have broken the Log reader. The 2 tables are unrelated. I am ...
1
vote
1answer
828 views

Query to compare 2 table results and filter extra records found in a table

I have two queries that I want to compare and get the extra rows found in in the first query that are not in the second plus 1. I need the DocEntryCN to perform some work. E.g. Query 1 SELECT ...
3
votes
0answers
138 views

Is it useful to have the SQL Server instance root directory on a separate drive? [closed]

I know it's possible to change many of the default paths when installing SQL Server, and generally when I do an install I change the data and log folders to be on separate drives (typically D and E), ...
11
votes
2answers
10k views

Is it useful to have the SQL Server instance root directory on a separate drive?

I know it's possible to change many of the default paths when installing SQL Server, and generally when I do an install I change the data and log folders to be on separate drives (typically D and E), ...
2
votes
1answer
4k views

Restore msdb from SQL Server 2008 R2 to SQL Server 2012 instance

I'm moving from a SQL Server 2008 R2 instance to a SQL Server 2012 instance on a separate box. I want to restore msdb to get all my agent jobs and the like and I'm getting the following error: ...
5
votes
1answer
495 views

The issue of sp_MScheckIsPubOfSub stored procedure

We have SQL Server 2008 R2 Merge replication set up. We have 20+ push subscriptions. Once in a while I get the following error message during the sync: Error messages: The merge process could not ...
5
votes
2answers
990 views

Large Fact table and partitioning key dilemma

I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes. The table ...
1
vote
0answers
177 views

Silently corrupted table after power-related shutdowns, records missing, *scrambled*?

Have a ssvr 2008 db, about 10gb. About 50 tables, 5 or so are frequent read/write, the rest are weekly write (append only), occasional read. The server came up and down a couple times over the past ...
0
votes
1answer
152 views

SQL Server Mirror failover on failing storage

Recently we had an issue with a sql server 2008 r2 HA mirror. I have tried to reproduce this in a lab environment and i came to the conclusion that i am missing something OR something is not possible. ...
2
votes
2answers
233 views

Required hardware to build a development environment in Sql Server

We have a bunch of applications that depends on about 10 different databases servers (all sql server) in our network. We are looking to build a development environment where we could have a mirror of ...
12
votes
2answers
4k views

Performance Tuning for Huge Table (SQL Server 2008 R2)

Background: I have a fact table in UAT Phase. Objective to load 5 yrs of data in Prod (expected size 400 Mn records). Currently it has only 2 years of data in Test. Table Features: No of ...
4
votes
6answers
32k views

Download SQL Server profiler for SQL Server Management Studio

How can I profile a SQL Server 2008 database to see code that's being executed on a particular database? I remember using the SQL Server profiler, but I don't see it in SQL Server Management Studio ...
3
votes
2answers
274 views

Why is my CPU usage increase after setting database to read-only on SQL Server 2008 R2 [on hold]

I administrate a reporting database. We run all the de-normalization process on a database DB1 daily, then we copy all the data to an another database DB2 on the same server which is queried by web ...
4
votes
1answer
5k views

How to grant a user UNSAFE ASSEMBLY permission

How can we assign a user , e.g.: the database owner (DBO) the UNSAFE ASSEMBLY permission? I'm asking it to enable a user to create an assembly and don't know the SA password, so EXEC sp_changedbowner ...
3
votes
2answers
1k views

Does adding page compression to a table also compress existing indexes?

I am creating a series of new tables that will have page level compression enabled. Will this affect the indexes as well, or will I have to also specify compression when creating the indexes? create ...
0
votes
1answer
100 views

UTC date in DMV dm_broker_queue_monitors

It seems that the DMV dm_broker_queue_monitors returns UTC time rather than local time. Why is it so? I am wondering how to get local time from this view? I can get current time offset, but that is ...
2
votes
2answers
851 views

SQL Server database generate script how to avoid adding PRINT statements

I need to create Insert scripts from the content of a table. To do that, I used the database tasks -> generate scripts. The table is quite big and in the generated script, it adds PRINT command after ...
3
votes
1answer
432 views

Difference between Intel and AMD chips?

I would like to know if there is a big dig difference between Intel and AMD chips in terms of how sql behaves? I've been told that their is a big difference between the chips depending on ...
8
votes
1answer
132 views

How can I prevent SSMS from re-writing my code when saving a view?

I'm creating a view that uses a statement with a WHERE clause similar to this: WHERE ( col1 IS NOT NULL OR col2 IS NOT NULL ) AND NOT EXISTS (SELECT ...) ...
2
votes
0answers
183 views

Fastest way to determine size of groups of FILESTREAM objects

We have a sql FILESTREAM enabled table, and are wondering what the fastest way is to determine the size grouped by a common criteria, as well as the sum of all objects. To date, we have been using ...
0
votes
1answer
71 views

How many connections reads queue

Is there a way to get to know how many external tasks (console applications) is reading queue? I am using Service Broker external activator. What i want is something similar to information i get ...
5
votes
1answer
651 views

Calling a .NET method in a SQL Server script

In the data migration process, I need to convert some columns values form the source system before writing to the target table. I've been provided with a Csharp code which reads a string and do the ...
0
votes
2answers
4k views

SQL Server : Is it possible to have a linked server link to another linked server?

Hello to all and thanks ahead of time for taking the time to look at my question! I'm trying to do a work-around to access a 32bit Advantage Database Server 8.1 database with 64bit SQL Server 2008R2. ...
3
votes
1answer
484 views

Applying user-defined fields to arbitrary entities

Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and ...
4
votes
2answers
563 views

Execution plan results for sp_executesql suggests adding missing index for already existing index

I'm analyzing a query being run inside our system via sp_executesql. I am able to execute the query on the production database and include the actual execution plan. In the execution plan results ...
0
votes
2answers
2k views

Save data to variable and use it in stored procedure

I have a table of 2 columns (Date, Text). I am getting data from the Text column according to the Date using a query like this: SELECT Text FROM MyTable WHERE Date>= @userDate; This works, ...
1
vote
2answers
410 views

How to connect to SQL Server Analysis Server via Management Studio command line

I can connect SQL Server Management Studio to a SQL DB by passing command line parameters like this: "C:\Program Files (x86)\....\Ssms.exe" -E -S SERVERNAME -d DB_NAME -nosplash (Obviously -E is ...
2
votes
2answers
2k views

Merge two records in the same table, keeping foreign key relationships intact for both

I have a table, called SITES, that has three columns, lets say it looks like this: ID Name Path 1 Google http://www.google.com/ 2 Microsoft http://www.microsoft.com/ I also have ...
4
votes
1answer
592 views

Select one name per person where spelling variations exist

I have a source table that contains Employee IDs and names. The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle ...
0
votes
1answer
496 views

Query performance and join hints, plan cost, and duration

Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases. Background I have a reporting database using ...
2
votes
1answer
58 views

Is it possible to determine what part of a stored procedure is currently executing?

Say I have a stored procedure like this (assume that the table schemas are fine): CREATE PROCEDURE p_MyProc AS INSERT MyTable SELECT Col1 FROM Table1 INSERT MyTable SELECT Col2 FROM Table2 INSERT ...
2
votes
1answer
182 views

tempdb thrashing and mirroring permissions (SQL Server) [closed]

We have a client site that uses SQL Server. The site this morning was slow, and we realised that the primary database server was responsible for a lot of disk activity on tempdb (Server 2008, SQL ...
3
votes
3answers
1k views

Cannot create login on cluster

I have a SQL Server 2008 R2 cluster on Win2008 R2. I am unable to add a login for the service account. Any other login I add to this instance works fine and I have been able to add the same service ...
2
votes
3answers
11k views

Unable to start SQL Server Instance 2008 R2 - DB file corrupt

I was not able to start the SQL Server 2008 R2 production DB instance. After reading the log file error message is " The log scan number passed to log scan in database ‘master’ is not valid. This ...
5
votes
2answers
2k views

Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?

E.g. consider the SQL query: SELECT A.[Name], ROW_NUMBER() OVER(ORDER BY A.[Name] ASC) FROM [FooTable] AS A Here I observe the results being returned sorted by A.[Name]. If I change the ...
2
votes
1answer
548 views

Add user to Server Role in a transaction

Situation One webapp responsible for creating other webapps using SQL Server 2008 R2 Stored Procedures. One of the steps in creating another webapp is creating a new database, a login/user, assigning ...
2
votes
3answers
850 views

Is there a way to tell SQLCMD not to output rows to text or a file?

I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD. My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows ...
2
votes
1answer
1k views

SQL column goes from first to last position in output

I have an SQL query inside a SQL Server Integration Services (SSIS) package where the preview shows the column order correctly, however the actual output uses a different column order. This is ...
1
vote
1answer
2k views

Full-text search in SQL Server on multiple tables fails

tl;dr version: A full-text search using FREETEXTABLE that worked fine on one column in one table produces 100% false postitives and 100% false negatives when the same steps are followed but the ...
2
votes
2answers
3k views

Fast XML, slow XML

Our database update Windows application needs to transfer some data between two databases as part of the process for a certain one-time update. I chose XML as the intermediary to move the data. The ...
3
votes
3answers
6k views

Altering nullability of column in SQL Server

Say I have the following table: CREATE TABLE test(test_id int not null identity primary key, field1 int not null, field2 int ); CREATE INDEX IDX_test_field1 ON test(field1); CREATE INDEX ...
1
vote
1answer
411 views

Issues on defining a PK on a nvarchar column and indexing the FK referring this PK

Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255). The value exists in this columns are ...
1
vote
2answers
887 views

Consistency errors for a SharePoint database

When I check my database (SQL Server 2008 R2, Windows Server 2008 R2, SharePoint 2007 DB) with the command: DBCC CheckDB I get 12 consistency errors (see below). Is the only way to fix it with ...
2
votes
1answer
815 views

Error 18456 trying to connect

Trying to connect to SQL Server (SQL Server 2008 R2) installed on Windows Server 2008 R2 results in the following error message (according to ERROLOG): Error: 18456, Severity: 14, State: 38. ...
1
vote
3answers
2k views

Putting SQL Server data files in a different partition

I have a SQL Server and I put the data files (the .mdf) on a different partition. Windows runs from C: and the data files and logs are on D: Is it advantageous or disadvantageous to separate the ...
0
votes
0answers
290 views

Real time replication issue - can't use transactional replication

We have an existing Microsoft SQL Server 2008 R2 database that we need to real time replicate to an identical off-site live web database. Transactional replication and merge replication will not be ...
1
vote
0answers
139 views

Looking for a good 3rd party Microsoft SQL replication app! [closed]

We have an existing Microsoft 2008 SQL R2 database that we need to real time replicate to an identical off-site live web database. Transactional replication and merge replication will not be able to ...
1
vote
1answer
1k views

Cannot change the Delete Rule of a self referencing foreign-key-relationship to cascade delete

I have the following table [dbo].[myObj] ( [ID] [int] IDENTITY(1,1) NOT NULL, [fk_ParentID] [int] NULL ) and I have a relationship from fk_ParentID to ID. I want to change the Delete Rule ...