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

learn more… | top users | synonyms

0
votes
1answer
5k views

How to give SQL Server Job Owner permission to Proxy a Credential

I've created a Credential Cred1 (DOMAIN\USER1 with the secret = domain password) I've created a SQL Agent job with owner to NT AUTHORITY\SYSTEM The job needs to proxy as credential Cred1 So when I ...
14
votes
3answers
4k views

Does the partition key also have to be part of the primary key?

I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, ...
14
votes
1answer
609 views

How does SQL Server know predicates are correlated?

While diagnosing SQL Server 2008 R2 queries with poor cardinality estimation (despite simple indexing, up-to-date statistics, etc.) and hence poor query plans, I found a perhaps-related KB article: ...
0
votes
3answers
252 views

Work out date difference when there is only one date

I have 2 records, one details when the stop clock was stopped and the other started. I only have the one date value in each record. I'm trying to work out the difference between the stop and start ...
0
votes
2answers
3k views

Conversion failed when converting date and/or time from character string [closed]

I have a problem with this query: SELECT @dateforaftersixmonth=U_PYMON FROM [@CCS_PYRUNPERIODS] WHERE @STRTEMP_DATE between U_BEGDA and U_ENDDA @STRTEMP_DATE is a datetime variable and ...
0
votes
3answers
6k views

Database is not accessible after 'taking it offline' process failed

I was trying to detach the DB when it gave me an error that it is currently in use. Hence, I tried to take my DB offline but it failed saying 'an exception occured while executing a transact SQL ...
11
votes
4answers
3k views

Rebuilding Indexes, DB now 10x the size

I have a SQL Server database (2008 R2 SP1) that was about 15 gigs. It turns out maintenance hadn't been running in a while, so I created a maintenance plan to rebuild all the indexes, they were very ...
2
votes
0answers
9k views

SQL Server named instance - can connect remotely, but not locally [closed]

I have an instance of SQL Server on my machine. Machine: Bert01 SQL Server instance: Bert2008 I connect to it using SSMS putting Bert01\Bert2008 in the Server name box, using sa and the . When I ...
3
votes
1answer
722 views

DENY EXECUTE on one schema object hides other objects in same schema

I have five stored procedures in a schema [XTR], the users are all in one AD group [AD\Users] and have db_datareader and public assigned to them. On one of the stored procedures I don't want to give ...
5
votes
3answers
2k views

Write to Error Log

Using SQL Server 2008 R2: How can I write to the SQL Server Error Log? I have a ROLLBACK statement that I'd like to couple with a statement written to the error log for external monitoring. ...
5
votes
4answers
124 views

Syncing Database for Server Transition

I'm transitioning from an old web/sql box combined to a much more robust solution on a completely different host (800hosting to RackSpace). Our application has very high uptime requirements. It runs ...
8
votes
1answer
401 views

What is the actual lowest possible positive REAL number

MSDN says that the range of REAL numbers is - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. Apparently the true lower limit is much lower. The following script populates a REAL column ...
2
votes
2answers
700 views

What could trigger that a login created from a certificate is not visible in metadata

I am trying to create a login and grant it rights to run and unsafe assembly but the check on server_principals does not return the login when it already exists. The script works perfectly fine on my ...
4
votes
3answers
2k views

SQL Server 2005/2008 system timezone

I work on a ship that has multiple SQL Servers, both 2005/2008 versions. As the ship moves from area to area so does our timezone. Sometimes we can jump ahead one hour and then back again. My ...
5
votes
2answers
2k views

Setting up filtering for server-side tracing

I'm trying to set up a server-side trace in SQL Server 2008 R2. While I have no trouble selecting events and data columns, the filtering seems a bit odd... I was trying to set up a filtering on both ...
4
votes
1answer
1k views

SSIS Lookup Component Irregularities

I'm seeing odd behaviour in a dataflow with a lookup operation. If I select 'full cache' mode, some portion of the lookups are failing and I have no explanation why. 'No Cache' works correctly, as ...
2
votes
1answer
11k views

What is different between SQL-Server versions 2008R2 and 2012

