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

learn more… | top users | synonyms (2)

8
votes
1answer
53k views

ODBC Data Source SQL Server Connection - Login Failed For User

I have moved my database from an SQL 2005 to a server with SQL 2008. I am now trying to create an ODBC Data Source. I am using "With SQL Server authentication using a login ID and password ...
1
vote
2answers
70 views

How do I prevent my clients viewing my database definition when it is installed on their server?

I want to give my database to the client to install on their server. However, I want to deny the client rights to see the tables and stored procedures and also want to restrict that database to run ...
1
vote
1answer
78 views

SQL logins Audit for SQL Server 2005/2008

For the Security Audit of SQL servers 2005/8 R2, We are looking for a method which can list us SQL accounts which have not changed password for last X days. For a single login we can do it via ...
4
votes
3answers
758 views

Table structure for many custom profiles

I'm currently developing an bulk-email campaign manager that will allow customers to upload as much information as they would like on their subscribers. For example, I have a basic profile that is ...
7
votes
2answers
2k views

Displaying query plans in Server Management Studio

Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset: select top 10 sum(qs.total_worker_time) as total_cpu_time, ...
6
votes
1answer
135 views

Why and how sometimes I get the “absent table error” while there are definitely Sch-M lock?

I have a synthetic test, which reproduces some error we have on the production environment. Here are the 2 scripts to reproduce it: 1st DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS; SET NOCOUNT ON; ...
1
vote
2answers
433 views

Detecting and fixing database corruption on SQL Server mirrored database

I’m new to my current company and we have a System Administrator but no DBA. In the past I’ve taken care of DBA related issues at other companies. I’ve run into and corrected database corruption in ...
1
vote
1answer
86 views

Manage multiple short-term tables with same schema or use single table for short-term data?

My team has proposed to store session state data for each open client session in a table in the database. The application server creates a new table for each client session as the user logs in via ...
1
vote
1answer
92 views

Need to query records based on Date only (Sample Query given)

sSQL = " SELECT ordID, ordName, ordDate, ordShipping, ordStateTax, ordHandling, ordTotal, ordDiscount, ordPrivateStatus FROM orders WHERE (ordStatus = 7) " I want to display orders that ...
4
votes
3answers
593 views

sp_send_dbmail fails when mailing queries, but no error returned, query just hangs

I have an issue SQL Server 2005. sp_send_dbmail fails when mailing out with the result of a query (inline or attatchment). No error message is given / reported, the query just keeps executing but ...
0
votes
1answer
152 views

Add linked server from SQL-Server to MySQL: “Data source name not found” (Error:7303)

I'm trying to link my SQL-Server 2005 on Windows Server 2008 R2 with MySQL 5.5.40 on a different computer. I have followed this guide: ...
0
votes
1answer
69 views

How do I load this SAS text file to SQL Server 2005

Every 3 months, I have to load several fixed-width text files into our SQL Server 2005 database. They can have anywhere from 5 to 798 columns (seriously) and the columns change pretty regularly. ...
3
votes
1answer
102 views

Impact of changing char(10) to varchar(12) on massive table

