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

learn more… | top users | synonyms

4
votes
1answer
423 views

Raising an error with a system message_id in SQLCLR

If call CREATE DATABASE foo twice I get the error message: Msg 1801, Level 16, State 3, Line 1 Database 'dropme' already exists. Choose a different database name. The error message is listed in ...
3
votes
1answer
880 views

Multi-tenant database using SQL Server 2008?

I am designing a database using a shared schema containing the "security views" to which all logged tenants will be directed. I read the msdn article on Multi-tenant Data Architecture. I understand ...
2
votes
1answer
116 views

Is it worth to update to an x64 environment?

Situation We have 10 to 20 Tomcat web applications with hundreds of concurrent users per application on a server. Each Tomcat application connects using its own user to a database on Microsoft SQL ...
4
votes
1answer
162 views

Security Question involving Sys tables

We have an application user which has been given read write to the appropriate databases. This user has the ability to query the sys tables by default. I've also heard that there are times when this ...
4
votes
2answers
4k views

Deny access to information schema in SQL Server

I am looking for the best way to disable access to the sys.tables / Information Schema for a user / group in SQL Server. I found this thread from 2008 It shows a way how to deny access on ...
3
votes
2answers
133 views

index maintenance strategy if few insertion points relative to # of rows

In SQL Server 2008 R2, I have a nonclustered covering index on multiple tables with 100M+ rows. The table has a few thousand "insertion points" where all new inserts happen. This means that regardless ...
11
votes
3answers
3k views

SQL Server database synchronization

Problem definition Our users need the ability to query a database that is mostly up to date. The data can be stale up to 24 hours and that is acceptable. What would be the lowest cost approach to ...
2
votes
2answers
649 views

SSRS Report dependencies

Is there an automatic way to track reports and their dependencies for SSRS? What I want is a map/tree where I can see whether a report depends on a other report or subreport. So far we did this by ...
0
votes
3answers
391 views

SQL Server 2008 R2 Remote Connection issue

I have installed SQL Server 2008 R2 in mixed mode, but currently issue is I can't use SQL through ip nor ip with port, even I have enabled TCP/IP and open port in firewall too. I tried to telnet ...
3
votes
2answers
800 views

User can alter procedures without permission

I have a sql server user that our application is connecting as. The user has been granted db_datareader and db_datawriter as well as Grant Execute on each stored procedure that it needs to use. ...
25
votes
4answers
3k views

Still wrong to start the name of a user stored procedure with sp_?

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
2
votes
1answer
81 views

Differential backup successfully completes without taking the backup of few databases

I have differential backups for multiple databases configured to run through a stored procedure. Sometimes one or more databases have not been backed up, even though the job was "successful." I'm ...
2
votes
0answers
218 views

Stored procedure doesn't return results or any messages when executed as other privileged user [closed]

I have many stored procedures with... EXECUTE permission given to role XXXUser. Login XXXLogin is created and assigned to the role of XXXUser There is one particular stored procedure that I have ...
8
votes
1answer
947 views

Does SQL Server Snapshot replication completely copy the data every time or does it issue deltas?

I am looking at snapshot replication between two servers. Here is what I have: 500GB database ~500MB nightly bcp loads ~50MB daily transactions I was asking other DBAs in the company about which ...
5
votes
2answers
3k views

Automate daily backup of database

I am trying to create automate/daily backup of database in SQL Server 2008 R2. I did bit of research and I get to know that as I am using Express edition; which doesn't come with Maintenance Plan, I ...
2
votes
2answers
1k views

renaming a sql server with installed named instances

We have a hosted server that is named, by the hosting company, as something like WinEnt99. We would like to rename it to something more meaning like OurMonitor1. We have already installed a few ...
3
votes
1answer
213 views

SQL Server Snapshot Replication and SRDF compatability

Is there anything that breaks down if you use snapshot replication and then use SRDF for disaster recovery (DR) on the snapshot publisher? Specifically I am asking is: On the DR system, would the DR ...
5
votes
2answers
4k views

