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

learn more… | top users | synonyms

0
votes
1answer
571 views

Where is sp_MSrepl_getpublisherinfo in SQL Server?

In learning about replication in SQL Server and having problems creating a subscription, I looked at the definition for sp_addsubscription. It calls sys.sp_MSrepl_getpublisherinfo, which I cannot find ...
1
vote
0answers
445 views

Why not use SQL_Latin1_General_CI_AS for a global system?

What would be some really good reasons to not use the SQL_Latin1_General_CI_AS collation on a system which handles English, German, Japanese, and Chinese data? I'm having a really hard time finding ...
3
votes
1answer
1k views

Data Collection reports fail because login is from an untrusted domain

I've set up a data warehouse and Data Collection according to BO. Reports are generated properly when running them in Management Studio running on the server but fail when they are run from Management ...
5
votes
3answers
2k views

BUILTIN\Administrators accidently removed

Hi, so recently when trying to fix another bug within my sql server 2008 R2 db I removed the BUILTIN\Administrators login from sysadmin. Now it will not let me change anything winthin the database. ...
4
votes
1answer
130 views

Where to place non clustered index

Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
3
votes
3answers
2k views

SQL Server 2005 Tools makes SQL Server 2008 Express install fail

When I tried installing SQL Server 2008 Express, the install failed due to this line. How can I circumvent this, or fix the issue?
4
votes
2answers
10k views

Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?

I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when ...
2
votes
2answers
599 views

SQL Server 2008 R2 Dormant Users

I would like to know if Too many Dormant Users/sessions on a SQL Server instance can force SQL server to not to create new connections or give connection denied error to the application which is ...
3
votes
1answer
110 views

can sql generate a good plan for this procedure?

I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
10
votes
1answer
14k views

Stored Procedure to return dynamically created table data

Quick back story, we are working with an outside vendor that has a survey system. The system is not necessarily designed the best in that when you create a new survey and the system creates a new ...
3
votes
1answer
3k views

Management Studio all buttons disabled

I get an odd problem in my SQL R2. Although I can attach a new db to my db instance, but all buttons on the tool menu panel are disabled. So I cannot execute any sql query statements. It is really ...
4
votes
2answers
724 views

Poor performance after changing SQL Server 2008 R2 database

Need some tips on what I can do to track down this problem. My client has a modest sized SQL Server 2008 R2 database (approx 6 gigs in size). Last week I modified the database to add new a single new ...
10
votes
2answers
4k views

Good way to call multiple SQL Server Agent jobs sequentially from one main job?

I've got several SQL Server Agent jobs that should run sequentially. To keep a nice overview of the jobs that should execute I have created a main job that calls the other jobs with a call to EXEC ...
2
votes
2answers
2k views

difference between having many non clustered index with single columns and with combination of many columns

Folks I am a newbie to DB design stuffs. I am wondering what would be the difference and performance difference between having designed many non clustered index with single columns and non ...
2
votes
2answers
285 views

Stored procedure compilation blocking due to many parameters to sp_executesql

I am seeing blocking due to locks on compiling a particular stored procedure (as described in KB 263889). Basically, several processes are waiting with LCK_M_X on the same resource "TAB: ...
3
votes
1answer
108 views

VARCHAR NULL storage

I read somewhere online and in a Microsoft Press book there is a field declaration attribute that can be used to indicate NULL VARCHAR values should not consume space at the leaf-level instead of ...
8
votes
2answers
10k views

Safely moving and creating new tempdb files

Two things I'd like to know, how do you safely move tempdb with minimal downtime? Also, how do you create new tempdb files? Is it 1 per core? So quad-core = 4 tempdb files, creating three new ones?
1
vote
1answer
94 views

Where does it make sense to store Indexes for partitioned tables?

Prior to table partitioning, all the indexes were stored on a separate drive, per this question. Now that I partitioned a couple of tables, SSMS is offering to store indexes in the partition scheme. ...
3
votes
1answer
579 views

Why am I getting Table scan on a partitioned table query?

I have table Inventory_Break partitioned on IR_ID (int). I also created a non-clustered index for IR_ID on the table. When I run the following: SELECT * FROM dbo.INVENTORY_BREAK WHERE IR_ID ...
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 ...
13
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
546 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
247 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
2k 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
8k 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
692 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
120 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
353 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
677 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
10k 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
890 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
597 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
259 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
466 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, ...
-1
votes
2answers
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
586 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
8k 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
607 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
810 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 ...