Bulk inserting into Sql Server

Hello Everyone,

I am currently struggling with a problem bulk inserting data into sql server.

The application I am writing is multi-threaded and downloads about 2000 records every 2 seconds on x amount of threads (depending on bandwidth).

What I would like to do is find a 'friendly' way to insert this data into sql server without hammering the cpu.. I have tried the following with little success.

1, Using a single insert and thread.sleep(x * 20) to allow for massive data input, altough this made the application more stable and lowered cpu usage to very little the data takes about 60 times longer to download and process into the database.

2, Using a SqlDataAdapter and DataSet and updating the database via the .Update method of the data adapter.. Simply this was awful and took forever to process the data into the database.. (Took about 30 seconds to process 2000 records and Command Timeout was high).

3, Using OpenXML in SqlServer and parsing the data as an XML string (nText), although this method is fast its still very CPU intensive. I have to set the command timeouts very high to allow for this approach (because of the multi-threaded nature of the app).

Does anyone have any idea's on a cpu friendly approach to this problem ??

Thanks in advance..

Gary.You can actually use BCP

BULK INSERT errors

I'm writing a .NET component that will bulk insert a file into a table.
I also have a string array inside the class whose purpose is to
capture the errors during the bulk insert process. Can this be done?If you are using the SqlBulkCopy class, an error will rollback the batch
containing the problem row and the WriteToServer operation will terminate.
In order to trap insert errors on individual rows yet still save good data,
you could use a hybrid technique in which you write a batch at a time using
WriteToServer and fall back on individual insert commands if an error is
encountered.
1) Read a batch of records from file (e.g. 1000)
2) Write records as a single batch via SqlBulkCopy.WriteToServer
3) If a SqlException is thrown, insert rows individually and trap errors
Note that you'll also need to address file parsing errors, such as an
unexpected number of fields in a CSV record.
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149281083.751510.288450@.y43g2000cwc.googlegroups.com...
> I'm writing a .NET component that will bulk insert a file into a table.
> I also have a string array inside the class whose purpose is to
> capture the errors during the bulk insert process. Can this be done?
>|||That's my biggest hurdle. I'm tied to using 1.1 Framework that doesn't
have the SqlBulkCopy class :(
I do plan on sending a batch parameter to control for that. Maybe
there's a property for the OleDbCommand that captures errors.
Dan Guzman wrote:
> If you are using the SqlBulkCopy class, an error will rollback the batch
> containing the problem row and the WriteToServer operation will terminate.
> In order to trap insert errors on individual rows yet still save good data
,
> you could use a hybrid technique in which you write a batch at a time usin
g
> WriteToServer and fall back on individual insert commands if an error is
> encountered.
> 1) Read a batch of records from file (e.g. 1000)
> 2) Write records as a single batch via SqlBulkCopy.WriteToServer
> 3) If a SqlException is thrown, insert rows individually and trap errors
> Note that you'll also need to address file parsing errors, such as an
> unexpected number of fields in a CSV record.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <tobinlim@.gmail.com> wrote in message
> news:1149281083.751510.288450@.y43g2000cwc.googlegroups.com...|||With OleDbCommand (and SqlCommand) about all you can do is try/catch. If
you send multiple statements in a batch, it's hard to identify which insert
caused the error.
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149365527.051997.242230@.u72g2000cwu.googlegroups.com...
> That's my biggest hurdle. I'm tied to using 1.1 Framework that doesn't
> have the SqlBulkCopy class :(
> I do plan on sending a batch parameter to control for that. Maybe
> there's a property for the OleDbCommand that captures errors.
> Dan Guzman wrote:
>|||What I meant is my batchsize is something that gets passed on to a BULK
INSERT statement: "BATCHSIZE = " + intBatchSize + ", ..."
Anyway, if what you're saying is true, I might have to force the
batchsize to 1 and bulk insert one row at a time and try to catch
errors.
Is this what you're saying?
Dan Guzman wrote:
> With OleDbCommand (and SqlCommand) about all you can do is try/catch. If
> you send multiple statements in a batch, it's hard to identify which inser
t
> caused the error.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <tobinlim@.gmail.com> wrote in message
> news:1149365527.051997.242230@.u72g2000cwu.googlegroups.com...|||Let me try to clarify. Some errors, like a primary key violation, will
abort the entire BULK INSERT batch. You'll need to resort to individual
inserts to identify the problem data and persist the good records. I
wouldn't recommend BULK INSERT with batch size of 1 because BULK INSERT is
optimized for large volumes of data. A batch size 1 will be much slower
than a regular inserts and defeat the purpose of BULK INSERT.
Other errors, such as a data type conversion error, will err the problem
record but not the batch. This will still throw a SqlException and you can
iterate over the Errors collection to get the details of the individual
errors. Unfortunately, it's not easy to programmatically distinguish
between errors that abort the BULK INSERT batch and those that err
individual records.
If your data is usually clean and you want the performance of BULK INSERT,
you might consider a modified version of the approach I suggested earlier:
1) BEGIN TRAN
2) BULK INSERT entire file
3) If no errors, COMMIT
4) If errors, ROLLBACK and use individual inserts to identify problem data
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149512589.648528.145350@.g10g2000cwb.googlegroups.com...
> What I meant is my batchsize is something that gets passed on to a BULK
> INSERT statement: "BATCHSIZE = " + intBatchSize + ", ..."
> Anyway, if what you're saying is true, I might have to force the
> batchsize to 1 and bulk insert one row at a time and try to catch
> errors.
> Is this what you're saying?
> Dan Guzman wrote:
>|||Thanks. I discovered the SqlException object in .NET. Very useful.
It's pretty much what I've been looking for. Now I noticed that if
multiple errors occur within a row insert, SqlException captures the
first error and moves on. I don't necessarily need to have all the
errors for each row. Just want to understand the nature of BULK INSERT
errors.
Dan Guzman wrote:
> Let me try to clarify. Some errors, like a primary key violation, will
> abort the entire BULK INSERT batch. You'll need to resort to individual
> inserts to identify the problem data and persist the good records. I
> wouldn't recommend BULK INSERT with batch size of 1 because BULK INSERT is
> optimized for large volumes of data. A batch size 1 will be much slower
> than a regular inserts and defeat the purpose of BULK INSERT.
> Other errors, such as a data type conversion error, will err the problem
> record but not the batch. This will still throw a SqlException and you ca
n
> iterate over the Errors collection to get the details of the individual
> errors. Unfortunately, it's not easy to programmatically distinguish
> between errors that abort the BULK INSERT batch and those that err
> individual records.
> If your data is usually clean and you want the performance of BULK INSERT,
> you might consider a modified version of the approach I suggested earlier:
> 1) BEGIN TRAN
> 2) BULK INSERT entire file
> 3) If no errors, COMMIT
> 4) If errors, ROLLBACK and use individual inserts to identify problem data
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <tobinlim@.gmail.com> wrote in message
> news:1149512589.648528.145350@.g10g2000cwb.googlegroups.com...|||> Thanks. I discovered the SqlException object in .NET. Very useful.
> It's pretty much what I've been looking for. Now I noticed that if
> multiple errors occur within a row insert, SqlException captures the
> first error and moves on.
Have you looked at SqlException.Errors? This contains a collection of
SqlError objects and contains details of row-level errors in my test.
However, as I mentioned earlier, you might find it problematic to
distinguish between errors that roll back the entire batch and errors that
err individual records.
Hope this helps.
Dan Guzman
SQL Server MVP
<tobinlim@.gmail.com> wrote in message
news:1149605426.090781.127220@.j55g2000cwa.googlegroups.com...
> Thanks. I discovered the SqlException object in .NET. Very useful.
> It's pretty much what I've been looking for. Now I noticed that if
> multiple errors occur within a row insert, SqlException captures the
> first error and moves on. I don't necessarily need to have all the
> errors for each row. Just want to understand the nature of BULK INSERT
> errors.
>
> Dan Guzman wrote:
>

Bulk insert causing timeout

Hi,
I'm using Bulk Insert to load a file about 1.5GB in size. When I do
this, another process which is continually reading and writing to other
tables in the same database gets a connection time out error. Why might
that be? Is the bulk insert just hogging the i/o?
Also, if I were to load the file into a different database on the same
server am I likely to still get the same error?
Thanks,
Nick
Hi Nick
If you look at the performance counters you may see what the issue with
system is!
You may also want to look at the output from sp_lock just to make sure that
each process is not locking itself out, there may be a trigger that you are
forgetting! If the latter is the case then loading into a staging table may
help, but you could still get blocking when you transfer the information to
the main table, in which case you may have to do the transfer in phases.
John
"nicholastoze@.gmail.com" wrote:

> Hi,
> I'm using Bulk Insert to load a file about 1.5GB in size. When I do
> this, another process which is continually reading and writing to other
> tables in the same database gets a connection time out error. Why might
> that be? Is the bulk insert just hogging the i/o?
> Also, if I were to load the file into a different database on the same
> server am I likely to still get the same error?
>
> Thanks,
> Nick
>

Bulk insert causing timeout

Hi,
I'm using Bulk Insert to load a file about 1.5GB in size. When I do
this, another process which is continually reading and writing to other
tables in the same database gets a connection time out error. Why might
that be? Is the bulk insert just hogging the i/o?
Also, if I were to load the file into a different database on the same
server am I likely to still get the same error?
Thanks,
NickHi Nick
If you look at the performance counters you may see what the issue with
system is!
You may also want to look at the output from sp_lock just to make sure that
each process is not locking itself out, there may be a trigger that you are
forgetting! If the latter is the case then loading into a staging table may
help, but you could still get blocking when you transfer the information to
the main table, in which case you may have to do the transfer in phases.
John
"nicholastoze@.gmail.com" wrote:

> Hi,
> I'm using Bulk Insert to load a file about 1.5GB in size. When I do
> this, another process which is continually reading and writing to other
> tables in the same database gets a connection time out error. Why might
> that be? Is the bulk insert just hogging the i/o?
> Also, if I were to load the file into a different database on the same
> server am I likely to still get the same error?
>
> Thanks,
> Nick
>

Bulk insert causing timeout

Hi,
I'm using Bulk Insert to load a file about 1.5GB in size. When I do
this, another process which is continually reading and writing to other
tables in the same database gets a connection time out error. Why might
that be? Is the bulk insert just hogging the i/o?
Also, if I were to load the file into a different database on the same
server am I likely to still get the same error?
Thanks,
NickHi Nick
If you look at the performance counters you may see what the issue with
system is!
You may also want to look at the output from sp_lock just to make sure that
each process is not locking itself out, there may be a trigger that you are
forgetting! If the latter is the case then loading into a staging table may
help, but you could still get blocking when you transfer the information to
the main table, in which case you may have to do the transfer in phases.
John
"nicholastoze@.gmail.com" wrote:
> Hi,
> I'm using Bulk Insert to load a file about 1.5GB in size. When I do
> this, another process which is continually reading and writing to other
> tables in the same database gets a connection time out error. Why might
> that be? Is the bulk insert just hogging the i/o?
> Also, if I were to load the file into a different database on the same
> server am I likely to still get the same error?
>
> Thanks,
> Nick
>

Building a Where Clause

I am not very experienced with stored procs and I'm attempting to write my first one. I am writing a search page via aspx and that page will call my proc and depending on the input parameters, the proc will return the search results. To do this I have built a where clause string but I don't know how to (if it's even possible) make this variable part of my query. Can anyone tell me a way to make the following work (input params left out to conserve space)?

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNTON;

SET ANSI_WARNINGSOFF

SET @.where=''

IF @.JobNoStart!=''SET @.where=+' AND LJOB BETWEEN @.JobNoStart AND @.JobNoEnd'

IF @.OrderDateStart!=''SET @.where=+' AND JOBDATE BETWEEN @.OrderDateStart AND @.OrderDateEnd'

IF @.DueDateStart!=''SET @.where=+' AND DUEDATE BETWEEN @.DueDateStart AND @.DueDateEnd'

IF @.ProofDateStart!=''SET @.where=+' AND PROOFDUE BETWEEN @.ProofDateStart AND @.ProofDateEnd'

IF @.CloseDateStart!=''SET @.where=+' AND CLOSEDATE BETWEEN @.CloseDateStart AND @.CloseDateEnd'

IF @.CogsDateStart!=''SET @.where=+' AND COGSDATE BETWEEN @.CogsDateStart AND @.CogsDateEnd'

IF @.ProductName!=''SET @.where=+' AND PRODUCT = @.ProductName'

IF @.CustomerNumber!=''SET @.where=+' AND FCUSTNO = @.CustomerNumber'

IF @.SalesPerson!=''SET @.where=+' AND FSALESPN = @.SalesPerson'

IF @.CSR!=''SET @.where=+' AND JOBPER = @.CSR'

IF @.Closed= 0SET @.where=+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'

ELSEIF @.Closed= 1SET @.where=+' AND CLOSEDATE IS NULL'

ELSEIF @.Closed= 2SET @.where=+' AND CLOSEDATE IS NOT NULL'

IF @.Canceled= 0SET @.where=+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'

ELSEIF @.Canceled= 1SET @.where=+' AND CANCDATE IS NOT NULL'

ELSEIF @.Canceled= 2SET @.where=+' AND CANCDATE IS NULL'

IF @.FinalShip= 0SET @.where=+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'

ELSEIF @.FinalShip= 1SET @.where=+' AND FINALSHIP IS NOT NULL'

ELSEIF @.FinalShip= 2SET @.where=+' AND FINALSHIP IS NULL'

SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL @.where

END

To answer my own post, this is how you build a dynamic query in a stored proc:

ALTERPROCEDURE [dbo].[cg_JobSearch]

-- Add the parameters for the stored procedure here

@.JobNoStart varchar(10)='',

@.JobNoEnd varchar(10)= @.JobNoStart,

@.OrderDateStart varchar(10)='',

@.OrderDateEnd varchar(10)= @.OrderDateStart,

@.DueDateStart varchar(10)='',

@.DueDateEnd varchar(10)= @.DueDateStart,

@.ProofDateStart varchar(10)='',

@.ProofDateEnd varchar(10)= @.ProofDateStart,

@.CloseDateStart varchar(10)='',

@.CloseDateEnd varchar(10)= @.CloseDateStart,

@.CogsDateStart varchar(10)='',

@.CogsDateEnd varchar(10)= @.CogsDateStart,

@.ProductName varchar(200)='',

@.CustomerNumber varchar(12)='',

@.SalesPerson varchar(3)='',

@.CSR varchar(15)='',

@.Closedint= 0,

@.Canceledint= 0,

@.FinalShipint= 0,

--@.Invoiced int = 0,

@.where varchar(8000)='',

@.sql varchar(8000)=''

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNTON;

SET ANSI_WARNINGSOFF

SET @.where=''

IF @.JobNoStart!=''SET @.where= @.where+' AND LJOB BETWEEN '+ @.JobNoStart+' AND '+ @.JobNoEnd

IF @.OrderDateStart!=''SET @.where= @.where+' AND JOBDATE BETWEEN '+ @.OrderDateStart+' AND '+ @.OrderDateEnd

IF @.DueDateStart!=''SET @.where= @.where+' AND DUEDATE BETWEEN '+ @.DueDateStart+' AND '+ @.DueDateEnd

IF @.ProofDateStart!=''SET @.where= @.where+' AND PROOFDUE BETWEEN '+ @.ProofDateStart+' AND '+ @.ProofDateEnd

IF @.CloseDateStart!=''SET @.where= @.where+' AND CLOSEDATE BETWEEN '+ @.CloseDateStart+' AND '+ @.CloseDateEnd

IF @.CogsDateStart!=''SET @.where= @.where+' AND COGSDATE BETWEEN '+ @.CogsDateStart+' AND '+ @.CogsDateEnd

IF @.ProductName!=''SET @.where= @.where+' AND PRODUCT = '+ @.ProductName

IF @.CustomerNumber!=''SET @.where= @.where+' AND FCUSTNO = '+ @.CustomerNumber

IF @.SalesPerson!=''SET @.where= @.where+' AND FSALESPN = '+ @.SalesPerson

IF @.CSR!=''SET @.where= @.where+' AND JOBPER = '+ @.CSR

IF @.Closed= 0SET @.where= @.where+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'

ELSEIF @.Closed= 1SET @.where= @.where+' AND CLOSEDATE IS NULL'

ELSEIF @.Closed= 2SET @.where= @.where+' AND CLOSEDATE IS NOT NULL'

IF @.Canceled= 0SET @.where= @.where+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'

ELSEIF @.Canceled= 1SET @.where= @.where+' AND CANCDATE IS NOT NULL'

ELSEIF @.Canceled= 2SET @.where= @.where+' AND CANCDATE IS NULL'

IF @.FinalShip= 0SET @.where= @.where+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'

ELSEIF @.FinalShip= 1SET @.where= @.where+' AND FINALSHIP IS NOT NULL'

ELSEIF @.FinalShip= 2SET @.where= @.where+' AND FINALSHIP IS NULL'

SET @.sql='SELECT LJOB, DUEDATE, FCOMPANY, ID, QUAN FROM BBJTHEAD WHERE LJOB IS NOT NULL'

SET @.sql= @.sql+ @.where

PRINT @.sql

EXEC(@.sql)

END

|||

SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL
AND ((@.JobNoStart='') OR (LJOB BETWEEN @.JobNoStart AND @.JobNoEnd))
AND ...
AND ((@.Closed<>0) OR ( CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL))
AND ((@.Closed<>1) OR (CLOSEDATE IS NULL))
AND ((@.Closed<>2) OR (CLOSEDATE IS NOT NULL))
AND ...

Which if you see the pattern, I flip the condition of your IF for the first part, and use the where condition in each of your IFs as the second part. Like:
AND ((reversed IF condition) OR (Your where clause minus the AND))

The SQL in pink is also worthless, it will always be true. Also, the way you are concatenating your OR's and AND's will cause your where clause to not behave the way you want. AND has precedence over OR, where it looks like you want OR to have precedence over AND. As written, if Closed, FinalShip, or Cancelled is 0, you will return data that I suspect you didn't want returned.

If you use EXEC like the above poster suggested (after you fix the AND/OR problems I mentioned, and some errors the above poster made), you'll end up with a SP that can suffer SQL Injection. You are much better off either using the approach I mentioned above, or using the stored procedure that takes both a query string, and a parameter string (Which is a bit more difficult to set up, which is why unless performance is really bad, I use the above). I believe you are looking for the stored procedure sp_ExecuteSQL.

|||Thanks for the advice. It is true that with my current proc I'm susceptable to an injection attack. That would not be good.|||

This is how I ended up doing mine.

CREATE PROCEDURE [dbo].[pe_getAppraisals]

-- Add the parameters for the stored procedure here

@.PTypenvarChar(500),

@.ClientnvarChar(500),

@.PageSizeINT

AS

DECLARE

@.l_SelectnvarChar(4000),

@.l_FromnvarChar(4000),

@.l_SetWherebit,

@.l_PTypenvarChar(500),

@.l_ClientnvarChar(500)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

--Initialize SetWhere to test if a parameter has Added the keyword WHERE

--Initialize the Where statement in case all parameters are null

SET @.l_SetWhere= 0

--Create WHERE portion of the SQL SELECT Statement

IF(@.PTypeISNOTNULL)AND(@.PType<>'')

BEGIN

SET @.l_PType=' WHERE o.PropertyTypeID='+ @.PType

SET @.l_SetWhere= 1

End

ELSESET @.PType=''

IF(@.ClientISNOTNULL)AND(@.Client<>'')

BEGIN

IF @.l_SetWhere= 0

BEGIN

SET @.l_Client=' WHERE o.ClientID='+ @.Client

SET @.l_SetWhere= 1

END

ELSESET @.l_Client=' AND o.ClientID='+ @.Client

END

ELSESET @.l_Client=''

--Build the SQL SELECT Statement

SET @.l_Select=

'o.OrderID, o.FileNumber, o.OrderDate, o.ClientID, o.ClientFileNumber, o.PropertyTypeID, o.EstimatedValue, o.PurchaseValue,

o.LoanOfficer, o.ReportFee, o.FeeBillInd, o.FeeCollectInd, o.CollectAmt, o.Borrower, o.StreetAddrA, o.StreetAddrB, o.City, o.State, o.Zip,

o.ContactName, o.PhoneA, o.PhoneB, o.ApptDate, o.ApptTime, o.AppraiserID, o.InspectionDate, o.DateMailed, o.TrackingInfo, o.ReviewedBy,

o.StatusID, o.Comments, o.SpecialNotes, o.EmailInd, o.MgmtName, o.MgmtContactName, o.MgmtAddress, o.MgmtPhone, o.MgmtFax,

o.MgmtFee, o.MgmtNotes, o.LoginName, on1.NotesDesc AS PreNotesDesc, on2.NotesDesc AS PostNotesDesc, os.StatusDesc,

ot.ReportDesc, ot.ReportFee AS ReportPrice, ot.ReportSeq, pc.PriceDesc, pt.PropertyTypeDesc, l.LoginName AS AppraiserName'

SET @.l_From=

'Orders AS o LEFT OUTER JOIN

OrderNotes AS on1 ON o.PreNotesID = on1.NotesID LEFT OUTER JOIN

OrderNotes AS on2 ON o.PostNotesID = on2.NotesID LEFT OUTER JOIN

OrderStatus AS os ON o.StatusID = os.StatusID LEFT OUTER JOIN

OrderTypes AS ot ON o.ReportID = ot.ReportID LEFT OUTER JOIN

PriceCodes AS pc ON ot.PriceID = pc.PriceID LEFT OUTER JOIN

PropertyTypes AS pt ON o.PropertyTypeID = pt.PropertyTypeID LEFT OUTER JOIN

Logins AS l ON o.AppraiserID = l.LoginID'

Execute('SELECT TOP('+ @.PageSize+') '+ @.l_Select+' FROM '+ @.l_From+ @.l_PType+ @.l_Client)

Build Errors with the code!!

Hi,

I'm new to SQL Server RS.

I'm getting build errors after writing the following code:

List<ReportParameter> reportParam = new List<ReportParameter>();
reportParam.Add(new ReportParameter("Database", connectionString));
reportParam.Add(new ReportParameter("master_dept", hidMasterDept.Value.ToString().Trim()));
reportParam.Add(new ReportParameter("station_id", hidStationId.Value.ToString().Trim()));

reportViewer.ServerReport.SetParameters(reportParam)

The error message is as follows:
No overload for method 'ReportParameter' takes '2' arguments

But when I build the page using "Build Page" command from the menu, it builds successfully and then the project build succeeds.

When I modify any other page and build the solution, the same build error for this page occurs again.

Any idea how to resolve this? Been spending a lot of time figuring this out.

Thanks in Advance,

Manjunath HK

First make sure that all your parameters are single-valued, not multi-valued.

The best practice is always to create the report parameter just with a name and then adding values to it using Values collection like this:

reportParam.Add((new ReportParameter("Database")).Values.Add(connectionString))

Shyam

|||Hi Shyam,

Thanks for the reply.
I tried the same code suggestion given by you. But still nothing the same problem exists.
|||

Go to the following folder on your machine:

Program Files\Microsoft Visual Studio 8\ReportViewer

right click on Microsoft.ReportViewer.WinForms.dll and go to Version tab and make sure that the version is 8.0.50727.42

Also check out this article:

http://support.microsoft.com/default.aspx/kb/933137

Shyam

|||The problem got solved.
I used the following code:

List<Microsoft.Reporting.WebForms.ReportParameter> reportParam = new List<Microsoft.Reporting.WebForms.ReportParameter>();
reportParam.Add(new Microsoft.Reporting.WebForms.ReportParameter("Database", connectionString));
reportParam.Add(new Microsoft.Reporting.WebForms.ReportParameter("master_dept", hidMasterDept.Value.ToString().Trim()));
reportParam.Add(new Microsoft.Reporting.WebForms.ReportParameter("station_id", hidStationId.Value.ToString().Trim()));

I really dont know how it worked. I had already given "using Microsoft.Reporting.WebForms" in my code. But everything is working fine after the above change.

The build succeeded and till now I haven't found any issues.