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

learn more… | top users | synonyms (2)

0
votes
1answer
18 views

Import (flat file) into SQL Server

I've seen a couple of questions about the subject backfiring so I'll try to be specific about my concern... I have an old (MS Access) frontend I'm trying to rebuild and I left the part that troubles ...
1
vote
0answers
14 views

Problem connecting to SSRS reporting services - SQL Server 2014 to SSRS 2012

When I try to connect to my reporting services server (SQL Server 2005) from my local machine, SQL Server 2014 I get the error message below Invalid Namespace (System.Management) But when I ...
0
votes
1answer
17 views

How to Create Trigger Insert with a SELECT LEFT OUTER JOIN

I want this trigger to be fired after an insert is made with the text, this is what I have so far ALTER TRIGGER [xxx] ON [dbo].[Main] AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT ...
2
votes
1answer
67 views

Single row update using primary key affects many rows

I'm doing a very simple datetime update in a table using its primary key: update TABLE set END_DATE = '2015-10-14 00:00:00' where ID = 1165027; I received the message (1 row(s) affected) 183 ...
3
votes
2answers
53 views

Is is possible to recover a database from the 'raw' files (.mdf and .ldf) of the old server to a new SQL Server instance on a new server?

I have a client that has POS software called Restaurant Pro Express (RPE) from www.pcamerica.com. RPE uses a SQL Server database. Their old POS server had a hardware failure, but I was able to attach ...
5
votes
2answers
168 views

SQL Server log file used pecentage does not drop after multiple backups

SQL Server 2005 I ran "dbcc SQLPerf(logspace)", then took two log backups, and ran "dbcc SQLPerf(logspace)" again. No change from 48.55% used, even though "dbcc opentran (foo)" says that there are no ...
3
votes
1answer
121 views

Enabling Constraint Silently Fails

Why would SQL Server silently fail to re-enable a foreign key constraint? I recently attempted to re-enable a number of constraints using ALTER TABLE. Most enabled successfully; however, minority of ...
0
votes
1answer
17 views

all rows from left table with sum of amount from right table

I'm trying to return a list of customers and the sum of their transactions in the last week, but have the query return customers who didn't wager at all. I am trying to do this with a left join, but ...
0
votes
1answer
29 views

SQL server data copy bug? [closed]

I have one query who shows some data, and those data I'm manually copying to Table1. The query shows the numeric type and it is in this form Query +---------------+ | status ...
2
votes
0answers
32 views

Join duplicates on T-sql [closed]

Background: This is largely a learning exercise. I am working on a task scheduling program. Part of the program requires making a report to print out. I am working with the original version of the ...
1
vote
1answer
46 views

microsoft sql server schema [closed]

From blog.sqlauthority.com: Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such ...
0
votes
0answers
22 views

Converting Legacy SQL Server 2005 Stored Procedure to Transaction based for parallelism [closed]

My task is to convert a legacy stored procedure written in SQL Server 2005 so that it can be used in parallel (multiple executions from different users). As of now the stored procedure is designed ...
2
votes
1answer
83 views

Tracking down unused Views and Stored Procedures

I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends. This database needs a lot of love and attention which I am attempting to give it. There are lots of stored ...
0
votes
0answers
23 views

SYNTAX ERROR: delete from multiple tables with inner join

