This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx

learn more… | top users | synonyms

0
votes
1answer
67 views

Converting date string to SQL DATETIME - hopefully without a udf

I am taking an Excel file that has dates in different formats but one of these formats is giving me some grief when I try converting it or casting it to a SQL DATETIME. For example: Thu Oct 16 ...
0
votes
1answer
39 views

UPDATE statement not consistent when including NULL fields

So I want to append Field B to C, then copy field A to B and set field A to "Awaiting update". I have written the following code UPDATE Notes SET /* append previous month to notes */ Notes=Notes + ...
2
votes
3answers
81 views

Bulk change Agent Jobs' definition to “Quit the job reporting failure” from “Go to the next step” On Failure

There are agent jobs with multiple steps in our servers and all their steps are defined as "go to the next step" "On Failure". So, even a job step fails, operator doesn't get notified because ...
5
votes
2answers
133 views

Sporadic execution time causing timeouts [closed]

We have a table with 100 million records holding 24 hour worth of data of our clients' customers actions (actions which are performed on 3rd party sites and replicated from there to our server) The ...
0
votes
1answer
74 views

How comes that login with no rights cam give himself db_creator?

I just created a new login using the GUI, which generates this script: USE [master] GO CREATE LOGIN [tester] WITH PASSWORD=N'tester', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF ...
1
vote
2answers
36 views

To what extent can we monitor jobs/suspended queries as DBO

Short Background: I work for company A, at company A we have a DBA that should not be. When we are tasked with items for troubleshooting which require quick turn around, it generally takes quite a ...
0
votes
1answer
102 views

More Best Practices To Updating Large Tables?

I know there are a number of topics on this question, but I'm always seeking more insights. I have a large table with a billion+ records. The amount of records could be reduced and archived, but the ...
0
votes
3answers
43 views

Move result of a query from one server to another table on another server

What are my options in moving result of a query in one server to another table on another server? Data contains ~400.000 rows so it won't be a quick one. (Both of them are SQL Server 2008 R2) ...
1
vote
1answer
65 views

Database backup issue in SQL Server

For security reason I always took back up of my database. I took full backup in each month. And differential in each week. I used to save each back up file in different name. I lost my latest full ...
0
votes
1answer
33 views

SQL Server: get meta-Info about LDF-File

I am trying to get the timestamp of the first entry in the .LDF file of a live database. Is there any way to do this? I already get the first LSN in the LDF using SELECT MIN([Current LSN]) FROM ...
1
vote
0answers
44 views

Trying to add a NULLable column to a table with over 90 million rows takes a very long time

I have been trying to add a NULLable column to a table with over 90 million rows. ALTER TABLE <TableName> ADD <FieldName> INT NULL; It is taking a very long time (been running for over ...
6
votes
1answer
371 views

SQL Server: Create default stored procedure as and when a new database is created

I am using SQL Server 2008 R2. I need one simple stored procedure to be created by default, as and when a new database is created in SQL Server. How can I achieve this? Assume the stored procedure to ...
0
votes
1answer
60 views

Order of Columns in Index

I have following query which has ON clause on different column, Where clause on different column and Selection on different columns so my question is what should be Order of Columns in Index as ...
0
votes
1answer
65 views

Can I re-replicate a transactional publication? Should I?

We use a cloud-based SAAS provider as one of our line-of-business applications. An on-site SQL Server 2008 R2 instance in our DMZ is a transactional replication subscriber to a number of publications ...
1
vote
1answer
78 views

Tempdb growing, however it has a lot of free space

On a sql server dedicated to a Dynamics CRM database, I've been seeing some odd behavior. While monitoring growth using the following query as basis, I've noticed tempdb has grown quite frequently ...
-4
votes
2answers
335 views

Incremental backup in SQL Server [closed]

I got a task for backing up my clients data through internet. Client has about 50 GB of data there. So I need to back up the data and pass through internet, but it takes about 8 to 9 hours for this ...
0
votes
0answers
22 views

Windows Administrator doesn't have permission on SQL Server [duplicate]

My previous system administrator created a SQL Server 2008 instance on my local machine with his domain User. Now, I cannot create, delete, or view any databases or tables on that instance. Even with ...
0
votes
0answers
34 views

Oracle Linked Server only works with Sql Login

I'm having this weird error for some time. When I login to sql server and use the linked server SELECT * FROM OPENQUERY(MEGA, 'SELECT 1 FROM DUAL') I receive this error OLE DB provider ...
0
votes
1answer
36 views

When does a port get assigned if SQL Server is set to TCP Dynamic Ports

I'm trying to determine when a dynamic port is assigned to SQL Server. If the TCP/IP Properties have the TCP Dynamic Ports enabled, does that port get assigned at the time the Server is installed or ...
2
votes
1answer
40 views

Agent service account shows as not clustered

One of the SQL Server 2008 R2 instances I administer is running on a two node Windows Cluster (Windows Server 2008 R2 Enterprise) When I run the DMV sys.dm_server_services, SQL Server Service Account ...
1
vote
2answers
102 views

Why would a query work slower when a where clause is added?

I've got two databases and both have got the same view over the same table which has the same indexes. The view selects the top location for a given IMEI from a locations table. CREATE VIEW ...
0
votes
0answers
53 views

ComputerNamePhysicalNetBIOS and “Current Host Server” shows different machines

I have a SQL Server 2008 R2 instance on a two node (say names X and Y) windows server cluster (Windows Server 2008 R2 Enterprise). Server Manager on both nodes shows 'X' as the 'Current Host ...
8
votes
1answer
210 views

statistics are up to date, but estimate is incorrect

When I do dbcc show_statistics ('Reports_Documents', PK_Reports_Documents) I get the following result for Report ID 18698: For this query: SELECT * FROM Reports_Documents WHERE ReportID = 18698 ...
0
votes
1answer
42 views

How to keep archive table accessible for some users which is not frequently accessible?

I have partitioned my table based on some boundary ranges. My Database is built at MSSQL server 2008 R2. I have done partitioning for a table which has 40 million records in it. Now it's time to ...
3
votes
1answer
113 views

How to give access to a user to a view, but not to the table the view is using?

I have a user (called myUser) which has db_datareader role on myCustomDatabase. myCustomDatabase and MSDB are owned by sa user. I've enable chaining (it's already enabled on MSDB): ALTER DATABASE ...
1
vote
2answers
170 views

SQL remove middle characters

Simple question, not so simple answer! I have a bunch of dates... how do I remove the middle numbers (which are incorrect) so I just have Week 3, 2011/2012 etc? Thanks! Reporting_Week_Name Week 52, ...
1
vote
1answer
53 views

Full text index does not honour deleted words - They are still indexed

I have a full text index defined as 'Automatic' radio button checked on in its Catalog. When I modify a word in the full text indexed column, the new word appears in the sys.dm_fts_index_keywords but ...
2
votes
0answers
139 views

Merge-Replication: New column doesn't get replicated down by update command

I do this frequently but all clients have this fault: I added a new column to a table using a SQL statement, which causes the column to be created and added to every publication that uses this table. ...
1
vote
1answer
86 views

Upgrade to SQL Server from 2008 to 2012 - Developer perspective [closed]

We are in the process of migration from SQL Server 2008 to SQL Server 2012. The entire process of migration will be taken care by dedicated DBA team. From a developer prespective, I would like to ...
2
votes
1answer
34 views

Circumstances under which a windows server login becomes disabled

Can anyone confirm under what circumstances a SQL Server windows login account changes from Enabled to Disabled? More specifically does this need to be a human interaction or can SQL Server ...
1
vote
0answers
22 views

SQL Server 2008 R2 - Capture or be notified when an sql handle is ran

I'd like to monitor a particular SQL handle and find out who (process id) is running the SQL. Is there a way to retrieve either a list of proc ids who executed the SQL handle in last N minutes or be ...
4
votes
1answer
144 views

SQL Server 2008 R2 Readable Mirror

I know that SQL Server 2012 and 2014 offer AlwaysOn Availability Groups which provide this functionality, but I'm still stuck on SQL Server 2008 R2 for a while. I recently came across this AWS ...
2
votes
1answer
77 views

Export entire SQL Server Database into platform independent data files

On a quarterly basis I need to provide our clients a copy of all their data. We need to provide this data in a database independent format (in other words I cannot simply send them a backup file). ...
3
votes
1answer
116 views

