Questions tagged [sql-server-2008-r2]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
50 views

Deny Delete to An Active Directory Account in Sql Server

I want to deny an AD account to delete any record in sql server. How can i do that? DENY DELETE TO [usergorup\username] did not work. Is there another way?
0
votes
2answers
24 views

MS SQL - Create trigger to prevent insert if it doesn't match specific parameters

Suppose I have two tables payments with columns payment_id, batch_id and batches with columns batch_id, batch_type Can I add a trigger to prevent inserts into the payments table when the batch_id ...
2
votes
1answer
54 views

Unable to backup to a UNC path with a gMSA

I'm trying to automate backups using Ola Hallengren's backup scripts. I'm testing this out on a SQL express instance 2008 R2 (I know it's EOL today). I've setup the jobs and I created a scheduled ...
-2
votes
0answers
25 views

Display data in sql into two column as per unique ID

here is my data CREATE TABLE #Sections (SecID INT, Secnam VARCHAR(50)); CREATE TABLE #Floor (FID INT, Fname VARCHAR(50));` CREATE TABLE #ItemMasterFile(...
1
vote
1answer
51 views

SQL Server Backup failing Error :3041

I am running SQL Server 2008R2 Express edition. For all databases full backup, I am using expressmaint.exe. In this, I am using the command -S server name -D ALL -T DB -R D:\SQL_BACKUP\...
0
votes
3answers
95 views

Select with dynamic columns

I have an InvoiceHeader, InvoiceItem tables InvoiceHeader content: InvoiceId InvoiceNumber BusinessId BusinessName BranchId 1 1 10 BSN INC 1 ...
0
votes
1answer
32 views

upgrade MSDB only?

good morning. we currently are running on SQL server 2008 r2 and going to Sql server 2016. we are told we cannot do an in place upgrade and will have to restore all SSIS and jobs manually (script, ...
0
votes
1answer
61 views

Query to Find Multiple Start Time and End Time on a Single Day based on Boolean Value

I want to find the multiple Start Time and End time in a single Day based on the Boolean value where data is divided into each half hour interval for a day. So Each day is divided into 24hrs/2 = 48. ...
3
votes
2answers
60 views

Can only specific SQL users be forced to use TLS?

My school would like to develop custom software for some embedded devices. As part of this, we may need the devices to connect to an SQL Server instance. Is it possible to force specific SQL users to ...
1
vote
1answer
28 views

How to Get the SUM Value from Grouped by Last value in a table result

I have this query table, CREATE TABLE BASE_Purchase ( PurchaseId BIGINT, PurchaseDate DATE NOT NULL, SupplierName VARCHAR(255) NOT NULL, Amount MONEY NOT NULL, Outstanding MONEY NULL PRIMARY KEY(...
0
votes
0answers
31 views

Multiple condition in if

I am making a new Report in SSRS. I have an If condition based on a SSRS STRING Parameter. This Parameter have 3 values; EX : john, Albert and sara. I have do something like this in sql and it works ...
-1
votes
1answer
68 views

TSQL Running Total but only based on Logic

Just been tasked to work on an old system that's running on SQL2008r2. Need to audit the number of reserv earned and compare against the number of ticks taken. So here's a sample dataset - Expected ...
0
votes
1answer
39 views

SQL SERVER Convert BLOB data to STRING

I am trying to extract blob data from a SQL SERVER 2008 R2 database. The data is annotation data that an application stores. I have done some research and I have been partially successful in ...
1
vote
1answer
60 views

How long time does it take to TRUNCATE 50 million rows? [duplicate]

I want to TRUNCATE (or remove all rows in) a table with 50 million rows. The database is in use in production. How long time will the TRUNCATE operation take?
1
vote
1answer
42 views

What is using all my space?

I'm using SQL Server Express 2008 R2 and a third part application has its database on this database server. The database seems way bigger than I would expect considering what it is actually doing. ...
2
votes
1answer
46 views

Catch OLE DB provider “SQLNCLI10” for linked server “[Some Server]” returned message “Query timeout expired”

Without changing the settings on servers, etc to the linked server connections, etc, is there a way to catch the dreaded? "OLE DB provider "SQLNCLI10" for linked server "[Some Server here]" ...
3
votes
2answers
48 views

Tracking Down a Problem User

How do I track down a user that may be running a relatively quick query, but running it excessively? For examples, they may: be running a report over and over and over again have a book set on their ...
2
votes
1answer
155 views

Improve OR inside INNER JOIN

I have this query. this is the second query. the first one was using the left/or outside the subquery, and the query plan was horrible.( Edited the question with the full sintax ): https://www....
0
votes
0answers
38 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
17 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 ...
3
votes
3answers
128 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
19 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
311 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
23 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
387 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 ...
7
votes
3answers
1k 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
239 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
63 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
39 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
56 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
86 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
39 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
63 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
20 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
26 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
28 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
54 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
40 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
65 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
130 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
379 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
65 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
191 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
81 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
49 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 ...
0
votes
1answer
186 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 ...