Fundamental help required with SQL connection

I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
server.

When I create and instance of the server, it has a the format
machinename/instancename

This is fine for the developement machine but how does my code connect to
the server when it is on another machine ? When an instance of MSDE is run
on the target, it will produce...
anotherPCname/instancename

Thanks for any helpThe 'Data Source' keyword in the connection string specifies the SQL Server
instance you want to connect to. A best practice is to store the connection
string externally (e.g. config file) rather than hard-code it in your app so
that you can connect to different servers without changing code. Connection
string examples:

Local default instance:
Data Source=ThisServer;Initial Catalog=MyDatabase;Integrated Security=SSPI

Local named instance:
Data Source=ThisServer\ThisInstance;Initial Catalog=MyDatabase;Integrated
Security=SSPI

Remote default instance:
Data Source=OtherServer;Initial Catalog=MyDatabase;Integrated Security=SSPI

Remote named instance:
Data Source=OtherServer\OtherInstance;Initial Catalog=MyDatabase;Integrated
Security=SSPI

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <david@.orbitcoms.com> wrote in message
news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> server.
> When I create and instance of the server, it has a the format
> machinename/instancename
> This is fine for the developement machine but how does my code connect to
> the server when it is on another machine ? When an instance of MSDE is run
> on the target, it will produce...
> anotherPCname/instancename
> Thanks for any help
>|||Dan,

Thanks for the reply.

So, I just need to create a text file with the connection string with say

the local machine ID and database name. The application loads this at boot

and

connects to the named server.

When I deploy the application, I have the client alter the text file by

finding

the machine name and modifying the text to suit ??

I suppose I could use a Try Catch and have try with the local settings and

if it fails

it loads the file in the catch sub (or maybe the string is stored in

registry).

One thing I am also unable to find out is. How does MSDE know where the file

is located on the third party machine ? Should the Initial Catalog =

Path+dasename ?

Thanks

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:FayEc.70$oD3.17@.newsread1.news.pas.earthlink. net...
> The 'Data Source' keyword in the connection string specifies the SQL
Server
> instance you want to connect to. A best practice is to store the
connection
> string externally (e.g. config file) rather than hard-code it in your app
so
> that you can connect to different servers without changing code.
Connection
> string examples:
> Local default instance:
> Data Source=ThisServer;Initial Catalog=MyDatabase;Integrated Security=SSPI
> Local named instance:
> Data Source=ThisServer\ThisInstance;Initial Catalog=MyDatabase;Integrated
> Security=SSPI
> Remote default instance:
> Data Source=OtherServer;Initial Catalog=MyDatabase;Integrated
Security=SSPI
> Remote named instance:
> Data Source=OtherServer\OtherInstance;Initial
Catalog=MyDatabase;Integrated
> Security=SSPI
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David" <david@.orbitcoms.com> wrote in message
> news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> > server.
> > When I create and instance of the server, it has a the format
> > machinename/instancename
> > This is fine for the developement machine but how does my code connect
to
> > the server when it is on another machine ? When an instance of MSDE is
run
> > on the target, it will produce...
> > anotherPCname/instancename
> > Thanks for any help|||Hi

To connect to a different instance/server that is running your database then
the connection string will need to be changed. Quite often this sort of
thing is held in the registry and you configure it on installation.
Alternatively you may want to provide a small application to configure the
settings.

http://www.microsoft.com/sql/msde/t...integration.asp

John

"David" <david@.orbitcoms.com> wrote in message
news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> server.
> When I create and instance of the server, it has a the format
> machinename/instancename
> This is fine for the developement machine but how does my code connect to
> the server when it is on another machine ? When an instance of MSDE is run
> on the target, it will produce...
> anotherPCname/instancename
> Thanks for any help
>|||Thanks again for the replies.

I have tried a hard-coded option of connection and it worked.

ie.

TRY
sqlconnectionstring = ...... originalmachinename......password=..."
CATCH
sqlconnectionstring = ...... newmachinename......password=..."
END TRY

I tried to hook this into a config file but I had trouble(I used the wizard
in VB.NET to make the connection string and edited it but the area the code
was placed did not seem to like me playing with the code adding file open
command etc.

I will try by completely coding the connection in the main form load
routine.
Like this

TRY
Load my original instance for development and test
CATCH
read registry and see if validconnect string exists
if so then try to connect else
prompt user for PC name
save name to registry
try to connect
FINALLY
give up
END TRY

would this approach seem reasonable ?

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:_FyEc.6675$%N6.73110644@.news-text.cableinet.net...
> Hi
> To connect to a different instance/server that is running your database
then
> the connection string will need to be changed. Quite often this sort of
> thing is held in the registry and you configure it on installation.
> Alternatively you may want to provide a small application to configure the
> settings.
> http://www.microsoft.com/sql/msde/t...integration.asp
> John
> "David" <david@.orbitcoms.com> wrote in message
> news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> > I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> > server.
> > When I create and instance of the server, it has a the format
> > machinename/instancename
> > This is fine for the developement machine but how does my code connect
to
> > the server when it is on another machine ? When an instance of MSDE is
run
> > on the target, it will produce...
> > anotherPCname/instancename
> > Thanks for any help|||Hi

This may help:
http://support.microsoft.com/defaul...kb;en-us;309485
http://support.microsoft.com/defaul...kb;EN-US;283245

John

"David" <david@.orbitcoms.com> wrote in message
news:qazEc.71562$sj4.10264@.news-server.bigpond.net.au...
> Thanks again for the replies.
> I have tried a hard-coded option of connection and it worked.
> ie.
> TRY
> sqlconnectionstring = ......
originalmachinename......password=..."
> CATCH
> sqlconnectionstring = ...... newmachinename......password=..."
> END TRY
> I tried to hook this into a config file but I had trouble(I used the
wizard
> in VB.NET to make the connection string and edited it but the area the
code
> was placed did not seem to like me playing with the code adding file open
> command etc.
> I will try by completely coding the connection in the main form load
> routine.
> Like this
> TRY
> Load my original instance for development and test
> CATCH
> read registry and see if validconnect string exists
> if so then try to connect else
> prompt user for PC name
> save name to registry
> try to connect
> FINALLY
> give up
> END TRY
> would this approach seem reasonable ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:_FyEc.6675$%N6.73110644@.news-text.cableinet.net...
> > Hi
> > To connect to a different instance/server that is running your database
> then
> > the connection string will need to be changed. Quite often this sort of
> > thing is held in the registry and you configure it on installation.
> > Alternatively you may want to provide a small application to configure
the
> > settings.
> > http://www.microsoft.com/sql/msde/t...integration.asp
> > John
> > "David" <david@.orbitcoms.com> wrote in message
> > news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> > > I am writing SQL data apps using VB.NET 2003, MSDE is being used as
the
> > > server.
> > > > When I create and instance of the server, it has a the format
> > > machinename/instancename
> > > > This is fine for the developement machine but how does my code connect
> to
> > > the server when it is on another machine ? When an instance of MSDE is
> run
> > > on the target, it will produce...
> > > anotherPCname/instancename
> > > > Thanks for any help
> > > >|||I finally have the problem figured out. I needed to ATTACH the database to
sql server (MSDE2000) using the osql utility.

Now, would someone be able to explain how I can automate the attachment at
the customer end.
Preferably, I would like my VN.NET application install to copy the
unattached database files and attach them to the MSDE server.

Thanks

"David" <david@.orbitcoms.com> wrote in message
news:YNvEc.71214$sj4.25491@.news-server.bigpond.net.au...
> I am writing SQL data apps using VB.NET 2003, MSDE is being used as the
> server.
> When I create and instance of the server, it has a the format
> machinename/instancename
> This is fine for the developement machine but how does my code connect to
> the server when it is on another machine ? When an instance of MSDE is run
> on the target, it will produce...
> anotherPCname/instancename
> Thanks for any help
>|||Hi
I am totally new to the VB.net, only learn this about two weeks. I
used codes from MS to create a file upload class then add function to
store the uploaded filename to a table in SQL server. However I kept
getting the error message about "cannot connect to database" No matter
what format I used. I have tried at least two dozen connection stringe
formats either in Sqlconnection or OleDBConnection. Here is the basic
formats that I used:
strCon = "Data Source =localhost;Password=;User ID=sa;Initial
Catalog=Northwind" for SqlConnection
strCon = "Provider=SQLOLEDB.1;User ID=sa;Password=;Persist Security
Info=True;Initial Catalog=Northwind;Data Source=FAL-L6388" for
OleDbConnection.
I use SQL 7, which was installed by default, i,e only have user ID =
sa, no password, use the default Northwind database. That's all. My
Server is local named FAL-L688.
I have replaced the Data Source with Server= but still give me same
error. I even used an udl to create the OleDB coonnection and put
into my string, still NO. I have exhauseted all alternatives. Does
anyone have clue? Is anyway to debug the trasaction? so i can find out
what went wrong?|||Here is my codes:
Imports System.Data.OleDb
Imports System.Configuration
...
Dim oCon As New OleDbConnection
Dim oCmd As New OleDbCommand
oCon = New OleDbConnection(ConfigurationSettings.AppSettings( "strCon"))
oCon.Open()

oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "set_order_attachments"
oCmd.Parameters.Add("@.cart_id", OleDbType.VarChar, 15, cart_id)
oCmd.Parameters.Add("@.attachment", OleDbType.VarChar, 255,
strFilenamesCollection)
oCmd.Connection = oCon
Try
oCon.Open()
oCmd.ExecuteNonQuery()
...
And I have fololowing setting in the Web.config file under the
<configuration>
<appSettings>
<add key="strCon"
value="Provider=SQLOLEDB.1;User ID=sa;Password=;Initial
Catalog=Northwind;Server=(local);" />
</appSettings>
Also tried to use "Data Source=.." and enter my local machine name
there.
None of them work , came back the same "Non DB connection"

C Chang|||There was a duplicated "oCon.Open()" in my codes, but removal it does
not help. However, I tried to use the OracleCommand with the SQL query
script directly and IT WORKS!. Does anyone know why it does not work
with the Oracle procedure. I hate to change all my procedure calls to
in-line scripts. Besides if there is a complicated procedure then the
query becomes a problem.|||(chi-soon_x_chang@.raytheon.com) writes:
> There was a duplicated "oCon.Open()" in my codes, but removal it does
> not help. However, I tried to use the OracleCommand with the SQL query
> script directly and IT WORKS!. Does anyone know why it does not work
> with the Oracle procedure. I hate to change all my procedure calls to
> in-line scripts. Besides if there is a complicated procedure then the
> query becomes a problem.

I'm getting confused, are you connecting to SQL Server or to Oracle?

To go back to your original code, the error message seems like it
would from your own code. That is, I cannot really recall any error
that says "Cannot connect to database". It would help, if you could
get hold of the actual message from the client library. To that end,
it would help if you posted more parts of the code, and also if you
found out on exactly which statement, things go wrong.

Stupid check: you have SQL Server running, haven't you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

In addition to Erlands comments, you may want to move the try block earlier
to catch the connection error. Also check out
http://www.connectionstrings.com/ for the connection string, I think you
should be using Data Source instead of Server. Try using the IP address
instead of the name if necessary.

John

<chi-soon_x_chang@.raytheon.com> wrote in message
news:1103838243.185277.162240@.z14g2000cwz.googlegr oups.com...
> There was a duplicated "oCon.Open()" in my codes, but removal it does
> not help. However, I tried to use the OracleCommand with the SQL query
> script directly and IT WORKS!. Does anyone know why it does not work
> with the Oracle procedure. I hate to change all my procedure calls to
> in-line scripts. Besides if there is a complicated procedure then the
> query becomes a problem.

Function with EXECUTE problem

Hello,

I'm running into some trouble writing a function that returns a table. I'm using OPENQUERY in the FROM clause to fetch data from a remote server (ORACLE). Because the query must be dynamic, I have to execute it using the EXECUTE command.

Basically, I first dynamically create a string containing my query and then I execute it by calling the EXECUTE function and passing my string as an argument.

Now, I'd like my function to return what's produced by the EXECUTE call. Here's how I wrote the function declaration:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE FUNCTION [dbo].[beh_GetRemoteData] (@.STARTDATE varchar(20), @.POS int, @.ID_ANALOG varchar(10))
RETURNS @.RET TABLE (HIST_TIMESTAMP DATETIME, ID_ANALOG INT, STATUT INT, QUALITY INT, VALUE NUMERIC(12,5)) AS
BEGIN
DECLARE @.REMOTEQUERY varchar(300)
DECLARE @.LOCALQUERY varchar(400)

--======== Create remote Query =======--
SET @.REMOTEQUERY = 'SELECT * FROM [...]'


--======== Create local Query =======--

SET @.LOCALQUERY = 'SELECT [...] FROM OPENQUERY(REMOTE_SERVER, ''' + @.REMOTEQUERY + ''')'

INSERT INTO @.RET
EXEC(@.LOCALQUERY)

RETURN
END

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Of course, this doesn't even passes the "Check syntax" because the EXEC statement cannot be used as as source when inserting into a table variable. This might be but it's EXACTELY what I want to do.

Any ideas on how I should write my function?

Thanks,

Skip.According to my reading of books on line for SQL Server 2000 you can't execute anything other than an extended stored procedure inside a function anyway - and those can't return results sets. Therefore, you can't use dynamic sql either.

Function Returns Data Type Error

I am writing my first function and it should be be a very simple one but
I am getting the error:
Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
Syntax error converting the varchar value 'N' to a column of data type int.
Below is the funtion and then the sleect statement that causes the error
----
CREATE FUNCTION InvTypeOther (@.InvoiceID int)
RETURNS varchar(3)
AS
BEGIN
DECLARE @.Type varchar(3)
select @.Type=
(Case InvoiceType.Name
When 'IN' Then 'N'
Else 0
End)
FROM Invoice
INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
WHERE (Invoice.ID = @.InvoiceID)
Return @.Type
END
--
Select dbo.InvTypeOther(ID) from Invoice where id = 2525Try to replace the line with this:
Else '0'
HTH, Jens Suessmeyer.|||Try to replace the line with this:
Else '0'
HTH, Jens Suessmeyer.|||your CASE expression is using type precedence to try to convert 'N' to
the 0 in the else.
not sure what it should be, but it shouldn't be an int. :)
either '0' or '' perhaps [or null]
Mike Harbinger wrote:
> I am writing my first function and it should be be a very simple one but
> I am getting the error:
> Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
> Syntax error converting the varchar value 'N' to a column of data type int
.
> Below is the funtion and then the sleect statement that causes the error
> ----
> CREATE FUNCTION InvTypeOther (@.InvoiceID int)
> RETURNS varchar(3)
> AS
> BEGIN
> DECLARE @.Type varchar(3)
> select @.Type=
> (Case InvoiceType.Name
> When 'IN' Then 'N'
> Else 0
> End)
> FROM Invoice
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> WHERE (Invoice.ID = @.InvoiceID)
> Return @.Type
> END
> --
> Select dbo.InvTypeOther(ID) from Invoice where id = 2525
>|||That was it, thanks guys!
I am used to another programming language where numbers do not have to be
quoted when used in string variables.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OyGttQyEGHA.524@.TK2MSFTNGP09.phx.gbl...
>I am writing my first function and it should be be a very simple one but
> I am getting the error:
> Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
> Syntax error converting the varchar value 'N' to a column of data type
> int.
> Below is the funtion and then the sleect statement that causes the error
> ----
> CREATE FUNCTION InvTypeOther (@.InvoiceID int)
> RETURNS varchar(3)
> AS
> BEGIN
> DECLARE @.Type varchar(3)
> select @.Type=
> (Case InvoiceType.Name
> When 'IN' Then 'N'
> Else 0
> End)
> FROM Invoice
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> WHERE (Invoice.ID = @.InvoiceID)
> Return @.Type
> END
> --
> Select dbo.InvTypeOther(ID) from Invoice where id = 2525
>

Function Parameter

I am writing a function which will take two parameters. One the field
to be returned from a table and second parameter is the ID of the
record to be returned.

Problem is it's not returning the value of the field specified in the
parameter but instead returns the parameter itself. Is there a
function that will get the parameter to be evaluted first?

ALTER FUNCTION [dbo].[getScholarYearData]
(
-- Add the parameters for the function here
@.FieldName varchar(50), @.ScholarID int
)
RETURNS varchar(255)
AS
BEGIN
-- Declare the return variable here
DECLARE @.ResultVar varchar(255)

-- Add the T-SQL statements to compute the return value here
SELECT @.ResultVar=EXECUTE(@.FieldName)
FROM dbo.qmaxScholarYearID INNER JOIN
dbo.tblScholarYears ON
dbo.qmaxScholarYearID.ScholarID = dbo.tblScholarYears.ScholarID AND
dbo.qmaxScholarYearID.MaxOfScholarYearID =
dbo.tblScholarYears.ScholarYearID

-- Return the result of the function
RETURN @.ResultVar

ENDHi,

I would do it using a procedure as follows:

CREATE PROCEDURE GetFieldValue
@.strTableNameVARCHAR(250),
@.strFieldNameVARCHAR(250),
@.nIDDECIMAL(9,0),
@.strResultVARCHAR(250) OUTPUT
AS
BEGIN
DECLARE@.strSQLNVARCHAR(4000)

SET @.strSQL = 'SELECT @.strResult = ' + @.strFieldName
+ ' FROM ' + @.strTableName
+ ' WHERE ID = ' + CAST (@.nID AS VARCHAR(9))

EXEC sp_executesql @.strSQL,
N'@.strResult VARCHAR(250) OUTPUT',
@.strResult OUTPUT
END
GO

And then execute it in a way like this:

DECLARE
@.strValueVARCHAR(250)

BEGIN

EXEC dbo.GetFieldValue 'TABLE1', 'COLUMN1', 1, @.strValue OUTPUT

PRINT @.strValue
END
GO

Obviously the print is just to ensure that you have the correct value.
This may not be the best way, but it's a way that works for me.

Hope this helps,

Paul|||SQL Server (alderran666@.gmail.com) writes:

Quote:

Originally Posted by

I am writing a function which will take two parameters. One the field
to be returned from a table and second parameter is the ID of the
record to be returned.
>
Problem is it's not returning the value of the field specified in the
parameter but instead returns the parameter itself. Is there a
function that will get the parameter to be evaluted first?
>
ALTER FUNCTION [dbo].[getScholarYearData]
(
-- Add the parameters for the function here
@.FieldName varchar(50), @.ScholarID int
)
RETURNS varchar(255)
AS
BEGIN
-- Declare the return variable here
DECLARE @.ResultVar varchar(255)
>
-- Add the T-SQL statements to compute the return value here
SELECT @.ResultVar=EXECUTE(@.FieldName)

This does not even compile.

The fact that you want to pass a parameter for the column name indicates
that you have a poor table design. Columns should represent unique
attributes, and normally it's not meaningful to sometimes return one
column and sometimes another.

Nevertheless, you can use the CASE expression:

@.ResultVar = CASE @.FieldName
WHEN 'thiscol' THEN thiscol
WHEN 'thatcol' THEN thatcol
WHEN 'thatothercol' THEN thatothercol
END

--
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 am writing a function which will take two parameters. One the field [sic] to be returned from a table and second parameter is the ID of the record [sic] to be returned. <<

You got nothing right in this code. Erland is nicer about it, but
that is real message.

1) fields and records are nothing like columns and rows. That is
basic SQL.

2) You have not looked at ISO-11179 rules for data elements. At least
get rid of that silly "tbl-" and learn why even Microsoft has dropped
camelCase. And things like a "year_id" make no sense; Years are
already unique entities and have a name; they do not need an id. You
might give them a name, like the Chinese Zodiac, but that is
reaching. But a year in the context of a student is an attribute for
attending school, graduation, etc.

Also, never put the data type in a data element name like Paul did;
This is SQL and not 1960's BASIC for a 16KB PC.

3) The whole idea of what you are trying to do is a violation of
coupling and cohesion -- remember them in your first software
engineering class? You should name this nightmare something like
"Britney Spears, Squids or Automobiles" so that people will know that
you have no idea what it does until run time.

Think for a second about how uselessly vague the name
"getScholarYearData" is. Which data?

4) If you REALLY want to learn, first get some books and courses --
you really need that SE course before any SQL or RDBMS course.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

A bad design leads to these insane and impossible to maintain kludges
you are being offered to get you out of the way. It is easier than
trying to fill in an IT education in a posting.|||Also, never put the data type in a data element name like Paul did;

Quote:

Originally Posted by

This is SQL and not 1960's BASIC for a 16KB PC.
>

Please can you explain what you mean by this? This procedure works
fine for me, but If there is a better way of doing something then I
would like to know what it is. I am not sure we actually need to have
a procedure like this in our system, but this is how I would have
written it if so. Any improvements would be appreciated!|||On Apr 23, 6:04 am, paul.wr...@.gmail.com wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Also, never put the data type in a data element name like Paul did;
This is SQL and not 1960's BASIC for a 16KB PC.

>
Please can you explain what you mean by this? This procedure works
fine for me, but If there is a better way of doing something then I
would like to know what it is. I am not sure we actually need to have
a procedure like this in our system, but this is how I would have
written it if so. Any improvements would be appreciated!

Don't waste your time with him. He's too busy insulting people to
offer any help. This stored procedure will be used to display
information in a form. Your code worked fine and I appreciate your
help.

In the end I decided to use an SQL statement and a subform, but was
just curious as to how I could manipulate the behavior of that
parameter. For the hate mongers please excuse my trying to learn
anything new.

I love the stuff about my table structure being poorly designed. They
know this without even seeing it. Or the naming conventions
changing. Oh sorry this database is about 10yrs old so I should go in
and start changing all the table names to make these big shots
happy.

Please if you don't have any useful information to share just move
along and keep your hateful statements to yourself.|||SQL Server wrote:

Quote:

Originally Posted by

I love the stuff about my table structure being poorly designed. They
know this without even seeing it.

It is not necessary to see your DDL to know much about the design.

Joe and Erland tried to do you a favor. Others like me just shook
their heads and just let it going knowing there are so many people
out there that think because they can write some syntactically
correct code they are developers.

You owe several people apologies.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)|||On Apr 28, 2:20 pm, DA Morgan <damor...@.psoug.orgwrote:

Quote:

Originally Posted by

SQL Server wrote:

Quote:

Originally Posted by

I love the stuff about my table structure being poorly designed. They
know this without even seeing it.

>
It is not necessary to see your DDL to know much about the design.
>
Joe and Erland tried to do you a favor. Others like me just shook
their heads and just let it going knowing there are so many people
out there that think because they can write some syntactically
correct code they are developers.
>
You owe several people apologies.
--
Daniel A. Morgan
University of Washington
damor...@.x.washington.edu
(replace x with u to respond)

I can take Erland's suggestion to rethink why I am doing what I am
doing (which I had already done) because it was delivered without
malice. Joe on the other hand is just plain nasty. I can do without
his kind of help.|||>Please can you explain what you mean by this? <<

The ISO-11179 standards for naming data elements are based on the idea
that you name things for what they inherently are. That name is then
used everywhere in the schema.

You do NOT name them for:

1) How they are physically stored -- that means you do not put the
data type into the name. We had to do in the original versions of
BASIC because the interpreters needed that information to allocate
storage on the fly. A lot of programmers never un-learned that.

Physical locators generated by the physical storage are never
attributes in the schema. IDENTITY is never a key. We do not make
the user navigate the tables using track and sector numbers, etc. The
SQL engine is supposed to handle surrogates and not the humans.

It also means no silly "tb-" or "tbl-" to tell us it is a table
(there is only *one* data structure in SQL, duh!). And no vw-" affix
to tell us it is a VIEW (the "vw-" thing always looked like a
"Volkswagen" to me).

2) Where they are located - that means the table name is not part of
the data element name. Do you change your name from place to place as
you move around? Of course not. This practice also screws up the
data dictionary (if you do not have a data dictionary your project is
really screwed).

3) How they are used in one place -- that means no "pk-" or "fk-"
affixes. That is also silly because the same identifier that is a
FOREIGN KEY in the referencing has to be UNIQUE or a PRIMARY KEY in a
second table by definition.

The correct format is "<entity name>_<attribute type>" in lower case
for column names. The attribute types are defined in your data
dictionary, but I have a short list in other postings.

4) A data element name does not have multiple attribute types. That
means you can have "customer_id" or "customer_type" but never
"customer_type_id" because the attribute has to be either an
identifier (unique per customer) or a type (applies to many
customers).

This is usually a newbie confusing data and metadata in his attempt at
a data model.

5) A data element name is not a single attribute type. There is no
such thing as the magical, universal "id" or "date" or "value" etc.
An attribute has to be the identifier of something in particular, the
date of a particular kind of event, the value of a known attribute as
measured on a scale, etc. It is also a sign the project has no data
dictionary because you would quickly see that these magical vague
attributes apply to automobiles, squids and Britney Spears.

"To be is to be something in particular; to be nothing in particular
or everything in general is to be nothing." - Aristotle

6) A data element name is not a dangling entity name. The data
element name "customer" by itself begs the question "what?"
--"customer_id", "customer_type", "customer_name", or what? My
favorite is assuming that "sex" means "sex-frequency" or
"sex_preference" and not "sex_code" when I get a form.

The programmer has confused a table with a file and expects context to
provide the information he was too lazy to put into the table. The
field names in a file are local to the file; the column names are
global to the schema or better yet, are global to a data model that
covers your entire enterprise or industry.

An exception to this is the use of industry standard names that are
well understood in your enterprise. For example, VIN for automobiles,
ISBN for books, etc.

7) Tables are sets and should have collective or plural names, not
singular ones. That is, "Employee" is a bad table name (exception:
you really do have only one employee); "Employee" is better;
"Personnel" is best. Collective nouns imply a set by their nature and
will not be used in attribute names, which have a singular name
because their values apply to an element in the set.

An exception to this is the use of industry standard names that are
well understood in your enterprise. But most of these will be
collective nouns.

8) Relationship tables should use a common name for the relationship
and not an invented hyphenate. For example, "Roster" and not
"StudentClass" or worse.

I you wish, I can also post a quick look at ISO-11179.|||--CELKO-- wrote:

Quote:

Originally Posted by

If you wish, I can also post a quick look at ISO-11179.

You should even though the OP probably thinks reading and
standards are not part of the requirement for the job.

Others would enjoy reading it.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||

Quote:

Originally Posted by

I you wish, I can also post a quick look at ISO-11179.

I'm trying to fight my way through the kludge of data online to get an
overview of this myself - and I happened to come across this thread.

Yes, a quick look at ISO-11179 would be great (an indept look would be
even better ;) )... I've inherated the tblConjoinedName legacy, but
have a chance with a new ap to do things right - if only I knew what
"right" was!