Extra parameters being added onto SP call with ADO?

We are running into problems on our development environment with extra
parameters being added on to a stored procedure call. Instead of the
expected stored procedure call, the trace shows something like this:

declare @.P1 int
set @.P1=NULL
<<expected stored procedure call>> , @.P1 output, <<repeat of first
three sp parms>>
select @.P1

The developer has checked the code, and I have checked the SP - both
seem to match what is in production (which works fine). The databases
are on the same server, and the apps are running on seperate web
servers.

I'm guessing this may be some sort of configuration issue with ADO,
SQL, or something else - has anyone run into something similar to
this? Thanks!

DaveSo, is the sp running at all when called with that additional parameter? It
should fail if that parameter (the one you think is additional) is not
declared in the sp.

What happens if you paste the output from Profiler into Query Analyzer and
run it?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm

"Dokter Z" <dzahn@.execpc.com> wrote in message
news:7e6a5a6.0402250644.4ef950eb@.posting.google.co m...
We are running into problems on our development environment with extra
parameters being added on to a stored procedure call. Instead of the
expected stored procedure call, the trace shows something like this:

declare @.P1 int
set @.P1=NULL
<<expected stored procedure call>> , @.P1 output, <<repeat of first
three sp parms>>
select @.P1

The developer has checked the code, and I have checked the SP - both
seem to match what is in production (which works fine). The databases
are on the same server, and the apps are running on seperate web
servers.

I'm guessing this may be some sort of configuration issue with ADO,
SQL, or something else - has anyone run into something similar to
this? Thanks!

Dave|||Vyas -

Thanks for the response. Actually, we have discovered that the initial
problem was caused by

1) Some "sub-optimal" Paramaters.Refresh code
2) A stored procedure call issued by user aaaaa ended up calling
bbbbb.stored_proc_name instead of dbo.stored_proc_name. I'm currently
researching that issue...

Dave

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

extended stored procedures: is there a 256 character limit on INPUT parameters?

Hello,
On SQL Server 2000 (SP3), for extended stored procedures: is there a
256 character limit on INPUT parameters?
And if so, is there a way around that?
Thanks,
BertAFAIK, there is no such limit.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bert Szoghy" <webmaster@.quadmore.com> schrieb im Newsbeitrag
news:34276ef9.0504241118.4596c291@.posting.google.com...
> Hello,
> On SQL Server 2000 (SP3), for extended stored procedures: is there a
> 256 character limit on INPUT parameters?
> And if so, is there a way around that?
> Thanks,
> Bert|||Here's a quick test script for you:
CREATE PROCEDURE dbo.usp_Char256Test
@.s VARCHAR(257)
AS
PRINT LEN(@.s)
GO
DECLARE @.TempString VARCHAR(257)
SET @.TempString = REPLICATE('X', 257)
PRINT LEN(@.TempString)
EXEC dbo.usp_Char256Test @.TempString
This creates an SP that allows a 257 char VARCHAR to be passed in. It then
prints the Length of the passed in string to verify that all 257 chars were
passed successfully.
The limit you're encountering is probably due to a CHAR(256) or VARCHAR(256)
limit placed on the definition of the parameter in the SP declaration (above
I set the limit to 257, but any value up to 8000 for VARCHAR [4,000 for
NVARCHAR] should work).
"Bert Szoghy" <webmaster@.quadmore.com> wrote in message
news:34276ef9.0504241118.4596c291@.posting.google.com...
> Hello,
> On SQL Server 2000 (SP3), for extended stored procedures: is there a
> 256 character limit on INPUT parameters?
> And if so, is there a way around that?
> Thanks,
> Bert|||Steve Kass <skass@.drew.edu> wrote in message news:<OJxPTgSSFHA.3716@.TK2MSFTNGP14.phx.gbl>..
.
> Bert,
> For some system extended stored procedures, the parameters
> are defined as varchar(256). For those, there is no way around
> unless you rewrite the xp (not a good idea I think). For xp's you
> write yourself, I'm not aware of any limit. Are you having a
> particular problem you need help with?
> Steve Kass
> Drew University
> Bert Szoghy wrote:
>
Hello,
Thank you for your responses.
I have an extended stored proc DLL with C++ code which just looks fine
to me, which it refuses to give me more than 256 characters.
I found the following reference before posting here which made me
suspect it was a SQL Server limitation:
http://groups.google.ca/groups?hl=e...r />
GP15.phx.
gbl%26rnum%3D1
I am using a varchar(8000) in the trigger calling the extended stored
proc.
I'm about to try stepping through the DLL but the code is a bit nasty
and it would be nice if a SQL Server guru would point out a detail to
tweak.
Thanks guys!
Thanks again,
Bertsql

