Profile value + Sql Value, a login problem

Hi all. Quick question. I'm using VS2005, C#, aspx page.

I'm creating a Profile to store login and password. That part is working... I can call the values (and display them) using this code <%= Profile.login %> and <%=Profile.password %
Now I want to create a Grid View that will connect to the SQL db, see if the login and password value stored in the Profile match that of ones in the SQL db.

So if the profile is login: bob password: dog, the grid view will output all application ID numbers associated with the bob and dog. Here is the SQL code...trying to use the <%=Profile.login %> as a filter on the login and password doesn't seem to work...

Can anyone tell me what I'm doing wrong? How can I reference a value in the Profile within an SQL statement?

SELECT ApplicationStatus.Description, Customer.CustomerName, Application.ApplicationDate
FROM Application INNER JOIN
ApplicationStatus ON Application.ApplicationStatusID = ApplicationStatus.ApplicationStatusID INNER JOIN
Customer ON Application.ApplicationID = Customer.ApplicationID INNER JOIN
[User] ON Application.DealerId = [User].UserId
WHERE ([User].LoginId = '<%= Profile.login %>') AND ([User].LoginPwd = '<%= Profile.password %>') AND (ApplicationStatus.Description = 'Pending')

Try getting the value into a variable and use the variable in the SQL.

SELECT ApplicationStatus.Description, Customer.CustomerName, Application.ApplicationDate
FROM Application INNER JOIN
ApplicationStatus ON Application.ApplicationStatusID = ApplicationStatus.ApplicationStatusID INNER JOIN
Customer ON Application.ApplicationID = Customer.ApplicationID INNER JOIN
[User] ON Application.DealerId = [User].UserId
WHERE ([User].LoginId = @.login) AND ([User].LoginPwd = @.pwd) AND (ApplicationStatus.Description = 'Pending')

Then add the parameters to the command object and set their values appropriately.

|||I agree with ndinakar.

Producing several letters based on the results of a query