Running SSIS from a different server than the primary database instance

We currently have a single SQL Server Integration Services (SSIS) package that performs an extract-transfer-load (ETL) process to move some data from SQL Server to DB2. On the current production ...
8
votes
2answers
2k views

Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100

I have a SQL Server 2008 R2 server with a bunch of published databases that are currently operating under compatibility level 90 (2005). The subscription databases are also SQL Server 2008 R2, ...
4
votes
2answers
7k views

How to debug deadlocks in SQL Server 2008 R2?

I'm using SQL Server 2008 R2 that automatically lock database items based on my queries. Sometimes this deadlocks - two threads with different queries never ends. Is there any way to debug how SQL ...
2
votes
1answer
1k views

Remove an IP address from server config

What is the safe way to remove an old IP address from SQL Server configuration? The scenario is, I have a server with 2 NIC's that should have been configured for teaming but weren't at the time SQL ...
1
vote
1answer
130 views

Upgrading to SSMS with SQL Web Edition turned off Code Outlining : How do I get it back?

I upgraded to SQL Server 2008 R2 Web from Express with Advanced Services recently and code outlining went away. How do I get my collapsible code back? Thanks
1
vote
1answer
573 views

how to capture the data using DMV's in sql server 2008 r2?

Hi i am working on sql server 2008 r2, i have a situation where i wanted to capture the DMV's data into a table, i would like to know when DMV's tables get refreshed or data flushed out. because as ...
3
votes
0answers
490 views

SQL Server 2008 R2 clustered index issue

Last week, we came up with a strange issue on Clustered index. We are using SQL Server 2008 R2 in our organization. A stored procedure is written for our application which contains a cursor in it. ...
3
votes
1answer
459 views

Why does Management Studio add the second alter table “CHECK CONSTRAINT” for a foreign key constraint

ALTER TABLE [dbo].[REMINDER_EMAIL] WITH CHECK ADD CONSTRAINT [FK__REMINDER_EMAIL__FACILITY_ID] FOREIGN KEY ( [FACILITY_ID] ) REFERENCES [dbo].[FACILITY] ( [FACILITY_ID] ) ALTER TABLE ...
3
votes
2answers
5k views

Disabling Checking of Schema On Function/Stored Procedure Creation

I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database. The process I put in place drops and recreates my stored procedures and functions, as well as run scripts ...
1
vote
2answers
633 views

SQL Log Shipping Firewall settings [closed]

I'm attempting to setup log shipping between two differnet domains. When I attempt to connect to the secondary server instance the primary server fails to connect. If I turn off the windows firewall ...
4
votes
2answers
832 views

Restore all applicable files in a backup device in T-SQL

I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a ...
0
votes
0answers
625 views

Unable to view “Maintenance Plan” folder under Maintenance in SQL Server 2008 R2 Standard Edition

SQL Server version is as follows : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) Copyright (c) Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service ...
2
votes
1answer
405 views

SQL Agent job does not show up in the jobs folder in SSMS although they are in sysjobs table

I tried to copy some SQL Agent Jobs from server1 to server2 by selecting rows from server1.msdb.dbo.sysjobs and inserting to server2.msdb.dbo.sysjobs. I did the same with sysjobsteps. This seemed to ...
2
votes
3answers
971 views

How to find out what caused a schema change?

I am investigating some performance issues on my SQL Server 2008 R2 server. Some queries are being recompiled due to a "schema change". I am pretty sure no one is changing the schema. All databases on ...
3
votes
1answer
435 views

SSMS login to different domain

Our production database is hosted by another company on their domain. I'm able to RDP into the box and login with the credentials they provided me but when I try to login from my local computer on my ...
2
votes
1answer
321 views

Does a change in a query executed with sp_executesql cause recompilations?

I am experiencing a lot of re-compilations in my database. Most of the queries being executed are simple queries and all of them are being executed using the sp_executesql stored procedure. ...
4
votes
1answer
95 views

