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

learn more… | top users | synonyms (2)

0
votes
2answers
104 views

Rebuilding index

I have a transaction database and one of nightly job is to rebuild indexes that followed by a log backup and then shrink the log. Is this safe to do so ? Thanks Antony
4
votes
3answers
569 views

Initializing Transactional Replication From two Different Database Backup

I have two different large Database (200GB for each) which I want to replicate their data to a central database (3rd DB). I followed this link and it was very useful for a single database. But my ...
0
votes
1answer
260 views

Geography union via cursor returning null

I need to combine multiple geography records into a single entity to store in a spatial data table, but I've learned that the only available function in MSSQL2008R2 that accomplishes this task is ...
1
vote
0answers
442 views

Sql Execution Plan - Hash Match

I'm currently working on a Sql Query that's having performance issues. I ran the execution plan and saw the Hash Match has 50% cost and also, doing a index scan. I'm starting to learning about the ...
0
votes
1answer
2k views

Query with PIVOT fails with second LEFT OUTER JOIN to same table

I have a big query that contains a PIVOT and several LEFT OUTER JOINS. I need to add a second LEFT OUTER JOIN to a table that is already joined in the query. When I do this the query errors and I'm ...
5
votes
3answers
712 views

Why is there a difference between these transaction DMVs in SQL Server 2008 R2?

When I execute the two queries below, SELECT session_id, transaction_id FROM sys.dm_tran_session_transactions; AND SELECT session_id, request_id, at.transaction_id FROM sys....
4
votes
3answers
2k views

Restrict range of dynamic ports available to SQL Server

Is there a way to limit the range of dynamic ports available to SQL Server from the database side, or database server side? Our SOP is to use static ports across the network firewall and a vendor is ...
10
votes
1answer
2k views

If a transaction is “committed” , is it then saved for sure?

If a transaction is committed successfully, can I then be 100% sure that it has been written to the database AND to the log files? Can I be sure that the data is SAVED ? Today our log files had ...
2
votes
1answer
3k views

select row data as column list

In my database I have a set of custom field values that can be setup by the customer. They can create labels for these fields and then assign values which are linked back to particular tables in the ...
4
votes
2answers
10k views

Cannot use SQLCMD

I have started SQL Server in single user mode like this: c:\....\sqlservr -m When I try to connect to it with SQLCMD -E, I get the following error: Msg 18461, Level 14, State 1, Server SERVERNAME, ...
0
votes
0answers
309 views

Getting Request Timeout from Store Procedure?

Only sometimes, I get Request Time-out(means my SP is not responding within a specfied time-period which is 90 seconds). Here is my SP. ALTER PROCEDURE [dbo].[Insertorupdatedevicecatalog] ...
2
votes
2answers
2k views

Unattended SQL install on a remote machine

My product installation does an unattended install of SQL Server (SQL Server Express by default). I use command line arguments rather than a configuration file, so I'm quite familiar with this process....
3
votes
1answer
1k views

MS Sql Server Management Studio gives error on start

When i try to connect my sa account on sql server management studio it gives me this error: *ps: the error message is turkish but you will see english version in 3rd image Then i clicked on "Get ...
1
vote
1answer
10k views

Error on changing ownership of database from Files Page in database properties dialog box

I've restored a database and created a new user(same user from which I've logged in) as db_owner. Now when I wanted to see database diagram got an error Database Diagram support objects cannot be ...
2
votes
1answer
123 views

Will cleaning data cache help to resolve compiler memory pressure?

I currently experience RESOURCE_SEMAPHORE_QUERY_COMPILE waits causing CPU spike 100% that lasts 10-20 mins and disappears only by completely stopping traffic. I figured it could be a memory pressure ...
1
vote
3answers
1k views

Production (20GB+) Database running slowly and timing out

I know the title thread is a bit ambiguous (and my first post here!) but I am having a very severe production database problem. I am not a DBA, though I know how to work with queries and SQL Manager. ...
1
vote
3answers
528 views

