Immediate IFF() ?

Is there an Immediate IFF() function in SQL 7.0?
I'm trying to do this:
INSERT INTO Myfile(Name,IFF(TDate='',Ddate,Tdate)
SELECT d.Name, d.tdate
FROM Masterfile d, Anotherfile s
WHERE d.Name = s.Name
thx,
mac>> Is there an Immediate IFF() function in SQL 7.0?
No, in most cases, CASE expressions would suffice. For details and other
alternatives, read through the topics CASE, COALESCE, ISNULL & NULLIF in SQL
Server Books Online.
--
Anith

Immediate IFF() ?

Is there an Immediate IFF() function in SQL 7.0?
I'm trying to do this:
INSERT INTO Myfile(Name,IFF(TDate='',Ddate,Tdate)
SELECT d.Name, d.tdate
FROM Masterfile d, Anotherfile s
WHERE d.Name = s.Name
thx,
mac>> Is there an Immediate IFF() function in SQL 7.0?
No, in most cases, CASE expressions would suffice. For details and other
alternatives, read through the topics CASE, COALESCE, ISNULL & NULLIF in SQL
Server Books Online.
Anith

Imbedded subroutine prefix error

I am trying to run this routine which works fine until I put the outside
Select in. The function consists of 4 Select statements that I need to do
to get counts of various comparisons in my tables. The problem is that the
inside PositionID has to refer to the outside PositionID and that is where I
get the error ("WHERE p3.PositionID = p.PositionID ").
How do I get it to refer this table (p).
SELECT c. CompanyID,PositionID,JobTitleShort,Poste
dData =
replace(convert(varchar,p.DateCreated,6),' ',''),
Qualified = (
SELECT Count(*)
FROM
(
SELECT
Cat1,Value1,Cat2,Value2,Cat3,Value3,Cat4
,Value4,Cat5,Value5,Cat6,Value6,Cat7
,Value7,
Cat8,Value8,TotalCats=Cat1+Cat2+Cat3+Cat
4+Cat5+Cat6+Cat7+Cat8,
TotalValues =
Value1+Value2+Value3+Value4+Value5+Value
6+Value7+Value8,
CriteriaStatus = CASE WHEN
(Cat1+Cat2+Cat3+Cat4+Cat5+Cat6+Cat7+Cat8
) =
(Value1+Value2+Value3+Value4+Value5+Valu
e6+Value7+Value8) THEN 'All'
WHEN
(Value1+Value2+Value3+Value4+Value5+Valu
e6+Value7+Value8) >=
((Cat1+Cat2+Cat3+Cat4+Cat5+Cat6+Cat7+Cat
8)/2) THEN 'Most'
ELSE 'Fails' END
FROM
(
SELECT Cat1=Case when OvertimeRequired = 1 then 1 else 0 end,
Value1=Case when OvertimeRequired = 1 then (Case when
WorkOvertime = 1 then 1 else 0 end) else 0 end,
Cat2=Case when SponserNonUS = 0 then 1 else 0 end,
Value2=Case when SponserNonUS = 0 then (Case when m.USCitizen
= 0 then 0 else 1 end) else 0 end,
Cat3=Case when p3.JobDistance > 0 then 1 else 0 end,
Value3 = Case when p3.JobDistance > 0 then (Case when
dbo.GetDistance(ZipCode,m.Zip) <= p3.JobDistance then 1 else 0 end) else 0
end,
Cat4=Case when p3.EducationLevel is not null then 1 else 0
end,
Value4=Case when p3.EducationLevel is not null then (Case
when m.EducationLevel >= p3.EducationLevel then 1 else 0 end) else 0 end,
Cat5=Case when p3.CareerLevel is not null then 1 else 0 end,
Value5=Case when p3.CareerLevel is not null then (Case when
m.CareerLevel >= p3.CareerLevel then 1 else 0 end) else 0 end,
Cat6=Case when p3.ExperienceLevel is not null then 1 else 0
end,
Value6=Case when p3.ExperienceLevel is not null then (Case
when m.ExperienceLevel >= p3.ExperienceLevel then 1 else 0 end) else 0 end,
Cat7=Case when p3.ScreenTestRequired = 1 then 1 else 0 end,
Value7=Case when p3.ScreenTestRequired = 1 then
(Case when ScreenTestScore >= p3.NotifyScreenMinScore then
1 else 0 end) else 0 end,
Cat8=Case when p3.SkillsTestRequired = 1 and
SkillsTestOffered is not null then 1 else 0 end,
Value8=Case when p3.SkillsTestRequired = 1 and
SkillsTestOffered is not null then
(Case when SkillsTestScore >= p3.NotifySkillsMinScore then
1 else 0 end) else 0 end
FROM applicant a
JOIN logon l on (a.UserID = l.UserID)
JOIN Position p3 on (a.PositionID = p3.PositionID)
JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
LEFT JOIN ApplicantPosition ap on (ap.ApplicantID =
a.ApplicantID)
LEFT JOIN MyInfo m on (m.UserID = a.UserID)
WHERE p3.PositionID = p.PositionID
) as a1
) as a2
WHERE CriteriaStatus = 'Most')
FROM position p
JOIN Companies c on (c.CompanyID = p.CompanyID)
The error I get is:
The column prefix 'p' does not match with a table name or alias name
used in the query.
Thanks,
Tomtshad (tfs@.dslextreme.com) writes:
> I am trying to run this routine which works fine until I put the outside
> Select in. The function consists of 4 Select statements that I need to
> do to get counts of various comparisons in my tables. The problem is
> that the inside PositionID has to refer to the outside PositionID and
> that is where I get the error ("WHERE p3.PositionID = p.PositionID ").
> How do I get it to refer this table (p).
Looking at your query, I will have to say that there is a whole lot of
fuzz just to get a COUNT(*). You must be able to simplify this. And maybe
even to the point you don't need to nest any derived tables.
Also, my experience is that subqueries in the SELECT list are often
expensive. Try to move the derived table to the FROM clause so you get
something like:
SELECT c.CompanyID, ... Qualified = d.cnt
FROM Positions p
JOIN Companies c ON ...
JOIN (SELECT position, cnt = COUNT(*)
FROM FROM applicant a
JOIN logon l on (a.UserID = l.UserID)
JOIN Position p3 on (a.PositionID = p3.PositionID)
JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
LEFT JOIN ApplicantPosition ap
on (ap.ApplicantID = a.ApplicantID)
LEFT JOIN MyInfo m on (m.UserID = a.UserID)
GROUP BY position) AS d ON p ON d.position = p.position
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm not sure if yours would work or not. I am just starting to look at it,
but I wasn't just trying to get the count().
The inside query is putting together a list of criteria and whether they
were met or not.
I then need to total the number of criteria and whether the user has met
each criteria or not (1=yes and 0= no). I use this total to tell whether
the user has met the criteria, met most or fails to meet them.
This is used in various reports. I now have a report that just gives me a
total number of users that have passed the criteria for each position. This
is why I need the inside querie to refer to the outside PositionID, but I
get an error on it. I have to do the inside 2 queries first to find out
whether users have passed or not.
It may not be the best way, but this was what I came up with a couple of
months ago on this group to make this work.
Thanks,
Tom
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9743B83E6219Yazorman@.127.0.0.1...
> tshad (tfs@.dslextreme.com) writes:
> Looking at your query, I will have to say that there is a whole lot of
> fuzz just to get a COUNT(*). You must be able to simplify this. And maybe
> even to the point you don't need to nest any derived tables.
> Also, my experience is that subqueries in the SELECT list are often
> expensive. Try to move the derived table to the FROM clause so you get
> something like:
> SELECT c.CompanyID, ... Qualified = d.cnt
> FROM Positions p
> JOIN Companies c ON ...
> JOIN (SELECT position, cnt = COUNT(*)
> FROM FROM applicant a
> JOIN logon l on (a.UserID = l.UserID)
> JOIN Position p3 on (a.PositionID = p3.PositionID)
> JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
> LEFT JOIN ApplicantPosition ap
> on (ap.ApplicantID = a.ApplicantID)
> LEFT JOIN MyInfo m on (m.UserID = a.UserID)
> GROUP BY position) AS d ON p ON d.position = p.position
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||tshad (tscheiderich@.ftsolutions.com) writes:
> I'm not sure if yours would work or not. I am just starting to look at
> it, but I wasn't just trying to get the count().
Ah, I see now that the outermost table had a WHERE clause. Still, all
the columns that comes before the definition of Criteria_status, has
no actual use in the query. (But I can understand that they are good
for debug.)
I can't say for sure that the outline that I gave will work for you,
as I don't have tables, nor sample data to test with (hint, hint!),
but I would encourage you to study the possibility.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97444A54ACE5Yazorman@.127.0.0.1...
> tshad (tscheiderich@.ftsolutions.com) writes:
> Ah, I see now that the outermost table had a WHERE clause. Still, all
> the columns that comes before the definition of Criteria_status, has
> no actual use in the query. (But I can understand that they are good
> for debug.)
>
Actually, I would be getting multiple records with Criteria_status equal to
either "All", "Most" or "Fail". But I am only interested in the ones that
are equal to "Most". But I have to read all the records and calculate the
Criterias and Values before I know if they fail or not.
I then need to filter out the "Most" ones and then count them.

> I can't say for sure that the outline that I gave will work for you,
> as I don't have tables, nor sample data to test with (hint, hint!),
> but I would encourage you to study the possibility.
I will.
But why was I getting the error?
And how can I get it work?
I have done subqueries before where the inside query references the outside
query. But I can't figure out why this one won't work.
Thanks,
Tom
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi tshad
I think I see what you're trying to do: in your set A2 you want to get
a count of rows BY PositionID, like so:
Position ID CountRows
1 6
2 3
3 10
etc.
Is this right?
To do this, you need to return a rowcount AND the position ID from A2 -
you can then join A2 to your other tables (position and Companies) on
PositionID, so that A2 doesn't have to try to refer to a column outside
itself. Instead of
SELECT Count(*) FROM (lots of SQL) AS A2
use
SELECT PositionID,Count(*) FROM (lots of SQL) AS A2 GROUP BY Position
ID.
Make this statement with the GROUP BY into a separate subquery, and
join it to the two other tables which you've put at the end:
SELECT c.CompanyID,p.[nb: qualified because there's now 2 PositionID
columns in the outer set]PositionID,JobTitleShort,PostedData =
replace(convert(varchar,p.DateCreated,6),' ',''),
totals.RowCount AS Qualified FROM
(SELECT PositionID,Count(*) FROM (lots of SQL) AS A2 GROUP BY
Position ID) totals
INNER JOIN
position p
ON totals.PositionID=p.PositionID
JOIN
Companies c
on (c.CompanyID = p.CompanyID)
WHERE... etc
By the way, you can also get rid of all the complicated calculation
within A2 - it's wasted, as having done all that calculation, SQL then
only returns a rowcount from the resulting set!
A2 (the SQL i've referred to as (lots of SQL) can be slimmed down to
something like this:
SELECT PositionID [qualify this, specifying which table it should come
from] FROM
applicant a
JOIN
logon l
etc
hope this helps.
cheers
Seb|||tshad (tscheiderich@.ftsolutions.com) writes:
> But why was I getting the error?
If you insist to get answer to that question, you better post the
CREATE TABLE statements for the table, so it's possible to play with
query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Fri, 6 Jan 2006 18:00:48 -0800, tshad wrote:
(snip)
>But why was I getting the error?
Hi Tom,
Normally, a derived table can't refer to columns from the outer query.
The table has to be materialized before the joins in the FROM clause can
be evaluated, so there's no way to know which row is referred to. (This
is the theoretic description - in reality, the optimizer will probably
choose a faster strategy).
If a derived table is used inside a subquery, it still can't refer to
other tables used in the subquery, but it can refer to the tables used
outside of the subquery. This is possible becuase the complete subquery
has to be re-evaluated for any row in the outer query anyway (again, in
theory).
It appears as if SQL Server is unable to recognise this situation if you
start nesting subqueries. I would consider this to be a bug. The very
simple script below will reproduce this behaviour on SQL Server 2000
SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
fixed in SQL Server 2005.
CREATE TABLE t1 (a int, b int)
CREATE TABLE t2 (a int, b int)
go
-- Single derived table - no problem
SELECT (SELECT a
FROM (SELECT *
FROM t2
WHERE t2.b = t1.b
) AS Derived
) AS Subquery
FROM t1
go
-- Nested derived table - error
SELECT (SELECT a
FROM (SELECT *
FROM (SELECT *
FROM t2
WHERE t2.b = t1.b
) AS Inner_Derived
) AS Outer_Derived
) AS Subquery
FROM t1
go
DROP TABLE t1
DROP TABLE t2
go

>And how can I get it work?
Now that's the harder question, I guess.
Start with www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
> It appears as if SQL Server is unable to recognise this situation if you
> start nesting subqueries. I would consider this to be a bug. The very
> simple script below will reproduce this behaviour on SQL Server 2000
> SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
> fixed in SQL Server 2005.
Both your queries work on SQL 2005, so it appears to have been fixed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:h9k0s1pqtgusku3pmh58bqk47d67ab75uj@.
4ax.com...
> On Fri, 6 Jan 2006 18:00:48 -0800, tshad wrote:
> (snip)
> Hi Tom,
> Normally, a derived table can't refer to columns from the outer query.
> The table has to be materialized before the joins in the FROM clause can
> be evaluated, so there's no way to know which row is referred to. (This
> is the theoretic description - in reality, the optimizer will probably
> choose a faster strategy).
> If a derived table is used inside a subquery, it still can't refer to
> other tables used in the subquery, but it can refer to the tables used
> outside of the subquery. This is possible becuase the complete subquery
> has to be re-evaluated for any row in the outer query anyway (again, in
> theory).
> It appears as if SQL Server is unable to recognise this situation if you
> start nesting subqueries. I would consider this to be a bug. The very
> simple script below will reproduce this behaviour on SQL Server 2000
> SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
> fixed in SQL Server 2005.
> CREATE TABLE t1 (a int, b int)
> CREATE TABLE t2 (a int, b int)
> go
> -- Single derived table - no problem
> SELECT (SELECT a
> FROM (SELECT *
> FROM t2
> WHERE t2.b = t1.b
> ) AS Derived
> ) AS Subquery
> FROM t1
> go
> -- Nested derived table - error
> SELECT (SELECT a
> FROM (SELECT *
> FROM (SELECT *
> FROM t2
> WHERE t2.b = t1.b
> ) AS Inner_Derived
> ) AS Outer_Derived
> ) AS Subquery
> FROM t1
> go
> DROP TABLE t1
> DROP TABLE t2
> go
Hi Hugo,
Yes, that is exactly what is happening. There may be a different way to
restructure the statement (maybe by a join to get rid of the inner table),
but I'm not sure how it would be done in my situation.
Maybe the inner_derived table has to reference the Outer_Derived and than
the Outer_Derived reference t1 somehow.

>
> Now that's the harder question, I guess.
> Start with www.aspfaq.com/5006.
This doesn't appear to be the faq you meant (or maybe it was) as doesn't
seem to have anything to do with query/subquery question.
Thanks,
Tom
> --
> Hugo Kornelis, SQL Server MVP

IIS and SQL Server Persmission Issue

IIS and SQL Server Persmission Issue
I am getting an error trying to run a SQL Server SELECT statement from an
ASP Application.
I am learning ASP/IIS/SQL Server by writing a small ASP app in Dreamweaver.
I've created and tested the ODBC connection just fine. And when I create the
connection in Dreamweaver and run the query it works just fine. However when
I try to access the web page I get:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
/webprodmx/categories.asp, line 9
Code is:
Dim MM_webprodmx_STRING
MM_webprodmx_STRING = "dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
Set rsCategories = Server.CreateObject("ADODB.Recordset")
9: rsCategories.ActiveConnection = MM_webprodmx_STRING
rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
rsCategories.CursorType = 0
The DSN is defined and working (testing outside of dreamweaver, via setup
directly). The database and table exist and have data present. Like I said
it works everyplace else except when going through IIS. I have read some of
the MS Support articles and made sure I am accessing my machine via (local)
so there is no network access. Everything is running on my single local
machine - even IIS and SQL Server 2000.
Any suggestions would be appreciated.
"Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
Dreamweaver.
> I've created and tested the ODBC connection just fine. And when I create
the
> connection in Dreamweaver and run the query it works just fine. However
when
> I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
Your DSN is set up to use integrated authentication. Change that or give
'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It works
outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
connecting to the database.
David
|||Thanks David.
I've gone in and check and that user has SELECT/INSERT/DELETE permissions on
all of the needed tables.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23mp8$gwgEHA.384@.TK2MSFTNGP10.phx.gbl...
> "Patrick24601" <patrick24601@.yahoo.com> wrote in message
> news:ePPTc.4368$wu.1124@.okepread04...
> Dreamweaver.
> the
> when
> Your DSN is set up to use integrated authentication. Change that or give
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It
> works
> outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
> connecting to the database.
> David
>
|||Thanks all for your responses on this.
What I ended up doing (although maybe not the best solution) is to create an
explicitly new userid on the SQL server and use that for everything.
Patrick
"Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
> Dreamweaver. I've created and tested the ODBC connection just fine. And
> when I create the connection in Dreamweaver and run the query it works
> just fine. However when I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
> Code is:
> Dim MM_webprodmx_STRING
> MM_webprodmx_STRING = "dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
> Set rsCategories = Server.CreateObject("ADODB.Recordset")
> 9: rsCategories.ActiveConnection = MM_webprodmx_STRING
> rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
> rsCategories.CursorType = 0
> The DSN is defined and working (testing outside of dreamweaver, via setup
> directly). The database and table exist and have data present. Like I said
> it works everyplace else except when going through IIS. I have read some
> of the MS Support articles and made sure I am accessing my machine via
> (local) so there is no network access. Everything is running on my single
> local machine - even IIS and SQL Server 2000.
> Any suggestions would be appreciated.
>

IIS and SQL Server Persmission Issue

IIS and SQL Server Persmission Issue
I am getting an error trying to run a SQL Server SELECT statement from an
ASP Application.
I am learning ASP/IIS/SQL Server by writing a small ASP app in Dreamweaver.
I've created and tested the ODBC connection just fine. And when I create the
connection in Dreamweaver and run the query it works just fine. However when
I try to access the web page I get:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user
'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
/webprodmx/categories.asp, line 9
Code is:
Dim MM_webprodmx_STRING
MM_webprodmx_STRING = " dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
Set rsCategories = Server.CreateObject("ADODB.Recordset")
9: rsCategories.ActiveConnection = MM_webprodmx_STRING
rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
rsCategories.CursorType = 0
The DSN is defined and working (testing outside of dreamweaver, via setup
directly). The database and table exist and have data present. Like I said
it works everyplace else except when going through IIS. I have read some of
the MS Support articles and made sure I am accessing my machine via (local)
so there is no network access. Everything is running on my single local
machine - even IIS and SQL Server 2000.
Any suggestions would be appreciated."Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
Dreamweaver.
> I've created and tested the ODBC connection just fine. And when I create
the
> connection in Dreamweaver and run the query it works just fine. However
when
> I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fo
r user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
Your DSN is set up to use integrated authentication. Change that or give
'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It works
outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
connecting to the database.
David|||Thanks David.
I've gone in and check and that user has SELECT/INSERT/DELETE permissions on
all of the needed tables.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23mp8$gwgEHA.384@.TK2MSFTNGP10.phx.gbl...
> "Patrick24601" <patrick24601@.yahoo.com> wrote in message
> news:ePPTc.4368$wu.1124@.okepread04...
> Dreamweaver.
> the
> when
> Your DSN is set up to use integrated authentication. Change that or give
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It
> works
> outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
> connecting to the database.
> David
>|||Thanks all for your responses on this.
What I ended up doing (although maybe not the best solution) is to create an
explicitly new userid on the SQL server and use that for everything.
Patrick
"Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
> Dreamweaver. I've created and tested the ODBC connection just fine. And
> when I create the connection in Dreamweaver and run the query it works
> just fine. However when I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed fo
r user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
> Code is:
> Dim MM_webprodmx_STRING
> MM_webprodmx_STRING = " dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
> Set rsCategories = Server.CreateObject("ADODB.Recordset")
> 9: rsCategories.ActiveConnection = MM_webprodmx_STRING
> rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
> rsCategories.CursorType = 0
> The DSN is defined and working (testing outside of dreamweaver, via setup
> directly). The database and table exist and have data present. Like I said
> it works everyplace else except when going through IIS. I have read some
> of the MS Support articles and made sure I am accessing my machine via
> (local) so there is no network access. Everything is running on my single
> local machine - even IIS and SQL Server 2000.
> Any suggestions would be appreciated.
>

IIS and SQL Server Persmission Issue

IIS and SQL Server Persmission Issue
I am getting an error trying to run a SQL Server SELECT statement from an
ASP Application.
I am learning ASP/IIS/SQL Server by writing a small ASP app in Dreamweaver.
I've created and tested the ODBC connection just fine. And when I create the
connection in Dreamweaver and run the query it works just fine. However when
I try to access the web page I get:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
/webprodmx/categories.asp, line 9
Code is:
Dim MM_webprodmx_STRING
MM_webprodmx_STRING = "dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
Set rsCategories = Server.CreateObject("ADODB.Recordset")
9: rsCategories.ActiveConnection = MM_webprodmx_STRING
rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
rsCategories.CursorType = 0
The DSN is defined and working (testing outside of dreamweaver, via setup
directly). The database and table exist and have data present. Like I said
it works everyplace else except when going through IIS. I have read some of
the MS Support articles and made sure I am accessing my machine via (local)
so there is no network access. Everything is running on my single local
machine - even IIS and SQL Server 2000.
Any suggestions would be appreciated."Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
Dreamweaver.
> I've created and tested the ODBC connection just fine. And when I create
the
> connection in Dreamweaver and run the query it works just fine. However
when
> I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
Your DSN is set up to use integrated authentication. Change that or give
'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It works
outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
connecting to the database.
David|||Thanks David.
I've gone in and check and that user has SELECT/INSERT/DELETE permissions on
all of the needed tables.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23mp8$gwgEHA.384@.TK2MSFTNGP10.phx.gbl...
> "Patrick24601" <patrick24601@.yahoo.com> wrote in message
> news:ePPTc.4368$wu.1124@.okepread04...
>> IIS and SQL Server Persmission Issue
>> I am getting an error trying to run a SQL Server SELECT statement from an
>> ASP Application.
>> I am learning ASP/IIS/SQL Server by writing a small ASP app in
> Dreamweaver.
>> I've created and tested the ODBC connection just fine. And when I create
> the
>> connection in Dreamweaver and run the query it works just fine. However
> when
>> I try to access the web page I get:
>> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
>> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
>> /webprodmx/categories.asp, line 9
> Your DSN is set up to use integrated authentication. Change that or give
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP' rights to connect you your database. It
> works
> outside of IIS because then it's you, not 'ATBSLAPTOP\IUSR_ATBSLAPTOP'
> connecting to the database.
> David
>|||Thanks all for your responses on this.
What I ended up doing (although maybe not the best solution) is to create an
explicitly new userid on the SQL server and use that for everything.
Patrick
"Patrick24601" <patrick24601@.yahoo.com> wrote in message
news:ePPTc.4368$wu.1124@.okepread04...
> IIS and SQL Server Persmission Issue
> I am getting an error trying to run a SQL Server SELECT statement from an
> ASP Application.
> I am learning ASP/IIS/SQL Server by writing a small ASP app in
> Dreamweaver. I've created and tested the ODBC connection just fine. And
> when I create the connection in Dreamweaver and run the query it works
> just fine. However when I try to access the web page I get:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'ATBSLAPTOP\IUSR_ATBSLAPTOP'.
> /webprodmx/categories.asp, line 9
> Code is:
> Dim MM_webprodmx_STRING
> MM_webprodmx_STRING = "dsn=DSNwebprodmxSQL;uid=IUSR_ATBSLAPTOP;
> Set rsCategories = Server.CreateObject("ADODB.Recordset")
> 9: rsCategories.ActiveConnection = MM_webprodmx_STRING
> rsCategories.Source = "SELECT * FROM dbo.categories ORDER BY category ASC"
> rsCategories.CursorType = 0
> The DSN is defined and working (testing outside of dreamweaver, via setup
> directly). The database and table exist and have data present. Like I said
> it works everyplace else except when going through IIS. I have read some
> of the MS Support articles and made sure I am accessing my machine via
> (local) so there is no network access. Everything is running on my single
> local machine - even IIS and SQL Server 2000.
> Any suggestions would be appreciated.
>

IIF Statement on Select

Is there a way in an SQL Select statement to set up something similar to the
IIf command in Access:
Select LastName, IIf(CodeA = "Y","Yes'","No")
From NameTablermcompute,
Try using a "case" expression.
Select LastName, case when CodeA = 'Y' then 'Yes' else 'No' end as CodeA
From NameTable
AMB
"rmcompute" wrote:

> Is there a way in an SQL Select statement to set up something similar to t
he
> IIf command in Access:
> Select LastName, IIf(CodeA = "Y","Yes'","No")
> From NameTable|||There is no IIF in SQL Server, check out the CASE expression in Books
Online. Also, suggest you stay away from " as string delimiters.
SELECT LastName, CodeA = CASE CodeA WHEN 'Y' THEN 'Yes' ELSE 'No' END
FROM NameTable
Or
SELECT LastName, CodeA = CASE WHEN CodeA = 'Y' THEN 'Yes' ELSE 'No' END
FROM NameTable
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:2DA1A89A-17DA-4E9C-AF6F-AC37C2337277@.microsoft.com...
> Is there a way in an SQL Select statement to set up something similar to
> the
> IIf command in Access:
> Select LastName, IIf(CodeA = "Y","Yes'","No")
> From NameTable|||Thank you.
"Alejandro Mesa" wrote:
> rmcompute,
> Try using a "case" expression.
> Select LastName, case when CodeA = 'Y' then 'Yes' else 'No' end as CodeA
> From NameTable
>
> AMB
> "rmcompute" wrote:
>|||Thank you.
"Aaron Bertrand [SQL Server MVP]" wrote:

> There is no IIF in SQL Server, check out the CASE expression in Books
> Online. Also, suggest you stay away from " as string delimiters.
> SELECT LastName, CodeA = CASE CodeA WHEN 'Y' THEN 'Yes' ELSE 'No' END
> FROM NameTable
> Or
> SELECT LastName, CodeA = CASE WHEN CodeA = 'Y' THEN 'Yes' ELSE 'No' END
> FROM NameTable
>
> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:2DA1A89A-17DA-4E9C-AF6F-AC37C2337277@.microsoft.com...
>
>

IIf problem

I'm using that expression in a select statment in generic query designer, but there is a problem with it. I'm wondering what would that be.

IIf(Parameters!StartDate.Value = "" or Parameters!EndDate.Value = "", "", "where (Date between '" & Parameters!StartDate.Value & "' and '" & Parameters!EndDate.Value & "')")

A double-quote character within a string literal is escaped by a preceding double-quote. So, the second argument of IIf() should look like:

"where (Date between '"" & Parameters!StartDate.Value & ""' and '"" & Parameters!EndDate.Value & ""')"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbls7/html/vblrfvbspec2_4_4.asp
>>

Visual Basic Language Specification

2.4.4 String Literals

A string literal is a sequence of zero or more Unicode characters beginning and ending with an ASCII double-quote character, a Unicode left double-quote character, or a Unicode right double-quote character. Within a string, a sequence of two double-quote characters is an escape sequence representing a double quote in the string.
>>

|||Thank you Deepak for your help. Actually it worked with one double quote, the problem was in the condition (Parameters!StartDate.Value = ""), I replaced it by IsNothing(Parameters!StartDate.Value) and it worked fine.

IIF in SQL server

Is there a way in SQL Server to run the simple query
having something like IIF function in Access.
Similar to this easy query.
SELECT Mobile,iif([PlanType]=1, "New", "Upgrade") As Type FROM Acts WHERE
(RepId = 2194)
Thanks,
Michael
On Wed, 27 Oct 2004 12:51:12 -0700, MichaelK wrote:

>Is there a way in SQL Server to run the simple query
>having something like IIF function in Access.
>Similar to this easy query.
>SELECT Mobile,iif([PlanType]=1, "New", "Upgrade") As Type FROM Acts WHERE
>(RepId = 2194)
>Thanks,
>Michael
>
Hi Michael,
SELECT Mobile,
CASE
WHEN PlanType = 1
THEN 'New'
ELSE 'Upgrade'
END AS Type
FROM Acts
WHERE RepId = 2194
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

IIF in SQL

hello!
In MS ACCESS 2000, I can use the IIF function like (
SELECT IIF(x>0, "True","False") from x_table.
I tried the ISNULL but got a wrong ouput.
How can I do this also in MS SQL?
Please help...
Any effort is higly appreciated.
Thanksselect case when x>0 then 'True' else 'False' end
from x_table

rudy

IIF Function

I've been looking at the online help and it appears that
this statement should function as follows:
Select IIf(1 > 0 , 'Yes', 'No')
If 1 is greater than 0, Yes should be returned, if not
then No
Sql returns an error message: Incorrect syntax near '>'
Any Ideas?
ThanksYou must be looking at help for MS Access or Excel or something. There is
no such thing as IIF in SQL Server. Maybe try CASE:
SELECT CASE WHEN 1 > 0 THEN 'Yes' ELSE 'No' END
"Raghib" <anonymous@.discussions.microsoft.com> wrote in message
news:100801c4f1fe$f73d1860$a301280a@.phx.gbl...
> I've been looking at the online help and it appears that
> this statement should function as follows:
> Select IIf(1 > 0 , 'Yes', 'No')
>
> If 1 is greater than 0, Yes should be returned, if not
> then No
> Sql returns an error message: Incorrect syntax near '>'
> Any Ideas?
> Thanks
>|||If you search for IIF in SQL Server Books Online, you will indeed find it.
However, you have to look at the title (or location) and realize it is for
Analysis Services only.
This is not a TSQL function, but rather an MDX function.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23GAGeFg8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> You must be looking at help for MS Access or Excel or something. There is
> no such thing as IIF in SQL Server. Maybe try CASE:
> SELECT CASE WHEN 1 > 0 THEN 'Yes' ELSE 'No' END
>
>
> "Raghib" <anonymous@.discussions.microsoft.com> wrote in message
> news:100801c4f1fe$f73d1860$a301280a@.phx.gbl...
>> I've been looking at the online help and it appears that
>> this statement should function as follows:
>> Select IIf(1 > 0 , 'Yes', 'No')
>>
>> If 1 is greater than 0, Yes should be returned, if not
>> then No
>> Sql returns an error message: Incorrect syntax near '>'
>> Any Ideas?
>> Thanks
>

IIF Expression with SELECT statement

Is it possible to use a SELECT statement as the expression in an IIF function?
I Cannot get it to work.
i.e. IIF((SELECT COUNT(column) FROM table WHERE column = Fields!Variable.Value) > 0, "BAD","GOOD")
--
Thanks in advance
GHLooks like you can't do that because query can be only inside of data set in
Query element.
Victor
"GH" wrote:
> Is it possible to use a SELECT statement as the expression in an IIF function?
> I Cannot get it to work.
> i.e. IIF((SELECT COUNT(column) FROM table WHERE column => Fields!Variable.Value) > 0, "BAD","GOOD")
> --
> Thanks in advance
> GH|||Nope. Selects can only be used when defining datasets. However, you can use
count (and sum etc) in expressions. From BOL,
>>>>>>>>
The following code example provides a count of employees in the outermost
data region:
Count(Fields!EmployeeID.Value, Nothing)The following code example provides a
count of all orders in the Orders grouping or data region:
Count(Fields!OrderID.Value, "Orders")>>>>>>>>>>--
Bruce Loehle-Conger MVP SQL Server Reporting Services"GH"
<vakar@.community.nospam> wrote in message
news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> Is it possible to use a SELECT statement as the expression in an IIF
function?
> I Cannot get it to work.
> i.e. IIF((SELECT COUNT(column) FROM table WHERE column => Fields!Variable.Value) > 0, "BAD","GOOD")
> --
> Thanks in advance
> GH|||="select count (column) from table where
column='"+iif(fields!variable.value>0, "BAD","GOOD")+"'"
hope works
regards
"GH" <vakar@.community.nospam> wrote in message
news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> Is it possible to use a SELECT statement as the expression in an IIF
function?
> I Cannot get it to work.
> i.e. IIF((SELECT COUNT(column) FROM table WHERE column => Fields!Variable.Value) > 0, "BAD","GOOD")
> --
> Thanks in advance
> GH|||You should do this in "Generic Query Designer"
"saglamtimur" <bsaglamtimur@.mayanet.com.tr> wrote in message
news:OExB7I#2EHA.2804@.TK2MSFTNGP15.phx.gbl...
> ="select count (column) from table where
> column='"+iif(fields!variable.value>0, "BAD","GOOD")+"'"
> hope works
> regards
>
> "GH" <vakar@.community.nospam> wrote in message
> news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> > Is it possible to use a SELECT statement as the expression in an IIF
> function?
> >
> > I Cannot get it to work.
> >
> > i.e. IIF((SELECT COUNT(column) FROM table WHERE column => > Fields!Variable.Value) > 0, "BAD","GOOD")
> > --
> > Thanks in advance
> > GH
>|||Thanks guys.
I had to get 'creative' with my data set ... not sure of the cost ... but
getting to where I want to go.
Thanks again.
"saglamtimur" wrote:
> You should do this in "Generic Query Designer"
>
> "saglamtimur" <bsaglamtimur@.mayanet.com.tr> wrote in message
> news:OExB7I#2EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > ="select count (column) from table where
> > column='"+iif(fields!variable.value>0, "BAD","GOOD")+"'"
> >
> > hope works
> >
> > regards
> >
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> > > Is it possible to use a SELECT statement as the expression in an IIF
> > function?
> > >
> > > I Cannot get it to work.
> > >
> > > i.e. IIF((SELECT COUNT(column) FROM table WHERE column => > > Fields!Variable.Value) > 0, "BAD","GOOD")
> > > --
> > > Thanks in advance
> > > GH
> >
> >
>
>

IIF and empty fields

Hi, apologies if this is a silly mistake on my part, but... I'm having trouble with working out if a field is empty

I've been trying to use "SELECT newfield = IIF(IsEmpty(table.field), 0, 1) ... " to return a bit value of whether the string field has something in it..

The syntax check says that IsEmpty is not a valid function - but I dont understand how it can be... am I missing something here?

I also tried using IIF(table.field = '', 0, 1) and that returns "invalid syntax near ="

Please help because its really confusing me, ThanksHi,

IsEmpty and IIF are Analysis Server functions, so you can't use it in reqular T-SQL. I don't seen any indication in your message that your using OLAP, so I'll assume you're using regular T-SQL with relational data.

What do you consider to be empty? A null value? Or what is commonly considered to be no data for a particular datatype (empty string for strings, zero for numerics, etc.)?

If nulls, consider using the ISNULL function. If the value is null it returns the value of your choice, otherwise the non-null value. The NULLIF function can help in some cases.

But if empty is an empty string or zero, etc., you can compare those values. Or use the CASE block, which lets you return different values based on various conditions.

I suspect that you'll find what you need in these T-SQL features.

Don|||ok, thanks a lot. explains why its not working anyway :)

Ignory all value if marked "Select-All" in reports

Hello!

I have SQL query in my report, using multi-value parameters:

Select Table1.Item

From Table1

Where Table1.Item in(@.item)

Multi-value parameters “item” have properties “Available values” From-query and returned ~200 values. I want, If I selected in my reports “Select-All” then SQL query ignore “Where Table1.Item in(@.item)” How?

Sorry my bad English :-)

Thanks!

Try using Where (@.item is null or table1.item in (@.item) or the COALESCE function.

Ignoring expressions

I have an write an expression which let me select a value if the condition is
met, but if not then the expression should be ignored.
i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
The 0 here is supposed to ignore the expression, but it does not. What can I
use to ignore the expression.
Any help will be appriciated.Where is this expression, in a filter?
"Sumi" wrote:
> I have an write an expression which let me select a value if the condition is
> met, but if not then the expression should be ignored.
> i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> The 0 here is supposed to ignore the expression, but it does not. What can I
> use to ignore the expression.
> Any help will be appriciated.|||Yes. This expression is in a filter.
"Antoon" wrote:
> Where is this expression, in a filter?
> "Sumi" wrote:
> > I have an write an expression which let me select a value if the condition is
> > met, but if not then the expression should be ignored.
> > i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> >
> > The 0 here is supposed to ignore the expression, but it does not. What can I
> > use to ignore the expression.
> >
> > Any help will be appriciated.|||Did you try 1 instead of 0
--
"Everyone knows something you don't know"
"Sumi" wrote:
> Yes. This expression is in a filter.
> "Antoon" wrote:
> > Where is this expression, in a filter?
> >
> > "Sumi" wrote:
> >
> > > I have an write an expression which let me select a value if the condition is
> > > met, but if not then the expression should be ignored.
> > > i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> > >
> > > The 0 here is supposed to ignore the expression, but it does not. What can I
> > > use to ignore the expression.
> > >
> > > Any help will be appriciated.|||Filter expression
switch(Parameter!Industry.value <> '_ALL_' and Parameter!Industry.value =Industry.value, 1, true, 0)
Filter value
=1
in this expression "Industry.value" is your query value that carresponds to
you parameter
"Sumi" wrote:
> I have an write an expression which let me select a value if the condition is
> met, but if not then the expression should be ignored.
> i.e. =Iif(Parameter!Industry.value <> '_ALL_', Parameter!Industry.value, 0)
> The 0 here is supposed to ignore the expression, but it does not. What can I
> use to ignore the expression.
> Any help will be appriciated.