how to kill odbc process in sql

[robsuttonjr]

I have an older odbc driver I am using. When I use openrowset queries against the datasource, it works fine. But if the connection gets interupted, I cannot query the datasource until I stop and restart sql server. So my question is 'how to kill hung odbc connections on sql server?' I looked in processes and found the query but killing this does not release the connection.

Forums rule, pass it on!!!

Rob

 

[philhege]

Rob, you're essentially hosed. We deal with this all the time: once a link server connection hangs, every other link server that depends on ODBC locks up also. The ODBC process is in-process; i.e. if it's hosed you have to re-start SQL. No ifs, ands, or buts.

I was on the line with Product Support Services for almost a month about this. Along the way, MS also told me that they no longer support ODBC connections to external data through SQL link servers, and that they were NEVER intended to manipulate external data, only query it. (Heck, even that blows up every now and then.)

We have done everything we can to abandon link servers to ODBC data. I feel for folks that can't work around this, but the official MS mantra is "DTS your legacy data into SQL Server." (Resistance is futile....)

Good luck,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[robsuttonjr]

Interesting the I have better results pulling from odbc in MS Visual FoxPro than in SQL server. You would think that it would be easier to manage such a crucial part of our job. MS just doesn't seem to care because they don't want us to be able to interact with 'other' databases. They provide the ability to access other data but seem to do only what they have to. I think they just want us to convert all of our 'other' databases to sql rather than make it possible to tightly integrate sql server with other data sources. Perhaps I am just venting but still I can't believe that MS would underdevelop such a critical part of many information system departments jobs. The job of pulling more than one type of data source type together and integrating them. Otherwise SQL server would be near perfect.

Forums rule, pass it on!!!

Rob

 

[philhege]

VFP is exactly what we have the problems with (well, some Pervasive too). We have gigs and gigs of Fox data in legacy systems going back to 2.6. There's no way you can adjust some of these simply to "point" at SQL data. Heck, migrating the data in our MaxFrame app is going to be pain enough, let alone re-architecting run-the-business applications.

It's definitely a Borgian data system.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[robsuttonjr]

We have an old CounterPoint retail software using Pervassive 7 and I use FoxPro exclusively for front end. But I hate hate hate using fox tables or any others for that matter. SQL server is just my bag I guess. Anyway I ended up moving the pervassive engine to SQL server and running the data directly from this SQL Server. I have created a dts to pull the data in and update sql tables. Works like a champ. We will use a table called 'request_update' which a process will check every 5 minutes for date changes. Then kick off the dts if true. This way I don't have to worry about clients needing to request and wait for processes. As soon as the update is finished the clients background process picks up on the change and updates the client from sql. Plus I found that DTS handles disconnecting the odbc sessions. If you are in say enterprise manager or query analyzer you have to exit to see this work. But using OPENROWSET, OPENQUERY, and/or LINKED SERVERS always stayed hung up whether I closed the session or not. (Always have to stop and restart sql engine in this case.) BUT using DTS I get the same type of clean up process that I do in Foxpro. In other words if I kick of a DTS from enterprise manager then close the odbc session in Pervassive monitor, the dts will fail unless I exit the current enterprise manager. But I tested running the DTS vis client and I never have to restart sql engine using DTS method. Sorry to ramble but I hope this could help someone who has lost hair and youth from trying to pull data together into a data warehouse. Its a witch.

Forums rule, pass it on!!!

Rob

 

[philhege]

The bear of my situation is that I have to replicate data in the other direction. Data is maintained primarily in SQL, but additions/changes/deletions must be replicated to FoxPro tables. DTS isn't a good avenue for this.

Our solution was to populate an action queue table, and then call a FoxPro executable with xp_cmdshell. The FP program reads the queue table and queries SQL for the records in question, then updates the FP tables using native Fox methods. No muss, no fuss, and no OPENQUERY/OPENROWSET or DTS merry-go-round.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?