Forms Authentication, LogonUser, and certificates

We are having an issue getting Report Manager to work with SSL.
Here's the setup:
We have thatken the forms authetication example and have modified it
to suit our needs, and it works fine (no SSL at this point). Now, we
want to make our application available over the Internet, so we have
gotten a certificate from VeriSign and turned on SSL - the application
works fine.
The snag comes in with trying to access the Report Manager (even from
the machine it is installed on). When I debug using Visual Studio,
the problem occurs in the UILogin.aspx when calling the
ReportingService's LogonUser class. The error that is reported is
"The underlying connection was closed: Could not establish trust
relationship with remote server." It seems like the cetificate is not
being accepted by the reporting service. From reading this forum, I
understand that the URL of the ReportingService object should be set
to "https://<machine_name>/ReportServer/reportservice.asmx". However,
our certificate from VeriSign has our production website name, which
obviously does not match the machine name. Could this be causing the
problem? If so, is there a way around this?
We do NOT want the Report Manger available over the internet, just
locally. A hokey solution is OK, as long as it keeps the Reporting
Service secure. I anybody has any insights into this situation would
be greatly appreaciated.
Thanks,
Terry RomanceTerry,
Is UseSSL in the the Report Manager RSWebApplication.config file on, such
as:
<UI>
<CustomAuthenticationUI>
<loginUrl>/Pages/UILogon.aspx</loginUrl>
<UseSSL>True</UseSSL>
</CustomAuthenticationUI>
<!--Change the following line to reflect the computer name where RS is
installed. DON NOT USE LOCALHOST-->
<ReportServerUrl>http://MyRSServer/ReportServer</ReportServerUrl>
</UI>
Yes, it will be a good idea to move the Report Manager to a private subnet
if you don't need it to be accessible on Internet. But I don't think this
will solve your issue.
In addition make sure your access the Report Manager by using the computer
name where it is not installed, not localhost.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.manning.com/lachev
Home page and blog http://www.prologika.com
---
"Terry Romance" <tedgromance@.yahoo.com> wrote in message
news:f284ebe.0408231338.3fa07029@.posting.google.com...
> We are having an issue getting Report Manager to work with SSL.
> Here's the setup:
> We have thatken the forms authetication example and have modified it
> to suit our needs, and it works fine (no SSL at this point). Now, we
> want to make our application available over the Internet, so we have
> gotten a certificate from VeriSign and turned on SSL - the application
> works fine.
> The snag comes in with trying to access the Report Manager (even from
> the machine it is installed on). When I debug using Visual Studio,
> the problem occurs in the UILogin.aspx when calling the
> ReportingService's LogonUser class. The error that is reported is
> "The underlying connection was closed: Could not establish trust
> relationship with remote server." It seems like the cetificate is not
> being accepted by the reporting service. From reading this forum, I
> understand that the URL of the ReportingService object should be set
> to "https://<machine_name>/ReportServer/reportservice.asmx". However,
> our certificate from VeriSign has our production website name, which
> obviously does not match the machine name. Could this be causing the
> problem? If so, is there a way around this?
> We do NOT want the Report Manger available over the internet, just
> locally. A hokey solution is OK, as long as it keeps the Reporting
> Service secure. I anybody has any insights into this situation would
> be greatly appreaciated.
> Thanks,
> Terry Romance|||Teo,
Thanks for the reply. I have SSL turned on, and here is the
relevant section of the RSWebApplication.config file:
<UI>
<CustomAuthenticationUI>
<loginUrl>/Pages/UILogin.aspx</loginUrl>
<UseSSL>True</UseSSL>
</CustomAuthenticationUI>
<ReportServerUrl>https://<machine_name>/ReportServer</ReportServerUrl>
</UI>
(I have the actual machine name, not 'localhost')
I also have the following in RSReportServer.config:
<Add Key="SecureConnectionLevel" Value="3"/>
I noticed in your post you had http: instead of https: - when I tried
making that change in the RSWebApplication.config file, I received the
following exception instead:
"The operation you are attempting requires a secure connection
(HTTPS)."
So, I'm guessing that the https: is correct.
Does my guess that it is a problem with the difference between the
name in the config file and the name on the certificate that is
causing the error?
I can validate the user directly using my own code, but is there any
way I could create a certificate on the fly and pass it to the Report
Server so that it will then allow the user into the Report Manager?
Currently we are stuck with switching back to Windows authentication
or turning off SSL.
Any ideas would be helpful at this point.
Thanks,
Terry
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message news:<eeC9HLYiEHA.3876@.TK2MSFTNGP12.phx.gbl>...
> Terry,
> Is UseSSL in the the Report Manager RSWebApplication.config file on, such
> as:
> <UI>
> <CustomAuthenticationUI>
> <loginUrl>/Pages/UILogon.aspx</loginUrl>
> <UseSSL>True</UseSSL>
> </CustomAuthenticationUI>
> <!--Change the following line to reflect the computer name where RS is
> installed. DON NOT USE LOCALHOST-->
> <ReportServerUrl>http://MyRSServer/ReportServer</ReportServerUrl>
> </UI>
> Yes, it will be a good idea to move the Report Manager to a private subnet
> if you don't need it to be accessible on Internet. But I don't think this
> will solve your issue.
> In addition make sure your access the Report Manager by using the computer
> name where it is not installed, not localhost.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.manning.com/lachev
> Home page and blog http://www.prologika.com
> ---|||Terry,
Yes, it should be https. I doubt that the problem is with the certificate.
What does navigating to https://machine_name/reportserver give you?
In addition, according to your explanation this error only happens with the
Report Manager. Have you tried callling LogonUser from another application?
Is the call successful?
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.manning.com/lachev
Home page and blog http://www.prologika.com
---
"Terry Romance" <tedgromance@.yahoo.com> wrote in message
news:f284ebe.0408240416.130c778e@.posting.google.com...
> Teo,
> Thanks for the reply. I have SSL turned on, and here is the
> relevant section of the RSWebApplication.config file:
> <UI>
> <CustomAuthenticationUI>
> <loginUrl>/Pages/UILogin.aspx</loginUrl>
> <UseSSL>True</UseSSL>
> </CustomAuthenticationUI>
> <ReportServerUrl>https://<machine_name>/ReportServer</ReportServerUrl>
> </UI>
> (I have the actual machine name, not 'localhost')
> I also have the following in RSReportServer.config:
> <Add Key="SecureConnectionLevel" Value="3"/>
> I noticed in your post you had http: instead of https: - when I tried
> making that change in the RSWebApplication.config file, I received the
> following exception instead:
> "The operation you are attempting requires a secure connection
> (HTTPS)."
> So, I'm guessing that the https: is correct.
> Does my guess that it is a problem with the difference between the
> name in the config file and the name on the certificate that is
> causing the error?
> I can validate the user directly using my own code, but is there any
> way I could create a certificate on the fly and pass it to the Report
> Server so that it will then allow the user into the Report Manager?
> Currently we are stuck with switching back to Windows authentication
> or turning off SSL.
> Any ideas would be helpful at this point.
> Thanks,
> Terry
>
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:<eeC9HLYiEHA.3876@.TK2MSFTNGP12.phx.gbl>...
> > Terry,
> >
> > Is UseSSL in the the Report Manager RSWebApplication.config file on,
such
> > as:
> >
> > <UI>
> > <CustomAuthenticationUI>
> > <loginUrl>/Pages/UILogon.aspx</loginUrl>
> > <UseSSL>True</UseSSL>
> > </CustomAuthenticationUI>
> > <!--Change the following line to reflect the computer name where RS is
> > installed. DON NOT USE LOCALHOST-->
> > <ReportServerUrl>http://MyRSServer/ReportServer</ReportServerUrl>
> > </UI>
> >
> > Yes, it will be a good idea to move the Report Manager to a private
subnet
> > if you don't need it to be accessible on Internet. But I don't think
this
> > will solve your issue.
> >
> > In addition make sure your access the Report Manager by using the
computer
> > name where it is not installed, not localhost.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.manning.com/lachev
> > Home page and blog http://www.prologika.com
> > ---|||Teo,
Thanks for your continuing help.
When I navigate to "https://machine_name/reportserver", it
redirects me to the Report Server's login screen (Login.aspx, not the
Report Manager's login, which is UILogin.apsx). I am able to login
successfully there - the validation is handled by my own DB lookup, it
then calls FormsAuthentication.RedirectFromLoginPage if the user is
verified.
I did try connecting to the report manager from an externally
(since I believed this would resolve any differences between the
domain name I was connecting to, and the name on the certificate). In
the debugger, execution seemed to hang on the LogonUser call, before
it eventually returned the following message:
"A connection attempt failed because the connected party did not
properly respond after a period of time, or established connection
failed because connected host has failed to respond."
This is different from the message I received when trying to
connect internally (the "trused connection" message). I never
received any prompt message in IE running on the external machine to
indicate that it was waiting for input. Does this help narrow down
what the problem might be?
I have not tried calling LogonUser from another application, but
I will try doing that and post the results.
Again, I appreciate all your help.
Thanks,
Terry
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message news:<ekCmv1diEHA.3944@.tk2msftngp13.phx.gbl>...
> Terry,
> Yes, it should be https. I doubt that the problem is with the certificate.
> What does navigating to https://machine_name/reportserver give you?
> In addition, according to your explanation this error only happens with the
> Report Manager. Have you tried callling LogonUser from another application?
> Is the call successful?
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.manning.com/lachev
> Home page and blog http://www.prologika.com
> ---
>|||Teo,
I hope you are still out there monitoring this thread. I'm
sorry, but we had another issue here that required my attention.
I was finally able to try to code a small test application to
call LogonUser. The result is the same: "The underlying connection
was closed: Could not establish trust relationship with remote
server."
We are currently stuck with switching back to the original config
files and Windows Authentication to access the Report Manager, and
then going back to our custom security when we are done, but this is
by no means a good long-term solution. At this point, I'll welcome
any suggestion or experiment to try.
Thanks in advance.
- Terry
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message news:<ekCmv1diEHA.3944@.tk2msftngp13.phx.gbl>...
> > Terry,
> >
> > Yes, it should be https. I doubt that the problem is with the certificate.
> > What does navigating to https://machine_name/reportserver give you?
> >
> > In addition, according to your explanation this error only happens with the
> > Report Manager. Have you tried callling LogonUser from another application?
> > Is the call successful?
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.manning.com/lachev
> > Home page and blog http://www.prologika.com
> > ---
> >|||Terry,
Check these threads:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&q=%22The+underlying+connection+was+closed%3A+Could+not+establish+trust+relationship+%22&btnG=Search
In addition, if you use client certificates review this:
This article was previously published under Q318103
SYMPTOMS
Note The following .NET Framework Class Library namespaces are referenced in
this article:
System.Web.Services
System.Security.Cryptography.X509Certificates
When you try to pass a client certificate from an ASP.NET page to a Web
service that requires Secure Sockets Layer (SSL) and client certificates,
you receive the following error message:
403 Access Denied
CAUSE
A certificate is associated with the user ID of the user who installed the
certificate (this is the certificate user); therefore, the certificate is
available only when that user profile is loaded. If no one is logged on to
the computer, or if a user other than the certificate owner is logged on,
the certificate is not accessible.
If you access the Web service .asmx file from a browser that passes the
certificates to you, you do not encounter this problem.
The following ASP.NET code fails when calling SimpleServices, which expects
a client certificate:
public void Submit_HelloWorldClick(Object sender, EventArgs E)
{
// Instantiate the proxy class for the web service
SimpleService service = new SimpleService();
// Pass in credentials using NTLM
service.Credentials = CredentialCache.DefaultCredentials;
// Load the client certificate from a file
X509Certificate x509 =X509Certificate.CreateFromCertFile(@."c:\user_der.cer");
service.ClientCertificates.Add(x509);
// Call the service
Result.Text = service.HelloWorld();
}
RESOLUTION
To resolve this issue, invoke the Web service from a Serviced Component, and
use a Microsoft Windows service to automatically load the profile of the
certificate user so that the Serviced Component can retrieve the client
certificate and then communicate with the Web service over SSL.
1.. Create a Windows service program with only one function to run under
the certificate user identity.
2.. Create a Serviced Component that runs under the identity of the
certificate user.
3.. Move the authentication code from the ASP.NET application to the
Serviced Component. Verify that the Serviced Component runs under the
identity of the certificate user.
4.. Call the Serviced Component method from the ASP.NET Web application.
STATUS
This behavior is by design.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Terry Romance" <tedgromance@.yahoo.com> wrote in message
news:f284ebe.0409131008.e9801ec@.posting.google.com...
> Teo,
> I hope you are still out there monitoring this thread. I'm
> sorry, but we had another issue here that required my attention.
> I was finally able to try to code a small test application to
> call LogonUser. The result is the same: "The underlying connection
> was closed: Could not establish trust relationship with remote
> server."
> We are currently stuck with switching back to the original config
> files and Windows Authentication to access the Report Manager, and
> then going back to our custom security when we are done, but this is
> by no means a good long-term solution. At this point, I'll welcome
> any suggestion or experiment to try.
> Thanks in advance.
> - Terry
> > "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:<ekCmv1diEHA.3944@.tk2msftngp13.phx.gbl>...
> > > Terry,
> > >
> > > Yes, it should be https. I doubt that the problem is with the
certificate.
> > > What does navigating to https://machine_name/reportserver give you?
> > >
> > > In addition, according to your explanation this error only happens
with the
> > > Report Manager. Have you tried callling LogonUser from another
application?
> > > Is the call successful?
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > http://www.manning.com/lachev
> > > Home page and blog http://www.prologika.com
> > > ---
> > >|||Basically, you have to disable certificate checking for the RM application.
You do this by writing a custom certificate checker and just ignoring errors
in that checker. You can make this part of your UILogon code.
Hope this helps.
--Carlos
"Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
news:uWfcNndmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Terry,
> Check these threads:
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&q=%22The+underlying+connection+was+closed%3A+Could+not+establish+trust+relationship+%22&btnG=Search
> In addition, if you use client certificates review this:
> This article was previously published under Q318103
> SYMPTOMS
> Note The following .NET Framework Class Library namespaces are referenced
in
> this article:
> System.Web.Services
> System.Security.Cryptography.X509Certificates
> When you try to pass a client certificate from an ASP.NET page to a Web
> service that requires Secure Sockets Layer (SSL) and client certificates,
> you receive the following error message:
> 403 Access Denied
> CAUSE
> A certificate is associated with the user ID of the user who installed the
> certificate (this is the certificate user); therefore, the certificate is
> available only when that user profile is loaded. If no one is logged on to
> the computer, or if a user other than the certificate owner is logged on,
> the certificate is not accessible.
> If you access the Web service .asmx file from a browser that passes the
> certificates to you, you do not encounter this problem.
> The following ASP.NET code fails when calling SimpleServices, which
expects
> a client certificate:
> public void Submit_HelloWorldClick(Object sender, EventArgs E)
> {
> // Instantiate the proxy class for the web service
> SimpleService service = new SimpleService();
> // Pass in credentials using NTLM
> service.Credentials = CredentialCache.DefaultCredentials;
> // Load the client certificate from a file
> X509Certificate x509 => X509Certificate.CreateFromCertFile(@."c:\user_der.cer");
> service.ClientCertificates.Add(x509);
> // Call the service
> Result.Text = service.HelloWorld();
> }
> RESOLUTION
> To resolve this issue, invoke the Web service from a Serviced Component,
and
> use a Microsoft Windows service to automatically load the profile of the
> certificate user so that the Serviced Component can retrieve the client
> certificate and then communicate with the Web service over SSL.
> 1.. Create a Windows service program with only one function to run under
> the certificate user identity.
> 2.. Create a Serviced Component that runs under the identity of the
> certificate user.
> 3.. Move the authentication code from the ASP.NET application to the
> Serviced Component. Verify that the Serviced Component runs under the
> identity of the certificate user.
> 4.. Call the Serviced Component method from the ASP.NET Web application.
> STATUS
> This behavior is by design.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Terry Romance" <tedgromance@.yahoo.com> wrote in message
> news:f284ebe.0409131008.e9801ec@.posting.google.com...
> > Teo,
> >
> > I hope you are still out there monitoring this thread. I'm
> > sorry, but we had another issue here that required my attention.
> >
> > I was finally able to try to code a small test application to
> > call LogonUser. The result is the same: "The underlying connection
> > was closed: Could not establish trust relationship with remote
> > server."
> >
> > We are currently stuck with switching back to the original config
> > files and Windows Authentication to access the Report Manager, and
> > then going back to our custom security when we are done, but this is
> > by no means a good long-term solution. At this point, I'll welcome
> > any suggestion or experiment to try.
> >
> > Thanks in advance.
> >
> > - Terry
> >
> > > "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
> news:<ekCmv1diEHA.3944@.tk2msftngp13.phx.gbl>...
> > > > Terry,
> > > >
> > > > Yes, it should be https. I doubt that the problem is with the
> certificate.
> > > > What does navigating to https://machine_name/reportserver give you?
> > > >
> > > > In addition, according to your explanation this error only happens
> with the
> > > > Report Manager. Have you tried callling LogonUser from another
> application?
> > > > Is the call successful?
> > > > --
> > > > Hope this helps.
> > > >
> > > > ---
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > http://www.manning.com/lachev
> > > > Home page and blog http://www.prologika.com
> > > > ---
> > > >
>|||Carlos,
Thanks for the reply. If it makes a difference, we are NOT using
client certificates.
Excuse me for asking, but I am relatively new with Reporting
Services, Visual Studio .NET, SSL and certificates, and I have no idea
how to disable certificate checking, much less write my own. Do you
have an example, or know a location on the web that might have one?
Thanks,
Terry
"Carlos C Tapang" <ctapang@.centerus.com> wrote in message news:<u##uaRzmEHA.2680@.TK2MSFTNGP15.phx.gbl>...
> Basically, you have to disable certificate checking for the RM application.
> You do this by writing a custom certificate checker and just ignoring errors
> in that checker. You can make this part of your UILogon code.
> Hope this helps.
> --Carlos
>|||Teo,
Thank you for the reply. I think I have read each of those posts
you link to several times each, to no avail, but I will keep digging
through them.
We are not using client certificates in our application.
I have been trying something different lately - linking to the
reporting service from a different machine using the URL that matches
the one on the certificate we were issued by VeriSign. I think I
managed to get around the "could not established trusted connection"
error, but am now getting SOAP errors. When I get more specific
details, I will post them here.
Thanks against for your continuing assistance.
- Terry
"Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message news:<uWfcNndmEHA.512@.TK2MSFTNGP10.phx.gbl>...
> Terry,
> Check these threads:
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&q=%22The+underlying+connection+was+closed%3A+Could+not+establish+trust+relationship+%22&btnG=Search
> In addition, if you use client certificates review this:
> (snip)
>|||Terry,
I don't know what it's causing your grief. I have implemented Forms
Authentication over SSL and didn't have a problem. Have you tried create a
sample web service on that box and calling it just to eliminate RS?
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Terry Romance" <tedgromance@.yahoo.com> wrote in message
news:f284ebe.0409200920.4627cee3@.posting.google.com...
> Teo,
> Thank you for the reply. I think I have read each of those posts
> you link to several times each, to no avail, but I will keep digging
> through them.
> We are not using client certificates in our application.
> I have been trying something different lately - linking to the
> reporting service from a different machine using the URL that matches
> the one on the certificate we were issued by VeriSign. I think I
> managed to get around the "could not established trusted connection"
> error, but am now getting SOAP errors. When I get more specific
> details, I will post them here.
> Thanks against for your continuing assistance.
> - Terry
> "Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
news:<uWfcNndmEHA.512@.TK2MSFTNGP10.phx.gbl>...
> > Terry,
> >
> > Check these threads:
> >
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&q=%22The+underlying+connection+was+closed%3A+Could+not+establish+trust+relationship+%22&btnG=Search
> >
> > In addition, if you use client certificates review this:
> >
> > (snip)
> >|||Teo,
Thanks, I will give that a try, which might not be for awhile -
we just ran into major performance issues. (We had 10 people try to
bring up reports at once, and I think it broke our Forms
Authentication - cookies seemed to disappear!)
Anyway, I hope you'll still be monitoring this when I get back to
checking this issue. Thanks again for all your help.
- Terry
"Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message news:<Od#tw23nEHA.3628@.TK2MSFTNGP09.phx.gbl>...
> Terry,
> I don't know what it's causing your grief. I have implemented Forms
> Authentication over SSL and didn't have a problem. Have you tried create a
> sample web service on that box and calling it just to eliminate RS?
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
>sql

Forms authentication fun

Anyway, I finally get forms authentication up and working (I think). I use the included example. I finally get to the point where I register a user. Then I modify the rsreportservice.config file to make the new user the 'admin' user. Now when I login, I have no permissions to anything. I restart the entire server. Now when I login I get a "Key cannot be null. Parameter name: key:". Any ideas here? Why the heck didn't ms just bake forms authentication as a selectable option! It is so complicated to setup, you have to modify like a bjillion config files.

I had exactly the same problem today.

Are you using the VB code sample? I usually code in C# but the customer I'm working with prefers VB so I used the VB sample solution and got the error you mention.

Using the C# sample solution instead works fine, in theory you should be able to just build the C# solution and drop the assembly in the appropriate folders.

I've not had a chance to take a look at what the difference is between the VB/C# solutions yet but that should get you going.

Totally agree with the config file madness.

Cheers,

Keith.

|||

Keith,

The problem is that the FormsAuthentication sample is incomplete and simply does not work. Only with help from other people on the forum was I able to get it working. I can email some working sample code to you if you wish.

|||

Hello Jon,

Can you send me some working example of FormsAuthentication?
A try Microsoft's example, but, isn’t working :(

Thanks!

Forms authentication fun

Anyway, I finally get forms authentication up and working (I think). I use the included example. I finally get to the point where I register a user. Then I modify the rsreportservice.config file to make the new user the 'admin' user. Now when I login, I have no permissions to anything. I restart the entire server. Now when I login I get a "Key cannot be null. Parameter name: key:". Any ideas here? Why the heck didn't ms just bake forms authentication as a selectable option! It is so complicated to setup, you have to modify like a bjillion config files.

I had exactly the same problem today.

Are you using the VB code sample? I usually code in C# but the customer I'm working with prefers VB so I used the VB sample solution and got the error you mention.

Using the C# sample solution instead works fine, in theory you should be able to just build the C# solution and drop the assembly in the appropriate folders.

I've not had a chance to take a look at what the difference is between the VB/C# solutions yet but that should get you going.

Totally agree with the config file madness.

Cheers,

Keith.

|||

Keith,

The problem is that the FormsAuthentication sample is incomplete and simply does not work. Only with help from other people on the forum was I able to get it working. I can email some working sample code to you if you wish.

|||

Hello Jon,

Can you send me some working example of FormsAuthentication?
A try Microsoft's example, but, isn’t working :(

Thanks!

Forms Authentication Example not safe for multi-threaded applications

I thought this might be useful for other Reporting Services users.
This relates both the Forms Authentication Example from Microsoft, and
the problem it was giving me loading the Authorization Extension:
http://groups.google.com/groups?selm=f284ebe.0409271146.279ecf4c%40posting.google.com&output=gplain
After contacting Microsoft, and getting their help with this problem,
it was determined that the solution was to use SyncLock on the
InitializeMaps method of the AuthorizationExtension class in the
example. That is, in VB:
Private Shared Sub InitializeMaps()
SyncLock GetType(AuthorizationExtension)
<Code as it appears in example>
End SyncLock
End Sub
This resolved our problem. I hope others find this useful.
- TerryWhat is the equivalent code for C#. I found the InitializeMaps() method in
the Authorization.cs file, but it is not clear how you would apply the
synclock option. Also I found no reference to syncLock in the C# help file.
"Terry Romance" wrote:
> I thought this might be useful for other Reporting Services users.
> This relates both the Forms Authentication Example from Microsoft, and
> the problem it was giving me loading the Authorization Extension:
> http://groups.google.com/groups?selm=f284ebe.0409271146.279ecf4c%40posting.google.com&output=gplain
> After contacting Microsoft, and getting their help with this problem,
> it was determined that the solution was to use SyncLock on the
> InitializeMaps method of the AuthorizationExtension class in the
> example. That is, in VB:
> Private Shared Sub InitializeMaps()
> SyncLock GetType(AuthorizationExtension)
> <Code as it appears in example>
> End SyncLock
> End Sub
> This resolved our problem. I hope others find this useful.
> - Terry
>|||JB,
I know it's been a while and you've probably puzzled this out yourself by
now, but the equivalent C# structure is
lock (typeof(AuthorizationExtension)) {
<...method body here...>
}
FWIW,
-Marc
"jbmeeh" wrote:
> What is the equivalent code for C#. I found the InitializeMaps() method in
> the Authorization.cs file, but it is not clear how you would apply the
> synclock option. Also I found no reference to syncLock in the C# help file.sql

