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

Image save to SQL server

When I am trying to save byte[] of an image to SQL server which is having an image column, I am getting an error like this " A severe error occured on the current command. The results, if any, should be discarded.". I am trying to save through a stored procedure.
In the sp also, the datatype is image.

Can you give a reply to this?Try this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240194

Image Error In SQL

I have a seperate table that holds employees pictures in it and for some reason I am getting an error all the sudden when I try to insert a picture. I never got that before.. Can anyone help please??It would appear that your image size has grown roughly eight times what it was (or you've added replication, or possibly you changed to a new SQL Server).

The fix is easy, but PLEASE think about this before you make the change... This can have relatively far reaching implications to replication performance.

The problem is the default size limit for replicated data. Your machine appears to have the default setting of 65536 bytes per replicated column. This only affects TEXT and IMAGE columns, since nothing else can be large enough to have problems with that setting. IF you decide to change this setting, the code snippet you need is:EXECUTE sp_configure 'max text repl size (B)', 655360 -- or pick another size to suit you-PatP|||I had forgotten that our techs are moving SQL server and our scanned documents over to its own server and one of the databases I created is there.

Ok Now I understand thank you again

I'm Stumped! - (AS 2005 - Remote Connections Cannot be enabled)

Hello SQL pros -
I am having a problem with enabling the remote connections for Analysis
Services 2005, Here's the gist...
--Error Information--
"To configure Analysis Services for Remote Connections, you must be an
adminsitrator for Analysis Services Windows Service (SQLSAC).
Additonal Info
A connection cannot be made. Ensure that service is running
(Microsoft.AnalysisServices)
No connection could be made because the target machine actively refused
it (System)."
--Actions Taken--
1) Analysis Services Windows Service was confirmed to be running.
2) Analysis Services Windows Service was stopped and restarted.
3) Analysis Services Windows Service (and SQL Accounts) confirmed to be
running under domain account with administrator priviledges.
4) Confirmed connectivity to Analysis Services instance through direct
connection (Windows Authentication)
--OS/System Information--
Windows 2000 Service Pack 5
--Clustering Roles--
Primary node for 1 SQL 2000 SP4 instance
Secondary node 1 SQL 2000 SP4 instance
Primary node for 1 Analysis Services 2000 instance
Primary node for 1 SQL 2005 instance, and primary
Primary node for 1 SQL 2005 Analysis Services Instance
I didn't find anything of relevance in the Windows logs or SQL logs.
So I am not sure where else to look. Is this a bug with 2005?
Any Suggestions to help troubleshoot are greatly appreciated!
thanks in advance.
benBP,
Do you have a firewall program installed? Is this or a previous version
CTP?
HTH
Jerry
"BP" <BPollitt01@.gmail.com> wrote in message
news:1148655002.461974.305510@.i39g2000cwa.googlegroups.com...
> Hello SQL pros -
> I am having a problem with enabling the remote connections for Analysis
> Services 2005, Here's the gist...
> --Error Information--
> "To configure Analysis Services for Remote Connections, you must be an
> adminsitrator for Analysis Services Windows Service (SQLSAC).
> Additonal Info
> A connection cannot be made. Ensure that service is running
> (Microsoft.AnalysisServices)
> No connection could be made because the target machine actively refused
> it (System)."
> --Actions Taken--
> 1) Analysis Services Windows Service was confirmed to be running.
> 2) Analysis Services Windows Service was stopped and restarted.
> 3) Analysis Services Windows Service (and SQL Accounts) confirmed to be
> running under domain account with administrator priviledges.
> 4) Confirmed connectivity to Analysis Services instance through direct
> connection (Windows Authentication)
> --OS/System Information--
> Windows 2000 Service Pack 5
> --Clustering Roles--
> Primary node for 1 SQL 2000 SP4 instance
> Secondary node 1 SQL 2000 SP4 instance
> Primary node for 1 Analysis Services 2000 instance
> Primary node for 1 SQL 2005 instance, and primary
> Primary node for 1 SQL 2005 Analysis Services Instance
> I didn't find anything of relevance in the Windows logs or SQL logs.
> So I am not sure where else to look. Is this a bug with 2005?
> Any Suggestions to help troubleshoot are greatly appreciated!
> thanks in advance.
> ben
>

I'm Stumped! - (AS 2005 - Remote Connections Cannot be enabled)

Hello SQL pros -
I am having a problem with enabling the remote connections for Analysis
Services 2005, Here's the gist...
--Error Information--
"To configure Analysis Services for Remote Connections, you must be an
adminsitrator for Analysis Services Windows Service (SQLSAC).
Additonal Info
A connection cannot be made. Ensure that service is running
(Microsoft.AnalysisServices)
No connection could be made because the target machine actively refused
it (System)."
--Actions Taken--
1) Analysis Services Windows Service was confirmed to be running.
2) Analysis Services Windows Service was stopped and restarted.
3) Analysis Services Windows Service (and SQL Accounts) confirmed to be
running under domain account with administrator priviledges.
4) Confirmed connectivity to Analysis Services instance through direct
connection (Windows Authentication)
--OS/System Information--
Windows 2000 Service Pack 5
--Clustering Roles--
Primary node for 1 SQL 2000 SP4 instance
Secondary node 1 SQL 2000 SP4 instance
Primary node for 1 Analysis Services 2000 instance
Primary node for 1 SQL 2005 instance, and primary
Primary node for 1 SQL 2005 Analysis Services Instance
I didn't find anything of relevance in the Windows logs or SQL logs.
So I am not sure where else to look. Is this a bug with 2005?
Any Suggestions to help troubleshoot are greatly appreciated!
thanks in advance.
benBP,
Do you have a firewall program installed? Is this or a previous version
CTP?
HTH
Jerry
"BP" <BPollitt01@.gmail.com> wrote in message
news:1148655002.461974.305510@.i39g2000cwa.googlegroups.com...
> Hello SQL pros -
> I am having a problem with enabling the remote connections for Analysis
> Services 2005, Here's the gist...
> --Error Information--
> "To configure Analysis Services for Remote Connections, you must be an
> adminsitrator for Analysis Services Windows Service (SQLSAC).
> Additonal Info
> A connection cannot be made. Ensure that service is running
> (Microsoft.AnalysisServices)
> No connection could be made because the target machine actively refused
> it (System)."
> --Actions Taken--
> 1) Analysis Services Windows Service was confirmed to be running.
> 2) Analysis Services Windows Service was stopped and restarted.
> 3) Analysis Services Windows Service (and SQL Accounts) confirmed to be
> running under domain account with administrator priviledges.
> 4) Confirmed connectivity to Analysis Services instance through direct
> connection (Windows Authentication)
> --OS/System Information--
> Windows 2000 Service Pack 5
> --Clustering Roles--
> Primary node for 1 SQL 2000 SP4 instance
> Secondary node 1 SQL 2000 SP4 instance
> Primary node for 1 Analysis Services 2000 instance
> Primary node for 1 SQL 2005 instance, and primary
> Primary node for 1 SQL 2005 Analysis Services Instance
> I didn't find anything of relevance in the Windows logs or SQL logs.
> So I am not sure where else to look. Is this a bug with 2005?
> Any Suggestions to help troubleshoot are greatly appreciated!
> thanks in advance.
> ben
>

Im getting a subquery returned more than 1 value error. Please Help!

The error is occurring in the where clause that I spaced out down below, Its dealing with the State_No I know for a fact. I can't figure out how to run it in the Query anzlyzer to relate to match up with another table

PLEASE HELP!!!!!!

This is the error I'm getting in the DTS package i ran and query analyzer:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Update HBC_Boiler_Inspection
set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "

strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = " & rs1 ("State_No") & ") "try

