This tag is for the 2005 version of Microsoft's SQL Server.
0
votes
2answers
32 views
Can you create an unrecoverable SQL Agent back log?
I have created and am using an SQL Agent for the first time. I have searched google but either the info isnot there or I am not getting the keywords correct (SQL agent + back log/callstack overflow ...
3
votes
2answers
44 views
Run two SQL Agent Jobs Simultaneously (not sequentially) as the next step
At the completion of Step 1, the job currently goes to step 2 - an osql command to start a Sql Agent job on another server. However at the completion of step 1, I would also like to start a ...
2
votes
2answers
79 views
SQL Server Express - How to check if I am hitting the size limit?
I am confused, AFAIK Sql Server 2005 Express has a limit of 4GB database data size.
How can I check if my DB is hitting the size limit?
Is unallocated space the space left untill I hit the limit?
...
0
votes
1answer
27 views
File locations for Full Text Catalogs using SQL Server 2005 on VMware
I have an issue with one of our development SQL Server Servers. We have databases that use full text search catalogs. Just some background info, our servers are configured to use separate drives for ...
0
votes
3answers
75 views
How to find all positions of a string within another string
How can I find all the positions with patindex in a table or variable?
declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek '
+ 'dokhtare khoshkel be disco raft va ali ...
1
vote
1answer
154 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 ...
1
vote
1answer
601 views
Maintenance Plan fails but the query it generates Runs
I have an SQL Server 2005 Enterprise Edition whose Maintenance plan fails constantly with the error:
backup MYSERVER (MYSERVER)
Backup Database on MYSERVER
Databases that have a compatibility level ...
6
votes
2answers
181 views
If I fail over one database, do the others that share the same mirror endpoint fail over as well?
We have two databases setup for mirroring on a single SQL Server instance: a test database and a production database. Both get mirrored to another server using the exact same endpoints.
If I go into ...
1
vote
1answer
49 views
User login error when trying to access secured SQL Server database
We have a username that was recently renamed from one username to another (think getting married). The Active Directory admin renamed the user because "it has always worked in the past".
One vendor ...
0
votes
0answers
73 views
DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'
I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
15
votes
4answers
993 views
Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?
Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.
The database development started in ...
3
votes
2answers
59 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
2answers
62 views
How to backup databases to ensure data recovery
I have 15 customer databases that have minimum replication at different sites. I currently do nightly back-ups and transfer the database back-up nightly to our main server via 7zip. We recently had ...
2
votes
3answers
91 views
How can I query data from a linked server, and pass it parameters to filter by?
I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned.
The basic syntax looks something like this:
INSERT INTO #tmpTable (Id, ...
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
78 views
SQL Server 2005 error while starting service
I am getting error while starting the service & errorlog is:
2013-04-04 14:50:49.14 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) ...
0
votes
1answer
136 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 ...
4
votes
1answer
79 views
Process attempted to unlock a resource it does not own
SQL Server 2005 SP4 32-Bit
I have a DBCC CHECKDB job running nightly. Last night, soon after the job started, I got the errors below. The database is NOT in suspect mode, and CHECKDB comes back clean ...
2
votes
2answers
85 views
Restoring differential backup from SQL Server 2005 to SQL Server 2012
We are migrating a client to a new server. Their old server has SQL Server 2005. I have attempted to perform a differential backup however I received an error about the LSN chain being broken.
Please ...
1
vote
2answers
96 views
How to retrieve the definition behind statistics added to tables
Is there a way to programmatically retrieve the definition of each STATISTICS added to table columns and indexes. For both user added and system created indexes. There are many STATISTICS like ...
2
votes
2answers
42 views
Is there a better way than the ones listed within to join data in an excel spreadsheet to a table in a SQL Server database?
Let's say I'm given a list of names and email addresses in an excel spreadsheet. I am asked to find all customers in the database who's name, and email are in that list.
I currently know of two ways ...
0
votes
2answers
136 views
SQL Server replication subscriptions marked as inactive
Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever?
It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription ...
1
vote
4answers
6k views
Retrieving row count without using Count function
I wrote a query below which shoots me a syntax error why would it do so,
SELECT MAX('Row') FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS)
Error Desc:
Incorrect syntax near ...
3
votes
1answer
124 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 ...
2
votes
0answers
29 views
How can service pack rollback file end up on non-system volume?
I'm attempting to upgrade a SQL 2005 SP3 clustered instance to SP4. During installation, a rollback event took place with the error indicating there was insufficient space on the the disk. The system ...
1
vote
3answers
166 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 ...
1
vote
0answers
39 views
When converting a table valued function to inline, why do I get a lazy spool?
I have a table valued function that I want to convert to inline to improve performance.
It sets the value of variables to the value four bit columns in one row of a table (looking up the row using a ...
0
votes
2answers
53 views
Can I copy the table structure of a #temp table to a new physical table?
I have a #tempTable that was created using
SELECT *
INTO #tempTable
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls',
'SELECT * FROM [Sheet1$]')
Is there an easy way ...
0
votes
1answer
42 views
SQL Server database files on different disk as is OS installed
My very short overview
SQL Server databases are installed as virtual machines on VMWare, there are plugged a few disk areas with the same (or very similair) parameters
versions are SQL Server ...
5
votes
1answer
93 views
How do I shrink the physical Transaction Log file when it's the principal in a mirror?
We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking ...
2
votes
1answer
83 views
Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box
We currently have a SQL Server 2005 instance hosting a lot of application specific databases (ArchiveManager, SolarWinds, Kaspersky etc). We are planning to deploy a new SQL Server 2008 R2 instance on ...
1
vote
1answer
44 views
Limiting user access to tables based on a ROLE
I have a SQL Server 2005 database to which I would like to LIMIT access. I have done the following:
Created a database role called NO_HR
Added the tables to this role under Securables that should be ...
1
vote
1answer
42 views
IIS takes longer to run queries than SMSS
I'm completely stumped on this one.
We're running a pretty usual intranet app here, classic ASP frontend and SQL Server 2005 backend.
Occasionally, once/twice a week, a certain stored proc takes ~50x ...
0
votes
3answers
81 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 ...
-1
votes
2answers
69 views
SQL Server Transaction logs
Could someone tell me the role of transaction logs in SQL Server ?
I have a transaction log file, after making a lot of transactions for testing purpose I take the backup of it via command. Sometimes ...
2
votes
1answer
57 views
Validate Primary Key and Index Selection
I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this ...
2
votes
3answers
62 views
How to take SQL Server database under this scenario?
I have a database of size 290 GB running on SQL Server 2005 (compression is not available) and disk space is z: drive 250 GB and Y: drive is 80 GB. How can I take a full backup in this scenario? How ...
3
votes
2answers
205 views
Lock escalation problem on a trigger
I've inherited a SQL Server 2005 database that is getting 2-3 deadlocks a day.
I've tracked it down to a scheduled job that runs during the day and inserts into a table with a trigger.
The trigger ...
4
votes
3answers
306 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 ...
0
votes
1answer
34 views
Does WITH SCHEMABINDING on a multi-statement TVF improve cardinality estimates?
Based on http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx and other articles, SQL Server assumes that a multi-line table valued ...
6
votes
7answers
178 views
Main Considerations When Moving From MS Access Programming to SQL Server
First post, be gentle...
I am a 100% self taught MS Access programmer (main part of my job is programming), I am now building larger databases; still using MS Access as the UI but SQL Server to store ...
3
votes
3answers
52 views
Which system databases should I checkdb and defrag indices?
I have SQL Server Agent (2005) jobs that periodically perform CHECKDB and a defrag (ALTER INDEX REORGANIZE/REBUILD) of any index that is highly fragmented. These are typical maintenance best ...
4
votes
2answers
103 views
Objects in Tempdb that are not associated with a session
I have a SQL Server 2005 instance where my tempdb i apparently holding on to objects that are not in use anymore. The database has 24gb total and is apparently unable to go below 8gb in use. For ...
0
votes
0answers
40 views
sql server 2005 profiler
We have a overnight agent job and one of the steps runs particular slow recently. The step calls a stored procedure. I used sql server profiler to capture the trace for that proc into a table. I ...
0
votes
1answer
51 views
Understanding Deadlock graph on one table
I do not understand this deadlock graph that we are experiencing on this table (I have had to redact the exact column names) I have not had much exposure to deadlocks outside of reading material and ...
3
votes
1answer
2k 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 ...
0
votes
1answer
24 views
sql server 2005 deadlock error 3930
I am trying to simulate a solution for a deadlock issue happened on our production server.
Run this to create a table to be used in 2 and 3.
IF (SELECT OBJECT_ID('dbo.MyProduct') ) IS NOT NULL
...
2
votes
1answer
66 views
Restoring a database back to a point in time before full backup was made
A problem with one of our database tables arose at the end of last week and I wanted to restore that database to a server in order to retrieve the table. We only have the last backup, and a full set ...
4
votes
2answers
2k 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 ...
2
votes
5answers
700 views
Track deleted rows/records
A project has the requirement that it shall not be possible to lose data from certain tables. That is, if I "delete" a row, that row's information should somehow be preserved.
Additional information
...
