SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

1
vote
1answer
999 views

SQL Server 2008 R2 - VLDB Backup Strategies

I have a 200GB database running on SQL Server 2008 R2 Enterprise Edition. I just recently partitioned it in order to archive 11 months of history, so it was pushing 400GB prior to archiving. Our ...
2
votes
2answers
2k views

SQL Server 2008, cpu 100% until recreate an index

I have a 4000000 lines table (SQL Server 2008). This table has a private key composed of two int columns. One corresponds to a PK in another table of the same database. There is a relation between ...
3
votes
2answers
1k views

Select total number of records from common table expression

We are using common table expressions to temporarily save the search results. WITH Results ( ------ ) AS ( select --- ) SELECT * FROM ...
3
votes
2answers
6k views

Access Linked Server error “The provider did not give any information about the error.”

A little information. I'm running SQL SERVER 2008 R2 64bit. I have Microsoft.ACE.OLEDB.12.0 provider installed. On my production server I've setup a linked server to the access db using the provider ...
2
votes
1answer
334 views

How to determine the size of a SQL Server database for a specific date in the past?

Microsoft SQL Server 2008 R2 Standard What I would like to find out is what the size of the DB was at specific dates in the past. I don't have backups going back that far. The current log may or ...
2
votes
1answer
4k views

How can I move LOB_DATA ( varbinary/image/text) to different Filegroups

LOB_DATA : varbinary(max),varchar(max), image , text columns. I have 4 tables with large amount of LOB_DATA in it. I would like to move LOB_DATA in these tables to different File group in a different ...
5
votes
3answers
2k views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
2
votes
2answers
392 views

Can I store half of a record on one page and half of the record on another page?

I'm watching Paul Randal's MCM video from http://technet.microsoft.com/en-us/sqlserver/gg313756.aspx From 27:11 of the video, the author said a record must be saved in one page, you can't have half ...
1
vote
2answers
208 views

Web application using SQL Server 2008 starts hanging

We have a custom web application that uses SQL Server 2008 (10.50.1600). It's worked fine for years, but lately web pages that return data "instantaneously" have begun showing the "endless swirly blue ...
-2
votes
2answers
1k views

How can I remove an index scan from a SQL Server SELECT statement?

This select statement: select * from tab1 inner join tab2 on tab1.col1 = tab2.col2 where tab1.col3 in ('<some conditions>') In the execution plan I see two indices used. But the second ...
2
votes
2answers
3k views

how to use database mail from SSIS

I try to use gmail in database mail. I have no problem to send out email from it. But how I can configure my send mail task to send out email by using database mail. Thanks
7
votes
3answers
478 views

Prevent Data Sources from being deployed outside designated folder

Before I just go ahead and put a really awful check constraint on the Catalog table, I wanted to solicit some better ideas first. I want to ensure that all shared data sources on our report server ...
3
votes
4answers
4k views

migrating tables from Postgres to SQL Server 2008

(I fear what I wish to accomplish shall not be easy, but a lot of my googling around about it yielded rather dated results, so here it goes...) I have a Postgres 8.4.x database with over 100 tables. ...
2
votes
1answer
2k views

Activity Monitor Showing Long Duration for WAITFOR

Using Activity Monitor, I am consistently seeing the following query showing an average duration of over 300 seconds: WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, ...
1
vote
1answer
314 views

SQL Data type for “Reference” fields in table - SQL Server

I am learning SQL Server. I need a bit of advice regarding data type for fields that will be referred to but will not actually themselves require values. I don't know the correct term to use(edit ...
2
votes
1answer
322 views

Cluster communication - SQL Server Failover Cluster

I am running a lab enviornment on my laptop two node SQL 2008 R2 running on windows 2008 R2 clustered Vms Based on my understanding a) Public Network or all Communication network I see checkbox ...
6
votes
2answers
3k views

Speed up INSERTs

I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is : Have no index on the table whatsoever Switch to simple logging (from full logging) ...
1
vote
1answer
9k views

Unable to telnet to port 1433 in SQL Server clustered instance?

Situation: I believe that multiple port 1433 can be implemented as long as the IP address are all unique. I’ve created SQL Server 2008 R2 SP1 clustered virtual instance on top of my Windows Server ...
6
votes
1answer
1k views

Can I call SMO from pure T-SQL?

I know how to script a stored procedure using PowerShell and SMO: [System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null [System.Reflection.Assembly]::...
3
votes
1answer
158 views

Availability Strategies in MS SQL Server 2008

I need some assistance in developing a backup strategy for my SQL Server deployments. Currently I have two classes of customer: Hosted customers, whose data is housed in our datacenter, and On ...
2
votes
1answer
463 views

encoding format of multilingual text while saving to database

I have website that save content in various languages. just to display that i have enter to database. Suppose i copy a French text in sql server table column it will display correct on my website, ...
1
vote
1answer
270 views

Copy database schema from SQL Server 2008 to SQL Server 2005 [duplicate]

Possible Duplicate: Downgrading from SQL Server 2008 to 2005 Is it possible to copy the database schema from SQL Server 2008 to SQL Server 2005, I don't need data from the tables, just the ...
1
vote
2answers
3k views

MS SQL Linked Server Security failing using current security context

I have 2 instances of SQL 2008 64 bit (ver 10.0.4000) running on a windows 2008 64 bit server db03 and db03\ins2. The ins2 has a linked server connecting to db03 that was setup by replication. I have ...
1
vote
1answer
162 views

SQL ServerRecovery Model Change Tracking

Are SQL Server recovery model changes logged anywhere...WMI, error logs...etc? For instance, if I change from FULL to SIMPLE, is this recorded anywhere?
4
votes
1answer
667 views

How to change database owner for login and database created inside stored procedure?

I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my SProc creates the new login and new database but will ...
0
votes
1answer
6k views

querying multiple values from one column [closed]

we have a table called transaction_details. when i write this query SELECT DESCRIPTION, item, cust_code, pack_description, uos, pack, total_value, total_value_vat, total_value_goods, price_indicator ...
2
votes
1answer
711 views

Add columns Query With parameters

Hello all I'm having 1 Question with following query syntax ALTER TABLE table_name ADD column_name column-definition; Question is can we use parameters to column_name field like @column_name? ...
2
votes
2answers
2k views

Using SSIS to get record counts for tables that may not exist

I have an SSIS package that executes simple queries against a list of servers. I am having an issue with one particular simple query: If a table exists, get the rowcount. Otherwise report "not ...
3
votes
1answer
313 views

Proper Design for Datamart Invoice

I'm trying to design a set of fact tables for a datamart that will work in an ssas cube for one of our clients. One of the challenges I'm facing is that there appears to be some disperencies between ...
3
votes
1answer
130 views

DTC in SQL server 2008 R2 Clustering

I believe DTC is optional but cannot confirm it (can't find it on MSDN) Why we need DTC (distributed transaction cordinator) when clusting a single instance (active passive scenario)?
1
vote
2answers
733 views

Create multiple files for each file group for table partitioning?

I am partitioning a table by date (month). The server has four drives. The partition scheme is defined as CREATE PARTITION SCHEME ByDate AS PARTITION ByDate TO ( [PRIMARY], [fg2], [fg3], [fg4], ...
3
votes
3answers
2k views

SQL Join slower than subquery on View with a Function

My database has a view that calls a function. Joining on that view is surprisingly slow. Is there a way to speed up joining to a view like that? I've included 2 queries which illustrate the issue. ...
3
votes
2answers
2k views

Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights?

I am struggling with maintaining or re-gaining access after restoring an SQL Server database (2008 R2, if that makes any difference). Here is what I am trying: SQLServer login APPUSER is member of ...
2
votes
2answers
6k views

How to deny drop permission for a table?

Is there a way to deny drop permissions for a specific table from a user or role?
1
vote
1answer
314 views

How to create a Job for Merge Replication?

I want to create a step in a Job for Merge Replication. I basically want to know which command should I write so that a particular Subscription starts synchronizing?
1
vote
1answer
351 views

drastically different runtimes in BIDS and the SSRS Web Portal

I have a report in a BIDS project that has been deployed to our SSRS Web Portal. On the web, it renders within seconds. In BIDS, it takes upwards of seven minutes. I can see that the query runs ...
5
votes
6answers
6k views

Any way around unique index 16 column max

According to the CREATE INDEX documentation: Up to 16 columns can be combined into a single composite index key. We've got a table with ~18 columns that need to form a unique combination. This ...
12
votes
2answers
41k views

How to create Sql Server login for a domain account?

All, I have Sql Server 2008 installed on a server (let's say Server1) in a Domain (let's say AD). I also have a domain account called AD\Sql1. This is not an Admin account on the Domain (AD), but I ...
3
votes
1answer
2k views

Sql Server 2008 Replication “Generate Scripts” option not available

All, On Sql Server 2008, I have Transaction Replication going from Server1 to Server3 (with Server2 as distribution). I have an option to "Generate Scripts" on Publication on Server1. But I do not ...
4
votes
2answers
3k views

SQL Server 2012 RC installation failing

I'm trying to test out the new SS 2012 RC, but I'm having problems with the installation. My installation stops a ways in saying that the resource needed cannot be found. I'm using the ISO from MSDN ...
3
votes
1answer
148 views

migrating from SQL server 2005 to 2008

I had SQL server 2005 installed on my windows server 2008 R2. Recently i installed SQL server 2008 and SQL server management studio 2008 r2 (SSMS) on my virtual server and i configured the instance ...
1
vote
2answers
5k views

Why can't I connect to SQL Server 2008?

I initially installed SQL Server 2008 R2 with Windows authentication mode and have successfully changed it to mixed mode (per http://msdn.microsoft.com/en-us/library/ms188670.aspx). After this I am ...
9
votes
3answers
9k views

Storing date as integer (numeric), what are the advantages

Question 1 I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example ...
12
votes
1answer
3k 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 ...
2
votes
1answer
294 views

How to recover SA account password without restoring it?

I forgot my SA password and resetting the password is out of the question because internal applications are using it and will will affect them. I also don't have an option to change the SA credentials ...
13
votes
2answers
15k views

In Sql Server's RANK function, how to limit the results to just the first 3 ranks

In Sql Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from Rank 1, 2, ...
1
vote
1answer
564 views

data driven subscriptions in SSRS - pulling a password value from the database

If I create a data driven subscription in SSRS and use the query to return some value for the username and password, how "safe" is this password? Let me explain. I am looking to set up a data source ...
2
votes
1answer
213 views

How do I store more than 15,000 Japanese characters in a column?

I want to store more than 15,000 characters of Japanese text in a SQL Server 2008 database. We have a front end tool that will display this text.
5
votes
2answers
1k views

Transaction independent writes inside a trigger

I am using sql express 2008 r2 express edition. Is it possible to do writes inside a trigger independent from the the transaction of the statement which causes the trigger to fire? I have triggers ...
2
votes
1answer
2k views

Performance Inserting and Updating Millions of rows into a table

We have a CSV the user imports into the Application. After inspecting the CSV, validating and applying some business logic, we got Inserts and Updates operations we should perform into a table(...