Tagged Questions

This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (1)

3
votes
1answer
669 views

Login problem to SQL Server

I attempt to connect to SQL Server 2008,I receive the following error. Login failed for user <>. Reason: Failed to open the explicitly specified database. By the way, I have a SQL Server 2008 ...
3
votes
1answer
505 views

Sql Server Performance Degradation in moving from 2005 to 2008

I have sql server 2005 setup for my performance bench marking exercise and I have put same database on another server which has sql server 2008 and exact replica in terms of hardware configuration. ...
5
votes
1answer
1k views

SQL Server 2008 Containstable generate negative rank with weighted_term

I have a table with full text search enabled on Title column. I try to make a weighted search with a containstable but i get an Arithmetic overflow for the Rank value. The query is as follow SELECT ...
2
votes
2answers
956 views

SQL 2008, how does it link Full Text Catalog to a filegroup

While creating Full Text Catalog, if "on filegroup" is specified - it creates the catalog on this file, but on MSDN I read "On Filegroup" clause has no affect?
4
votes
1answer
2k views

select permission was denied on the object 'sysjobs'

The select permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'. I have already tried to grant sqlagentoperatorrole and it didn't work. How do I resolve this error?
5
votes
1answer
1k views

Migrate DTS Jobs to 2008 Server

We have been running SQL Server 2000 for years. Finally, at long last, we have an SQL 2008 Server being shipped in. We have many DTS jobs on our SQL 2000 Server that need to be migrated to the SQL ...
4
votes
2answers
5k views

SQL Server 2008 posibilities of having views from one database in another database?

I have a vendor database from I want to extract and transform certain information. As I dont want to change the structure of the original database due to licensing I therefor wondering if I can put ...
3
votes
3answers
235 views

Checking for dupes on Compact Insert Statement

I'm not sure if that is the correct term "Compact Insert Statement" It is just what I have always heard it referred to. It goes as follows: INSERT INTO [tblUsers] ([username], [password]) ...
6
votes
1answer
2k views

Duplicating some rows of a data flow in SSIS

I have a data flow to migrate data from an old database to a new one. The old design had all the data and historical information (changes) stored in a single table with a "version" (incrementing ...
29
votes
7answers
2k views

The smallest backup possible … with SQL Server

Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever. We don't mind if our backup process takes a bit longer; as it stands ...
8
votes
3answers
7k views

Hard and Fast rule for include columns in index

Is there any hard and fast rule to decide what columns and in which order it should be put in Included in non clustered index. I was just reading this post Why use the INCLUDE clause when creating an ...
5
votes
4answers
6k views

Optimising join on large table

I am trying to coax some more performance out of a query that is accessing a table with ~250-million records. From my reading of the actual (not estimated) execution plan, the first bottleneck is a ...
4
votes
1answer
751 views

Strange SQL Server 2008 behavior while using XLOCK and ROWLOCK

I wrote this script without COMMIT in a new Query window (Query1): BEGIN TRAN SELECT [RegionID], [RegionDescription] FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK) WHERE RegionID = 3 and in ...
3
votes
1answer
997 views

How to fix discrepancies between publisher and subscription data?

I have a transactional replication between two SQL Server 2008 SP1 servers, both on Windows 2003 Server. I discovered that there are discrepancies between some of the tables of the main database and ...
7
votes
1answer
2k views

noexpand hint for non-enterpise edition and performance

I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says: Indexed views can be created in any ...
0
votes
1answer
9k views

How to backup and restore a database to a different location and name via command line only

I have a database that I want to copy from one location to another (maybe even to a different computer) via the command line only. The name of the database on the new location should not be identical ...
6
votes
2answers
973 views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
4
votes
2answers
709 views

How to mark an item as “Expired” when it's ExpiryDate value is in the past?

I have a table of items, each of which has a Status and ExpiryDate. What is the best way of enforcing this: when ExpireyDate > DateTime.Now update Status to "expired" I could have a stored ...
6
votes
1answer
530 views

How to back up a small database in SQL Server 2008 R2 Express Edition

I will shortly be migrating a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition The database is small, and there are only a few hundred short transactions per day ...
11
votes
1answer
2k views

Patterns for setting up sharding for SQL Server 2008 R2, in order to handle large datasets?

I want to handle a large dataset (> 1 billion rows) in SQL Server 2008 R2. I heard that if you set up "sharding" or "horizontal partitioning", it makes it quicker to handle large datasets as it breaks ...
15
votes
3answers
368 views

How can I guarantee that inserts to SQL Server 2008 R2 are cached in RAM first?

Imagine a stream of data that is "bursty", i.e. it could have 10,000 events arrive very quickly, followed by nothing for a minute. Your expert advice: How can I write the C# insert code for SQL ...
3
votes
1answer
331 views

Badly performing query plan after adding ORDER BY to Full Text CONTAINS search

The following query is proving beyond my abilities to fix - hope someone can help. SELECT top 100 Id, Name, InsertDate FROM Foo WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"') ORDER BY ...
9
votes
4answers
4k views

Query becomes drastically slower when wrapped in a TVF

I have a fairly complex query which runs in just a few seconds on its own, but when wrapped in a table-valued function, it's far slower; I've not actually let it finish, but it's run for up to ten ...
2
votes
1answer
696 views

Are master/slave replication relationship between different versions of SQL Server possible?

I have a database server, SQL Server 2005, that's inside a private network. Now i need to make a database there available for a webserver, so i want to replicate this db to an external server, which ...
3
votes
3answers
900 views

SQL Server 2008 on Hyper-V Virtual Machine with Multiple instances

