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

learn more… | top users | synonyms (1)

1
vote
2answers
14k views

How to get table columns with datatypes (and lengths) using PowerShell?

I'm trying to get column names with data types (and lengths) for SQL Server instance using PowerShell. I got this far: #Load PSSnapin Add-PSSnapin *SQL* #Get column names $colNames = dir ...
5
votes
2answers
3k views

How do I clear a table from SQL Server's cache?

I have a few tables in my database that should not be cached. How do I tell SQL Server not to cache a table's pages or how do I flush a single table from the cache? Flushing all the cache is not an ...
1
vote
1answer
301 views

Running a sql server Trace of 1 GB using sqlcmd

I am load testing two sql server instances. I have a trace file from sql server 2000 database with a file size of little less than 1 GB. I am running this using the sqlcmd utility and it is been ...
4
votes
3answers
1k views

For every insert statement I see sp_executesql in SQL Server Profiler

Working on tuning our database and queries for one of our products, I decided to pop open Profiler in the QA environment and see what it shows. I saw for every insert I called, a call was made to ...
4
votes
1answer
244 views

SQL Server transactional replication - behaviour of a MERGE command on the subscriber(s) vs. publisher

Background I have two instances of SQL server running on two different machines: One is a publisher, the other is a subscriber for a single (large) database. The replication type is transactional. ...
4
votes
1answer
2k views

Is there a way to force sp_send_dbmail to use ANSI, or to not include the Unicode prefix?

This is pretty trivial, but if someone has the answer handy, I'd appreciate it. I use the msdb..sp_send_dbmail sproc as a lightweight way to distribute simple reports. This often entails producing a ...
2
votes
1answer
2k views

“Linked server does not contain table” error when using VFPOLEDB provider linked server

I am receiving the below error when attempting to run any queries via a linked server in SQL Server 2008 R2 using the Visual Fox Prox OLE DB Provider v9.0.0.3504. I am attempting this as I need to ...
4
votes
1answer
756 views

How to get text for default language

I have so tables: and so data at Language table: and so data at Text table: I have to return text for requested language if it exists and text for default language if it does not exist. Is it ...
1
vote
2answers
271 views

Case-sensitive query - SQL Server 2008

How can I rewrite as Latest Developer a record which was created as LATEST DEVELOPER via SQL query?
5
votes
1answer
610 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
1answer
564 views

SQL Server 2008 Mirroring Pauses without warning

I have several databases mirroring with a witness. All 3 machines are local to our network and have generous nics. Monitoring the mirroring, it does not seem like there is a lot of network traffic. ...
1
vote
1answer
147 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
100 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*" ') ...
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 ...
16
votes
4answers
8k views

Task scheduler for SQL Server Express

I have an ASP.NET MVC app which works with database under SQL Server 2008 R2 Express edition. There is a need to perform a regular task on updating some records in the database. Unfortunately the ...
1
vote
1answer
493 views

How to restore a database from a newer version of SQL Server

I have SQL Server 2008 and I want to restore a database from SQL Server 2008 R2. When I try to restore, SQL Server produces an error message that the database came from a newer version of SQL Server ...
14
votes
3answers
2k views

Why might a table's data space take up 4x the size of the raw data?

I have a table with 490 M rows and 55 GB of table space, so about 167 bytes per row. The table has three columns: a VARCHAR(100), a DATETIME2(0), and a SMALLINT. The average length of the text in ...
5
votes
1answer
496 views

Can filtered indexes help improve queries that are based on a time entered or should this be avoided?

I'm just learning about the new filtered indexes in MS SQL 2008 and I'm trying to understand where they would hurt and where they would help. I can see that adding a filtered index to an employees ...
3
votes
1answer
652 views

Attach Database Trigger

Is it possible to write a trigger when someone makes a change at the server level such as attaching or creating a database? I have a dev server where anyone can add a copy of the product database for ...
0
votes
3answers
739 views

SQL Query Declaration problem

I have a view as shown below.The View is good running.When I attempt to run view,I got the following the error.What might the problem? Incorrect syntax near the keyword 'Declare' SQL Query: create ...
5
votes
3answers
580 views

Which is quicker: Select of existing row vs Update where no row exists?

Which would run the fastest? Select from a table using the primary key (integer, clustered index, 1,000,000+ rows) Attempt an update on a different table, where the row does not exist? (where ...
3
votes
2answers
138 views

SQL server 2005 backup statistic

I'd like to share with you this simple snippet (answering my own question) and I'm also curious how do you control your SQL server backups.
2
votes
2answers
161 views

Is it fine to run a public website on the sql express db?

Is it fine to run a public high traffic website on a sql server 2008 express db?
4
votes
3answers
2k views

How to sync the data between two identical DB

The thing is I've two identical databases, one is in my localhostserver at my laptop and the other one is on the office main server. Is there any automatic way or software that can synchronize the ...
1
vote
1answer
34 views

How to know which website is causing the sql server to stop responding?

I have multiple databases on the same sql server 2008. Some times the server stops working and I should restart it to make it work again. Is there an easy way to know which application or website ...
2
votes
2answers
1k views

Determining maximum table size, type of engine and type of indexes supported

How many types of indexes are available with SQL Server 2005 and SQL Server 2008? MySQL has a variety of options like: Hashed, BTree, etc. What are the choices in SQL Server? I also want to determine ...
4
votes
2answers
333 views

How do I track the usage of XML indexes?

My XML indexes are not listed in sys.dm_db_index_usage_stats Does this mean they aren't being used? Or does this particular DMV not track XML index usage? (Books Online doesn't appear to answer the ...
3
votes
3answers
3k 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 ...
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 ...
0
votes
1answer
655 views

How to create a backup schema script including System Tables in Sql Server 2008 R2?

I need to back up my entire DB including one table that's allocated in "Tables -> System Tables". When I try to generate the sql scripts to back up my DB this table isn't mapped in this scripts. Do I ...
0
votes
2answers
101 views

what are steps should i do to make my local sql server connects to a remote sql server on my server?

I want my sql server to remote connect to the server sql server, so that I can access databases from my local machine. How to do that in steps, or if there is an article online that will be nice as ...
1
vote
2answers
426 views

Scheduled backup errors

I am trying to do the following. I have about 50 DBs and a network NAS that acts as backup machine. I have made an sql script(lets say backup.sql) with 50 lines each of these say "BACKUP DATABASE ABC ...
0
votes
1answer
101 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
2answers
325 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 ...
3
votes
1answer
989 views

Replication slows down on massive deletes

First of all, I'm not a DBA, but I'd like to get advice how to deal with the following issue. We have few database servers based on SQL Server 2008 R2 SP1. They have been organized as shown below, so ...
2
votes
2answers
821 views

Determine last usage date of a linked server

We have a tonne of linked servers on our database server that we would like to clean up. Is it possible to determine the last date a linked server was used? e.g. the last time a query was performed ...
5
votes
2answers
2k views

Will transactional replication push a CREATE INDEX immediately? Or do I need a snapshot?

I have transactional replication set up on SQL 2008, and the article in question has "Copy nonclustered Indexes" set to TRUE. If I add a non-clustered index on the publisher (using CREATE INDEX), ...
10
votes
2answers
423 views

@table_variable or #temp_table

I have a big user defined table type variable that has 129 columns. I will store around 2000-3000 records in this table variable at a time and pass it to various stored procedures and functions to get ...
2
votes
2answers
672 views

Can a database file be brought back online if the file is recovered from a disk backup?

TL:DR Deleted .ndf files Database wouldn't load Set deleted files offline Brought database online Restored files from disk backup How can I bring them online/remove the database reference? During ...
8
votes
2answers
2k views

SQL Disk Setup Advice - TempDB, Log DB, Data file placement question

We have a very active database server with a ecclectic collection of applications running on it. Two of the busiest are a Laserfiche database that does document scanning and workflow processing all ...
3
votes
2answers
140 views

Is it possible in SQL Server 2008 to have Structures in one file and Rows Data in another file?

I know this may sound ridiculous but: Is it possible to separate the Data Definition (i.e. Structures of my Tables, Views and other things) and Rows-Data on a File-Level in SQL Server. I think it will ...
1
vote
1answer
3k views

12.0 Access Database Engine OLD DB Provider Not showing up in SQLEXPRESS 2008

I am trying to create a linked server to an 32 bit 2010 access database inside of SQLEXPRESS 2008 R2 64bit. Everything I've read has pointed me at the download : Access Database Engine I've ...
9
votes
1answer
757 views

Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space?

Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered ...
2
votes
1answer
1k views

Intermittent Disconnection to DB on SAN. Event ID 17053 and 9001

I am getting some strange issues with our MS SQL 2008 database where intermittently we lose connection to the db for 10+ seconds or so. The db is stored on a SAN over fiber. The latest event log ...
3
votes
2answers
815 views

What is the best way to mirror an ASPState database?

I'm not having issues with the actual mirroring part, but because of the use of temporary tables, IIS is throwing a bit of a wobbler if a failover occurs (I'm still testing a few things, so it's me ...
0
votes
1answer
425 views

How to start and then stop one cyclic process

I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) ...
4
votes
3answers
502 views

Usage of SQL Server 2008 R2 Express. Please Advise

I'd like you all to give me hints and advise on the following scenario: We are considering using Microsoft's SQL Server 2008 R2 Express for a business Web Application. The Web Application won't have ...
23
votes
3answers
16k views

Why is my database still fragmented after I rebuilt and reindexed everything?

I have a database which I tried to defragment all the tables at once by running this T-SQL: SELECT 'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) + 'ALTER INDEX all ON ' + ...
12
votes
1answer
7k views

How do you ORDER BY a parameter?

Just wondering if I could solicit some feedback on a stored procedure I'm running and whether there's a more efficient way of handling the scenario (I'm pretty sure there will be!). Basically I have ...
13
votes
1answer
1k views

What types of corruption can DBCC CheckDB miss?

This question was prompted by this earlier post and my having a database filed away for future investigation that was restored following: BACKUP 'BrokenDatabase' detected an error on page (1:123456) ...