Skip to main content
  • EN

@steveatsafe

Good morning,

I am having issues with writing to our Azure Database. We have a bunch of features that have correlating Ids. The objective is to find the latest and greatest feature(s) based on a uuid that is assigned to each feature and the features that relate to it.

The SQL query I wrote before worked up until now. Now I receive this error message

 

"com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting from a character string to uniqueidentifier."

 

Unsure why all of sudden this is happening.

 

DECLARE @aprt_id INT = $(Arpt_ID), @address_id UNIQUEIDENTIFIER;

;WITH idetails AS

(

SELECT *, ROW_NUMBER() OVER (PARTITION BY venue_id ORDER BY record_id DESC) AS recency

FROM airport

WHERE ARPT_ID = @arpt_id

)

SELECT @address_id = address_id FROM idetails WHERE recency = 1;

SELECT *

FROM airport, dbo.bbuilding] as building, dbo.butility_pipe] as utility_pipe

WHERE airport.raddress_id] = @address_id AND building.naddress_id] = @address_id AND building.narpt_id] = $(Arpt_ID) AND building.nbuilding_id]=$(Bldg_ID) AND building.nid] = utility_pipe.pbuilding_id]

 

Any assistance would be great!

Thanks!

David

 

At first glance the error seems to be related to SQL Server only, not FME. My first guess would be some issue with the data where SQL Server is unable to cast address_id as a UUID.

What happens if you execute the same query in the MS SQL Server Management Studio and you scroll through the entire result set?

At first glance the error seems to be related to SQL Server only, not FME. My first guess would be some issue with the data where SQL Server is unable to cast address_id as a UUID.

What happens if you execute the same query in the MS SQL Server Management Studio and you scroll through the entire result set?

Hi @david_r,

When I copy the query into sql server, I receive the same error message. What is really odd, I have the same query in other sql creators but with a slight difference at the end, and it works fine.

Hi @david_r,

When I copy the query into sql server, I receive the same error message. What is really odd, I have the same query in other sql creators but with a slight difference at the end, and it works fine.

Difficult (impossible) to tell without knowing your data, but my general recommendation would be to make sure that the exact same query runs successfully in SQL Server Studio first.

Hi @david_r,

When I copy the query into sql server, I receive the same error message. What is really odd, I have the same query in other sql creators but with a slight difference at the end, and it works fine.

Additionally, If I was to remove " AND building.[id] = utility_pipe.[building_id] " from the script, it returns values, but the values are related to the building table not the utility pipe.

Difficult (impossible) to tell without knowing your data, but my general recommendation would be to make sure that the exact same query runs successfully in SQL Server Studio first.

Agree, and I have tested it in SQL Server, the results were correct. But for some reason I am now getting this error. It's weird that it' s not working, when it was for 2 months.

Additionally, If I was to remove " AND building.[id] = utility_pipe.[building_id] " from the script, it returns values, but the values are related to the building table not the utility pipe.

If you change the meaning of the SQL enough to change the resulting data, then naturally that may have an impact on whether the error occurs or not.

I think it's fair to say that this is most probably an issue with the query and how it relates to the data, and not with FME.

If you change the meaning of the SQL enough to change the resulting data, then naturally that may have an impact on whether the error occurs or not.

I think it's fair to say that this is most probably an issue with the query and how it relates to the data, and not with FME.

Thanks for the help! I'll review the query.

Agree, and I have tested it in SQL Server, the results were correct. But for some reason I am now getting this error. It's weird that it' s not working, when it was for 2 months.

That's an interesting bit of information: if it suddenly stopped working, my first hunch would be an issue with the data.

Try validating all the uniqueidentifier type columns, e.g. using something like

SELECT *
FROM   airport
WHERE  TRY_CONVERT(UNIQUEIDENTIFIER, address_id) IS NULL;

The records returned will be those that contain invalid uniqueidentifier values.

Try validating all the uniqueidentifier type columns, e.g. using something like

SELECT *
FROM   airport
WHERE  TRY_CONVERT(UNIQUEIDENTIFIER, address_id) IS NULL;

The records returned will be those that contain invalid uniqueidentifier values.

Thanks. None returned. I've been testing the ids to determine if the ids are indeed UUIDs and they are all return as correct UUIDs.

To add to the information above, I receive these warning lines:

"Semicolons detected in SQL. May need to use FME_SQL_DELIMITER keyword to specify a delimiter. SQL is: DECLARE @aprt_id INT = $(Arpt_ID), @address_id UNIQUEIDENTIFIER;

;WITH idetails AS

(

SELECT *, ROW_NUMBER() OVER (PARTITION BY venue_id ORDER BY record_id DESC) AS recency

FROM airport

WHERE ARPT_ID = @arpt_id

)

SELECT @address_id = address_id FROM idetails WHERE recency = 1;

SELECT *

FROM airport, dbo.[building] as building, dbo.[utility_pipe] as utility_pipe

WHERE airport.[address_id] = @address_id AND building.[address_id] = @address_id AND building.[arpt_id] = $(Arpt_ID) AND building.[building_id]=$(Bldg_ID) AND building.[id] = utility_pipe.[building_id]"

To add to the information above, I receive these warning lines:

"Semicolons detected in SQL. May need to use FME_SQL_DELIMITER keyword to specify a delimiter. SQL is: DECLARE @aprt_id INT = $(Arpt_ID), @address_id UNIQUEIDENTIFIER;

;WITH idetails AS

(

SELECT *, ROW_NUMBER() OVER (PARTITION BY venue_id ORDER BY record_id DESC) AS recency

FROM airport

WHERE ARPT_ID = @arpt_id

)

SELECT @address_id = address_id FROM idetails WHERE recency = 1;

SELECT *

FROM airport, dbo.[building] as building, dbo.[utility_pipe] as utility_pipe

WHERE airport.[address_id] = @address_id AND building.[address_id] = @address_id AND building.[arpt_id] = $(Arpt_ID) AND building.[building_id]=$(Bldg_ID) AND building.[id] = utility_pipe.[building_id]"

You can safely ignore the warning here, it's not related to the error.

At first glance the error seems to be related to SQL Server only, not FME. My first guess would be some issue with the data where SQL Server is unable to cast address_id as a UUID.

What happens if you execute the same query in the MS SQL Server Management Studio and you scroll through the entire result set?

There were issues with the UUID values that were entering the database. Appreciate the assistance!

Reply


Community Stats

31,806
Posts
120,814
Replies
39,430
Members

Latest FME

Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.