is it possible to recieve result of SQL stored procedure to web page?

I have web server with .aspx page from wich I call stored procedure on MSSQL server. In this procedure are lots of "select" statements and I need to show results of this statements in web page. Can I call this procedure in that manner that procedure output is writen in some file and this file then ir recieved by web server and included in web page.

for (int i=0; i<dataset.Tables.Count; i++) {
 Response.Write(dataset.Tables[i].TableName);
 Response.Write("\n");
 // loop rows
 for (int j=0; j<dataset.Tables[i].Rows.Count; j++) {
 // loop columns
 for (int k=0; k<dataset.Tables[i].Columns.Count; k++) {
 Response.Write(dataset.Tables[i].Rows[j][k].ToString());
 if (k < dataset.Tables[i].Columns.Count - 1) {
 Response.Write(", ");
 }
 }
 Response.Write("\n");
 }
 Response.Write("\n");
}

this isn't exactly what you want, but idea.|||If the procedure returns a fixed number of result sets, you can return the results a datareader and then bind each result set to something like a datagrid or a repeater using .nextresult between bindings. A datareader can hold more than one result set. It can be pretty handy.

Is it possible to know whether report is still executing in report viewer? (Web Forms)

Hi All,

I have a Report Viewer and a Exit button in Web Form. Untill the report is processed i don't want to enable the button.

so Is it possible to know whether report is still executing in report viewer ?

I am using Remote Mode.

Any help will be appreciated.

Thanks

Akash

YOu can use the asynchronous methods for rendering the report, if the report did not raise the event of finishing you can be sure that the report is already running.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am using HistoryID to generate the report so i can't use the asynchronous thing.

Below are the lines which are generating the report.

ReportViewer1.ServerReport.ReportServerUrl = new Uri(ConfigurationManager.AppSettings["ReportServerPath"].ToString());

ReportViewer1.ShowParameterPrompts = false;

ReportViewer1.ServerReport.HistoryId = strHistoryID; -- Assigning HistoryID

ReportViewer1.ServerReport.ReportPath = strReportPath;

ReportViewer1.ServerReport.Refresh();

Report is rendering asynchronously from the rest of the web page.(i.e. Report Viewer AsyncRendering attribute is set to true)

Is it possible to insert a PDF File into a SQL 2k Table?

Hello,
I want to develop a small PDF Management System for our Web Insurance
Systems and Im wondering if I can use SQL Server to save my generated PDF
Documents. Is it possible? If so is it suggested? Are there any other
alternatives?
Jorge Luzarraga C
Fidens S.A.
321 7610 Anx 23
"I can do it quick. I can do it cheap. I can do it well. Pick any two."Jorge Luzarraga Castro wrote:
> Hello,
> I want to develop a small PDF Management System for our Web Insurance
> Systems and Im wondering if I can use SQL Server to save my generated PDF
> Documents. Is it possible? If so is it suggested? Are there any other
> alternatives?
>
It would be better to store the filenames to the PDF files in the
database. Otherwise, the database could become too large, or if the
database was to be corrupted, all pdf-files could be lost.
Steven|||> Is it possible?
Yes.

> If so is it suggested?
Typically, no.
http://www.aspfaq.com/2149

> Are there any other alternatives?
Yes, store the files in the filesystem, and their paths and other
information about them in the database.
A

Is it possible to customize the look of the parameters form?

Hi all...
When a report is viewed in web browser, a parameters form appear at the top
of the screen so that user can enter parameters and then press View Report.
Is it possible to display a title before that form? or to customize the
colors and fonts for that form?
Thanks
JaimeHave the SAME question!! =:)
"Jaime Stuardo" wrote:
> Hi all...
> When a report is viewed in web browser, a parameters form appear at the top
> of the screen so that user can enter parameters and then press View Report.
> Is it possible to display a title before that form? or to customize the
> colors and fonts for that form?
> Thanks
> Jaime|||You can adjust the colours of the parameters form by changing the
htmlviewer.css stylesheet. Supposedly if you have installed the hot fix for
sp2 then you should find a proptery called htmlviewrstylesheet which you can
set to any style sheet you have created to be th edefault for all reports but
I havent found this.
so in the url of the reports when they are called from another web page I
use the command rc:Stylesheet=stylesheetname
"=:)" wrote:
> Have the SAME question!! =:)
> "Jaime Stuardo" wrote:
> > Hi all...
> >
> > When a report is viewed in web browser, a parameters form appear at the top
> > of the screen so that user can enter parameters and then press View Report.
> >
> > Is it possible to display a title before that form? or to customize the
> > colors and fonts for that form?
> >
> > Thanks
> > Jaime|||IS there a way of hiding the parameters completely as I dont want to
allow the users to change the parameters.
Sorry for the dumb question but this is my 2nd day on reporting
services and i'm trying to get to grips with it very quickly|||Hi Jim,
&rc:Parameters=False is an option.
(have a look here:
http://weblogs.asp.net/mhawley/archive/2004/11/17/259178.aspx for more).
HTH,
Ed Richard
"JimW13UK" <jimw13uk@.uku.co.uk> wrote in message
news:1120725823.751966.67920@.g14g2000cwa.googlegroups.com...
> IS there a way of hiding the parameters completely as I dont want to
> allow the users to change the parameters.
> Sorry for the dumb question but this is my 2nd day on reporting
> services and i'm trying to get to grips with it very quickly
>|||Thanks Ed. The link you posted has answered my question. I can program my
custom parameter form using either VB.NET or C# and using ReportViewer
control to actually show the report.
Jaime
"Ed Richard" wrote:
> Hi Jim,
> &rc:Parameters=False is an option.
> (have a look here:
> http://weblogs.asp.net/mhawley/archive/2004/11/17/259178.aspx for more).
> HTH,
> Ed Richard
> "JimW13UK" <jimw13uk@.uku.co.uk> wrote in message
> news:1120725823.751966.67920@.g14g2000cwa.googlegroups.com...
> > IS there a way of hiding the parameters completely as I dont want to
> > allow the users to change the parameters.
> > Sorry for the dumb question but this is my 2nd day on reporting
> > services and i'm trying to get to grips with it very quickly
> >
>
>

Is it possible to convert T-SQL 2005 to T-SQL 2000

I am working with the Web Service Software Factory and I have created my stored procedures that I will be needing. The problem is that when I try to apply the SP's to the database, I receive errors regarding the syntax. I am using SQL Server 2000 and it generates the script in 2005 T-SQL format. The script in 2005 is quite different from 2000 and I am not sure if converting it is even possible (eg. no try catch equivalent in 2000 and new error handling ). I searched msdn to check if I could specify the script version in WSSF, but I could not find anything. Any suggestions as to how I might solve this without purchasing SQL Server 2005?

The Web Service Software Factory had created this sql file that included the following function that is used throughout the file:

Code Snippet

IF NOT EXISTS (SELECT NAME FROM dbo.sysobjects WHERE TYPE = 'P' AND NAME = 'RethrowError')

BEGIN

EXEC('CREATE PROCEDURE [dbo].RethrowError AS RETURN')

END

GO

ALTER PROCEDURE RethrowError AS

/* Return if there is no error information to retrieve. */

IF ERROR_NUMBER() IS NULL

RETURN;

DECLARE

@.ErrorMessage NVARCHAR(4000),

@.ErrorNumber INT,

@.ErrorSeverity INT,

@.ErrorState INT,

@.ErrorLine INT,

@.ErrorProcedure NVARCHAR(200);

/* Assign variables to error-handling functions that

capture information for RAISERROR. */

SELECT

@.ErrorNumber = ERROR_NUMBER(),

@.ErrorSeverity = ERROR_SEVERITY(),

@.ErrorState = ERROR_STATE(),

@.ErrorLine = ERROR_LINE(),

@.ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

/* Building the message string that will contain original

error information. */

SELECT @.ErrorMessage =

N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +

'Message: '+ ERROR_MESSAGE();

/* Raise an error: msg_str parameter of RAISERROR will contain

the original error information. */

RAISERROR(@.ErrorMessage, @.ErrorSeverity, 1,

@.ErrorNumber, /* parameter: original error number. */

@.ErrorSeverity, /* parameter: original error severity. */

@.ErrorState, /* parameter: original error state. */

@.ErrorProcedure, /* parameter: original error procedure name. */

@.ErrorLine /* parameter: original error line number. */

);

GO

And the error I receive is this ( I added 'machine\instance' to make it more generic):

Code Snippet

Msg 195, Level 15, State 10, Server Machine\Instance, Procedure RethrowError, Line 4
'ERROR_NUMBER' is not a recognized function name.
Msg 195, Level 15, State 10, Server Machine\Instance, Procedure RethrowError, Line 19
'ERROR_NUMBER' is not a recognized function name.
Msg 195, Level 15, State 10, Server Machine\Instance, Procedure RethrowError, Line 30
'ERROR_MESSAGE' is not a recognized function name.
Msg 170, Level 15, State 1, Server Machine\Instance, Procedure InsertUsers, Line 29
Line 29: Incorrect syntax near 'TRY'.
Msg 170, Level 15, State 1, Server Machine\Instance, Procedure InsertUsers, Line 37
Line 37: Incorrect syntax near 'TRY'.
Msg 156, Level 15, State 1, Server Machine\Instance, Procedure InsertUsers, Line 41
Incorrect syntax near the keyword 'END'.
Msg 156, Level 15, State 1, Server Machine\Instance, Procedure InsertUsers, Line 44
Incorrect syntax near the keyword 'END'.
Msg 170, Level 15, State 1, Server Machine\Instance, Procedure UpdateUsers, Line 31
Line 31: Incorrect syntax near 'TRY'.
Msg 170, Level 15, State 1, Server Machine\Instance, Procedure UpdateUsers, Line 40
Line 40: Incorrect syntax near 'TRY'.
Msg 156, Level 15, State 1, Server Machine\Instance, Procedure UpdateUsers, Line 44
Incorrect syntax near the keyword 'END'.
Msg 156, Level 15, State 1, Server Machine\Instance, Procedure UpdateUsers, Line 47
Incorrect syntax near the keyword 'END'.

The other errors involving the try-catch and end revolve around the try-catch syntax where it does not recognize the rethrowerror function.

Code Snippet

BEGIN TRY

Do Something

END TRY

BEGIN CATCH

EXEC RethrowError;

END CATCH

Any suggestions as to how I could convert this without having to rewrite the sql file generated the project tools? Thanks in advance.

You will have to remove the exception handling code (it is new in SQL Server 2005). You need to also remove references to the ERROR* functions. Only @.@.ERROR was available before.|||That seems so easy. I cant believe I didnt think of that on my own! Thanks for your help.

Is it possible

I have a .NET Web application that points to quite a few SQL reports.

Here is what I am looking to do:

I have a list of values that will change quite frequently. Say this is the list for example

A

B

C

D

E

In my report I need to say "where MyField = 'A' OR MyField = 'B' OR MyField = 'C'" .... etc.

The problem here is the list above is going to vary in value and *in number of parameters*. It won't always be 5 parameters...sometimes 6, etc. I won't know at compile time how many parameters.

***Is there a way to build the query for the Report in Visual Studio and have the report use that query for its dataset?***

Basically in Visual Studio I would say:

myQuery = "select whatever from wherever where MyField = FirstListValue "

While not at end of list

myQuery += "OR" + nextValue

End While

Has anyone done this before?

Are you using SQL Server 2005 and are you saying that it is a different number of values that can be in the same field? If so, just us a multi value parameter and in your SQL in the report add the clause: and MyField in (@.MyParm)

Then pass the values to the report in the MyParm parameter.

|||I'm fairly sure that is what I need. I'll give it a try and let you know how it turns out. Thanks!

Is it possbile to define Database server and web server on different machine (Not same phy

Hi All,

Is it possbile to define connection string for web and database server which is running on different machine. Note( Not same LAN).

E.g (My Web Server is in London and Database server in Sydney).

Please can any one help me.??

Thanks in advance...

Dj

They dont need to be on the same LAN, but you'll still need a viable WAN link.

To connect you could use the remote servers IP address with sql credentials.

data source=192.x.x.x;initial catalog=yourDatabase;user id=yourUsername;password=yourPassword;

Is it a good idea to use SQL2005 MDF for audit, exception tracking

I am writing a web application that uses a Teradata database as the primary data source. While Teradata is great as a data warehouse and managing Terabytes of information it doesn't do as well when update or inserting. I was thinking of using a local SQL2005 MDF file to hold a few reference tables and an audit table to collect usage information and exception database to capture any errors.

There could be a few thousand users of the web application but no more than a couple hundred at a time.

I just trying to get some opinions on these technique. I am open to all comments and suggestions.

Thank You

Hi John,

If you're using the SQL Server as the auditing database and exception tracking, I think it will be fine.

Although there will be hundreds of connections to the main app, the exception will not be much and audit data size will not be huge then. Just connect the audit and exception handling module to your SQL Server database, and it will be OK.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

Kevin

Thank you. I'm wondering if it is acceptable practice to run SQL Server 2005 express rather than installing a full SQL Server 2005 on the Advanced Server for doing the Audit tracking and exception reporting. The MDF file should never reach the 4GB limit of Express. Thank you again for your answer.

IS IT A Challenge?(cOnfigurE a vIrtuaL dIrectorY fOR sYncronizatioN wIthouT wEB sYnchronizatioN

if you R Right Person Help Me Please.....

Explain the requirement about SQL & Operating system you are using.

Is FOR XML EXPLICIT still an accepted technique?

I have inherited (someone else built it) an ASP IIS site attached to a SQL
Server 2000 database. It is quite a large web site job and I don't want to
rewrite it in .NET. I don't have the time to do that and I am not familiar
with .NET. Our company still uses VB6 for our products.
The remote site allows the user to select recordsets which currently can be
emailed as HTML or TEXT and also downloaded in an Excel (XLS) file. My job
is to create XML from the recordset, transmit it to the client browser
(which is part of a VB program) and have the client program load it into the
local SQL Server database.
I am new at using XML and have done considerable reading (my head hurts).
Some of the books are a couple years old. The recordsets are composed of
header records from the main table and child records (one to many) from 3
other tables.
I am leaning toward using FOR XML EXPLICIT in conjunction with ADODB stream
sent to the Response object. I have gotten a simple FOR XML AUTO program to
work properly and send the stream back to the browser, but now I need to
shape the more complicated XML properly.
I just want to make sure that the FOR XML EXPLICIT will not become "legacy"
code in the next few years. I have looked at using XML Views briefly, but do
not like the setup required on the SQL server to use them. It will be a
hosted remote server that houses the IIS ASP code and the SQL database.
So before I spend weeks writing and debugging the process, I want to make
sure I haven't missed some spectacular new, reliable and "easy" method of
accomplishing the same thing.
Also I plan on using the Transact/SQL OPENXML function to write the
resulting XML to the database at the client site.
There is one other problem. Using the ADODB stream sent to the Response
object results in the XML remaining "hidden" (such that a blank page appears
in the browser) which is fine...except I don't know how to access it. I
have experience using XML data islands (in HTML pages) to populate SQL
Server and also opening XML files on disk and writting to SQL Server.
Thanks for your help in advance...
For XML Explicit is definitely supported in the next release of SQL Server.
There is also a For XML Path option in the next release that would be easier
for you to use but anything you do in Explicit mode should work for the
foreseeable future. OpenXML is also fully supported in SQL Server 2005.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:e5SLwHbaEHA.3684@.TK2MSFTNGP09.phx.gbl...
>I have inherited (someone else built it) an ASP IIS site attached to a SQL
> Server 2000 database. It is quite a large web site job and I don't want to
> rewrite it in .NET. I don't have the time to do that and I am not familiar
> with .NET. Our company still uses VB6 for our products.
> The remote site allows the user to select recordsets which currently can
> be
> emailed as HTML or TEXT and also downloaded in an Excel (XLS) file. My job
> is to create XML from the recordset, transmit it to the client browser
> (which is part of a VB program) and have the client program load it into
> the
> local SQL Server database.
> I am new at using XML and have done considerable reading (my head hurts).
> Some of the books are a couple years old. The recordsets are composed of
> header records from the main table and child records (one to many) from 3
> other tables.
> I am leaning toward using FOR XML EXPLICIT in conjunction with ADODB
> stream
> sent to the Response object. I have gotten a simple FOR XML AUTO program
> to
> work properly and send the stream back to the browser, but now I need to
> shape the more complicated XML properly.
> I just want to make sure that the FOR XML EXPLICIT will not become
> "legacy"
> code in the next few years. I have looked at using XML Views briefly, but
> do
> not like the setup required on the SQL server to use them. It will be a
> hosted remote server that houses the IIS ASP code and the SQL database.
> So before I spend weeks writing and debugging the process, I want to make
> sure I haven't missed some spectacular new, reliable and "easy" method of
> accomplishing the same thing.
> Also I plan on using the Transact/SQL OPENXML function to write the
> resulting XML to the database at the client site.
> There is one other problem. Using the ADODB stream sent to the Response
> object results in the XML remaining "hidden" (such that a blank page
> appears
> in the browser) which is fine...except I don't know how to access it. I
> have experience using XML data islands (in HTML pages) to populate SQL
> Server and also opening XML files on disk and writting to SQL Server.
> Thanks for your help in advance...
>

is displayed when returned from database call

I am making a web Application, where non breakable space (&nbsp;) is
crucial to the layout. When I write &nbsp; directly into the html, it
is displayed correctly. But If I store a value in the database (MS Sql
server 2003) containing the &nbsp; code it is rendered to the browser
like this:
King&nbsp;Kong
instead of
King Kong
Any solutions?
Yes, formatting shouldn=B4t be stored in the database unless you have to
use this in any way. I suggest formatting your text on the client side
not storing in in the server. Anyway, did you look at your HTML Code
which is rendered ? Perhap syou use a special control which does the
formatting for you, so that &nbsp; will be HTML coded as &nbsp; int
the client code.
HTH, Jens Suessmeyer.
|||To add to Jens' response, this is not a SQL Server issue because SQL Server
treats the data as a simple character string. The altering of the embedded
html codes occurs somewhere in your client code or components.
Hope this helps.
Dan Guzman
SQL Server MVP
"koldskaal" <bjarkeriis@.gmail.com> wrote in message
news:1139151007.170882.40360@.g43g2000cwa.googlegro ups.com...
>I am making a web Application, where non breakable space (&nbsp;) is
> crucial to the layout. When I write &nbsp; directly into the html, it
> is displayed correctly. But If I store a value in the database (MS Sql
> server 2003) containing the &nbsp; code it is rendered to the browser
> like this:
> King&nbsp;Kong
> instead of
> King Kong
> Any solutions?
>
|||thanks Guys!
yes the hml code formats the retrieved string as KING&nbsp;KONG. I
will try to make a serverside function that exchanges all spaces in the
string with &nbsp;=20
I=B4ll let you know the result in 2 minnutes
|||it worked!
it was quite easy
public static string unbreakSpaces(string oldstring)
{
return oldstring.Replace(" ", "&nbsp;");
}

is displayed when returned from database call

I am making a web Application, where non breakable space ( ) is
crucial to the layout. When I write directly into the html, it
is displayed correctly. But If I store a value in the database (MS Sql
server 2003) containing the code it is rendered to the browser
like this:
King Kong
instead of
King Kong
Any solutions'Yes, formatting shouldn=B4t be stored in the database unless you have to
use this in any way. I suggest formatting your text on the client side
not storing in in the server. Anyway, did you look at your HTML Code
which is rendered ? Perhap syou use a special control which does the
formatting for you, so that will be HTML coded as int
the client code.
HTH, Jens Suessmeyer.|||To add to Jens' response, this is not a SQL Server issue because SQL Server
treats the data as a simple character string. The altering of the embedded
html codes occurs somewhere in your client code or components.
Hope this helps.
Dan Guzman
SQL Server MVP
"koldskaal" <bjarkeriis@.gmail.com> wrote in message
news:1139151007.170882.40360@.g43g2000cwa.googlegroups.com...
>I am making a web Application, where non breakable space ( ) is
> crucial to the layout. When I write directly into the html, it
> is displayed correctly. But If I store a value in the database (MS Sql
> server 2003) containing the code it is rendered to the browser
> like this:
> King Kong
> instead of
> King Kong
> Any solutions'
>|||thanks Guys!
yes the hml code formats the retrieved string as KING KONG. I
will try to make a serverside function that exchanges all spaces in the
string with =20
I=B4ll let you know the result in 2 minnutes|||it worked!
it was quite easy
public static string unbreakSpaces(string oldstring)
{
return oldstring.Replace(" ", " ");
}

is displayed when returned from database call

I am making a web Application, where non breakable space ( ) is
crucial to the layout. When I write directly into the html, it
is displayed correctly. But If I store a value in the database (MS Sql
server 2003) containing the code it is rendered to the browser
like this:
King Kong
instead of
King Kong
Any solutions'Yes, formatting shouldn=B4t be stored in the database unless you have to
use this in any way. I suggest formatting your text on the client side
not storing in in the server. Anyway, did you look at your HTML Code
which is rendered ? Perhap syou use a special control which does the
formatting for you, so that will be HTML coded as &nbsp; int
the client code.
HTH, Jens Suessmeyer.|||To add to Jens' response, this is not a SQL Server issue because SQL Server
treats the data as a simple character string. The altering of the embedded
html codes occurs somewhere in your client code or components.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"koldskaal" <bjarkeriis@.gmail.com> wrote in message
news:1139151007.170882.40360@.g43g2000cwa.googlegroups.com...
>I am making a web Application, where non breakable space ( ) is
> crucial to the layout. When I write directly into the html, it
> is displayed correctly. But If I store a value in the database (MS Sql
> server 2003) containing the code it is rendered to the browser
> like this:
> King Kong
> instead of
> King Kong
> Any solutions'
>|||thanks Guys!
yes the hml code formats the retrieved string as KING&nbsp;KONG. I
will try to make a serverside function that exchanges all spaces in the
string with
I=B4ll let you know the result in 2 minnutes|||it worked!
it was quite easy
public static string unbreakSpaces(string oldstring)
{
return oldstring.Replace(" ", " ");
}

IS ASP.Net 1.x Working together with SQL 2005?

Hi

Should ASP.Net 1.x work togheter with sql 2005 without problems? I have try to open a web project but I got the following error:

Cannot open database "crm" requested by the login. The login failed. Login failed for user 'crmuser'.

This crmuser is "promoted" to owner of the crm base, but still I got the problem.

I upgrade this web project to ASP.Net 2.0 and I don't have the login problems, that's why I'm wondring.

Hope someone can answear me on this question. Thanks!

Jan

SQL Server 2005 does not care what version is your application all you need is correct permissions and the correct connection string for 1.1, there are two permissions in SQL Server both are covered in the thread below and look up connections string for 1.1 in the product docs. Hope this helps.

http://forums.asp.net/thread/1492092.aspx

|||You solve my problems, thanks!!|||

lsoljf:

You solve my problems, thanks!!

I am glad I could help.