On 07/28/2015 03:13 PM, Jens Sorensen (Intuii) wrote:
> Adrian>> You are doing a server to server transformation where psqlodbc
> is just one component in the mix. It would be helpful to have the entire
> transformation laid out in schematic for:
>
> Adrian>> Postgres --> psqlodbc ????? --> SQL Server
>
> I’m not sure how to lay this “out in schematic”, but hopefully what
> follows will provide the info you need.
Heavily edited, to get to point:)
I have to believe your problem is in the below. From here:
https://msdn.microsoft.com/en-us/library/ms190312.aspx
"OPENROWSET(BULK...) assumes that, if not specified, the maximum length
of SQLCHAR, SQLNCHAR or SQLBINARY data does not exceed 8000 bytes."
I know you are not using BULK, but I am betting it making the same
assumption when you are not using. Doing some searching I see the 8000
limit coming up often, however I have not found a definitive solution.
You might get an answer faster if you ask this on a SQL Server mailing
list/forum.
As to your cast problems:
https://msdn.microsoft.com/en-us/library/ms187928.aspx
"text and image Data Types
Automatic data type conversion is not supported for the text and image
data types. You can explicitly convert text data to character data, and
image data to binary or varbinary, but the maximum length is 8000 bytes.
If you try an incorrect conversion such as trying to convert a character
expression that includes letters to an int, SQL Server returns an error
message."
Which is what you are trying to do here:
CAST(log.uri AS varchar(max)) AS uri,
>
> -- =============================================
>
> -- Description: Retrieves the HTTP Events Log entries from Untangle
>
> -- =============================================
>
> CREATE PROCEDURE [dbo].[RetrieveHttpEvents]
>
> AS
>
> BEGIN
>
> SET NOCOUNT ON;
>
> declare
>
> @NumRowsChanged int = 0;
>
> declare
>
> @ErrorCode int;
>
> INSERT http_events
>
> SELECT *
>
> FROM openrowset
>
> (
>
> 'MSDASQL',
>
> 'Dsn=PostgreSQL35W;',
>
> 'Select
>
> event_id,
>
> time_stamp,
>
> session_id,
>
> client_intf,
>
> server_intf,
>
> c_client_addr,
>
> s_client_addr,
>
> c_server_addr,
>
> s_server_addr,
>
> c_client_port,
>
> s_client_port,
>
> c_server_port,
>
> s_server_port,
>
> policy_id,
>
> username,
>
> hostname,
>
> request_id,
>
> method,
>
> uri,
>
> host,
>
> c2s_content_length,
>
> s2c_content_length,
>
> s2c_content_type,
>
> adblocker_blocked,
>
> adblocker_cookie_ident,
>
> adblocker_action,
>
> webfilter_reason,
>
> webfilter_category,
>
> webfilter_blocked,
>
> webfilter_flagged,
>
> sitefilter_reason,
>
> sitefilter_category,
>
> sitefilter_blocked,
>
> sitefilter_flagged,
>
> clam_clean,
>
> clam_name,
>
> virusblocker_clean,
>
> virusblocker_name
>
> FROM reports.http_events AS log'
>
> )
>
> END
>
> GO
>
> Adrian>> Remember we are not looking at your set up directly, we only
> have what you provide and the more you provide the better.
>
> I understand. Let me know if there is anything else I can provide you.
>
> Jon>>I believe your problem is that SQL Server is determining how to
> translate the data types. You might have better luck casting the column
> to varchar(max) (or to text)
>
--
Adrian Klaver
adrian.klaver@aklaver.com