I want to make Store procedure

I want to make Store procedure. it has function for import data from foxpro.
but I want it can be flexible to set location data.
CREATE PROCEDURE #SP_Import_BC
@.Lokasidata varchar(100)='E:\agr_absen',
@.Tg1 smalldatetime,
@.Tg2 smalldatetime
AS
--
if exists (select 1 from sysobjects
where id = object_id('"TTEMP_BC"') and type = 'U')
DROP TABLE TTEMP_BC
--
CREATE TABLE TTEMP_BC (
RecID int IDENTITY (1, 1) Primary Key,
FDDATE smalldatetime NULL ,
FCTIME smalldatetime NULL ,
FCIDNUMBER nvarchar (6) NULL ,
FCSTATUS nvarchar (3) NULL ,
Bagian nvarchar (3) NULL ,
Oprt nvarchar (8) NULL ,
FNTRNUM smallint NULL ,
FNPROSES smallint NULL
)
insert into TTEMP_BC
SELECT * FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="'+@.Lokasidata+'";Extended properties=DBase III')...cardraw
where fctime is not null and fddate between @.Tg1 and @.Tg2
GO
But it cannot be compiled. How to make stored procedure that I want?You need to execute your query with EXECUTE('yourquery') to be able to conca
t
a variable to a query.
I suggest also to create a linked server.
Jonathan
"Bpk. Adi Wira Kusuma" wrote:

> I want to make Store procedure. it has function for import data from foxpr
o.
> but I want it can be flexible to set location data.
> CREATE PROCEDURE #SP_Import_BC
> @.Lokasidata varchar(100)='E:\agr_absen',
> @.Tg1 smalldatetime,
> @.Tg2 smalldatetime
> AS
> --
> if exists (select 1 from sysobjects
> where id = object_id('"TTEMP_BC"') and type = 'U')
> DROP TABLE TTEMP_BC
> --
> CREATE TABLE TTEMP_BC (
> RecID int IDENTITY (1, 1) Primary Key,
> FDDATE smalldatetime NULL ,
> FCTIME smalldatetime NULL ,
> FCIDNUMBER nvarchar (6) NULL ,
> FCSTATUS nvarchar (3) NULL ,
> Bagian nvarchar (3) NULL ,
> Oprt nvarchar (8) NULL ,
> FNTRNUM smallint NULL ,
> FNPROSES smallint NULL
> )
> insert into TTEMP_BC
> SELECT * FROM
> OpenDataSource('Microsoft.Jet.OLEDB.4.0',
> 'Data Source="'+@.Lokasidata+'";Extended properties=DBase III')...cardraw
> where fctime is not null and fddate between @.Tg1 and @.Tg2
> GO
> But it cannot be compiled. How to make stored procedure that I want?
>
>|||Please, I dont understand with your explaining. Can u give so detail?
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:939E0500-5A6B-4D19-AD18-03676F885598@.microsoft.com...
> You need to execute your query with EXECUTE('yourquery') to be able to
concat
> a variable to a query.
> I suggest also to create a linked server.
> Jonathan
> "Bpk. Adi Wira Kusuma" wrote:
>
foxpro.|||Do you have an error message ?
I think that you probably have one because I don't think that this query is
working fine.
Normally, when you concat a variable in a query, you need to execute them
with the EXECUTE fonction, like that.
EXECUTE('insert into TTEMP_BC
SELECT * FROM
OpenDataSource(Microsoft.Jet.OLEDB.4.0,
'Data Source='+@.Lokasidata+';Extended properties=DBase III)...cardraw
where fctime is not null and fddate between @.Tg1 and @.Tg2')
... if I understand ...
Jonathan
"Bpk. Adi Wira Kusuma" wrote:

> Please, I dont understand with your explaining. Can u give so detail?
>
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:939E0500-5A6B-4D19-AD18-03676F885598@.microsoft.com...
> concat
> foxpro.
>
>