Raj - reg. transact-sql on july 28 2003

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Raj - reg. transact-sql on july 28 2003

Author

Topic

rajsequel
Starting Member

8 Posts

Posted - 2003-07-28 : 09:20:08

i want to transfer the table data from one server (sql server) to another (sql server) i tried the below..but error occurs...i need a sample how to pass table data from one server to another...

" select * into satish.pubs.emp1 from
design2new\design2new.m118mail.emp1 "

rajesh

rajesh

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-28 : 09:33:56

Hi

You need to set up a linked server. Have a look at sp_addlinkedserver in BOL. You should find out how to reference it there too.

rajsequel
Starting Member

8 Posts

Posted - 2003-07-29 : 02:36:08

hi Andraax,

thank u for yr reply..i have tried using ,

EXEC sp_addlinkedserver 'satish', N'SQL Server'

then, i tried to execute the below queries ...i.e i tried in 2/3 ways...i don't know whether we are having "\" in the server name, hope it is not accepting or what will be the reason

1. Select * into satish.pubs.emp1
from design2new\design2new.pubs.emp1

(for this error says, Incorrect syntax near '\' )

2.
declare @msql varchar(100)
set @msql = 'design2new\design2new.pubs.emp1'
select * into satish.pubs.emp1
from @msql

(for this error says, Must declare the variable '@msql')

3. create proc myproc
as
begin
declare @msql varchar(200)
declare @mquery varchar(1000)
set @msql = 'design2new\design2new.pubs.emp1'
set @mquery = ' select * into satish.pubs.emp1 from ' + @msql
execute(@mquery)
end

exec myproc

(error says like, Incorrect syntax near '\')...

hope the servername "design2new\design2new" is creating problem...

rajesh

Nazim
A custom title

1408 Posts

Posted - 2003-07-29 : 03:19:18

your name should follow four part naming covention.

servername.databasename.ownername.objectname .

enclose your name in [ ]. it should handle name with / or spaces.
eg [Nazim/testserver].testdb.dbo.[Congrats X002548 on hitting 2000]

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-29 : 03:22:13

Hi again rajesh.

Since you in fact are logged on to the server design2new\design2new, you can skip that in the query. Also, you have forgotten the owner part of the object name:

<server>.<database>.<owner>.<table>

Try this:

Select * into satish.pubs..emp1
from pubs..emp1

If it doesn't work, check which users own the tables. For the above to work, the owner should be dbo. Otherwise you have to specify the owner between the two dots.

Also, if you get security problems, look into sp_addlinkedsrvlogin in BOL.

Good luck,
Andraax

rajsequel
Starting Member

8 Posts

Posted - 2003-07-30 : 11:29:35

hi andraax,

thanks for yr reply...anyway same error raises what i specified previous time...so have to check in some other environment.

regds.
rajesh

rajesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources