Stored procedure param problem

[wbodger]

So, I have been monkeying around with a stored procedure trying to post into Sales Force.com. I can get the stored procedure to work if I run it manually and fill in the values, but when I pass in the values from the web page into one sproc and then another, something happens. I *think* it has to do with the fact that I am trying to pass a space as one of the param values. So, when I am running the sproc and starting from a web page, how do I insert code within the sproc that will show me where I am at during certain phases of execution? You know, with ASP I can just write the variables to the screen and then end processing, but I don't know how I can accomplish that or something similar with SProcs. Anybody have any great suggestions?

 

[wbodger]

OK, here is the code. First, the call in ASP:

dim cmd
set cmd = Server.CreateObject("ADODB.command")
set cmd.ActiveConnection = oOnyx
cmd.CommandText = "dbo.http_SFDCPost"
cmd.CommandType = adCMDStoredProc
with cmd
        .Parameters.Append .createparameter("@vchFirstName", adVarChar, adParamInput, 30)
        .Parameters.Append .createparameter("@vchLastName", adVarChar, adParamInput, 30)
        .Parameters.Append .createparameter("@vchEmailAddress", adVarChar, adParamInput, 255)
        .Parameters.Append .createparameter("@vchPhoneNumber", adVarChar, adParamInput, 20)
        .Parameters.Append .createparameter("@vchCompanyName", adVarChar, adParamInput, 255)
        .Parameters.Append .createparameter("@chCountryCode", adVarChar, adParamInput, 4)
        .Parameters.Append .createparameter("@chRegionCode", adVarChar, adParamInput, 4)
        .Parameters.Append .createparameter("@CustomerID", adInteger, adParamInput, 10)
        .Parameters.Append .createparameter("@campaignid", adVarChar, adParamInput, 10)
        .Parameters.Append .createparameter("@product", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@description", adVarChar, adParamInput, 255)
        .Parameters.Append .createparameter("@sfga", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@lead_Source", adVarChar, adParamInput, 50)
		.Parameters.Append .createparameter("@retURL", adVarChar, adParamInput, 50)
        .Parameters.Append .createparameter("@oid", adVarChar, adParamInput, 50)

    end with

    cmd.Parameters("@vchFirstName") = FIRST
    cmd.Parameters("@vchLastName") = LAST
    cmd.Parameters("@vchEmailAddress") = email
    cmd.Parameters("@vchPhoneNumber") = companyphone
    cmd.Parameters("@vchCompanyName") = companyname
    cmd.Parameters("@chCountryCode") = Country
    cmd.Parameters("@chRegionCode") = State
    cmd.Parameters("@CustomerID") = CustomerID
    cmd.Parameters("@campaignid") = campaignid
    cmd.Parameters("@product") = product
    cmd.Parameters("@description") = description
    cmd.Parameters("@sfga") = sfga
    cmd.Parameters("@lead_source") = lead_source
	cmd.Parameters("@retURL") = retURL
    cmd.Parameters("@oid") = oid
   
    cmd.Execute()

Then the Sproc:

CREATE  PROCEDURE dbo.http_SFDCPost
(
@vchFirstName varchar(30),
@vchLastName varchar(30),
@vchEmailAddress varchar(255),
@vchPhoneNumber varchar(20),
@vchCompanyName varchar(255),
@chCountryCode varchar(4),
@chRegionCode varchar(4),
@CustomerID varchar(10),
@campaignid varchar(10),
@vchproduct varchar(50),
@vchdescription varchar(255),
@sfga varchar(50),
@lead_source varchar(50),
@retURL varchar(50),
@oid varchar(50)

)
 AS
BEGIN
/*************************************
	process to post data to the SalesForce.com servers

	Runs when it is called, no schedule

***************************************/
SET NOCOUNT ON

DECLARE @vchURLToPostTo varchar(1000),
	@vchHTTPVerbToUse varchar(10),
	@vchAuthorization varchar(100),
	@encoding varchar(50)


DECLARE @post varchar(4000)
declare @ret int

set @encoding="UTF-8"
set @sfga = "0"

BEGIN
	-- now, depending on what kind of notification protocol the SN calls for, we setup the post data
	-- to accomodate it.
	BEGIN		
		SELECT @post = 'encoding=' + @encoding + '&first_name=' + @vchFirstName +'&last_name=' + @vchLastName + '&email=' + @vchEmailAddress +  '&phone=' + @vchPhoneNumber + '&company=' + @vchCompanyName + '&country=' + @chCountryCode + '&state=' + @chRegionCode + '&oid=' + @oid + '&retURL=' + @retURL + '&lead_source=' + @lead_source + '&00N200000019CLw=' + @vchProduct + '&sfga=' + @sfga + '&00N200000019DkC=' + @campaignid + '&description=' + @vchDescription + '&00N200000019De9=' + @CustomerID,
			@vchURLToPostTo = '[URL unfurl="true"]https://www.salesforce.com/servlet/servlet.WebToLead',[/URL]
			@vchAuthorization=NULL,
			@vchHTTPVerbToUse = 'POST'
	END

Print @post

	-- URL encode the post data!
	-- this is a lame implementation of a URL ENCODE.  basically it assumes that the data portion of 
	-- a varialble will never contain '&' or '=' which just isn't always true
	--
	-- we do the ENCODE in this lame way, because it's easier than actually working and writing a full Encoder.
	declare @i int
	declare @ch nchar
	declare @hex varchar(6)
	SET @i = 1
	WHILE @i <= datalength(@post)
	BEGIN
		SET @ch = substring(@post, @i, 1)
		IF @ch not in ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r',
's','t','u','v','w','x','y','z','0','1','2','3','4','5','6','7','8','9',
'-','_','.','!','~','*','',(',')','&','=') 
		BEGIN
			if @ch = ' '
			BEGIN
				-- use alternate RFC1738 encoding -- Spaces are encoded as a "+" not "%20"
				set @hex = '+'
			END
			ELSE
			BEGIN
				SELECT @hex = ASCII(@ch)
				-- converts the number from base10 to base16 (dec to hex)
				exec base_convert @hex OUTPUT, 10, 16
				SET @hex = lower('%'+@hex)
			end

			SELECT @post = stuff(@post, @i, 1, @hex)
			Select @i = @i + datalength(@hex) - 1
		END 
		set @i = @i + 1
	END

	-- ok, now, do the post!
	-- it is likely that the post data if done as a post will need to have dummy variables prepended and appended to the body parameters
	-- something in the interaction with TSQL and the WinHTTP.5.1 object puts a space before and after the body, which causes
	-- some level of parameter mis-naming and variable data corruption.  putting dummy parameters at the beginning and end of the post
	-- data ensures that the Parameter Mis-naming and data corruption is limited to the dummy parameters.
	exec @ret = http_post_noauth @vchURLToPostTo,
		@post OUTPUT

	
END


SET NOCOUNT OFF

-- all done!
return	0

END
GO

And I get the highly informative error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[Microsoft][ODBC SQL Server Driver][SQL Server]encoding=UTF-8&first_name=Willie&last_name=Bodger&email=
willie.bodger@laplink.com&phone=4259526056&company=Laplink&country=
US&state=WA&oid=00D200000006AgW&retURL=noway&lead_source=Inbound Lead - Website&00N200000019CLw=PCmover&sfga=0&00N200000019DkC=0&description=This is a test straight from the web no Onyx.&00N200000019De9=2140628

/products/corporate/testsfdcpost.asp, line 100

Where line 100 is the execute command. If I write out the variables before I post to the sproc, everything looks fine. If I run the sproc manually in Query Analyzer everything works fine, but if I post to the sproc from the web page I get the error above. All of the permissions are set correctly, I have no idea where else to go. I have tried it with and without the dummy variables surrounding the Post data and makes no difference, same error. Any suggestions/thoughts out there?

Willie

 

[wbodger]

Also, the text in the error message is coming from the sproc, so I am really confused now. If I run this manually from Query Analyzer, it works. If I post to the same sproc from a web form it gets into the sproc and then dies with a rather generic error.

 

[ESquared]

If it works in Query Analyzer, try this: run a trace on your SQL Server to capture RPC Completion and try your code again. Stop the trace and find the execution line & column. Copy the text and paste it into QA and run it.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[wbodger]

OK, I red flagged it, sorry about that.

So, here is what I know:

Input 1: web form to sproc http_sfdcpost
Input 2: manual thru opening sproc http_sfdcpost in QA

Thru using fc at the command prompt I have determined that the strings generated are exactly the same.

http_sfdcpost then executes http_post_noauth. If I go with input 2, then everything works. If I go with input 1, then I get the error. When I ran NM it gave me nothing. I will try a trace and see what I find. Thanks for the help.

Willie

 

[ESquared]

So it's a permission problem then!

Is your sql server on a different box than your web server? Are you using integrated authentication on your web server? Is your connection string to your database using a hard-coded password or integrated authentication?

 

[wbodger]

I keep thinking it's a permissions problem, then I get confused. Does SQL server carry thru the permissions of the origination point?

Yes, the SQL Server and web server are discreet boxes and it is a hard-coded dsn, but I can get to the SQL server from the web server, in fact the string in the error is created by the first stored procedure, presumably when the first stored procedure communicates to the second stored procedure.

 

[wbodger]

So, I have 2 stored procedures that I am using, one of which I wrote, one of which my predecessor wrote. My stored procedure hits my predecessor's stored procedure and that one calls the procedure that runs WinHTTPRequest.5.1 with sp_OACreate and sp_OAMethod etc.. So, my connection to the database uses a login whose default database is Master, but my stored procedures are in Onyx. No problem, it has access to Onyx. Now, when I look around a bit more, I see that the user exists in Master, but has no permissions and if I go to the user and look at the properties there, Master is not a database that it has listed as having access to, but when I try to give it access it says that user already exists in the Master database.

Now, this user is the default that our dsn uses, so I hate to go dropping it and then have an issue re-creating it. Any thoughts on how to get around this issue? I am wondering if this is what is causing the problems with my stored procedure.

Willie

 

[wbodger]

Well, I decided to go ahead and write to a temp table and then use a batch call for the post, which works great, until I have the time to pull everything apart and figure out where the permissions fall apart.

wb