[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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
0
humer2000
Asked:
humer2000
  • 7
  • 6
  • 2
  • +1
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now