Monday, October 31, 2005
URL of that post can be seen here >> http://groups.msn.com/ChennaiNetUserGroup/general.msnw?action=get_message&mview=0&ID_Message=9327&all_topics=0
My response to that post:
There are two undocumented functions in SQL Server (since SQL Server 6.5). They are:
1. Pwdencrypt and
Pwdencrypt -- It uses one way encryption. That is, it takes a string and returns a encrypted version of that string. Pls note that in one way encryption you can't get back the actual string (i.e., you can decrypt the encrypted data).
Pwdcompare -- It compares an unencrypted string to its encrypted representation to see whether they match.
Since it is undocumented functions there is a possibility that MS can remove or change it at anytime without prior notice. So use it at your own risk.
---Sample table to hold the encrypted password
Create Table tstSQLEncryption(EmailId varchar(50),Pwd varbinary(255))
--Encrypt password and save it into the table
Insert Into tstSQLEncryption (EmailId, Pwd) Values ('email@example.com', pwdencrypt('test'))
--Compare the encrypted value and the value you give newly
Select Pwdcompare('test',Pwd ) from tstSQLEncryption where EmailId = 'firstname.lastname@example.org'
If the above select statement returns 1 then it means the value you entered and the value already encrypted are one and the same. Hope this is helps!
Sunday, October 30, 2005
1. Openrowset has new bulk features introduced in SQL Server 2005.
2. Openrowset supports bulk operations through a built-in bulk provider that allows data from a file to be read and returned as a rowset.
3. Using the BULK rowset provider you can load a file into a table's column using regular DML.
4. Unlike SQL Server 2000, instead of being limited to Text, NText and Image datatypes for large objects, in SQL Server 2005 we can also use Varchar(max), nvarchar(max) and Varbinary(max) datatypes. The new MAX option allows you to manipulate large objects the same way you manipulate regular datatypes
5. With OPENROWSET you'll be able to return a rowset from a file as a single varbinary(max), varchar(max) or nvarchar(max) data type value. We'll use "SINGLE_BLOB", "SINGLE_CLOB" or "SINGLE_NCLOB" to diffentiate what kind of single-row, single-column data is being read.
Sample table structure:
CREATE TABLE EmployeeProfile
EmpName VARCHAR(50) not null,
EmpPhoto VARBINARY(MAX) not null
Import image into this table:
Openrowset with the Bulk option requires a correlation name (also known as a range variable or alias) in the FROM clause.
INSERT EmployeeProfile (EmpId, EmpName, EmpPhoto)
SELECT 1001, 'Vadivel', BulkColumn
FROM OPENROWSET( BULK 'C:\Sample.gif', Single_Blob) AS EmployeePicture
I suggest to read http://msdn2.microsoft.com/en-us/library/ms175915.aspx to know more about "BCP, Bulk insert, and Openrowset (Bulk)".
Yago: Before we reach that day, certainly I know a lot of people in high school and college are hearing a lot about how India and China will take over a lot of American jobs. What do you say to that generation of young people now that's in college, that's now in high school or approaching high school?
Gates: India and China advancing and getting rich is fantastic news. What that means is that people who have been living in poverty, had ill health and illiteracy, are now getting jobs that allow them to be educated and realize their potential. If we had a choice today where India and China would be as rich as the United States, we should all want that, because not only would it be great for them, but they'd be buying more of our products. ... Their advancing isn't taking away from a finite pool of jobs. What it does is it grows the global economy. It does mean that we have to renew our skills, renew our leadership, and that largely means investing in the education system. So it doesn't have to be a bad thing, it just highlights that we've underinvested in education and in fact other countries do a better job.
Hmm why did I like it .. might be because I am born and brought up in India.
Code snippet for the sample table:
Create Table tstSQLPaging
Enter sample data into that table:
Insert into tstSQLPaging values (1, 'Vadivel','M','email@example.com',10000)
Insert into tstSQLPaging values (2, 'Sailakshmi','L','firstname.lastname@example.org',9000)
Insert into tstSQLPaging values (3, 'Raj','A','aRaj@yahoo.com',11000)
Insert into tstSQLPaging values (4, 'Dhina','B','bDhina@yahoo.com',25000)
Insert into tstSQLPaging values (5, 'Siddharth','s','email@example.com',6000)
Insert into tstSQLPaging values (6, 'Vicky','L','firstname.lastname@example.org',19000)
Insert into tstSQLPaging values (7, 'Tejas','P','email@example.com',22000)
Insert into tstSQLPaging values (8, 'Revathy','L','firstname.lastname@example.org',4000)
Extract from MSDN ::
Row_number() - Returns a running count of all rows in the specified scope.
With the help Row_Number function we get the running count of all the rows and using the between clause we filter the records as per our need.
With SQLPaging As
Select Row_number() Over (Order by Sno) as RunningNumber, FirstName, LastName, EmailId
Select * from SQLPaging
where RunningNumber between 2 and 3
This method is much more advanced (!!), we get the page size and page number as input and then with the help of Row_Number function filter out data pertaining that particular page.
Create Proc uspFetchPagedData
@pPageSize as int,
Select Top(@pPageSize * @pPageNum) ROW_NUMBER() Over (Order by Sno) as RunningNumber,
FirstName, LastName, EmailId
Select * from SQLPaging2 where RunningNumber > ((@pPageNum - 1) * @pPageSize)
i) Exec uspFetchPagedData 6,2
ii) Exec uspFetchPagedData 5,2
Saturday, October 29, 2005
Open SQL Server 2005
--Lets create a sample table populate it with few test data
Create Table WSDemo (Sno int, EmpName varchar(50), EmpMailId varchar(100))
Insert into WSDemo Values (1, 'Vadivel','email@example.com')
Insert into WSDemo Values (1, 'Velias','firstname.lastname@example.org')
--- Stored procedure which would return all employee name with their corresponding email address.
Create Procedure dbo.FetchWSDemo
Select EmpName, EmpMailId From WSDemo
--The actual WS work happens here
Create EndPoint FetchWSDemo
State = Started
Path = '/SQLWSDemo',
Authentication = (INTEGRATED),
Ports = (Clear),
Site = 'localhost')
Batches = Disabled,
Wsdl = DEFAULT,
Database = 'AdventureWorks',
Namespace = 'http://AdventureWorks/SQLWSDemo'
Points to note:
1) “State” can take one among the below three arguments:
Started— listening and responding
Disabled — neither listening nor responding
Stopped — listening, but returns errors to client requests.
2) Path – This clause specifies the URL on the server that clients will use to consume this Web service.
3) Authentication -- This clause specifies how clients will authenticate themselves to the SQL Server. For ex:
i) Integrated – most secure.
ii) Digest is not as secure as Integrated. You should use it only if Integrated authentication is not possible.
iii) Basic authentication is the least secure. It requires SSL as the Port value.
4) Ports – Clear
5) Site – The server name where the Web service is running
Open VS.NET 2005
Lets now create a client app to consume our webservice.
1. Create a new C# based windows application
2. Right click on the project name and choose Add >> Add web reference.
3. Type http://thinkingms.com/vadivel/ct.ashx?id=5e4247f6-13a0-4f44-a8cc-132e6e6fc816&url=http%3a%2f%2flocalhost%2fSQLWSDemo%3fwsdl and click on GO button. You will the webmethod come up in the list.
4. By default the name would be "localhost" change it to SQLWebServiceDemo
5. On the Form1 drag and drop a button and a Listview control.
6. Double click on the Button control and copy-paste the below code into it.
private void button1_Click(object sender, EventArgs e)
// Let us create a new instance of our web service
SQLWebServiceDemo.FetchWSDemo objWS = new SQLWebServiceDemo.FetchWSDemo();
/* We have to pass in credentials to authenticate into our service. Lets use the same credentials we have logged into our computer.*/
objWS.Credentials = System.Net.CredentialCache.DefaultCredentials;
DataSet objDS = new DataSet();
object objEmpData = objWS.FetchEmployeeData();
// iterate over the object array with a foreach statement,
// testing for the first object of type DataSet
foreach (object objArr in objEmpData)
if (objArr is DataSet) objDS = (DataSet)objArr;
listBox1.DataSource = objDS.Tables;
listBox1.DisplayMember = "EmpName";
listBox1.ValueMember = "EmpMailId";
Now we are all set to test our work. Just press F5 and click on the button to see the data being populated into the Listbox through our webservice/webmethod.
Monday, October 24, 2005
Check out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/fs/naming_a_file.asp to understand what are all the reserved words using which you can't create folders in Windows.
That said, go to dos prompt and try out md\\.\c:\con ... to your surprise a folder by name "con" would have been created in your c drive!!
Sunday, October 23, 2005
Extract from BOL:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
Lets see this in action:
Let us create couple of tables and populate with some test data.
Create Table EmployeeMaster
Create Table Loan
Insert Test data:
Insert into EmployeeMaster values (1,'Vadivel',29,'I')
Insert into EmployeeMaster values (2,'Sai',28,'I')
Insert into EmployeeMaster values(3, 'Velias',20, 'II')
Insert into Loan values(1,1,2000,'Jan')
Insert into Loan values(1,2,1000,'Feb')
Insert into Loan values(1,1,1000,'Feb')
Insert into Loan values(1,1,100,'Mar')
Insert into Loan values(1,2,1700,'Jun')
Insert into Loan values(1,1,800,'Aug')
Let see how to make use of Cross Apply operator in the below code snippet
Select E.EmpName, A.LoanAmount from EmployeeMaster E
Select top 2 LoanId, LoanAmount from Loan L
order by L.LoanAmount desc
Let see how to make use of Outer Apply operator in the below code snippet
Select C.EmpName, A.LoanAmount from EmployeeMaster E
Select top 2 LoanId, LoanAmount from Loan L
order by L.LoanAmount desc
Syntax of sp_readerrorlog
Parameter 1 : Non zero integer value
Parameter 2 : File name
Parameter 3 : Line number in the file
Parameter 4 : Search string
1. Code snippet to display the content of the current event log
2. Code snippet to display the content of eventlog.1
EXEC sp_readerrorlog 1, 'C:\Program Files\Microsoft SQL Server\MSSQL$Velias\log\ERRORLOG.1'
EXEC sp_readerrorlog 1
3. Code snippet to search a string --Would search the second row of the specified text file for the number 2000.
EXEC sp_readerrorlog 1, 'C:\SendToTextFile.txt',2, '2000'
Yep you got it right ... this system stored proc can be used not only on log file but on any file (as shown in the above code snippet).
Script to import the SQL Error Logs, format the results and then allow querying using TSQL: http://www.replicationanswers.com/Downloads/ErrorLogs.txt
Saturday, October 22, 2005
As we all know we used to end a command or a script block by typing a command terminator. i.e, GO.
GO :: Signals the end of a batch of Transact-SQL statements to the MS SQL Server utilities.
Extract from BOL:
GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. In SQL Server 2005, we can follow the command terminator (GO) with an integer value to specify how many times the command should be run. Cool isn't it?
Test it for yourself:
If you execute the above script it would print the current date 10 times.
Wednesday, October 19, 2005
Windows Registry Editor Version 5.00
Step 1: Copy - Paste the above script into a notepad. Save the file as "SearchUtility_IE.reg".
Step 2: Double click on the .reg file.
Step 3: Open Internet explorer and try this dic test
Step 4: Try msdn vista
Step 5: Try net vadivel
Also try the following if you have direct access to internet:
Step 6: google vadivel
Step 7: msn Linq
Step 8: yahoo vadivel
Step 9: vadi sql
If you feel this tip would be useful do feel free to spread the word around .... if at all by any chance you run into any issues write back to me.
Disclaimer: Normally it is not advisable to edit the registry keys directly. So if at all you are going to try it ... please be advised that you doing it at your own risk. Don't hold me responsible for anything :)
Friday, October 14, 2005
Wednesday, October 12, 2005
This is one of my favorite questions. A few months ago, the question used to be "How do I start a blog?" That's no longer the popular question. Almost everyone knows how to start a blog. In fact, one of the easiest things about the blog is starting one. With so many free blogging services available, it should be a snap to setup one. Of course, if it's easy for you to start a blog, it's easy for several million people out there. So, for obvious reasons, starting a blog is no longer a differentiator. You got to do something more. So, what are those additional things that you can do?
Over the last few months I have a learnt a thing or two about blogging from several of my friends who are my role models in the blogging world.
Here's my $.02 for those of you who are starting to blog (partial list)
1. Focus on providing high ROII (Return on Investment for an Interaction)
Every time a reader visits your blog, remember that you are taking his or her precious time and the only way you can get a reader to return to your blog is if you can ensure that the reader gets the highest return on their investment of interaction time with your blog.
2. Write something worth linking to
Unlinke newspapers, blogosphere is full of conversations. Of course, you will link to something that you find interesting. It's the same with others - they will link to you only if you write something that may be useful to their readers. There are no favors here.. sometimes may be :)
3. Participate in other conversations (Thanks to Dave Taylor for the suggestion)
To make the most out of your blog, you need to participate in conversations outside of your blog too
4. Update your blog frequently
Frequency is subjective and all I can say is work hard to maintain a reasonable frequency. It takes a lot of commitment to write quality content at regular frequency.
5. Provide reasons for why you are the right person to say what you are saying
With the amount of information available on the internet, you can write brilliantly about almost anything.However what you write will gain more credibility if you are qualified to say what you are saying. For example, if you are writing about entrepreneurship, it helps if you are one. This means that
Last but not the least:
6. Blog is not your brand
There is a confusion that you can build a brand through your blog. While in some remote cases it is a possibility, in most cases, you have to work online and offline to build a powerful brand and use your blog to extend it. "Who you are" matters a lot. Think about some of your favorite bloggers and you will see that they are all working on exciting things apart from writing interesting blog entries. They speak at conferences, write books, contribute to other magazines, participate in charitable causes etc.
In summary, starting a blog is the easiest thing to do. Maintaining one is hard. Not because you won't get enough content to write but because while maintaining your blog, you need to invest in yourself to keep growing to continue to enhance credibility for your blog.