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

learn more… | top users | synonyms (2)

0
votes
3answers
26 views

Exclude databases from automatic full backup

I have 10 databases on my SQL Server. We do an Automatic Full Backup every day at 00.00. The issue is there are two databases out of the 10 that should not be backed up as these databases are ...
1
vote
1answer
6k views

Standard data types for email address, telephone number , first name, rixh text editor, etc

I want to build a database for our system using Sql Server 2008 R2. but i need to know what are the best data types for storing these info:- Email address Telephone number with extension, such as ...
0
votes
0answers
17 views

Manual sync between 2 SQL server databases [duplicate]

I will not go in details and explain everything. For some (not good) reason I need to maintain 2 databases synchronized together. The full database schema may be different but not on table I want to ...
0
votes
3answers
42 views

Minimum value within a set (or window) within a three column table

I only have SQL Server 2008R2, therefore I do not have all the window functions available since SQL Server 2012. I have a table that looks like this: I would like to retrieve a result set like ...
0
votes
1answer
32 views

List of queries run

I'm looking for a DMV query for SQL Server 2008 R2 which shows me all queries (full history) run by a specific user. I have this code for now: SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY ...
0
votes
1answer
10 views

Why do I not see Configure Distribution when I right-click on replication?

I'm using SSMS 2014 for SQL Server 2008 R2. Why don't I see 'configure distribution' when I right-click on Replication?
5
votes
2answers
8k views

Importing a large column (8000 chars) from Excel using SSIS

I'm trying to set up a regular import of an excel spreadsheet that we get from a vendor. I'm using SQL 2008 R2 SSIS to import it into a table. The problem connection manager is an OLE DB connection ...
4
votes
6answers
8k views

Cannot remove unused filegroups

I wanted to remove some unused filegroups/files in a SQL Server Database but am stuck because SQL Server thinks the filegroups/files are still in use. Some background: We had some partioned tables ...
2
votes
2answers
59 views

SQL Row concatenation with XML PATH and STUFF giving aggregate sql error

I am trying to query two tables and get results like the following: Section Names shoes AccountName1, AccountName2, AccountName3 books AccountName1 The tables are: CREATE TABLE ...
2
votes
1answer
354 views

SQL Server Log Shipping Randomly Stops Copying and Restoring

I'm fairly new to Log Shipping but have configured a few with no issues, however I have a server sitting on SQL 2008 R2 with 6 databases on there, all 6 of these are supposed to be log shipping. The ...
2
votes
2answers
12k views

How to fix “The OLE DB provider ”MSOLAP“ has not been registered”?

Here's the code I'm using to register a linked server to SSAS: USE [master]; GO EXEC master.dbo.sp_addlinkedserver @server = N'ServerAlias', @srvproduct = N'', @provider= N'MSOLAP', ...
1
vote
0answers
50 views

To find SQL Server is actually used or not?

I have an SQL Server 2012 and a 2008R2 running in my environment. I need to decommission the server if the server is not actually being used. I did go through this and this where connection count is ...
4
votes
2answers
6k views

Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files): DBCC SHRINKFILE('tempdbfile8', EMPTYFILE); ...
0
votes
0answers
16 views

MS SQL data synchronization question

I'm in charge of modifying an application which was intended to run Microsoft SQL Server Compact 3.5. The application must run on a local database during fieldwork when there is no network ...
0
votes
2answers
33 views

How to separate values from one column into two columns in SQL Server [on hold]

I have this dataset as input: Name Contact No ------- ---------- Deepika 75865558 Deepika 207586558 Rnveer 746655558 Rnveer 946655558 vaibhav 2356841 vaibhav 6655558 I want to get ...
-1
votes
0answers
26 views

How to configure table with BLOBS (type image)?

I am about to overwork a table storing BLOBs in an IMAGE column combined with those file's metadata during a database maintenance. Lot's of the BLOBS will be removed during this process. We are ...
0
votes
0answers
452 views

SQL server 2008 querying very large large tables

I've recently started a new job, the table row counts are huge compared to where I was before (11 billion rows in one of our main tables). I've been told there isn't time to update the statistics on ...
4
votes
3answers
4k views

