Facing Error of "The Default Schema Does Not Exist." When Executing Runtime Query Inside Sp Using Exec()

Facing error of The default schema does not exist. when executing runtime query inside sp using exec()

Use CREATE PROCEDURE dbo.MySP

The user you are logged in as must have a non existent default schema.

DEFAULT_SCHEMA can be set to a schema that does not currently exist in the database.

Also you should use quotename(@tableName) and a parameter type of sysname rather than varchar(100) to avoid SQL injection or just errors from non standard object names.

Getting a temporary table returned from dynamic SQL in SQL Server 2005, and parsing

I used stored procedure for returning a table with a dynamic column definition

i generated dynamic name for global table:

declare @sp varchar(3)
set @sp = cast( @@spid as varchar(3) )

if object_id ( N'tempdb.dbo.#Periods' ) is not null
    drop table #Periods      
if object_id ( N'tempdb.dbo.##Result' + @sp ) is not null
    execute ( 'drop table ##Result' + @sp )

i have sp for return periods table:

create table #Periods
(
    [PERIOD_NUM]    int
    ,[START_DATE]   datetime
    ,[END_DATE]     datetime   
)

insert into #Periods
exec GET_PERIODS_TABLE_SP @pFromDate, @pToDate, @pPeriodType, 0

some fields in result table are dynamic:

select @PeriodCount = ...

declare @PeriodsScript varchar(max)  

set @PeriodsScript = ''
set @i = 1

while @i <= @PeriodCount
begin
    set @PeriodsScript = @PeriodsScript + ',PERIOD' + cast (@i as varchar(3))

    set @i = @i + 1
end

generated and inserted data into ##Result:

declare @script varchar(max)

set @script = 'create table ##Result' + @sp +
            '(ROW_NUM int'+
            ',BRANCH_ID int' +
            ',PARAM_NAME varchar(25)' +
            @PeriodsScript + ')'       

execute ( @script )

execute(
            'insert into ##Result' + @sp + '( ROW_NUM, BRANCH_ID, NOM_SIZE_ID, PARAM_NAME )' +
            'select ( row_number() over( order by BRANCH_ID, NOM_SIZE_ID ) - 1 ) * 3 + 1' +
            '        ,BRANCH_ID' +
            '        ,NOM_SIZE_ID' +
            '        ,''Min.price''' +
            '  from ( ' +
            '        select distinct BRANCH_ID' +
            '                ,NOM_SIZE_ID' +
            '          from ##ResultNomSizePrices' + @sp +
            '        ) as t'
            )

and finaly, select from result table:

set @script = 
            'select distinct gb.TINY_NAME'+
            '        ,r.GROUP_NAME_1 as group1'+
            '        ,r.GROUP_NAME_2 as group2'+
            '        ,r.GROUP_NAME_3 as group3'+
            '        ,r.PARAM_NAME'+
            '        ,r.ROW_NUM'+
            @PeriodsScript +
            '  from ##Result' + @sp + ' as r'+
            ' inner join dbo.GD_BRANCHES as gb'+
            '    on r.BRANCH_ID = gb.BRANCH_ID'+
            ' order by gb.TINY_NAME'+
            '        ,r.GROUP_NAME_1'+
            '        ,r.GROUP_NAME_2'+
            '        ,r.GROUP_NAME_3'+
            '        ,r.ROW_NUM'

 execute ( @script )

p.s. sry for my english

How should I fix this code with RuntimeError: Catalog Error: Schema with name a does not exist!? (SQL)

You have the wrong query to concat string, here is how you can fix it:

CONCAT(a.FirstName , ' ' ,a.LastName) as FullName

SQL Server : USE instruction in stored procedure

Slim

This code will work

    SELECT @SQL = N'IF NOT EXISTS (SELECT * FROM [' + @SourceDatabase + '].SYS.SCHEMAS WHERE NAME = ''' + @DestinationSchema + ''')
        BEGIN
            DECLARE @SQL NVARCHAR(MAX);

            SELECT @SQL = N''USE ['+ @SourceDatabase +']; 
                            EXEC(''''CREATE SCHEMA '+ @DestinationSchema +' '''')''
            EXEC sp_executesql @SQL
        END'
    EXEC sp_executesql @SQL         

As Sandip spotted you need to declare the @SQL variable in your dynamic code. When you use sp_executesql to run the dynamic code, it is effectively running in isolation to your main stored proc, and thus knows nothing about any variables declared previously. You'll see I've added a few more single quotes to get everything working; also replaced the [] in the SELECT statement so that @DestinationSchema appears as text, and not a column name.

As an aside, do you know how to use the debug tools in SQL Server Management Studio? Type the following

EXEC usp_copytable_TEST 'SourceServer', 'SourceDatabase', 'SourceSchema', 'DestinationSchema'

on its own in a new query window. Instead of pressing F5 to execute, press ALT+F5, this will invoke a debug session and you can now step through the code line by line, watch variables and so on. You'll find this very helpful as you can see the values of your dynamic SQL strings.

---

Related Topics