sql-server-2005 - sql server - Google maps api 返回太多数据

Question

我正在使用 sql server 调用 google maps api 并获取 gps 位置的数据。它工作正常，但最近我调用的存储过程因此错误而失败

0x8004271F 
ODSOLE Extended Procedure 
The source data specified for this string or binary column or parameter is too long.

当 google 返回“大量”位置数据时，似乎已达到字符数限制。

这就是我到目前为止所说的:

-- Select dbo.fnGetAddressFromGPS('33.83528299,-118.28754585')  

-- -- GO
Declare @GPS VarChar(50)
Set @GPS = '34.55,-118.50'
DECLARE @URL VarChar(4000)
DECLARE @win int 
DECLARE @hr  int 
DECLARE @text varchar(8000)

Set @URL = 'http://maps.googleapis.com/maps/api/geocode/xml?latlng=' + @GPS + '&sensor=false'


Set @URL = 'http://maps.googleapis.com/maps/api/geocode/xml?latlng=34.5566767,-118.50677688&sensor=false'
Set @URL = 'http://maps.googleapis.com/maps/api/geocode/json?  latlng=34.5566767,-118.50677688&sensor=false'

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 OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win 
--IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 



Select @Text As 'GoogleResult'
Return



IF CHARINDEX('ZERO_RESULTS', @text) > 0
Select 'No Address Found'


Declare @i Int
Declare @j Int
Declare @Address VarChar(150)

SELECT @i = CHARINDEX('<formatted_address>', @Text);
SELECT @j = CHARINDEX('</formatted_address>', @Text);

SELECT @i = CHARINDEX('"formatted_address" : "', @Text);
Select @i
Return


If @j-@i-Len('<formatted_address>') <= 0 
Select @Address = 'N/A'
Else
Select @Address = SubString(@text, @i+Len('<formatted_address>'), @j-@i-    Len('<formatted_address>'))

--Select @Address

Select @Address  

我想做的是将来自 google 的返回数据限制为仅格式化的地址部分，这是我真正需要的，但我还没有在文档中找到任何关于如何做这样的事情的地方。我唯一能想到的另一件事就是截断结果数据，但我现在也想不通。任何帮助将不胜感激。

Answer 1

这里有趣的部分是插入表格:

将@resTbl 声明为表(结果 NVARCHAR(MAX))

INSERT @resTbl EXEC @HttpWinRequest=sp_OAGetProperty @win, 'ResponseText'

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.usp_http_get
    /*
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
*/
    @url VARCHAR(8000)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @win int, @HttpWinRequest  int

DECLARE @resTbl  AS TABLE (result NVARCHAR(MAX))



EXEC @HttpWinRequest=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT

IF @HttpWinRequest <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @HttpWinRequest=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'

IF @HttpWinRequest <> 0 EXEC sp_OAGetErrorInfo @win EXEC @HttpWinRequest=sp_OAMethod @win,'Send'


IF @HttpWinRequest <> 0 EXEC sp_OAGetErrorInfo @win

INSERT @resTbl EXEC @HttpWinRequest=sp_OAGetProperty @win, 'ResponseText'

IF @HttpWinRequest <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @HttpWinRequest=sp_OADestroy @win

IF @HttpWinRequest <> 0 EXEC sp_OAGetErrorInfo @win

SELECT * FROM @resTbl
END
GO

EXEC usp_http_get @url = 'http://www.yahoo.com' -- varchar(8000)