Skip to main content

Posts

Showing posts from October, 2005

Encryption in SQL Server 7.0

After a long time I visited CNUG (Chennai .NET User Group) . When I was going through the questions I saw a question posted by "Nitin" asking Is it possible to encrypt data within data server (SQL Server 7.0) 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 2. Pwdcompare. 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

Saving images as BLOB into SQL Server 2005

In this article we would look into the easiest way of importing an image as BLOB content into a SQL table. 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,

Bill Gates in MTV!!

I came to know about "Notorious B.G on MTV" from my favourite bloggers blogspace >> http://scobleizer.wordpress.com/2005/10/29/why-do-i-work-at-microsoft/ I went through the complete transcript @ http://www.mtv.com/thinkmtv/features/education/gates_forum/ and the below QA is what I liked the most. 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 on

Paging records using SQL Server 2005

In this article we would look into basics of paging records in SQL Server 2005. I have provided couple of methods with correspondng code snippets for our better understanding. Code snippet for the sample table: Create Table tstSQLPaging ( Sno int, FirstName varchar(50), LastName varchar(50), EmailId varchar(100), Salary int ) Go Enter sample data into that table: Insert into tstSQLPaging values (1, 'Vadivel','M','vmvadivel@yahoo.com',10000) Insert into tstSQLPaging values (2, 'Sailakshmi','L','abc@yahoo.com',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','itissiddhu@yahoo.com',6000) Insert into tstSQLPaging values (6, 'Vicky','L','vicky@yahoo.com',19000) Insert into tstSQLPaging values (7, &#

Exposing SQL Server 2005 data via a Web Service

In this article we would see how SQL Server 2005 has been powered to create Webservices with ease. Implementing real webserices in SQL Server 2005 is officially called HTTP Endpoints. Ok without fasting any further time lets start to understand HTTP Endpoint by getting our hands dirty. Open SQL Server 2005 Use Adventureworks go --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','vmvadivel@yahoo.com') Insert into WSDemo Values (1, 'Velias','smart3a@yahoo.com') --- Stored procedure which would return all employee name with their corresponding email address. Create Procedure dbo.FetchWSDemo As Select EmpName, EmpMailId From WSDemo Go --The actual WS work happens here Create EndPoint FetchWSDemo State = Started As HTTP ( Path = '/SQLWSDemo', Authentication = (INTEGRATED), Ports = (Clear), Site = 'localhost') For Soap ( W

Why can't we create a folder by name "CON", "prn" etc., in Windows?

Many a times in the last couple of months I got mails or rather spams :) saying we can't create folder in the name of "CON", "PRN" etc., Instead of responding to them individually (that is what I was doing all this while) I have created this blog post. 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!!

Cross Apply and Outer Apply in SQL Server 2005

In this article I would talk about the New APPLY operator in SQL Server 2005. As usual I would provide a working sample / code snippet for better understanding. 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.

About sp_readerrorlog

In this article let us understand the system stored procedure Sp_readerrorlog in detail. Sp_readerrorlog is a system stored procedure which can be used to read the SQL Server error logs from within T-SQL. 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 Execute sp_readerrorlog 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' Or 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 res

SQL Server 2005 and GO operator ...

In this article we would see an interesting feature which was introduced in SQL Server 2005. Do you know that you can execute a T-SQL batch "any number" of times just by providing an integer value along with GO. 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 r

Cool search utility ...

Find below the script which would help you all in searching contents within "msdn", "dictionary", "msn", "yahoo", "netscape", "Google" and within my blogspace with ease. Script: Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl] [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\dic] @=" http://dictionary.reference.com/search?q=%s " [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\google] @=" http://www.google.com/search?hl=en&lr =&q=%s" [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\msdn] @=" http://search.microsoft.com/search/results.aspx?qu=%s " [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\net] @=" http://search.netscape.com/ns/search?fromPage=NSCPTop&query=%s " [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Internet Explorer\SearchUrl\msn] @=" htt

I started a blog; now what?

Rajesh Shetty author of Beyond Code gives us pointers of what to do when you start a blog. 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) Ever