Deny access to one database, allow all the rest

How can I deny access to specific databases from a login? I know that I can walk through and make the login a db_owner on all the ones I want the login to have access to, but how do I do the opposite?...
1
vote
2answers
4k views

Updating an indexed column performance impact in SQL server

I am trying to understand is there any performance impact if I am trying to update a column which is the key column of a non-clustered index in SQL server 2008? For e,g I have a column IsActive in ...
6
votes
1answer
7k views

SQL Server backup fails on disk space error

SQL Server 2008 R2 RTM running on Windows 2008 64bit. Having problems backing up a single database with the following command backup database [somedatabase] to disk = '\\somehost\d$\BACKUP\...
3
votes
1answer
11k views

Stored Procedure which accept xml file as input

<?xml version="1.0"?> <catalog> <book id="bk101"> <author>Gambardella, Matthew</author> <title>XML Developer's Guide</title> <genre>...
1
vote
1answer
975 views

dcexec login failure

I have a SQL Server 2008 R2 instance that I have been trying to get Data Collection setup on. I get everything setup, but there is no data that gets uploaded. I've traced the root of the problem to be ...
2
votes
0answers
602 views

Using Mirroring to upgrade SQL Server 2008 R2 to SQL Server 2012

I am attempting to mirror a SQL Server 2008 R2 database currently in a fail over with the purpose of doing an in place upgrade. I have installed SQL Server 2012 on another machine to serve as my ...
2
votes
2answers
17k views

Options for Data Encryption in SQL Server 2008 R2 Standard Edition?

I'm helping a friend with setting up encryption of data on SQL Server 2008 R2 Standard edition. Upon original research I thought I could use TDE but did not realize that it was only available for ...
1
vote
0answers
790 views

Easy replication for SQL Server: Can it be set up like mongodb or Oracle?

This might look like a noob question... but it isn't. (I have 18 years of SQL Server experience, and three years of MongoDB experience.) The basic scenario is: -- Data Center has big, primary SQL ...
16
votes
1answer
24k views

Upgrade SQL Server 2000 database to 2008 R2 and enable new features

I have recently upgraded SQL Server 2000 database to 2008 R2. What I did was: Shutdown SQL Server 2000 (express) service on old machine, Move datafiles (mydatabase.mdf and mydatabase.ldf) to new ...
0
votes
2answers
5k views

Custom Auto-Generated Sequences with SQL Server [closed]

I have Employee table with Empolyee ID column EmpID (nvarchar(5)) , and I want to make EmpID's value to 00001 00002 00003 00004 .... .... How can I make Custom Auto-Generated ...
3
votes
1answer
215 views

Help with Cybernetics SAN Configuration

There are tons of articles out there recommending RAID/ LUN/ drive configurations for SQL Servers, but most leave me still questioning as they usually state "it depends" and for this reason I'd like ...
3
votes
1answer
2k views

Should snapshot agent continue to run in transactional replication?

We have transactional replication running for a large number of publications on sql server 2008 R2 2 node active/active cluster. I have noticed that the snapshot agent job runs hourly and it looks ...
1
vote
1answer
165 views

Database Tuning Advisor Index Recommendations

How does DTA make an index recommendation? What is the technology behind that? If we can use same on our side then we can shorten the whole process of Tuning recommendation: creating trace file ...
3
votes
3answers
27k views

How to automatically execute a stored procedure every day at a particular time?

Can you please help me how to execute a stored procedure automatically in SQL database without any transaction in SQL? I want to execute on particular fix time.
2
votes
0answers
296 views

Read-Commited Snaphsot isolation causing deadlocks?

Our DBA has some type of process setup to monitor deadlocks and sends out an emails when a deadlocks occurs. We have been testing read committed snapshot isolation on our application for a couple ...
4
votes
1answer
3k views

How does one query a SQL Server Linked Server when SQL Server Authentication is specified?