Boiler_ID IN (Select ID from ...

caution: this will update all the boilers selected in the subquery

rudy

Im getting a Login failed... error when trying to open my database from my default.aspx.vb

Good morning,

When I start my browser and try to open my (SQL Server 2000) database, using the 'Integrated Windows Authentication' method, I get the following error msg:
[SqlException: Login failed for user 'MYDOMAIN\ASPNET'.].

Where DOMAIN = MYDOMAIN
and USER = ASPNET
I have made this user known to the SQL Server 2000...

I opened my browser and executed the following function, which is in my default.aspx.vb file:

Public Function checkuser(ByVal userName As String, ByVal password As String) As Boolean
Dim strConnection As String = _
"Data Source=localhost;Initial Catalog=dbo; Integrated Security=True"
Dim sqlconQCards As SqlConnection = New SqlConnection(strConnection)
sqlconQCards.Open()
End Function

Rather than opening the database, the error message, shown above, is displayed.

I'd appreciate any help.

Thanks,
PatDid you add the user in the database itself ?!

Rudy|||Hi Rudy,

I finally got it to work by changing to 'SQL Server Authentication'. It requres that the user/pswd be supplied.

The 'Integrated Windows Authentication', which does not require the user/pswd, is not working for me...This is the Microsoft recommended method...

I am using Microsoft SQL Server 2000...

Thanks,
Pat

Illegal xml character error during Bulk Load

Hello,
I am receiving the error 'Illegal xml character' a Bulk Load insert using
SQLXML 3.0
Has anyone else received this error? Any help would be greatly appreciated.
Thanks!
Sean
This is an XML parser error. It means that you most likely have a byte code
in your data stream that XML does not allow (most likely in the range hex(0)
to hex(31) where only TAB, CR and LF are allowed).
In order to fix it, you need to clean your data.
Best regards
Michael
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:%23Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am receiving the error 'Illegal xml character' a Bulk Load insert using
> SQLXML 3.0
> Has anyone else received this error? Any help would be greatly
> appreciated.
> Thanks!
> Sean
>
|||Your input Xml file could be an invalid Xml file. Try to fix the errors
before you pass it to Bulkload. You may use IE to check if the Xml file is
valid by simply loading it.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:#Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am receiving the error 'Illegal xml character' a Bulk Load insert using
> SQLXML 3.0
> Has anyone else received this error? Any help would be greatly
appreciated.
> Thanks!
> Sean
>
|||Thanks for the quick reply.
Sorry for such a newbie question, but how do I begin to clean this data?
The file is a 500 MB XML document and I'm not sure how to clean out these
types of characters. Any suggestions?
Thanks Michael,
Sean
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Oj8yEoZpEHA.1136@.TK2MSFTNGP12.phx.gbl...
> This is an XML parser error. It means that you most likely have a byte
> code in your data stream that XML does not allow (most likely in the range
> hex(0) to hex(31) where only TAB, CR and LF are allowed).
> In order to fix it, you need to clean your data.
> Best regards
> Michael
> "Sean McCain" <sean.mccain@.windenrane.com> wrote in message
> news:%23Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
>
|||Bertran,
I've tried opening in IE, but the page times out after 20 mins. The file is
about 500 MB. I have a total of 3 XML files that need importing and I have
had success importing the smallest of these files (500 & 5000 KB). It's
the big one that errors out almost immediately.
The task continues to be searching for the illegal XML syntax...
Sean
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:Oe853sZpEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Your input Xml file could be an invalid Xml file. Try to fix the errors
> before you pass it to Bulkload. You may use IE to check if the Xml file is
> valid by simply loading it.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Sean McCain" <sean.mccain@.windenrane.com> wrote in message
> news:#Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> appreciated.
>
|||The easiest is to run a script that basically streams over the 500MB and
removes all offending characters.
Best regards
Michael
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:ux8QSuZpEHA.1688@.TK2MSFTNGP10.phx.gbl...
> Thanks for the quick reply.
> Sorry for such a newbie question, but how do I begin to clean this data?
> The file is a 500 MB XML document and I'm not sure how to clean out these
> types of characters. Any suggestions?
> Thanks Michael,
> Sean
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:Oj8yEoZpEHA.1136@.TK2MSFTNGP12.phx.gbl...
>
|||Try xmlspy, the home edition is free and it will validate your file assuming
it can load it, it's worth a try
john
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:OFxliIapEHA.3896@.TK2MSFTNGP15.phx.gbl...
> Bertran,
> I've tried opening in IE, but the page times out after 20 mins. The file
> is about 500 MB. I have a total of 3 XML files that need importing and I
> have had success importing the smallest of these files (500 & 5000 KB).
> It's the big one that errors out almost immediately.
> The task continues to be searching for the illegal XML syntax...
> Sean
>
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:Oe853sZpEHA.1688@.TK2MSFTNGP10.phx.gbl...
>
|||There is probably an illegal xml character in your data file that you are
uploading to the server.
- Chandra
"Sean McCain" <sean.mccain@.windenrane.com> wrote in message
news:%23Bs4dPZpEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am receiving the error 'Illegal xml character' a Bulk Load insert using
> SQLXML 3.0
> Has anyone else received this error? Any help would be greatly
appreciated.
> Thanks!
> Sean
>

illegal variable name/number error in script component

Hi There,

I am trying to populate a table in Oracle Db using SSIS. For some reason, I get this error..

illegal variable name/number

This is what I have....

Public Overrides Sub PreExecute()

oracleCmd = New OracleCommand("INSERT INTO Temp(ID) VALUES(@.KEY)", oracleConn)

oracleParam = New OracleParameter("@.KEY", OracleType.Number)

oracleCmd.Parameters.Add(oracleParam)

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'With oracleCmd

.Parameters("@.KEY").Value = CType(Row.KEY, OracleClient.OracleNumber)

.ExecuteNonQuery()

End With

End Sub

==

Row.Key returns a row from the oracel table which has one column of type numeric..

Can somebody please help me?

Thanks a lot!!

Question... What is @.KEY?

Also, why aren't you using an OLE DB destination?

I'll try this question again...

When I run an RS report on server1 that connects to a sql database on server2
I get the error:
"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. "
Suggestions?Jimbo,
Check the IIS settings, under the "Directory Security" tab on the website
properties.
Reeves|||A few possibilities. Are you calling the report on server1 from another
server. If so, then you might be seeing the double hop issue.
My guess is that your web site on Server1 is running anonymous AND you are
using windows security credentials to access the sql database source. In
report manager open up the data source. If you have Windows Integrated
Security checked that means RS will use the windows credentials of the
person running the report to access the data. If the website is in anonymous
mode then RS does not know who that user is. Also, all users will be quests,
nobody will have admin rights (including yourself) if the website is in
anonymous mode.
Two points, you cannot run the website in anonymous mode (unless you
perpetually take it out of anonymous mode anytime you need admin
priveleges). Second, my suggestion is to pick one of the other two
credentials options, have all users for a report use the same account to
access the data. I run SQL in mixed mode and have a SQL login just for
reporting that gives only readonly data access. This is better for
performance too since connection pooling will now work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:543C8DE5-5093-4545-BC0D-F25D78942D4E@.microsoft.com...
> When I run an RS report on server1 that connects to a sql database on
> server2
> I get the error:
> "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. "
>
> Suggestions?

IIS Virtual Directory Management for SQL Server

I got the IIS Virtual Directory Management for SQL Server working but I
deleted the Virtual Directory and then went to reinstate it I got the error
"An Error occurred creating virtual directory [VD Name] The virtual
directory already exists"
Do I need to reinstall anything?
What are the correct service packs to have on a server Windows 2003 and SQL
2000?
"Dean" wrote:

> I got the IIS Virtual Directory Management for SQL Server working but I
> deleted the Virtual Directory and then went to reinstate it I got the error
> "An Error occurred creating virtual directory [VD Name] The virtual
> directory already exists"
> Do I need to reinstall anything?
|||Hi Dean,
There is no special requirements for a specific service pack for Windows
2003. For SQL 2000, you should have SP3 with you.
Are you using the MMC tool from the SQL 2000 installation or have you
installed the latest version of SQLXML from
(http://msdn.microsoft.com/library/de...-us/dnanchor/h
tml/anch_SQLXML.asp)
Generally this kind of error should not happen and is not dependent on the
version of the operating system. In your case you should check using the
Internet Information Services manager on the machine to see if the Virtual
Directory still exists If it is not there and you still see the error you
can use the Metabase edit tool from http://support.microsoft.com/kb/840671
to check and edit the IIS metabase store.
Thank you,
Amar Nalla [MSFT]
"Dean" <Dean@.discussions.microsoft.com> wrote in message
news:EA982781-9011-4121-B266-A45ECF5D864D@.microsoft.com...
> What are the correct service packs to have on a server Windows 2003 and
SQL[vbcol=seagreen]
> 2000?
> "Dean" wrote:
error[vbcol=seagreen]

IIS virtual directory - what to do?

Why why why do I get the message "an error occured connecting to the server 'servername'. This server does not exist or is unavailable" when trying to connect to a server in IIS. I know the server exists (it's the same computer I'm using) and that is up and running. Also, why do I need to connect, can I use the SQLXML features without a virtual directory? I have no need to access my DB from the Internet.
Thank you,
HelenaHi

Is the SQL account you are using to connect ot SQL Server, actually exist on the SQL server?

Is the SQL server oyu are trying to connect to the default instance or a named instance?

Are you using OLEDB or ODBC connection to SQL from IIS?

Cheers

SG|||Yes, the SQL account exists.

The server I'm trying to connect is a named instance. (But it should be the default as well since i only have one server?)

I have no idea whether I use OLEDB and ODBC connection, how do I know..?

Thank you, Helena

Originally posted by sqlguy7777
Hi

Is the SQL account you are using to connect ot SQL Server, actually exist on the SQL server?

Is the SQL server oyu are trying to connect to the default instance or a named instance?

Are you using OLEDB or ODBC connection to SQL from IIS?

Cheers

SG|||Hi Helena,

If you are using OLEDB ( i.e a DSN-less connection ) you will see the connection string like this in your ASP page :

Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=SQLADMIN1;UID=testiis1sql;PWD=password;DATA BASE=ServerMon"

If you had an ODBC ( i.e. a DSN connection ) it would look like this :

"Provider=MSDASQL;DSN=dsnName;UID=MyUserID;PWD=MyPa ssword;"

see also :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/pg_ado_providers_2.asp

See also :

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForODBCDatabases

What does your connection look like?

Cheers

SG.

IIS Synchronizing Data Error

hi..need help here,
I sometime get error when syncronizing data at New Subscription Wizard, and sometime I dont.but right now i cant sync, this is me error messages :
"Synchronizing Data (Error)
Messages
A request to send data to the computer running IIS has failed. For more information, see HRESULT.
HRESULT 0x80070002 (28037)
The operation could not be completed."

I've turn off the firewall, turn off the antivirus, plug the LAN cable but still error (Maybe it has to do with it).A long time a go I've had this error, and then I decided to reinstall SQL Server and try it again and it work, now it back to me again, do I have to reinstalled it again(hopefully it solved my problem)?or any otherway?

And want to ask about SQL Mobile, When I choose the Merge, the uniqueidentifier will be add at my table right?what should I do at inserting query process, I cant just insert it with NULL value right?
After my subscription doing input data, is it my publications will be automatic updated?
I'm little lost here..need advise

Its a miracle.. "_" now I can sync it without any changes on my computer, I give one more shot try to sync after I write this thread and then it works!!

but still curious why sometime I can, sometime I cant?

and still know about uniqueidentifier, please..

IIS SQL Server connection error (urgent)

I keep getting this error when I try to connect to my SQL Server DB

Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'Administrator'. Reason: Not associated with a trusted SQL Server connection.

when I installed SQL Server I set username and password to windows default which is Administrator and no password

the asp files are on IIS (localhost)

connstr = "Provider=SQLOLEDB;" &_
"Data Source=(local);" &_
"Initial Catalog=ShotWatcher;"&_
"User ID=Administrator;"&_
"Password="

shouldn't this work?

Please Help

JustinDo you have a SQL Server Standard account called Administrator? Or you're trying to use Windows account with the same name? If it's the latter, - remove User ID and Password attributes and replace them with Integrated Security=SSPI.|||And in connection string you should specify TRUSTED_CONNECTION=TRUE and try using servername instead of specifying local.

KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;306586) to more about it.|||i'll try that, thx

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?
The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>
|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
Ekrem nsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
Ekrem nsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
--
Ekrem Önsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS issue

I just installed IIS on my windows xp pro machine and when I try to access a
website on the local IIS server, I get the following error message:

"You are not authorized to view this page"

I have enabled "Anonymous access" inside Directory Security option of IIS
and I also granted full NTFS permissions to all users so that the IIS root
directory is readily accessible. However, I am still not able to access any
website that resides on the localhost.

Please help.

Thanks.
JessIs this a SQL Server question?

IIS for SQL Server - problem

I configured IIS Virtual Directory Management for SQL Server Utility as
is described in Books Online, but I receive 404 error.
I gave everywhere everyone full access where I can…. But I don’t know
where to look for an error…
IIS seems to work properly because other WWW are working OK.
My SQL Server is from Small Business Server, maybe it shouldn’t work
under SBS?
Hello,
Please let me know the SQL server version you use. For example, SQL server
2000 Standard Edition with SP3. I suggest that you perform the following
steps to narrow down the issue:
1. Make sure your IIS server works fine.
2. Create the virtual directory <nwind>. For more information about how to
configure a virtual directory by using the IIS Virtual Directory Management
SQL Server utility, visit the following MSDN Web site:
http://msdn.microsoft.com/library/en..._xml1_2xvd.asp
3. Open Microsoft Internet Explorer and locate:
http://IISServer/nwind?sql=SELECT+*+...TO&roo t=root
If you receive the error message "HTTP error 404 - File or directory not
found", please perform the steps in the following article 810784:
810784 Error message "HTTP error 404 - File or directory not found" occurs
when
http://support.microsoft.com/?id=810784
Check the issue again. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

IIS for SQL Server - problem

I configured IIS Virtual Directory Management for SQL Server Utility as
is described in Books Online, but I receive 404 error.
I gave everywhere everyone full access where I can…. But I don’t know
where to look for an error…
IIS seems to work properly because other WWW are working OK.
My SQL Server is from Small Business Server, maybe it shouldn’t work
under SBS?Hello,
Please let me know the SQL server version you use. For example, SQL server
2000 Standard Edition with SP3. I suggest that you perform the following
steps to narrow down the issue:
1. Make sure your IIS server works fine.
2. Create the virtual directory <nwind>. For more information about how to
configure a virtual directory by using the IIS Virtual Directory Management
SQL Server utility, visit the following MSDN Web site:
http://msdn.microsoft.com/library/e...c_xml1_2xvd.asp
3. Open Microsoft Internet Explorer and locate:
http://IISServer/nwind? sql=SELECT+...r />
&root=root
If you receive the error message "HTTP error 404 - File or directory not
found", please perform the steps in the following article 810784:
810784 Error message "HTTP error 404 - File or directory not found" occurs
when
http://support.microsoft.com/?id=810784
Check the issue again. I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.