Question Remote exec of SQL stored procedure

[MikeSel]

Hello

We are working on a VB.Net project to execute a SQL Stored Procedure and passing it parameters. We have the execution up and working as expected, however the application will only successfully execute once. On the second execution, it throws the following error:

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

For readability I have broken the code down into relevant 'chunks'...

For the DB connection:

connectionString = "Data Source=<server>;Initial Catalog=<database>;Integrated security=sspi"

For the execution:

24:     sqlconn = GetConnect()
25:     sqlconn.Open()
26:     sqlcmd_sp = sqlconn.CreateCommand
27:     sqlcmd_sp.CommandTimeout = 6400
28:     sqlcmd_sp.CommandText = "sp_MonthlyTrends " & vSalesperiod & ", '" & vDate & "'"
29:     sqlcmd_sp.ExecuteNonQuery()

As mentioned, on first run - this works without issue. However future executions fail with the error noted above. To clear the error we have to open the stored procedure in Ms SQL Management Studio, click to modify it, then chose execute from the tool bar. Obviously this doesn't actually execute the stored procedure or return results, but for some reason seems to clear the error.

The stored procedure does collate data across several SQL servers, so I am guessing its the 'hop' between them that's causing the issue.

Any help would be appreciated.

Thanks
Mike

 

[cjard]

If you run it in SSMS twice, does it also display this behaviour?