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

learn more… | top users | synonyms (2)

17
votes
1answer
32k views

SIMPLE or FULL recovery model for databases?

When should I use the full recovery model and when should I use the simple recovery model for databases? I always used the full recovery model because it is the default, but today i encountered this ...
1
vote
1answer
24 views

How can I perform a full-text CONTAINS call across a list of phrases?

I have a list of words/phrases in a table phrases. I have another table with a full-text catalog indexing the description column. I want to populate a table results with a record for each phrase, and ...
0
votes
0answers
93 views

Cannot Update a System Collection Set

I'm trying to tweak the "Server Activity" Data Collection set as outlined in this blog post (I wrote the post, btw. Sorry--it's kinda long). It is working in SQL 2008 R2 and also in SQL 2014. However,...
4
votes
1answer
175 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 ...
1
vote
3answers
52 views

Need to intentionally create blocking processes for testing

My team and I have an issue where we need to be able to identify processes that are blocking other processes and kill them. There are a ton of scripts that are freely available to perform these ...
0
votes
0answers
15 views

Finding records only when all ids present in a group

Having these two tables id | val TableA -------- 1 | A 2 | B 3 | A 4 | B 5 | A 6 | C 7 | D id_A | data TableB ----------- 1 | a 2 | b 3 | c 5 | d 7 | e First I ...
0
votes
0answers
5 views

Generic Rowset Error Causing SSIS package to Fail

A vendor-provided stored procedure on a SQL 2012 instance and DB in compatibility mode 110 performs several ETL steps and returns a single resultset from a temp table. Execution of the sproc takes 3-5 ...
1
vote
1answer
22 views

Configuring a non-nullable foreign key uniqueidentifier with a default Guid.Empty value that exists in foreign table

Project Details: MS SQL 2008 R2 I have already asked what amounts to one half of the question here, but I am hoping someone in this forum can answer the other half, involving MS SQL server ...
7
votes
2answers
3k views

Broken tempdb location and can't recover

Made a mistake and mistyped an alter database command for tempdb. Now the instance won't start. I can't start in single user mode using -m as it states the tempdb could not be found. I tried using: ...
1
vote
5answers
82 views

Passing comma separated values to parameters in stored procedure

I have a requirement to retrieve data from a table based on the comma separated values sent in the parameters for stored procedure. As of now I have made the code work for one single value not sure ...
1
vote
0answers
27 views

Deleted sql server windows authentication user

I had deleted a system user "sysuser" from the server. I had previously used this user to login to the SQL server through "windows authentication" mode.Now, I tried to create a new user with the same ...
0
votes
1answer
189 views

Application is hanging when database upgraded from SQL Server 2000 to SQL Server 2008 R2

Presently we are working with SQL Server 2000, the database is perfectly working with our software for retail sales counter application developed with VB 6.0. But after upgraded to SQL Server 2008 R2, ...
1
vote
1answer
111 views

Shrink database or DB files not reducing size even after dropping biggest table

Our MDF file was originally 550GB in size (log is just 10MB), and the biggest table we had [HourlyCounters] was 140 million rows with 580 columns (varchar[50]). It's a table with a very inefficient ...
0
votes
0answers
48 views

Cannot execute code connecting to SQL Server 2008 R2 in SQL Server 2014 SSMS

I am not able to connect and execute a code in SQL Server 2008 R2 while connected to SQL Server 2014 SSMS. SQL Server 2008 R2 is on Windows 2008 R2 64 Bit SP2 (Stand Alone) SQL Server 2014 is on ...
3
votes
1answer
61 views

Why does the “Date Created” for the master database show such an old date (8 April 2003) in SQL Server 2008 R2?

Why does the "Date Created" for the master database show such an old date? I created the new instance today, not 2003.
0
votes
0answers
25 views

Creating view to another instance

I am trying to create a view from one SQL instance to another on the same server. Client wants to "get tables from the INSTANCE-A into the views for INSTANCE-B. I have tried a couple different ...
1
vote
1answer
160 views

Indexed view doesn't exist on the subscriber

