Stack Exchange Network

Stack Exchange network consists of 175 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.

0
votes
1answer
20 views

List of databases which a user can access in SQL 2008 R2

Is it possible to create a list of all the databases where a specific user can access? For example, My user is 'Domain\MarySmith'. This user can access on a single database or can access many ...
0
votes
1answer
124 views

SQL PRINT vs SQL EXEC

When executing the query below, if I use PRINT it prints correctly. I can copy and paste the printed code and execute it. However, if I use EXEC I get the following error: Is there a way of ...
0
votes
3answers
42 views

Simple way to use SP_EXECUTESQL for loop through all databases

I have a BIG problem running queries on all databases. I have a lot of scripts that works, but I would like to understand better how to use SP_EXECUTESQL instead of SP_MSFOREACHDB. Normally when I'm ...
2
votes
3answers
120 views

How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

Weird question?...maybe, but I have a need. :) I have a stored procedure that I want to use universally in any database. The stored procedure generates some dynamic SQL and then executes that SQL in ...
0
votes
1answer
34 views

Sql Server Database 2008 R2 is in recovery mode still after 16 hours

Sql Server 2008 R2 Database is in recovery mode still after 16 hours for one of the database which is is Simple Recovery model. The data file is 200 GB and Log file 90 GB. We have to do Crash ...
5
votes
2answers
480 views

Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?

If I size my log files to evenly split the entire drive they reside on, leaving no extra space available, will log backups still be able to occur successfully? I have several databases, with one log ...
0
votes
2answers
34 views

There's a SQL trace file being populated every day. How can I find the routine running the trace file populating it with data?

So I found a strange folder inside my SQL Server. It's called Analysis and most of its files was not updated since 2015. but there are 2 trace files being updated daily. theres some useless ...
0
votes
1answer
50 views

Using IF clause or while clause in OUTER APPLY

I want to select dates between @startdate and @enddate but I get an error when a month doesn't have 31 days. DECLARE @StartDate date, @EndDate date set @StartDate = '2019-04-01' set @EndDate = '2019-...
3
votes
4answers
78 views

Creating temporary table dynamically using condition

I am trying to create a temporary table dynamically using the below code, and I know we can't have two create statements for the same table. Is there a workaround for this to put it in a conditional ...
4
votes
1answer
161 views

Does restoring a database break external synonyms targeting objects in that database?

If one has a database where synonyms in another database refer to objects in that database, does restoring a backup into that database invalidate the synonyms? To be specific, imagine this situation: ...
0
votes
2answers
51 views

Subtracting sum of previous rows

I want to create total field in this table. But in SQL server 2008 can't use 'order by' in OVER clause. I have same PO and difference INV. I want to Qty field minus Quantity field and next row Total ...
4
votes
1answer
48 views

What's the values for the Freq_Interval column in MSDB.dbo.SysSchedules when Freq_Type is weekly and more than one day is selected in the schedule?

I'm reading up on the SysSchedules table in the Microsoft API, and am trying to incorporate using this table in a query. In the documentation, the Freq_Interval column seems to be defined out for ...
3
votes
1answer
33 views

Does the SQL Server generate the contents for a derived table contents for every matching record in a join

I am on SQL Server 2008 R2. I am executing the following SQL a use tempdb go create table t1 ( c1 int, c2 int, c3 uniqueidentifier null) go insert t1(c1,c2) values (1,2),(1,3) go update t1 set c2 = ...
0
votes
0answers
28 views

Is there a “@SkipChecks…” parameter equivalent for sp_BlitzFirst and sp_BlitzCache?

Silly question, how do I skip over certain databases when running sp_BlitzFirst and sp_BlitzCache? I'm on SQL Server 2008 R2 - Enterprise (10.50.4042.0). (I'm not sure how to get the First Responder ...
1
vote
1answer
40 views

Forced Parameterization and CASE statements

We have an application which uses an ORM which does not (easily) support parameterized queries. To combat this, I have enabled Forced Parameterization on our database (SQL2008 R2) in order to avoid ...
1
vote
1answer
44 views

Convert SQL Server results to a text file that PowerShell can access and use

The query below is used to create a text file with a list of server names. The problem I have is that the text file gets created like Server, Server, Server etc. but I need it as follows Server ...
0
votes
2answers
38 views

