Saving User's Report Parameters

(Reposting, reworded)
I am writing a C# Web application with a web form that displays a report
using URL access method. The URL displays the report for the user, and then
they can pick their parameters and run the report.
I want to know: how can I display the report to the user the next time with
all of the same parameters that they chose? I don't want a snapshot, I want
to refresh the data for the same parameters the user chose.
Do I need to use form post method? or SOAP? Or is there a way to do this
with URL access.
The problem is that when the user changes the value of a parameter, the URL
does not change, so I cannot access that information from my C# Web
application.
MalikFor the level of control you want you need to use web services (soap). You
can definitely get the information you want with that. The URL access will
not provide what you want.
Bruce L-C
"Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
news:%23OdayAThEHA.3612@.TK2MSFTNGP12.phx.gbl...
> (Reposting, reworded)
> I am writing a C# Web application with a web form that displays a report
> using URL access method. The URL displays the report for the user, and
then
> they can pick their parameters and run the report.
> I want to know: how can I display the report to the user the next time
with
> all of the same parameters that they chose? I don't want a snapshot, I
want
> to refresh the data for the same parameters the user chose.
> Do I need to use form post method? or SOAP? Or is there a way to do this
> with URL access.
> The problem is that when the user changes the value of a parameter, the
URL
> does not change, so I cannot access that information from my C# Web
> application.
> Malik
>|||While there's no easy way to do this, it is possible by making the parameter default populate their
values from a database table which looks up the last parameter values used by that user (or a
default if no row exists). You would then also need a set of queries which stored the current
parameter values in the database when the report is run.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e7sShOThEHA.3148@.TK2MSFTNGP10.phx.gbl...
> For the level of control you want you need to use web services (soap). You
> can definitely get the information you want with that. The URL access will
> not provide what you want.
> Bruce L-C
> "Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
> news:%23OdayAThEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > (Reposting, reworded)
> >
> > I am writing a C# Web application with a web form that displays a report
> > using URL access method. The URL displays the report for the user, and
> then
> > they can pick their parameters and run the report.
> >
> > I want to know: how can I display the report to the user the next time
> with
> > all of the same parameters that they chose? I don't want a snapshot, I
> want
> > to refresh the data for the same parameters the user chose.
> >
> > Do I need to use form post method? or SOAP? Or is there a way to do this
> > with URL access.
> >
> > The problem is that when the user changes the value of a parameter, the
> URL
> > does not change, so I cannot access that information from my C# Web
> > application.
> >
> > Malik
> >
> >
>|||Thank you very much for both of your responses.
I did assume that I would have to store parameter values in my own database
table to be able to save report parameters, so that is not really a problem.
In fact, my application already has a table to store parameters, I just have
to define a new parameter type, then put parameter names and values in the
existing table.
But I wouldn't get anywhere without SOAP. My gap in knowledge here was how
to do SOAP integration. I am now hurriedly learning how to do this, but it
doesn't seem overly complicated. It seems like there is a reporting services
object model, and I can somehow get the current parameter values from an
object, so I am content to keep hitting the books until the solution comes
together.
Thanks again for pointing me in the right direction.
Malik
"Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> wrote in message
news:eKhQrFUhEHA.2624@.TK2MSFTNGP12.phx.gbl...
> While there's no easy way to do this, it is possible by making the
parameter default populate their
> values from a database table which looks up the last parameter values used
by that user (or a
> default if no row exists). You would then also need a set of queries
which stored the current
> parameter values in the database when the report is run.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e7sShOThEHA.3148@.TK2MSFTNGP10.phx.gbl...
> > For the level of control you want you need to use web services (soap).
You
> > can definitely get the information you want with that. The URL access
will
> > not provide what you want.
> >
> > Bruce L-C
> >
> > "Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
> > news:%23OdayAThEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > > (Reposting, reworded)
> > >
> > > I am writing a C# Web application with a web form that displays a
report
> > > using URL access method. The URL displays the report for the user, and
> > then
> > > they can pick their parameters and run the report.
> > >
> > > I want to know: how can I display the report to the user the next time
> > with
> > > all of the same parameters that they chose? I don't want a snapshot, I
> > want
> > > to refresh the data for the same parameters the user chose.
> > >
> > > Do I need to use form post method? or SOAP? Or is there a way to do
this
> > > with URL access.
> > >
> > > The problem is that when the user changes the value of a parameter,
the
> > URL
> > > does not change, so I cannot access that information from my C# Web
> > > application.
> > >
> > > Malik
> > >
> > >
> >
> >
>sql

Saving to SQL

I am currently migrating my application from Access to SQL. It looks like it's working good but one thing I cannot understand is why when I have created a varchar(40) field and after I insert a field that is less then 40 characters long, the rest of the string is filled with the leading spaces. I have also create an nchar(40) that did not solve it ether. For example: the text is "Hello World". The text is 11 characters long but the data saved to the table is: "Hello Wolrd" + 29 spaces.

Am I doing something wrong?

Thanks for your help in advance.

Use nvarchar(40) instead. The nchar(40) is for a fixed length column.

|||

I have used nvarchar(40) and varchar(40) and both save with the trailing spaces. I have changed one of the tables and it seems it took effect but then the other table still does the same thing. I wonder what I did to get it working. I don't remember the steps I took. Is there something else besides changing the datafields?

|||

You can update your varchar(nvarchar) column with TRIM functions to remove trailing spaces from both ends(just in case).

UPDATE yourTableSET colVarChar40=LTRIM(RTRIM(colChar40))

|||

I am actually Inserting the new value not updating. How can I accomplish this?

|||

please show your insert code.

|||

the update is for after you have done all your inserting just update it.... or in your sproc before you insert the value do the trim

Saving Stored Procedure

Hello,
I installed SQL Server 2005 on my PC and am using it to access a SQL
database running on a remote server. Using SQL Server Management Studio on
my PC, I connect to the remote database using SQL Server Authentication and
an account that has database owner permissions. At least I think it does -
when I go to the database, then Security / Roles / Database Roles / db_owner
,
it is listed as a role member there.
Once connected, I can change data in tables and view the stored procedures.
However, when I modify one of the stored procedures (by right clicking on it
in Object Explorer, then clicking Modify, then changing it), and then attemp
t
to save the changes, a window pops up on my screen asking me to identify the
location where I want to save the changed stored procedure. It defaults to
a
folder on my local hard drive: My Documents/SQL Server Management
Studio/Projects.
I just want to save the changes in the stored procedure on the remote
database (the master version). Shouldn't I be able to do that? If so, how?
By the way, when I click Modify to view the stored procedure, it's name, at
the top of the window where the T-SQL text is, reads: the address of the
server…SQLQuery1.sql (instead of it's actual name) - for example it reads:
100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
Thanks in advance for help.SSMS opens a Query Window for you when you select to modify the stored proce
dure. Same thing as if
you right-click the server and say "New Query". SSMS also pastes the source
code for the proc in
that query window. As with Word, for instance, a new document has some stand
ard name and when you
select "save" Word (or SSMS) ask you for how you want to name that file.
If you want to execute the ALTER PROCEDURE statement to actually change the
procedure, just execute
the code in the query window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"conmat" <conmat@.discussions.microsoft.com> wrote in message
news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
> Hello,
> I installed SQL Server 2005 on my PC and am using it to access a SQL
> database running on a remote server. Using SQL Server Management Studio o
n
> my PC, I connect to the remote database using SQL Server Authentication an
d
> an account that has database owner permissions. At least I think it does
-
> when I go to the database, then Security / Roles / Database Roles / db_own
er,
> it is listed as a role member there.
> Once connected, I can change data in tables and view the stored procedures
.
> However, when I modify one of the stored procedures (by right clicking on
it
> in Object Explorer, then clicking Modify, then changing it), and then atte
mpt
> to save the changes, a window pops up on my screen asking me to identify t
he
> location where I want to save the changed stored procedure. It defaults t
o a
> folder on my local hard drive: My Documents/SQL Server Management
> Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so, ho
w?
> By the way, when I click Modify to view the stored procedure, it's name, a
t
> the top of the window where the T-SQL text is, reads: the address of the
> server…SQLQuery1.sql (instead of it's actual name) - for example it read
s:
> 100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
> Thanks in advance for help.|||You don't use the Save button. Use the Execute button (or press F5). This
runs the ALTER PROCEDURE script you have modified. The Save button is for
saving scripts on your machine, not for saving changes to the database...
"conmat" <conmat@.discussions.microsoft.com> wrote in message
news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
> Hello,
> I installed SQL Server 2005 on my PC and am using it to access a SQL
> database running on a remote server. Using SQL Server Management Studio
> on
> my PC, I connect to the remote database using SQL Server Authentication
> and
> an account that has database owner permissions. At least I think it
> does -
> when I go to the database, then Security / Roles / Database Roles /
> db_owner,
> it is listed as a role member there.
> Once connected, I can change data in tables and view the stored
> procedures.
> However, when I modify one of the stored procedures (by right clicking on
> it
> in Object Explorer, then clicking Modify, then changing it), and then
> attempt
> to save the changes, a window pops up on my screen asking me to identify
> the
> location where I want to save the changed stored procedure. It defaults
> to a
> folder on my local hard drive: My Documents/SQL Server Management
> Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so,
> how?
> By the way, when I click Modify to view the stored procedure, it's name,
> at
> the top of the window where the T-SQL text is, reads: the address of the
> server…SQLQuery1.sql (instead of it's actual name) - for example it
> reads:
> 100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
> Thanks in advance for help.|||conmat (conmat@.discussions.microsoft.com) writes:
> However, when I modify one of the stored procedures (by right clicking
> on it in Object Explorer, then clicking Modify, then changing it), and
> then attempt to save the changes, a window pops up on my screen asking
> me to identify the location where I want to save the changed stored
> procedure. It defaults to a folder on my local hard drive: My
> Documents/SQL Server Management Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so,
> how?
But you should really save the code to disk and put it under version
control. The datbase should just be viewed as a binary repository.
Of course, as long as you are only playing around, the database may do,
but it's not suitable for real application development.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your help.
I don't think I understand the difference between:
1) Modifying the script in a stored procedure on the database without
actually running the content / code of the procedure
versus
2) Running the actual content / code of the procedure.
If I right click on a stored procedure, choose modify, the script of the
procedure pops up in the Query Window(?), and it starts with:
ALTER PROCEDURE ____
AS
If I then modify the script, then Execute, does that just change the script
without actually running the content (I think the answer is yes, if I
understand you correctly)?
If that is so, how do I then actually execute the content - do I need to
remove the ALTER PROCEDURE___ AS, then Execute (but, how do I remove the
ALTER PROCEDURE without altering the procedure)?
I'm very new to SQL Sever, as you can tell - thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You don't use the Save button. Use the Execute button (or press F5). Thi
s
> runs the ALTER PROCEDURE script you have modified. The Save button is for
> saving scripts on your machine, not for saving changes to the database...
>
>
> "conmat" <conmat@.discussions.microsoft.com> wrote in message
> news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
>
>|||> 1) Modifying the script in a stored procedure on the database without
> actually running the content / code of the procedure
> versus
This changes the code that SQL Server has for the stored procedure. This
means it will change what is stored, and will change what is executed every
time anyone calls the stored procedure.

> 2) Running the actual content / code of the procedure.
This merely runs the code, once.
Consider:
CREATE PROCEDURE dbo.foo
AS
BEGIN
SELECT a = 1;
END
GO
When you run this code, it creates a procedure. It does not run the SELECT
statement. When you *EXECUTE* the stored procedure:
EXEC dbo.foo;
You will see a single-column resultset with the value 1. Just like you
would have seen if you ran the code within the procedure, by itself:
SELECT a = 1;
Now, if you run this:
ALTER PROCEDURE dbo.foo
AS
BEGIN
SELECT a = 2;
END
GO
Again, it does not run the SELECT statement, it changes the script that SQL
Server has stored for the procedure. So the next time someone executes the
stored procedure, they will get a single-column resultset with the value 2.

> If that is so, how do I then actually execute the content
Well, if you have already run the ALTER and you are happy with it, then like
above:
EXEC dbo.foo;

> I'm very new to SQL Sever, as you can tell - thanks again for your help.
I think there is plenty of material out there that you should familiarize
yourself with.
http://www.aspfaq.com/2423|||Hey Guys,
The Orginal question was how to save a modified stored procedure.
I read all your reponses but none addressed the issue.
I have a similar problem
Can anyone just simple show how to save a modified sp in sql server managem
ent studio.
Thanks|||> The Orginal question was how to save a modified stored procedure.
Save where? Do you want to create a file containing the TSQL code used to cr
eate that procedure? Or
do you want to execute the ALTER PROCEDURE ... command so you do modify that
procedure according to
the changes you have made?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sportrx" <sportrx.24qulq@.mail.codecomments.com> wrote in message
news:sportrx.24qulq@.mail.codecomments.com...
> Hey Guys,
> The Orginal question was how to save a modified stored procedure.
> I read all your reponses but none addressed the issue.
> I have a similar problem
> Can anyone just simple show how to save a modified sp in sql server
> management studio.
> Thanks
>
> --
> sportrx
> ---
> Posted via http://www.codecomments.com
> ---
>|||> The Orginal question was how to save a modified stored procedure.
> I read all your reponses but none addressed the issue.
That's because the original question remains unclear.

> Can anyone just simple show how to save a modified sp in sql server
> management studio.
What does "save a modified sp" mean? Do you want to apply the changes to
the database, save the script in a .PRC or .SQL file, check the changes into
source control, ...?
If you run an ALTER PROCEDURE script, that will apply the changes to the
stored procedure in the current database. If you want to save the script to
a file, click the Save icon. Those are two *very* different actions.
A

Saving Report (Access Denied when rendering)

Dear All,

It's a beginner of reporting service. I would like to render a report and then save it automatically. However, I face the problem as below:

When I render a report, I got an error : "Exception Details:System.Net.WebException: The request failed with HTTP status 401: Access Denied.". The code is as follows:

ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
byte[] ResultStream;
string[] StreamIdentifiers;
string OptionalParam = null, filename="NorthwindCustomers.pdf";
ParameterValue[] optionalParams = null;
Warning[] optionalWarnings = null;
ResultStream = rs.Render("/Northwind Customers", "PDF", null,
"<DeviceInfo<Toolbar>False</Toolbar></DeviceInfo>",null,null,
null, out OptionalParam, out OptionalParam, out optionalParams,
out optionalWarnings, out StreamIdentifiers);
// Creating a verbatim string.
FileStream stream = File.OpenWrite(@."C:\Articles\SQL Server Reporting
Services\SourceCode\RSWebServiceXS\NorthwindCustomers\" + filename);
stream.Write(ResultStream, 0, ResultStream.Length);
stream.Close();

Error occurs when the method rs.Render() is called. Any suggestion to solve the problem??

Wow...so happy that the problem has been solved as follows:

Replace:

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

To:

CredentialCache cache = new CredentialCache();
cache.Add( new Uri(rs.Url), // Web service URL
"Negotiate", // Kerberos or NTLM
new NetworkCredential("username", "password", "domainname") );
rs.Credentials = cache;

Saving a database into an importable file format

I am brand new to SQL. Have limited coding experience w/ Visual Basic and database ops w/ Access.

I have come into possession of a SQL 2005 database. The problem is that the designer tried to re-invent the wheel and code a very very poor LMS back end for tracking and reporting. I would like, if possible, to save the database and as far as I am aware - should be able to import into Access and generate statistics and reports to my hearts content.

I have tried w/out success to save the database going through SQL Server Management Studio Express.

Any help or information that could be provided would be appreciated. Any additional information that I can provide, please let me know.

Thanks - Will

You can not save the database as is, you will still need the sql enine to read the information. The easiest way to store the information is to use the backup and restore functions.

To export the data to Access you will need to use SSIS (Sql Server Intergration Services), but if all you want to do is report off it using access why don't you just use the linked tables functions inside Access.

|||Thanks Glenn.

Any suggested reading material on the basics operating through SQL Server Management Stuido?