Skip to main content
Solved

SQLCreator issues

D
Forum|alt.badge.img

@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.[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]

 

Any assistance would be great!

Thanks!

David

 

Best answer by david_r

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?

View original
Did this help you find an answer to your question?

13 replies

david_r
Celebrity
  • david_rCelebrity
  • Best Answer
  • March 15, 2019

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?

D
Forum|alt.badge.img
david_r wrote:

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.

david_r
Celebrity
  • david_rCelebrity
  • March 15, 2019
david_prosack88 wrote:

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.

D
Forum|alt.badge.img
david_prosack88 wrote:

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.

D
Forum|alt.badge.img
david_r wrote:

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.

david_r
Celebrity
  • david_rCelebrity
  • March 15, 2019
david_prosack88 wrote:

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.

D
Forum|alt.badge.img
david_r wrote:

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.

david_r
Celebrity
  • david_rCelebrity
  • March 15, 2019
david_prosack88 wrote:

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.

david_r
Celebrity
  • david_rCelebrity
  • March 15, 2019

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.

D
Forum|alt.badge.img
david_r wrote:

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.

D
Forum|alt.badge.img

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]"

david_r
Celebrity
  • david_rCelebrity
  • March 18, 2019
david_prosack88 wrote:

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.

D
Forum|alt.badge.img
david_r wrote:

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


Helpful Members This Week

Community Stats

31,378
Posts
119,016
Replies
39,010
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.