Converting A FoxPro Procedure To SQL
Oct 10, 2007
I have a standard adjacency matrix recursive table.
XOrderChildParent
1100NULL
2101100
3102100
4103100
5104102
6105102
7106NULL
8107106
The problem is the rows are in a random order and XOrder is not populated. I need to populate XOrder such that when the table is ordered by XOrder it will be in the order above.
I originally did this in FoxPro. Being able to SCAN through records in a table made it easy. SQL however is a different story. What I did in Fox was
1. Indexed on XOrder.
2. Set XOrder to 1 for all Parent = NULL.
3. This placed all the parents at the top of the file.
4. This is where Fox has a SCAN command that would sequentially step through the file from top to bottom. Starting from the top of the file…
5. Find the children of this row.
6. Set their XOrder to the current XOrder+.5. This placed the children under the parent.
7. Sequentially number all remaining records starting from the XOrder of the current record.
8. Now the current record and the record immediately below it have the correct XOrder.
9. Next SKIP to the next record
10. Replete from step 5. This would be the end of the Fox SCAN loop and would automatically repeat until it reached the end of the table then exited the loop.
This will correctly set XOrder for the entire table.
The problem is there is no SCAN or SKIP in SQL. How would I accomplish this in SQL?
View 9 Replies
ADVERTISEMENT
Mar 10, 2005
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
View 2 Replies
View Related
Jun 5, 2005
CREATE PROCEDURE procCreateBasket
@ShopperID int,
@BasketID int OUTPUT
AS
INSERT INTO Basket(ShopperID)
VALUES (@ShopperID)
SELECT @BasketID = @@IDENTITYI don't want to use this as a stored procedure I want to convert it as a string an use in that way in my asp.net application how can I do that?
View 2 Replies
View Related
Sep 2, 2005
I have the following code in C# that I would like to port to a Stored Procedure.
The problem is it's a query built on many factors.
Here's the code:
Code:
DataAccess.Connection conList = new Connection();
string strWhere = "";
string strSQL = "";
if (PackageID > 0)
{
//strWhere += " AND EAItems.PackageID = " + PackageID;
strWhere += " AND EAPackageItems.PackageID = " + PackageID;
}
if (From != DateTime.MinValue)
{
strWhere += " AND LogItem.DateCreated >= " + From;
}
strWhere += " AND LogItem.DateCreated <= " + To;
if (!Closed)
{
strWhere += " AND EAItems.Closed = " + Closed;
}
if (Search != null && Search.Length > 0)
{
strWhere += " AND (EAItems.Number LIKE " + "%" + Search + "%" + " OR EAItems.Description LIKE " + "%" + Search + "%" + ")";
}
strSQL = "Big SQL Statement with 5 inner joins " + strWhere + " ORDER BY Number";
return conList.GetDataTable(strSQL);
View 3 Replies
View Related
Feb 10, 2008
Hi i have the following method in my page, it works fine and everything, but i am trying to modify it so that it can take a stored procedure;
protected void Button1_Click(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);String sql = "SELECT userID, userName FROM users WHERE userName LIKE " + "'" + userName.Text + "%' OR organisation= " + "'" + OrganisationList.SelectedValue + "'";
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();
}
My question is what should my stored procedure be, and also how will i now structure the above method.
View 5 Replies
View Related
Sep 14, 2006
Hi All
Can any one help me in converting this from WHICH IN ORACLE to MS SQL SERVER
sqlplus -s $UserId/$PassWord@$DataBase <<EOSQL> $LogFile (This is the Connection String)
set serveroutput on
Declare
tempCnt Number:=0;
totDelCnt Number:=0;
Begin
Loop
$DelStmt
tempCnt := tempCnt+ SQL%ROWCOUNT ;
totDelcnt := totDelCnt+ SQL%ROWCOUNT ;
If SQL%NOTFOUND Then
Exit;
End if;
If tempCnt >= 50000 Then
Commit ;
tempCnt:=0 ;
End if ;
End Loop;
Commit ;
dbms_output.put_line('No of Recs Deleted From $TableName: '|| totDelcnt);
End;
/
exit
EOSQL
View 9 Replies
View Related
Aug 6, 2006
I need to link together string (NT-) and integer variable (1). The value of variable2 should be NT-1. I always got such an error message:
Conversion failed when converting the varchar value 'NT-1' to data type int.
DECLARE @variable1 int;
SET @variable1 = 1;
DECLARE @variable2 varchar(10);
SET @variable2 = CONVERT(varchar, 'NT-1')+ CONVERT(varchar, @variable1);
View 1 Replies
View Related
Nov 9, 2001
I am switching my database from MS access to SQL server, and i want the following query to br converted to SQL stored procedure
CREATE PROCEDURE FORUM_MESSAGE AS
SELECT *
FROM FORUM_MESSAGES
WHERE ID=MessageID;
here "MessageID" is a run time generated parameter, and is not a field in the database.
thanx
View 1 Replies
View Related
Aug 19, 2004
I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.
But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.
Any idea how this is done?
View 1 Replies
View Related
Aug 5, 2014
I have a rather strange requirement where I need to convert my stored procedure into a normal SQL query for generating a SSRS report.
The business is very specific not to use SP or temp table in the query for the report generation.
I have a stored proc where I have used temp table and I have inserted values into that table.
what all these things I need to take when I convert it to as a SQL query for report generation?
View 9 Replies
View Related
Aug 5, 2015
i have table scripts , i want to create all the tables by passing database name as variable using stored procedure in sql.
View 4 Replies
View Related
Jul 31, 2006
HI Experts...
I am using SQL SERVER 2005 standard edition
I have encountered a problem regarding converting date data type in stored procedure
As i was having problem taking date as input parameter in my stored procedure, so, then I changed to varchar (16) i.e.
CREATE PROCEDURE sp_CalendarCreate
@StDate VARCHAR(16) ,
@EDate VARCHAR(16),
then I am converting varchar to date with following code
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
SELECT @STARTDATE = CAST(@STDATE AS DATETIME)
SELECT @ENDDATE = CAST(@EDATE AS DATETIME)
When I try to execute the procedure with following code
execute sp_CalendarCreate @stdate='12-1-06',@edate='20-1-06'
but it gives me following error
Msg 242, Level 16, State 3, Procedure sp_CalendarCreate, Line 45
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can any one tell me the solution of that problem (at ur earliest)
regards,
Anas
View 5 Replies
View Related
May 30, 2015
I have a stored proc that is being executed from an OLE DB Source component in my Data Flow. Takes one input parm, has several variables declared within, a derived table which is used in a join and all within a try catch block with transaction handling. No updates, just returning data, works great, except now I have been asked to replace these stored procs with inline queries.
ALTER PROCEDURE [dbo].[usp_Get_Test]
(
@numberOfMonthsint
)
AS
BEGIN
SET NOCOUNT ON
set transaction isolation level read uncommitted
[Code] ....
The problems I have run into so far are...
SQL command text in OLE DB Source Editor does not like:
- TRY/CATCH block
- Will not let me use my input parm (@numberOfMonths int)
- When I hard code in my input parm (select @BeginDate = dateadd(MONTH, -1, GETDATE())) I can parse query and run the step but no results are returned. So I am let to assume that it does not like the @TESTY derived table.
The query here as a sample has had pivots removed as well, but research suggests this should be an issue in the SQL command text.
Also, I know not even to try the Build Query... cause it will complain about any variable declarations (i.e., declare @BeginDate datetime).
For instance, can I pull this off with an Execute SQL Task? The problem is I don't see this available in the toolbox for the Data Flow.
Also, would my error handling be done in the Event Handlers tab now and if so, is there a good example of this?
View 3 Replies
View Related
Aug 28, 2012
Here's the SP
Code:
USE [byrndb]
GO
/****** Object: StoredProcedure [dbo].[sp_GetLikeJobsByJobNumber] Script Date: 08/28/2012 15:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
Why when I run
Code:
USE [byrndb]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_GetLikeJobsByJobNumber]
@number = N'23913'
SELECT'Return Value' = @return_value
GO
Am I getting an "Msg 8114, Level 16, State 5, Procedure sp_GetLikeJobsByJobNumber, Line 16
Error converting data type varchar to numeric." error? Line 16 is "@number varchar = null"
View 5 Replies
View Related
Sep 30, 1998
I found toll for migrating FoxPro( 2.6 and 3.0) databases to Oracle but I need one
for FoxPro DB to MS-SQL. Any idea where to find it( if there is any ?)
Thanks
View 1 Replies
View Related
Nov 1, 2006
Hello everyone,My company uses a FoxPro database right now as an interfaceand a database. For our situation, I have come to the conclusion thatit would be a better choice for us to move to an SQL server of somesort. I have been given the task of overseeing the overhaul on theprogram. I am paranoid about security and uptime, and so is the CEO andthere is more and more demand for the company to get on the interactiveinternet. I'd like our clients to be able to submit data to ourdatabase and pull data from it (only certain data of course). My ideais to convert the FP tables to and SQL server and write an internalapplication(or web-based - advantages? I dunno) for the interface. Forthe internet side of things, my idea is to have seperate web database(SQL) that will put information from web clients. Through the internalinterface, internal users would then be able to pull data from the webdatabase to the internal SQL. And through the internet (authenticatedof course), the web users would pull data though the web database, whopulls information from the internal SQL database. Would someone pleasetear this idea apart w/ advantages and disadvantages. Also, if this isthe best route, tell me how I can sell this idea to my boss. What's sogood about using SQL vs. FP over the internet? What about internally?What about security? Cost is going to place a big role on the what theCEO decides, unless I can sell him otherwise. Should I tell him that weshouldn't do it now and save some money to do it right? Or what? Somehelp please. Thanks.Alex
View 12 Replies
View Related
Jul 12, 2007
Hi All,
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Thanks in advance,
Anand
View 4 Replies
View Related
Aug 4, 2000
Any suggestions on the least slowest way to import a large Foxpro table file into SQL Server? I'm looking for ANY suggestions. I'm not sure if I changed a setting, but I get an "out of disk space: can't create file c:emplahblah.xxx " error message when I try to run my standard data pump DTS (with a Fox ODBC datasource to a SQL table, no tranformations, straight copy). Thanks for the help.
Jim
View 4 Replies
View Related
Sep 11, 2007
Hello, all. I need to migrate a visual Foxpro database to SQL server. Is there a way I can import the visual Foxpro data files into Enterprise manager?
Thanks in advance.
View 2 Replies
View Related
Oct 25, 2004
Hi,
There is an issue at my company that creates a lot of confusion. Some people blame the slowness of queries on the FoxPro database, so we're considering to migrate to MS SQL Server.
What is the real difference between MS SQL Server and MS FoxPro?
Isn't MS SQL Server supposed to be faster? (It's a high-performance and very mainstream database, where I personally never knew anybody who used FoxPro). Some people claim that FoxPro performs faster in benchmarks. Is it true? If it is, why don't most people use FoxPro?
Also, when buying the database software for the server, which one is more expensive?
I would appreciate any pointers. Thanks.
View 3 Replies
View Related
Aug 30, 2007
Hello,
I'm looking to migrate a visual foxpro database to SQL server. Before I map and migrate over to the new system I need to get an understanding of the structure of the old one and cleans the data. Unfortunatley I don't have any of the orginal design documention. Is there a way I can import the visual foxpro data files into Enterprise manager? Or what method would you recommend to first get an understanding of how the database works?
Thanks for any help you can give me.
Andy
View 2 Replies
View Related
Oct 18, 2007
I want to send parameters to a DTS (Foxpro to SQL 2000). I got it figured out with a DTS(Exel to SQL 2000) but i can't find a way to get the thing to work for foxpro.
I want to do this because i have a stored procedure that calls the xp_cmdshell function and then by running the dts and just passing a parameter to it for the old order i want in my new application database...
Please any help would be appreciated......
View 3 Replies
View Related
Jun 1, 2007
I'm trying to export the data from sql to foxpro dbf file. I can't seem to find the OLE in 2005 any where. Can some one give me the direction how to export and connect to foxpro dbf file from import/export wizard
thanks
View 1 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Jul 16, 2007
Is it possible to connect virtual foxpro v6.0?
View 5 Replies
View Related
May 24, 2006
I have 2 packages, one to import data from Foxpro to Sql and another to send the data back from Sql to FoxPro.
The time it takes to execute the package that moves 2 million rows of data from FoxPro to Sql takes about 18 to 25 mins. Not bad. However, moving approximately the same number of rows from Sql back to FoxPro takes about 8 hrs. Not acceptable. I have tried several things with no luck.
Any advice would be appreciated.
V
View 3 Replies
View Related
Nov 5, 2004
Hello,
I want to know if there is an way to SET an index when send a query using the OpenQuery function to a FoxPro database using a linked server in the MS-SQL that points to a System DSN with the MS ODBC Driver to FoxPro.
Thank you in advance,
Aldair.
View 5 Replies
View Related
Jul 15, 2004
Hi,
Today was my first day to use DTS. I tried to transfer a Visual FoxPro Database to my SQL Server. I noticed that I am getting errors on the data that contains date fields. So, for testing purposes I transformed the Visual FoxPro fields that contained datefields into char fields. And this works, but obviously I cannot hunt around in a huge database looking for datefields in tables and change them manually. Plus, by changing to char fields I noticed that those fields that are empty are transformed as 1899-12-30. :p
Does anyone have any ideas?
Thanks,
Laura
View 3 Replies
View Related
Mar 7, 2004
I have to migrate Foxpro database ( DOS and windows versions) to SQLserver. Could you please someone write me, any measures that i would need to consider in migration?
View 1 Replies
View Related
May 6, 2004
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?
View 3 Replies
View Related
Feb 19, 2008
Hi all, I'm having issues with a FoxPro linked server.
I've set up a linked server to a FoxPro dbc using the Microsoft OLE DB Provider for Visual FoxPro. When I'm on Management Studio on my server the link appears to be working fine and a stored procedure I've created to get the indo from the dbc and put it into a temp table works fine.
However, when I try to execute the sp on management studio on my local machine I get the following error:
OLE DB provider "VFPOLEDB" for linked server "tern" returned message "Invalid path or file name.".
Msg 7303, Level 16, State 1, Procedure usp_SSRS_007, Line 28
Cannot initialize the data source object of OLE DB provider "VFPOLEDB" for linked server "tern".
And I also get a similar error when I try to test the connection of the linked server on my local machince.
This is now driving me nuts , so many many thanks in advance for any help!!!
View 4 Replies
View Related
Mar 17, 2008
I need help to import data from FoxPro tables to SQL server 2005. I want to be able to use SSIS Pkgs in 2005.
We already have Foxpro Drivers installed on the 2005 Server. Pl let me know what data source connections shud I use to be able to extract data from Foxpro .dbf tables into sql server 2005?
View 2 Replies
View Related
Oct 25, 2007
Hallo,
I have a question and hope that someone can help me.
I use of accountview application and now that are stored in the Foxpro database. I want to copy the data in my sql server, but not with ODBC but with SSIS. But I dotnow how this works. Can someone explain me step by step?
if I with the OBDC do see only virtual tables and I do not want that. I want the complete bases tables in my sql-server copying
Please Help me!!!!!!!!!
View 1 Replies
View Related
