Just a thought.
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.
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 open for suggestions and improvements!
Peter Larsson
Helsingborg, Sweden
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)?
Peter Larsson
Helsingborg, Sweden
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
View 1 Replies View RelatedHi.
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 thank you.
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
COMMIT TRANSACTION
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:
Cars Brand: BMW (catalog number: 120)
Cars Model: 325 (catalog number: 30)
Year: 2008 (catalog number: 18)
So the unique certification number will takes from the catalog numbers * 20 / 5 for example for cars Brand
for Cars Modelo: catalog number *3 + 15
CERTIFICATE NUMBER: 480-105
I'm not sure if this is possible.
I will realy thanks for your help in this issue.
Best Regards
Gian
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?
Thanks/
Hi
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.
thanks
Z
Hi.
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).
Your feedback is much apprecaited.
Thanks.
Hello,
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?)
Thanks,
Chris
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...
alasdair.
I have the following UNION ALL query with SELECT INTO @tblData temp table. I would like to confirm if my query is correct.
In my first SELECT statement, I have INSERT INTO @tblData.
Do I need another INSERT INTO @tblData again in my second SELECT statement after UNION ALL?
DECLARE @BeginDate as Datetime
DECLARE @EndDate as Datetime
SET @BeginDate = '7/1/2015'
SET @EndDate = '7/13/2015'
DECLARE @tblData table
[Code] ....
oK if anyone remembers the problems with my Update/termination Trigger I found it works best if I do it as a Stored procedure instead of a trigger. Thanks again for your help guys. I really appreciate it
View 3 Replies View RelatedHi:
I create a DTS package in 'Copy SQL Objects Task' to transfer 3 stored procedures from my local SQL msdb to remote testing msdb database.
Execution is 'successful' but the destination server msdb does not have the 3 stored procedures.
Any hint on this 'vanished' copy objects?
thanks
-D
Hi expert,
I use a SQL Job that makes use of the BACKUP command. How can I check this job success or not?
Thanks
Hey all
Which system variable do I use if I would like to transfer success message into an SQL table? I'm running a task to import data into an SQL table. And I'm using the System::ErrorDescription variable (Execute SQL task) to insert error messages into a table but I would really appreciate it if someone could tell me what system variable would insert 'success' message into an SQL table. Tried a lot of searching on the net but couldn't find anything.
Many thanks,
Rupa
Has anyone here been involved in a successful commercial Data Warehouse project?
If so, what was your role? How big was the project? Did you do it all or was their one guy data modelling, one for ETL, one for Client-side, one project managing, etc...? What tools did you use? Was it in SQL2000? 2005? Did you use DTS/SSIS for your ETL or Sprocs? Did you use SSAS? Were you writing lots of MDX to meet client-side needs? Or did you use a completely different toolset for all or some of this?
Did you use the Kimball approach? Or were you in the other camp?
Was your latency nightly or did you have some type of "real-time" capability?
I would prefer to hear from people who've actually been involved in successful projects (or failed projects if you would like to share with us why you think they failed). I'm not too interested in hearing from people that have never done it but have a theory as to how it should work (like me).
Many Thanks,
Coolerbob
(Or is there some other website forum I should be asking this question on? (like mdxteam, olapteam, datawarehouseteam or something)
Is there a parameter allowing to verify successful execution of a stored procedure?
Something that can be run as the last line in a stored procedure to verify its success...?
Thanks!
Hi Guys, basically I have a scheduled job in MSSQL7, which on completion e-mails one of the operators.....
How can I get this job to email more than one operator upon completion???
Cheers....
Trying to delete records associated with the same contact id from all the tables used for that contact. But I'm getting an error. Strange error because the table does exist and I've been able to do single deletes from that table. So, something tells me I'm not getting something right in my SQL statement. How do I delete records from all 3 tables using the same id (as opposed to doing 3 different SQL calls, one for each)?
-----------------
DELETE names, addresses, phones
FROM names AS n, addresses AS a, phones AS p
WHERE n.id = '12' AND a.id = '12' AND p.id = '12'
Error: Unknown table 'names' in MULTI DELETE
-----------------
(BTW, how do tag my code so it shows up in a code div?)
I apologize in advance for what is probably a really dumb question.
I have an SSIS package that is executing a number of data flow tasks simultaneously, and then all are followed by a single script step. I'm trying to get all the constraint arrows to look uniform by having them exit the DFTs on the right side and enter the script step on the left. Unfortunately, BI Dev Studio is putting the exiting arrows on the bottom, which looks awful and is difficult to follow. I can only move them between specific points on the bottom of the step. How do I move those arrows to the right side?
Thanks in advance.
How to use Xquery to get the success value and the result value from the following XML.
This XML is stored in a SQL Table XML field, i want to be able to get all the success and reslut values for all the records:
Here is my XML
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:ns1="https://data.abc.biz/mysoappathtest/?soap:customcalls:v1"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
[Code] ....
I am trying to get the result = 15810 and the Success value = true
My ssis package errors out because one of the database connection failed. I successfully logged error but also indicated that package finished successfully. My confusion is if a sheduling software schedules this package, what would be return code sent by dtexe... . would it be success or failure? In this scnerio i want it to return failure so that appropriate team can be contacted.
thanks,
kushpaw
Hello again to everyone,
Because of aleatory problems some of our Analysis Services Tasks fails and sometimes success with not know reasons. You run the task and it fails, the next time it fails, the next time is success, and so on aleatorily.
The solution of this is to put those tasks in a loop (ore someting similar) that only ends if all of the Analysis Services Tasks succeeds. If not, those tasks starts again. Do you know what I mean? How can I build this? I have investigated with checkpoints, with For Loops ... but still I dont find the way to do it.
Does anybody can help me? I suppose it's not so difficult, but I'm stuck in finding the solution...
Thankx once again.
JohnSSIS
I have an ActiveX Script Task in SQL Server 2000. It chooses one of two
possible success-paths depending on if a file exist or not. (Part of the old ActiveX Script for choosing next step is below)
I need to rewrite this for a Script Task in SQL Server 2005 but it seems like it
doesn't have this functionality and objects.
Does anyone know how to write the code for choosing the next step in a
Script Task or knows another way to solve my problem?
The package is not suppose to fail if the file is missing, it's must succeed and that's why I need two success-paths.
Regards,
Sara
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main ()
Dim pkg
Dim stpContinuePkg
Dim stpExit
SET pkg = DTSGlobalVariables.Parent
SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_4")
SET stpExit = pkg.Steps("DTSStep_DTSActiveScriptTask_21")
If ...... Then
Main = DTSStepScriptResult_ExecuteTask
stpContinuePkg.DisableStep = False
stpExit.DisableStep = True
Else
.......
End If
Main = DTSTaskExecResult_Success
End Function
I export all tables from serverA databaseAA to serverB (both SQL2k sp3)databaseAA with 'Success', but without any relationships. In SQL6.5 Tools of Transfer will bring all objects include relationship.
I also tried export "All Objects" which fails with unclear message. I just don't want to do a backup and restore....
thanks
David
Hi
I am writing a package that i want to have email me on two possible success scenarios.
essentially, this is the conditions:
IF @result = 0
BEGIN
EXEC master..xp_cmdshell @copyfile, NO_OUTPUT
PRINT 'Operation Successful'
END
ELSE
BEGIN
RAISERROR ('This operation failed. Error Code:01. The source and destination files are identical.', 0, 1)
END
If the first condition is met, I want to fire off an email stating success.
If the second condition is met (the RAISERROR) then i want to fire off a different email.
Now, the problem is, I am not sure how to flow it. Both conditions are successful, thus it always fires off the success email if i use an "On Success" flow.
How do i capture the RAISERROR before the email to tell it which email to send?
Does this make sense?
Hi
I am writing a package that i want to have email me on two possible success scenarios.
essentially, this is the conditions:
IF @result = 0
BEGIN
EXEC master..xp_cmdshell @copyfile, NO_OUTPUT
PRINT 'Operation Successful'
END
ELSE
BEGIN
RAISERROR ('This operation failed. Error Code:01. The source and destination files are identical.', 0, 1)
END
If the first condition is met, I want to fire off an email stating success.
If the second condition is met (the RAISERROR) then i want to fire off a different email.
Now, the problem is, I am not sure how to flow it. Both conditions are successful, thus it always fires off the success email if i use an "On Success" flow.
How do i capture the RAISERROR before the email to tell it which email to send?
Does this make sense?
I recently upgrade my Enterprise Manager to Sql 2000 from 7.0; the databases are still SQL 7.0. Since then I've noticed that all scheduled jobs that are calling dtsrun.exe's are reporting successful but do not actually run. I think the total process time is 1 sec.The message from View History reports: "The step did not generate any output. Process Exit Code 0. The step succeeded." Every job that uses the dtsrun.exe reports the same message. I've ran the same command from DOS and it runs fine. I've executed some basic batch commands within the Scheduled Jobs and those fail as well. I can open the DTS package and execute those with success, but when these are scheduled and are executed from the Agent Jobs they fail. Can anyone shed some light on this please?
Thanks,
Jeff
Hi all,
I have a stored proc that uses xp_cmdshell to boot off a batch file on the NT side of the box (box OS is Windows 2000 Advanced Server).
Here is the pertinent code:/*----- Kick off the NT bat job to suck over the data through the web service pipe*/
SELECT @NTCommand = 'D:TradeAnalysisWondaDataStoreJobsPullFromWONDA _InstitutionalRankings.bat ' + CONVERT(varchar(10), @ReqDate, 101)
EXECUTE @e_error = master.dbo.xp_cmdshell @NTCommand
SELECT @m_error = CASE WHEN ISNULL(@e_error, 0) <> 0 THEN (@e_error + 50000) ELSE @@Error END
IF @m_error <> 0 GOTO ErrorHandlerThe trouble is that the batch file is failing (soft error, caught internally to the batch file, which then kills itself, screaming loudly all the way).
The batch file is using the following "voice" in which to scream in pain as it dies (a.k.a., using this code to terminate itself, which kills the cmd shell and returns 13 as an error code)REM WonDBService.exe says we failed
Date /T
Time /T
EXIT /B 13
Meanwhile, back at the ranch (errr...back in stored procedure), what is being returned is a ZERO (in the first code block, @e_error is being set to ZERO when the xp_cmdshell returns from the bat file.
So, now that I have ruled out the obvious *LOL* how can I get my xp_cmdshell to realize it has failed miserably at the one, tiny, simple, not-too-much-to-ask, job that it is designed to do?
Is there some example of how i can add a return status into my stored procedure to have an extra field where I can verify if it runs successfully
1 if success
-1 if error (server, etc)
0 if empty record return
Thanks