How to insrt data from foxpro9 table to stored procedure in sql server.

[Niki_S]

This is my sp,

USE [MIS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Wash_Para_Inc]
(
		@nParaID smallint ,
		@cParaNo varchar(15),
		@cParaYear char(4) ,
		@cParaCD varchar(4) 
		
	)
AS
BEGIN
SET NOCOUNT ON
SET IDENTITY_INSERT wshPara ON;
Select  nParaID ,cParaYear,cParaCD ,  row_number() over (partition by cParaCD order by nParaID ) as cParaNo from wshPara;
  

END

I did SP based on this table,

ParaID    cParaYear   cParaCD    cParaNo 
218       2021        VTMC       1
54        2020        VTMF       1

And this is my foxpro table,

nParaID   cParaNo   cParaCD   cParaYear   cFtyCD   cInvNo
218       1         VTMC      2021        VTM      234/S/34

I need to get my final result as below and I need to insert auto increment number to SP when I click on my button.

ParaID    cParaYear   cParaCD    cParaNo 
218       2021        VTMC       1
[b]218       2021        VTMC       2[/b]
54        2020        VTMF       1

what should I do for this.

 

[Chris Miller]

This SP is selcting data, not inserting it. SET IDENTITY_INSERT wshPara ON; points out there should be an update or insert, but you don't do that.

Take a look at updatable remote views instead of using SPs, and when using SPs for such things, well, you need one that actualy does the insert or update on a table.

Chriss

 

[Mike Lewis]

Niki,

When you post sample data in Tek Tips, it would be very helpful if you could ensure that it all lines up in the correct columns. Otherwise, it is hard to see which values belong to which columns. So, instead of this:

ParaID cParaYear cParaCD cParaNo
218 2021 VTMC 1
54 2020 VTMF 1

it would be clearer if you did this:

ParaID   cParaYear   cParaCD   cParaNo
218      2021        VTMC      1
54       2020        VTMF      1

The way to do that is to wrap the text in [ignore]

[/ignore] tags. You can either type those tags yourself, or click on the "code" button in the toolbar.

This would also apply to the data you posted in thread184-1810456.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Niki_S]

How can I create an SP to insert auto increment record field according to group.

 

[Chris Miller]

There is no such autoincrement function.

Chriss

 

[Mike Lewis]

If your ultimate aim is to get the data into a VFP cursor, and for the rows in that cursor to contain an autoinc value (that is, a series of consecutive integers that are guaranteed to be unique), you could do it like this:

* In VFP
CREATE CURSOR MyCursor (ID integer AUTOINC, <other field specs>)
APPEND FROM <cursor that you get back from the SP>

But I suspect that is not what you want. It's hard to know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

The thing about automtic values created by a database is, they are createed by the one single central instance, not distributed.

So you don't query data from SQL Server with an IDENTITY column, add new rows with VFPs autoinc and then insert them into SQL Server, that's not how this works. Even without the need of a reset for each group.

In any report or form that gives you such a subsequence you generate it on the fly. And if you really store it in the table it'll be in simple integer field maintained by the business logic of your application, not by the database. The database can help you prevent double values but there's no generator for main and sub sequences itself.

I am with Mike, it's hard to tell what exactly you want to accomplish. Needing to write into an MSSQL identity column by SET IDENTITY_INSERT ON is also no solution for that.

Such sub sequences and wanting to overwrite them point out, that the database itself is the wrong place to generate them. To go back to my material of bills example, the bill is generated at a single client, it can number the mmaterials of a module of a product and to store that yoou best use a simple normal integer field.

Let's look at it from the other side and think of an extreme situation. 100s of users want to add bills of materials and each one wirks separately on diffferent products, so there is no concurrency. But a single sequence counter as an identity column is won't help in such situations, as there only is one identtity sequence curent number and every client would need its own counter.

Last not least, when you realize you need to SET IDNETITY_INSERT ON wheneer you write to that column, then realize the conclusion is you don't make any use of the IDENTITY nature of that column at all. So use a simple integer column for such sub sequences for numbering the items of a group.

Chriss

 

[Chris Miller]

Okay, but for sake of an example, a call to an SP with VFP needs SQLEXEC of the procedure call. It's not what VFP offers to update remote data, that is - as mentioned earlier - remote views, for example. Quite similar but with more options are cursoradapters and then you can do SQL Passthrough, SQLEXEC is central to that.

Chriss