Formatting values being passed to Sql

I have a generic page with some fields on it, I would like to be able to "groom" the values so that the data is nice and consistent.

Example: input 1 -- user types in "bob" or "BOB" or "Bob" or "JOE" etc...

the resulting record in Sql would be "Bob", "Fred", "Joe"
nice and formted out.

Any suggestions?string str1, str2 , formattedname
str1 = (name.ToUpper()).SubString(0,1)
str2 = (name.ToLower()).SubString(1)

formattedname = String.concat(str1,str2)

wher name is the string that contains the name to format. hope this helps

Formatting subtotals in Matrix reports

I am trying to alter the color of a cell of a subtotal based on the value of the subtotal.
Example: =IIF(Subtotal.Value < 20 ,"Brown","Blue")
This doesn't work. How do I refer to the value of a subtotal inside an expression' I think it has something to do with InScope(). I have definitely clicked the little green tab and am getting the correct properties window.Please see this posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c9b1dcda-cd35-4669-bb9b-4076ad21ed43
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Getting started and liking it" <Getting started and liking
it@.discussions.microsoft.com> wrote in message
news:5BC824BC-61A2-44A8-BA0B-FD0163954F8E@.microsoft.com...
> I am trying to alter the color of a cell of a subtotal based on the value
of the subtotal.
> Example: =IIF(Subtotal.Value < 20 ,"Brown","Blue")
> This doesn't work. How do I refer to the value of a subtotal inside an
expression' I think it has something to do with InScope(). I have
definitely clicked the little green tab and am getting the correct
properties window.

Formatting partial text

Is it possible to bold a part of the text in the expression? For example, In Crystal, all I have to do is hightlight a word and format it anyway I like and leave the rest of the paragraph alone. So far, I can only use ucase("test"). I need something like bold("test") to work.
ThanksNot in the current release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Le Toan" <Le Toan@.discussions.microsoft.com> wrote in message
news:B05D245D-BCEC-4EBF-8DBA-AC5240288BA3@.microsoft.com...
> Is it possible to bold a part of the text in the expression? For example,
In Crystal, all I have to do is hightlight a word and format it anyway I
like and leave the rest of the paragraph alone. So far, I can only use
ucase("test"). I need something like bold("test") to work.
> Thanks

formatting of reports and blank pages

Why whenever I create a report that is more than 1 page long, do I get a blank page between every page of my report? In VS for example if I create a 3 page report, I get a 6 page report and every other page is blank.

How do you prevent this?

Cheers,

Mike

The culprit is ration of Page Heigth and Page Width. Check in Report-Properties-> Page Width and Page Height. Try setting Width = 31cm and Height = 28cm.

-kushpaw

Formatting labels in chart

Hello,

I have one report with chart. And labels of X axis at this chart are too long. How can I format them to show, for example, only 3 first symbols of titles?
As far as I understand, to solve this problem I should use Format property. But I know only about formatting date and numbers, not strings.
Can you help me?

Thank you.

I have a similar problem. The labels are little too long and are shown alternatively on 2 different lines.

Is it possible to have the labels angled at 45 degrees or something?

Thanks for any help!!!

|||To shorten labels i use "substring" in the sql-query..

select label, substring(label,1,5) as shortlabel, value from table (MSSQL)
select label, substr(label,1,5) as shortlabel, value from table (Oracle)

|||

in the Label property do the following.

= Code.getThreeCharsFromMyLabel(Fields!LabelField.Value)

In the code section of the report you could write a function named "getThreeCharsFromMyLabel" that accepts the label string and returns a Substring of the label.

and thats it u get what u want...... :)

|||

Note: you could also just use the =Left(..., 3) function to get the first 3 characters: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctleft.asp

-- Robert

sql

Formatting data with FOR XML and sp_makewebtask?

Hi all,

I have a question about using FOR XML and sp_makewebtask. This (see below) is a simplified example where I have a table with 3 columns, the two first columns hold varius data and the third column (Subdata3) holds duplicated data.

My question is: Is it possible to transform the hierachy of data (see "Prefered XML output" for details) when transforming from SQL to XML using either FOR XML EXPLICIT or by using some sort of more advanced sp_makewebtask template (or some other technique perhaps)? And if, how? Any help welcome!

Cheers,
Christian

-------

-- sp_makewebtask --
EXEC sp_makewebtask
@.outputfile = c:\temp\output.xml,
@.query = 'SELECT Subdata1, Subdata2, Subdata3 FROM Data WHERE Subdata3 = X FOR XML AUTO, ELEMENTS',
@.templatefile ='c:\temp\template.tpl'

-- c:\temp\template.tpl --
<?xml version="1.0" encoding="UTF-8"?>
<Data>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</Data>

-- c:\temp\output.xml --
<?xml version="1.0" encoding="UTF-8"?>
<Data>
<Subdata>
<Subdata1>455</Subdata1>
<Subdata2>12312</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
<Subdata>
<Subdata1>435345</Subdata1>
<Subdata2>675</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
<Subdata>
<Subdata1>133323</Subdata1>
<Subdata2>976</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
</Data>

-- Prefered XML output --
<?xml version="1.0" encoding="UTF-8"?>
<Data Subdata3="1">
<Subdata>
<Subdata1>455</Subdata1>
<Subdata2>12312</Subdata2>
</Subdata>
<Subdata>
<Subdata1>435345</Subdata1>
<Subdata2>675</Subdata2>
</Subdata>
<Subdata>
<Subdata1>133323</Subdata1>
<Subdata2>976</Subdata2>
</Subdata>
</Data>Nevermind, solved it using FOR XML EXPLICIT. :D

