Distributed Query Connection Issues

I have the following situation: I have a database on ServerA and a database
on ServerB. My client app connects to ServerA and uses a stored procedure to
retrieve a dataset from both ServerA and ServerB.
While developing the app (using SQL 2K Dev on my workstation), I used a
linked server approach. I linked ServerB and refered to the database as
ServerB.DBName.dbo.TableName and it worked fine.
I'd like to use trusted connections and my users will have SQL server access
to both servers.
Here is the problem:
If I install the app on another workstation (i.e. not on the one running
ServerA), the I get:
Msg 18452
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Using Query Analyser from the third workstation I can connect to both
serverA and ServerB using Windows Authentication, with both Named Pipes and
TCP/IP protocols.
What am I doing wrong?
Thank you very much for your help.
The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation
-Sue
On Mon, 10 Jan 2005 10:55:01 -0800, "Konstantin"
<Konstantin@.discussions.microsoft.com> wrote:

>I have the following situation: I have a database on ServerA and a database
>on ServerB. My client app connects to ServerA and uses a stored procedure to
>retrieve a dataset from both ServerA and ServerB.
>While developing the app (using SQL 2K Dev on my workstation), I used a
>linked server approach. I linked ServerB and refered to the database as
>ServerB.DBName.dbo.TableName and it worked fine.
>I'd like to use trusted connections and my users will have SQL server access
>to both servers.
>Here is the problem:
>If I install the app on another workstation (i.e. not on the one running
>ServerA), the I get:
>Msg 18452
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Using Query Analyser from the third workstation I can connect to both
>serverA and ServerB using Windows Authentication, with both Named Pipes and
>TCP/IP protocols.
>What am I doing wrong?
>Thank you very much for your help.

Distributed Query Connection Issues

I have the following situation: I have a database on ServerA and a database
on ServerB. My client app connects to ServerA and uses a stored procedure to
retrieve a dataset from both ServerA and ServerB.
While developing the app (using SQL 2K Dev on my workstation), I used a
linked server approach. I linked ServerB and refered to the database as
ServerB.DBName.dbo.TableName and it worked fine.
I'd like to use trusted connections and my users will have SQL server access
to both servers.
Here is the problem:
If I install the app on another workstation (i.e. not on the one running
ServerA), the I get:
Msg 18452
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Using Query Analyser from the third workstation I can connect to both
serverA and ServerB using Windows Authentication, with both Named Pipes and
TCP/IP protocols.
What am I doing wrong?
Thank you very much for your help.The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation
-Sue
On Mon, 10 Jan 2005 10:55:01 -0800, "Konstantin"
<Konstantin@.discussions.microsoft.com> wrote:

>I have the following situation: I have a database on ServerA and a database
>on ServerB. My client app connects to ServerA and uses a stored procedure t
o
>retrieve a dataset from both ServerA and ServerB.
>While developing the app (using SQL 2K Dev on my workstation), I used a
>linked server approach. I linked ServerB and refered to the database as
>ServerB.DBName.dbo.TableName and it worked fine.
>I'd like to use trusted connections and my users will have SQL server acces
s
>to both servers.
>Here is the problem:
>If I install the app on another workstation (i.e. not on the one running
>ServerA), the I get:
>Msg 18452
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Using Query Analyser from the third workstation I can connect to both
>serverA and ServerB using Windows Authentication, with both Named Pipes and
>TCP/IP protocols.
>What am I doing wrong?
>Thank you very much for your help.

Distributed App, Technology Question

Hi Everyone.

I am trying to get ideas for a possible technology we could use for a certain scenario.

In a nutshell we want to run a distributed application at hundred of remote stores, anything that happens at the store must be propagated to a central sort of hub server.

Now i have already thought about replication, but maintaining replcation across hundreds or even a thousand remote instances would be a nightmare.Also the App must work offline so if no link exists the app must still work and sort of reliably queue the transactions.

So i been experimenting with service broker, to write transactions, that then queue on to service broker to the central hub, therefore enabling remote stores to continue to work even if no comms exist between the remote app and the hub.

But also maintaining service broker at hundred of remote sites would be an administrative nightmare.

So basically i am trying to pick your brains out there, can anyone reccomend a possible technology out there worth looking at for this solution , the simpler the better, even if it simply pushes database somewhere , must it must be reliable and queue like in case comms are down.

Also perhaps there is some sort of .NET technology i am not aware of, that can run the .NET app at the store and the app will try propagate the transaction up to the hub, and will automatically queue them. I know am i grabbing at straws , but perhaps there is some sort of technology that i dont know of as a dba that could be great for this scenario ?

Any reccomendations just to look into would be greatly appreciated.

Thanx

Since this is an archtiecture question here is my "2"...

You could in theory leverage .net to stream records in a distributed transaction (via DTC) but I dont really see how this would be any simpler in terms of administration. Replication & service broker are actually excellent candidates for this solution and would be the two I would look at the most. The other issue here is latency, how long can it take for the records to be merged together? Service Broker I know is a very "fast" technology. I am not a BizTalk expert but you may want to consider it as well.

