All Questions
Tagged with sql-server-2008-r2 sql-server-2005
52 questions
18
votes
3
answers
44k
views
EXCEPT operator vs NOT IN
The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ?
Does it do the same? I would like a simple explanation with an example.
- 1,505
14
votes
3
answers
24k
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 [sys].[...
- 359
11
votes
2
answers
10k
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, ...
- 430
9
votes
2
answers
3k
views
What characters are word breakers in English for SQL Server 2005 and 2008 R2?
I can find what DLL supports English word breakers by using sp_help_fulltext_system_components but I have not been able to find an actual list of the word breaking characters for English (like blank, ....
- 91
8
votes
3
answers
2k
views
Risks involved in upgrading to SQL Server 2008 R2
We have quite a number of SQL Servers that need to be upgraded from version 2005 to 2008 R2. Work is planned before the middle of the year as Microsoft is ending its support for the same.
The 2005 ...
- 2,210
7
votes
2
answers
5k
views
How to find out which host / IP address / program performed a delete?
We are using SQL Server 2005. In our database some rows are deleted; how can I find the system (host name / IP address), program and date & time of deletion?
- 71
7
votes
1
answer
7k
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
...
- 1,258
6
votes
3
answers
8k
views
Is there a way to execute SQL statement based on SQL Server version?
I would like to create computed index on a table if SQL Server is 2008 or newer and a simple index if SQL Server is 2005 or older:
-- check for sql server version
if (select cast(left(cast(...
- 233
6
votes
4
answers
192
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 ...
- 261
6
votes
1
answer
2k
views
Preventing users who are dbo from dropping their own databases
We allow our users to be dbo in their own databases, however this does give them the ability to drop their own databases. This doesn't happen very often, but I want to prevent them from doing this.
I'...
- 1,336
6
votes
3
answers
4k
views
Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance
We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like
PK BIGINT
ID VARCHAR(50)
Col1
Col2
etc
So we need to insert a row in the ...
- 161
5
votes
3
answers
3k
views
One of the Database Ownerhas been Removed and corresponding property changes in sql server
A database has been created on SQL 2008 by a user named user1 ,on dec 2013.Today ,we are going to remove that user(/owner) from the server.What will be the owner property of the database show then?
user1254579
5
votes
2
answers
8k
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.
...
- 151
4
votes
3
answers
5k
views
Attempting to restore SQL Server 2005 database to a SQL Server 2008 R2 instance gives reason: 15105 error
I have a SQL Server 2005 database I am attempting to restore a SQL Server 2008 R2 SP2 machine using this statement:
RESTORE DATABASE [Db]
FROM DISK = N'C:\temp\sql\DB.bak' WITH FILE = 1,
MOVE N'...
- 161
4
votes
3
answers
3k
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 ...
user1494358
4
votes
1
answer
166
views
What started this SQL Server job?
I'm looking at a SQL Server environment in which jobs start in several different ways. One way is from a job running on a different SQL Server.
So, for example, "SQL Server A" runs a job named "...
3
votes
1
answer
4k
views
What are the most compelling reasons to upgrade from SQL Server 2005 to SQL Server 2012?
We currently have 2 SQL Server 2005 Enterprise machines in a mirrored environment running on Dell R815 servers. These machines consist of:
Four AMD Opteron 6176 Processors (12 cores at 2.3Ghz), for ...
- 70.5k
3
votes
1
answer
5k
views
Best way to migrate a database involved in bidirectional replication
I have two SQL Server 2005 that are to be migrated to SQL Server 2008 R2.
The original pair of servers contain a database that are involved in bidirectional transactional replication between them. In ...
- 61
3
votes
1
answer
6k
views
Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box
We currently have a SQL Server 2005 instance hosting a lot of application specific databases (ArchiveManager, SolarWinds, Kaspersky etc). We are planning to deploy a new SQL Server 2008 R2 instance on ...
user20442
3
votes
2
answers
24k
views
How to make SQL Server Management Studio use latest version of SQL Server?
I'm upgrading my dev machine from Windows 7 to 8. While installing earlier versions of Visual Studio it installed SQL Server 2005. I then went to this site and downloaded and installed SQL Server ...
- 133
3
votes
2
answers
361
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 ...
- 33
3
votes
0
answers
69
views
Any checklist/guide when transferring a database to a new server? [duplicate]
I'm looking for steps that I need to follow to transfer a database to a new server. So far I have:
I restored the current database that I have in SQL Server 2005 instance to another SQL Server ...
- 655
2
votes
1
answer
19k
views
SQL Server - How can varbinary(max) store > 8000 bytes?
In SQL Server 2005 and 2008 R2, I'm calling into a stored procedure that has an out parameter defined as varbinary(max). The out parameter returns 10020 bytes according to DATALENGTH.
However, SQL ...
- 215
2
votes
1
answer
1k
views
SQL Server Database Keeps Semi-Detaching Itself
Forgive me if I am not using all the proper terms but I have databases that will detach themselves from SQL Server. In SQL Server Management Studio the plus sign will be missing next to the database, ...
2
votes
1
answer
1k
views
Migrate database from SQL Server 2005 to SQL Server 2008 R2
DBAs... please be gentle with me, I'm a 'greenhorn' SQL Server DBA, pressed into service because my company's DBA had resigned and we haven't found his replacement, yet.
First, my situation:
I'm ...
- 187
2
votes
1
answer
8k
views
SQL Server : how to disable connections to a linked server?
SQL Server Linked Server to default instance - service is disabled but connection is still possible
I have a server called SWLON1 that has SQL Server 2005 and SQL Server 2008 r2 installed.
The SQL ...
- 16.6k
2
votes
3
answers
1k
views
SQL Server Pagination using CTE
I have to apply paging on query as per requirement i need two extra columns other than query result
Total Records
Total Pages
but i am trying to confirm that this the right way to retrieve total ...
- 4,073
2
votes
1
answer
993
views
Script file execution through Power shell
We are trying to automate few things, one of them is to deploy patch in one go on selected DBs of SQL Server, as per my knowledge Powershell is an option. But I have zero taste buds of Power shell.
...
- 4,073
2
votes
2
answers
264
views
Impact of using mirroring to migrate between SQL Server 2005 and SQL Server 2008 R2
I've got a DB which sits on a machine (SQL Server 2005) due for replacement very soon.
I'm planning on having the database mirrored to the replacement machine (SQL Server 2008 R2) so that when the ...
- 430
2
votes
0
answers
261
views
Unexplainable behavior of SQL Server under Windows Authentication
The versions affected are SQL Server 2005, 2008, 2008 R2.
If we grant CONNECT SQL to public server role, any AD account can log in to server.
So we'll have all the AD members on the server without ...
- 11.2k
2
votes
0
answers
711
views
SQL SERVER 2005 slow Performance on Windows Server 2008
Recently I have upgraded my server to new one. But because of compatibility issues with my old Windows Server 2003 R2 32 bit, I have also upgraded it to Windows Server 2008 R2 64 bit.
(I have ...
- 21
2
votes
2
answers
3k
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 ...
- 751
1
vote
1
answer
757
views
Recommendations on creating user-objects on system database
I want to setup a bunch of Event Notifications on SQL Server using Service Broker for our production database.
I've setup a new database on my testing machine, and everything works well.
The problem ...
- 2,323
1
vote
3
answers
631
views
Upgrading to SQL Server 2008 R2 but downgrading the edition - will this cause problems?
I am looking at upgrading our SQL Servers from 2005 Enterprise edition to SQL Server 2008 R2 Standard edition.
Obviously, the upgrade to 2008 R2 gives us more features even in Standard edition, but I ...
HAdes
1
vote
2
answers
327
views
Error while running sp_blitz in 2008 SQL Server and lower versions
I have created an SSIS package to run sp_blitz in my production servers. The stored procedure runs fine for servers having a version higher than 2012 but fails for 2008 and 2005.
Please find the ...
- 908
1
vote
1
answer
314
views
How to confirm if the backups are going to tape in SQL Server 2005 and 2008 R2
We have many of our SQL Servers where we take the backup locally on the server as well as over the network to other server in case of disaster.
Also, we have a process set that server support team ...
- 7,177
1
vote
1
answer
1k
views
Setting Alerts for Autogrow
Can I set alerts for an autogrow event in SQL Server (2005 or 2008 R2)?
I've searched around and haven't found the 'error number' for autogrowth, if there is one.
In case this isn't possible, how ...
- 2,323
1
vote
1
answer
87
views
microsoft sql server schema [closed]
From blog.sqlauthority.com:
Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such ...
- 51
1
vote
1
answer
275
views
SQL logins Audit for SQL Server 2005/2008
For the Security Audit of SQL servers 2005/8 R2,
We are looking for a method which can list us SQL accounts which have not changed password for last X days. For a single login we can do it via ...
- 7,177
1
vote
1
answer
328
views
SQL Server 2008 Analysis Management objects work with SQL Server 2005?
Can SQL Server 2008 Analysis Management objects API work with SQL Server 2005 install?
Ngm
1
vote
1
answer
658
views
Send a trigger to an application upon a specific SQL Server server usage pattern
I've been dealing with a vendor's unconfigurable application which is, unfortunately, the most feature-complete in its niche. I'm looking to augment its capabilities with on-top screen display. Its ...
- 327
1
vote
1
answer
5k
views
"Request to run job dbxxxxx refused" error in merge replication
I'm doing merge replication whole of my database but when I create the subscription I encounter this error:
SQLServerAgent Error: Request to run job DBXX-XX--bazresiostani2-2 (from User sa) refused ...
- 269
1
vote
0
answers
142
views
SQL Server Reporting Services Upgrade/Migration
I couldn't find any documentation on this. I'm currently doing a dry run of SSRS 2005 to SSRS 2008 R2 upgrade/migration. I've backed everything up (encryption key and configuration files) and did a ...
- 226
1
vote
1
answer
754
views
SQL Server 2005 Cluster Service pack 4 upgrade failure at remote user account
I built SQL Server 2005 ENT Cluster on Windows Server 2008 R2. I need to upgrade it to SQL Server 2005 SP4. However, I get this error at the Remote User Account screen
Enter the remote user account ...
- 41
0
votes
1
answer
4k
views
Restoring a SQL Server 2008 R2 backup on SQL Server 2005
I'm getting an error whenever I restore a testing database on SQL Server 2005 that was backed up from SQL Server 2008 R2. It seems that I cannot restore from a higher version of sql onto lower version,...
- 655
0
votes
1
answer
234
views
SQL Server Mirror failover on failing storage
Recently we had an issue with a sql server 2008 r2 HA mirror. I have tried to reproduce this in a lab environment and i came to the conclusion that i am missing something OR something is not possible.
...
- 133
0
votes
1
answer
131
views
Collecting trace info of SQL Server 2005 using SQL Server 2008 R2 profiler
I have a vs2005 application that uses SQL Server 2005. I was trying to collect the SQL trace generated by the application while executing. For collecting the trace I've connected to SQL Server 2005 ...
- 111
0
votes
1
answer
46
views
Linked-server query against remote DB does NOT fail when DB is in SINGLE_USER mode
This is really strange to me. I have a primary server Foo2005 and it has a linked-server entry pointing to Bar2008R2; say the name of the linked-server is BarLINK. I write a query such as SELECT * ...
- 804
0
votes
1
answer
66
views
Sub Query vs Table Filter
I have have been assigned to resolve Performance issue of a Stored Procedure.
Select TableTwo.Column1, DatabaseTwo..TableThree.Column2, Sum(TableTwo.Column3) As Column3
--- Section 1
from ...
- 4,073
0
votes
0
answers
46
views
How to Split string into multiple values using best approach [duplicate]
We are trying to save result of a query in database by tokenizing a column but query is taking more than 3 minutes to finish his objective. My question is, do we have any faster way to tokenize a ...
- 4,073