How do I loop through a record set in a stored procedure?

i came across a situation where i had to loop through a recordset, have to pick some column values and need to insert it to another table.

A sample snippet as below :

DECLARE MyCursor CURSOR READONLY FORWARD SELECT id FROM mainNav ORDER BY sort,id

OPEN MyCursor
READ NEXT FROM MyCursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT id,label,url,sort,mainnavid FROM SubNav

WHERE mainNavID=@id ORDER BY sort,id
READ NEXT FROM MyCursor INTO @id
END
CLOSE MyCursor
DEALLOCATE MyCursor

attached the file script that i have used for the project.

-- ========================
-- Inserting Notifications –
-- =========================

ALTER PROCEDURE [dbo].[NotificationJob] AS
declare @TransId Int
declare @ToUserId Int
declare @RequestId Int
declare @FileId Int
declare @NoOfDays int
declare @TransType nvarchar(50)
declare @TransTypeId int
declare @NotificationType nvarchar(50)
declare @Active Int
declare @Description nVarchar (max)
declare @CreatedBy nvarchar(50)
declare @RecordCount Int

set @CreatedBy='DB_JOB'

--opening a cursor to select records from the Transaction table

DECLARE CUR_Notification
CURSOR FOR
SELECT
FileTransaction.TransId,
FileTransaction.UserId,
FileTransaction.FlowID,
FileTransaction.FileId,
FLOOR(CONVERT(real, GETDATE()) - FileTransaction.TransDate),
FileTransactionType.Name,
FileTransactionType.TransTypeId
FROM
FileTransaction
INNER JOIN
FileTransactionType
ON
FileTransaction.TransTypeId = FileTransactionType.TransTypeId
where
FLOOR(CONVERT(real, GETDATE()) - FileTransaction.TransDate) > 12
AND
FileTransactionType.TransTypeId= dbo.GetTransStatusId('Issue')
Or
FileTransactionType.TransTypeId= dbo.GetTransStatusId('Forward')
Or
FileTransactionType.TransTypeId= dbo.GetTransStatusId('AcceptReturn')

-- Opening the Cursor

OPEN CUR_Notification
FETCH NEXT FROM CUR_Notification
INTO @TransId, @ToUserId, @RequestId,@FileId, @NoOfDays, @TransType, @TransTypeId

declare @NotificationDate real

set @NotificationDate=(select CONVERT(real, GetDate()))

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @RecordCount=COUNT(*) FROM FRSNotification
WHERE
FRSNotification.TransID=@TransId
AND
FRSNotification.ToUserID=@ToUserId
AND
FRSNotification.RequestID=@RequestId

-- checking whether is there are any records exists

if @RecordCount=0
exec FRSNotification_Insert @TransId, @ToUserId,
@RequestId, @FileId, @TransTypeId, 1,
@NotificationDate, null, @TransType,
@NotificationDate, @CreatedBy

FETCH NEXT FROM CUR_Notification
INTO @TransId, @ToUserId, @RequestId,@FileId,
@NoOfDays, @TransType, @TransTypeId

END

--cleaning up operations
CLOSE CUR_Notification
DEALLOCATE CUR_Notification

SQL Doc™

Document SQL Server 2000, 2005 and 2008 databases without any hassle.

• Fast, simple database documentation
• Document one or multiple databases, down to object level
• Write, edit, and export documentation in easy-to-read HTML files or .chm files for team distribution
• Includes all cross-database dependencies

download trial version :
http://www.red-gate.com/supportcenter/Product.aspx?p=SQL%20Doc

How to identify your SQL Server 2005 version and edition

To determine which version of Microsoft SQL Server 2005 is running, connect to SQL Server 2005 by using SQL Server Management Studio, and then run the following Transact-SQL statement:

SELECT
SERVERPROPERTY('productversion') as productversion,
SERVERPROPERTY ('productlevel') as productlevel,
SERVERPROPERTY ('edition') as edition

The results are:

* The product version (for example, "9.00.1399.06").
* The product level (for example, "RTM").
* The edition (for example, "Enterprise Edition").

