How To Pass A SP Parameters W/o Calling The Sp?
Dec 1, 2003
MY DBA wrote a trigger, that upon hitting the delete button for a payment,
the payment would be transferred to another table and the trigger would
call a sp as well. since the trigger is calling the sp, I should only supply the parameters.
How do you do that w/o calling the procedure?
View 1 Replies
ADVERTISEMENT
Nov 17, 2006
Currently i am working in a project of report generation in MS ACCESS.
The tables are in sql server 2000.
I have to write stored proc in ms access.
Illustration:
I am having a stored proc as follows
name: myproc
-------------------
Create procedure my_proc
@f1 char(1),
@f2 char(5)
As
select * from table1 where field1=@f1 and field2=@f2
________________________________________________
and calling proc
name: call_myproc
execute my_proc 'A','2004'
If i am getting the vales of field1/@f1 and field2/@f2 from forms in ms access.
I have to get the values from forms in ms access.
I have to write the calling proc as follows
my_proc [forms]![form_a].[Combo4],[forms]![form_a].[text12]
But ms access throws syntax error.
How is it possible to pass values from ms access FORMS to a calling stored procedure.
I have followed the way of creating and executing the stored procedure as given in the article as follows.
http://www.databasejournal.com/features/msaccess/article.php/10895_3363511_1
As per the given link. They did not give values dynamically.
could you please help me to fix this problem ?
regards,
Krishna
View 1 Replies
View Related
Jul 20, 2005
I'm using an Access2K front end on a SQL Server2K backend.I use Scope_Identity() in a lot of stored procedures to pass thenewwly inserted record's unique ID to the next select statement in thesame stored procedure.What I'm wondering is how I can pass the Scope_Identity back to thecalling application.I'm hoping someone can show me the SP code and the aceess code neededto accomplish the following:I insert a new record in a table with a stored procedure. The SPpasses the uniqueID (Scope_Identity) back to MS Access, then MS Accessuses the uniqueID when calling another function...thanks
View 1 Replies
View Related
Aug 24, 2007
Hi,
I need to create a batch process which calls a stored procedure.
Here's the scenario.
I have 3 tables
Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId
There is a stored procedure spoc_updateTheater that accepts the state and location id and runs a set of sql statements against the theater table. However i want this to run for all the locations in a state one by one. There are some 700 locations in 45 states. How do i pass the location and state id one by one to the stored proc. Can i call this from a commandline or run it as batch process?
vidkshi
View 8 Replies
View Related
Dec 1, 2007
hi,
I am new to sql server reporting services. When i pass more than 30 parameters ,my report is showing parameters which is not a desired one even though i am making showparameterPrompts property to false.
can any one tell me how to pass more no of parameters to the Report.
Advance thanks
Srinivas Govada
View 1 Replies
View Related
Jun 3, 2000
Hello,
What is the syntax of calling stored procedure from vb
Is there any1 can please give me a example of how to call sp with passing parameters, thanks a lot.
Thanks
View 1 Replies
View Related
Feb 3, 2006
Hello All,
Is calling another SP and getting out parameters supposed to work inside CRL SP via ADO.NET?
If I setup my command the standard way - a copy of the Quicksample code:
// Fill the parameters collection based upon stored procedure.
SqlParameter workParam = null;
workParam = myCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 40);
workParam.Direction = ParameterDirection.Output;
myCommand.ExecuteNonQuery();
string temp = myCommand.Parameters["@CompanyName"].Value.ToString();
why would I not be able to obaint the out parameter in temp.
myCommand.Parameters["@CompanyName"].Value.ToString() reamains null after execution and when the same Stored procedure is executed in TSQL things work just fine.
Thank you very much,
Lubomir
View 1 Replies
View Related
Nov 27, 2007
Hi there!
My report uses an OLAP query with a (multi-valued) parameter, the generated MDX query contains something like "...ON ROWS FROM ( SELECT ( STRTOSET(@Region, CONSTRAINED) ) ON COLUMNS FROM [cube]) ...".
The report works fine using the Report Manager frontend. Now I want to pass this parameter directly within the query string:
https://servername/ReportServer?/PathTo/MyReport&rs:Command=Render&rs:Format=HTML4.0&Region=[foo].[bar 123].&[baz]
But I cannot get Reporting Services to accept the parameter via URL access (instead of complaining about a missing parameter value). I've already tried quoting/encoding the brackets, spaces, and the ampersand, putting the dimension in curly braces and/or quotes... No success.
Normal (url-encoded) query parameters work fine, I just can't pass OLAP parameters (dimensions like "[foo].[bar 123].&[baz]") this way. I think ReportServer doesn't like the way I'm trying to quote them...
How do I quote dimension parameters correctly?
Thanks and best regards,
Thomas
View 3 Replies
View Related
Dec 26, 2007
Hi Gurus,
I want to pass 0 value to all subreport parameters in the beginning when main report is run. This is because i dont want subreport query to run.
when user clicks on '+' in main report, i want to pass the parameter values so that the subreport will run with correct parameters for that particular main report row.
My purpose of doing this is to achieve speed with subreport
Thanks a lot in advance.
Respectfuly,
Korr
View 18 Replies
View Related
May 8, 2008
how do u pass parameters to a subreport.... i tried doin it but got an error msg sayin "Sub report cannot be displayed"
any solution????
View 4 Replies
View Related
Mar 27, 2008
I have recently had a few reports that are built around the userid parameter start failing to pass the userid parameter. If I run the report and hard code the userid everything runs just fine, but I am not able to collect the userid when the user is logging into the report server. Any thoughts or suggestions?
R/ AA
View 3 Replies
View Related
Jul 15, 2007
how do i pass the paramenters and storedprocedure to dataview from code-behind?
the below code have sqldatasource control but i want to pass through code-behind everything...
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
EmptyDataText="There are no data records to display." DataKeyNames="NewsId">
<Columns>
<asp:BoundField DataField="NewsId" HeaderText="NewsId" InsertVisible="False" ReadOnly="True"
SortExpression="NewsId" />
<asp:BoundField DataField="PostDate" HeaderText="PostDate" SortExpression="PostDate" />
<asp:BoundField DataField="PostedBy" HeaderText="PostedBy" SortExpression="PostedBy" />
<asp:BoundField DataField="PostedByName" HeaderText="PostedByName" SortExpression="PostedByName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" />
<asp:BoundField DataField="LastUpdated" HeaderText="LastUpdated" SortExpression="LastUpdated" />
<asp:CheckBoxField DataField="IsVisible" HeaderText="IsVisible" SortExpression="IsVisible" />
</Columns>
</asp:GridView> i have storedprocdure which accepts 4 parameters 3 paramenters which user will supply and 1 parameter will be supplied from code-behindwhat i mean by that is:the 4 parameters storedprocedure accepts is:empid, start_date, end_date (user will supply those 3 parameters)internal_id - which internally pass along with other 3 parmeterssomething like this:internal_id, empid, start_date, end_dateany thoughts?thanks.
View 3 Replies
View Related
Aug 11, 2007
How do I pass values from my ASP.NET page code into my Stored Procedure, to become parameters to be used in my Stored Proc?
Much thanks
View 2 Replies
View Related
Feb 29, 2008
I’ve got the following piece of ASP code, which updated a
table through the standard edit/update command filed options.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:CAT_SYSTEMConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="sp_diplomaViewQualifications"
UpdateCommand="UPDATE
PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and
qualId=@original_qualId"
DeleteCommand="UPDATE
PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy
WHERE rowstatus=1 and qualId=@original_qualId"
SelectCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="quantity"
/>
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="createdBy" DefaultValue="TEST"/>
</DeleteParameters>
<SelectParameters>
<asp:Parameter Name="masterKey"
/>
</SelectParameters>
</asp:SqlDataSource>
My problem is that I need to pass the contents
of the VB variable ‘createdBy’ into the DeleteParameters option.
This is defined in the code as: Dim createdBy As String = getUserLoginName(Me.Page)
How do I pass this into the update and/or delete part
of the command field on the ASP page?
View 3 Replies
View Related
Apr 23, 2012
I don't know how to pass parameters required (dates) to a query1, if I'm using a subquery (query2) which is using the results of query1, but I'm not showing that field on that subquery (query2)
Example
table1
id - autonumeric
id_user - id from user
dates - date of register
table2
id - user id
name - user name
query1
SELECT Table1.id_user, Count(Table1.id_user) AS CuentaDeid_user
FROM Table1
WHERE (((Table1.datess) Between [begining] And [ending]))
GROUP BY Table1.id_user
ORDER BY Table1.id_user;
subquery (query2)
SELECT query1.id_user, query1.CuentaDeid_user, Table2.name
FROM query1 LEFT JOIN Table2 ON query1.id_user = Table2.id;
This is just an example, the think is that I want to know that if it's possible to pass the parameters requested in query1 from the SQL of the subquery (query2)?
View 3 Replies
View Related
Jun 11, 2008
Hi,
I have a table with queries. I need to execute those queries and pass results into a variable. Then use that variable/result to execute other queries to make business decisions.
EXAMPLE:
TASK#1
TABLE-A :has queries below
select count(*) from employee
select count(* ) from mangers
I want to execute those queries and store results in @counts. How I execute all queries in table A and pass that to a variable?
TASK#2
Then I have another SQL task(may be SQL task) which use the value @counts make some decisions
If @count > 1 then pass
If @count <1 then fail
How can I do that?
I am still new to SSIS and not very familiar with variables. Any advice would be appreciated.
View 6 Replies
View Related
Nov 28, 2013
I am trying to use one sql store procedure but don't know how to pass the parameters. I am posting here my store procedure:-
create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as
declare @pivot varchar(max), @sql varchar(max)
[Code] ....
My table looks like below:
emp_id, time_code, date, time_charged
RB, VAC, 20130222, 8
RB, HOL, 20131128, 8
RB, VAC, 20130311, 8
My output should be:
emp id VAC HOL
RB 16 8
View 5 Replies
View Related
Aug 17, 2007
In a matrix report with column and row groupings, how to pass the parameters to the drill-through report? The column grouping could open up into multiple columns and the same to the row groupings. I have a navigation link to a drill-through report on a total field in the matrix report and the drill-through report is required three parameters, one from the parameter from the matrix report (this one I can map to easily), second parameter is one of the value from the column grouping field and the third parameter is one of the value from the row grouping field. How to select those values from the column/row grouping.
Thanks
View 8 Replies
View Related
Dec 27, 2007
How do you create a link on one report to run another report passing a parameter (or two).
View 4 Replies
View Related
Apr 5, 2007
I defined the following stored Procedure as the datasource
CREATE PROCEDURE p1(@cond String)
AS
BEGIN
SELECT Id,Name from table1 Where @cond
END
@cond is a long sql , for example
Id in (select sunId from table2 where .......)
my question is
how can I pass the parameters( @cond ) to RS2005?
thanks!
View 1 Replies
View Related
Apr 12, 2006
I am using a parameterized select query as displayed below to fetch values from source.
SELECT A.Account_GUID,
M.Merchant_GUID,
H.Household_GUID,
B.BankAU_GUID,
SR.SalesRep_GUID,
E.Entitlement_GUID,
I.Income_GUID,
Exp.Expense_GUID,
SP.Sales_Product_GUID,
P.Product_cd,
SUM(S.Sales) AS Monthly_gross_MC_VI_amt,
SUM(S.Sales) - SUM(S.[Returns]) AS Monthly_net_MC_VI_amt,
SUM(S.SaleTxns) AS Monthly_gross_MC_VI_tran_cnt,
SUM(S.SaleTxns) - SUM(S.ReturnTxns) AS Monthly_net_MC_VI_tran_cnt
FROM Account AS A
LEFT OUTER JOIN dbKAIExtract.dbo.tblSales_STG AS S
ON A.Account_No = S.AccountNo
And S.BucketNo = ? And S.ProductCode in ('01','02')
LEFT OUTER JOIN Merchant AS M
ON A.Account_No = M.Account_no
INNER JOIN SalesRep AS SR
ON SR.Rep_SSN = isnull(A.rep_SSN,'000000000')
INNER JOIN Household AS H
ON A.Account_No = H.Account_no
LEFT OUTER JOIN BankAU AS B
ON A.Assigned_AU = B.AU_No
LEFT OUTER JOIN SalesProduct AS SP
ON A.Account_No = SP.Account_no
And SP.Reporting_Interval_Id = ?
LEFT OUTER JOIN Entitlement AS E
ON E.Account_no = A.Account_No
AND SP.Product_Cd = E.Entitlement_Card_Type
LEFT OUTER JOIN Income AS I
ON I.Account_no = A.Account_No
And I.Reporting_Interval_Id = ?
LEFT OUTER JOIN Expense AS Exp
ON Exp.Account_no = A.Account_No
And EXP.Reporting_Interval_Id = ?
LEFT OUTER JOIN Product AS P
ON P.Product_cd = SP.Product_cd
WHERE (A.current_ind = 1)
AND (SR.current_ind = 1)
GROUP BY A.Account_GUID,
M.Merchant_GUID,
H.Household_GUID,
B.BankAU_GUID,
SR.SalesRep_GUID,
E.Entitlement_GUID,
I.Income_GUID,
Exp.Expense_GUID,
SP.Sales_Product_GUID,
P.Product_cd
My problem is, I am not able to assign any variables to parameterized query. Can any body guide how to assign respective variables to the parameterized query. I have the above query as a part of OLE DB Source step within Data flow task.
Thank you
Jatin
View 11 Replies
View Related
Apr 25, 2008
Hi,
I am working on SSRS. I need to open a new report from one report when user clicks on some particular summerized count link.
Its a sort of drilled down report. I am not getting how to pass the respected Ids (more than one) to the next report when user clicks on the link in the 1st report. These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from the database.
Please help.
Regards,
Sachin
View 4 Replies
View Related
Aug 27, 2007
I will like to configure the Report or the Report server to pass parameters to Inetrnet Explorer when creating the report.
Is there a way to do that ?
View 1 Replies
View Related
May 8, 2008
Hi,
I'm having Parent package, which has For each loop which returns Val1 , Val2 , Val3 and Val 4
I want to create child package which accepts one parameter and call this package inside For each loop.
Inside loop i want to call 4 child packages(same pakage) simultaneously.
If i create child package which access parent package variable, i will have to modify modify each child package. I cannot reused child package.
How can i create a generic child package ?
Thanks,
Sandeep.
View 6 Replies
View Related
Jun 22, 2006
Hi,
I use the " JUMP to URL" properties and the code as follow:
=SWITCH(Fields!KpiCode.Value=Fields!KpiCode.Value,void(window.open('"+First(Fields!ServerString.Value,
"HostInfo")+"/L3/"+Fields!KpiCode.Value+".aspx','"+First(Fields!PropertyString.Value,
"HostInfo")+"'))")
I want to know is any way or any possible way could pass the report
parameter on this kind of porperties or this code?
For some requirement, I just can use Jump to URL, but hope can pass the
parameters on "jump to url" properties.
Thanks for any advice!
Angi
View 13 Replies
View Related
Feb 15, 2008
I have a method like follows:
string EmpCount = null;
DateTime _dtstart = Convert.ToDateTime(txtStart.Text.Trim());
DateTime _dtend = Convert.ToDateTime(txtEnd.Text.Trim());SQLProvider sqlp = new SQLProvider(System.Configuration.ConnectionStrings["ConString"].ConnectionString);string SqlText = @"
select count(*) from employee
where activeemp=1 and startdate BETWEEN @dtstart and @dtend;
using (sqlp.Connection) {
sqlpm [] param = {
new sqlpm("dtStart", _dtstart),
new SqlP("dtEnd", _dtend)
};
}EmpCount = sqlp.ExecuteScalar(SqlText, param).ToString();
return Convert.ToInt32(mbrCount);
Then the method I am calling is:public Object ExecuteScalar(String sqlText, Sqlp[] param)
{try
{
//Some Code here
}
}
So in the calling method (ExecuteScalar), the second parameter is defined as an array, is it ok to have an array in the called method too?
View 1 Replies
View Related
Nov 13, 2007
Hi all,
I wish to pass parameter from a dropdownlist in my web application to the SSIS package so as to export the user specified table.
Is using package configuration the right way to do this?
Any advice is appreciated!
View 8 Replies
View Related
Feb 19, 2008
I have stored procedure that expects 2 input parameters (@UserID uniqidentifier and @TeamID int). My datasource is SQLDataSource. So i need to pass parameters to SP..When i add parameters ans execute i got an error "....stored procedure expects @TeamId parameter, which was not supplied" But i pass them. How should i pass parameters? Maybe the reason is some mismatching of parametrs. 1 Parameter [] param = new Parameter[2];
2
3 param[0] = new Parameter();
4 param[0].Name = "@TeamID";
5 param[0].Type = TypeCode.Int32;
6 param[0].Direction = ParameterDirection.Input;
7 param[0].DefaultValue = "1";
8
9 param[1] = new Parameter();
10 param[1].Name = "@UserID";
11 param[1].Direction = ParameterDirection.Input;
12 param[1].DefaultValue = "edf26fd8-d7cd-4b32-a18a-fc888cac63ef";
13 param[1].Type = TypeCode.String;
14
15 dataSource = new SqlDataSource();
16 dataSource.ID = "Source";
17 dataSource.ConnectionString = settings.ToString();
18
19 dataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
20 dataSource.DataSourceMode = SqlDataSourceMode.DataReader;
21 dataSource.SelectCommand = "dbo.GetAprfAssessmentTeamData";
22
23
24 dataSource.SelectParameters.Add(param[0]);
25 dataSource.SelectParameters.Add(param[1]);
26
View 4 Replies
View Related
Mar 26, 2008
Hi,
I want to create a stored procedure which I can pass multi parameters. This is what I need, I have a gridview which is used for displaying customer info of each agent. However, the number of customers for each agent is different. I will pass customer names as parameters for my stored procedure. Here is a sample,
CREATE PROCEDURE [dbo].[display_customer]
@agentID varchar(20),
@customer1 varchar(20),
@customer2 varchar(20),
..... -- Here I do know how many customers for each agent
AS
SELECT name, city, state, zip
FROM rep_customer
WHERE agent = @agentID and (name = @customer1 or name = @customer2)
Since I can not decide the number of customers for each agent, my question is, can I dynamically pass number of parameters to my above stored procedure?
Thanks a lot!
View 6 Replies
View Related
Nov 15, 2006
Hello all,
I have a project where I need to automatically pass 2 sets of parameters to a SSRS report and generate PDF files. I have set the defaults for the parameters based on one set of parameters and created a subscription to automatically generate PDF copy. However, I am not sure how to generate the report automatically using the other set of parameters. Also, I don't want to create two report files to accomplish this.
Can you please help?
Thanks in advance,
Saurav
View 1 Replies
View Related
Apr 25, 2008
Hi,
I am working on SSRS. I need to open a new report from one report when user clicks on some
particular summarized count link.
It is a sort of drilled down report. I am not getting how to pass the respective Ids (more
than one) to the next report when user clicks on the summarized link in the 1st report.
These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from
the database.
View 3 Replies
View Related
Apr 25, 2008
Hi,
I am working on SSRS. I need to open a new report from one report when user clicks on some
particular summarized count link.
It is a sort of drilled down report. I am not getting how to pass the respective Ids (more
than one) to the next report when user clicks on the summarized link in the 1st report.
These ids I want to use as a parameter (multiple) in the 2nd report to dump the rows from
the database.
View 1 Replies
View Related
Sep 15, 2007
I have developed some Reports using Reporting Services and I want to deploy them for access over the Internet.
These Reports allow the Users to specify parameters, such as Start and End Dates.
I am told that a User Interface has to be developed manually to provide this facility for some kind of security reason.
This seems very bizarre and the reason doesn€™t make much sense.
Is this really true ?
Thanks very much
Barry
View 19 Replies
View Related