Please help me SQL 2005 and Access Databases

I have a restaurant POS application that is written in VB 6 and the data is being written to a access database. I need to access this database over the internet. I need to get certain tables (current sales) in this database to automatically send the updated data to a Web Server. My questions are: What is the best way to do this? Do I need to have sql express loaded on site and let it do the interactions with the Access server and then connect to SQL express over the internet? I am new to this and not sure of the best way to design it. Do I use Report services or analysis services to design?

Thanks

What about using a WebService which exposes the data grabbing functionality. The WebService can be additionally secured via various methods. I would not use SQL Server as a gateway to get the data in that case.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you. I will look into this. I don't really understand the background process to get the data but will learn.

Bob

|||

Web Service is the only way to go if you're going to use SQL Express. You can not connect directly to SQL Express through the internet as it does not support HTTP Endpoints.

Mike

Please help me out

I have written a sql procedure, i want to pass delared variable @.cnt in line131,char24
below is the PL Code:
CREATE PROCEDURE PL
@.dt varchar(10),
@.stock as bit --1/true for updated and 0/false for previous executed data
AS

set dateformat dmy
set nocount on

Declare @.sdate VARCHAR(10)
Declare @.clup as Decimal(18,2)
declare @.cnt as integer
DECLARE @.amt as decimal(18,2),@.gamt as decimal(18,2), @.damt as decimal(18,2), @.camt as decimal(18,2)

delete from temp_pl
delete from temp_bal

Select @.sdate = Convert(varchar(10),StartDate,103 ) From Param
exec show_trial @.dt
-- Now data is in temp_trial
if( @.Stock =1 )
begin
exec Closing @.sdate, @.dt
end

select party_nm as acc, dr_amt as Amtdr, cr_amt as Amtcr into #Tlist from temp_trial WHERE party_cd not in (150,152,151,38,36,33,32,35,34)
--select * from #tlist

SELECT Acc,AmtDr,AmtCr ,Type, odr into #tmpop FROM (
Select 'Opening Stock (UP)' as Acc ,'AmtDr'= case when oup >0 then oup else 0 end ,
'AmtCr'= case when oup <0 then abs(oup) else 0 end , 'T' as Type ,1 as odr From temp_stock_balance
union
Select 'Opening Stock (Ex UP)' as Acc ,'AmtDr'= case when oex >0 then oex else 0 end ,
'AmtCr'= case when oex <0 then abs(oex) else 0 end , 'T' as Type ,1 as odr From temp_stock_balance

union
Select 'Purchase (UP)' as Acc ,'AmtDr'= case when pup >0 then pup else 0 end ,
'AmtCr'= case when pup <0 then abs(pup) else 0 end , 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Purchase (Ex UP)' as Acc ,'AmtDr'= case when pex >0 then pex else 0 end ,
'AmtCr'= case when pex <0 then abs(pex) else 0 end , 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Sales (UP)' as Acc , 'AmtDr' =case when sUp < 0 then abs(sup) else 0 END ,
'AmtCr' =case when sup > 0 then sup else 0 eND, 'T' as Type ,2 as odr From temp_stock_balance
union
Select 'Sales (Ex-UP)' as Acc , 'AmtDr' =case when sex < 0 then abs(sex) else 0 END ,
'AmtCr' =case when sex > 0 then sex else 0 eND, 'T' as Type ,2 as odr From temp_stock_balance

union
Select 'Closing Stock(UP)' as Acc , 'AmtDr' =case when CUp < 0 then abs(cup) else 0 END ,
'AmtCr' =case when Cup > 0 then cup else 0 eND, 'T' as Type ,3 as odr From temp_stock_balance
union
Select 'Closing Stock(Ex-UP)' as Acc , 'AmtDr' =case when Cex < 0 then abs(cex) else 0 END ,
'AmtCr' =case when Cex > 0 then cex else 0 eND, 'T' as Type ,3 as odr From temp_stock_balance
)A

Select * into #tmpl from(
SELECT Acc, AmtDr, AmtCr ,Type ,odr
From (Select * from #tlist ) a INNER JOIN (Select party_nm,Ac_type as Type,odr From Party where Ac_type IN ('P','T') )
Acmast ON a.Acc = Acmast.Party_nm
UNION
select Acc,AmtDr,AmtCr ,Type , odr from #tmpop
) a

update #tmpl set Amtdr = abs(AmtCr) where AmtCr < 0
update #tmpl set AmtCr =abs( Amtdr) where AmtDr < 0
/*
--Gp
set @.clup = (Select 'GP' = case
when sum(AmtDr)>= sum(AmtCr) then (Sum(AmtDr) -Sum(AmtCr) )* -1

When sum(AmtDr) < Sum(AmtCr) then (Sum(AmtCr) -Sum(AmtDr) ) end From #tmpl where Type= 'T' )
*/
--TRADING A/C
SELECT Acc , AmtCr,type into #CT FROM #tmpL WHERE (AmtCr > 0 and Type ='T') order by odr Asc, Acc asc
SELECT Acc , AmtDr,type into #DT FROM #tmpL WHERE (AmtDr > 0 and Type ='T') order by odr Asc, Acc asc

SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtCr,type into #CreditT FROM #CT
SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtDr,type into #debitT FROM #DT

