This tag is for the 2005 version of Microsoft's SQL Server.
2
votes
1answer
174 views
SQL Server 2012 data striping
Is data striping possible like mentioned here data striping in older SQL Server 2005
in the newest version of SQL Server like SQL Server 2012?
I know this is possible in 2008 and 2008R2.
2
votes
2answers
478 views
How to make a unique column with default value of UUID
I have a key column for internal use which is just a increasing integer, but I would like to have a second unique column which is a UUID, but I don't know how to have a function called for the default ...
3
votes
1answer
768 views
MS Access displays alle columns in SQL Server tables as “deleted”
I have created an ODBC connection to a SQL Server 2005.
When I link to the tables from MS Access, some tables have only "deleted" data.
This means the rows and columns are displayed, but all data ...
1
vote
1answer
245 views
SQL Server Database Keeps Semi-Detaching Itself
Forgive me if I am not using all the proper terms but I have databases that will detach themselves from SQL Server. In SQL Server Management Studio the plus sign will be missing next to the database, ...
2
votes
1answer
351 views
How to add another table to publisher and subscriber without reinitializing the subscriber?
Is there chance to add another table in SQL Server 2005 transactional replication without reinitializing subscriber.
Thanx.
6
votes
1answer
10k views
SQL Server 2005/2008 UTF-8 Collation/Charset
I can't find option(s) directly to set UTF-8 rellated Collations/Charsets in SQL Server 2005/2008, same as is possible to set in another SQL engines, but in SQL Server 2005/2008 are there only Latin ...
2
votes
1answer
1k views
Create a view with dynamic column headers based on original table row data? [closed]
Given the two sample tables here:
Tickets Table
ID User Description
------------------------------------
0 James This is a support ticket
1 Fred This is a ticket too
Properties ...
3
votes
2answers
2k views
SQL Server 2005 tempdb Time out occurred while waiting for buffer latch type 4 (pagenum)
Three separate processes have failed in the last two days with very similar errors:
Time-out occurred while waiting for buffer latch type 4 for page (1:1189832),
database ID 2.
Time-out occurred ...
12
votes
3answers
4k views
How does shrinking a SQL Server log file affect performance?
I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
4
votes
3answers
161 views
how to copy data from SQL Server table to excel Spreadsheet thru Linked Servers
I try to copy data from a table in SQL Server 2005 into a excel spreadsheet. Since that sheet is linked to some external charts, so I have to overwrite the existing data with the updated data from SQL ...
1
vote
0answers
158 views
Update excel sheets from SQL Server 2005 linked servers
I use SQL Server linked servers to connect to Excel 2003 multiple tabs workbook. After connected, I have not problem to use select statement like:
select * FROM OPENQUERY(TEST, 'select * from [fist ...
2
votes
2answers
87 views
Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail
I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file ...
5
votes
2answers
260 views
Can I Run Two Full Backups on SQL Server 2005 at the Same Time?
Running SQL Server Express 2005 here. We are creating several programs which run a backup on the database using Server Management Objects (SMO) mostly through C# (.NET). We have separate processes ...
10
votes
1answer
1k views
Can I query a tab-delimited file from SSMS?
Is it possible to query a tab-delimited file from Sql Server Management Studio to view its data without saving it anywhere?
I know you can BULK INSERT from a tab-delimited file using something like:
...
1
vote
1answer
2k views
Shrink my .mdf file in SQL Server?
I have a 10 GB .mdf file with 250 mb .LDF.
I want to shrink the 10 GB .mdf (if possible)
I read about the dbcc command, but I read it in context of shrinking the .LDF (into any size desired using ...
2
votes
1answer
125 views
clustered and covering index ignored on delete statement. Table scan occurs
Why would SQL Server 2005 find it more efficient to perform a table scan instead of using the available clustered index on the primary key (and only the primary key)?
DISCLAIMER:
There is also a ...
1
vote
3answers
120 views
Check of SQL Server upgrade success
I have performed SQL Server upgrade from 2005 to SQL Server 2008. Is there any tools/possibilities to check, whether the upgrade from SQL Server 2005 to SQL Server 2008 has been accomplished ...
4
votes
2answers
112 views
SQL Server update and database transfer
In my test network there is a database created with SQL Server 2005. I have to update SQL Server 2005 to SQL Server 2008. The data base should be used with SQL Server 2008. So it is to be moved. What ...
3
votes
3answers
816 views
How can I get the correct offset between UTC and local times for a date that is before or after DST?
I currently use the following to get a local datetime from a UTC datetime:
SET @offset = DateDiff(minute, GetUTCDate(), GetDate())
SET @localDateTime = DateAdd(minute, @offset, @utcDateTime)
My ...
1
vote
2answers
1k views
Clearing SQL Server 2005 event log
I need to delete the SQL Server 2005 logs, but can't figure out how to do it. I'm talking about the logs that are found in Management -> SQL Server Logs in SSMS object explorer. I can't seem to ...
5
votes
2answers
12k views
Sql Server database in recovery: How to stop, Take ofline or delete?
I have a development database which has entered recovery mode on restart, and has been recovering for the last hour or so.
I need to either stop the recovery, or kill it in some way.
I dont care one ...
3
votes
1answer
190 views
SQL Server Row Count sp_spaceused rows discrepancy
I am researching a performance problem with an application running on SQL Server 2005. I've conducted performance tuning, and I realize that a key table is poorly structured(poorly chosen clustered ...
4
votes
2answers
147 views
Display all tables and their columns
Is there a way to display the layouts for all the tables to see their columns? I am trying to find tables with a certain column names.
I am using the web app Web Data Administrator
Example:
table1
...
2
votes
2answers
133 views
SQL Server 2005-2008 Compatibility Check
A few databases were recently migrated from SQL Server 2005 to SQL Server 2008, and I have to check if all the stored procedures, and triggers still work well. With 'checking', I mean that I should ...
0
votes
1answer
55 views
Changing column value [closed]
Normally used to MySQL so need help on syntax check.
with
SELECT * FROM Bit WHERE CustomerID='3512242' I have a field called Filer. I want to change it from a P to a D. Do I use UPDATE to change the ...
3
votes
2answers
443 views
command or query to ping SQL server
I have 2 windows 7 machines on a very high security domain one acting as a server, the other as a client.
Each box has a SQL Server Express 2005 database and application "A", however I'm only using ...
3
votes
2answers
248 views
Sharing certificates encryped by password between DBs and instances
Question:
I am trying to create a single (self signed) certificate and deploy across many DB's and instances.
I create and backup the cert with:
USE MASTER
CREATE CERTIFICATE DavesCert ENCRYPTION ...
2
votes
0answers
66 views
Is it normal for tempdb TPS to be higher than all other databases combined?
I'm investigating some performance issues on a production DB here, and I've noticed a slightly odd thing that I'm not sure is weird or not.
If I run a quick Performance Monitor trace for the ...
4
votes
1answer
126 views
T-SQL and GPS co-ordinates
I'm looking to store GPS co-ordinates in an MS SQL Server DB for location matching of users with GPS-enabled mobile phones.
I've read about STDistance on MSDN, but it only appears to exist in SQL ...
2
votes
2answers
156 views
Defrag a HEAP by creating clustered index and immediately dropping it
I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
1
vote
3answers
131 views
Inside a trigger create database is not working
I tried using trigger concept. If any new row inserts in the user table my trigger will fire and try to create a dynamic database for that new user. But Inside a trigger create database is not ...
7
votes
4answers
2k views
TSQL: Find Queries causing too many SQL Compilations and SQL ReCompilations seperately
I want to find out what is causing the high SQL Compilations (not re-compilations) I am seeing in performance monitor counters.
Here is my take on it:
If I am seeing a lot of SQl compilations, then ...
9
votes
4answers
259 views
Determining that Indexes on a table are unused
I’ve been running this script to try to find extraneous indexes
select o.name as TableName, i.name as IndexName, p.reserved_page_count * 8.0 / 1024 as SpaceInMB, s.*
from ...
4
votes
1answer
430 views
Failed Upgrade from SQL Server 2005 sp2 to SQL Server 2005 sp4
SQL Server Standard 2005 sp2 32bit running on Windows 2003 Standard 32bit service pack 2.
I attempted a service pack upgrade from sp2 to sp4 using SQLServer2005SP4-KB2463332-x86-ENU.exe and received ...
3
votes
1answer
103 views
Preventing users who are dbo from dropping their own databases
We allow our users to be dbo in their own databases, however this does give them the ability to drop their own databases. This doesn't happen very often, but I want to prevent them from doing this.
...
5
votes
3answers
286 views
Query performance degrades with time and use
I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my ...
3
votes
1answer
104 views
Audit user queries on SQL Server 2005
I'm in an unpleasant situation where I have a requirement to audit any queries executed by users via SSMS, SQLCMD, etc. on a SQL Server 2005 database.
The situation is made worse by the inability to ...
3
votes
1answer
116 views
Restrict record to be update or deleted according to dates inside row for SQL Server 2005
I have transaction replication between two SQL Servers. For testing purposes I deleted a lot of records at the subscriber that still existed at the publisher.
I now know that if someone updates, ...
3
votes
3answers
214 views
Does separating VARCHAR values based on size have a performance gain?
I am working with a team that is trying to implement an EAV system. They have decided to split the attribute-value tables out by type and they are debating using different tables for different size ...
3
votes
2answers
134 views
Integrated Security Success on Master, Fails on Other Database
I'm a bit perplexed as to why my connection string is selectively succeeding on the master database in SQL Server 2005. Here are the details of my app. Any ideas where to look? I'm clearly missing ...
2
votes
2answers
188 views
SQL Server imports data in wrong charset with SSIS
I have a flat file which I am importing into a SQL Server table using a SSIS package. The code page is set to 65001.
However, when I run a
SELECT * FROM tableName
the data appears mangled. For ...
5
votes
2answers
2k views
SQL Server backup failing. Error: 3041, Severity: 16, State: 1
First of all, I'm very new at stackexchange, so please bear with me.
I'm running a SQL Server 9.0.4060.
My problem is: my SQL Server backup keeps failing on a lot of the databases.
The Maintenance ...
4
votes
4answers
2k views
SQL Server database size didn't decrease after deleting large number of rows.
I'm no good in SQL, but I've got a database to maintain.
There's almost no place left for it, so I've decided to delete all the data for, let's say, year 2008. After executing delete query (had ...
11
votes
3answers
5k views
Rebuilding the transaction log
We have a very large database (~6TB), whose transaction log file was deleted. We have tried:
Detaching and reattaching the database; and
Undeleting the transaction log file
but nothing has worked ...
5
votes
1answer
518 views
Why do we need to rebuild and reorganize Indexes in SQL Server
After searching the internet i couldn't find the reason for
Why do we need to rebuild and reorganize indexes in SQL Server ?
what does internally happens when we rebuild and reorganize ?
An ...
3
votes
0answers
207 views
TempDB Contention with 24 physical tempdb files on 48 core AMD Opteron box
I just received the following notification about an instance of SQL Server 2005. The instance has a core-to-tempdb-file ratio of 2:1 for a total tempdb file count of 24 files. Contention should not ...
0
votes
1answer
99 views
SQL Server Mirror failover on failing storage
Recently we had an issue with a sql server 2008 r2 HA mirror. I have tried to reproduce this in a lab environment and i came to the conclusion that i am missing something OR something is not possible.
...
1
vote
1answer
227 views
Audit 'sa' login in SQL Server 2005
I have a requirement where I need to audit (and potentially be notified) whenever the 'sa' user account logs into an SQL Server 2005 instance. Additionally, there is a preference that the database ...
2
votes
2answers
270 views
Executing query is too slow when accessing SQL Server located in an other country
I'am doing an investigation about a slow running query (see below info) when accessing our production SQL Server located in an other country. Now I'm looking for tweaks on how to solve this and make ...
1
vote
1answer
220 views
Performance of SQL Server Triggers
I'm coming from an Oracle/PostgreSQL background and have a hard time coping with the limits of SQL Server's trigger implementation.
I want to update two different datetime columns if the value of a ...
