I have a task to perform a new SQL Server 2005 installation at a client.
They have a system with 10 external SCSI drives, each of 72GB. They only have one database of 80GB in use at this system.
1) Userdata: I think that I will put 5 disks at SCSI channel 1, with RAID 3 or 6, with formatted space of 140GB.
2) Log: I think that I will put 3 disks at SCSI channel 2, with RAID 5, with formatted space of 70GB.
3) TempDB: I think that I will put 2 disks at SCSI channel 3, with RAID 0, with formatted space of 140GB.
System already has two built-in drives (RAID 1) for operating system, where I think I will put system databases.
I am working with a client who has all databases, logs, tempdb and user/system databases on a single raid set. In order to make some speed improvement, we have decided to move log files to a second raid controller card.
To accomplish this, we are thinking about taking these steps:
1) Detach database XYZ. 2) Stop database service. 3) Create a new partition on new controller card. 4) Copy old log files from I: to new K: drive. 5) Remove drive letter I: from system. 6) Change drive letter K: to I: 7) Attach database
My question is, does SQL Server recognize the log files automatically, since they are placed at same logical position (i: drive)?
If an issue is resolved please note that the problem has been resolved. Because there are "many ways to skin a cat" it would be helpful to anyone else with a similar problem (or someone trying to learn) what the solution was.
Hello all, I have 2 primary key fields the ssn and refnum... if the data in the file is duplicated it will not import to my table rights even though i am using DTS to do my import, correct? or do I need to add an extra validator in there?
We have a project at work that requires us to run an msde database from cd. My sup. and I disagree on if this can be done or not. We currently have an application written in asp that uses an access db. We copy the *.mdb file and all the asp files to cd and use the app. to query the db. Now, we have a client who wants the same capability only using msde. They will not allow us to install the full version of sql on there machines. No one in the dept wants to re-write this app. so we're trying to figure out if we can make it work with msde. So, My first question is, does anyone think that it is possible to just copy the *.mdf file to cd (mind you WITHOUT the *.ldf file... can't write to a cd)andMy second question is, can a sql db run without both files in any situation(mdf,ldf)? (I believe this is impossible, but would like to here it from someone with more experience)Any comments are greatly appreciatedthanks
I am about to write my registration confirmation tables. Right now I have my registration form and table "Users" that receives input from user registration.
I want to do email confirmation of a registered account. I was thinking of doing this by creating a table "Verify" that is set to 0 if not verified and 1 if the user verifies his email address.
Then I started thinking about the original registration information. If a user "doesn't" verify, then there is all this dormant data in the "Users" database. So, maybe it is better to write all the registration data to the "Verify" table and then have SQL move that data to the "Users" table upon verification? This sounds a bit sloppy to me... is this how the email verification of data is done or, if not, can somebody suggest a best practice?
I need confirmation from you SQL Server experts out there. Please let me know if the following works. Thanks!
This stored procedure gets a value and increments by 1, but while it does this, I want to lock the table so no other processes can read the same value between the UPDATE and SELECT (of course, this may only happen in a fraction of a second, but I anticipate that we will have thousands of concurrent users). I need to manually increment this column because an identity column is not appropriate in this case.
BEGIN TRANSACTION
UPDATE forum WITH (TABLOCKX) SET forum_last_used_msg_id = forum_last_used_msg_id + 1 WHERE forum_id = @forum_id
SELECT @new_id = forum_last_used_msg_id FROM forum WHERE forum_id = @forum_id
Hi to alll of you, i'm working in a project to save cars information, when the user who adds on the new record enter all the data, this will need to be printed with a particulary number, which needs to be unique, (lets take a passport number as an example) this generated number will takes some info from the filling fields, for example:
How can we say whether the SP is successfully compiled or not if we are compiling it on the server as a part of the TSQL script since it does not throw any message like ORACLE does.
In oracle, system will let you know whether the the procedure is successfully complied or not?
How can I check "Native client" is present on my client? I'm trying to eliminate all the reasons my client won't run a VC++ app which access a sql server.
I have set up my confirmation system to work as thus:
1) user registers 2) registration goes into a temp table with a code 3) user gets a MD5 code in his inbox 4) user clicks back 5) click-back page moves registration data from TEMP table to USER table and sends the user to the signin page.
6) ...
Now, my question is - how do I handle this final step in account confirmation? Should the first signin act as a final confirmation of the users account? This makes sense. Should the data in the USER table self-delete after a day if there has been no first sign-in? Should I have a column in the USER table to show if the first login has happened? How should I do this?
I am sure I could mess around with this but it would be great to get feedback from somebody that has done this multiple times and has a sense of what the best practice is (based on large volume examples).
I am looking for some confirmation on a behavior of the SSIS Script Task. I have a custom script task that takes an input file, and archives it after it has been processed into the database.
When I run this package in the Visual Studio GUI, if the destination drive is full, it throws an exception telling me that there is not enough disk space. So, my questions are:
1) If this happens when the package is running through the command line, would this exception still be thrown? (I am thinking it will be)
2) Also, Do I need to explicitly fail the script task in the event handler, in order to ensure this .Net exception being thrown will cause the component to fail. (I am fairly certain I do, since this is what I had to do inside of the Visual Studio GUI, but does anyone know if this same behavior would occur when running from the command line?)
I have discovered what looks like a bug in the optimiser. I've posted it at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288243 but I wonder if any of you with SQL 2005 RTM, 2005 SP1 or 2008 CTP could confirm when this was introduced and whether it is still an issue?
Code Snippet
-- Bug report
-- 2007/07/19
-- Alasdair Cunningham-Smith
-- alasdair at acs-solutions dot co dot uk
set nocount on
go
-- example date in in British date format
set dateformat dmy
go
use tempdb
go
create table foo( bar varchar( 30 ) not null )
go
insert into foo( bar ) values ( 'fishy' )
insert into foo( bar ) values ( '19/07/2007' )
go
-- this works fine in all versions - only valid dates are passed to the convert function
select
convert( smalldatetime, bar, 103 ) as bardate
from
foo
where
bar like '__/__/____'
go
-- this works on SQL 2000, but fails on SQL 2005 SP2 (I've not tried other SPs of SQL 2005):
-- Msg 295, Level 16, State 3, Line 2
-- Conversion failed when converting character string to smalldatetime data type.
--
-- I believe the query is rewritten as if the derived table query contained
-- "and convert( smalldatetime, bar, 103 ) < getdate()"
-- which would expose the convert to the invalid data
select
*
from
(
select
convert( smalldatetime, bar, 103 ) as bardate
from
foo
where
bar like '__/__/____'
) as derived
where
bardate < getdate()
go
-- Workaround:
-- Use a case statement to protect the convert operator from the invalid data
select
*
from
(
select
case when bar like '__/__/____' then
convert( smalldatetime, bar, 103 )
else
null
end as bardate
from
foo
where
bar like '__/__/____'
) as derived
where
bardate < getdate()
go
drop table foo
go
The workaround I discovered is simple but ugly. I invite your comments...
