SAS/ASSESS从SQLserver中取类型为text的字段的数据取不全，有什么解决办法？

1. proc sql flow=100000;
   
2. connect to sqlservr as server
   
3. (server=192.168.1.100
   
4. database=SasTest
   
5. user=sa
   
6. password=sa);
   
7. create table code as select *from connection to server (select code from a );
   
8. disconnect from server;
   
9. quit;

复制代码

用上面这段代码从SQL Server中取表a中的数据其中code在sql中保存为text类型
运行后，创建的sas数据集code中code字段的内容数据库中的内容要少，如果要获取完整内容要怎么解决
code中有6w字左右

You got the error because you may not have the sqlsrv engine. It can be verified in PROC SETINIT.
Try the ODBC or OLEDB engine instead.

1. libname test odbc noprompt='database=CLOUD;driver={sql server};server=ben-pc;uid=sa;pwd=xxxxx' DBMAX_TEXT=32767;

复制代码

Just my thought.
(1) the length of a SAS character variable is limited up to 32767, so your SQL text might have been truncated.
(2) you may create a SQL Server view with the code variable separated into two pieces, then let SAS read the view. If you're not allowed to create views in the database, create a temporary table sort of thing would be fine.

邓贵大 发表于 2013-5-8 18:51
Just my thought.
(1) the length of a SAS character variable is limited up to 32767, so your SQL tex ...

I only get 1000 character far away from 32767
Do you know what happen?

zhou.wen 发表于 2013-5-8 19:05
I only get 1000 character far away from 32767
Do you know what happen?

no idea! haven't used SQL Server since I went down hell.
Maybe it's because SAS cannot determine the actual length of a SQL TEXT or VARCHAR(MAX) field, then it decides by itself to keep the first 1000 characters?
http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#n1aqglg4ftdj04n1eyvh2l3367ql.htm
Check the link above, the default value for DBMAX_TEXT is 1024. How the hell you got 1,000?!

Anyway, I think you may break your SQL TEXT field into either 8 VARCHAR(7500) fields or 2 VARCHAR(MAX) fields. In the latter case, set option DBMAX_TEXT to 32767 as well. Then SAS should be able to handle that.

邓贵大 发表于 2013-5-8 19:14
no idea! haven't used SQL Server since I went down hell.
Maybe it's because SAS cannot determin ...

thank for helping
My fault.the accurate number is 1024
But the DBMAX_TEXT is only supported in the libname statement
As soon as I run the code as follow ,it turn out the errors.

1. libname mydblib sqlsvr
   
2.    noprompt="uid=testuser;
   
3.    pwd=testpass;
   
4.    dsn=sqlservr;"
   
5.    stringdates=yes;

复制代码

The error is:

1. 41   libname mydblib sqlsvr
   
2. 42      noprompt="uid=sa;
   
3. 43      pwd=sa;
   
4. 44      DATASRC=CLOUD;
   
5. 45      dsn=sqlservr;"
   
6. 46      stringdates=yes;
   
7. ERROR: 找不到 SQLSVR 引擎。
   
8. ERROR: LIBNAME 语句出错。

复制代码

what does it mean? I haven't install the SQLSVR engine?But I do run the code using the "SQL PASS:proc sql" successfully !
Can you give me some advice?

邓贵大 发表于 2013-5-8 19:14
no idea! haven't used SQL Server since I went down hell.
Maybe it's because SAS cannot determin ...

1. 93   proc sql noprint;
   
2. 94   connect to sqlservr as server(server=ben-pc database=CLOUD user=sa password=XXXXX);
   
3. 
   
4. NOTE: Variable 'MSSQLDSN' not set, 'DRIVER=SQL Server' is assumed
   
5. 95   create table a as select *from connection to server (select *from a);
   
6. NOTE: 表 WORK.A 创建完成，有 3 行，1 列。
   
7. 
   
8. 96   disconnect from server;
   
9. 97   quit;
   
10. NOTE: “PROCEDURE SQL”所用时间（总处理时间）:
    
11.       实际时间          4.04 秒
    
12.       CPU 时间          0.09 秒
    
13. 
    
14. 
    
15. 98
    
16. 99
    
17. 100  libname mydblib sqlsvr server=ben-pc database=CLOUD user=sa password=XXXXX;
    
18. 
    
19. ERROR: 找不到 SQLSVR 引擎。
    
20. ERROR: LIBNAME 语句出错。

复制代码

邓贵大 发表于 2013-5-9 11:24
You got the error because you may not have the sqlsrv engine. It can be verified in PROC SETINIT.
T ...

Eventually,I  succeed.
Thank you for your helping,I appreciate it greatly.

1. 222  proc sql ;
   
2. 223  connect to oledb
   
3. 224  (provider=sqloledb DBMAX_TEXT= 32767 dsn='ben-pc' user=sa password=XXXXX
   
4. 224! properties=("Initial Catalog"=cloud));
   
5. 225  create table a as select *from connection to oledb (select *from a);
   
6. NOTE: 表 WORK.A 创建完成，有 3 行，1 列。
   
7. 
   
8. 226  disconnect from oledb;
   
9. 227  quit;
   
10. NOTE: “PROCEDURE SQL”所用时间（总处理时间）:
    
11.       实际时间          0.03 秒
    
12.       CPU 时间          0.03 秒

复制代码

One last question,Can I set the length of the SAS character variable longer than 32767 by some option?
It is nconvenient to separate the code ,because it store in the SQL server as a whole

No, I think 32,767 is a hard limit.
Are you the old Zhou Wen I knew?

邓贵大 发表于 2013-5-9 12:26
No, I think 32,767 is a hard limit.
Are you the old Zhou Wen I knew?

Who is the old Zhou Wen?
By the way,I figure out a Compromise.

1. create table a as select *from connection to oledb (select substring(code,1,32767) as code1 ,substring(code,32767,len(code)) as code2  from a);

复制代码