Sql Assistant

hi friends, today morning i find one issues to find available sheet in Excel workbook.. so here is solution for this. first of all we has to upload that file to server [excel] folder and then read excel sheet with oledb provider. 1. create aspx page with having two controls      File upload controls with name [flUpldExcel]      One command button to perform action.  than once we save excel sheet to server so next step is to find available sheet in work book. Now want to find available sheets on workbook. for that i write code that will find all sheets. Code for saving file on server  try         {             string strFilePath = Server.MapPath("excel") + @"\";             string strFileName = string.Empty;             if (flUpldExcel.HasFile)             {                 strFileName = flUpldExcel.PostedFile.FileName;                 flUpldExcel.SaveAs(strFilePath + strFileName);                 string[] strSheetName;                 strSheetName = GetEx

In this very short tip I'd like to share my solution to a yesterday quesiton in MSDN forum - how to find the database the table belongs to. I use my favorite idea of dynamically constructing a query using INFORMATION_SCHEMA.TABLES view. DECLARE @table sysname, @SQL NVARCHAR(MAX) SET @table = 'Items' SET @SQL = '' SELECT @SQL = @SQL + '; IF EXISTS (SELECT 1 from ' + QUOTENAME(name) + '.INFORMATION_SCHEMA.Tables WHERE Table_Name = @table AND TABLE_TYPE = ''BASE TABLE'') PRINT ''Table ' + @table + ' found in ' + name + '''' FROM sys.databases   EXECUTE sp_executeSQL @SQL, N'@table sysname', @table That's all. Try it with the table which you may have in multiple databases. I used a bit undocumented way of concatenating multiple rows in a string, so I can not actually use ORDER BY in this code. To be strict, I need to use XML PATH('') approach here instead.

1) "Learn from the mistakes of others... you can 't live long enough to make them all yourselves!!" - Chanakya 2)"A person should not be too honest. Straight trees are cut first and Honest people are screwed first." - Chanakya 3)"Even if a snake is not poisonous, it should pretend to be venomous." Chanakya 4)"There is some self-interest behind every friendship. There is no friendship without self-interests. This is a bitter truth." - Chanakya                                                              5)" Before you start some work, always ask yourself three questions - Why am I doing it, What the results might be and Will I be successful. Only when you think deeply and find satisfactory answers to these questions, go ahead." - Chanakya 6)"As soon as the fear approaches near, attack and destroy it." - Chanakya 7)"The world's biggest power is the youth and beauty of a woman." - Chanakya

What is ACID properties and How SQL Server Comply to the same Answer ACID  Propertied is Acronym for A- Atomicity-  Either all the operations/transactions are performed or None.Each transaction is said to be atomic if when one part of the transaction fails, the entire transaction fails and database state is left unchanged. C- Consistency-  This states that at a given moment of time the database is always in consistent. This guarantees that a transaction never leaves your database in a half-finished state. I- Isolation-  Isolation ensures that the transactions are separated(Isolated) from each other until they are finished. This means the transactions does not interfere with one another. D- Durability-  This ensures that once a transaction is commited it is available forever at any given moment of time. This guarantee that once the user has been notified of a transaction's success the transaction will not be lost, the transaction's data changes will survive system failure,

Method Overloading ------------------ Method Overloading means having two or more methods with the same name but different signatures in the same scope. These two methods may exist in the same class or one in base class and another in the derived class. The signature of a function is determined by 3 factors: a) Number of arguments received by the function. b) Data types of the parameters/arguments. c) Position/order of the arguments. The signature of a function never depends upon the return type. Two functions differ only in their return type cannot be overloaded. Example of Method Overloading ----------------------------- public partial class _Default : System.Web.UI.Page { protected void Button1_Click(object sender, EventArgs e) { MyBaseClass mbc = new MyBaseClass(); Response.Write(mbc.AddNumbers(1,2).ToString()); Response.Write("<br />" + mbc.AddNumbers(1, 2, 3).ToString()); } } public class MyBaseClass { public int AddNumb

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