Executing a stored procedure that uses linked server from vb.NET

22 Jun 2006 12:52 PM

Mikael.Bergstrand

I can't execute my stored procedure from .NET (I think it might be
because the stored proc has joins in it via linked servers). I get
timeout error from .NET

MyBase.connString ="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DB;Data Source=Server;"

MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
MyBase.GetDataSet()

The stored procedure takes the 3 arguments and uses joins to 2 other
db-servers that I have access to via linked server connections. (MS SQL
Server)

I can run the stored proc on in QA on my MS SQL with no prob.

Must i open connections to the 2 linked servers from .NET as well?

Glad for any opinions so i dont have to rewrite my sp,

Mikael

22 Jun 2006 1:03 PM

bermik

....or does it have something to do with permissions on the linked
server? Maybe .NET does not have access to the linked servers? On the
other hand I thought that .Net would only start the sp as is, with the
same result as if I started it in QA...

/Mikael

22 Jun 2006 1:48 PM

bermik

....I wrote a smaller sp to test if it was the linked server that made a
mess. But it turns out that it worked out fine. Therefore it is not a
matter of login or linked server. I figure it might be the fact that
the sp takes about 15 min to execute. I have tried to set the
..cmdTimeout = 15000. Is there a way to set the connection time also?

/Mikael

22 Jun 2006 2:07 PM

bermik

.......and timeout problem it was!

(MyBase.cmdTimeout = 15000)

topic.close() //Mikael

22 Jun 2006 2:12 PM

bermik

.......and timeout problem it was!

(MyBase.cmdTimeout = 15000)

topic.close() //Mikael