I am brand new to SQL Server (coming from PL/SQL). I'm trying to modify someone elses PL/SQL code to our new SQL environment.
So far I have the following procedure that I call from an Access front end:
USE [APPS_DEV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_ENGCM_FINAL]
WITH EXEC AS CALLER
AS
DECLARE @PL_TEMP_1 TABLE (PART CHAR(32), QPA NUMERIC, FILE_TYPE CHAR(32))
DECLARE @PL_TEMP_2 TABLE (USER_NAME VARCHAR(20), DATE_ADD DATE, FILE_TYPE VARCHAR(10),
ASSY CHAR(32), PART CHAR(32), QPA NUMERIC, REF_DES VARCHAR(MAX))
DECLARE @PL_TEMP_3 TABLE (USER_NAME VARCHAR(20), DATE_ADD DATE, FILE_TYPE VARCHAR(10),
ASSY CHAR(32), PART CHAR(32), QPA NUMERIC, REF_DES VARCHAR(MAX))
END
I then need to output that table as a .csv file. I have this code that works fine within the SSMS environment but I can't call it from either 1) the prior procedure or 2) from the Access front end.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
SET NOCOUNT ON;
:!!if exist C:\engcm\cp_bom_csv_export\pl_temp_final.csv del c:\engcm\cp_bom_csv_export\pl_temp_final.csv
:connect aegis-data\sqlaegistmp
:out c:\engcm\cp_bom_csv_export\pl_temp_final.csv
SELECT assy, item, part, qpa, ref_des
FROM L3_APPS_DEV.DBO.pl_temp_final
END
GO
Any suggestions are greatly appreciated.
What I have tried:
I've tried BCP but that would require a directory on each users machine (at least that is how I understand it would work). That could problematic.