Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server-2008-r2]

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

2
votes
1answer
72 views

Refactoring subquery to JOIN and CROSS APPLY, get only row for each record in parent table

Given the following query: SELECT p.ProductName, CASE WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1) THEN 1 ELSE 0 END ...
-2
votes
0answers
34 views

how to employee leave application days mentioned in attendance table? [on hold]

I am first time working on employee leaves management system.so need help that if employee take leave from 01-01-2019 to 05-01-2019 means 5 days then how these days will mentioned in attendance ...
2
votes
2answers
71 views

Enlighten me about INCLUDE on indexes

Well, After reading Erik Darling's post Filtered Indexes: Just Add Includes I'm really curious and sad about how I use INCLUDES: He gives some examples and etc. about INCLUDE, but I'm confused about ...
1
vote
2answers
51 views

Recovering a SQL Server database

In a series of unfortunate mistakes, I managed to lose 4 months of data and rapid database modifications. I’m not a database administrator, I’m a programmer that also designs and maintains a database. ...
1
vote
0answers
19 views

MS SQL Server 2008 R2 duplicate key row in object 'sys.syscommittab'

I have a server that is failing to backup due to an error with the syscommittab and have gone through everything in this question: SQL Server 2008 unable to backup due to Failed to flush the commit ...
1
vote
1answer
77 views

2 CTES to insert into a different #temp , and then executing a variable

I have this simple query that I'm creating just to test some backups ( full and diff and I'm going to improve it later. it's just a test): -------------------------------------------------------------...
7
votes
4answers
971 views

What is the best way to rebuild a date from integer inputs?

I've got a bunch of financial reports, and we want to be able to pass them two inputs (year and quarter) as variables. I'm doing it this way, but I really don't like it: declare @quarter int, ...
-3
votes
1answer
56 views

How to update with join query and case when condition?

below query is just updating INTIME and OUTTIME and rest of case when condition is not getting calculating as per case when condition. Update EmployeesAttendance set EmployeesAttendance.INTIME=@...
-3
votes
0answers
40 views

Select Distinct or without duplicated records by specific column using aggregate

I have below query, which produced the result with duplicated [SourceGLId] with Curr as (select CurrencyCodeFrom, MidRevalRate from [dbo].[v_SourceCurrencyBS] where SourceCurrencyID like concat('BS:',...
0
votes
2answers
32 views

Full Migration 2008R2 -> 2016

We currently have Windows Server 2008 with MS/SQL 2008R2: the default instance and two named instances. We are spinning up a Windows 2016 Server onto which we will install MS/SQL Server 2016 Standard ...
1
vote
0answers
64 views

Query performance of searching pattern

Posted the same question on SO, but did not get answer. As per Andrey Nikolov comment, tried with Trigram, but the table name and column(more than 50 columns) are dynamic i mean will get from front ...
0
votes
0answers
34 views

DB Restore Agent Job Fails After “..running the upgrade from version 695 to version 696.”

Recently I have started to see an intermittent error when trying to restore database 'X' via an agent job. The job runs a T-SQL script that restores a database from a 2008 R2 (production) instance to ...
1
vote
0answers
52 views

MS SQL, fastest way to delete 99% of rows from 100+ million rows table to deduplicate data

Due to an error in our application, one of the tables in our MS SQL has been flooded with duplicate rows differing only in PK identity column and timestamp +--------+---------------------+------------...
0
votes
1answer
55 views

How to remove unnecessary symbol in the column in SQL Server 2008

my database got some data with unnecessary symbol in front it like "" How do I remove the front symbol. I try to do like this but it do not work. SELECT DISTINCT REPLACE(NEW,'.','') FROM [FixedAsset ...
0
votes
1answer
28 views

Will a SQL Server 2008 R2 MDF file work on SQL Server 2016 with Windows Server 2016?

We release our database as a .MDF file taken from a SQL Server 2008 R2 server. I have a client who is planning to move to SQL Server 2016 on a Windows OS 2016 and is asking if our .MDF can be restored ...
0
votes
1answer
22 views

Permanently delete SQL server database - anti recovery

I rent a server from a server provider. And one day I'm no longer rent it. I have some important database on the server need to wipe out so no one can see it. Is that safe to just right click on the ...
4
votes
3answers
383 views

Does Compatibility Level allow unsupported functions?

I've got a SQL Server 2016 instance with a database on it, running with a compatibility level of 2008. However, the following dummy proc creates, and executes just fine. CREATE PROC usp_TestFormat AS ...
0
votes
2answers
42 views

Will TDE encryption protect my SQL database from hacker in this scenario

I'm a bit understand that TDE encryption protect the database from being stolen and read by a hacker. However, I don't understand if it can help me in this scenario: If a hacker got into my server and ...
2
votes
1answer
86 views

what does a “/” mean after a sql statement

I have a script that we are using to update our ERP database to a new version, UPDATE ENUM_CODES SET COLUMN_NAME = 'PAY_STATUS' WHERE TABLE_NAME = 'PAYABLE' and COLUMN_NAME = 'STATUS' / but i dont ...
2
votes
1answer
63 views

Best way to compare multiple non null/non zero fields in a row in T-SQL?

I need to compare figures across a row and show where there is any mismatch, excluding zero and null values. So in the example, ids 1, 4 and 5 would be returned. What is the most painless way of doing ...
1
vote
1answer
62 views

Whats the term in Oracle, when I'm speaking about SCHEMAS on SQL Server

Here, we have our applications developed both for oracle and SQL Server. Devs are trying to fix their own way to be organized, using VW on views, PR on procedures and etc. they told me that they're ...
0
votes
1answer
49 views

Stored Procedure infinite looping after index updates

I have inherited a very old and poorly written stored procedure with nested fetch statements. This code processes a large amount of data by copying from one database into another. This process was ...
0
votes
1answer
24 views

Get the marketdata based on present date and previous date

Table prices: ID Date OPEN HIGH Low CLOSE 417 9/23/1994 24.399 24.399 24.399 24.399 417 9/28/1994 23.3 23.3 23.3 23.3 417 9/29/1994 23.35 23.35 23.35 ...
0
votes
1answer
21 views

How do you delete an aliased user

I am trying to delete an aliased user from a sql server 2000 database that was restored to sql server 2008 r2. Originally, I was attempting to convert the aliased user to a regular user, but it does ...
1
vote
1answer
36 views

DBCC SHRINKFILE performance

I have a production SQL Server 2008 with 8 TB spread over several databases. I've deleted about 85% of the data in each database and MUST recover the disk space. The final SUM of all USED_SPACE will ...
2
votes
1answer
34 views

SQL Server 2008 Version of rows between… preceding and current row

Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008 As you can see, The SUM column for July 2017 for example is equal to the following: August to Dec 2016 + ...
1
vote
1answer
49 views

Inefficient Indexes on Primary Keys

So I was cleaning some indexes,with some queries I made, and some that I found over the internet with ineficient indexes ( lots of updates and no seeks, scans, etc.) But something that I'm not sure ...
1
vote
1answer
26 views

Validity period of an open ssrs report

We have SSRS 2008 r2 rdls. I open an SSRS report and keep it open in my browser for more than 30 mins. If I now click any drill downs either through an action or go to report. I don't want the report ...
1
vote
1answer
39 views

What does WITH FORMAT do on a DIFFERENTIAL backup?

I could find a lot of questions about this. This is a really good question, exactly what i'm asking here. Down there, a guys asks the question with: you can have compressed and uncompressed on the ...
2
votes
1answer
36 views

How to execute a query based on the condition if a column exists?

I want to run the following query: CREATE TABLE dummy AS SELECT x, y, z FROM table1 WHERE y!=0 then I want to do the following: if z column has a non-null entry do: 1. copy the content of z into A ...
2
votes
1answer
64 views

Is database partitioning only created to get a better performance? and why should one partition a table, and a database? [closed]

I was asked if SQL Server can handle huge databases ( for them a huge database is 100GB). I know it can. I worked with 4tB databases already. But I would like to ask, thinking about really huge ...
1
vote
1answer
103 views

How to set the first row of CSV data as column name in SQL Server?

I am using BULK INSERT to read a csv file in SQL Server. Is there a way to keep the first row as column name while I am reading the file? If not, after reading the data from the csv (from second row),...
0
votes
0answers
51 views

SQL Server 2008 R2 SSRS Failure sending mail: At least one of the From or Sender fields is required, and neither was found

I am trying to set up a subscription for a SSRS report in SQL Server 2008 R2 and I am getting the following message: Failure sending mail: At least one of the From or Sender fields is required, and ...
1
vote
0answers
34 views

fn_trace_gettable empty default trace rollover files

For some time now I've been using fn_trace_gettable to automatically collect and report certain events on the server. Function is being called with following parameters: ::fn_trace_gettable('C:\...
0
votes
0answers
34 views

Linked Server Provider Options - Do I need to restart the SQL service (or server) after modifying any of these options?

Under Server Options -> Linked Server there are the Providers that each of my linked servers use. If I right click on a specific Provider and modify the Provider Options, such as "Dynamic parameter" ...
-2
votes
1answer
37 views

Query for max CPU, Memory and System Queue length

I am looking for a query to find maximum CPU, Memory and System Queue length for last month. Any help would be greatly appreciated.
0
votes
1answer
45 views

SP_WhoIsActive - RESOURCE_SEMAPHORE_QUERY_COMPILE

Had an issue with one of our production servers earlier today. Many transactions were suspended with RESOURCE_SEMAPHORE_QUERY_COMPILE wait. Have sp_whoisactive data logged to table every minute. Below ...
2
votes
0answers
30 views

Query involving TVF very slow on SQL Server 2008

I have a query that runs in under 1 second on SQL Server 2014. A similar query takes over 12 minutes when run against a table on a linked server running SQL Server 2008. I don't know if it's because ...
0
votes
1answer
27 views

Really Small details about storage ( data size ) on columns

I'm doing tests to better understand those minimal technical details about tables, pages, and etc. I'm using sp_spaceused to better understand this with INT values ( I will do a lot of more tests ...
0
votes
1answer
31 views

How can I get the last second of the last month as a default parameter value?

I am trying to get the last second of the last month as a default value for a parameter. I can get the last day of the last month from this query on social.msdn.microsoft.com last month last day ...
0
votes
1answer
37 views

SQL Server 2008 R2 Auditing

I am new to database administration, and I need to implement auditing of all users of multiple databases. Can we audit multiple users of a multiple database by creating just one audit specification? ...
0
votes
1answer
49 views

why when I change a column from DATETIME to DATE, I can't see any storage improvement?

Long story short. I have a table with more than 10millions rows with a datetime field. WE can change that to DATE ( because date uses 3bytes, and datetime 8). I thought if I: alter table table1 ...
1
vote
2answers
34 views

SQL Server 2008 R2 - How does the SQL Trigger actually work in this case?

I have a table named _Test which has the following values stored in it. Primary Key : Id I also have designed a trigger on that table ALTER Trigger [dbo].[_Test_Data_Save] On [dbo].[_Test] For ...
0
votes
0answers
48 views

Cleaning up the environment on old cluster after migrating to newer SQL Server cluster

We migrated many instances of SQL Server 2008/R2 on Windows 2008R2 and 2012 on Windows 2012 to newer Windows 2016 with SQL Server 2014 and 2016 versions. The old clusters were configured with either ...
1
vote
1answer
33 views

Is there a way to find all Logins, and all database users this login have?

I'm trying to join, every login and permissions tables. Looking over the internet but I just can't find something to list like: LOGIN | USER | DATABASE_THE_USER_HAS_ACCESS This is because I would ...
0
votes
1answer
45 views

Do I need to detach a database to attach it?

I am trying to move data from one SQL Server server to another (located on another computer). I know of the detach/attach method, but I don't want to disturb the prod environment (detaching a prod ...
0
votes
0answers
20 views

Snapshot replication from SQL Server 2012 to SQL Server 2008R2

We are using snapshot replication as a standby to replicate prod data for a month from 2012 to 2008R2 after migration from 8R2 to 2012, just as a rollback process. Using snapshot replication for 10 ...
0
votes
1answer
72 views

Does the Fill Factor setting affect heaps / heap tables with non-clustered indexes?

I came across this old article on Brent Ozar's blog (written by Kendra Little) regarding Fill Factor: https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/ In it, she briefly ...
1
vote
1answer
314 views

GRANT select on function FN_DUMP_DBLOG in SQL Server without GRANTing sysadmin

I'm trying to GRANT permission for an SQL login to access the functions fn_db_log and fn_dump_dblog but GRANTing SELECT on the function still fails with error: Msg 9010, Level 14, State 1, Line 3 ...
1
vote
2answers
72 views

Does SQL Server automatically update the foreign key relationships between tables when one of those tables is renamed?

What happens to the foreign key relationships on a table if that table is renamed?