Creating schemas

[ssrajpal2001]

hello expert
problem is
i am using mssql 2005 and i have created a procedure that will create schema and tables under that schema
procedure name is "createschema(<schema_name>) .
code is
SET @Schema_Tables=N'Create schema '+ @Schema_Name
EXEC sp_executesql @Schema_Tables
this creates schema but when create table is encountered
code:
SET @Schema_Tables=N'CREATE TABLE '+@Schema_Name+'.UserGroup('
SET @Schema_Tables=@Schema_Tables+N'Group_id int primary key,'
SET @Schema_Tables=@Schema_Tables+N'GroupName varchar(50))'
EXEC sp_executesql @Schema_Tables
it gives an error
either schema not created or you dont have permission to use that schema
now if the procedure is successfully executed table created is not under the schema name i have specified but the user name.tablename is seen
Note:username is the name from which i loggen in

.this runs successfully in local computer but not in remote server.

please help
thanking you
sarabjeet singh

[iburyak]

Try changing this:

SET @Schema_Tables=N'CREATE TABLE '+@Schema_Name+'.UserGroup('

to this:

SET @Schema_Tables=N'CREATE TABLE '+@Schema_Name+'.dbo.UserGroup('