Can not access SQL Server

Hi,
In this case, the environment is:
Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address: 10.35.3.43)
Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a WinForms
application. (In the same subnet)
In the WinForms application, I can connect to the SQL server sucessfully,
but in the ASP.Net application, I can not, the error message say that "SQL
Server does not exist, or access deny".
The code is the same in ASP.Net application and WinForms application:
==>>
string cnstring = "user id=sa;data source='10.35.3.43';persist security
info=True;initial catalog=MyDB;password='MyPassword'";
string cmdstring = "SELECT Count(*) FROM FunctionCode";
System.Data.SqlClient.SqlConnection cn = new
System.Data.SqlClient.SqlConnection(cnstring);
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand(cmdstring, cn);
cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
int count = (int)cmd.ExecuteScalar();
cn.Close();
Response.Write(count.ToString());
<<==
How to fix this problem?
Thanks in advance
KevinRun a profiler trace on the SQL server to see if the login attempt is making
it the the SQL server. This will give you insight in to where to look next
for the problem, network or program.
--
Nik Marshall-Blank MCSD/MCDBA
"Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
news:%23g6oPUDtFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In this case, the environment is:
> Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address:
> 10.35.3.43)
> Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a
> WinForms application. (In the same subnet)
> In the WinForms application, I can connect to the SQL server sucessfully,
> but in the ASP.Net application, I can not, the error message say that "SQL
> Server does not exist, or access deny".
> The code is the same in ASP.Net application and WinForms application:
> ==>>
> string cnstring = "user id=sa;data source='10.35.3.43';persist security
> info=True;initial catalog=MyDB;password='MyPassword'";
> string cmdstring = "SELECT Count(*) FROM FunctionCode";
> System.Data.SqlClient.SqlConnection cn = new
> System.Data.SqlClient.SqlConnection(cnstring);
> System.Data.SqlClient.SqlCommand cmd = new
> System.Data.SqlClient.SqlCommand(cmdstring, cn);
> cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
> int count = (int)cmd.ExecuteScalar();
> cn.Close();
> Response.Write(count.ToString());
> <<==> How to fix this problem?
> Thanks in advance
> Kevin
>|||It's not network problem, I can connect through a WinForm application. I
found something in the following article, I try but still doesn't work.
http://support.microsoft.com/default.aspx?scid=kb;en-us;888228
By the way, the two machines are in different domain.
Regars
Kevin
"Nik Marshall-Blank" <Nik@.Operatelecom.com> дÈëÏûÏ¢ÐÂÎÅ:4iSTe.305886$WN5.7191@.fe02.news.easynews.com...
> Run a profiler trace on the SQL server to see if the login attempt is
> making it the the SQL server. This will give you insight in to where to
> look next for the problem, network or program.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
> news:%23g6oPUDtFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> In this case, the environment is:
>> Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address:
>> 10.35.3.43)
>> Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a
>> WinForms application. (In the same subnet)
>> In the WinForms application, I can connect to the SQL server sucessfully,
>> but in the ASP.Net application, I can not, the error message say that
>> "SQL Server does not exist, or access deny".
>> The code is the same in ASP.Net application and WinForms application:
>> ==>>
>> string cnstring = "user id=sa;data source='10.35.3.43';persist security
>> info=True;initial catalog=MyDB;password='MyPassword'";
>> string cmdstring = "SELECT Count(*) FROM FunctionCode";
>> System.Data.SqlClient.SqlConnection cn = new
>> System.Data.SqlClient.SqlConnection(cnstring);
>> System.Data.SqlClient.SqlCommand cmd = new
>> System.Data.SqlClient.SqlCommand(cmdstring, cn);
>> cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
>> int count = (int)cmd.ExecuteScalar();
>> cn.Close();
>> Response.Write(count.ToString());
>> <<==>> How to fix this problem?
>> Thanks in advance
>> Kevin
>>
>|||I would expect that to apply to both connections from Winform and ASP.NET.
Can you perform a profile trace on to see what credentials are being used
for the failing connection.
--
Nik Marshall-Blank MCSD/MCDBA
"Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
news:%237j8zSFtFHA.2792@.tk2msftngp13.phx.gbl...
> It's not network problem, I can connect through a WinForm application. I
> found something in the following article, I try but still doesn't work.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;888228
> By the way, the two machines are in different domain.
> Regars
> Kevin
>
>
> "Nik Marshall-Blank" <Nik@.Operatelecom.com>
> дÈëÏûÏ¢ÐÂÎÅ:4iSTe.305886$WN5.7191@.fe02.news.easynews.com...
>> Run a profiler trace on the SQL server to see if the login attempt is
>> making it the the SQL server. This will give you insight in to where to
>> look next for the problem, network or program.
>> --
>> Nik Marshall-Blank MCSD/MCDBA
>> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
>> news:%23g6oPUDtFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> In this case, the environment is:
>> Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address:
>> 10.35.3.43)
>> Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a
>> WinForms application. (In the same subnet)
>> In the WinForms application, I can connect to the SQL server
>> sucessfully, but in the ASP.Net application, I can not, the error
>> message say that "SQL Server does not exist, or access deny".
>> The code is the same in ASP.Net application and WinForms application:
>> ==>>
>> string cnstring = "user id=sa;data source='10.35.3.43';persist
>> security info=True;initial catalog=MyDB;password='MyPassword'";
>> string cmdstring = "SELECT Count(*) FROM FunctionCode";
>> System.Data.SqlClient.SqlConnection cn = new
>> System.Data.SqlClient.SqlConnection(cnstring);
>> System.Data.SqlClient.SqlCommand cmd = new
>> System.Data.SqlClient.SqlCommand(cmdstring, cn);
>> cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
>> int count = (int)cmd.ExecuteScalar();
>> cn.Close();
>> Response.Write(count.ToString());
>> <<==>> How to fix this problem?
>> Thanks in advance
>> Kevin
>>
>>
>|||Hi, Nik,
I use profile trace, but there is no login or login fail events when using
ASP.Net. There is login even when using WinForm.
The source code and connection string are the same in WinForm and ASP.Net.
"Nik Marshall-Blank" <Nik@.Operatelecom.com> дÈëÏûÏ¢ÐÂÎÅ:XrTTe.308487$kM5.134787@.fe01.news.easynews.com...
>I would expect that to apply to both connections from Winform and ASP.NET.
>Can you perform a profile trace on to see what credentials are being used
>for the failing connection.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
> news:%237j8zSFtFHA.2792@.tk2msftngp13.phx.gbl...
>> It's not network problem, I can connect through a WinForm application. I
>> found something in the following article, I try but still doesn't work.
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;888228
>> By the way, the two machines are in different domain.
>> Regars
>> Kevin
>>
>>
>> "Nik Marshall-Blank" <Nik@.Operatelecom.com> дÈëÏûÏ¢ÐÂÎÅ:4iSTe.305886$WN5.7191@.fe02.news.easynews.com...
>> Run a profiler trace on the SQL server to see if the login attempt is
>> making it the the SQL server. This will give you insight in to where to
>> look next for the problem, network or program.
>> --
>> Nik Marshall-Blank MCSD/MCDBA
>> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
>> news:%23g6oPUDtFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> In this case, the environment is:
>> Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address:
>> 10.35.3.43)
>> Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a
>> WinForms application. (In the same subnet)
>> In the WinForms application, I can connect to the SQL server
>> sucessfully, but in the ASP.Net application, I can not, the error
>> message say that "SQL Server does not exist, or access deny".
>> The code is the same in ASP.Net application and WinForms application:
>> ==>>
>> string cnstring = "user id=sa;data source='10.35.3.43';persist
>> security info=True;initial catalog=MyDB;password='MyPassword'";
>> string cmdstring = "SELECT Count(*) FROM FunctionCode";
>> System.Data.SqlClient.SqlConnection cn = new
>> System.Data.SqlClient.SqlConnection(cnstring);
>> System.Data.SqlClient.SqlCommand cmd = new
>> System.Data.SqlClient.SqlCommand(cmdstring, cn);
>> cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
>> int count = (int)cmd.ExecuteScalar();
>> cn.Close();
>> Response.Write(count.ToString());
>> <<==>> How to fix this problem?
>> Thanks in advance
>> Kevin
>>
>>
>>
>|||Ok now we're getting somewhere, not sure where yet but we now going to look
at the ASP.Net server more closely.
But first just try adding the account under which ASP.Net runs as a login on
the SQL Server and see if it connects.
--
Nik Marshall-Blank MCSD/MCDBA
"Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
news:eYSsLGGtFHA.3908@.tk2msftngp13.phx.gbl...
> Hi, Nik,
> I use profile trace, but there is no login or login fail events when using
> ASP.Net. There is login even when using WinForm.
> The source code and connection string are the same in WinForm and ASP.Net.
>
>
> "Nik Marshall-Blank" <Nik@.Operatelecom.com>
> дÈëÏûÏ¢ÐÂÎÅ:XrTTe.308487$kM5.134787@.fe01.news.easynews.com...
>>I would expect that to apply to both connections from Winform and ASP.NET.
>>Can you perform a profile trace on to see what credentials are being used
>>for the failing connection.
>> --
>> Nik Marshall-Blank MCSD/MCDBA
>> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
>> news:%237j8zSFtFHA.2792@.tk2msftngp13.phx.gbl...
>> It's not network problem, I can connect through a WinForm application. I
>> found something in the following article, I try but still doesn't work.
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;888228
>> By the way, the two machines are in different domain.
>> Regars
>> Kevin
>>
>>
>> "Nik Marshall-Blank" <Nik@.Operatelecom.com>
>> дÈëÏûÏ¢ÐÂÎÅ:4iSTe.305886$WN5.7191@.fe02.news.easynews.com...
>> Run a profiler trace on the SQL server to see if the login attempt is
>> making it the the SQL server. This will give you insight in to where to
>> look next for the problem, network or program.
>> --
>> Nik Marshall-Blank MCSD/MCDBA
>> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
>> news:%23g6oPUDtFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> In this case, the environment is:
>> Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address:
>> 10.35.3.43)
>> Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a
>> WinForms application. (In the same subnet)
>> In the WinForms application, I can connect to the SQL server
>> sucessfully, but in the ASP.Net application, I can not, the error
>> message say that "SQL Server does not exist, or access deny".
>> The code is the same in ASP.Net application and WinForms application:
>> ==>>
>> string cnstring = "user id=sa;data source='10.35.3.43';persist
>> security info=True;initial catalog=MyDB;password='MyPassword'";
>> string cmdstring = "SELECT Count(*) FROM FunctionCode";
>> System.Data.SqlClient.SqlConnection cn = new
>> System.Data.SqlClient.SqlConnection(cnstring);
>> System.Data.SqlClient.SqlCommand cmd = new
>> System.Data.SqlClient.SqlCommand(cmdstring, cn);
>> cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
>> int count = (int)cmd.ExecuteScalar();
>> cn.Close();
>> Response.Write(count.ToString());
>> <<==>> How to fix this problem?
>> Thanks in advance
>> Kevin
>>
>>
>>
>>
>|||I now found the root cause. It's because the Machine A and Machine B are in
different domain, two domains are not trusted. If both machines in the same
domain, it works.
The reason why WinForm app succeeds is because Machine B have stored a use
account and password on Domain A, I could see the NT user name from sql
profiler, it's Domain A's user account.
"Nik Marshall-Blank" <Nik@.Operatelecom.com> дÈëÏûÏ¢ÐÂÎÅ:DEUTe.127980$Lg2.47868@.fe12.news.easynews.com...
> Ok now we're getting somewhere, not sure where yet but we now going to
> look at the ASP.Net server more closely.
> But first just try adding the account under which ASP.Net runs as a login
> on the SQL Server and see if it connects.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
> news:eYSsLGGtFHA.3908@.tk2msftngp13.phx.gbl...
>> Hi, Nik,
>> I use profile trace, but there is no login or login fail events when
>> using ASP.Net. There is login even when using WinForm.
>> The source code and connection string are the same in WinForm and
>> ASP.Net.
>>
>>
>> "Nik Marshall-Blank" <Nik@.Operatelecom.com> дÈëÏûÏ¢ÐÂÎÅ:XrTTe.308487$kM5.134787@.fe01.news.easynews.com...
>>I would expect that to apply to both connections from Winform and
>>ASP.NET. Can you perform a profile trace on to see what credentials are
>>being used for the failing connection.
>> --
>> Nik Marshall-Blank MCSD/MCDBA
>> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
>> news:%237j8zSFtFHA.2792@.tk2msftngp13.phx.gbl...
>> It's not network problem, I can connect through a WinForm application.
>> I found something in the following article, I try but still doesn't
>> work.
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;888228
>> By the way, the two machines are in different domain.
>> Regars
>> Kevin
>>
>>
>> "Nik Marshall-Blank" <Nik@.Operatelecom.com> дÈëÏûÏ¢ÐÂÎÅ:4iSTe.305886$WN5.7191@.fe02.news.easynews.com...
>> Run a profiler trace on the SQL server to see if the login attempt is
>> making it the the SQL server. This will give you insight in to where
>> to look next for the problem, network or program.
>> --
>> Nik Marshall-Blank MCSD/MCDBA
>> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
>> news:%23g6oPUDtFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> In this case, the environment is:
>> Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address:
>> 10.35.3.43)
>> Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a
>> WinForms application. (In the same subnet)
>> In the WinForms application, I can connect to the SQL server
>> sucessfully, but in the ASP.Net application, I can not, the error
>> message say that "SQL Server does not exist, or access deny".
>> The code is the same in ASP.Net application and WinForms application:
>> ==>>
>> string cnstring = "user id=sa;data source='10.35.3.43';persist
>> security info=True;initial catalog=MyDB;password='MyPassword'";
>> string cmdstring = "SELECT Count(*) FROM FunctionCode";
>> System.Data.SqlClient.SqlConnection cn = new
>> System.Data.SqlClient.SqlConnection(cnstring);
>> System.Data.SqlClient.SqlCommand cmd = new
>> System.Data.SqlClient.SqlCommand(cmdstring, cn);
>> cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
>> int count = (int)cmd.ExecuteScalar();
>> cn.Close();
>> Response.Write(count.ToString());
>> <<==>> How to fix this problem?
>> Thanks in advance
>> Kevin
>>
>>
>>
>>
>>
>

Can not access SQL Server

Hi,
In this case, the environment is:
Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address: 10.35.3.43)
Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a WinForms
application. (In the same subnet)
In the WinForms application, I can connect to the SQL server sucessfully,
but in the ASP.Net application, I can not, the error message say that "SQL
Server does not exist, or access deny".
The code is the same in ASP.Net application and WinForms application:
==>>
string cnstring = "user id=sa;data source='10.35.3.43';persist security
info=True;initial catalog=MyDB;password='MyPassword'";
string cmdstring = "SELECT Count(*) FROM FunctionCode";
System.Data.SqlClient.SqlConnection cn = new
System.Data.SqlClient.SqlConnection(cnstring);
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand(cmdstring, cn);
cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
int count = (int)cmd.ExecuteScalar();
cn.Close();
Response.Write(count.ToString());
<<==
How to fix this problem?
Thanks in advance
KevinRun a profiler trace on the SQL server to see if the login attempt is making
it the the SQL server. This will give you insight in to where to look next
for the problem, network or program.
Nik Marshall-Blank MCSD/MCDBA
"Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
news:%23g6oPUDtFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In this case, the environment is:
> Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address:
> 10.35.3.43)
> Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a
> WinForms application. (In the same subnet)
> In the WinForms application, I can connect to the SQL server sucessfully,
> but in the ASP.Net application, I can not, the error message say that "SQL
> Server does not exist, or access deny".
> The code is the same in ASP.Net application and WinForms application:
> ==>>
> string cnstring = "user id=sa;data source='10.35.3.43';persist security
> info=True;initial catalog=MyDB;password='MyPassword'";
> string cmdstring = "SELECT Count(*) FROM FunctionCode";
> System.Data.SqlClient.SqlConnection cn = new
> System.Data.SqlClient.SqlConnection(cnstring);
> System.Data.SqlClient.SqlCommand cmd = new
> System.Data.SqlClient.SqlCommand(cmdstring, cn);
> cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
> int count = (int)cmd.ExecuteScalar();
> cn.Close();
> Response.Write(count.ToString());
> <<==
> How to fix this problem?
> Thanks in advance
> Kevin
>|||It's not network problem, I can connect through a WinForm application. I
found something in the following article, I try but still doesn't work.
http://support.microsoft.com/defaul...kb;en-us;888228
By the way, the two machines are in different domain.
Regars
Kevin
"Nik Marshall-Blank" <Nik@.Operatelecom.com> д?:4iSTe.305886$WN5.7191@.fe02.news.eas
ynews.com...
> Run a profiler trace on the SQL server to see if the login attempt is
> making it the the SQL server. This will give you insight in to where to
> look next for the problem, network or program.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
> news:%23g6oPUDtFHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||I would expect that to apply to both connections from Winform and ASP.NET.
Can you perform a profile trace on to see what credentials are being used
for the failing connection.
Nik Marshall-Blank MCSD/MCDBA
"Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
news:%237j8zSFtFHA.2792@.tk2msftngp13.phx.gbl...
> It's not network problem, I can connect through a WinForm application. I
> found something in the following article, I try but still doesn't work.
> http://support.microsoft.com/defaul...kb;en-us;888228
> By the way, the two machines are in different domain.
> Regars
> Kevin
>
>
> "Nik Marshall-Blank" <Nik@.Operatelecom.com>
> д?:4iSTe.305886$WN5.7191@.fe02.news.easynews.com...
>|||Hi, Nik,
I use profile trace, but there is no login or login fail events when using
ASP.Net. There is login even when using WinForm.
The source code and connection string are the same in WinForm and ASP.Net.
"Nik Marshall-Blank" <Nik@.Operatelecom.com> д?:XrTTe.308487$kM5.134787@.fe01.news.e
asynews.com...
>I would expect that to apply to both connections from Winform and ASP.NET.
>Can you perform a profile trace on to see what credentials are being used
>for the failing connection.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
> news:%237j8zSFtFHA.2792@.tk2msftngp13.phx.gbl...
>|||Ok now we're getting somewhere, not sure where yet but we now going to look
at the ASP.Net server more closely.
But first just try adding the account under which ASP.Net runs as a login on
the SQL Server and see if it connects.
Nik Marshall-Blank MCSD/MCDBA
"Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
news:eYSsLGGtFHA.3908@.tk2msftngp13.phx.gbl...
> Hi, Nik,
> I use profile trace, but there is no login or login fail events when using
> ASP.Net. There is login even when using WinForm.
> The source code and connection string are the same in WinForm and ASP.Net.
>
>
> "Nik Marshall-Blank" <Nik@.Operatelecom.com>
> д?:XrTTe.308487$kM5.134787@.fe01.news.easynews.com...
>|||I now found the root cause. It's because the Machine A and Machine B are in
different domain, two domains are not trusted. If both machines in the same
domain, it works.
The reason why WinForm app succeeds is because Machine B have stored a use
account and password on Domain A, I could see the NT user name from sql
profiler, it's Domain A's user account.
"Nik Marshall-Blank" <Nik@.Operatelecom.com> д?:DEUTe.127980$Lg2.47868@.fe12.news.ea
synews.com...
> Ok now we're getting somewhere, not sure where yet but we now going to
> look at the ASP.Net server more closely.
> But first just try adding the account under which ASP.Net runs as a login
> on the SQL Server and see if it connects.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
> news:eYSsLGGtFHA.3908@.tk2msftngp13.phx.gbl...
>

Can not access SQL Server

Hi,
In this case, the environment is:
Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address: 10.35.3.43)
Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a WinForms
application. (In the same subnet)
In the WinForms application, I can connect to the SQL server sucessfully,
but in the ASP.Net application, I can not, the error message say that "SQL
Server does not exist, or access deny".
The code is the same in ASP.Net application and WinForms application:
==>>
string cnstring = "user id=sa;data source='10.35.3.43';persist security
info=True;initial catalog=MyDB;password='MyPassword'";
string cmdstring = "SELECT Count(*) FROM FunctionCode";
System.Data.SqlClient.SqlConnection cn = new
System.Data.SqlClient.SqlConnection(cnstring);
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand(cmdstring, cn);
cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
int count = (int)cmd.ExecuteScalar();
cn.Close();
Response.Write(count.ToString());
<<==
How to fix this problem?
Thanks in advance
Kevin
Run a profiler trace on the SQL server to see if the login attempt is making
it the the SQL server. This will give you insight in to where to look next
for the problem, network or program.
Nik Marshall-Blank MCSD/MCDBA
"Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
news:%23g6oPUDtFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In this case, the environment is:
> Machine A: SQL Server 2000 SP3 on Windows 2003 SP1. (IP Address:
> 10.35.3.43)
> Machine B: IIS Server on Windows XP SP2, an ASP.Net application, a
> WinForms application. (In the same subnet)
> In the WinForms application, I can connect to the SQL server sucessfully,
> but in the ASP.Net application, I can not, the error message say that "SQL
> Server does not exist, or access deny".
> The code is the same in ASP.Net application and WinForms application:
> ==>>
> string cnstring = "user id=sa;data source='10.35.3.43';persist security
> info=True;initial catalog=MyDB;password='MyPassword'";
> string cmdstring = "SELECT Count(*) FROM FunctionCode";
> System.Data.SqlClient.SqlConnection cn = new
> System.Data.SqlClient.SqlConnection(cnstring);
> System.Data.SqlClient.SqlCommand cmd = new
> System.Data.SqlClient.SqlCommand(cmdstring, cn);
> cn.Open(); //Suceeds in WinForm, but fails in ASP.Net
> int count = (int)cmd.ExecuteScalar();
> cn.Close();
> Response.Write(count.ToString());
> <<==
> How to fix this problem?
> Thanks in advance
> Kevin
>
|||It's not network problem, I can connect through a WinForm application. I
found something in the following article, I try but still doesn't work.
http://support.microsoft.com/default...b;en-us;888228
By the way, the two machines are in different domain.
Regars
Kevin
"Nik Marshall-Blank" <Nik@.Operatelecom.com> д?:4iSTe.305886$WN5.7191@.fe02.news.easyn ews.com...
> Run a profiler trace on the SQL server to see if the login attempt is
> making it the the SQL server. This will give you insight in to where to
> look next for the problem, network or program.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
> news:%23g6oPUDtFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||I would expect that to apply to both connections from Winform and ASP.NET.
Can you perform a profile trace on to see what credentials are being used
for the failing connection.
Nik Marshall-Blank MCSD/MCDBA
"Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
news:%237j8zSFtFHA.2792@.tk2msftngp13.phx.gbl...
> It's not network problem, I can connect through a WinForm application. I
> found something in the following article, I try but still doesn't work.
> http://support.microsoft.com/default...b;en-us;888228
> By the way, the two machines are in different domain.
> Regars
> Kevin
>
>
> "Nik Marshall-Blank" <Nik@.Operatelecom.com>
> д?:4iSTe.305886$WN5.7191@.fe02.news.easyn ews.com...
>
|||Hi, Nik,
I use profile trace, but there is no login or login fail events when using
ASP.Net. There is login even when using WinForm.
The source code and connection string are the same in WinForm and ASP.Net.
"Nik Marshall-Blank" <Nik@.Operatelecom.com> д?:XrTTe.308487$kM5.134787@.fe01.news.eas ynews.com...
>I would expect that to apply to both connections from Winform and ASP.NET.
>Can you perform a profile trace on to see what credentials are being used
>for the failing connection.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
> news:%237j8zSFtFHA.2792@.tk2msftngp13.phx.gbl...
>
|||Ok now we're getting somewhere, not sure where yet but we now going to look
at the ASP.Net server more closely.
But first just try adding the account under which ASP.Net runs as a login on
the SQL Server and see if it connects.
Nik Marshall-Blank MCSD/MCDBA
"Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
news:eYSsLGGtFHA.3908@.tk2msftngp13.phx.gbl...
> Hi, Nik,
> I use profile trace, but there is no login or login fail events when using
> ASP.Net. There is login even when using WinForm.
> The source code and connection string are the same in WinForm and ASP.Net.
>
>
> "Nik Marshall-Blank" <Nik@.Operatelecom.com>
> д?:XrTTe.308487$kM5.134787@.fe01.news.eas ynews.com...
>
|||I now found the root cause. It's because the Machine A and Machine B are in
different domain, two domains are not trusted. If both machines in the same
domain, it works.
The reason why WinForm app succeeds is because Machine B have stored a use
account and password on Domain A, I could see the NT user name from sql
profiler, it's Domain A's user account.
"Nik Marshall-Blank" <Nik@.Operatelecom.com> д?:DEUTe.127980$Lg2.47868@.fe12.news.easy news.com...
> Ok now we're getting somewhere, not sure where yet but we now going to
> look at the ASP.Net server more closely.
> But first just try adding the account under which ASP.Net runs as a login
> on the SQL Server and see if it connects.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Kevin Dai" <Kevin_Dzy@.Hotmail.com> wrote in message
> news:eYSsLGGtFHA.3908@.tk2msftngp13.phx.gbl...
>

Can I use CASE expression in AND condition?

Hello:
Is it possible to useCASE expression inAND condition? i.e.
-------------
CREATE PROC spBlah
(
@.id INT,
@.val INT
)
AS
SELECT *
FROM aTable
WHERE tableID = @.id
AND
(
CASE @.val WHEN 1 THEN otherCol = someValue END
CASE @.val WHEN 2 THEN otherCol != someOtherVlaue END
)
--------------AND is a Boolean Operator and is also a JOIN condition operator used by databases like SQL Server for extra JOIN search conditions. I don't think it can be used with a CASE statement. Try the link below for CASE statement code. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm|||For your case, you might try to build the query dynamically and store the query into a SQL variable. Then use the exec method to execute the query. I don't think you can do the way you are heading. Build only the variable part and then append to the fixed part. Hope I am not confusing you...
declare @.tmp varchar(300)
if(@.val = 1)
set @.tmp = 'otherCol = someValue'
else
set @.tmp = 'otherCol != someOtherVlaue'
set @.tmp = 'SELECT * FROM aTable WHERE tableID = @.id AND ' + @.tmp
exec(@.tmp)
Thanks

Can I update several columns with CASE expressions using SQL at the same time?

Hi, all here,

I am having a question about update several columns with CASE expression in SQL Server 2005 database engine. Is it possible to do that in SQL Server 2005 database engine?

Thanks a lot in advance for any help and guidance.

Can you be more specific about what you're trying to achieve? Using CASE in an update statement is perfectly legal. For instance you can set the value of a column based on a case statement:

declare @.var int

set @.var = 10

update T1 set Col1 = case @.var when 1 then 'Something' when 2 then 'Something2' else 'SomethingElse' end

where Col1 = 1

Can I update several columns by CASE expressions?

Hi, all here,

I am having a question-is it possible to update several columns using CASE expression in SQL language? like if I wanna set each CASE for each column.

Thanks a lot in advance for any help and guidance.

I think so. Have you tried something and it didn't work? A CASE expression just returns a scalar value, so you can do:

update tablename
set column1 = case when .... end,
column2 = case when ... end,

If you want. If the case statements are completely independent of one another this might be the best way to do something, but if they are from the same data set then you might want to use a join, and possibly a derived table.

Post an example and someone will give you an idea

|||Yes, you can.
Few times, i had to create Stored Procedures that work on just one table and all fields did not allow "NULL" values.
E.g.)

CREATE TABLE NewsTitle (
newstitle_id INT IDENTITY PRIMARY KEY
, title NVARCHAR(512)
, page SMALLINT NOT NULL
, media SMALLINT NOT NULL
)

If you would like to create a simple Sproc that will update a record in that table you might do something like(using CASE) the following:

CREATE PROCEDURE UpdateNewsTitle
@.id INT
, @.title NVARCHAR(512)
, @.page SMALLINT
, @.media SMALLINT
AS
BEGIN
UPDATE NewsTitle
SET title = CASE WHEN @.title IS NULL THEN title ELSE @.title END
, page = CASE WHEN @.page IS NULL THEN page ELSE @.page END
, media = CASE WHEN @.media IS NULL THEN media ELSE @.media END
WHERE newstitle_id = @.id
END
GO

But if you are just checking if passed argument is NULL or not and, if argument is not null then try to use that value, you can use COALESCE function to rid of CASE statement like the following:

ALTER PROCEDURE UpdateNewsTitle
@.id INT
, @.title NVARCHAR(512)
, @.page SMALLINT
, @.media SMALLINT
AS
BEGIN
UPDATE NewsTitle
SET title = COALESCE(@.title, title)
, page = COALESCE(@.page, page)
, media = COALESCE(@.media, media)
WHERE newstitle_id = @.id
END
GO

If you are not familiar with COALESCE, you can look it up on BOL(Books Online). In short, COALESCE returns FIRST non-null value.

Can I Merge a table with itself ?

Bishman,
select
a.c1,
case b.c1
when 1 then a.c2
when 2 then a.c3
when 3 then a.c4
end as new_c2
from
dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
select 3) as b
go
-- or
select c1, c2 as new_c2
from dbo.t1
union all
select c1, c3
from dbo.t1
union all
select c1, c4
from dbo.t1
go
AMB
"Bishman" wrote:

> Hi,
> I have a table which contains several integer columns that represent
> similiar date related information. ie
> RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
> I need a view of the table where these different columns have been merged
> into a single column, so where there was one row with x number of integers,
> I would have X number of rows with a single integer. Is this possible ?
> What I am actually wanting to do is sort the rows based on the value that is
> highest / lowest from the values in the row.
> Can this be done ?
> Thanks,
> Jon.
>
>
Bishman,
If you are working with 2005, then you can also use new operator "unpivot".
create table dbo.t1 (
a int not null identity unique,
b int,
c int,
d int
)
go
insert into dbo.t1(b, c, d) values(10, 20, 30)
insert into dbo.t1(b, c, d) values(40, 50, 60)
go
select
a, val
from
(
select a, b as c1, c as c2, d as c3
from dbo.t1
) as pvt
unpivot
(val for c in (c1, c2, c3)) as unpvt
go
drop table dbo.t1
go
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Bishman,
> select
> a.c1,
> case b.c1
> when 1 then a.c2
> when 2 then a.c3
> when 3 then a.c4
> end as new_c2
> from
> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
> select 3) as b
> go
> -- or
> select c1, c2 as new_c2
> from dbo.t1
> union all
> select c1, c3
> from dbo.t1
> union all
> select c1, c4
> from dbo.t1
> go
>
> AMB
> "Bishman" wrote: