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

learn more… | top users | synonyms (2)

3
votes
3answers
185 views

Log File Management - Space on Disk

At our organisation we have several non-Production environments where Developers run free and wild with TSQL code and databases. The DBA team do not normally monitor or maintain them. Recently several ...
0
votes
0answers
365 views

MS SQL SERVER Database mail stopped working automatically due to Timeout error

I am using SQL Server 2008 R2 SP1 , sometime database mail stopped automatically with the following error. How to fix the same. It have service broker enabled and i cleared the unsent mail. ...
2
votes
1answer
4k views

How do I go from .xlsx to sql Server [closed]

I'll try my best to explain this. I've got a spreadsheet (.xlsx) with 220 rows and 15 columns. I need to make it into 3rd normalized form eventually, but how do I get the data from the spreadsheet ...
2
votes
2answers
57 views

Should I opt for a new database or adding more tables?

this is a question around best practice that I'm currently dealing with. I have a database (SQL Server 2008 R2) with a heap of tables (around 40) with data, now the need has risen to have logging of ...
0
votes
2answers
218 views

Are #tables and #procedures 'not best practice' or 'bad practice'?

I am working on a project and part of it is an interface tool that executes SQL code stored in a xml file to import/export files. In the file, temp procedures and temp tables are used quite heavily (...
-2
votes
1answer
2k views

Select Top N rows for Employees in a SQL Server table

I would like to select the TOP 2 activities carried out by all employees from an Employees table "Emp" which has a timestamp column. The regular "Select Top 2 * from Emp...." will only fetch the top ...
0
votes
1answer
650 views

Configuring Reporting Services - ExecutionLogDaysKept

Is it possible to set Reporting Services options when Reporting Services is not installed on a server? Is this how a SQL Server instance used by SharePoint works? I'm a new DBA (very junior...), and ...
1
vote
3answers
1k views

Matching 3 columns in row - if one column is null then exclude it from comparison

A B C Equal 1 1 1 Y 2 2 NULL N Hi, I have 3 columns which I need to compare : Columns A,B,C. If these 3 columns are the same then column 'Equal' = 'Y' But, if a column is null, ...
0
votes
0answers
32 views

Rebuilding an index not working? [duplicate]

I noticed that when I right click manually on an index and choose "Rebuild" or "Reorganize", its not giving any indication that its doing so, then I click on properties again for the same index, I see ...
2
votes
1answer
3k views

SQL Server many-to-many relationship, row reuse?

Below is your typical many-to-many relationship. Pretend there are 2 tasks that have the exact same Tags. because of foreign key/primary key relationship constraints, there is no way for the rows in ...
1
vote
2answers
43 views

Garbage DB Objects Cleansing

How do I find in use db objects from all application pages is there any tool or trick I can use, just like we do search in Visual studio for an object in entire solution
-2
votes
1answer
3k views

Using the “Select Top (100) percent vs. Select * , or selecting any column” [closed]

I wrote the first query below that I was hopeing to give me the exact same results that the 2nd query does, but it seems there is one recrod different: SELECT DocumentSourceName FROM ...
3
votes
1answer
184 views

Maxdop in Profiler

Say I run a server side trace against a SQL Server 2008 R2 instance, which has Maxdop = 1 in place, then I run the trace through readtrace.exe to get RML files for replay. Does the replay preserve ...
0
votes
0answers
2k views

Error attaching mdf file to SQL Server 2008 R2 [closed]

When I want to attach a .mdf file to SQL Server 2008 R2, but I get the following error: Attach database failed for Server 'BABAK-PC'. Unable to open the physical file "C:\madrase\madrase\...
5
votes
3answers
2k views

Virtual Log Files (VLFs) Fragmentation

I have a database with 160 VLFs. How do I determine whether the VLFs are fragmented? Further, how do I find the percentage of fragmentation? Another thing that I want to confirm: Are the following ...
3
votes
2answers
18k views

Reclaiming data file free space after deleting a large amount of data

I am an accidental DBA. We have a sql cluster that hosts 10 databases. Suddenly the size of one of the database increased drastically from 100GB to 250GB. When we checked the datafile, the size had ...
3
votes
1answer
637 views

SQL Agent Permissions - Job Level

We currently have an SQL Job that runs an SSIS package. The output of the package is required for a report. As a result, the application that can create the report on demand first tries to run the ...
0
votes
0answers
93 views

msdb 'backupset' log history

One of our production server‘s backup job is getting failed for one DB because of Low disk space when I looked into ‘physical_device_name’ column in ‘msdb.dbo.backupset’ to get the path of backups I ...
0
votes
1answer
85 views

SQL Server - Is it possible to forbid creation of default constraint name?

I was having a looking on this article about default constraints and I have a question. Is there a configuration in SQL Server 2008 that forbid naming creation of new default constraints declared ...
0
votes
1answer
101 views

Using existing SQL Server credentials for log shipping reporting purposes

I have set up log shipping, but I don't see how I can reset orphaned SQL Server credentials on the destination server as databases are in standby/read-only mode. I am getting push-back on ...
2
votes
1answer
4k views

Can I upgrade SQL Server 2008 R2 Standard to SQL Server 2014 Developer?

Is there a migration path to upgrade SQL Server 2008 R2 Standard to SQL Server 2014 Developer? If so, are there any pain points? If not, how would you recommend I proceed? The context of this ...
1
vote
1answer
56 views

Should I add it as an additional column or a seperate table?

I have a table in my SQL Server that's called "Regions", it has information about 9 regions and the 10th row holds the info about our Division. Now that we will be merging another 2 divisions with its ...
0
votes
2answers
271 views

Make a database inaccessible through “sa” user but should be accessible through another user

I have a database BugTracker which is accessible from "sa" user. I have just created new user AdminBugCatcher. I have also mapped "BugTracker" database with this user. But this database should not be ...
0
votes
0answers
198 views

Using SQL Server Import\Export Wizard with Ms. Access as a source DB

I need to copy this Ms. Access database that we have to test it in SQL Server 2008 R2, is the Import\Export wizard will let me copy all tables, queries, forms... without affecting the live database? I ...
2
votes
1answer
33 views

remove a paritition table

I need to remove a partition table for an application upgrade. It is SQL Server 2008 R2. The partition column is on column col1 data type smallint, null. Col1 has length 10 and precision 5. As you ...
3
votes
2answers
2k views

db_owner Member Can't Delete on Tables

SQL server 2008 R2. A user is part of an AD group. That AD group has a SQL account in this instance. That group has the following access to a Database: db_datareader db_datawriter db_ddladmin ...
2
votes
1answer
366 views

Is there a negative effect if I place an index on every column in a wide table?

We have a wide dataset that isn't very tall (say 250K rows, 90 columns). We will be providing users with an interface that allows them to generate arbitrary queries against this dataset. They can ...
1
vote
1answer
529 views

After log shipping, I activate failover with a RESTORE. What happens when the next log shipping job runs?

I'm using log shipping for the first time. I've got it working between my primary and secondary server, on the secondary server the database is Restoring... which I understand basically means it is ...
0
votes
1answer
147 views

Unsure of Rank / Dense_Rank - Mulitple Columns - change rank when unique combination changes

I need to get the unique combinations of 2 fields, with a RankID for each unique combination In this example I need to create new IDs for each Customer/Value combination. Source table: CustID ...
-1
votes
2answers
99 views

Master Database Custom Table - Performance Issue?

So, using this article (http://www.sswug.org/articles/viewarticle.aspx?id=22848) Use master Create table master.dbo.EvalRes (expVal real) -go Use databasename GO Create Function [dbo].[Eval] (@exp ...
5
votes
1answer
297 views

How to audit users connections via ODBC to all dbs in SQL Server 2008 R2

I am not a trained DBA, but I manage a software application that uses SQL Server for the back end. The applications manages "projects" and each project has its own database. Because of the limitation ...
1
vote
1answer
870 views

How should I set up SQL Server Login permissions for access to website on a different server using Windows Authentication

I know this question doesn't really belong here, but I posted on the MS IIS Site and am still hoping for a reply. I have an intranet site created with .NET Framework 4.0 in Visual Studio 2012 and I ...
2
votes
1answer
7k views

Moving SSIS Packages to New Server

I have a number of SSIS packages in a number of folders on MSDB SSIS on SQL Server 2008R2. We are migrating to a new server host but remaining on SQL Server 2008R2. How can I copy/migrate these SSIS ...
3
votes
0answers
193 views

Using Import Wizard with Access - No null values

I am trying to import a table from Access to SSMS using the Import Wizard but when I do the resulting table has a lot of empty strings instead of the NULL values that I am expecting. How do I make ...
2
votes
1answer
790 views

Mirror failure after windows updates - possible cause?

Over a dozen SQL Server mirrors had windows updates installed this morning - 2 of which developed serious faults afterwards. The windows updates installed where: KB2939576 KB2957189 KB2957509 ...
1
vote
1answer
124 views

Database slowdown at random times using mirroing

Our web application suffers DB query timeouts a few times a day at random times. Can you help to work out the cause, e.g. deadlocks, mirroring (disk or network), etc? We have two identical database ...
7
votes
2answers
6k views

An efficient way to compare two large data sets in SQL

Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations. The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and ...
8
votes
2answers
12k views

How can I tell how many Cores SQL Server is actually using?

I have two servers running SQL Server. Server 1: SQL Server 2008 R2 Express (4 core) Server 2: SQL Server 2012 Developer Edition (8 core) As far as I am aware SQL Server 2008 R2 Express should ...
1
vote
1answer
112 views

Should I use a surrogate key in a mapping table?

I am deploying a mapping table which connects Items to ItemGroups Though an Item will only ever be in a single ItemGroup please accept a priori it is not appropriate in my use case to append a column ...
3
votes
1answer
111 views

Mirroring SQL Server 2008R2

My question might be dumb... But anyone please tell me Does automatic fail over happens in mirroring if the connection between witness server and principle server is lost for some reason.? Thanks ...
3
votes
1answer
245 views

Cannot set NOCOUNT to OFF inside the trigger execution because the server option “disallow_results_from_triggers” is true

I have morning job schedule that starts at 5:30 AM....starting on 5-27-2014. I've begin implementing table replication on one of our heavily used tables...and use the replicated table for reporting ...
3
votes
2answers
4k views

Ideal values for wait time in SQL server

This is related to my own post here The SQL server starts everyday at 8:30 AM and stops around 9:30 PM.( So wait times will reset everyday) SQL server will be used by around 60 users simultaneously(...
1
vote
1answer
471 views

Simple query not returning blank records

Very simple example here to a very frustrating treasure hunt for the mystery character. I have a table with a varchar(1) NOT NULL column - everything default - ANSI_PADDING & NULLS are ON for the ...
2
votes
2answers
2k views

Fix Linked Server Without Restarting SQL Server Instance

Problem: Occasionally a nightly job will fail, and when I look into it, it is the result of a query across a linked server failing. But for some reason that query seems to remain 'open' in some way ...
3
votes
1answer
23k views

distributed queries are configured to run in single-threaded apartment mode

I have sql server 2008 R2 64bit Developer's Edition Installed on my machine. And Microsoft Office 2010 Professional 32bit. I have been trying to import some Excel data from an Excel sheet into a sql ...
2
votes
0answers
310 views

SSIS Bulk Insert Task Operating system error code 1311

I have been running SSIS 2008 R2 Packages for a long time and when ever my Packages trough SQL Server Job accesses FTP Server which is on the same domain it uses correct credentials ,e.g. domain\...
0
votes
0answers
19 views

Date Range Limit SQL [duplicate]

I am trying to create a report that calculates how many contracts have been funded each month for the year 2014. The problem is I am trying to limit the dates, meaning I want the user to be able to ...
0
votes
2answers
468 views

Get Date Limitation

So what I need is a limitation for the user of the funded contracts. So this is what I am trying to implement. I am trying to let the user enter dates within the year 2014, which will bring up how ...
2
votes
2answers
5k views

Run “USE” with linked server alternatives

I am using SQL server linked servers and find some difficulties while running queries. Below is the actual query. USe testdb SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1')...
2
votes
2answers
9k views

Remove last character in selected column?

Query: select * from aa; Output: ID FirstName LastName City ----------------------------------- 29 Abcrdrr hai chennai 67 adf adsd adsf 1 John vinoth ...