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

learn more… | top users | synonyms (2)

9
votes
1answer
425 views

Where does an execution plan come from?

Is there a way I can determine if a plan was just generated for a particular query, or was found in the plan cache?
4
votes
1answer
44 views

Nest SUM() Function Inside Case Statement?

I am trying to run this syntax, but I get an error of Msg 4145, Level 15, State 1, Line 9 An expression of non-boolean type specified in a context where a condition is expected, near 'SUM'. ...
7
votes
3answers
74 views

SQL Server: Using Dual Databases for Performance?

We have a SQL database that stores application usage logs for about 3000 PCs. These PCs send their usage data to the SQL server around 10-20 times per day. We used to store only the most recent 60 ...
0
votes
1answer
15 views

Getting a logical consistency-based I/O error error in sql server

I am using SQL Server 2008 R2. My server got accidentally shutdown. After restarting it, I am not able to access my database. I am getting the following error for a few tables: SQL Server detected ...
2
votes
1answer
46 views

SQL Server 2008: Unused Clustered Index

I have an MS SQL Server 2008 R2 database that contains a table that has just under 100 million rows & is both read and write heavy. It contains Tweets, and the id of each Tweet is one of the ...
-2
votes
0answers
64 views

SQL Server Management Studio Has Stopped Working [closed]

I was executing a large script and suddenly the Sql Server had crashed, after that I tried to run the SQL Server Management Studio, but was getting the mentioned error. I even tried to repair the SQL ...
3
votes
0answers
35 views

why some one will use approximate data types for sales

I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
2
votes
1answer
64 views

Run aspnet_regsql.exe from SQLCMD Post-Deployment Script

I am trying to create a Post-Deployment script in SSDT that will include the creation of an ASP.NET Session State database to make sure everyone has this thing in place after deployment of my database ...
4
votes
1answer
43 views

How to handle Schedules when moving Jobs to another server?

We are migrating our SQL Server 2008 R2 to another physical server. For this, I will script out all jobs and run it on the target server. My question is about handling the schedules: When a job is ...
9
votes
1answer
131 views

How to reproduce “Could not continue scan with NOLOCK due to data movement”

I occasionally get "Could not continue scan with NOLOCK due to data movement" with some large jobs, which do have WITH (NOLOCK) on the select queries. I understand this has something to do with ...
2
votes
1answer
66 views

Script file execution through Power shell

We are trying to automate few things, one of them is to deploy patch in one go on selected DBs of SQL Server, as per my knowledge Powershell is an option. But I have zero taste buds of Power shell. ...
1
vote
1answer
61 views

How to pass credentials to a linked server in another domain?

I am trying to link two SQL Servers (2008R2 and 2014) which are running in two separate (!) Active Directory domains. The problem is that I can login into the remote server only with a Windows ...
7
votes
1answer
143 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 ...
2
votes
1answer
64 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
204 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
20 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
63 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
95 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
97 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
26 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
219 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
215 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
160 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
117 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
84 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
285 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
191 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
64 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 ...