I have established a linked server and am attempting to query a database to which I have a SQL Server username and password, however I can't find the syntax for specifying the userid/pw in the query ...
0
votes
2answers
490 views

linked server to specific access table

I have built a linked server which links my SQL Server database to an Access DB. Using this object someone is able to see all tables in Access. What I would like to do is to isolate it just to 1 table....
3
votes
1answer
138 views

How can I monitor a production database to see if there are table scans happening?

How can I monitor a production database to see if there are table scans happening? And what indexes might fix them... I'm told that we have adequate indexes but I can't see every code path, and we ...
7
votes
2answers
2k views

Clustered index compression vs table compression - are they the same thing?

If I set compression (either page or row) on the clustered index of a table, is that the same as setting compression on the table? SQL Server provides options for doing both, which suggests that they ...
2
votes
1answer
4k views

View all 32 possible Binary combinations for 5 digits

I have 5 digits A,B,C,D,E Is it possible in SQL to view all the possible Binary combinations for them? (Not the list of 120 possible permutations, just the 32 possible combinations in SQL http://...
5
votes
1answer
5k views

Improve speed of index rebuild on SQL server

I'm importing a large amount of data into an empty database, and before I start I disabled all non-unique non-clustered indexes to see if I could improve the performance of the import. Now I want to ...
2
votes
1answer
1k views

Counting the number of calls of a stored procedure

I have a CLR sp in SQL Server 2008 R2, and I want to count how often it is called, in order to create a statistic on data quality. (The sp allows manual correction of data). How can I let a counter ...
2
votes
2answers
278 views

Create Login command error [closed]

What is wrong here in following screen shot? I am trying solutions posted here and here to add a login
2
votes
2answers
662 views

How to improve performance if index is not Most SELECTIVE?

As per suggestion index must be most selective to improve the performance. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows The ...
4
votes
1answer
2k views

Can non-domain-connected users use Report Builder

I'm dealing with a scenario where SSRS is being provided to remote clients on a different domain. We've discovered that they can launch Report Builder (a ClickOnce application) from their Report ...
2
votes
1answer
4k views

How to find column with all same repeating character

I have a char(15) NOT NULL column. I need to see if it is empty or contains all zeros. I can obviously do= '000000000000000' But is there a way using LIKE so its not hard coded to 15? I don't see why ...
2
votes
3answers
3k views

SQL Server 2008R2 Database refresh in isolated environment

I would greatly appreciate your help on my database "refresh" issue. The scenario: I have 12 databases on a QA server that were restored there from a Production server approx. 2 years ago. Now QAs ...
1
vote
0answers
243 views

Row not being deleted from the publication when it's deleted in a subscription

We've merge replication configured in our environment(1 Publisher, 3 Subscribers) and We know by default merge replication is bi-directional. Its working fine in case of inserts i.e, if a row is ...
2
votes
1answer
7k views

How to trace the exact query fired in sql server

I am facing a problem with performance tuning. We are using an application called ARAS (one of the leading PLM software) which is using Sql server as the back end. Every time we find some data using ...
5
votes
3answers
5k views

Is there any time where we should avoid cascading delete?

In SQL Server 2008, there is a Primary table which is linked to three other child tables by 1 to many relationship. So, we are thinking of using Cascading delete in the primary table, so that all the ...
3
votes
2answers
747 views

Configure Email to be sent on restoring a database

Is there a way to configure Email in SQL Server such that an Email is sent when a particular database is restored.
3
votes
1answer
3k views

Best way to migrate a database involved in bidirectional replication

I have two SQL Server 2005 that are to be migrated to SQL Server 2008 R2. The original pair of servers contain a database that are involved in bidirectional transactional replication between them. In ...
1
vote
1answer
615 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
7
votes
4answers
715 views

Are there any problems with deploying an SQL Server database to a production server by taking a backup?

This is a slightly loaded question in that I have already assumed that the described scenario is wrong. A DBA is deploying an application I have written that includes an MS SQL Server 2008 database. ...