Extended Stored Procedures 7.0 - 2000

I have a problem with passing parameters into an extended stored procedure in sql 2000 that was not present in sql 7.0.

I pass in a parameter of type varchar(8000) but sql 2000 truncates this value to 255 characters.

I was using srv_paramdata(sproc,1) for example to get the pertinent data. Microsoft says that srv_paramdata has been superceded by srv_paraminfo, but this function is crashing my dll when I run it....

BYTE bType;
unsigned long cbMaxLen;
unsigned long cbActualLen;
BOOL fNull;
BYTE data;

// Use srv_paraminfo to get data type and length information.
srv_paraminfo(sproc, 2, &bType, &cbMaxLen, &cbActualLen,&data, &fNull);

Any ideas?

This worked in sql 7.0.

wsprintf(string,"%s",(const char*) srv_paramdata(sproc,1));

Umm help appreciated.

WintermuteUmm actual guys... thanks to anyone who has looked to help me out, but I think I have (*this).sorted->muchos[gracias++];

umm in keeping with Open Source Software paradigms and stuff if anyone is curious

PBYTE*data;
BYTE type;
unsigned long Maxlen;
unsigned long Reallen;
int nParams;
BOOL boolnull;
char head_descriptor[24];
FILE*file;

nParams=srv_rpcparams(sproc);


data=new PBYTE[1];
if(data==NULL)
{
ServerErrorMsg(sproc,"Was unable to allocate the requisite memory for this data operation");
return -1;
};
memset(data,0,nParams*sizeof(PBYTE));


srv_paraminfo(sproc,2,&type,&Maxlen,&Reallen,NULL,&boolnull);

sprintf(head_descriptor,"Parameter 2: Input");
srv_describe(sproc,2,head_descriptor,SRV_NULLTERM, type,Reallen,type,Reallen,NULL);

if(boolnull==0)
{
data[0]=(unsigned char*)malloc(Reallen);
if(data[0]==NULL)
{
ServerErrorMsg(sproc,"Unable to allocate memory for this variable!");
delete data;
return -1;
};

srv_paraminfo(sproc,2,&type,&Maxlen,&Reallen,data[0],&boolnull);
}
else
{
ServerErrorMsg(sproc,"There Seems to be no data present for parameter 2");
return -1;
};

file=fopen("C:/bod.txt","a+");
if(file==NULL)
{
ServerErrorMsg(sproc,"Unable to access the specified filename for file input, Please check the filename and try again!");
return -1;
};

fwrite(data[0],Reallen,1,file);
fclose(file);


return 0;
};

Regards
Wintermute.sql

expressions linked to parameters

Is it possible to link expressions to report parameters
for instance i have WTD, MTD and YTD sales targets in my DB and have
WTD, MTD and YTD as report Parameters can i do this?
If i select MTD link the Expression to say if MTD then Show MTD sales
target etc etcOn Jun 8, 10:23 am, blueboy <matt_me...@.hotmail.com> wrote:
> Is it possible to link expressions to report parameters
> for instance i have WTD, MTD and YTD sales targets in my DB and have
> WTD, MTD and YTD as report Parameters can i do this?
> If i select MTD link the Expression to say if MTD then Show MTD sales
> target etc etc
If I'm understanding you correctly, you will want to access the values
of a parameter as opposed to if a parameter value has been selected or
not. Something like this should work:
=iif(Parameters!SalesTargetParam.Value = "MTD", Fields!
MTDSalesTarget.Value, Nothing)
Of course, this should also work:
=switch(Parameters!SalesTargetParam.Value = "WTD", Fields!
WTDSalesTarget.Value, Parameters!SalesTargetParam.Value = "MTD",
Fields!MTDSalesTarget.Value, Parameters!SalesTargetParam.Value ="YTD", Fields!YTDSalesTarget.Value, true, nothing)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Expression help on dataset parameter value

