This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx
1
vote
1answer
32 views
Can you grant an “Active directory Group” rights to linked server in SQL server 2008 R2?
My company uses sql server 2008 R2. I am trying to grant rights to an Active directory Group rights to a Linked Server. I am unable to do it, it only allows me to grant rights to individual active ...
-1
votes
4answers
68 views
Dropping and recreating indexes [closed]
I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness).
I somehow manage to rebuild ...
1
vote
0answers
34 views
Record versioning and promotion
Let's say we have this hierarchy:
-World
--USA
---WA
----Seattle
-----Downtown
------1st Ave
-------945 1st ave
------3rd Ave
...
1
vote
2answers
47 views
Can I trust the Database Engine Tuning Advisor recommendations?
Our database has become large, and we are trying to optimize performance.
Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
1
vote
2answers
58 views
View column has an 8000 character limit?
In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 ...
3
votes
0answers
80 views
Key Lookup and Full-text index
I have the following query:
SELECT T2.Title
FROM TitleTable T1
INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1
AND T2.FKID2 = @ID_PARAM2
AND ...
4
votes
0answers
156 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 ...
4
votes
0answers
292 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, ...
5
votes
0answers
789 views
Deploying dacpac application to SQL Server 2008 R2 [closed]
I cannot get dacpac working on my installation of MS SQL Server 2008 R2. I am using Visual Studio 2010 Shell with MS SQL Server Data Tools to build a project into a dacpac file. However, when I try to ...
2
votes
2answers
106 views
How to restore a filegroup from its backup in SQL Server
I need guidance on how to restore a file group in Database from the File group backup taken from another same DB server. Is it possible to restore the filegroup by running restore DB for File Group.
...
4
votes
1answer
114 views
MI Data Warehouse Advice
I have recently started a new job and part of my remit is to try to rescue the Management Information (MI) Data Warehouse. I use the term Data Warehouse very loosely here!
The server setup is:
...
2
votes
1answer
41 views
Can I decrypt TDE transaction log files for use with 3rd party software?
Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to ...
4
votes
1answer
75 views
Is it “legal” to CREATE and DROP #SomeTable more than once?
I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this:
CREATE TABLE #WidgetSetting
...
2
votes
1answer
48 views
The Login is from an untrusted domain - domain setup with computer level login
I have a Sql Server 2008 R2 database I'm attempting to connect to using ADO.NET using integrated security in the connection string.
The database resides on a 64 bit Windows Server 2003 machine. The ...
2
votes
2answers
45 views
Restore database created in SQL Server 2008 R2 to SQL Server 2008 R2 SP2
I have a .bak file created by some other user in (I think) SQL Server 2008 R2 (10.50) and I need to restore it in SQL Server R2 SP2 (10.52). I used a hex viewer to look into the .bak file and found ...
4
votes
3answers
77 views
Auditing specific database events
For auditing purposes, we have a requirement in which we have to enable Auditing for theses events:
DBA Logins
Changes made by DBAs LIKE INSERT, DELETE, UPDATE, ALTER (etc.)
Changes made by our ...
3
votes
6answers
107 views
Backup very large table
I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
5
votes
1answer
135 views
Reindexing plan
I have a reindexing plan failing with error message:
Refresh failed for PhysicalPartition '1'
(Details: SQL Server Enterprise edition running in cluster)
Searching through BOl and other online ...
1
vote
1answer
375 views
SQL Server Alert Based On Severity Level Not Working
I am new to SQL Server alerts, and I am trying to setup an alert That will run a job and send a notification, based on severity level.
In order to test if the alert works correctly I have downloaded ...
1
vote
2answers
55 views
Update my local db based on scripts executed in my server database
I need to create an solution that update my local db version based on changes that was made on my server database.
First, I need to check the differences that has exists between these databases and ...
5
votes
1answer
66 views
SA permissions issues with many nested objects
I have a broker application that's relatively complicated.
Today, after I made some changes, I started getting the error:
The server principal 'sa' is not able to access the database 'XYZ' under ...
2
votes
1answer
52 views
Is this a good strategy for importing a large amount of data and decomposing as an ETL?
I have a set of five tables (a highly decomposed schema for an ETL if I understand the nomenclature) that I'm going to load via bulk import, then run some inserts from those five tables into a SLEW of ...
1
vote
3answers
127 views
Logging queries and other T-SQL
I would like to know if SQL Server 2008 R2 has a default logging scheme for SELECT statements (or any other T-SQL for that matter).
If yes, where can I see it? If not, how could I set it up?
3
votes
1answer
170 views
Applying user-defined fields to arbitrary entities
Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and ...
1
vote
1answer
30 views
mdw_purge_data_ long runtime
I have a SQL Server 2008 R2 that is running several databases and has also been configured as our central MDW.
When the management data warehouse is created it creates a number of agent jobs one of ...
1
vote
1answer
70 views
Use a condition to limit the databases a policy checks
I'd like to create a Policy that checks, on demand, one database in an instance for tables that have no index. I can see how to create a Condition, linked to the 'Database' Facet and checks for my ...
1
vote
1answer
60 views
How do I specify a linked server to a remote database over tcp/ip?
I have a remote database running on an IAAS provider and I need to be able to execute joined queries from my local workstation. I am having trouble because when I attempt to create the link it is ...
-1
votes
1answer
28 views
VarChar to NVarChar causing truncate error
I am importing some data from a temporary table which has a column defined as VARCHAR(150). The destination tables column is defined as NVARCHAR(200). I'm using an INSERT INTO statement, but when I ...
2
votes
1answer
70 views
Attaching a database in SSMS gives error
I am accessing SQL Server using SSMS, and a user with adequate permissions.
When this user tries to attach a database by using SSMS (by right clicking on database node and then clicking on attach), ...
1
vote
1answer
200 views
INSERT INTO #temp taking 1000 times longer than SELECT INTO #temp
I am shredding an xml blob that is being passed to me and loading a temp table for further processing. One of the things I noticed while working through the process is that performing an INSERT INTO ...
1
vote
4answers
147 views
Speed up large clustered index creation?
I have a large table, the row count of the table is more than 3 billion, the data space for this table is about 120 GB.
And Intel Xeon CPU E5645 @2.4GHz(2 processors), 24 CPUs, 64G memory, 64bit ...
4
votes
1answer
134 views
Indexes file damaged, was on ramdisk
I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
0
votes
1answer
48 views
Disabled trigger still fired
I've just run the following t-sql to disable a trigger (with a deploy script wrapper around osql):
disable trigger dbo.trExampleTableInsert ON dbo.ExampleTable
In SSMS I could see the trigger was ...
9
votes
5answers
371 views
Force sql server to run query conditions as written?
Im using Sql server 2008 R2 And I have this pseudo query (SP) :
select ...
from ...
WHERE @LinkMode IS NULL
AND (myColumn IN (...very long time exeted query...))
...
...
The ...
2
votes
2answers
78 views
Trying to write a function that doesn't require dbo. prefix
I am trying to write a simple system function so that the dbo. prefix is NOT required to access the function. I have found an example of this for SQL Server 2000 but when I run
EXEC SP_CONFIGURE ...
0
votes
1answer
98 views
Real-time merge of two databases on different servers
One of the requirements our customer has given is to merge two databases running on two different machines in real time. This means that we have to build some sync policy (or use SQL Server tools to ...
-1
votes
1answer
33 views
Directory lookup failed during log shipping
I have configured log shipping for all our SharePoint 2010 databases. All worked well since Friday and today Monday I ran this query:
SELECT *
FROM [msdb].[dbo].[sysjobhistory]
WHERE [message] like ...
2
votes
1answer
30 views
Audit full text activity
I need to find when and which user created full text catalog, and who added/modified/deleted the columns (that were full text indexed) in that full text catalog.
2
votes
2answers
55 views
Constraints based on other columns
Is it possible to limit what values are allowed in a coulumn based off of other values in the row?
For example, my table:
ID Test_mode Active
-- --------- ------
1 1 Null
2 0 ...
0
votes
1answer
31 views
Database size not reduced after compressing tables
Using Data Compression, my table size was reduced from 1770 MB to 250 MB, but the database size was not reduced. Why?
0
votes
3answers
122 views
Merge Replication identity field issues
One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of ...
4
votes
3answers
63 views
How to reduce network latency during mirroring?
We are using SQL Server 2008 R2 Standard edition for mirroring.
But we found out that network latency is too high. It takes nearly four times time to complete a transaction while mirroring is active. ...
1
vote
1answer
324 views
SQL Server Login failed for user Error: 18456, Severity: 14, State: 11
I have an AD group XYZ that I have added to SQL Server security with data_reader permissions.
The XYZ group has around 10 users in there who are successfully able to access the SQL Server database. I ...
2
votes
0answers
6k views
SQL Server named instance - can connect remotely, but not locally
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 ...
1
vote
1answer
38 views
Moving one TempdB on 3 instance server
I have a large virtualized SQL Server (Full 2008R2).
I run 3 SQL instances and would like to relocate the TempdB database file to another location, splitting off from the TempdB log file.
The trouble ...
-1
votes
3answers
92 views
How to restore SQL Server 2008 R2 backup to SQL Server 2008 [duplicate]
I am unable to restore a backup from a 64-bit instance onto a 32-bit instance.
Source Server:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Destination server:
Microsoft SQL Server ...
1
vote
2answers
91 views
Working Linked Server Query fails in sp_send_dbmail
Take the following example:
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'me@whatever.co.uk' ,
@query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' ,
@attach_query_result_as_file = N'True' ...
0
votes
0answers
43 views
Copy tables from a stand-by database
I want to explain the following. I have a database that is a recovery db from our main environment. This database is kept up-to-date through log shipping. However, I want to copy all tables from this ...
0
votes
1answer
29 views
distribution.dbo.MSdistribution_history comments explanation
The comment column of distributor.dbo.MSdistribution_history contains comments such as
<stats state="2" fetch="11554" wait="214007" cmds="17898546" callstogetreplcmds="106880">
...
1
vote
3answers
71 views
Document database structure for auditors
I am DBA for a Microsoft SQL Server 2008r2.
Due to an audition, I need to provide data structure of my database, to people without an explicit IT background.
I guess they would like the traditional ...
