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
3
votes
1
answer
1k
views
How do I grant a SELECT permission to an individual user who's part of a large group that is explicitly denied?
I have a table SuperSecretData which is currently setup to deny SELECT to everyone in the company by the standard Active Directory account "Domain Users". But a couple of specific individual users do ...
- 35.2k
8
votes
2
answers
2k
views
Is It normal to keep log file larger than data file? [duplicate]
Is It normal to keep log file larger than data file?
I know why my log file is large, it is because I have a huge modification and locking occurred on a specific time, and caused my log file to be ...
- 101
2
votes
2
answers
4k
views
How to bulk convert ISO8859 text in Text columns to UTF8 in nvarchar(max) columns?
I've been struggling for days with a problem on a MS SQL database. Here is the situation: I have an old Open Source PHP application (Limesurvey version 1.92) running on a Windows 2008R2 server, with ...
- 21
1
vote
1
answer
553
views
SQL Server Setup: Report Server and RAM Considerations
DISCLAIMER
I am a software developer by trade, but sometimes have to put on my 'dba' hat in the absence of a true dba.
SETUP
I am working on a project where:
SQL Version is SQL Server 2008 R2 (...
- 113
6
votes
3
answers
4k
views
Is it possible to take a database offline when doing a backup using an SQL job?
I have the scenario where I have to generate a backup of the database (SQL Server 2008) and restore into the new server (SQL Server 2016). While taking backup data should not be changed in any case. ...
- 189
8
votes
1
answer
1k
views
Slow query when having 'contains' and '=' together in where clause
The following query takes about 10 seconds to finish on a table with 12k records
select top (5) *
from "Physician"
where "id" = 1 or contains("lastName", '"a*"')
But if I change the where clause to ...
- 103
-1
votes
1
answer
81
views
Is there any possible way to modify the database state from within the scope of a CTE? [closed]
For example:
-- Directly with a DML Statement
WITH CTE_DML AS
(
UPDATE Table1
SET Column1 = 'Value1'
FROM Table1
)
-- Indirectly by executing a procedure
WITH CTE_SP AS
(
EXEC ...
- 35.2k
0
votes
1
answer
662
views
Set up read-only privileges on the dbo schema, but allow creation of entities in another schema?
Is it possible to create a custom schema in a database such that users that have read-only access in that database for the dbo schema can be set up with the ability to create and modify entities for ...
- 35.2k
0
votes
1
answer
53
views
Will an AD Account inherit all the User Mappings to databases that it's parent AD Group is mapped to?
If a Windows AD Group and an AD Account that belongs to that AD Group are both setup as Logins on a SQL server, will the AD Account Login inherit all the User Mappings that it's parent AD Group Login ...
- 35.2k
7
votes
1
answer
8k
views
What is the purpose of "Schema Owner"?
If I create a new schema and set the schema owner to another schema, for example dbo, what is the meaning of that in regards to my new schema?
Does my new schema inherit the same permissions from it'...
- 35.2k
5
votes
2
answers
853
views
SQL Server Agent Job is Delayed?
I have an SQL Server Agent job set up to run every minute on the minute.
Usually it runs exactly at :00 on the clock but occasionally it waits several seconds.
See below, one of the jobs was ...
1
vote
0
answers
194
views
Has my repair fixed incomplete patching?
I am patching a SQL2008R2 Express Edition instance. On the first failed attempt, all seemed to be fine - SQL loaded, dbs online, @@VERSION returned the expected results, but errors were found during ...
- 369
2
votes
1
answer
128
views
Connect to SQL Server 2008 R2 from Windows 98
Is it possible to connect to SQL Server 2008 R2 from a Windows 98 machine?
If it is possible, what would I use?
I have an old testing machine that currently writes to an access database. This ...
- 213
5
votes
1
answer
107
views
Performance Metrics After Enabling Auto-Stats
Short Version
After enabling AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS_ASYNC on a database, what performance metric will best display the changes?
Long Version
We just realized that the ...
- 325
1
vote
1
answer
63
views
Is there a way to group the output of a query based on two rows
I'm not sure my question is asking the right question, so i will be as specific as possible in here.
I have the following data which i have changed the values of.
Below is the code I am using to ...
- 13
4
votes
3
answers
7k
views
What is the latest version of SQL Server native client that is compatible with Sql Server 2008 r2
Title says it all, what is the most recent version of Sql Server native client that is compatible with Sql Server 2008 R2
- 213
0
votes
1
answer
77
views
Application/MS SQL Server Express Installer
We're developing a small(ish) application that collects and stores data. It's low-throughput, low-volume, but needs to be able to archive and retrieve data from (almost) arbitrarily earlier times, ...
- 103
1
vote
1
answer
2k
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 ...
- 213
1
vote
1
answer
442
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....
- 423
0
votes
0
answers
764
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
0
answers
136
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 ...
- 1,771
0
votes
1
answer
741
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 ...
- 329
1
vote
1
answer
4k
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 ...
- 55
0
votes
2
answers
163
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 ...
- 47
0
votes
1
answer
33
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
2
answers
29
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 @...
- 113
1
vote
1
answer
171
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?
- 47
0
votes
2
answers
7k
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 ...
- 3
2
votes
1
answer
1k
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 ...
- 23
1
vote
1
answer
2k
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\...
- 31
0
votes
3
answers
108
views
Select with dynamic columns
I have an InvoiceHeader, InvoiceItem tables
InvoiceHeader content:
InvoiceId InvoiceNumber BusinessId BusinessName BranchId
1 1 10 BSN INC 1
...
- 103
0
votes
1
answer
195
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, ...
- 1
0
votes
1
answer
994
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. ...
- 171
3
votes
2
answers
396
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 ...
- 167
1
vote
1
answer
174
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(...
- 329
-1
votes
1
answer
76
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 ...
- 441
0
votes
1
answer
12k
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 ...
- 111
2
votes
1
answer
2k
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?
- 155
1
vote
1
answer
210
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. ...
- 111
2
votes
1
answer
1k
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 ...
- 21
3
votes
2
answers
58
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
1
answer
178
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....
- 7,374
0
votes
1
answer
60
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 ...
- 283
5
votes
3
answers
5k
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 ...
- 35.2k
0
votes
1
answer
163
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 ...
- 293
4
votes
2
answers
1k
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 ...
- 283
10
votes
1
answer
3k
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 ...
- 283
8
votes
3
answers
7k
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?
- 35.2k
2
votes
1
answer
5k
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, ...
- 35.2k
0
votes
1
answer
78
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 ...
- 147