Problems hitting SqlServer from Global.asax Application_Start

Hi,

In my application I Application_Start event in Global.asax I am wanting to populate an Application state variable to hold data from a database.

I can use the exact code elsewhere in the application but when I try to open my SqlConnection object I get the error "SQL Server does not exist or access denied."

I'm using NT security and impersonation.

I've put a trace on the server and my user name isn't appearing as trying anything so it is as if it cannot find the server.

Are there any issues with trying to open a connection from Application_Start?

Thanks.

Can you post your source code here?

|||There was an atricle about this in the latest MSDN magazine. Basically, asp.net won't do impersonation in the application start event. Your best bet is to grab the data the first time that you need it in regular code.

Problems formatting my table...

I have a table that looks like this...

City State Server Type
Chicago IL Svr1 Data
Chicago IL Svr2 Data
Chicago IL Svr3 Backup
Chicago IL Svr4 Backup
Atlanta GA Svr1 Data
Atlanta GA Svr2 Data

I already have a function to convert the server rows into a comma delimited string...
ALTER function dbo.fnGetServers (@.City varchar(25), @.State varchar(25), @.Type varchar(25), @.Tree varchar(25))
returns varchar(1000)
as
begin
declare @.NewSvrCol varchar(1000)
select @.NewSvrCol = ''
select @.NewSvrCol = @.NewSvrCol + Server + ', ' from serverops.dbo.v_userviews where city = @.City and State = @.State and Type = @.Type and Tree = @.Tree
select @.NewSvrCol = left(@.NewSvrCol, len(@.NewSvrCol)-1)
return(@.NewSvrCol)
end

Any suggestions on how to display the table in the following format?

City State DataSvrs BackupSvrs
Chicago IL Svr1,Svr2 Svr3,Svr4
Atlanta GA Svr1,Svr2 NULLread the article titled Cross-Tab Reports in Books online and the read about CASE statements.|||read the article titled Cross-Tab Reports in Books online and the read about CASE statements.

Thanks a lot, I've made some progress but am still having a slight problem...
I am using this query:

ALTER VIEW v_USAtlasAuthServers
as
select distinct u.City, u.State,
CASE u.Type WHEN 'Data' then dbo.fnGetServers(City, State, Type) ELSE null END as Data_Servers,
CASE u.Type WHEN 'Backup' then dbo.fnGetServers(City, State, Type) ELSE null END as Backup_Servers
from serverops.dbo.v_userviews u

where u.tree='tree1' and u.country='united states' and u.type in ('Data Server','Backup Server')
group by u.city, u.state, u.type

which gives me the following table:

City State Data_Servers Backup_Servers
Chicago IL Svr1,Svr2 NULL
Chicago IL NULL Svr3,Svr4
Atlanta GA Svr1,Svr2 NULL

Which is not right as I want Chicago, IL to be in one row...any idea on how to fix this?sql

Problem with WHERE clause...

Hi All,
I keep getting the following error with my WHERE clause.
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near '-'.
We've found that the code works if the segment " -
(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So
it's clear that the error is within this segment.
The object "dbo.ReorderItemsTotals" is a view and uses a UNION command
in the SELECT statement. It joins two fields and outputs it as one
field - "OnPurchaseOrder". The two original fields are smallints and
the resulting UNION'd field is a varchar 10 - however I don't know why
this is.
Can anyone help fix this error? My code is as follows.
Many thanks in advance,
Rype
WHERE
(dbo.ProductAssembly.SubProduct IS NULL) AND
((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *
dbo.SoldLast120TotalWith0Total.Despatched) *
dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,
dbo.ReorderItemsTotals.Stock)) -
(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR
(dbo.ProductAssembly.SubProduct IS NULL) AND
(dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode)
AND
(dbo.ProductDetails.Product1 + dbo.productdetails.product2 <
dbo.ReorderItemsTotalsSets.ReorderQuantity)This is a multi-part message in MIME format.
--=_NextPart_000_0007_01C804CC.9AEE5810
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Everything seems to check out using SSMS to balance the parentheses. I =do question if the logic is as you intend, given the groupings. Having =the OR out by itself like that is often not what is desired. Also, do =yourself a favor and start using aliases!! :-)
WHERE dbo.ProductAssembly.SubProduct IS NULL
AND ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched + =dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 * dbo.SoldLast120TotalWith0Total.Despatched) * =dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int, dbo.ReorderItemsTotals.Stock)) - =(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0)
OR
(dbo.ProductAssembly.SubProduct IS NULL) AND (dbo.ProductDetails.Product1 < =dbo.ReorderItemsTotalsSets.StockCode)
AND (dbo.ProductDetails.Product1 + dbo.productdetails.product2 < =dbo.ReorderItemsTotalsSets.ReorderQuantity)
<ryanwpenfold@.gmail.com> wrote in message =news:1191330035.815038.230250@.n39g2000hsh.googlegroups.com...
> Hi All,
> > I keep getting the following error with my WHERE clause.
> > Msg 102, Level 15, State 1, Line 47
> Incorrect syntax near '-'.
> > We've found that the code works if the segment " -
> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So
> it's clear that the error is within this segment.
> > The object "dbo.ReorderItemsTotals" is a view and uses a UNION command
> in the SELECT statement. It joins two fields and outputs it as one
> field - "OnPurchaseOrder". The two original fields are smallints and
> the resulting UNION'd field is a varchar 10 - however I don't know why
> this is.
> > Can anyone help fix this error? My code is as follows.
> > Many thanks in advance,
> > Rype
> > > > WHERE
> (dbo.ProductAssembly.SubProduct IS NULL) AND
> ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +
> dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *
> dbo.SoldLast120TotalWith0Total.Despatched) *
> dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,
> dbo.ReorderItemsTotals.Stock)) -
> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
> dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR
> (dbo.ProductAssembly.SubProduct IS NULL) AND
> (dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode)
> AND
> (dbo.ProductDetails.Product1 + dbo.productdetails.product2 <
> dbo.ReorderItemsTotalsSets.ReorderQuantity)
>
--=_NextPart_000_0007_01C804CC.9AEE5810
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Everything seems to check out using SSMS to balance the =parentheses. I do question if the logic is as you intend, given the groupings. =Having the OR out by itself like that is often not what is desired. Also, =do yourself a favor and start using aliases!! :-)
WHERE dbo.ProductAssembly.SubProduct IS NULL AND ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched + dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 * dbo.SoldLast120TotalWith0Total.Despatched) * =dbo.ReorderItemsTotals.ReorderLevel * 120) - =(CONVERT(Int, dbo.ReorderItemsTotals.Stock)) - (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) + dbo.ReorderItemsTotals.SumOfSalesBackOrder) > =0) OR (dbo.ProductAssembly.SubProduct IS =NULL) AND (dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode) AND =(dbo.ProductDetails.Product1 + dbo.productdetails.product2 < dbo.ReorderItemsTotalsSets.ReorderQuantity)
wrote in message news:=1191330035.815038.230250@.n39g2000hsh.googlegroups.com...> Hi All,> > I keep getting the following error with my =WHERE clause.> > Msg 102, Level 15, State 1, Line 47> =Incorrect syntax near '-'.> > We've found that the code works if the =segment " -> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So> it's clear that the error is within this segment.> > The object "dbo.ReorderItemsTotals" is a view =and uses a UNION command> in the SELECT statement. It joins two =fields and outputs it as one> field - "OnPurchaseOrder". The two =original fields are smallints and> the resulting UNION'd field is a =varchar 10 - however I don't know why> this is.> > Can anyone =help fix this error? My code is as follows.> > Many thanks in =advance,> > Rype> > > > =WHERE> (dbo.ProductAssembly.SubProduct IS NULL) AND> ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +> dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *> dbo.SoldLast120TotalWith0Total.Despatched) *> dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,> dbo.ReorderItemsTotals.Stock)) -> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +> dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR> (dbo.ProductAssembly.SubProduct IS NULL) AND> (dbo.ProductDetails.Product1 < =dbo.ReorderItemsTotalsSets.StockCode)> AND> (dbo.ProductDetails.Product1 + =dbo.productdetails.product2 <> =dbo.ReorderItemsTotalsSets.ReorderQuantity)>

