Backwards Technology(SQL 2005 - SQL 2000)

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

General SQL Server Forums

New to SQL Server Programming

Backwards Technology(SQL 2005 - SQL 2000)

Author

Topic

majolamp
Starting Member

15 Posts

Posted - 2008-02-29 : 05:03:18

Hi ALL

In my script below. I'm trying to update a SQL Server 2000 DB with data from SQL Server 2005. I've written a Cursor to call tables from information schema. Compare the data between 2005 and 2000 then Insert into SQL 2000 Table. But I'm getting The multi-part identifier "c.ID" could not be bound.. Can you please help me to resolve this.

DECLARE @TableName VARCHAR(255)
DECLARE @Sql VARCHAR(1000)

DECLARE TableCursor CURSOR FOR
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Type = 'Base Table' and Table_Name like 'L_%'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @Line VARCHAR(1000)

SET @Line = ''

SELECT @Line = @Line + 'c.'+ c.Column_Name + ' , '
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON t.Table_Name = c.Table_Name
WHERE t.Table_Name = @TableName and t.Table_Name like 'L_%'

SELECT @Sql = SUBSTRING(@Line, 1, LEN(@Line) -1)
SELECT @Sql = 'SELECT'+ ' ' + @Sql
SELECT @Sql = @Sql + 'INTO dbo.L_BrokerTest'
SELECT @Sql = @Sql + ' '+ 'FROM'+ ' '
SELECT @Sql = @Sql + @TableName + ' ' +'A' + ' '
SELECT @Sql = @Sql + 'LEFT JOIN'
SELECT @Sql = @Sql + '[Blake-DBN12].Staging.dbo.'
SELECT @Sql = @Sql + @TableName + ' ' +'B'+ ' '
SELECT @Sql = @Sql + 'ON' + ' '
SELECT @Sql = @Sql + 'A.ID <> B.ID'
--PRINT @Sql
EXEC (@Sql)

FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-29 : 05:44:40

from reading you code (rather than testing), i think...

where you've set @line you've made it 'c.column_name', but you've then used that string to build your next statement which uses alias's of A and B for the tables, so it doesn't know what 'c.' is.

Em

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-02-29 : 06:32:15

the Solution for this kind of problem ("The multi-part identifier "c.ID" could not be bound.. ")is
just add DBO. infront of every object name .

Vinod
Even you learn 1%, Learn it with 100% confidence.

majolamp
Starting Member

15 Posts

Posted - 2008-02-29 : 07:33:16

Thankx Guys

You were both right. Yes its worked.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources