About milts

milts · ‎09-27-2017

I have a script which extract data from a MS SQL table. However I'm getting a UCS-2 transcoding error as I have a column with a nvarchar(max) data type. I have some records with more than 32000 Is there a syntax for my proc sql script that I can define the dbmax_text? I know for libname I'll be able to add the dbmax_text. proc sql noprint; connect to odbc("Driver=ODBC Driver 11 for SQL Server;Server=SQLSVR;Database=MYDB;uid=user;pwd=pwd;"); create table extract(compress=no) as select * from connection to odbc ( select * from mysch.mytbl ); disconnect from odbc; quit; Thanks!

milts · ‎09-10-2017

Yes, confirming the user has a home folder under C:\Users, also has Log on as a batch job permissions. I actually have an initial post with a similar issue on the FolderID_Documents which I had to remove in my custom sas config file because the Windows Task Scheduler fails due to this. https://communities.sas.com/t5/Base-SAS-Programming/SASUSER-Library-FOLDERID-Documents-My-SAS-Files-9-4-Fails-on/m-p/389838#M93491 After doing some investigation, it seems like the one which is causing the problem is when I initialize the saspy module in my python script. It seems it cannot open up a workspace or it's also calling the default sasv9.cfg file which actually has a declaration of the FolderID_Documents. Apparently, I don't see any options in the saspy module to select a specific sas config file.

milts · ‎09-07-2017

I get an error in the event viewer which contains "Unknown error for KNOWNFOLDERID FOLDERID_Documents" when I try to run my batch script which calls SAS in the Windows Task Scheduler. When I try to run it outside I don't get any problems. Other info: The SAS script uses systask to run another batch script which calls a python script When I try to run that batch script calling a python script, it works properly Anyone who have a similar problem like this? Greatly appreciate any assistance.

milts · ‎08-22-2017

Yes this is what I currently my setup. But I actually copied it from the u8 folder as a baseline and then removed those options I do not need.

milts · ‎08-22-2017

It's an admin account with privilege as Run As Batch Job

milts · ‎08-22-2017

On the default u8 sasv9.cfg there's a section on SASUSER -SASUSER "?FOLDERID_Documents\My SAS Files\9.4" When I execute my sas scripts manually or through command prompt, I get no issues. But when I execute the script through Windows Task Scheduler I get an error that it cannot find FOLDERID_Documents. Currently I resolved my problem in the task scheduler by remove lines which tries to reference FOLDERID_Documents Is there any SAS Note what causes and explains these? What's the default behavior if I remove these on my config file?

milts · ‎08-17-2017

I have a dataset extracted from a SQL table which contains nvarchar(max) fields. My DBMAX_TEXT is set to 32000 and bulkload for the libname is set to YES. When I writeback to another SQL table, I get the error "Invalid field size for datatype". I actually saw an old post asking a similar question but seems left unanswered. https://communities.sas.com/t5/General-SAS-Programming/How-to-load-large-text-fields-into-SQL-Server-with-bulkload/td-p/134298 Appreciate any resolution into this issue. Thanks!

milts · ‎08-17-2017

Hi, did you manage to find a solution for this one? I'm currently having the same problem. If I remove the bulkload I guess it's gonna take very long to insert data when it's 100k+ or even million of records.

milts · ‎07-16-2017

Is it the collation in SQL Server? The database is Latin1_General_CI_AI and I can view chinese characters when I query in SQL Management Studio. I'm actually able to view them now in SAS EG also, but when I filter by dates I get thrown again to a similar error.

milts · ‎07-16-2017

This is a very silly beginner question but would like to seek some advise. I'm getting this error when the value of the macro variable layer has a dash. Seems like it's still trying to evaluate it even I have placed %str. This is the error I get and the code sample below. ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &layer = stg or &layer = stg-m options symbolgen; %let layer=stg-m; %macro test; %if %str(&layer) = %str(stg) or %str(&layer) = %str(stg-m) %then %do; %put CORRECT; %end; %mend; %test;

milts · ‎07-14-2017

You might want to explore reading them as strings first then use prxmatch to check the pattern so you could convert them with the proper informat. Usage of prxmatch can be found here http://support.sas.com/documentation/cdl/en/lefunctionsref/69762/HTML/default/viewer.htm#n0bj9p4401w3n9n1gmv6tf**bleep**9m.htm

milts · ‎07-14-2017

You are correct it seems including the encoded password does not work in the connection string. I had to do a manual work around that picks up the password from a file while supressing the log when assigning the librefs.

milts · ‎07-14-2017

The default is already in UTF-8. I managed to resolve it by adding read permissions to the library and table itself in SMC and pre-assigning the library with metadata engine. I find it strange though that sometimes the error appears when you first open the table but once it's in the process flow it can actually be viewed properly.

milts · ‎07-14-2017

Thanks for all the responses. I've managed to solve it. As per explanation by @AnandVyas, when launching SMC cached credentials are not available yet, so when i go register tables, it asks for a two prompts, first is on the SASApp which is a domain account (Default Auth) that launces the workspace, followed by the prompt which asks for the username and password for the SQL Server (DB Auth). I didn't notice that prompts initially so I was actually trying to key in the SQL server account when it as still asking for the domain account.

milts · ‎07-14-2017

Thanks to everyone for all the enlightening responses!

Online Status	Offline
Date Last Visited	‎05-12-2018 02:19 AM

Error when importing SPK Files with VA Reports

Re: Metadata Server Denied Access to Operation when loading SAS datase...

Re: 500 Internal Server Error An error occurred. Please contact your s...

Re: Metadata Server Denied Access to Operation when loading SAS datase...

Re: Metadata Server Denied Access to Operation when loading SAS datase...

Impact of Deleting and Reloading Tables in SAS VA Reports

Metadata Server Denied Access to Operation when loading SAS dataset to...

Re: SAS PROC Report Totals on Second Column

Re: SAS PROC Report Totals on Second Column

SAS PROC Report Totals on Second Column

Re: Metadata Server Denied Access to Operation when loading SAS datase...

Re: Metadata Server Denied Access to Operation when loading SAS datase...

Re: Metadata Server Denied Access to Operation when loading SAS datase...

Re: SAS Visual Analytics Email Log

Re: Macro %mduchglb behaves incosistently

Re: A problem with integrity constraint

Impact of Deleting and Reloading Tables in SAS VA Reports

SASUSER Library ?FOLDERID_Documents\My SAS Files\9.4 Fails on Task Sch...

Re: Adding Authentication Domain for SQL Server

Re: SAS Config Files Sequence

Extract SQL Server data with NVARCHAR(max) data type

Re: Unknown error for KNOWNFOLDERID FOLDERID_Documents

Unknown error for KNOWNFOLDERID FOLDERID_Documents

Re: SASUSER Library ?FOLDERID_Documents\My SAS Files\9.4 Fails on Task...

Re: SASUSER Library ?FOLDERID_Documents\My SAS Files\9.4 Fails on Task...

SASUSER Library ?FOLDERID_Documents\My SAS Files\9.4 Fails on Task Sch...

Invalid field size for datatype ERROR when appending to SQL table

Re: How to load large text-fields into SQL-Server with bulkload-option...

Re: SQL ODBC ERROR: Unable to transcode data to/from UCS-2 encoding

Macro %IF fails when variable has minus sign

Re: date formats

Re: Connection String to SQL Server Not Working

Re: SQL ODBC ERROR: Unable to transcode data to/from UCS-2 encoding

Re: Adding Authentication Domain for SQL Server

Re: SAS Config Files Sequence