I am configuring transactional replication in SQL Server. The subscription is configured as push from 2008R2 publisher (distributor is the same server) to 2012 subscriber. The object I want to ...
3
votes
3answers
20k views

SQL Server 2008 R2 Management Studio : “Evaluation period has expired”

I've spent several hours trying to fix this issue so far with no success. I had installed SQL Server 2008 Evaluation Edition. Out of fear it might expire & create problems for me I installed SQL ...
14
votes
4answers
6k views

Move multiple databases from C: to D: at once

I have SQL Server 2008 R2 with 323 databases consuming some 14 GB on my C: drive, a fast SSD. Because I want to reclaim some space on my C: drive, I would like to move them to my D: drive. I have ...
2
votes
1answer
43 views

Database Shrinking as a logged operation

I would like to know if shrinking a data file is a logged operation or not. I do not have a definite answer to this. I have heard people say it is a logged operation but so far I did not see an ...
0
votes
2answers
37 views

Need demo scripts and sample corrupted databases for corruption databases troubleshoot [on hold]

I am looking for a demo for recovery of corruption databases in different ways. Is there any link for those examples to practice in LAB machines.
0
votes
0answers
24 views

Query that shows name of excel spreadsheet that is calling the stored procedure

Does anyone know if there is a way to query sql server for the name of the excel spreadsheets in which a stored procedure is being called to produce the data?
4
votes
1answer
76 views

Copy Millions of Records from one table to another with the same schema, in the same database

I am trying to find the best way to copy a large amount of rows ( ~70million) from one table to another with the same schema inside the same database. Our system currently runs with a set of A/B ...
7
votes
3answers
552 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 ...
2
votes
1answer
877 views

SQL Server connection failed for domain user from Mac OS machine

I have SQL Server 2008 R2 SP1. I am using connection string to connect to SQL Server. In connection string I am passing the driver path, server name, TCP port number, user name with which I want to ...
0
votes
1answer
47 views

Migrate ssrs 2008 r2 to ssrs 2016

The current setup of the server is Windows Server 2008 sp1, with SQL Server Reporting Services 2008 r2 installed. The databases are on a separate server which is using SQL Server 2008 r2. I have a ...
3
votes
1answer
124 views

SQL Server 2008 R2 DB grows overnight and slow

I am running a SQL Server 2008 R2 SP2 DB. I have a DB that was running around 19 GB in size and three days ago grew to 34 for no good reason. We typically have about 100 MB growth in the DB every ...
3
votes
2answers
54 views

Can an SQL Job Deletes itself after its end date?

I have created a job which will sets up its start date and end date automatically with a dynamic value. For example: Job will run for eight days and at eighth day it needs to automatically deletes ...
1
vote
1answer
34 views

Use stored procedure to get minimum value from column and decrement it and other values

I'm trying to understand how the following algorithm could be completed in SQL 2008 R2. I have a table that contains production data. The table uses a "rolling" scheduling method, meaning that orders ...
5
votes
1answer
39 views

Recover Instance after Log File disk error

This is mainly theoretical, but I would like to have a documented list of options if this happens in the future. Today we had a critical disk error on the SAN, meaning the disk holding the ...
0
votes
1answer
27 views

Temporarily stop Log shipping [closed]

How can I temporarily stop log shipping --> Do the task --> Start the Log shipping ? Is it possible ?
6
votes
5answers
57k views

List all permissions for a given role?

I've searched around all over and haven't found a conclusive answer to this question. I need a script that can give ALL permissions for an associated role. Any thoughts, or is it even possible? ...
0
votes
1answer
18 views

The merge process could not connect to the Publisher 'Server:Reports'. Check to ensure that the server is running

Sql version:Microsoft SQL Server 2008 R2 Replication type: Merge Getting Below error in 'View Synchronization Status' window. The merge process could not connect to the Publisher 'Server:Reports'. ...
2
votes
1answer
150 views

Intermittent errors sending email from SQL Server Database Mail

I've been seeing some SMTP errors in my database mail logs. So I have a set up a test SQL Agent job that runs every ten minutes (it's only action is to SELECT NULL). It is set to send email ...
0
votes
1answer
25 views

