Using ODBC in a trigger
in [Programming SQL]
|
From: shank on 7 Aug 2010 14:54 Can someone point me in the right direction on how to connect 2 tables, in 2 different SQL Servers, with ODBC? I have no idea what the syntax would be for the connect. ON INSERT into Table1, I need to INSERT that record into Table2 on the remote SQL Server. thanks!
From: Tom on 7 Aug 2010 15:45 On Aug 7, 2:54 pm, "shank" <sh...(a)tampabay.rr.com> wrote: > Can someone point me in the right direction on how to connect 2 tables, in 2 > different SQL Servers, with ODBC? I have no idea what the syntax would be > for the connect. > > ON INSERT into Table1, I need to INSERT that record into Table2 on the > remote SQL Server. > > thanks! How ancient are the versions of SQL server you are using. If it is later than 7.0 why don't you use a more modern technology than ODBC. In SQL Server Management Studio on the server with the table that has the trigger set up a Linked Server to the other SQL Server. Don't know if ODBC is still available. INSERT INTO LinkedServerName.DatabaseName.SchemaName.TableName ( col1, col2 etc.) SELECT col1, col2 etc. FROM INSERTED;
From: shank on 7 Aug 2010 17:43 "Tom" <tom.groszko(a)charter.net> wrote in message news:3676ba1e-671f-4ca7-b186-378fcb173849(a)q35g2000yqn.googlegroups.com... On Aug 7, 2:54 pm, "shank" <sh...(a)tampabay.rr.com> wrote: > Can someone point me in the right direction on how to connect 2 tables, in > 2 > different SQL Servers, with ODBC? I have no idea what the syntax would be > for the connect. > > ON INSERT into Table1, I need to INSERT that record into Table2 on the > remote SQL Server. > > thanks! How ancient are the versions of SQL server you are using. If it is later than 7.0 why don't you use a more modern technology than ODBC. In SQL Server Management Studio on the server with the table that has the trigger set up a Linked Server to the other SQL Server. Don't know if ODBC is still available. INSERT INTO LinkedServerName.DatabaseName.SchemaName.TableName ( col1, col2 etc.) SELECT col1, col2 etc. FROM INSERTED; - - - - - - - - - - - - - - - - - - - - - - - - - First time I'm trying to link a server and not having much joy. SQL 2005 Databases > Server Objects > Linked Servers Right-click Linked Servers and get 2 options: SQL or Other Data Source Option 1: Selected SQL Linked Server: SQL27.Web.com (ficticious) ....result: no connection. Cannot run a select statement on a table. Option 2: Other Data Source Linked server: SQL27 Provider: Microsoft OLE DB Provider for SQL Product Name: SQL Server Data Source: SQL27.Web.com Provider String: Catalog: MyDatabase Assuming I did get the above right, what provider string are they asking for? thanks!
From: Sylvain Lafontaine on 7 Aug 2010 20:39 > First time I'm trying to link a server and not having much joy. > SQL 2005 > Databases > Server Objects > Linked Servers > Right-click Linked Servers and get 2 options: SQL or Other Data Source > > Option 1: Selected SQL > Linked Server: SQL27.Web.com (ficticious) > ...result: no connection. Cannot run a select statement on a table. Probably a permission problem, you must also fill up the Security and Server Options pages that you can acces from the menu at the left; see: http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm > Option 2: Other Data Source > Linked server: SQL27 > Provider: Microsoft OLE DB Provider for SQL > Product Name: SQL Server > Data Source: SQL27.Web.com > Provider String: > Catalog: MyDatabase > > Assuming I did get the above right, what provider string are they asking > for? Some provider like MSDASQL requires a Provider String because they are not a provider by themselves (MSDASQL is used to connect to an ODBC provider from an OLEDB Client) or because you must specify other parameters. Leave it blank. -- Sylvain Lafontaine, ing. MVP - Access Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French)
From: Erland Sommarskog on 8 Aug 2010 05:37 shank (shank(a)tampabay.rr.com) writes: > Can someone point me in the right direction on how to connect 2 tables, > in 2 different SQL Servers, with ODBC? I have no idea what the syntax > would be for the connect. > > ON INSERT into Table1, I need to INSERT that record into Table2 on the > remote SQL Server. The syntax for the trigger is simple, almost too simple: INSERT THATSERVER.db.dbo.tbl(...) SELECT .... Configuring the linked server and getting the whole thing to work is a lot more difficult. Not to say that it may be about impossible. If the other server is another SQL Server in the same domain, it may be easy. Just define the linked server with: exec sp_addlinkedserver THATSERVER But if you have a workgroup, the server is in a different domain it may be more difficult to get it do work. (I have not been successful myself.) And if the data source is not SQL Server, there is an even greater challenge. It's worth saying that there is an increased level of difficulty here: 1) Just running a plain SELECT against the remote data source. 2) An independent INSERT/UPDATE/DELETE against the remote data source. 3) An INSERT/UPDATE/DELETE within a transaction (which you have in a trigger) against the remote data source. The first point is usually possible to succeed with, although it can take some tears to get there. The second is uaually also possible. But the third... No, that is not a game for kids. The conclusion of all this: look for a different solution now, to save yourself from earning some grey hairs. If you want to pursue this, you can at least tell us what the other data source is. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: help Next: Running a query in SSIS thwn importing Excel spreadsheets |