Access to SQL Server Upsize Query Problem

Hi,

I have a query written in Jet-SQL which i need to convert to T-SQL as I am upgrading an Access DB to SQL Server 2000.

Query is:
SELECT WDF_TBLSKILL.RECID,WDF_TBLSKILL.TECHNOLOGY,Iif(EXI STS(SELECT * FROM WDF_TBLCOMPANY_TBLSKILL_LNK WHERE SKILLID=WDF_TBLSKILL.RECID AND COMPANYID=857),1,0) AS CHOSENSKILL FROM WDF_TBLSKILL ORDER BY WDF_TBLSKILL.TECHNOLOGY ASC;

It is falling over on the 3rd select item (The Iif statement) which I want to return a 1 if any records are found in the subquery or 0 if they aren't.

I know Iif is not supported in SQL but can't find anything that seems to do the job.

Please help!!!SELECT WDF_TBLSKILL.RECID
, WDF_TBLSKILL.TECHNOLOGY
, case when EXISTS
(SELECT * FROM WDF_TBLCOMPANY_TBLSKILL_LNK
WHERE SKILLID=WDF_TBLSKILL.RECID
AND COMPANYID=857) then 1 else 0 end AS CHOSENSKILL
FROM WDF_TBLSKILL
ORDER BY WDF_TBLSKILL.TECHNOLOGY ASC|||thanks mate - much appreciated. you've saved me a lot of time and effort.

Access to sql database from two app servers

Hi - I have a single database server "database" and two app servers
written in VB.NET trying to access this database. Lets say the app
servers are "serverA" and "serverB".
I get a "sql server does not exist or access denied" error during
the connect part of my code from serverB.
There is no problem with connecting from serverA. I checked the
connection string used and they are similar and no difference except
the workstation id=" " which has the respective values. Both the
servers try connecting to the same database port.
One thing that our firm uses is a service id or custom id for the
domain that the database provides access to.
Has anyone experienced such a problem before. Please help me in
this.
Thanks in advance.
AnushThe database is sql 2000.
On Jan 23, 10:27 pm, iaman...@.gmail.com wrote:
> Hi - I have a single database server "database" and two app servers
> written in VB.NET trying to access this database. Lets say the app
> servers are "serverA" and "serverB".
> I get a "sql server does not exist or access denied" error during
> the connect part of my code from serverB.
> There is no problem with connecting from serverA. I checked the
> connection string used and they are similar and no difference except
> the workstation id=" " which has the respective values. Both the
> servers try connecting to the same database port.
> One thing that our firm uses is a service id or custom id for the
> domain that the database provides access to.
> Has anyone experienced such a problem before. Please help me in
> this.
> Thanks in advance.
> Anush|||Hi,
try to solve the problem on a physical layer first. Is the machine
reachable from the app server ? Is it pingable ? Is any firewall
activated or no rule implemented to let the app Server talk to the SQL
Server ?
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hi
Is it linked server? Have you tried to connect to SQL_B with the same user
but using Query Analyzer?
<iamanush@.gmail.com> wrote in message
news:1169616422.571959.132600@.h3g2000cwc.googlegroups.com...
> Hi - I have a single database server "database" and two app servers
> written in VB.NET trying to access this database. Lets say the app
> servers are "serverA" and "serverB".
> I get a "sql server does not exist or access denied" error during
> the connect part of my code from serverB.
> There is no problem with connecting from serverA. I checked the
> connection string used and they are similar and no difference except
> the workstation id=" " which has the respective values. Both the
> servers try connecting to the same database port.
> One thing that our firm uses is a service id or custom id for the
> domain that the database provides access to.
> Has anyone experienced such a problem before. Please help me in
> this.
> Thanks in advance.
> Anush
>|||HI Jens - The machine is pingable from this serverB and I can also see
the connection coming in to the db box. I see the log registering the
incoming connection, however it fails in the db and logs an exception
on the log. so its not the physical layer i believe.
Thx.
Anush
On Jan 24, 12:21 am, "Jens" <J...@.sqlserver2005.de> wrote:
> Hi,
> try to solve the problem on a physical layer first. Is the machine
> reachable from the app server ? Is it pingable ? Is any firewall
> activated or no rule implemented to let the app Server talk to the SQL
> Server ?
> HTH, Jens K. Suessmeyer.
> --http://www.sqlserver2005.de
> --|||i havent tried this yet. Will try now.
Anush
On Jan 24, 12:32 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> Is it linked server? Have you tried to connect to SQL_B with the same user
> but using Query Analyzer?
> <iaman...@.gmail.com> wrote in messagenews:1169616422.571959.132600@.h3g2000cwc.googlegroups.com...
> > Hi - I have a single database server "database" and two app servers
> > written in VB.NET trying to access this database. Lets say the app
> > servers are "serverA" and "serverB".
> > I get a "sql server does not exist or access denied" error during
> > the connect part of my code from serverB.
> > There is no problem with connecting from serverA. I checked the
> > connection string used and they are similar and no difference except
> > the workstation id=" " which has the respective values. Both the
> > servers try connecting to the same database port.
> > One thing that our firm uses is a service id or custom id for the
> > domain that the database provides access to.
> > Has anyone experienced such a problem before. Please help me in
> > this.
> > Thanks in advance.
> > Anush|||Ok, can you please post the connectring here ? You can obfuscate it a
bit to cover your secrets. WHat is the database, is it a service or a
Windows 7 Console application ? There might apply the (Access denied
part) to it and you are providing the wrong credentials. You can track
the unsuccessfull connections in profiler and can see which user is
currently accessing the database, but perhaps the post of the
connectionstring might light up the situation.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--