This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (1)

1
vote
4answers
103 views

Backup from Server1 and restore it to Server2?

IF we have two Servers installed with SQL Server 2008 instances, Server1 has three drives C, D and E with 100, 500 and 500 GB respectively and Server2 has three drives C, D and E with 100, 300 and ...
3
votes
2answers
602 views

Are there advantages to using temporary tables over derived tables in SQL Server?

I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why? I must do queries with large data (millions records) and I ...
1
vote
2answers
177 views

Move a huge databases from one SQL Server to another

I have customers databases located on my SQL Server 2008 machine. The size of these databases are around 300 GB. I would like to move them all to another SQL Server 2008. Is there a way to shrink ...
3
votes
1answer
81 views

Simple method to setup SQL Server for development access

I've created a virtual development box and installed SQL Server 2008 with mixed authentication. Then I connect to SQL Server via SQL Server Management Studio using my Windows account but when I try to ...
3
votes
1answer
558 views

sql server service broker queue pass parameter to stored procedure

Stored Procedure Create PROCEDURE [dbo].[InitServiceLog] ( @name varchar(max)=null ) AS BEGIN SET NOCOUNT ON; insert into [AdventureWorks].[dbo].[ServiceBroker_Log](Service) ...
1
vote
2answers
380 views

Convert Login from Windows Authentication to SQL Server authentication?

I have an account that is setup for Windows authentication. The account is used by SSRS for running reports and has various permissions in multiple databases. I need to convert this account to use SQL ...
0
votes
2answers
262 views

Log shipping - switchover and failback

We are doing a planned failover and switch back from the primary to DR with mirroring. We do not have a separate monitor instance. Please help me with the following 1. Is it possible to switch back ...
2
votes
0answers
87 views

Maintaining Referential Integrity with a Filestream enabled database

We have a production database which is filestream enabled and has grown, within a year, to about 20Gb as it stores documents. On many occasions we need to bring the live data back to UAT or Dev ...
1
vote
2answers
132 views

SQL Server Linked Servers and Remote Statistics

I'm trying to get a better understanding of how SQL Server uses statistics on remote SQL Servers (e.g. a Linked Server). I understand that the user needs db_owner/db_ddladmin rights to be able to use ...
1
vote
3answers
3k views

How to calculate exact month (in days) difference b/w two dates

I am trying to get all the rows where the visit date was exact before five months from to date but somehow DateDiff function is including 26th Sept 2013 record which is not right as five month has ...
0
votes
1answer
49 views

An SQL Server 2008 R2 based program fails when updating stored procedures?

I'm trying to update MIP software and one of the "companies" fails while it is updating a stored procedure. Is there any place in SQL Server 2008 R2 / Windows where I could see an error message ...
1
vote
3answers
1k 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 ...
-2
votes
1answer
55 views

can exec work with while loop of cursor? [duplicate]

Declare @var varchar(200) set @var='select max(val) from floattable' declare c1 cursor for select id from floattable declare @val int Fetch next from c1 into @val open c1 while @@Fetch_Status=0 ...
2
votes
0answers
256 views

SSRS slow opening a Datasource

While optimizing a report, I discovered that the time reported by TimeDataRetrieval in ExecutionLog2 was a couple of seconds longer than the query times shown in SQL Server Profiler for that report ...
0
votes
1answer
113 views

T-SQL Optimizing a Join on TOP Value from another table

I've got a data warehouse that goes through a full refresh each night that can take about an hour to process 16 million rows/25gigs of data and we're looking for ways to reduce this time without going ...
0
votes
0answers
21 views

SQL Server 2008 server cannot connect to itself with alias name or “localhost” [duplicate]

We have a SQL Server 2008 ENT 64bit SP3 server running Server 2008 R2 Standard SP1 64-bit. This is also a windows/sql clustered environment. There is the cluster alias for the server, and then we have ...
1
vote
1answer
2k views

SQL Server 2008 server cannot connect to itself with alias name or “localhost”

We have a SQL Server 2008 ENT 64bit SP3 server running Server 2008 R2 Standard SP1 64-bit. This is also a windows/sql clustered environment. There is the cluster alias for the server, and then we ...
1
vote
1answer
338 views

Does MAXDOP affect the number of tempdb data files I should create?

From what I have read, best practices for configuring tempdb includes creating multiple data files. The rule of thumb as I understand it is if you should create the same number of data files as you ...
2
votes
2answers
255 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 ...
1
vote
0answers
52 views

SQL Server Full-Text Index with Stoplist

I've searched this site and the internet in general and can't find any information which answers my question, so I thought I'd post it here. I have Full Text search operating on a database in SQL ...
0
votes
0answers
44 views

dbcc shrinkfile not reducing size of log file in sql server 2008

I am shrinking log file of SQL Server 2008 R2 database, but it is not working. I was using this code: ALTER DATABASE [database] SET RECOVERY SIMPLE DBCC SHRINKFILE(DATABASE_LOG, 20) ALTER DATABASE ...
1
vote
1answer
653 views

Best Format to Bulk INSERT/UPDATE Data to SQL SERVER using C# [closed]

Can anyone tell me what is best way to bulk insert/update data to SQL SERVER 2012 using C#? I have an excel file which I am passing as TVP(table value parameter) to a SP. But this is little bit ...
2
votes
1answer
86 views

Restoring Differential Backups

Is it possible to restore a differential backup separately? The production server runs the following every sunday DECLARE @Pathname VARCHAR(512) = 'N:\Backup\Dir\DBName_' + (select ...
7
votes
1answer
163 views

Necessary to include clustered index columns in non-clustered indexes?

Considering that a non-clustered index is based on the clustered index, would it be necessary for the non-clustered index to list any of the columns contained in the clustered index? In other words ...
2
votes
1answer
70 views

Database Design guidance required

I have scenario where there is Table T with columns PK,A, B, C,D TABLE t ( PK, A, B, C, D ) i have to insert data from different sources ie columns A,B,C,D are calculated separately.. and they same ...
3
votes
1answer
145 views

How can a blocking_session_id be negative?

I have a case where a query I'm running is taking a long time. When I checked sys.dm_exec_requests the blocking_session_id is a negative value, -2 to be specific. The wait is a LCK_M_X and the ...
1
vote
1answer
54 views

1 time shrink of abused databse or restore from backup [duplicate]

I have a database that I have inherited which has been left for a long time to grow and grow. 95% of this growth is caused by 1 log table used by our application. The database is 120GB and in full ...
1
vote
1answer
106 views

SSIS v/s Replication v/s logshipping

I have 2 SQL server instances, on different servers. One is Production instance and gets lots of reads and writes daily. Another one is for external applications to use a copy of few of the tables ...
0
votes
1answer
58 views

Sql Server Full Backup Duration Increased Dramatically

since last week My Full Backup Increased dramatically .before last week the time is about 3 hour and now the time is about 6 hour.in addition some query take long time to run. (not all the query).I ...
0
votes
1answer
216 views

Installation SQL Server 2012 - “Ready to Install” page missing

I need to create config file, so I could install sql server from cmd. I need basic server installation so I've downloaded SQLEXPR32_x86_ENU I need to go through whole installation process til page ...
0
votes
2answers
14 views

One of the Database Ownerhas been Removed and corresponding property changes in sql server

A database has been created on SQL 2008 by a user named user1 ,on dec 2013.Today ,we are going to remove that user(/owner) from the server.What will be the owner property of the database show then?
0
votes
1answer
38 views

Does a filtered index improve searching on the opposite condition?

Does having a filtered index on a column improve queries with a WHERE clause that is the opposite of the filter condition? Since the filtered index specifies what not to return, does it reduce the ...
0
votes
1answer
286 views

Managing SQL Server 2008 R2 transaction logs when using principal/mirror databases [duplicate]

My system has the main database as a principal (reads/writes only to that database) and then a mirror database on a different site that receives all database updates from the principal database. This ...
1
vote
2answers
152 views

Improving a search query

I have a query for search function. Basically search function allowed user to define what they "have" and "want". Then this query will filter out all the possible result which created by other user. ...
1
vote
2answers
301 views

Need advice on abnormal login auditing

Recently i am facing some slow issue for my web application. And i have check my visitor report and it shows normal traffic rate as usual. And then i check my db login auditing and find below login ...
1
vote
0answers
814 views

The memory usage of SQL Server keeps growing

We have a production SQL Server 2008r2 x64. 16GB RAM and this server is used by the application (hosted) and SQL Server. The max memory setting = 6GB The min memory setting = 128MB. There are ~25k ...
0
votes
1answer
113 views

Scheduled copy of large table from SQL Server 2000 to 2008

I have a table on SQL Server 2000 with 10 million records and 20 columns. The table is rebuilt once a day and is essentially a reporting table, not a transactional table. Of the 10 million records, ...
1
vote
1answer
87 views

How do I find out if Sql server Agent's login has rights to a DB?

How do I find out if the SQL server Agent's login has rights to the target DB, that is the DB on which I will be executing a query ? Also, how do I find the service that's controlling Agent on the ...
4
votes
2answers
209 views

Why shouldn't a pre-hashed password be used with CREATE LOGIN all the time?

(For the purpose of this question, assume that I only need to support SQL Server 2008 and higher. Or to future-proof this question, let's say recent, current, and near-future versions.) The ...
1
vote
2answers
51 views

what's result for sql server log backup twice? [closed]

If I have a sql server database mydb. Then I run a transaction log backup at 12:00pm. Then run the transaction log backup(either full or differential) again at 12:30pm. what's result for the second ...
3
votes
1answer
1k views

Downgrade database from SQL Server 2012 to 2008 [duplicate]

Hi guys got a problem with a database. I've been asked to perform some indexing and delete queries on a live database, so first of I wanted to get the database to our dev server so I can test out ...
0
votes
0answers
528 views

SQL Server to MySQL sync, using Workbench, FreeTDS, and iODBC on 10.9

I've been struggling to get the MySQL Workbench to behave properly to complete the following: With Mavericks (OS X 10.9), connect to a SQL Server and create a migration job to a MySQL server. The ...
2
votes
1answer
69 views

What is happening with locks in my database?

I'm having some problems on my database. At any given time, I'm trying to execute an update with five records on my table, but this table is locked and I get a time out executing the query. I ...
3
votes
1answer
87 views

Suppressing the SSMS prompt to save changes

Is it possible to configure SQL Server Management Studio (SSMS). so that the dialog box shown above does not appear when I close an unsaved query window?
0
votes
0answers
70 views

Detecting changes in a table without enabling CDC [closed]

We are doing a transaction based on a weekly basis, where the first transaction is recorded on 1-Jan-14 in the table a. Subsequently, the second transaction that was done on 8-Jan-14 also got ...
2
votes
1answer
1k views

Using a linked server with OPENQUERY in a database project

I have a SQL Server 2008 running a database I want to throw in TFS. Therefore I used a Visual Studio 2013 database project where I imported the DB. After fixing a bunch of errors I'm stuck with only ...
2
votes
2answers
105 views

Similar Products with bitwise

I have the following tables. So one product can have one category, one category can have n genres.(Products-> Genre Column data 6 ( 2 + 4), 132 (128 + 4) like that). So my question is; i want to ...
2
votes
3answers
483 views

how to dump MS-SQL DB using PHP with MS-SQL-SRV.? [closed]

I had created a MySQL database dump in php successfully using this below code. $dbuser='root'; $dbname='Student'; $dbtable='StudentDetails'; $dbhost='localhost'; $dbpass='root'; ...
0
votes
0answers
25 views

Indexing and Deadlock in SQL Server 2008? [duplicate]

I am running into a deadlock in SQL Server 2008. Using profiler I was able to determine that the deadlock happens as I am trying to select and update row(s) in table in 2 different sessions. The ...
5
votes
2answers
197 views

Any relation between SQL Server Service Name and Instance Name?

My SQL Server's instance name is SQLEXPRESS and SQL Server Service Name looks like MSSQL$SQLEXPRESS. Is there any relation between instance name and service name? I'm trying to check SQL Server ...