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

learn more… | top users | synonyms (2)

1
vote
1answer
14 views

execute as login for nonexistent login successful

On Sharepoint database server, we found 18456 state 11: infrastructure error for account Domain\machineA$ every 10 minutes and nt authority\anonymous every hour. I checked syslogins but didn't find ...
2
votes
2answers
670 views

Scripting all of available databases to .sql files containing schema+data+users with single command

I have a SQL 2008 Express with 80 databases in it. I want to move these databases completely to another server which is hosting a SQL 2008R2 Enterprise instance. As i'm told it is better to script ...
3
votes
1answer
379 views

find total money of parent node of tree according to a particular pattern

actually i am working on a project in which members are added in a tree pattern, and get the payment accordingly. My tbltestingtree structure is ass follow: Id ParentId IsLeft ...
0
votes
1answer
32 views

Maximum Server Memory adjust in blocks of 1024MB?

Our protocol is to set Maximum Server Memory at about 75% of RAM. For a 4GB RAM, sometimes it gets set at 3000MB and sometimes to 3072MB. There is a 2 or 3 percent difference in these values. Is ...
2
votes
5answers
10k views

SQL Server mirrored database is stuck in recovery mode

I have a database that is stuck in recovery mode for few days already. There are multiple threads about this, but the solutions there don't work for me. Here is what I tried already: RESTORE ...
7
votes
6answers
15k views

A query that lists all mapped users for a given login

When looking at the properties of a particular login, it's possible to see a list of users mapped to that login: I profiled SQL Server Management Studio (SSMS) and I see that SSMS connects to every ...
3
votes
3answers
67 views

Assigning a domain user to run MSSQLSERVER service 2008R2

I have done this on a server (windows server 2008) before, but can not achieve the same thing on another virtual server I want a low level domain user to run the MSSQLSERVER service so that I can ...
9
votes
5answers
5k views

Attach/detach vs. backup/restore

I need to transfer the database (as a whole) to another server, to make a duplicate database to setup another test environment. I have two choices: Make a full backup at source server/restore at ...
3
votes
3answers
132 views

How can I make a database backup go faster?

I recently attended an interview for my next job and was asked this: How can I speed up the backup of a database sized close to 1TB or 800 GB in SQL Server? My Answer: We can use Database ...
0
votes
1answer
464 views

Automate SQL Server restore from Prod to Dev

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the ...
0
votes
0answers
16 views

SQL Server users when restoring from older version to newer version WITH REPLACE

Let's say we have a SQL Server 2000 database with a user named user2000. We have a database called foo on SQL Server 2000, to which user2000 has been given access. We also have a SQL Server 2008 ...
0
votes
1answer
56 views

SQL Server Shrink

When running the Maintenance Plan Wizard to shrink a DB ... I am selecting all databases, however I have it set to shrink only when it grows beyond a specific size. If a database grows beyond a ...
1
vote
0answers
34 views

How can I make a database backup faster? [duplicate]

I recently attended an interview for my next job and was asked this: How can I speed up the backup of a database sized close to 1TB or 800 GB in SQL Server? My Answer: We can use Database ...
2
votes
1answer
85 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
58 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
49 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
11 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
40 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
421 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
831 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
84 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
65 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
71 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
26 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
68 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 ...
1
vote
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
347 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
199 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
35 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
12k 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
37 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
176 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 ...
6
votes
2answers
152 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
421 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
15k 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
41 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
980 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
154 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
54 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
11k 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
89 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
426 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
34k 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 ...