Must declare the scalar variable "@data_path".

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

Must declare the scalar variable "@data_path".

Author

Topic

Bala_p
Starting Member

3 Posts

Posted - 2010-07-20 : 13:30:08

Hello Guys, I am new to SQL Programming, I am running this create db query by declaring a variable and it throws a error,
( Must declare the scalar variable "@data_path" ). below is the script.

------------------------------------------------------

DECLARE @data_path as nvarchar(256);

SET @data_path = (SELECT SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
FROM sysdatabases
WHERE dbid = 1);

---------------------------------------------------------------------
-- execute the CREATE DATABASE statement
---------------------------------------------------------------------
EXECUTE ('CREATE DATABASE TESTDB
ON
PRIMARY
(NAME = CORE,
FILENAME = '''+ @data_path + 'coredata_01.mdf'',
SIZE = 4MB,
MAXSIZE = 16MB,
FILEGROWTH = 4MB),
FILEGROUP CORESLA
( NAME = CORESLA,
FILENAME = '''+ @data_path + 'coresladata_01.ndf'',
SIZE = 4MB,
MAXSIZE = 16MB,
FILEGROWTH = 4MB)

LOG ON
(NAME = CORELOG,
FILENAME = '''+ @data_path + 'corelog_01.ldf'',
SIZE = 4MB,
MAXSIZE = 16MB,
FILEGROWTH = 4MB),
(NAME = CORESLALOG,
FILENAME = '''+ @data_path + 'coreslalog_01.ldf'',
SIZE = 4MB,
MAXSIZE = 16MB,
FILEGROWTH = 4MB),
(NAME = DATALOG,
FILENAME = '''+ @data_path + 'datalog_01.ldf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 16MB)'
);
GO
----------------------------------------------------------------

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 13:37:33

Your code works as is for me, so perhaps you aren't showing us the full code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Bala_p
Starting Member

3 Posts

Posted - 2010-07-20 : 14:43:58

Thanks for the response, here is the complete script. Please Correct if i am wrong, i guess i am missing something in the place where i declare @data_path, THANKS.

--------------------------------------
-- Tablespaces and datafiles creation
-- CORE y CORESLA

USE master;
GO
IF DB_ID (N'TESTDB') IS NOT NULL
DROP DATABASE TESTDB;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
FROM sysdatabases
WHERE dbid = 1);

-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE TESTDB
ON
PRIMARY
(NAME = CORE,
FILENAME = '''+ @data_path + 'coredata_01.mdf'',
SIZE = 4MB,
MAXSIZE = 16MB,
FILEGROWTH = 4MB),
FILEGROUP CORESLA
( NAME = CORESLA,
FILENAME = '''+ @data_path + 'coresladata_01.ndf'',
SIZE = 4MB,
MAXSIZE = 16MB,
FILEGROWTH = 4MB),
FILEGROUP DATA
(NAME = DATA,
FILENAME = '''+ @data_path + 'data_01.mdf'',
SIZE = 64MB,
MAXSIZE = 1024MB,
FILEGROWTH = 32MB),
FILEGROUP IND_DATA
( NAME = IND_DATA,
FILENAME = '''+ @data_path + 'ind_data_01.ndf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 32MB),
FILEGROUP DATAALAR
( NAME = DATAALAR,
FILENAME = '''+ @data_path + 'dataalar_01.ndf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 32MB),
FILEGROUP IND_DATAALAR
( NAME = IND_DATAALAR,
FILENAME = '''+ @data_path + 'ind_dataalar_01.ndf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 32MB),
FILEGROUP DATAGRAF
( NAME = DATAGRAF,
FILENAME = '''+ @data_path + 'datagraf_01.ndf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 32MB),
FILEGROUP IND_DATAGRAF
( NAME = IND_DATAGRAF,
FILENAME = '''+ @data_path + 'ind_datagraf_01.ndf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 32MB),
FILEGROUP DATAINDI
( NAME = DATAINDI,
FILENAME = '''+ @data_path + 'dataindi_01.ndf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 32MB),
FILEGROUP IND_DATAINDI
( NAME = IND_DATAINDI,
FILENAME = '''+ @data_path + 'ind_dataindi_01.ndf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 32MB)
LOG ON
(NAME = CORELOG,
FILENAME = '''+ @data_path + 'corelog_01.ldf'',
SIZE = 4MB,
MAXSIZE = 16MB,
FILEGROWTH = 4MB),
(NAME = CORESLALOG,
FILENAME = '''+ @data_path + 'coreslalog_01.ldf'',
SIZE = 4MB,
MAXSIZE = 16MB,
FILEGROWTH = 4MB),
(NAME = DATALOG,
FILENAME = '''+ @data_path + 'datalog_01.ldf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 16MB),
(NAME = IND_DATALOG,
FILENAME = '''+ @data_path + 'ind_datalog_01.ldf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 16MB),
(NAME = DATAALARLOG,
FILENAME = '''+ @data_path + 'dataalarlog_01.ldf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 16MB),
(NAME = DATAGRAFLOG,
FILENAME = '''+ @data_path + 'datagraflog_01.ldf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 16MB),
(NAME = DATAINDILOG,
FILENAME = '''+ @data_path + 'dataindilog_01.ldf'',
SIZE = 64MB,
MAXSIZE = 10240MB,
FILEGROWTH = 16MB)'
);
GO

-- Database users creation

USE TESTDB;
declare @logindb varchar(30), @loginlang varchar(30) select @logindb = 'TESTDB', @loginlang = null
if @logindb is null or not exists (select * from master..sysdatabases where name = @logindb)
select @logindb = 'master'
if @loginlang is null or (not exists (select * from master..syslanguages where name = @loginlang) and @loginlang <> 'us_english')
select @loginlang = @@language
exec sp_addlogin 'CORE', 'COREJEIZER', 'TESTDB', @loginlang /*usr,psw,schema,idioma*/
if not exists (select * from sysusers where name = 'CORE' and uid < 16382)
EXEC sp_adduser 'CORE', 'CORE', 'public'

GRANT CREATE TABLE TO CORE;
GRANT CREATE VIEW TO CORE;
GRANT CREATE FUNCTION TO CORE;
GRANT CREATE PROCEDURE TO CORE;
GRANT CREATE RULE TO CORE;

GO

declare @logindb varchar(30), @loginlang varchar(30) select @logindb = 'TESTDB', @loginlang = null
if @logindb is null or not exists (select * from master..sysdatabases where name = @logindb)
select @logindb = 'master'
if @loginlang is null or (not exists (select * from master..syslanguages where name = @loginlang) and @loginlang <> 'us_english')
select @loginlang = @@language
exec sp_addlogin 'CORESLA', 'CORESLA', 'TESTDB', @loginlang /*usr,psw,schema,idioma*/
if not exists (select * from sysusers where name = 'CORESLA' and uid < 16382)
EXEC sp_adduser 'CORESLA', 'CORESLA', 'public'

GRANT CREATE TABLE TO CORESLA;
GRANT CREATE VIEW TO CORESLA;
GRANT CREATE FUNCTION TO CORESLA;
GRANT CREATE PROCEDURE TO CORESLA;
GRANT CREATE RULE TO CORESLA;

GO

declare @logindb varchar(30), @loginlang varchar(30) select @logindb = 'TESTDB', @loginlang = null
if @logindb is null or not exists (select * from master..sysdatabases where name = @logindb)
select @logindb = 'master'
if @loginlang is null or (not exists (select * from master..syslanguages where name = @loginlang) and @loginlang <> 'us_english')
select @loginlang = @@language
exec sp_addlogin 'DATA', 'DATAJEIZER', 'TESTDB', @loginlang /*usr,psw,schema,idioma*/
if not exists (select * from sysusers where name = 'DATA' and uid < 16382)
EXEC sp_adduser 'DATA', 'DATA', 'public'

GRANT CREATE TABLE TO DATA;
GRANT CREATE VIEW TO DATA;
GRANT CREATE FUNCTION TO DATA;
GRANT CREATE PROCEDURE TO DATA;
GRANT CREATE RULE TO DATA;

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 14:49:16

Your posted code works as is for me still.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Bala_p
Starting Member

3 Posts

Posted - 2010-07-20 : 15:08:24

Yes, it worked for me, Sorry i was trying to run the code part by part and for some reason it showed up the error. Thanks for you prompt response.

Bala.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 15:12:58

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources