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

learn more… | top users | synonyms (2)

2
votes
1answer
689 views

MS SQL 2008 Express - Database Tuning Advisor

I want to use DTA, but on my website I have an Express Server version. Can I backup my DB on Express server, restore it on my personal computer with Enterprise Version, make some tuning by DTA and ...
2
votes
1answer
1k views

How can I check the integrity of disabled foreign key relationships for each individual table? (SQL Server 2008)

I'm porting a huge legacy database that many different legacy frontends depend on. When I'm finished, a new frontend will be running alongside all the old legacy frontends minus the frontend I ...
1
vote
1answer
15k views

Installing SQL Server 2008 Express Edition in quiet mode issue?

I want to install SQL Server Express 2008 in quiet mode. I know that we can use /ConfigurationFile="ConfigurationFile.INI" parameter to install SQL Server from a configuration file. The following is ...
3
votes
1answer
361 views

Why CDC records nothing while being enabled?

I enabled Change Data Capture on some tables in a Microsoft SQL 2008 database. Both of the following queries return 1: select top 1 is_cdc_enabled from sys.databases where name = ...
2
votes
1answer
4k views

Cannot fetch the rowset from OLE DB provider “SQLNCLI” for linked server X

I am getting this error while doing concurrent read from same table through linked server. Time out setting is alright. When this error happens, I also start getting the following error for the same ...
6
votes
1answer
2k views

NESTING_TRANSACTION_FULL latch

Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which ...
7
votes
1answer
2k views

Msg 2534 : DBCC CHECKDB allocation errors

We recently moved our intranet production database from an SQL Server 2008 to an SQL Server 2008 R2 brand new server. The backup Job have been failing since migrating, due to DBCC Errors (Check ...
-1
votes
1answer
82 views

Index Proposals

As a theoretical database question, if you are asked 'which indexes would you propose and why?' are the answers from primary, clustering and secondary index? How would we know if we should introduce ...
4
votes
1answer
4k views

Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different

Im using query to find out database file size. I'm using system view and DMV, because i do want to find out both- actual file size and theoretical (in case of sparse files) file size. Select ...
3
votes
1answer
368 views

Microsoft SQL Server 2008 R2 Developer Edition Data Tuning Advisor tool

I have Microsoft SQL Server 2008 R2 Developer Edition installed on my machine which has Win7 64 bit OS. I am not able see Data Tuning Advisor tool in my SSMS. Before this installation my machine ...
3
votes
1answer
566 views

why do SSRS reports run longer for a particular user

We have been receiving complaints from a particular SSRS user that her reports run slow. I investigated in the ExecutionLog table in the ReportServer database and I observed something strange. ...
2
votes
0answers
645 views

SSIS - Derive column values based on join/subselect from a non-table source

I have a flat file source that contains a lot of duplicate records, which have the same key fields but differ by their "changed date". I need to split this up into two outputs, one for the "latest" ...
1
vote
1answer
1k views

Merge multiple table output but indicate which tables the records came from

I have an application with a function that uses a stored procedure (SQL Server 2008 R2) to pull in and arrange data from one table. Now, I’ve been asked to make it pull from two tables, but it’s not ...
1
vote
2answers
261 views

SQL Trigger to verify account, if not verified dont process stored procedure/transactions

Is there a way to create a trigger or function to check a user against the database and if not verfied, the following stored procedure is not ran. I would like this option for verification check ...
0
votes
1answer
79 views

View in SQL Server 2008

I am in need of getting data from multiple select queries. So I thought of creating two views and to inner join them to get my data. My questions is: which option is better creating a view, ...
5
votes
1answer
361 views

Database structure, relational or data warehouse?

Good morning, Im having some issues with how my database should be laid out and would appreciate some guidance. I have several tables (rainfall data), each containing the following columns: Date ...
4
votes
2answers
1k views

replication monitor: why is current average performance poor?

I am a replication rookie. We have a very simple transactional replication set up in SQL Server. I am looking at the Replication Monitor on the subscriber and one of my subscriptions show Poor ...
1
vote
1answer
835 views

replication warning: transactional replication latency alert not working

I have set up a predefined replication alert (as outlined here) in SQL Server 2008 and the alert for Replication Warning: Transactional replication latency does not appear to work. This is triggered ...
4
votes
4answers
2k views

What options exist for copying data between servers on a regular basis?

My apologies if this has already been answered. I've searched SO and of course here on DBA, and am surprised to find no close matches. Specifically, I'm looking for a solution which survives schema ...
2
votes
1answer
2k views

creating an index for varchar column greater than 900 bytes

I have a var char column in SQL Server 2008 which is usually less than 100 bytes but in some cases can be huge up 5607 bytes. I need to index it, and I can't because the max index size in SQLServer is ...
3
votes
1answer
579 views

Restore SQL Server database backup to other directory than root

I have a database backup file (db.bak) which I want to restore and import in my SQL Server 2008 R2 instance. However, the database takes approx 1 GB of space, and my C: partition is really strained at ...
1
vote
1answer
97 views

SQL Server Agent

hi i need to run a procedure every first day of month. And the procedure should automatically run at every month.So with out using timer i have to use it. So i Think Better to go for SqlServer agent ...
1
vote
1answer
2k views

Why is a Login losing a User mapping?

Is there any way to determine why is a Login is losing a User mapping? I've created them with the following SQL ... CREATE LOGIN [qauser] WITH PASSWORD = 'xxxx', DEFAULT_DATABASE = [app_tst], ...
34
votes
7answers
23k 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
0answers
370 views

going from heirarchical XML to heirarchical SQL tables then SQL to c# objects to go to user view, how should I store the data in the databases?

Hi I'm getting my data from a web service using their getData call and it comes in as fairly chunky heirarchical XML, first everything all at once, like <allData> <product1> ...
1
vote
1answer
345 views

Importing large file while keeping table available for other operations

We need to import millions of rows in a quite big table. Say like 5 million rows into a table with more than 1 billion rows. After preprocessing the import file we will have update operations and ...
4
votes
1answer
3k views

UPDATE Statistics with full scan

Does running UPDATE Statistics on SQL 2008 r2 with full scan on a large table 50 million rows (non partitioned)? I am trying to confirm the following behavior. a) takes any locks during the entire ...
7
votes
1answer
179 views

Disallow new UDFs that contain a cursor

Is there a way to prevent developers from creating new user-defined functions that use a cursor? Would a database trigger that can read the code of a UDF do?
1
vote
0answers
1k views

Server Collation Change Error [closed]

I am using following command to change the collation of my SQL Server R2 (10.50.1600): setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLServer /SQLSYSADMINACCOUNTS=sa /SAPWD=**** ...
3
votes
1answer
317 views

SQL Server: Duplicate Index Question

We recently has a massive performance problem on our system, which we narrowed down to a problem with I/O on the LUN. IOPS increased twenty-fold during the period when the problem was occuring. The ...
7
votes
1answer
891 views

computed columns, index, clustered index and covering index?

We all know the we can only have one clustered view per table, cool. But apparently you don't have values of computed columns on leaf even when using a clustered index and they are computed each time. ...
3
votes
1answer
441 views

Table values being cut-off by table lines

Is there a way in the SSRS table component to avoid that the values get cut-off by the lines of the table.
6
votes
2answers
1k views

Can't get SQL Server 2012 RTM LocalDB working on Server 2008/x86/ SQL Server 2008 machine

I have installed SQL Server 2012 and LocalDB on my development machine, and am able to connect to LocalDB using both the SQL Server Management tool 2012 and from within my winform application using a ...
4
votes
2answers
5k views

sql 2008, select from a view as a different user, like owner

In sql server 2008, the system views sys.database_role_members and sys.database_principals, only returns the users and group that the current account is allowed to view. I need to check id a certain ...
7
votes
1answer
3k views

SQL Server, remove all users' access right to a table object

As a clean up job, I am trying to remove all users' (including non public users) access right to a single table object (apart from superusers) and will subsequently grant new access rights. Is there a ...
5
votes
2answers
2k views

SQL Server Wildcardless LIKE Optimization

In a web application using SQL Server 2008 R2 as the backend, I have several fields, that if present, add WHERE clauses to the query. If I was to have all queries generated as like clauses, such as: ...
1
vote
0answers
205 views

What overhead is there if I MERGE row by row?

I have a target table of about 2000 rows and I want to be able to MERGE (with HOLDLOCK) about 5-10 rows at a time into the target table. The target table doesn't get used heavily, with only 1-2 users ...
5
votes
1answer
521 views

SQL Server 2008 Multi Page Allocation

When a query or stored procedure has an execution plan greater than 8000 bytes, instead of using plan cache in buffer pool, it will use MPA (multi page allocator) which used memory outside buffer ...
1
vote
1answer
2k views

Full Text Search not searching special character

I am working on SQL Server 2008. I have created full text index on a Table(Column) and I am trying to find. SELECT * FROM TblName WHERE CONTAINS(ColName,'@#'); This is giving no results. But I ...
5
votes
1answer
6k views

Why does sqlcmd -Lc not show the local instance?

I have two machines, each with a default instance of SQL Server 2008 installed; each exhibiting a different behavior. On PC1, when I run sqlcmd -Lc, I do not see PC1 in the list of SQL servers on ...
10
votes
1answer
855 views

Why does 10^37 / 1 throw an arithmetic overflow error?

Continuing my recent trend of playing with large numbers, I recently boiled an error I was running into down to the following code: DECLARE @big_number DECIMAL(38,0) = '1' + REPLICATE(0, 37); PRINT ...
1
vote
1answer
141 views

SQl code performance

What measurement do you know if this sql code is efficient based on execution plans? How do you know if this sql code is efficient based on execution plans? SELECT [CustomerID], ...
5
votes
1answer
333 views

difference of left join and left outer join

In SQL Server join syntax we can use LEFT JOIN or LEFT OUTER JOIN and query result with each of above join not difference. this is a question for me that which are more useful.
3
votes
1answer
1k views

New foreign key and merge replication

We are experiencing an issue when creating a new foreign key to an existing table, which is being replicated to another SQL Server 2008. More specifically, we are adding a new foreign key to this ...
8
votes
2answers
6k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
3
votes
2answers
1k views

Tools for Identifying Needed Indexes

I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?
2
votes
1answer
841 views

Any problem with same conversation handle used by multiple services communicating with one target queue in a multiplexed service broker order queue?

I asked a question about implementing conversation groups in a SQL Service Broker setup which has several User Services communicating with the Server Service (Processing xml data requests to be sent ...
2
votes
1answer
123 views

Create a Function in sql server without specifying return_data_type

I am trying to create a Scalar-valued function in SQL server, without specifying the return_data_type i want to be able to return any datatype based on a parameter sent by the function caller e.g. ...
2
votes
1answer
325 views

Calculate number for each row

My table structure is below: CREATE TABLE [ACC].[Document]( [DocumentID] [int] IDENTITY(1,1) NOT NULL, [Date] [date] NOT NULL, [SalesCompanyFinancialPeriodID] [int] NOT NULL, ...
3
votes
1answer
4k views

SSRS email subscription with custom file name on the attachment

I would like to set up an SSRS email subscription that attaches the report in PDF format to the email with a custom file name that changes daily. I was hopeful that I could to this via a data-driven ...