Stored Procedure Dynamic SQL using sp_executesql Not working

Question

Post reply

Stored Procedure Dynamic SQL using sp_executesql Not working

Charles_P

Old Hand

Points: 352
More actions
January 20, 2018 at 6:25 pm

#331012

I can successfully run a Stored Procedure (SP) that uses dynamic SQL and Exec sp_executesql in my local environment. But when I run the same SP in our dev environment
I get "default schema does not exist", which is not true. I create schema SP all the time and it runs fine. If I comment out the Exec sp_executesql when running in Dev environment it runs. So, I think the Exec sp_executesql runs ok in my local but not in the Dev environment. Can you think of another way to execute the SP with dynamic SQL in our Dev environment??
Hoping someone has resolved this issue of running a SP with dynamic SQL using Exec sp_executesql in a dev or text environment.
Below is my SP code.
Thanks,
Charles P.
CREATE PROCEDURE [Rcn].[uspRpts]
@key int
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('Report', 'U') IS NOT NULL
DROP TABLE Report;
DECLARE @ColumnsForCREATE nvarchar(MAX)
DECLARE @ColumnsForSELECT nvarchar(MAX)
SET @ColumnsForCREATE = ''
SET @ColumnsForSELECT = ''
select @ColumnsForCREATE = @ColumnsForCREATE + '['+ RTRIM(Header) + '] nvarchar(255), ' from rcn.tblRefReportTemplateLayout WHERE reporttemplatekey = @key order by HeaderOrder
select @ColumnsForSELECT = @ColumnsForSELECT + '['+ RTRIM(TableField) + '] AS [' + RTRIM(Header) +'], ' from rcn.tblRefReportTemplateLayout WHERE reconreporttemplatekey = @key order by HeaderOrder
SET @ColumnsForCREATE = SUBSTRING(@ColumnsForCREATE, 0, LEN(@ColumnsForCREATE))
SET @ColumnsForSELECT = SUBSTRING(@ColumnsForSELECT, 0, LEN(@ColumnsForSELECT))
SET @ColumnsForCREATE = 'CREATE TABLE Report (' + @ColumnsForCREATE +')'
SET @ColumnsForSELECT = 'INSERT INTO Report SELECT ' + @ColumnsForSELECT + ' FROM rcn.tblMaster'
Exec sp_executesql @ColumnsForCREATE
Exec sp_executesql @ColumnsForSELECT
SELECT * FROM REPORT
END

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick thought, add the schema name to the code to make the object references unambiguous.
😎

Paulo de Jesus Default port Points: 1403 More actions · Answer 2

It could have something to do with the default schema assigned to the user you are executing the code with.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

Charles_P - Saturday, January 20, 2018 6:25 PM
I can successfully run a Stored Procedure (SP) that uses dynamic SQL and Exec sp_executesql in my local environment. But when I run the same SP in our dev environment
I get "default schema does not exist", which is not true. I create schema SP all the time and it runs fine. If I comment out the Exec sp_executesql when running in Dev environment it runs. So, I think the Exec sp_executesql runs ok in my local but not in the Dev environment. Can you think of another way to execute the SP with dynamic SQL in our Dev environment??
Hoping someone has resolved this issue of running a SP with dynamic SQL using Exec sp_executesql in a dev or text environment.
Below is my SP code.
Thanks,
Charles P.
CREATE PROCEDURE [Rcn].[uspRpts]
@key int
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('Report', 'U') IS NOT NULL
DROP TABLE Report;
DECLARE @ColumnsForCREATE nvarchar(MAX)
DECLARE @ColumnsForSELECT nvarchar(MAX)
SET @ColumnsForCREATE = ''
SET @ColumnsForSELECT = ''
select @ColumnsForCREATE = @ColumnsForCREATE + '['+ RTRIM(Header) + '] nvarchar(255), ' from rcn.tblRefReportTemplateLayout WHERE reporttemplatekey = @key order by HeaderOrder
select @ColumnsForSELECT = @ColumnsForSELECT + '['+ RTRIM(TableField) + '] AS [' + RTRIM(Header) +'], ' from rcn.tblRefReportTemplateLayout WHERE reconreporttemplatekey = @key order by HeaderOrder
SET @ColumnsForCREATE = SUBSTRING(@ColumnsForCREATE, 0, LEN(@ColumnsForCREATE))
SET @ColumnsForSELECT = SUBSTRING(@ColumnsForSELECT, 0, LEN(@ColumnsForSELECT))
SET @ColumnsForCREATE = 'CREATE TABLE Report (' + @ColumnsForCREATE +')'
SET @ColumnsForSELECT = 'INSERT INTO Report SELECT ' + @ColumnsForSELECT + ' FROM rcn.tblMaster'
Exec sp_executesql @ColumnsForCREATE
Exec sp_executesql @ColumnsForSELECT
SELECT * FROM REPORT
END

The only way that someone could troubleshoot this remotely (because we don't have access to your tables/data), is for you to print out the content of the following variables (add the following print statements just before the first EXEC). When you run the proc in the environment you're having difficulty in, make sure that you're in the text mode and then copy the output a paste it in another post so we can have a look at it.

PRINT @ColumnsForCREATE;
PRINT @ColumnsForSELECT;

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Charles_P Old Hand Points: 352 More actions · Answer 4

Thank Jeff!

Using the text mode for
PRINT @ColumnsForCREATE;
PRINT @ColumnsForSELECT;
It clearly showed me that I was missing the schema. It now runs perfectly in the dev environment.

Thanks again for the troubleshooting tip.
Charles P.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 5

Charles_P - Sunday, January 21, 2018 8:18 PM
Thank Jeff!
Using the text mode for
PRINT @ColumnsForCREATE;
PRINT @ColumnsForSELECT;
It clearly showed me that I was missing the schema. It now runs perfectly in the dev environment.
Thanks again for the troubleshooting tip.
Charles P.

Thanks for the feedback, Charles. Heh... yeah... printing the contents of variables is one of the oldest troubleshooting methods there is and should be one of the first things done. You don't even need any special privs to run DEBUG to do it. 😀

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Slightly off topic but this procedure throws up huge red flags to me. It seems that you are using the same table for various different table layouts. This is not usually a very good idea. Add to that you have a strong likelihood of concurrency issues going on here. Something tells me this approach to reporting is not going to hold up well in a busy system with multiple users. You are going to have people dropping tables of data from underneath other users.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Why would you create a table, insert rows and then select the new table?
Why can't you simply do a straight select?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Luis Cazares - Monday, January 22, 2018 8:46 AM
Why would you create a table, insert rows and then select the new table?
Why can't you simply do a straight select?

If I had a nickel for every time I have seen this in client code over the years, I would be living on my own private island. Like Oahu!!!! 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

MMartin1 One Orange Chip Points: 27795 More actions · Answer 9

TheSQLGuru - Monday, January 22, 2018 11:13 AM
Luis Cazares - Monday, January 22, 2018 8:46 AM
Why would you create a table, insert rows and then select the new table?
Why can't you simply do a straight select?
If I had a nickel for every time I have seen this in client code over the years, I would be living on my own private island. Like Oahu!!!! 😎

It looks like he really want s to be able to create a generic script that recreates tables locally, that are existing in another system, it appears. I can easily recreate the schema of an existing table with

Select * Into dbo.myNewTable From dbo.myOldTable Where 1=0

If this is the case Maybe a linked server to the other systems table and apply the above ? Or just SSIS if available to move data? I guessing a little here on what prompted this script.

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