Derek

|||

You could also look at MSMQ, which in .Net is implemented in the System.Messaging namespace. What follows is a short description of the namespace via Object Explorer in VS2005:

The System.Messaging namespace provides classes that allow you to connect to, monitor, and administer message queues on the network and send, receive, or peek messages.

|||

Thanx Guys

As far as i know MSMQ is kinda being replaced by Service Broker, but that may purely be from a DB perspective not .NET.

Looks like Service Broker is the better option then, replication is not an option due to the offline capabilities required by this app, service broker ensures that all transaction can be queue and deleivered later, replication cannot garantee this as a connection the the distributor and susncibers is obviously required.

However i am concerned about the amount of setup and adminsitration that would be required at the hundred of remote instances to enable and maintain service broker.

Thanx

|||

Service Broker does not replace MSMQ, it is as you said it is merely a similiar option now available in the database. MSMQ and Service Broker are not the same in terms of their pros/cons. I built a real-time ETL system based upon Service Broker about 18 months ago now. Yukon had just entered Beta 2 whenever that was...Anyway, I ended up making friends with Roger Wolter, Service Broker PM and he kinda coached me though my leading the engieering effort with the new technology. http://blogs.msdn.com/rogerwolterblog/archive/2006/02/28/540803.aspx go there to read Roger's quick comparisons between the technologies.

As far as minimizing dev/admin time, you could build everything the same way as I did with the real time ETL system. For example, we always had an "in" queue, "out" queue, "failed" queue, the activation procs had the same structure etc...This way only the logic specific to the individual service is unique.

HTH,

Derek

|||

Hi Derek

Thank you for the feedback.

I would be very interested to discuss and idea with you if you have the time. Sounds like you know a lot about service broker, not many do. I have been experimenting with service broker in an idea based on a realtime tranactional retail system using service broker, primarily so that if comms went down the store could continue to transact even though communication the central loan system was not possible therefore the transaction would be delivered by service broker as soon as the comms were up again. Service broker would always be used to send the transactions realtime.

My biggest concern is the initial setup of hundreds of remote sites with endpoints to a single central hub. I am concerned about the robustness and high avalibilty of servcie broker, by that i mean i would hate to be faced everyday with the prospect of having to manually check x amount of remote sites and troubleshoot service broker issues at each remote instance. Also i am not sure if service broker architecture, in terms of scalabiltiy, is more of a db server to db server design, or like i said would a central hub single endpoint with hundreds of remote site initiator endpoints, be perfectly feasable?

In a nutshell i am going to try do data replication from remote sites via service broker to a central loan environment.

However if i could give you my email address or vica versa i would love to shoot the idea past you, basically i would just like a yes this could work or no this is not going to work for you viewpoint, it has been very difficult to find someone who knows enough about servoce broker to really comment.

Thanx

|||

sure, just go to my consulting site (i wont charge u lol) and select contact. www.sqlserverdbas.com. We can compare contrast and I may try to enlist Roger's input if he has the time.

|||Please mark a reply as an answer too. Service Broker is in my opinion the best new feature of sql 2005 for devs, SQLCLR or not.|||

Sounds like you have the basic issues identified. One that you may not have considered is the network connection from the remote sites. Service Broker, distributed transactions both require a TCP/IP connection which may or may not be possible with your network configuration. MSMQ normally uses TCP/IP also but it has an optional SOAP protocol. Replication can use HTTP in some circumstances also.

I agree the Service Broker is probably your best alternative if you can get it to work with your network configuration. You will probably want to look at using forwarding to set up some concentrator machines so you don't have the overhead of hundreds or thousands of TCP/IP connections into your central server. You also want to be sure to test with the predicted volume of replication messages to ensure your server can handle the load. Dialog lifetime management can also be an issue. Most of the samples floating around use one dialog for each message. While this makes for a simple sample, it may not be the most efficient way to handle things. You may want each remote system to open a limited number of permanent dialogs and reuse them instead of opening and closing dialogs continuously. There's a discussion of this issue here: http://blogs.msdn.com/rogerwolterblog/archive/2006/05/20/602938.aspx

For more information, try these blogs:

http://blogs.msdn.com/remusrusanu/

http://rushi.desai.name/Blog/tabid/54/BlogID/1/Default.aspx

http://blogs.msdn.com/rogerwolterblog/default.aspx

And this book:

http://www.amazon.com/gp/product/1932577270/sr=8-1/qid=1151000196/ref=pd_bbs_1/002-4995511-1360840?redirect=true&%5Fencoding=UTF8

|||

There you go dude, straight from the man himself :) If you still want to discuss more thats cool, just contact me. And by the way, if do decide to implement SB, buy that book. BOL is good too, but Roger's book is "it".

"D"

|||

Thanx Roger

I have already ordered that book, just waiting for it to arrive, yes the network is a major concern for me, however that is also why i am gonna try a few thigns out with SB, as i am not very familiar with messenging and queueing etc (I am more of a Database engine person) i am have ALOT of proof of concept to do. However my general understanding is that when the network is down , the initiator endpoints can still put things on the queue , i hope the central endpoint being "up" is not a must ?

Thanx again, after i have played around with some idea i may post back.

Thanx

|||Yes, when the connection is not available, sent messages will accumulate in the sys.transmission_queue until the connection is available again. Be sure to set your lifetime in the BEGIN DIALOG statement to a long enough value to last through network outages or just don't specify a lifetime and dialogs will last forever.

Distributed APP strategies-What to do where is killing me...

I just read this quote at DevX.Com
"It's generally better to use the stored procedure option to perform the
transaction against the database rather than initiate a transaction on a
Connection object. When possible, you should isolate transactions to the
data tier, because it's the DBMS that actually performs the transactions."
OK, that seems reasonbale to me. Also, it makes sense to me that Business
Rule edits should be performed in the Application tier.
Howver, what if the Business edit requires a DB lookup, should it be
performed in the database in a SP or in a Buiness Object on the app tier?
I expect the "correct" answer is in the App Tier? Hower, if the edit is
performed in the App tier and if it passes the edit, and a SP is called to
perform the update, it seems to me that unless we have placed the Business
Rule lookup AND the UPDATE in a TRANSACTION by intiatiating a Transaction on
the Connection object, that we cannot guarantee that at the time of the db
update, that the Business Rule edit check would still pass. Some other user
may have snuck in a performed an update in between the Business Rule check
on the application Tier and the actual update to the database!
I guess I need to understand more about LOCKING. If the records queried by
the Business Edit were still locked at the time of the update, we could
assume that the state of the database is still such that an update is still
permitted. I assumed that enclosing the Business edit and the update in a
transaction would ensure that.
Now my head starts hurting when I throw into the mix the fact that we are
using .NET Remoting (Well Known Single Call) STATELESS objects.
I understand that STATELESS remote objects allow for increase scalability
and load balancing, and so that is why this approach was probably taken.
Hwoever, since the objects ae stateless, each time we instantiate an object
to perform a database call, we are establishing a new connection. And this
presents the problem of how do you tie the Business Edit lookup and the Data
Update into a single transaction by initiating a Transaction on the
Connection object is a new Connection is established with each call to the
database!
Furthermore, if you have two business abojects, Account and User, and you
want to call the ACCOUNT.AddNewAccount method and the User.AddNewUser method
and have both operation performed in a transaction, how do you do this if a
each object is establishing its own connection!?
The answer, it seems to me, is that you can't, that you need to create a new
object to do both, but this throws away the ability to reuse existing
objects.
It seems that the more granular an Object is the more reusable it is, but by
using SingleCall Remoting we do not have the ability to tie multiple updates
into a transaction. Not to mention that the Account object need to perform
an BO edit before adding an Account (to make sure it doesnt already exist in
the db) and the User object needs to peform an edit before adding a user,
that this will require *TWO* round trips to the server, which hurts
performance. By writing a new object to do both at the same time, you
increase performance by making one trip by throw away reusability since most
of the time you will need to do one operation or the otehr, not both.
I could go on and on. I hope you were able to follw the jist it.
I wish you MVPs were sitting next to my cube.
Many thanks for all the help you've given me in just a short time..
Chad
"Chad" wrote:

> I just read this quote at DevX.Com
> "It's generally better to use the stored procedure option to perform the
> transaction against the database rather than initiate a transaction on a
> Connection object. When possible, you should isolate transactions to the
> data tier, because it's the DBMS that actually performs the transactions."
> OK, that seems reasonbale to me. Also, it makes sense to me that Business
> Rule edits should be performed in the Application tier.
> Howver, what if the Business edit requires a DB lookup, should it be
> performed in the database in a SP or in a Buiness Object on the app tier?
>
Thats where you need to the data to apply your rules, you're right..

> I expect the "correct" answer is in the App Tier? Hower, if the edit is
> performed in the App tier and if it passes the edit, and a SP is called to
> perform the update, it seems to me that unless we have placed the Business
> Rule lookup AND the UPDATE in a TRANSACTION by intiatiating a Transaction
on
> the Connection object, that we cannot guarantee that at the time of the db
> update, that the Business Rule edit check would still pass. Some other use
r
> may have snuck in a performed an update in between the Business Rule check
> on the application Tier and the actual update to the database!
This is a concurrency issue, I create a BINARY_CHECKSUM containing the rows
that will be updated when the data is loaded into the business object, it's
a
private readonly property, this gets passed back to the procedure that
updates if the checksum is different at the time of update then you know tha
t
the data has changed since the read, do whatever to handle it then.

> I guess I need to understand more about LOCKING. If the records queried by
> the Business Edit were still locked at the time of the update, we could
> assume that the state of the database is still such that an update is stil
l
> permitted. I assumed that enclosing the Business edit and the update in a
> transaction would ensure that.
The locking will only occur when you do the update.

> Now my head starts hurting when I throw into the mix the fact that we are
> using .NET Remoting (Well Known Single Call) STATELESS objects.
>
Don't let this confuse you these are totaly seperate techonologies, have you
looked at CSLA...? It's a very well thought out object oriented framework of
which there are many users...
http://groups.msn.com/CSLANET
http://www.apress.com/book/bookDisplay.html?bID=198
Also take a look at Ingo Rammers remoting book, very good stuff must have if
your doing .Net remoting.
http://www.apress.com/book/bookDisplay.html?bID=108

> I understand that STATELESS remote objects allow for increase scalability
> and load balancing, and so that is why this approach was probably taken.
> Hwoever, since the objects ae stateless, each time we instantiate an objec
t
> to perform a database call, we are establishing a new connection. And this
> presents the problem of how do you tie the Business Edit lookup and the Da
ta
> Update into a single transaction by initiating a Transaction on the
> Connection object is a new Connection is established with each call to the
> database!
> Furthermore, if you have two business abojects, Account and User, and you
> want to call the ACCOUNT.AddNewAccount method and the User.AddNewUser meth
od
> and have both operation performed in a transaction, how do you do this if
a
> each object is establishing its own connection!?
> The answer, it seems to me, is that you can't, that you need to create a n
ew
> object to do both, but this throws away the ability to reuse existing
> objects.
In these cases where you have more than one business object that you want to
enlist in the same transaction you might create a controller class that
orchestrates the transaction using the EnterpriseServices.ServicedComponent
functionality which is basically MTS for .Net. This is not an ADO.NET
transaction anymore, even though your still using an ADO transaction in your
business object and maybe even T-SQL transactions in your sp's. Nonetheless
they'll be controlled by the serviced component, keep in mind this creates a
LOT of overhead, don't do this unless you have to.

> It seems that the more granular an Object is the more reusable it is, but
by
> using SingleCall Remoting we do not have the ability to tie multiple updat
es
> into a transaction. Not to mention that the Account object need to perform
> an BO edit before adding an Account (to make sure it doesnt already exist
in
> the db) and the User object needs to peform an edit before adding a user,
> that this will require *TWO* round trips to the server, which hurts
> performance. By writing a new object to do both at the same time, you
> increase performance by making one trip by throw away reusability since mo
st
> of the time you will need to do one operation or the otehr, not both.
> I could go on and on. I hope you were able to follw the jist it.
> I wish you MVPs were sitting next to my cube.
> Many thanks for all the help you've given me in just a short time..
> Chad
>
>

Distribute app/database with SQL or Windows security?

This question is for anybody who has created & sold/distributed an
application that uses MSDE as its backend database.
I am converting my application which currently uses Access 2000 to MSDE
SP3a.
I already have the install process together, but I an in a quandry as to
what security method to use when installing.
If I use SQL security and set the SA password, am I looking for issues in
the future when a client might possibly purchase another program that uses
MSDE and won't install because I have set the SA password?
If I use Windows authentication, then I am leaving my database open to
anybody with Admin privliges.
I use information within a database table to license my program, so I'd like
to keep it secured.
Can anybody tell me what they do for their applications? Install with SQL
or Windows authentication?
TIA
Jim K.
As for other applications using your instance - the MSDE license only allows
your instance to be used by your application. If another application
installs its database on your instance of MSDE, then not only is it
violating the MSDE licensing but it is rather poor behavior, in my opinion.
Users with Administrative rights will always have total access to your MSDE
installation, whether you use integrated (Windows) authentication or not. If
you are concerned about the security of your data then you should implement
some form of encryption for the any data that you consider important
intellectual property or secure data.
With my application we encrypt any important data and implement in the
database a simple way of protecting the licensing the program. I don't go to
great extremes to prevent unlicensed clients from connecting to the
database - just enought to make it a hassle to try to break any system I
might have put in place.
Jim
"Jim K" <krusej@.megsinet.net> wrote in message
news:%235%23vNcuiEHA.3876@.TK2MSFTNGP12.phx.gbl...
> This question is for anybody who has created & sold/distributed an
> application that uses MSDE as its backend database.
> I am converting my application which currently uses Access 2000 to MSDE
> SP3a.
> I already have the install process together, but I an in a quandry as to
> what security method to use when installing.
> If I use SQL security and set the SA password, am I looking for issues in
> the future when a client might possibly purchase another program that uses
> MSDE and won't install because I have set the SA password?
> If I use Windows authentication, then I am leaving my database open to
> anybody with Admin privliges.
> I use information within a database table to license my program, so I'd
like
> to keep it secured.
> Can anybody tell me what they do for their applications? Install with SQL
> or Windows authentication?
> TIA
> Jim K.
>

Displaying Updated Columns

I want to use a gridview in my asp.net app to just show changes to one field in an employee table. I have a history table trigger on all fields already for other reasons, however for this purpose i want to be able to show the user what the column said before and after the change.

I read up on the columns_updated function and it seems like it could work however It seems there would be an easier way than figuring out what the bitmask is on my 14 column table for a change on the 3rd column?

Any ideas.Okay I think I already solved this using a stored procedure joining my employee table and employee history table.sql

Displaying Reports without Report Manager & Login

I'm having a hard time believing others have not ran into this issue.
Anyway...here it is:
I've got a pretty extensive web app. that we're using SQL Reporting Services
to fullfill the reporting requirements. (This is a shared server in a server
farm...I don't have the capabilities to change security settings on the
reporting server).
So, long story short...I'd like to display the reports to the users a single
report at a time, without the report manager capabilities. I've went through
the rendering process and it would work if that is the only way to do it, but
I'd rather show them the HTML toolbar the comes with reporting services and
let them interact with the report. I realize that I can do this just by
calling a URL but when I do that it asks for a username and password. So I
guess the bottom line question is how can I open the report via the URL
without asking me for the report manager username and password (without using
the reporting services render method).
Thanks in Advance!!This sounds like a security issue between domains/machines etc.
Does the user sign in on machine 1 and then click on a link to go to machine
2 that has a different username/password? If so the user will need to enter
their username/password to get on to machine 2.
There are some work arounds but that may cause security wholes for you.
What is the exact security scenario you have?
Craig
"Nick Stineman" <NickStineman@.discussions.microsoft.com> wrote in message
news:476573EC-A15B-4F36-B6EF-EFB4ABE15E9F@.microsoft.com...
> I'm having a hard time believing others have not ran into this issue.
> Anyway...here it is:
> I've got a pretty extensive web app. that we're using SQL Reporting
> Services
> to fullfill the reporting requirements. (This is a shared server in a
> server
> farm...I don't have the capabilities to change security settings on the
> reporting server).
> So, long story short...I'd like to display the reports to the users a
> single
> report at a time, without the report manager capabilities. I've went
> through
> the rendering process and it would work if that is the only way to do it,
> but
> I'd rather show them the HTML toolbar the comes with reporting services
> and
> let them interact with the report. I realize that I can do this just by
> calling a URL but when I do that it asks for a username and password. So
> I
> guess the bottom line question is how can I open the report via the URL
> without asking me for the report manager username and password (without
> using
> the reporting services render method).
> Thanks in Advance!!|||Exact Situation...
We use a shared web server host to host our web application. We've signed
up for SQL reporting services with the host. From what I can decipher with
my e-mails to the web hosting provider the reporting services resides on a
seperate machine as the website. The reporting services server requires SSL
encryption. It is when I try and access the report manager that I am asked
for a username and password. I've created a rendering capability that spits
my reports out as PDF files...this will work if required...but I'd rather
give the end user the functionality of the HTML toolbar located within
reporting services.
If our hosting provider has made a choice or installation configuration that
is causing issues accessing via a direct URL I guess I just need to know. I
can't believe that Microsoft would roll out a product that required
additional login to simply view a report.
Thanks!!
-Nick
"Craig" wrote:
> This sounds like a security issue between domains/machines etc.
> Does the user sign in on machine 1 and then click on a link to go to machine
> 2 that has a different username/password? If so the user will need to enter
> their username/password to get on to machine 2.
> There are some work arounds but that may cause security wholes for you.
> What is the exact security scenario you have?
> Craig
>
> "Nick Stineman" <NickStineman@.discussions.microsoft.com> wrote in message
> news:476573EC-A15B-4F36-B6EF-EFB4ABE15E9F@.microsoft.com...
> > I'm having a hard time believing others have not ran into this issue.
> > Anyway...here it is:
> >
> > I've got a pretty extensive web app. that we're using SQL Reporting
> > Services
> > to fullfill the reporting requirements. (This is a shared server in a
> > server
> > farm...I don't have the capabilities to change security settings on the
> > reporting server).
> >
> > So, long story short...I'd like to display the reports to the users a
> > single
> > report at a time, without the report manager capabilities. I've went
> > through
> > the rendering process and it would work if that is the only way to do it,
> > but
> > I'd rather show them the HTML toolbar the comes with reporting services
> > and
> > let them interact with the report. I realize that I can do this just by
> > calling a URL but when I do that it asks for a username and password. So
> > I
> > guess the bottom line question is how can I open the report via the URL
> > without asking me for the report manager username and password (without
> > using
> > the reporting services render method).
> >
> > Thanks in Advance!!
>
>|||This is an IIS security issue. I think the hosting provided doesn't allow
anonymous access to the report server.
Is the website your running public domain or do users have to sign in to
that as well?
Craig
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:AC213DC2-7DAA-4B9A-8688-FB92E22426C2@.microsoft.com...
> Exact Situation...
> We use a shared web server host to host our web application. We've signed
> up for SQL reporting services with the host. From what I can decipher
> with
> my e-mails to the web hosting provider the reporting services resides on a
> seperate machine as the website. The reporting services server requires
> SSL
> encryption. It is when I try and access the report manager that I am
> asked
> for a username and password. I've created a rendering capability that
> spits
> my reports out as PDF files...this will work if required...but I'd rather
> give the end user the functionality of the HTML toolbar located within
> reporting services.
> If our hosting provider has made a choice or installation configuration
> that
> is causing issues accessing via a direct URL I guess I just need to know.
> I
> can't believe that Microsoft would roll out a product that required
> additional login to simply view a report.
> Thanks!!
> -Nick
> "Craig" wrote:
>> This sounds like a security issue between domains/machines etc.
>> Does the user sign in on machine 1 and then click on a link to go to
>> machine
>> 2 that has a different username/password? If so the user will need to
>> enter
>> their username/password to get on to machine 2.
>> There are some work arounds but that may cause security wholes for you.
>> What is the exact security scenario you have?
>> Craig
>>
>> "Nick Stineman" <NickStineman@.discussions.microsoft.com> wrote in message
>> news:476573EC-A15B-4F36-B6EF-EFB4ABE15E9F@.microsoft.com...
>> > I'm having a hard time believing others have not ran into this issue.
>> > Anyway...here it is:
>> >
>> > I've got a pretty extensive web app. that we're using SQL Reporting
>> > Services
>> > to fullfill the reporting requirements. (This is a shared server in a
>> > server
>> > farm...I don't have the capabilities to change security settings on the
>> > reporting server).
>> >
>> > So, long story short...I'd like to display the reports to the users a
>> > single
>> > report at a time, without the report manager capabilities. I've went
>> > through
>> > the rendering process and it would work if that is the only way to do
>> > it,
>> > but
>> > I'd rather show them the HTML toolbar the comes with reporting services
>> > and
>> > let them interact with the report. I realize that I can do this just
>> > by
>> > calling a URL but when I do that it asks for a username and password.
>> > So
>> > I
>> > guess the bottom line question is how can I open the report via the URL
>> > without asking me for the report manager username and password (without
>> > using
>> > the reporting services render method).
>> >
>> > Thanks in Advance!!
>>

Displaying data from a certain date range

Hey all, hopefully this question is in the right spot. I'm writing a .NET app talking to a MS SQL 2000 DB. I have two date range input boxes, and I want to display the data (probably in a dataGrid) from those 2 certain dates. How do I go about this with my SQL server??

My DB table has a date field that I would use to search for the data between those two user specified dates. Any tips, examples, etc. would be greatly appreciated!!!Hows about...

USE Northwind
GO

-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0|||Okay, now whats up with this DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

What am I doing with my Date1.text and Date2.text input boxes.. sorry, I'm having trouble converting the code some.|||That's TSQL

The DECALRE and SELECT is to mimic the values in your text box...

Do you have sql server client tools installed?

Query Analyzer?

Your best bet would be to call a stored procedure...|||Yes, I have those tools installed, but I really don't know how to use them to my benefit.... :(|||Puttin this code in the Query analyzer shows the DATEDIFF in pink, and some of the code after that grey... I'm guessing something else needs to be used here?|||Hows about...

USE Northwind
GO

-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0

Brett, I am having a similar problem...can't seem to use datetime parameters with default values in a stored procedure.

This is the code I used. Basically, trying to default dates on the Begin and End Dates for the query...but when I run it, always get this error
'Syntax error converting datetime from character string.'

Is the problem in the default values or the datediff functions? Then is the solution to format the dates differently or use a cast/convert function?

Thanks for the help

Alex

CREATE PROCEDURE dbo.usp_TempTest
@.BeginDate DateTime = '11/1/2004',
@.EndDate DateTime = getdate

AS

SELECT dbo.vw_BasicAuditDetails_Complete.*
FROM dbo.vw_BasicAuditDetails_Complete
WHERE (DATEDIFF(d, @.BeginDate, Audit_TM) > 0)
AND (DATEDIFF(d, Audit_TM, @.EndDate) > 0)
GO|||Has anyone cut and pasted my code in to query analyzer and executed it?|||That's what I was trying to do earlier.... :confused: I'm not a pro with the analyzer though..|||Well, if you highlighted the text, and pasted it in to a QA (Query Analyzer) window, and the typed [CTRL]+E, the code would execut and give you this