I am trying to use and expression in the parameters tab on the dataset. I
have a parameter called END_DATE and I want it to be equal to the expression
=DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
The expression comes out fine on the report. I tested it there to see if it
would generate the proper date and it did. Then I moved the expression from
the report and into the Value side of the Parameter on the Parameter tab of
the dataset and
I get an error CLI0111E Numeric value out of range SQLSTATE=22003
on this (I am using DB2)It probably has to be a DB2 function in the dataset...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:596479BF-E8BE-4475-A7A8-912BAAE61CCE@.microsoft.com...
>I am trying to use and expression in the parameters tab on the dataset. I
> have a parameter called END_DATE and I want it to be equal to the
> expression
> =DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
> The expression comes out fine on the report. I tested it there to see if
> it
> would generate the proper date and it did. Then I moved the expression
> from
> the report and into the Value side of the Parameter on the Parameter tab
> of
> the dataset and
> I get an error CLI0111E Numeric value out of range SQLSTATE=22003
> on this (I am using DB2)|||Sorry Wayne ... I am not sure what you mean by your reply. The dataset
consists of just a stored procedure. In the dataset tab this is all there is
- -
GRSINST1.SP_RPT_RES_UPTIME
how would I make this a DB2 function in the dataset?
"Wayne Snyder" wrote:
> It probably has to be a DB2 function in the dataset...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:596479BF-E8BE-4475-A7A8-912BAAE61CCE@.microsoft.com...
> >I am trying to use and expression in the parameters tab on the dataset. I
> > have a parameter called END_DATE and I want it to be equal to the
> > expression
> > =DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
> >
> > The expression comes out fine on the report. I tested it there to see if
> > it
> > would generate the proper date and it did. Then I moved the expression
> > from
> > the report and into the Value side of the Parameter on the Parameter tab
> > of
> > the dataset and
> > I get an error CLI0111E Numeric value out of range SQLSTATE=22003
> > on this (I am using DB2)
>
>|||I must have forgotten to say that I am using a db2 stored procedure and
passing it parameters so I dont know where else I can manipulate the parm
since it is used for the query. I thought I had read that you can use
expressions on the parameter tab of the dataset. So why cant I get this
expression to work. It is fairly simple and it works when I put it on the
report (which I did just to verify syntax).
"Wayne Snyder" wrote:
> It probably has to be a DB2 function in the dataset...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
> news:596479BF-E8BE-4475-A7A8-912BAAE61CCE@.microsoft.com...
> >I am trying to use and expression in the parameters tab on the dataset. I
> > have a parameter called END_DATE and I want it to be equal to the
> > expression
> > =DATEADD(DateInterval.Day, 1, Parameters!END_DATE.Value)
> >
> > The expression comes out fine on the report. I tested it there to see if
> > it
> > would generate the proper date and it did. Then I moved the expression
> > from
> > the report and into the Value side of the Parameter on the Parameter tab
> > of
> > the dataset and
> > I get an error CLI0111E Numeric value out of range SQLSTATE=22003
> > on this (I am using DB2)
>
>

Expose SQL Meta Data via ASP

Hi,

Apologies if this is better posted in an ASP group, but here goes
anyway ...

Is it possible to work out what parameters a stored procedure expects,
using ASP?

I would like to take the name of a stored procedure, work out what
input parameters it has and build a form based on them in ASP.

Thanks,
MB.Tools (mbrocklehurst@.hotmail.com) writes:
> Apologies if this is better posted in an ASP group, but here goes
> anyway ...
> Is it possible to work out what parameters a stored procedure expects,
> using ASP?
> I would like to take the name of a stored procedure, work out what
> input parameters it has and build a form based on them in ASP.

You can use the .Refresh method for a command object. That will populate
a parameter collection for you.

Now, how you actually access that from ASP, you will have to ask someone
else. I don't know ASP.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||or you can run a query against INFORMATION_SCHEMA.PARAMETERS

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959DF15265D4AYazorman@.127.0.0.1...
> Tools (mbrocklehurst@.hotmail.com) writes:
> > Apologies if this is better posted in an ASP group, but here goes
> > anyway ...
> > Is it possible to work out what parameters a stored procedure expects,
> > using ASP?
> > I would like to take the name of a stored procedure, work out what
> > input parameters it has and build a form based on them in ASP.
> You can use the .Refresh method for a command object. That will populate
> a parameter collection for you.
> Now, how you actually access that from ASP, you will have to ask someone
> else. I don't know ASP.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Exporting to Excel

Hi,
I am having a problem exporting to excel for a particualr report. Based on
the parameters chosen then a different type of report shows up. However,
when I try to export a particular report it gives me all three reports (one
with valid data and two with data in the form of XXXXXXXXs). Does anyone
have any ideas on how to fix this?
Thanks, CaseyI have several reports where the table that is visible in the report is
determined by a parameter, which works fine until I try to export the report
to excel. When I try to export to excel all four tables are visible. Any
ideas on how to work around this behavior.
TIA,
Arly