Help on passing param to store procedure ??

Dear all,
I am calling a store procedure in SQL server 2000 from VS2003
No problem from that side.
The store procedure has an input parameter and define as follow :
ALTER PROCEDURE sp_GetReels (@.LineId varchar) AS
SELECT LINE_ID , ID
FROM REELHIST
WHERE (LINE_ID = @.LineId)
GO
If I execute that procedure from TSQL and pass the parameter value: WSE30
(which is the criteria that I want), then no records gets return and I am
sure they are records.
Then if I hardcode my string in the store procedure as follow :
ALTER PROCEDURE sp_GetReels AS
SELECT LINE_ID , ID
FROM REELHIST
WHERE LINE_ID = 'WSE30'
Then records are returns
What I am doing wrong in my procedure to pass that input parameter ?
Thnaks for your help
regards
sergeYou need to be more specific about the length of the varchar
parameter.
For example
ALTER PROCEDURE sp_GetReels (@.LineId varchar(10)) AS|||You didnt specify the length of the varchar so it assumes it is 1 :).
Put instead
@.LineId varchar (50) or something like that.
At this point your string becomes 'W' in the stored procedure.
MC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:FDF4F7FB-45B6-4F0D-8535-1CDED2492157@.microsoft.com...
> Dear all,
> I am calling a store procedure in SQL server 2000 from VS2003
> No problem from that side.
> The store procedure has an input parameter and define as follow :
> ALTER PROCEDURE sp_GetReels (@.LineId varchar) AS
> SELECT LINE_ID , ID
> FROM REELHIST
> WHERE (LINE_ID = @.LineId)
> GO
> If I execute that procedure from TSQL and pass the parameter value: WSE30
> (which is the criteria that I want), then no records gets return and I am
> sure they are records.
> Then if I hardcode my string in the store procedure as follow :
> ALTER PROCEDURE sp_GetReels AS
> SELECT LINE_ID , ID
> FROM REELHIST
> WHERE LINE_ID = 'WSE30'
> Then records are returns
> What I am doing wrong in my procedure to pass that input parameter ?
> Thnaks for your help
> regards
> serge

Help Needed: DataSet

