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

learn more… | top users | synonyms (2)

0
votes
0answers
20 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
2answers
26 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 ...
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 ...
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 ...
1
vote
0answers
28 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 ...
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.
1
vote
5answers
84 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 ...
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 ...
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
0answers
25 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?
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.
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 ...
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 ...
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 ?
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 ...
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
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 ...
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'. ...
0
votes
1answer
27 views

Change the Hosts files on both SQL nodes to show 127.0.0.1 LocalHost

How to do this ? Change the Hosts files on both SQL nodes to show 127.0.0.1 LocalHost
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.[...
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 ...
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'....
0
votes
1answer
43 views

Improve SQL query performance, need help

I don't see any missing indexes on the execution plan apart from Hash Match, Parallelism and Index Scan. I get 218,673 rows in 5 minutes. Planning the improve the performance. Here is the sql query, ...
1
vote
1answer
59 views

BCP utility:“Copy direction must be either 'in', 'out' or 'format'”

I moved a SQL Agent job from a SQL Server 2008 R2 instance to a 2016 instance. The job called a PowerShell script which in turn called bcp.exe. After moving the job, I receive this error: Copy ...
2
votes
0answers
24 views

Is it possible to deny RESTORE permissions to login with db_owner role membership?

Is it possible to deny RESTORE permissions to login with db_owner role membership? Is dbcreator required to RESTORE over an existing DB with a BACKUP of that DB? Or is that only needed when creating a ...
0
votes
0answers
94 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,...
0
votes
0answers
36 views

Shrink .ldf file issue

These are the timing of some jobs on SQL-Server: rebuild indexing at 10.30 am (all timing in IST) reorg indexing at 11 am full backup at 12.30 pm transaction backup every 2 hours truncate log ...
0
votes
1answer
16 views

Query to map a particular database to a syslogin

I need to map a database to a syslogin. Following is the query I have used to create login and make it a db_owner but after the execution, I can see a tick as db_owner for that database in ...
3
votes
0answers
44 views

ODBC Connections locking tables in SQL Server

We have an ERP system that is a little less than modern, the backend is currently sitting on a SQL Server 2008 R2 installation. We have through mandate of management (isn't that always the case) the ...
0
votes
1answer
115 views

How to Insert all PDF files from a folder into a varbinary(max) column in SQL using a Query

I am trying to create a query script for SQL Server that will convert pdf file into varbinary data and store it into the SQL Table column which is varbinary(max). I also have to make sure it loops so ...
3
votes
1answer
23 views

How do I uninstall only SSMS and Profiler for 2008R2?

I'm running SQL Server 2008R2 locally on my dev workstation, and I use SSMS and Profiler quite often. I recently installed the July 2016 version of those tools, and I'd like to remove the 2008R2 ...
1
vote
1answer
46 views

sp_help with different object types with the same name

I have a table with the same name as a user-defined data type (UDDT), and when I use sp_help, only the table definition is shown. Is there a way to use sp_help or an equivalent to show the ...
0
votes
0answers
28 views

NULL value in query_plan column from the sys.dm_exec_query_plan DMV [duplicate]

I've written a query to return data from the sys.dm_exec_query_plan DMV, but for a specific stored procedure the query_plan column which should contain a link to the XML version of the plan is NULL. ...
1
vote
0answers
42 views

Query to find plan details for a specific stored procedure, searching by name

I'm trying to write a query to find information about query stats and the query plan for a specific stored procedure, but I can't manage to find the right DMV or query to look for a specific stored ...
2
votes
1answer
42 views

Problem with a partitioning switch script

Working on some dynamic SQL to create a 13 month rolling partitioned table. See here for some background info. The dynamic SQL part is simple enough, and the first partition switches out without a ...
-4
votes
0answers
39 views

Find out if there is actual CPU pressure on SQL server machine

I'm trying to find out if there is a cpu pressure or threadcount issue on the SQL server instance. Environment overview: Windows 2008 r2 cluster SQL Server 2008 R2 Standard edition Max memory 64GB ...
0
votes
0answers
25 views

Replication Malfunctioned - Distribution Missing

I have configured the Replication in SQL Server 2008 R2 among 3 instances. Let's say INSTANCE1 as Publisher, INSTANCE2 as Distributor and INSTANCE3 as Subscriber. All the three instances are in ...
0
votes
0answers
25 views

Tlog restore keeps failing, now states “restoring”, normally completes after restoring last file, this time no luck, any thoughts?

I am hoping you can help me out!! Our restore keeps failing with the error message: Message Executed as user: NT AUTHORITY\SYSTEM. ALTER DATABASE is not permitted while a database is in the ...
0
votes
2answers
21 views

Set Login User as database Onwer

What's the query to set a login user as db_owner during the creation of database. Following is the set of queries, which I'm using right now, Create database Test1; ALTER AUTHORIZATION ON DATABASE::...
-1
votes
0answers
26 views

Create Login on SQL Server 2008 R2

I want to create a login from windows and also set network location. Same like we do in UI i.e. click on search button select location and then add login. Currently, I'm using the following code ...
3
votes
1answer
63 views

DBCC IND not working

I'm trying to get the pages used by a clustered index on table1 in a SQL2008 R2 DEV box using the below query. dbcc traceon(3604, -1) go dbcc ind (0, 'dbo.table1', 1) go but it still displays ...
2
votes
2answers
308 views

Recommended memory configuration for many SQL Server instances per server

I am currently managing a server cluster for a SaaS system. We currently have 6 SQL servers running MSSQL 2008 R2. Each server is a quad core virtual machine with 16GB of RAM provisioned on a server ...
1
vote
1answer
45 views

Installing SQL Server version 10.50.4000 is failing

I am attempting to install SQL Server 2008 R2 version 10.50.4000 on a virtual machine running Microsoft Windows Server 2008 R2. When I try to install SQL Server, I get an error that, when I look it ...
0
votes
0answers
56 views

Why can't a user in a group with the sysadmin role see any stored procs in SSMS?

A developer who is in a "Developers" group granted the server fixed role "sysadmin" on the development SQL Server installation is unable to see any stored procedures in a database - only the "System ...
-1
votes
1answer
51 views

RESTORE DATABASE execution continues restore completed

Today, I faced a strange problem which didn't cause any error or downtime. I executed a query to restore a database. RESTORE DATABASE Database_Name FROM DISK='PATH\FILE1.BAK', ...
0
votes
1answer
36 views

sqlserver 2008r2 service pack incompletely applied on shared features

I have installed sqlserver 2008R2 service pack 3 on my instance. The installation went through successfully but the instance still shows version number of SP1 in management studio. When I start the ...