---

--=_NextPart_000_0007_01C804CC.9AEE5810--

Problem with User Defined Function

I wrote a UDF to get a rolling average based upon a date passed to the UDF. The error I get is:

Server: Msg 102, Level 15, State 1, Procedure fn_RollAverage, Line 26
Incorrect syntax near ')'.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION fn_RollAverage(@.CURVE_DATE AS SMALLDATETIME)
RETURNS @.TBLRESULT TABLE
(US0001M_Index AS FLOAT,
US0003M_INDEX AS FLOAT,
US0006M_INDEX AS FLOAT,
US0012M_INDEX AS FLOAT,
usswap2_index AS FLOAT,
usswap3_index AS FLOAT,
usswap4_index AS FLOAT,
usswap5_index AS FLOAT,
usswap6_index AS FLOAT,
usswap7_index AS FLOAT,
usswap8_index AS FLOAT,
usswap9_index AS FLOAT,
usswap10_index AS FLOAT,
usswap11_index AS FLOAT,
usswap12_index AS FLOAT,
usswap13_index AS FLOAT,
usswap14_index AS FLOAT,
usswap15_index AS FLOAT,
usswap20_index AS FLOAT,
usswap25_index AS FLOAT,
usswap30_index AS FLOAT)

AS BEGIN

DECLARE @.BEG_DATE AS SMALLDATETIME

SET @.BEG_DATE = (@.CURVE_DATE - 30)

INSERT @.TBLRESULT(
US0001M_Index,
US0003M_INDEX,
US0006M_INDEX,
US0012M_INDEX,
usswap2_index,
usswap3_index,
usswap4_index,
usswap5_index,
usswap6_index,
usswap7_index,
usswap8_index,
usswap9_index,
usswap10_index,
usswap11_index,
usswap12_index,
usswap13_index,
usswap14_index,
usswap15_index,
usswap20_index,
usswap25_index,
usswap30_index)

SELECT
AVG(US0001M_Index) AS US0001M_Index,
AVG(US0003M_INDEX) AS US0003M_INDEX,
AVG(US0006M_INDEX) AS US0006M_INDEX,
AVG(US0012M_INDEX) AS US0012M_INDEX,
AVG(usswap2_index) AS usswap2_index,
AVG(usswap3_index) AS usswap3_index,
AVG(usswap4_index) AS usswap4_index,
AVG(usswap5_index) AS usswap5_index,
AVG(usswap6_index) AS usswap6_index,
AVG(usswap7_index) AS usswap7_index,
AVG(usswap8_index) AS usswap8_index,
AVG(usswap9_index) AS usswap9_index,
AVG(usswap10_index) AS usswap10_index,
AVG(usswap11_index) AS usswap11_index,
AVG(usswap12_index) AS usswap12_index,
AVG(usswap13_index) AS usswap13_index,
AVG(usswap14_index) AS usswap14_index,
AVG(usswap15_index) AS usswap15_index,
AVG(usswap20_index) AS usswap20_index,
AVG(usswap25_index) AS usswap25_index,
AVG(usswap30_index) AS usswap30_index

FROM dbo.LiborSwap
WHERE CURVE_DATE BETWEEN @.BEG_DATE AND @.CURVE_DATE

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

DECLARE @.TBLRESULT TABLE
(US0001M_Index FLOAT,
US0003M_INDEX FLOAT,
US0006M_INDEX FLOAT

...)

|||

The " AS " keyword is not valid in the @.TBLRESULT table declaration.

|||Thanks thanks a thousand thanks