''CREATE VIEW'' does not allow specifying the database name as a prefix to the object name

Question

Post reply

''CREATE VIEW'' does not allow specifying the database name as a prefix to the object name

BEN-405167

SSC-Addicted

Points: 470
More actions
August 10, 2007 at 4:27 am

#118116

For each customer a separate database is created. The variable @dbname is set from within a .dotnet application
ALTER PROC SP_CREATE_DATABASE_BASIS
@dbname sysname
AS
DECLARE @cmd varchar(4000)
SET @cmd = 'CREATE TABLE ' + @dbname + '.dbo.ETL_LAAD (
LAAD_KEY bigint IDENTITY (1, 1) NOT NULL ,
LAAD_DATUM datetime NOT NULL
) ON [PRIMARY]'
--EXEC @cmd
Not only tables have to be created but 1 view as well.
But 'CREATE VIEW' does not allow specifying the database name.
Is there a workaround?

Viewing 9 posts - 1 through 8 (of 8 total)

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

BEN-405167 SSC-Addicted Points: 470 More actions · Answer 1

You can, however, use the three part name for the table(s) used in the VIEW definition.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Try this:

SET @cmd = 'USE ' + @dbname + ';CREATE TABLE dbo.ETL_LAAD (LAAD_KEY bigint IDENTITY (1, 1) NOT NULL , LAAD_DATUM datetime NOT NULL) ON [PRIMARY]'

--EXEC @cmd

BEN-405167 SSC-Addicted Points: 470 More actions · Answer 3

I tried this but i get :

The name 'USE db_presmon_dwh_9;CREATE TABLE dbo.ETL_TEST (LAAD_KEY bigint IDENTITY (1, 1) NOT NULL , LAAD_DATUM datetime NOT NULL) ON [PRIMARY]' is not a valid identifier.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Must be...

EXEC (@Cmd)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BEN-405167 SSC-Addicted Points: 470 More actions · Answer 5

I tried this:

ALTER procedure SP_CREATE_VIEW_TEST

@dbname sysname,

@Klant_ID bigint

as

DECLARE @cmd varchar(3000)

SET @cmd = 'USE ' + @dbname + ';CREATE VIEW FACTUURGEGEVENS_' + convert(varchar,@Klant_ID) + ' as

SELECT ikp.IKP_GROEPNAAM,

ikp.IKP_NAAM,

ks.KS_NUMMER,

ks.KS_NAAM,

lev.LEV_CREDITEURNUMMER,

lev.LEV_NAAM,

lev.LEV_GROEPNAAM,

org.ORG_KOSTENPLAATS_NUMMER,

org.ORG_KOSTENPLAATS_NAAM,

org.ORG_AFDELING_NAAM,

adm.ADM_CODE,

ft.FEIT_BOEKSTUKNUMMER,

ft.FEIT_FACTUUROMSCHRIJVING,

ft.FEIT_FIN_JAAR,

ft.FEIT_FACTUUR_REGEL_BEDRAG,

ft.FEIT_FACTUUR_NUMMER

FROM '+ @dbname +'.dbo.DIM_ADMINISTRATIE adm INNER JOIN

'+ @dbname +'.dbo.FEIT_BOEKREGEL ft ON adm.ADM_KEY = ft.ADM_KEY INNER JOIN

'+ @dbname +'.dbo.DIM_INKOOPPAKKET ikp ON ft.IKP_KEY = ikp.IKP_KEY INNER JOIN

'+ @dbname +'.dbo.DIM_LEVERANCIER lev ON ft.LEV_KEY = lev.LEV_KEY INNER JOIN

'+ @dbname +'.dbo.DIM_ORGANISATIE org ON ft.ORG_KEY = org.ORG_KEY INNER JOIN

'+ @dbname +'.dbo.DIM_KOSTENSOORT ks ON ft.KS_KEY = ks.KS_KEY'

EXEC (@cmd)

I get the message:

'CREATE VIEW' must be the first statement in a query batch.

goodguy SSCertifiable Points: 5456 More actions · Answer 6

I normally place a GO before any CREATE statements to distinguish the BATCH.

SET @cmd = 'USE ' + @dbname + ';GO;CREATE VIEW...

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

BEN-405167 SSC-Addicted Points: 470 More actions · Answer 7

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

ALTER procedure SP_CREATE_VIEW_TEST

@dbname sysname,

@Klant_ID bigint

as

DECLARE @cmd varchar(3000)

SET @cmd = 'USE ' + @dbname + ';GO;CREATE VIEW FACTUURGEGEVENS_' + convert(varchar,@Klant_ID) + ' as

SELECT ikp.IKP_GROEPNAAM,

ikp.IKP_NAAM,

ks.KS_NUMMER,

ks.KS_NAAM,

lev.LEV_CREDITEURNUMMER,

lev.LEV_NAAM,

lev.LEV_GROEPNAAM,

org.ORG_KOSTENPLAATS_NUMMER,

org.ORG_KOSTENPLAATS_NAAM,

org.ORG_AFDELING_NAAM,

adm.ADM_CODE,

ft.FEIT_BOEKSTUKNUMMER,

ft.FEIT_FACTUUROMSCHRIJVING,

ft.FEIT_FIN_JAAR,

ft.FEIT_FACTUUR_REGEL_BEDRAG,

ft.FEIT_FACTUUR_NUMMER

FROM '+ @dbname +'.dbo.DIM_ADMINISTRATIE adm INNER JOIN

'+ @dbname +'.dbo.FEIT_BOEKREGEL ft ON adm.ADM_KEY = ft.ADM_KEY INNER JOIN

'+ @dbname +'.dbo.DIM_INKOOPPAKKET ikp ON ft.IKP_KEY = ikp.IKP_KEY INNER JOIN

'+ @dbname +'.dbo.DIM_LEVERANCIER lev ON ft.LEV_KEY = lev.LEV_KEY INNER JOIN

'+ @dbname +'.dbo.DIM_ORGANISATIE org ON ft.ORG_KEY = org.ORG_KEY INNER JOIN

'+ @dbname +'.dbo.DIM_KOSTENSOORT ks ON ft.KS_KEY = ks.KS_KEY'

EXEC (@cmd)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

I still get:

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'GO'.

Server: Msg 111, Level 15, State 1, Line 1

'CREATE VIEW' must be the first statement in a query batch.

Stored Procedure: DB_PRESMON_REPOS.dbo.SP_CREATE_VIEW_TEST

Return Code = 0

steve smith-401573 SSChampion Points: 10889 More actions · Answer 8

Is there a reason NOT to use two separate variables and place the view create in its own variable? Or do you end up then having the view placed in the wrong database? Which brings us back to using a fully qualified view name, including database, owner, and view name?