Click here to Skip to main content
15,868,419 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

SQL
USE [APPS_DEV]
GO
/****** Object:  StoredProcedure [dbo].[P_ENGCM_FINAL]    Script Date: 3/22/2016 9:02:38 AM ******/
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))

--Lots of code that ultimately creates a table called: pl_temp_final

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.

SQL
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.
Posted
Updated 22-Mar-16 3:40am
v3
Comments
CHill60 23-Mar-16 7:52am    
It looks like that is just a code snippet. You need to Create a Stored Procedure[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900