I have created a single page letter using a number of text boxes and one
table, which is populated with static text along with some data from a single
row results set. This works perfectly when the results set only contains one
row, but when there are multiple data rows, the report still only contains
one page, whereas I expected there to be a page for every row.
A simple way to reproduce this is to create a report with a single text box
containing the value of one column from a query that returns multiple rows.
This results in a one page report, rather than a page for each row returned.
I'd be grateful if someone can help with this - I'm hoping there's a simple
solution, like setting a 'repeating region' property.
By the way, I've tried creating a single column table in the hope I could
put the entire contents of the letter into a cell of the table and that the
table row (and therefore the letter) would then repeat for each row of data,
but it doesn't appear to be possible to fix the size of a table row and have
multiple text boxes (and a table) inside it.
Thanks in advance,
David.Hey David,
If I understand you correctly, your trying to make, basically, a form
letter, that populates the variables from a query. For example
This is my test letter to "DAVID".
Where "DAVID" is generated from column/row in your Dataset. Why can't you
make a single cell table with all the text as you want it, except insert the
Field references you need (instead of textboxes)? For Example, the cell
would look like:
= "This is my test letter to " & Fields!ContactName.Value & ". " &
Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
etc...
Once this is done, you choose the "Group" properties and place a page break
after each group (or row in this case). This creates a new letter, each on
its own pages, for each row returned.
Michael C
"David C" wrote:
> I have created a single page letter using a number of text boxes and one
> table, which is populated with static text along with some data from a single
> row results set. This works perfectly when the results set only contains one
> row, but when there are multiple data rows, the report still only contains
> one page, whereas I expected there to be a page for every row.
> A simple way to reproduce this is to create a report with a single text box
> containing the value of one column from a query that returns multiple rows.
> This results in a one page report, rather than a page for each row returned.
> I'd be grateful if someone can help with this - I'm hoping there's a simple
> solution, like setting a 'repeating region' property.
> By the way, I've tried creating a single column table in the hope I could
> put the entire contents of the letter into a cell of the table and that the
> table row (and therefore the letter) would then repeat for each row of data,
> but it doesn't appear to be possible to fix the size of a table row and have
> multiple text boxes (and a table) inside it.
> Thanks in advance,
> David.|||Thanks Michael - it was the grouping that I needed. I actually stumbled
across the same answer last night in a TechNet article
(http://technet.microsoft.com/en-us/library/ms155816.aspx).
I put a List in first and then added a single column Table to it. I then
created a group expression on the list in Properties - General - Edit Details
Group - General, as follows: -
=Ceiling(RowNumber(Nothing)/1)
I also set 'Page break at end'.
Is this what you meant?
My problem now is that I want to make some of the text bold and I thought
I'd be able to do this using the Format command - something like Format("some
text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
that?
Thanks again.
David.
"Michael C" wrote:
> Hey David,
> If I understand you correctly, your trying to make, basically, a form
> letter, that populates the variables from a query. For example
> This is my test letter to "DAVID".
> Where "DAVID" is generated from column/row in your Dataset. Why can't you
> make a single cell table with all the text as you want it, except insert the
> Field references you need (instead of textboxes)? For Example, the cell
> would look like:
> = "This is my test letter to " & Fields!ContactName.Value & ". " &
> Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> etc...
> Once this is done, you choose the "Group" properties and place a page break
> after each group (or row in this case). This creates a new letter, each on
> its own pages, for each row returned.
> Michael C
> "David C" wrote:
> > I have created a single page letter using a number of text boxes and one
> > table, which is populated with static text along with some data from a single
> > row results set. This works perfectly when the results set only contains one
> > row, but when there are multiple data rows, the report still only contains
> > one page, whereas I expected there to be a page for every row.
> >
> > A simple way to reproduce this is to create a report with a single text box
> > containing the value of one column from a query that returns multiple rows.
> > This results in a one page report, rather than a page for each row returned.
> >
> > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > solution, like setting a 'repeating region' property.
> >
> > By the way, I've tried creating a single column table in the hope I could
> > put the entire contents of the letter into a cell of the table and that the
> > table row (and therefore the letter) would then repeat for each row of data,
> > but it doesn't appear to be possible to fix the size of a table row and have
> > multiple text boxes (and a table) inside it.
> >
> > Thanks in advance,
> >
> > David.|||Hey David,
Unfortunately I do not think there is a way, and if there i do not know
it. I had asked the forum recently about 2 fonts, 1 control but got no
responses.
You have done basically what I was describing. Glad it (sort of) worked for
you.
Michael
"David C" wrote:
> Thanks Michael - it was the grouping that I needed. I actually stumbled
> across the same answer last night in a TechNet article
> (http://technet.microsoft.com/en-us/library/ms155816.aspx).
> I put a List in first and then added a single column Table to it. I then
> created a group expression on the list in Properties - General - Edit Details
> Group - General, as follows: -
> =Ceiling(RowNumber(Nothing)/1)
> I also set 'Page break at end'.
> Is this what you meant?
> My problem now is that I want to make some of the text bold and I thought
> I'd be able to do this using the Format command - something like Format("some
> text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
> that?
> Thanks again.
> David.
>
> "Michael C" wrote:
> > Hey David,
> > If I understand you correctly, your trying to make, basically, a form
> > letter, that populates the variables from a query. For example
> >
> > This is my test letter to "DAVID".
> >
> > Where "DAVID" is generated from column/row in your Dataset. Why can't you
> > make a single cell table with all the text as you want it, except insert the
> > Field references you need (instead of textboxes)? For Example, the cell
> > would look like:
> >
> > = "This is my test letter to " & Fields!ContactName.Value & ". " &
> > Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> > etc...
> >
> > Once this is done, you choose the "Group" properties and place a page break
> > after each group (or row in this case). This creates a new letter, each on
> > its own pages, for each row returned.
> >
> > Michael C
> >
> > "David C" wrote:
> >
> > > I have created a single page letter using a number of text boxes and one
> > > table, which is populated with static text along with some data from a single
> > > row results set. This works perfectly when the results set only contains one
> > > row, but when there are multiple data rows, the report still only contains
> > > one page, whereas I expected there to be a page for every row.
> > >
> > > A simple way to reproduce this is to create a report with a single text box
> > > containing the value of one column from a query that returns multiple rows.
> > > This results in a one page report, rather than a page for each row returned.
> > >
> > > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > > solution, like setting a 'repeating region' property.
> > >
> > > By the way, I've tried creating a single column table in the hope I could
> > > put the entire contents of the letter into a cell of the table and that the
> > > table row (and therefore the letter) would then repeat for each row of data,
> > > but it doesn't appear to be possible to fix the size of a table row and have
> > > multiple text boxes (and a table) inside it.
> > >
> > > Thanks in advance,
> > >
> > > David.|||Hi Michael,
The text I wanted to be in bold was on a separate line, so in the end I just
put in a separate row in the table and set the row to bold. This worked fine,
presumably because the List control was handling the grouping.
Thanks again for your help.
David.
"Michael C" wrote:
> Hey David,
> Unfortunately I do not think there is a way, and if there i do not know
> it. I had asked the forum recently about 2 fonts, 1 control but got no
> responses.
> You have done basically what I was describing. Glad it (sort of) worked for
> you.
> Michael
> "David C" wrote:
> > Thanks Michael - it was the grouping that I needed. I actually stumbled
> > across the same answer last night in a TechNet article
> > (http://technet.microsoft.com/en-us/library/ms155816.aspx).
> >
> > I put a List in first and then added a single column Table to it. I then
> > created a group expression on the list in Properties - General - Edit Details
> > Group - General, as follows: -
> >
> > =Ceiling(RowNumber(Nothing)/1)
> >
> > I also set 'Page break at end'.
> >
> > Is this what you meant?
> >
> > My problem now is that I want to make some of the text bold and I thought
> > I'd be able to do this using the Format command - something like Format("some
> > text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
> > that?
> >
> > Thanks again.
> >
> > David.
> >
> >
> >
> > "Michael C" wrote:
> >
> > > Hey David,
> > > If I understand you correctly, your trying to make, basically, a form
> > > letter, that populates the variables from a query. For example
> > >
> > > This is my test letter to "DAVID".
> > >
> > > Where "DAVID" is generated from column/row in your Dataset. Why can't you
> > > make a single cell table with all the text as you want it, except insert the
> > > Field references you need (instead of textboxes)? For Example, the cell
> > > would look like:
> > >
> > > = "This is my test letter to " & Fields!ContactName.Value & ". " &
> > > Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> > > etc...
> > >
> > > Once this is done, you choose the "Group" properties and place a page break
> > > after each group (or row in this case). This creates a new letter, each on
> > > its own pages, for each row returned.
> > >
> > > Michael C
> > >
> > > "David C" wrote:
> > >
> > > > I have created a single page letter using a number of text boxes and one
> > > > table, which is populated with static text along with some data from a single
> > > > row results set. This works perfectly when the results set only contains one
> > > > row, but when there are multiple data rows, the report still only contains
> > > > one page, whereas I expected there to be a page for every row.
> > > >
> > > > A simple way to reproduce this is to create a report with a single text box
> > > > containing the value of one column from a query that returns multiple rows.
> > > > This results in a one page report, rather than a page for each row returned.
> > > >
> > > > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > > > solution, like setting a 'repeating region' property.
> > > >
> > > > By the way, I've tried creating a single column table in the hope I could
> > > > put the entire contents of the letter into a cell of the table and that the
> > > > table row (and therefore the letter) would then repeat for each row of data,
> > > > but it doesn't appear to be possible to fix the size of a table row and have
> > > > multiple text boxes (and a table) inside it.
> > > >
> > > > Thanks in advance,
> > > >
> > > > David.

Producing reports on a web page

Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
reading up on MS SQL Reporting Services and from what I can see they offer a
report repository as well as a a web based portal for downloading the reports.
The company I work for is currently using the following:
â?¢ Webserver running IIS
â?¢ SQL Server 2000
â?¢ Coldfusion MX 7.0 Server
â?¢ Crystal Enterprise Server 10.0
The current set-up we have is to produce the report in a web page (not
necessarily through a proprietary portal or anything like that).
Crystal Reports no longer supports this functionality in version 11. Does
anyone here know if it's possible for me to be able to create and produce a
report and then display it on a web page using Cold Fusion? This would
include being able to create a subreport and passing it parameters with
stored procedures.
Thanks!
ToddNot sure how Cold Fusion works but there are two ways you can integrate with
Report Services (assuming you are not using the Portal it ships with). One
is to use URL integration and the other is web services. With VS 2005 there
are two new controls (webform and winform) that integrate with RS 2005 using
webservices. Very good and very tight integration. They require 2.0
framework.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
> Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
> reading up on MS SQL Reporting Services and from what I can see they offer
> a
> report repository as well as a a web based portal for downloading the
> reports.
> The company I work for is currently using the following:
> . Webserver running IIS
> . SQL Server 2000
> . Coldfusion MX 7.0 Server
> . Crystal Enterprise Server 10.0
> The current set-up we have is to produce the report in a web page (not
> necessarily through a proprietary portal or anything like that).
> Crystal Reports no longer supports this functionality in version 11. Does
> anyone here know if it's possible for me to be able to create and produce
> a
> report and then display it on a web page using Cold Fusion? This would
> include being able to create a subreport and passing it parameters with
> stored procedures.
>
> Thanks!
> Todd|||Thanks Bruce, I really appreciate the response.
This is how they currently do it:
Can you tell me if it is at all possible to do this with Microsoft Reporting
services? They are asking me to do the research and find out if MS SQL is a
viable solution.
-----
This document has been created demonstrating how we use Crystal Reports in
real life for reporting. It would be great if Cold Fusion reports could
produce the same result in the near future. Not only would it relive us of
many yearly fees from Business Objects, but it would rededicate our future to
ColdFusion and Macromedia/Adobe.
We currently use Crystal Reports Developer 10 and XI to develop reports that
get generated and displayed in our software on the fly. This software is
driven by ColdFusion MX 7.0. The reports are built upon stored procedures
that accept variables and produce different results.
Crystal Developer lets you browse the available stored procedures and select
the one you would like to use:
After you select which stored procedure you would like to use, the next
screen automatically asks you for some sample data to pass to the SP
variables:
Once the report has been attached to the stored procedure, the rest is just
creating the report. The field explorer on the shows which fields are
available from the stored procedure:
Fields are dragged on and the report is saved. Displaying the report can be
done by using some simple javascript. So we have the flexibility to have
whatever we want on the screen, and the report to be â'embeddedâ' in our
application.
We created a header and a footer to wrap around the parameters because those
donâ't change.
In our attempts to move over to the early version of ColdFusion reports,
this process has been real clumsy. The stored procedures donâ't show up on
the database browser and the only way we can get them to execute is using the
exec <stored procedure> in the query window. You can forget about passing
variables to it. It would be great if a similar process could be duplicated
with ColdFusion reports.
Thanks!!!
Todd
"Bruce L-C [MVP]" wrote:
> Not sure how Cold Fusion works but there are two ways you can integrate with
> Report Services (assuming you are not using the Portal it ships with). One
> is to use URL integration and the other is web services. With VS 2005 there
> are two new controls (webform and winform) that integrate with RS 2005 using
> webservices. Very good and very tight integration. They require 2.0
> framework.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
> news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
> > Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
> > reading up on MS SQL Reporting Services and from what I can see they offer
> > a
> > report repository as well as a a web based portal for downloading the
> > reports.
> >
> > The company I work for is currently using the following:
> >
> > . Webserver running IIS
> > . SQL Server 2000
> > . Coldfusion MX 7.0 Server
> > . Crystal Enterprise Server 10.0
> >
> > The current set-up we have is to produce the report in a web page (not
> > necessarily through a proprietary portal or anything like that).
> >
> > Crystal Reports no longer supports this functionality in version 11. Does
> > anyone here know if it's possible for me to be able to create and produce
> > a
> > report and then display it on a web page using Cold Fusion? This would
> > include being able to create a subreport and passing it parameters with
> > stored procedures.
> >
> >
> > Thanks!
> >
> > Todd
>
>|||The process here is just how the report is created by the developer? I.e.
this is not happening by an end user is it?
What you would do with RS is to create the report using your stored
procedures. SP are fully supported as long as the SP returns a single
resultset. Multiple resultsets are not supported. Once you have the report
working then you use either URL integration or webservices and integrate it
with your web pages.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Todd Jaspers" <ToddJaspers@.discussions.microsoft.com> wrote in message
news:8E94C9CC-0C74-4F60-AA08-C02CA08C71DB@.microsoft.com...
> Thanks Bruce, I really appreciate the response.
> This is how they currently do it:
> Can you tell me if it is at all possible to do this with Microsoft
> Reporting
> services? They are asking me to do the research and find out if MS SQL is
> a
> viable solution.
>
> -----
> This document has been created demonstrating how we use Crystal Reports in
> real life for reporting. It would be great if Cold Fusion reports could
> produce the same result in the near future. Not only would it relive us
> of
> many yearly fees from Business Objects, but it would rededicate our future
> to
> ColdFusion and Macromedia/Adobe.
> We currently use Crystal Reports Developer 10 and XI to develop reports
> that
> get generated and displayed in our software on the fly. This software is
> driven by ColdFusion MX 7.0. The reports are built upon stored procedures
> that accept variables and produce different results.
> Crystal Developer lets you browse the available stored procedures and
> select
> the one you would like to use:
> After you select which stored procedure you would like to use, the next
> screen automatically asks you for some sample data to pass to the SP
> variables:
> Once the report has been attached to the stored procedure, the rest is
> just
> creating the report. The field explorer on the shows which fields are
> available from the stored procedure:
> Fields are dragged on and the report is saved. Displaying the report can
> be
> done by using some simple javascript. So we have the flexibility to have
> whatever we want on the screen, and the report to be "embedded" in our
> application.
> We created a header and a footer to wrap around the parameters because
> those
> don't change.
> In our attempts to move over to the early version of ColdFusion reports,
> this process has been real clumsy. The stored procedures don't show up on
> the database browser and the only way we can get them to execute is using
> the
> exec <stored procedure> in the query window. You can forget about passing
> variables to it. It would be great if a similar process could be
> duplicated
> with ColdFusion reports.
>
> Thanks!!!
> Todd
>
> "Bruce L-C [MVP]" wrote:
>> Not sure how Cold Fusion works but there are two ways you can integrate
>> with
>> Report Services (assuming you are not using the Portal it ships with).
>> One
>> is to use URL integration and the other is web services. With VS 2005
>> there
>> are two new controls (webform and winform) that integrate with RS 2005
>> using
>> webservices. Very good and very tight integration. They require 2.0
>> framework.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
>> news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
>> > Hi, I'm a bit unfamiliar with report distribution and whatnot. I've
>> > been
>> > reading up on MS SQL Reporting Services and from what I can see they
>> > offer
>> > a
>> > report repository as well as a a web based portal for downloading the
>> > reports.
>> >
>> > The company I work for is currently using the following:
>> >
>> > . Webserver running IIS
>> > . SQL Server 2000
>> > . Coldfusion MX 7.0 Server
>> > . Crystal Enterprise Server 10.0
>> >
>> > The current set-up we have is to produce the report in a web page (not
>> > necessarily through a proprietary portal or anything like that).
>> >
>> > Crystal Reports no longer supports this functionality in version 11.
>> > Does
>> > anyone here know if it's possible for me to be able to create and
>> > produce
>> > a
>> > report and then display it on a web page using Cold Fusion? This would
>> > include being able to create a subreport and passing it parameters with
>> > stored procedures.
>> >
>> >
>> > Thanks!
>> >
>> > Todd
>>|||Hi Todd,
I assume you know Cold fussion ? Does cold fusion, uses web services,
if yes, you can easily..integrate the way of reporting services or crystal
report
to be see in ur portal.
Why crystal report is not supported in the web in Version 11'who said this
regards
"Todd Jaspers" wrote:
> Thanks Bruce, I really appreciate the response.
> This is how they currently do it:
> Can you tell me if it is at all possible to do this with Microsoft Reporting
> services? They are asking me to do the research and find out if MS SQL is a
> viable solution.
>
> -----
> This document has been created demonstrating how we use Crystal Reports in
> real life for reporting. It would be great if Cold Fusion reports could
> produce the same result in the near future. Not only would it relive us of
> many yearly fees from Business Objects, but it would rededicate our future to
> ColdFusion and Macromedia/Adobe.
> We currently use Crystal Reports Developer 10 and XI to develop reports that
> get generated and displayed in our software on the fly. This software is
> driven by ColdFusion MX 7.0. The reports are built upon stored procedures
> that accept variables and produce different results.
> Crystal Developer lets you browse the available stored procedures and select
> the one you would like to use:
> After you select which stored procedure you would like to use, the next
> screen automatically asks you for some sample data to pass to the SP
> variables:
> Once the report has been attached to the stored procedure, the rest is just
> creating the report. The field explorer on the shows which fields are
> available from the stored procedure:
> Fields are dragged on and the report is saved. Displaying the report can be
> done by using some simple javascript. So we have the flexibility to have
> whatever we want on the screen, and the report to be â'embeddedâ' in our
> application.
> We created a header and a footer to wrap around the parameters because those
> donâ't change.
> In our attempts to move over to the early version of ColdFusion reports,
> this process has been real clumsy. The stored procedures donâ't show up on
> the database browser and the only way we can get them to execute is using the
> exec <stored procedure> in the query window. You can forget about passing
> variables to it. It would be great if a similar process could be duplicated
> with ColdFusion reports.
>
> Thanks!!!
> Todd
>
> "Bruce L-C [MVP]" wrote:
> > Not sure how Cold Fusion works but there are two ways you can integrate with
> > Report Services (assuming you are not using the Portal it ships with). One
> > is to use URL integration and the other is web services. With VS 2005 there
> > are two new controls (webform and winform) that integrate with RS 2005 using
> > webservices. Very good and very tight integration. They require 2.0
> > framework.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Todd Jaspers" <Todd Jaspers@.discussions.microsoft.com> wrote in message
> > news:058E4F12-E1DF-4409-AA42-9215BF1F578D@.microsoft.com...
> > > Hi, I'm a bit unfamiliar with report distribution and whatnot. I've been
> > > reading up on MS SQL Reporting Services and from what I can see they offer
> > > a
> > > report repository as well as a a web based portal for downloading the
> > > reports.
> > >
> > > The company I work for is currently using the following:
> > >
> > > . Webserver running IIS
> > > . SQL Server 2000
> > > . Coldfusion MX 7.0 Server
> > > . Crystal Enterprise Server 10.0
> > >
> > > The current set-up we have is to produce the report in a web page (not
> > > necessarily through a proprietary portal or anything like that).
> > >
> > > Crystal Reports no longer supports this functionality in version 11. Does
> > > anyone here know if it's possible for me to be able to create and produce
> > > a
> > > report and then display it on a web page using Cold Fusion? This would
> > > include being able to create a subreport and passing it parameters with
> > > stored procedures.
> > >
> > >
> > > Thanks!
> > >
> > > Todd
> >
> >
> >

Processes seen in enterprise manager

Hi,
We are using a new third party application that has SQL Server 2000 as the
database. It is an ASP page front end and uses ODBC to connect. There are
about 20 people who use it during the day. When I look in EM at the
processes there are well over 100. Even in the morning after everyone has
logged out the night before. All the processes are sleeping so they aren't
using any resources. I feel kind of dumb here but is there a server
property setting where I can set a value for these to expire? Looked in BOL
and in my other books but this doesn't seem to be available. Only timeout
settings when waiting for a connection or running a query. I wasn't worried
about these thinking SQL Server was managing them but then I noticed that
our in house application that uses the same type of setup doesn't have this
problem.
Thanks,
Wayne
I have worked with one third party application whose idea of connection
pooling was to open up 100 connections on start-up, even though it never
used more than 2 during the time we used it. Your third party application
might have been designed by a similarly brilliant and knowledgeable
developer.
You can't set a timeout for the connections, but you can schedule a job to
run the following script on a regular basis. This example kills all
connections that have not been used for 6 hours:
DECLARE @.sql varchar(4000)
WHILE 1=1
BEGIN
SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
AND spid <> @.@.spid AND spid >= 50)
IF @.sql IS NULL BREAK
EXEC (@.sql)
END
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We are using a new third party application that has SQL Server 2000 as the
> database. It is an ASP page front end and uses ODBC to connect. There
are
> about 20 people who use it during the day. When I look in EM at the
> processes there are well over 100. Even in the morning after everyone has
> logged out the night before. All the processes are sleeping so they
aren't
> using any resources. I feel kind of dumb here but is there a server
> property setting where I can set a value for these to expire? Looked in
BOL
> and in my other books but this doesn't seem to be available. Only timeout
> settings when waiting for a connection or running a query. I wasn't
worried
> about these thinking SQL Server was managing them but then I noticed that
> our in house application that uses the same type of setup doesn't have
this
> problem.
> Thanks,
> Wayne
>
|||Thanks Jacco,
I'll give the script a try. Based on other things I've seen with this app I
would not be surprised if they are doing something similar.
Thanks again,
Wayne
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I have worked with one third party application whose idea of connection
> pooling was to open up 100 connections on start-up, even though it never
> used more than 2 during the time we used it. Your third party application
> might have been designed by a similarly brilliant and knowledgeable
> developer.
> You can't set a timeout for the connections, but you can schedule a job to
> run the following script on a regular basis. This example kills all
> connections that have not been used for 6 hours:
> DECLARE @.sql varchar(4000)
> WHILE 1=1
> BEGIN
> SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> GETDATE()) >= 6
> AND spid <> @.@.spid AND spid >= 50)
> IF @.sql IS NULL BREAK
> EXEC (@.sql)
> END
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
> are
has[vbcol=seagreen]
> aren't
> BOL
timeout[vbcol=seagreen]
> worried
that
> this
>
|||Ok, don't be surprised though if the application either fails to work when
it's 100 or so connections aren't there, or just recreates them on a regular
basis after you have killed them....
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uJQkvJrLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> Thanks Jacco,
> I'll give the script a try. Based on other things I've seen with this app
I[vbcol=seagreen]
> would not be surprised if they are doing something similar.
> Thanks again,
> Wayne
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
application[vbcol=seagreen]
to[vbcol=seagreen]
> the
There[vbcol=seagreen]
> has
in
> timeout
> that
>

Processes seen in enterprise manager

Hi,
We are using a new third party application that has SQL Server 2000 as the
database. It is an ASP page front end and uses ODBC to connect. There are
about 20 people who use it during the day. When I look in EM at the
processes there are well over 100. Even in the morning after everyone has
logged out the night before. All the processes are sleeping so they aren't
using any resources. I feel kind of dumb here but is there a server
property setting where I can set a value for these to expire? Looked in BOL
and in my other books but this doesn't seem to be available. Only timeout
settings when waiting for a connection or running a query. I wasn't worried
about these thinking SQL Server was managing them but then I noticed that
our in house application that uses the same type of setup doesn't have this
problem.
Thanks,
WayneI have worked with one third party application whose idea of connection
pooling was to open up 100 connections on start-up, even though it never
used more than 2 during the time we used it. Your third party application
might have been designed by a similarly brilliant and knowledgeable
developer.
You can't set a timeout for the connections, but you can schedule a job to
run the following script on a regular basis. This example kills all
connections that have not been used for 6 hours:
DECLARE @.sql varchar(4000)
WHILE 1=1
BEGIN
SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
AND spid <> @.@.spid AND spid >= 50)
IF @.sql IS NULL BREAK
EXEC (@.sql)
END
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We are using a new third party application that has SQL Server 2000 as the
> database. It is an ASP page front end and uses ODBC to connect. There
are
> about 20 people who use it during the day. When I look in EM at the
> processes there are well over 100. Even in the morning after everyone has
> logged out the night before. All the processes are sleeping so they
aren't
> using any resources. I feel kind of dumb here but is there a server
> property setting where I can set a value for these to expire? Looked in
BOL
> and in my other books but this doesn't seem to be available. Only timeout
> settings when waiting for a connection or running a query. I wasn't
worried
> about these thinking SQL Server was managing them but then I noticed that
> our in house application that uses the same type of setup doesn't have
this
> problem.
> Thanks,
> Wayne
>|||Thanks Jacco,
I'll give the script a try. Based on other things I've seen with this app I
would not be surprised if they are doing something similar.
Thanks again,
Wayne
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> I have worked with one third party application whose idea of connection
> pooling was to open up 100 connections on start-up, even though it never
> used more than 2 during the time we used it. Your third party application
> might have been designed by a similarly brilliant and knowledgeable
> developer.
> You can't set a timeout for the connections, but you can schedule a job to
> run the following script on a regular basis. This example kills all
> connections that have not been used for 6 hours:
> DECLARE @.sql varchar(4000)
> WHILE 1=1
> BEGIN
> SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> GETDATE()) >= 6
> AND spid <> @.@.spid AND spid >= 50)
> IF @.sql IS NULL BREAK
> EXEC (@.sql)
> END
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
the[vbcol=seagreen]
> are
has[vbcol=seagreen]
> aren't
> BOL
timeout[vbcol=seagreen]
> worried
that[vbcol=seagreen]
> this
>|||Ok, don't be surprised though if the application either fails to work when
it's 100 or so connections aren't there, or just recreates them on a regular
basis after you have killed them....
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uJQkvJrLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> Thanks Jacco,
> I'll give the script a try. Based on other things I've seen with this app
I
> would not be surprised if they are doing something similar.
> Thanks again,
> Wayne
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
application[vbcol=seagreen]
to[vbcol=seagreen]
> the
There[vbcol=seagreen]
> has
in[vbcol=seagreen]
> timeout
> that
>

Processes seen in enterprise manager

Hi,
We are using a new third party application that has SQL Server 2000 as the
database. It is an ASP page front end and uses ODBC to connect. There are
about 20 people who use it during the day. When I look in EM at the
processes there are well over 100. Even in the morning after everyone has
logged out the night before. All the processes are sleeping so they aren't
using any resources. I feel kind of dumb here but is there a server
property setting where I can set a value for these to expire? Looked in BOL
and in my other books but this doesn't seem to be available. Only timeout
settings when waiting for a connection or running a query. I wasn't worried
about these thinking SQL Server was managing them but then I noticed that
our in house application that uses the same type of setup doesn't have this
problem.
Thanks,
WayneI have worked with one third party application whose idea of connection
pooling was to open up 100 connections on start-up, even though it never
used more than 2 during the time we used it. Your third party application
might have been designed by a similarly brilliant and knowledgeable
developer.
You can't set a timeout for the connections, but you can schedule a job to
run the following script on a regular basis. This example kills all
connections that have not been used for 6 hours:
DECLARE @.sql varchar(4000)
WHILE 1=1
BEGIN
SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
GETDATE()) >= 6
AND spid <> @.@.spid AND spid >= 50)
IF @.sql IS NULL BREAK
EXEC (@.sql)
END
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We are using a new third party application that has SQL Server 2000 as the
> database. It is an ASP page front end and uses ODBC to connect. There
are
> about 20 people who use it during the day. When I look in EM at the
> processes there are well over 100. Even in the morning after everyone has
> logged out the night before. All the processes are sleeping so they
aren't
> using any resources. I feel kind of dumb here but is there a server
> property setting where I can set a value for these to expire? Looked in
BOL
> and in my other books but this doesn't seem to be available. Only timeout
> settings when waiting for a connection or running a query. I wasn't
worried
> about these thinking SQL Server was managing them but then I noticed that
> our in house application that uses the same type of setup doesn't have
this
> problem.
> Thanks,
> Wayne
>|||Thanks Jacco,
I'll give the script a try. Based on other things I've seen with this app I
would not be surprised if they are doing something similar.
Thanks again,
Wayne
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> I have worked with one third party application whose idea of connection
> pooling was to open up 100 connections on start-up, even though it never
> used more than 2 during the time we used it. Your third party application
> might have been designed by a similarly brilliant and knowledgeable
> developer.
> You can't set a timeout for the connections, but you can schedule a job to
> run the following script on a regular basis. This example kills all
> connections that have not been used for 6 hours:
> DECLARE @.sql varchar(4000)
> WHILE 1=1
> BEGIN
> SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> GETDATE()) >= 6
> AND spid <> @.@.spid AND spid >= 50)
> IF @.sql IS NULL BREAK
> EXEC (@.sql)
> END
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > We are using a new third party application that has SQL Server 2000 as
the
> > database. It is an ASP page front end and uses ODBC to connect. There
> are
> > about 20 people who use it during the day. When I look in EM at the
> > processes there are well over 100. Even in the morning after everyone
has
> > logged out the night before. All the processes are sleeping so they
> aren't
> > using any resources. I feel kind of dumb here but is there a server
> > property setting where I can set a value for these to expire? Looked in
> BOL
> > and in my other books but this doesn't seem to be available. Only
timeout
> > settings when waiting for a connection or running a query. I wasn't
> worried
> > about these thinking SQL Server was managing them but then I noticed
that
> > our in house application that uses the same type of setup doesn't have
> this
> > problem.
> >
> > Thanks,
> > Wayne
> >
> >
>|||Ok, don't be surprised though if the application either fails to work when
it's 100 or so connections aren't there, or just recreates them on a regular
basis after you have killed them....
--
Jacco Schalkwijk
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uJQkvJrLEHA.3904@.TK2MSFTNGP09.phx.gbl...
> Thanks Jacco,
> I'll give the script a try. Based on other things I've seen with this app
I
> would not be surprised if they are doing something similar.
> Thanks again,
> Wayne
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:%23m6mO%23qLEHA.3516@.TK2MSFTNGP11.phx.gbl...
> > I have worked with one third party application whose idea of connection
> > pooling was to open up 100 connections on start-up, even though it never
> > used more than 2 during the time we used it. Your third party
application
> > might have been designed by a similarly brilliant and knowledgeable
> > developer.
> >
> > You can't set a timeout for the connections, but you can schedule a job
to
> > run the following script on a regular basis. This example kills all
> > connections that have not been used for 6 hours:
> >
> > DECLARE @.sql varchar(4000)
> > WHILE 1=1
> > BEGIN
> > SET @.sql = (SELECT TOP 1 'KILL ' + CAST(spid AS VARCHAR(10))
> > FROM master.dbo.sysprocesses WHERE DATEDIFF(hh, last_batch,
> > GETDATE()) >= 6
> > AND spid <> @.@.spid AND spid >= 50)
> > IF @.sql IS NULL BREAK
> > EXEC (@.sql)
> > END
> >
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > news:OPecmwqLEHA.1340@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > > We are using a new third party application that has SQL Server 2000 as
> the
> > > database. It is an ASP page front end and uses ODBC to connect.
There
> > are
> > > about 20 people who use it during the day. When I look in EM at the
> > > processes there are well over 100. Even in the morning after everyone
> has
> > > logged out the night before. All the processes are sleeping so they
> > aren't
> > > using any resources. I feel kind of dumb here but is there a server
> > > property setting where I can set a value for these to expire? Looked
in
> > BOL
> > > and in my other books but this doesn't seem to be available. Only
> timeout
> > > settings when waiting for a connection or running a query. I wasn't
> > worried
> > > about these thinking SQL Server was managing them but then I noticed
> that
> > > our in house application that uses the same type of setup doesn't have
> > this
> > > problem.
> > >
> > > Thanks,
> > > Wayne
> > >
> > >
> >
> >
>