Hello, friends,
We call stored procedures from our app (asp.net), and use Try...Catch to
handle any possible DB error. But, can we handle all errors in a stored
proceudre? In another word, all DB errors should be caught within a stored
procedure, and return back to callers gracefully, as if nothing wrong.
We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
Violation were still caught by our app, not sp.
Any ideas, reference papers, sample source code?
Thanks a lot.http://www.sommarskog.se/error-handling-II.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:59F7E8E6-1477-4A42-9B42-BB34D0136102@.microsoft.com...
> Hello, friends,
> We call stored procedures from our app (asp.net), and use Try...Catch to
> handle any possible DB error. But, can we handle all errors in a stored
> proceudre? In another word, all DB errors should be caught within a stored
> procedure, and return back to callers gracefully, as if nothing wrong.
> We tried IF @.@.ERROR > 0 in sp, but errors such as Unique Constraint
> Violation were still caught by our app, not sp.
> Any ideas, reference papers, sample source code?
> Thanks a lot.
Showing posts with label net. Show all posts
Showing posts with label net. Show all posts
Thursday, March 29, 2012
Can we handle all errors within a stored proceudre.
Hello, friends,
We are calling stored procedures (sp) from our web app (asp.net). Since
using Try...Catch... in asp.net with C# or VB.net is very expensive, we are
considering to handle all database error in sp so that web app won't see any
DB error.
Do we have a way to catch all erros in sp and return back to callers
gracefully, as if nothing wrong in DB?
We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
Unique contraint violation, etc.
Any ideas, reference papers, sample source code?
Thanks a lotexamnotes <Andrew@.discussions.microsoft.com> wrote in
news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:
> We are calling stored procedures (sp) from our web app (asp.net).
> Since using Try...Catch... in asp.net with C# or VB.net is very
> expensive, we are considering to handle all database error in sp so
> that web app won't see any DB error.
> Do we have a way to catch all erros in sp and return back to callers
> gracefully, as if nothing wrong in DB?
> We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
> Unique contraint violation, etc.
> Any ideas, reference papers, sample source code?
In SQL Server 2005 you can, I've used the following code sample to
demonstrate it in my classes (Using the pubs database):
set XACT_ABORT on
begin try
begin transaction
declare @.id int;
insert into jobs values ('Testjobb',10,10)
set @.id = @.@.identity;
insert into employee values ('123456789','Gates',null,'Bill',@.id+
1,10,'0877',GetDate());
commit transaction
end try
begin catch
select error_message();
rollback transaction
end catch
I'll leave it up to you to implement this in a stored procedure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||What about SQL Server 2000? That is the one we are using.
"Ole Kristian Bang?s" wrote:
> examnotes <Andrew@.discussions.microsoft.com> wrote in
> news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:
>
> In SQL Server 2005 you can, I've used the following code sample to
> demonstrate it in my classes (Using the pubs database):
> set XACT_ABORT on
> begin try
> begin transaction
> declare @.id int;
> insert into jobs values ('Testjobb',10,10)
> set @.id = @.@.identity;
> insert into employee values ('123456789','Gates',null,'Bill',@.id+
> 1,10,'0877',GetDate());
> commit transaction
> end try
> begin catch
> select error_message();
> rollback transaction
> end catch
> I'll leave it up to you to implement this in a stored procedure.
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||examnotes <Andrew@.discussions.microsoft.com> wrote in
news:C25DD758-622D-4CB5-A870-F112B1C337C6@.microsoft.com:
> What about SQL Server 2000? That is the one we are using.
Sorry. This feature was introduced in SQL Server 2005. In SQL Server 2000
you have to check for errors after each and every command that may cause an
error (which may happen to be almost everyone)
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql
We are calling stored procedures (sp) from our web app (asp.net). Since
using Try...Catch... in asp.net with C# or VB.net is very expensive, we are
considering to handle all database error in sp so that web app won't see any
DB error.
Do we have a way to catch all erros in sp and return back to callers
gracefully, as if nothing wrong in DB?
We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
Unique contraint violation, etc.
Any ideas, reference papers, sample source code?
Thanks a lotexamnotes <Andrew@.discussions.microsoft.com> wrote in
news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:
> We are calling stored procedures (sp) from our web app (asp.net).
> Since using Try...Catch... in asp.net with C# or VB.net is very
> expensive, we are considering to handle all database error in sp so
> that web app won't see any DB error.
> Do we have a way to catch all erros in sp and return back to callers
> gracefully, as if nothing wrong in DB?
> We tried to check IF @.@.ERROR > 0, but web app still get erros, such as
> Unique contraint violation, etc.
> Any ideas, reference papers, sample source code?
In SQL Server 2005 you can, I've used the following code sample to
demonstrate it in my classes (Using the pubs database):
set XACT_ABORT on
begin try
begin transaction
declare @.id int;
insert into jobs values ('Testjobb',10,10)
set @.id = @.@.identity;
insert into employee values ('123456789','Gates',null,'Bill',@.id+
1,10,'0877',GetDate());
commit transaction
end try
begin catch
select error_message();
rollback transaction
end catch
I'll leave it up to you to implement this in a stored procedure.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||What about SQL Server 2000? That is the one we are using.
"Ole Kristian Bang?s" wrote:
> examnotes <Andrew@.discussions.microsoft.com> wrote in
> news:0F041009-08FF-4FF1-8AC7-D74968D16C05@.microsoft.com:
>
> In SQL Server 2005 you can, I've used the following code sample to
> demonstrate it in my classes (Using the pubs database):
> set XACT_ABORT on
> begin try
> begin transaction
> declare @.id int;
> insert into jobs values ('Testjobb',10,10)
> set @.id = @.@.identity;
> insert into employee values ('123456789','Gates',null,'Bill',@.id+
> 1,10,'0877',GetDate());
> commit transaction
> end try
> begin catch
> select error_message();
> rollback transaction
> end catch
> I'll leave it up to you to implement this in a stored procedure.
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||examnotes <Andrew@.discussions.microsoft.com> wrote in
news:C25DD758-622D-4CB5-A870-F112B1C337C6@.microsoft.com:
> What about SQL Server 2000? That is the one we are using.
Sorry. This feature was introduced in SQL Server 2005. In SQL Server 2000
you have to check for errors after each and every command that may cause an
error (which may happen to be almost everyone)
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql
Tuesday, March 27, 2012
Can we do Even Programming in MS SQL 2005 Reporting Services ?
Hi,
Can anyone help me in this ?
Can we do event drive Programming things only in SQL 2005 Reporting Services without using VB.net ?
I want that on mouse move event or on focus on some particular cell of table or matrix a hidden picture or graph is visible.
I am using DUNDA's Plugins in my report.
Can anyone tell me how i can do this ?
RS_DBA
Do you have Visual Studio 2005? You can do event driven programming with that and use the report viewer to show your SQL reports. It allows you to easily create a front end for you reports.|||
Hi,
This is my limitation i have to use only SQL 2005 reporting service.
can we deploy RDL files into report server without using web services through .Net
how can we deploy the reports into report server with out using web services from .Net. Is it possible.
Hi vidya
It is possible, after the initial deployment and folders were created. You could do the following:
Browse to your reportserver. Normally [server]/reports
Browse to the report and click on it. Select the report's 'Properities' Tab. Under the heading 'Report Definition' are 2 options 'Edit' and 'Update'. Use the 'Update' to upload the new rdl file. This will deploy a new version.
Hope this helps
,l0n3i200n
Sunday, March 25, 2012
Can we build Web UIs with SSRS?
If all one needs is a bunch of reports, then, instead of going the
whole hog writing a UI in a bespoke ASP.NET application, can one take
the sole refuge of SQL Server 2005 Reporting Services to do a neat UI
along with the reports?
As in, one of the parameters for a report may be a period range. Can
SSRS help me build a form with two date pickers that I can:
1. program the events of, package the date ranges, pass it to the
report and call my report file from?
2. publish the form as a Web form?
Or, does one have to rely on a bespoke ASP.NET app to do it? What are
the limits of SSRS?SSRS has its limitations.
SSRS relies greatly on how the SQL code is written.
For example, if you are using date ranges - reporting services is there
to be the middle man between presentation and data. Nothing more. All
reporting services will require is 2 parameters(start date and end
date).
Meaning SSRS will have to TEXTBOX's where the user will just enter 2
dates and it will run the report between the 2 dates. In this case
there is no validation of the paramters but i can all be handleled on
the DB side. So reporting services doesnt have built in controls where
the user will enter dates, its only there to GET parameters. These
parameters are provided in a text box.
SSRS is not a web application where the user has easy to read UI to
enter paramters. It does have a VERY nice way of displaying the
results. Its buily to display a result of a DB call, not to present the
user with a web form.
regards,
Stas K.|||Thanks, Sorcerdon. I understood what you're saying. Now let me present
a broader picture of what I'm thinking might just be possible.
I have to build a bespoke ASP.NET app, which mostly requires to use
SSRS as a backend for reporting. So, in effect, this bespoke Web app is
just to provide UI forms and call SSRS with parameters.
I was considering if it might be possible to use any of the SQL Server
2005 services (Integration, Application, Reporting, Anything Else) to
get the UI functionality also as it is, out-of-the-box.
After all, what I'll be building a bespoke application is to get a UI
to call my reports. Surely, Microsoft, with its DSI and software
factories initiative might have as well provided with some tool to
automate that as well out-of-the-box.
Thoughts?|||Actually, in SSRS 2005, there is a better implementation of the Date type
parameters, with a calendar control and input validation controls.
Thiago
--
Regards,
"Sorcerdon" wrote:
> SSRS has its limitations.
> SSRS relies greatly on how the SQL code is written.
> For example, if you are using date ranges - reporting services is there
> to be the middle man between presentation and data. Nothing more. All
> reporting services will require is 2 parameters(start date and end
> date).
> Meaning SSRS will have to TEXTBOX's where the user will just enter 2
> dates and it will run the report between the 2 dates. In this case
> there is no validation of the paramters but i can all be handleled on
> the DB side. So reporting services doesnt have built in controls where
> the user will enter dates, its only there to GET parameters. These
> parameters are provided in a text box.
> SSRS is not a web application where the user has easy to read UI to
> enter paramters. It does have a VERY nice way of displaying the
> results. Its buily to display a result of a DB call, not to present the
> user with a web form.
> regards,
> Stas K.
>|||I was solely speaking of Reporting Services 2000. My bad.
There is a UI for reporting that is built in for reporting
services(report manager). People can log in subsrcibe to report and get
it in their mail every morning. All of that is built in. And they can
run the reports at will.
The problem is that in RS2000 (I have not yet started working with 2005
until very recently) it wasnt as nice of a UI. So, perhaps a RS 2005
PRO can answer this?
As for out-of-the-box - there is always report manager. The users input
their parameters - or they are defualted to defualt parameters. It DOES
have it built in, yes!
Here are some screenies of what the report manager looks like:
http://www.windowsitpro.com/Files/09/40529/Figure_07.gif
http://msdn.microsoft.com/library/en-us/dnhcvs04/html/408dobson5.jpg
http://www.codeproject.com/books/MSReportingServices/img005.jpg
In that last screenie you can see how the parameters are inputed - in
those text boxes.
regards,
Stas K.|||Hi,
Ofcourse RS is meant for reporting and not giving some fancy UI. But yes in
2005 they have provided what is called "Report Viewer" (comes with VS2005)
which you can use it in your Asp.Net along with your UI. Want to explore ?
search for "ReportViewer Controls (Visual Studio)" in VS 2005 documentation.
Amarnath
"Water Cooler v2" wrote:
> Thanks, Sorcerdon. I understood what you're saying. Now let me present
> a broader picture of what I'm thinking might just be possible.
> I have to build a bespoke ASP.NET app, which mostly requires to use
> SSRS as a backend for reporting. So, in effect, this bespoke Web app is
> just to provide UI forms and call SSRS with parameters.
> I was considering if it might be possible to use any of the SQL Server
> 2005 services (Integration, Application, Reporting, Anything Else) to
> get the UI functionality also as it is, out-of-the-box.
> After all, what I'll be building a bespoke application is to get a UI
> to call my reports. Surely, Microsoft, with its DSI and software
> factories initiative might have as well provided with some tool to
> automate that as well out-of-the-box.
> Thoughts?
>|||Thanks everyone.
Sorcerdon and Amarnath,
Where do I find these "Report Manager" and "Report Viewer"? I cannot
see "ReportViewer Controls" in my toolbox. Do I have to import them (as
in set a reference to some assembly containing them)?
Or, is it because I do not have the complete Visual Studio 2005
Enterprise Architect Edition. I only have SQL Server 2005 Business
Intelligence Development Studio (which is basically a stripped down
version of VS .NET 2005 with only data-centric project templates).|||This is so wrong on so many levels.
I don't usually say this sort of thing but please until you learn more about
the product DO NOT jump in and answer questions.
Let's start with the most glaring problem. You state that the date would be
text with no validation. In RS 2000 there was not a date picker. In RS 2005
there is a date picker. BUT, even assuming you are on RS 2000 you are still
totally wrong. If you go to the menu Report->Report Parameters you can set
the data type for each parameter. So, even in RS 2000 there is validation
for a date field (or an integer field etc), they cannot put in a non-date if
the field should be a date field.
To say RS is not a web app that the user can enter parameters in easily is
also so totally wrong. Yes, some people prefer to have more control over
placement of controls and other things but RS comes with a totally usable
portal called Report Manager. You can have the parameters be based on a
query (so you that pick from a dropdown list). You can have cascading
parameters where the second parameter list is based on the previous
parameter. You can set defaults for the parameters, etc etc. Each report you
can place a description with it so when the user sees the list of reports
each report has a brief description. In RS 2005 you can have multi-select
parameter lists.
So in short, your answer has set this guy off on a totally wrong tangent. It
could be that the existing portal that ships with RS would fullfill all his
needs.
I know you were trying to be helpfull but you are lacking in knowledge about
the product.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1144686176.448071.239430@.i40g2000cwc.googlegroups.com...
> SSRS has its limitations.
> SSRS relies greatly on how the SQL code is written.
> For example, if you are using date ranges - reporting services is there
> to be the middle man between presentation and data. Nothing more. All
> reporting services will require is 2 parameters(start date and end
> date).
> Meaning SSRS will have to TEXTBOX's where the user will just enter 2
> dates and it will run the report between the 2 dates. In this case
> there is no validation of the paramters but i can all be handleled on
> the DB side. So reporting services doesnt have built in controls where
> the user will enter dates, its only there to GET parameters. These
> parameters are provided in a text box.
> SSRS is not a web application where the user has easy to read UI to
> enter paramters. It does have a VERY nice way of displaying the
> results. Its buily to display a result of a DB call, not to present the
> user with a web form.
> regards,
> Stas K.
>|||Hey Bruce,
Thanks for that RS2000 validation control re-answer. I seem to have
never known it existed - perhaps because I am running all my reports of
a web app.
Same for the second answer.
regards,
Stas K.|||I have noticed other posts of yours after my harsh response here and you
have good answers in other areas. I got a little excitable on my post.
You obviously do know other areas of the product, just not Report Manager.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1144787538.172402.57350@.g10g2000cwb.googlegroups.com...
> Hey Bruce,
> Thanks for that RS2000 validation control re-answer. I seem to have
> never known it existed - perhaps because I am running all my reports of
> a web app.
> Same for the second answer.
> regards,
> Stas K.
>|||Bruce,
I am actually a software developer not a report writer although I do
both.
So I really should go answer questions in other forums. In these forums
I hope to help myself learn and perfect my technique when writing
reports.
Although some questions here come from N-Users... So I am able to think
of solutions.
As for Report Manager, in this case I am the N-user ^_^ - I dont like
how it looks as a matter of fact. I want a whole new look for the whole
thing. I like a sharepoint type of look(if your familiar).
regards,
Stas K.
whole hog writing a UI in a bespoke ASP.NET application, can one take
the sole refuge of SQL Server 2005 Reporting Services to do a neat UI
along with the reports?
As in, one of the parameters for a report may be a period range. Can
SSRS help me build a form with two date pickers that I can:
1. program the events of, package the date ranges, pass it to the
report and call my report file from?
2. publish the form as a Web form?
Or, does one have to rely on a bespoke ASP.NET app to do it? What are
the limits of SSRS?SSRS has its limitations.
SSRS relies greatly on how the SQL code is written.
For example, if you are using date ranges - reporting services is there
to be the middle man between presentation and data. Nothing more. All
reporting services will require is 2 parameters(start date and end
date).
Meaning SSRS will have to TEXTBOX's where the user will just enter 2
dates and it will run the report between the 2 dates. In this case
there is no validation of the paramters but i can all be handleled on
the DB side. So reporting services doesnt have built in controls where
the user will enter dates, its only there to GET parameters. These
parameters are provided in a text box.
SSRS is not a web application where the user has easy to read UI to
enter paramters. It does have a VERY nice way of displaying the
results. Its buily to display a result of a DB call, not to present the
user with a web form.
regards,
Stas K.|||Thanks, Sorcerdon. I understood what you're saying. Now let me present
a broader picture of what I'm thinking might just be possible.
I have to build a bespoke ASP.NET app, which mostly requires to use
SSRS as a backend for reporting. So, in effect, this bespoke Web app is
just to provide UI forms and call SSRS with parameters.
I was considering if it might be possible to use any of the SQL Server
2005 services (Integration, Application, Reporting, Anything Else) to
get the UI functionality also as it is, out-of-the-box.
After all, what I'll be building a bespoke application is to get a UI
to call my reports. Surely, Microsoft, with its DSI and software
factories initiative might have as well provided with some tool to
automate that as well out-of-the-box.
Thoughts?|||Actually, in SSRS 2005, there is a better implementation of the Date type
parameters, with a calendar control and input validation controls.
Thiago
--
Regards,
"Sorcerdon" wrote:
> SSRS has its limitations.
> SSRS relies greatly on how the SQL code is written.
> For example, if you are using date ranges - reporting services is there
> to be the middle man between presentation and data. Nothing more. All
> reporting services will require is 2 parameters(start date and end
> date).
> Meaning SSRS will have to TEXTBOX's where the user will just enter 2
> dates and it will run the report between the 2 dates. In this case
> there is no validation of the paramters but i can all be handleled on
> the DB side. So reporting services doesnt have built in controls where
> the user will enter dates, its only there to GET parameters. These
> parameters are provided in a text box.
> SSRS is not a web application where the user has easy to read UI to
> enter paramters. It does have a VERY nice way of displaying the
> results. Its buily to display a result of a DB call, not to present the
> user with a web form.
> regards,
> Stas K.
>|||I was solely speaking of Reporting Services 2000. My bad.
There is a UI for reporting that is built in for reporting
services(report manager). People can log in subsrcibe to report and get
it in their mail every morning. All of that is built in. And they can
run the reports at will.
The problem is that in RS2000 (I have not yet started working with 2005
until very recently) it wasnt as nice of a UI. So, perhaps a RS 2005
PRO can answer this?
As for out-of-the-box - there is always report manager. The users input
their parameters - or they are defualted to defualt parameters. It DOES
have it built in, yes!
Here are some screenies of what the report manager looks like:
http://www.windowsitpro.com/Files/09/40529/Figure_07.gif
http://msdn.microsoft.com/library/en-us/dnhcvs04/html/408dobson5.jpg
http://www.codeproject.com/books/MSReportingServices/img005.jpg
In that last screenie you can see how the parameters are inputed - in
those text boxes.
regards,
Stas K.|||Hi,
Ofcourse RS is meant for reporting and not giving some fancy UI. But yes in
2005 they have provided what is called "Report Viewer" (comes with VS2005)
which you can use it in your Asp.Net along with your UI. Want to explore ?
search for "ReportViewer Controls (Visual Studio)" in VS 2005 documentation.
Amarnath
"Water Cooler v2" wrote:
> Thanks, Sorcerdon. I understood what you're saying. Now let me present
> a broader picture of what I'm thinking might just be possible.
> I have to build a bespoke ASP.NET app, which mostly requires to use
> SSRS as a backend for reporting. So, in effect, this bespoke Web app is
> just to provide UI forms and call SSRS with parameters.
> I was considering if it might be possible to use any of the SQL Server
> 2005 services (Integration, Application, Reporting, Anything Else) to
> get the UI functionality also as it is, out-of-the-box.
> After all, what I'll be building a bespoke application is to get a UI
> to call my reports. Surely, Microsoft, with its DSI and software
> factories initiative might have as well provided with some tool to
> automate that as well out-of-the-box.
> Thoughts?
>|||Thanks everyone.
Sorcerdon and Amarnath,
Where do I find these "Report Manager" and "Report Viewer"? I cannot
see "ReportViewer Controls" in my toolbox. Do I have to import them (as
in set a reference to some assembly containing them)?
Or, is it because I do not have the complete Visual Studio 2005
Enterprise Architect Edition. I only have SQL Server 2005 Business
Intelligence Development Studio (which is basically a stripped down
version of VS .NET 2005 with only data-centric project templates).|||This is so wrong on so many levels.
I don't usually say this sort of thing but please until you learn more about
the product DO NOT jump in and answer questions.
Let's start with the most glaring problem. You state that the date would be
text with no validation. In RS 2000 there was not a date picker. In RS 2005
there is a date picker. BUT, even assuming you are on RS 2000 you are still
totally wrong. If you go to the menu Report->Report Parameters you can set
the data type for each parameter. So, even in RS 2000 there is validation
for a date field (or an integer field etc), they cannot put in a non-date if
the field should be a date field.
To say RS is not a web app that the user can enter parameters in easily is
also so totally wrong. Yes, some people prefer to have more control over
placement of controls and other things but RS comes with a totally usable
portal called Report Manager. You can have the parameters be based on a
query (so you that pick from a dropdown list). You can have cascading
parameters where the second parameter list is based on the previous
parameter. You can set defaults for the parameters, etc etc. Each report you
can place a description with it so when the user sees the list of reports
each report has a brief description. In RS 2005 you can have multi-select
parameter lists.
So in short, your answer has set this guy off on a totally wrong tangent. It
could be that the existing portal that ships with RS would fullfill all his
needs.
I know you were trying to be helpfull but you are lacking in knowledge about
the product.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1144686176.448071.239430@.i40g2000cwc.googlegroups.com...
> SSRS has its limitations.
> SSRS relies greatly on how the SQL code is written.
> For example, if you are using date ranges - reporting services is there
> to be the middle man between presentation and data. Nothing more. All
> reporting services will require is 2 parameters(start date and end
> date).
> Meaning SSRS will have to TEXTBOX's where the user will just enter 2
> dates and it will run the report between the 2 dates. In this case
> there is no validation of the paramters but i can all be handleled on
> the DB side. So reporting services doesnt have built in controls where
> the user will enter dates, its only there to GET parameters. These
> parameters are provided in a text box.
> SSRS is not a web application where the user has easy to read UI to
> enter paramters. It does have a VERY nice way of displaying the
> results. Its buily to display a result of a DB call, not to present the
> user with a web form.
> regards,
> Stas K.
>|||Hey Bruce,
Thanks for that RS2000 validation control re-answer. I seem to have
never known it existed - perhaps because I am running all my reports of
a web app.
Same for the second answer.
regards,
Stas K.|||I have noticed other posts of yours after my harsh response here and you
have good answers in other areas. I got a little excitable on my post.
You obviously do know other areas of the product, just not Report Manager.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1144787538.172402.57350@.g10g2000cwb.googlegroups.com...
> Hey Bruce,
> Thanks for that RS2000 validation control re-answer. I seem to have
> never known it existed - perhaps because I am running all my reports of
> a web app.
> Same for the second answer.
> regards,
> Stas K.
>|||Bruce,
I am actually a software developer not a report writer although I do
both.
So I really should go answer questions in other forums. In these forums
I hope to help myself learn and perfect my technique when writing
reports.
Although some questions here come from N-Users... So I am able to think
of solutions.
As for Report Manager, in this case I am the N-user ^_^ - I dont like
how it looks as a matter of fact. I want a whole new look for the whole
thing. I like a sharepoint type of look(if your familiar).
regards,
Stas K.
Thursday, March 22, 2012
can use microsoft .net 2002
Hi everybody,
I want to know if I can install reporting services using microsoft .Net
version 2002...because I read the requirements and it said microsoft .net
2003...but I have .net version 2002...Do you think that I can use version 200?
cheers,No, you cannot. You need any version of VS 2003 (VB.Net is the cheapest at
about $100).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kellyc" <Kellyc@.discussions.microsoft.com> wrote in message
news:E4BD0C49-9655-453A-87F0-632E2345C360@.microsoft.com...
> Hi everybody,
> I want to know if I can install reporting services using microsoft .Net
> version 2002...because I read the requirements and it said microsoft .net
> 2003...but I have .net version 2002...Do you think that I can use version
200?
> cheers,
I want to know if I can install reporting services using microsoft .Net
version 2002...because I read the requirements and it said microsoft .net
2003...but I have .net version 2002...Do you think that I can use version 200?
cheers,No, you cannot. You need any version of VS 2003 (VB.Net is the cheapest at
about $100).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kellyc" <Kellyc@.discussions.microsoft.com> wrote in message
news:E4BD0C49-9655-453A-87F0-632E2345C360@.microsoft.com...
> Hi everybody,
> I want to know if I can install reporting services using microsoft .Net
> version 2002...because I read the requirements and it said microsoft .net
> 2003...but I have .net version 2002...Do you think that I can use version
200?
> cheers,
can u please tell me the difference...
i downloaded an rdbms s/w from http://www.vaman.net/vmndataserver.htm
these people claimed that this particular s/w has integrated data server, mail server & web server in2 one.
also they claim that they have a data migration tool which can migrate data from any db to vaman & vice versa.
i tried this data migration tool, & to my amazement , its working just fine.
but somehow, i didn't understand their first claim of having an integrated data server, web server & mail server in2 1. i mean what's the use 4 that?
on the top of it, there enterprise manager is a true copy of MS SQL Server. is it allowed?
isn't there anyone who can stop this?
this is sheer copying of a software.
it is like a copying the source code of a web site & building a new web site by giving a few cosmetic changes.
can u please explain it 2 me.Don't worry dude they have a data migration tool and according to you it is working just fine..... good for you ... why r u concerned about their claims...... if for academic reasons or for knowledge sake u wish to know y they are offering mail, data and web server all in one ...then the reason behind that may be that since nobody such a product ... they may be promoting it as their USP.... probably they cannot compete in the database market with the likes of IBM, Microsoft and oracle without offering a bundle of a product..... the buyer need not go for a separate web or mail server that is what they must be saying...... mean while if their software is working properly take full advantage of it without worrying.
Originally posted by manishbafna80
i downloaded an rdbms s/w from http://www.vaman.net/vmndataserver.htm
these people claimed that this particular s/w has integrated data server, mail server & web server in2 one.
also they claim that they have a data migration tool which can migrate data from any db to vaman & vice versa.
i tried this data migration tool, & to my amazement , its working just fine.
but somehow, i didn't understand their first claim of having an integrated data server, web server & mail server in2 1. i mean what's the use 4 that?
on the top of it, there enterprise manager is a true copy of MS SQL Server. is it allowed?
isn't there anyone who can stop this?
this is sheer copying of a software.
it is like a copying the source code of a web site & building a new web site by giving a few cosmetic changes.
can u please explain it 2 me.
these people claimed that this particular s/w has integrated data server, mail server & web server in2 one.
also they claim that they have a data migration tool which can migrate data from any db to vaman & vice versa.
i tried this data migration tool, & to my amazement , its working just fine.
but somehow, i didn't understand their first claim of having an integrated data server, web server & mail server in2 1. i mean what's the use 4 that?
on the top of it, there enterprise manager is a true copy of MS SQL Server. is it allowed?
isn't there anyone who can stop this?
this is sheer copying of a software.
it is like a copying the source code of a web site & building a new web site by giving a few cosmetic changes.
can u please explain it 2 me.Don't worry dude they have a data migration tool and according to you it is working just fine..... good for you ... why r u concerned about their claims...... if for academic reasons or for knowledge sake u wish to know y they are offering mail, data and web server all in one ...then the reason behind that may be that since nobody such a product ... they may be promoting it as their USP.... probably they cannot compete in the database market with the likes of IBM, Microsoft and oracle without offering a bundle of a product..... the buyer need not go for a separate web or mail server that is what they must be saying...... mean while if their software is working properly take full advantage of it without worrying.
Originally posted by manishbafna80
i downloaded an rdbms s/w from http://www.vaman.net/vmndataserver.htm
these people claimed that this particular s/w has integrated data server, mail server & web server in2 one.
also they claim that they have a data migration tool which can migrate data from any db to vaman & vice versa.
i tried this data migration tool, & to my amazement , its working just fine.
but somehow, i didn't understand their first claim of having an integrated data server, web server & mail server in2 1. i mean what's the use 4 that?
on the top of it, there enterprise manager is a true copy of MS SQL Server. is it allowed?
isn't there anyone who can stop this?
this is sheer copying of a software.
it is like a copying the source code of a web site & building a new web site by giving a few cosmetic changes.
can u please explain it 2 me.
Tuesday, March 20, 2012
Can this Stored Proc be more efficient?
Hey all,
Figured I'd get everyone's input on this. The stored proc below works
fine, no errors, however the ASP.NET page which calls it takes forever
to load (it averages 25 seconds per search). Anyone have any insight on
how I can boost the speed? 25-35 seconds is too dang long. For those
who want a full perspective: I have a sortable datagrid with custom
paging. On the site there is a textbox where one can search by
"containernum." As you can see below, the core of the search uses LIKE
'%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
full-text index on the containernum field but perhaps I did it wrong
(it's the first time I've used that feature) because there appears to
be no gain in speed. Help! :-)
BTW, there are about a million records in my test table, the real table
has almost 5 million records, so I can just imagine the slowdown on
that one. :-(
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.SortField nvarchar(50),
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
uid uniqueidentifier NOT NULL,
event nvarchar(6) NOT NULL,
bookingnum nvarchar(50) NOT NULL,
vanowner nvarchar(6) NOT NULL,
containernum nvarchar(8) NULL,
tcn nvarchar(20) NULL,
poe nvarchar(50) NULL,
pod nvarchar(6) NULL,
shipname nvarchar(50) NULL,
vdn nvarchar(8) NULL,
eventlocation nvarchar(50) NOT NULL,
pcfn nvarchar(8) NULL
)
INSERT INTO #TempTable
(
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
dbo.new315_itv
WHERE
containernum LIKE '%'+@.con_num+'%'
ORDER BY
CASE
WHEN @.SortField = 'event' THEN event
WHEN @.SortField = 'bookingnum' THEN bookingnum
WHEN @.SortField = 'containernum' THEN containernum
WHEN @.SortField = 'tcn' THEN tcn
WHEN @.SortField = 'poe' THEN poe
WHEN @.SortField = 'pod' THEN pod
WHEN @.SortField = 'shipname' THEN shipname
WHEN @.SortField = 'vdn' THEN vdn
WHEN @.SortField = 'eventlocation' THEN eventlocation
WHEN @.SortField = 'pcfn' THEN pcfn
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
#TempTable
WHERE
ID > @.FirstRec AND ID < @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
GOANytime you use Like with a % at the beginning of the value, as in
containernum LIKE '%'+@.con_num+'%'
you automatically induce a full table scan. this is why your query is so
slow.
You need to extract the @.con_num portion of the data into a separate column
and index that... change the query so that it uses = instead of like, or at
least so that there is no % at the beginning...
Also the idea you are using of creating a temp table of all the values, and
then extracting only one pages wrth to return over the wire is a good one,
but you cancarry this a step furthur... Instead of using a temp table, use a
table variable, with an identity Primary Key RowNum, and ONLY put the keys
into this table variable... (You are incurring an enormous amount of
overhead right now stuffing ALL the data into the temp table, not just the
data you will eventually return to client)
Declare @.T Table (RowNum Integer Primary Key Identity Not Null,
PK Integer Not Null)
Insert @.T
Select uid
From ....
Then, at the end just use this table variable t o join back to your main
table, based on which StartRownNum and EndRowwNum defines the page you want
SELECT uid,event,bookingnum,
vanowner,containernum,tcn,
poe,pod,shipname,
vdn,eventlocation,pcfn
FROM new315_itv O Join @.T
On T.RowNum = O.uid
WHERE RowNum Between @.FirstRec AND @.LastRec
"roy.anderson@.gmail.com" wrote:
> Hey all,
> Figured I'd get everyone's input on this. The stored proc below works
> fine, no errors, however the ASP.NET page which calls it takes forever
> to load (it averages 25 seconds per search). Anyone have any insight on
> how I can boost the speed? 25-35 seconds is too dang long. For those
> who want a full perspective: I have a sortable datagrid with custom
> paging. On the site there is a textbox where one can search by
> "containernum." As you can see below, the core of the search uses LIKE
> '%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
> full-text index on the containernum field but perhaps I did it wrong
> (it's the first time I've used that feature) because there appears to
> be no gain in speed. Help! :-)
> BTW, there are about a million records in my test table, the real table
> has almost 5 million records, so I can just imagine the slowdown on
> that one. :-(
>
> CREATE PROCEDURE [Get_Data]
> @.CurrentPage int,
> @.PageSize int,
> @.SortField nvarchar(50),
> @.TotalRecords int output,
> @.con_num nvarchar(8)
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> uid uniqueidentifier NOT NULL,
> event nvarchar(6) NOT NULL,
> bookingnum nvarchar(50) NOT NULL,
> vanowner nvarchar(6) NOT NULL,
> containernum nvarchar(8) NULL,
> tcn nvarchar(20) NULL,
> poe nvarchar(50) NULL,
> pod nvarchar(6) NULL,
> shipname nvarchar(50) NULL,
> vdn nvarchar(8) NULL,
> eventlocation nvarchar(50) NOT NULL,
> pcfn nvarchar(8) NULL
> )
> INSERT INTO #TempTable
> (
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> )
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> dbo.new315_itv
> WHERE
> containernum LIKE '%'+@.con_num+'%'
> ORDER BY
> CASE
> WHEN @.SortField = 'event' THEN event
> WHEN @.SortField = 'bookingnum' THEN bookingnum
> WHEN @.SortField = 'containernum' THEN containernum
> WHEN @.SortField = 'tcn' THEN tcn
> WHEN @.SortField = 'poe' THEN poe
> WHEN @.SortField = 'pod' THEN pod
> WHEN @.SortField = 'shipname' THEN shipname
> WHEN @.SortField = 'vdn' THEN vdn
> WHEN @.SortField = 'eventlocation' THEN eventlocation
> WHEN @.SortField = 'pcfn' THEN pcfn
> END
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
> SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> #TempTable
> WHERE
> ID > @.FirstRec AND ID < @.LastRec
> SELECT @.TotalRecords = COUNT(*) FROM #TempTable
> GO
>|||Hey CB,
Thanks for the terrific knowledge. I learned something new today! :-)
Having said that... while using a table variable has increased the
performance, it's only saved me 3 or 4 seconds on average. Here's the
weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
slow down the search, but it doesn't. In fact, the opposite occurs!
When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
seconds.
I'm clueless as to why this is occuring. :-( The only thing I can
come up with is that the "containernum" field is a nvarchar and
includes a mishmash of char's and integers, which may be distorting the
scans somehow.|||This is an indication that your quuery optimizer has decided NOT to use the
index on containernum, and is still doing table scan... (There IS an index o
n
containernum , right ?)
This can happen when there are a large number of records which are a "match"
for the criteria you are passing in... If this query the only query you are
running on this table than you might cnsider mking the index on containernum
the clustered index.. That would improve perfoemce when using ...
containernum Where Like @.con_Num + '%'
What xactly is containernum, and what kind of values are stored in there?
"roy.anderson@.gmail.com" wrote:
> Hey CB,
> Thanks for the terrific knowledge. I learned something new today! :-)
> Having said that... while using a table variable has increased the
> performance, it's only saved me 3 or 4 seconds on average. Here's the
> weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
> slow down the search, but it doesn't. In fact, the opposite occurs!
> When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
> 27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
> seconds.
> I'm clueless as to why this is occuring. :-( The only thing I can
> come up with is that the "containernum" field is a nvarchar and
> includes a mishmash of char's and integers, which may be distorting the
> scans somehow.
>|||"containernum" is an nvarchar(8) field and contains various
alphanumeric characters. The length of entries in that field varies
from 1 to 8. I did have an clustered index on "containernum"
originally, but I was getting slow results and one of my coworkers
suggested enabled a Full-Text Index using "containernum." When I did
that it deleted the original "containernum" clustered index and
replaced it with a "UID" clustered index (UID is the PK for table
new315_itv). I'm currently using the full-text index. Should I delete
it and switch back to using the containernum clustered index?
Am I making sense? :-)|||<roy.anderson@.gmail.com> wrote in message
news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> "containernum" is an nvarchar(8) field and contains various
> alphanumeric characters. The length of entries in that field varies
> from 1 to 8. I did have an clustered index on "containernum"
> originally, but I was getting slow results and one of my coworkers
> suggested enabled a Full-Text Index using "containernum." When I did
> that it deleted the original "containernum" clustered index and
> replaced it with a "UID" clustered index (UID is the PK for table
> new315_itv). I'm currently using the full-text index. Should I delete
> it and switch back to using the containernum clustered index?
> Am I making sense? :-)
>
I think CBretana was suggesting that you reconsider you table design.
Specifically, the containernum column seems to contain composite data; the
container number plus whatever the alphabetic data represents. If these
pieces of data were separated out into discreet columns, the query analyzer
could take advantage of the index on the container_number column. In the
alternative, if you are unable to alter the table model, you might consider
creating an indexed view on the new315_itv table that includes a calculated
expression to extract the actual container number from the containernum
column. Here's a proof of concept on how to extract a number from an
alphanumeric string:
DECLARE @.s NVARCHAR(8)
SET @.s = 'abc123de'
SELECT SUBSTRING(
@.s,
PATINDEX('%[0-9]%',@.s),
CASE PATINDEX('%[0-9]',@.s)
WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
END
)
Also, have you considered using VARCHAR instead of NVARCHAR for your textual
data. NVARCHAR requires twice the storage of VARCHAR an should only be used
if your textual data includes Unicode characters. Here's an article:
http://aspfaq.com/show.asp?id=2354
Finally, here's an article that compares various methods of paging through a
recordset. It includes an example of a stored procedure that makes use of a
temp table as well as other stored procedure examples with better
performance.
http://aspfaq.com/show.asp?id=2120
HTH
-Chris Hohmann|||I agree w/Chris... If you can, Redesign the table so that each discreet data
element is in it's own column... But yes, you should switch back to using
Clustered index on the column that the query predicate (Whats in the Where
Clause) uses... ESPECIALLY If the query extracts a range of values.
And that is what ...
Where X Like @.Value + '%' will be doing, since it translates to Where X >=
@.Value And <= @.Value + 'ZZZZZZZZZZZZZZZZZZZZZZZZ' (actually whatever the
Query Parser determines is the last possible value in sort order that will
satisfy the Like.)
"Chris Hohmann" wrote:
> <roy.anderson@.gmail.com> wrote in message
> news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> I think CBretana was suggesting that you reconsider you table design.
> Specifically, the containernum column seems to contain composite data; the
> container number plus whatever the alphabetic data represents. If these
> pieces of data were separated out into discreet columns, the query analyze
r
> could take advantage of the index on the container_number column. In the
> alternative, if you are unable to alter the table model, you might conside
r
> creating an indexed view on the new315_itv table that includes a calculate
d
> expression to extract the actual container number from the containernum
> column. Here's a proof of concept on how to extract a number from an
> alphanumeric string:
> DECLARE @.s NVARCHAR(8)
> SET @.s = 'abc123de'
> SELECT SUBSTRING(
> @.s,
> PATINDEX('%[0-9]%',@.s),
> CASE PATINDEX('%[0-9]',@.s)
> WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> END
> )
> Also, have you considered using VARCHAR instead of NVARCHAR for your textu
al
> data. NVARCHAR requires twice the storage of VARCHAR an should only be use
d
> if your textual data includes Unicode characters. Here's an article:
> http://aspfaq.com/show.asp?id=2354
> Finally, here's an article that compares various methods of paging through
a
> recordset. It includes an example of a stored procedure that makes use of
a
> temp table as well as other stored procedure examples with better
> performance.
> http://aspfaq.com/show.asp?id=2120
>
> HTH
> -Chris Hohmann
>
>|||Thanks for the info you two. I'll be sure to check out those articles
Chris.
Actually, believe it or not, I have the Stored Proc at a comfortable
spot now. When a user searches using at least 3 characters, the results
return in under a second, when searches occur with less than 3
characters, the results can take up to 35 seconds to return (but this
warning is now noted on the site). See my new stored proc below. I
utilized CB's table variable idea but maintained the full-text index.
Basically (I believe) if one uses LIKE sqlserver looks for a regular
index, if one uses CONTAINS or FREETEXT sqlserver looks for a full-text
index. However, for whatever reason, I can't use CONTAINS on character
searches that contain less than 3 characters. It doesn't error out, it
just doesn't display anything. Probably an idiosyncrasy of full-text
searches.
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
DECLARE @.T Table
(
RowNum INTEGER PRIMARY KEY Identity NOT NULL,
PK UNIQUEIDENTIFIER NOT NULL
)
IF LEN(@.con_num) >= 3
BEGIN
SET @.con_num = '"'+@.con_num+'*"'
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE CONTAINS (containernum, @.con_num)
END
ELSE
BEGIN
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE containernum LIKE '%'+@.con_num+'%'
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
A.uid,
A.event,
A.bookingnum,
A.vanowner,
A.containernum,
A.tcn,
A.poe,
A.pod,
A.shipname,
A.vdn,
A.eventlocation,
A.pcfn
FROM
dbo.new315_itv A INNER JOIN @.T T ON T.PK = A.uid
WHERE
T.RowNum BETWEEN @.FirstRec AND @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM @.T
GO
Figured I'd get everyone's input on this. The stored proc below works
fine, no errors, however the ASP.NET page which calls it takes forever
to load (it averages 25 seconds per search). Anyone have any insight on
how I can boost the speed? 25-35 seconds is too dang long. For those
who want a full perspective: I have a sortable datagrid with custom
paging. On the site there is a textbox where one can search by
"containernum." As you can see below, the core of the search uses LIKE
'%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
full-text index on the containernum field but perhaps I did it wrong
(it's the first time I've used that feature) because there appears to
be no gain in speed. Help! :-)
BTW, there are about a million records in my test table, the real table
has almost 5 million records, so I can just imagine the slowdown on
that one. :-(
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.SortField nvarchar(50),
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
uid uniqueidentifier NOT NULL,
event nvarchar(6) NOT NULL,
bookingnum nvarchar(50) NOT NULL,
vanowner nvarchar(6) NOT NULL,
containernum nvarchar(8) NULL,
tcn nvarchar(20) NULL,
poe nvarchar(50) NULL,
pod nvarchar(6) NULL,
shipname nvarchar(50) NULL,
vdn nvarchar(8) NULL,
eventlocation nvarchar(50) NOT NULL,
pcfn nvarchar(8) NULL
)
INSERT INTO #TempTable
(
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
dbo.new315_itv
WHERE
containernum LIKE '%'+@.con_num+'%'
ORDER BY
CASE
WHEN @.SortField = 'event' THEN event
WHEN @.SortField = 'bookingnum' THEN bookingnum
WHEN @.SortField = 'containernum' THEN containernum
WHEN @.SortField = 'tcn' THEN tcn
WHEN @.SortField = 'poe' THEN poe
WHEN @.SortField = 'pod' THEN pod
WHEN @.SortField = 'shipname' THEN shipname
WHEN @.SortField = 'vdn' THEN vdn
WHEN @.SortField = 'eventlocation' THEN eventlocation
WHEN @.SortField = 'pcfn' THEN pcfn
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
#TempTable
WHERE
ID > @.FirstRec AND ID < @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
GOANytime you use Like with a % at the beginning of the value, as in
containernum LIKE '%'+@.con_num+'%'
you automatically induce a full table scan. this is why your query is so
slow.
You need to extract the @.con_num portion of the data into a separate column
and index that... change the query so that it uses = instead of like, or at
least so that there is no % at the beginning...
Also the idea you are using of creating a temp table of all the values, and
then extracting only one pages wrth to return over the wire is a good one,
but you cancarry this a step furthur... Instead of using a temp table, use a
table variable, with an identity Primary Key RowNum, and ONLY put the keys
into this table variable... (You are incurring an enormous amount of
overhead right now stuffing ALL the data into the temp table, not just the
data you will eventually return to client)
Declare @.T Table (RowNum Integer Primary Key Identity Not Null,
PK Integer Not Null)
Insert @.T
Select uid
From ....
Then, at the end just use this table variable t o join back to your main
table, based on which StartRownNum and EndRowwNum defines the page you want
SELECT uid,event,bookingnum,
vanowner,containernum,tcn,
poe,pod,shipname,
vdn,eventlocation,pcfn
FROM new315_itv O Join @.T
On T.RowNum = O.uid
WHERE RowNum Between @.FirstRec AND @.LastRec
"roy.anderson@.gmail.com" wrote:
> Hey all,
> Figured I'd get everyone's input on this. The stored proc below works
> fine, no errors, however the ASP.NET page which calls it takes forever
> to load (it averages 25 seconds per search). Anyone have any insight on
> how I can boost the speed? 25-35 seconds is too dang long. For those
> who want a full perspective: I have a sortable datagrid with custom
> paging. On the site there is a textbox where one can search by
> "containernum." As you can see below, the core of the search uses LIKE
> '%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
> full-text index on the containernum field but perhaps I did it wrong
> (it's the first time I've used that feature) because there appears to
> be no gain in speed. Help! :-)
> BTW, there are about a million records in my test table, the real table
> has almost 5 million records, so I can just imagine the slowdown on
> that one. :-(
>
> CREATE PROCEDURE [Get_Data]
> @.CurrentPage int,
> @.PageSize int,
> @.SortField nvarchar(50),
> @.TotalRecords int output,
> @.con_num nvarchar(8)
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> uid uniqueidentifier NOT NULL,
> event nvarchar(6) NOT NULL,
> bookingnum nvarchar(50) NOT NULL,
> vanowner nvarchar(6) NOT NULL,
> containernum nvarchar(8) NULL,
> tcn nvarchar(20) NULL,
> poe nvarchar(50) NULL,
> pod nvarchar(6) NULL,
> shipname nvarchar(50) NULL,
> vdn nvarchar(8) NULL,
> eventlocation nvarchar(50) NOT NULL,
> pcfn nvarchar(8) NULL
> )
> INSERT INTO #TempTable
> (
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> )
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> dbo.new315_itv
> WHERE
> containernum LIKE '%'+@.con_num+'%'
> ORDER BY
> CASE
> WHEN @.SortField = 'event' THEN event
> WHEN @.SortField = 'bookingnum' THEN bookingnum
> WHEN @.SortField = 'containernum' THEN containernum
> WHEN @.SortField = 'tcn' THEN tcn
> WHEN @.SortField = 'poe' THEN poe
> WHEN @.SortField = 'pod' THEN pod
> WHEN @.SortField = 'shipname' THEN shipname
> WHEN @.SortField = 'vdn' THEN vdn
> WHEN @.SortField = 'eventlocation' THEN eventlocation
> WHEN @.SortField = 'pcfn' THEN pcfn
> END
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
> SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> #TempTable
> WHERE
> ID > @.FirstRec AND ID < @.LastRec
> SELECT @.TotalRecords = COUNT(*) FROM #TempTable
> GO
>|||Hey CB,
Thanks for the terrific knowledge. I learned something new today! :-)
Having said that... while using a table variable has increased the
performance, it's only saved me 3 or 4 seconds on average. Here's the
weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
slow down the search, but it doesn't. In fact, the opposite occurs!
When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
seconds.
I'm clueless as to why this is occuring. :-( The only thing I can
come up with is that the "containernum" field is a nvarchar and
includes a mishmash of char's and integers, which may be distorting the
scans somehow.|||This is an indication that your quuery optimizer has decided NOT to use the
index on containernum, and is still doing table scan... (There IS an index o
n
containernum , right ?)
This can happen when there are a large number of records which are a "match"
for the criteria you are passing in... If this query the only query you are
running on this table than you might cnsider mking the index on containernum
the clustered index.. That would improve perfoemce when using ...
containernum Where Like @.con_Num + '%'
What xactly is containernum, and what kind of values are stored in there?
"roy.anderson@.gmail.com" wrote:
> Hey CB,
> Thanks for the terrific knowledge. I learned something new today! :-)
> Having said that... while using a table variable has increased the
> performance, it's only saved me 3 or 4 seconds on average. Here's the
> weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
> slow down the search, but it doesn't. In fact, the opposite occurs!
> When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
> 27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
> seconds.
> I'm clueless as to why this is occuring. :-( The only thing I can
> come up with is that the "containernum" field is a nvarchar and
> includes a mishmash of char's and integers, which may be distorting the
> scans somehow.
>|||"containernum" is an nvarchar(8) field and contains various
alphanumeric characters. The length of entries in that field varies
from 1 to 8. I did have an clustered index on "containernum"
originally, but I was getting slow results and one of my coworkers
suggested enabled a Full-Text Index using "containernum." When I did
that it deleted the original "containernum" clustered index and
replaced it with a "UID" clustered index (UID is the PK for table
new315_itv). I'm currently using the full-text index. Should I delete
it and switch back to using the containernum clustered index?
Am I making sense? :-)|||<roy.anderson@.gmail.com> wrote in message
news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> "containernum" is an nvarchar(8) field and contains various
> alphanumeric characters. The length of entries in that field varies
> from 1 to 8. I did have an clustered index on "containernum"
> originally, but I was getting slow results and one of my coworkers
> suggested enabled a Full-Text Index using "containernum." When I did
> that it deleted the original "containernum" clustered index and
> replaced it with a "UID" clustered index (UID is the PK for table
> new315_itv). I'm currently using the full-text index. Should I delete
> it and switch back to using the containernum clustered index?
> Am I making sense? :-)
>
I think CBretana was suggesting that you reconsider you table design.
Specifically, the containernum column seems to contain composite data; the
container number plus whatever the alphabetic data represents. If these
pieces of data were separated out into discreet columns, the query analyzer
could take advantage of the index on the container_number column. In the
alternative, if you are unable to alter the table model, you might consider
creating an indexed view on the new315_itv table that includes a calculated
expression to extract the actual container number from the containernum
column. Here's a proof of concept on how to extract a number from an
alphanumeric string:
DECLARE @.s NVARCHAR(8)
SET @.s = 'abc123de'
SELECT SUBSTRING(
@.s,
PATINDEX('%[0-9]%',@.s),
CASE PATINDEX('%[0-9]',@.s)
WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
END
)
Also, have you considered using VARCHAR instead of NVARCHAR for your textual
data. NVARCHAR requires twice the storage of VARCHAR an should only be used
if your textual data includes Unicode characters. Here's an article:
http://aspfaq.com/show.asp?id=2354
Finally, here's an article that compares various methods of paging through a
recordset. It includes an example of a stored procedure that makes use of a
temp table as well as other stored procedure examples with better
performance.
http://aspfaq.com/show.asp?id=2120
HTH
-Chris Hohmann|||I agree w/Chris... If you can, Redesign the table so that each discreet data
element is in it's own column... But yes, you should switch back to using
Clustered index on the column that the query predicate (Whats in the Where
Clause) uses... ESPECIALLY If the query extracts a range of values.
And that is what ...
Where X Like @.Value + '%' will be doing, since it translates to Where X >=
@.Value And <= @.Value + 'ZZZZZZZZZZZZZZZZZZZZZZZZ' (actually whatever the
Query Parser determines is the last possible value in sort order that will
satisfy the Like.)
"Chris Hohmann" wrote:
> <roy.anderson@.gmail.com> wrote in message
> news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> I think CBretana was suggesting that you reconsider you table design.
> Specifically, the containernum column seems to contain composite data; the
> container number plus whatever the alphabetic data represents. If these
> pieces of data were separated out into discreet columns, the query analyze
r
> could take advantage of the index on the container_number column. In the
> alternative, if you are unable to alter the table model, you might conside
r
> creating an indexed view on the new315_itv table that includes a calculate
d
> expression to extract the actual container number from the containernum
> column. Here's a proof of concept on how to extract a number from an
> alphanumeric string:
> DECLARE @.s NVARCHAR(8)
> SET @.s = 'abc123de'
> SELECT SUBSTRING(
> @.s,
> PATINDEX('%[0-9]%',@.s),
> CASE PATINDEX('%[0-9]',@.s)
> WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> END
> )
> Also, have you considered using VARCHAR instead of NVARCHAR for your textu
al
> data. NVARCHAR requires twice the storage of VARCHAR an should only be use
d
> if your textual data includes Unicode characters. Here's an article:
> http://aspfaq.com/show.asp?id=2354
> Finally, here's an article that compares various methods of paging through
a
> recordset. It includes an example of a stored procedure that makes use of
a
> temp table as well as other stored procedure examples with better
> performance.
> http://aspfaq.com/show.asp?id=2120
>
> HTH
> -Chris Hohmann
>
>|||Thanks for the info you two. I'll be sure to check out those articles
Chris.
Actually, believe it or not, I have the Stored Proc at a comfortable
spot now. When a user searches using at least 3 characters, the results
return in under a second, when searches occur with less than 3
characters, the results can take up to 35 seconds to return (but this
warning is now noted on the site). See my new stored proc below. I
utilized CB's table variable idea but maintained the full-text index.
Basically (I believe) if one uses LIKE sqlserver looks for a regular
index, if one uses CONTAINS or FREETEXT sqlserver looks for a full-text
index. However, for whatever reason, I can't use CONTAINS on character
searches that contain less than 3 characters. It doesn't error out, it
just doesn't display anything. Probably an idiosyncrasy of full-text
searches.
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
DECLARE @.T Table
(
RowNum INTEGER PRIMARY KEY Identity NOT NULL,
PK UNIQUEIDENTIFIER NOT NULL
)
IF LEN(@.con_num) >= 3
BEGIN
SET @.con_num = '"'+@.con_num+'*"'
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE CONTAINS (containernum, @.con_num)
END
ELSE
BEGIN
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE containernum LIKE '%'+@.con_num+'%'
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
A.uid,
A.event,
A.bookingnum,
A.vanowner,
A.containernum,
A.tcn,
A.poe,
A.pod,
A.shipname,
A.vdn,
A.eventlocation,
A.pcfn
FROM
dbo.new315_itv A INNER JOIN @.T T ON T.PK = A.uid
WHERE
T.RowNum BETWEEN @.FirstRec AND @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM @.T
GO
Sunday, March 11, 2012
Can Store Procedure Return Rows?
Can store procedure return rows?
If store procedure can return rows, how to use ASP.NET to receive it? thanks!
Yes, Stored Procedure (SP) can return rows, accept parameters for updating/inserting rows, and do other cool stuff related to manipulating your DB. Normally, SP's that return rows can replace the usual SELECT SQL statements in most of the ASP.NET data controls. The topic is a vast one, so you should google for "ASP.NET Stored Procedure" for various articles available on the net.|||Thank you for the reply!Can start new database from Web Matrix
I am running Web Matrix version 0.5, .Net version 1.1 on a computer with XP Pro.
Web Matrix works fine and returns forms properly.
I installed MSDE from the website (SQL2KDeskSP3a.exe) Ibelieve that SQL Server IS running, because I see the tower icon with agreen arrow; when I double-click that I get a message that it isrunning SQL Server.
When I click the "data" tab in Web Matrix I get the blankworkspace. Then I click the New Connection icon at the top left,which opens up a dialog box. I change "Windows Authentication" to"SQL Authentication." That opens up the Username/Passwordprompt. I am entering "sa" for the username (I AM NOT SURE IFTHAT IS CORRECT) and "**secret**" for the password (that's what Ientered in the command prompt when I setup the MSDE). Then Iclick "Create a New Database." I am asked to enter a name.
After a pause, I get an error message: "Unable to connect tothe database server. SQL Server does not exist or access denied.Connection Open (Connect ( )). OK
Do you have any ideas?
I installed the newer (version 0.6) Matrix and now I can find the database.Can SSRS use an ASP.NET dataset?
We find that quite often we have devoted a great deal of work in our asp.net
application to generate just the right dataset. Now we want to produce a
report from it. Is there any way just to pass our existing dataset to
reporting services instead of having to re-inquire, re-filter, re-join, etc?
thanks,
THI Tim,
> We find that quite often we have devoted a great deal of work in our
asp.net
> application to generate just the right dataset. Now we want to produce a
> report from it. Is there any way just to pass our existing dataset to
> reporting services instead of having to re-inquire, re-filter, re-join,
etc?
Yes, I have solved the same question.
I have implemented a custom RS DataExtension. The DataExtension use a
System.Data.Dataset as data source.
At report design time I use an XML file to load schema and sample data.
When I must cosume the report from my ASP.NET application I use
ReportingServices web service (render method) and I pass my dataset as
parameters... I pass dataset as XML String: your custom DataExtension must
be able to treat it.
The dataset must have a compatible schema with dataset you have used to
design report.
You can find a sample of DataExtension implementation in your folder
"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Extensions\FsiDataExtension".
HTH M.rkino
--
Marco Barzaghi - [MVP - MCP]
http://mvp.support.microsoft.com - http://italy.mvps.org
UGIDotNet - User Group Italiano .NET, http://www.ugidotnet.org
Read my WebLog: http://www.ugidotnet.org/436.blog|||Marco has a good solution if you must do it this way. But look at the hoops
to go through. First you have to write a data extension (although this could
be done just once). But then look at what you have to do with every report
you want to do. You have to have an xml defining the schema with some sample
data. This will definitely affect productivity in creating reports plus it
raises the skill level necessary to create reports. My feeling is that a
better direction to take is to put the logic into a stored procedure. If you
have code that ends up with a dataset in your asp application then someone
can easily look at it and create a stored procedure. I have been doing that
some when taking reports written in C. Although, when people work in
something like C, C#, VB they don't tend to solve the solution in a set
based manner so sometimes you are better off to totally rewrite the logic.
Anyway, once it is in a stored procedure then both your asp application and
RS can use it. RS supports stored procedures quite well (at least with SQL
Server, I have tried unsuccessfully with Sybase 11.x). My guess is the
effort to encapsulate the logic into a stored procedure will be no greater
than going through the hoops for a data extension. Once Yukon comes out then
this would be even easier to port your code to a stored procedure but for
now you would need to use T-SQL.
Now, in some cases you can not use a stored procedure easily in which case
Marco's solution is a very elegant one.
Bruce L-C
"Tim Seaburn" <tims@.removespamexcite.com> wrote in message
news:OIwXtK1WEHA.2852@.TK2MSFTNGP12.phx.gbl...
> We find that quite often we have devoted a great deal of work in our
asp.net
> application to generate just the right dataset. Now we want to produce a
> report from it. Is there any way just to pass our existing dataset to
> reporting services instead of having to re-inquire, re-filter, re-join,
etc?
> thanks,
> T
>
application to generate just the right dataset. Now we want to produce a
report from it. Is there any way just to pass our existing dataset to
reporting services instead of having to re-inquire, re-filter, re-join, etc?
thanks,
THI Tim,
> We find that quite often we have devoted a great deal of work in our
asp.net
> application to generate just the right dataset. Now we want to produce a
> report from it. Is there any way just to pass our existing dataset to
> reporting services instead of having to re-inquire, re-filter, re-join,
etc?
Yes, I have solved the same question.
I have implemented a custom RS DataExtension. The DataExtension use a
System.Data.Dataset as data source.
At report design time I use an XML file to load schema and sample data.
When I must cosume the report from my ASP.NET application I use
ReportingServices web service (render method) and I pass my dataset as
parameters... I pass dataset as XML String: your custom DataExtension must
be able to treat it.
The dataset must have a compatible schema with dataset you have used to
design report.
You can find a sample of DataExtension implementation in your folder
"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Extensions\FsiDataExtension".
HTH M.rkino
--
Marco Barzaghi - [MVP - MCP]
http://mvp.support.microsoft.com - http://italy.mvps.org
UGIDotNet - User Group Italiano .NET, http://www.ugidotnet.org
Read my WebLog: http://www.ugidotnet.org/436.blog|||Marco has a good solution if you must do it this way. But look at the hoops
to go through. First you have to write a data extension (although this could
be done just once). But then look at what you have to do with every report
you want to do. You have to have an xml defining the schema with some sample
data. This will definitely affect productivity in creating reports plus it
raises the skill level necessary to create reports. My feeling is that a
better direction to take is to put the logic into a stored procedure. If you
have code that ends up with a dataset in your asp application then someone
can easily look at it and create a stored procedure. I have been doing that
some when taking reports written in C. Although, when people work in
something like C, C#, VB they don't tend to solve the solution in a set
based manner so sometimes you are better off to totally rewrite the logic.
Anyway, once it is in a stored procedure then both your asp application and
RS can use it. RS supports stored procedures quite well (at least with SQL
Server, I have tried unsuccessfully with Sybase 11.x). My guess is the
effort to encapsulate the logic into a stored procedure will be no greater
than going through the hoops for a data extension. Once Yukon comes out then
this would be even easier to port your code to a stored procedure but for
now you would need to use T-SQL.
Now, in some cases you can not use a stored procedure easily in which case
Marco's solution is a very elegant one.
Bruce L-C
"Tim Seaburn" <tims@.removespamexcite.com> wrote in message
news:OIwXtK1WEHA.2852@.TK2MSFTNGP12.phx.gbl...
> We find that quite often we have devoted a great deal of work in our
asp.net
> application to generate just the right dataset. Now we want to produce a
> report from it. Is there any way just to pass our existing dataset to
> reporting services instead of having to re-inquire, re-filter, re-join,
etc?
> thanks,
> T
>
Wednesday, March 7, 2012
Can SQL break an array into one string? [Stored Procedure]
Hello, I have a question on sql stored procedures.
I have such a procedure, which returnes me rows with ID-s.
Then in my asp.net page I make from that Id-s a string like
SELECT * FROM [eai.Documents] WHERECategoryId=11 ORCategoryId=16 ORCategoryId=18.
My question is: Can I do the same in my stored procedure?
Here is it:
set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[eai.GetSubCategoriesById](@.Idint)ASdeclare @.pathvarchar(100);SELECT @.path=PathFROM [eai.FileCategories]WHERE Id = @.Id;SELECT Id, ParentCategoryId,Name, NumActiveAdsFROM [eai.FileCategories]WHERE PathLIKE @.Path +'%'ORDER BY Path
Thank you
Artashes
There is no Array in Stored procedure, but you do can it by use dynamic sql . pass stored procedure a string as 11,16,18
then
in store procedure do
exec N'select * from yourTable where id in ' + @.idlist
Hope this help
|||DavidDu thank you for answer. I found another solution.
It is herehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=475225&SiteID=1
Artashes
Saturday, February 25, 2012
Can someone show me how.....
I'm beginner in asp.net. Can someone show me how to create coding for this page..
i have to create my own login page and database. I'm using sql sever 2005 for database. can someone show me how to make connection with my login button and my database? please...i'm really need help from you all guys.
Hi
Simple User Account Login demonstration of managing user account information on a server in traditional way using ASP.NET 1.1.
If you prefer asp.net 2.0 refer toHow to implement Two basics uses for the Asp.net Login control 2.0 (login and RememberMe).
Hope this helps.
|||hi yyy8347, i will try it now. thanks yeah!Can someone see whats wrong...??
I have a SQL query in my asp.net & c# application. im trying to retrieve the data from two tables where the ID's of the tables match. once this is found i am obtaining the value associated with one of the keys. e.g. my two tables
Event EventCategoryType
Field Type Example Field Type Example
EventID Int(4) 1 CategoryID(PK)int(4) 1
CategoryID(FK)int(4) 1 Type varchar(50) Exercise
Type varchar (200) Exercise Color varchar(50) Brown
The CategoryID is a 1:n relationship. my SQL query will retrive the values where the CategoryIDs match and the Tyoe matches as well. once this is found it will apply the associated color with that categoryID (each unique category has its own Color).
my application will read all the data correctly (ive checked it with a breakpoint too and it reads all the different colors for the different ID's) but it wont display the text in the right color from the table. it will just display everything in the first color it comes across.
Im including my code if it helps. can anyone tell me where i am going wrong please?? (the procedures are called on the On_Page_Load method)
private
void Load_Events(){
///<summary>
///Loads the events added from the NewEvent from into a dataset
///and then just as with the Holidyas, the events are wriiten to
///the appropriate calendar cell by comparing the date. only the
///title and time will be displayed in the cell. other event details
///such as, Objective, owner will be shown in a dialog box syle when
///the user hovers over the event.
///</summary>
mycn =new SqlConnection(strConn);
myda =new SqlDataAdapter("SELECT * FROM Event, EventTypeCategory WHERE Event.CategoryID = EventTypeCategory.CategoryID AND Event.Type = EventTypeCategory.CategoryType", mycn);
myda.Fill(ds2, "Events");
}
privatevoid Populate_Events(object sender, System.Web.UI.WebControls.DayRenderEventArgs e)
{
///<summary>
///This procedure will read all the data from the dataset - Events and then
///write each event to the appropriate calendar cell by comparing the date of
///the EventStartDate. if an event is found, the title and time are written
///to the cell, other details are shown by hovering over the cell to bring
///up another function that will display the data in a dialogBox. once the
///event is written, the appropriate color is applied to the text.
///</summary
if (!e.Day.IsOtherMonth)
{
foreach (DataRow drin ds2.Tables[0].Rows)
{
if ((dr["EventStartDate"].ToString() != DBNull.Value.ToString()))
{
DateTime evStDate = (DateTime)dr["EventStartDate"];
string evTitle = (string)dr["Title"];
string evStTime = (string)dr["EventStartTime"];
string evEnTime = (string)dr["EventEndTime"];
string evColor = (string)dr["CategoryColor"];
if(evStDate.Equals(e.Day.Date))
{
e.Cell.Controls.Add(new LiteralControl("<br>"));
e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = evColor>"));
e.Cell.Controls.Add(new LiteralControl(evTitle + " " + evStTime + " - " + evEnTime));
}
}
}
}
else
{
e.Cell.Text = "";
}
}
e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = " + evColor + ">"));
does that solve the problem ?
|||Fantastic! works perfectly! Thanks!
Friday, February 24, 2012
Can someone helpl me write this query to create a crosstab(piv
Hi Rob,
Originally I was sending the data and using a function in VB.Net to create
the pivot table, but I'm trying to have the work done on the SQL Server
instead of transferring all that data to the ASP.Net page. I think at the
moment that it's duplicating some of the work in the query but was wondering
if someone knew of a better way to write the query.
David
"Rob Farley" wrote:
> Presumably you're going to display your results somewhere, such as in
> ReportingServices, or a web page, etc... So why not add them up there inst
ead
> if you're worried?
> Personally, I'm not too keen on the PivotTable concept of SQL. I would
> rather return all the data, and then use something at the presentation lay
er
> to turn it into a PivotTable.
>Just to be sure, could you lay out the output that you are looking for'
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
> "Rob Farley" wrote:
>|||There's no pretty way of returning a PivotTable directly from SQL. You can d
o
it through a large amount of temporary table population or creating an ugly
piece of 'dynamic' SQL. But honestly, it's much easier to do it once you've
got the data away from SQL.
If you group by all your columns and rows, so that you get only one record
back for each cell of your PivotTable, then you can easily handle that in
VB.Net. If you're worried about the amount of data passed back, you could
group by IDs instead of column/row names, and pass back separate datasets
which translate the IDs into the more human-readable form.
If you're really determined to create a stored procedure that will do it for
you, then I'm sure we can come up with something, but please, pick the
'simple' solution, which is to find a control that will display the
PivotTable for you.
Rob
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
Originally I was sending the data and using a function in VB.Net to create
the pivot table, but I'm trying to have the work done on the SQL Server
instead of transferring all that data to the ASP.Net page. I think at the
moment that it's duplicating some of the work in the query but was wondering
if someone knew of a better way to write the query.
David
"Rob Farley" wrote:
> Presumably you're going to display your results somewhere, such as in
> ReportingServices, or a web page, etc... So why not add them up there inst
ead
> if you're worried?
> Personally, I'm not too keen on the PivotTable concept of SQL. I would
> rather return all the data, and then use something at the presentation lay
er
> to turn it into a PivotTable.
>Just to be sure, could you lay out the output that you are looking for'
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
> "Rob Farley" wrote:
>|||There's no pretty way of returning a PivotTable directly from SQL. You can d
o
it through a large amount of temporary table population or creating an ugly
piece of 'dynamic' SQL. But honestly, it's much easier to do it once you've
got the data away from SQL.
If you group by all your columns and rows, so that you get only one record
back for each cell of your PivotTable, then you can easily handle that in
VB.Net. If you're worried about the amount of data passed back, you could
group by IDs instead of column/row names, and pass back separate datasets
which translate the IDs into the more human-readable form.
If you're really determined to create a stored procedure that will do it for
you, then I'm sure we can come up with something, but please, pick the
'simple' solution, which is to find a control that will display the
PivotTable for you.
Rob
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
Can someone help me with this sqlConnectionString?
Hi.
I just uploaded our website on a free hosting service, calledaspspider.net. I am having problems with the database. I uploaded the .mdf file and attached it. I think the problem lies with the sqlConnectionString, and I was hoping someone could help me it.
First off, here is a tip from the hosting developers on how to set the connection string:
http://www.aspspider.net/tips/Tip18.aspx.
It says,
"Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=YourUserId_DatabaseName"
My UserId (that I signed up with) is "RedTeamBattleship", and the database name that I uploaded is called "ASPNETDB.MDF".
Now here is what I had in TestDatabase.aspx.cs, running on the localhost. It used to work fine:
String sqlConnectionString = @."Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; // connects to the localhost
Following their pattern, I made the following connection string:
String sqlConnectionString = @."Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=RedTeamBattleship_ASPNETDB.MDF";// connects to the DB hosted on ASPSpiderUnfortunately, it did not work. I tried to Google for a solution, and someone had mentioned their problem was solved when they added "user instance=false;" so I added it. However, I didn't notice any difference.String sqlConnectionString = @."Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;user instance=false;Initial Catalog=RedTeamBattleship_ASPNETDB.MDF";// connects to the DB hosted on ASPSpider
Now every time I run the application, I get the following exception:
Server Errorin'/RedTeamBattleship' Application.
Cannot open database"RedTeamBattleship_ASPNETDB.MDF" requested by the login. The login failed.
Login failedfor user'DOTNETSPIDER3\RedTeamBattleship'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack tracefor more information about the error and where it originatedin the code.Exception Details: System.Data.SqlClient.SqlException: Cannot open database"RedTeamBattleship_ASPNETDB.MDF" requested by the login. The login failed.
Login failedfor user'DOTNETSPIDER3\RedTeamBattleship'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identifiedusing the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Cannot open database"RedTeamBattleship_ASPNETDB.MDF" requested by the login. The login failed.
Login failedfor user'DOTNETSPIDER3\RedTeamBattleship'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
In addition, I changed the one in the Web.Config file, though I'm not sure if I should have done so.
I changed it from:
<connectionStrings>
<add name="MyDbConn1" connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>
<add name="MyDbConn2" connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;"/>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
To:
<connectionStrings>
<add name="MyDbConn1" connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>
<add name="MyDbConn2" connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;"/>
<add name="ConnectionString" connectionString="Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;user instance=false;Initial Catalog=RedTeamBattleship_ASPNETDB.MDF" />
</connectionStrings>
And the application still crashes with the same way.
Can someone help me fix that problem? Thank you very much.
PieCook:
String sqlConnectionString = @."Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=RedTeamBattleship_ASPNETDB.MDF";// connects to the DB hosted on ASPSpider
Unfortunately, it did not work. I tried to Google for a solution, and someone had mentioned their problem was solved when they added "user instance=false;" so I added it. However, I didn't notice any difference.String sqlConnectionString = @."Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;user instance=false;Initial Catalog=RedTeamBattleship_ASPNETDB.MDF";// connects to the DB hosted on ASPSpider
I believe because you are using Integrated Security, what that means is you will be logging into the database under the context of the account the ASP.NET worker process is running under.
You will need to check the account that is used when a user connects to IIS, you can check this from going to IIS, select your virtual directory and right click properties and select the directory security tab. The account listed there must be added to your database logins.
|||
Thank you for replying. I did as you said I should, but I'm not sure how to proceed from there:
I opened the "Default SMTP Virtual Server Properties" window, and here is what I see under the Security tab:
Grant operator permissions to these Windows user accounts.
Operators:
And inside the listbox, I see the following items:
Administrators
COMPUTER\ASP
COMPUTER\Guest
COMPUTER\HelpAssistant
COMPUTER\ISUR_COMPUTER
COMPUTER\IWAM_COMPUTER
COMPUTER\Jim
COMPUTER\SUPPORT_388945a0
String sqlConnectionString = @."Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=RedTeamBattleship_ASPNETDB.MDF";// connects to the DB hosted on ASPSpider
String sqlConnectionString = @."Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;user instance=false;Initial Catalog=RedTeamBattleship_ASPNETDB.MDF";// connects to the DB hosted on ASPSpiderI believe because you are using Integrated Security, what that means is you will be logging into the database under the context of the account the ASP.NET worker process is running under.
You will need to check the account that is used when a user connects to IIS, you can check this fromgoing to IIS, select yourvirtual directory and right clickproperties and select the directorysecurity tab. Theaccount listed there must be added to your database logins.
So it seems I had the ASP.NET worker process (COMPUTER\ASPNET), and it was already added. Or am I looking at the wrong place?
Thank you very much for your time.
|||You are a looking at the wrong place.
Under IIS, there are web sites, and in web sites there are virtual directories, and one of these are your web site application. What you are looking at is the SMTP server and not the web site.
Do you have access to this hosting service?
|||Try it without the .MDF extension (in the connect string).
|||
Thank you all for replying.
You are a looking at the wrong place.
Under IIS, there are web sites, and in web sites there are virtual directories, and one of these are your web site application. What you are looking at is the SMTP server and not the web site.
Do you have access to this hosting service?
OK, I got it this time, so it seems I have access to this hosting service... I think. Here is a screenshot of what I got:
http://i206.photobucket.com/albums/bb211/piecook/IIS%20Web%20Site/IISWebSiteVirtualDirectory.jpg
I went to the Directory Security tab and then clicked Edit, and the account I found was IUSR_COMPUTER.
Now you said earlier that "the account listed there must be added to your database logins." Can you please tell me how to do that?
Try it without the .MDF extension (in the connect string).
I tried that, but it gave me the exact same error.
Thanks again.
|||
Try it without the .MDF extension (in the connect string).
I tried that, but it gave me the exact same error.
No problem, but you may have two errors - a security error you are trying to work past and the one I mentioned. Keep it in mind as you work thru the other problem. :)
|||
OK, I got it this time, so it seems I have access to this hosting service... I think. Here is a screenshot of what I got:
http://i206.photobucket.com/albums/bb211/piecook/IIS%20Web%20Site/IISWebSiteVirtualDirectory.jpg
I went to the Directory Security tab and then clicked Edit, and the account I found was IUSR_COMPUTER.
Now you said earlier that "the account listed there must be added to your database logins." Can you please tell me how to do that?
What database are you using? If you using SQL Server you can use Enterprise Manager/Management Studio to login. There is a node called Security and under that Logins. You need to create a login for that IUSR account and map it to the database.
|||Thanks everyone for the replies.
No problem, but you may have two errors - a security error you are trying to work past and the one I mentioned. Keep it in mind as you work thru the other problem. :)
I read elsewhere that we shouldn't have the .MDF extension, just like you said. Thanks. Now one more problem remains, and I'll be back in business ^^
What database are you using? If you using SQL Server you can useEnterprise Manager/Management Studio to login. There is a node calledSecurity and under that Logins. You need to create a login for thatIUSR account and map it to the database.
Yes, I am using SQL Server. I'll try to download SQL Server Management Studio from the link below, and I'll let you know what I did.
http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en
Thank you very much.
|||
Yes, I am using SQL Server. I'll try to download SQL Server Management Studio from the link below, and I'll let you know what I did.
If you are using SQL Server 2000 it should come with Enterprise Manager unless you are using MSDE.
SQL Server 2005 also comes with Management Studio unless you are using Express.
|||I'm using Microsoft Visual Web Developer 2005 Express Edition, and I manage the SQL Server database from the database explorer. I did download the SQL Server Management Studio Express to do what you suggested.
If you using SQL Server you can useEnterprise Manager/Management Studio to login. There is a node calledSecurity and under thatLogins. You need tocreate a login for thatIUSR account andmap it to the database.
Here are the steps I took:
* I downloaded the program, and in the Object Explorer, I went to Security -> Logins.
* I right-clicked Logins and selected New Login.
* In the General tab/page, I clicked Search -> Advanced -> Find Now.
* I clicked "IUSR_COMPUTER" followed by OK -> OK. This typed "COMPUTER\IUSR_COMPUTER" in the textbox next to Login name. I then clicked OK.
Now I wasn't sure whether this is right, but I right-clicked "COMPUTER\IUSR_COMPUTER" and selected Properties, then selected the User Mappings page/tab. There are four databases available:
- master
- model
- msdb
- tempdb
So I checked the Map checkbox next to "master." Below that, there were many checkboxes concerning the "Database role membership for: master." By default, public was the only one checked, but I checked them all, as shown in this screenshot:
http://i206.photobucket.com/albums/bb211/piecook/IIS%20Web%20Site/SQLServerLoginProperties.jpg
Can you tell me the next step?
Again, your help and patience is greatly appreciated. Thank you.
Is your database not listed there? the 4 databases you mentioned are all system databases.
You are meant to be doing this for your database, so you need to be connecting to the database server. Is the database server on the hosted web server as well?
Once you have added the appropriate account to the login list, you should be able to connect to the database.
This use may be'DOTNETSPIDER3\RedTeamBattleship'or the IUSRaccount depending on how your IIS is configured, so try add both to the logins and do a process of elimination.
|||
You are meant to be doing this for your database, so you need to be connecting to the database server. Is the database server on the hosted web server as well?
Sorry, I'm not quite sure what you mean. I followed the instructions in [ http://www.aspspider.com/tips/Tip16.aspx ] on how to attach the database file. I hope this means my database server is on the hosted web server as well.
Once you have added the appropriate account to the login list, you should be able to connect to the database.
I think I already added the IUSR_COMPUTER account (by clicking search and letting it find the file). But how can I findDOTNETSPIDER3\RedTeamBattleship? It's not on the local system. Sorry if this is a beginner question.
Sorry, I'm not quite sure what you mean. I followed the instructions in [ http://www.aspspider.com/tips/Tip16.aspx ] on how to attach the database file. I hope this means my database server is on the hosted web server as well.
What I am trying to say is you have to be doing all this on your database server. when you open Managemet Studio you should be connecting to your SQL Server Instance. This is where your database lives, and it is on this instance where you have to add the appropriate user logins.
You mention that you only see the 4 databases you mentioned before, that indicates that you are not logging onto the correct SQL instance as you should be able to see you database in that list.
|||For some reason, I went to bed, and when I woke up I realized that the website was working... (?)
I also did not connect to the server database; I only followed your instructions until post #10, and then the database worked. I'm pretty sure I made no changes to the Web.Config file (although I was tempted to). But, it seems that your instructions did help somehow; I'm just not sure which specific post did it.
Thank you very much for spending the time and helping me out.
Cheers.
Can someone explain why this stored proc does not work?
Sorry if this angers anyone. I'm posting here and to the .NET group. I
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
@.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).Value
How about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegr oups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
|||Hi
"jbonifacejr" wrote:
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
@.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).Value
How about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegr oups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
|||Hi
"jbonifacejr" wrote:
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John
Can someone explain why this stored proc does not work?
Sorry if this angers anyone. I'm posting here and to the .NET group. I
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName = @.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).ValueHow about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
--
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegroups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName => @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>|||Hi
"jbonifacejr" wrote:
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName => @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName = @.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).ValueHow about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
--
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegroups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName => @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>|||Hi
"jbonifacejr" wrote:
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName => @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John
Can someone explain why this stored proc does not work?
Sorry if this angers anyone. I'm posting here and to the .NET group. I
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
@.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).ValueHow about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegroups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>|||Hi
"jbonifacejr" wrote:
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
@.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).ValueHow about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegroups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>|||Hi
"jbonifacejr" wrote:
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John
Sunday, February 19, 2012
Can Reportviewer control size be dynamic
When using an asp.net 2.0 reportviewer control to display an SSRS report, the size of the report is always bigger that the control it is housed in. So you have to scroll back and forth.
Can't the Reportviewer control size be dynamic? What is the general parctice on this?
Thanks
ReportControl and SSRS newbie!
Hi,
I'm not sure what do you mean by "control the size dynamically". Based on my knowledge, if the report does not need to scroll verticall within the iframe, you can handle it in two ways.
First, assign the height, width value in a hard coded way, you can refer the following code which shares the solution.
http://www.codeproject.com/sqlrs/ReportViewer2005.asp
Second, you can set two properties to make the repoertviewer autosize:
AsyncRendering = false; and SizeToReportContent = true;
Thanks.
Subscribe to:
Posts (Atom)