Counting how many characters per columns (database field)

Hi

I have an access database with lot's of memo fields and text fields

I would like to migrate to SQL

I need a method that gives me the number of character i am actually using in each of my access database field so that i set correctly my varchars in SQL

thx
LVL 1
humer2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nurbekCommented:
use Len function

mLength = Len(SR("myMemoField"))
0
nurbekCommented:
from sql try to get the max length

SELECT MAX(Len(myMemoField)) FROM myTable
0
humer2000Author Commented:
sorry i am new to this
i need an automated way that writes on the screen the number of characters used per field / table
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

lengreenCommented:
Hi

I think you are after something like this

<%


Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\inetpub\wwwroot\asp\db\employees.mdb;Uid=Admin;Pwd=;"


set RS = server.createobject("ADODB.recordset")


RS.Open "SELECT * FROM tblemployees", DataConn



FOR i=0 to RS.Fields.Count-1
   response.write "NAME=" & RS.Fields(i).Name
   response.write "<BR>SIZE=" & RS.Fields(RS.Fields(i).Name).definedsize
   response.write "<BR><BR>"
NEXT

RS.Close
DataConn.Close
set RS = Nothing
set DataConn = Nothing


 %>

cheers

Len
0
lengreenCommented:
also

FOR i=0 to RS.Fields.Count-1
   response.write "NAME=" & RS.Fields(i).Name
   response.write "<BR>SIZE=" & RS.Fields(RS.Fields(i).Name).definedsize
   response.write "<BR>Type=" & RS.Fields(RS.Fields(i).Name).type
   response.write "<BR><BR>"
NEXT

you should then be able to work out the Name, datatype & size for your new SQL Server Field

cheers

Len
0
humer2000Author Commented:
it does not give me the result i need

it gives the following :

NAME=cname
SIZE=255
Type=202

the cname field is a text field (type 202) and it supports 255 characters
but it does not tell me how many characters ar occupied or left ??
this is what i need for my SQL migration
0
Anant KuslekarSr. Strategy Manager - IT ConsultancyCommented:

DTS-Export data(even without data).

Source as u r mS-Access
Destn as S-SQL server

And finish migration.
Why to worry lenght of field before hand.

DTS is there to take care.
After generate MS-SQL script and trim it.
0
humer2000Author Commented:
because i have lot's of fields and whilevarchars in SQL cannot exceed 8064 charatcers and while i cannot use the text or ntext type
i need to set my varchars correctly

ie, if all my cname requires maximum 24 charcters ten i set my varchar to 25 rather then fixing it blindly to 50

DTS is not ok for me, i have already tried it
 
0
lengreenCommented:
If you use a combination of mine & nubeks solution you will e able to get what you need, I don't have time to fully code this at the moment but  

do something like



  FOR i=0 to RS.Fields.Count-1
     response.write "NAME=" & RS.Fields(i).Name
     response.write "<BR>SIZE=" & RS.Fields(RS.Fields(i).Name).definedsize
     response.write "<BR>Type=" & RS.Fields(RS.Fields(i).Name).type
     

     For each row in RS ' set up a counter
       maxlength = 0
       length = row(RS.Fields(i).Name)
        if length > maxlength then
           maxlength =length
        end if
     Loop
     response.write "<BR>maxlength of field=" & maxlength
     response.write "<BR><BR>"
  NEXT

(not the most effiicient, but as its a one off)

you will now have your field length, the maximum length of any field in that row, and you can calculate any other value you want

cheers

Len


0
humer2000Author Commented:
not working
i am getting this error

Microsoft VBScript compilation error '800a040e'

'loop' without 'do'

/csasp_db_countchar.asp, line 22

Loop
^
0
lengreenCommented:
That was just to give you the idea

For irow = 0 to  RS.rowcount -1
       maxlength = 0
       length = len(row(RS.Fields(irow).Name))
        if length > maxlength then
           maxlength =length
        end if
next
0
humer2000Author Commented:
i am new to this and with your code errors, i guess i am too far from solution or even having an idea
you new code generates the following error

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'rowcount'

/csasp_db_countchar.asp, line 16

0
lengreenCommented:

Hi

Okay the code I have given you is just a suggestion to help you, not the exact code(see previous posts), if you are saying I am new to this & want to learn ado / asp then you need a good book or somewhere like www.w3schools.com and look under Server Scripting

This is the correct code, you now will have the info you requested



<%


Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\inetpub\wwwroot\asp\db\employees.mdb;Uid=Admin;Pwd=;"


set RS = server.createobject("ADODB.recordset")


on error resume next
RS.CursorLocation = 3 ' adUseClient
RS.Open "SELECT * FROM tblEmployees", DataConn

FOR i=0 to RS.Fields.Count-1
     response.write "NAME=" & RS.Fields(i).Name
     response.write "<BR>SIZE=" & RS.Fields(RS.Fields(i).Name).definedsize
     response.write "<BR>Type=" & RS.Fields(RS.Fields(i).Name).type
     
  RS.movefirst
  do While not RS.EOF
      
       maxlength = 0
       length = len(RS(i))
        if length > maxlength then
           maxlength =length
        end if
            RS.movenext
     loop
     response.write "<BR>longest entry in this field is " & maxlength &" chars long"
     response.write "<BR><BR>"
      
  next

RS.Close
DataConn.Close
set RS = Nothing
set DataConn = Nothing
if err <> 0 then

response.write err.description

end if


 %>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
humer2000Author Commented:
I hurried when i posted your answer as ACCEPTED
but in fact it's not resolved

i am not getting any error but the results obtained are not accurate
ie , it says for example
cdescription
SIZE= 536870910
Type= 203
longest entry in this field is 128 chars long  

when i check my table
i have records with 2966 characters

and also when it says

specialoffer
SIZE= 100
Type= 202
longest entry in this field is 0 chars long  

After checking my table this fields contains a message of 86 characters

what's the problem

0
lengreenCommented:
oopps  sorry, should be

 RS.movefirst
 maxlength = 0
  do While not RS.EOF
     
       
0
humer2000Author Commented:
great,
it works great
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.