Esql/C calling stored procedure with output parameters

I'm trying to write an esqlc program that will run a stored procedure that returns several output parameters. I haven't been able to find any documentation to date that explains how to run the "EXEC SQL EXECUTE procname" command and specify the output parameters.

My stored procedure "aek_proc1" takes one input parameter (p1 - an 8-character string) and 3 output parameters (p2 - an integer; p3 - an 8-character string, and p4 a 40-character string).

My esqlc program contains the following code.

EXEC SQL BEGIN DECLARE SECTION;
char p1[9];
int p2;
char p3[9];
char p4[41];
WXEC SQL END DECLARE SECTION;

sprintf(&p1[0], "GL");
p2 = 0;
sprintf(&p3[0], "");
sprintf(&p4[0], "");

EXEC SQL EXECUTE aek_proc1 :p1,
:p2 OUTPUT,
:p3 OUTPUT,
:p4 OUTPUT;

I am getting errors at runtime about constants being passed for OUTPUT parameters.

I can run the same stored procedure in Query Analyser and it works beautifully (see below)

declare @.p1 char(8)
declare @.p2 integer
declare @.p3 char(8)
declare @.p4 char(40)

set @.p1 = 'GL'

execute aek_proc1 @.p1, @.p2 output, @.p3 output, @.p4 output

select @.p1 p1, @.p2 p2, @.p3 p3, @.p4 p4

Any idea what I'm doing wrong or how it should be coded?

I'd really appreciate any advice you can offer!!

I've spend hours browsing this newsgroup and found lots of examples of how to do this in VB and from Query Analyser but I can't find any examples for ESQL/C that work.

So, please help!!!

Thanks,

AllanK :rolleyes:Oops.

Don't know why the host variable names got turned into smileys, but that bit should have read...

EXEC SQL EXECUTE aek_proc1 :p1,
:p2 OUTPUT,
:p3 OUTPUT,
:p4 OUTPUT;|||Have you tried to replace : with @.?|||I tried changing the EXEC SQL EXEC command in the ESQL/C program to read...

EXEC SQL EXEC aek_proc1 @.p1, @.p2 OUTPUT, @.p3 OUTPUT, @.p4

after which I got the runtime error...

"0137- Must declare the variable '@.p1'."

I tried adding...

EXEC SQL DECLARE @.p1 char(8);
EXEC SQL DECLARE @.p2 int;
EXEC SQL DECLARE @.p3 char(8);
EXEC SQL DECLARE @.p4 char(40);

EXEC SQL SET @.p1 = 'GL';
EXEC SQL SET @.p2 = 0;
EXEC SQL SET @.p3 = '';
EXEC SQL SET @.p4 = '';

EXEC SQL EXEC aek_proc1 @.p1, @.p2 OUTPUT, @.p3 OUTPUT, @.p4 OUTPUT;

but the error persists.

Any further suggestions?

Thanks,

AllanK

Escaped URI

Hi
I have a valid and working template which takes 2 parameters and can be accessed
using a URI of the form
http://localhost/dbports/template/tm...65&AddrID=3084
However when I try to include this information in an XSLT transformation using the
document() function it escapes the & in the URIstring to & and accessing the
template using
http://localhost/dbports/template/tm...mp;AddrID=3084
doesn't seem to work. It just returns the following HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-1252"></HEAD>
<BODY></BODY></HTML>
This must be something simple mustn't it ? Any ideas ?
TIA
Feargal Hogan
Can you please forward the code fragment that does the invocation? Is that
the argument to the document() function or the output of the XSLT that you
then send over HTTP?
Thanks
Michael
"Feargal Hogan" <feargalhNO@.Spam.portinfo.co.uk> wrote in message
news:uVRIGc6rEHA.1232@.TK2MSFTNGP11.phx.gbl...
> Hi
> I have a valid and working template which takes 2 parameters and can be
> accessed
> using a URI of the form
> http://localhost/dbports/template/tm...65&AddrID=3084
> However when I try to include this information in an XSLT transformation
> using the
> document() function it escapes the & in the URIstring to & and
> accessing the
> template using
> http://localhost/dbports/template/tm...mp;AddrID=3084
> doesn't seem to work. It just returns the following HTML
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html;
> charset=windows-1252"></HEAD>
> <BODY></BODY></HTML>
> This must be something simple mustn't it ? Any ideas ?
> TIA
> Feargal Hogan
>
|||Michael
Thanks. I figured out the problem. I just set disable-output-escaping in
"<xsl:value-of " element
Feargal
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OcFfI2HsEHA.2136@.TK2MSFTNGP14.phx.gbl...
> Can you please forward the code fragment that does the invocation? Is that
> the argument to the document() function or the output of the XSLT that you
> then send over HTTP?
> Thanks
> Michael
> "Feargal Hogan" <feargalhNO@.Spam.portinfo.co.uk> wrote in message
> news:uVRIGc6rEHA.1232@.TK2MSFTNGP11.phx.gbl...
>

