March 19, 2018 at 5:40 am
Hello,
I have a job that consists of two very simple steps:
Step 1:
INSERT INTO MyTable
SELECT * FROM DatabaseTable
Step 2:
exec sp_MyProcedure
sp_MyProcedure is simply "SELECT * FROM MyTable"
I have excepted to receive records back but i don't get them back in Results window but i don't. Is it possible to do so?
March 19, 2018 at 5:55 am
SELECT * FROM MyTable;
If that statement is returning no results, it's because there are no rows in the table MyTable. If, prior to that, you are inserting into that table, and getting no results that means one of 2 things:
Note, for example, that the following works:CREATE TABLE MyTable (ID int, String varchar(10));
INSERT INTO MyTable VALUES (1,'asdas'),(2,'dgaids'),(3,'asdjhasvd');
CREATE TABLE YourTable (ID int, String varchar(10));
GO
CREATE PROC MyProc AS
SELECT *
FROM YourTable;
GO
INSERT INTO YourTable
SELECT *
FROM MyTable;
GO
EXEC MyProc;
GO
--Clean Up
DROP TABLE MyTable;
DROP TABLE YourTable;
DROP PROC MyProc;
GO
If, on the other hand we change the DDL of YourTable a little...CREATE TABLE MyTable (ID int, String varchar(10));
INSERT INTO MyTable VALUES (1,'asdas'),(2,'dgaids'),(3,'asdjhasvd');
CREATE TABLE YourTable (ID int, String varchar(3));
GO
CREATE PROC MyProc AS
SELECT *
FROM YourTable;
GO
INSERT INTO YourTable
SELECT *
FROM MyTable;
GO
EXEC MyProc;
GO
--Clean Up
DROP TABLE MyTable;
DROP TABLE YourTable;
DROP PROC MyProc;
GO
Now it returns no results as the INSERT failed due to a truncation error.
On a totally different note, avoid prefixing your stored procedures with "sp_": https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 19, 2018 at 6:03 am
lukaszpiech - Monday, March 19, 2018 5:40 AMHello,
I have a job that consists of two very simple steps:
Step 1:
INSERT INTO MyTable
SELECT * FROM DatabaseTable
Step 2:
exec sp_MyProceduresp_MyProcedure is simply "SELECT * FROM MyTable"
I have excepted to receive records back but i don't get them back in Results window but i don't. Is it possible to do so?
Is this a SQL Agent job? These are designed to run unattanded and not in the context of any other users' sessions, so I am not surprised that you do not see any output.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 19, 2018 at 6:23 am
Thank you both for your replies.
There are records in DatabaseTable and insert works fine, i've checked. So i guess that Phil Parkin answer is more suitable in this case.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply