Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

''CREATE VIEW'' does not allow specifying the database name as a prefix to the object name Expand / Collapse
Author
Message
Posted Friday, August 10, 2007 4:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 11:42 PM
Points: 16, Visits: 40

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?

 

 

Post #389571
Posted Friday, August 10, 2007 4:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 11:42 PM
Points: 16, Visits: 40

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

 

Post #389574
Posted Friday, August 10, 2007 7:47 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:19 PM
Points: 23,286, Visits: 32,012

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




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #389654
Posted Saturday, August 11, 2007 9:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 11:42 PM
Points: 16, Visits: 40

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.

Post #389965
Posted Saturday, August 11, 2007 8:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:03 PM
Points: 36,983, Visits: 31,508

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #389981
Posted Monday, August 13, 2007 12:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 11:42 PM
Points: 16, Visits: 40

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.

 

Post #390064
Posted Monday, August 13, 2007 3:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 1, 2012 3:30 PM
Points: 292, Visits: 1,028
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

Post #390077
Posted Tuesday, August 14, 2007 12:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 11:42 PM
Points: 16, Visits: 40

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

 

Post #390435
Posted Wednesday, August 15, 2007 1:51 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 21, 2012 1:52 PM
Points: 696, Visits: 743
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?
Post #391062
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse