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

learn more… | top users | synonyms (2)

1
vote
2answers
1k 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
90 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 ( ...
3
votes
2answers
131 views

Inconsistent information from different DMVs

I've a server with 12 GB of physical RAM. I just lowered its 'Max Server Memory (MB)' setting from 10 GB to 8 GB. If I look at the processes' committed memory in Perfmon, I see: select * from ...
1
vote
1answer
80 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 ...
0
votes
0answers
52 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
64 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
55 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
318 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
56 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
528 views

Cannot shrink transaction log

Bit of a headscratcher here. I have a db that has less than 1GB of data, but a 40GB log file. Transaction logs are backed up daily, and there is not a lot of activity on this database; approximately ...
2
votes
1answer
239 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
152 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
92 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 ...
0
votes
4answers
2k views

Best table schema to store daily hits for many items?

In my Sql Server 2005 database, I'm considering two ways of creating a table to track daily views on 100,000 items: Option 1) Each day would insert a new row for each item. So the table columns would ...
3
votes
3answers
5k views

How can I search the full text of a stored procedure for a value?

I use the following script to search the text of all stored procedures when I want to find specific values. SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION ...
1
vote
1answer
233 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 ...
7
votes
2answers
4k views

ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?

Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
0
votes
1answer
63 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 ...
11
votes
3answers
4k views

Is it possible to backup and restore part of a database in sql-server?

We have a sql-server 2005 database that we regularly transfer from our client site to ours. This takes a long time because we don't have a direct connection and have to transfer the file over their ...
0
votes
1answer
39 views

Count SQL Query [closed]

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
895 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 ...
5
votes
2answers
6k views

Deny access to information schema in SQL Server

I am looking for the best way to disable access to the sys.tables / Information Schema for a user / group in SQL Server. I found this thread from 2008 It shows a way how to deny access on ...
4
votes
2answers
1k views

How can I find databases with a large number of virtual log files?

I have several databases on a production server that are in the hundreds of gigabytes, and have many thousands of transactions running through them on a daily basis. Almost all these databases are ...
0
votes
2answers
122 views

The dump database on a SSD disk is slowly restored in SQL Server

I prepared new server for my DB on Windows 2008R2 + SQL Server 2005 SP4. I always estimated time of restore database of a backup as time of copying of the file of a backup at that place where the ...
2
votes
2answers
262 views

What's faster than Mirroring tables, compare or write?

A co-worker is trying to sync table B with the contents of table A, both tables have the same structure, we wrote 3 statements, one for update, one for delete and one for inserts. I have a question ...
1
vote
2answers
1k views

Backup without using SQL Server Management Studio

I am trying to take a backup on a Windows Server 2003 and SQL Server 2005 installed on it. But, there is no SQL Server Management Studio on it. How can I take full backup of a particular database.
0
votes
1answer
151 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 ( ...
1
vote
3answers
346 views

SQL Server 2005 Log Truncate - how long for a 60Gb log; mins, hours or days? [closed]

I have taken over an old website. To cut a long story short the people that host the computer, backup all the database files, but there is no maintenance plan in place for the backup (e.g. they ...
4
votes
1answer
7k views

How can I call a remote User Defined Function with Parameters?

We have one SQL server which contains a link to another server that is on a different box. I would like to create a user-defined-function on ServerA which returns the value of the udf on ServerB, ...
4
votes
3answers
277 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 ...
2
votes
0answers
537 views

DBCC CHECKDB reports torn page. How do I find what is in the torn page?

Using SQL Server 2005 I get the following error when running DBCC CHECKDB SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual ...
0
votes
2answers
64 views

Duplicate Statistics?

I have been looking at poorly performing stored procedure that performs well for larger customer databases. I believe the reason is that SQL Server has not had time to properly create good statistics ...
8
votes
2answers
14k views

Why “Begin Transaction” before “Insert Query” locks the entire table?

I am using SQL Server 2005 Express. In a scenario, I added Begin Transaction command just before an INSERT statement in a stored procedure. When I executed this stored procedure, it locked the entire ...
0
votes
1answer
644 views

converting then outputting varbinary(max) FOR XML PATH

I have a vendor application that has stored some of its text data in a varbinary(max) column. I am exporting this data (from several tables with different data types), ideally using FOR XML PATH. What ...
0
votes
1answer
212 views

Replicating a View to a Table. Issues with schema binding, view indexing

I am rather new to this. On my development server, I have 3 views that get live data from another database on a private internal server. Sample structure: DevDB --> Tables --> Views ...
2
votes
2answers
1k views

how to resolve mssqlsystemresource.mdf corruption?

Desperate for help, but fearing the worst here :( Something crashed SQLSERVER 2005 this afternoon approx 3pm. Since then I have been trying to get running again. Original log file messages told that ...
16
votes
1answer
2k views

Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT?

This is a followup question from: http://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically I'm still having deadlock/timeout situations in ...
1
vote
0answers
83 views

SQL Server 2005 CHECKCATALOG fails

CHECKCATALOG is failing on a database that originated in SQL Server 2000. I am getting errors of the type: Msg 3853, Level 16, State 1, Line 1 Attribute (object_id=489156888) of row ...
7
votes
4answers
10k views

Converting RTF in a text column to plain text in bulk

I have a legacy system with about 10 million rows in a table. In that table there is a column of type text, most of them are standard text but about 500 thousand rows have RTF markup in them. I need ...
6
votes
0answers
279 views

Dropped index from view still referenced in execution plans [closed]

On a SQL Server 2005 Enterprise edition server, an index has been dropped from a view. When running a select * query that includes this view, no results are shown. In addition, the execution plan ...
3
votes
2answers
583 views

Microsoft SQL Server 2005 Encryption Options

I'm trying to explore some encryption options on Microsoft SQL Server 2005. I've recently got around SQL Server 2008 and was able to enable Transparent Data Encryption (TDE). Now I'm trying to figure ...
1
vote
0answers
155 views

How do I use MDX scripting in SQL server 2005 Business Intelligence Development Studio to create the following Member? [closed]

I am using Microsoft SQL server 2005 Business Intelligence Development Studio to build a cube. I need to calculate a member called INCREMENTAL OVERTIME. INCREMENTAL OVERTIME is based off of the ...
2
votes
2answers
5k views

SQL Server drop and create all users in every DB

I would like to drop and recreate every user in a database without having to manually do this for 500 users. I have this so far: USE [Maintenance] GO IF EXISTS (SELECT * FROM ...
4
votes
3answers
186 views

What is the proper way to store a full table of data outside of a database? It needs to be able to be restored as well

I have a large SQL Server 2005 database that hasn't had the best maintenance over the years (I just took it over recently). There are around 100 tables that can be completely removed from the ...
-1
votes
2answers
5k views
6
votes
1answer
6k views

Mirroring - Server network address cannot be reached

I have SQL Server 2008 R2 Installed. It contains three instances. Default (MSSQLServer) First Instance Second Instance All these are Log On as Network Service.. Default Instance is Principal ...
1
vote
2answers
73 views

Store n:m data with most n:1 tuples

I have these tables: create table data ( id int identity not null, ref int not null ); create table reference ( refid int not null ); with ~2 million rows in data and ~200k rows in ...
0
votes
1answer
493 views

Get complete value of varchar(max) field - SQL Server 2005

I have searched for a couple of hours but cannot find the exact solution for this. In my SQL Server 2005 database table (tableA) there is a varchar(max) column. I want to get complete value of that ...
0
votes
0answers
34 views

Got error with lookup transformation in sql server 2005 BIDS

I have query to seperate the current and history DB's ** select , year(doj) from source where year(doj)<=(year(getdate())-3)* Here the moved data(<=2011) from current DB should be deleted and ...
3
votes
1answer
227 views

Why is there ASYNC_NETWORK_IO wait type on Shared Memory connections?

I have an app (windows service) local to the SQL Server box. It calls a sproc on the SQL Server box that returns a ton of data and thus causes ASYNC_NETWORK_IO wait. The database connection from is ...