Proc SQL write to MS SQL - target tablename has more than 32 characters

jjmccoydk — Mon, 15 Jun 2020 06:28:39 GMT

Hey
I have problem with this variale.

I want to use a transformation in Data stadio to automate loading til MS SQL with long table names.

I get this error.

ERROR: CLI execute error: [SAS][ODBC 20101 driver][Microsoft SQL Server]Invalid object name '[Mellemkommunal Odense$OSR Vendor

No_]'.

The problem is single quote, when i am using the variable.

then i write the table name in the cod

execute (
delete
from [Mellemkommunal Odense$OSR Vendor No_]

No problems.

Any suggestion

LIBNAME xpophona BASE "!SAS_DATA/6_eksport/opholdsbetaling_navision/data";


/* Access the data for EXT Opholdsbetaling Navision Write odbc  */ 
LIBNAME x_ophnaw ODBC  DATAsrc=EXT_Opholdsbetaling_Navision_W  SCHEMA=dbo ;
/* code options in the transformation*/
%let SQL_table =%nrqoute([Mellemkommunal Odense$OSR Vendor No_]); /*automatic generated by the transformation*/
%let SQL_columns = %nrquote([VAT Registration No_],[OSR Vendor No_],[OSR Vendor Name],[B-Taxable]);/*automatic generated by the transformation*/

--------------------------------------------------------*/
/*1) Def. of variables                                                                          */
/*---------------------------------------------------------------------------------------------*/
%put &=SQL_table;
%put &=SQL_columns;

proc sql;
	connect to odbc (DATAsrc=EXT_Opholdsbetaling_Navision AUTHDOMAIN="AuthDom MSSQL sasbatch");

	execute (
			delete	   
			from "&SQL_table"
			)
			by odbc;
disconnect to odbc;
quit;

Re: Proc SQL write to MS SQL - target tablename has more than 32 characters

Ksharp — Mon, 15 Jun 2020 10:52:06 GMT

1) Try libname 's option preserve_table_name=yes
2) Let DB create a view to correct this >32 table name ?

Re: Proc SQL write to MS SQL - target tablename has more than 32 characters

jjmccoydk — Wed, 17 Jun 2020 06:56:56 GMT

hey

preserve_tab_names=yes

I have the same problem.

2) It is not an option , bacause tablename is defined by Microsoft Business Central.

help

topic Proc SQL write to MS SQL - target tablename has more than 32 characters in SAS Programming

Proc SQL write to MS SQL - target tablename has more than 32 characters

Re: Proc SQL write to MS SQL - target tablename has more than 32 characters

Re: Proc SQL write to MS SQL - target tablename has more than 32 characters