HTTP EndPoint in Microsoft SQL-Server 2005 –Implemented in Visaul Studio 2008

 HTTP EndPoint in Microsoft SQL-Server 2005 –Implemented in Visaul Studio 2008

You must have heard the terms “Web Service”. This is not something new. People used to read and write many things about this term from year of 2000 for Microsoft Technology. It was inbuilt tool in .NET Framework but it is bit new concept in SQL-Server as it has been implemented from Microsoft SQL-Server 2005. “Web Service” is known as “HTTP ENDpoint” in SQL-Server.  It has opened the door for cross platform compatibility.

If you wants to built WebService then you have to have IIS server but in case of HTTP EndPoint, you don’t need any IIS server rather it is directly hooked up with kernel mode driver “http.sys”. Yes, it has some security concerns and issues but there are also some mechanisms to prevent the security thread.

You can use SQL-Server function or else SQL-Server Stored Procedure along with HTTP Endpoint. You can create two type of end point in SQL-Server 2005. 1.)TCP Endpoint and 2.) HTTP Endpoint.

Let us see how we can define HTTP Endpoint and can use it in Windows application in C#.NET.

First let us create one simple stored procedure for Adventure Works database.

–SP will be used in HTTP Endpoint and will return

–record set to calling application

USE AdventureWorks

GO

CREATE PROC Testing

AS

SELECT Name,GroupName FROM HumanResources.Department

 

This is very simple stored procedure one can ever make. J Now it is time to implement this stored procedure in ENDpoint.

 

CREATE ENDPOINT Test_END_Point –This will create HTTP Endpoint

AUTHORIZATION sa  –this is something absolutely optional authoraization for db owner

STATE = STARTED  — State could be STARTED, STOPPED and DISABLE

AS HTTP  — You can create HTTP or TCP endpoint

( 

   PATH = ‘/SQLENDPOINT’,  –virtual path, will be used in adding reference in web or windows app.

   AUTHENTICATION = (INTEGRATED),  –authentication type for endpoint

   PORTS = (CLEAR),   — PORT coulbe be all (CLEAR) or may be SSL

   SITE = ‘localhost’  –site name, in this case “localhost” as I am running it locally

) 

FOR SOAP  –protocol type

( 

   WEBMETHOD ‘getVal’ — you can define more than one webmethod also to expose

                  (

                        NAME = ‘AdventureWorks.dbo.Testing’, SCHEMA = STANDARD, 

                        FORMAT = ROWSETS_ONLY

                  ), 

   WSDL = DEFAULT,  –this will generate WSDL as per request

   BATCHES = DISABLED  –you could enable BATCHES but it becomes security thread

) 

As soon as you will done with creating this Endpoint, we will move forward to implement this in windows application.

Here are the steps to implement HTTP Endpoint  in your Windows application in Visual Studio 2008.

—Create one new project from File->New->Project

 11

 

—Draw One button and dataGridView in your webform.

2

 

— Add reference of HTTP Endpoint in your application. Right click on your project name in Solution Explorer and click on “Add Service reference”, you will see on dialog box and you will have to click on “Advance” button at left bottom corner and you will see another dialog box like below.

3

 

— Add reference of HTTP Endpoint in your application. Right click on your project name in Solution Explorer and click on “Add Service reference”, you will see on dialog box and you will have to click on “Advance” button at left bottom corner and you will see another dialog box like below.

4

 

Once you final add the reference, you will have to code following snippet under the click event of your button.

private void button1_Click(object sender, EventArgs e)

{

localhost.Test_END_Point tEnd = new ForHTTPendPoint.localhost.Test_END_Point();

  tEnd.Credentials = System.Net.CredentialCache.DefaultCredentials;

  dataGridView1.DataSource = tEnd.getVal().Tables[0];

}

Hey, you just finished your first HTTP Endpoint. What are you waiting for?  Runt it now!!!!

Reference: Ritesh Shah

 

Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005

Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005

As a developer, many times we need to delete all the records from all tables. For example we are developing one application in our development server. At the time of developing we must have entered lots of temporary data in our database; we shouldn’t upload all those data along with structure at live server. We should have to delete all dummy and temp data.

Note: This article is moved to following URL on 6th NOV 2012. Kindly use following link:

http://blog.extreme-advice.com/2009/03/04/empty-all-table-or-delete-all-data-from-all-table-by-sp_msforeachtable-in-sql-server-2005/

sp_MSforeachtable: Undocumented but useful stored procedure for auto iteration in SQL-Server 2005.

sp_MSforeachtable:  Undocumented but useful stored procedure for auto iteration in SQL-Server 2005.

sp_MSforeachtable is one of the undocumented stored procedure in Microsoft SQL-Server 2005. It has an amazing facility of auto iteration. It means that it will run T-SQL command for each and every single table of your database. As other system SP, you can find this SP in MASTER DB. If you are a member of sysadmin then you can use this SP very beautifully for your task.

Let’s look at its example:

Note: Create one dummy database and use this example in it only as it will take effect throughout the database and will affect all tables resides in that database.

USE DummyDB

GO

EXEC sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

GO

 

Above query will disable all the constraints of all tables in DummyDB. “?”  In above query will be replaced automatically by table name internally and will iterate the loop for all table automatically.

How easy is this??????

 

Reference: Ritesh Shah

Startup Stored Procedure -SQL-Server 2005

 Startup Stored Procedure -SQL-Server 2005

All of you may aware with Stored Procedure but you may not aware with startup stored procedures which suppose to run every time SQL-Server’s services restart.

This task may be useful for some administrative task, i.e.: you may know that TempDB will be recreated from scratch whenever server restarts. You may want some user to grant permission of TempDB every time it is being created.

Before you start generating startup stored procedure, have a look at some basic ground regarding that.

You have to make sure that ‘Scan for Startup Proc’ option is set to 1. You can see that option and make it disable and enable with following queries.

 

–this will show you all advance option

EXEC sp_configure ‘show advanced option’, ‘1’;

RECONFIGURE

 

–this will enable startup procedure

EXEC sp_configure ‘scan for startup procs’, ‘1’;

RECONFIGURE

Now, you can create stored procedure and it will be set as startup proc, before you create it, you have to follow some strict guideline.

–Procedure should be reside in MASTER database
–Its owner should be DBO
–Mark your stored procedure as startup stored procedure with sp_procoption
–Only sysAdmin can set sp_procoption
–Your procedure shouldn’t have any input parameter or return any output parameter

Now, we will create one stored procedure and will mark it as startup procedure.

–Creating strored procedure

CREATE PROC spCreateDatabaseAtStartup

AS

      EXEC(‘CREATE database StartupDatabase’)

GO

 

–Marking SP as startup

exec sp_procoption N‘spCreateDatabaseAtStartup’, ‘startup’, ‘on’

In sp_procoption first parameter is our stored procedure name. Second parameter is “OptionName”  but fortunately or unfortunately there is only one option name “startup” J and third parameter is “OptionValue” that should be “on” or “off”.

Reference: Ritesh Shah

Create your own system stored procedure – SQL-SERVER 2005

 

 Create your own system stored procedure – SQL-SERVER 2005

Sometime, its great help if we can create our own stored procedure like any other system stored procedure. It will help us to access that stored procedure from any of the database within one sql-server instance though it have been created only once.

If you create any stored procedure in MASTER database with “sp_” prefix and mark it as system stored procedure than you can take an advantage of name resolution algorithm of engine. Don’t create any stored procedure with “sp_” prefix in your own database as it incur burden on engine, because when you try to execute stored procedure with “sp_” prefix, engine will first try to search it in MASTER database.

We will create one stored procedure in MASTER database.

CREATE PROC sp_list_of_Employees

AS

SELECT * FROM Employee

RETURN

I am assuming that you are having one table, name “Employee” in “AdventureWorks” database but not in “MASTER” database, though we have created above stored procedure in MASTER database.

Now, try to run above stored procedure.

USE MASTER
GO
exec sp_list_of_Employees

As soon as you will run this, you will be greeted with an error:

Msg 208, Level 16, State 1, Procedure sp_list_of_Employees, Line 4

Invalid object name ‘Employee’.

If you have “Employee” table in “AdventureWorks” database then try to run procedure in AdventureWorks database.

USE AdventureWorks
GO
exec sp_list_of_Employees

Again you will be greeted with the same error:

Msg 208, Level 16, State 1, Procedure sp_list_of_Employees, Line 4

Invalid object name ‘Employee’.

Though you ran the procedure from AdventureWorks database, but it is still looking at “MASTER” database for “Employee” table, because we have not marked it as system routines.

We can mark stored procedure with below given command:

USE MASTER

 EXECUTE sp_ms_marksystemobject ‘sp_list_of_Employees’

Now, try to run SP in AdventureWorks database and you will get results or you can run this stored procedure in any of the database in your SQL instance, which have Employee table.

USE AdventureWorks
GO
exec sp_list_of_Employees

Hope you have enjoyed reading.

Happy SQLing!!!!

 

Reference: Ritesh Shah

 

Reference: Ritesh Shah