[dba-SQLServer] SQL Server linked to Teradata

Francisco Tapia fhtapia at gmail.com
Thu Mar 26 09:33:10 CDT 2009 

hmm,   I'll need to read up on the teradata oledb connector and see what it
does support... sorry...

-Francisco



On Thu, Mar 26, 2009 at 7:27 AM, Mark A Matte <markamatte at hotmail.com>wrote:

>
> Tried that...I get an error:"data definition not valid unless solitary."
>
> Thanks,
>
> Mark
>
> ----------------------------------------
> > Date: Wed, 25 Mar 2009 14:28:50 -0700
> > From: fhtapia at gmail.com
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: Re: [dba-SQLServer] SQL Server linked to Teradata
> >
> > What I figured you could do, as I know it works for sql server is that
> you
> > would run the DROP TABLE command followed by a semicolon then the next
> sql
> > statement, I think this is stilll ANSI-92 standard tsql, and should work
> for
> > other engines that support the DROP TABLE syntax
> > that's what I ment.
> > -Francisco
> > http://sqlthis.blogspot.com | Tsql and More...
> >
> >
> > On Wed, Mar 25, 2009 at 10:32 AM, Mark A Matte wrote:
> >
> >>
> >> Not sure I follow...
> >>
> >>
> >>
> >> Both of these work:
> >>
> >>
> >>
> >> Select syntax:
> >>
> >>
> >> select * from openquery(TDATA, 'select * from test_tbls.testTable')
> >>
> >>
> >> Insert Syntax:
> >>
> >> insert openquery(tdata, 'select ship_to_phn from test_tbls.testTABLE')
> >> select phone from ccms.dbo.tbltest;
> >>
> >>
> >>> Date: Wed, 25 Mar 2009 10:16:12 -0700
> >>> From: fhtapia at gmail.com
> >>> To: dba-sqlserver at databaseadvisors.com
> >>> Subject: Re: [dba-SQLServer] SQL Server linked to Teradata
> >>>
> >>> what if you use a simple select on a small table?
> >>>
> >>> -Francisco
> >>> http://sqlthis.blogspot.com | Tsql and More...
> >>>
> >>>
> >>> On Wed, Mar 25, 2009 at 10:09 AM, Mark A Matte>>>wrote:
> >>>
> >>>>
> >>>> That gives me an error :
> >>>>
> >>>> OLE DB provider "MSDASQL" for linked server "TDATA" returned message
> >>>> "[NCR][ODBC Teradata Driver][Teradata Database] Data definition not
> >> valid
> >>>> unless solitary. ".
> >>>>
> >>>> Thanks,
> >>>>
> >>>> Mark
> >>>>
> >>>> Date: Wed, 25 Mar 2009 09:24:47 -0700
> >>>> From: fhtapia at gmail.com
> >>>> To: dba-sqlserver at databaseadvisors.com
> >>>> Subject: Re: [dba-SQLServer] SQL Server linked to Teradata
> >>>>
> >>>> Given that OpenQuery is a return set statement, maybe doing something
> >> like:
> >>>> SELECT * FROM OPENQUERY(LinkedServer, 'Drop Table TableName; SELECT *
> >> FROM
> >>>> DBC.TABLES WHERE TABLENAME = TableName ;'
> >>>> Ideally you should be running something like the equivalent of Select
> >>>> @@Error that way you can get a confirmation, of what occurred on the
> >>>> TeraData system.
> >>>>
> >>>> On Wed, Mar 25, 2009 at 6:33 AM, Mark A Matte
> >>>>>> :
> >>>>
> >>>> Also,
> >>>>
> >>>> I can use a pass-through query in access to drop a table...any way to
> >> port
> >>>> that syntax over to sql server?
> >>>>
> >>>> Thanks,
> >>>>
> >>>> Mark
> >>>>
> >>>> From: ab-mi at post3.tele.dk
> >>>> To: dba-sqlserver at databaseadvisors.com
> >>>> Date: Tue, 24 Mar 2009 23:32:57 +0100
> >>>>
> >>>> Ooops, ignore my answer. Didn't notice the subject line: "linked to
> >>>> Teradata"...
> >>>> Using sp_executesql of course only applies to SQL Server.
> >>>> Don't know if Teradata has an equivalent sp.
> >>>>
> >>>> Asger
> >>>>
> >>>> -----Oprindelig meddelelse-----
> >>>> Fra: dba-sqlserver-bounces at databaseadvisors.com
> >>>> [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger
> >>>> Blond
> >>>> Sendt: 24. marts 2009 22:05
> >>>> Til: 'Discussion concerning MS SQL Server'
> >>>> Emne: Re: [dba-SQLServer] SQL Server linked to Teradata
> >>>>
> >>>> Mark,
> >>>>
> >>>> A linked server does not directly support DDL-statements. But you can
> >> use
> >>>> sp_executesql to submit a DDL-statement against the linked server.
> Like
> >>>> this:
> >>>>
> >>>> EXECUTE YourLinkedServer.YourDatabase.dbo.sp_executesql "CREATE TABLE
> >>>> TestTable(TestColumn int)"
> >>>>
> >>>> EXECUTE YourLinkedServer.YourDatabase.dbo.sp_executesql "DROP TABLE
> >>>> TestTable"
> >>>>
> >>>> Asger
> >>>>
> >>>> -----Oprindelig meddelelse-----
> >>>> Fra: dba-sqlserver-bounces at databaseadvisors.com
> >>>> [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Mark
> A
> >>>> Matte
> >>>> Sendt: 24. marts 2009 19:36
> >>>> Til: dba-sqlserver at databaseadvisors.com
> >>>> Emne: Re: [dba-SQLServer] SQL Server linked to Teradata
> >>>>
> >>>> Thanks,
> >>>>
> >>>> I have those...its just the CREATE and DROP syntax I can't seem to
> >> find.
> >>>>
> >>>> Thanks,
> >>>>
> >>>> Mark
> >>>>
> >>>> Date: Tue, 24 Mar 2009 11:01:18 -0700
> >>>> From: fhtapia at gmail.com
> >>>> To: dba-sqlserver at databaseadvisors.com
> >>>> Subject: Re: [dba-SQLServer] SQL Server linked to Teradata
> >>>>
> >>>> Sorry, it's generally used as
> >>>> SELECT * FROM OPENQUERY(LinkServer, 'Select Field From Table')
> >>>>
> >>>> or for Inserts
> >>>>
> >>>> INSERT OPENQUERY(LinkServer, 'Select Field From Table')
> >>>> Values('Value')
> >>>>
> >>>> DELETE OPENQUERY(LinkServer, 'Select Field From Table')
> >>>> Etc...
> >>>> -Francisco
> >>>> http://sqlthis.blogspot.com | Tsql and More...
> >>>>
> >>>> On Tue, Mar 24, 2009 at 10:47 AM, Mark A Matte
> >>>>>>>>
> >>>> Thanks Francisco,
> >>>>
> >>>> I get an error (from SQL Server) "Incorrect syntax near 'OpenQuery'"
> >>>>
> >>>> when I use: OpenQuery(LinkedServerName, 'Drop Table TableName')
> >>>>
> >>>> Any ideas?
> >>>>
> >>>> Thanks,
> >>>>
> >>>> Mark
> >>>>
> >>>>
> >>>>
> >>>> Date: Tue, 24 Mar 2009 10:24:10 -0700
> >>>> From: fhtapia at gmail.com
> >>>> To: dba-sqlserver at databaseadvisors.com
> >>>> Subject: Re: [dba-SQLServer] SQL Server linked to Teradata
> >>>>
> >>>> you should be able to execute any number of the Teradata's commands
> >>>> by
> >>>> issuing the command via OpenQuery
> >>>> It will be constructed as such: OpenQuery(LinkedServerName, 'Drop
> >>>> Table
> >>>> TableName') or what ever the command is for TeraData (sorry I am
> >>>> not
> >>>> as
> >>>> familiar with this engine so I cannot help).
> >>>>
> >>>>
> >>>>
> >>>> -Francisco
> >>>> http://sqlthis.blogspot.com | Tsql and More...
> >>>>
> >>>>
> >>>> On Tue, Mar 24, 2009 at 7:25 AM, Mark A Matte <
> >>>> markamatte at hotmail.com
> >>>> wrote:
> >>>> Hello All,
> >>>>
> >>>> I have successfully linked SQL Server2005 to Teradata. I can pull
> >>>> data
> >>>> from...and insert records into Teradata.
> >>>>
> >>>> I have NOT been able to create or drop a table in Teradata from
> >>>> SQL
> >>>> Server.
> >>>>
> >>>> Can anyone help with syntax ( or if even possible)...or what
> >>>> approach???
> >>>>
> >>>> Thanks,
> >>>>
> >>>>
> >>>> Mark A. Matte
> _________________________________________________________________
> Internet Explorer 8 – Get your Hotmail Accelerated.  Download free!
> http://clk.atdmt.com/MRT/go/141323790/direct/01/
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>

More information about the dba-SQLServer mailing list