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

learn more… | top users | synonyms (2)

1
vote
2answers
63k views

How to rollback after wrong update statement in sql server 2008R2? [duplicate]

select * from aa update aa set City='chennai',LastName='vinoth'; ID FirstName LastName City 29 Abcrdrr vinoth chennai 1 John vinoth chennai 2 Joe vinoth chennai 35 ...
1
vote
2answers
491 views

SQL Server Login to access tables of three different database from single database

I have created a login in SQL Server 2008 R2 and given access to run stored procedures in that database only. But this stored procedure is actually accessing data from other two databases to which ...
0
votes
0answers
222 views

Page count differences between sys.dm_db_index_physical_stats and sys.allocation_units

In doing research for index maintenance, I found some significant differences between the page counts reported by sys.dm_db_index_physical_stats and sys.allocation_units in some of the clustered ...
1
vote
1answer
607 views

Database Mirroring and Replication in SQL Server 2008 R2

I have configured the mirroring and replication between 4 servers (A,B,C,D).i.e, Mirroring between A to B and C to D, Replication between A and C. The configuration was a success and I am able test ...
1
vote
0answers
724 views

Backup failed due to lack of hard drive space…how?

I had a maintenance plan fail last night (runs daily) for the first time in ages. The following is the specific error I received, minus the actual database name: Extend Disk Backup: failure on ...
0
votes
0answers
26 views

SQL Server 2008 R2 hamachi connection [duplicate]

I need to connect to remote DB over Hamachi, the tutorial states that I need to set tcp/ip to enable (which I did, in SQL Server Configuration Management tool) and add Hamachi IP to ipadresses, but ...
3
votes
1answer
3k views

Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue?

I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express ...
0
votes
1answer
53 views

group by+where on calculated column [duplicate]

SELECT SUM(a)-SUM(b) AS c1, SUM(a) AS c2, SUM(b) AS c3 FROM tbldoc GROUP BY Cid WHERE c1<>0 If you take ( WHERE c1<>0 ) not error Thank you Answer
1
vote
3answers
1k views

How do I make SQL Server database accessible to PC's on the LAN?

We are using Microsoft SQL Server 2008 R2 Express on server machine with instance name abc12. This instance contains two databases: alarm report but when we connect to the abc12 instance from ...
0
votes
2answers
185 views

SQL Server Management Studio is not available

When I installed SQLManagementStudio_x64_ENU.exe I am not able to connect to my server. The server name is not visible in either image. Is there any issue in my installation?
-1
votes
2answers
730 views

How to Find All SQL Server database back list in my system? [closed]

My SQL Server 2008 R2 system has different database backups on different drives like C:, D:, E: Now I would like to see all database backups listed in SQL Server 2008 R2. Thanks for your time
2
votes
3answers
574 views

How to restore a database then upgrade it in a single SQL script?

I'm trying to write some SQL that will restore a backup if the database doesn't exist, then run SQL on the database. It is used for installing a database we received from a 3rd party in the form of a ...
1
vote
1answer
671 views

How can I count different types in a column in sql? [duplicate]

Data: QID Type DOB ------------- 1 1 01/01/1980 1 2 03/01/1981 1 2 01/02/1991 1 1 01/01/1980 1 2 03/01/1981 ...
0
votes
1answer
8k views

How to export data from sql server 2008 to excel and email as attachement

I'm new. Can anyone advise how I can export data from database sql 2008 to excel and email as attachment?
2
votes
1answer
604 views

Database deployment script for SQL Server 2008 has different behaviors

Using Visual Studio 2008, I have compared schemas between remote SQL Server database with scripts hosted in a TFS server. The goal was to generate a migration script that would update the remote ...
2
votes
2answers
973 views

How do I map a SQL Server login to a user that already exists in a read-only database?

One of our database users complains that they can't select from one of the databases in this particualr instance while they have no problems in accessing others in this very same instance. The one ...
1
vote
1answer
230 views

Include fields from a table function in a select

I'm running this query on SQL Server 2008 R2: SELECT orr.Rate, orr.ID, orr.UserKey, fn.NetRate FROM OngoingRate orr CROSS APPLY dbo.GetRateNetView(orr.UserKey) as fn WHERE orr....
-1
votes
1answer
21 views

How to synchronized data in different database?

