This tag is for the 2005 version of Microsoft's SQL Server.
3
votes
1answer
94 views
Selecting 'edge' records in a sequence of data
I have a table of items which run sequentially by date with both a start and end for each entry:
ID | Start | End
----------------------------------------
1 | 2012-08-20 00:00 | ...
1
vote
1answer
122 views
Problems on filegroup size — SQL Server 2005
consider FG_january_2011 file group contains 100000 records. and FG_january_2012 file group contains 2000 records.
update Table_Name set date=dateadd(yyyy,+1,date) where Year(date)=2011
if i execute ...
1
vote
2answers
129 views
How do I create a rule for a Primary Key
I am using SQL Server 2005 and I need to create a primary key with an alpha character and 3 numbers together:
p001
b201
I know I need to create a rule and bind that rule to the primary ...
9
votes
4answers
348 views
Is shrinking a transaction log OK to do on a live database?
In Microsoft SQL Server 2005, is it OK to shrink the log file of an online database, or will it cause an interruption of service?
3
votes
3answers
225 views
Performance of Inline-TVF vs. Views
I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together ...
0
votes
2answers
315 views
User defined function performance disadvantages
I have a database with a lot of UDFs that are called by a long running process involving lots of data manipulation and calculations.
My thinking in using UDFs is to separate out logical units of ...
2
votes
1answer
150 views
SQL Server 2005: Full-text search on a multi-lingual table
I have an articles table as follows.
+-----------+--------------+---------+
| uid | int | PRIMARY |
| articleId | int | UNIQUE |
| lang | varchar(2) | UNIQUE |
| ...
-2
votes
1answer
168 views
Invalid database file version error in SQL Server 2005 [closed]
I am experiencing this error message on my SQL Server:
Invalid database file version
The .MDF in question was working previously.
How can I fix this because the information in the file is very ...
3
votes
2answers
186 views
which one is more efficient query?
I have a table called STUDENT
+-----------+-----------+-----------+---------------+
| StudentID | FirstName | LastName | EnrollmenDate |
+-----------+-----------+-----------+---------------+
| ...
9
votes
3answers
3k views
What can cause a mirroring session to timeout then failover? SQL Server 2005
We have two production SQL Servers running SQL Server 2005 SP4 with cumulative update 3. Both servers run on physical machines that are identical. DELL PowerEdge R815 with 4 x 12 core CPUs and 512GB ...
3
votes
2answers
213 views
SQL Server 2005: Full-text search space requirements
Is there a way to calculate the physical drive space required for a full-text search catalog? Space is cheap, but I'd like to know what I'm getting into.
I'm looking at a table of about 200 articles ...
1
vote
1answer
44 views
SQL Server 2005: Full-text search catalog design
I'm new to full-text searches, and am curious about how to properly design the full-text catalogs. I have an "articles" table. I would like to do full-text search on both the "title" and the "body" ...
1
vote
1answer
89 views
group by and having query need help?
I am using the pub database in SQL Server 2005. Which stores have at least 5 orders? Should diplay the store name and store id. I have come up with a query something like this:
select
...
2
votes
1answer
96 views
Can I move data and full text search files to another drive and change the drive letter?
My server's G: drive is rapidly running out of space. It mainly contains data files (*.mdf) for both system and user databases. The FullText Catalogs also exist on that drive.
IT added a much lager ...
3
votes
3answers
10k views
SELECT DISTINCT on one column, while returning the other columns?
I have a query which uses three lookup tables to get all the information I need. I need to have DISTINCT values for one column, however I also need the rest of the data associated with it.
My SQL ...
1
vote
1answer
127 views
transactional Replication of millions of updates
I have standard transactional replication setup from 2005 to 4 2008 subscribers. One of the tables that is replicated is updated several times from an agent job resulting in 6 million updates ...
5
votes
3answers
391 views
Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance
We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like
PK BIGINT
ID VARCHAR(50)
Col1
Col2
etc
So we need to insert a row in the ...
2
votes
4answers
301 views
SQl Server Password Change after installation
I have installed SQL Server 2005, but at the time of installation I forgot to change my password. Now I can access my database only in window authentication mode, and I want to access the database in ...
6
votes
2answers
343 views
SQL Server database : moving from 2008 to 2005 - encrypted procedure issue
I could normally use generate scripts / DTS to move data and tables etc. However one of the databases has a large amount of encrypted procedures that I can't script to move.
Neither do I have older ...
1
vote
2answers
288 views
Slow queries on SQL Server [closed]
We have SQL Server 2005. Our main table is the archive table which has nearly 200 million rows in it. There are 2000 clients that connect a service so the service writes the information to the ...
3
votes
1answer
246 views
Send data from SQL to a serial port or IP address
I've been asked to integrate SQL transaction data with a video monitoring system in real-time. The video system's application is only capable of receiving data by listening on a serial port or a ...
1
vote
0answers
67 views
summary column in ssrs2005 matrix
Hi I create a monthly sale report. there is a week-to-date column at the end of each week, then the end of report, I need to a month-to-date column. The the column will display date and the row will ...
3
votes
2answers
681 views
How to find out which host / IP address and Program deleted the records from SQL Server
We are using SQL Server 2005. In our database some records are deleted. I want to know how these records are deleted like from which system (Host name / Ip address), program and date&time of ...
5
votes
2answers
103 views
New Cluster Testing - Best Practice
We've finished setup of a 4-node SQL Server 2005 cluster. We are using Windows 2008 R2 as the underlying OS.
We are looking for suggestions on a set of tests we could perform to test failover of the ...
4
votes
1answer
1k views
MS SQL Server - Checkpoint process stuck in SLEEP_BPOOL_FLUSH
We seem to have a problem that came out of nowhere. The checkpoint process is stuck in SLEEP_BPOOL_FLUSH state, and not actually reducing the usage in the log file.
This never seemed to happen ...
4
votes
4answers
188 views
Can I reorganise index on a sql server 2005 database without performance hit on production server
I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. ...
2
votes
0answers
93 views
CREATE ASSEMBLY - EXCEPTION_ACCESS_VIOLATION
I am trying to create an assembly on my SQL Server 2005 server. Here is what I am using:
CREATE ASSEMBLY [SQLCLRTest]
AUTHORIZATION [dbo]
FROM 'path\to\my\file.dll'
WITH PERMISSION_SET = ...
2
votes
1answer
59 views
Full Text search returns duplicate keys
I have a search like the example below. The problem is that the result returns a key muliple times if the term "beach" is in myTextColumn multiple times, for example "I went to the beach because the ...
6
votes
2answers
289 views
How do I create a user for SQL Server whose queries (any/all) are time limited?
Is there any way to create a database user in SQL Server 2005 so that none of their queries are allowed to exceed a certain time limit (for e.g 20-30 seconds) or all queries are limited to a certain ...
5
votes
2answers
126 views
Unique Contraint/Index Based On Values
I have a table that has the following column definitions:
ID (INT, PK)
Name (VarChar)
Active (Bit)
Bunch_of (Other_columns)
Question: I want to have a Constraint on Name/Active such that we can ...
3
votes
1answer
83 views
What is recommended level of security for a production database?
I have a production database on our local server. Currently there are two many logins to the database, about 20, half of them are windows authenticated and the other rest are SQL Server authenticated. ...
3
votes
2answers
246 views
sp_send_dbmail fails when mailing queries, but no error returned, query just hangs
I have an issue SQL Server 2005.
sp_send_dbmail fails when mailing out with the result of a query (inline or attatchment).
No error message is given / reported, the query just keeps executing but ...
14
votes
5answers
3k views
What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?
I'm in a situation where I want to get the minimum value from of 6 columns.
I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like ...
1
vote
1answer
341 views
SQL Server Log: The database 'master4IDR' is marked RESTORING
Why SQL Server log showing following message. I am dead sure, there is no such db on our production server naming "master4IDR"
{The database 'master4IDR' is marked RESTORING and is in a state that ...
2
votes
1answer
65 views
Need a solution on dataset as given below
Data Set:
col1,col2,col3,col4.col5
(A,b,c,d,e)
(A,z,p,q,r)
(F,s,t,y,v)
(F,w,x,y,z)
(F,p,x,r,t)
(G,m,n,o,p)
(G,m,i,q,s)
Actually I need result set that has all the columns and only single row for ...
1
vote
1answer
92 views
Follow up to certificate sharing question
Follow up to the post Sharing certificates encryped by password between DBs and instances
I created a certificate with a password
CREATE CERTIFICATE testcert
ENCRYPTION BY PASSWORD = 'test123'
...
2
votes
6answers
297 views
Taking backup of all the databases in SQL Server
I have a Microsoft SQL Server 2005 DB server. In the DB server, I have around 250 user databases. I have to take a back up of all these databases. Since, manually taking backup consumes lot of time, I ...
3
votes
2answers
250 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 ...
1
vote
0answers
1k views
Error Authenticating Proxy Account when Executing SSIS job
I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages.
These packages have been running for years and the associated steps run via a Proxy Account ...
2
votes
0answers
185 views
I/O is frozen on database databasename
On SQL Server 2005 log report, everyday I receive the following message:
I/O is frozen on database databasename.
Is it serious ?
How to resolve it ?
3
votes
1answer
437 views
SQL Server has encountered 1 occurrence(s) of cachestore flush
On one of our production server log files, the following message is being observed on daily basis. What does it mean? Is it a serious issue?
SQL Server has encountered 1 occurrence(s) of ...
4
votes
1answer
3k views
How can I strip non-numeric characters out of a string?
Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type.
One ...
-3
votes
1answer
215 views
Single query to copy data from 3 tables into a single empty table
In an interview today, I was asked if I could write a single query to copy data from 3 source tables to an empty destination table.
I started saying "I will use a temp table or table variables", ...
2
votes
4answers
158 views
How does the database decide which Index to use
DDL
create table t
(
id int,
id1 int
)
create index Example_Index
On t(id,id1)
create index Example1_Index
On t(id1,id)
DML
insert into t(id, id1)values(1, 100)
insert into t(id, ...
4
votes
2answers
523 views
SQL Server Memory Paging Issue
We have a dedicated SQL server running Windows Server 2003 R2 Standard x64 SP2 and SQL Server 2005 (64-bit) Standard Edition with 16GB RAM. Every night we're experiencing severe performance issues ...
1
vote
0answers
101 views
ClientHost for Logon Trigger showing NULL values [closed]
I am trying to create a Logon trigger, in SQL SERVER but
EVENTDATA().value ('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)')
is returning NULL values. Why So ?
1
vote
1answer
1k views
Quick look at how much RAM is allocated to SQL Server?
With SQL Server 2005, you could look at the Task Manager and, at least, get a cursory look at how much memory is allocated to SQL Server.
With SQL Server 2008, the Working Set or Commit Size never ...
1
vote
1answer
176 views
What is connection time?
We are currently trialling ManageEngine SQLDBManager Plus. On the dashboard for a server it shows connection time in milliseconds (currently 15ms).
Can anyone tell me what Connection Time represents ...
2
votes
1answer
3k views
CASE statement with IS NULL and NOT NULL
Is there any better way to write the lines below in SQL Server 2005?
CASE
WHEN (ID IS NULL)
THEN 'YES'
WHEN (ID IS NOT NULL)
THEN 'NO'
END AS ID_Value,
9
votes
1answer
332 views
Are there reasons why I should not set my db owner to [sa]?
Yesterday I asked this question regarding changing the dbo of multiple databases that I have. The change makes sense, but I want to be clear.
Is there any, good reason or circumstance why I should ...
