Incorrect Syntax in a USE statement
-
Hi All,
I am trying to write a script to create a view in a database.
I have a variable declared for the database name, but my USE statement isn't liking the variable.
Here is the code:
declare @i_TargetDB nvarchar(128)
set @i_TargetDB = 'Trinity_Target3'
USE @i_TargetDB ;
go
IF OBJECT_ID ('AG_E_Activity_R_COGS_OPEXP', 'view') is not null
DROP View AG_E_Activity_R_COGS_OPEXP ;
go
CREATE VIEW AG_E_Activity_R_COGS_OPEXP
AS
SELECT ****************
Here is the error message I get:
Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@i_TargetDB'.
Can anyone tell me what I am doing wrong???
Thanks
Paul
-
You can't dynamically change the in the connection like that. You have to use dynamic sql (including the following code) to make this work.
If you need to run this cod ein multiple dbs at the same time, you can also check the sp_msforeachdb system procedure.
-
not sure what you mean. Did you mean to include some code?
Thanks!
Paul
-
Hmm, looks like the dynamic sql doesn't work in this context.
In which context do you need to run this script?
-
I have a script that goes into an application's database, calculates some data and then puts the data into a simpler target database to make external reporting much easier.
I didn't write the script, my company's application developers did.
I want to add in script to create several views when the script is run and that is where my problem is.
I have the following at the top of the whole script:
declare @i_TargetDB nvarchar(128)
-----------------------------------------------------------------------
-- Input Parameters
-----------------------------------------------------------------------
-- Source Data Base
use [Source]
-- Target Data Base
set @i_TargetDB = 'Target'
-------------then follows all the script for the database creation. Its 500+ rows.
-------------Then I have my create view I changed the USE to EXEC, which solved the -------------incorrect syntax error that started this thread but now, clearly the CREATE -------------VIEW is looking at the target database:
exec('use ' + @i_TargetDB)
IF OBJECT_ID ('AG_E_Activity_R_COGS_OPEXP', 'view') is not null
DROP View AG_E_Activity_R_COGS_OPEXP ;
go
CREATE VIEW AG_E_Activity_R_COGS_OPEXP
AS
SELECT Export_AccountGroup.GroupMeat_Name, Export_Versions.Version_Name, Export_AccountGroup_Entity_Data.FISCALYEAR,
SUM(Export_AccountGroup_Entity_Data.M1 - Export_AccountGroup_Entity_Data.BB) AS M1,
SUM(Export_AccountGroup_Entity_Data.M2 - Export_AccountGroup_Entity_Data.M1) AS M2,
SUM(Export_AccountGroup_Entity_Data.M3 - Export_AccountGroup_Entity_Data.M2) AS M3,
SUM(Export_AccountGroup_Entity_Data.M4 - Export_AccountGroup_Entity_Data.M3) AS M4,
SUM(Export_AccountGroup_Entity_Data.M5 - Export_AccountGroup_Entity_Data.M4) AS M5,
SUM(Export_AccountGroup_Entity_Data.M6 - Export_AccountGroup_Entity_Data.M5) AS M6,
SUM(Export_AccountGroup_Entity_Data.M7 - Export_AccountGroup_Entity_Data.M6) AS M7,
SUM(Export_AccountGroup_Entity_Data.M8 - Export_AccountGroup_Entity_Data.M7) AS M8,
SUM(Export_AccountGroup_Entity_Data.M9 - Export_AccountGroup_Entity_Data.M8) AS M9,
SUM(Export_AccountGroup_Entity_Data.M10 - Export_AccountGroup_Entity_Data.M9) AS M10,
SUM(Export_AccountGroup_Entity_Data.M11 - Export_AccountGroup_Entity_Data.M10) AS M11,
SUM(Export_AccountGroup_Entity_Data.M12 - Export_AccountGroup_Entity_Data.M11) AS M12
FROM Export_AccountGroup INNER JOIN Export_AccountGroup_Entity_Data ON
Export_AccountGroup.AccountGroup_ID = Export_AccountGroup_Entity_Data.AccountGroup_ID INNER JOIN
Export_Versions ON Export_AccountGroup_Entity_Data.Version_ID = Export_Versions.Version_ID
GROUP BY Export_AccountGroup.GroupMeat_Name, Export_Versions.Version_Name, Export_AccountGroup_Entity_Data.FISCALYEAR
HAVING (Export_AccountGroup.GroupMeat_Name = N'General Expense') OR
(Export_AccountGroup.GroupMeat_Name = N'COGS') OR
(Export_AccountGroup.GroupMeat_Name = N'Depreciation') OR
(Export_AccountGroup.GroupMeat_Name = N'Amortization') OR
(Export_AccountGroup.GroupMeat_Name = N'Tax Provision') OR
(Export_AccountGroup.GroupMeat_Name = N'Salary Expense') OR
(Export_AccountGroup.GroupMeat_Name = N'Salary Related Expense') OR
(Export_AccountGroup.GroupMeat_Name = N'Other (Income)/Expense') OR
(Export_AccountGroup.GroupMeat_Name = N'Interest (Income)/Expense') OR
(Export_AccountGroup.GroupMeat_Name = N'(Gain)/Loss on Disposal of Assets')
UNION
SELECT Export_AccountGroup_1.GroupMeat_Name, Export_Versions_1.Version_Name, Export_AccountGroup_Entity_Data_1.FISCALYEAR,
SUM(Export_AccountGroup_Entity_Data_1.M1 - Export_AccountGroup_Entity_Data_1.BB) * - 1 AS M1,
SUM(Export_AccountGroup_Entity_Data_1.M2 - Export_AccountGroup_Entity_Data_1.M1) * - 1 AS M2,
SUM(Export_AccountGroup_Entity_Data_1.M3 - Export_AccountGroup_Entity_Data_1.M2) * - 1 AS M3,
SUM(Export_AccountGroup_Entity_Data_1.M4 - Export_AccountGroup_Entity_Data_1.M3) * - 1 AS M4,
SUM(Export_AccountGroup_Entity_Data_1.M5 - Export_AccountGroup_Entity_Data_1.M4) * - 1 AS M5,
SUM(Export_AccountGroup_Entity_Data_1.M6 - Export_AccountGroup_Entity_Data_1.M5) * - 1 AS M6,
SUM(Export_AccountGroup_Entity_Data_1.M7 - Export_AccountGroup_Entity_Data_1.M6) * - 1 AS M7,
SUM(Export_AccountGroup_Entity_Data_1.M8 - Export_AccountGroup_Entity_Data_1.M7) * - 1 AS M8,
SUM(Export_AccountGroup_Entity_Data_1.M9 - Export_AccountGroup_Entity_Data_1.M8) * - 1 AS M9,
SUM(Export_AccountGroup_Entity_Data_1.M10 - Export_AccountGroup_Entity_Data_1.M9) * - 1 AS M10,
SUM(Export_AccountGroup_Entity_Data_1.M11 - Export_AccountGroup_Entity_Data_1.M10) * - 1 AS M11,
SUM(Export_AccountGroup_Entity_Data_1.M12 - Export_AccountGroup_Entity_Data_1.M11) * - 1 AS M12
FROM Export_AccountGroup AS Export_AccountGroup_1 INNER JOIN
Export_AccountGroup_Entity_Data AS Export_AccountGroup_Entity_Data_1 ON
Export_AccountGroup_1.AccountGroup_ID = Export_AccountGroup_Entity_Data_1.AccountGroup_ID INNER JOIN
Export_Versions AS Export_Versions_1 ON Export_AccountGroup_Entity_Data_1.Version_ID = Export_Versions_1.Version_ID
GROUP BY Export_AccountGroup_1.GroupMeat_Name, Export_Versions_1.Version_Name, Export_AccountGroup_Entity_Data_1.FISCALYEAR
HAVING (Export_AccountGroup_1.GroupMeat_Name = N'REVENUE')
This CREATE VIEW gives the following errors:
Msg 208, Level 16, State 1, Procedure AG_E_Activity_R_COGS_OPEXP, Line 5
Invalid object name 'Export_AccountGroup'.
Msg 208, Level 16, State 1, Procedure AG_E_Activity_R_COGS_OPEXP, Line 5
Invalid object name 'Export_AccountGroup_Entity_Data'.
Msg 208, Level 16, State 1, Procedure AG_E_Activity_R_COGS_OPEXP, Line 5
Invalid object name 'Export_Versions'.
Msg 208, Level 16, State 1, Procedure AG_E_Activity_R_COGS_OPEXP, Line 5
Invalid object name 'Export_AccountGroup'.
Msg 208, Level 16, State 1, Procedure AG_E_Activity_R_COGS_OPEXP, Line 5
Invalid object name 'Export_AccountGroup_Entity_Data'.
Msg 208, Level 16, State 1, Procedure AG_E_Activity_R_COGS_OPEXP, Line 5
Invalid object name 'Export_Versions'.
Its clear to me that the CREATE VIEW is not looking at the Target db. In SQL Server Management Studio (connected to a SQL 2000 instance), if I change the database dropdown field to my target database and run the code for CREATE VIEW on its own, it works fine. But not when it is set to the source DB.
Thanks!
-
Why can't you hardcode the dbname in the script?
USE DbName
GO
IF EXISTS... DROP VIEW
GO
CREATE VIEW...
GO
Any other option will involve creating a file on the fly with the correct db name, directly included, then running that script file (sorry but I don't have any examples on to offer).
-
you can use exec() (in a tricky way
) for dynamic sql but you will have to escape *all* your '
* Noel -
Execresultset?
Demo please!
-
Thanks for all your replies. I can't directly state the db name. This is a script going out to customers and the database could be any name. The customer declares the database name at the beginning of the script, so in the section for creating my view, I'd like to be able to go after the dbname that they declare.
-
small install app could handle that without any problem. I'm guessing osql as well but I never used that so I can't tell for sure.
-
Correct OSQL or sqlcmd *are* the tool of choise. But just for the fun of it :
declare @i_TargetDB varchar(200)
set @i_TargetDB = 'AdventureWorks'
EXEC('USE '+ @i_TargetDB +'
IF OBJECT_ID (''dbo.mine'', ''view'') is not null
DROP View dbo.mine
')
-- Embeded exec for Create View requirements
EXEC('USE '+ @i_TargetDB +'
EXEC (''
create view dbo.mine
as
select * from dbo.ErrorLog
''
)
')
Cheers,
* Noel -
Depends on your permissions but you can do DROP db.dbo.view just as you can do SELECT * FROM db.dbo.view
-
yeah but you can't CREATE VIEW db.dbo.view
* Noel -
Exactly... been burned by that one before!
-
Me too
* Noel
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply