This tag is for the 2005 version of Microsoft's SQL Server.
2
votes
1answer
93 views
ISNULL on NTEXT causes fatal exception on SQL Server
I have a weird error that occurs on a client's SQL Server 2005 box. We have provided them with a dataview that is populated with a query with a CTE with several joins / subqueries that does some text ...
4
votes
2answers
113 views
Can't select database for transaction log backup in maintenance plan
I am running into a problem while trying to save a transaction log database backup maintenance task:
Add the 'Back Up Database Task' to the maintenance plan.
Edit the task.
Select backup type: ...
5
votes
3answers
183 views
What type of SQL Server Replication fits our scenario? Merge or Transactional?
Background
In our current setup, we have a single SQL Server 2005 instance on our remotely hosted web server. We additionally have another (non-MSSQL) database which we use for our POS system, which ...
1
vote
1answer
55 views
SQL Server 2005 encryption - What Grants should I give
I use Mixed mode Authentication and I want my database users (in the Public role membership) to have the ability to JUST execute procedures that encrypt/decrypt data (using Certificate and Symmteric ...
2
votes
2answers
106 views
How to restore a filegroup from its backup in SQL Server
I need guidance on how to restore a file group in Database from the File group backup taken from another same DB server. Is it possible to restore the filegroup by running restore DB for File Group.
...
1
vote
1answer
668 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 ...
0
votes
3answers
88 views
How to check if database is in mirrored mode?
I have two database servers A and B where I have databases set up in mirroring namely X. There is another database on server A named Y which is only present on database server A. Y database has a ...
2
votes
3answers
173 views
Why is SQL running the same query longer from another connection?
Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely?
Dont jump to any conclusion just yet, let me explain what I mean...
Here is the setup:
A Windows ...
6
votes
3answers
272 views
What alternatives exist when a table requires too many foreign keys?
We have a base table that defines parts and holds information like part number, description, price, weight, etc. We also have approximately 400 tables that reference the base table and provide ...
3
votes
2answers
70 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
132 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|
...
3
votes
1answer
64 views
Primary key with “NOT FOR REPLICATION” option
I have recently taken over a project, and I have discovered that in most tables the primary key has the property "NOT FOR REPLICATION".
I'm no DBA, but surely in most databases, a record without a ...
1
vote
1answer
172 views
how to add attachment(text file) to database mail?
I have scenario
Daily i run a sql job to apply a new updates to one table - this job will create one text file daily - text file contains all new updates
I can send a mail to client that job is ...
4
votes
1answer
112 views
Find transactions that are filling up the version store
we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases.
Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be ...
0
votes
0answers
33 views
Email alerts and Server Side Tracing
This question is in regards to SQL 2005 and email alerts on long running queries.
Does this sound like the best approach (with the least impact to the server) to receiving alerts to long running ...
2
votes
2answers
84 views
Is there a way that I can export a list of all table triggers on a server?
I have two SQL Server instances that I manage. One is a SQL Server 2000 instance, and the other is 2005.
Some where, on these servers, I recall setting up a couple table triggers that were executed ...
1
vote
1answer
99 views
How do I understand this deadlock?
I have two threads doing inserts into a table at the same time, causing a deadlock. The interaction with the table is in a new transaction, so I'm pretty confident there is nothing else going on here.
...
0
votes
0answers
39 views
SQL Server 2000 database attach error to SQL Server 2005
I have a SQL Server 2000 database (mdf and ldf).
When I try to attach it, using SQL Server Management Studio Express 2005, I get this error:
Attach database failed for Server 'XXXXX\SQLEXPRESS'.
...
0
votes
1answer
100 views
Restoring a differential backup in SQL server 2005
I have created a full back up of my database by executing the line below in my application:
Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & ...
0
votes
1answer
87 views
Restoring a differential backup in SQL server 2005 [duplicate]
I have created a full back up of my database by executing the line below in my application:
Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & ...
2
votes
2answers
126 views
Help with tricky update statement
I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier.
I have two table variables:
DECLARE
...
0
votes
1answer
143 views
FETCH API_CURSOR causing open transaction in tempDB
A select statement run from Oracle 11gR1 to SQL Server 2005 using Gateway leaves an open transaction in tempdb. The user is a datareader in Database XYZ. Not sure why the open tran is in tempDB when ...
1
vote
1answer
74 views
Consolidating indexes
I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a ...
1
vote
1answer
47 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 ...
1
vote
1answer
60 views
Can I run a Report Server and a Mirror server from a working Log Shipping secondary instance?
Our setup:
City A - Primary server for logshipping
City B - Secondary server for logshipping
Now there is a lot of data being updated from A - B.
And the server in City B is underutilized, and it ...
7
votes
2answers
178 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 ...
3
votes
1answer
132 views
SQL Server 2005 Replication
I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription.
server 1 windows 2003 server 192.168.10.1 connected ...
4
votes
1answer
66 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 ...
1
vote
0answers
46 views
SQL Server 2005 Relationship [closed]
I have SQL Server 2005. Tables have relations which each others.
Some tables are libraby. 1 table has 10 foreign relations. I dont like the Database performance, a little slowly and I dont know what ...
3
votes
0answers
34 views
SQL Server 2005 not collecting logins in the log file
I am currently running SQL Server 2005 Standard Edition on a Windows 2003 server machine.
I have gone to the properties of the server and confirmed that the Login Auditing is set to both failed and ...
0
votes
0answers
91 views
Is my SQL Server 2005 Maintenance Plan running twice a day?
I'm a new DBA trying to discover how a production Reports server is setup. I did find a Maintenance Plan. It does show to be scheduled to run in a Job at 1am each day.
Here's my dilemma: When I ...
10
votes
1answer
370 views
What exactly does “No Join Predicate” mean in SQL Server?
MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate".
But unfortunately it does not seem to be as easy as that.
For example, very ...
3
votes
2answers
113 views
Find orphaned users
In SQL Server 2005, is there a way to find users that either don't exist at the server level (an account that was deleted at server level but wasn't disassociated from databases before it was deleted) ...
8
votes
4answers
495 views
Can I launch a stored procedure and immediately return without waiting for it to finish?
We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day.
I have a second stored procedure that should be run after ...
5
votes
3answers
198 views
Everyone warns against shrinking a database, but will it work in my case? 1 GB db with tables removed now 100 MB
Everyone is warning against shrinking a database, e.g.
SQLAuthority
But will it be a valid option in my case? The database was originally more than 1 GB,
but with a table removed from it (moved to ...
1
vote
0answers
57 views
Have @Table1 with values to update into @Table2, can I update @Table1 with @Table2.@@Identity? or Cursor?
I have 2 tables:
declare @Customer_Links table (
Customer_LinkID int identity(1,1),
CustomerID int
)
declare @Customer_Regions table (
CustomerRegionID int identity(1,1),
Customer_LinkID int,
...
3
votes
1answer
114 views
Adding indices to views
I have a table that contains XML data in a varchar type field. This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table.
...
0
votes
1answer
68 views
Getting the exact edited data from a column in SQL Server
I have two Tables:
Articles(artID, artContents, artPublishDate, artCategoryID, publisherID).
ArticleUpdated(upArtID, upArtContents, upArtEditedData, upArtPublishDate, upArtCategory, ...
3
votes
1answer
84 views
Replication between SQL Server 2005 and Azure database
Is it possible to set up transitional replication between a SQL Server 2005 and a SQL Azure database?
My server is nearing end of life and I would rather migrate to a cloud infrastructure instead of ...
4
votes
1answer
148 views
One CPU in a 32-core system is getting to 100% usage and hurting performance
I am experiencing a problem with one server. It has 32 cores and seems to be well-configured in all aspects (memory, hard disk drives...). Performance Dashboard is not showing blocks or deadlocks and ...
1
vote
2answers
422 views
32-bit ODBC driver on 64-bit SQL Server [closed]
I am unable to use an ODBC 64-bit driver with SSMS 2012 running on a SQL Server 2012 64-bit instance. However, I believe that the 32-bit driver works.
My question is can I use SSMS 2005 32-bit to ...
1
vote
3answers
313 views
SQL Server Restore a SINGLE table from a backup [duplicate]
on SQL Server 2005, we do a weekly full backup with nightly incremental backups.
I want to know if it is possible to restore a single table from a backup, either to the source database or a ...
0
votes
1answer
79 views
How can I install a provider for linked server?
I have a provider for a linked server called PervasiveOLEDB.11.0 installed on a sql server 2005 machine. It always me to have a connection to a pervasive sql database.
I would like to use this same ...
2
votes
1answer
43 views
Can one assign a performance score to a stored proc to compare performance of 2 procs
I have an existing stored procedure,
and an updated version of that same stored procedure where changes have been made, but the same resultset is returned.
The updated version of the procedure ...
0
votes
0answers
10 views
User selects to view Report by Units/Empl/State- how to optimize query when selecting U/E/S (3 if statements)
I have a stored procedure in which the user selects to view sales by either Units / Employees/ State (in the real application there are 10 options)
This inserts into a #temp table via the following ...
0
votes
1answer
132 views
How do you avoid a deadlock on the primary key of a table? [closed]
So I have a deadlock that looks like this. Both sides of the deadlock complain about the the primary key clustered index.
<deadlock-list>
<deadlock victim="processec49b8">
...
3
votes
2answers
116 views
Update row based on match to previous row
I have the following table:
Declare @m_TestTable table
(
RowID int,
OrderNo nvarchar(10),
DetailID int,
FirstUniqueDetailEntry int
)
Insert into ...
2
votes
1answer
121 views
Does rowlocks on index cause keylocks? : Transaction (Process ID xy) was deadlocked, example included
I am currently experiencing the error (logged via traceID 1222 ):
keylock hobtid= 8205698989 objectname=mydb.dbo.Orders indexname = _dta_Index_Orders_5_120345_K1_K2_7_1 id=lock43fe181 mode=S ...
2
votes
1answer
298 views
How do you avoid a foreign key constraint creating deadlocks?
So I have two tables that create a deadlock. The application literally do nothing but update two different tables in two different transactions. There is a foreign key constraint (not cascading, just ...
2
votes
2answers
53 views
2 databases in sql server 2005 “Production Server”
Can 2 databases decrease the performance in a sql 2005 production server? Server specs:
Dell Power edge 2950, 2 Quad core Xeon, 16 GB of RAM, 3x73 GB sata hard drive hot plug, windows server 2003 R2 ...
