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

learn more… | top users | synonyms (2)

5
votes
3answers
5k views

“TASK MANAGER” process is taking over a single-user mode database. What is it?

This is a duplicate of the question I asked on stackoverflow, but I was advised, that someone here could have a better idea what is happening. I have a sporadic problem, when upgrading SQL Server in ...
2
votes
0answers
40 views

How to Replicate data between a hosted database and a local database

I have a Cloud hosted Database, maintained by a different company. Here is my scenario: Cloud Hosted Database SQL2012 - Need to connect through a VPN. We setup a local Server with with Dual NIC ...
12
votes
2answers
14k views

Why do we need to rebuild and reorganize Indexes in SQL Server

After searching the internet i couldn't find the reason for Why do we need to rebuild and reorganize indexes in SQL Server ? what does internally happens when we rebuild and reorganize ? An ...
7
votes
3answers
208 views

In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?

Edit After getting first answers to this question I realised that I should narrow it down and changed the title of the question. Sorry for confusion. I've been reading MSDN about TRY...CATCH and ...
5
votes
2answers
3k views

Why isn't the objectname and indexname populated in deadlock graph?

I got a request to check out some errors on a 2008 sp3 server (10.0.5500.0 - Enterprise Edition (64-bit)) and see if I could find deadlocks that were happening earlier. Traceflag 1222 wasn't enabled ...
1
vote
1answer
15 views

Auto Increment Key Pair with Start Digit at 200000 SQL SERVER 2008

I'm currently rewriting a front end for a program that handles interviews. I want to prevent an issue the older program had were during the tiny amount of time it was doing a lookup for the next ...
0
votes
0answers
19 views

SQL server is replicating to wrong subscriber

I have a publication which is intended to push data to a subscriber over the internet from a remote server to our onsite local subscriber but it ends up on a wrong subscriber.Here is the scenario: ...
0
votes
0answers
15 views

Connecting database ISSUE to remote database in mssql [duplicate]

May be This question seems repeated but please read whole question carefully. I have Microsoft SQL Sever in one PC (ex. machine A) and I wants to connect and get data from that database from another ...
1
vote
2answers
637 views

After Restore Log_reuse_wait_desc of Replication

I'm wondering if anybody has ran into this issue before and has any suggestions on what's causing it/how to fix it. We are restoring a database into lower Dev environments and after the restore the ...
4
votes
1answer
50 views

Can't get databases or its objects with powershell

I have Powershell installed both in my server and development pc. In my dev machine apparently have a most recent version of PowerShell installed or some its components of SQL Server 'sqlps'. My ...
8
votes
1answer
548 views

Very high transactions per second

Our production server runs on average 4,000 transactions per second. In the past few days the average has jumped to 175,000 transactions per second. That is not a typo, it's 175K per sec. Looking at ...
0
votes
1answer
172 views

LDF files vs. TRN files vs. Tempdb.MDF+LDF files.:

We have a dilemma… We have all of our MDF and LDF files in the same…\192.168.6.22\d$\Program Files\Microsoft SQL Server\DATA\Production...folder. We have all of our TRN and BAK files on ...
0
votes
1answer
248 views

Migration script fires 'The data definition language (DDL) command cannot be executed at the Subscriber'

Yesterday I had run a migration script, produced by Visual Studio 2008 schema compare option, on a SQL Server 2008 database. It had run correctly. But today, when I executed the same script, it threw ...
4
votes
2answers
2k views

How to find which edition the SQL Server installation media belongs to

Without installing SQL Server media 2008 and 2012, how can we know that the media belongs to a certain edition (like Developer, Standard or Enterprise)?
6
votes
2answers
935 views

Executing Powershell SMO against a cluster with elevated privileges to update service accounts

I'm working on a Powershell script to update service accounts on named SQL instances in a failover cluster. I'm trying to use the SMO and WMI to update this so that I can update the service account ...
7
votes
2answers
2k views

Shared Lock issued on IsolationLevel.ReadUncommitted

I read that if I use IsolationLevel.ReadUncommitted, the query should not issue any locks. However, when I tested this, I saw the following lock: Resource_Type: HOBT Request_Mode: S (Shared) ...
2
votes
1answer
393 views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
1
vote
2answers
97 views

Which data type should I use to store a 11 digit number [closed]

Which data type I should use to store an 11 digit number? It must store '0' as the first digit. For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows ...
9
votes
1answer
119 views

Why would suser_name() not reflect an AD account name change?

One of our users' names was legally changed, so we changed their Active Directory username to match - from domain\oldname to domain\newname. However, when suser_sname() is called by this user in a ...
4
votes
1answer
2k views

Receiving error Error: 18059, Severity: 20, State: 1 in the SQL Logs

I'm using SQL Server 2008 Sp3 Lately I'm having errors in the SQL Logs: Error: 18059, Severity: 20, State: 1. The connection has been dropped because the principal that opened it ...
3
votes
0answers
48 views

SQL Server Incremental Backups Split

I am trying to recover a database where the incremental (log) backups are split between two methods (Carbonite/Zmanda and SQL .bak files). When I try to use the restore wizard I get the following ...
6
votes
0answers
108 views

DBCC Check DB issue

I have database of 99GB on this database when I am running DBCC Checkdb('IssueDB') It gives error as: Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while ...
11
votes
3answers
9k views

How to clone a user in SQL Server 2008 R2?