How to tell when DBO was changed (and if possible, by whom)

I am looking for a way to track when the DBO account is changed. For example, if someone uses sp_changedbowner is there a way, after the fact, to determine when this occurred, and even better, what ...
6
votes
2answers
158 views

Most effective way to determine the minimum set of permissions for user

I have a database that an application connects to using db_owner permissions. How do I effectively determine the minimum set of requirements actually needed by this user (application) to run without ...
9
votes
1answer
465 views

Initializing Transactional Replication From Backup

There is an option when setting up a publication for replication to Allow Initialization From Backup. We have been creating replicated databases for several years now and have always initialized from ...
7
votes
3answers
1k views

MDF and LDF contents in SQL Server

Let's say I have a database with a single .mdf and .ldf file. The database is detached, there are no transactions and SQL Server is stopped. I am presuming that all the data is already in the .mdf ...
3
votes
1answer
885 views

Log shipping setup across domains

I'm running SQL Server 2008 R2 on a Windows machine. I've been tasked with setting up log shipping between our production site (managed by a service provider) and our QA environment which is hosted ...
1
vote
0answers
170 views

Configuring SQL Server 2008 R2 Change Data Capture with SSAS

I'm completely new to SQL Server Analysis Servers (SSAS) so you'll have to excuse my ignorance. I've been running through the MSDN SSAS tutorial get a handle on how to use SSAS. We have SQL Server ...
2
votes
1answer
905 views

SSMS 32bit can't connect SSIS 64bit

I can't find the solution and searching for info doesn't give desired results Environment: Server: W2K8 EE 64 bit + MSSQL SE 2K8 R2 64 bit Client: Windows XP Prof 32 bit Using SSMS on client ...
2
votes
0answers
303 views

Auditing - parameter values not being written to log

I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The ...
4
votes
1answer
780 views

Replicating multiple databases into one database

I would like to replicate multiple databases on different servers into one master database. I have 5 different instances on separate servers all running SQL 2008 R2. The schemas and structure are all ...
-4
votes
1answer
548 views

Why does a VIEW with a CLUSTERED index not use the index?

I have a view that has a clustered index along with other non-clustered indexes. A simple SELECT query does not use any of the indexes even when I select an indexed column. What could be the ...
2
votes
2answers
5k views

SQL Server: Should we use TCP or Named Pipes or use the default?

When connecting to a SQL Server 2008 R2 from a .NET 4 client application on a different server in the same LAN, one can set three different network protocols: TCP Named Pipes Don't set anything in ...
5
votes
1answer
11k views

SQL Server 2008 R2 Setup Client Tools Connectivity

During SQL Server 2008 R2 setup I see a checkbox for "Client Tools Connectivity" and "Client Tools Backward Compatibility" installation options. It says these are component for client server ...
3
votes
1answer
2k views

Sql server 2008 R2 installation fails with Error: 17204

Sql server 2008 R2 installation fails with the below error message in the log message, This is a part of the sql server log Starting up database 'msdb'. Error: 17204, Severity: 16, State: 1. ...
5
votes
2answers
2k views

SQL Server Agent not observing “execute as” permissions

I have a curious permissions problem. Something recently happened that caused existing SQL Server Agent Jobs that were configured to run as a particular User to stop working. They previously worked; ...
3
votes
1answer
2k views

Retrieve XML of large plan from plan cache

I'm trying to view (graphically) a large query plan that is in the plan cache and am running into problems where the XML is truncated in the query results. This is the query I'm using: SELECT TOP 1 ...
1
vote
1answer
777 views

Can operations in a loop cause table locks?

I know a bit about locking etc, that when a transaction is working on a table, if it is altering a certain amount of data it might try and lock the entire table (I'm sure that is over simplified, but ...
2
votes
2answers
726 views

Issues with uninstalling and re-installing SQL Server 2008 R2

I have installed SQL Server 2008 R2, and was able to login and also create a database and everything. When I was facing some problems with connecting my .NET project with SQL Server, I created some ...