topic Re: sql server using 'with' in SAS Programming

sql server using 'with'

Steelers_In_DC — Mon, 30 Dec 2024 15:18:48 GMT

I am using a pass through connection to sql server. I am attempting to translate from a pass through connection to hadoop. Can anyone look a the code below and see if this looks correct. The normal way I use the pass through to sql server isn't working due to an extra open parenthesis, but when I remove it I still get an error. The open parens prior to select distinct, as well as the open parens in front of WITH are the issue. If I add another close parens at the end it errors.

proc sql;
connect to sqlsvr (dsn=chsrproddb &_log_host);
CREATE TABLE FullData AS
SELECT * FROM CONNECTION TO sqlsvr
(select distinct
(WITH this_srvc_auth AS
( SELECT

Re: sql server using 'with'

Tom — Mon, 30 Dec 2024 18:51:46 GMT

Everything between the () in a statement like:

select * from connection to XXX ( .... ) ;

has to be valid syntax on the database that the connection named XXX is pointing to.

If you have a HADOOP question perhaps you should ask it on a HADOOP forum?

Re: sql server using 'with'

SASKiwi — Mon, 30 Dec 2024 21:18:36 GMT

It would help if you posted your complete Passthru query. Then we might understand what it is trying to do and be able to offer more helpful advice. In my experience SQL dialects don't necessarily translate well because of custom additions like the one you have found.

Re: sql server using 'with'

LinusH — Tue, 07 Jan 2025 08:38:40 GMT

1. Have your query work in a SQL Server interface (e.g., SQL Server Management Studio).

2. Attach thew full log in you post.