Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Modifying Linked Server settings fails.

A while back I've set up a linked server setup and it worked fine for a time.

Recently we've had a stored procedure that uses this Linked Server start failing. I was investigating this issue and found that I'm no longer to modify the Linked server settings.

All of this started when some killed processes appeared in SQL server and their Rollback is stuck at 0% and they do not have an estimated completion time.

Stepping into the properties and pressing the OK button causes the progress spinner to spin indefinitely and never apply the settings. Running the test query from the documentations installation section provides the same result, it just executes indefinitely and does not time out. I'm also unable to cancel it.

I've used the test configuration option in the configuration application and it returns successful. I've recreated the setup on a UAT box and it works perfectly well.

The question is: what could have changed for this to start happening, what ports and protocols does dbamp use when applying linked server settings. I assume that the sql server attempts to connect to SF using the dbamp provider and the configured user name and password.

Is there any difference between the configuration app and it's test dialog and what the SQL provider does or can I assume that if one works the other should work as well?

Does any one know if around 20 failing Rollbacks can be the cause of such behaviour?
1 person has
this problem
+1
Reply
  • What stored procedure are you running that is failing? Can you provide specific commands and message output from those commands? Also, what version of DBAmp are you running?

    Can you also run the Task Manager on the Server where DBAmp is and see if there are a bunch of DBAmp.exe or DBAmpNet2.exe processes running?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • The line that is failing is:

    INSERT #ACCOUNTS () select FROM openquery('+@SF_LINKEDSERVER+','' from Account'')

    the variable simply holds the linked server name. There is no output as this connection seems to run indefinitely and killing it does nothing either.

    No query going to this server is retuning as mentioned above. It cant be cancelled either.

    The version we are using is: 3.4.9 so latest greatest.

    There are no processes with the provided names running on the SQL server.

    I've found some articles that pointer to the openquery causing behaviours similar to this one where a client app closes and the query connections are just left hanging. Could that be it? Some parallel connection count limit being hit? It seems like a lot of people are having issues with similar symptoms (uncancellable queries in the KILL\ROLLBACK state). Like this one:

    https://getsatisfaction.com/dbamp/top...

    sadly mine seems to have the same symptoms but the root cause looks to be different.

    Any ideas would be appreciated. I'm getting closer to having to restart a live server but as you are probably aware that's not a great thing to tell the business I have to do :D
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Oh the query had columns but I've replaced them with brackets comments and they got stripped out so its more like :

    INSERT #ACCOUNTS (|bunch of column names|) select |bunch of column names| FROM openquery('+@SF_LINKEDSERVER+',''Select |bunch of column names| from Account'')

    Sorry.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Did you see our earlier comment on looking for DBAmp processes in the task manager of the server? Can you post a screenshot of that?


    Though probably not related to your issue, I would not use open query and just use a from salesforce...account in the subselect. The only advantage openquery has is that it lets you do soql, which is looks like in your case is not needed.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated