SQL Server 2017 - Database mail stored procedure
0
I have a stored procedure which basically I want to do the following:
- Create temp table (if not exists) and populate with data
- Output the query to SSMS, and assigning the query a variable (@sql)
- Using the query, e-mail the contents of the query to the recipients
My script is this:
Create Procedure ListDaysofYear(@year as integer)
as
Declare @sql as varchar(200), @DBqry as varchar(200),
@tab as char(1) = char(9)
Declare @dayofyear as bigint = 1
Declare @monthofyear as int = 1
Declare @day as int = 1
Declare @curDate as datetime
Declare @DB as varchar(40)
Declare @sql2 as varchar(40)
Set @curDate = datefromparts(@year, @monthofyear, @day)
Set @DB = 'msdb'
IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL
DROP TABLE ##daysofYear
--Print 'YES'
ELSE
CREATE TABLE ##daysofYear
(
cDate DATETIME PRIMARY KEY NOT NULL,
cMonth VARCHAR(20) NOT NULL,
cDay VARCHAR(20) NOT NULL
)
WHILE year(@curDate) = @year
BEGIN
-- Insert rows based on each day of the year
INSERT INTO ##daysofYear (cDate, cMonth, cDay)
VALUES( (@curDate),
(DATENAME([MONTH], @curDate)),
(DATENAME([WEEKDAY], @curDate)) )
SET @curDate = @curDate + 1
END
--Output file to SSMS query window
Select dy.* from ##daysofYear dy;
Set @sql = 'Select dy.* from ##daysofYear dy;'
Set @sql2 = 'Use ' + @DB + '; Exec msdb.dbo.sp_send_dbmail
@profile_name = ''Notifications'',
@recipients = ''mikemirabelli6@hotmail.com'',
@attach_query_result_as_file = 1,
@query_attachment_filename = ''daysofyear.txt'',
@query_result_separator = '',
@body = ''The attached output file - DaysofYear table'',
@query = ''Select dy.* from ##daysofYear dy'' ;'
--Execute sp_sqlexec @sql
Exec(@sql2)
Basically when I run the execute line:
Exec dbo.ListDaysofYear 2018 ;
I get the following message the first time:
Msg 208, Level 16, State 0, Procedure dbo.ListDaysofYear, Line 25
[Batch Start Line 52] Invalid object name '##daysofYear
I believe it’s related to the "DROP TABLE" part of the T-SQL.
Thanks
sql-server-2017 sp-send-dbmail drop-table ssms-2017 edited Nov 21 '18 at 5:57
marc_s
579k12911181264
asked Nov 20 '18 at 21:19
Mike MirabelliMike Mirabelli
150110
|
show 6 more comments
0
I have a stored procedure which basically I want to do the following:
- Create temp table (if not exists) and populate with data
- Output the query to SSMS, and assigning the query a variable (@sql)
- Using the query, e-mail the contents of the query to the recipients
My script is this:
Create Procedure ListDaysofYear(@year as integer)
as
Declare @sql as varchar(200), @DBqry as varchar(200),
@tab as char(1) = char(9)
Declare @dayofyear as bigint = 1
Declare @monthofyear as int = 1
Declare @day as int = 1
Declare @curDate as datetime
Declare @DB as varchar(40)
Declare @sql2 as varchar(40)
Set @curDate = datefromparts(@year, @monthofyear, @day)
Set @DB = 'msdb'
IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL
DROP TABLE ##daysofYear
--Print 'YES'
ELSE
CREATE TABLE ##daysofYear
(
cDate DATETIME PRIMARY KEY NOT NULL,
cMonth VARCHAR(20) NOT NULL,
cDay VARCHAR(20) NOT NULL
)
WHILE year(@curDate) = @year
BEGIN
-- Insert rows based on each day of the year
INSERT INTO ##daysofYear (cDate, cMonth, cDay)
VALUES( (@curDate),
(DATENAME([MONTH], @curDate)),
(DATENAME([WEEKDAY], @curDate)) )
SET @curDate = @curDate + 1
END
--Output file to SSMS query window
Select dy.* from ##daysofYear dy;
Set @sql = 'Select dy.* from ##daysofYear dy;'
Set @sql2 = 'Use ' + @DB + '; Exec msdb.dbo.sp_send_dbmail
@profile_name = ''Notifications'',
@recipients = ''mikemirabelli6@hotmail.com'',
@attach_query_result_as_file = 1,
@query_attachment_filename = ''daysofyear.txt'',
@query_result_separator = '',
@body = ''The attached output file - DaysofYear table'',
@query = ''Select dy.* from ##daysofYear dy'' ;'
--Execute sp_sqlexec @sql
Exec(@sql2)
Basically when I run the execute line:
Exec dbo.ListDaysofYear 2018 ;
I get the following message the first time:
Msg 208, Level 16, State 0, Procedure dbo.ListDaysofYear, Line 25
[Batch Start Line 52] Invalid object name '##daysofYear
I believe it’s related to the "DROP TABLE" part of the T-SQL.
Thanks
sql-server-2017 sp-send-dbmail drop-table ssms-2017 edited Nov 21 '18 at 5:57
marc_s
579k12911181264
asked Nov 20 '18 at 21:19
Mike MirabelliMike Mirabelli
150110
I noticed that when I try to write anything as simple in the @query parameter for send mail SP, I get this message, otherwise that second error NEVER fires. SOmething whenever I write a simple select (Ex Select 1), always returns this message.
– Mike Mirabelli
Nov 20 '18 at 23:33
I don’t think temp tables are accessible via dynamic SQL. Can you not use real table and clean them up at the end?
– sheeni
Nov 20 '18 at 23:47
I thought of that but when I tried using a regular table , the same result - the IF is not firing as intended within the query. It's strange when I do the following : 'IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Print 'Exists' ELSE Print 'Does Not Exist' IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Drop Table ##daysofYear ELSE Create Table ##daysofYear (id# int primary key); ' And I go back and forth it works as expected.
– Mike Mirabelli
Nov 21 '18 at 0:06
1
NVM, think i found the issue, IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE. Let me know if this works i will add it as the answer
– sheeni
Nov 21 '18 at 0:46
1
you can have multiple statements if you add BEGIN and END underneath your if and else. You can have multiple lines of code between BEGIN and END
– sheeni
Nov 21 '18 at 1:11
|
show 6 more comments
0
0
0
I have a stored procedure which basically I want to do the following:
- Create temp table (if not exists) and populate with data
- Output the query to SSMS, and assigning the query a variable (@sql)
- Using the query, e-mail the contents of the query to the recipients
My script is this:
Create Procedure ListDaysofYear(@year as integer)
as
Declare @sql as varchar(200), @DBqry as varchar(200),
@tab as char(1) = char(9)
Declare @dayofyear as bigint = 1
Declare @monthofyear as int = 1
Declare @day as int = 1
Declare @curDate as datetime
Declare @DB as varchar(40)
Declare @sql2 as varchar(40)
Set @curDate = datefromparts(@year, @monthofyear, @day)
Set @DB = 'msdb'
IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL
DROP TABLE ##daysofYear
--Print 'YES'
ELSE
CREATE TABLE ##daysofYear
(
cDate DATETIME PRIMARY KEY NOT NULL,
cMonth VARCHAR(20) NOT NULL,
cDay VARCHAR(20) NOT NULL
)
WHILE year(@curDate) = @year
BEGIN
-- Insert rows based on each day of the year
INSERT INTO ##daysofYear (cDate, cMonth, cDay)
VALUES( (@curDate),
(DATENAME([MONTH], @curDate)),
(DATENAME([WEEKDAY], @curDate)) )
SET @curDate = @curDate + 1
END
--Output file to SSMS query window
Select dy.* from ##daysofYear dy;
Set @sql = 'Select dy.* from ##daysofYear dy;'
Set @sql2 = 'Use ' + @DB + '; Exec msdb.dbo.sp_send_dbmail
@profile_name = ''Notifications'',
@recipients = ''mikemirabelli6@hotmail.com'',
@attach_query_result_as_file = 1,
@query_attachment_filename = ''daysofyear.txt'',
@query_result_separator = '',
@body = ''The attached output file - DaysofYear table'',
@query = ''Select dy.* from ##daysofYear dy'' ;'
--Execute sp_sqlexec @sql
Exec(@sql2)
Basically when I run the execute line:
Exec dbo.ListDaysofYear 2018 ;
I get the following message the first time:
Msg 208, Level 16, State 0, Procedure dbo.ListDaysofYear, Line 25
[Batch Start Line 52] Invalid object name '##daysofYear
I believe it’s related to the "DROP TABLE" part of the T-SQL.
Thanks
sql-server-2017 sp-send-dbmail drop-table ssms-2017 edited Nov 21 '18 at 5:57
marc_s
579k12911181264
asked Nov 20 '18 at 21:19
Mike MirabelliMike Mirabelli
150110
I have a stored procedure which basically I want to do the following:
- Create temp table (if not exists) and populate with data
- Output the query to SSMS, and assigning the query a variable (@sql)
- Using the query, e-mail the contents of the query to the recipients
My script is this:
Create Procedure ListDaysofYear(@year as integer)
as
Declare @sql as varchar(200), @DBqry as varchar(200),
@tab as char(1) = char(9)
Declare @dayofyear as bigint = 1
Declare @monthofyear as int = 1
Declare @day as int = 1
Declare @curDate as datetime
Declare @DB as varchar(40)
Declare @sql2 as varchar(40)
Set @curDate = datefromparts(@year, @monthofyear, @day)
Set @DB = 'msdb'
IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL
DROP TABLE ##daysofYear
--Print 'YES'
ELSE
CREATE TABLE ##daysofYear
(
cDate DATETIME PRIMARY KEY NOT NULL,
cMonth VARCHAR(20) NOT NULL,
cDay VARCHAR(20) NOT NULL
)
WHILE year(@curDate) = @year
BEGIN
-- Insert rows based on each day of the year
INSERT INTO ##daysofYear (cDate, cMonth, cDay)
VALUES( (@curDate),
(DATENAME([MONTH], @curDate)),
(DATENAME([WEEKDAY], @curDate)) )
SET @curDate = @curDate + 1
END
--Output file to SSMS query window
Select dy.* from ##daysofYear dy;
Set @sql = 'Select dy.* from ##daysofYear dy;'
Set @sql2 = 'Use ' + @DB + '; Exec msdb.dbo.sp_send_dbmail
@profile_name = ''Notifications'',
@recipients = ''mikemirabelli6@hotmail.com'',
@attach_query_result_as_file = 1,
@query_attachment_filename = ''daysofyear.txt'',
@query_result_separator = '',
@body = ''The attached output file - DaysofYear table'',
@query = ''Select dy.* from ##daysofYear dy'' ;'
--Execute sp_sqlexec @sql
Exec(@sql2)
Basically when I run the execute line:
Exec dbo.ListDaysofYear 2018 ;
I get the following message the first time:
Msg 208, Level 16, State 0, Procedure dbo.ListDaysofYear, Line 25
[Batch Start Line 52] Invalid object name '##daysofYear
I believe it’s related to the "DROP TABLE" part of the T-SQL.
Thanks
sql-server-2017 sp-send-dbmail drop-table ssms-2017 sql-server-2017 sp-send-dbmail drop-table ssms-2017 edited Nov 21 '18 at 5:57
marc_s
579k12911181264
asked Nov 20 '18 at 21:19
Mike MirabelliMike Mirabelli
150110
edited Nov 21 '18 at 5:57
marc_s
579k12911181264
asked Nov 20 '18 at 21:19
Mike MirabelliMike Mirabelli
150110
edited Nov 21 '18 at 5:57
marc_s
579k12911181264
edited Nov 21 '18 at 5:57
marc_s
579k12911181264
edited Nov 21 '18 at 5:57
marc_s
579k12911181264
579k12911181264
asked Nov 20 '18 at 21:19
Mike MirabelliMike Mirabelli
150110
asked Nov 20 '18 at 21:19
Mike MirabelliMike Mirabelli
150110
asked Nov 20 '18 at 21:19
Mike MirabelliMike Mirabelli
150110
150110
I noticed that when I try to write anything as simple in the @query parameter for send mail SP, I get this message, otherwise that second error NEVER fires. SOmething whenever I write a simple select (Ex Select 1), always returns this message.
– Mike Mirabelli
Nov 20 '18 at 23:33
I don’t think temp tables are accessible via dynamic SQL. Can you not use real table and clean them up at the end?
– sheeni
Nov 20 '18 at 23:47
I thought of that but when I tried using a regular table , the same result - the IF is not firing as intended within the query. It's strange when I do the following : 'IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Print 'Exists' ELSE Print 'Does Not Exist' IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Drop Table ##daysofYear ELSE Create Table ##daysofYear (id# int primary key); ' And I go back and forth it works as expected.
– Mike Mirabelli
Nov 21 '18 at 0:06
1
NVM, think i found the issue, IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE. Let me know if this works i will add it as the answer
– sheeni
Nov 21 '18 at 0:46
1
you can have multiple statements if you add BEGIN and END underneath your if and else. You can have multiple lines of code between BEGIN and END
– sheeni
Nov 21 '18 at 1:11
|
show 6 more comments
I noticed that when I try to write anything as simple in the @query parameter for send mail SP, I get this message, otherwise that second error NEVER fires. SOmething whenever I write a simple select (Ex Select 1), always returns this message.
– Mike Mirabelli
Nov 20 '18 at 23:33
I don’t think temp tables are accessible via dynamic SQL. Can you not use real table and clean them up at the end?
– sheeni
Nov 20 '18 at 23:47
I thought of that but when I tried using a regular table , the same result - the IF is not firing as intended within the query. It's strange when I do the following : 'IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Print 'Exists' ELSE Print 'Does Not Exist' IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Drop Table ##daysofYear ELSE Create Table ##daysofYear (id# int primary key); ' And I go back and forth it works as expected.
– Mike Mirabelli
Nov 21 '18 at 0:06
1
NVM, think i found the issue, IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE. Let me know if this works i will add it as the answer
– sheeni
Nov 21 '18 at 0:46
1
you can have multiple statements if you add BEGIN and END underneath your if and else. You can have multiple lines of code between BEGIN and END
– sheeni
Nov 21 '18 at 1:11
I noticed that when I try to write anything as simple in the @query parameter for send mail SP, I get this message, otherwise that second error NEVER fires. SOmething whenever I write a simple select (Ex Select 1), always returns this message.
– Mike Mirabelli
Nov 20 '18 at 23:33
I noticed that when I try to write anything as simple in the @query parameter for send mail SP, I get this message, otherwise that second error NEVER fires. SOmething whenever I write a simple select (Ex Select 1), always returns this message.
– Mike Mirabelli
Nov 20 '18 at 23:33
I don’t think temp tables are accessible via dynamic SQL. Can you not use real table and clean them up at the end?
– sheeni
Nov 20 '18 at 23:47
I don’t think temp tables are accessible via dynamic SQL. Can you not use real table and clean them up at the end?
– sheeni
Nov 20 '18 at 23:47
I thought of that but when I tried using a regular table , the same result - the IF is not firing as intended within the query. It's strange when I do the following : 'IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Print 'Exists' ELSE Print 'Does Not Exist' IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Drop Table ##daysofYear ELSE Create Table ##daysofYear (id# int primary key); ' And I go back and forth it works as expected.
– Mike Mirabelli
Nov 21 '18 at 0:06
I thought of that but when I tried using a regular table , the same result - the IF is not firing as intended within the query. It's strange when I do the following : 'IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Print 'Exists' ELSE Print 'Does Not Exist' IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Drop Table ##daysofYear ELSE Create Table ##daysofYear (id# int primary key); ' And I go back and forth it works as expected.
– Mike Mirabelli
Nov 21 '18 at 0:06
1
1
NVM, think i found the issue, IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE. Let me know if this works i will add it as the answer
– sheeni
Nov 21 '18 at 0:46
NVM, think i found the issue, IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE. Let me know if this works i will add it as the answer
– sheeni
Nov 21 '18 at 0:46
1
1
you can have multiple statements if you add BEGIN and END underneath your if and else. You can have multiple lines of code between BEGIN and END
– sheeni
Nov 21 '18 at 1:11
you can have multiple statements if you add BEGIN and END underneath your if and else. You can have multiple lines of code between BEGIN and END
– sheeni
Nov 21 '18 at 1:11
|
show 6 more comments
1 Answer
1
active
oldest
votes
1
Think i found the issue:
IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE.
answered Nov 21 '18 at 1:12
sheenisheeni
14310
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53401685%2fsql-server-2017-database-mail-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Think i found the issue:
IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE.
answered Nov 21 '18 at 1:12
sheenisheeni
14310
add a comment |
1
Think i found the issue:
IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE.
answered Nov 21 '18 at 1:12
sheenisheeni
14310
add a comment |
1
1
1
Think i found the issue:
IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE.
answered Nov 21 '18 at 1:12
sheenisheeni
14310
Think i found the issue:
IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE.
answered Nov 21 '18 at 1:12
sheenisheeni
14310
answered Nov 21 '18 at 1:12
sheenisheeni
14310
answered Nov 21 '18 at 1:12
sheenisheeni
14310
answered Nov 21 '18 at 1:12
sheenisheeni
14310
14310
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53401685%2fsql-server-2017-database-mail-stored-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I noticed that when I try to write anything as simple in the @query parameter for send mail SP, I get this message, otherwise that second error NEVER fires. SOmething whenever I write a simple select (Ex Select 1), always returns this message.
– Mike Mirabelli
Nov 20 '18 at 23:33
I don’t think temp tables are accessible via dynamic SQL. Can you not use real table and clean them up at the end?
– sheeni
Nov 20 '18 at 23:47
I thought of that but when I tried using a regular table , the same result - the IF is not firing as intended within the query. It's strange when I do the following : 'IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Print 'Exists' ELSE Print 'Does Not Exist' IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Drop Table ##daysofYear ELSE Create Table ##daysofYear (id# int primary key); ' And I go back and forth it works as expected.
– Mike Mirabelli
Nov 21 '18 at 0:06
1
NVM, think i found the issue, IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE. Let me know if this works i will add it as the answer
– sheeni
Nov 21 '18 at 0:46
1
you can have multiple statements if you add BEGIN and END underneath your if and else. You can have multiple lines of code between BEGIN and END
– sheeni
Nov 21 '18 at 1:11