Error-Unable to get report parameters:Unable to access Web Service

Hi - In our RS + SP1 installation on Windows Server 2003, we recently began
to get the following error:
"Unable to get report parameters: Unable to access Web Service: The request
failed with HTTP status 403: Forbidden."
We are calling Render() in code using an account which has admin rights to
the RS box, is not locked out, etc. Event logs show no failures and the RS
logs are as useless as ever. I see no access denieds from Windows auditing
either.
Thoughts?
thanks muchHi,
Thanks for your posting!
From your descriptions, I understood that you are not able to access Web
Service with the error message "Unable to get report parameters: Unable to
access Web Service: The request failed with HTTP status 403: Forbidden."
Have I understood you? Correct me if I was wrong.
Based on my knowledge, it seems that "anonymous" for Reportserver virual
folder is enabled. Please try the following steps and let me know whether
it works
1. Right click ReportServer in IIS->Properties->Directory Security
2. Make sure "Intergrated Windows authentication" is checked and "enable
anonymouse access" is unchecked.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael, I looked at my anonymous setting but it was set properly.
By chance, I tried to execute http://server/reportserver/reportservice.asmx
and it said that there were no permissions to execute scripts in this virtual
root. Sure enough, I looked at the ReportServer virtual root config and the
Execute permissions were improperly set. Not sure how changed, but once I
put it back to "Scripts and Executables" I was good.
Thanks much
"Michael Cheng [MSFT]" wrote:
> Hi,
> Thanks for your posting!
> From your descriptions, I understood that you are not able to access Web
> Service with the error message "Unable to get report parameters: Unable to
> access Web Service: The request failed with HTTP status 403: Forbidden."
> Have I understood you? Correct me if I was wrong.
> Based on my knowledge, it seems that "anonymous" for Reportserver virual
> folder is enabled. Please try the following steps and let me know whether
> it works
> 1. Right click ReportServer in IIS->Properties->Directory Security
> 2. Make sure "Intergrated Windows authentication" is checked and "enable
> anonymouse access" is unchecked.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hi,
Thanks for your prompt updates!
It's great to hear that it did turn out to be an authority issue and you
yourself have resolved it.
Thank you for your patience and corporation. If you have any questions or
concerns on SQL Server, don't hesitate to let me know. We are always here
to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

Errors using multiple parameters in a SQL Statement

In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.

Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.

The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.

Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?

Thanks,

Mike

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...|||

I used to have a lot of problems with that when the query was to big or complex (Sub queries, etc). To solve it, and I use it as a standard, I place the query inside of a variable and then use an expression to make it parameterized.

Here is how:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=756434&SiteID=1

|||

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

|||

Rafael Salas wrote:

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

Kirk Haselden comments otherwise in his SSIS book... (Page 182) Granted, that's for Execute SQL Tasks.

Now I guess I'll have to go test it myself as that's what I've always used.|||Either will work. "Parameter0" or just "0".

The issue is likely as Rafael pointed out if you are using a complex query.|||

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

|||

Mike Batton wrote:

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

Can you post your query?|||

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

|||

Mike Batton wrote:

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

Use question marks - always, and don't number them.

select * from tew_d_dt where dt_key = ? and cal_yr_id = ?

Then, in the parameter mappings, you can use "Parameter0" or "0" for the parameter name to map to your variable.|||

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

|||

Mike Batton wrote:

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

You mean to dynamically select columns? That would not be a good idea as it will change metadata.|||

What about in the case part of a select statement... should that work or not?

Below is part of the actual query that we're trying to get to work.

Mike

select 'ent' AS level_desc,

sku.new_concept_cd AS code_1,

sku.color AS color_name,

case when sls.date_key = ?

then

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) else 0 end as sales_lw,

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) as sales_life,

from edw.jjwhse.tew_l_stock_status_sku sku

left outer join

edw_jj.sales.vew_r_sls_sku_loc_wk sls

on sls.sku_key = sku.sku_key

where sls.date_key <= ?

and sku.sku_type_desc = 'sales'

and sls.loc_key not in (6,9,10,11,12,13,14,106)

and sku.new_concept_cd is not null

group by sku.new_concept_cd, sku.color, sls.date_key

|||

Mike Batton wrote:

What about in the case part of a select statement... should that work or not?

Try it... I don't know. If it doesn't work, then as Rafael stated, you can build your query first as an expression in a variable. Build it there first, then use that variable in the OLE DB connector.|||

Anything that changes the structure of the dataflow pipeline will not work; this is in the select part of the query: adding/removing columns; changing data types; changing column names, etc.

Perhaps if you provide an example of what you need to accomplish; somebody around here could help you.

Error: StringStartsWith can't accept null parameters

Hi,
I wish to edit security on a certain report that has been published on my report
server. I visit the "Properties" tab in Report Manager, then click on "Security".
I click on "Edit Item Security" and click through the javascript popup about
inheritance. Then I get a page which says simply, "Error - StringStartsWith can't
accept null parameters".
Has anyone encountered this error? I have no clue how to proceed with debugging,
since I can't even find the code that's throwing this error, nor do I know to
which parameter it's trying to apply StringStartsWith.
Any help would be appreciated.
--Adam BlissPlease, tell us which version of Reporting Service you are using. Also
please provide stack trace for this failure. You should be able to locate it
in one of the log files. Log files are located in the LogFiles directory in
Reporting Services. Look at ones like
ReportServer__07_08_2004_##_##_##.log
and
ReportServerWebApp__07_08_2004_##_##_##.log
Look inside appropriate files and search for string 'StringStartsWith'.
Attach about 50 lines around it.
Thank you.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Adam Bliss" <abliss@.gmail.com> wrote in message
news:c52bcaf.0407081034.6f9bd2a2@.posting.google.com...
> Hi,
> I wish to edit security on a certain report that has been published on my
report
> server. I visit the "Properties" tab in Report Manager, then click on
"Security".
> I click on "Edit Item Security" and click through the javascript popup
about
> inheritance. Then I get a page which says simply, "Error -
StringStartsWith can't
> accept null parameters".
> Has anyone encountered this error? I have no clue how to proceed with
debugging,
> since I can't even find the code that's throwing this error, nor do I know
to
> which parameter it's trying to apply StringStartsWith.
> Any help would be appreciated.
> --Adam Bliss|||Has anyone solved this?
I am hitting this error when using the Sample Security Extension supplied by M$. Eventviewer tells me that Report server could not load the forms extension.
w3wp!ui!1!9/6/2006-09:09:40:: e ERROR: StringStartsWith can't accept null parameters
w3wp!ui!1!9/6/2006-09:09:40:: e ERROR: HTTP status code --> 500
--Details--
System.ArgumentException: StringStartsWith can't accept null parameters
at Microsoft.SqlServer.ReportingServices2005.RSConnection.GetSecureMethods()
at Microsoft.ReportingServices.UI.Global.RSWebServiceWrapper.GetSecureMethods()
at Microsoft.SqlServer.ReportingServices2005.RSConnection.IsSecureMethod(String methodname)
at Microsoft.SqlServer.ReportingServices2005.RSConnection.ValidateConnection()
at Microsoft.ReportingServices.UI.ReportingPage.EnsureHttpsLevel(HttpsLevel level)
at Microsoft.ReportingServices.UI.ReportingPage.ReportingPage_Init(Object sender, EventArgs args)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Control.OnInit(EventArgs e)
at System.Web.UI.Page.OnInit(EventArgs e)
at System.Web.UI.Control.InitRecursive(Control namingContainer)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
w3wp!ui!1!9/6/2006-09:09:41:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
Thanks,
Zac
From http://www.developmentnow.com/g/115_2004_7_0_0_448611/Error-StringStartsWith-cant-accept-null-parameters.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||Did you get this resolved? I'm getting exactly the same error message in different circumstances..
From http://www.developmentnow.com/g/115_2004_7_0_0_448611/Error-StringStartsWith-cant-accept-null-parameters.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||Got this error when trying to make RS work with forms authentication overr SSL. Though i can browse ReportServer folder, trying to access Reports virtual directory brings this error up.
Anyone has solved this issue
From http://developmentnow.com/g/115_2004_7_0_0_448611/Error-StringStartsWith-cant-accept-null-parameters.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||Hi
I'm also getting a very similar error. Any solutions for the same?
Any help would be appreciated
From http://www.developmentnow.com/g/115_2004_7_0_0_448611/Error-StringStartsWith-cant-accept-null-parameters.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Error: SetParameterInfo has not been called

I get error message "Provider cannot derive parameter information
and SetParameterInfo has not been called."

When I try to click on the Parameters button in the Transform Data Task Properties. I wanted to create variables to use in the where clause.

any idea how to fix this error?

novice userWhat isthe version of ADO?