Mark A Matte
markamatte at hotmail.com
Thu Mar 26 09:45:21 CDT 2009
Thanks Francisco,
I've hit almost every site google found that mentioned Teradata or linked servers. The one funny part...is I CAN do this with Access as a pass-through query.
Anything you find would be much appreciated...but I would not expect you to put too much effort into it.
Thanks again,
Mark
----------------------------------------
> Date: Thu, 26 Mar 2009 07:33:10 -0700
> From: fhtapia at gmail.com
>
> 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 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
>>>
>>> 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
_________________________________________________________________
Windows Live™ SkyDrive: Get 25 GB of free online storage.
http://windowslive.com/online/skydrive?ocid=TXT_TAGLM_WL_skydrive_032009