sample results :

9.00.1399.06 RTM DeveloperEdition

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

One of strange error that i had to deal with is the one mentioned above. The error used to prompt more frequently when the number of records increased in table. So at the initial stage of developments you won't have this timeout error. But when the system is in operation level and when the production data increases you may haev to come across this timeout error.

There are two main Timeout property in ADO.NET.

1. Connection Timeout for Connection
2. Timeout for Data Access( Command Object or DataAdapter)

Setting Connection Timeout property for SQLConnnection object ?

You can't set timeout property in connection object because its a READ ONLY property.

The proper way to do this is to set the timeout value in connection string as mentioned below.

SqlConnection conn = new SqlConnection("server=Server;uid=sa;pwd=123456;database=myDB;Connection Timeout=120");

Setting the timeout property in DataAdapter or CommandObject.

SqlDataAdapter sqlAdapter = new SqlDataAdapter();
sqlAdapter.SelectCommand = new SqlCommand(sqlQuery, conn);
sqlAdapter.SelectCommand.CommandTimeout = 0;

Setting the timeout value to 0 will makes adapter or command to wait for indefinite time before terminating the attempt to execute a command and generating an error. The default timeout value for connection object is 15 seconds.

hope this might help you to solve the problem.

Working with System Tables in SQL 2005

Find ALL tables that have columns with an identity property

SQL SERVER 2000:

select object_name(id),name from syscolumns where columnproperty(id,name,'IsIdentity')=1 

SQLSERVER 2005:

select object_name(object_id),name from sys.identity_columns 

Note: SQL Server 2005 stores the last value of the identity property that was generated. To query the last value execute the following query.

select name,last_value from sys.identity_columns

Find ALL columns in a table that are computed columns

SQL SERVER 2000:

select name from syscolumns where id =object_id('TableName') and iscomputed=1

SQL SERVER 2005:

select name from sys.computed_columns where object_id =object_id('TableName')

Note: The computed column in SQL Server 2005 may be persisted. To narrow down the result set, you could execute the following query:

select * from sys.computed_columns where is_persisted=0

Find ALL database names in a SQL Server instance

SQL SERVER 2000:

select name from master..sysdatabases

SQL SERVER 2005:

select name from sys.databases

Note: Many enhancements were made to the database. Query all of the columns in sys.databases to understand the new enhancements like snapshot, etc.

Find ALL Procedures in a Database

SQL SERVER 2000:

select name from sysobjects where type='P'

SQL SERVER 2005:

select name from sys.procedures

Note: You can find whether the stored procedure execution is used in replication or if the stored procedure is a startup procedure. Execute the following queries:

select name from sys.procedures where is_execution_replicated=1
select name from sys.procedures where is_auto_executed=0

Find ALL tables in a Database

SQL SERVER 2000:

select name from sysobjects where type='U'

SQL SERVER 2005:

select name from sys.tables

Note: In SQL Server 2005, you can find whether a table is replicated. Execute the following query.

select * from sys.tables  where is_replicated =1

Find ALL views in a Database

SQL SERVER 2000:

select name from sysobjects where type='V'

SQL SERVER 2005:

select name from sys.views

Note: In SQL Server 2005, you can find whether a view is replicated. Execute the following query.

select * from sys.views where is_replicated =1

Find ALL Triggers in a Database

SQL SERVER 2000:

select name from sysobjects where type='TR'

SQL SERVER 2005:

select name from sys.triggers where parent_class=1

Note: In SQL Server 2005, the triggers can be Assembly trigger (CLR) or a SQL trigger. In addition, we can find whether the trigger is an AFTER trigger or INSTEAD of trigger. Execute the following query:

select name from sys.triggers where type='TA'
select name from sys.triggers where type='TR'
select name from sys.triggers where is_instead_of_trigger=1

Find ALL SQL logins in a server

SQL SERVER 2000:

select * from master..syslogins where isntgroup=0 and isntname=0

SQL SERVER 2005:

