Cons w/ UDTs?

I came across a database at my company that contained many columns in many
tables defined as UDTs. Upon further investigation, there were no rules
associated with the UDTs and they were not explicitly bound.
Upon even further investigation, the person who created the database said
they were using a modeling tool to test the quality/validity of the data
model and the tool complained that no data domains were present and suggested
the utilization of UDTs.
After reading more about them, i was intrigued and can definitely see their
value. My question is are there any cons to using them? For example, can
over usage cause performance issues? What issues are encountered if a rule
or data type needs to be altered? Etc... Thanks in advance.parchk,
Are you using SQL Server 2000 or 2005? Things are changing.
User-Defined Types in 2000 and 2005 can simply be an alias for describing
data domains, but for both versions of SQL Server, the gotcha is: "When you
create a user-defined type, it is local to a single database." If you don't
care about cross-database use of the UDT, not even in tempdb, then no
problem. (But even then, 2005 offers some options that 2000 does not.)
(SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
(SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
Then you can use them in your code in the proper database as if they were a
native datatype.
I would guess that this is the type of UDT you have in your database. I
used to use them heavily, but decided that the value of using UDTs as
documentation of data domains was to small to repay the hassle. (And, when
you generate scripts you tend to get the base datatypes scripted out for
you.)
--
With 2005, there is now "CLR User-Defined Types". This is a much richer and
more complex type of UDT, where you define the type and the operations that
work with the datatype. If you need this, you need to read about them in
detail, starting in the SQL Server 2005 Books Online.
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>I came across a database at my company that contained many columns in many
> tables defined as UDTs. Upon further investigation, there were no rules
> associated with the UDTs and they were not explicitly bound.
> Upon even further investigation, the person who created the database said
> they were using a modeling tool to test the quality/validity of the data
> model and the tool complained that no data domains were present and
> suggested
> the utilization of UDTs.
> After reading more about them, i was intrigued and can definitely see
> their
> value. My question is are there any cons to using them? For example, can
> over usage cause performance issues? What issues are encountered if a
> rule
> or data type needs to be altered? Etc... Thanks in advance.|||If you can imagine a db system that is 'strongly' typed, where
you can define types natively instead of going 'outside' the system
and where any type can be the type of a 'variable' not just
a value then I have a surprise for you :-)
www.beyondsql.blogspot.com
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eMMF6yO6HHA.2380@.TK2MSFTNGP02.phx.gbl...
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When
> you create a user-defined type, it is local to a single database." If you
> don't care about cross-database use of the UDT, not even in tempdb, then
> no problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were
> a native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And,
> when you generate scripts you tend to get the base datatypes scripted out
> for you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer
> and more complex type of UDT, where you define the type and the operations
> that work with the datatype. If you need this, you need to read about
> them in detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>>I came across a database at my company that contained many columns in many
>> tables defined as UDTs. Upon further investigation, there were no rules
>> associated with the UDTs and they were not explicitly bound.
>> Upon even further investigation, the person who created the database said
>> they were using a modeling tool to test the quality/validity of the data
>> model and the tool complained that no data domains were present and
>> suggested
>> the utilization of UDTs.
>> After reading more about them, i was intrigued and can definitely see
>> their
>> value. My question is are there any cons to using them? For example,
>> can
>> over usage cause performance issues? What issues are encountered if a
>> rule
>> or data type needs to be altered? Etc... Thanks in advance.
>|||Hi Russell,
We are using 2005. Thanks for your response. For databases that make heavy
use of UDTs to define data domains, is there any performance impact? The
volume will not be high; however, the database is involved with replication.
"Russell Fields" wrote:
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When you
> create a user-defined type, it is local to a single database." If you don't
> care about cross-database use of the UDT, not even in tempdb, then no
> problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were a
> native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And, when
> you generate scripts you tend to get the base datatypes scripted out for
> you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer and
> more complex type of UDT, where you define the type and the operations that
> work with the datatype. If you need this, you need to read about them in
> detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
> >I came across a database at my company that contained many columns in many
> > tables defined as UDTs. Upon further investigation, there were no rules
> > associated with the UDTs and they were not explicitly bound.
> >
> > Upon even further investigation, the person who created the database said
> > they were using a modeling tool to test the quality/validity of the data
> > model and the tool complained that no data domains were present and
> > suggested
> > the utilization of UDTs.
> >
> > After reading more about them, i was intrigued and can definitely see
> > their
> > value. My question is are there any cons to using them? For example, can
> > over usage cause performance issues? What issues are encountered if a
> > rule
> > or data type needs to be altered? Etc... Thanks in advance.
>
>|||So overall would you recommend extensive utilization of UDTs for defining
data domains and/or the other capabilites of UDTs?
"Russell Fields" wrote:
> parchk,
> Are you using SQL Server 2000 or 2005? Things are changing.
> User-Defined Types in 2000 and 2005 can simply be an alias for describing
> data domains, but for both versions of SQL Server, the gotcha is: "When you
> create a user-defined type, it is local to a single database." If you don't
> care about cross-database use of the UDT, not even in tempdb, then no
> problem. (But even then, 2005 offers some options that 2000 does not.)
> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
> Then you can use them in your code in the proper database as if they were a
> native datatype.
> I would guess that this is the type of UDT you have in your database. I
> used to use them heavily, but decided that the value of using UDTs as
> documentation of data domains was to small to repay the hassle. (And, when
> you generate scripts you tend to get the base datatypes scripted out for
> you.)
> --
> With 2005, there is now "CLR User-Defined Types". This is a much richer and
> more complex type of UDT, where you define the type and the operations that
> work with the datatype. If you need this, you need to read about them in
> detail, starting in the SQL Server 2005 Books Online.
> RLF
>
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
> >I came across a database at my company that contained many columns in many
> > tables defined as UDTs. Upon further investigation, there were no rules
> > associated with the UDTs and they were not explicitly bound.
> >
> > Upon even further investigation, the person who created the database said
> > they were using a modeling tool to test the quality/validity of the data
> > model and the tool complained that no data domains were present and
> > suggested
> > the utilization of UDTs.
> >
> > After reading more about them, i was intrigued and can definitely see
> > their
> > value. My question is are there any cons to using them? For example, can
> > over usage cause performance issues? What issues are encountered if a
> > rule
> > or data type needs to be altered? Etc... Thanks in advance.
>
>|||Parchk,
The 'alias' form of UDTs should have absolutely no performance overhead. It
is really just a naming / documentation scheme.
In terms of replication (I am not a replication expert) I would say that you
want to make sure that the UDTs are also replicated or created with the same
id on the replicated database. And even that is probably not completely
necessary, since the underlying datatypes are knows on both servers.
(Perhaps someone who does replication a lot, can add a comment.)
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:4512EE17-B439-46FE-9504-86F7BD01A882@.microsoft.com...
> Hi Russell,
> We are using 2005. Thanks for your response. For databases that make
> heavy
> use of UDTs to define data domains, is there any performance impact? The
> volume will not be high; however, the database is involved with
> replication.
> "Russell Fields" wrote:
>> parchk,
>> Are you using SQL Server 2000 or 2005? Things are changing.
>> User-Defined Types in 2000 and 2005 can simply be an alias for describing
>> data domains, but for both versions of SQL Server, the gotcha is: "When
>> you
>> create a user-defined type, it is local to a single database." If you
>> don't
>> care about cross-database use of the UDT, not even in tempdb, then no
>> problem. (But even then, 2005 offers some options that 2000 does not.)
>> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
>> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
>> Then you can use them in your code in the proper database as if they were
>> a
>> native datatype.
>> I would guess that this is the type of UDT you have in your database. I
>> used to use them heavily, but decided that the value of using UDTs as
>> documentation of data domains was to small to repay the hassle. (And,
>> when
>> you generate scripts you tend to get the base datatypes scripted out for
>> you.)
>> --
>> With 2005, there is now "CLR User-Defined Types". This is a much richer
>> and
>> more complex type of UDT, where you define the type and the operations
>> that
>> work with the datatype. If you need this, you need to read about them in
>> detail, starting in the SQL Server 2005 Books Online.
>> RLF
>>
>> "parchk" <parchk@.discussions.microsoft.com> wrote in message
>> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>> >I came across a database at my company that contained many columns in
>> >many
>> > tables defined as UDTs. Upon further investigation, there were no
>> > rules
>> > associated with the UDTs and they were not explicitly bound.
>> >
>> > Upon even further investigation, the person who created the database
>> > said
>> > they were using a modeling tool to test the quality/validity of the
>> > data
>> > model and the tool complained that no data domains were present and
>> > suggested
>> > the utilization of UDTs.
>> >
>> > After reading more about them, i was intrigued and can definitely see
>> > their
>> > value. My question is are there any cons to using them? For example,
>> > can
>> > over usage cause performance issues? What issues are encountered if a
>> > rule
>> > or data type needs to be altered? Etc... Thanks in advance.
>>|||Parchk,
It is handy for defining things that you want to be the same everywhere.
For example:
CREATE TYPE SSN FROM varchar(11) NOT NULL
If you use this everywhere a Social Security Number is needed, then the
columns and variables will always be 11-characters, which leaves room for
the dashes, eg. 123-45-6789. This helps avoid having some places coded for
varchar(9), which is intended for 123456789 without dashes, but might wind
up holding 123-45-67 which, of course, is invalid.
The thing that moved me away from using them was the cross-database issues.
These are describe in the 2005 Books Online topic: "Using User-defined Types
Across Databases". It seems, without testing, like it is a little more
robust than in earlier versions, but there are still things to be dealt
with.
I switched over to using class names for my columns, which has served me
well in the years since then. e.g.
Policy_Holder_SocialSecurityNumber
Mate_SocialSecurityNumber
Business_PhoneNumber
Person_FamilyName
In these examples the final token of the column (or variable) name is the
data class. This does not, of course, enforce that everyone defines the
same datatype for the class names. Documentation and review are needed to
catch errors.
FWIW,
RLF
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:6B7E0FAD-0EA4-46C7-98AE-825692FC1543@.microsoft.com...
> So overall would you recommend extensive utilization of UDTs for defining
> data domains and/or the other capabilites of UDTs?
> "Russell Fields" wrote:
>> parchk,
>> Are you using SQL Server 2000 or 2005? Things are changing.
>> User-Defined Types in 2000 and 2005 can simply be an alias for describing
>> data domains, but for both versions of SQL Server, the gotcha is: "When
>> you
>> create a user-defined type, it is local to a single database." If you
>> don't
>> care about cross-database use of the UDT, not even in tempdb, then no
>> problem. (But even then, 2005 offers some options that 2000 does not.)
>> (SQL Server 2000 syntax): EXEC sp_addtype SSN, 'varchar(11)', 'NOT NULL'
>> (SQL Server 2005 syntax): CREATE TYPE SSN FROM varchar(11) NOT NULL ;
>> Then you can use them in your code in the proper database as if they were
>> a
>> native datatype.
>> I would guess that this is the type of UDT you have in your database. I
>> used to use them heavily, but decided that the value of using UDTs as
>> documentation of data domains was to small to repay the hassle. (And,
>> when
>> you generate scripts you tend to get the base datatypes scripted out for
>> you.)
>> --
>> With 2005, there is now "CLR User-Defined Types". This is a much richer
>> and
>> more complex type of UDT, where you define the type and the operations
>> that
>> work with the datatype. If you need this, you need to read about them in
>> detail, starting in the SQL Server 2005 Books Online.
>> RLF
>>
>> "parchk" <parchk@.discussions.microsoft.com> wrote in message
>> news:FDE0AF7A-68C5-495A-8F16-9D7FDDF6E607@.microsoft.com...
>> >I came across a database at my company that contained many columns in
>> >many
>> > tables defined as UDTs. Upon further investigation, there were no
>> > rules
>> > associated with the UDTs and they were not explicitly bound.
>> >
>> > Upon even further investigation, the person who created the database
>> > said
>> > they were using a modeling tool to test the quality/validity of the
>> > data
>> > model and the tool complained that no data domains were present and
>> > suggested
>> > the utilization of UDTs.
>> >
>> > After reading more about them, i was intrigued and can definitely see
>> > their
>> > value. My question is are there any cons to using them? For example,
>> > can
>> > over usage cause performance issues? What issues are encountered if a
>> > rule
>> > or data type needs to be altered? Etc... Thanks in advance.
>>

connectivity to the databse in SQL server 7

Can somebody tell me, how can I connect to the server(OS WIN NT server 4.0). I have created one database(microsoft SQl server 7.0) in my company server.And I tried to connect using ODBC Data source administrator to connect to it. It's failed. This error prompted.

Connection failed
SQL state'28000'
SQL server driver'18456'
[Microsoft ][ODBC SQL server driver ][SQL server] Login failed for user 'faraidba']

do I need check my login ID for the database I have created or my ODBC driver is obsolete. Please help as I'm new with it.Did you select NT authentication or Server authentication?
I suspect it was server authentication, You have either supplied an invalid username, an invalid password, or some combination of both.
If you used NT authentication, your problem is that your network id has not been setup as a valid login.

If you wish to test your login you can always use I/OSQL.EXE. If you are using NT authentication you should be able to login in using "OSQL -S <your server name> -E", and if using Server authentication try "OSQL -S <your server name> -U <your user id> -P <your password>". If either of these work then you have a problem with ODBC.

give this some thought and let me know what you find.|||Thanx.

I've followed ur suggestion. I used the correct login name and password. The network library: I chose 'name pipes' connection in ODBC to connect to the server and this message propmted;

Connection failed
SQL state:'08004'
SQL serve error:4062
Server rejected the connection;
Access to selected database has been denied

Should I use TCP/IP(network libraries) instead?|||Thanx.

I've followed ur suggestion. I used the correct login name and password. The network library: I chose 'name pipes' connection in ODBC to connect to the server and this message propmted;

Connection failed
SQL state:'08004'
SQL serve error:4062
Server rejected the connection;
Access to selected database has been denied

Should I use TCP/IP(network libraries) instead?

I try to connect using server authentication.|||Okay, I think your problem is that the usreid you are using has not been granted access to the DB you are trying to connect to.

Again if you use I/OSQL.EXE to try and connect as I outlined before you would have connected to your default database. Most of the time this is the master db. If you try usng I/OSQL again but add "-d <your db name>" and see if you don't get a similar message or connect as before and issue the T-SQL command "use <your db name> go".

The NETWORK Library you are using is just fine. The fact that you have connected to the SQL engine is evident by the error message.|||you're correct. now i'm able to do the connection.

hope to seek ur help next time.. ;)
thanks, freind

laziaf238
KL,
Malaysia

Connections through a server alias, IP address, or any other alternate name are not supported.

Hi

I am trying to connect to another sql server in my company using the IP address for exmp. : 111.111.1.1\SQL2005 but it show this message:

Connections through a server alias, IP address, or any other alternate name are not supported. it was working well on SQL2000

Please help

Have you tried to create an alias in SQL server configuration manager?

Gary

|||Yes I did and is still not accepting|||

Hi Ziad,

Can you explain what you did exactly ? did you create the following:

1-create alisan name : mysecondserver

2-port: by default empty , you need to specify the port in case you have firewall issue.

3-protocol: TCP/IP or other..

4-server: IP\instance_name

NB: to be sure about server name on the second server select @.@.servername in query window on the second server.

If you still cannot connect , please send me a direct email, you can find it on my site and i will try to discuss it with you.

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com

Connection to SQL Server 2000 From an ASP Page

Dear All,
I'm really confusing while reading the connections Issue in the support
knowledge.
Our Company website guest users can register themselves in my website. The
data goes to the SQL Server database 2000 which also used by internal users
of my company as Inhouse Database. So I want to restrict the Website Guest
users. Whats the safest way to connect to?.
Actually MY IIS SQL Server running on different machine(as stated in
support base)
1. I have created windows user a/c (say WEBUSER) on both machines with the
same password.
2. I have created a login a/c SQL Server for WEBUSER too.
But unfortunately I am getting "Internal Server Error" - Page Can not be
Displayed.
My connection String is as follows:
dc0.Open "Provider=sqloledb;" & _
"Network Library=DBNETLIB;" & _
"Integrated Security=SSPI." & _
"Data Source=ServerName;" & _
"Initial Catalog=DatabaseName;" & _
"User ID=UserName;" & _
"Password=password"
Please advice me is this safe?. Why I'm getting the error?
Thanks
Kavi
As a first step, try the following:
Log onto SQL Query Analyser and log on with the DataSource/username/password
that is used withing your connection string.
If this is OK , simulate a SQL statement that is similar that will run by a
web user.
At this point, you'll know that the account is OK.
can you connect , for example, via telnet to the sql server?
Are other pages running on the web server.
Also, see if you can trap the error and print it on the page.
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
news:FA375A40-B27B-44DE-9584-7CC8B781AFE1@.microsoft.com...
> Dear All,
> I'm really confusing while reading the connections Issue in the support
> knowledge.
> Our Company website guest users can register themselves in my website. The
> data goes to the SQL Server database 2000 which also used by internal
users
> of my company as Inhouse Database. So I want to restrict the Website Guest
> users. Whats the safest way to connect to?.
> Actually MY IIS SQL Server running on different machine(as stated in
> support base)
> 1. I have created windows user a/c (say WEBUSER) on both machines with
the
> same password.
> 2. I have created a login a/c SQL Server for WEBUSER too.
> But unfortunately I am getting "Internal Server Error" - Page Can not be
> Displayed.
> My connection String is as follows:
> dc0.Open "Provider=sqloledb;" & _
> "Network Library=DBNETLIB;" & _
> "Integrated Security=SSPI." & _
> "Data Source=ServerName;" & _
> "Initial Catalog=DatabaseName;" & _
> "User ID=UserName;" & _
> "Password=password"
> Please advice me is this safe?. Why I'm getting the error?
> Thanks
> Kavi
>
|||Thanks Jack. I'll try.
"Jack Vamvas" wrote:

> As a first step, try the following:
> Log onto SQL Query Analyser and log on with the DataSource/username/password
> that is used withing your connection string.
> If this is OK , simulate a SQL statement that is similar that will run by a
> web user.
> At this point, you'll know that the account is OK.
> can you connect , for example, via telnet to the sql server?
> Are other pages running on the web server.
> Also, see if you can trap the error and print it on the page.
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> news:FA375A40-B27B-44DE-9584-7CC8B781AFE1@.microsoft.com...
> users
> the
>
>
|||In the Advanced tab of Internet Options of IE, uncheck "Show friendly
HTTP error messages". Then you usually can see the whole error
message.
Shane

Connection to SQL Server 2000 From an ASP Page

Dear All,
I'm really confusing while reading the connections Issue in the support
knowledge.
Our Company website guest users can register themselves in my website. The
data goes to the SQL Server database 2000 which also used by internal users
of my company as Inhouse Database. So I want to restrict the Website Guest
users. Whats the safest way to connect to?.
Actually MY IIS SQL Server running on different machine(as stated in
support base)
1. I have created windows user a/c (say WEBUSER) on both machines with the
same password.
2. I have created a login a/c SQL Server for WEBUSER too.
But unfortunately I am getting "Internal Server Error" - Page Can not be
Displayed.
My connection String is as follows:
dc0.Open "Provider=sqloledb;" & _
"Network Library=DBNETLIB;" & _
"Integrated Security=SSPI." & _
"Data Source=ServerName;" & _
"Initial Catalog=DatabaseName;" & _
"User ID=UserName;" & _
"Password=password"
Please advice me is this safe?. Why I'm getting the error?
Thanks
KaviAs a first step, try the following:
Log onto SQL Query Analyser and log on with the DataSource/username/password
that is used withing your connection string.
If this is OK , simulate a SQL statement that is similar that will run by a
web user.
At this point, you'll know that the account is OK.
can you connect , for example, via telnet to the sql server?
Are other pages running on the web server.
Also, see if you can trap the error and print it on the page.
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
news:FA375A40-B27B-44DE-9584-7CC8B781AFE1@.microsoft.com...
> Dear All,
> I'm really confusing while reading the connections Issue in the support
> knowledge.
> Our Company website guest users can register themselves in my website. The
> data goes to the SQL Server database 2000 which also used by internal
users
> of my company as Inhouse Database. So I want to restrict the Website Guest
> users. Whats the safest way to connect to?.
> Actually MY IIS SQL Server running on different machine(as stated in
> support base)
> 1. I have created windows user a/c (say WEBUSER) on both machines with
the
> same password.
> 2. I have created a login a/c SQL Server for WEBUSER too.
> But unfortunately I am getting "Internal Server Error" - Page Can not be
> Displayed.
> My connection String is as follows:
> dc0.Open "Provider=sqloledb;" & _
> "Network Library=DBNETLIB;" & _
> "Integrated Security=SSPI." & _
> "Data Source=ServerName;" & _
> "Initial Catalog=DatabaseName;" & _
> "User ID=UserName;" & _
> "Password=password"
> Please advice me is this safe?. Why I'm getting the error?
> Thanks
> Kavi
>|||Thanks Jack. I'll try.
"Jack Vamvas" wrote:

