Sql Assistant

hi friends, On Today Morning i go through with Custom Error Message in MSSql server. for that i work around below step and it's done man. i found one stored procedure "sp_addmessage" which used for adding my custome error message on server.. USE master GO EXEC sp_addmessage 50002, 10, N'The data %s already exists!' GO RAISERROR (50002, 10, 1, 'MyText') syntax for sp_addmessage is sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg'      [ , [ @lang= ] 'language' ]      [ , [ @with_log= ] { 'TRUE' | 'FALSE' } ]      [ , [ @replace= ] 'replace' ] [ @msgnum= ] msg_id Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647. The combination of msg_id and language must be unique; an error is returned if the ID already exists for the specified language. [ @severity = ]se...

My blog has many practical tips and best pratices for SQL/BI developers,but I haven’t focused on interview questions for SQL/BI developers so far. This might change in the future. It’s been a challenge for many people to break into or stay competitive in the SQL/BI profession. I am very lucky to be able to stay in the profession and also stay in the financial industry. It’s been very rewarding for me to share my experience and knowledge through this blog. Many recruiters do not understand what exactly a SQL/BI Developer does. One thing they assume we don’t do is design. On the contrary, designing from simple table structure to the entire sub-system for staging and ETL is our daily job. In this blog, I’ll share with you one simple SQL design interview question and the answer that will set you apart from other candidates. Interview question: what is First Normal Form (1NF)? In order to give an answer that will earn you an A, we need to relate 1NF to what we do every day first. Memorisin...

hi friends, today i find one of the good feature to find Identity column from table.. go though with this example create Table T1(Col1 int identity(1,5),Col2 int) Go Insert into T1(Col2) Select 100 Go 100 Select $Identity,* From T1 so at here i am creating one table with having two columns one is my identity column and another is used to store value (100) now after creating table at here i am inserting record in it.. Insert into T1(Col2) Select 100 Go 100 Inserting Identity value starting from 1 and seeds to 5 element like 1,6,11,16,21,26 and so on.... and Go 100 is used for looping that insert statement 100 times.. and i am also selecting 100 value at here.. by using $Identity i can come to know about Identity column value.

Question What is the difference between SQLStored Procedure and SQLFunction? Answer 1) A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value. 2) Stored procedure in Sql Server can not we executed within the DML statement.It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement.

The CTE is one of the essential features in the sql server 2005.It just store the result as temp result set. It can be access like normal table or view. This is only up to that scope. The syntax of the CTE is the following. WITH name (Alias name of the retrieve result set fields) AS ( //Write the sql query here ) SELECT * FROM name Here the select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE. CTE 1: Simple CTE WITH ProductCTE AS ( SELECT ProductID AS [ID],ProductName AS [Name],CategoryID AS [CID],UnitPrice AS [Price] FROM Products ) SELECT * FROM ProductCTE Here all the product details like ID, name, category ID and Unit Price will be retrieved and stored as temporary result set in the ProductCTE. This result set can be retrieved like table or view. CTE2:Simple CTE with alias WITH ProductCTE(ID,Name,Category,Price) AS ( SELE...

hi friend,  what is strong password?  Ans :: it contains at least  1 small letter character  1 capital letter character  1 numeric character  1 special symbol  and length of password is between 4 to 8 Character  that is called strong password..  regex for Strong password  ^(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%&?]).{4,8}$ how to work with asp.net regex library  bool IsMatch1 = Regex.IsMatch(passwordString, @"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%&?]).{4,8}$"); thanks  jayeshl  http://sqlassistant.blogspot.com  

Generate Dyanmic Password using Asp.net protected void btnGenerate_Click(object sender, EventArgs e) { string allowedChars = ""; allowedChars = "a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,"; allowedChars += "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,"; allowedChars += "1,2,3,4,5,6,7,8,9,0,!,@,#,$,%,&,?"; char[] sep ={ ',' }; string[] arr = allowedChars.Split(sep); string passwordString = ""; string temp = ""; Random rand = new Random(); for (int i = 0; i < Convert.ToInt32(txtPassLength.Text); i++) { temp = arr[rand.Next(0, arr.Length)]; passwordString += temp; } txtPassword.Text = passwordString; }