Best Way to Grant Read All Permission in SQL Server 2008 R2?

What is the best method to grant read access to all current and future databases to a user (AD Group in this case)? Currently I have a flaky scheduled job that does this using (going to start ...
3
votes
1answer
61 views

Amazing improvement of script execution speed when split - not understood

I have an ETL job that merges data into about 20 columns of a fact table, all integer non-null (default 0) columns. There are 8 merge statements to completely update all the columns and they involve ...
2
votes
1answer
204 views

Deployment script always re-create constraints and indexes

When generating a deployment script from SSDT it always generates code that drops the foreign key constraints & indexes then creates again those constraints and indexes. How to remove the code ...
0
votes
0answers
24 views

Permissions required to add user to fixed server role

We want to take a group out of sysadmin but grant them a role so they can do the following add, change, remove database access on prod servers for users, groups and service accounts. create and ...
0
votes
1answer
26 views

Script to Delete Tables With A Prefix

I am running SQL Serve 2008 and we have a few test tables that were obviously used for testing. The prefix of the tables are Test_, my question is, will this script delete all tables with the prefix ...
0
votes
0answers
59 views

Suggestions for indexes or how to partition a table in SQL Server 2008R2

Table structure and DBMS SQL Server 2008 R2 I have a single table structure (flat) that has summarized sales data (YTD,MAT etc) by unique combinations of "Customer", "Product" and "Territory". ...
0
votes
1answer
22 views

OLEDB provider for linked server reported change of schema version error

I am trying to run a report but its giving this message: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'ParamYear'. ...
2
votes
1answer
109 views

sys.dm_exec_procedure_stats missing history?

My boss would like me to find which (of approx 400+) stored procedures are not being used by our website any more. I'm found and reworked a script to give me the details of most recent executions, ...
5
votes
1answer
190 views

Property class Length Annotation PersistedResolvableAnnotation

I am trying to deploy an ssdt project using sqlpackage.exe utility. I get the following error (in German): Fehler bei der Erstellung des Bereitstellungsplans. Die Bereitstellung kann nicht ...
3
votes
2answers
569 views

Why not use SQL_Latin1_General_CI_AS for a global system?

What would be some really good reasons to not use the SQL_Latin1_General_CI_AS collation on a system which handles English, German, Japanese, and Chinese data? I'm having a really hard time finding ...
0
votes
0answers
29 views

Incorrect database backup file size in SQL Server 2008 R2

I am getting different sizes of the database backup file from SQL Server 2008 R2 even when backing up at very granular or frequent intervals. Scenario: I have taken database backup at 8:00am = ...
3
votes
2answers
98 views

How to rewrite a query with MAX in WHERE clause

I have a query with a max in the where clause, which is slow. select count(*) from TableName tbl1 where tbl1.id = ( select max(tbl2.id) from TableName tbl2 ...
0
votes
0answers
8 views

SQL Server 2008 R2 Developer edition - upgrade or install SQL Server 2016

Currently I'm working with SQL Server 2008 R2 Developer edition. I have several databases on it more than 500GB. Now I want to upgrade or install SQL Server 2016. What is going to happen to my ...
10
votes
2answers
16k views

Safely moving and creating new tempdb files

Two things I'd like to know, how do you safely move tempdb with minimal downtime? Also, how do you create new tempdb files? Is it 1 per core? So quad-core = 4 tempdb files, creating three new ones?
4
votes
5answers
291 views

Does index rebuild time depend on the fragmentation level?

Is the required time for index rebuild dependent on the level of fragmentation? Does the rebuild of a 80% fragmented index approximately take 2 minutes if the rebuild of the same index fragmented ...
3
votes
1answer
40 views

Apply SQLServer Service Pack to a Single Node Cluster

I have to apply a SQL Server 2008 R2 Service Pack to a Single Node Cluster. The other node died and new hardware has been ordered. Is there anything weird to worry about? Normally we'd apply the ...
4
votes
1answer
111 views

Scenario of huge SQL Server Database backup

I use SQL Server and have a huge database with large compressed backup size (about 250 GB). The backup scenario of this database is: Create full backup at the first day of each month. Create ...
3
votes
1answer
36 views

Are indexes bloated if using nvarchar field sizes that are much bigger than needed?

I've been provided with a database where it appears the designer has used much larger field sizes than are needed. Commonly nvarchar (128) fields have been used but the data being stored in them is ...
-1
votes
1answer
55 views

UPDATE statement in a trigger occasionally updates some columns but not others [closed]

I have a table called NOTATION with the below table structure: ( NOTATION (primary key) TABLE_NAME , TABLE_KEY , NOTE_TIMESTAMP, SUBJECT , COMPANY , ...
1
vote
0answers
14 views

msdb.dbo.backupset.compressed_backup_size is inaccurate?

I thought the value in msdb.dbo.backupset.compressed_backup_size represented the size, in bytes, of the full, differential, or log backup file (that is, the BAK, DIFF, or TRN file). However, although ...
1
vote
2answers
41 views

SQL Server engine in one place with database files on NAS

My office uses accountant programs which run on SQL Server 2008 R2. The SQL Server engine must be installed on one of the laptops (called "server") and the other laptops (called "clients") connect to ...
4
votes
1answer
122 views

how to ignore the SSMS (sql server management studio) sql server ansi settings?

The options in Management Studio On Management studio Tools->Options->Query Execution->SQL Server there are options that are set and these prevail over the same options that are set on database ...
3
votes
1answer
757 views

SSDT project with working linked server references gets SQL71562 warning

I have a VS 2013 solution with two database projects. One of them, let's call it the "worker" is supposed to get deployed to about 10 different production servers. The other one, that is supposed ...
1
vote
2answers
81 views

How can I improve performance on a 3 table query?

I'm attempting to analyze a resource drawdown (the percentage of time individual units are being utilized during a reporting period, e.g. 2-weeks or 1-year). I need the aggregation down to the ...
1
vote
1answer
181 views

Query to get total time logged in per week

I need to find the number of hours each user is logged into an application per week. The table structure is as follows: [ID] [UserID] [Time] (datetime field) [EventType] ("login" or "logout") There ...
-3
votes
1answer
36 views

SQL Server 2008 R2 - Rebuild Log

I'm trying to rebuild a log file for my db and I'm wanting to know if what is happening is normal. This is what I'm doing: use master ALTER DATABASE my_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ...
4
votes
2answers
64 views

Performance of a self join in a temporal table with a single day's difference between end_date and start_date

Title says it all really. A pretty standard temporal table design has a start_date and an end_date where the end_date is the day before the next record's start_date. In this case, there is an ...
0
votes
1answer
12 views

Can't list columns or query in MS Access 2003 linked server in SQL Server 2008 R2 using Microsoft Jet 4.0 OLE DB Provider?

I can however list the tables... If I run Exec sp_tables_ex 'LSTest' I get a result with a list of tables. But if I run Exec sp_columns_ex 'LSTest', '<one of those table names>' I get an ...
1
vote
2answers
74 views

Databases in Suspect state after enabling TDE

A few of my databases are in a suspect state. How can I get them online? I don’t know what info is relevant and what isn’t, but I’ll give as much as I can. Here is what happened: I enabled TDE in ...
-2
votes
3answers
91 views

Split URL string into columns

Given the string: URL:'ID=1!&user=xxx&depart=21&companyId=43&workOrder=67' I need to get the result: Sno ID user depart companyId workOrder 1 1! XXX 21 43 67 ...
5
votes
1answer
353 views

Distributed transaction and Read Committed Snapshot

Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?
4
votes
4answers
5k views

Extracting a field from RESTORE HEADERONLY

I'm trying to use 'RESTORE HEADERONLY' to get the date when the backup I'm about to restore was made. The command: RESTORE HEADERONLY FROM DISK = '<path to .bak file>' works fine in Query ...
2
votes
1answer
26 views

SQL Server 2012 In-Place3 Upgrade and Timing of Database Version Change

We are updating several SQL Server 2008R2 instances to SQL Server 2012 SP3 CU2. I know that the database versions will be changed from 100 to 110 during this process (e.g. once finished, the databases ...