You can transfer current users page request to another page with two methods:
* Server.Transfer (HttpServerUtility.Transfer Method)
* Response.Redirect (HttpResponse.Redirect Method)
Its not always clear how these two approaches differ so let us try to clarify things a little:
Response.Redirect sends HTTP code 302 down to the users browser along with the new URL location of the wanted page.
HTTP Code 302 actually means ' The requested resource resides temporarily under a different URI'.
After browser receives this code it tries to open the new location of the resource that was suggested by the server.
This actually causes two requests to the server, first one to the original URL, and second to the new URL that is suggested via 302 response.
All the Query Strings and Form variables are lost during the redirect and they are not available to the redirected URL.
Also its important to say that the new URL can reside on the same server but also it can be on some other server and the redirected URL does not need to be .aspx page it can be regular HTML page also).
So we can us the Redirect method to redirect users request to another page on our server like this:
Response.Redirect("newPage.html");
or to redirect our it to some other server like this:
Response.Redirect("http://www.someotherserver.com/newPage.aspx");
In contrast to all this when we call Server.Transfer we do not initiate another request to the server, but the original request is simply rewritten and transfered to some other page on the same server.
(This off course means that we can use it only to transfer requests to the pages on the same server, not to some other servers and we can only transfer to .aspx pages and not other page types like HTML, php etc).
All posted Form variables and query strings can optionally remain available to the second Page where we transfered request (if we use second overload Server.Transfer(string path, bool preserveForm) and supply true for the second parameter).
Otherwise the Form Variables and Query String are cleared just like when we use Redirect.
WARNING: If you use this method to preserve Query String and Form variables and receive error: "View State Is Invalid" its because your EnableViewStateMac attribute of the
Its also important to note that because of the way Server.Transfer works, after the transfer, the URL shown in the users Web Browser remains the original one that was requested, because browser has no knowledge that its request was transfered (transfer occurs on the server side).
TIP: One thing to be careful about when using the Server.Transfer is to clear the the HttpResponse object with Response.Clear method on the transfered page to avoid any output from the first page to be shown on the second page.
So now that we know what are the similarities and differences between these two approaches we can try to use them wisely.
Here is the summary:
Response.Redirect should be used when:
* we want to redirect the request to some plain HTML pages on our server or to some other web server
* we don't care about causing additional roundtrips to the server on each request
* we do not need to preserve Query String and Form Variables from the original request
* we want our users to be able to see the new redirected URL where he is redirected in his browser (and be able to bookmark it if its necessary)
Server.Transfer should be used when:
* we want to transfer current page request to another .aspx page on the same server
* we want to preserve server resources and avoid the unnecessary roundtrips to the server
* we want to preserve Query String and Form Variables (optionally)
* we don't need to show the real URL where we redirected the request in the users Web Browser
Kamis, Juni 12, 2008
What is the difference between Server.Transfer and Response.Redirect methods?
How to programmatically Encrypt and Decrypt sections of web.config file?
Asp.Net 2.0 has built-in support for encryption and decryption of configuration file sections
with the .Net 2.0 Configuration API.
There are various sections of typical web.config file that usually contain sensitive information:
*
*
*
* etc
These important parts of web.config files are ideal candidates for encryption, especially if your website is hosted on
shared-hosting.
By using the built-in Encryption methods we can protect those configuration sections, so even if someone somehow manages
to obtain our web.config files or just take a brief look at them , there will not be much for him to see there.
The fun part is that those encrypted sections are automagically decrypted on-the-fly when our web application reads them,
so no change in application code is needed to be done after we protect web.config!
ASP.NET 2.0 comes with two encryption providers we can use to protect our configuration files:
* DataProtectionConfigurationProvider
* RSAProtectedConfigurationProvider
* also you can implement your own Providers, because its a plugin based model
The DataProtectionConfigurationProvider uses Windows Data Protection API (DPAPI) and this provider
uses a amchine-specific secret key for encryption and decryption.
This means that when you encrypt some data on one server, you can only decrypt it on the same server.
If you need to move configuration files with encrypted sections from server to server or if you have a Server-Farm
you will need to use the RSAProtectedConfigurationProvider. This provider uses RSA public key protection,
and you can use command line tool aspnet_regiis to create, import and export keypairs for encryption.
more on that on this link: http://msdn2.microsoft.com/en-us/library/ms998283.aspx
We will consider the simpler case where we use a machine specific DataProtectionConfigurationProvider
to programmatically encrypt our configuration files.
We pass the DataProtection provider name as a string to the ProtectSection method to protect our config sections.
here are two simple methods to protect and unprotect your config files sections.
using System.Web.Configuration;
public void EncryptSection(string sectionName, string provider)
{
Configuration cfg = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);
if (cfg == null)
return;
ConfigurationSection section = cfg.GetSection(sectionName);
if (section == null)
return;
if (!section.SectionInformation.IsProtected)
{
section.SectionInformation.ProtectSection(provider);
cfg.Save();
}
}
public void DecryptSection(string sectionName)
{
Configuration cfg = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);
if (cfg == null)
return;
ConfigurationSection section = cfg.GetSection(sectionName);
if (section == null)
return;
if (section.SectionInformation.IsProtected)
{
section.SectionInformation.UnprotectSection();
cfg.Save();
}
}
and here is an example on how to use these methods to encrypt/decrypt conectionStrings section of your web.config file:
EncryptSection("connectionStrings","RSAProtectedConfigurationProvider");
and
DecryptSection("connectionStrings");
or using the DataProtectionConfigurationProvider:
EncryptSection("connectionStrings","DataProtectionConfigurationProvider");
and
DecryptSection("connectionStrings");
To be positive that our configuration file is indeed protected, here is a code that
reads the web.config file from website root using server's FileSystem IO and
displays it in the TextBox component on ASP.NET page:
(we must use the IO commands to read the web.config file to see if its encrypted,
because if we would just read the settings directly from our web application they
would be decrypted on the fly and we would receive the real values).
When using FileSystem IO functions to read the web.config file we receive its true
encrypted contents:
using System.IO;
public void ShowWebConfigFile()
{
StreamReader sr = File.OpenText(Path.Combine(Request.PhysicalApplicationPath,"Web.Config"));
string contents = sr.ReadToEnd();
sr.Close();
webconfig.Text = contents;
TextBox1.Text = WebConfigurationManager.ConnectionStrings["local"].ConnectionString;
}
and here is how our connectionStrings section should look when read this way:
89xTx8n6fgJu3M4AEAAAAAASAAACgAAAAEAAAAJj77evc30ixlYD7sXdZyUbIAAAATK3ZOyiXebQMhT7KOT1TpRDqPxd/zeD2uwg0
4LeC7CHmMquRKcXGI5Xb7LJnNKysbfXycu7ZmUgd4i7mp3ly/unc/FVWG9PAk6b5LG8BTsdNzTQeZYT7u8Iap2BDFKPgiZqLA4SvM
ti1MyjAK6P+lQCp0irWiE2yqWpMiQ82+k1SZj6AdYjNvYLmoAPFzqX1++ybMdcHueXsv4dvd5Fw2fFTh6iiBdR2xQtvgMrtP9OHmm
CTM1IJ6G8Jv0oxqG7J1b3JWQSIs9MUAAAA4/q1tlT5sNGslF4/yogmvKEz4MY=
Put your ASP.NET 2.0 application Offline - the user friendly way!
Every web application has its Life Cycle. This cycle involves (among many other things) developing, deploying, debugging, maintenance etc.
When your web application is already deployed on production server, making some critical changes on it can be a challenging
task if you have multiple visitors online.
Developers at Microsoft added one nice feature to ASP.NET 2.0 that addresses this very problem:
if you upload a html file named "app_offline.htm" to the root of your website, ASP.NET recognizes this file, shuts down your application, and for every next requested page, contents of your "app_offline.htm" file is server instead.
When you are done updating your website, you can delete this file or just rename it, and your website will start working again
NOTE:
One thing to be careful about: It is recommended that your "app_offline.htm" file size is minimum 512 bytes in order to be shown correctly in Internet Explorer 6.
(This is because IE6 has setting called "Show Friendly Http Errors" that shows its own error messages instead of the pages returned from web server - if they have HTTP status code different than 200 and if they are smaller than 512 bytes in size).
Minggu, Juni 08, 2008
Introduction to Language Integrated Query (LINQ)
ProblemLanguage-Integrated Query (LINQ) is a groundbreaking innovation in Visual Studio 2008 and the .NET Framework version 3.5 that bridges the gap between the world of objects and the world of data. As LINQ is part of the development enhancements in SQL Server 2008, how can I have an understanding of how it works and how I can use it in other areas of administration, not just SQL Server?
SolutionLet's start off by explaining LINQ. LINQ is a codename for a project which is a set of extensions to the .NET Framework that encompasses language-integrated query, set and transform operations. It extends C# and VB with native language syntax for queries and provides class libraries to take advantage of these capabilities, available only in .NET Framework 3.5. For developers who write code that regularly access a recordset, this means a lot. The fact that queries are usually expressed in a specialized query language for different data sources makes it difficult for developers to learn a query language for each data source or data format that they must access. This is what LINQ is all about. It simplifies data access by providing a consistent model for working with data across various kinds of sources and formats. In LINQ, data is translated into objects, something that developers are more comfortable with working . Understanding LINQ will give us an idea of its capabilities and its benefits
Create a simple LINQ project
Let's start by creating a simple console project using the C# language in Visual Studio 2008. You can also download the free Visual C# 2008 Express Edition from the MSDN Download Center. Make sure you select .NET Framework 3.5 from the target framework drop-down menu.
This will open up your Program.cs file. Notice that by simply creating a project that targets the .NET Framework 3.5 automatically adds a using directive for the System.Linq namespace as this is already a part of the System.Core assembly. The System.Linq namespace provides classes and interfaces that support queries that use LINQ. We will start with this to understand the basics of LINQ.
Let's start writing some code inside the static void Main(string[] args):
//Obtaining the data source string[] carNames = {"Ferrari", "Porsche", "Mercedes" , "McLaren", "Audi", "BMW"}; // Create the query // query is an IEnumerable var query = from name in carNames where name.Contains("e") select name; // Execute the query foreach (string name in query) { Console.WriteLine(name); }
//Pause the application Console.ReadLine();
We'll examine the basic components of a LINQ query. Any LINQ query consists of three distinct actions. These are obtaining the data source, creating the query and executing the query. The first thing that we need to do is to have a data source. In this case, it's an array of strings which supports the generic IEnumerable(T) interface. This makes it available for LINQ to query. A queryable type does not require special modification to serve as a LINQ data source as long as it is already loaded in memory. If not, you would have to load it into memory so LINQ can query the objects. This is applicable to data sources like XML files. Next, is the query. A query specifies information to retrieve from the data source. This is similar to a SQL query which includes syntaxes like SELECT, FROM, WHERE, GROUP BY, etc. Looking at the code above, you'll notice that its not like your typical SQL statement as the FROM clause appeared before the SELECT clause. There are a couple of reasons for this. One, it adheres to the programming concept of declaring the variable before using it. Also, from the point of view of Visual Studio, this makes it easy to provide the IntelliSense feature using the dot (.) notation as the variable has already been declared and that the framework has already inferred the correct type to the object. This provides the appropriate properties and methods, making it easy for the developers to write their code.
Let's look at how the code was constructed. The from clause specifies the data source, in this case, the carNames collection. The where clause applies the filter, in this case, the list of all elements in the collection containing the letter 'e'. The select clause specifies the type of the returned elements. This means that you can create an instance of the elements in your collection. An example could be creating an instance of an object with fewer attributes. The query variable, query, just stores the information required to produce the results when the query is executed, maybe at a later point. Simply defining the query variable does not return any data nor takes any action. The third component of the code above is query execution. As I mentioned, the query variable does not contain any data, but rather simply contains only the query commands. The actual execution of the query is when we iterate over the query variable. There are a couple of ways to do this. One of which is shown above. The use of a foreach statement iterates thru the query variable and execute it as well. This concept is called deferred query execution. This is very much important when dealing with data sources such as highly-transactional database systems as you minimize connecting to the database unless necessary (database connections are additional resources on the database server as well). You can opt to execute the query immediately by using aggregate functions such as Count, Max, Average and First or calling the ToList() or ToArray() methods. Another way is to bind the collection to a data-bound control in either a web or windows form control similar to how you would do it in previous versions of Visual Studio - specifying the DataSource property of the control to be the query variable and calling the DataBind() method.
Another area to highlight in the code is the use of the keyword var, which is a new keyword introduced in C# 3.0. What this does is it looks at the value assigned to the variable, then determines and sets the appropriate one. This concept is called type inference. From the code above, the query variable, query, appears to be an array of string. So the compiler will automatically assume that it is a variable of type IEnumerable. This is helpful if you do not know the variable type during runtime. But this does not mean that any type can be assigned to the variable after the initial assignment - something like a dynamic type - since .NET is a strongly typed language platform. This simply means that an object can take on a different type and the compiler can simply handle that. Assigning a different type to an already existing one violates the concept of polymorphism in object-oriented programming. Let's say you assign the value 12 to the query variable, query. This will throw a type conversion exception as the original type of the variable is a string collection.
Your output will look like this when you run your project in Visual Studio. You can press F5 or click on Debug - Start Debugging in Visual Studio
Next Steps
You have seen how powerful LINQ queries are and how similar they are to SQL queries. There are other data access methods that LINQ implements including
LINQ to SQL
LINQ to XML
LINQ to Objects
LINQ to Entities
LINQ to DataSets
Give this example a try and change the query parameters so you can have a feel of how LINQ works.
Have a look at the 101 LINQ Samples at the MSDN Visual C# Developer Center.
For samples in Visual Basic, you can check the Getting Started with LINQ in Visual Basic site from MSDN
source : http://www.mssqltips.com/tip.asp?tip=1502
Power Of Empati
Anda tidak akan tahu dimana besok, minggu depan atau bulan depan berada. Sekarang mungkin anda sedang asik memainkan remote televisi anda sambil menikmati long weekend anda. Tapi terbayangkah oleh anda bahwa senin besok dikarena satu dan lain hal anda haus di pindahkan ke propinsi baru? Mungkin di Aceh atau mungkin daerah Paapua? Hal yang baru bukan? Dan mungkin ini menjadi suatu tantangan tersendiri bagi anda. Berat menantang tapi itulah hidup, dimana semuanya akan selalu berubah dan berganti. Dan satu yang pasti walaupun dunia berubah, namun anda akan dan harus tetap struggling. How to survive and struggling to every condition, empaty is the solution for this.
Empati atau yang secara gamblang sering dibilang berpikir seperti orang lain (how to thinks like others), terlebih pihak lain disaat kita berkomunikasi atau berkerja dengan orang lain. Mengerti orang lain dan memahami orang lain akan memudahkan anda untuk bekerja dan berkembang dimanapun anda berada. Empati sudah menjadi ilmu dasar bagi kita, cuma terkadang kita terlalu sombong untuk mau dan berusaha mengerti orang lain. Padahal anda tahu bahwa ilmu dan kepintaran anda memahami orang lain merupakan senjata anda untuk berperang agar survive dan maju dalam kehidupan anda.
Bila anda mau menyisihkan sedikit waktu anda dan melihat di sekitar anda, Pelajarilah! Bahwa belum tentu orang yang pintar itu akan lebih mudah berhasil dibandingkan dengan orang-orang yang memiliki kemampuan untuk bisa berempati dan bekerja sama dengan orang lain. Kenapa demikian? Hal yang menarik bukan? Hal ini terjadi karena orang yang memiliki kemampuan berempati lebih mampu untuk memuaskan orang lain yang bekerja sama dengannya. Tentu saja anda akan bisa menyenangkan orang lain pada saat anda mengetahui apa yang dibutuhkan orang lain dan memenuhi kebutuhannya. Bagaimana anda bisa mengetahui orang lain, inilah the power of empathy. Dia mampu membawa anda untuk menyenangkan orang lain. Dan bila anda mampu untuk melakukan hal ini maka semua orang akan berusaha bekerja dengan anda dan ingin merasakan kepuasan berkerjasama dengan anda. Melalui inilah orang akan datang kepada anda dan membukakan pintu-pintu kehidupan baru (kesempatan) untuk anda.
Untuk membantu anda dalam memahami kekuatan empati ini, saya akan membawakan kepada anda salah satu cerita menarik dari buku yang saya baca yaitu “How to Get Rich” karangan Donald J. Trump. Di dalam buku ini dia bercerita bahwa salah satu temannya Wayne Newton sedang mengalami masalah dengan para bangkirnya. Di mana dia tidak bisa membayar hutang-hutangnya kepada para bangkirnya. Lalu Wayne menghubungi Trump dan bertanya apa yang harus dia lakukan untuk menghadapi masalahnya ini. Lalu trump menjawab “Wayne, aturlah pertemuan dengan mereka. Idealnya makan malam dengan mereka dan keluarganya dan kenali mereka, maka akhirnya mereka akan menyukai anda.
Tiga minggu kemudian setelah pembicaraan itu Wayne menelepon Trump dan mengatakan ia telah makan malam dengan ketiga banker dan berkata bahwa mereka adalah orang-orang teramah yang pernah ia temui, dan sebagai hasilnya bank-bank tempat Wayne berhutang mereschedule hutang-hutang Wayne dan membuat Wayne mampu untuk survive sampai sekarang.
Anda lihat bukan bahwa empati memiliki power yang sangat besar di dalam kehidupan anda. Semua hal bisa anda hadapi bila anda tahu dengan siapa anda berhadapan, dengan siapa anda berkomunikasi dan bagaimana mereka mengharapkan anda memperlakukan mereka. Jadi pelajarilah sifat dan kepribadian mereka, maka mereka akan mengantarkan anda ke pintu-pintu kesempatan yang mereka miliki.
Buat mereka suka dan percaya kepada anda, maka mereka tidak akan ragu untuk memberikan kesempatan kepada anda.
Salam Sukses!
D_LoebizFounder & Moderator Beranie Gagal
Jumat, Juni 06, 2008
Free Tools for the SQL Server DBA
We've all done it.
Scourer the Internet for a script or free tool to help do our job or solve a problem. SQL Server Central and similar sites contain an abundance of scripts and guides to assist us. There are times when I want to use a tool instead of a collection of scripts and commands. A tool more often than not requires less customization than a script and sometimes less technical know how to get them to work. So I decided to share a list of free tools that I use from time to time.
NVU
To start with I wrote this article using NVU an open source WYSIWYG HTML editor. It creates HTML pages with very simple HTML code. It still adds a few extra commands such as " " but that's nothing compared to what MS Word generates for HTML code.
NVU is open source software and does not require any registration to download or use.
FullSun SQL Server Toolkit
It is a nice tool kit with many helpful features. Currently this tool is for SQL Server 2000 but a 2005 version is being developed. Here are some of the functions available with this tool:
List all SQL ids with very easy passwords so they can be fixed.
Review all the errors or failed logins in the SQL Error Log files.
Monitor database growth through their backup file sizes along with viewing the current table sizes.
Check nine predefined performance counters.
Read a database log file. (This was primary reason I got this tool. It's nothing fancy but I like it.)This tool is CharityWare software. Every time the tool is started, a panel pops up in the background with instructions on using the tool and a request for a charitable donation.
EMS SQL Manager for SQL Server Lite
EMS provides a free lite version of their SQL Manager tool with limited administration functions. The SQL Monitor is fairly simple and only works on SQL Server 2005. The feature I like most about this tool is when I am browsing or editing data in a table, I can view the data in a grid or form. The form shows the contents of one row vertically. This is very helpful for reading long columns.To download from the EMS website you need to register. There are two download options. One is a full download of the product and the other is just the executable. So far I've only used the 2.73 MB executable because it does not require any installation. The full download of this tool is available from an alternate site:
http://www.download.com/3001-10255_4-10594854.html
Toad for SQL Server
Quest has a freeware version of their toad for SQL Server. I downloaded it because I heard how popular Toad for Oracle was. It seems to have all the basic functions of Enterprise Manager. The SQL Editor is incorporated into the tool as a separate tab. Yes, it uses tabs to provide access to the various functions. The export wizard supports a variety of formats. It does disappoint me that you need the commercial version to export a table as insert statements. You would start to think they are in the business of making money. Wait, I guess they are.Quest does not seem to require any registration to download and use this tool. Previously it required you to download the product every 60 days but that restriction has been removed for the current release.
SQLCheck
SQLCheck is a free performance dashboard from Idera. It provides real time monitoring of about a dozen key performance indicators such as Buffer Cache Ratio, User Connections, and Processor Time. In addition, you can review SQL processes, SQL error log, jobs, and configurations. All this information is available using other tools but this tool provides a single location to finding this information.Idera requires you to register before you download the product. You will probably get a phone call from the company, but you won't be continually spammed.
WinSql
Synametrics provides a free lite version of their WinSql product. It is an ODBC SQL Query tool with create table options. I work with some developers who love using it. Since developers use it, I use it to help troubleshoot any problems they are having executing SQL statements.
To use this product you need to register.
SQLinForm
SQLinForm is a free online SQL Formatter for many database engines. This tool is great for cleaning up complex SQL and making it more readable. There are other sites with free online SQL formatting tools (like SQL Parser). When one parser has trouble formatting a SQL statement you can try it on another site.These tools are free to use online with no registration required.SpaceMonger It happens, a drive is running out of space. To get a quick view of what is taking up the space, I use SpaceMonger v1.4.0. It provides a bird's-eye, graphical view of the file allocations on a single drive. Plus it is faster than some similar tools. The tool consist of a single 212k executable that can be copied to servers and executed locally to create the report as fast as possible. When needed, it can be used remotely on mapped drives.
Sixty-Five has released a purchasable version of this tool that contains more functions. They are still providing the version 1.4.0 of SpaceMonger at no cost.
This free version does not require any installation or registration to download and use.
ExamDiff
There are times when I need to compare SQL code, stored procedures, table counts, and other data. For this I use ExamDiff. It has a clean GUI interface for selecting files and browsing the results. While browsing the results, it has an option to edit the files. The company PrestoSoft provides a more advance version, but for simple text file comparisons the free version is adequate.This program does not require any installation or registration to use. Just download the zip and click on the executable.
Windows Sysinternals
Mark Russinovich and Bryce Cogswell at Sysinternals created a large collection of free tools to help troubleshoot and diagnose window servers and desktops. In 2006 Microsoft purchased Sysinternals. Most of these tools are still free and continue to be updated. I am a big fan of the tools Process Explorer and Handle. When I have trouble with a locked file, I turn to these tools. Most of tools are executables and require no installation.Last I knew they still do not require registration to use.
7-Zip
7-Zip is a compression tool that supports multiple formats and files larger than 4 GB. WinZip v8 does not compress files larger than 4 GB. My company was not going to pay the $50 dollars for a newer version. Why? Because that's the way they are. So I found 7-zip which does support zip files > 4 GB. It is slower than WinZip 11 and does not include encryption but at least it gets the job done. I don't use the 7z format because it is really slow on big files even though the files it creates are suppose to 30-70% smaller than Zip.7-Zip is open source software and does not require any registration to download or use.
The 46 Best-ever Freeware Utilities
This site does not have any database specific tools. Instead it has 46 other categories of tools that are reviewed and rated by the site owner. So if you are looking for an FTP client, text editor, registry editor or some other tool give it a look.This site only provides links to other sites from which you can download the software.
Yahoo E-mail
Many companies released free tools that require registration. I use a free Yahoo e-mail account when I register. It provides an excellent spam filter. Yeah it's not 100% but it's sure better than some others I've had. Sometimes registration information gets caught as SPAM but as long as it goes to the Bulk (SPAM) folder you can retrieve it.Yahoo sometimes sends you an advertisement, but they are easily identified and deleted.
Conclusion
That's it for my list of DBA tools. Most of the time, purchased tools provide more functionality. But when it comes to free, these tools do fill a need. Last I checked all these tools where still available and free. If you know of any other useful free tools for a SQL Server DBA be sure to mention it in "Your Opinion" discussion for this article.
By David Bird,
source : http://www.sqlservercentral.com/articles/Miscellaneous/2959/
Rabu, Juni 04, 2008
Undocumented Extended and Stored Procedures
Undocumented Extended and Stored Procedures
By Timothy A Wiseman
t
Introduction
There are several undocumented system stored procedures and extended stored procedures in SQL Server 2005 which can be of enormous use. Since they are undocumented, it can be risky to rely upon them. There is always the possibility that they will be dropped or altered, which can be worse, with the next version of SQL Server or even the next service pack. If it is important that the code in question works with the next version of SQL Server then it is wise to avoid undocumented procedures. But they can be more than worth that small risk when compatibility with future versions is not a priority.
XP_FileExist
The usage is:
EXEC xp_fileexist <filename> [, <file_exists INT> OUTPUT]
If executed without providing an output variable, it will display 3 columns indicating whether the table passed exists, whether it is a directory, and if the parent directory exists. For instance:
exec master.dbo.xp_fileexist 'C:\temp'
Results in this output:
xp_fileexist Output File Exists File is a Directory Parent Directory Exists
0 1 1
If it is executed with the output parameter it will set the value to one if the file exists and 0 otherwise. It will set the variable to 0 if the name given is a directory.
Generally, it is better to check validate the existence of any files prior to calling the T-SQL script, but there are certainly times when it is convenient to be able to do it from within a T-SQL script. This syntax is far more graceful than work arounds which can be achieved with xp_cmdshell.
SP_MSForEachDb
sp_msforeachdb will execute the attached string as a command against every database on the server. Any question marks (?), within the string will be replaced by every database name. It can be useful for finding the database which holds a given table on a server. For example:
exec dbo.sp_MSforeachdb 'select ''?'', * from [?].INFORMATION_SCHEMA.TABLES where table_name like ''authors'' '
will find every table named authors on the server. It can also be useful for running maintenance routines against every database. For instance, this:
exec dbo.sp_MSforeachdb 'DBCC SHRINKDATABASE(N''?'' )'
will shrink the file size of every database on the server. It may be worth noting that on a well designed production system it would not be a good idea to run that command, but it can be useful for reclaiming file space on test and development servers and illustrates the broader ways it can be used for administrative commands when needed.
SP_MSForEachTable
sp_msforeachtable is very similar to sp_msforeachdb, except executing the command against every table in the database. For instance, if there is a test database and all tables in the test database need to emptied while retaining the table structures, this command could be used:
exec dbo.sp_msforeachtable 'delete test.dbo.[?]'
SP_who2
Sp_who2 is like sp_who's bigger brother. It functions in a very similar fashion to the documented procedure sp_who except that some of the column names are slightly changed, the ecid column is dropped, and some additional columns are listed. It adds the cputime, diskio, lastbatch, and programname columns to the information provided by sp_who. Its output appears as:
sp_who2 output SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
1 BACKGROUND sa . . NULL RESOURCE MONITOR 100 0 04/12 15:58:54 1 0
2 BACKGROUND sa . . NULL LAZY WRITER 10 0 04/12 15:58:54 2 0
3 SUSPENDED sa . . NULL LOG WRITER 0 0 04/12 15:58:54 0 0
4 BACKGROUND sa . . NULL LOCK MONITOR 0 0 04/12 15:58:54 4 0
Both sp_who and sp_who2 with its added columns can be tremendously valuable in scripts and programs, but for use on an ad hoc basis it is often more convenient to invoke the Activity Monitor GUI.
sp_MSdependencies
sp_MSdependencies is a powerful procedure that can be used to determine all dependencies for an object within a database. Its usage is:
exec dbo.sp_MSdependencies [<object name> ] , [<object type>], [<Flags>], [<objlist>]
The procedure also accepts an integer variable @intrans, but it does nothing. All of the variables are optional and in most cases it is not necessary to pass both an object name and an object type. If
exec sp_msdependencies '?'
is executed it will provide a brief description of the usage options.
sp_MSdependencies is similar to the documented stored procedure sp_depends, but it is simultaneously more flexible and more difficult to use. sp_depends will return two result sets, the first lists those things that the targeted object depends on directly and the second listing those things which depend directly on the targeted object, and in both cases it does not consider foreign key relationships to be dependencies. sp_MSdependencies on the other hand does count foreign key relationships and will return either things which depend on the target or on which the target depends and precisely what it returns is determined by the flags passed. By default, it will not return system tables or other internal objects or user defined data types, but it can be set to do so by using the flags switch. It also formats its results different from sp_depends.
If it is executed with only an object name, it will return everything on which that object depends. For instance:
exec sp_msdependencies 'dbo.titleview'
exected in the pubs database returns
sp_msdependencies output oType oObjName oOwner oSequence
8 authors dbo 1
8 publishers dbo 1
8 titles dbo 2
8 titleauthor dbo 3
Which indicates that dbo.titleview depends on authors, titles, and titleauthor while authors depends on the publishers table. The oType of 8 indicates that they are all tables.
Providing only an object type by number will result in a list of everything which depends directly or indirectly on any object of that type within the current database. In the sample pubs database, the dbo.titleview is the only view, so if views were selected and
exec sp_msdependencies NULL, 2
were executed the results would be
oType oObjName oOwner oSequence
----------- ------------------------ ------------------------------- ---------
8 authors dbo 1
8 publishers dbo 1
8 titles dbo 2
8 titleauthor dbo 3
4 titleview dbo 4
Providing everything which any view depends on, including the view itself.
The flags can be added to either an object name or an object type and they determine precisely what is returned. The flags paramater is a bitmap expressed in hexadecimal. It permits precise control over what types of objects are returned and will allow the return of children, or objects which depend on the target, instead of parents, or objects on which the target depends. Using the flags, it can be set to return the system objects and user defined data types which are normally skipped. It can also be set to return only first level relationships which makes it more like sp_depends. For instance, to retrieve the objects which depend on the titles table execute:
exec sp_msdependencies 'titles', NULL, 0x401fd
which returns:
sp_msdependencies output oType oObjName oOwner oSequence
8 roysched dbo 1
8 sales dbo 1
8 titleauthor dbo 1
16 reptq1 dbo 1
16 reptq2 dbo 1
16 reptq3 dbo 1
4 titleview dbo 2
16 byroyalty dbo 2
Everything on the list depends either directly or indirectly on the titles table. For instance, the procedure byroyalty does not directly rely on the titles table, but it relies on titleview which in turn is dependant on the titles table.
In general, it is more convenient and user friendly to simply use the GUI provided in the SSMS to gather the dependencie information when it is needed on an ad hoc basis, but sp_msdepencies can be invaluable in creating scripts in which dependencies need to be evaluated. Its ability to consider foreign key relationships and indirect dependencies can also make it valuable in situations where the documented sp_depends is not appropriate.
Conclusion
Undocumented procedures should always be used cautiously, especially when dealing with procedures that are expected to be able to migrate from one version of SQL server to another. Undocumented procedures are far more likely than their documented counterparts to be changed, and they will not be officially supported if technical support is ever required. With those risks in mind, they can often be tremendously useful. This provides a short list of some of the more useful ones and how they can be used in SQL Server 2005.
By Timothy A Wiseman
http://www.sqlservercentral.com/articles/Stored+Procedures/62868/
Ciptakan Kehidupan, Bukan Sekedar Hidup
Your successes and happiness are forgiven you only if you generously consent to share them. ?Kesuksesan dan kebahagiaan akan sangat berarti jika kau mau berbagi dengan orang lain." Albert Camus
Untuk dapat sekedar hidup, mungkin kita tidak perlu bersusah payah mencari peluang ataupun memikirkan bagaimana meningkatkan kualitas dan manfaat diri kita. Namun sebagai mahluk yang paling spesial diantara mahluk ciptaan Tuhan YME, kita berkewajiban untuk mendapatkan kehidupan yang berarti. Kita harus berupaya semaksimal mungkin. Sebuah pepatah bijak menyebutkan, "Find a meaningful need and fill it better than anyone else. Kejarlah sesuatu yang bermakna, dan gunakanlah setiap peluang yang ada secara lebih baik dari siapapun."
Ada beberapa langkah untuk menjadikan kehidupan kita menjadi lebih berarti.
*Langkah pertama adalah memperbesar kemauan untuk belajar.
Manusia mempunyai pikiran yang luar biasa, maka gunakan pikiran tersebut untuk belajar menciptakan kemajuan-kemajuan dalam hidup.
Kita dapat belajar dari berbagai hal, diantaranya adalah belajar kepada pengalaman hidup, kegagalan, kejadian sehari-hari, orang lain dan sebagainya. Maka tingkatkan terus kemauan
belajar.
*Langkah kedua supaya kehidupan kita lebih berati adalah mencoba melakukan sesuatu agar lebih dekat dengan impian yang diidamkan.
Bekerjalah lebih keras, lebih aktif atau produktif. Langkah ini sangat efektif dalam meningkatkan kemungkinan mendapatkan uang, kekayaan atau segala sesuatu yang berharga bagi manusia.
Satu hal yang patut dijadikan pedoman bahwasanya kerja keras itu bukan semata-mata mengejar 5 P, yaitu power (kekuasaan), position (posisi), pleasure (kesenangan), prestige (kewibawaan) dan prosperity (kekayaan).
Setiap usaha yang hanya berorientasi kepada lima hal tersebut memang menjamin kesuksesan atau bahkan hasil yang melimpah ruah, tetapi tidak menjamin sebuah akhir yang menyenangkan. Contohnya adalah sebuah fakta tentang delapan orang miliarder di Amerika Serikat yang berkumpul di Hotel Edge Water Beach di Chicago, Illionis pada tahun 1923. Mereka adalah orang-orang yang sangat sukses, tetapi mengalami nasib tragis 25 tahun kemudian.
Salah seorang diantara mereka adalah Charles Schwab, CEO perusahaan besi baja ternama pada waktu itu, yaitu Bethlehem Steel. Tetapi Charles Schwab mengalami kebangkrutan total. Sehingga ia terpaksa berhutang untuk membiayai hidupnya selama 5 tahun sebelum meninggal.
Yang kedua adalah Richard Whitney, President New York Stock Exchange.
Namun pria ini ternyata menghabiskan sisa hidupnya dipenjara Sing Sing. Orang ketiga adalah Jesse Livermore, raja saham "The Great Bear" di Wall Street. Tetapi Jesse mati bunuh diri.
Orang ke empat adalah "The Match King", Ivar Krueger, CEO perusahaan hak cipta, yang juga mati bunuh diri. Begitu juga dengan Leon Fraser, Chairman of Bank of International Settlement, ia mati bunuh diri.
Yang keenam adalah Howard Hupson, CEO perusahaan gas terbesar di Amerika Utara. Tetapi ia sakit jiwa dan dirawat di rumah sakit jiwa hingga akhir hidupnya. Arthur Cutton sebelumnya adalah pemilik pabrik tepung terbesar di dunia, tetapi ia meninggal di negri orang lain.
Sedangkan Albert Fall, waktu itu ia adalah anggota kabinet presiden Amerika Serikat. Namun ia meninggal di rumahnya di Texas ketika baru saja keluar dari penjara.
Di dunia ini tidak sedikit orang yang semula sangat sukses, tetapi merana di tahun-tahun terakhir kehidupan mereka.
Kehidupan mereka seakan-akan tidak berarti meskipun sebelumnya sangat kaya raya. Upaya terbaik memang dapat menghasilkan kesuksesan besar, tetapi bukan berarti merupakan jaminan sebuah akhir kehidupan sebagai manusia yang penuh arti.
Karena itu langkah berikutnya yang harus kita lakukan adalah mengimbangi kerja keras dengan berbuat kebaikan.
Seorang penulis pada abad 20-an yang berkebangsaan Perancis, Andr?Gide, mendefinisikan kebaikan itu sebagai berikut; "True kindness presupposes the faculty of imagining as one's own the suffering and joys of others. ?nbsp; Kebaikan yang sesungguhnya adalah kemampuan merasakan penderitaan maupun kebahagiaan orang lain."
Kerja keras yang diimbangi dengan berbuat kebaikan akan menghasilkan semangat yang tinggi untuk mendapatkan lebih dari apa yang dibutuhkan. Hal itu terdorong oleh keinginan untuk dapat berbagi kebahagiaan dengan orang lain. Pada akhirnya kebaikan tersebut berpengaruh positif terhadap semangat hidup, motivasi, dan kemajuan sikap dan ekonomi. James Allen, penulis buku berjudul As a Man Thinketh mengatakan, "Pemikiran serta perbuatan baik tidak mungkin mendatangkan hasil yang buruk; pemikiran dan perbuatan buruk tidak mungkin mendatangkan hasil baik."
Dengan belajar, bekerja keras dan berbuat kebaikan maka kita akan dapat menciptakan kehidupan yang jauh lebih berarti.
Langkah-langkah sebagaimana dijelaskan diatas terbukti juga sangat efektif menjadikan kesan positif tentang diri kita tidak mudah dilupakan orang. Saya meyakini bahwa kita masih mempunyai banyak kesempatan dan potensi untuk mendapatkan kehidupan berharga itu dimanapun dan apapun pekerjaan kita.
Sumber: Make A Life, Not Merely A Living - Ciptakan Kehidupan, Bukan Sekedar Hidup
By Andrew Ho