I have a table in a database in Microsoft SQL Server 2005 server that is 16gb in size and has 58 million rows. It has a column called 'balance_forward' that is char(10) (Unusual choice for a ...
0
votes
1answer
39 views

What are the implications of upgrading statistics SQL Server [closed]

I Have a SQL Server 2005 DB, with 500Gb of Data. The Statics do not upgrade from 2010 and this past saturday were updated. Yesterday the querys on DB were very slow and caused bad response times. ...
0
votes
0answers
37 views

I want to make stored procedure for the TABLE1 that have another table2 column which is not primary key in sql server

I have two table Maker(maker_id(PK), maker_code, maker_desp) Model(model_id(PK), model_code, model_desp, maker_id(FK)) Now I have a gridview 2 in asp.net c# MAKER TABLE (GRIDVIEW 1): Maker_id, ...
2
votes
1answer
53 views

Can I throttle individual databases instances on one SQL Server 2005 box

I am running a very old server that has a database that is being permanently retired within 14 months. My company will not invest anymore in hardware or software for this system as we are moving to a ...
1
vote
1answer
53 views

maximum allocate RAM for MS SQL Server

I have MS SQL Server with different size of RAM. So I would like to control the values which are set for the RAM. Somewhere I read a 'rule' to calculate the maximum of RAM. These SQL Server are only ...
3
votes
2answers
2k views

Job Schedule view

I'm trying to create a view for a report so that I can easily keep track of all of the backups that I have scheduled on a server. All of these backups are scheduled in subplans of a maintenance plan, ...
11
votes
3answers
13k views

How can I backup a large SQL Server database without impacting performance?

We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job. The database itself is about ...
0
votes
1answer
64 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 ...
12
votes
4answers
9k views

What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?

I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better: delete rows and insert new ones, or update already existing rows For me ...
2
votes
3answers
489 views

Save multiple SQL jobs in single files

I'm using SQL Server 2005 and I need to save every SQL agent job in single 'create in' *.sql files. The problem is that I got about 20 SQL Server with 15 (up to 51) SQL jobs. How can I export these ...
2
votes
0answers
108 views

Proxy Account SSIS and NETWORK SERVICE

I currently have an instance of SQL running as NETWORK SERVICE which for various reasons I can't change. I have a maintenance plan running under a proxy account which has access to a network share to ...
4
votes
1answer
992 views

I/O is frozen on database databasename

On SQL Server 2005 log report, everyday I receive the following message: I/O is frozen on database databasename. Is it serious ? How to resolve it ?
33
votes
4answers
26k views

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything ...
1
vote
1answer
235 views

table partition in order to solve frequent deadlock issues - how would I know if it will work?

I use sql server 2005 enterprise edition and have a subscriber DB - part of a transactional replication - that is used for Business Intelligence purposes - they run reports out of it, and SSIS ...
1
vote
1answer
1k views

Replication Monitor Information using T-SQL

The image below shows a transaction replication problem that I am currently investigating. The image comes from the Replication Monitor. How can I get this information using T-SQL?
0
votes
0answers
64 views

Tran Log file growing even after log backups and Log shipping configured

I have log shipping configured for a database in sql server 2005 with below details: A) Database X hosted on primary: Log backup every 1 hour as scheduled in LS Backup job: Data file: 40 GB and Log ...
2
votes
1answer
302 views

Listing expired SQL logins

For audit purposes, we need to list all SQL logins with their creation and expiration dates. Is there a way to script this for all of my SQL Servers, rather than performing the task manually through ...
0
votes
2answers
287 views

How can i automate the process of log shipping monitoring

I have log shipping configured between my Prod SQL server 2005 and QA SQL server 2005. Per the requirement from Business we need to provide them with a report at the end of the day for the LOG ...
5
votes
1answer
112 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
0answers
20 views

Usage of temporary tables in my tempdb database on sql server 2005 [duplicate]

I am trying to resolve the disk space issue on one of my drives where tempdb residing occupies more than 100 GB: Stats shown below I read some similar post on the same and i found few queries using ...
6
votes
2answers
2k views

SQL Profiler - Scripting a trace with HostName filter

I have a stock of scripted trace definitions that I use for different levels of diagnostics, none of which filter by HostName. I needed to filter traffic by host today so: Fired up Profiler Created ...
2
votes
4answers
482 views

Help understanding SQL Server 2005 execution plan

One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster. I am looking at execution plan and two things I see that using ...
10
votes
5answers
2k views

Using IF in T-SQL weakens or breaks execution plan caching?

It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate this assertion. I'm using SQL Server 2005 ...
4
votes
1answer
13k views

trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?

I am trying to learn how to turn XML into heirarchical SQL tables. I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone ...
2
votes
1answer
186 views

XML Whitespaces/Unwanted Characters [closed]

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 ...
2
votes
0answers
40 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 ...
0
votes
0answers
122 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
1answer
258 views

How to add an automatically-populated custom sequence number?

I have table something like this: CREATE TABLE [dbo].[ado_test_table]( [id] [int] IDENTITY(1,1) NOT NULL, [tip] [varchar](10) NOT NULL, [datum] [datetime] NOT NULL, [doc_number] [int] ...
1
vote
1answer
147 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 ...
0
votes
0answers
51 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
2k 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 ...
1
vote
1answer
948 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
22 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 ...
3
votes
3answers
939 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 ...
2
votes
2answers
98 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
1answer
108 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
1answer
50 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 ...
0
votes
1answer
27 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 ...