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

learn more… | top users | synonyms (2)

7
votes
1answer
142 views

Lead/Lag Implementation in SQL Server 2008 R2: Max memory exceeded

Background I'm trying to establish a "visits" sequence, wherein if an animal is detected at essentially the same place (General_Location) it counts as a single visit, but if it goes elsewhere and ...
3
votes
2answers
47 views

Replication DB move

I have 2TB of DB involved in SQL Server transactional replication. I want to move publisher DB files to a newer disk with minimum amount of downtime and without breaking replication. Please let me ...
1
vote
1answer
62 views

SQL Server Database Migration - Backup/Restore while Original DB still in Production

I have a very large DB in full recovery mode. My SQL Server is 2008 R2. The thing is that I need to migrate to a new server while having the original DB still in production. Both backup and restore ...
3
votes
1answer
31 views

Agent shows disabled(although it is not) after migration

I am migrating our old server to a more reliable and powerful new server. SQL Server edition and build number stays the same (SQL Server 2008 R2 Standard). For this I followed the steps given in this ...
7
votes
3answers
202 views

Application querying empty tables

My company uses an application which has pretty major performance issues. There are a number of problems with the database itself which I am in the process of working through, but many of the problems ...
0
votes
0answers
19 views

RDS Data Loss after modify DB instance

I recently modified the size of one of our RDS instances in AWS, after this operation had completed overnight I came in to find that some of the jobs had dropped out of SQL Server Agent & the job ...
1
vote
1answer
60 views

SQL Server Change Tracking and Replication

My colleagues and I are working on a solution revolving around change tracking… Our goal is to use SQL Server change tracking to track changes to be pushed to another non-SQL system. Our database is ...
1
vote
1answer
87 views

MERGE with IDENTITY_INSERT ON does not work if Identity is not the primary key

I frequently use MERGE statements and am quite familiar with it. Now I ran into a situation where some tables have IDENTITY columns that are not the primary key. In this situation the script failed ...
0
votes
1answer
50 views

How do you GRANT SELECT on hidden resource database mssqlsystemresource?

I get an error when I run this query: SELECT dependencies.referencing_id as object_id, dependencies.referencing_minor_id as column_id, dependencies.referenced_id AS ...
3
votes
1answer
45 views

Multi-command SQLCMD mode script

I am trying to execute a sql procedure for a list of tables in sqlcmd mode to redirect all the output into one file per table. Unfortunately only the last file of the redirected outputs of all ...
0
votes
2answers
96 views

What is the alternate to SA account in SQL Server? [closed]

My client is currently using SQL Server for their app. At this time they are using SA account everywhere. For security reasons we need to disable the SA account. The solution I've is create another ...
1
vote
1answer
37 views

What options are there for rebuilds of indexes on large tables for a data warehouse?

I have a bulk loading process that loads millions of records into a few fact tables within a warehouse. Those tables are primarily clustered on time. The non-clustered indexes are in place for how the ...
0
votes
1answer
25 views

How to find Over payment amount of three tables using inner join

How to find Overpaymet value of only ClientNo 1 from bellow 3 table using inner join. (Each client wise i need to retrieve query) For example 1: Once i retrieved client 2 only, answer should be 0 ...
0
votes
1answer
23 views

SQL Server authentication failed after server promote

we recently promoted a Windows 2008R2 server to domain controller. We then found that it hosted a SQL Server 2008R2 Express database instance. If we try to login to the DB with the domain admin ...
6
votes
2answers
216 views

User Defined Function (UDF) in master database

I have a function which is used in more than a dozen databases. I want this function to be accessible in all the databases by writing it only once, so I want to write the function in master database ...
2
votes
1answer
76 views

Online index maintenance in SQL Server using SNAPSHOT isolation level

I have recently altered our SQL Server 2008 R2 database to enable SNAPSHOT isolation level and made appropriate changes to hibernate to run all transactions in SNAPSHOT mode. I have however noticed ...
1
vote
1answer
42 views

Jetbrains 0xDBE : How can I load a SQL Server .bak file?

I'm trying this new Jetbrains IDE and I can't figure out how to load a SQL Server 2008 R2 .bak file. Maybe I'm looking in the wrong menus. Is it possible?
4
votes
2answers
214 views

Executing the same SQL statement for different tables

I have a stored procedure that deletes data from multiple tables. The SQL is all the same, except for the name of the table. UPDATE desttable SET desttable.reportarea = esz2reportarea.reportarea ...
3
votes
1answer
45 views

Unable to change servername after VM Clone

For reasons out of my control I must find a solution to this issue. Simply reinstalling the instance is not an option. The server name that appears as server_id 0 in sys.servers is still showing the ...
2
votes
0answers
21 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 ...
4
votes
2answers
38 views

Timeout expired SqlException for complex Procedure

I use Sql Server 2008 R2 and ASP.NET 4.5. Sometimes, I get this error when I executed a complex procedure: System.Data.SqlClient.SqlException Timeout expired. The timeout period elapsed ...
3
votes
1answer
24 views

Crosstab Pivot or not?

I am have difficulty wrapping my head around the following issue. I currently have a set of data in one table as shown below: As you can see, there is an invoice ref number along with a date, and ...
0
votes
0answers
33 views

Database(in US) access slow for Romanian counterparts

So I am not much of a db guy, I can basically get a sql server up and running, create tables and use them for internal tools. So I am in no way an expert on database systems. I went to Romania to work ...
9
votes
1answer
157 views

Problem creating a transaction in an SSIS package

I'm working on a package that needs to use a transaction but I'm currently getting the following error: SSIS package "CATS-Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, ...
0
votes
2answers
49 views

How to find the sum and overpayment of three table values

I need to find the each client sum of TotalAmount in Invoice_TBL and sum of TotalPaid in Payment_TBL. The below i have mentioned three tables. Retrieve query answer should like below image from the ...
0
votes
1answer
33 views

Converting SQL Server recovery model simple to full

I am using SQL Server 2008 R2 with a recovery model of simple. Monthly we have: Total number of insert is 2196716 Total number of delete is 639542 Total number of update is 4203398 Now we are ...
3
votes
2answers
116 views

SQL Server transferring results slowly in table result set, but instantly with XML

