Hello All,
Does anyone know how to convert data in a Memo field from Foxpro into a Text field in SQL table? Straight import via DTS package seems to entirely ignore this memo field and result in blank.
Thanks in advance for your reply.
Koann
I have an Access application with various DB's linked together. One of the DB's contains a field, SOURCECODE, which was mistakenly entered in as an nvarchar(4000) field in MS Sql Server. When I link the table, Access converts it to a memo field.
There will never be more than 10 chars in that field. The company that created the DB says its will be too risky to change the data type. I need to link this field to another field that's a VARCHAR.
How can I do this? Access doesn't allow the CAST feature.
Hi,
I've got about 6000 tables in foxpro 3.0 and a database in sql srv 2000.
now i've to convert the data of these 6000 tables into the database. the problem is that i have to convert all the tables seperatly!
is there a tool where i can convert these tables automatically?
i know it can with DTS, but the 6000 tables are divided over 1400 directories. So with DTS i can't convert them automatically.
does anyone know a solution?
Hi
I am trying to convert foxpro database table example mytable.dbf in to MSSQL Database table. I have created odbc connection to foxpro database, Then i have created an data set in which i have fill the mytable (stucture and data) by odbc adapter. Now i want to import this Data Set in to the sql database. My table (stucture and data) is in memory and my proble is that how i can write to sql data set or create data table which i can see in MSSQL Database.
Please Suggest
Thanks in Advance.
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View RelatedHi,
I'm importing an Access database to SQL Server 2000.
The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import?
I would appreciate any pointers.
Hi all,
i've a reasonable amount of experience with MS Access and less
experience with SQL Server. I've just written an .NET application that
uses an SQL Server database. I need to collate lots of data from around
the company in the simplest way, that can then be loaded into the SQL
Server database.
I decided to collect the info in Excel because that's what most people
know best and is the quickest to use. The idea being i could just copy
and paste the records directly into the SQL Server database table (in
the same format) using the SQL Server Management Studio, for
example.
Trouble is, i have a problem with line feed characters. If an Excel
cell contains a chunk of text with line breaks (Chr(10) or Chr(13))
then the copy'n'paste doesn't work - only the text up to the first line
break is pasted into the SQL Server database cell. The rest is not
pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a
memo field into SQL Server database, but with exactly the same problem.
I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases
interacting together, can someone suggest the simplest way of
transferring the data without getting this problem with the line feeds?
I don't want to spend hours writing scripts/programs when it's just
this linefeed problem that is preventing the whole lot just being
cut'n'pasted in 5 seconds!
cheers
Dominic
Hi,
Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.
Thanks.
I'm trying to create a site which allows me to add Memo type fields but when I insert or edit my record it will not take any of my text after an enter (vbNewLine).
In Access I used the field type "Memo" but I do not see that type in SQL Server 2005 just a nvarchar(max) which does not seam to work.
Thanks for the help!
Chad
Hey guys. I have a SQL query I'm trying to create. It's nice and dandy, but I have a memo field that's being trunicated to 256 characters.
SQL is something like this:
Code:
Select distinct `group`,sortorder,HideLabel,category,img,Description,Null as num,Null as subcat
from catalogimages
where Inact=0 and subcateg is NULL
group by `group`,sortorder,HideLabel,category,img,Description
order by `group`,sortorder,category
Description would be the memo field.
Is the 'GROUP BY' clause even necessary here?
I'm also willing to bet that the 'DISTINCT' clause might not be necessary.
Any help would be greatly appreciated.
Our company is migrating a Microsoft Access 2010 backend database to a SQL Server 2008 database. One of the memo fields in the Access backend can store up to 150 Kb of Unicode data. To store this data in SQL server, we found that we can use the following data types:
ntext = (2^30) - 1 = 1,073,741,823 bytes.
nvarchar(max) = (2^31) - 1 = 2,147,483,647 bytes.
Because ntext will be deprecated in future releases of SQL Server, the only good alternative to store an Access memo field in SQL server is to use nvarchar(max), which is what Microsoft recommends for large Unicode texts.Storing a large amount of text like 150 Kb in an nvarchar(max) field using only SQL server works as expected. However, if Access is used to store the data in a table linked to SQL server, the maximum number of characters allowed is only 4000. We found that this limitation is imposed by the ODBC driver that limits nvarchar(max) to 4000 characters.
The connection string we are currently using to link a table to SQL server is this:
ODBC;DRIVER={SQL Server Native Client 10.0};SERVER= SQLEXPRESS;DATABASE=TestDB;Trusted_Connection=No;UID=uid;PWD=pwd;
Any solution for this limitation storing large amounts of data in a Microsoft Access memo field mapped to an nvarchar(max) data field in a SQL Server database?
I'm having some problems importing data from a memo column (Access) into varchar column in SQL Server.
My idea was to use slowly changing dimesion to identify modified and new rows. No matter what data type I use to convert the memo column (using Data Conversion Transformation) and then using the converted column in SCD, I get the following error :
'The SCD transform does not allow mapping between columns of different types except for DT_STR and DT_WSTR.'
What do I have to do to get Memo column 'to behave' as a string?
Same problem with a different data type - decimal (18,5) in sql server - no matter what datatype I use in Data Conversion Trans, I get the same error trying to generate scd.
Thank you for your answers.
I have an application written in Access 97 that connects to a SQL2000backend. One field is a description field that is a data type NTEXT in theSQL database. In my access form, I can not enter more than 255 characters.Before I converted the backend to SQL, the description field was a memofield in Access.What do I need to do to make it so I can enter more text into this field?
View 1 Replies View RelatedI have used DTS in SQL Server 2000 to import an MDB filed (MS ACCESS) of a table. When the table is imported the primary key is lost and the memo field data is completely gone.
I use the tranformation option in the DTS wizard to add the primary key and make sure the data type for the memo field is varchar and has a size of 8000. I need that large size since I am storing lots of html code.
When I preview the data I see the html code that is supposed to get imported. However, when I return all rows from the table in Enterprise Manager the field is empty.
So I tried to manually copy the data from the MS Access Database into SQL Server. Could not figure out if SQL Server has an interface like MS Access to simply copy data into a table. So I linked to the tables from MS Access to the SQL Server table.
When I opened the linked table I see the data in the description field. However, if I return the rows from within SQL Server no data is present.
I have some ASP code trying to read the data in the SQL Server table. However, nothing is returned and when I run the SQL Statement, nothing gets returned. The SQL statement returns all rows. All the other data is present but nothing in the description field.
What am I doing wrong? Any suggestions anyone, please!
TIA
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
Hello,
I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value.
How can I remove all speical characters and then convert that field to an int?
Also I tried the following query which did not work as well, same error about conversion.
UPDATE myTable SET field = CAST(field AS int)
Does anyone know how I can, using SQL and/or T-SQL syntax (although I could do a CLR in C# too..), export a SQL table from SQL Server 2005 to a new Visual FoxPro 9 DBF (table)? The DBF would need to be created on the fly too because this will have to basically be able to export any SQL table...
Thanks ahead of time for any and all assistance...
Hi,
I need to convert a var char field to date field (DD/MM/YYYY)
Current convertion format:
CAST(Report_Date as DATETIME)
How can i convert to Date field with date format of DD/MM/YYYY?
Thanks.
Onn Onn
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
I know that the DTS Wizard is supposed to be able to handle heterognous
data imports but I can't get it to work with a free FoxPro table. I have to export to a text file and then import from the text file and spend an hour renaming columns and farting around with datatypes.
CAN I GET A FOXPRO TABLE INTO SQL SERVER DIRECTLY OR NOT?
HOW DO I DO IT?
I don't fully understand some of the questions the DTS Wizard is asking. Can anybody give me a blow by blow account before I ring the Samaritans?
Thank you and Happy Christmas - it may be my last if I can't speed up these imports. Either I'll jump under a bus or my boss will make mince pies out of me.
Thanks
Mark
Using the Import/Export Data Wizard, I'm trying to export a FoxPro 2.5 DOS (as dBase III) table of 15,000 records to SQL Server 2000. I keep getting this error message:
Insert Error, Column 32 ('PROG_START',DBTYPE_DBTIMESTAMP), Status 6: Data Overflow.
Invalid character value for cast specification.
I have SQL Server create the table each time I run the wizard. The new table allows NULLS in this column and I made sure to overwrite the empty date fields in the FoxPro table with blanks to make sure it would result in NULL. Originally SQL Server tried to put this as SMALL DATETIME, but when I got the message earlier, I changed it to DATETIME.
Any suggestions?
I'm running SQL Server 2008 (x64) version.
How can i create a linked server for Microsoft Visual Foxpro databases ?
I'm using Microsoft.ACE.OLEDB.12.0 driver.
I success create a linked server, and can browse all tables from linked server connections, but why when query data using
SELECT * FROM OPENQUERY(PIP_TEST,'select * from tbctrl')
It getting error message
Cannot process the object "select * from tbctrl". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "pip_test" indicates that either the object has no columns or the current user does not have permissions on that object.
Hi guys,
I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE
JurnalTransMoves.SUF
SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752
10
60
5752
20
5752
30
5753
400
3000
5753
500
5753
600
5753
700
5753
800
5754
7
15
5754
8
Is there any chance to do that?
Thanks in advance,
Aldo.
Code Snippet
SELECT
Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',
Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',
Accounts.FILTER AS 'ACCOUNTS.FILTER',
Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',
AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',
Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',
CreditTerms.DETAILS AS 'CreditTerms.DETAILS'
CreditTerms.CURRENF AS 'CreditTerms.CURRENF'
CreditTerms.MONTH AS 'CreditTerms.MONTH',
CreditTerms.DAYS AS 'CreditTerms.DAYS',
CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',
CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE
WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'
WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'
ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE
WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE
ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',
JurnalTrans.REF2 AS 'JurnalTrans.REF2',
JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',
JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF',
JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM
JURNALTRANSMOVES AS JurnalTransMoves_1
INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves
INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID
INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID
INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY
LEFT OUTER JOIN ITEMS AS Items
INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY
INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE
LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '3001' And '3020'
AND Accounts.ACCOUNTKEY IN ('123456')
ORDER BY Accounts.ACCOUNTKEY
Hi,
I'm not entirely sure that this is the correct forum for this question but it relates to my SSIS package.
I am currently implementing an SSIS package to replace an existing stored procedure which is getting very unmanageable. I have come across a part in the stored procedure which performs a convert(datetime, @Parameter3, 14) on a string of data. The string value of @Parameter3 is in the following format HH:mm:ss.
The problem i am having is how to implement similar functionality in a script task. everything i have tried involving the datetime object returns 1,1,0001, 00:00:00 or similar. It never seems to get the time so that it can be passed to a datetime field in the database.
Thanks in advance for any help that is provided.
Grant
Hi,
I have source table , fact table and four dim. tables , I have to update a field in fact table from source table.
How can I do it?
thanks...
Hi,
I am getting a date string that is contained in a character field (char(20)).
An example of the data is as follows:
2005-09-20121315001
it is in the format YYYY-MM-DDHHMMSSMMM.
I want to insert this value into a datetime field. I have used convert, but can only insert it when I split out the time portion and separate them with a colon
i.e. to get it to insert into a datetime field the data has to be in the following format
2005-09-20 13:15:18.001
YYYY-MM-DD HH:MM:SS.MMM
Is there a style parameter in the convert function that will allow me to insert this value without having to separate the hours/minutes/seconds with a colon? I have tried BOL but it seems that all the examples have colons in them where there is a time portion to the date,
Thanks in advance
I have one table and this field is character field with save data in below.
Bonus_table->bonus_amt_field. Char(20)
======================================
Record information
0
Null
Blank
3
4
Null
Blank
if i want to convert this character field => change to numeric field to display ,how to handle "Blank" and "null" record?
The result expect:
0
0
0
3
4
0
0
I try this query but wrong message :
select cast(convert(numeric,3)bonus_amt) as bonus_amt
from test
Hi everyone.
So as the subject says, I have a few fields that are nvarchar but hold date information. Most of these fields I have been able to move to datetime easiliy enough, simply by going into edit mode for the table and converting the fields to datetime. But 1 field is giving me problems I keep getting this error.
quote:- Unable to modify table.
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
I really dont know why I'm getting this error, but I"m assuming it may have to be something like one of the records may not be in date format. But I don't know if this is the case and I don't know how to locate where my problem is coming from.
Any guidance is greatly appreciated.
Thanks.
Hi experts;
How can I generate the result using typical SQL statement based on the following tables?
Table a - Salesman (salesId, Name) pk : salesId
Table b - Invoice(InvoiceNo, salesId, InvoiceAmt) pk : invoiceNo fk : salesId -> Table a
The result set :
salesId, Name, sum(InvoiceAmt), InvoiceNos with comma separator
For example:
Table a
SalesId Name
S001 Peter
S002 Alice
Table b
InvoiceNo SalesId InvoiceAmt
INV001 S001 $100
INV002 S001 $100
INV003 S001 $400
INV004 S002 $200
Result set
SalesId Name Sum(InvoiceAmt) InvoiceNos
S001 Peter $600 INV001, INV002, INV003
S002 Alice $200 INV004
Thanks
how i convert varchar sal field to numeric in query
select sum(sal) from emp1
error:the sum or average aggregate operation cannot take a varchar data type as an argument.
I have a text file like this :
K045|13/03/1997
When I upload this text file to SQL Server 6.5, with the following fields :
Code Chraracter 4
Tdate Smalldatetime
I use BCP to upload this text file to SQL tables, but I got a wrong date,
like :
Text file :
K045|13/03/1997
SQL Tables data :
K045 Nov 19 1935 7:00PM
Can anyone on the net can help me ?
Thank's
I am struggling getting a time field from as/400 into SQL 200 using SSIS. The time field for some reason is being seen as dt_I8, And every type of conversion I try to do with it, it gives me an error about truncation. Example of my time field 12.35.00
How do you convert time field from as/400 which sql is seeing as DT_I8 to a string so that I can combine it with a date field to put it into a database timestamp field.
Stacy