I have four database(ALD,ASO,PAD,STF) each database having table vendors. All database vendors having same schema but data are vary from database to database.so my problem is i need to synchronized ...
0
votes
2answers
607 views

migrate from sql server 2012 to sql server 2008 R2 [duplicate]

I have been looking for the information since yesterday and could not find anything... I have sharepoint 2012 fundation and my SQL Server 2010 Evaluation is short of space (we already used 10GB), ...
3
votes
1answer
1k views

Check if database is restored to the most recent log file

Folks, I am running a copy of our production database as a backend for an Instance of SQL Server Reporting Services. The copy is being updated off transaction logs which are taken frequently (hourly) ...
3
votes
1answer
4k views

Problems with clustered DTC server 2008 R2 when failed over or restarted

I had a working clustred DTC, but due to a disk migration, I had to delete the service and recreate it. Thats how the problem started. When I recreate the service, it comes online perfectly. But when ...
1
vote
1answer
580 views

Transaction Log Shipping doesn't use the latest transaction log

I have setup transaction log shipping between two servers every 15 minutes, this is on SQL Server 2008 R2. The problem is that when the logs are being restored on the secondary server, it always uses ...
6
votes
1answer
24k views

QUERY - pivot multiple columns, variable number of rows

I have a table that looks like this: RECIPE VERSION_ID INGREDIENT PERCENTAGE 4000 100 Ing_1 23,0 4000 100 Ing_100 0,1 4000 200 ...
4
votes
1answer
2k views

Security implications disabling promotion of distributed transaction for linked server

I've a a linked server and I need to run the following statement: INSERT INTO...EXEC linkedserver.sp @parameter Both servers SQL Server 2008R2 SP1. Once I run it I get this error: Msg 7391, ...
3
votes
3answers
8k views

How to create new table with same constraints and indexes?

I am Creating a new table with Primary Key Constraints and a Non Clustered Index in that table. I know, I would like to create another table with same structure and values as well keys and indexes ...
1
vote
0answers
860 views

Backup Restore Procedure error SQL Server 2008 R2

We use a stored procedure to do a backup and restore of databases as a copy function. This allows our front end users to spin up sites from pre created template databases. Lately we've been getting ...
0
votes
1answer
2k views

How to get accurate row counts in partitions in a table

I am trying to fetch row counts in each partition for a table. But I got confused as in sys.partitions row counts is on basis of indexes. So how can I get exact row count for partitions in a table.
2
votes
1answer
1k views

Intentionally recursive instead of insert trigger does not want to nest

I have been struggling to get an instead of trigger to recursively call itself. The idea is that when a row gets inserted into my table the trigger is placed on, based upon certain conditions ...
2
votes
2answers
3k views

Upgraded from SQL Server 2008 R2 Express to SQL Server 2014

I've upgraded my SQL Server from the 2008 R2 Express edition to a full standard version of SQL Server 2014. Now I'm running them both side by side. But my problem is how can I abandon the 2008 version ...
0
votes
1answer
359 views

Recurring Memory Issues

We have a Windows Server 2008R2 server with 32GB or RAM running SQL Server 2008R2, along with an IIS server. The machine is also used as a backup server. The problem is, periodically (every week or ...
0
votes
0answers
294 views

SLEEP_TASK and PAGELATCH_XX peaking after upgrade to R2 SP1

I wanted to just add a comment to this question: SLEEP_TASK Wait Type in SQL Server - What does it indicate? But I'm having not only SLEEP_TASK wait but also PAGELATCH_SH and PAGELATCH_EX wait types. ...
5
votes
2answers
870 views

DBCC CheckDB causing high values for resource_semaphore wait type

On one of our production server, where SQL Server 2008R2 (Service Pack 2) is installed with 24 GB memory and 24 CPU cores grouped into two NUMA nodes, we are facing high counts for resource_semaphore ...
-1
votes
1answer
723 views

Find day values in bigint date?

This query is working good. select DATENAME(DW,getdate()) AS [Weekday] My Problem is suppose I give input values like select DATENAME(DW,InvoiceDate) AS [Weekday] from store; is not working in ...
6
votes
3answers
19k views

How to SELECT multiple COUNT(*) columns with each GROUP BY having a different WHERE clause?

I have the following schema: CREATE TABLE Person ( PersonId int PRIMARY KEY ) CREATE TABLE Action ( ActionId int PRIMARY KEY, PersonId int NOT NULL FOREIGN KEY REFERENCES Person(PersonId), ...
0
votes
0answers
517 views

Suppressing the removal of trailing zeros in numeric data type in sql server

I initially declared the data type of the columns for some of the tables i am working on as "NUMERIC". cause of that, trailing zeros are being removed. Is there anyway to suppress this behavior? i ...
4
votes
1answer
2k views

Shrinking large DB file not working

I have a 5 TB database file (don't ask why) that I am cleaning up. I was already able to free over a TB and I will probably end up at 2.5 TB. All best-practice advice aside, I need to now shrink this ...
1
vote
1answer
2k views

Using ODBC Connection to Access Firebird Database

I have an ODBC connection to a Firebird database that tests successfully. Is there a way to use this ODBC connection within SQL Server 2008 R2 to access the tables? I haven't used Linked Servers ...
1
vote
0answers
96 views

GRANT CONTROL SERVER when user may be an entity owner

I have a login called user1. He only has the role of public. I want to grant CONTROL SERVER to him. So from sa I run this query USE [master] GRANT CONTROL SERVER TO [user1] Which gives me this ...
6
votes
1answer
94 views

Giving server-role to login breaks stored procedures

I have a login called user1. user1 can execute storedprocedure [Test].[SP1] EXEC [Test].[TestSortation] EXEC [TestSortation] Both of the above commands work. Now if I give the user1 the server ...
3
votes
4answers
260 views

How to find duration in a dataset

I have a dataset of the following structure: Target, PollTime, Value ------------------------ 1. A, 2013-12-13 13:31:44.000, 15.00 2. A, 2013-12-13 13:32:44.000, 16.00 3. A, 2013-12-13 13:33:44.000, ...
2
votes
1answer
518 views

Does SQL check each And condition if first condition fails? [duplicate]

For a query of below kind does sql process data for each And condition if first And condition fails? If Emp_ID is not 12 for a row then does it check condition of Emp_Name? So how SQL process this ...
5
votes
4answers
2k views

SQL agent job, why is the job history showing that a step is still running even though the job has completed

I have a sql agent job that runs Powershell as its first step (there are 3 steps in total). I have set this step to have 2 retries, with a 3 minute retry interval. When I look into the job history, ...
0
votes
0answers
132 views

Transactional Replication

SQL server 2008 R2. I have set up transactional replication on 40 tables in a database (other tables don't replicate). There was an issue in the morning and it is fixed now. I resynchronised the ...
1
vote
0answers
162 views

SQL Server 2008R2 Developer's Edition Error while Installing

I had Sql Server 2008R2 Developer's Edition Installed on my machine, Two Named instances, Default Instance was Sql Server Express. and there was another named Instance Evaluation Enterprise Edition. ...
1
vote
0answers
4k views

Management Studio Generate Script Errors

I am trying to generate a script (right click database, tasks, generate scripts, tables) from SQL Server Management Studio 2008 R2 Express and I keep getting an error. I read somewhere that it could ...
4
votes
2answers
66 views

Determining the tables and impact of a large number of SPs

I'm trying to track down what is causing some response times to increase when data is compressed by page compression, when the system is not CPU bound and 95% of responses see a decrease in response ...
7
votes
7answers
24k views

How to find out the content differences between 2 SQL tables and produce sync SQL

How do I find out the differences in data between the two tables that have exact schema, and how to produce synchronisation SQL to get the union results (without duplicates) ? These are the 2 ...
3
votes
1answer
3k views

CREATE DATABASE permission denied for sysadmin

I'm attempting to create a stored procedure that can be used as a proxy for creating databases where users do not have rights for creating databases. See Restricting Database Access/Functions to User ...
6
votes
3answers
475 views

What are some important security-related error codes / events to monitor?

I am setting up alerts in SQL Server and am interested in what events I should be raising alerts for. In particular, I am looking for any events related to security. I am already monitoring error ...
1
vote
1answer
19k views

Trigger to update a date field on one table when another table is updated

I want to be able to update a date filed in one table when another table is updated. I have the following two tables: Transdetails, Customers. In the Customers table I have a field called ...