Dear SQL Server experts, Can you tell me the different between SQL-Server 2008 R2 and SQL-Server 2012 versions? I designed my product with version 2008 but when I tried to buy a licence, Microsoft ...
4
votes
1answer
1k views

How does SQL Server choose an index key for a foreign key reference?

I am working with a legacy database that was imported from MS Access. There are about twenty tables with non-clustered, unique primary keys that were created during the MS Access > SQL Server upgrade. ...
3
votes
1answer
951 views

Can I Rebuild indexes in a DB except for one table?

I have a table in my database that is constantly monitored by an application server for changes. Unfortunately rebuilding the indexes connected with this table breaks the app server's connection. This ...
4
votes
2answers
5k views

SQL Server 2008R2 - Why is my index not used

I have a table defined in the following way: CREATE TABLE [dbo].[MyTable] ( [MyTable_ID] [int] IDENTITY(1,1) NOT NULL, [COLUMN_WITH_DATA] [varchar](128) NOT NULL, [COLUMN_A] [varchar](128) ...
2
votes
2answers
787 views

How to get SQLCMD to output errors and warnings only?

How can you get SQLCMD, when executing a SQL script file, to just output any errors or warnings it encounters? I essentially dont want information based messages to be output.
1
vote
1answer
264 views

syscacheobjects is cleared randomly

Microsoft SQL Server Web Edition (64-bit) Version 10.50.1600.1 SQL Server 2008 R2 I have noticed that we are getting a lot of execution plan re-complilcations - first executions quite slow, ...
1
vote
0answers
502 views

Create stored procedure to select the data?

I have four tables user, shops, offer, merchant in that id is unique and present in all table shop ------ id shops 1 shop1 1 shop2 1 shop3 user ----- id name 1 abc merchant ...
2
votes
1answer
3k views

Reconcile users with logins using 'sp_change_users_login'

Sql Server 2008 (and 2005): I have a Sql Server instance, let's say, InstA, in a Domain (called AD). In that I have a domain Windows Authentication Login, called AD\Log1 that has an associated user, ...
-2
votes
1answer
3k views

This version of Microsoft SQL Server Management Studio Express can only be used to connect to SQL Server 2000 and SQL Server 2005 servers

I have already SQL Server Management Studio 2008R2 on my local machine. I just installed SQL Server Management Studio 2005 and wanted to connect for the first time. After going through the following ...
1
vote
1answer
637 views

Return a percentage of the full record set

I'm pulling back a list of orders but I need to only display a percentage of the records. for example we want the top 30 or top 50. The percentagbe is variable so I'd like to not use TOP. I tried ...
5
votes
4answers
9k views

Copy Database option not found in SQL Server Management Studio 2008 R2

I am unable to see the Copy Database option in SQL Server Management Studio 2008 R2. Can anyone tell me whats going on please?
2
votes
2answers
652 views

Plan Cache Pages by Database in SQL Server 2008R2

Is it possible to get a break down of how many plan cache pages are being used per database? Perfmon seems to only have the total for the server, but maybe there is a DMV with more detail? Also, does ...
4
votes
1answer
3k views

Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different

Im using query to find out database file size. I'm using system view and DMV, because i do want to find out both- actual file size and theoretical (in case of sparse files) file size. Select ...
4
votes
2answers
856 views

Massive peer-to-peer replication topology?

We have a client that's geographically distributed in remote communities, with somewhat unreliable network/internet connectivity between each of the physical locations. There is a single central ...
1
vote
1answer
1k views

Self blocking session

we are experiencing unresponsive Sql Server 2008 R2 Web Edition. The only way how to connect to the server is via DAC. The dm_os_tasks return 539 tasks almost all SUSPENDED or PENDING. There is one ...
1
vote
2answers
813 views

Copy Database wizard fails but leaves no text file log

I've been using the copy database wizard to create a development copy of my database. Unfortunately, the copy database wizard has repeatedly failed. I suspect the problem is that the sql server ...
2
votes
2answers
784 views

Design best practices for last tracking with indexed view

My tables structure is below : TbDoc (ID int , ...) TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...) I want to have indexed view to get last TbDocActions columns for ...
2
votes
1answer
1k views

