So, I'm now in the middle of trying to convert the VFP data to SQL - and

I get an error like this:

`po_lines` -> [dbo].[po_lines]:

- The data type could not be assigned to the column

"packslip_line_msg_line_no1" in "SQL Server Native Client 10.0".

- The data type could not be assigned to the column

"vendor_line_notes" in "SQL Server Native Client 10.0".

I know it's the issue w/Memo fields - since SQL doesn't have anything

like it. But, if I want to use something even CLOSE to a memo field

within SQL - what type of field should I use? I saw there is Char,

VarChar, Text and more - I just don't the actually differences between

them.

TIA,

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:20 AM

OK - my Bad!

Obviously I need to go into that Properties button & select the VFP DBC

- before I can hit Next. I made the wrong assumption before - figuring

that when I hit NEXT - it would THEN prompt me for the VFP DBC that I

wanted to Import.

Once I selected the VFP DBC from within the Properties button - I was

now able to successfully hit the Next button.

Just FYI - I've never really worked with SQL before - at least not the

extent of setting up an SQL Database or even installing SQL.

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:11 AM

In regards to my previous discussion on here - I just installed MS SQL

Server 2008 R2 Express. And, upon looking under programs to run it - I

found this "SQL Server Import and Export Wizard" tool. So - I ran it -

and found that one of the Data Sources available to choose from is

"Microsoft OLE DB Provider for Visual FoxPro". However, upon selecting

that option and hitting Next - I get an error window "Feature is Not

Available. (Microsoft OLE DB Provider for Visual FoxPro)

So - does that just mean that it's not available under the Express

version? IF that IS the case - I find it quite annoying, since if the

feature is supposed to be N/A - then it should NOT even allow you to

select it to begin with!!!

Just to add - I saw the Properties button under the Data Source

selection drop-down, but, I did NOT change any of those Properties.

Thanks,

-K-

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/289EA162F5642645B5CF64D624C66A140E260324@us-ny-mail-002.waitex.net

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

Kury,

If you want to hold Blob data then use Varbinary(Max) but if it is just text data (memo field) use text.

The ODBC driver maps Memo in VFP directly to Text in SQL and vice versa.

Dave

-----Original Message-----

From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On Behalf Of Kurt Wendt

Sent: 15 May 2012 17:12

To: profox@leafe.com

Subject: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data Source]

So, I'm now in the middle of trying to convert the VFP data to SQL - and I get an error like this:

`po_lines` -> [dbo].[po_lines]:

- The data type could not be assigned to the column "packslip_line_msg_line_no1" in "SQL Server Native Client 10.0".

- The data type could not be assigned to the column "vendor_line_notes" in "SQL Server Native Client 10.0".

I know it's the issue w/Memo fields - since SQL doesn't have anything like it. But, if I want to use something even CLOSE to a memo field within SQL - what type of field should I use? I saw there is Char, VarChar, Text and more - I just don't the actually differences between them.

TIA,

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:20 AM

OK - my Bad!

Obviously I need to go into that Properties button & select the VFP DBC

- before I can hit Next. I made the wrong assumption before - figuring that when I hit NEXT - it would THEN prompt me for the VFP DBC that I wanted to Import.

Once I selected the VFP DBC from within the Properties button - I was now able to successfully hit the Next button.

Just FYI - I've never really worked with SQL before - at least not the extent of setting up an SQL Database or even installing SQL.

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:11 AM

In regards to my previous discussion on here - I just installed MS SQL Server 2008 R2 Express. And, upon looking under programs to run it - I found this "SQL Server Import and Export Wizard" tool. So - I ran it - and found that one of the Data Sources available to choose from is "Microsoft OLE DB Provider for Visual FoxPro". However, upon selecting that option and hitting Next - I get an error window "Feature is Not Available. (Microsoft OLE DB Provider for Visual FoxPro)

So - does that just mean that it's not available under the Express version? IF that IS the case - I find it quite annoying, since if the feature is supposed to be N/A - then it should NOT even allow you to select it to begin with!!!

Just to add - I saw the Properties button under the Data Source selection drop-down, but, I did NOT change any of those Properties.

Thanks,

-K-

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/BD031ECABF2B60499200AAB3DBB4A999F11E464C@EX-A-FPL.FPL.LOCAL

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

After sending the last e-mail, I decided to turn to my Russian buddy

here (of the 4 other programmers here - he is the ONLY Nice one!). He

suggested I used NVarchar. Is that not a good option?

And, yes, the fields were just holding regular Text type data before.

Sounds like the Text field is probably the best option to use - as you

suggested.

FYI - after I reset the Mappings for those fields to convert as

NVarchar, it let me proceed with the conversion - but, then it ran into

errors when the conversion was actually in process. One of those errors

are as follows:

Setting Source Connection (Warning)

Messages

Warning 0x80202066: Source - arch_head [1]: Cannot retrieve the column

