Hi Everyone, I am new in the forum, I am getting a strange problem with BIDS(Business Intellisense Developement Studio) 2005, as when I write my dynamic query keeping it in varchar variable and save then BIDS goes in hang stae...Too much time I have spent to overcome this problem but luck My query is as below declare @Employee varchar(max)='' declare @Project varchar(max)='' declare @Strptdt datetime=getdate() declare @Endrptdt datetime=getdate() declare @qry varchar(max),@condition varchar(max) declare @ParamTower varchar(max),@TempTower varchar(max),@TTower varchar(max),@Temp2Tower varchar(max) set @ParamTower='' set @TempTower='' set @TTower='' set @Temp2Tower='' declare @ParamProject varchar(max),@TempProject varchar(max),@TProject varchar(max),@Temp2Project varchar(max) set @ParamProject='' set @TempProject='' set @TProject='' set @Temp2Project='' set @condition='' set @qry= 'select * from ( SELECT m.employee employee, e.firstname, PR.Name ProjName, e.firstname +'',''+e.lastname name, e.middlename middlename, e.lastname lastname, e.org profit_center, m.reportname rptname, m.reportdate rptdate, m.advanceamount advamt, m.status status, d.transdate transdate, c.description category, d.description descrip, d.wbs1 project, d.wbs2 phase, d.wbs3 wbs3, d.billable billable, d.companypaid companypaid, d.account account, d.amount amount, d.reason reason, d.other other, d.miles miles, d.amountpermile per_mile, k.allowcompanypaid allowcompanypaid, k.allowadvances allowadvances, k.showaccount showaccount, k.electronicsignature, k.ApprovalRequired, d.seq seq, d.editdetail, c.detailtype, pr.name wbs1name, d.TaxCode, d.TaxAmount, d.Tax2Code, d.Tax2Amount, d.CurrencyCode As transCurrencyCode, CFGCurrency.decimalPlaces As transDecimalPlaces, d.PaymentAmount, k.distancetype, c.CalculateDistanceUnits, esubmit.lastname Submit_Lname, esubmit.firstname Submit_Fname, esubmit.middlename Submit_Mname, eapprov.lastname Approve_Lname, eapprov.firstname Approve_Fname, eapprov.middlename Approve_Mname FROM ekdetail d LEFT JOIN ekmaster m ON d.employee = m.employee AND d.reportname = m.reportname AND d.reportdate = m.reportdate LEFT JOIN em e ON m.employee = e.employee LEFT JOIN em esubmit ON m.submittedby = esubmit.employee LEFT JOIN em eapprov ON m.approvedby = eapprov.employee LEFT JOIN cfgekcategory c ON d.category = c.category /***ACTIVE WHERE CLAUSE***/ LEFT JOIN pr ON d.wbs1 = pr.wbs1 AND pr.wbs2 = /*N*/'' AND pr.wbs3 = /*N*/'' LEFT JOIN CFGCurrency ON d.CurrencyCode = CFGCurrency.Code, cfgekmain k ) tbl WHERE (1=1) ' if(@Employee<>'') begin set @Temp2Tower=''''+@Employee+'''' set @TTower =''',''' set @ParamTower=@Temp2Tower set @TempTower= REPLACE(@ParamTower,'#',@TTower) SET @condition = @condition+ ' AND REPLACE(tbl.name,'''''''',''@'') IN ( '+@TempTower+') ' end if(@Project<>'') begin set @Temp2Project=''''+@Project+'''' set @TProject =''',''' set @ParamProject=@Temp2Project set @TempProject= REPLACE(@ParamProject,'#',@TProject) SET @condition = @condition+ ' AND tbl.project IN ( '+@TempProject+') ' end if(@Strptdt is not null) begin set @condition = @condition+ ' AND tbl.rptdate>=convert(datetime,'''+CONVERT(varchar(30),@Strptdt)+''') ' end if(@Endrptdt is not null) begin set @condition = @condition+ ' AND tbl.rptdate <= convert(datetime,'''+CONVERT(varchar(30),@Endrptdt)+''') ' end exec(@qry+@condition) --print (@qry+@condition) where @Employee,@Project,@Strptdt and @Endrptdt are Report Parameter. Please help I will be thakfull to you

Hi Sanjeet, The solution for this request is that first you have create stored procedure in database and then call that stored procedure at dataset properties as before this you have select 'Text' so just click 'Stored Procedure'. whenever you will click 'OK' it will ask for refereshing I mean will ask those 3 parameter then just pass values and it may be take 5 or minutes so just wait for 5 or 6 minutes. If you don't have permission to create stored procedure or you want to use it query level in BIDS then you don't need to declare those parameter in starting declare @Employee varchar(max)='' declare @Project varchar(max)='' declare @Strptdt datetime=getdate() just remove these code and just refresh it will ask you for these parameter values. ow you will those three parameter and you can change data type of these parameter at 'Parameter->Parameter Properties'. But what I snuggest you is that you must use first option. Let me know if this helps or not.Cheers!! Sumit