0

I'm trying to run a stored procedure that is linked to two servers. The stored procedure runs perfectly from SSMS but it will not run from my VB.Net code. If I remove the reference to the linked server, it runs correctly. However, I need the linked server reference in the stored procedure.

The 4 part reference is set up correctly. The linked server connection is set up correctly. We seem to think it's the data connection in VB but we've tried everything but we keep getting a login-mapping error.

The connection string is below. We basically pull from an XML file where our IP addresses and login information are stored. That's the "/Settins/Catalogs..." variable.

Public Shared Function GetConnString(ByVal sConn As String) As SqlClient.SqlConnectionStringBuilder        
    _dbServer = GetBakerGlobalSettingsValue("/Settings/Catalogs/SNL/DataSource")
    _dbLoginName = GetBakerGlobalSettingsValue("/Settings/Catalogs/SNL/UserId")
    _dbLoginPassword = GetBakerGlobalSettingsValue("/Settings/Catalogs/SNL/Password")
    _InitialCatalog = "SNL"

    Dim sbConnString As New SqlClient.SqlConnectionStringBuilder
    sbConnString.DataSource = _dbServer
    sbConnString.InitialCatalog = _InitialCatalog
    sbConnString.UserID = _dbLoginName
    sbConnString.Password = _dbLoginPassword
    sbConnString.IntegratedSecurity = False
    Return (sbConnString)
End Function

Dim aDate As Date = "2010-06-30"
Using sqlCommand As New SqlCommand("VerifyPortfolioFedIds '" & aDate & "'", connPort)
    sqlCommand.CommandTimeout = 300
    Dim da As SqlDataAdapter = New SqlDataAdapter() With {.SelectCommand = sqlCommand}
    Dim dt As DataTable = New DataTable
    da.Fill(dt)
    MsgBox(dt.Rows.Count.ToString)
End Using
Andrew Morton
  • 24,203
  • 9
  • 60
  • 84
Sean M
  • 1
  • 2
  • 3
    "it just won't work" is not very much info. How is it not working? How are you calling the stored procedure? How you generate the connection string doesn't tell us much. – Glorin Oakenfoot Jul 06 '16 at 17:25
  • Same user each time? Same transaction state? – JimmyB Jul 06 '16 at 17:26
  • 2
    When it works in SSMS, have you logged in with the same credentials as your program uses? [Security for Linked Servers](https://technet.microsoft.com/en-us/library/ms175537(v=sql.105).aspx) – Andrew Morton Jul 06 '16 at 17:32
  • Sorry, I edited the "just won't work" line. We're getting a login-mapping error. Here's the call: Dim aDate As Date = "2010-06-30" Using sqlCommand As New SqlCommand("VerifyPortfolioFedIds '" & aDate & "'", connPort) sqlCommand.CommandTimeout = 300 Dim da As SqlDataAdapter = New SqlDataAdapter() With {.SelectCommand = sqlCommand} Dim dt As DataTable = New DataTable da.Fill(dt) MsgBox(dt.Rows.Count.ToString) End Using The credentials are the same and good. – Sean M Jul 06 '16 at 17:35
  • 1
    You should use an SQL parameter to pass the date, and set `sqlCommand.CommandType = CommandType.StoredProcedure` as shown in [How to use a DataAdapter with stored procedure and parameter](http://stackoverflow.com/a/3528647/1115360). – Andrew Morton Jul 06 '16 at 17:47
  • I don't need to set those things because they're set on the Using line. – Sean M Jul 06 '16 at 20:49

0 Answers0