Linked Server Via ODBC And Stored Procedure

Mar 11, 2006

Hi

We're trying to use call a stored procedure to update information in a
remote Ingres database. We've linked the server, and can read
information using SELECT * FROM OPENQUERY (..........), but we can't
find a suitable syntax for executing a procedure.

Using SELECT * FROM OPENQUERY and passing the EXEC statement in a
string gives a message about not returning any columns - not surprising
as there aren't any, and trying to execute the procedure more directly
using:-

EXECUTE abrs..vipdba.ats_reader_pi0 ........

Gives the error

Could not execute procedure 'ats_reader_pi0' on remote server 'abrs'.
[OLE/DB provider returned message: Parameter type cannot be determined
for at least one variant parameter.]

Any bright ideas?

Chloe

View 1 Replies


ADVERTISEMENT

Scripting Stored Procedure Add With Linked Server Reference When Linked Server Is Not Available

Jul 18, 2006

Is there a way to bypass the syntax checking when adding a stored procedure via a script?

I have a script that has a LINKed server reference (see below) .

INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.

ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.

PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.

Thanks,

Terry

View 4 Replies View Related

ODBC For MS SQL Server To Access Stored Procedure

Jun 3, 2004

I have a stored procedure written in MS SQL Server2000 which takes argument(OUTPUT) as a cursor, and fills in the cursor with the record from the table.

I have to run this stored procedure from my C application program running in SUN OS2.9 with the help of ODBC calls.

Can anyone guide me through steps as to how to run the store procedure from my C program and receive records with the help of the cursor.

The store procedure is as follows

CREATE PROCEDURE testCursor @xyzCursor cursor varying OUT AS
DECLARE temp CURSOR
LOCAL
FOR SELECT * FROM table
OPEN temp
SET @xyzCursor=temp
RETURN(0)
GO

View 3 Replies View Related

Trigger Executing Linked Server Stored Procedure

Jul 3, 2006

What is the syntax for creating a update trigger and passing the values which were updated to a stored procedure on a linked server?? Specifically need syntax for updated value, as well as the syntax for executing the stored proc on the linked server.

Thank you

View 1 Replies View Related

Issue With Sql Server To Sybase Linked Server Using Stored Procedure

Sep 27, 2007

I have a linked server from Sql Server 2000 to Sybase Adaptive Server 12.5.1.

When i try to call a stored procedure on Sybase from Sqlserver i get the following message:

"could not execute procedure sp_who on remote server 'linked server name'(42000,7212)

command executed from sql server:
exec <linked_server>.<database>..sp_who


i am able to user open openquery for selects and inserts, successfully

Help appreciated

Thanks.

View 4 Replies View Related

Stored Procedure In Query Analyzer Vs Linked Procedure In MS Access

Jan 12, 2007

For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same.

ddave

View 2 Replies View Related

Linked Server - ODBC

Jul 25, 2000

I have a linked server set up to a FoxPro database. I have used the 'Microsoft OLE DB Provider for ODBC' as the Provider name, the datasource name is 'TestFoxPro' and the Catalog is AssetTracker.

I have set it up and I am able to see the table objects in the Enterprise manager, but I am unable to select from the tables in the Query analyzer. I I have tried

select * from Test2.assettracker.dbo.assets

but i get the following error

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.


So i have tried three part name, but then it does not find the object.

Any suggestions??

paull

View 1 Replies View Related

Linked Server To ODBC

Jul 14, 2002

Hi,,
I am trying to create linked server in sql2000 using odbc which point
to foxpro free files.
I tryed a lot but the following errro message is displayed:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]File 'data1020.dbf' does not exist.]


thanks

View 1 Replies View Related

ODBC In Stored Procedure

Jan 17, 2006

Hey guys,I'm trying to get the column names from a table to which I'm connectedthrough OBDC. I don't want to use linked server. I get my data withOpenrowset.I found the SQLDescribeCol and SQLColumns ODBC functions.Can anybody tell me how to use those functions in a SQL Server storedprocedure?Thanks a lot

View 1 Replies View Related

ODBC Linked Server Problems

Oct 27, 1999

I created a Linked ODBC Server named HP3000, I can view the tables in enterprise manager, but I cannot SELECT the server in my SQL statement.
Any pointers would be greatly appreciated.

View 1 Replies View Related

ODBC Provider For Linked Server.

Mar 4, 2008

Gurus,

I cant find any odbc provider from my linked server.(SQL 2005)
I am using MDAC 2.8 SP1 ON WINDOWS XP SP2 on my machine.
What can i do to get odbc provider..?

Please help me to do this

Thanks
Krishna

View 2 Replies View Related

Connecting To A Linked Server With ODBC

Jul 20, 2005

I'm currently trying to access data from a Cache DB using MSSQL. I havelinked the Cache server through an ODBC connection. I can see in the LinkedServerexpansion all the tables in Cache for the File(?) I want to access.Here is my problem: Normally to access a linked sever I would do thefollowing:select *from ServerName.DatabaseName.dbo.TableNameI have triedselect *from ServerName.DSN.dbo.TableNameselect *from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)None have worked (error no such object...)What is the syntax to select data on a linked sever via an ODBC connection?In the Linked server set up I have also check the RPC and RPC Out options...My ultimate goal is join tables in Cache and MSSQL into MSSQL.Any help would be greatly appreciated!Thanks,-p

View 2 Replies View Related

SQL Express Linked Server With ODBC

Nov 5, 2007



I have SQL Server 2005

I have a linked server using an ODBC System DSN setup.
A DB with a view to access on of the table from the Acomba DB.

This view work just fine when I logged in using the Windows Authentification.

If I create a user (bla) with "sysadmin" as server roles
the user bla is the owner of the DB (he also created the DB)
He can't access that view. But can access any tables from the same DB

The error I keep having is the following :

Executed SQL statement : SELECT * FROM OPENQUERY(ACOMBABD, 'SELECT * FROM Customer') AS derivedtbl_1
Error Source: .NEt sqlClient DataProvider
Error Message: Cannot initialize the data srouce object of OLE DB prodiver "MSDASQL" for linked server "ACOMBA"


Major over and out

View 3 Replies View Related

Using Stored Procedure To Read From ODBC ?

Sep 25, 2006

Dear all,

I am using SQL 2000 and would like to dynamically assign ODBC data source to transform data task. Do you have a stored procedure to perform read/write from/to ODBC data source? I would like to input data source and table name.

Thank you and Best regards,

Chaivat

View 5 Replies View Related

ODBC / Oracle Linked Server Problem

Feb 15, 2004

I have a SQL server that I am trying to link to a number of Oracle environments. After much tuning, we managed to achieve this although the four-part naming was not possible and we had to use Openquery and run pass throughs.

Nothing in our configuration has changed and SQL Server is no longer able connect to the linked databases. The Oracle client on the PC is fine and is able tnsping any of the remote databases. I am also able to create ODBC connections to the remote databases on the SQL box that are fine.

Using a datalink in DTS, I can connect to the remote databases. This suggests to me that there is something wrong within the actual database links. I have set them up using the working ODBC DSN's on the SQL box.

If I try and run a query against them in Query Analyser, I get the following error message :

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

If I click on the tables icon in EM to view the remote catalogues I get the following error :

Error 7399: OLE DB provider 'MSDORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].


Any help that could be give on this would be greatly appreciated.

View 3 Replies View Related

Report Based On Stored Procedure - ODBC

Jun 25, 2001

Can anyone tell me if this can be done?

I'm using CR8 against SQL Server 7 and am trying to use a stored procedure as my data source.

Basically my boss would like to move all the code that is now client-side(formula fields, parameters, suppressions, etc.) to the server-side.

I connect by Database>ODBC and then choose my sp here.
I get the error: "There are no fields in the file"
All my sp is doing is accepting two parameters: a report type and a user name, and then generating a report based on this data.
I can post the exact sp but it is a pretty long IF THEN ELSE block.

I checked the Seagate site and it said to convert the database driver to the native driver;
I guess this would mean to connect by: Database>More Data Sources>Microsoft SQL Server
But we need to connect by ODBC since we don't want the popup for the Login to Database, Server, etc., to be entered by the client.

Can someone tell me if there is a way around this to connect by ODBC using stored procedures.

Thanks in Advance.

View 1 Replies View Related

Performance Issues With Linked Server's With OLEDB && ODBC

Jan 6, 2004

Hello,
I have a SQL Server instance on my local computer and an Oracle
Database on a remote server. I want to run queries from tables
within both databases and am using linked servers to accomplish
this.

I configure my linked server in SQL Server using the Microsoft
OLE DB Provider for Oracle and can run queries using sql server
tables and oracle tables. However, even the simplest queries
take more than 10 minutes to run.
I have the Oracle 9 Client Installed and MDAC 2.7. I configured
my registry settings to match oracle 9's settings.
However nothing i do improves the performance of the queries
through the Microsoft OLEDB Provider for Oracle.
When I use MS Access, or use an ASP page with the following

string:
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "dsn=Oberon;uid=mfs;pwd=mfs;"

I implement the ODBC driver that I configured in my system DSN
and both run the same queries very fast. The data comes back
without a problem.

So i believe I have narrowed down my problem to the OLEDB
Provider. However, SQL Server does not give me a choice to use
the ORACLE native ODBC Provider.

So then I tried using Pass-Through Queries and this worked alot
faster in SQL Server...I am completely confused as to whats
going on.

Linked Server Query that takes over 10 minutes:
SELECT * FROM OBERON..LOGS.DATA_PHOTO ldp where Machine=301 AND C3='I051097';

Pass-Through Query that works faster:
select * from Openquery(OBERON, 'SELECT * FROM LOGS.DATA_PHOTO ldp where MACHINE=301 AND C3=''I051097''')

From researching pass through queries, my understanding is that
it actually uses ODBC to give the whole query to the remote
database where the query is then run and the results are passed
back as a table, thats why you say select * from (query)...however if my understanding is correct, then you cant combine tables in different databases very easily. And will it work with .asp and .aspx pages?

Anybody have any insight as to whats going on?

View 1 Replies View Related

Linked Server To Oracle Via ODBC, Return Less Records

Apr 22, 2006

I have an Oracle 9i server. To access the data in Oracle, I setup an ODBC connection to it and am able to return data from it using WinSQL (a general ODBC database client). The SQL statement is simply "SELECT * FROM COLOR" and all 133 records returned properly.

I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above.

The problem is: only 32 records returned.

May I ask if there is any problem to this linked server setup?

Thanks.

View 4 Replies View Related

Queries Against Linked Server To Informix Via ODBC Errors

Oct 9, 2007

What can be the reason(s) why I can't get data from a linked server using an ODBC datasource that works fast & fine from MS Access?


I have an ODBC connection (System DSN) configured for an Informix ODBC driver.
The Test button (belonging to this driver-setup) reports a successfull connection test.
Getting data from this database by linking tables in MS Access works fast and easy.
But I have tried for many days now to setup a linked server from SQL Server (2005)
Creation goes fine, but as soon as I issue a query, (e.g. 'select * from infrem723...remotetable' or using 'openquery')
I get the following error:
---

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "infrem723" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "infrem723".

---

Why is Access able to read what "MSDASQL" cannot?
I am desperate - can anybody help?Thanks a lot!

View 8 Replies View Related

ODBC Stored Procedure Call Rolls Back

Apr 18, 2008

Hi,

I have a ms access application with a button. Clicking this button runs a sql server 2000 stored procedure. The stored procedure updates product information. When I click the button it updates the products fine. When the users (with lesser permissions than me) click the button, no error message is sent but the transactions are rolled back. Does anyone know why or how this could happen? Please let me know before I pull out the last strands of hair from my head.

Thank you,
Anand

View 4 Replies View Related

Return Records Through A Linked Server That Uses ODBC Data Source

Jul 20, 2005

Hi,I used sp_addlinkedserver to link to a remote server through ODBC.When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL QueryAnalyzer. It returns 13705 records. But when I execute select * fromLinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify somecolumns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.It returns more records, 1213 records.I guess there is something limit the return storage, but I can notfind it.Any suggestion will be appreciated. Thank you

View 3 Replies View Related

Newbie: Why Do I Have To Use OPENQUERY To A Linked Server? (ODBC To Firebird Database)

Jan 17, 2008

Hello,

pls. let me know where I could post if this is the wrong place.

I have a Firebird 1.5 application. I created a linked server from my SQL
Server 2000 to the firebird database. In SQL Server Query Analyzer I get errors from various ODBC drivers with "normal" queryies like

SELECT LVNR FROM LINKEDSRV...LVVERW

Pls. note, this all works perfectly in MS Access databases with ODBC-Links to Firebird!


From a programmer of a commercial ODBC driver I heard that this problem may be caused internally by SQL Server, there may be no solution possible in the ODBC driver. One workaround would be to use the OPENQUERY-Syntax like

SELECT * FROM OPENQUERY(LINKEDSRV, 'select LVNR from LVVERW ')

Are there any other solutions? Are there any known issues with firebird odbc-drivers and sql server? Are
there any known good drivers for the use with sql-server? What is the purpose of OPENQUERY - workaround ODBC problems? Are there any settings in SQL Server 2000 (2005 Express) that could help? Are there any settings in ODBC DSN that would help?

regards

arno


PS: Here are my favorite error messages

Error -2147217900 [OLE/DB provider returned message: Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 89
"Col1014"] (Source: Microsoft OLE DB Provider for SQL Server) (SQL State:
01000) (NativeError: 7312)Error -2147217900 OLE DB-Fehlertrace [OLE/DB
Provider 'MSDASQL' ICommandPrepare:repare returned 0x80004005: ].
(Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 01000)
(NativeError: 7300)Error -2147217900 Der OLE DB-Provider 'MSDASQL' meldete
einen Fehler. (Source: Microsoft OLE DB Provider for SQL Server) (SQL
State: 42000) (NativeError: 7399)

This "tricky" query does not work:
SELECT LVNR FROM LINKEDSRV...LVVERW;

Error -2147217900 OLE DB-Fehlertrace [Non-interface error: Column
'ERHALTENABSCHLAG' (compile-time ordinal 35) of object 'LVVERW' was reported
to have a DBTYPE of 5 at compile time and 131 at run time]. (Source:
Microsoft OLE DB Provider for SQL Server) (SQL State: 01000) (NativeError:
7300)Error -2147217900 Der OLE DB-Provider 'MSDASQL' hat inkonsistente
Metadaten für eine Spalte übergeben. Die Metadateninformationen wurden zur
Ausführungszeit geändert. (Source: Microsoft OLE DB Provider for SQL
Server) (SQL State: 42000) (NativeError: 7356)

View 2 Replies View Related

Extended Stored Procedure - ODBC Loopback Connection Problem

Jul 23, 2005

Hi,I have a loopback connection using ODBC in the DLL initialization codeofthe SQL Server ESP Module (SQL Server 2000). The loopback connectionworksfine when the DSN is specifed with the "NT Authentication", however thesamefails when specified with the "SQL Server user authentication". I havetriedusing both the SQLConnect and SQLDriverConnect calls, butu none of themworks. Also the same code works fine on SQL Server 2005. Is this aknownproblem with some fix, or am I doing something wrong here??The code is as given below,// ESPODBCLoopback.cpp : Defines the entry point for the DLLapplication.//#include "stdafx.h"#include <sql.h>#include <sqlext.h>#include <srv.h>#define XP_NOERROR 0#define XP_ERROR 1#define SEND_ERROR(szMessage, pServerProc) { srv_sendmsg(pServerProc, SRV_MSG_ERROR, 20001, SRV_INFO, 1, NULL, 0, (DBUSMALLINT) __LINE__, szMessage, SRV_NULLTERM); srv_senddone(pServerProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0); }// typedef const char* (_MakeODBCConnection)(void);static const char* _szMessage = "ODBC Working out....";void_MakeODBCConnection(void){char szConnOut[1024];SQLSMALLINT nOut = 0;const char* szDSNName = "TestOdbc";const char* szUsername = "test";const char* szPassword = "test";SQLHANDLE hEnvironment = NULL;SQLHANDLE hDBConnection = NULL;if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,&hEnvironment)) {_szMessage = "Failed to create the environment handle";return;}SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,SQL_IS_INTEGER);if (SQL_ERROR == SQLAllocHandle(SQL_HANDLE_DBC, hEnvironment,&hDBConnection)) {SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "Failed to create the database connection";return;}/*----------------- This is where it fails -------------------*//* Tried both the with/Without database name */if (SQL_ERROR == SQLDriverConnect(hDBConnection, GetWindow(,(SQLCHAR*)"{DSN=TestOdbc;UID=test;PWD=test;DATABASE=test;}", SQL_NTS,(SQLCHAR*)szConnOut, sizeof(szConnOut), &nOut, SQL_DRIVER_COMPLETE)){SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "Failed to connect to the database";return;}/*if (SQL_ERROR == SQLConnect(hDBConnection, (SQLCHAR*)szDSNName,SQL_NTS,(SQLCHAR*)szUsername, SQL_NTS, (SQLCHAR*)szPassword, SQL_NTS)) {SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "Failed to connect to the database";return;}*/SQLFreeConnect(hDBConnection);SQLFreeHandle(SQL_HANDLE_DBC, hDBConnection);SQLFreeHandle(SQL_HANDLE_ENV, hEnvironment);_szMessage = "ODBC Connection cycle completed successfully";}BOOL APIENTRY DllMain( HANDLE hModule,DWORD ul_reason_for_call,LPVOID lpReserved){switch (ul_reason_for_call){case DLL_PROCESS_ATTACH:_MakeODBCConnection();break;case DLL_THREAD_ATTACH:break;case DLL_THREAD_DETACH:break;case DLL_PROCESS_DETACH:break;}return TRUE;}static void_CheckODBCConnection(void){//_MakeODBCConnection pFunction = NULL;//_szMessage = pFunction();}extern "C" __declspec(dllexport)RETCODE xp_test_odbc(SRV_PROC *pServerProc){//_szMessage = _MakeODBCConnection();if (FAIL == srv_paramsetoutput(pServerProc, 1, (BYTE*)_szMessage,(ULONG)strlen(_szMessage),FALSE)) {return XP_ERROR;}return XP_NOERROR;}Thanks,Anil KumarArizcon Corporation ( http://www.arizcon.com )

View 6 Replies View Related

ODBC Connection With Stored Procedure On ISeries As Data Source

Mar 6, 2006

I have created a stored procedure on the iSeries that creates a cursor and opens it.  I am trying to write my report to use the stored procedure. I cannot get the data source to work.  How do I create my data source so that it uses the stored procedure?  My SP has three parameters I am trying to pass from the report.  The parms are created in the layout.

Thank you

View 6 Replies View Related

ODBC Problems In Linked Server In SQL Server 2005

Feb 19, 2008

Actually i have linked server in SQL 2000 with Provider:Microsoft OLEDB for ODBC which is working perfectly fine. I don't know the password for this linked server too.

Now i want to create same linked server with same provider in SQL server 2005 for Microsoft oledb for ODBC but they don't have this provider in 2005. How can i make it to work.ANy expert who knows this.

and how to use same password in linkedserver in 2005

View 12 Replies View Related

Bad Performance In Queries With Jet4.0 And Linked ODBC-tables To SQL-Server 2000

Jul 20, 2005

I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas

View 6 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

Linked Server Procedure Vs. T-SQL

Dec 11, 2007



Hello,

I'm very boggled as to what is going on with my procedure.

I have a procedure that is using linked tables and when I execute it, it take approximately 45 seconds to run it.

However, If i take the exact piece of code and run it in query analyzer with the parameters put in manually,
then the whole thing takes less then 10 seconds. I don't understand. Has anyone run into something similar?
Am I doing somethign wrong? What can I do to speed it up.

I tried setting the value to true for sp_serveroption for that particular linked server, but when I did that, it took over 1 minute to run.

This is so mind boggling. I've checked all the indexes, statstics and restarted both of the instances and still not sure what is going on.

Any one know what i can do to resolve this issue?

View 1 Replies View Related

ODBC Linked Tables

Mar 23, 2001

In order to use Access 97 as your frontend and SQL 7 as your backend on a
network (where the frontend is located on the same computer as the backend and
people just map to Access frontend) where security for the data is recognized
by your domain login account and what domain group you belong to..do you
have to have the same System DSN on everyone's computer for linking? I am
still having problems with people linking to the SQL server through the network and only my account is working correctly from my desktop. Everyone
else who tries to link gets an ODBC failure error message when trying to
open one of the linked tables. I have heard from one person that you must
have the same system DSN on every client's computer but to me that makes no
sense as I do not have the DSN listed on my individual computer...I am just
going through the network as myself..not as an admin..and getting directly
to the data. Everyone else can get to the shared folder on the server but
cannot get to the linked tables. Any ideas?

View 2 Replies View Related

Linked Server Procedure Call

May 30, 2013

I am trying to call a DB2 procedure in SQl server through a linked server object. I use the IBMDADB2 provider for the linked server. on the DB2 I have a simple procedure with 2 in and 1 out paramater. on DB2 it works no problem.but in SQL server I just cannot get it to work.

so my code:
DB2IBM is the name of the linked server.

declare @I_DIS smallint
declare @I_UID char(8)
declare @ID_TICK int

alternative 1:
Exec ('Call btp.GET_TICK(?,?,?)',@I_DIS, @I_UID, @ID_TICK OUTPUT) AT DB2IBM

alternative 2:
EXECUTE DB2IBM..BTP.GET_TICK 2,'19', @ID_TICK

in both cases I got the same error:

OLE DB provider "IBMDADB2.DB2COPY1" for linked server "DB2IBM" returned message " CLI0100E Wrong number of parameters. SQLSTATE=07001".

so how can I call this procedure?

View 5 Replies View Related

ODBC Linked Tables Go Read Only

Jun 14, 2007

Hi,
I have an Access database Front End which use SQL server as a Back End. The two are connected using ODBC. Occasionally, some of the linked tables in Access go read only. I can't add or edit records. The only way I know to get round the problem is to delete the link and re-create it. Refreshing the link does not work. Can anybody suggest why this would happen, and the best way to fix it when it does occur?

Edited 12:10 06/14/07
Some extra info. At the same time it goes read only, the link loses it's primary key.


Thanks

Colin

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved