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

learn more… | top users | synonyms (2)

2
votes
1answer
8k views

TSQL Get minute part from time data type

With the time data type (I'm using Microsoft SQL Server 2008), is there any way to take only the minute part out of it? I tried to pass the time to datepart and datediff function but both refused to ...
1
vote
1answer
160 views

Which should be used after messing up clustered index? Update statistics, Reindex or Reorganize

If I have a clustered single column index, e.g FooId in SQL Server 2008, 2012 and I know I have messed up the order of the rows to not have sequential order of the FooId column anymore, which ...
2
votes
1answer
2k views

Error attaching database after deleting NDF file

I have deleted .ndf file by mistake from a SQL Server 2008 database. While trying to attach a previous copy, SSMS raises this error: Attach database failed for Server 'MDSWIN34'. ...
2
votes
2answers
83 views

Data Backup and Restoring the same if requried in SQL SERVER 2008

In Multi-tenancy , Shared Database Shared Schema. How to take Tenant level backup and restored the tenant data when required? Can any one guide me or point me to the right direction. Thanks!!! NB: ...
1
vote
1answer
2k views

FILESTREAM is enabled, but I get a message that it's not. Anyone able to help?

I have been given the task of setting SharePoint Foundation 2010 up in work. I have hit quite a few problems (see other questions I have posted), so I decided to start from scratch again. I have ...
1
vote
0answers
237 views

Sort a non-grouped (SUM) column in descending order [closed]

I have a SSRS Report that contains 3 columns: Asset, Project Type and Total Hours. Report is grouped by first level grouping on asset and second level on Project Type. For example, clicking on Asset ...
1
vote
1answer
983 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 ...
2
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
319 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 ...
4
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
381 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
437 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
3k 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
295 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
320 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
8k 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 ...
3
votes
1answer
156 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
444 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
268 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 ...
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
155 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
655 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
5k 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
692 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
1k 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
310 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
129 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
694 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
5k 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
295 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
333 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
39k 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
1k 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
146 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 ...