Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

2
votes
1answer
4k views

How do I backup SQL Server Agent configuration?

I've tried googling for this. The problem is that the all the results are about using SQL Server Agent to schedule automatic backups. How do I backup the SQL Server Agent configuration itself? If ...
2
votes
1answer
85 views

Bad Windows Authentication sign on

I do not have install privileges on my machine. Another user who has admin privileges installed it, but told the system to use Windows Authentication for the sign on. Is there a way they can change ...
10
votes
3answers
2k views

Snapshot Replication Retention

I've got Snapshot replication set up on my SQL Server 2008 production server, and I just noticed that there are snapshots ranging back to a year ago in the snapshot folder. How can I change the ...
1
vote
1answer
2k views

Recreate MSDB for SQL Server Express 2008

My SQL Server Express database's msdb file has become 'suspect'. All the other mdf files seem to be OK. I now need to recreate the msdb file, but I do not have a backup for it (guilty as charged!). ...
5
votes
1answer
689 views

Is there a way to have SQL Server format a number into ordinal form?

Data 1, 2, 3, 4, 11, 21, 99 I want to get 1st, 2nd, 3rd, 11th, 21st, and 99th I could roll my own, but is there any function built into SQL server that could so this for me? Corollary: I will also ...
2
votes
2answers
1k views

Set permissions on a view

I'm creating a view on a SQL Server 2005 database via SQL Server Management Studio 2008, the view reads from two tables. The view is accessed from a web application and I want users to be able to ...
2
votes
1answer
168 views

Is this Index checking logic ok?

I am writing a script to quickly check the indexes in a given database. I think I have the script written ok but to remove duplicate rows I have done alot of SUM functions and I am unsure whether this ...
18
votes
5answers
42k views

How do you kick users out of a SQL Server 2008 database?

We need to do a restore, and cannot because other users are connected. We thought we had disconnected every process, but apparently not. How can we, from Management Studio, kick off everyone else so ...
1
vote
3answers
2k views

Grouping results to get unique rows after multiple joins

this is my first question on dba.stackexchange, hopefully it will be worth it. I am usually a bit more active on Stackoverflow as I am a developer. disclaimer : I don't have full control over the db ...
3
votes
2answers
2k views

SQL Server 2008 R2 - Error 18456 State 12 - nothing I find helps me

First off let me state that I'm not a DBA, I don't have access to a DBA, I'm just a programmer who has to get a CMS working. I'm generally pretty good at getting random servers working, but this is ...
1
vote
1answer
154 views

Migrating or Synchronizing SSAS Changes

Assume you have 2 SSAS databases, DB1 and DB2, that are identical. Now they branch off. Changes A and B are made to DB1 and changes C and D are made to DB2. Is there an easy method to migrate ...
2
votes
1answer
1k views

Export data into word documents

My boss has a kindle and likes to save a lot of stuff to his clippings. Currently I'm going through and cutting out each clipping and pasting it into a seperate word document with the title of the ...
59
votes
8answers
5k views

Does SQL Server read all of a COALESCE function even if the first argument is not NULL?

I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process: ...
2
votes
2answers
5k views

Attach SQL Server 2008 R2 on SQL Server Express 2008

I have a database on SQL Server 2008 R2. I want to attach it on a SQL Server Express 2008 instance running on Windows 2003. I set the SQL server compatibility level on the database to SQL Server ...
4
votes
3answers
428 views

sql script repository [closed]

I am a fresh DBA just being promoted, I am looking for few good sites which constains T-sql Scripts for DBA tasks. Is there a common place from where you can get a collection scripts to perform ...
3
votes
2answers
394 views

Unable to BackUp Database on SQL2005

I have a database in MS SQL2005 that I can not backup. Whenever I try to create a backup of the selected database through SQL Mgm studio I get this error: The Backup of full-text catalog in not ...
4
votes
1answer
516 views

Why is 'Allow Snapshot Isolation' set to TRUE on the master and msdb databases?

Why is the database property Allow Snapshot Isolation set to TRUE on the master and msdb databases, but FALSE on other system databases? The default option for this property is also FALSE when ...
3
votes
2answers
1k views

Recent Queries in SQL Server 2005

I'm using a PHP web application to interact with a database and need to catch an erroneous query. It's dynamically generated SQL and I don't know 100% of the code nor did I write it so I need to see ...
1
vote
1answer
149 views

Using the name of a table contained in a field of another table?

I have a table (MyTable) with varchar columns named SourceID and ReferenceID where SourceID contains a table name and ReferenceID contains a key value for the source table. Is it possible in a query ...
2
votes
1answer
101 views

Full Text Search in SQL 2008

When I attempt to search in the full-text catalog which is my created earlier, I haven't been finding any word within database. For example: My word: Where CONTAINSTABLE(TABLO,*,' "*Where*" ') ...
10
votes
3answers
2k views

Testing stored procedure scalability

I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level ...
2
votes
1answer
358 views

What system information is stored in the primary file in SQL Server?

Suppose I have created a new database in SQL Server with a file group FG1 and marked this as the default. All my user tables etc are now created in FG1. However I also created a file that is part of ...
5
votes
5answers
3k views

How much memory is needed to keep a table cached? (SQL Server 2008)

I run long and complex queries which mostly use one big table - 8GB, 40M rows. AFAIK, all/most rows are used in each query. I'm seeing lots of IO in the activity monitor - for the first query and for ...
5
votes
3answers
3k views

Why is SSMS inserting new rows at the top of a table not the bottom?

Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using ...
1
vote
2answers
193 views

Workload analysis before migration

Does anybody have a recommendation on which performance counters to collect and/or templates to use on the profiler before migrating SQL databases? The purpose is to evaluate the workload and make ...
1
vote
1answer
5k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
2
votes
1answer
433 views

Best Practice for Folder strucure?

I don't like my data files in C:\Program Files... , especially with a new system that has a smallish SSD as C: which holds mainly the operating system. So on a recent install I put my data files into ...
2
votes
4answers
305 views

Database backups

I have 10gb RDBMS (SQL Server). I would like to back it up as often as possible, but it seems that I will need an awful lot of space to make this happen on the order of several times per day. How ...
2
votes
4answers
3k views

how to turn off delimiters in generated SQL scripts for data types in SQL Server Management Studio

We are a software engineering firm. We use SQL Server Management Studio (SSMS) to generate the few scripts we need to build and upgrade our databases. While it does an OK job, it bugs me that it ...
3
votes
3answers
4k views

What's the difference between Linked Server solution and SSIS solution?

When I want to move data between two databases (e.g source: Oracle, destination: SQL Server), I think I have two options: Linked Server and SQL Server Integration Services. But is there any benefit ...
0
votes
4answers
741 views

Why Clustered Index property is enabled on few columns and not on others?

I heard that there can be only one Clustered Index? I may be wrong, but I am sure I read somewhere. In one of my SQL Server 2005 table, I have following columns: SerialNum ------- BigInt -------- ...
0
votes
1answer
241 views

Why SQL Server replication related jobs do not show up under the jobs folder in SSMS?

I found some SQL Server replication-related jobs that do not show up under the jobs folder in SSMS. Can anybody explain why this is, please? Thanks.
6
votes
3answers
636 views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
1
vote
1answer
54 views

Replication : Which subscription changed a row

Is it possible to query the replication metadata to see which subscription caused a change to a specific row in a table?
1
vote
1answer
280 views

DB Integrity Check Lock Database

Hi all i'm using The Integrity Check script from http://ola.hallengren.com to check through my database of roughly 65 million records. I was just wondering if this will lock my database? In terms of ...
2
votes
1answer
6k views

SQL query to convert columns into rows

I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005. my input Data ONAY_ID sip_RECno KULLANICI ONAY 1 1 ...
5
votes
1answer
8k views

How to get the foreign and primary key details for a table in SQL Server 2008

I would like to use 2 queries, one that lists all the foreign keys for a given table with the following information Schema name, foreign table name and the other that lists all the primary keys. Is ...
4
votes
1answer
2k views

Which workarounds exists to missing relative path feature of SQLCMD :r?

I'm not the first to notice the lack of this feature. But I wonder if there are some useful workarounds. I want to deploy a main script and some depended scripts in the same folder. BTW Is there a ...
5
votes
2answers
289 views

What is an advantage or disadvantage for not upgreading database server?

I have a legacy database server. The person who wrote it is long gone. There is no documentation at all just some comments in the code. Luckily, it is not that complicate but if I want to fix it ...
6
votes
1answer
728 views

What makes an OLTP database an OLTP database?

This is a pretty broad question. I know the definition of OLTP. But it seems like unless you have an environment that has a clear distinction between OLTP and OLAP, in the sense of a database that ...
31
votes
7answers
16k views

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so ...
2
votes
2answers
327 views

Indexing of Data Warehouse tables?

Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because ...
1
vote
3answers
4k views

ON DUPLICATE KEY UPDATE question

I'm trying to compare data from two systems. The basic idea is: Create Temp Table. Insert Table1 Data. Insert Table2 data (Table a linked, remote server) with UNIQUE Ids respected. Filter where ...
0
votes
1answer
104 views

What would cause the speed difference between servers?

I have this query on my dev server: select snStudyNumber as ProjectNumber from CDB.cdb.Quotes q inner join CDB.cdb.LineItems l on q.PK_quID=l.FK_quID inner join ...
2
votes
3answers
3k views

Backup Compression in SQL Server 2005

I noticed that simply zipping my backup files reduces the space used by 70% My idea was to, initially, backup the databases to a local server; then, using Bacula or Cobian (I don't have a budget), ...
0
votes
2answers
659 views

Upgrading Instances with Mirroring

If you want to upgrade or install a patch on two separate instances that house both the principal and mirrored database, how can you go about that? If the database that is being mirrored needs to be ...
5
votes
2answers
2k views

Changing Domain on SQL Server

I currently have some SQL Servers that sit on a sub-domain. We are eliminating that sub-domain and moving everything to the regular domain. As far as SQL Server configuration goes, is there anything ...
4
votes
2answers
6k views

Minimum requirements to run Sql Server BCP.exe utility

I am working with a client who needs to do a data import to a remote SQL Server (2008) instance. The machine that the import will be done from cannot have SQL Server installed on it. Can the BCP.exe ...
2
votes
2answers
240 views

What is an industry standard method for verifying openquery connection is open prior to running queries?

I'm a programmer, but this question is more specifically related to databases so I'm posting it among this community. It could very well cross over into stackoverflow as well. I'm seeking an ...
13
votes
2answers
6k views

How to know when/if I have too many indexes?

Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement..."). From my understanding every added ...