Running SQL Server 2008 instance on a Virtual Machine (Hyper-V) on a Windows 2008 R2 OS. Currently has a few production DBs. The whole VM itself is on RAID 5 volume (so are the DB files etc). We ...
5
votes
1answer
562 views

Why can the target of the OUTPUT INTO clause not be a view?

Here is my statement: DELETE FROM table1 OUTPUT deleted.col1, deleted.col2 INTO view1; It gives me this error: The target 'view1' of the OUTPUT INTO clause cannot be a view or common table ...
1
vote
1answer
1k views

Inner cursor performance issues

Before I tried to avoid using cursor because of performance issues. But now I have to do some calculations to reach some goals. I have a stored procedure that has a code like this: DECLARE ...
11
votes
2answers
2k views

When should extended events be used instead of SQL Profiler/perfmon?

The extended events seem like a better technology and less stress on the server, but the SQL Profiler/perfmon has better tooling. Also the extended events seem to have a steeper learning curve. In ...
5
votes
1answer
953 views

Using integrated security over VPN

We've got a client that we're going to allow to use one of our applications. Staff on site regularly connect to our network via VPN. The .Net application that connects to the SQL Server uses ...
4
votes
2answers
4k views

What is the best practice for storing image varbinary(max) data in a database table?

I have a table that stores images that range in size between 16-100 KB each. Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type. The table is ...
3
votes
1answer
910 views

SQL: transfer database schema

If i want to transfer schema ownership to another schema i know that i need to use something like: ALTER SCHEMA destinationschema TRANSFER sourceschema.objectname; But how is it different from: ...
1
vote
1answer
794 views

How to ensure that SQL Jobs run in the correct order within a minimal maintenance window?

From this question I learned about a script which creates jobs for backing up databases, checking database integrity, optimizing indexes, and cleaning files. According to the documentation, this ...
2
votes
1answer
478 views

What is the best practice for backing up a database in Simple mode?

I am trying to create a SQL Job for backing up my database. I want to have: A weekly full backup A daily differential backup Something that will clean up backups that are older that 14 days I ...
1
vote
1answer
100 views

Help on writing server-side trace

I need help on writing a server-side trace to check logins to try to determine if and when vendor accounts are used. The purpose is to remove sa access. These accounts sure have sa but I need to ...
0
votes
1answer
4k views

Maintenance plan delete backup

I succeeded in making a backup through maintenance plan. All I need now is to find how to delete a folder (containing old backup) through the same maintenance plan.
12
votes
4answers
27k views

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights). There are no other ...
15
votes
3answers
22k views

How do I assign an entire Active Directory group security access in SQL Server 2008?

I would like to use integrated security with my internal application which is all on a domain. Unfortunately, I've never been able to get this to work well. I would like to assign an entire Exchange ...
2
votes
2answers
517 views

Should I create a maintenance plan for my database or use jobs?

I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box. However, after it was created, it seemed difficult to ...
6
votes
4answers
2k views

Duplicate database and keep it up to date - Replication or SSIS?

I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally. We don't need ...
4
votes
1answer
2k views

Make SqlClient default to ARITHABORT ON

First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection. For performance reasons I've created ...
9
votes
4answers
1k views

What is the best way to monitor long running queries in SQL Server?

I have to run quite a few long-running queries (rebuilding indexes, updating large sets of data) for my database. Is there an alternative to running the query in SQL Server Management Studio and ...
3
votes
2answers
279 views

Report model data source

Does anybody know how i could use a linked server as a data source for a report model in SQL server 2008 r2? or how i could make this linked server a data source on a report model? Please help. Thank ...
3
votes
1answer
4k views

How do I alter index rebuild an entire database?

I want to reorganize the indexes of all tables in my SQL Server 2008 database, named 'db'. I try the following command: ALTER INDEX all ON db REBUILD And I get the following error: Cannot find ...
11
votes
4answers
782 views

Creating centralized DBA database

We have 200+ servers and I am thinking about creating a centralized DBA database where it logs backup, errors, space jobs and generate reports on SSRS. I am not sure where to start and I would highly ...
1
vote
0answers
513 views

DBCC CheckDB Consistency error on an empty table [duplicate]

I restored a DB from SQL Server 2000. I ran DBCC CHECKDB on SQL Server 2000, and it did not report any errors. What could be the reason behind a DBCC CHECKDB reporting a consistency error on a empty ...
2
votes
3answers
740 views

Is encrypted database possible?

I'm not a DBA but I've been asked to see if we can encrypt the data in sql server such that the admin, despite having access to SQL Server won't be able to see the data but application would still be ...
3
votes
2answers
744 views

MS SQL : Use Computed value to Compute other values

I'm having problem with generating results which are computed from data that exists in database. This is the case I have Coupons that can be amount or percent, if percent is 0 then amount is used ...
5
votes
3answers
736 views

Deadlock Error from SQL Server 2000 to SQL Server 2008

After migrating data from SQL Server 2000 to SQL Server 2008,when I am doing any a transaction in the table which find out more row on the database,that's gives me error. Transaction (Process ID ...
4
votes
2answers
1k views

Can I have different publishers and one subscriber in replication?

I have the same databases in three cities which each database have three tables: 1- tbl_worker 2-tbl_workshop 3-tbl_users and cities are:city1,city2,city3 city2 and city3 insert informations in ...
1
vote
1answer
638 views

ActiveX script throwing runtime error inside DTS package

I am a DBA. We have migrated SQL 2000 databases + DTS packages to SQL 2008. One of the DTS packages runs an ActiveX script to open an Excel file (which has runs macros when you open it). Coding ...