User in multiple windows groups mapped to sql logins

I have two C# apps with Windows Auth to Microsoft SQL Server 2008 R2. For every app: SQL login based on Windows Group is created SQL server login is mapped to SQL database user user is member of a ...
1
vote
2answers
594 views

SQL Server 2008 R2 recognizes a domain account as a SQL Server account

After migrating from SQL Server 2000 to SQL Server 2008 R2, we have a few domain accounts that appear to the new server as SQL Server accounts. These accounts can SELECT from tables in the database ...
6
votes
1answer
13k views

SQL Server 2012 performance v SQL Server 2008 R2

Does anyone have any links to performance benchmarking of the database engine, or any anecdotal reports of the performance improvements in SQL Server 2012. We are only using the DB engine,not ...
1
vote
2answers
3k views

Recover registered servers in SQL Server Management Studio 2012

My computer died so it's be reinstalled. Previously I had SQL Server 2008 R2 installed. On the new system I have SQL Server 2012. I have the old RegSrvr.xml file (that contains the list of ...
2
votes
1answer
2k views

Prevent a single query from hogging all resources

I have a production database server running SQL Server 2008 R2. How can I keep a user query on one database from hogging all the system resources and bogging down all the databases on the server? The ...
2
votes
1answer
560 views

SQL 2012: Import DB changes original data scheme

I'm migrating data from SQL 2008 R2 Servers to SQL 2012. Therefore I'm using SSMS Import Data Task on the SQL 2012 machines. As a test I copied the data files of one of the DBs, did an "Attach ...
3
votes
2answers
289 views

SQL database backups methods-tools-procedures

Currently, I am investigating some way to more efficiently backup 4 Production SQL Databases (3 different servers/instances) and would like to know if anyone else has had the problems we are having ...
5
votes
1answer
744 views

Custom aggregation for non-leaf level nodes to avoid double counting

I have a fact table with the following columns: WorkerName OrderId NumberOfPackagesPerOrder Note that neither WorkerName nor OrderId is unique in this fact table. Multiple users could work on a ...
1
vote
1answer
1k views

SQL Server 2008 R2 : multiple files and filegroup

I am getting bit confused regarding advantages of multiple files and filegroups in SQL Server Using SQL Server 2008 R2, I have following filegroups and files configuration Drive D: Primary Filegroup ...
6
votes
2answers
8k views

Create new function by code if it doesn't exist

I want to create new function by script in my database. The script code is below: IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return; CREATE FUNCTION ...
5
votes
1answer
307 views

difference of left join and left outer join

In SQL Server join syntax we can use LEFT JOIN or LEFT OUTER JOIN and query result with each of above join not difference. this is a question for me that which are more useful.
2
votes
1answer
1k views

Cannot CAST '33.33' to INT

I have encountered a problem where the WHERE clause is causing a problem. The varcharColumn is of datatype VARCHAR and contains numbers as well as text. The query looks to be averaging this column, ...
1
vote
0answers
295 views

SQL Server 2008 R2 DeadLock on query Notification

for the first time, I'm running into a deadlock on ...The deadlock graph doesn't help me much except the fact the query notification system seems to responsible. <resource-list> ...
1
vote
2answers
454 views

Tools for Identifying Needed Indexes

I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?
2
votes
1answer
296 views

Calculate number for each row

My table structure is below: CREATE TABLE [ACC].[Document]( [DocumentID] [int] IDENTITY(1,1) NOT NULL, [Date] [date] NOT NULL, [SalesCompanyFinancialPeriodID] [int] NOT NULL, ...
5
votes
2answers
680 views

Release SQL Server Memory usage

How can release memory usage of sql server by command. my system have 16GB RAM, but when get huge query by sql server, memory usage become full. if I restart SQL Server service memory usage become ...
7
votes
1answer
2k views

Recursive CTE performance

Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical data recusively. Table is big with every root id having upto 3 recursive itemid. There ...