> As a first step, try the following:
> Log onto SQL Query Analyser and log on with the DataSource/username/passwo
rd
> that is used withing your connection string.
> If this is OK , simulate a SQL statement that is similar that will run by
a
> web user.
> At this point, you'll know that the account is OK.
> can you connect , for example, via telnet to the sql server?
> Are other pages running on the web server.
> Also, see if you can trap the error and print it on the page.
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> news:FA375A40-B27B-44DE-9584-7CC8B781AFE1@.microsoft.com...
> users
> the
>
>|||In the Advanced tab of Internet Options of IE, uncheck "Show friendly
HTTP error messages". Then you usually can see the whole error
message.
Shane

Connection String

I am a web database Developer not server Admin
my company use SQL server2000 I use vb.net2003
Last week My web on intranet worked but
Today we have a new server and move the data to another server
my old connection string is "user id=sa;password=;datasource=server1;initail catalog=table1;"
Now I have changed only datasource to "user id=sa;password=;datasource=server2;initail catalog=table1;"
I am not sure about the user id(that the admin told me) because when I test my web it show error message like this
******************
Login failed for user 'sa'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'sa'.
*******************
(and I have tried user ID= blank too)

How can I know the user id of the server (SQL server 2000)?
Thanks for helping me
Have a nice dayTwo things.

First, you give wrong information. Your connectionstrings are not valid. It is NOT spelled "initiail catalog".

Second, what about the SA user? It looks simply like you give the wrong password. That simple. Try entering the correct password. Try resetting the password.|||YEs,I mean "initial catalog"--> not in case 1
the old one worked.I think the problem is only --> I don't know The user ID and password.
For case 2--> I can create and update (edit , delete) the table in SQL database.but I don't know well how to manage the SQL server I am just the user who can design DB and create tables in DB.So now I don't know both of user ID and password.
I think 1.ask the db administrator-->but he's on vacation.
2.Can I know by searching in SQL Enterprise Manager or something like that?
thanks for helping me.
Have a nice day.|||A web database developer huh?

Case 1: owned
Case 2: owned
Case 3: owned

You're using the 'sa' account? That's foul! Create a user specifically for the site, and give it permissions based on the databases and tables you need. Also.. initial catalog isn't a table. It's a database name.|||YEs initial catalog is my db name.The problem is-> I am not admin,I just want USER ID and Password For accessing DB (via my web page).
For the old server I used SA and blank password,I know these from admin but for the new one I don't know both of them I have tried "SA" and PAssword SA or blank oe something like that it show the error message -->Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
or-->Login failed for user 'SA'
so I want to know how can I get the sql user id and password?
(I cannot right click at the server for see the property of the server)

Or I cannot do anything.
Thanks a lot for helping me.
Have a nice day.

Connection problem, please

I have a server, in datacenter, that have installed a SQL server 2000
standard version
then another PC, in company, need to connect to that SQL server through the
Enterprise Manager.
Althrough I have created both window account or SQL account, I still cannot
connect to that SQL server, it prompt me connOpen(), then access deny or the
server not exist...how to solve it?
thx a lot~Hello.
I don't have an answer to your question but I would advise
that you look at the following.
1) Does the person have access rights to the server (not
SQL) ?
2) Are you using NT Authorisation or mixed mode.
3) Can you user "see" the server though enterprise
manager ? (if not then its a network problem)
4) Does your user have access rights to the database they
are trying to connect to ?
I'm sorry I can't be of more help but there is a lot that
can go wrong.
Can you give actual error messages ?
Peter
>--Original Message--
>I have a server, in datacenter, that have installed a SQL
server 2000
>standard version
>then another PC, in company, need to connect to that SQL
server through the
>Enterprise Manager.
>Althrough I have created both window account or SQL
account, I still cannot
>connect to that SQL server, it prompt me connOpen(), then
access deny or the
>server not exist...how to solve it?
>thx a lot~
>
>.
>

connection problem to sql 2000 on windows 2003 from Windows 95

Hi all,
I have a Windows 2003 server, which is also a terminal server for
application, with sql 2000 installed. My company has developed an
application that uses SQL 2000 as its database. The application is a
client/server one. In each client computer there's a link to the
application on the server. There is no problem with Windows 98,
Windows 2000 pro, Windows xp pro clients, but the windows 95 ones
cannot log in to the database. The log of the application shows the
following error:
connection error -2147467259. Cannot open database requested in login
'database name'. Login fails.
Till a week ago the application was running on a Windows 2000 server
with SQL 2000 install and the W95 clients had no problem connecting to
the database, so my guess is the error has something to do with
Windows 2003 server, but what'causing the error?
I tried to install a newer version of MDAC (MDAC 2.5, the last version
of MDAC you can install on W95)but with no success. By the way W95
clients have no problem accessing shared folder on the Windows 2003
server.
Any idea?
Thanks
Marino[posted and mailed, please reply in news]

Marino (mmagi@.itconsult.it) writes:
> I have a Windows 2003 server, which is also a terminal server for
> application, with sql 2000 installed. My company has developed an
> application that uses SQL 2000 as its database. The application is a
> client/server one. In each client computer there's a link to the
> application on the server. There is no problem with Windows 98,
> Windows 2000 pro, Windows xp pro clients, but the windows 95 ones
> cannot log in to the database. The log of the application shows the
> following error:
> connection error -2147467259. Cannot open database requested in login
> 'database name'. Login fails.
> Till a week ago the application was running on a Windows 2000 server
> with SQL 2000 install and the W95 clients had no problem connecting to
> the database, so my guess is the error has something to do with
> Windows 2003 server, but what'causing the error?

That particular error message means that the logins have a default
database that does not exist on the server, or which they have not
been granted access to.

Are the W95 clients using SQL authentication or Windows authentication?
I would assume that SQL authentication is the only thing that works.

Review how the Win95 clients access the server, and then do an
sp_helplogins on the logins they use. Then check DefDbName, whether
this database exists, or if they have been granted access. Use
sp_defaultdb to change their default db if necessary. (sp_defaultdb
may be called something else; use Books Online to find out.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp