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
365 views

Why am i getting a truncation error when running a SSIS package on the sql server agent, but not when i manually run it

I created a SSIS package that drops then creates a table, to transfer data from Sql Server 2008 r2 to an access file. When I run it in BIDS 2008r2 it runs just fine, no truncation issues at all. When ...
1
vote
1answer
96 views

SQL Server: I need to estimate execution time of a SELECT .. INTO .. statement

To take backup of a big table (using SELECT .. INTO .. ) took me almost 4 hours in a machine with 4 CPUs and 16 GB of RAM. No external application/process were accessing the table during the operation....
0
votes
0answers
106 views

How to Analyze and Document a SQL Server Stored Procedure

I have been handed a large SQL Server batch process containing <100 stored procedures and functions. There is no documentation, I cannot rely on comments. High level, what are the best practices ...
0
votes
0answers
61 views

Partition Alignment in AWS SSD EBS

I'm setting up a new SQL Server 2008 r2 in AWS with IO1 SSD EBS volumes. My smaller EBS volumes are aligned correctly when they were formatted but the larger volumes, 3TB, are not because they used ...
0
votes
1answer
57 views

How to Search values Integer column alias in a WHERE Clause

How to Filter values in where clause alias variable, CREATE TABLE BASE_Chq ( ChequeInvoicePaymentId BIGINT NULL, ChequeNumber VARCHAR(45) NOT NULL, Amount MONEY NOT NULL ) Once i select the whole ...
1
vote
1answer
938 views

Arithmetic overflow error for data type smallint, value = xxxxx

Same query run on two identical servers of the same SQL version(including SP/KB level) with identical server and database level settings errors out on one server(Arithmetic overflow error for data ...
0
votes
2answers
52 views

How To Grant A User Just To Create A Table In Existing Database But Not To See Other Existing Tables In It

We have dealed with an outsource company to install an application for use of our accounting department. Now, they want to access our existing database to install their application but i don't want ...
0
votes
1answer
26 views

TSQL - use columns as date range

How can I retrieve the cto column using an @variable? For example, for @Variable = '2019-03-02' the expected output is'MAR 1 - MAR 7'.
1
vote
2answers
25 views

Best Practices - Copying Table with datetime stamped on name

For backing up purposes I need to copy a table into the same DB but with Date and Time on the TableName. My solution doing this with T-sql Job: select * into dbo.tmp from dbo.something go declare @...
1
vote
1answer
67 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
241 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
278 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 ...
1
vote
1answer
237 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
103 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
72 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
183 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
71 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
35 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(...
-1
votes
1answer
69 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
3k 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
324 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
66 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
445 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 ...
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
166 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
1answer
52 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 ...
4
votes
3answers
1k 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
51 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
640 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 ...
10
votes
1answer
1k 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 ...
8
votes
3answers
3k 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?
2
votes
1answer
925 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
66 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
51 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
41 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
690 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
47 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
137 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
25 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
211 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
42 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
66 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
32 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
165 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
45 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
152 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
2k 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
380 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
86 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 ...
1
vote
1answer
2k 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?

1 2
3
4 5
76