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
0answers
23 views

Dynamic SQL - Resource [on hold]

Can someone please recommend resources/books to learn and become efficient in Dynamic SQL for MSSQL. I have researched online but most of the content/book I am coming across has basic Dynamic SQL ...
0
votes
0answers
37 views

Alter view after table drop

I want to alter the view for the drop tables. For an example: View: create view View1 as select * from table1 union all select * from table2 union all select * from table3 union all select * from ...
0
votes
0answers
7 views

Disabling SSRS Scale out feature during migration to non-enterprise edition

Server A is SQL Server 2008 R2 Enterprise which has SSRS installed. I have migrated across the reporting database, reporting tempdb, etc. On Server B is SQL Server 2017 standard edition with SSRS. I ...
0
votes
0answers
35 views

How to find all values in one column that have two or more values in another column [closed]

I have a table that has a column that can have multiple identical values. In another column, the values can be different. I'm looking for all the instances in the first column that have specific ...
3
votes
3answers
63 views

What's an example of an exploitation of the security from enabling Ole Automation Procedures Server Configuration option?

I have a need to enable to the Ole Automation Procedures Server Configuration Option on my SQL Server, but was wondering what specific kind of security risks do I incur by doing so? What is an ...
0
votes
1answer
15 views

MSX Job deployment: Not creating job on certain servers

I am deploying a new index maintenance jobs through MSX to about 100 servers, I am in the process of manually checking that each job has been created and I have already discovered that for some ...
4
votes
2answers
283 views

Permutation and Combination of words

Follow up of question I have the following data: I have the following sample data for permutations and combination. create table tbltest ( name varchar(50), addres varchar(100) ); insert ...
0
votes
0answers
22 views

Failed to map 16777216 bytes of contiguous memory when max memory changed from 8GB to 13GB where total server memory is 16GB

we have changed the SQL server max memory from 8 GB to 13GB where total server memory is 16GB at this we got below error messages in log file Failed to map 16777216 bytes of contiguous memory when ...
10
votes
1answer
315 views

Generate permutations and combination for name column

I have the following sample data for permutations and combination. create table tbltest ( name varchar(50), addres varchar(100) ); insert into tbltest values('Sam Mak John','Street 1 HNo 101 ...
6
votes
3answers
991 views

Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?
1
vote
1answer
194 views

Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list? [closed]

My code: SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn SELECT ColumnWithIdentity, ...
0
votes
1answer
62 views

Simple insert statements are taking so long

I have a scenario where one main stored procedure calls a nested procedure and that nested procedure contains simple insert statements for multiple tables and almost every insert is taking so long I ...
0
votes
1answer
49 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 ( ...
-1
votes
1answer
38 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
2answers
35 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 ...
-1
votes
2answers
31 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. ...
3
votes
3answers
70 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, ...
1
vote
1answer
21 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. ...
0
votes
2answers
72 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 ...
2
votes
0answers
33 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 ...
0
votes
1answer
62 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
0answers
18 views

What are SSIS ,SSRS and DB engine SQL server 2008R2 to 2012 IN PLACE UPGRADE steps and cautions? [duplicate]

I am trying to find, SSIS ,SSRS and DB engine SQL server 2008R2 to 2012R2 IN PLACE UPGRADE steps and cautions ? Thanks, DL
0
votes
0answers
15 views

I am migrating SSRS from SQL server 2008 r2 to sql server 2016 on another server. Finding and backing up the right web.config and machine.config

I am following "https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/migrate-a-reporting-services-installation-native-mode?view=sql-server-2017#bkmk_backup". Under "Backup Files and ...
0
votes
0answers
26 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), ...
1
vote
0answers
28 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
1answer
38 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 ...
0
votes
0answers
60 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 ...
0
votes
1answer
100 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 ...
2
votes
2answers
375 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))) )...
0
votes
1answer
64 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 ...
0
votes
1answer
85 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?
0
votes
1answer
53 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 ...
0
votes
1answer
28 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
152 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
65 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
131 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
39 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
493 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
39 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
59 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-...
4
votes
4answers
97 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
185 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
110 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
103 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
37 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
31 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
44 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
58 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
41 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
41 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 ...