[dba-SQLServer] SQL Server linked to Teradata

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/