April 19, 2012 at 3:32 am
I'm using some scripts to insert small sets of data from DB to DB and everything works using dynamic SQL. However , I cannot get a simple Select on the Information_schema views to work..
I may be missing the obvious, but what is wrong with the following script:
DECLARE @DATABASE_NEW VARCHAR(30)
DECLARE @QUERY VARCHAR(4000)
SET @DATABASE_NEW = N'Some_database'
SET @QUERY = 'SELECT TABLE_NAME,COLUMN_NAME FROM ['+ @DATABASE_NEW + N '].[INFORMATION_SCHEMA].[COLUMNS]'
EXEC (@QUERY)
April 19, 2012 at 3:59 am
blom0344 (4/19/2012)
I'm using some scripts to insert small sets of data from DB to DB and everything works using dynamic SQL. However , I cannot get a simple Select on the Information_schema views to work..I may be missing the obvious, but what is wrong with the following script:
DECLARE @DATABASE_NEW VARCHAR(30)
DECLARE @QUERY VARCHAR(4000)
SET @DATABASE_NEW = N'Some_database'
SET @QUERY = 'SELECT TABLE_NAME,COLUMN_NAME FROM ['+ @DATABASE_NEW + N '].[INFORMATION_SCHEMA].[COLUMNS]'
EXEC (@QUERY)
Is this statement exactly as you try to execute it? If yes, then remove the space between the N and '], i.e. it should read
SET @QUERY = 'SELECT TABLE_NAME,COLUMN_NAME FROM ['+ @DATABASE_NEW + N'].[INFORMATION_SCHEMA].[COLUMNS]'
April 19, 2012 at 5:16 am
Thanks Jan,
I new it was something small and obvious.. 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply