Questions tagged [sql-server-2008-r2]
SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.
3,778
questions
0
votes
1
answer
61
views
SELECT columns which contain data first [closed]
I have result of a query as below first table but the requirement is to display columns first which contain data in it.
Result:
Requirement:
Sample data generation:
Create table #CustData
(
...
- 147
-1
votes
1
answer
51
views
if and else condition in select query
I want a query in which if and else will use but I do not know how it will use,below is my table structure ,i want result below mentioned after Produce table.
Item Master Table
item_code ...
1
vote
2
answers
3k
views
SQL Job Agent Service Account - Access is denied to run Powershell
I am trying to run a PowerShell script via the command line via SQL Job.
"C:\Windows\System32\WindowsPowerShell\v1.0" "C:\Test\TestScript.ps1"
I am running the SQL Job from a SQL Server 2008 R2 ...
- 111
-1
votes
2
answers
156
views
Prevent 'Last' Trigger from firing unnecessarily
We have a legacy application running on SQL Server 2008 R2.
On DML operations on some of these legacy tables, I need to write data to a set of tables in another database under the same instance.
...
- 373
3
votes
3
answers
860
views
Is there a problem creating only differential backups every hour?
I have three databases which are not big. Every differential backup is roughly 50MB, then we have a full backup at midnight.
Is there any problem doing this?
I could take the full backup then logs, ...
- 7,374
1
vote
1
answer
437
views
Would SQL Server Profile show triggers or other code which run as a side-effect of the query I'm profiling?
I've been using profiler to test a massive script I'm running in order to check for unintended side effects, e.g. triggers that I don't know about running because I've updated data in a certain table.
...
- 93
0
votes
2
answers
292
views
Issue migrating from SQL Server 2008 R2 to SQL Server 2017 Standard (on Linux)
I am currently trying to carry out a database migration from SQL Server 2008 to SQL Server 2017 (Linux) using the DMA tool.
I get the all green after running the assessment, however I get hit with ...
- 11
2
votes
0
answers
51
views
TEMP Database is not showing Encrypted in TDE in SQL Server 2008R2
As per my knowledge, when we encrypt any user database in SQL Server through TDE, the TEMP database automatically gets encrypted.
I have done TDE in SQL Server 2016, and when I check through SELECT ...
- 397
0
votes
1
answer
356
views
Conversion of Row into Column
I'm in very weird situation and i have researched a lot but not able to find anything similar to this scenarios.
Input looks something link this.
Where Each patient has Primary Dx code, Admit Dx ...
0
votes
0
answers
38
views
How to better deal with INSERT if new, UPDATE if exists (70million rows)
So I have a big cursor with similar parts. it looks like this:
set nocount on;
Declare
@linha VarChar(1200),
@TipoRegistro VarChar(1),
@Cnpj VarChar(14),
...
- 7,374
1
vote
0
answers
197
views
SSMS - Data load Option
I have recently installed the newer version of SSMS on my system. But the option to load the excel file is grey out. I'm not able to load BULK Load
I tried to run DTSWizard.exe, even it doesn't give ...
0
votes
1
answer
131
views
How to get the SUM of custom made query
I have created a relation database structure below,
from this relational table i want to get the SUM of all data, below my query,
SELECT ((BASE_InvoiceLine.Price - IIf(SUM(BASE_Expense.Amount) IS ...
- 329
0
votes
0
answers
245
views
Transactional replication from Oracle PL/SQL to MS SQL Server broke from the data in a Date column but previous records with same date are ok?
I have a heterogenous transactional replication subscription setup between PL/SQL (12C 12.1.0.1.0) and SQL Server (2008 R2), where the Oracle PL/SQL server is the publisher and the MS SQL server is ...
- 35.2k
0
votes
1
answer
6k
views
How to find how many number of transactions are happening in SQL Server/SQL Server database in a day?
I have to migrate my SQL Server on a particular public cloud, which is charging/licensing on the basis of number of transactions happening on the SQL Server.
I am aware of the Performance Counters ...
- 397
2
votes
2
answers
383
views
Is it a good idea to use COLUMN AS (left([Another_Column],(4)) instead of LEFT in the select?
I came across a table like this:
CREATE TABLE TABLE1
(
CD1 int
,CD2 varchar(16)
,CD3 varchar(21)
,CD4 decimal(14,0)
,CD5 varchar(4)
,CD6 decimal(18,2)
,CD7 AS (left([CD3],(4)))
)...
- 7,374
0
votes
1
answer
253
views
Query to obtain indexes information - why does it produce duplicated values?
SELECT OBJECT_NAME(S.OBJECT_ID) AS 'TABLE NAME'
,I.NAME AS 'INDEX NAME'
,I.INDEX_ID ...
- 7,374
1
vote
1
answer
7k
views
Does SQL server log when and who enabled / disabled jobs by default?
Is there any default logging or history of when a SQL job was enabled or disabled?
- 35.2k
0
votes
1
answer
1k
views
GRANT REFERENCE on all user defined types
The original problem is: a user, who has the permission to create/alter view granted is getting this message when trying to perform either operation:
Cannot find the type 'nvarchar', because it ...
- 105
0
votes
1
answer
83
views
List of databases which a user can access in SQL 2008 R2 [duplicate]
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 ...
- 113
0
votes
1
answer
2k
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 ...
- 294
1
vote
3
answers
3k
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 ...
- 7,374
3
votes
4
answers
5k
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 ...
- 35.2k
1
vote
1
answer
625
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 ...
- 11
5
votes
2
answers
744
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 ...
- 35.2k
0
votes
2
answers
120
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 ...
- 7,374
0
votes
1
answer
149
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-...
- 11
6
votes
5
answers
17k
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 ...
- 2,063
4
votes
1
answer
637
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:
...
1
vote
2
answers
4k
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 ...
- 11
5
votes
1
answer
4k
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 ...
- 35.2k
3
votes
1
answer
51
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 = ...
- 373
1
vote
2
answers
157
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 ...
- 324
1
vote
1
answer
632
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
...
- 294
0
votes
2
answers
523
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 ...
- 35.2k
1
vote
1
answer
47
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 ...
- 13
1
vote
1
answer
359
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 ...
- 71
0
votes
0
answers
58
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
1
vote
0
answers
34
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:
...
- 11
0
votes
1
answer
165
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 ...
2
votes
1
answer
765
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
-...
- 211
1
vote
2
answers
234
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 ...
- 7,374
1
vote
1
answer
64
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 ...
- 2,169
3
votes
1
answer
1k
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 ...
- 133
0
votes
2
answers
655
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 ...
- 11
2
votes
1
answer
1k
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, ...
- 250
0
votes
1
answer
364
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 ...
- 111
0
votes
0
answers
61
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 ...
- 136
2
votes
1
answer
1k
views
Refactoring subquery to JOIN and CROSS APPLY, get only one 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
...
- 187
2
votes
2
answers
370
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 ...
- 7,374
3
votes
1
answer
1k
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 ...
- 151