Is there a way to clone users' security and permissions in Microsoft SQL Server, preferably using the SQL Server Management Studio GUI?
0
votes
2answers
67 views

Concatenate column values [duplicate]

I have a query that uses a couple joins to get a name from another table. It works okay except that I would like to combine "Examiners Name" instead of having multiple duplicates: ID | Name ...
1
vote
3answers
356 views

Using Echo statement writes Echo is ON in the text file

Following code is used for write a number in the text file but instead it is writing "Echo is ON" into the text file. SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck Print @CMD EXEC xp_cmdshell ...
14
votes
4answers
27k views

When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

Background I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 ...
1
vote
1answer
38 views

SQL Server Import Export Wizard Error - Server throw Exception

I am trying to move all database tables from one db to another as a nightly job. The first (export) db is running sql server 2008 - SQL Server Authentication - I connect with SQL Server Native Client ...
4
votes
1answer
932 views

Microsoft SQL Server 2008 - Catching user role changes with SQL Server Profiler?

I'm trying to figure out why one of my SQL server users keeps losing sysadmin role when I uninstall a particular program. Can I use the SQL Server Profiler to track down the action that causes this to ...
3
votes
2answers
478 views

Migration to databases Domain Users do not have access

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
4
votes
1answer
244 views

'CONCAT' is not a recognized built-in function name

A client reported that they were running on SQL Server 2012, and we delivered some test queries for testing prior to a final delivery, however: 'CONCAT' is not a recognized built-in function name. ...
0
votes
0answers
46 views

Index Maintenance taking long time - Primary Key (Clustered Index) Varchar(35) [closed]

I have a table in my database that contains around 70 Million rows. Unfortunately the database designer had made the primary key (Clustered Index) column of type : Varchar(35). Obviously this is ...
0
votes
0answers
22 views
6
votes
1answer
8k views

Best approach for populating date dimension table

I am looking to populate a date dimension table in a SQL Server 2008 database. The fields in the table are as follows: [DateId] INT IDENTITY(1,1) PRIMARY KEY [DateTime] ...
0
votes
1answer
40 views

Copy A Database

Is it possible to copy a SQL Server 2008 Express without launching SSMS? Little more back-story: my internal drive was removed and is now used as a USB drive (upgraded to a larger hard drive) and ...
2
votes
1answer
32 views

String parsing: Extracting the filename from a URL

This is a link I have in a column of a table: http://earthexplorer.usgs.gov/browse/etm/1/11/2000/LE70010112000130KIS00.jpg There are like 1000's of rows like this. Is it possible to extract the ...
36
votes
9answers
85k views

Passing array parameters to a stored procedure

I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm ...
5
votes
5answers
23k views

How to drop multiple tables with common prefix in one query?

I'm using Microsoft SQL Server 2008.My question is:How to drop multiple tables with common prefix in one query? something like that table names: LG_001_01_STLINE, LG_001_02_STFICHE
7
votes
7answers
6k views

Stored Procedures under Source Control, best practice

I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my ...
6
votes
3answers
1k views

SQL Server parallelism for DML operations

I know SQL Server can use parallelism for certain DDL operation e.g. index rebuild or certain SELECT statements mostly likely in a data warehouse environments when query cost exceeds cost threshold ...
2
votes
3answers
17k 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 ...
-2
votes
3answers
57 views

Extract Portion From A Decimal [closed]

I need to extract any value > .5 from a decimal. Here is sample ddl Create Table #b ( fv decimal(16,4) fv1 decimal(16,4) ) Insert Into #b (fv) Values ('.8032') How can I capture the .3032 and ...
0
votes
1answer
44 views

Conversion failed when converting the varchar value 'Hours' to data type int

Conversion failed when converting the varchar value 'Hours' to data type int. This is my query: Sum(Cast(Cast(DATEDIFF(HH, ld_time_tracker_start_time, fld_time_tracker_end_time)%3600/60 As int) ...
0
votes
0answers
45 views

Cannot See Stored Procedure on Linked Server

Cannot see Stored Procedure on Linked Server. I have connected two server using linked server, I can see only tables and views. I'm not able to see stored procedures. I have set RPC to true.
26
votes
4answers
5k views

Does the order of columns in a table's definition matter?

When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
0
votes
0answers
42 views

Query Freezes and SSMS Crashes

I feel like I have the syntax set on my query, but anytime I try to run this SSMS freezes and I have to reboot and once I get my PC back up and running no records have been updated. The table only ...
8
votes
2answers
11k views

How do I check for a null or empty table-valued parameter?

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be ...
5
votes
1answer
899 views

Calculating Median Value within a Group By

I have a Stored Procedure which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do ...
4
votes
2answers
56 views

Users running expensive queries

I am currently monitoring the expensive queries from the activity monitor for SQL server. How do I see who is running the different queries?
2
votes
0answers
16 views

TLS 1.2 with SQL Server 2008 [duplicate]

I have a VM where I use SQL Server 2008 as a black-box data store for a product I work with. I'm being told by my IT police that my VM is not (or will not be) secure in a short time unless I can ...
4
votes
1answer
654 views

Cannot Delete FileStream Group from Sql Server 2008

We have migrated all our varbinary(max) column data to Azure Blob Storage and so we want to remove the old filestream columns that remain in our Sql 2008 database and the filestream filegroup but when ...