select * from sys.sql_logins

Find ALL dependencies of the SQL Object in a Database

SQL SERVER 2000:

select * from sysdepends

SQL SERVER 2005:

select * from sys.sql_dependencies

Find ALL data types in SQL server

SQL SERVER 2000:

select * from systypes

SQL SERVER 2005:

select * from sys.systypes

Find ALL error messages in SQL server

SQL SERVER 2000:

select * from master..sysmessages

SQL SERVER 2005:

select * from sys.messages

Find ALL the database files of the current database

SQL SERVER 2000:

select name,filename from sysfiles

SQL SERVER 2005:

select name, physical_name from sys.database_files

Find the type of index

SQL SERVER 2000: We have to use indid column to determine the type of index from 0,1 or 255.

SQL SERVER 2005:

select object_name(object_id),name, type_desc  from sys.indexes where type_desc ='CLUSTERED'
select object_name(object_id),name, type_desc  from sys.indexes where type_desc ='HEAP'
select object_name(object_id),name, type_desc  from sys.indexes where type_desc ='NONCLUSTERED'
select object_name(object_id),name, type_desc  from sys.indexes where type_desc ='XML'

Google Jumps Head First Into Web Services With Google App Engine

Google App Engine is designed for developers who want to run their entire application stack, soup to nuts, on Google resources. Amazon, by contrast, offers more of an a la carte offering with which developers can pick and choose what resources they want to use.

Google Product Manager Tom Stocky described the new service to me in an interview today. Developers simply upload their Python code to Google, launch the application, and can monitor usage and other metrics via a multi-platform desktop application.

More details from Google:

Today we’re announcing a preview release of Google App Engine, an application-hosting tool that developers can use to build scalable web apps on top of Google’s infrastructure. The goal is to make it easier for web developers to build and scale applications, instead of focusing on system administration and maintenance.

Leveraging Google App Engine, developers can:

• Write code once and deploy. Provisioning and configuring multiple machines for web serving and data storage can be expensive and time consuming. Google App Engine makes it easier to deploy web applications by dynamically providing computing resources as they are needed. Developers write the code, and Google App Engine takes care of the rest.
• Absorb spikes in traffic. When a web app surges in popularity, the sudden increase in traffic can be overwhelming for applications of all sizes, from startups to large companies that find themselves rearchitecting their databases and entire systems several times a year. With automatic replication and load balancing, Google App Engine makes it easier to scale from one user to one million by taking advantage of Bigtable and other components of Google’s scalable infrastructure.
• Easily integrate with other Google services. It’s unnecessary and inefficient for developers to write components like authentication and e-mail from scratch for each new application. Developers using Google App Engine can make use of built-in components and Google’s broader library of APIs that provide plug-and-play functionality for simple but important features.

Google App Engine: The Limitations

The service is launching in beta and has a number of limitations.

First, only the first 10,000 developers to sign up for the beta will be allowed to deploy applications.

The service is completely free during the beta period, but there are ceilings on usage. Applications cannot use more than 500 MB of total storage, 200 million megacycles/day CPU time, and 10 GB bandwidth (both ways) per day. We’re told this equates to about 5M pageviews/mo for the typical web app. After the beta period, those ceilings will be removed, but developers will need to pay for any overage. Google has not yet set pricing for the service.

One current limitation is a requirement that applications be written in Python, a popular scripting language for building modern web apps (Ruby and PHP are among others widely used). Google says that Python is just the first supported language, and that the entire infrastructure is designed to be language neutral. Google’s initial focus on Python makes sense because they use Python internally as their scripting language (and they hired Python creator Guido van Rossum in 2005).

Source

Microsoft Silverlight

Microsoft Silverlight is the next generation, cross browser web client, cross platform runtime. Silverlight is a lightweight subset of XAML for building rich media experiences on the web.

http://www.microsoft.com/silverlight/

Visual Studio 2008

you can download the VS 2005 and SQL Server 2008 Express Edition from here :

http://www.microsoft.com/express/download/#webInstall