I'm about to begin studying for the MCDBA certification and I'm
wondering what books people would recommend?
The following gets a good review, so I'm planning on purchasing it:
MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000
System Administration, 70-228, Second Edition
However, I'd like to also go through a second book as well, just to
make sure my coverage is as complete as possible, and I'm wondering if
anyone has any suggestions?
Hi All, I am new to SQL Server but have been doing database programming since last 3 years. I recently attended MOC (Microsfot Official Curriculum) training on SQL Server and have started to use at my company. I am comfortable with SQL but want to dig deeper into T-SQL side. I searched on the Internet but not many good books available in that either they are ranked very low or are very old i.e. written around 1999/2000 or covers SQL Server 2000 as a whole. Can anybody suggest me any T-SQL book which was written recently and focuses purely or majorly on T-SQL?
Thanks to all for your time and advice in advance.
Well, I just start to install .Net into my computer and same time I need SQL Server 2000 as well. I went to microsoft.com and noticed there are couple of files called "Microsoft SQL Server 2000 Service Pack 3a", "MSDE" ..., are they only the update file for sql server 2000? And where I can get a FREE one from Internet?
I've been working with SQL for close to a year now and I'm wondering if its worth the time and money to become certified. Are DBA's still on the up and rising or another job that will be outsourced to India?
I am wanting to take the 70-228 and 70-229 exams. Does anyone who has already passed them have any recommendations for the best / most complete books for studying them? Opinions seem so varied on Amazon...
Is it worth studying towards an MCDBA (hopefully going to get myemployer to pay for it) ... has anyone completed it in the UK? if sowhat learning methods did you use(ie Boot Camp, elearning etc)? Andwho do you recommend for the training course?ThanksChris Longstaff
Anyone take (and pass) the MCDBA exams? If so, do you feel the experience was worth it? I'm most interested in what I can learn. Do you feel that it's a respected certification (like an MCSE)?
Is MCDBA worth of the time and money one has to put into to acheive it? I am talking in terms of getting a job. Do you think after getting MCDBA you have gained sustantial knowledge about the subject?
I havent appeared for any of the MS exams as yet but would like to start with 70 228 and 70 229.
I like reading BOL, but i guess for certfn exams you have to have a diff approach...
For my last 3 years I have work with the SQL 2000 as a database analysis. Now I want to get a certification to prepare me to look for a better job. Which certification is more valuable in today job marker MCTS or MCDBA.
I'll be taking this test and was hoping for some input, please. 1. How long is it ? 2. Anything in particular I should look out for ? 3. Should you only spend a certain amount of time on each question ? 4. Any place I can find any coupons for the heavy $125 fee ? 5. What company did you sign up through ? 6. Any other input, would greatly be appreciated !!
Hey guys, Im wanted to know what was the best training software to pass the mcdba cert or better yet, what is best software for exam 70-228 which is the first exam to take. Classes are just too expensive right now for me=( I am open for suggestions. Thank Guys
I am certified Oracle DBA/Developer/Internet Developer. Now, I am interested in Microsoft Certificated DBA (MCDBA/MCSD).
By the way, I don't want to waste money to purchase the Microsoft exam software (Self Test Software) and training material. So, I want to exchange the MCDBA exam software and training material with you by using Oracle OCP 8i, Developer 2000 Rel 2.0 and MCSE 2000 Self Test Software.
I can use Oracle 8iDBA, Oracle Developer and MCSE 2000 self test software listed below to exchange 70-228,70-229,70-215,70-216,70-175 or other MCDBA 2000 and MCSE 2000 self test software and training materials.
<Oracle 8i DBA> 1Z0-001 Introduction to Oracle: SQL and PL/SQL 1Z0-023 Oracle8i: Architecture and Administration 1Z0-024 Oracle8i: Performance Tuning 1Z0-025 Oracle8i: Backup and Recovery 1Z0-026 Oracle8i: Network Administration
<Oracle Developer> 1Z0-101 Develop PL/SQL Program Units 1Z0-121 Developer/2000: Build Forms I 1Z0-122 Developer/2000: Build Forms II 1Z0-123 Developer/2000: Build Reports
<MCSE 2000> 70-210 Installing, Configuring and Administering Microsoft Windows 2000 Professional 70-215 Installing, Configuring, and Administering Microsoft Windows 2000 Server 70-216 Implementing and Administering a Microsoft Windows 2000 Network Infrastructure 70-217 Implementing and Administering a Microsoft Windows 2000 Directory Services Infrastructure 70-219 Designing a Windows 2000 Directory Services Infrastructure - (Includes 4 Case Studies)
By the way, if you have any one of 70-228,70-229,70-175,70-176,70-100, I can use whole set of Self test software to exchange yours. Please provide email accounts with at least 5MB space for exchanging Self Test Software.
Thanks and Regards,
Jack OCP DBA/Developer/Internet Developer jacklam@tom.com
ApplicantID FirstName LastName CompanyName Line1 Line2 City
State Zip PhoneNum
Owner
OwnerID FirstName LastName CompanyName Line1 Line2 City
State Zip PhoneNum
Now i know what im doing with the applicantID and ownerID...but the BZAcase# is a number/unique identifier that looks like this....2007-VU-000, 2007-VU-001, 2007-VU-003....so my question is 1. how do i get the last three numbers to increment each time a new application is created? 2. how do i retrieve the last record in the table??? 3. Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"???
Background: We are changing the way we pay commissions to our rep groups. We used to pay when the order was placed, now we want to pay when the invoice is paid.
Problem: The commision information is currently stored in the customer order, not in the invoice. These orders get deleted a couple weeks after the order was completed (shipped).
I want to create another, rather dynamic, table/structure that will store the order number and the commission percentage.
This info in this table should:
Be deleted: if the order has been deleted and the invoice either does not exist or was payed some period of time ago (maybe 6 months)
Be updated: if the customer order has been updated (i.e. the commission was changed)
Be inserted: if the order exists but the order number is not in the new table.
hi i have written a procedure for stock report. its working fine. please go through the sp and give me some Suggestions. please tell me where i need to improve my code. thanks
Note: User is required to execute this procedure daily. i am taking the sum of issues,purchases,returns,physical adjustments for each and every product from last updated date to today's date and storing it in a table i,e stock_Dump. from this table i generate the date wise stock report
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
CREATE PROCEDURE dbo.spUpdateStock @strReturn varchar(70) output AS BEGIN declare @maxDt smalld atetime if exists(Select * from Stock_Dump where Txn_Date= Convert(varchar,Getdate(),101)) BEGIN set @strReturn='Stock Table already generated for the day. cannot generate it again' END
ELSE BEGIN TRUNCATE TABLE Stock_Dump_Temp select @maxDt=max(Txn_Date) from Stock_Dump /* insert (Opening stock) Closing stock for all all the products from last max Date*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date, Closing_Stock as Opening_Stock , 0,0,0,0,0,0,0 from Stock_Dump Where Txn_Date=Convert(varchar,@maxDt,101) /* Issues*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0, Sum(Qty) as Issue_Qty,0,0,0,0,0,0 from Issue_Details Where Issue_No IN(Select Issue_No from Issue_Hdr Where Issue_Date > Convert(varchar,@maxDt,101) and Issue_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Goods receipt*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, Sum(Qty) as Purchase,0,0,0,0,0 from Dlv_note_Details Where Dlv_Note_No IN(Select Dlv_Note_No from Dlv_Hdr Where Dlv_Note_Date > Convert(varchar,@maxDt,101) and Dlv_Note_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Rejection after receipt*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,Sum(Qty) as Rejected,0,0,0,0 from Rejection_Details Where Rejection_No IN (Select Rejection_No from Rejection_Hdr Where Rejection_Date > Convert(varchar,@maxDt,101) and Rejection_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Issues returns*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,Sum(Qty) As Issue_Returns,0,0,0 from Issue_Return_Details Where Issue_R_No IN(Select Issue_R_No from Issue_Return_Hdr Where Return_Date > Convert(varchar,@maxDt,101) and Return_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Physical Stock + */ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,0,Sum(Var_Qty) as Phy_Qty_P,0,0 from Physical_Details Where Var_Qty>0 and Txn_No IN(Select txn_No from Physical_Hdr Where Txn_Date > Convert(varchar,@maxDt,101) and Txn_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Physical -*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,0,0,Sum(Var_Qty) as Phy_Qty_M,0 from Physical_Details Where Var_Qty<0 and Txn_No IN(Select txn_No from Physical_Hdr Where Txn_Date > Convert(varchar,@maxDt,101) and Txn_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* insert all the records into actual table i,e Stock_dump from Stock_dump_temp (temporory table)*/ INSERT INTO Stock_Dump Select Product_code,Txn_Date, Sum(Opening_Stock) as Opening_Stock,Sum(Issue_Qty) as Issue_Qty,Sum(purchase) as Purchase,Sum(Rejected) as Rejected,Sum(Issue_Returns) as Issue_returns, Sum(Phy_Qty_P) as Phy_Qty_P,Sum(Phy_Qty_M) as Phy_Qty_M,0 as Closing_Stock from Stock_Dump_Temp Group By ProducT_Code,Txn_Date /* update closing stock*/ UPDATE Stock_Dump Set Closing_Stock=abs((Opening_Stock+Purchase+Issue_Returns+Phy_Qty_P)-(Issue_Qty+Rejected+Phy_Qty_M)) Where Txn_Date=Convert(varchar,Getdate(),101) /* delete unwanted records */ DELETE From Stock_Dump Where Opening_Stock=0 and Issue_Qty=0 and Purchase=0 and Rejected=0 and Issue_Returns=0 and Phy_Qty_M=0 and Phy_Qty_P=0
set @strReturn='Stock Table Update Successfully' return END
END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I have a database which contains more than 20000 stored procedureswhich were created withansi nulls off. This i found out using the querySELECT name,AnsiNullsOn FROM(SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOnFROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0Is there any way that i can set this property to 1 for all the storedprocedures i have??I know the alternate method is to drop the procedure and execute thescripts again with AnsiNullsOn = 1.Is there any other simple ways?? It will be very helpful for me..
Now I have lets say VISID 1 to 50. I'm using this SP to change the text on a button. Now I have 50 buttons. So I run this SP, then I run this in my vb.net code
Code Snippet Dim constr As New SqlConnection(PVDBConn) Try 'Variable to hold the results Dim results As String = String.Empty cmdUpd = New SqlCommand("SelVis1Name", constr) cmdUpd.CommandType = CommandType.StoredProcedure constr.Open() 'Set results to the value returned from ExecuteScalar() results = CType(cmdUpd.ExecuteScalar(), String) constr.Close() 'Set our buttons text to that value Button1.Text = results Catch ex As Exception MsgBox(ex.Message.ToString) End Try
At any time, when I start my program, I may need to label 10 buttons, or up to 50. Now I will have this number in a text file. Can I grab that number from a text file, and pass it into a SP?
And can I write this SP only once, to work for more than one label per time. Or do I have to write this sp 50 times?
I have a database that will be used by two or more organizations. I would like to use pass phrase encryption to encrypt a couple of columns.
I'm looking for suggestions on how I might set up the db to let the organization change the pass phrase that is used for their encryption?
I don't really want to hard code it into stored procedures or select statements with parameters. I will be using SSL if that should make a difference with what you suggest.
I have two stored procedures (l'll call them P1 & P2). P1, after a lot of processing, creates a temporary table that is used by P2 after an "exec P1" is done. I've separated the logic into two stored procedures because, ultimately, other sprocs will need the output of P1.
I get an error if I use #tempTable as the output table in P1 because it no longer exists after P1 finishes. ##tempTable works, but I'm concerned about concurrency issues. Any suggestions on what construct(s) I should be using?
I'm looking for some help on how i should index this table.
current table has about 500k records in it. the fields in the table are: member_num (varchar(12), not null) first_name (varchar(20), null) last_name (varchar(20), null) ssn (varchar(50), null) address1 (nvarchar(200), null) address2 (nvarchar(200), null) city (nvarchar(200), null) state (nvarchar(200), null) zip (nvarchar(100), null) phone1 (nvarchar(50), null)
all of the fields are searchable through an asp.net webform.
my first stab at this consisted of creating a clustered index on member_num and then creating a separate index for each of the remaining fields.
What I have. I have a spreadsheet that is used in 4 or more locations on a daily basis by 1-3 ppl per locations. The spreadsheet is used to gather Quality Control information. So everyday there are a couple of spreadsheets from each system that is used to generate weekly and monthly reports. This is becoming to much work and I would like to automate the process.
What I have access to. I currently run a Sharepoint 2007 Server for all our collaboration and document needs. I also have the ability to setup any sql server.
What I want. I want the QC techs in each system to be able to upload the data at the end of each day and be done with it. This way they do not have email or do a weekly report. I would prefer to use Sharepoint and create reports weekly and monthly that can be pulled just by going to a site.
I'm knowledgeable in Sharepoint and Excel. I have some skills in VBA. I haven't dealt with SQL any, but willing to learn. Also I'm knowledgeable in Microsoft Access as well.
Any suggestions on how I could accomplish this would be appreciated.
Well, as a VB/VBA applications developer I'm not well prepared for this, but it looks like I will be riding herd on a production SQL Server.
TSQL I know well enough to get along, but where can I get a fast fix on all the logins, security, and process management info? Today we had a DTS package crash overnight and it took me forever to figure out that it had left half a dozen tables locked. (Note that the scripts for the DTS package are being re-written as we speak with use of transactions and NOLOCK.) Meanwhile tech support was handling a whole mess of grumpy users.
Are there any books you would recommend as resources/references? Is there a particular author who is good at writing the stuff you really need to know in English that can be read by a mere mortal like I? I am fond of the Microsoft resources/help files but I'd like to have somthing that holds highlighter and post-it flags a bit better. Not to mention something that focuses more on the beast as a whole rather than the minutia at length.
I have a set of tables with about the same structure
dataID, recordID, 15 other columns
dataID is unique but is never referenced in queries
recordID is one of the most referenced columns but only has a cardinality of about 30%
The current structure has a clustered PK on (dataID,recordID)
Someone suggested reversing the clustered PK to (recordID,dataID) because of the number of references to recordID but that didn't seem to boost performance any
After staring at this for a while I came up with something but I'd like some advice whether it makes sense or not.
create a non-clustered PK on dataID create a non-unique clustered index on recordID
Let me know if any other information is needed. Thanks
I would be teaching an applied database course to buisness majorundergrads. I'm looking for a book that introduces database conceptsusing SQLServer as the database. I would really appreciate if you couldrecommend me a few such books.ThanksNemo
I'm trying to count the number of records in 'game_dates' where thecolumns home_team_id or away_team_id have the same value. E.g., iwant to know the number of records for each team_id where team_id ishome_team_id or away_team_id.I'm doing this in two separate select statements now. Example:SELECT count(home_team_id),home_team_id FROM gamesWHERE league_id = 218 and ((home_score IS NOT NULL OR away_score ISNOT NULL) OR (home_score <> 0 OR away_score <> 0))GROUP BY home_team_idandSELECT count(away_team_id),away_team_id FROM gamesWHERE league_id = 218 and ((home_score IS NOT NULL OR away_score ISNOT NULL) OR (home_score <> 0 OR away_score <> 0))GROUP BY away_team_idand then combining the results. Is there anyway to combine these toqueries into one query? ...and have a single result set returned withtwo columns (count,team_id)?Thanks,Glenn
