Post reply

creating table using linked server

  • July 27, 2010 at 9:29 am

    #141239

    i am trying to create a table on another server remote using linked server. My statement would be " create table linkedserver.dbname.dbo.tablename" and i get the error saying " max 2 prefixes are allowed" Mentioned below is my thought but can get only half way through

    i) i created a sp on the source server with create table statement in it, now i want to execute the proc and druing run time the output should run on the remote server since the proc is not available on the remote server...how can i do this?

    If someone has a better way to do this please let me know.thanks...

  • July 27, 2010 at 10:02 am

    #1198811

    you have to use the EXECUTE AT [LinkedServerName] syntax to do that; 4 part names are DML only, so you cna insert/update/delete to a 4 part named object like MyLinkedServer.DatabaseName.Schema.Table, but not create things that way:

    try this instead:

    EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'

    GO

    EXECUTE ( 'CREATE TABLE AdventureWorks2008R2.dbo.SalesTbl

    (SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;

    GO

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 27, 2010 at 12:29 pm

    #1198902

    works for create table thanks...can you tell me the same way for create/alter stored procedure. I tried doesnt work since you cant have 4 part naming convention while creating proc..thnaks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply