DB2 Stored Procedures using ODBC

I am trying to call DB2 z/OS Stored Procedures from the report designer using
a DataDirect ODBC driver. I get a result set with a simple SP which has no
input parameters. If I call a SP with one input parameter defined as
integer, my result set is returned if I code the value in the call statement.
But, if I give it a parameter marker and supply the value after being
prompted, I get an error stating "VALUE OF INPUT HOST VARIABLE NUM 001 NOT
USED; WRONG DATA TYPE".
My guess is that the parameter marker may get passed instead of the value,
or the integer is not being converted correctly. If I change the datatype of
the input parm in my SP to CHAR(1), everything works fine. Note: I am not
using the input parms in my SP. They are being ignored for testing reasons.
If I used the IBM OLE DB driver, my above scenario works fine. This leads
me to believe I have an ODBC setting issue (or some other programmer error).
And for the record, I am using the DataDirect ODBC driver instead of the IBM
OLE DB because of licensing concerns.
Has anyone had similar issues while calling DB2 Stored Procedures via an
ODBC driver?Hello,
Based on the symptom, it is more like that the issue exist in the odbc
driver. If you are use SQL 2005, You could try latest Microsoft OLEDB
provider for DB2
http://www.microsoft.com/downloads/details.aspx?FamilyID=D09C1D60-A13C-4479-
9B91-9E8B9D835CDC&displaylang=en
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks. We are using SQL 2000 and accessing DB2 on z/OS. I am also in
contact with the driver's support team, but I was wondering if anyone has
tried something similar. Reporting Services works great with this driver for
mapping to tables, but using Stored Procedures has proved difficult.
""privatenews"" wrote:
> Hello,
> Based on the symptom, it is more like that the issue exist in the odbc
> driver. If you are use SQL 2005, You could try latest Microsoft OLEDB
> provider for DB2
> http://www.microsoft.com/downloads/details.aspx?FamilyID=D09C1D60-A13C-4479-
> 9B91-9E8B9D835CDC&displaylang=en
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hello,
I think the driver support team shall be the best resource on this issue.
You may also want to try some other drivers if possible
http://www.codeproject.com/dotnet/DotnetDb2.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

DB2 Connection Custom Assembly

Have a small problem. I have written a custom assembly to query a db2
database through a dsn datasource. It works fine in Report Designer but
shows the #error message in the fields on the report once it is published.
Following is my rssrvpolicy.config entry:
<CodeGroup
class="UnionCodeGroup"
version="1.0.0.0"
PermissionSetName="FullTrust"
Name="DB2Link"
Description="This assembly connects to the DB2 database server and
retrieves information">
<IMembershipCondition
class="UrlMembershipCondition"
version="1.0.0.0"
Url="C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin\DB2Link.dll"
/>
</CodeGroup>
I have also added the following code to my Class Library prior to compiling
and deploying:
Dim Permission As New
Odbc.OdbcPermission(Security.Permissions.PermissionState.Unrestricted)
Permission.Assert()
but to no avail. Any help would be greatly appreciated. BTW, the ODBC
connection information is all housed within the library. It takes a string
value passed from SRS and returns a single string value. Nothing fancy...
Do I need to create an access group to the DSN File?
Thanks,Asserting the ODBC Permission might not be sufficient. You may want to try
asserting full trust in the custom assembly:
[PermissionSet(SecurityAction.Assert, Unrestricted=true)]
public foo()
{
// Your code:
// open database connection
// ...
}
See also this thread related to connecting to Oracle from a custom assembly:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=3cd1a1de-15d4-41fe-bc9b-3c9df93ac2ee&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JamesH" <JamesH@.discussions.microsoft.com> wrote in message
news:84982C3D-32DD-4D3B-B46C-2766810046F0@.microsoft.com...
> Have a small problem. I have written a custom assembly to query a db2
> database through a dsn datasource. It works fine in Report Designer but
> shows the #error message in the fields on the report once it is published.
> Following is my rssrvpolicy.config entry:
> <CodeGroup
> class="UnionCodeGroup"
> version="1.0.0.0"
> PermissionSetName="FullTrust"
> Name="DB2Link"
> Description="This assembly connects to the DB2 database server and
> retrieves information">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1.0.0.0"
> Url="C:\Program Files\Microsoft SQL
> Server\MSSQL\Reporting Services\ReportServer\bin\DB2Link.dll"
> />
> </CodeGroup>
> I have also added the following code to my Class Library prior to
compiling
> and deploying:
> Dim Permission As New
> Odbc.OdbcPermission(Security.Permissions.PermissionState.Unrestricted)
> Permission.Assert()
> but to no avail. Any help would be greatly appreciated. BTW, the ODBC
> connection information is all housed within the library. It takes a
string
> value passed from SRS and returns a single string value. Nothing fancy...
> Do I need to create an access group to the DSN File?
> Thanks,|||Thanks, I will look at it, I left the code on another computer and won't be
able to try it until tomorrow, will let you know.
"JamesH" wrote:
> Have a small problem. I have written a custom assembly to query a db2
> database through a dsn datasource. It works fine in Report Designer but
> shows the #error message in the fields on the report once it is published.
> Following is my rssrvpolicy.config entry:
> <CodeGroup
> class="UnionCodeGroup"
> version="1.0.0.0"
> PermissionSetName="FullTrust"
> Name="DB2Link"
> Description="This assembly connects to the DB2 database server and
> retrieves information">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1.0.0.0"
> Url="C:\Program Files\Microsoft SQL
> Server\MSSQL\Reporting Services\ReportServer\bin\DB2Link.dll"
> />
> </CodeGroup>
> I have also added the following code to my Class Library prior to compiling
> and deploying:
> Dim Permission As New
> Odbc.OdbcPermission(Security.Permissions.PermissionState.Unrestricted)
> Permission.Assert()
> but to no avail. Any help would be greatly appreciated. BTW, the ODBC
> connection information is all housed within the library. It takes a string
> value passed from SRS and returns a single string value. Nothing fancy...
> Do I need to create an access group to the DSN File?
> Thanks,|||Robert, I've tried about everything I can think of: I even tried a sample
that has worked for others using an assembly to read a text file but to no
avail. Sorry for all of the code below but I'm at a loss. I've also set the
version to be 1.0.0.0.0 in my assembly and added the : <Assembly:
AllowPartiallyTrustedCallers()> although I haven't setup strong naming. If
you see anything that I've messed up, please let me know...I'm stumped.
This is my Class Code:
Imports System.IO
Imports System.Security.Permissions
Public Class ReadResultsFile
<FileIOPermissionAttribute(SecurityAction.Assert,
Read:="C:\JHHTST2.txt")> _
Public Shared Function GetResults() As String
Dim reader As StreamReader = New StreamReader("c:\JHHTST2.txt")
Dim hello As String = reader.ReadToEnd()
reader.Close()
Return hello
End Function
End Class
This as the PermissionSet Code:
<PermissionSet
class="NamedPermissionSet"
version="1"
Name="ReadObjectFilePermissionSet"
Description="A special permission set that grants read access to my hello
file.">
<IPermission
class="FileIOPermission"
version="1"
Read="C:\JHHTST2.txt"
/>
<IPermission
class="SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
</PermissionSet>
This is my Codegroup:
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="ReadObjectFilePermissionSet"
Name="ReadScriptFile"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\ReadScriptFile.dll"
/>
</CodeGroup>
Thanks,
JamesH.|||Make sure the the CodeGroup section you added is right below the
Report_Expressions_Default_Permissions CodeGroup. Position in the file
does have an effect.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||I added it in there, no change.
JamesH.
"Donovan R. Smith [MS]" wrote:
> Make sure the the CodeGroup section you added is right below the
> Report_Expressions_Default_Permissions CodeGroup. Position in the file
> does have an effect.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Never Mind, got it working today by adding a linked server to SQL and then
using the SQLClient connection in the assembly, worked like a champ... Is
there anybody that has used ODBC with Custom assemblies?
JamesH.
"JamesH" wrote:
> This weekend I rebuilt everything at least 10 times and then built a virtual
> pc image and reloaded everything to no avail. I've added Strong_name to the
> .dlls and still no luck. I'm not seeing any errors in the logs at all, just
> the #error message on my report screen. I've tried deleting the reports,
> re-adding the custom assembly each time and then re-deploying along with
> re-copying the .dll. I even tried to get it to work with another .dll that
> still doesn't work. Any help now would be greatly appreciated on just
> getting a single custom assembly working. Are there any walk-throughs
> (cradle to grave) at all by MS?
> THanks,
> "JamesH" wrote:
> > I added it in there, no change.
> >
> > JamesH.
> >
> > "Donovan R. Smith [MS]" wrote:
> >
> > > Make sure the the CodeGroup section you added is right below the
> > > Report_Expressions_Default_Permissions CodeGroup. Position in the file
> > > does have an effect.
> > >
> > > --
> > > Thanks.
> > >
> > > Donovan R. Smith
> > > Software Test Lead
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >sql

DB Redesign - use junction/xref table?

I'm currently redesigning our db. Mind you I was NOT the designer of the
current db. I have the following tables:
CREATE TABLE [CUSTOMERS] (
[CID] [int] IDENTITY (1, 1) NOT NULL ,
[customerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[customerID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[address] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[businessName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companyID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [TRANSACTIONS] (
[Transaction_ID] [int] NOT NULL ,
[CID] [int] NULL ,
[Customer_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Customer_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Total_Collection_Amount] [money] NULL ,
[Account_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [ADMIN] (
[user_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[company_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
Now, there are multiple orphaned records in TRANSACTIONS (I will be using
TRANSACTIONS.CID = CUSTOMERS.CID, which is a new column I added to the TRANS
table). I can't rely on using the CustomerID field to join them as identica
l
Customers.CustomerID exist (even when grouping by Customers.CompanyID).
The problem I'm facing is that I have multiple records in the TRANS table
for the same Customer, but have different Company_IDs. These are the orphane
d
records I speak of that do not have a corresponding record in Customers. Th
e
Company_ID is derived by joining Transactions.User_Name = Admin.User_Name.
An example of a record in my Transactions table:
Tran_ID Customer_Name Admin.CompanyID
123 Home Repair R9
124 Home Repair R11
The company I work for operates under different dba (doing business as)
names. That's how this has happened (hence a customer_name having multiple
Company_IDs). What I plan to do is insert Distinct
Transactions.Customer_Name into Customers, retrieve the CID then populate
this value into theTransactions.CID field. Then I can simply remove the
Transactions.Customer_Name field. Now without adding two records to my
Customers table, how can I overcome this design flaw?As frightened as I am by the concept of having a many to many relationship
between transactions and customers, if that is what you need, then you will
need to have a xref table with the customerId and transactionId. It seems
to me that what needs to be done is have a company table, then a customer or
doingBusinessAs table that relates to the transaction. More thought needs
to go into your design.
Keep in mind the key part of database design, every table should represent
one thing. This is the basis of normalization, and it seems to me that your
customers table, and even your transactions table might be representing > 1
thing at a time, which generally will cause you problems like this as too
many things relate to too many things.
ou probably ought to standardize your names customerName customer_name, only
one naming style (clearly you probably only want to see that attribute once
in the db anyhow.)
The same concern is with CID and Transaction_ID or how about TransactionId.
I like TransactionId, but the key is to not make your users guess how
something will be named.
I know this is kind of a lot to swallow at once, but think about this
statement:

> I'm currently redesigning our db. Mind you I was NOT the designer of the
> current db.
The goal will be to not have the next person say the same about you :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4E996864-39D1-4866-B2C4-71AA3991C005@.microsoft.com...
> I'm currently redesigning our db. Mind you I was NOT the designer of the
> current db. I have the following tables:
> CREATE TABLE [CUSTOMERS] (
> [CID] [int] IDENTITY (1, 1) NOT NULL ,
> [customerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [customerID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [address] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [state] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [zip] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [businessName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [companyID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TRANSACTIONS] (
> [Transaction_ID] [int] NOT NULL ,
> [CID] [int] NULL ,
> [Customer_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Customer_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Total_Collection_Amount] [money] NULL ,
> [Account_Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [user_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> CREATE TABLE [ADMIN] (
> [user_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [company_ID] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> ) ON [PRIMARY]
> GO
> Now, there are multiple orphaned records in TRANSACTIONS (I will be using
> TRANSACTIONS.CID = CUSTOMERS.CID, which is a new column I added to the
> TRANS
> table). I can't rely on using the CustomerID field to join them as
> identical
> Customers.CustomerID exist (even when grouping by Customers.CompanyID).
> The problem I'm facing is that I have multiple records in the TRANS table
> for the same Customer, but have different Company_IDs. These are the
> orphaned
> records I speak of that do not have a corresponding record in Customers.
> The
> Company_ID is derived by joining Transactions.User_Name = Admin.User_Name.
> An example of a record in my Transactions table:
> Tran_ID Customer_Name Admin.CompanyID
> 123 Home Repair R9
> 124 Home Repair R11
> The company I work for operates under different dba (doing business as)
> names. That's how this has happened (hence a customer_name having
> multiple
> Company_IDs). What I plan to do is insert Distinct
> Transactions.Customer_Name into Customers, retrieve the CID then populate
> this value into theTransactions.CID field. Then I can simply remove the
> Transactions.Customer_Name field. Now without adding two records to my
> Customers table, how can I overcome this design flaw?
>|||You seem to have a composite candidate key ( customerID, companyID ) which
uniquely identify a customer in a transaction, right? If they are
duplicated then you should start over.
Generally, it is impossible to give you an accurate solution unless your
business model is familiar to others in this forum. However based on your
narrative one could reasonably conclude that you have an under-normalized
schema. In other words, you have various instances where multiple entity
types are bundled up into single table, for instance your transaction table
seems to have information about both transactions as well as customers, and
perhaps about accounts as well.
Unless, your business model and rules are thoroughly analyzed, it is hard to
provide any substantial advice. In the meantime, consider learning the data
design fundamentals and apply them to the business model in hand. If this is
time critical, considering a professional hire might be worth it - that last
statement in Louis' post has the gist.
Anith|||Louis - thanks for your input. Just some follow up:
"It seems to me that what needs to be done is have a company table..."
There actually already is one. However, the original db designer (who I
might add is no longer w/the company), decided to join Transactions.User_Nam
e
= Admin.User_Name, where the Admin table also contains the user's CompanyID.
So each user in Admin belongs to a Company_ID (so then Admin.Company_ID =
Company.Company_ID)
"...the key part of database design, every table should represent
one thing." I understand this, hence it's why I'm now redesigning it.
"The goal will be to not have the next person say the same about you :)" I
couldn't agree w/you more.
As for a proposed xref table, are you suggesting something like this:
Xref Table Columns:
XID
CID
CompanyID
So now, my Customers table will no longer have a CompanyID. Instead the
relationship will be Customers.CID = XREF.CID Next, I will have a new colum
n
in my TRANSACTIONS table, so that XREF.XID = TRANSACTIONS.XID. Some sample
data:
Customers Table:
CID CustomerName
2 A1 Home
XREF Table
XID CID CompanyID
33 2 R9
34 2 R11
Transactions Table
TranID XID Amount
1 33 $1.00
2 34 $1.25
Thanks for your help
"Louis Davidson" wrote:

> As frightened as I am by the concept of having a many to many relationship
> between transactions and customers, if that is what you need, then you wil
l
> need to have a xref table with the customerId and transactionId. It seems
> to me that what needs to be done is have a company table, then a customer
or
> doingBusinessAs table that relates to the transaction. More thought need
s
> to go into your design.
> Keep in mind the key part of database design, every table should represent
> one thing. This is the basis of normalization, and it seems to me that yo
ur
> customers table, and even your transactions table might be representing >
1
> thing at a time, which generally will cause you problems like this as too
> many things relate to too many things.
> ou probably ought to standardize your names customerName customer_name, on
ly
> one naming style (clearly you probably only want to see that attribute onc
e
> in the db anyhow.)
> The same concern is with CID and Transaction_ID or how about TransactionId
.
> I like TransactionId, but the key is to not make your users guess how
> something will be named.
> I know this is kind of a lot to swallow at once, but think about this
> statement:
>
> The goal will be to not have the next person say the same about you :)
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:4E996864-39D1-4866-B2C4-71AA3991C005@.microsoft.com...
>
>|||Actually, from your data here:

> Customers Table:
> CID CustomerName
> 2 A1 Home
> XREF Table
> XID CID CompanyID
> 33 2 R9
> 34 2 R11
> Transactions Table
> TranID XID Amount
> 1 33 $1.00
> 2 34 $1.25
The xref table is not really a simple many to many table. It is more of a
company allocation. It works, I think, since now both transactions are
allocated to customer 2, but tran1 is for their company r9, and tran2 is for
company r11.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:204589FB-CC82-40D6-A4F8-ED4EA500E7A0@.microsoft.com...
> Louis - thanks for your input. Just some follow up:
> "It seems to me that what needs to be done is have a company table..."
> There actually already is one. However, the original db designer (who I
> might add is no longer w/the company), decided to join
> Transactions.User_Name
> = Admin.User_Name, where the Admin table also contains the user's
> CompanyID.
> So each user in Admin belongs to a Company_ID (so then Admin.Company_ID =
> Company.Company_ID)
> "...the key part of database design, every table should represent
> one thing." I understand this, hence it's why I'm now redesigning it.
> "The goal will be to not have the next person say the same about you :)"
> I
> couldn't agree w/you more.
> As for a proposed xref table, are you suggesting something like this:
> Xref Table Columns:
> XID
> CID
> CompanyID
> So now, my Customers table will no longer have a CompanyID. Instead the
> relationship will be Customers.CID = XREF.CID Next, I will have a new
> column
> in my TRANSACTIONS table, so that XREF.XID = TRANSACTIONS.XID. Some
> sample
> data:
> Customers Table:
> CID CustomerName
> 2 A1 Home
> XREF Table
> XID CID CompanyID
> 33 2 R9
> 34 2 R11
> Transactions Table
> TranID XID Amount
> 1 33 $1.00
> 2 34 $1.25
> Thanks for your help
> "Louis Davidson" wrote:
>|||Find the guy that did this and kill him.
Almost every VARCHAR(n) is totally wrong or absurd. There are not
keys. All columns can be NULL, so you cannot ever have keys.
CHAR(20) as a ZIP code' Everything is a VARCHAR(<< magic number >> )
in this world. Give me an example of that stuff. The rest of the
stinking crap uses "magic numbers: like VARCHAR(50) for anything.
Codes without validation, etc.
Columns are not fields!! This is FOUNDATIONS of RDBMS!! And the
definition of an identifier is that it is unique to each entity. This
is a disaster without any hope of data integrity.
You need to throw the whole damn thing and start over. Other people
will tell you the same thing in a nicer way (i.e. "As frightened as I
am by the concept of having .."), but I tend to be blunt.|||--CELKO-- wrote:

>Other people
>will tell you the same thing in a nicer way (i.e. "As frightened as I
>am by the concept of having .."), but I tend to be blunt.
>
LOL!
Understatement of the century.
*mike hodgson*
http://sqlnerd.blogspot.com|||Actually, it was a woman.
"--CELKO--" wrote:

> Find the guy that did this and kill him.
> Almost every VARCHAR(n) is totally wrong or absurd. There are not
> keys. All columns can be NULL, so you cannot ever have keys.
> CHAR(20) as a ZIP code' Everything is a VARCHAR(<< magic number >> )
> in this world. Give me an example of that stuff. The rest of the
> stinking crap uses "magic numbers: like VARCHAR(50) for anything.
> Codes without validation, etc.
>
> Columns are not fields!! This is FOUNDATIONS of RDBMS!! And the
> definition of an identifier is that it is unique to each entity. This
> is a disaster without any hope of data integrity.
> You need to throw the whole damn thing and start over. Other people
> will tell you the same thing in a nicer way (i.e. "As frightened as I
> am by the concept of having .."), but I tend to be blunt.
>|||It's hard to believe that people are still modeling basic customer tables
and relationships from scratch. It's like re-developing a bubble sort
algorithm or re-inventing the wheel.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137741113.315703.103120@.g44g2000cwa.googlegroups.com...
> Find the guy that did this and kill him.
> Almost every VARCHAR(n) is totally wrong or absurd. There are not
> keys. All columns can be NULL, so you cannot ever have keys.
> CHAR(20) as a ZIP code' Everything is a VARCHAR(<< magic number >> )
> in this world. Give me an example of that stuff. The rest of the
> stinking crap uses "magic numbers: like VARCHAR(50) for anything.
> Codes without validation, etc.
>
> Columns are not fields!! This is FOUNDATIONS of RDBMS!! And the
> definition of an identifier is that it is unique to each entity. This
> is a disaster without any hope of data integrity.
> You need to throw the whole damn thing and start over. Other people
> will tell you the same thing in a nicer way (i.e. "As frightened as I
> am by the concept of having .."), but I tend to be blunt.
>|||Could you provide a link to a standard design?
"JT" <someone@.microsoft.com> wrote in message
news:eo%23aP6dHGHA.3448@.TK2MSFTNGP10.phx.gbl...
> It's hard to believe that people are still modeling basic customer tables
> and relationships from scratch. It's like re-developing a bubble sort
> algorithm or re-inventing the wheel.
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1137741113.315703.103120@.g44g2000cwa.googlegroups.com...
>