Sql Assistant

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; }

bulk insert into sqlserver using textfile Bulk insert sql server 2008 example I find so many issues while inserting bulk record in database.. Need to create temp table than I has to map all fields which is in file and in table than Insert it in to actual table but here is one Master command to insert record in bulk through simple statement.. Step 1 ) Create Table. create table AddressBook (id int,name varchar(50),address varchar(50)) Step 2) Create Text file with “,” delimiter. 1,John,India 2,Raj,India 3,Sonal,India 4,Roshan,India Step 3) Bulk insert record in to AddressBook table bulk insert AddressBook from 'c:\Addressbook.txt' with ( fieldterminator = ',' , rowterminator = '\n' )

SQL - ISO Country Names and Codes This document provides the necessary SQL statements for ISO 3166 country names and two and three letter codes. This page and the data are in UTF-8 encoding. Create Country Table Table is called base_country as it follows Edoceo naming conventions, change as necessary. This is for a PostgreSQL database, CREATE TABLE statements may need to be altered for your RDBMS. create table base_country ( id integer primary key, iso2 char(2), iso3 char(3), name_en varchar(64), name_fr varchar(64), name_de varchar(64) ); Create indexes as necessary, which may not be as this table is so small. Insert the Country Data This SQL should work on any SQL-99 compliant RDBMS. INSERT INTO base_country (id, iso2, iso3, name_en, name_fr) VALUES (1, 'AF', 'AFG', 'Afghanistan', NULL); INSERT INTO base_country (id, iso2, iso3, name_en, name_fr) VALUES (2, 'AX', 'ALA', 'Åland Islands', NULL); INSERT INTO ba

We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles. Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced. CREATE PROC dbo.ProcName AS SET NOCOUNT ON; --Procedure code here SELECT column1 FROM dbo.TblTable1 -- Reset SET NOCOUNT to OFF SET NOCOUNT OFF; GO