I have a query that left joins 100+ tables and returns 2,000+ columns, but only a few rows (10-20). This part is out of my control at the moment and it used to run ok with a bit fewer columns (maybe ...
0
votes
0answers
38 views

Large differences between estimated and actual row counts but stats are OK

I'm having a problem with a Function which is taking 10 times longer to run than it used to and high CPU usage. And I'm puzzled by what I'm seeing in the execution plan with some index seek operators ...
2
votes
2answers
80 views

Failed to update SQL Server 2008 R2 RTM to SP3

I'm trying to update SQL Server 2008 R2 RTM to SP3 by running SQLServer2008R2SP3-KB2979597-x64-ENU.exe, on Windowns Server 2008 R2 SP1. It failed with the following error in the log, 2015-11-26 ...
1
vote
0answers
18 views

SSIS package version GUID does not stay stable between runs

We have a large quantity of SSIS packages created in visual studio 2008. Currently running on hundreds of servers. We are logging the details of each package before it runs. One of the things we are ...
2
votes
2answers
48 views

Index-refresh in SQL Server

Is there any way to identify when a specific index was last rebuilt; unless it is used as a batch maintenance activity? In SQL Server 2008R2 or in advanced versions.
6
votes
2answers
284 views

Doubts in a Glenn Berry Query ( CPU per Database )

Please, if this is not place to post questions like these, let me know and I will delete it. Inside the Glenn berry's Diagnostic queries, there is a query to show how much CPU a database is using. ...
4
votes
4answers
189 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 ...
0
votes
1answer
43 views

Do you need Table Lock when you have multiple OLE DB connections inserting to the same table?

SSIS is not really my forte. I noticed you have a table lock option on OLE DB destinations where the entire table becomes locked in what I assume is during the transaction of inserting data. What ...
1
vote
1answer
63 views

Scramble / masking data from SQL Server [closed]

I need to share a SQL Server 2008 R2 DB with the development team. The data is classified but the schema and the volumes are not. The DB includes all primitive types (long, decimal, nvarchar etc.) but ...
0
votes
1answer
36 views

Query to combine each duplicate found entry

Hey all I am wanting to create a query so that I can combine each of the found duplicates into one entry. An example of this is: Name | ID | Tag | Address |carNum ...
1
vote
1answer
46 views

Forbid user to query internal SQL server information

I'm trying to forbid database user to get SQL server internal information. Script that creates DB user: CREATE USER [testuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo] DENY SELECT, INSERT, UPDATE, ...
5
votes
2answers
39 views

SQL Server 2008R2 - MAX Result and Hour per Day

I have a table full of network interface statistics. InterfaceID TimeStamp RCV_bps XMT_bps ----------- ------------------------------ ------------- ------------- 275 ...
0
votes
0answers
37 views

SQL Server 2008 r2 performance drop

Following a change of server, the SQL server performances dropped drastically, the difference between the servers is as follow: old machine new machine ...
0
votes
1answer
39 views

SQL Server 2008 R2 SP1 - Can't install “Full-text Search”

I have a "Microsoft SQL Server Express 2008 R2 SP1" installation on a machine without the "Advanced Services". Now I want to install the "Full-text Search", so I downloaded the setup ...
0
votes
0answers
14 views

Usage of the AMR tool for migration to SQL Server In-memory OLTP Tables

After reading the following articles about the forecast of benefits of SQL-Server 2014 in Memory functionality and getting recommendations for this, I am confused about this procedure. Determining ...
2
votes
0answers
81 views

Why is this update creating a deadlock?

I have these queries: (This one was deadlocked) And this one: update tbldealerativacao set flagusado = 0 from tbldealerativacao WITH (NOLOCK) inner join tbldealerusuario WITH (NOLOCK) on ...
11
votes
1answer
348 views

How can I shred this Extended Events XML as fast as possible?

I created an Extended Events session in SQL Server 2008 R2. The session runs, and collects events as they happen, exactly like you'd expect. If I shred the xml when there are relatively few events, ...
0
votes
0answers
27 views

Restore a table deleted seconds ago with Ghost Record

I've been reading about ghost records on SQL Server 2008 R2. I've read about, but only found topics about "restore rows" of data in ghost record. Is there a way to restore a table ( or tables ) that ...
0
votes
1answer
38 views

Choosing Between SQL Server 2008 R2 and SQL Server 2012 - Which to learn about first? [closed]

As the title suggests, I'd like to get advice on which version should I learn about first? Most online courses now emphasize on 2012 and Microsoft no longer gives out exams on SQL Server older than ...
0
votes
0answers
26 views

How can I check logs/audit files for changes made to a view in SQL Server 2008 R2?

On a SQL Server 2008 R2 database, I have a view which may have been altered by another user. The only change that was authorized to be made was for the addition of a new column to one of the tables ...
2
votes
1answer
82 views

WAIT STATS - high Wait Time on ASYNC_IO_COMPLETION

I currently look at my wait stats. I followed best practices explained by Paul Randal and go through the documents he references. Looking at the statistic slices I take once / day for a while now ...
1
vote
2answers
60 views

Cannot generate SSPI Context

When I try to connect to a SQL Server from my local machine I get the following error: Cannot connect to Computer1\Instance1. The target principal name is incorrect. Cannot generate SSPI ...
0
votes
2answers
77 views

Invalid connection error on SQL Server 2008

I have this third party application that is interfacing with a MS SQL Server and I have some issue migrating its database from a MS SQL Server 2005 to A MS SQL Server 2008. The application was ...
2
votes
2answers
101 views

Get count based on 15 minutes of interval

I have a table containing Call records: tbl_calls cl_Id cl_StartDate cl_endDate I am passing two parameters @StartDate and @EndDate to my stored procedure. My requirement is to get the count of ...