Should I create a new dataset in order to populate a multi select parameter
drop down box for users?
I am trying to capture all Companies stored in our backend database as a
drop down selection to users.
Thanks!Hi Terry,
You are correct. Thats how the guy explained it on the Reporting Services
webcasts.
The webcasts are quite informative. I would advise everyone to watch them.
R
S
--
http://dotnet.org.za/stanley
"Terry" wrote:
> Should I create a new dataset in order to populate a multi select parameter
> drop down box for users?
> I am trying to capture all Companies stored in our backend database as a
> drop down selection to users.
> Thanks!|||Which webcast are you referring to?
Provide URL or link, if available?
Again, thanks!
"Stan" wrote:
> Hi Terry,
> You are correct. Thats how the guy explained it on the Reporting Services
> webcasts.
> The webcasts are quite informative. I would advise everyone to watch them.
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Should I create a new dataset in order to populate a multi select parameter
> > drop down box for users?
> >
> > I am trying to capture all Companies stored in our backend database as a
> > drop down selection to users.
> >
> > Thanks!|||I am getting an error when I create a new dataset and use it as a parameter.
Error:
[rsMissingDataSetName] The data set name is missing in the data region
â'DataSetNameâ'
CompanyLookup Dataset:
select DISTINCT ADDR_Org_Name_1, ADDR_Org_Name_2
from addresses
where len(rtrim(ADDR_Org_Name_1)) > 0
order by ADDR_Org_Name_1
"Stan" wrote:
> Hi Terry,
> You are correct. Thats how the guy explained it on the Reporting Services
> webcasts.
> The webcasts are quite informative. I would advise everyone to watch them.
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Should I create a new dataset in order to populate a multi select parameter
> > drop down box for users?
> >
> > I am trying to capture all Companies stored in our backend database as a
> > drop down selection to users.
> >
> > Thanks!|||The best thing to do is to go through the webcasts and see what you like.
But the ones I'm reffering to is a 5 day series. They touch on everything.
Follow the URL and scroll down till you see the webcast that starts on 07
March part 1
Very long URL :-)
http://www.microsoft.com/events/AdvSearch.mspx?EventsAndWebcastsControlName=As1%3AAdvSrc&As1%3AAdvSrc%3AAudienceID=0&As1%3AAdvSrc%3AProductID=261ba873-f3ab-420e-96d6-e3004596a551&As1%3AAdvSrc%3AEventType=OnDemandWebcast&As1%3AAdvSrc%3ACountryRegionID=en%7CUS%7CUnited+States&StateProvinceID=0&As1%3AAdvSrc%3ATimeframeID=-1&As1%3AAdvSrc%3ASearchFilter=%C2%A0+Go+%C2%A0&PageNumber=3
Hope this helps man...
R
S
--
http://dotnet.org.za/stanley
"Terry" wrote:
> Which webcast are you referring to?
> Provide URL or link, if available?
> Again, thanks!
> "Stan" wrote:
> > Hi Terry,
> > You are correct. Thats how the guy explained it on the Reporting Services
> > webcasts.
> > The webcasts are quite informative. I would advise everyone to watch them.
> >
> > R
> > S
> > --
> > http://dotnet.org.za/stanley
> >
> >
> > "Terry" wrote:
> >
> > > Should I create a new dataset in order to populate a multi select parameter
> > > drop down box for users?
> > >
> > > I am trying to capture all Companies stored in our backend database as a
> > > drop down selection to users.
> > >
> > > Thanks!|||Thank you for your assistance.
"Stan" wrote:
> The best thing to do is to go through the webcasts and see what you like.
> But the ones I'm reffering to is a 5 day series. They touch on everything.
> Follow the URL and scroll down till you see the webcast that starts on 07
> March part 1
> Very long URL :-)
> http://www.microsoft.com/events/AdvSearch.mspx?EventsAndWebcastsControlName=As1%3AAdvSrc&As1%3AAdvSrc%3AAudienceID=0&As1%3AAdvSrc%3AProductID=261ba873-f3ab-420e-96d6-e3004596a551&As1%3AAdvSrc%3AEventType=OnDemandWebcast&As1%3AAdvSrc%3ACountryRegionID=en%7CUS%7CUnited+States&StateProvinceID=0&As1%3AAdvSrc%3ATimeframeID=-1&As1%3AAdvSrc%3ASearchFilter=%C2%A0+Go+%C2%A0&PageNumber=3
> Hope this helps man...
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Which webcast are you referring to?
> >
> > Provide URL or link, if available?
> >
> > Again, thanks!
> >
> > "Stan" wrote:
> >
> > > Hi Terry,
> > > You are correct. Thats how the guy explained it on the Reporting Services
> > > webcasts.
> > > The webcasts are quite informative. I would advise everyone to watch them.
> > >
> > > R
> > > S
> > > --
> > > http://dotnet.org.za/stanley
> > >
> > >
> > > "Terry" wrote:
> > >
> > > > Should I create a new dataset in order to populate a multi select parameter
> > > > drop down box for users?
> > > >
> > > > I am trying to capture all Companies stored in our backend database as a
> > > > drop down selection to users.
> > > >
> > > > Thanks!|||I hope I understand you correctly.
Just to clear things out.
You have a report that takes parameters, but you want the user to select the
parameter from a drop down box. Is this correct?
If that is the case, go to you Data Tab and click on new dataset. Write
your query that will bring back the company names. Now go to your layout
view and click on the report menu. Go to report parameters. Now select on
your company parameter and then click the radio button that says from query.
Select your new dataset in there. When you preview your report the dropdown
box should be populated.
Hope this helps man...
--
http://dotnet.org.za/stanley
"Terry" wrote:
> I am getting an error when I create a new dataset and use it as a parameter.
> Error:
> [rsMissingDataSetName] The data set name is missing in the data region
> â'DataSetNameâ'
> CompanyLookup Dataset:
> select DISTINCT ADDR_Org_Name_1, ADDR_Org_Name_2
> from addresses
> where len(rtrim(ADDR_Org_Name_1)) > 0
> order by ADDR_Org_Name_1
>
> "Stan" wrote:
> > Hi Terry,
> > You are correct. Thats how the guy explained it on the Reporting Services
> > webcasts.
> > The webcasts are quite informative. I would advise everyone to watch them.
> >
> > R
> > S
> > --
> > http://dotnet.org.za/stanley
> >
> >
> > "Terry" wrote:
> >
> > > Should I create a new dataset in order to populate a multi select parameter
> > > drop down box for users?
> > >
> > > I am trying to capture all Companies stored in our backend database as a
> > > drop down selection to users.
> > >
> > > Thanks!

Help needed with this sproc

Hi, I am trying to Implement Multi parameter...

If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'.

This is my sproc

ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]@.ClientIdnvarchar(max)=NULL,@.StartDatedatetime,@.EndDatedatetimeASDeclare @.SQLTEXT nvarchar(max)If @.ClientIdISNULLBeginSelect o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInnerJoin ClientPlan cpon o.PlanId = cp.PlanIdInnerJoin ClientUser con o.CreatedByUserId = c.UserIdWHERE--cp.ClientId = @.ClientId--AND o.OrderDateBETWEEN @.StartDateAND @.EndDateORDER BYo.OrderIdDESCENDELSEBEGIN SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' + @.ClientId +')AND o.OrderDate BETWEEN ' +Convert(varchar,@.StartDate) +' AND ' +convert(varchar, @.EndDate) +' ORDER BYo.OrderId DESC'execute (@.SQLTEXT)END

any help will be appreciated.

Regards

Karen

Try this:

SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' +Convert(Varchar,@.ClientId) +')AND o.OrderDate BETWEEN ' +Convert(varchar,@.StartDate) +' AND ' +convert(varchar, @.EndDate) +' ORDER BYo.OrderId DESC'Exec(@.SQLTEXT)

If you still have errors, post exactly how you are calling the proc.

|||

i am first execting the stored proc using query analyser and this is how i am calling it

usp_GetOrdersByOrderDate'7,4','12/17/2007','12/20/2007'

|||

ahh.. you need quotes around the dates too..

SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' +Convert(Varchar,@.ClientId) +')AND o.OrderDate BETWEEN ''' +Convert(varchar,@.StartDate) +''' AND ''' +convert(varchar, @.EndDate) +''' ORDER BYo.OrderId DESC'

|||

thanks,,,

Yes that did it.

Regards

Karen

Help Needed For reporting service 2005(Regarding default date value for report parameter)

Hi experts
I am working on sql server reporting services 2005.
I am using Date time control for my report parameter. And default
value i given =Today(). This is working fine.
But i want some days previous date for default value and when I am
writting Today()-1, Report is giving error.
So can any body tell me how i can do this for defalut value. I want to
give one month previous date as default value.
And second thing i wish to know is it possible to make instalable file
for this solution so that where ever i want i can
install these reports. If yes then how can i make instalable file of
my this solution.
Any help will be gratefull.
Regards
DineshOn Oct 23, 9:38 am, Dinesh <dinesh...@.gmail.com> wrote:
> Hi experts
> I am working on sql server reporting services 2005.
> I am using Date time control for my report parameter. And default
> value i given =Today(). This is working fine.
> But i want some days previous date for default value and when I am
> writting Today()-1, Report is giving error.
> So can any body tell me how i can do this for defalut value. I want to
> give one month previous date as default value.
> And second thing i wish to know is it possible to make instalable file
> for this solution so that where ever i want i can
> install these reports. If yes then how can i make instalable file of
> my this solution.
> Any help will be gratefull.
> Regards
> Dinesh
Hi!
You can try it;
(Date = DATEADD(Day, - 1, GetDate())
Hope this helps
Regards
Shima

Help needed - Parameter driven extract

Hi All,

I am designing a data migration tool using SSIS. As part of it, within a package I need to get a list of of customers from a SQL Server database table and extract the data for those customers from a seperate Sybase database. How do I make my SQL command to extract the data parameter driven? If I store the list of customer ID's in a package variable can I access it in the SQL command? I am using an ODBC connection for Sybase.

Any help would be greatly apreciated.

Nadella

If I were you; I would create a staging table to load the Sybase data that is accesible via Query-join from the SQL Server database. That way you could write single query joining the 2 tables.

I don't know how you could implement this via SSIS variable...

Help me resolve this error."Missing parameter field current value." Code Attach

Hi ,
am getting this error."Missing parameter field current value." When i Use this line of code
crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues() it says "ApplyCurrentValues is not a member of CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition".How do i get rid of this error.
Also is there anyway that without Exporting , i Can Print the Report from vb.net application directly using default Printer.
Code is:
Private Sub Btn_Export_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Export.Click
Try
Dim ConnInfo As New ConnectionInfo
With ConnInfo
.ServerName = "WASA00150"
.DatabaseName = "iCalls"
.UserID = "sa"
.Password = "courage"
End With
Me.CrystalReportViewer1.ParameterFieldInfo.Clear()
If Me.txtSTdate.Text.Trim.Length > 0 Then
Me.CrystalReportViewer1.ReportSource = Server.MapPath("iCalls_CrystalReport_Department.rpt")
Dim ParamFields As ParameterFields = Me.CrystalReportViewer1.ParameterFieldInfo
Dim Per As New ParameterField
Per.ParameterFieldName = "Period"
Dim Period_Value As New ParameterRangeValue
Period_Value.StartValue = Me.txtSTdate.Text
Period_Value.EndValue = Me.txtCLdate.Text
Per.CurrentValues.Add(Period_Value)
ParamFields.Add(Per)
End If
For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo
cnInfo.ConnectionInfo = ConnInfo
Next
Me.CrystalReportViewer1.RefreshReport()
CrystalReportViewer1.Visible = True
Dim exportPath As String = "D:\SampleCrystalReports\iCalls_Export\test1.pdf"
Dim crExportOptions As ExportOptions
Dim crDestOptions As New DiskFileDestinationOptions
crDestOptions.DiskFileName = exportPath
crExportOptions = crReportDocument.ExportOptions
crExportOptions.DestinationOptions = crDestOptions
crExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
crExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat
'crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues()
crReportDocument.Export()
Catch ex As Exception
lblmsg.Text = ex.Message.ToString
End Try
End Sub
Many Thanks.Not sure if this is it, but looking at the code this line.

'crReportDocument.DataDefinition.ParameterFields.ApplyCurrentValues()

has a ' at the begining, is that supposed to be there ?