This is my very first post to this forum. How to use the INSERT INTO... SELECT FROM query to export MS SQL 2000 data into FoxPro 2.6 DBF file? I want to write a VB 6.0 program, and already connected to MS SQL 2000 Database by MS SQL ODBC connection. Now I want to Export this MS SQL data to FoxPro 2.6 DBF. I found ROWSET option, but don't know how to use it for FoxPro. Please help me.
my question is : how do I query data from the linked foxpro database?
more: I have linked a visual foxpro server to my sql server database by using the addserver clause. Two server is in a local network. My linked foxpro server named 'fox'. its datasourse is not a dbc file, but a directory of dbf files ,and its full path is d:foxpro object. In the directory ,there are three table,'show2003.dbf','sysu.dbf','szszj.dbf'. And the .dbf files are not in any database. They are just three files in the same directory. There is no dbc file. Now I can see the table list on the right page of the linked server. But there is something wrong with my sql clause.
sample:
select * from fox..sysu
then the message is: server: message 7313,level 16,status 1,row 1 the appointed constructure or directory to the provider is inefficacious 'MSDASQL'
I know I may use 'openquery',or 'openrowset'. The problem is that variable is not valid in 'openquery' and 'openrowset'. But I must use variable. so ,please give me some advice.
i have a old database in foxpro and it has to be converted to sql server 6.5 database . the table in the foxpro has been broken into more than 1 tables in the sql server . so how can i transfer the data from 1 foxpro table to different tables in sql server 6.5. vineet
i have a linked server that i use a variable to get the last invoice date and only pull in the latest records that are after the last invoice date but i dont know how to insert that into a table?
DECLARE @TSQL varchar(MAX), @LastDate As Datetime, @LastRunDate As Varchar(6) SET @LastDate = (SELECT MAX(OrderDate) FROM [SYNC_REPORT].[DPY_SyncReport_Prod].[Stage].[Sales]) Set @LastRunDate = ( select right(convert(char(4), year(@LastDate)), 2) + right('000' + convert(varchar(3), datediff(dd, convert(datetime, '01/01/' + convert(char(4), year(@LastDate))), @LastDate+1)), 3) +100000 )
[code]....
but i get msdtc on server xxxxx is unavaiable?how to get my linked server query into my table
I have a recordset in my VB6 app that was created using ODBC and I would like to insert the data into a SQL db table. I already have the target Db & table setup. What is the fastest way to accomplish the data transfer? Can I do a Select into using the ODBC recordset as my source?Thanks
In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.
In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?
I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.
We are running SQL Server 2000, SP4. I recently noticed that my DTS packages were missing from the local server. Since I had saved them as Structure Storage Files, I imported them back into DTS Local Packages. That was yesterday. Today I opened the Meta Data browser to see what was in it. (We don't use Meta Data Services.) It displayed a message:"An error occurred while trying to access the database information. The msdb database could not be opened." These (restore of DTS pkgs and Meta Data error) may not be related, but I need to know why I am getting this error, because I am about to upgrade to SQL Server 2005 for Workgroups.
1. Does anyone know what causes this error message?
2. Are local DTS packages stored in the MSDB database?
Post installation of SQL Server 2014 Express edition, I am able to connect to the Database Instance.
But while opening a new query window in SSMS or opening a table getting the error:
Package 'RadLangSvc.Package, RadLangSvc, Version 12.0.0.0, Culture=Neutral, Public Token=89845dcd8080cc91' failed to load
Object reference not set to an instance of an object. (mscorlib)..Have already tried installing the componentsDACProjectSystemSetup_enu.msi, TSql LanguageService_enu.msi, DACFramework_enu.msi from path VS 2010 WCU DAC.
I get the below error only when my IDE open. It connects well when it is found closed. [SqlException (0x80131904): Cannot open user default database. Login failed.Login failed for user 'JPASPNET'.] I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab But I wish someone could give me the proper solution. I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool. Environment: XP pro, VWD and SQL Express
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:
I cannot open now my database after changing it name from the folder where it is save and also changed back to its original name.Message recovery pending now is appearing as shown below.Any chance that I could recover my database back?
I like to know if anyone of you out there can help me solve this issue. I need to generate an SQL Query to access two different FOXPro (databases) connections.
Example, Connection 1 - has a DSN setup of CMSBM and it sits in the path of D:/CMS/CMSBM/= and the table is bmboml15 Connection 2 - has a DSN setup of CMSMA and it sits in the path of D:/CMS/CMSMA/= and the table is maprodl15
I am coding in ASP.NET using C#. I have tried the command like:
SELECT codeno, link, type FROM bmboml15 IN CMSBM INNER JOIN maprodl15 IN CMSMA ON bmboml15.codeno = maprodl15.codeno.
Obviously the above query is not a working one and a very poor construction.
I'm having problem with an OpenQuery statement in stored procedure, which should be run on FoxPro linked server. I'm getting either an error messages or not the result I expected. I know that the problem is with WHERE part, without this part it works.
Here is the code: ------------------------------------- DECLARE @LastDate datetime SELECT @LastDate = MAX(DateChaged) FROM tblPersonel WHERE ACTIVE IN( 'A', 'T')
1. I tried: SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=''+@LastDate+''')
This line gives me an error msg:
Could not execute query against OLE DB provider 'MSDASQL'. [OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.]
2. I tried to use CTOD() - FOXPRO function to convert character to date.
SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=CTOD(''+@LastDate+'')')
-this doesn't give any error, but doesn't limit the result set as it should.
i have an old database in foxpro. The table in foxpro now has been broken into more than tables in sql server 6.5 . how do i append the data to sql server database to the respective tables from the foxpro database. vineet
I have worked soley with Sybase for almost ten years! Now I have been tasked with converting a bunch of data currently in a FoxPro database to a Microsoft SQL Server database. Short of writing some routines myself (which I don't mind doing), is there a shortcut for doing this? Any specifics would be great as it seems this could be mind boggling as far as dates go and so many other things!
I have a report that accessed a Visual FoxPro 6.0 database via ODBC. Since I upgraded to Visual FoxPro 9.0, now I am getting the error:
Query Server Error DMS-E-RBI_TABLE The table or view <table name> was not found in the dictionary
I've verified that the right path is setup and I've tried installing about every driver and none of them are working. I created a new database in 9.0 and the I can access it fine. Also, there are old tables that are accessible in the same path, just not the ones I need. Any ideas would be helpful..
I am using a lookup component in a SSIS data flow. The lookup is a select to a foxpro table. THe lookup works fine with full cache selected. I cannot get the lookup to work with a partial or no cache. I have the latest Foxpro OLE DB driver installed which I understand to support paramaterized queries. Has anyone had success with using cached lookup to Foxpro? Does anyone know how to set the lookup properties of sqlcommand and sqlcommandparam? I am unable to find any examples in BOL or on the web.
Here are some details. IF I go with "use a table or a view" option with the default cache query I get initialization errors
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E14 Description: "Command contains unrecognized phrase/keyword.".
In the advanced editor I see
SQLCommand set to
"select * from `kcf`"
and SQLCommandParam set to
"select * from (select * from `kcf`) as refTable where [refTable].[patkey] = ? and [refTable].[dayof_stay] = ? and [refTable].[modifier] = ? and [refTable].[kcf_code] = ? and [refTable].[source] = ? and [refTable].[kcf_time] = ?"
I believe the above error is because Foxpro V7 does not support the inner subselect . In addition the query contains CRLF without a continuation character (";").
If I remove the CRLF in the sqlcommandparam query, using the advanced editor, I get this design time error "OLE D error occurred while loading column metadata. Check the sqlcommand and sqlcommandparam properties". The designer requires both properties to be set, its unclear to me how the interact.
I cannot find any examples in BOL or on the web on how to set these 2 properties. Can someone give me a few guidelines?
I can get past the design errors by changing sqlcommandparam to a plain select that is VFP 7 compatible ( I removed the subselect and the square brackets):
select * from kcf as refTable where refTable.patkey = ? and refTable.dayof_stay = ? and refTable.modifier = ? and refTable.kcf_code = ? and refTable.source = ? and refTable.kcf_time = ?
But then I get a runtime error
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E46. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E46 Description: "One or more accessor flags were invalid.".
[lkp_lab_worst_value [6170]] Error: OLE DB error occurred while binding parameters. Check SQLCommand and SqlCommandParam properties.
I'm working on a report to show financial transactions from a table over a certain period. For most transactions there is a PDF document that is stored in a separate table in a binairy format. In my report I would like to include a link on every line with transaction information in the report that opens the PDF that is linked to that transaction. Just to be clear, I don't want to embed the PDF in the report but I want the users of the report to have the option to view the PDF that is related to that transaction in their standard pdf reader (adobe).
Code to do the following:
Once a user clicks on the link to view the PDF I need the code to get the binairy data of the PDF file from the table, convert it back to a PDF and open it in the default pdf reader (for example adobe reader). If it can't directly open the file then it's maybe possible to activate the 'open or download' pop up that you also get when you download something from a website.
After installing sql2005 sp2 a simple select query to a linked server reports the following error message:
Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded. Before installing SP2 we used sql2005 without any service packs, the linked server worked fine.
The linked server is a Visual FoxPro database.
After uninstalling and installing the 'Microsoft OLE DB Provider for Visual FoxPro 9.0' the issue stil remains.
Since my foxpro OLE driver has been rendered useless by service pack 1 for sql server 2005 I am forced to use the .net data provider for odbc.
I am importing a number of tables.. each time I add the DataReader Source to the dataflow and connected it to the OLE DB Destination I get a load of the good old "cannot convert between unicode and non-unicode string data types" errors...
So I'm having to do derived column transforms, for each and every column that it coughs up on.
Using expressions like (DT_STR,30,1252)receivedby to convert the "recievedby" column to a DT_STR,
Some of these tables have 100 string columns.. so I'm getting a bit sick of the drudgery of adding all these derivations...
Is there any way to tell this provider to stop deciding that the strings in the foxpro tables are unicode?
Is it possible to use SSIS to synchronize the data between a Foxpro .dbf and a compatible SQL Server table on a near realtime basis?
I have succesfully created an SSIS package that will insert data into the SQL Server Table but this is only useful for migrating data. What I need is a way to insure that the data in the SQL Server table matches that in the .dbf on a near realtime basis.
Or is there a way to link from SQL Server to the .dbf (similar to an Oracle DBLink).
I am trying load data from multiple Foxpro tables which are under a folder. I can have multiple folders with 17 foxpro tables. I was able to do it in DTS using ActiveX script. Here is the ACtiveX script.
'********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Option Explicit Dim conObj,DSNGosfbill,comObj,objRs,HostServer Dim sFolder,sFileFolder, Details,subFolderoccur,sFileFolderDBF,sFileFolderFPT,CheckFile,dFiles,Fil Dim fso, folderObj,subFolderList,dFolderObj Dim objPackage,oStep,objPackage_1,oStep_1,ConnObj_001,ConnObj_004,ConnObj_031,ConnObj_032,ConnObj_033 Dim ConnObj_Hclaimb, ConnObj_HProv, ConnObj_Hids, ConnObj_HCodes, ConnObj_HSpan, ConnObj_002, ConnObj_HCHGB Set conObj = CreateObject("ADODB.Connection") HostServer =DTSGlobalVariables("gvServer").Value
set comObj=CreateObject ("adodb.command") set comObj.ActiveConnection =conObj Function Main() Dim Dir_Name,DirFlag Dir_Name = "" DirFlag = "N" Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(checkFile) Then Else Details = "***** Success.Lst file is missing in Batch folder. BATCH job may not be successfull or there are no folders in UNZIP directory to process. Check the batch run.*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Set folderObj = fso.GetFolder(sFolder) Set subFolderList = folderObj.SubFolders For Each subFolderOccur in subFolderList DirFlag = "Y" Dir_Name = subFolderOccur.Name Call Process_Dir(1,subFolderOccur.Name) Next If DirFlag = "N" Then Details = "***** No directories to process in SSI UNZIP folder*****" Call Write_Log End If If DirFlag = "Y" Then Call Process_Dir(2,Dir_Name) If objRs.Eof Then Details = "***** No directories to process in SSI UNZIP folder*****" Call Write_Log End If While not objRs.EOF set sFileFolder = fso.GetFolder(sFolder & objRs("zip_file_name")) Details = "***** Start-Time " & sFileFolder & " " & Date & " " & Time & "*****" Call Write_Log Call Update_Process_Flag("L",objRs("zip_file_name")) '*******Execute the package for each directory****************' '********* Call the Package**************' Set objPackage = CreateObject("DTS.Package") Set objPackage_1 = CreateObject("DTS.Package")
Set ConnObj_001 = objPackage.Connections("SSIPATH001") ConnObj_001.DataSource = sFileFolder
Set ConnObj_002 = objPackage.Connections("SSIPATH002") ConnObj_002.DataSource = sFileFolder
Set ConnObj_004 = objPackage.Connections("SSIPATH004") ConnObj_004.DataSource = sFileFolder Set ConnObj_031 = objPackage.Connections("SSIPATH031") ConnObj_031.DataSource = sFileFolder Set ConnObj_032 = objPackage.Connections("SSIPATH032") ConnObj_032.DataSource = sFileFolder Set ConnObj_033 = objPackage.Connections("SSIPATH033") ConnObj_033.DataSource = sFileFolder
Set ConnObj_Hclaimb = objPackage.Connections("SSIPATHCLAIMB") ConnObj_Hclaimb.DataSource = sFileFolder
Set ConnObj_HProv = objPackage.Connections("SSIPATHPROV") ConnObj_HProv.DataSource = sFileFolder Set ConnObj_Hids = objPackage.Connections("SSIPATHHIDS") ConnObj_Hids.DataSource = sFileFolder Set ConnObj_HCodes = objPackage.Connections("SSIPATHCODES") ConnObj_HCodes.DataSource = sFileFolder Set ConnObj_HSpan = objPackage.Connections("SSIPATHSPAN") ConnObj_HSpan.DataSource = sFileFolder
Set ConnObj_HCHGB = objPackage.Connections("SSIPATHCHGB") ConnObj_HCHGB.DataSource = sFileFolder
objPackage.Execute For Each oStep In objPackage.Steps If oStep.ExecutionResult = DTSStepExecResult_Failure Then Details = "***** GOSFBILL_SSI_Staging_Load failed. " & Date & " " & Time & "*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Next
For Each oStep_1 In objPackage_1.Steps If oStep_1.ExecutionResult = DTSStepExecResult_Failure Then
Details = "***** GOSFBILL_SSI_Update_FileSource failed. " & Date & " " & Time & "*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Next
'********************************************' Details = "***** End-Time " & sFileFolder & " " & Date & " " & Time & "*****" Call Write_Log objPackage.Uninitialize objPackage_1.Uninitialize Set objPackage = Nothing Set objPackage_1 = Nothing sFileFolder = "" sFileFolderDBF = "" sFileFolderFPT = "" objRs.MoveNext Wend objRs.Close End If Call Close_Conn Main = DTSTaskExecResult_Success End Function Sub Process_Dir (Para_cntl,Dir_Name) comObj.CommandText ="dbo.Usp_Process_Dir" comObj.commandtype = 4 comobj.parameters.Refresh comobj.parameters("@Para_Cntl")= para_cntl comobj.parameters("@Dir_Nm")= Dir_Name comobj.parameters("@File_Type")= "SSI" If (Para_Cntl = 1)Then comObj.Execute() Else If Para_Cntl = 2 Then Set objRs = comObj.Execute() End If End If
End Sub Sub Update_Process_Flag(P_Flag,Dir_Name) comObj.CommandText ="dbo.Usp_Process_Flag" comObj.commandtype = 4 comObj.parameters.Refresh comObj.parameters("@Process_Flag")= P_Flag comobj.parameters("@Dir_Nm")= Dir_Name comObj.Execute() End Sub Sub Write_Log comObj.CommandText ="dbo.usp_etl_write_log" comObj.commandtype = 4 comobj.parameters.Refresh comobj.parameters("@Text")= Details Comobj.parameters("@NDC_SSI_IND")= "SSI" Comobj.parameters("@Process_Stage")= "Staging" comObj.Execute() End Sub
Sub Close_Conn Set comObj = Nothing Set objRs = Nothing conObj.Close Set conObj = Nothing Set fso = Nothing Set folderObj = Nothing Set subFolderList = Nothing End Sub
When I migrated this code to SSIS, its not working. How can I achive this functionality in SSIS. Any one pls help me.
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables. 2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables #3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1 2nd choice: Change step 1 to use #3 instead of #1 3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Hi all,I am fairly new to using triggers and was seeking some help from thosethat have experience with them. I am looking to transfer data from aSQL 2000 database to a Visual FoxPro database on another computer. Iwould like to transfer about three fields of data to a VFP table eachtime an insert is made on the SQL table. I am some what familiar withthe structure of creating the trigger but here is what I would likehelp with: Selecting the SQL data to transfer, Connecting to VFPdatabase, Insert SQL data into VFP table.CREATE TRIGGER [xyz] ON [dbo].[AAA]FOR INSERT??? Select a,b,c from SQL table??? Connect to VFP Database and Table??? Insert into VFP table Values a,b,cAny information, tips, or even an example Trigger procedure would helpand be greatly appreciated.Thank you,Brett
The DTS works perfectly when I run it manually. However, when I run itas a job it fails. Before you ask if i'm running it under differentsecurity context. I have already made sure of that. I was logged intothe server through remote viewer, when I created and ran the package,as well as scheduling the job. So the accounts they're running underare consistent. They're the same accounts as the SQL Agent is runningunder and it's the sys admin account.The data source is a Fox pro database with a pull of two tables. I amusing Microsoft OLE DB Foxpro driver as my source connection and OLE DBConnection for SQL Server as my destination. I am doing a simple tableto table transformation. The path of my connection is a mapped Drive:E:Main. There are other packages and jobs within my job queue that arepointing to the same database and they seem to run fine using the abovemapped drive. The ONLY difference between this package and otherpackages are that, they're few months old and this one was created lastnight. I have also enabled logging on this package and here is thebelow error when the job fails:Package Steps execution information:Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft OLE DB Provider for Visual FoxProStep Error Description:Invalid path or file name.Step Error code: 80040E21Step Error Help File:Step Error Help Context ID:0Step Execution Started: 9/23/2006 11:39:17 AMStep Execution Completed: 9/23/2006 11:39:17 AMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0