OrderDate
---------------- ---- ----
1996-09-02 00:00:00.000 1 28
1996-09-03 00:00:00.000 2 27
1996-09-04 00:00:00.000 3 26
1996-09-05 00:00:00.000 4 25
1996-09-06 00:00:00.000 5 24
1996-09-09 00:00:00.000 8 21
1996-09-09 00:00:00.000 8 21
1996-09-10 00:00:00.000 9 20
1996-09-11 00:00:00.000 10 19
1996-09-12 00:00:00.000 11 18
1996-09-13 00:00:00.000 12 17
1996-09-16 00:00:00.000 15 14
1996-09-17 00:00:00.000 16 13
1996-09-18 00:00:00.000 17 12
1996-09-19 00:00:00.000 18 11
1996-09-20 00:00:00.000 19 10
1996-09-20 00:00:00.000 19 10
1996-09-23 00:00:00.000 22 7
1996-09-24 00:00:00.000 23 6
1996-09-25 00:00:00.000 24 5
1996-09-26 00:00:00.000 25 4
1996-09-27 00:00:00.000 26 3
1996-09-30 00:00:00.000 29 0

Which is the range of data from the sample database Northwinds Tables Orders...The USE Statement should have brought you there.

Isn't that what you want?|||OK Ok, thanks! It is working now in Northwind... :)

Some suggested I use a Stored Procedure for doing this...

""You'll want to create a Stored Procedure in the SQL Server and then pass it's result into a DataSet to use a the Source for your DataGrid.

Try a Stored Procedure like this, Changing table and field names appropriately:
Code:
------------------------

CREATE PROCEDURE sp_BetweenDates ( @.StartDate DATETIME, @.EndDate DATETIME ) AS SELECT * FROM YOURTABLENAMEHERE WHERE [YOURDATEFIELDNAMEHERE] Between @.StartDate And @.EndDate;GO
------------------------

Then you would just pass the two dates as parameters to the Stored Procedure.""

What do you think of something like this?|||Okay, i have contrusted this query that gives me what I want...

USE billing1SQL2
GO

SELECT Hours.Employ#, Hours.Purchord, Hours.Datewrk, Hours.Hourswrk, Hours.typewrk, Hours.formwrk, Hours.class, Hours.brate, PurchaseOrder.Descr, Employee.Lastname, Employee.Firstname, Employee.[Employ#] AS Expr1, Hours.[Ticket#], PurchaseOrder.Purchord AS Expr2 FROM Hours As Hours INNER JOIN PurchaseOrder As PurchaseOrder ON Hours.Purchord = PurchaseOrder.Purchord INNER JOIN Employee As Employee ON Hours.[Employ#] = Employee.[Employ#] WHERE Hours.Datewrk between '6/15/2004' And '6/28/2004' ORDER BY Hours.Datewrk, Employee.Lastname

I just need to figure out how to use it with VB.Net the best way. Suggestions?|||Long answer:
I would start from:
System.Data.SqlClient Namespace (help is available with VB.Net)
Then I would take a class or read a book about T-SQL
Same thing for SQL server.

Short answer:
Public Sub ReadMyData(myConnString As String)
Dim mySelectQuery As String = "SELECT OrderID, Customer FROM Orders"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Try
While myReader.Read()
Console.WriteLine((myReader.GetInt32(0).ToString & ", " & myReader.GetString(1)))
End While
Finally
' always call Close when done reading.
myReader.Close()
' always call Close when done reading.
myConnection.Close()
End Try
End Sub 'ReadMyData
(source: VB.Net help)

good luck!|||In your VB code, you can create your query like this

dim str1 as string

str1 = "exec sp_between_dates '" & Format(Text1.text, "mm/dd/yy") & _
"','" & Format(Text2.text, "mm/dd/yy") & "'"

Roshmi Choudhury|||Okay, thank you! soo I have this stored procedure:

CREATE PROCEDURE sp_BetweenDates (@.StartDate DATETIME, @.EndDate DATETIME)
AS
SELECT *
FROM Hours WHERE [DateWrk] Between @.StartDate And @.EndDate;
GO

..and this in VB.net...

Dim str1 As String

str1 = "exec sp_BetweenDates '" & Format(txtDate1.Text, "mm/dd/yy") & _
"','" & Format(txtDate2.Text, "mm/dd/yy") & "'"

Do I need to put my txtDates.text into a variable/string "StartDate" and "EndDate" and put that in my str call, like this... ??

str1 = "exec sp_BetweenDates '" & Format(StartDate, "mm/dd/yy")

I'm just unclear how the best way to get the actual date from my text box, to the stored procedure works...

Displaying customer data

Hi all,
I need to design a solution that will display customer data from Sql
Server tables in an Internet Explorer tab of a third party app, e.g. the
third party app has a tab that can display web pages.
The third party app display one customer record at a time. I need to
display Payment Transaction records for the current customer(linked by
CustomerNo field) in the Internet Explorer tab. There are six types of
Payment Transaction records. I need to display all Payment transaction
records for the current customer, the subtotal for each transaction type and
the total ampont for all transactions.
What are the best tools for implementing this solution? Shoule I use
DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
also point me to any examples on the web or any tutorial.
Thanks in advance.Hi,
Take a look at this page
http://office.microsoft.com/en-gb/a...1153831033.aspx
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uVMSYfaXGHA.3972@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to design a solution that will display customer data from Sql
> Server tables in an Internet Explorer tab of a third party app, e.g. the
> third party app has a tab that can display web pages.
> The third party app display one customer record at a time. I need to
> display Payment Transaction records for the current customer(linked by
> CustomerNo field) in the Internet Explorer tab. There are six types of
> Payment Transaction records. I need to display all Payment transaction
> records for the current customer, the subtotal for each transaction type
> and the total ampont for all transactions.
> What are the best tools for implementing this solution? Shoule I use
> DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
> also point me to any examples on the web or any tutorial.
> Thanks in advance.
>

Displaying customer data

Hi all,
I need to design a solution that will display customer data from Sql
Server tables in an Internet Explorer tab of a third party app, e.g. the
third party app has a tab that can display web pages.
The third party app display one customer record at a time. I need to
display Payment Transaction records for the current customer(linked by
CustomerNo field) in the Internet Explorer tab. There are six types of
Payment Transaction records. I need to display all Payment transaction
records for the current customer, the subtotal for each transaction type and
the total ampont for all transactions.
What are the best tools for implementing this solution? Shoule I use
DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
also point me to any examples on the web or any tutorial.
Thanks in advance.Hi,
Take a look at this page
http://office.microsoft.com/en-gb/assistance/ha011153831033.aspx
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uVMSYfaXGHA.3972@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to design a solution that will display customer data from Sql
> Server tables in an Internet Explorer tab of a third party app, e.g. the
> third party app has a tab that can display web pages.
> The third party app display one customer record at a time. I need to
> display Payment Transaction records for the current customer(linked by
> CustomerNo field) in the Internet Explorer tab. There are six types of
> Payment Transaction records. I need to display all Payment transaction
> records for the current customer, the subtotal for each transaction type
> and the total ampont for all transactions.
> What are the best tools for implementing this solution? Shoule I use
> DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
> also point me to any examples on the web or any tutorial.
> Thanks in advance.
>

Displaying customer data

Hi all,
I need to design a solution that will display customer data from Sql
Server tables in an Internet Explorer tab of a third party app, e.g. the
third party app has a tab that can display web pages.
The third party app display one customer record at a time. I need to
display Payment Transaction records for the current customer(linked by
CustomerNo field) in the Internet Explorer tab. There are six types of
Payment Transaction records. I need to display all Payment transaction
records for the current customer, the subtotal for each transaction type and
the total ampont for all transactions.
What are the best tools for implementing this solution? Shoule I use
DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
also point me to any examples on the web or any tutorial.
Thanks in advance.Hi,
Take a look at this page
http://office.microsoft.com/en-gb/a...1153831033.aspx
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uVMSYfaXGHA.3972@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to design a solution that will display customer data from Sql
> Server tables in an Internet Explorer tab of a third party app, e.g. the
> third party app has a tab that can display web pages.
> The third party app display one customer record at a time. I need to
> display Payment Transaction records for the current customer(linked by
> CustomerNo field) in the Internet Explorer tab. There are six types of
> Payment Transaction records. I need to display all Payment transaction
> records for the current customer, the subtotal for each transaction type
> and the total ampont for all transactions.
> What are the best tools for implementing this solution? Shoule I use
> DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
> also point me to any examples on the web or any tutorial.
> Thanks in advance.
>

Displaying customer data

Hi all,
I need to design a solution that will display customer data from Sql
Server tables in an Internet Explorer tab of a third party app, e.g. the
third party app has a tab that can display web pages.
The third party app display one customer record at a time. I need to
display Payment Transaction records for the current customer(linked by
CustomerNo field) in the Internet Explorer tab. There are six types of
Payment Transaction records. I need to display all Payment transaction
records for the current customer, the subtotal for each transaction type and
the total ampont for all transactions.
What are the best tools for implementing this solution? Shoule I use
DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
also point me to any examples on the web or any tutorial.
Thanks in advance.Hi,
Take a look at this page
http://office.microsoft.com/en-gb/assistance/ha011153831033.aspx
"helpful sql" <nospam@.stopspam.com> wrote in message
news:uVMSYfaXGHA.3972@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to design a solution that will display customer data from Sql
> Server tables in an Internet Explorer tab of a third party app, e.g. the
> third party app has a tab that can display web pages.
> The third party app display one customer record at a time. I need to
> display Payment Transaction records for the current customer(linked by
> CustomerNo field) in the Internet Explorer tab. There are six types of
> Payment Transaction records. I need to display all Payment transaction
> records for the current customer, the subtotal for each transaction type
> and the total ampont for all transactions.
> What are the best tools for implementing this solution? Shoule I use
> DbNetGrid, Reporting Services, Crystal Reports or any other tool? Please
> also point me to any examples on the web or any tutorial.
> Thanks in advance.
>