Transaction log backup file larger than expected?

I've recently taken over a SQL Server database that didn't have any transaction log backup routine running for at least 2 years. The first log backup was very large (3.8 gb on a 1.14 gb database) as ...
8
votes
1answer
194 views

High PAGELATCH_* and WRITELOG waits. Are they related?

We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion ...
0
votes
0answers
30 views

Database mirroring in SQL Server 2008 R2

I have problem with database mirroring. I have 2 servers on both I have installed SQL Server 2008 R2 and on both I created the same user name and password. Db mirroring is working if I use "This ...
7
votes
1answer
159 views

Unable to restore (error 3456)

I have a situation that is not easy to figure out, and thought I'd ask on this forum if others might have suggestions. I'm running SQL Server 2008 R2 Standard SP3 on Windows Server 2008R2 Enterprise. ...
2
votes
0answers
107 views

How to copy SQL Server log shipping files to AWS EC2 instance

I am using SQL Server 2008 R2 and would like to log ship from my local data center SQL Server instance to a log shipping target SQL Server running in AWS EC2. What is the best way to copy the log ...
0
votes
0answers
48 views

cannot show requested dialog - job activity monitor

We have SQL Server 2008 R2 enterprise edition. When I am trying to open job activity monitor I get error " unable to execute request command jobs (sqlmanagergui) Note: We applied latest sp3 ...
0
votes
1answer
31 views

Function to handles firstname and lastname to a specific format

I'm trying to figuring out a way to show a name properly. Let's say I have 2 variable : @Firstname = 'Marc-Oliver' @Lastname = 'Saint-Onge' I would like to have name shown as; M.-O. St.Onge Or ...
3
votes
3answers
95 views

I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches

I have the following basic dataset in MS SQL Server 2008R2: create table #temptable ( Source varchar(255), PollTime datetime, Value numeric(20,2) ) insert into #temptable( Source, ...
2
votes
1answer
155 views

remove old SSRS Schedule jobs

We have a application that creates SSRS Schedules 2 times a year and it does create one for each of our customers. Each schedulation results in a SQL Agent job with a GUID as name and the description ...
3
votes
1answer
130 views

Biggest wait WriteLog

I have been given a server (SQL Server 2008R2 64 bit) where our application is running. There has been a gradual decrease in the performance of the application. Now it has come to a point where ...
0
votes
1answer
95 views

SQL Server : how to disable connections to a linked server?

SQL Server Linked Server to default instance - service is disabled but connection is still possible I have a server called SWLON1 that has SQL Server 2005 and SQL Server 2008 r2 installed. The SQL ...
0
votes
1answer
92 views

What is the correct SQL for using where ID in List and the items are with AND operator

I need a SQL Statement to use IN operator within the items with "And" connected. SELECT * from tbl_Users WHERE id in [1,2,3] is equivalent to: SELECT * from tbl_Users WHERE (id=1) or (id = 2) or ...
8
votes
3answers
1k views

SQL server service won’t start after disabling TLS 1.0 and SSL 3.0

Because of security reasons, we disabled TLS 1.0 and older protocols on our windows, and enabled just TLS 1.1 and TLS 1.2 under the following registry path: ...
4
votes
1answer
71 views

What is the performance impact of queries against DM objects using DMVs in SQL Server 2008?

Recently, I've been running queries against our production system on the DMVs for querying the plan cache. In particular, here's the from clause of the query: FROM sys.dm_exec_cached_plans AS cp ...
1
vote
1answer
48 views

Preventing orphaned users in SQL Server 2008 R2

I've recently had a situation where a database was refreshed from production to staging, and the users on the database ended up orphaned. These are sql server users, not mapped to windows ...
0
votes
1answer
42 views

How to remove the duplicate records from primary filegroup after partitioning?

Basically, our database is created based on primary filegroup alone. Now, I have done partitioning for a large table which was approx 25 GB. The partitioning was done on secondary filegroups which ...
0
votes
0answers
36 views

When trying to get my database diagram in SQL I get “Access is denied (SQLEditors)”

I am using SQL Server 2008 R2 with the AdventureWorkLT2008R2 test database. I have been able to create my tables and insert data, but when I try to run my database diagram I get the following error ...