Stored Procedures, Linked Servers, And Cursors

Feb 18, 2006

I've been trying to copy tables from a linked server to a SQL Server
Express database. Express seems to have no direct/automatic way to do
it, so I've been looking into doing this by hand (i.e., with a T-SQL
procedure).

I've discovered some system stored procedures that seem relevant (like
sp_tables_ex and sp_columns_ex). But they're procedures, not
table-valued functions, so I'm not sure if I can actually do anything
with the data they return.

If I could get to the data, I thought maybe I could use a cusor and a
while loop to recreate the tables in Express.

Is this crazy?

Really could use some advice on this.

Thanks,

-Dan

View 1 Replies

Stored Proc Using Cursors On A Linked Server

Jul 13, 2004

As part of a nightly process, I must call a stored procedure that uses cursors to update a Sybase linked server from SQL Server. The stored proc was written by someone else and is intended to interface to a finance system to upload new transactions.

The stored proc works fine most of the time but occasionally dies part-way through with the following confusing error message:

[OLE/DB provider returned message: Internally generated command failed to affect a row.]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IRowsetChange::SetData returned 0x80004005: ].
Msg 7399, Level 16, State 1, Server ********, Procedure ********, Line 162
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.

I initially ran this stored proc from a perl script via an ODBC link but in the futile hope of fixing this problem I tried manually executing it via isql.exe instead. Both return exactly the same error. I have also increased the OLE/DB query timeout to 15 minutes with no success in stopping the problem.

There seems to be no connection I can spot between when it succeeds and when it fails. When scheduled to run just after midnight it fails about 30% of the time (at a time when there should be a light load on the servers). When run manually it almost always suceeds (I have only managed to get it to fail through running manually once in dozens of attempts).

I can't seem to find anything relevant about this error via Google or any of my usual reference sources so hoped someone else might have a clue as to what the hell's going on. :confused:

The stored procedure uses a cursor to traverse a SELECT statement then performs several INSERTs, UPDATEs and DELETEs depending upon each row processed and attempts (but usually fails) to perform a ROLLBACK upon an error.

I'd appreciate any pointers in the right direction here. Is it because I'm performing cursor-based operations on a linked server? Is it due to the linked server being a Sybase server? Could it be due to locking problems or timeouts? Is my server in a bad mood and simply trying to taunt me? I'm tearing my hair out trying to find more information on this annoyingly intermittent problem and really appreciate any help or tales of similar errors.

Thanks in advance

View 11 Replies View Related

Calling Stored Procedures Inside Cursors

Aug 13, 2001

I have created a cursor using the following type of syntax:

DECLARE MyCursor CURSOR FOR
SELECT ID FROM tblEmployees
OPEN MyCursor
BEGIN
FETCH NEXT FROM MyCursor
END
CLOSE MyCursor

Instead of the SELECT statement (SELECT ID FROM tblEmployees), I actually have a very complex select statement. I have created a stored procedure to handle this select. However, I cannot find a way to call a stored procedure in place of the SELECT statement in the cursor. Is this possible? Thanks.

View 2 Replies View Related

Calling Stored Procedures On Different Servers

Oct 4, 2004

I'm using reporting services to build a report and I plan on using a stored procedure to gather my data for the report. My question is:

I know it's possible to call a stored procedure from a stored procedure, both within the same dB and in different dB's, but is it possible to call a SP that's on a different server? My gut says "definitely not. at least not easily." But I wanted to see if anyone had any thoughts on this before I pursue a different course of action.

View 3 Replies View Related

Disappearing Linked Server Stored Procedures

Jul 20, 2005

When I create a stored procedure like the one below and save it,everything seems fine. But when I close Enterprise Manager andre-open it, the stored procedure is gone.Any ideas?SET ANSI_DEFAULTS ONGOCREATE PROCEDURE [dbo].[sp_Test] ASSELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT V.VENDOR_ID,V.VENDOR_NAME FROM VENDOR V')GOTed

View 2 Replies View Related

Extended Stored Procedures -&> Loading Linked Files

Feb 11, 2004

Hello everybody

I actually wrote a stored procedure (in xp_wrapper.dll) that is using a dll (original.dll) which uses a license file (no file extension).... clear? :)

Anyway.

All the required files are placed in the BINN dir of the server.

The problem is now, that original.dll can't find it's license file. It seems, that this file was not load by SQL Server.

How can I load this file into SQL Server's heap?

Yours
Mike

View 1 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!