[dba-SQLServer] SQL Server linked to Teradata

Francisco Tapia fhtapia at gmail.com
Wed Mar 25 16:28:50 CDT 2009 

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 <markamatte at hotmail.com>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 <markamatte at hotmail.com
> >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
> <markamatte at hotmail.comwrote
> > > :
> > >
> > > 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
> > > <markamatte at hotmail.comwrote:
> > >
> > > 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 – Now Available. Faster, safer, easier.
> > > 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
> > >
> > >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
>
> _________________________________________________________________
> Get quick access to your favorite MSN content with Internet Explorer 8.
>
> http://ie8.msn.com/microsoft/internet-explorer-8/en-us/ie8.aspx?ocid=B037MSN55C0701A
> _______________________________________________
> 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