Select IDENTITY(int, 1,1) AS sr_no, * INTO #TRADE1 from
(Select d.Acc as Accdr , d.AmtDr as AmtDr , C.acc as AccCr ,C.amtCr as AmtCr ,'T' as Type from
#debitT d Full outer join #creditT c on c.ids =d.ids
)A
insert into #trade1 values(null,0,null,0,'T') --Line before sum

select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #trade1

select @.damt=sum(isnull(amtdr,0)) from #trade1
select @.camt=sum(isnull(amtcr,0)) from #trade1
insert into #trade1 values (null,@.damt,null,@.camt,'T')
if @.amt>0
begin
select @.gamt=sum(isnull(amtcr,0)) from #trade1
insert into #trade1 (accdr,amtdr,type) values('Gross Profit C/F to P&L',@.amt,'T')
end
else if @.amt<0
begin
select @.gamt=sum(isnull(amtdr,0)) from #trade1
insert into #trade1 (acccr,amtcr,type) values('Gross Loss C/F to P&L',abs(@.amt),'T')
end
else
begin
select @.gamt=sum(isnull(amtdr,0)) from #trade1
end
insert into #trade1 values(null,0,null,0,'T') --Line after gross
insert into #trade1 values(null,@.gamt,null,@.gamt,'T')
insert into #trade1 values(null,0,null,0,'T') --Line after Trade gross
if @.amt>0
begin
insert into #trade1 (acccr,amtcr,type) values('Gross Profit B/F from P&L',@.amt,'P')
end
else if @.amt<0
begin
insert into #trade1 (accdr,amtdr,type) values('Gross Loss B/F from P&L',abs(@.amt),'P')
end
else
begin
insert into #trade1 values(null,null,null,null,'T')
end
Select * INTO #TRADE from #trade1 order by sr_no
------------------------------
--P/L A/C

SELECT Acc , AmtCr,type into #CP FROM #tmpL WHERE (AmtCr > 0 and Type ='P') order by odr Asc, Acc asc
SELECT Acc , AmtDr,type into #DP FROM #tmpL WHERE (AmtDr > 0 and Type ='P') order by odr Asc, Acc asc

SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtCr,type into #CreditP FROM #CP
SELECT IDENTITY(int, 1,1) AS ids,Acc , AmtDr,type into #debitP FROM #DP

select @.cnt=count(SR_NO)+1 from #trade

select IDENTITY (int,@.cnt,1) AS sr_no,accdr,amtdr,acccr,amtcr,type INTO #PL_TEMP1 from #trade t WHERE t.type='P'

insert into #pl_temp1 (accdr,amtdr,acccr,amtcr,type)
Select d.Acc ,d.AmtDr, C.acc ,C.amtCr as AmtCr ,'P' as Type from
#debitP d Full outer join #creditP c on c.ids =d.ids

delete from #trade WHERE type='P'
select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #PL_TEMP1

insert into #PL_TEMP1 values(null,0,null,0,'P') --line before sum

select @.amt=sum(isnull(amtcr,0))-sum(isnull(amtdr,0)) from #PL_TEMP1
insert into temp_bal values(@.amt)

select @.damt=sum(isnull(amtdr,0)) from #PL_TEMP1
select @.camt=sum(isnull(amtcr,0)) from #PL_TEMP1
insert into #PL_TEMP1 values (null,@.damt,null,@.camt,'P')
if @.amt>0
begin
select @.gamt=sum(isnull(amtcr,0)) from #PL_TEMP1
insert into #PL_TEMP1 (accdr,amtdr,type) values('Net Profit C/F to Balance Sheet',@.amt,'P')
end
else if @.amt<0
begin
select @.gamt=sum(isnull(amtdr,0)) from #PL_TEMP1
insert into #PL_TEMP1 (acccr,amtcr,type) values('Net Loss C/F to Balance Sheet',abs(@.amt),'P')
end
else
begin
select @.gamt=sum(isnull(amtdr,0)) from #PL_TEMP1
end
insert into #PL_TEMP1 values(null,0,null,0,'P') --Line after NET
insert into #PL_TEMP1 values(null,@.gamt,null,@.gamt,'P')
insert into #PL_TEMP1 values(null,0,null,0,'P') --Line after PL NET

Select * INTO #PL_TEMP from #PL_TEMP1 order by sr_no

INSERT INTO temp_PL
SELECT * FROM
(select * from #PL_TEMP
UNION
SELECT * FROM #TRADE
) a order by sr_no
GO

please help meDo not post your procedure. Just explain your problem in detail and post code in the pirticular error area.

PL/SQL command to print a saved file in unix?

I've created and written a file to a unix directory using a stored procedure. Is there a way through that same stored procedure to send that file to a printer.

Right now I'm using lp -d 1_it_zeb overpack_lbl.txt to print the file from a command prompt. How would I send this same command to unix using my procedure?

Thanks!
CraigOne method is to create a pipe on unix that performs a specific task, say printing in your case. The following example shows a pipe that is created such that it compresses that file being written to (in this case, PL/SQL).

#--Setup the pipeline to be used in the export process
mknod $dmp_file_name p
compress < $dmp_file_name > $dmp_file_name.Z &

You should be able to formulate a pipe to send the contents to a printer.

Or, you could set up a cron job to look at files being generated and once completed, send it off to the printer.