imports table structures in SQL 2000 into Excel

Hi.
Is there anyway to export the table structures : data type,length,NULLABLE,Description into an Excel file using MS SQL Server?

Or I need to do it manually?
Thank you in advanced.
Sincerely

AgustinaRun this in Query Analyzer: (common data types, add the the case statement for more)

select name,
case xtype
 when 56 then 'Int'
 when 127 then 'BigInt'
 when 167 then 'VarChar'
 when 175 then 'Char'
 when 60 then 'Money'
 when 58 then 'SmallDateTime'
 when 104 then 'Bit'
 when 173 then 'TimeStamp'
 when 61 then 'DateTime'
 when 48 then 'TinyInt'
 else 'Other' end,
length
from syscolumns
where id = (
select id
from sysobjects
where name = 'TheTableName')
order by colid

|||You could look up the Schema. Run this in Query Analyzer and adjust accordingly:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = '<DATABASE NAME>' AND TABLE_SCHEMA = '<DB OWNER>' AND TABLE_NAME = '<YOUR TABLES NAME>'

sql

Importing text File

I am trying to import a text file into Crystal 9. I have used text ODBC drivers, MS Assess type connections. Whatever I try, the first record is always skipped. Crystal is using the 1st record as a field label. The file is a straight ASCII file, 80 characters long per record. There are no field labels.

Any suggestions?You have no choice. All fields need label and CR identifies first record as field label.

Importing Excel in SQL 2005

Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
Data in Mgmt Studio I get this -
The connection type "EXCEL" specified for connection manager
"{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
Thanks
--
RichRich
Peter Yang seems to have answered this in .tools. A reinstall of MDAC was
required. Please do not multi-post the same message.
John
"Richard" wrote:
> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich|||Opps... a different Rich!!
See http://tinyurl.com/f5uqe
John
"Richard" wrote:
> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich

Importing Excel in SQL 2005

Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Import
Data in Mgmt Studio I get this -
The connection type "EXCEL" specified for connection manager
"{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
connection manager type. This error is returned when an attempt is made to
create a connection manager for an unknown connection type. Check the
spelling in the connection type name.
({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
Thanks
--
RichRich
Peter Yang seems to have answered this in .tools. A reinstall of MDAC was
required. Please do not multi-post the same message.
John
"Richard" wrote:

> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Impo
rt
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich|||Opps... a different Rich!!
See http://tinyurl.com/f5uqe
John
"Richard" wrote:

> Whenever I try to import an Excel spreadsheet to SQL 2005, using Task Impo
rt
> Data in Mgmt Studio I get this -
> The connection type "EXCEL" specified for connection manager
> "{418DD599-C076-4CBC-8A52-60EF9684B965}" is not recognized as a valid
> connection manager type. This error is returned when an attempt is made to
> create a connection manager for an unknown connection type. Check the
> spelling in the connection type name.
> ({FAF6492C-0838-41EF-8D56-07E4F3B4C831})
>
> Thanks
> --
> Rich

Importing date w/SQLExpress appends '.000' to field

Hello
I have a DOB field in text file I am importing into an SQLExpress database
with the field data type is set to 'datetime'. For some reason the date
imports as 1958-08-01 00:00:00.000 ( with a decimal and 3 zeros appended to
the end of the field ). What am I doing wrong? There is no time associated
with this date in the flat file.
Thanks for your help...Dale,
That is the normal action for a datetime. The datetime datatype includes
hours, minutes, seconds, and milliseconds. If you store a date (without the
time value), the default time is midnight, or 00:00:00.000.
If possible, you may wish to change the datatype for the DOB field to
smalldatetime. Smalldatetime will have only hours and minutes for the time
portion -but they too will be set to the default, midnight, e.g., 00:00.
When you retrieve data, you can always remove the time portion either in the
data retrieval query, or in the display application or reporting
application. For T-SQL, look in Books Online for "CAST and CONVERT" for
information about datetime formats.
Likewise, if you were to have a field that you wanted only time values, and
if you inserted [12:45 PM] into that field, SQL Server will add the default
date value, 01/01/1900.
I hope this helps understand what is going on, and points you in a direction
to more relevant information.
Regards,
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dale" <dale@.nospam.com> wrote in message
news:OOMGHdxwGHA.3964@.TK2MSFTNGP04.phx.gbl...
> Hello
> I have a DOB field in text file I am importing into an SQLExpress database
> with the field data type is set to 'datetime'. For some reason the date
> imports as 1958-08-01 00:00:00.000 ( with a decimal and 3 zeros appended
> to the end of the field ). What am I doing wrong? There is no time
> associated with this date in the flat file.
> Thanks for your help...
>|||Thanks Arnie
I guess what threw me is the behaviour isn't consistent sometimes the import
has the '.000' appended and other times not despite having the data type as
datetime and the flat file is always the same.
But at least you indicated it wasn't something I was doing.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23O$VCnywGHA.1484@.TK2MSFTNGP04.phx.gbl...
> Dale,
> That is the normal action for a datetime. The datetime datatype includes
> hours, minutes, seconds, and milliseconds. If you store a date (without
> the time value), the default time is midnight, or 00:00:00.000.
> If possible, you may wish to change the datatype for the DOB field to
> smalldatetime. Smalldatetime will have only hours and minutes for the time
> portion -but they too will be set to the default, midnight, e.g., 00:00.
> When you retrieve data, you can always remove the time portion either in
> the data retrieval query, or in the display application or reporting
> application. For T-SQL, look in Books Online for "CAST and CONVERT" for
> information about datetime formats.
> Likewise, if you were to have a field that you wanted only time values,
> and if you inserted [12:45 PM] into that field, SQL Server will add the
> default date value, 01/01/1900.
> I hope this helps understand what is going on, and points you in a
> direction to more relevant information.
> Regards,
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dale" <dale@.nospam.com> wrote in message
> news:OOMGHdxwGHA.3964@.TK2MSFTNGP04.phx.gbl...
>> Hello
>> I have a DOB field in text file I am importing into an SQLExpress
>> database with the field data type is set to 'datetime'. For some reason
>> the date imports as 1958-08-01 00:00:00.000 ( with a decimal and 3 zeros
>> appended to the end of the field ). What am I doing wrong? There is no
>> time associated with this date in the flat file.
>> Thanks for your help...
>

Importing date w/SQLExpress appends '.000' to field

Hello
I have a DOB field in text file I am importing into an SQLExpress database
with the field data type is set to 'datetime'. For some reason the date
imports as 1958-08-01 00:00:00.000 ( with a decimal and 3 zeros appended to
the end of the field ). What am I doing wrong? There is no time associated
with this date in the flat file.
Thanks for your help...Dale,
That is the normal action for a datetime. The datetime datatype includes
hours, minutes, seconds, and milliseconds. If you store a date (without the
time value), the default time is midnight, or 00:00:00.000.
If possible, you may wish to change the datatype for the DOB field to
smalldatetime. Smalldatetime will have only hours and minutes for the time
portion -but they too will be set to the default, midnight, e.g., 00:00.
When you retrieve data, you can always remove the time portion either in the
data retrieval query, or in the display application or reporting
application. For T-SQL, look in Books Online for "CAST and CONVERT" for
information about datetime formats.
Likewise, if you were to have a field that you wanted only time values, and
if you inserted [12:45 PM] into that field, SQL Server will add the defa
ult
date value, 01/01/1900.
I hope this helps understand what is going on, and points you in a direction
to more relevant information.
Regards,
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dale" <dale@.nospam.com> wrote in message
news:OOMGHdxwGHA.3964@.TK2MSFTNGP04.phx.gbl...
> Hello
> I have a DOB field in text file I am importing into an SQLExpress database
> with the field data type is set to 'datetime'. For some reason the date
> imports as 1958-08-01 00:00:00.000 ( with a decimal and 3 zeros appended
> to the end of the field ). What am I doing wrong? There is no time
> associated with this date in the flat file.
> Thanks for your help...
>|||Thanks Arnie
I guess what threw me is the behaviour isn't consistent sometimes the import
has the '.000' appended and other times not despite having the data type as
datetime and the flat file is always the same.
But at least you indicated it wasn't something I was doing.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23O$VCnywGHA.1484@.TK2MSFTNGP04.phx.gbl...
> Dale,
> That is the normal action for a datetime. The datetime datatype includes
> hours, minutes, seconds, and milliseconds. If you store a date (without
> the time value), the default time is midnight, or 00:00:00.000.
> If possible, you may wish to change the datatype for the DOB field to
> smalldatetime. Smalldatetime will have only hours and minutes for the time
> portion -but they too will be set to the default, midnight, e.g., 00:00.
> When you retrieve data, you can always remove the time portion either in
> the data retrieval query, or in the display application or reporting
> application. For T-SQL, look in Books Online for "CAST and CONVERT" for
> information about datetime formats.
> Likewise, if you were to have a field that you wanted only time values,
> and if you inserted [12:45 PM] into that field, SQL Server will add th
e
> default date value, 01/01/1900.
> I hope this helps understand what is going on, and points you in a
> direction to more relevant information.
> Regards,
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Dale" <dale@.nospam.com> wrote in message
> news:OOMGHdxwGHA.3964@.TK2MSFTNGP04.phx.gbl...
>

importing a text file

Hi
I am trying to import a text file into sql server..but i am not able to do so..because the size of the var type is 8000..
and i have too many columns to change the variable manually
thus i copied the create table query from the import window
and i got an error
The table 'test' has been created but its maximum row size (25863) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

can some help
thanksDid you consider to use the memo data type TEXT instead VARCHAR?|||Is you column defined as char or is it defined as varchar?

A char column always requires the same amount of space, equivalent to its defined length.

A varchar column only requires as much space as the data it holds.

Are you trying to import an entire record into a single field? If you have a single field in your source data that is 8000+ characters, your only option is to split it or use a text or image datatype.

If you are still having problems, post your table definition and the sql you using to import the data.

blindman|||I don't think SQL would even allow you to create a table of the specified size with CHAR. Have you tried it yourself, bm?:cool:|||Thank you for all the response..
the solution to my problem..I edit the create table query during my import process and change the 8000 to 255 and then with that edited query i make an empty table and then i append my orginal text file with data into that empty table..
Well it is a long process..but it keeps going.
thanks for the help|||Are you saying that a maximum size of 255 is sufficient for you? If not, I repeat myself, consider to use TEXT instead of VARCHAR. It allows you to import texts of variable length without getting on the row length limits.|||The text and image type should not be used unless absolutely necessary. They require additional overhead, and have greatly limited functionality compared to char and varchar.

blindman|||I'd say, it's a pretty blunt statement. It invalidates the very presence of existence of companies that specialize in DMS (document management systems).|||Blunt yes, but not completely dull. Document Management is an area where the use of text and image data may be absolutely necessary.

blindman|||Document Management capabilities are present in many software products. Actually, those that are least reliable are the ones that avoid the usage of datatypes that are targeting this specific need, - to store large volumes of data. I've seen tricks like storing paths to actual document locations, or parsing text documents into 70-character lines. Cute, but if path is stored, - there is no guarantee for integrity of the referenced document, and in case of parsing - very resource-intensive in respect to drastic growth of such tables while loosing any formating information. Contrary to that, here we even store web pages in text fields and scan paper claims and store them into image. You also mentioned limited functionality? What do you mean? Each datatype should be treated accordingly, so if you attempt to treat text field using char/varchar approach, - sure, you loose functionality. But not because of "limited functionality" of the datatype... Wonder, because of what? ;)|||rdjabarov,

All the examples you mentioned are legitimate uses of text and image data.

As far as limited functionality, try indexing your text fields.
...or sorting them
...or using the LIKE operator
...or concatenating them

blindman|||bm: operations you mentioned are not designed for TEXT and IMAGE datatypes. But I like your way of looking at it:

- can you multiply a CHAR field?
- can you index a BIT field?
- can you store 2G of data into VARCHAR field?

Good luck answering :rolleyes:|||rdjabarov,

No. I use the appropriate datatype for the data. I recommend that you do the same. If you are using text and image columns in your design when you don't need to, then I pity the poor DBA who has to clean up after you when you get canned.

blindman|||By the way: what about a text-indexed table, or the powerful CONTAINS() function searching for words in a text field ?!

However, we shouldn't bother NosWal anymore. If anybody still has to say about the (ab)use of data types, I suggest to open a new thread.|||bm: you're REALLY on a mission. You just never answered what its name. What are you trying to say? It looks like you adore listening to yourself. I suggest call-forwarding services, until your office phone gets reassigned to your replacement. Then, - it'll be just this forum, until your internet connection gets cut due to late payments. Anything else you want to say please take it offline.|||rjabberon,

That makes five posts for you on this thread, and none of them have provided any assistance to noswal. Whatever mission I'm on, it ain't yours.|||that's a cute way of spelling my nick :)

But please don't count my posts, because your employer will fire you for your "math" :)