1
vote
1answer
59 views

Backup the whole SQL Server databases in one .bak file - SQL Server 2005

First I asked my question here: Migrate all databases from one SQL Server 2005 to another Many friends replied to it and I gathered lots of useful information. I have another scenario in which we ...
0
votes
1answer
29 views

Sales by month cross tab query using pivot

I am trying to show sales by month using PIVOT in Sql Server 2005. I know it's a common question, but I just can't figure out what I am missing here: select ShippedYear, 1 as Jan, 2 as Feb from ( ...
4
votes
3answers
99 views

Make a Copy of a Microsoft SQL Server 2005 database from a Mirror Copy

I have a SQL mirror database in Microsoft SQL Server 2005. I would like to get a copy of it as a new database so I have the ability to read, write, and backup the database. I cannot break the ...
0
votes
0answers
25 views

Reporting Services Scripter ScheduleID Error

I'm currently in a SQL Server 2005 environment that's being upgraded to 2012. I need a tool to script out all of my SSRS subscriptions, and I chose to use RSScripter. I'm having an issue whenever I ...
0
votes
1answer
155 views

How to restore Network backup to Network Path in SQL Server 2005?

I am trying to restore network backup file to network path.. is there any way to do it? I am using SQL Server 2005 and code.... RESTORE DATABASE DB_NAme FROM DISK = ...
4
votes
1answer
208 views

How to restore symmetric key on the new database?

I have an issue with symmetric key restoring on the another database. I have table in the first(old) database with encrypted column.(The table contains encrypted data) The key in the old database has ...
2
votes
2answers
2k views

Check if all record columns of 2 Tables/Query are equal

In SQL Server 2005, I would like to make one query that checks if all record columns of 2 tables are the same, Example: declare @tbl1 table(col nvarchar(50)) declare @tbl2 table(col nvarchar(50)) ...
5
votes
3answers
3k views

SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012

Yesterday we migrated a SQL Server 2005 database to SQL Server 2012. We made a full backup and restored it on the new server. Problem now is that the database on SQL Server 2012 is much slower than ...
0
votes
0answers
51 views

Database migrated to SQL Server 2005 with 80 compatibiltiy from SQL Server 2000 experiencing locks

I am supporting an application whose code my company did not write, and was originally developed back in 2000-2002, by a company that no longer exists. The code, while not great to maintain, ...
5
votes
2answers
1k views

Find which session is holding which temporary table

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session ...
1
vote
0answers
42 views

Will installing WebMatrix (and Consequently SQL Server CE) Break My Current Installation of SQL Server 2005?

We have a server that has (and needs to keep) SQL Server 2005. We are hosting intranet sites on one server (server 1), but have a database on another server (server 2). One of these sites uses AJAX ...
0
votes
1answer
122 views

Best Isolation Level to avoid deadlocks in Sql Server [duplicate]

I need execute un update statement over an sql server table, this table is used by another process at the same time. because that sometimes deadlocks ocurs. wich Isolation Level do you recomend for L1 ...
0
votes
0answers
89 views

Master Slave Databases and their architecture

We are currently using Sql Server 2005. We currently have separate databases for different instances of our application. The application is a heavy in terms of data and processing. We now want to ...
5
votes
1answer
83 views

Why are zero length spaces being returned from a Union All against an empty table in SQL Server 2005 but not SQL Server 2012

Supporting an existing VB6 app with a SQL Server 2005 back end and we're migrating our database server to SQL Server 2012. Created a backup of the production database and restored it into a dev ...
0
votes
2answers
616 views

c# Backup sql server database, error Operating system error 5(Access is denied.)

I have been trying to backup an sql server database, and I understand that this error is in regards to drive write protection. I am at the end of my rope with this, as nothing that I have found online ...
5
votes
2answers
1k views

Issues with TempDB mdf file ever increasing

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and ...
1
vote
1answer
784 views

'Tempdb' is full warning message in SQL Server 2005

I am using SQL Server 2005. it's a running plant, Everyday large number of data posted into SQL Server 2005 SELECT TOP 5000 [T_idx], [T_TAG], [T_Date] FROM [house].[dbo].[total] ORDER BY T_Date ...
2
votes
1answer
167 views

SQL Server 2005, non-yielding scheduler Spinloop on Snapshot

I was running dbWarden's procedure for CheckFiles on my server. The job never ended and my logs started to fill up with message at a rate of 1 per minute: 2014-01-07 08:40:04.07 Server Process 58:0:0 ...
0
votes
0answers
147 views

How to move SQL server transaction replication from one server to another server [closed]

Currently we have SQL Server 2005 as a publisher and SQL 2008 R2 as subscriber for one of our database (about 350 GB in size). I want to move the replication to our different server (Which also has ...
1
vote
2answers
152 views

How can I run this SQL query across all of my databases at once?

I have a query to let me know basic info on backups. I have several SQL servers that I'd like to be able to query at once. Is it possible to run the following query across several servers at one time? ...
0
votes
0answers
519 views

How to determine if SQL Server Reporting Services database is being used?

I have a SQL 2005 server that needs to be decommissioned. For regular databases, I have a couple scripts that look at sys.dm_db_index_usage_stats to determine last access. My normal process is to make ...
37
votes
3answers
700 views

Huge slowdown to SQL Server query on adding wildcard (or top)

I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not ...
2
votes
1answer
275 views

SQL Server Linked Server error and Query Problem

I am having two servers running SQL2000 and SQL2005. There is a database A,B in SQL2000. Similarly i have the same in A,B in SQL2005. A,B are name of the database. I have a linked server pointing ...
1
vote
3answers
346 views

How to determine if SQL Server will not execute parts of the script in parallel?

Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"? USE UserDB; GO DBCC SHRINKFILE (DataFile1, 5); GO DBCC SHRINKFILE (DataFile2, ...
1
vote
0answers
55 views

Too Many Backup Devices Specified while restoring

I have similar problem like this one http://www.sqlcoffee.com/Troubleshooting023.htm But the issue is i have setup of sql sever 2005 now i am installing same on the client's pc so the main issues is ...
0
votes
2answers
489 views

Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram?

I'm using Window Server 2003 Enterprise, Microsoft SQL Server 2005 Standard Edition. The problem is the SQL Server memory gets stuck at allocating 1.7GB RAM max even though I have 11GB of RAM left. ...
0
votes
1answer
276 views

Return all possible combinations for each group

I have got a table looks like this one here shows the student ID and all classes this student took: +-------------+ | ID | Class | +-------------+ | 01 | A | | 01 | B | | 01 | C | | ...
7
votes
2answers
2k views

Database size - MDF too large?

I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery ...
3
votes
1answer
1k views

PAGELOCK Conversion Deadlock From Update to Intent Exclusive

A customer of ours is experiencing frequent deadlocks. The deadlocks are mostly on the same UPDATE statement. The deadlocks follow a pattern in which both SPIDs have acquired an update (U) lock on a ...
1
vote
2answers
148 views

SQL Server 2005 onwards- Capturing Baseline

I want to design a process by which I can capture the baseline data of multiple SQL Server instances (2005 onwards) in a single database from where I can get all the info and alerts. As of now I ...
2
votes
2answers
207 views

How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped. The table hierarchy is as follows: Order table contains OrderReference(PO), OrderID ...
0
votes
0answers
26 views

How to join multiple tables (SQL) to find the difference between an order and shipped quantities? [duplicate]

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped. The table hierarchy is as follows: Order table contains OrderReference(PO), OrderID ...
-4
votes
0answers
75 views

Multi-row INSERT rejected? [closed]

What's wrong with my query? :( USE tempdb; IF OBJECT_ID('dbo.OrderTotals', 'V') IS NOT NULL DROP VIEW dbo.OrderTotals; IF OBJECT_ID('dbo.OrderDetails', 'U') IS NOT NULL DROP TABLE dbo.OrderDetails; ...
4
votes
1answer
2k views

Need to divide single column into multiple columns

My table has data with a single column: 1 2 3 4 5 6 7 8 9 10 And I want output like this: 1 2 3 4 5-first row 6 7 8 9 10 - second row Another has two columns like this: Column 1 ...
4
votes
1answer
40 views

Verify Query Request Received

I have a software system which queries data via OLEDB from an external SQL Server 2005. The query request is sent every 15 minutes. I want to verify that the SQL Server is receiving the query ...
6
votes
2answers
1k views

Turning OFF CHECK_POLICY by default

We moved from SQL Server 2000 to SQL Server 2005. The client software, which I can not change, creates a user without option CHECK_POLICY = OFF; After creating a user, I have to run the command ...
2
votes
4answers
158 views

When should I use the shrink option

I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
0
votes
3answers
2k views

How to find all positions of a string within another string

How can I find all the positions with patindex in a table or variable? declare @name nvarchar(max) set @name ='ali reza dar yek shabe barani ba yek ' + 'dokhtare khoshkel be disco raft va ali ...
1
vote
2answers
107 views

How to backup databases to ensure data recovery

I have 15 customer databases that have minimum replication at different sites. I currently do nightly back-ups and transfer the database back-up nightly to our main server via 7zip. We recently had ...
2
votes
3answers
82 views

How to take SQL Server database under this scenario?

I have a database of size 290 GB running on SQL Server 2005 (compression is not available) and disk space is z: drive 250 GB and Y: drive is 80 GB. How can I take a full backup in this scenario? How ...
5
votes
1answer
377 views

Unexpected Table Scan with Parameterized LIKE

I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
0
votes
2answers
376 views

Missing data after attaching .mdf file

Recently, one of my customers had a server crash. Unfortunately, the 3th party who was responsible for the backup was 'forgotten' to schedule a backup of the DB (I know, I should have notice that). ...
3
votes
2answers
2k views

Identifying which values do NOT match a table row

I would like to be able to easily check which unique identifiers do not exist in a table, of those supplied in a query. To better explain, here's what I would do now, to check which IDs of the list ...
2
votes
1answer
11k views

IF NOT NULL then UPDATE else keep the value of the field

I think I am somehow close to get it work, but for some reason I still get errors. I have the following UPDATE query: UPDATE DeviceAttribute SET Details = CASE Name WHEN 'Accessories' THEN ...
1
vote
3answers
3k views

How do I determine if a database is the principal in a mirroring setup?

I have two database servers Server1 and Server2, configured with mirroring. A single database, MirrorDB, is mirrored. There is another database on Server1 named OtherDB which is only present on ...
4
votes
2answers
134 views

SQL Server 2005 Database move and rationalisation - Recommended maintenance processes

We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct. I inherited this database and it came to ...
0
votes
1answer
1k views

SQL update set column = @ parameter

doeas anyone knows how to update a table where column = @parameter WHERE SELECT Basically I have a table with the following values. Table name is UserAttribute ID|Name |Properties| ...
1
vote
1answer
69 views

Scheduling a backup in SQL Server 2005

I am trying to schedule back up in SQL Server 2005 with management option. I am unable to find database name while scheduling a backup with full option. Database name is not over there in selected ...
7
votes
2answers
720 views

Different results rebuilding an index online and offline

I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages. When I run ...
4
votes
1answer
79 views

Index Evaluation

I have been doing some index evalaution on a table and this is the first time I have ever looked at how well indexes are performing so and would like some advice on a certain index. The non ...