Query analyzer .sql script error

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)

Query analyzer .sql script error

Author

Topic

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-12-15 : 10:49:40

I'm trying this in query analyzer, but it's giving me an error:

Incorrect syntax near '.'.

.... on the two places where I try to use @ODB and @DDB

--set originating database
Declare @ODB nvarchar(10)
Set @ODB = 'Demo1'

--set destination database
Declare @DDB nvarchar(10)
Set @DDB = 'Demo2'

--set WBS var equal to what's in the @ODB (originating database)
Declare @WBS nvarchar(50)
Set @WBS = '1.2.8.5.002'

--Table
Insert into @DDB.dbo.Table
([Number], [Title], [Mgr], Person, CreatedDate, CreatedBy, UpdatedBy, UpdatedDate, Number1, Number2)

SELECT [Number], [Title], [Mgr], Person, CreatedDate, CreatedBy, UpdatedBy, UpdatedDate, Number1, Number2
FROM @ODB.dbo.WBS_Table where [Number] = @WBS

Any suggestions?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-15 : 10:55:17

Can't do that. You have to use dynamic SQL.

Declare @sql varchar(8000)

set @sql = 'Insert into ' + @DDB + '.dbo.Table
([Number], [Title], [Mgr], Person, CreatedDate, CreatedBy, UpdatedBy, UpdatedDate, Number1, Number2)
SELECT [Number], [Title], [Mgr], Person, CreatedDate, CreatedBy, UpdatedBy, UpdatedDate, Number1, Number2 
FROM ' + @ODB + '.dbo.WBS_Table where [Number] = ''' + @WBS + ''''

Exec(@sql)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"