code page info from the OLE DB provider. If the component supports the

"DefaultCodePage" property, the code page from that property will be

used. Change the value of the property if the current string code page

values are incorrect. If the component does not support the property,

the code page from the component's locale ID will be used.

(SQL Server Import and Export Wizard)

Do I actually need to open up the files in VFP and change some kind of

Code Page???

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Dave Crozier

Sent: Tuesday, May 15, 2012 12:28 PM

To: profoxtech@leafe.com

Subject: RE: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data

Source]

Kury,

If you want to hold Blob data then use Varbinary(Max) but if it is just

text data (memo field) use text.

The ODBC driver maps Memo in VFP directly to Text in SQL and vice versa.

Dave

-----Original Message-----

From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On

Behalf Of Kurt Wendt

Sent: 15 May 2012 17:12

To: profox@leafe.com

Subject: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data

Source]

So, I'm now in the middle of trying to convert the VFP data to SQL - and

I get an error like this:

`po_lines` -> [dbo].[po_lines]:

- The data type could not be assigned to the column

"packslip_line_msg_line_no1" in "SQL Server Native Client 10.0".

- The data type could not be assigned to the column

"vendor_line_notes" in "SQL Server Native Client 10.0".

I know it's the issue w/Memo fields - since SQL doesn't have anything

like it. But, if I want to use something even CLOSE to a memo field

within SQL - what type of field should I use? I saw there is Char,

VarChar, Text and more - I just don't the actually differences between

them.

TIA,

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:20 AM

OK - my Bad!

Obviously I need to go into that Properties button & select the VFP DBC

- before I can hit Next. I made the wrong assumption before - figuring

that when I hit NEXT - it would THEN prompt me for the VFP DBC that I

wanted to Import.

Once I selected the VFP DBC from within the Properties button - I was

now able to successfully hit the Next button.

Just FYI - I've never really worked with SQL before - at least not the

extent of setting up an SQL Database or even installing SQL.

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:11 AM

In regards to my previous discussion on here - I just installed MS SQL

Server 2008 R2 Express. And, upon looking under programs to run it - I

found this "SQL Server Import and Export Wizard" tool. So - I ran it -

and found that one of the Data Sources available to choose from is

"Microsoft OLE DB Provider for Visual FoxPro". However, upon selecting

that option and hitting Next - I get an error window "Feature is Not

Available. (Microsoft OLE DB Provider for Visual FoxPro)

So - does that just mean that it's not available under the Express

version? IF that IS the case - I find it quite annoying, since if the

feature is supposed to be N/A - then it should NOT even allow you to

select it to begin with!!!

Just to add - I saw the Properties button under the Data Source

selection drop-down, but, I did NOT change any of those Properties.

Thanks,

-K-

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/289EA162F5642645B5CF64D624C66A140E260326@us-ny-mail-002.waitex.net

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

Seems I didn't scroll down far enough - since the last one I reported

was a Warning - while the following is an actual ERROR - which stopped

the process:

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 12:33 PM

To: profoxtech@leafe.com

Subject: RE: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data

Source]

After sending the last e-mail, I decided to turn to my Russian buddy

here (of the 4 other programmers here - he is the ONLY Nice one!). He

suggested I used NVarchar. Is that not a good option?

And, yes, the fields were just holding regular Text type data before.

Sounds like the Text field is probably the best option to use - as you

suggested.

FYI - after I reset the Mappings for those fields to convert as

NVarchar, it let me proceed with the conversion - but, then it ran into

errors when the conversion was actually in process. One of those errors

are as follows:

Setting Source Connection (Warning)

Messages

Warning 0x80202066: Source - arch_head [1]: Cannot retrieve the column

code page info from the OLE DB provider. If the component supports the

"DefaultCodePage" property, the code page from that property will be

used. Change the value of the property if the current string code page

values are incorrect. If the component does not support the property,

the code page from the component's locale ID will be used.

(SQL Server Import and Export Wizard)

Do I actually need to open up the files in VFP and change some kind of

Code Page???

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Dave Crozier

Sent: Tuesday, May 15, 2012 12:28 PM

To: profoxtech@leafe.com

Subject: RE: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data

Source]

Kury,

If you want to hold Blob data then use Varbinary(Max) but if it is just

text data (memo field) use text.

The ODBC driver maps Memo in VFP directly to Text in SQL and vice versa.

Dave

-----Original Message-----

From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On

Behalf Of Kurt Wendt

Sent: 15 May 2012 17:12

To: profox@leafe.com

Subject: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data

Source]

So, I'm now in the middle of trying to convert the VFP data to SQL - and

I get an error like this:

`po_lines` -> [dbo].[po_lines]:

- The data type could not be assigned to the column

"packslip_line_msg_line_no1" in "SQL Server Native Client 10.0".

- The data type could not be assigned to the column

"vendor_line_notes" in "SQL Server Native Client 10.0".

I know it's the issue w/Memo fields - since SQL doesn't have anything

like it. But, if I want to use something even CLOSE to a memo field

within SQL - what type of field should I use? I saw there is Char,

VarChar, Text and more - I just don't the actually differences between

them.

TIA,

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:20 AM

OK - my Bad!

Obviously I need to go into that Properties button & select the VFP DBC

- before I can hit Next. I made the wrong assumption before - figuring

that when I hit NEXT - it would THEN prompt me for the VFP DBC that I

wanted to Import.

Once I selected the VFP DBC from within the Properties button - I was

now able to successfully hit the Next button.

Just FYI - I've never really worked with SQL before - at least not the

extent of setting up an SQL Database or even installing SQL.

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:11 AM

In regards to my previous discussion on here - I just installed MS SQL

Server 2008 R2 Express. And, upon looking under programs to run it - I

found this "SQL Server Import and Export Wizard" tool. So - I ran it -

and found that one of the Data Sources available to choose from is

"Microsoft OLE DB Provider for Visual FoxPro". However, upon selecting

that option and hitting Next - I get an error window "Feature is Not

Available. (Microsoft OLE DB Provider for Visual FoxPro)

So - does that just mean that it's not available under the Express

version? IF that IS the case - I find it quite annoying, since if the

feature is supposed to be N/A - then it should NOT even allow you to

select it to begin with!!!

Just to add - I saw the Properties button under the Data Source

selection drop-down, but, I did NOT change any of those Properties.

Thanks,

-K-

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/289EA162F5642645B5CF64D624C66A140E260327@us-ny-mail-002.waitex.net

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

Darn - I hit a wrong key and sent the e-mail incomplete.

Error was:

- Executing (Error)

Messages

Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE

TABLE [dbo].[arch_head] (

[file_type] char..." failed with the following error: "The size

(2147483647) given to the column 'packslip_message' exceeds the maximum

allowed for any data type (8000).". Possible failure reasons: Problems

with the query, "ResultSet" property not set correctly, parameters not

set correctly, or connection not established correctly.

(SQL Server Import and Export Wizard)

Dave - I'm going to take your suggestion and use TEXT field - since that

one will probably NOT give me this problem! Also, although I do like

eating Curry (even though these days I can't do it any longer - as its

even more painful on the Way Out) - I'm generally not referred to as

Kury!

:-)

Kurt

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 12:40 PM

Seems I didn't scroll down far enough - since the last one I reported

was a Warning - while the following is an actual ERROR - which stopped

the process:

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 12:33 PM

After sending the last e-mail, I decided to turn to my Russian buddy

here (of the 4 other programmers here - he is the ONLY Nice one!). He

suggested I used NVarchar. Is that not a good option?

And, yes, the fields were just holding regular Text type data before.

Sounds like the Text field is probably the best option to use - as you

suggested.

FYI - after I reset the Mappings for those fields to convert as

NVarchar, it let me proceed with the conversion - but, then it ran into

errors when the conversion was actually in process. One of those errors

are as follows:

Setting Source Connection (Warning)

Messages

Warning 0x80202066: Source - arch_head [1]: Cannot retrieve the column

code page info from the OLE DB provider. If the component supports the

"DefaultCodePage" property, the code page from that property will be

used. Change the value of the property if the current string code page

values are incorrect. If the component does not support the property,

the code page from the component's locale ID will be used.

(SQL Server Import and Export Wizard)

Do I actually need to open up the files in VFP and change some kind of

Code Page???

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Dave Crozier

Sent: Tuesday, May 15, 2012 12:28 PM

Kury,

If you want to hold Blob data then use Varbinary(Max) but if it is just

text data (memo field) use text.

The ODBC driver maps Memo in VFP directly to Text in SQL and vice versa.

Dave

-----Original Message-----

From: profox-bounces@leafe.com [mailto:profox-bounces@leafe.com] On

Behalf Of Kurt Wendt

Sent: 15 May 2012 17:12

So, I'm now in the middle of trying to convert the VFP data to SQL - and

I get an error like this:

`po_lines` -> [dbo].[po_lines]:

- The data type could not be assigned to the column

"packslip_line_msg_line_no1" in "SQL Server Native Client 10.0".

- The data type could not be assigned to the column

"vendor_line_notes" in "SQL Server Native Client 10.0".

I know it's the issue w/Memo fields - since SQL doesn't have anything

like it. But, if I want to use something even CLOSE to a memo field

within SQL - what type of field should I use? I saw there is Char,

VarChar, Text and more - I just don't the actually differences between

them...

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/289EA162F5642645B5CF64D624C66A140E260328@us-ny-mail-002.waitex.net

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

SQL<->VFP

CHAR=CHARACTER (fixed width column definition)

VARCHAR=VARCHAR (variable width column definition although VFP fakes it)

TEXT=MEMO (unlimited width for most practical purposes)

