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

learn more… | top users | synonyms

1
vote
0answers
153 views

SQL Server 2008 R2 Cluster Service pack 3 fail to failover node

I have a SQL Server R2 cluster on Windows 2008 R2 boxes, and today i was to install SP3. I installed it without any errors and rebooted the passive node. When i then try to failover to the passive ...
0
votes
0answers
28 views

Disk space requirements for SSAS database backup

Can anyone advise how I can gauge the disk space required to backup a SSAS database? I have a database that is as per SSMS estimate 146GB in size. The contents of the data folder amount to 143GB and ...
0
votes
0answers
30 views

Oracle Linked Server on SQL partial insert

I am using OpenQuery to insert into OracleLinkedServer, inside a while loop from while ( @id <= @cnt) Begin INSERT INTO OPENQUERY ( MY_LINKED_SERVER,'SELECT COL1, ...
2
votes
1answer
146 views

Add linked server from SQL Server 11.0.5058 to SQL Server 10.50.4000 [closed]

I tried to add a linked server running on SQL Server 2008 R2 (10.50.4000) from SQL Server 2012 (11.0.5058). I keep getting this error: The linked server has been created but failed a connection ...
0
votes
0answers
693 views

BCP Errors: String data, right truncation; Unexpected EOF

OK... I give up. I have been struggling with trying to import a semicolon-separated file (field1;field2) with BCP for the past two days, but I only have been getting these errors: Starting copy... ...
0
votes
0answers
45 views

Why does this plan cost even though there are no rows?

I execute a Batch-Operation. The whole execution needs about 60s. The following statement needs 75.6% of the whole Batch-Operation: update WW_Pos set LieferantID=null where LieferantID in (select ...
0
votes
1answer
47 views

Joining two inline functions, slows considerably when filtering on second function

ok, So I have two inline functions that run quickly enough alone. When I left join them like this select * from Function1(-1) F1 left join Function2(-1) F2 on F1.key = F2.key When I run this I ...
7
votes
1answer
303 views

Sudden increase in Page Writes/s after patching / rebooting

I was given a SQL Server 2008R2 to run, but my DBA skills are rusty after some years away from the day-to-day ops, so please bear with my (somewhat) silly questions. This SQL Server is running in ...
3
votes
1answer
142 views

I can backup my SQL Server 2008 R2 but can't restore from the very same folder

I ran a backup into a folder. The Network Service has full control access to this folder. The backup process didn't work until I granted that account full control. I try to restore that same file but ...
1
vote
1answer
91 views

Enforce a column as unique based on another column value

I want to make a column unique, but only if a different column is a specific value. Consider the following table: CREATE TABLE [SampleTable] ( [Id] INTEGER NOT NULL IDENTITY(1,1) ...
4
votes
0answers
117 views

Log shipping and SIMPLE recovery mode

We have the need of implement the log shipping in our production environment, and we'll do this on the next weeks. In the other hand, we do monthly a "cleanup" of the database: we set the database in ...
2
votes
4answers
74 views

How can I compare the schema of two databases?

Is there a way to find the differences in two SQL Server databases (schema only). One is local and the second is at a customers site. We are experiencing problems with crystal reports running some ...
1
vote
1answer
45 views

How to use a variable for object's owner (schema)?

In Oracle we can use a variable instead of the object's owner. The value of this variable will be prompted by Oracle when the scripts starts. Something like CREATE TABLE **&owner.**MY_TABLE. I'm ...
0
votes
1answer
42 views

DELETE statement execution cancellation in SSMS

I ran a delete statement with wrong WHERE condition (which deletes all records from table instead of just 10 records) but during execution I found it and CANCELLED the execution in sql server ...
3
votes
2answers
446 views

Efficently Remove 90% of a table's data

I am working on a process to remove 90% of a table's data because only 10% is needed for testing. The best way I have found includes storing 10% of the table's rows into a temporary table. Current ...
0
votes
1answer
117 views

How To Migrate From MSDE 2000 to SQL Server 2008R2 Express

We need to migrate a MSDE 2000 database running on a Windows Server 2003 Standard to a Windows Server 2008 R2 running SQL Server 2008 R2 Express (fresh install). The database is small, about 300MB. ...
7
votes
1answer
127 views

Can SQL Server be configured to backup to a network share through a separate NIC?

We're in the process having our SQL Server instances backup to a network share, but our network admins want to try and divert the bandwidth from the backups through a different network segment. I was ...
2
votes
2answers
525 views

Insert/Update Stored Procedure

My database model is quite simple. I have the following tables: Monitor, Computer, and Monitor_Computer_Map as my mapping table for a many-to-many relationship between the other two tables. I have a ...
3
votes
0answers
92 views

Is it possible to have different authentication mode for SQL Server Database Engine and corresponding SQL Server instance?

I have installed the x64 SQL Server 2008 R2 Express with default settings and run MBSA 2.3 Microsoft analizer (using default settings too). Tried on clean Windows 8 installation (not 8.1) and Windows ...
0
votes
1answer
77 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
40 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
98 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
135 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
76 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
38 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
130 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
4answers
65 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
67 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
46 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
389 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
62 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
69 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
96 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
504 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
42 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
39 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
48 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
117 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
72 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 ...
9
votes
1answer
222 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
146 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
181 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
56 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
147 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
103 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
35 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
24 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 ...