/Christian

Formatting a field to spec in SQL

Can anyone tell me how to format a field to spec?

Example:

Select num_field from table = 12345

I want

Select Format(num_field,"0000000000") from table = 0000012345

or some equivalent. I know in Access you could do this using the Format function, but I cannot seem to find anything like this in SQL.

Thanks in advance,

Michael

Hi,

there is no format function in SQL Server, you will have to do it this way here:

LEFT('0000000000' + CONVERT(VARCHAR(10),num_field), 10)

That will give you the appropiate format.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jen,

Thanks for the response, it works with one small exception, I need to use the RIGHT function not the LEFT. But all is good, and it works...THANKS!

Michael

|||You are sure right, my mistake :-)

formating string to time

Hello,

I have the following question.

I have a report where i do a lot of time calculation

in one colomn i have values in seconds.

for example i have a total of 246 seconds

how can i format this to view as 00:04:06 or even 4 mins 6 secs

Vincent

Hello Vincent,

Try putting this in your textbox's expression...

=cStr(Floor(Fields!TotalSeconds.Value / 60)) + " mins "
+ cStr(Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60) + " secs"

Hope this helps.

Jarret

|||

Hello Jarret,

This works great.

Thank you.

Vincent

|||

Hi,

How can i add the hours also to it ?

I'm still really quite new to all this stuff, but i'm working on it.

Thanks

Vincent

|||

For hours, try this...

=cStr(Floor(Fields!TotalSeconds.Value / 3600)) + " hours "
+ cStr(Floor(Fields!TotalSeconds.Value / 60) - Floor(Fields!TotalSeconds.Value / 3600) * 60) + " mins "
+ cStr(Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60) + " secs"

Jarret

formating h:mm:ss in a report (SSRS2005)

Hello,

I'm trying to format a numeric value (in seconds) to the format

h:mm:ss (hours:minutes:seconds).

Example: 89 seconds should formated as 0:01:29

The value is not a date/time value - so I'm not able to use time formatting.

Is there a solution available - any ideas?

best regards

-flyall-

Hello flyall,

This should do what you want:

=Format(Floor(Fields!NumField.Value / 3600), "0")

+ ":"

+ Format(Floor(Fields!NumField.Value / 60) mod 60, "00")

+ ":"

+ Format(Floor(Fields!NumField.Value mod 60), "00")

Hope this helps.

Jarret

Formating column widths with SQLPlus

How do you set column widths to (20 for example) using SQLPlus.Originally posted by rwj7p
How do you set column widths to (20 for example) using SQLPlus.
For a numeric column:
COLUMN colname FORMAT 9999999999.999

For a varchar2 column:
COLUMN colname FORMAT A20

You can also define different default wisth for ALL number columns:

SET NUMWIDTH 20|||Thanks, just what I was needing

Format parameter date

Hello

My problem is with parmater format date, when i input for example the date format yyyy/mm/dd and then press in boton view report this foramt change to dd/mm/yyyy, this succed in all machines with all configuration in the regional configuration.

When i change this format in th configuration of reporting services.

Thank you

In your query add a convert to your parameter field.

convert(datetime, @.parameter, 111)

|||

Thank you for your request, but the problem isn't the database, i thnik the problem is the configuration of Reporting Services, because this software function with date format mm/dd/yyyy and i don't know when change this format. For example when i type in the paramater of the report 2006/08/06 (6 august 2006) and press the boton "show report" the screen refresh and then show 08/06/2006 (8 june 2006) and this i don't know why show. This is y problem.

Thank you

|||

The interpretation of the value typed in for a date in the report parameters area is based on the client browser locale. The display of the date within the body of the report is based on the language property of the report (which can be bound to the client language if the report developer wants it to).

|||Are you setting the various lanuage settings for Reporting Services? These settings are very configurable. See the following link for more information.

International Considerations for Reporting Services:
http://msdn2.microsoft.com/en-us/library/ms156493.aspx

Format of zeros

How would I clear up all the zeros in a report to either blanks or dashes?

Example:

With a standard format numbers:

Col A | Col B

--

1.00 | 0.00

I want to clean it up like in Excel's Cell format called "Accounting"?

Col A | Col B

--

1.00 | -

This makes it easy to read

You can use expression to show "-" conditionally, i.e.

=iif(Fields!ColB.Value = 0,"-",Fields!ColB.Value)

|||

You can use custom numeric format strings as well, for example:

=Fields!ColB.Value.ToString("#.00;(#.00);-")

Format of zeros

How would I clear up all the zeros in a report to either blanks or dashes?

Example:

With a standard format numbers:

Col A | Col B

--

1.00 | 0.00

I want to clean it up like in Excel's Cell format called "Accounting"?

Col A | Col B

--

1.00 | -

This makes it easy to read

You can use expression to show "-" conditionally, i.e.

=iif(Fields!ColB.Value = 0,"-",Fields!ColB.Value)

|||

You can use custom numeric format strings as well, for example:

=Fields!ColB.Value.ToString("#.00;(#.00);-")

Format for Parameters

Simple question - I have a date as a parameter. When I enter 12/1/2004 for
example and run the report, after the data is displayed, the parameter
displays "12/1/2004 12:00:00AM". Is there any way to pass the typical date
format like "MM/dd/yyyy" to the parameter so that it formats the date only?
thanks
billHi Bill,
Based on my knowledge, we are unable to format the
parameters. But I would like to perform further research
to confirm if this is possible. I will update you once I
have more information.
BTW: If you display the date in a text box in the Report
body, you can set the format via the text box Properties
dialog box.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Reply-To: <sutphinwb@.nospam.nospam>
>From: <sutphinwb@.nospam.nospam>
>Subject: Format for Parameters
>Date: Wed, 2 Feb 2005 00:37:39 -0500
>Lines: 10
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: ip24-250-32-203.ri.ri.cox.net
24.250.32.203
>Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.p
hx.gbl!TK2MSFTNGP09.phx.gbl
>Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:41614
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>Simple question - I have a date as a parameter. When I
enter 12/1/2004 for
>example and run the report, after the data is
displayed, the parameter
>displays "12/1/2004 12:00:00AM". Is there any way to
pass the typical date
>format like "MM/dd/yyyy" to the parameter so that it
formats the date only?
>thanks
>bill
>
>|||William is correct to the best of my knowledge as well... If you need pretty
formatting either use a string, check that it is a valid date, and format it
as you wish... You could also use an HTML page as a front end to the report,
formating date strings etc using html techniques, then passing the params to
the URL... ( I prefer the simpler string method if possible.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<sutphinwb@.nospam.nospam> wrote in message
news:es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl...
> Simple question - I have a date as a parameter. When I enter 12/1/2004
for
> example and run the report, after the data is displayed, the parameter
> displays "12/1/2004 12:00:00AM". Is there any way to pass the typical
date
> format like "MM/dd/yyyy" to the parameter so that it formats the date
only?
> thanks
> bill
>|||You can use a stored procedure to retrieve the data from the database. And
then you can format the parameter inside the stored procedure.
"Wayne Snyder" wrote:
> William is correct to the best of my knowledge as well... If you need pretty
> formatting either use a string, check that it is a valid date, and format it
> as you wish... You could also use an HTML page as a front end to the report,
> formating date strings etc using html techniques, then passing the params to
> the URL... ( I prefer the simpler string method if possible.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <sutphinwb@.nospam.nospam> wrote in message
> news:es9bQlOCFHA.1424@.TK2MSFTNGP09.phx.gbl...
> > Simple question - I have a date as a parameter. When I enter 12/1/2004
> for
> > example and run the report, after the data is displayed, the parameter
> > displays "12/1/2004 12:00:00AM". Is there any way to pass the typical
> date
> > format like "MM/dd/yyyy" to the parameter so that it formats the date
> only?
> >
> > thanks
> >
> > bill
> >
> >
>
>

Format Files - Bcp

Hi,
I want to write a BCP format file, which uses one data column Value to map to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MVThis type of functionality is best done with DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Yes you can define a fixed length file with fixed length fields.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:
> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> > Hi,
> >
> > I want to write a BCP format file, which uses one data column Value to map
> > to
> > Two Column Fields in the table.
> >
> > For Example:
> > Data File: Pin Value - "5600055"
> > Table Columns
> > 1) tmp_pin
> > 2) per_pin
> >
> > Is the above possible to specify in the format file? Does this flexibility
> > is provided by BCP Utility to load the data.
> >
> > Regards
> > Govardhan MV
> >
>
>|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
>> What are the datatypes and what are the widths, i.e. how do you map
>> the 5600055 to the two values?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> This type of functionality is best done with DTS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in
>> message news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>> Hi,
>> I want to write a BCP format file, which uses one data column Value
>> to map to
>> Two Column Fields in the table.
>> For Example:
>> Data File: Pin Value â' â'5600055â'
>> Table Columns
>> 1) tmp_pin
>> 2) per_pin
>> Is the above possible to specify in the format file? Does this
>> flexibility is provided by BCP Utility to load the data.
>> Regards
>> Govardhan MV|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
>> What are the datatypes and what are the widths, i.e. how do you map
>> the 5600055 to the two values?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> This type of functionality is best done with DTS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in
>> message news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>> Hi,
>> I want to write a BCP format file, which uses one data column Value
>> to map to
>> Two Column Fields in the table.
>> For Example:
>> Data File: Pin Value â' â'5600055â'
>> Table Columns
>> 1) tmp_pin
>> 2) per_pin
>> Is the above possible to specify in the format file? Does this
>> flexibility is provided by BCP Utility to load the data.
>> Regards
>> Govardhan MV

Format expressions(converting seconds to minutes)

can any one tell me how to change seconds to minutes by using if condition in rss...

for example i have value of seconds=50, minutes should come like this 50/60=0.833333.... i got the answer ... but the problem is how to roundoff or truncate the minutes to 0.83 or 0.84 from 0.8333333...

Thanks in anticipation

raj

using this expression i have got it ....

=iif( Fields!duration.Value=0,0,format(Fields!duration.Value/60,"0.00"))