VARCHAR and NVARCHAR can work in place of TEXT and are more efficient in terms of storage in SQL land. You can also avoid certain limitations of TEXT columns by using VARCHAR. For example, you can't use aggregate functions on TEXT columns. NVARCHAR & NTEXT are used for storing binary data and typically used when you want to store text in a language/encoding scheme that is different from the encoding of the DB itself, which is probably why your Russian friend recommended that. Speaking of encoding, that's another thing that can bite you so you need to be aware of potential differences in your source data and the encoding of your SQL database.

I don't think you said how you were trying to move your data around - SPT? Remote views? ODBC/OLE DB will attempt to do implicit conversions of certain data types to other data types but it doesn't always get it right and you can get the kind of message you reported. When that conversion is not applicable, you will need to do your own conversions using CAST or CONVERT.

--

rk

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 12:12 PM

To: profoxtech@leafe.com

Subject: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data Source]

So, I'm now in the middle of trying to convert the VFP data to SQL - and I get an error like this:

`po_lines` -> [dbo].[po_lines]:

- The data type could not be assigned to the column "packslip_line_msg_line_no1" in "SQL Server Native Client 10.0".

- The data type could not be assigned to the column "vendor_line_notes" in "SQL Server Native Client 10.0".

I know it's the issue w/Memo fields - since SQL doesn't have anything like it. But, if I want to use something even CLOSE to a memo field within SQL - what type of field should I use? I saw there is Char, VarChar, Text and more - I just don't the actually differences between them.

TIA,

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:20 AM

OK - my Bad!

Obviously I need to go into that Properties button & select the VFP DBC

- before I can hit Next. I made the wrong assumption before - figuring that when I hit NEXT - it would THEN prompt me for the VFP DBC that I wanted to Import.

Once I selected the VFP DBC from within the Properties button - I was now able to successfully hit the Next button.

Just FYI - I've never really worked with SQL before - at least not the extent of setting up an SQL Database or even installing SQL.

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:11 AM

In regards to my previous discussion on here - I just installed MS SQL Server 2008 R2 Express. And, upon looking under programs to run it - I found this "SQL Server Import and Export Wizard" tool. So - I ran it - and found that one of the Data Sources available to choose from is "Microsoft OLE DB Provider for Visual FoxPro". However, upon selecting that option and hitting Next - I get an error window "Feature is Not Available. (Microsoft OLE DB Provider for Visual FoxPro)

So - does that just mean that it's not available under the Express version? IF that IS the case - I find it quite annoying, since if the feature is supposed to be N/A - then it should NOT even allow you to select it to begin with!!!

Just to add - I saw the Properties button under the Data Source selection drop-down, but, I did NOT change any of those Properties.

Thanks,

-K-

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/DF1EEF11E586A64FB54A97F22A8BD0441D52D831FF@ACKBWDDQH1.artfact.local

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

Hey there Richard,

Thanks for the informative reply. Yes, Am now trying to use the Text

field for the Memo field conversions. As for HOW I am doing this - It's

the Import/Export Wizard that comes w/SQL Server.

The conversion is still giving me errors - including that Codepage

stuff. But, am still trying to work my way thru the errors...

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Richard Kaye

Sent: Tuesday, May 15, 2012 1:10 PM

To: profoxtech@leafe.com

Subject: RE: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data

Source]

SQL<->VFP

CHAR=CHARACTER (fixed width column definition)

VARCHAR=VARCHAR (variable width column definition although VFP fakes it)

TEXT=MEMO (unlimited width for most practical purposes)

VARCHAR and NVARCHAR can work in place of TEXT and are more efficient

in terms of storage in SQL land. You can also avoid certain limitations

of TEXT columns by using VARCHAR. For example, you can't use aggregate

functions on TEXT columns. NVARCHAR & NTEXT are used for storing binary

data and typically used when you want to store text in a

language/encoding scheme that is different from the encoding of the DB

itself, which is probably why your Russian friend recommended that.

Speaking of encoding, that's another thing that can bite you so you need

to be aware of potential differences in your source data and the

encoding of your SQL database.

I don't think you said how you were trying to move your data around -

SPT? Remote views? ODBC/OLE DB will attempt to do implicit conversions

of certain data types to other data types but it doesn't always get it

right and you can get the kind of message you reported. When that

conversion is not applicable, you will need to do your own conversions

using CAST or CONVERT.

--

rk

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 12:12 PM

To: profoxtech@leafe.com

Subject: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data

Source]

So, I'm now in the middle of trying to convert the VFP data to SQL - and

I get an error like this:

`po_lines` -> [dbo].[po_lines]:

- The data type could not be assigned to the column

"packslip_line_msg_line_no1" in "SQL Server Native Client 10.0".

- The data type could not be assigned to the column

"vendor_line_notes" in "SQL Server Native Client 10.0".

I know it's the issue w/Memo fields - since SQL doesn't have anything

like it. But, if I want to use something even CLOSE to a memo field

within SQL - what type of field should I use? I saw there is Char,

VarChar, Text and more - I just don't the actually differences between

them.

TIA,

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:20 AM

OK - my Bad!

Obviously I need to go into that Properties button & select the VFP DBC

- before I can hit Next. I made the wrong assumption before - figuring

that when I hit NEXT - it would THEN prompt me for the VFP DBC that I

wanted to Import.

Once I selected the VFP DBC from within the Properties button - I was

now able to successfully hit the Next button.

Just FYI - I've never really worked with SQL before - at least not the

extent of setting up an SQL Database or even installing SQL.

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:11 AM

In regards to my previous discussion on here - I just installed MS SQL

Server 2008 R2 Express. And, upon looking under programs to run it - I

found this "SQL Server Import and Export Wizard" tool. So - I ran it -

and found that one of the Data Sources available to choose from is

"Microsoft OLE DB Provider for Visual FoxPro". However, upon selecting

that option and hitting Next - I get an error window "Feature is Not

Available. (Microsoft OLE DB Provider for Visual FoxPro)

So - does that just mean that it's not available under the Express

version? IF that IS the case - I find it quite annoying, since if the

feature is supposed to be N/A - then it should NOT even allow you to

select it to begin with!!!

Just to add - I saw the Properties button under the Data Source

selection drop-down, but, I did NOT change any of those Properties.

Thanks,

-K-

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/289EA162F5642645B5CF64D624C66A140E260329@us-ny-mail-002.waitex.net

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

Although, in the end - I still had the warnings about the

DefaultCodePage stuff - I finally reached success! The data was finally

converted. Now I just need to see how the data looks in SQL - and if

anything looks fine - hopefully just have to tweak and re-convert a

couple tables instead of all of them.

Thank you ALL - as this ProFox list Rocks as usual!

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 1:22 PM

To: profoxtech@leafe.com

Subject: RE: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data

Source]

Hey there Richard,

Thanks for the informative reply. Yes, Am now trying to use the Text

field for the Memo field conversions. As for HOW I am doing this - It's

the Import/Export Wizard that comes w/SQL Server.

The conversion is still giving me errors - including that Codepage

stuff. But, am still trying to work my way thru the errors...

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Richard Kaye

Sent: Tuesday, May 15, 2012 1:10 PM

To: profoxtech@leafe.com

Subject: RE: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data

Source]

SQL<->VFP

CHAR=CHARACTER (fixed width column definition)

VARCHAR=VARCHAR (variable width column definition although VFP fakes it)

TEXT=MEMO (unlimited width for most practical purposes)

VARCHAR and NVARCHAR can work in place of TEXT and are more efficient

in terms of storage in SQL land. You can also avoid certain limitations

of TEXT columns by using VARCHAR. For example, you can't use aggregate

functions on TEXT columns. NVARCHAR & NTEXT are used for storing binary

data and typically used when you want to store text in a

language/encoding scheme that is different from the encoding of the DB

itself, which is probably why your Russian friend recommended that.

Speaking of encoding, that's another thing that can bite you so you need

to be aware of potential differences in your source data and the

encoding of your SQL database.

I don't think you said how you were trying to move your data around -

SPT? Remote views? ODBC/OLE DB will attempt to do implicit conversions

of certain data types to other data types but it doesn't always get it

right and you can get the kind of message you reported. When that

conversion is not applicable, you will need to do your own conversions

using CAST or CONVERT.

--

rk

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 12:12 PM

To: profoxtech@leafe.com

Subject: Memo Fields in SQL [was...RE: SQL Server Import & VFP Data

Source]

So, I'm now in the middle of trying to convert the VFP data to SQL - and

I get an error like this:

`po_lines` -> [dbo].[po_lines]:

- The data type could not be assigned to the column

"packslip_line_msg_line_no1" in "SQL Server Native Client 10.0".

- The data type could not be assigned to the column

"vendor_line_notes" in "SQL Server Native Client 10.0".

I know it's the issue w/Memo fields - since SQL doesn't have anything

like it. But, if I want to use something even CLOSE to a memo field

within SQL - what type of field should I use? I saw there is Char,

VarChar, Text and more - I just don't the actually differences between

them.

TIA,

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:20 AM

OK - my Bad!

Obviously I need to go into that Properties button & select the VFP DBC

- before I can hit Next. I made the wrong assumption before - figuring

that when I hit NEXT - it would THEN prompt me for the VFP DBC that I

wanted to Import.

Once I selected the VFP DBC from within the Properties button - I was

now able to successfully hit the Next button.

Just FYI - I've never really worked with SQL before - at least not the

extent of setting up an SQL Database or even installing SQL.

-K-

-----Original Message-----

From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

On Behalf Of Kurt Wendt

Sent: Tuesday, May 15, 2012 11:11 AM

In regards to my previous discussion on here - I just installed MS SQL

Server 2008 R2 Express. And, upon looking under programs to run it - I

found this "SQL Server Import and Export Wizard" tool. So - I ran it -

and found that one of the Data Sources available to choose from is

"Microsoft OLE DB Provider for Visual FoxPro". However, upon selecting

that option and hitting Next - I get an error window "Feature is Not

Available. (Microsoft OLE DB Provider for Visual FoxPro)

So - does that just mean that it's not available under the Express

version? IF that IS the case - I find it quite annoying, since if the

feature is supposed to be N/A - then it should NOT even allow you to

select it to begin with!!!

Just to add - I saw the Properties button under the Data Source

selection drop-down, but, I did NOT change any of those Properties.

Thanks,

-K-

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/289EA162F5642645B5CF64D624C66A140E26032A@us-ny-mail-002.waitex.net

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

At the risk of joining this thread a bit late, I would suggest you use

varchar(MAX) for memo fields, available only in SQL Server 2008. With

previous versions (ie 2005), use varchar(8000). When you convert data

from a VFP table, including a memo field, into a SQL Server table, use

this construct:

local nOK

nOK = 1

select vfptable

scan all while nOK > 0

scatter memvar memo fields except pkfield

cCmd = [insert into sqltable(field1,field2,....memofieldequivalent)]+;

[ values( m.field1,m.field2,....m.memofield)]

nOK = sqlexec( nHandle,cCmd)

endscan

if nOK < 0

*** branch to your error routine

endif

Exclude the pkfield (if there is one in your vfp table), because SQL

server generates its own (I assume you created a PK field in your sql

table, if you did not, do it, by all means!)

I read somewhere that Microsoft intended to get rid of the text type in

the near future, so try not to use it.

BTW, I do not use the built in import app from SQL or the upsize wizard

from VFP. I created my own routines to generate all tables, with their

indexes and constraints, from VFP to SQL Server. And also to pass data

from the VFP tables to the SQL Tables. In this manner, I control what is

going on at all times.

Rafael Copquin

El 15/05/2012 02:49 p.m., Kurt Wendt escribió:

> Although, in the end - I still had the warnings about the

> DefaultCodePage stuff - I finally reached success! The data was finally

> converted. Now I just need to see how the data looks in SQL - and if

> anything looks fine - hopefully just have to tweak and re-convert a

> couple tables instead of all of them.

>

> Thank you ALL - as this ProFox list Rocks as usual!

>

> -K-

>

> -----Original Message-----

> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

> On Behalf Of Kurt Wendt

> Sent: Tuesday, May 15, 2012 1:22 PM

> To: profoxtech@leafe.com

> Subject: RE: Memo Fields in SQL [was...RE: SQL Server Import& VFP Data

> Source]

>

> Hey there Richard,

>

> Thanks for the informative reply. Yes, Am now trying to use the Text

> field for the Memo field conversions. As for HOW I am doing this - It's

> the Import/Export Wizard that comes w/SQL Server.

>

> The conversion is still giving me errors - including that Codepage

> stuff. But, am still trying to work my way thru the errors...

>

> -K-

>

> -----Original Message-----

> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

> On Behalf Of Richard Kaye

> Sent: Tuesday, May 15, 2012 1:10 PM

> To: profoxtech@leafe.com

> Subject: RE: Memo Fields in SQL [was...RE: SQL Server Import& VFP Data

> Source]

>

> SQL<->VFP

> CHAR=CHARACTER (fixed width column definition)

> VARCHAR=VARCHAR (variable width column definition although VFP fakes it)

> TEXT=MEMO (unlimited width for most practical purposes)

>

> VARCHAR and NVARCHAR can work in place of TEXT and are more efficient

> in terms of storage in SQL land. You can also avoid certain limitations

> of TEXT columns by using VARCHAR. For example, you can't use aggregate

> functions on TEXT columns. NVARCHAR& NTEXT are used for storing binary

> data and typically used when you want to store text in a

> language/encoding scheme that is different from the encoding of the DB

> itself, which is probably why your Russian friend recommended that.

> Speaking of encoding, that's another thing that can bite you so you need

> to be aware of potential differences in your source data and the

> encoding of your SQL database.

>

> I don't think you said how you were trying to move your data around -

> SPT? Remote views? ODBC/OLE DB will attempt to do implicit conversions

> of certain data types to other data types but it doesn't always get it

> right and you can get the kind of message you reported. When that

> conversion is not applicable, you will need to do your own conversions

> using CAST or CONVERT.

>

> --

> rk

>

> -----Original Message-----

> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

> On Behalf Of Kurt Wendt

> Sent: Tuesday, May 15, 2012 12:12 PM

> To: profoxtech@leafe.com

> Subject: Memo Fields in SQL [was...RE: SQL Server Import& VFP Data

> Source]

>

> So, I'm now in the middle of trying to convert the VFP data to SQL - and

> I get an error like this:

> `po_lines` -> [dbo].[po_lines]:

>

> - The data type could not be assigned to the column

> "packslip_line_msg_line_no1" in "SQL Server Native Client 10.0".

> - The data type could not be assigned to the column

> "vendor_line_notes" in "SQL Server Native Client 10.0".

>

> I know it's the issue w/Memo fields - since SQL doesn't have anything

> like it. But, if I want to use something even CLOSE to a memo field

> within SQL - what type of field should I use? I saw there is Char,

> VarChar, Text and more - I just don't the actually differences between

> them.

>

> TIA,

> -K-

>

> -----Original Message-----

> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

> On Behalf Of Kurt Wendt

> Sent: Tuesday, May 15, 2012 11:20 AM

>

> OK - my Bad!

>

> Obviously I need to go into that Properties button& select the VFP DBC

> - before I can hit Next. I made the wrong assumption before - figuring

> that when I hit NEXT - it would THEN prompt me for the VFP DBC that I

> wanted to Import.

>

> Once I selected the VFP DBC from within the Properties button - I was

> now able to successfully hit the Next button.

>

> Just FYI - I've never really worked with SQL before - at least not the

> extent of setting up an SQL Database or even installing SQL.

>

> -K-

>

> -----Original Message-----

> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

> On Behalf Of Kurt Wendt

> Sent: Tuesday, May 15, 2012 11:11 AM

>

> In regards to my previous discussion on here - I just installed MS SQL

> Server 2008 R2 Express. And, upon looking under programs to run it - I

> found this "SQL Server Import and Export Wizard" tool. So - I ran it -

> and found that one of the Data Sources available to choose from is

> "Microsoft OLE DB Provider for Visual FoxPro". However, upon selecting

> that option and hitting Next - I get an error window "Feature is Not

> Available. (Microsoft OLE DB Provider for Visual FoxPro)

>

> So - does that just mean that it's not available under the Express

> version? IF that IS the case - I find it quite annoying, since if the

> feature is supposed to be N/A - then it should NOT even allow you to

> select it to begin with!!!

>

> Just to add - I saw the Properties button under the Data Source

> selection drop-down, but, I did NOT change any of those Properties.

>

> Thanks,

> -K-

>

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/4FB29F3C.4030802@fibertel.com.ar

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.

On Tue, May 15, 2012 at 1:23 PM, Rafael Copquin

<rcopquin@fibertel.com.ar> wrote:

> At the risk of joining this thread a bit late, I would suggest you use

> varchar(MAX) for memo fields, available only in SQL Server 2008. With

> previous versions (ie 2005), use varchar(8000). When you convert data

> from a VFP table, including a memo field, into a SQL Server table, use

> this construct:

In SQL Server do this first:

USE [master]

GO

ALTER DATABASE [Your DB name here] SET RECOVERY SIMPLE WITH NO_WAIT

GO

This will stop logging all inserts to the log file while you are doing this:

To reset logging

USE [master]

GO

ALTER DATABASE [Your DB name here] SET RECOVERY FULL WITH NO_WAIT

GO

> local nOK

> nOK = 1

> select vfptable

> scan all while nOK > 0

>     scatter memvar memo fields except pkfield

>     cCmd = [insert into sqltable(field1,field2,....memofieldequivalent)]+;

>                  [ values( m.field1,m.field2,....m.memofield)]

>    nOK = sqlexec( nHandle,cCmd)

> endscan

> if nOK < 0

>    *** branch to your error routine

> endif

>

> Exclude the pkfield (if there is one in your vfp table), because SQL

> server generates its own (I assume you created a PK field in your sql

> table, if you did not, do it, by all means!)

>

> I read somewhere that Microsoft intended to get rid of the text type in

> the near future, so try not to use it.

>

> BTW, I do not use the built in import app from SQL or the upsize wizard

> from VFP. I created my own routines to generate all tables, with their

> indexes and constraints, from VFP to SQL Server. And also to pass data

> from the VFP tables to the SQL Tables. In this manner, I control what is

> going on at all times.

>

> Rafael Copquin

>

> El 15/05/2012 02:49 p.m., Kurt Wendt escribió:

>> Although, in the end - I still had the warnings about the

>> DefaultCodePage stuff - I finally reached success! The data was finally

>> converted. Now I just need to see how the data looks in SQL - and if

>> anything looks fine - hopefully just have to tweak and re-convert a

>> couple tables instead of all of them.

>>

>> Thank you ALL - as this ProFox list Rocks as usual!

>>

>> -K-

>>

>> -----Original Message-----

>> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

>> On Behalf Of Kurt Wendt

>> Sent: Tuesday, May 15, 2012 1:22 PM

>> To: profoxtech@leafe.com

>> Subject: RE: Memo Fields in SQL [was...RE: SQL Server Import&  VFP Data

>> Source]

>>

>> Hey there Richard,

>>

>> Thanks for the informative reply. Yes, Am now trying to use the Text

>> field for the Memo field conversions. As for HOW I am doing this - It's

>> the Import/Export Wizard that comes w/SQL Server.

>>

>> The conversion is still giving me errors - including that Codepage

>> stuff. But, am still trying to work my way thru the errors...

>>

>> -K-

>>

>> -----Original Message-----

>> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

>> On Behalf Of Richard Kaye

>> Sent: Tuesday, May 15, 2012 1:10 PM

>> To: profoxtech@leafe.com

>> Subject: RE: Memo Fields in SQL [was...RE: SQL Server Import&  VFP Data

>> Source]

>>

>> SQL<->VFP

>> CHAR=CHARACTER (fixed width column definition)

>> VARCHAR=VARCHAR (variable width column definition although VFP fakes it)

>> TEXT=MEMO (unlimited width for most practical purposes)

>>

>> VARCHAR and NVARCHAR can work  in place of TEXT and are more efficient

>> in terms of storage in SQL land. You can also avoid certain limitations

>> of TEXT columns by using VARCHAR. For example, you can't use aggregate

>> functions on TEXT columns. NVARCHAR&  NTEXT are used for storing binary

>> data and typically used when you want to store text in a

>> language/encoding scheme that is different from the encoding of the DB

>> itself, which is probably why your Russian friend recommended that.

>> Speaking of encoding, that's another thing that can bite you so you need

>> to be aware of potential differences in your source data and the

>> encoding of your SQL database.

>>

>> I don't think you said how you were trying to move your data around -

>> SPT? Remote views? ODBC/OLE DB will attempt to do implicit conversions

>> of certain data types to other data types but it doesn't always get it

>> right and you can get the kind of message you reported. When that

>> conversion is not applicable, you will need to do your own conversions

>> using CAST or CONVERT.

>>

>> --

>> rk

>>

>> -----Original Message-----

>> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

>> On Behalf Of Kurt Wendt

>> Sent: Tuesday, May 15, 2012 12:12 PM

>> To: profoxtech@leafe.com

>> Subject: Memo Fields in SQL [was...RE: SQL Server Import&  VFP Data

>> Source]

>>

>> So, I'm now in the middle of trying to convert the VFP data to SQL - and

>> I get an error like this:

>> `po_lines` ->  [dbo].[po_lines]:

>>

>>             - The data type could not be assigned to the column

>> "packslip_line_msg_line_no1" in "SQL Server Native Client 10.0".

>>             - The data type could not be assigned to the column

>> "vendor_line_notes" in "SQL Server Native Client 10.0".

>>

>> I know it's the issue w/Memo fields - since SQL doesn't have anything

>> like it. But, if I want to use something even CLOSE to a memo field

>> within SQL - what type of field should I use? I saw there is Char,

>> VarChar, Text and more - I just don't the actually differences between

>> them.

>>

>> TIA,

>> -K-

>>

>> -----Original Message-----

>> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

>> On Behalf Of Kurt Wendt

>> Sent: Tuesday, May 15, 2012 11:20 AM

>>

>> OK - my Bad!

>>

>> Obviously I need to go into that Properties button&  select the VFP DBC

>> - before I can hit Next. I made the wrong assumption before - figuring

>> that when I hit NEXT - it would THEN prompt me for the VFP DBC that I

>> wanted to Import.

>>

>> Once I selected the VFP DBC from within the Properties button - I was

>> now able to successfully hit the Next button.

>>

>> Just FYI - I've never really worked with SQL before - at least not the

>> extent of setting up an SQL Database or even installing SQL.

>>

>> -K-

>>

>> -----Original Message-----

>> From: profoxtech-bounces@leafe.com [mailto:profoxtech-bounces@leafe.com]

>> On Behalf Of Kurt Wendt

>> Sent: Tuesday, May 15, 2012 11:11 AM

>>

>> In regards to my previous discussion on here - I just installed MS SQL

>> Server 2008 R2 Express. And, upon looking under programs to run it - I

>> found this "SQL Server Import and Export Wizard" tool. So - I ran it -

>> and found that one of the Data Sources available to choose from is

>> "Microsoft OLE DB Provider for Visual FoxPro". However, upon selecting

>> that option and hitting Next - I get an error window "Feature is Not

>> Available. (Microsoft OLE DB Provider for Visual FoxPro)

>>

>> So - does that just mean that it's not available under the Express

>> version? IF that IS the case - I find it quite annoying, since if the

>> feature is supposed to be N/A - then it should NOT even allow you to

>> select it to begin with!!!

>>

>> Just to add - I saw the Properties button under the Data Source

>> selection drop-down, but, I did NOT change any of those Properties.

>>

>> Thanks,

>>   -K-

>>

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/profox

OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/CAJidMYKqRnsGjY_AUrrc6mtCUes0s5R+vzbSxP-dXqPzELAA+w@mail.gmail.com

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.