BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

Hello,

 

We are tryng to Insert an .CSV file as a Blob object in the Table in MS SQL server.

A) SAS is installed on the UNIX sever and We are using SQL Passthrough facility to insert the file in the database table.
B) We are facing an error while doing this task.


ERROR: CLI prepare error: [SAS/ACCESS][ODBC 20101 driver][Microsoft SQL Server]Cannot bulk load because the file
       "/opt/sasinside/SASDATA/EXPORT_LOGS/TESTEXPORT.csv" could not be opened. Operating system error code 3(failed to retrieve
       text for this error. Reason: 15105).

 

It seems like system is not able to find the path specified (Error code 3?)


To insert file as a blob in the Database, is it necessary to store the file in the same server/location? Is there any workaround to do this task?

 

Code Used:

proc sql;
   connect to sqlsvr as myconn (datasrc="MSSQLDSN" user=WinProfileSSRReader password='xxxxxxxxxxxxxxxxxx');

    select * from connection to myconn
     (

DECLARE @file AS VARBINARY(MAX);
SELECT @file = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(
            BULK
            '/opt/sasinside/SASDATA/EXPORT_LOGS/TESTEXPORT.csv',
            SINGLE_BLOB ) AS x;

/*insert into WinProfileRefUser.SSR_REPORTS (id,tnuid,FILE)*/
/*          values (NEWID(),'CCCCCC',@file)*/
         );

   disconnect from myconn;
quit;

 

Thanks in Advance,
Nikhil

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

There are a few things wrong with your SQL.

 

select * from connection to myconn
     (

DECLARE @file AS VARBINARY(MAX);
SELECT @file = CAST(bulkcolumn AS VARBINARY

 

The SELECT... statement is for a query and not for what you're trying to do. Look-up the EXECUTE statement.

http://support.sas.com/documentation/cdl/en/sqlproc/68053/HTML/default/viewer.htm#n0tpd3yaqvep53n1g8...

 

You can't have two statements in a single EXECUTE() block. You need either to wrap this into PL/SQL or into multiple EXECUTE() blocks.

 

You're sending statements to the database. Loading happens from there so the external file inclusive path must be as "seen" from the database server.

 

Not sure about your statements. Googling a bit it looks as if this wouldn't need to be that complicated.

https://msdn.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

 

And last but not least: Why do you want to store a textual file a BLOB and not as CLOB?

View solution in original post

5 REPLIES 5
Patrick
Opal | Level 21

There are a few things wrong with your SQL.

 

select * from connection to myconn
     (

DECLARE @file AS VARBINARY(MAX);
SELECT @file = CAST(bulkcolumn AS VARBINARY

 

The SELECT... statement is for a query and not for what you're trying to do. Look-up the EXECUTE statement.

http://support.sas.com/documentation/cdl/en/sqlproc/68053/HTML/default/viewer.htm#n0tpd3yaqvep53n1g8...

 

You can't have two statements in a single EXECUTE() block. You need either to wrap this into PL/SQL or into multiple EXECUTE() blocks.

 

You're sending statements to the database. Loading happens from there so the external file inclusive path must be as "seen" from the database server.

 

Not sure about your statements. Googling a bit it looks as if this wouldn't need to be that complicated.

https://msdn.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

 

And last but not least: Why do you want to store a textual file a BLOB and not as CLOB?

nikhil_khanolkar
Calcite | Level 5

Hi Patrick,

 

thanks a lot for your thoughts. You are right, database  server does not have access to the File location and there are challeges in providing access beacuse of security concerns. So it would take a while before we have required access in place.

 

I was thinking about the workaround where

  • store .CSV file into the SAS server memory as a binary object variable.
  • and then stream this object from SAS memory into the SQL table column as a BLOB

Is this feasible to do in SAS?

Please share your thoughts/suggestions.

 

Thanks in advance,

Nikhil

Patrick
Opal | Level 21

 

I don't believe your idea can work for multiple reasons.

 

 

Would it be possible to copy your files to a location where SQL Server can read them from (eg. via a SFTP) and then dynamically generate the pass-through SQL code for loading?

 

nikhil_khanolkar
Calcite | Level 5

Hi Patrick,

 

That approach is being considered where in SQL reading a file from the location and then inserting in a table. But that would involve creating a area that is accesible to SQL and SAS. We would take that path if any other approach does not work.  🙂

 

Can you please advice reasons why idea discussed wouldn't work? Just for my understanding.

 

Is it because concept of storing a file as a binary object in memory is not native to SAS among other reasons?

 

 

Regards,

Nikhil

Patrick
Opal | Level 21

Hi Nikhil

 

I'm a bit out of my depth here that why I wrote "believe". So just a few thoughts.

1. The SAS/Access engine supports loading a SAS table into a data base but there is no SAS variable type which allows you to store a BLOB and there is no support of loading into a BLOB documented.

2. The pass-through code you've posted and everything I could find on the Internet uses a path - and as this executes on the database server this path or url must be accessible from the database server.

 

Having said that it appears to be possible to load BLOBs via ODBC but it will impact heavily on performance AND you would have to write this on your own (=without the SAS/Access engine). So: I don't know if it's possible at all but I do know that it won't be easy and certainly something you need to write on your own as it's not supported by SAS.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3939 views
  • 2 likes
  • 2 in conversation