This tag is for the 2005 version of Microsoft's SQL Server.
0
votes
0answers
23 views
How to disable SSMS table designer timeout?
How do i disable the Table Designer timeout in SQL Server Management Studio?
Background
This KB Article seems to indicate that if you stop overriding the Designer timeout:
To resolve this ...
2
votes
0answers
35 views
TempDB MDF file huge after update script ran
On the weekend we had a huge deployment run that affected a large chunk of our system. It was all run in one massive transaction (probably the wrong thing to do). As a result our TempDB DatabaseFile ...
1
vote
0answers
15 views
Problem with sorting matrix groups in SSRS
I've hit the following issue in SQL Server 2005 Reporting Services
This is a slightly odd and complicated situation: I have a table containing various groups and within each group is also embedded a ...
0
votes
2answers
61 views
Does a SQL Server job run on the server or the local machine?
Does a SQL Server job always run on the server, or will it run in the context of the local machine, similar to a DTS package run from Enterprise Manager on the user's machine?
The job in question ...
0
votes
1answer
46 views
How to add custom sequence number in table which is going to be populated automatically
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] ...
3
votes
1answer
31 views
Verify Query Request Received
I have a software system which queries data via OLEDB from an external SQL Server 2005. The query request is sent every 15 minutes. I want to verify that the SQL Server is receiving the query ...
0
votes
2answers
32 views
SQL Server 2005 xp_sendmail error
I have come across a SQL 2005 server that uses SQL mail to send emails. This setup works fine as long as the MAPI profile points to an Exchange 2003 server, but breaks when the profile points to an ...
6
votes
2answers
66 views
Turning OFF CHECK_POLICY by default
We moved from SQL Server 2000 to SQL Server 2005. The client software, which I can not change, creates a user without option
CHECK_POLICY = OFF;
After creating a user, I have to run the command
...
5
votes
1answer
100 views
Failed copy job deletes all users
Since the progression of this was apparently somewhat difficult to follow the first time around:
I attempted a completely boring, been-done-a-thousand-times-before copy of a database using the copy ...
0
votes
2answers
33 views
Is there any way to connect to one server and utilize a linked server using Windows authentication and NTLM?
I currently have SQL Server 2005 (Server A) that has a SSAS 2005 linked server (Server B). The powers that be do not want to enable Kerberos authentication, so I'm stuck with NTLM.
The problem is ...
-1
votes
0answers
103 views
All users missing after a detach/reattach [closed]
After detaching and reattaching a database to do a copy operation, all of the user objects are now missing from the database (I should add that the copy failed; according to the logs, there was an ...
2
votes
4answers
105 views
When should I use the shrink option
I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
4
votes
2answers
114 views
Why do linked servers have a limitation of 10 branches in a CASE expression?
Why does this CASE expression:
SELECT CASE column
WHEN 'a' THEN '1'
WHEN 'b' THEN '2'
... c -> i
WHEN 'j' THEN '10'
WHEN 'k' THEN '11'
END [col]
...
2
votes
0answers
21 views
ODBC - how to set up ODBC driver for 2 languages (for different code page)
How to set up an ODBC driver to operate the Polish code page (1250) and Lithuania (1257) at the same time? Although the code page Lithuanian is wider than the Polish code page, the driver incorrectly ...
7
votes
4answers
518 views
Index Seek vs Index Scan
Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan.
What is the difference between and index seek and an index ...
1
vote
1answer
37 views
Help with best practice of merging databases
Bad news, our website server (Windows 2003) crashed because of dead RAID controller.
Luckily few hours later our backup server was up and website was live again.
Hopefully tomorrow our original ...
4
votes
2answers
70 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
109 views
How to check if I am hitting the Express Edition size limit?
I am confused. AFAIK SQL Server 2005 Express has a limit of 4GB database data size. However I have the following results from sp_spaceused:
How can I check if my DB is hitting the size limit?
Is ...
0
votes
1answer
34 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
89 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 ...
6
votes
2answers
246 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 ...
0
votes
0answers
101 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 ...
3
votes
2answers
101 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
74 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
132 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, ...
2
votes
2answers
119 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 ...
2
votes
2answers
43 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 ...
2
votes
0answers
31 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
0answers
50 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
66 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
47 views
Should database files be on different disk(s) than OS?
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
175 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 ...
1
vote
1answer
49 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 ...
1
vote
1answer
63 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
votes
2answers
71 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
122 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 ...
2
votes
3answers
73 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 ...
2
votes
1answer
62 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 ...
0
votes
1answer
45 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 ...
3
votes
3answers
59 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 ...
3
votes
2answers
240 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 ...
0
votes
0answers
43 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
64 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 ...
4
votes
2answers
128 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
1answer
33 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
...
6
votes
7answers
192 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 ...
2
votes
1answer
69 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 ...
1
vote
1answer
102 views
SQL Server taking a lot of memory
I have SQL Server Express on my VPS(2GB RAM) with a database size of 1.5 GB that I expect will increase to 15 GB. I have noticed that SQL Server is using 1.5 GB RAM even when only small queries has ...
0
votes
0answers
46 views
Invalid Object SQL Server 2005 Express
I have 2 SQL Servers, let's call them A and B. I have a ColdFusion app that runs on server A. It connects to my database, MyDb. I needed to update the db content so I backed up the same db on server B ...
1
vote
1answer
103 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) ...
