Unable to insert record with datatype sysname

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Unable to insert record with datatype sysname

Author

Topic

Mamatha
Posting Yak Master

102 Posts

Posted - 2005-01-20 : 06:21:20

Hi

I am trying to convert SQL server table into Access table using ASP,before that we retrieve the SQL table structure,in that table strudture if any field contains the datatype nvarchar then field name repeated with "sysname" datatype.But we have some problem with sysname,while trying to convert into Access datatype it displays error.How can we manage the SQL table structure without repeatation.
The code is

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body>
<%
'Set Conn = Server.CreateObject("ADODB.Connection")
Set cn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=SERVER;UID=sa;PWD=thanks;DATABASE=parameter"
cn.open DSNtest
Set sysObjects = cn.Execute("SELECT * FROM sysObjects WHERE xtype = 'U' ORDER BY name")

If sysObjects.eof Then
Response.write("No records returned")
End If

do until sysObjects.eof
'if not sysObjects("name")="dtproperties" then

Response.write("<table width='100%' border='1'>")
Response.write("<tr><td colspan='3'>Table Name:" & sysObjects("name") & "</td></tr>")
Response.write("<tr><td>Field Name</td><td>Data Type</td><td>Data Type Length</td></tr>")
Set sysColumns = cn.Execute("SELECT A.name As 'ColumnName', B.name As 'DataType', A.length As 'DataTypeLength' FROM sysColumns As A INNER JOIN sysTypes As B On (A.xtype = B.xtype) WHERE id = " & sysObjects("id") & " ORDER BY A.name")

if sysColumns.eof Then
Response.write("<tr><td>No columns found for " & sysObjects("name") & " table.</td></tr>")
end if

do until sysColumns.eof
Response.write("<tr><td>" & sysColumns("ColumnName") & "</td><td>" & sysColumns("DataType") & "</td><td>" & sysColumns("DataTypeLength") & "</td></tr>")
sysColumns.movenext
loop
Response.write("</table>")
Response.write(" ")
sysObjects.movenext
'end if
loop

cn.Close
Set cn = Nothing
%>

</body>

</html>

Thanks in advance.

Mamatha

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-20 : 07:04:51

How does building an ASP/HTML page help you to convert the tables to Access? You can link the SQL Server tables in Access, then use them in make-table queries to build new Access tables with identical structures. This can be automated with a few lines of VB to process all of the tables in one go.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources