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

learn more… | top users | synonyms

0
votes
1answer
87 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
108 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
72 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
41 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
444 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
115 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
125 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
515 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
91 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
75 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
96 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
129 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
64 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
45 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
386 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
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
95 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
476 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
116 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
70 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
221 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
144 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
179 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
99 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 ...
4
votes
1answer
203 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
97 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
161 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
224 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
32 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
183 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
122 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
71 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
32 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 ...