This tag is for the 2005 version of Microsoft's SQL Server.
3
votes
1answer
103 views
Need to divide single column into multiple columns
My table has data with a single column:
1
2
3
4
5
6
7
8
9
10
And I want output like this:
1 2 3 4 5-first row
6 7 8 9 10 - second row
Another has two columns like this:
Column 1 ...
2
votes
1answer
37 views
How do these snapshot isolation level configurations interact on a SQL Server 2005 instance?
I started troubleshooting a tempdb issue we are having on SQL Server 2005 Enterprise Edition. A developer is getting an error that tempdb is out of space. Technically, the error is:
Transaction ...
0
votes
2answers
71 views
Shrink transaction log while transaction in progress
I have a several-GB import into my SQL Server 2005 (Standard Edition) database.
The import is separated into 4 parts, each part in its own transaction.
As you can guess, the transaction log files ...
0
votes
3answers
37 views
Access to tables in SQL Server
I would like to know if there is a table where accesses to tables are saved, per user or per system.
I'm using both SQL Server 2005 and 2008.
3
votes
1answer
37 views
Does the week day number changes according to localization in SQL Server?
Does the weekday number changes according to server/database regional configuration in SQL Server (2005+)?
4
votes
0answers
97 views
After 5 years of working, SQL Server 2k5 Table now locking without any changes made to DB
I have a table: OrderDetails.
For 5 years, only the odd Deadlock has occured.
If it happened, it happened on indexes which was on OrderDetails table
Now all of a sudden, deadlocks are everywhere - ...
0
votes
2answers
48 views
History from SQL Agent Jobs (SQL Server 2005)
how can I able to see all available records from SQL Agent Jobs, because in SSMS is dispayed only reduced number of records
are these records stored in system databases, not only in plain log files
...
0
votes
0answers
29 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
43 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
19 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
64 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
50 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
35 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
72 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
104 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
38 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
108 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
118 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
133 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
622 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
43 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
84 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
128 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
37 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
92 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
252 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
115 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
117 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
75 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
137 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
131 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
46 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
33 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
55 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
69 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
49 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
193 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
53 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
73 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
72 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
166 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
74 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
64 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
52 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
61 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
255 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 ...
1
vote
1answer
77 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 ...
