This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx

learn more… | top users | synonyms (2)

6
votes
2answers
135 views

FOR XML could not serialize the data because it contains character (0x0000)

I have a big query (if necessary I will post it here) and I'm getting this error: Msg 6841, Level 16, State 1, Line 1 FOR XML could not serialize the data for node 'NoName' because it contains ...
3
votes
0answers
44 views

SQL Job Step created via wizard does not correctly set server & database_name values

I've recently been tasked with migrating a database from SQL Server 2005 to 2008R2. This has all been accomplished without any issues, but one part of the system requires a Snapshot Replication to be ...
4
votes
1answer
43 views

How to increase performance in Cross Database operations?

We currently have a SQL Server 2008R2 being accessed by a legacy ASP application. Among different processes, one of them essentially SELECTs data from one database and INSERTs (or UPDATEs) them into ...
4
votes
1answer
47 views

SQL Server 2008 R2 : LDF file size not increasing

I am using SQL Server 2008 R2. I have a table statDB of 100 GB in primary filegroup. I have created a secondary filegroup in same database (Lab1) and created a table copyStatDB. Now I start copy ...
4
votes
4answers
538 views

SQL Server - Alter table vs drop and create

Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are ...
1
vote
1answer
68 views

Is it Possible that a variable name changes in a stored procedure after a restore?

I often restore my Testing database from Production db, but this time I noticed something odd: we have a stored procedure called (USP_AddTS), one of its variable names have slightly a different name ...
1
vote
0answers
40 views

Could not redo log record on mirror

One of my database mirrors is in the suspended state today and the error log on the mirror shows the following. Could not redo log record (338934:102862:477), for transaction ID (0:848150408), ...
0
votes
0answers
34 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 ...
1
vote
1answer
18 views

Recovering credential data from a master DB backup

On a SQL 2008 R2 instance, a misconfigured credential used as a SSIS proxy for an SSA job was dropped before I could look at the system views to figure out when it was originally misconfigured. I ...
0
votes
0answers
26 views

How to connect SQL SERVER 2008 R2 as a server client architecture using vb.net 2010? [closed]

I want to store the database in a server machine and other clients machines will be access this machine via LAN. So, how to configure the SQL SERVER 2008 R2 in Server machine, and how to access it ...
1
vote
1answer
75 views

select query on Temp table is not returning data in correct order even after specifying index hint [duplicate]

In my code, I am creating a temp table using select * into #tempTable. Temp table contains the huge number of records and I need to retrieve data from it many times in my code so I created a ...
1
vote
1answer
103 views

Oracle 12c Client to 10g Server (OPENQUERY)

Scenario: We currently have a SQL 2008 R2 server (with Oracle 10g Client) using OPENQUERY to return data from an Oracle 10g database. Problem: We would like to upgrade to SQL Server 2014 SP1 (with ...
0
votes
2answers
32 views

SSMS fail to connect localhost SQL Server 2008 R2 suddenly [closed]

Until today when accessed by remote desktop to my sql server could connect using localhost, but suddenly I cant (without any update or change in my server), now I can connect using localhost\instance ...
1
vote
0answers
17 views

Transactional publication and database mirroring considerations

I've recently taken over a project, and with it comes 2 backends. 1st is the production services backend and the 2nd is a reporting service backend. Both of which are 2008r2 instances. The production ...
3
votes
1answer
123 views

Hiding a SQL Server vs Turning Off SQL Browser

Can anyone explain the difference between turning off the SQL Browser service and Hiding an instance through the SQL Server Configuration Manager? I've always assumed they do the same thing and have ...
3
votes
1answer
24 views

SQL Server 2008 R2 lack of space?

I have upgraded from SQL Server 2005 to SQL Server 2008 R2 because my database was 4G and needed extra space. I though SQL Server 2008 R2 has a limit of 10Gigs hence I would have 6 Gigs free, however ...
2
votes
1answer
79 views

Running SSRS and SSAS from different server than the primary database instance(SQL Server Engine)

Right now both SSRS, SSAS and SQL Server Engine are on the same server. We are using 2008R2 Enterprise. We are thinking to upgrade to SQL Server 2012 or 2014. and We want put on separate serve the ...
0
votes
0answers
41 views

Property error when linking SQL Servers

I'm running into an issue linking SQL servers. The servers I have are: Server A - SQL Server 2008 R2 Server B - SQL Server 2014 I can set B as a linked server on A with no problem, but setting up A ...
1
vote
1answer
86 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 ...
1
vote
3answers
58 views

Installing SQL server 2008R2/2012 on windows server 2012 box

we are trying to install SQL2k8R2 and SQL server 2012(Standard edition) on the windows server 2012 box (Standard 64 Bit). For SQL 2k8r2, i used slipstream method so as to bring set up to atleast SP1 ...
4
votes
2answers
183 views

Does .bak file also contain backup of ldf file?

I am running MS SQL Server, which is holding mdf and ldf files of sizes 110 GB and 245 GB respectively. My question is, if I take .bak backup of said database, will it result in 355 GB (110+245) ...
0
votes
0answers
24 views

Cannot upgrade from SQL Server 2008 R2 SP2 standard edition to SQL Server 2014

When I run the upgrade wizard, it says "the specified edition upgrade is not supported". I have SQL Server 2008 R2 standard edition (SP2) and it has been added in the supported list on MSDN website. ...
2
votes
1answer
28 views

Pivots giving too many results

I'm trying to learn how this pivot thing works. I don't think I'm understanding it quite right. I just want to do a simple count for a bunch of item types that have some statuses. The table I'm ...
2
votes
1answer
128 views

stored procedure not showing a result

If I run the query outside the stored procedure, I get results, but I only get the column names as the result after executing the query - even though return value is 0. Any help would be appreciated. ...
-3
votes
1answer
48 views

SQL Server questions (for masters) [closed]

How to rename sa to new_sa in SQL Server 2000? How to do and what is the impact SA rename impact in SQL Server 2005, 2008, 2008R2, 2012, 2014, Express Edition How to recreate sa login if deleted How ...
1
vote
1answer
41 views

SET ANSI_NULLS at the database level versus setting it at the table level

I have generated schema-only script for my sql server 2008 R2, here is part of it:- USE [master] GO /****** Object: Database [****] Script Date: 03/25/2015 12:17:09 ******/ CREATE DATABASE [****] ...
0
votes
1answer
23 views

Do i need “CHECK CONSTRAINT” when i am using “WITH CHECK ADD CONSTRAINT”

I am working on SQL Server 2008 R2, where I generated a schema-only database script using SQL Server Management Studio. The generated script is as follows: ALTER TABLE [dbo].[ConsoleServer] WITH ...
0
votes
2answers
65 views

how to create trigger in SQL server using two tables to find stock quantity

I have two table sales and purchase. I want to create a trigger which stores final quantity in stock table by subtracting sales table sold quantity from purchase table. There is kikkat as a name n ...
1
vote
1answer
50 views

What is the best SQL Server field type to cast to when storing a UNC path stored in a sql_variant field?

What is the best choice of field type to cast to when storing a UNC path in a sql_variant in Microsoft SQL Server 2008? It appears that I could store up to 4000 characters according to Microsoft ...
1
vote
0answers
46 views

Moved tempdb, now File 'tempdev2' does not exist

I moved the tempdb first locating the files: use master select name, physical_name from sys.master_files where database_id = DB_ID('tempdb'); GO then I moved each file: use master go alter ...
2
votes
1answer
75 views

SQL Server 2008 Edition Upgrade Enterprise Evaluation to Standard problem

On my Windows Server 2012 machine SQL Server 2008 evaluation time has expired. After Edition Upgrade nothing happens, SQL Service doesn't start. In service log still is information about expired ...
4
votes
1answer
56 views

What started this SQL Server job?

I'm looking at a SQL Server environment in which jobs start in several different ways. One way is from a job running on a different SQL Server. So, for example, "SQL Server A" runs a job named ...
-3
votes
1answer
23 views

Implication of owned schemas for a user having db_owner privilege

Importance of "Schema owned by the user"? The user is db_owner for the db. Is it required to select owned schemas?, or else it will own all the schemas as it is a db_owner? Please provide me a detail ...
1
vote
1answer
51 views

Simple SSIS Package Failed User does not exist or has not permission

I created and build SSIS Package with "Transfer SQL Server Objects Task". I Copied the package.dtsx to network folder with permissions set to everyone. I created the SSIS Job where i set path ...
3
votes
1answer
136 views

Unable to shrink log file due to snapshot replication

Our database log (SQL Server 2008 R2) is growing too big, and truncating it via DBCC SHRINKFILE doesn't work (the database is set to simple recovery mode). A bit of background. I released some ...
9
votes
2answers
161 views

SARG cardinality estimate, why not full-scan?

Why there is no full-scan (On SQL 2008 R2 and 2012)? Test data: DROP TABLE dbo.TestTable GO CREATE TABLE dbo.TestTable ( TestTableID INT IDENTITY PRIMARY KEY, VeryRandomText VarChar(50), ...
3
votes
1answer
55 views

Unable to change database owner to a user that is already mapped as user

I ran into this problem. Trying to change a database owner to acct1; however, I get the following error message when doing so: The proposed new database owner is already a user in the database ...
9
votes
2answers
122 views

SQL Server DB becomes unusable overnight

Yesterday, my SQL Server database was fine. Today it is almost unusable - it's slowed down by a factor of between five to twenty, depending on when I hit it. Some data was added to the server in an ...
-2
votes
1answer
42 views

Recursive cte addition in SQL

I need to add the total value to the parent nodes.(totaling the total value to base to top levels). 118 records are there and 1-8 levels. Root ACCCODE='00001' Root ACCPARENTCODE='0' Eg: adding ...
-1
votes
2answers
33 views

How to reduce database log file size frequently? [duplicate]

I want to reduce the transaction log file size of my Database frequently. If I use transactional log backup frequently for example every 1 hour will it reduce the database log size or not? Does ...
2
votes
1answer
54 views

Searching SSIS packages for the use of specific tables

I have a number of SSIS packages, how can I search them to find the use of specific tables? I need to make tables changes and I am trying to assess the impact of these.
1
vote
1answer
50 views

SQL Server 2008 R2 copy Schema with all user security permissions

I have a prod support request from a high value user to adjust the settings on a company-wide dashboard so that he can manage a section of his workflow currently being excluded from these reports. ...
1
vote
1answer
112 views

What is the best option to easily fix fragmented heap indexes?

I have SQL Server 2008 R2 SP2 and also SQL Server 2012 SP2 on these servers. I have a database in which there is a lot of heap indexes which are more than 90% fragmented. What is the best option ...
0
votes
0answers
27 views

Re-installing SQL Server 2008 R2

I uninstalled SQL Server 2008 R2 and I am trying to re-install. I get this error. Here is the part of the Detail Landing Page.txt: 2015-06-26 13:07:42 Slp: Running Action: ...
0
votes
0answers
49 views

SQL Server Management Studio greyed out

We have an issue where Object Explorer will not display. When we choose View > Object Explorer, New Query, or press F8, we get: A generic error occured in GDI+.
0
votes
0answers
27 views

SSMS SQL Server 2014 - Selecting BackupSets for a Restore takes a long time

I'm having an issue with SSMS SQL Server 2014 (before and after SP1). When I go and select 'restore database' from the server and select a database, it just sits there and spin and spins. More than ...
3
votes
1answer
173 views

Alternatives to SQL Server TDE

Due to the high cost of SQL Server Enterprise Edition which includes the Transparent Data Encryption feature I am looking for an alternative product and have only found a couple of options: ...
1
vote
2answers
145 views

How get the sum of two tables value using inner join

There are there tables the below image. My question is retrieve from each customers and get the sum of Invoice total due, Invoice Total balance in invoice table and return cheque total due and ...
1
vote
1answer
94 views

SQL errors Microsoft.ACE.OLEDB.12.0 on dev box, but works on server

I'm inserting into a .xls spreadsheet from TSql code. Runs fine as an Agent job and on command from the server, but not on my dev box. Both complex and simple commands like: Insert into ...
4
votes
1answer
76 views

Understanding DBCC CHECKDB, Snapshots, and SQL Server Standard Edition

Understanding the circumstances under which SQL Server creates a snapshot when running CHECKDB, I followed a link to this KB that states: Database snapshots are available only in SQL Server ...