SQL Server 2008 SSIS Packages on Windows 10 Machine? [closed]

I want to utilize Business Intelligence Development Studio (mainly SSIS) to populate a datamart that I have in a SQL Server 2008 R2 instance. My client has just upgraded all their laptops to Windows ...
0
votes
0answers
42 views

Selecting a View consumes 4GB memory but executing SQL statement of View only 70 MB

I am facing high memory consumption on my database server. During diagnosis, I found a view consuming 4 GB memory. But when I executed it's statement (select statement of view) it's taking only 70 MB. ...
0
votes
1answer
518 views

SSRS CROSS Domain Reports Server Authentication

I have a pretty complicated situation at hand, let me try to explain. I have a SQL Server and a Web Server in a separate domain (lets call it domain A). SQL Server has the databases for Reporting ...
5
votes
1answer
164 views

Cluster node upgrade of SQL Server 2008 R2 to SP3 fails when moving resource

I'm trying to update a cluster to SQL Server 2008 R2 SP3. I've installed SP3 on the passive node but when I failover the cluster it errors when running through it's upgrade scripts. The messages &...
0
votes
3answers
4k views

SQL Server Configuration Manager returns remote procedure call failed

I open the SQL Server Configuration Manager and it returns remote procedure call failed. This is Windows 7 Ultimate version, SQL Server 2008 R2 Express version with advanced services. Previously I ...
3
votes
7answers
2k views

How can I compare the schema of two databases?

Is there a way to find the differences in two SQL Server databases (schema only). One is local and the second is at a customers site. We are experiencing problems with crystal reports running some ...
0
votes
1answer
27 views
0
votes
0answers
31 views

sqlcmd returning literal 'NULL' for null columns when outputting to CSV

I'm using sqlcmd (SQL Server 2008 R2) to execute a simple query to output data to a .csv file. In the SQL file, I have the following code: USE [OSCAR_UK] SELECT DISTINCT ISNULL(LTRIM(RTRIM(il.[...
2
votes
1answer
2k views

SQL Server recommended MAXDOP settings for NUMA

I have an OLTP SQL Database running on SQL 2008 R2 Enterprise Edition that may be fairly busy (5000 batches/sec) in next couple of months after going live production. Looking into server configuration ...
3
votes
1answer
2k views

How to output more than 4000 characters in sqlcmd

I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this: restore database [DB] from disk='F:\FULL1.bak' with norecovery restore ...
0
votes
1answer
93 views

Delete missing database

Event Viewer, I can fins this event logged : 2016-07-10 11:45:01.83 Logon Error: 18456, Severity: 14, State: 38. 2016-07-10 11:45:01.83 Logon Login failed for user 'Domain\Username'....
2
votes
2answers
423 views

Stored procedure working when run manually, not running in SQL Agent Job

When I execute a Stored Procedure through a SQL Agent Job, I get the following error message: Executed as user: NT AUTHORITY\SYSTEM. XML parsing: line 10, character 33, unexpected end of ...
1
vote
0answers
17 views

Report Builder does not connect for a remote reporting Server

Current Environment I am in a bit of trouble here, I have a Reporting Services installed on one server and SQL Server database engine installed on another server. The users are connecting to ...
1
vote
3answers
2k views

Can SQL Agent job notifications be sent to one operator for success and another for failure?

We have a requirement that some people are sent notification on success, and other on failure. But the UI seems to only allow a combination of 1 operator + 1 condition. So I was wondering if we ...
3
votes
1answer
310 views

SQL query failing because of commented part of T-SQL query

I am executing below query but I am facing issue. Note :SQL 2008 R2 and SQL 2014 running with two diff serv accounts on Win 2008 R2 and Win 2012 R2. TCP Ports, Firewall ports are all enabled along ...
3
votes
2answers
893 views

SQL Server 2008 R2 Mirroring issues

I have this very frustrating problem that I've been trying to figure out for almost a week now. I have two SQL Server 2008 R2 STD w/SP3 box running on Windows 2012 R2 with two instances each, Server1 ...