This tag is for SQL Server 2005, major build version 9.00.xxxx.

learn more… | top users | synonyms (2)

2
votes
0answers
26 views

Can I convert SQL Server 2008 full db to SQL Server 2005?

Recently I converted my SQL Server 2005 database to SQL Server 2008. Now I can't attach that db to SQL Server 2005. I do not have any backup of that db file as well. I have tried to generate scripts ...
1
vote
0answers
24 views

SQL Native backup MIRROR TO performance

We are moving to SQL Server 2014 Expensive Edition Q1 next year. Right now we are on SQL Server 2005 Standard Edition, so we don't have access to the oh-so-wonderful MIRROR TO option for our backups ...
0
votes
1answer
48 views

Is my SQL Job causing the High Disk usage?

I have a concern regarding High Average disk queue length for one of my server with below configuration: Version: SQL 2005 with SP3, standard edition. windows: version 2003 r2 SP2 No Processors= 4 ...
0
votes
0answers
38 views

Physical Table being used a Temp Table in a warehosue. Any advantage?

As I stated in previous posts I've inherited a warehouse based on SQL Server framework. In my continuous review of existing components and the practices adopted by the predecessors I found something ...
1
vote
1answer
42 views

reusing space after deleting lots of rows [duplicate]

I have a database which is 20 GB and it has history information since year 2001. Now I started deleting data prior to year 2008. after completion of deletion how can i reuse the space that is cleared ...
1
vote
1answer
40 views

Mirroring SQL error log - Error: 8474, State: 11

I'm getting these errors in SQL error logs: An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far ...
0
votes
1answer
35 views

Select into existing table where ID is in another table [closed]

I have one table containing ~35k IDs (TableC), one table that features multiple columns (TableA), amongst others the ID from the before mentioned table and a third empty table, featuring the same ...
0
votes
1answer
18 views

Copy every entry from TableA to TableB where the ID is in a textfile containing a list of IDs

I have a text file (or an xls) with a 35k lines, each containing a single ID. I'm required to copy every entry of TableA to TableB where the ID is in said list. Would it be best if I'd create a new ...
2
votes
2answers
65 views

Deleting rows from a table with a very small amount of expansion space

I have multiple tables that I have backed up the data in, and now am deleting the old data for the time, archiving is not an option due to space constraints at this time. I have tried running the ...
3
votes
3answers
108 views

A way to clear/restart virtual memory creep automatically?

I know it's not good practice to do this, but my company cannot afford me trying to find a proper solution to our problem. I've read this and I could do the same with SQL Server 2005, but I was hoping ...
0
votes
1answer
23 views

Removing Components from a backup

Ok, I will start this off by: Yes, I know I should have paid more attention. That being said, I was trying to create a full backup of my DB prior to doing truncation on a number of the tables. When ...
1
vote
1answer
33 views

Linked server in SQL Server 2005 using NT authentication impersonation

I am trying to Link a server using an NT authentication as the impersonated connection, but this connection will be static to a specific user instead of passing the currently logged-in user's ...
3
votes
1answer
66 views

How can I create notification on user's connecting to database?

I have users that have access to several databases. I used to have an alert and notification sent to me when a specific user connects to a specific database, but I accidentally clobbered it. Now I ...
1
vote
0answers
57 views

SQL SERVER 2005 slow Performance on Windows Server 2008

Recently i have upgraded my server to new one. But because of compatibility issues with my old Windows Server 2003 R2 32 bit, I have also upgraded it to Windows Server 2008 R2 64 bit. (i have ...
3
votes
1answer
60 views

Discrepancy between system catalog view and sp_change_users_login?

I want to check for orphaned users on a database. To that end, I use the following query on my SQL Server 2005 instance to get all users and their accompanying logins for a specific database (say ...
2
votes
2answers
73 views

Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

We are running into an issue where if we look at the avg row size (0.2kb) x number of rows (200) inserted in one of our table, the size should grow by around 40 KB. However the total used space has ...
-1
votes
1answer
96 views

EXCEPT operator vs NOT IN

The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ? Does it do the same? I would like a simple explanation with an example.
0
votes
1answer
35 views

Move/Copy unique entries only from TableA to TableB, if it's not in the destination table

I'm required to move every unique entry from TableA to TableB, it the unique entry from TableA is not already in TableB. Both tables look the same, they don't have any keys, but the combination of to ...
1
vote
2answers
45 views

Delete duplicates with specific columns as identifier

I'm required to delete all duplicate entries of a table. The duplicates are identified by two non-key values num (int) and com (varbinary(512)).. My amateur-ish SQLfoo lead me to this non-functioning ...
1
vote
2answers
40 views

Granting permission on a particular job to execute on SQL Server 2005

An user is requesting permission on a particular job. How can I give him access on job activity monitor which shows only that requested job?
2
votes
0answers
57 views

Quirk update for cumulative sum of chances for a raffle. What can go wrong?

I need to make a raffle where users have different chances. | USER | CHANCES | |------|---------| | A | 10 | | B | 25 | | Z | 15 | Looking in this answer: drawing a riffle ...
0
votes
1answer
43 views

Where's my missing Sql Server Express 2005 Database space? Size and space available dont add up to 4G?

I recently deleted some data from a table that was not needed and growing to too big a size with this query which provided to me by a support tech... declare @mdate datetime set ...
0
votes
1answer
37 views

Data Files Outgrowing Hardware - Split the File?

Where I'm currently working, the primary business database is located on a virtual server (SQL Server 2005 standard). The temp DB and logs are on one disk, the database itself on another. The SAN is ...
-1
votes
1answer
23 views

Foreign keys can't be added because they exist but they don't exist!

I have a conundrum. I am re-engineering a sqlserver 2005 database, and in the course of doing so have attempted to correct a simple lookup table flaw between tblUsers, tblUserRoles, and tblRoles ...
1
vote
3answers
117 views

Select only rows with minimum values from union table

SQL Server 2005 Here is a sampling of the data from a union of two tables (named "vProducts_All"): DBID UPC Price1 Price2 1 023614025559 689.0000 649.0000 2 ...
1
vote
1answer
44 views

Join select queries on an id in SQL Server

I have three tables that SELECT from and I'd need to join the results. It feels like this should be a rather easy task but I struggle with the syntax. Working on Microsoft SQL Server Enterprise ...
1
vote
3answers
88 views

Clustered Index improvemens for particular queries

I found a table that looks like this: CREATE TABLE [dbo].Table1 ( id INT primary key IDENTITY (1, 1), [idUser] INT NOT NULL , [Amount] INT NOT NULL , [Attempts] INT NOT NULL , ...
3
votes
4answers
279 views

Keep track of time intensive stored procedure with prints

I am no database admin nor is my SQL-foo any good (yet), but have a few tasks to get done in MSSQL, and one is a rather time intensive procedure where I fetch from a CURSOR and WHILE @@FETCH_STATUS = ...
0
votes
1answer
67 views

Store the resulting list of a SELECT in a variable to count AND use the result

I have stored procedure that uses a cursor and for every fetch a duplicate SELECT, once to determine the number of IDs found (SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var) and if the ...
1
vote
0answers
62 views

Can you speed up this query to aggregate millions of rows?

We store our website traffic data in SQL Server 2005, like so: | Date | IPAddress | GalleryUserID | ThumbView | LargeView | ZoomView | | 2014-11-11 | 192.168.0.1 | 22 | true ...
0
votes
1answer
52 views

How to know the Database user Login History from begining? [closed]

I need to know one particular user's login and logout database history from beginning in SQL SERVER 2005. Can anyone help?
-1
votes
1answer
40 views

SQL Server edition support [closed]

Does anyone know whether Microsoft supports SQL Server 2005 Enterprise Edition SP2 anymore? The following is from reported from @@version Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 ...
1
vote
0answers
36 views

Increasing 4 vCPUs to 8 vCPUs [closed]

Can anyone share their experience if they encountered any issues trying to provision extra CPUs for SQL Server running on VM ?
1
vote
1answer
57 views

Refactoring sql for tables with similar columns but different table names

I have the task of re-factoring a SQL script that performs a lot of unions from different tables. These tables have similar information, so the script queries the same columns as in each table, except ...
1
vote
2answers
121 views

Create/Alter stored procedure without compiling

Is it possible to create or alter a stored procedure in SQL Server 2005* that doesn't compile the procedure? (* - yes, unfortunately, we still need to support 2005 as our minimum standard) I have a ...
2
votes
1answer
84 views

Why am I getting cardinality issues when querying a single table

I have the following query: SELECT [iri].[RateItemKey], [iri].[RateItemType] FROM [prism72].[dbo].[ispRateItem] AS [iri] WHERE [iri].[Status] = 1 AND ( ...
0
votes
0answers
42 views

select random tuples but prefer old dates

With this table: CREATE TABLE T_TEST ( id int identity, somevalue varchar(256), lastupdate smalldatetime ); i want to select n random tuples which age is older than x days but prefer old tuples ...
1
vote
1answer
59 views

Delete from table where two of the three keys in another select

I have tableA which has 4 columns: IDA (bigint), IDB (int), IDC (varchar(255)) and comment (varchar(255)). The primary key is (IDA, IDB, IDC). I'm required to delete the records where the count of ...
1
vote
1answer
50 views

Set bit in Table A if values are present in Table B

I have two tables, Containing up to ~10 million entries. TableA which has 3 columns: IDA (bigint), IDB (int) and CHK (bit). (IDA, IDB) is the primary key. TableB which has 2 columns: IDA (bigint), ...
2
votes
0answers
46 views

SQL Server migration from 2005/2008 to 2012 [duplicate]

We need to upgrade two SQL Servers for our projects to SQL Server 2012. Since we’re not db-admins or familiar with such migrations, I wanted to ask you if you could tell me what we have to be aware of ...
0
votes
0answers
69 views

Problem installing SQL Server Standard Edition (9.0.1399.06) on Windows 8.1

I need to install SQL Server 2005 Standard on Windows 8.1 for specific software, I have installed SQL Server 2005 Express Edition SP4 (9.0.5000) in the same O.S. without problems and running fine, but ...
2
votes
1answer
43 views

Restore database with different filename

I have to restore a new back up file daily (as an agent job, after first deleting the existing db) but the file name will vary as it will include the timestamp as well. How can I achieve this so ...
1
vote
1answer
61 views

The query processor could not produce a query plan

I'm getting this message: Msg 8624, Level 16, State 17, Line 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support ...
1
vote
1answer
43 views

Difference in architecture between SQL Server 2005 and SQL Server 2012 [closed]

I've been asked to identify the architecture differences between Microsoft SQL Server 2005 and 2012. I've been pointed out to http://technet.microsoft.com/en-us/library/bb545450.aspx but I haven't ...
0
votes
1answer
36 views

moving all databases from SQL 2005 EXPRESS SP2 to SQL2005 Enterprise SP4

Thank you for such a great forums on web. I'm not a DBA specialist and my knowledge about SQL server is low so this simple issue is getting challenging for me. We have an old SQL Server express 2005 ...
1
vote
1answer
92 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
30 views

Possible to CREATE EVENT NOTIFICATION with a different owner?

We are in the process of removing a previous dba login and he owns all the endpoints and event notification objects. Endpoints were easy to change; Event notification objects not so much. I found ...
0
votes
1answer
36 views

Count SQL Query

Hi all i'm just wondering if anyone could suggest the easiest way to write this query. The problem with writing the subquery HomePageHits this way is that is bring the same records for the Hits and ...
1
vote
1answer
205 views

How to reclaim the unused Space in a database

The database is almost of size 55GB. This database should have never grown to this size as some of the maintenance jobs were not running. I have deleted almost 30GB of data. Now the database size is ...
2
votes
1answer
71 views

XML Whitespaces/Unwanted Characters

Could anyone help on how to remove white spaces from an XML file? I used the following code that i got from Mikael Eriksson on Stack Exchange to extract the site id: cast(msg as ...