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

learn more… | top users | synonyms (2)

5
votes
2answers
292 views

Cannot tune database any further; what next?

We have a vendor-supplied application. It is under support and we are talking to their developers (we have their largest database size by an order of magnitude), but in the meantime, we have the ...
1
vote
3answers
251 views

Is it possible to have the transaction log file write to a disk on a remote machine?

For the SQL server transaction log file, it is possible to set its target file to a disk on a remote machine? I am working on a BI project. In its current iteration, when the data warehouse ...
4
votes
3answers
967 views

Calculating the maximum seen so far for each point in time

What I'm trying to calculate is a bit tricky to describe, so please bear with me... I have a table with product numbers, and the price of each product each week over a period of 4 years. Price can ...
0
votes
1answer
881 views

getting error incorrect syntax near @certificate

I have created a certificate and now I want to remove certificate signature and drop the certificate. When I supply the certificate name directly it is working but when I'm making use of local ...
1
vote
0answers
142 views

Converting Query to Pass-through query [closed]

Below query is row source of a form with a list box and some fields for search. the tables are linked in sql server 2008. it works fine. I am trying to convert the query to pass-through query as the ...
2
votes
0answers
1k views

Getting “Read Timed Out” exception frequently for last few days on my already running SQL Server 2008 R2

Our client has a setup of 2 VMs . One of them has SQLServer 2008 R2 and other has a Java utility which I am using to update the Database.. It was working some time back, but recently it started ...
7
votes
1answer
1k views

TRANSACTION ISOLATION LEVEL SNAPSHOT vs. TRUNCATE?

I'm hoping someone can shed some light on this behavior that I was not expecting regarding SNAPSHOT isolation vs. TRUNCATE. Database: Allow Snapshot Isolation = True; Is Read Committed Snapshot On = ...
4
votes
1answer
640 views

Which version of Microsoft Tape Format is supported by which MS SQL Server

When you create a backup with Microsoft Sql Server, the resulting file is in Microsoft Tape Format. Different versions of SQL Server apparently use different version of this format. Which version of ...
2
votes
2answers
9k views

MS SQL Server 2008 R2 network backup

Can MS SQL 2008 R2 backup database to a file on different network location...I've been unable to set network path for backup to other machine on local network via tasks-backup-destination.add ...
0
votes
2answers
57k 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
423 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
218 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
566 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
685 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
25 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
52 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
168 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
703 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
547 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
643 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
7k 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
600 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
940 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
224 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 ...
-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
575 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
3k 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
569 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
22k 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, ...
2
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
852 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
337 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
283 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
821 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
639 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
18k 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
506 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
91 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
256 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
492 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 ...