Mark A Matte
markamatte at hotmail.com
Thu Mar 26 09:27:57 CDT 2009
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/