Sorry, but I need syntax help :( This needs to run on SQL Server 2005 + DELETE SO, ASO, ASOV, AOV FROM SYS_OPTION SO INNER JOIN ADM_SYS_OPTION ASO on SO.OPTION_ID = ASO.OPTION_ID INNER JOIN ...
-3
votes
1answer
48 views

SQL Server questions (for masters) [closed]

How to rename sa to new_sa in SQL Server 2000? How to do and what is the impact SA rename impact in SQL Server 2005, 2008, 2008R2, 2012, 2014, Express Edition How to recreate sa login if deleted How ...
4
votes
1answer
56 views

What started this SQL Server job?

I'm looking at a SQL Server environment in which jobs start in several different ways. One way is from a job running on a different SQL Server. So, for example, "SQL Server A" runs a job named ...
1
vote
1answer
87 views

Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060

I have a scenario where some inserts/updates are failing due to maximum row size of 8060 limitation. I have a table with lots of varchar 250 to 500 columns, numerics, datetime columns. I was trying to ...
1
vote
0answers
34 views

What transaction logs are for

I'm confused about tranasaction logs for SQL Server. I was under the impression they can be used to roll the database forward. For example say I made a software release on Sunday, I added three new ...
2
votes
0answers
73 views

No significant performance increase after migrating from SQL Server 2005 32bit 4 Cores 4 GB RAM to SQL Server 2012 64bit 4 Cores 32 GB RAM [closed]

We run regularly a series of data analysis scripts on one of our databases. The MDF File of the database is several gigabytes big, the scripts perform not only selects, but also inserts, updates and ...
2
votes
1answer
104 views

Get employee names on the basis of Date

I want a dynamic query which will fetch the employee name who has completed successfully 6 months and are ready for confirmation. I have written a hardcode query does that. Here is the query SELECT ...
1
vote
2answers
40 views

Role to view SQL server job log

We have a monitoring tool that can check if Microsoft SQL Server jobs have run successfully. What I want to do is create a user that has as little permission as possible but it must be able to watch ...
1
vote
2answers
54 views

Understanding the usage of version store in tempdb

We are trying to analyze the growth of tempdb on one of our SQL server 2005 Ent SP4. I know tempdb would need the space depending upon the transactions, and how queries are designed to use it. We ...
1
vote
1answer
27 views

Import .bak into SQL Server 2008 Management Studio

I have a custom program that uses a database. I have little knowledge of servers but the way it works is by using a "local" server in the same computer as the one where the program is installed. I am ...
7
votes
1answer
79 views

Error in partition switching in Sql Server 2014 when migrating a database from Sql Server 2005

I am migrating an old database from SQL Server 2005 to SQL Server 2014. This database has a partitioned table; a scheduled job periodically moves old data from the "online" table to the the historical ...
2
votes
0answers
52 views

Why does SQL Server 2005 only use an IS lock in this scenario?

I am attempting to examine the locking behind different queries but with the same output to avoid deadlocking situations. I have the following code: SELECT MIN(ID), TextData FROM PKKEYTESTER ...
19
votes
2answers
260 views

Optimization issue with user defined function

I have a problem understanding why SQL server decides to call user defined function for every value in the table even though only one row should be fetched. The actual SQL is a lot more complex, but I ...
1
vote
4answers
116 views

How to select first row from a join that returns multple rows on the primary key

This is related to this question: Joining multiple tables results in duplicate rows I have two tables that I am joining. They share a key. The person table has one name per primary key but the ...
4
votes
1answer
75 views

Restoring database without worring about partitions

On our our production database there is a partitioned table called FullLog. This is updated via triggers so we know who changed the database and when. Which is a problem for me when I restore the ...
1
vote
1answer
45 views

Duplicate records referenced across different databases

I have a table holding 'People' data (about 70K records) that coexists with an 'Address' table so there's one AddressID per every person in the 'People' table. The idea was to centralize the data in ...
3
votes
2answers
110 views

Online index rebuild snapshot location and impact of filling index drive

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index. My question is where does the snapshot index ...
-1
votes
1answer
133 views

Unable to start SQL Server Agent

We are unable to start SQL server Agent and it fails with below error: SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but ...
1
vote
1answer
40 views

How to check what type of lock an UPDATE statement uses?

How can I determine if an update statement puts a table lock, page lock, or row lock on a table?
1
vote
1answer
254 views

Joining multiple tables results in duplicate rows

I am running SQL 2005. I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. Here is my query: There are multiple tables that have ...
4
votes
1answer
136 views

NUMA Nodes - MAXDOP - PLE

We have a server with 8 CPUS across 2 NUMAs with hyperthreading enabled. Currently Maxdop is set to 8, but actually should be set to 4 as per the Maxdop section of this article: ...
0
votes
0answers
29 views

Trigger copies history table: Will removing index lock options speed up the process?

I have a trigger applied to a table to write inserts and updates to a history table. That history table can grow pretty fast, and we want to see, if we can speed up insert operation a bit. The values ...
0
votes
2answers
59 views

Unable to delete Log Shipping configuration

I need some help with my SQL 2005 server. I am trying to setup log shipping, I've successfully done so for all user-databases, except one. From the looks of it, it once had log shipping configured ...
0
votes
2answers
79 views

How to create a VIEW in SQL Server [closed]

I have a user table and I need to create a VIEW so a user can only see their own data/row. How could I achieve this? I have been searching online but I can't find anything useful User with user_id, ...
1
vote
1answer
89 views

LSCopy job fails on SQL Server 2005 - Insufficient space

I'm having some trouble getting my LSCopy job of Log-Shipping on my SQL Server 2005 server, to work. The LSBackup job is configured so the backup is made locally (D: drive) The LSCopy job is ...
1
vote
1answer
55 views

How to confirm if the backups are going to tape in SQL Server 2005 and 2008 R2

We have many of our SQL Servers where we take the backup locally on the server as well as over the network to other server in case of disaster. Also, we have a process set that server support team ...
0
votes
1answer
131 views

Cannot select features to install from SQL Server 2005 SP4 installation process [closed]

The issue: I cannot select MSSQLSERVER from the feature list I want to update as part of the SQL Server 2005 x64 Service Pack 4 installation process. Please note exe file is being raised from C:\ ...
1
vote
0answers
145 views

SQL Server 2005 Cluster Service pack 4 upgrade failure at remote user account

I built SQL Server 2005 ENT Cluster on Windows Server 2008 R2. I need to upgrade it to SQL Server 2005 SP4. However, I get this error at the Remote User Account screen Enter the remote user ...
-4
votes
1answer
836 views

Unable to open SQL Server database file (.mdf) [closed]

I have a SQL Server 2005 .mdf file that I am trying to open in SQL Server Management Studio so I can add a field to a table, but I am unable to open the file. I can't say that I really understand how ...
0
votes
2answers
39 views

Shall i upgrade Database server from 2005 to 2012 without upgrading SSRS or web Application

Shall i upgrade Database server from 2005 to 2012 without upgrading SSRS or web Application relying upon the database? Will the SSRS reports and Web application continue to work? Would be appreciated ...
0
votes
0answers
49 views

mdf files too big in SQL Server 2005 [closed]

I hope someone can advise me on what to do here. I am running my database on SQL Server 2005. I had created a backup maintenance job myself using the SQL Server Management Studio, when using a ...
2
votes
0answers
44 views

SQL Server 2005 Datawarehouse DB - Integrity failure caused by NonClustered indexes

Last year I've been working with a data warehouse database (SQL Server 2005) loaded on a daily basis during the nights. Overall database performance is acceptable. However, there is a "known error" ...
1
vote
0answers
131 views

Replication slowness: massive MSrepl_commands table?

Background: I have a SQL Server 2005 transactional replication setup that pushes normal traffic fine, but occasional larger processing batches have been a problem. Obviously the changes take longer ...
0
votes
2answers
63 views

SQL Server 2005 query with multiple conditions, some indexed, some not

I have a table with 50 million rows. It is indexed on zip. A query Select * from table where zip = '12345' returns 20,000 rows in about 2 seconds When I add an additional condition it jumps to ...
0
votes
1answer
41 views

Restricting multiple records to one by preference?

Below is a query I'm using to extract the best Price1 amongst several distributor's pricing tables. SELECT products.UPC, T2.Price1, vProducts_PriceQty_Union.Price2, ...
5
votes
1answer
249 views

Get specific result without using loop

I've a table in which data is like this for single user ID - Number - SubNumber - Name 1 101 201101 Jack 2 101 201102 Jack 3 101 201103 Jack 4 101 ...
3
votes
1answer
128 views

Unable to start SQL Server Browser service in Sql server 2005 [closed]

we are unable to start the SQL browser service on one of our machine with below error: I believe this to be the reason, why we could not connect to the instance with below error: I tried ...