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

learn more… | top users | synonyms (2)

2
votes
1answer
87 views

Filtered Index hint rejected by SQL Server

I have a filtered non-clustered index that SQL Server is not using. I'm pretty sure the optimizer is doing the right choice but I would like to force it to run with that index so I can compare the ...
3
votes
1answer
72 views

CLR Procedure with XML parameter returns Data at the root level is invalid. Line 1, position 1

I have following CLR stored procedure, which is throwing error on this command xmlDoc.LoadXml(inputXml); code public static int spGetTaxOfficeXML(SqlXml _inputXml) { // this ...
0
votes
2answers
52 views

RIGHT JOIN did not work as expected

I have a question about joining two tables. Schema CREATE TABLE [dbo].[DCString] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [DCDistributionBoxID] [bigint] NOT NULL, [CurrentMPP] ...
0
votes
0answers
12 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: ...
1
vote
1answer
43 views

How to SELECT a column in a row and return another row if it doesn't exists?

I want to write a query with two SELECT Statements in an order that if the first SELECT Statement doesn't return anything then only the second select statement runs. SELECT col1 FROM MyTable WHERE ...
7
votes
1answer
474 views

How do I count the number of columns in each table?

I want to write a script to listing the tables in my database with the number of columns in that table. Like this: table name number --------- -------- table1 1 ...
5
votes
1answer
856 views

SQL Server 2012 Publisher SQL Server 2008 Subscriber version issue

So I have a setup where one virtual machine is a SQL Server 2012 publisher and I need to setup merge replication on a SQL Server 2008 subscriber on a different vm. When setting up the subscription ...
3
votes
1answer
92 views

Intermittent “Conversion failed…” error

I have several stored procedures. These procedures when called have been fully operational for over 18 months without error. Over the last 3-5 months, occasionally I will receive the error: ...
2
votes
1answer
97 views

Efficient way to generate Time Slots for a schedule

I have a table named #MySchedule with columns StartTime, EndTime and duration. What I need is a query to get all time Slots for each row in MySchedule between these StartTime and EndTime based on ...
6
votes
2answers
3k views

When was a Database taken Offline

I am trying to find a way to figure out when was couple of my sql databases were taken offline. I checked the the logs but could not find any such info and moreover there is no default trace enabled. ...
3
votes
2answers
76 views

Edit Application Name to Log Connection once for Logons on Pooled Connections

I am wondering if I can change the application name of a connection in SQL Server itself, e.g. in the logon trigger? Is that possible or is the application name "read only"? Edit to explain the ...
2
votes
4answers
3k views

Msg 21, Level 21, State 1, Line 1 Warning: Fatal error 9001

We recently moved to a SQL Server that's on a VPS and every now and then we get a 9001 error when trying to do an update. Rebooting the server fixes the problem. I changed the maximum server memory ...
0
votes
1answer
27 views

Alter Time of DateTime Field

I am needing to alter the time piece of a datetime field. I tried to use this syntax, but it gives me an error of Incorrect syntax near the keyword as What syntax can I use to alter the end ...
0
votes
1answer
75 views

Create Calculated Fields from Distinct Values in Second Table

I'm trying to create a report in Microsoft SQL Server Report Builder 3.0 that has a date field populated with dates for a specified year and alongside, calculated fields for each distinct error code ...
2
votes
1answer
4k views

How to set the Tablix outer border width

In SSRS 2008 R2, a Tablix has got a "Border Width" property. I assumed that by assigning a value to this property, I can define the outer border's width. But it does nothing. I'd like to have the ...
2
votes
2answers
352 views

Found errors in table after executing DBCC CHECKTABLE command

I am trying to alter table to add primary key ALTER TABLE GeoCity ADD CONSTRAINT PK_GeoCity PRIMARY KEY(locId) and got this error message Msg 1750, Level 16, State 0, Line 1 then I run ...
0
votes
1answer
208 views

Creating database with a relative path

I am supposed to be testing a piece of software that uses mssql express as the backend for data storage. When it is creating its databases, it tries to create them in my home directory that is on a ...
0
votes
0answers
40 views

SQL Server 2008 Patch Installer shows incorrect information

When running the SQL Server 2008 SP3CU16, on one of our instances, the install seems to run through the wizard OK. However, the Setup Bootstrap log reports "The patch installer has failed to ...
3
votes
2answers
13k views

How to install SQL Server Management Console in 2008 Express Version?

I started a dozen attempts to install the SQL Server Management Studio for the 2008 Express Version of SQL Server. What i use: Windows Vista 2007 SP 2 What i did: a) I downloaded the install file ...
0
votes
0answers
39 views

Limit number of entries written by Logon-Trigger

We are auditing connections to our SQL server by using a logon trigger. (SQL Server 2008 Standard Edition.) However what we are seeing is that there are always a number of entries logged for each ...
6
votes
2answers
163 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 ...
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 ...
7
votes
4answers
529 views

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

I've been reading MSDN about TRY...CATCH and XACT_STATE. It has the following example that uses XACT_STATE in the CATCH block of a TRY…CATCH construct to determine whether to commit or roll back a ...
12
votes
1answer
16k 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 ...
3
votes
1answer
20 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
42 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 ...
4
votes
2answers
3k 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
994 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 ...
8
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) ...
10
votes
1answer
164 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 ...
3
votes
0answers
57 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 ...
12
votes
3answers
12k 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
97 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
460 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 ...
17
votes
4answers
35k 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
86 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
987 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 ...
0
votes
0answers
24 views
8
votes
1answer
10k 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
41 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
39 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 ...
5
votes
5answers
26k 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
8
votes
7answers
7k 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
18k 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
61 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
123 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) ...
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
45 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 ...