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 character search

Is there an easy way to perform a SELECT where you have

blah LIKE 'asdf'

but instead of just returning all the asdf's, it also looks for sdf,
sdf, sdf, etc?Right now, this is what I'm doing: replacing each accent letter (a, e,
i, etc) with a string of possible accents. For example

A search for 'GONCALTRONICA' actually searches with:

'G[o][n][c][a]LTR[o][n][i][c][a]'

and returns the correct row with 'GONALTRNICA' in the field. I'm
thinking there has to be a better way to have a case insensitive
search. Maybe an option somewhere?

Thanks|||"PepperellMA" <andy@.pepperell.net> wrote in message
news:1106163716.326409.20730@.c13g2000cwb.googlegro ups.com...
> Right now, this is what I'm doing: replacing each accent letter (a, e,
> i, etc) with a string of possible accents. For example
> A search for 'GONCALTRONICA' actually searches with:
> 'G[o][n][c][a]LTR[o][n][i][c][a]'
> and returns the correct row with 'GONALTRNICA' in the field. I'm
> thinking there has to be a better way to have a case insensitive
> search. Maybe an option somewhere?
> Thanks

You can specify an accent-insensitive collation in your queries:

create table #pep (col1 nvarchar(100))
insert into #pep select 'GONALTRNICA'

-- Returns 0 rows
select * from #pep
where col1 = 'GONCALTRONICA'
-- Returns 1 row
select * from #pep
where col1 = 'GONCALTRONICA' collate SQL_Latin1_General_CP850_CI_AI

Simon|||I am getting the error "Line 7: Incorrect syntax near 'collate'." Maybe
I am using an out of date version of sql server that doesn't support
COLLATE (Microsoft SQL Server 7.00 - 7.00.623) ?

> You can specify an accent-insensitive collation in your queries:
> create table #pep (col1 nvarchar(100))
> insert into #pep select 'GONALTRNICA'
> -- Returns 0 rows
> select * from #pep
> where col1 = 'GONCALTRONICA'
> -- Returns 1 row
> select * from #pep
> where col1 = 'GONCALTRONICA' collate SQL_Latin1_General_CP850_CI_AI
>
> Simon|||"PepperellMA" <andy@.pepperell.net> wrote in message
news:1106165927.837051.321340@.f14g2000cwb.googlegr oups.com...
> I am getting the error "Line 7: Incorrect syntax near 'collate'." Maybe
> I am using an out of date version of sql server that doesn't support
> COLLATE (Microsoft SQL Server 7.00 - 7.00.623) ?

COLLATE is only available in SQL 2000 - please always mention which version
you have (and to be fair, I shouldn't have assumed you had 2000). In SQL 7,
the sort order is fixed at install time, so you would have to rebuild the
master database to change it.

If it's important enough to you, it might be worth rebuilding (but of course
you run the risk of breaking other code), setting up an additional MSSQL
installation for insensitive searches, or upgrading to 2000, otherwise
you're probably stuck with writing code as you've already done. Fulltext
searching is always case and accent sensitive, so unfortunately that's not
an option either.

By the way, your build version indicates you haven't installed any
servicepacks (SP4 is the latest one for SQL 7).

Simon

Extend the metadata

Hi,
I want to add more fields in report list. So instead of just showing the
report name and description, I want hyperlink to some other information as
well. Is it possible? if so how can I do that?
Any help would be highly appreciated
Regards,You can add additonal fields to the table on the report (where you drop the
report fields). Right mouse click on a column and add another column. Then
put some text in the field (like detail ...). I then make it blue and
underlined. The do a right mouse click, properties, advanced properties,
navigation tab and use the jump to URL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mna" <mna@.somewhere.com> wrote in message
news:%230qKrQ4YFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to add more fields in report list. So instead of just showing the
> report name and description, I want hyperlink to some other information as
> well. Is it possible? if so how can I do that?
> Any help would be highly appreciated
> Regards,
>|||Thanks Bruce!
I think this is not what I wanted to know.
What I want to know is when I have created one report, it goes to the main
reports list. That list contains all the reports. Now on that list I want to
add additional columns so before running the report if users want to see
additional information they can do so. For example besides report name I
want to see who actually approved that report or what is the name of the
department this report belongs to.
Regards,
Nadeem
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uugoGU5YFHA.1368@.tk2msftngp13.phx.gbl...
> You can add additonal fields to the table on the report (where you drop
the
> report fields). Right mouse click on a column and add another column. Then
> put some text in the field (like detail ...). I then make it blue and
> underlined. The do a right mouse click, properties, advanced properties,
> navigation tab and use the jump to URL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "mna" <mna@.somewhere.com> wrote in message
> news:%230qKrQ4YFHA.2124@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> > I want to add more fields in report list. So instead of just showing the
> > report name and description, I want hyperlink to some other information
as
> > well. Is it possible? if so how can I do that?
> >
> > Any help would be highly appreciated
> >
> > Regards,
> >
> >
>|||The data needs to come from somewhere. If you modify the xml it is being
rendered and any modifications are going to be ignored. I don't think they
would even be accessible by the time the report runs. If you can get the
extra information from a database then you can have an additional dataset
that you get this information from.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mna" <mna@.somewhere.com> wrote in message
news:eiiI8c9YFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Thanks Bruce!
> I think this is not what I wanted to know.
> What I want to know is when I have created one report, it goes to the main
> reports list. That list contains all the reports. Now on that list I want
> to
> add additional columns so before running the report if users want to see
> additional information they can do so. For example besides report name I
> want to see who actually approved that report or what is the name of the
> department this report belongs to.
> Regards,
> Nadeem
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uugoGU5YFHA.1368@.tk2msftngp13.phx.gbl...
>> You can add additonal fields to the table on the report (where you drop
> the
>> report fields). Right mouse click on a column and add another column.
>> Then
>> put some text in the field (like detail ...). I then make it blue and
>> underlined. The do a right mouse click, properties, advanced properties,
>> navigation tab and use the jump to URL.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "mna" <mna@.somewhere.com> wrote in message
>> news:%230qKrQ4YFHA.2124@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> > I want to add more fields in report list. So instead of just showing
>> > the
>> > report name and description, I want hyperlink to some other information
> as
>> > well. Is it possible? if so how can I do that?
>> >
>> > Any help would be highly appreciated
>> >
>> > Regards,
>> >
>> >
>>
>

Exporting to Excel format

Quite often, when a user exports to an Excel spreadsheet and selects "Open"
instead of "Save" for the file download dialog the file does not open.
Instead, they get an error saying "xxxxx.xls could not be found. Check the
spelling of the file name, and verify that the location is correct". This
seems to happen more often than not.
Is there anything which can be done about this? The problem doesn't occur if
they save the file instead of opening it, but they should not have to
perform extra steps for what should be a simple operation.
PeterThis is a bug. You have identified the workaround: Save the file to the
local machine and then open it. At this time I cannot give you any specific
guidance when a fix might be available.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Kenyon" <p.kenyon.no.spam@.paradise.net.nz> wrote in message
news:%23XZJHuPdEHA.3572@.TK2MSFTNGP10.phx.gbl...
> Quite often, when a user exports to an Excel spreadsheet and selects
"Open"
> instead of "Save" for the file download dialog the file does not open.
> Instead, they get an error saying "xxxxx.xls could not be found. Check the
> spelling of the file name, and verify that the location is correct". This
> seems to happen more often than not.
> Is there anything which can be done about this? The problem doesn't occur
if
> they save the file instead of opening it, but they should not have to
> perform extra steps for what should be a simple operation.
> Peter
>

Exporting to a xml file

Dear all,
I've got a table of which I would need obtain a XML file. How do I such
thing?
I mean, instead of to obtain a .DAT or .CSV from that table as it customary,
a xml.
Any advice or though woud be greatly.
Regards,Are you using SQL Server 2005 or SQL Server 2000? Can it be manual or does
it need to be programmatic?
I would look into using FOR XML to generate the XML from the table and use
ADO or ADO.Net to take the result stream (use the stream interface, not the
rowset interface) to load a file.
Best regards
Michael
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30EBA615-16A1-4751-8358-A32030FC2CCE@.microsoft.com...
> Dear all,
> I've got a table of which I would need obtain a XML file. How do I such
> thing?
> I mean, instead of to obtain a .DAT or .CSV from that table as it
> customary,
> a xml.
> Any advice or though woud be greatly.
> Regards,

Exporting to a xml file

Dear all,
I've got a table of which I would need obtain a XML file. How do I such
thing?
I mean, instead of to obtain a .DAT or .CSV from that table as it customary,
a xml.
Any advice or though woud be greatly.
Regards,
Are you using SQL Server 2005 or SQL Server 2000? Can it be manual or does
it need to be programmatic?
I would look into using FOR XML to generate the XML from the table and use
ADO or ADO.Net to take the result stream (use the stream interface, not the
rowset interface) to load a file.
Best regards
Michael
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30EBA615-16A1-4751-8358-A32030FC2CCE@.microsoft.com...
> Dear all,
> I've got a table of which I would need obtain a XML file. How do I such
> thing?
> I mean, instead of to obtain a .DAT or .CSV from that table as it
> customary,
> a xml.
> Any advice or though woud be greatly.
> Regards,