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

learn more… | top users | synonyms (2)

0
votes
1answer
19 views

Foreign key not able to get one to many working

I want to have a one to many relation with player points but I need it in such a way that entity framework sees it as a collection so i can add to it. I mean that one player can have multiple points ...
1
vote
1answer
38 views

Active Directory group as a schema in SQL Server

In SQL Server 2008 R2 we have a database that is being maintained by a team of developers. Each of them should have permissions to perform DDL operations such as adding a column or creating a table. ...
1
vote
2answers
53 views

Parameters in Activity Monitor

To view "recent expensive queries" I use Activity Monitor. Some of the queries there are with parameters. Is there a way to know the values?
0
votes
1answer
39 views

How to pivot this unpivot?

This is the pivot I made: SELECT * FROM ( SELECT *-- codigo,ID,Grupo,convert(int,CodOpcao) as CodOpcao FROM ( SELECT * FROM ( SELECT 39 as codigo , ...
0
votes
1answer
36 views

How to get an exclusive connection in AutoRestore job ( sp_RestoreGene)?

I am using Paul Brewer's script for AutoRestore sp_RestoreGene on SQL Server 2008R2 box. I created the stored procedure dbo.sp_RestoreGene on the Server 1 where the backup file is located. I called ...
1
vote
1answer
68 views

SQL Server database has corruption, but displays no errors

We have an extensive database of tables. I moved over a table from our dev db to our production db using a program called SQL Compare. I just copy paste the SQL it produces into Management Studio and ...
2
votes
3answers
100 views

SQL Server Pagination using CTE

I have to apply paging on query as per requirement i need two extra columns other than query result 1. Total Records 2. Total Pages but i am trying to confirm that this the right way to retrieve ...
0
votes
1answer
37 views

Get SQL server instance installation date [duplicate]

I would like to know the date when the sql server installed on the system. I did try to look in %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\ directory but there are so many files and ...
0
votes
1answer
32 views

How to Override a column in table.*

select (column1+column2) AS column1, table1.* from table1 both column1 and column2 are columns in table1. Is there anyway that when I run the query I can get only the new column1 is shown and the ...
1
vote
3answers
94 views

Copy table structure excluding nullability into local temporary table

How can I copy the structure of a table into a temp table (or table variable) excluding the nullability of each column? Mainly I need to copy the column names and data types, but not nullability, ...
4
votes
3answers
684 views

Correct way to stop SQL Server (through automation)

Question Is it safe to start & stop SQL's services using PowerShell's Start-Service and Stop-Service commands? If not, is there a recommended way to start & stop SQL's services through ...
0
votes
0answers
17 views

Temporary migration to a new server for firmware upgrade

I need to temporarily migrate an SSRS deployment to a new server so that I can perform a firmware upgrade on the server. Once the firmware upgrade has been completed, I will move SSRS back to its ...
0
votes
2answers
33 views

Temporarily Archive Databases on SQL Server 2008 R2

I've got a client that has approximately 100 databases across 10 servers. We're attempting to create a standardized process for removing old and unnecessary databases. In the process, we'd like a step ...
3
votes
0answers
35 views

Influence of index rebuild tasks in tempdb on the transaction log?

I have read pretty much about index maintenance and all the pros/cons of reorg, rebuild, online, offline, TEMPDB and timing and right now I am kind of more confused than ever before... The goal is to ...
-1
votes
1answer
58 views

Error with Loop using Cursors - Invalid Object Name with Set = @

Working on a script to Loop through databases to query one table that each Identical Database has with cursors then return result set to table for each database. I think I have the structure for Set ...
0
votes
2answers
47 views

From SQL Server 2005 to SQL Server 2012

I need to convert some databases from SQL Server 2005 to 2012; I know that you must convert it into 2008 before, but I can't rely on a SQL Server 2008 Enterprise installation: is there a faster way ...
6
votes
1answer
142 views

Why is my SP_HELPINDEX showing a lot of indexes?

Using sp_helpindex My_table I have this list: But my table has only these indexes: Why is this happening? I'm using a query to search for "equal indexes" in a particular table. This query is ...
2
votes
2answers
77 views

DDL trigger permission Error

I have a two users, user1 and user2. User1 has sysadmin rights and I have created a database level trigger through sa. Now I am trying to create a table through user2 who has db-ddladmin rights and ...
1
vote
1answer
39 views

How to insert and update bulk data From one DB to another

Two DB on different server , I want to run procedure once in a day so that i can get updated record,if already exists then it should update else insert new So FAR I did like this : if not exists ...
0
votes
0answers
40 views

Issue with multiple execution of sys.sp_addextendedproperty

I'm using SQL Server 2008 R2. I need to store a text as a description of each table of my DB. But I don't want to do this with wizard. I handled to build some code for this purpose. Here's a part of ...
0
votes
2answers
51 views

Create View For All Table Names Listed In Table

I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure. I have a table that holds table names, I need to ...
0
votes
0answers
17 views

Why I don't see database engine tuning advisor option in SQL Server 2008 r2?

I see a couple of videos that this feature is available in SQL Server 2008 R2, so I go to management studio, write a query, select the query right click and I suppose to see this option, but I don't. ...
1
vote
0answers
62 views

Prdouction Database went into restoring mode

Yesterday one of my production database went into restoring mode,but fortunately i recovered it using below query USE MASTER EXEC SP_CONFIGURE 'Allow updates',1 RECONFIGURE WITH OVERRIDE GO EXEC ...
1
vote
1answer
50 views

How Merge Replication Works

I'm not DBA at all but I need to do something to synchronize four SQL server databases. I just discover there is a Merge Replication system in SQL Server 2008. At this moment I'm reading the ...
0
votes
2answers
33 views

What would cause an UPDATE statment to hang when executing from an .mdb file to SQL Server 2008 R2 over ODBC?

Is there anything that would cause an UPDATE statement INNER JOINed between a separate MS-Access linked table and an SQL Server 2008 R2 table to hang, when the values are being set on the SQL Server ...
1
vote
1answer
56 views

Really big LOG backups on a DB with Image field

Normally, our log backups has < 10MB size In a big database with a lot of transactions per hour ). But now we have a database, that is storing Images on a Image field. the problem is, the ...
-1
votes
2answers
229 views

Is there a way that I can merge the following update queries into one?

Is it possible to merge the following update queries into a single query? UPDATE tag.ItemTagValue SET [Value] = @firstNode WHERE FkTagID = 6 and TagIndex = 1 ...
2
votes
1answer
71 views

Are there undocumented Linked Server limitations?

I'm working on a SQL Server 2008 R2 SP1 with a Sage product registered as a linked server. There are a few stored procedures that execute a pass-through against the linked server. Once in a while, a ...
1
vote
1answer
37 views

Why does everyone want me to create an alert for severity 025?

Brent Ozar and others suggest to listen to severity 025 SQL-Server events. I searched for the meaning of all different high severity events, looked through different articles and read the most recent ...
0
votes
1answer
24 views

Index Rebuilding Log File Growth

I would like to start running regular index maintenance on a server that has been running for a while without it. The database is running in FULL recover with log shipping. The problem I am having is ...
0
votes
2answers
64 views

SQL Jobs logging only 2 days

When I righ click the job + view history, I can only see 2 days of logs. I'm sure this is a really noob question but I can't find the answer. I've been changing values but with no success. The same ...
2
votes
1answer
39 views

Strange Backup and Alerting Issue

I received alerts from one of my database servers over the weekend that was somewhat disconcerting. There was an alert for each single database within the maintenance plan. A full backup has never ...
0
votes
1answer
46 views

Finding Deleted / Updated record statement from SQL Server transaction Log backup file

One of my production server's data was updated / deleted by someone. I want to find out when the data was deleted or updated, and if it's updated/ deleted then through which statement it was updated ...
0
votes
1answer
30 views

Upgrading a big database inside a transaction

Assuming a big database, where normal upgrade (structural changes and data upgrade) currently takes about 2-3 days (not a issue, is not done during production and about 1-2 times per year. Currently, ...
4
votes
2answers
161 views

Can I restore all tables but not the stored procedures?

I have a testing database that I restore regularly from a production backup file in SQL Server 2008 R2 to get new data. We often edit\change or update the stored procedures in the testing DB to test ...
3
votes
0answers
83 views

Database corruption extends beyond nonclustered index issue in SQL Server

Background I have a server running SQL Server 2008 R2 on the matching MS OS. Over the last few months this server had a RAID drive failure on two separate occasions (which simply required hotswapping ...
2
votes
1answer
31 views

onsite and offsite backup question

We have an sql database of 165GB (includes filestreaming) and we do an onsite full backup every evening using veritas. We would like to also have an online backup in place just so that we don't need ...
4
votes
2answers
70 views

Does a covering nonclustered index help to answer Select * queries?

Our application developers run a lot of SELECT * queries against our database system, unfortunately. The missing index suggestions of sys.dm_db_missing_index contain a lot of cases that indicate to ...
0
votes
1answer
40 views

What are the low hanging fruits of a SQL Server 2014 migration?

I have read about new features and changes of the SQL Server 2014 version. But I still don't know the benefits every server will have by upgrading (from 2008 R2) to 2014? Are there any new features ...
0
votes
1answer
38 views

Moving MSDB database files fails

I'm trying to relocate system databases files. I managed to do that with the tempdb database, but I'm stuck with msdb. When I run: SELECT name, physical_name AS current_file_location FROM ...
-1
votes
1answer
30 views

I cannot add a new Server Role “SQL Server 2008 R2”

I want to add a new user-defined Server Role to my SQL Server instance, but the menu doesn't show all options, it only shows this: Instead of this : and I also tried the T-SQL command to create ...
0
votes
1answer
23 views

Upgrade from SQL Server 2008 R2 SP2 to SQL Server 2014 Error

I am upgrading from SQL Server 2008 R2 SP2 to SQL Server 2014 and receiving an error I cannot get past. Everything checks out before the upgrade runs. The upgrade stops at ...
1
vote
1answer
26 views

Function is counting -1 char ( wrong )

I have this function ( It counts how many characters I have in a certain string, separated by ,: ALTER FUNCTION [dbo].[fContaRespostas] ( @List VARCHAR(6000),@SplitOn VARCHAR(5),@Resposta ...
2
votes
1answer
24 views

Refreshing a table using CREATE TABLE, DROP and Rename

A read-only database (database1) is updated via log shipping every minute, then a procedure queries multiple tables in it and refreshes a large table on database2. For this process I run an ...
1
vote
0answers
28 views

Reasons why plans of sys.dm_exec_cached_plans do not match actual plans?

I re-assess index / query optimization and actually focus on key/bookmark lookpus that could be eliminated. Using a script of Jonathan Kehayas I go through the list of query plans that used those ...
2
votes
1answer
44 views

Why is Page Life Expectancy value sometimes exceptionally high (4,3 M)?

My value of PAGE LIFE EXPECTANCY usually is in a range of 3000 - 10000. But as you can see in the little data sample below, there are regular values of about 4,3 Milion. I get those values using ...
0
votes
1answer
54 views

Sub Query vs Table Filter

I have have been assigned to resolve Performance issue of a Stored Procedure. Select TableTwo.Column1, DatabaseTwo..TableThree.Column2, Sum(TableTwo.Column3) As Column3 --- Section 1 from ...
0
votes
1answer
50 views

How Do I Filter a Group of Rows based on one Column's Value in TSQL?

I am trying to make a SQL query in Server 2008 R2 that selects the rows only for employees who have over 40 hours in a pay week and a pay description that doesn't include "over time". So far I have ...
6
votes
1answer
179 views

Getting database sizes on an MSSQL Server named instance

I need to get a list of database sizes for a SQL server migration project. The SQL Server server we're migrating from has three installed instances, one default and and two named. It's running SQL ...
7
votes
2answers
123 views

How can I get individual rowcounts like SSMS?

I have a client c# program that is executing stored procedures via ExectueNonQuery, including catching the PRINT and Error output with InfoMessage events. It works fine, but I have noticed something ...