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

learn more… | top users | synonyms (2)

1
vote
3answers
481 views

Will performance be better if I specify a filter multiple times?

Do I gain more performance if I repeat the filter in the WHERE clause on both sides of an OUTER JOIN? To clarify using code: select * from #main_select left outer join ( select main_id ...
3
votes
3answers
1k views

Running a DELETE stored procedure but finding degradation in performance in WHILE loop as iterations increase

Have a quick general question. I have a table I'm trying to purge a table. I'm deleting using a WHILE loop with WAITFOR DELAY time of 50ms between batches and 2000 records per batch. The real issue is ...
3
votes
1answer
2k views

SQL Server Many-to-One replication

I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data. I would like to establish a reporting ...
4
votes
2answers
1k views

What is the best way to migrate multiple databases?

I want to migrate my database server to a new server. Right now I have a database server with Windows Server 2008 and now I am migrating to a new, separate server with Windows Server 2012. There are ...
5
votes
2answers
17k views

Can I install SQL Server 2012 on a machine with 2008 R2 already?

I have been using SQL Server 2008 R2 for quite a while, and it is working great. Now I need to use SQL Server 2012. Will this effect my existing databases? How much risk is involved in installing SQL ...
4
votes
1answer
2k views

Import Data replaces NULL with 0

I am using sql server 2008 r2 express. I am getting some problems when i use import data feature to import data from one database to another. I am using SSMS to do it. While using the wizard i choose ...
5
votes
2answers
499 views

If I update a column record in a table, will indexes that do NOT have this column in it be affected?

In terms of performance if I have a table like so: CREATE TABLE [TESTDATA].[TableA]( [Col1] [nchar](5) NOT NULL, [Col2] [nchar](2) NULL, [Col3] [float] NULL CONSTRAINT [TableA_PK] PRIMARY ...
3
votes
1answer
2k views

SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

SQL Server 2008R2 PowerShell 2.1 I am trying to create a SQL Agent job that dynamically backs up all non-corrupted SSAS databases on an instance without the use of SSIS. In my SQL Agent job, when I ...
5
votes
4answers
11k views

How to block user access to perform database application update?

Where I work we have a SQL Server database (Microsoft SQL Server 2008 R2) that serves as the back-end with two different user interfaces, a .NET web interface and a FoxPro interface. Every month we ...
7
votes
3answers
2k views

How should I best handle a rapidly growing database?

I have a database that I need to maintain. Sadly, the setup and use of that database I can't change, much (thanks to some internal politics). It's running on SQL Server 2008r2. Its only been live ...
8
votes
1answer
235 views

-E startup option and SSDs

Has anybody seen evidence of the effect of -E when using SSDs? The effect on 'spinning rust' drives is not disputed - but SSDs aren't really flustered by random I/O. I wonder if the -E option might ...
1
vote
2answers
928 views

Does a database restore remove existing stored procedures

I have a PROD and NON-PROD system NON-PROD already has a copy of an application PROD database I have also created new stored procedures on the NON-PROD application database and made changes to a ...
2
votes
1answer
533 views

SSIS - How do I execute a single OLE DB Command for mutiple paths?

I have an SSIS package (2008 R2) that I'm working on and I find myself duplicating the same OLE DB commands repeatedly and whenever there is a change its a nightmare to go back through and update all ...
0
votes
1answer
71 views

Merge subscription behaviour

I have a merge subscription with the ExchangeType parameter set to 1 (upload only). Rows are transferred to the publisher as expected. If I delete a row at the subscriber, the corresponding row at ...
11
votes
1answer
13k views

Parent-Child Tree Hierarchical ORDER

I have to following data in SQL Server 2008 R2. SQLFiddle Schema: CREATE TABLE [dbo].[ICFilters]( [ICFilterID] [int] IDENTITY(1,1) NOT NULL, [ParentID] [int] NOT NULL DEFAULT 0, ...
5
votes
3answers
1k views

memory used by Locks

I am kind of curious, one of SQL 2012 enterprise edition with 128 GB of RAM size of database is 370 GB and growing, amount of memory used by locks (OBJECTSTORE_LOCK_Manager) memory clerk showing ...
4
votes
2answers
139 views

Partitioned Views - Storage of Indexes

I have a single table that contains 4 billions rows that I plan on splitting up in to separate tables for each Quarter of the year. Then I want to create a partitioned view across all tables. At the ...
0
votes
1answer
2k views

SSIS Adding column to excel sheet template destination when importing from SQL Table Source

I have an excel sheet template that has 3 columns. I also have a SQL server table that has the same 3 columns plus an additional 4th column. I need to import all 4 columns from the SQL table into the ...
0
votes
1answer
142 views

Efficient way to fetch records since a given criteria

I'm trying to implement a logic where the user can say give me n records since a given id#. E.g. SELECT TOP (100) col1, col2, ... colN FROM Table1 WHERE ID > @id ORDER BY ID Performance is the ...
2
votes
1answer
858 views

Evaluation edition expired

I have a client that has a recently expired SQL Server 2008 R2 Enterprise Evaluation Edition. I've been tasked to get it licensed and running SQL Server 2008 R2 Standard. How can I achieve this?
-2
votes
1answer
103 views

Handle vast amount of data in DB [closed]

Now my current database is working in SQL Server 2008 R2 and we are uploaded its only in 1 server. How can I migrate my existing database in to Hyper Table ? (My current database has huge amounts of ...
2
votes
0answers
105 views

os_buffer_descriptors blocking problem SQL Server 2008 R2

The following query : select (buffer_descriptors.datacache) as 'Data Cache (MB)', case when buffer_descriptors.datacache > 4096 then (((buffer_descriptors.datacache)/4096)*300) else ...
2
votes
2answers
771 views

Can I open a SQL Server 2008 R2 database in SQL Server 2008 R2 Express?

This question has probably been asked a dozen times but I couldn't find them here or at SO. Can I attach/restore a SQL Server 2008 R2 database to my local SQL 2008 R2 Express instance?
2
votes
1answer
468 views

SQL Server Memory for Backup Buffer

In SQL Server 2012 memory for backup buffer comes from buffer pool or non buffer pool (VAS)? Is this behavior changed between SQL server 2008 R2 and SQL 2012? EDIT: Does MAX Server Memory ...
1
vote
1answer
68 views

Is there any way to Restore a Database from newer version to an Older Version of SQL Server? [duplicate]

I am having a database backup file from SQL SERVER 2012 and i wanted to restore it on SQL SERVER 2008R2 . If we can’t restore a SQL Server 2012 database to earlier version what is the next best ...
1
vote
1answer
262 views

How to repair Microsoft.SqlServer.Types assembly

When I run a checkdb('mydb') this is the only error message printed. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3857, State 1: The attribute (clr_name=NULL) is required but is missing for ...
2
votes
1answer
974 views

Can you grant an Active Directory Group rights on a linked server?

I am trying to grant rights to an Active Directory Group on a Linked Server. I am unable to do it; it only allows me to grant rights to individual active directory users. Does anyone know how to ...
2
votes
2answers
172 views

Record versioning and promotion

Let's say we have this hierarchy: -World --USA ---WA ----Seattle -----Downtown ------1st Ave -------945 1st ave ------3rd Ave ...
0
votes
0answers
30 views

SQL 2008R2 Trial [duplicate]

I currently have a small db running on SQL Server 2008R2 Express. I'm considering moving to SQL 2008R2 Small Business or Standard to use all the sever is capable of. I have a Dell T610 - 2xQuad Core ...
1
vote
0answers
119 views

How can I generate a usage log? [closed]

I'm trying to automatically generate an events log report in SQL Server that records events including successful login, Create/Delete Account, An Account was Unlocked, Account locked due to maximum ...
3
votes
2answers
1k views

Can I trust the Database Engine Tuning Advisor recommendations?

Our database has become large, and we are trying to optimize performance. Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
-1
votes
4answers
169 views

Dropping and recreating indexes [closed]

I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness). I somehow manage to rebuild ...
4
votes
1answer
274 views

Can I decrypt TDE transaction log files for use with 3rd party software?

Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to ...
2
votes
2answers
748 views

View column has an 8000 character limit?

In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 ...
0
votes
1answer
3k views

How to do an in-place upgrade of SQL Server 2005 Express to SQL Server 2008 R2 Express

I would like to upgrade my SQL Server 2005 Express instance which is installed using this command: SQLEXPR.EXE/qs /action=INSTALL /IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQLEngine ...
1
vote
2answers
381 views

Update my local db based on scripts executed in my server database

I need to create an solution that update my local db version based on changes that was made on my server database. First, I need to check the differences that has exists between these databases and ...
1
vote
2answers
3k views

Restore database created in SQL Server 2008 R2 to SQL Server 2008 R2 SP2 [closed]

I have a .bak file created by some other user in (I think) SQL Server 2008 R2 (10.50) and I need to restore it in SQL Server R2 SP2 (10.52). I used a hex viewer to look into the .bak file and found ...
3
votes
2answers
987 views

Is this a good strategy for importing a large amount of data and decomposing as an ETL?

I have a set of five tables (a highly decomposed schema for an ETL if I understand the nomenclature) that I'm going to load via bulk import, then run some inserts from those five tables into a SLEW of ...
4
votes
1answer
227 views

Is it “legal” to CREATE and DROP #SomeTable more than once?

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this: CREATE TABLE #WidgetSetting ...
5
votes
6answers
2k views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
-1
votes
1answer
568 views

Query returns no data [closed]

I am running a query to show LastName and Hire_Date and my results display the columns but do not include any data. This is an assignment for an online class and I have been waiting for my instructor ...
0
votes
1answer
1k views

VarChar to NVarChar causing truncate error

I am importing some data from a temporary table which has a column defined as VARCHAR(150). The destination tables column is defined as NVARCHAR(200). I'm using an INSERT INTO statement, but when I ...
3
votes
1answer
3k views

Attaching a database in SSMS gives error

I am accessing SQL Server using SSMS, and a user with adequate permissions. When this user tries to attach a database by using SSMS (by right clicking on database node and then clicking on attach), ...
1
vote
1answer
241 views

Database size not reduced after compressing tables

Using Data Compression, my table size was reduced from 1770 MB to 250 MB, but the database size was not reduced. Why?
4
votes
2answers
3k views

Constraints based on other columns

Is it possible to limit what values are allowed in a coulumn based off of other values in the row? For example, my table: ID Test_mode Active -- --------- ------ 1 1 Null 2 0 ...
0
votes
0answers
66 views

Copy tables from a stand-by database

I want to explain the following. I have a database that is a recovery db from our main environment. This database is kept up-to-date through log shipping. However, I want to copy all tables from this ...
5
votes
3answers
1k views

How to reduce network latency during mirroring?

We are using SQL Server 2008 R2 Standard edition for mirroring. But we found out that network latency is too high. It takes nearly four times time to complete a transaction while mirroring is active. ...
2
votes
1answer
49 views

Audit full text activity

I need to find when and which user created full text catalog, and who added/modified/deleted the columns (that were full text indexed) in that full text catalog.
5
votes
1answer
271 views

SA permissions issues with many nested objects

I have a broker application that's relatively complicated. Today, after I made some changes, I started getting the error: The server principal 'sa' is not able to access the database 'XYZ' under ...
0
votes
3answers
9k views

How to restore SQL Server 2008 R2 backup to SQL Server 2008 [duplicate]

I am unable to restore a backup from a 64-bit instance onto a 32-bit instance. Source Server: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Destination server: Microsoft SQL Server ...