We help IT Professionals succeed at work.

T-SQL and CLR parameter strings

502 Views
Last Modified: 2017-04-26
I have an assembly I added to SQL
It has 4 parameters in the vb code that are all string


In SQL Server I added the assembly...
But when I try and run this...
CREATE PROCEDURE [dbo].[HelloEveryware] (@user VARCHAR(100), @token VARCHAR(100), @PID VARCHAR(100),@ProspectID VARCHAR(100) )
--RETURNS BIT WITH EXECUTE AS CALLER
AS EXTERNAL NAME [HelloEveryware].[HelloEveryware.PassIn].[Confirmation];

I get this message
Msg 6552, Level 16, State 3, Procedure HelloEveryware, Line 1
CREATE PROCEDURE for "HelloEveryware" failed because T-SQL and CLR types for parameter "@user" do not match.

the VB side of this is...

Partial Public Class PassIn
    <Microsoft.SqlServer.Server.SqlFunction>
    Public Shared Function Confirmation(user As String, token As String, PID As String, ProspectID As String) As Int32
Comment
Watch Question

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
I believe you want that to be a sub and not a function.

Currently Confirmation looks like a scalar function from the SQL side.
Larry Bristersr. Developer

Author

Commented:
Hey Kyle...
I was able to get it to resolve by changing things to NVARCHAR

CREATE FUNCTION [dbo].[HelloEveryware] (@user NCHAR(100), @token NCHAR(100), @PID NCHAR(100),@ProspectID NCHAR(100))
RETURNS NCHAR(255) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [HelloEveryware].[HelloEveryware.PassIn].[Confirmation];

Open in new window


Now I am getting the message at the end on this select...
Select dbo.HelloEveryware( 'User', 'Token', 'PID', 'ProspectID')

Open in new window


Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
you have to grant permissions on the assembly:

eg:
grant external access assembly to [user]
grant external access assembly to sa

Open in new window

Larry Bristersr. Developer

Author

Commented:
Kyle,
 This assembly is in...
SELECT * FROM [TangoCRMCTCGGV5].sys.assemblies

When I run this...
GRANT EXTERNAL ACCESS ASSEMBLY TO lbrister

I get this message
Permissions at the server scope can only be granted when the current database is master

When I run from master... error as well?
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
Ensure CLR is enabled:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Open in new window


ensure your project is built with external access (or safe) and that the assembly was created that way.  (You can right click -> script assembly).
Larry Bristersr. Developer

Author

Commented:
Ran all of that..
When I do a select on sys.assemblies I get...
helloeveryware, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
SAFE_ACCESS

When I resistall the assembly and function and run I am still getting...
 Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.              

Then I try and grant permissions... and get this.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Senior .Net Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Larry Bristersr. Developer

Author

Commented:
I'll try in the morning when I return to my desk
Larry Bristersr. Developer

Author

Commented:
Sorry for the very late get back... actually took my first vacation in 5 years.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.