cancel
Showing results for 
Search instead for 
Did you mean: 

Passing BOUSER into a SQL Server function

christian_key2
Participant
0 Kudos

Hi all,

I wish to pass the BOUSER variable into my SQL function which returns a 2 column table when it is executed. The SQL I have in a derived table is this.

SELECT UserID, UserGroupID

FROM   fnRptUserGroupSecurity(@Variable('BOUSER'))

However when I validate this SQL in the derived table I get this error message from SQL Server 2014

[Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting from a character string to uniqueidentifier.

The UserID column is of type UNIQUEIDENTIFIER and the parameter that is passed in to the function is of type CHAR(36).

Any ideas about how I can get around this error message in IDT as until it is validated I cannot see the columns in the dervied table to be able to join on them in the .dfx?

Thanks,

Christian

Accepted Solutions (1)

Accepted Solutions (1)

christian_key2
Participant
0 Kudos

Hi all,

I have managed to find a solution to this problem which I will share now.

Basically the solution is to specify the SQL statement as detailed in the original post and ignore the "Conversion failed..." error message that you get when you validate the statement and just save the changes.

What this does is it still creates a derived table in the data foundation layer but you will not be able to see any columns in the data foundation layer to be able to join on graphically. However you can still add a manual join between the derived table and another table by manually typing in the join expression. This join expression will also fail when you validate but this is OK.

If you now publish the changes back (ignoring integrity check errors related to this issue) to the CMS and try running a report you will find that the SQL is generated correctly and that the @Variable('BOUSER') function is replaced with the actual user in the SQL.

Thanks,

Chris

Answers (1)

Answers (1)

PPaolo
Advisor
Advisor
0 Kudos

Hello Christian,

just to make sure that the basics are covered, when you validate the derived table in IDT is your data foundation based on a secured connection?

If not, the @VAriable( 'BOUSER') returns a null.

Also, do you get the same error if you hardcode a username instead of using the @Variable?

Regards

PPaolo

christian_key2
Participant
0 Kudos

Hi PPaolo,

Thanks for your comments. In answer to your questions. Yes the data foundation layer is based on a secure connection.

I have replaced @Variable('BOUSER') with a hard coded user name and the derived table is now valid and can be joined to another table.

So how do I replace hard coded value with @Variable('BOUSER') ands not get the validation error message from SQL server?

Thanks,

Christian

mhmohammed
Active Contributor
0 Kudos

Hi Christian,

As dumb as this may sound, did you try ('@Variable('BOUSER')')? Try that and see what happens. It's just a single quote around @variable.


Thanks,
Mahboob Mohammed

christian_key2
Participant
0 Kudos

Hi Mahboob,

Thanks for your suggestion. I have just tried ('@Variable('BOUSER')') and when I parse/validate this in IDT I get a SQL Server syntax error (Incorrect syntax near 'BoUser').

However I have found a solution to this problem which I will share now in a separate post to the main thread.

Thanks,

Christian

mhmohammed
Active Contributor
0 Kudos

That's great, Christian.


Eagerly waiting for the solution, I expect a similar situation coming to me soon.


Thanks,

Mahboob Mohammed