Partition By A Column When The Value Isn't Continuous From One Record To The Next

How can I Partition By the PreviousScanIndex column when the value isn't continuous from one record to the next (ordered by the RowIndex column), so that I can rank each continuous group of records ...
1
vote
1answer
40 views

How do you do two date calculations in one SQL Statement?

In a SQL Stored Procedure, I need to calculate a date by subtracting 1 month and set the day to the 17th of the month. I can subtract 1 month with: DATEADD(month,-1, loanFirstPmtDate) But do not ...
0
votes
1answer
39 views

Restore job run successfully without error but actually not applied during log ship from older version to newer one

Our requirement is for SQL Server 2008 R2 databases to be upgraded/migrated to SQL Server 2016. We want to configure log shipping between SQL Server 2008 R2 (source) and SQL Server 2016 (target). We ...
0
votes
0answers
26 views

renaming Windows machine and SQL Server

We've built a new Windows 2016 VM with SQL Server 2016 that we'll migrate a mission critical database currently on SQL Server 2008 R2 to. The original plan was to have users test, test, test and test ...
1
vote
0answers
14 views

MS SQL 2008 R2, security and auditing

I have an 2008R2 SQL Server. There are a few AD groups added to the logins. Each group must have some permissions (and only these permissions if possible). Can someone help me with a few questions: ...
0
votes
1answer
32 views

Missing data in SQL Server

I need to find the data that is present in Oracle, but missing from SQL Server. I have tried the below query, but am getting the following error: The multi-part identifier "PS.TXN_ID" could not be ...
0
votes
0answers
17 views

Auto rebuild index in SQL Server 2008 R2 [duplicate]

How to define schedule to rebuild all database index in SQL Server 2008 R2.
2
votes
1answer
39 views

How to run merge agent job step on tsql

I'm having a problem with the merge replication and I want to try and run the job as a script on ssms so I can see the errors (I've tried almost everything else) Original post Here is the script -...
1
vote
2answers
67 views

Best approach instead of granting permissions all the time to logins

So, I'm trying to fix logins/users permissions here ( everybody was using DBO for everything, weak passwords and etc ). For each customer we have, I will create only one login. It will access test ...
1
vote
1answer
46 views

Upgrade SQL 2008R2 to SQL 2016 on windows server 2012R2

I am checking for expertise opinion for IN PLACE UPGRADE from SQL 2008R2 to 2016. We have tested side by side which looks good in dev env's. Considering production can i have some opinions on how ...
3
votes
1answer
94 views

How to benchmark disk performance from within SQL Server?

I know of tools such as SQLIO and diskspd to benchmark IO/disk performance, but need something that can be run from the SQL Server instance itself via T-SQL, as I'm working on a Cloud-hosted ...
0
votes
2answers
89 views

What is the best way to remove large amount of data from large tables?

I have two large SQL 2008 tables: table_header with 7 columns and ~ 2 million records table_detail with 22 columns and ~ 78 million records The common field between the two is called item_name ...
2
votes
1answer
43 views

How to know SQL Server installation language while it is down?

SQL Server is down after a security update. I need to install a cumulative service pack which is language discriminated. I'm downloading Spanish and English versions, I'm sure it will be either one, ...
0
votes
1answer
38 views

Upgrade SQL Server 2008 R2 to SQL Server 2012 and then set up data replication to SQL Server 2016

Currently I have a Windows 2008 server with SQL Server 2008 R2 and I want set up a data replication to a Windows 2016 Server with SQL Server 2016. I am aware that the aforementioned is not possible ...
0
votes
0answers
45 views

What would cause a login to unexpectedly become a member of db_denydatareader?

After months of working without issue a stored procedure began failing this morning with the error message: SELECT permission was denied on the object 'XXX', database 'XXX', schema 'dbo'. So I ...
2
votes
1answer
95 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
2answers
90 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 ...
2
votes
1answer
56 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
82 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): -------------------------------------------------------------...
8
votes
4answers
1k 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
61 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=@...
0
votes
2answers
40 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 ...
0
votes
2answers
98 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 ...
1
vote
0answers
80 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
58 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
57 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
34 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
27 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
389 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
43 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
90 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
67 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
65 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
54 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 ...