none
Getting error "Could not find stored procedure 'sp_OACreate'" in Sql Azure.

    Question

  • When I try to execute the 'sp_OACreate' store procedure in SQL azure database. I am getting following error:-

    "Could not find stored procedure 'sp_OACreate'" in Sql Azure.

    Can anyone tell me how to execute the OLE automation methods in SQL azure.

    Any help will be highly appriciated.

    Thanks

    Kapil Bhatia

    Thursday, January 13, 2011 11:22 AM

Answers

  • Hi Kapil,

    SQL Azure prevents you from calling server resources; it is limited to pure transactional commands for the time being. sp_OACreate attempts to create a reference to an OLE object on the server; that's simply not allowed. So you will need to redesign your solution to move this type of logic in a middle-tier of some kind, such as a web role in Windows Azure.

    Herve


    Herve Roggero, Blue Syntax MVP SQL Azure Co-Author: Pro SQL Azure
    Friday, January 14, 2011 8:42 PM

All replies

  • Kapil,

    although OLE automation isn't mentioned in the 'missing features section' of the MSDN documentation I suspect this feature isn't currently supported in SQL Azure. Maybe you can elaborate a bit more on your scenario and we can think of a work around (assuming I am right).

    Edward

     

    Thursday, January 13, 2011 1:14 PM
  • Hi Edward,
    I am trying to hit the google map services to get the co-orinates(Latitude & Longitude) of 
    any address. I am executing below sql queries. Its working fine in SQL server 2005 but its giving 
    error in SQL Azure 2008.Plz see below:-(Please suggest some alternate for this)
    DECLARE @Sample TABLE 
    ( 
    RowID INT
    IDENTITY(1, 1) PRIMARY KEY CLUSTERED, 
    Postal VARCHAR
    (50) NOT NULL, 
    ZipCode VARCHAR
    (50) NOT NULL, 
    City VARCHAR
    (50) NOT NULL, 
    Country VARCHAR
    (50) NOT NULL, 
    Status SMALLINT
    , 
    Accuracy TINYINT
    , 
    Lat DECIMAL
    (9, 6), 
    Lon DECIMAL
    (9, 6), 
    CreDate DATETIME 
    )  
     
    INSERT @Sample 
    SELECT 'One Microsoft Way', '98052', 'Redmond, WA', 'USA',null,1,0,0,getdate() UNION ALL 
    SELECT '170 W. Tasman Dr.', '95134', 'San Jose, CA', 'USA',null,1,0,0,getdate() UNION ALL 
    SELECT '500 Oracle Parkway', '94065', 'Redwood Shores, CA', 'USA',null,1,0,0,getdate() 
     
    select * from @Sample 
     
    -- Initialize 
    DECLARE @url VARCHAR(300), 
    @win INT, 
    @hr INT, 
    @Text VARCHAR(8000), 
    @RowID int, 
    @Status smallint, 
    @Accuracy tinyint, 
    @Lon decimal(9, 6), 
    @Lat decimal(9, 6) 
     
    SELECT @RowID = MIN(RowID) FROM @Sample WHERE Status IS NULL 
     
    WHILE @RowID IS NOT NULL 
    BEGIN 
       
    SELECT @url = 'q=' + Postal + '+' + ZipCode + '+' + City + '+' + Country 
       
    FROM @Sample 
       
    WHERE RowID = @RowID 
     
       
    SET @url = 'http://maps.google.com/maps/geo?' + @url 
       
    SET @url = @url + '&output=csv&key={your google api key here}' 
     
       
    EXEC @hr = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @win OUT 
       
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 
     
       
    EXEC @hr = sp_OAMethod @win, 'Open', NULL, 'GET', @url, 'false' 
       
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 
     
       
    EXEC @hr = sp_OAMethod @win, 'Send' 
       
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 
     
       
    EXEC @hr = sp_OAGetProperty @win, 'ResponseText', @Text OUT 
       
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 
     
       
    EXEC @hr = sp_OADestroy @win  
       
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win  
     
       
    SET @Text = REPLACE(REPLACE(@Text, '.', '#'), ',', '.') 
       
    SELECT @Status = PARSENAME(@Text, 4), 
           
    @Accuracy = PARSENAME(@Text, 3), 
           
    @Lat = REPLACE(PARSENAME(@Text, 2), '#', '.'), 
           
    @Lon = REPLACE(PARSENAME(@Text, 1), '#', '.') 
     
       
    UPDATE @Sample 
       
    SET Accuracy = @Accuracy, 
            Lat
    = @Lat, 
            Lon
    = @Lon, 
            Status
    = @Status, 
            CreDate
    = GETDATE() 
       
    WHERE RowID = @RowID 
     
       
    WAITFOR DELAY '00:00:00.010' 
     
       
    SELECT @RowID = MIN(RowID) 
       
    FROM @Sample 
       
    WHERE Status IS NULL 
     
    END 
     
    SELECT * FROM @Sample 
     
    --If Above not executing then first execute below  
    --sp_configure 'show advanced options', 1;  
    --GO RECONFIGURE;  
    -- 
    --GO  
    -- 
    --sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE;  
    -- 
    --GO 
    Friday, January 14, 2011 6:15 AM
  • Hi Kapil,

    SQL Azure prevents you from calling server resources; it is limited to pure transactional commands for the time being. sp_OACreate attempts to create a reference to an OLE object on the server; that's simply not allowed. So you will need to redesign your solution to move this type of logic in a middle-tier of some kind, such as a web role in Windows Azure.

    Herve


    Herve Roggero, Blue Syntax MVP SQL Azure Co-Author: Pro SQL Azure
    Friday, January 14, 2011 8:42 PM