ERROR:- An INSERT EXEC statement cannot be nested.

HI,

WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.

BUT WE ARE GETTING A FEW HICCUPS. PLS HELP

THIS IS HOW IT GOES :-

CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .......;
ST2........;
END

CREATE PROCEDURE MY_PROC2
AS
BEGIN

CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP2
EXEC MY_PROC1

ST1 .......;
ST2........;

END

THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED

NO PROBLEM TILL NOW......

BUT,

CREATE PROCEDURE MY_PROC3
AS
BEGIN

CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP3
EXEC MY_PROC2

ST1 .......;
ST2........;

END

THEN IT GIVES AN ERROR AS :-

"An INSERT EXEC statement cannot be nested."

CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE......

WHAT IS THE NESTING LEVEL OF A PROCEDURE ?

IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?

PLS HELP ME OUT IN THIS

THANKSYou can nest stored procedures pretty deep (I think it's 32 levels or so). What you cannot do is have an INSERT #TMP3 EXEC proc1 in one procedure and have the next procedure that calls it with an INSERT #TEMP EXEC proc2.

You have to architect around this limitation. There is no setting to change the above that I'm aware of.|||Hi,

Thanks Derrick, But Is There A Way You Know To Get Around This One.

I Could Use Permanent Tables To Get Around This But It Takes Too Much Space,which Is A Constraint In Our Case.

Have To Use Temp Table :-

Is There A Way Around It ,as I Am Using The Results Of The First Procedure To Drive The Second One And The Results Of The Second One To Drive The Third .

Pls , If You Know Of Anything Pls Let Me Know.

Thanks.|||Without manual intervention, one level of INSERT...EXECUTE is the limit.

To do what you want, you really need an N-tier server. You can sort of kludge it via multiple instances, but you run out of RAM pretty quickly. You can also kludge it by compounding your cursor (rolling up all of the logically nested SELECTs into a single monster query).

There are a number of choices available, but due to your tight restrictions on RAM and disk, very few of those choices make good sense.

-PatP|||Hi,

I Got The Point Of Set -level Processing Rather Than Row-level Processing As You Had Said Before.so I Might Do Away With The Cursor Thing Altogether.but Is There Any Way To Use Mulitple Insert..exec Statement.

Now,that I Am Not Using Cursors ,i Don't Think Ram And Disk Matters Much Now.

Is There Any Way Out Of It Now ? Pls Do Help

Thanks.|||No. That's what we're trying to tell you. What you can do is have multiple queries that INSERT into a regular "process" table, which is just a normal table that records processes. You can then have a wrapper query that runs these in order. As long as all of your inserts are occurring on the second level, as opposed to the first order of queries meaning the wrapper query, you can run these infinitely.

Our EMC SnapClone process uses this methodology.|||Hi,

Thanks Derrick For The Help,but Frankly Speaking Being Still A Newcomer In The Field Of Ms-sql Databases.some Of The Words You Have Said Have Escaped My Vivid Imagination. Could You Pls Explain Me.

First,

What Do You Mean By A Wrapper Query ?

Second,

How To Implement It In Out Here ?

Could You Pls Explain This ?

Thanks.|||First,

What Do You Mean By A Wrapper Query ?
--This is just a query that call several subqueries and has overall control of a process.

Second,

How To Implement It In Out Here ?

Have each query right the results to a regular table. The query controls the overall process by reading these tables and deciding which query it needs to run next. As long as you stay one level under the wrapper query, you can run as many of these INSERT EXEC statements as you need to. If you post your query, I should be able to help you out more.|||DROP PROCEDURE PROC1
CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID
END

DROP PROCEDURE PROC2
CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 6 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 7 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 2 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 3 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM #SAATHI A
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

DROP PROCEDURE PROC3
CREATE PROCEDURE PROC3
AS
BEGIN
CREATE TABLE #SAATH2(INTCUSTOMERID INT,TOTAL_COUNTS INT)

INSERT INTO #SAATH2
EXEC PROC2

DECLARE @.EKEK INT
DECLARE @.KAUNSARE VARCHAR(100)
SET @.EKEK = 1
SET @.KAUNSARE = 'International Pop'
WHILE @.EKEK <= 3
BEGIN
SELECT @.KAUNSARE AS NAAMRE,COUNT(*) AS TOTAL_COUNTS
FROM SAATH
WHERE SUBSTRING(PREFER,@.EKEK,1) = 1
GROUP BY SUBSTRING(PREFER,@.EKEK,1)
SET @.EKEK = @.EKEK + 1
IF @.EKEK=2
BEGIN
SET @.KAUNSARE = 'International Rock'
END
IF @.EKEK=3
BEGIN
SET @.KAUNSARE = 'Hindi Pop'
END
END
END

OUT HERE, THE PROBLEM ARISES IN PROC3 ,

THE THING IS THE RESULT OF 1 PROC IS I/P TO SECOND ONE AND THE RESULT OF 2 PROC IS I/P TO THIRD ONE.

COULD YOU DO SOMETHING ABOUT IT ?

HOW COULD WE IMPLEMENT WRAPPER QUERY OUT HERE, AS MAINTAINING ALL 3 PROCS ARE NECESSARY AS THEY ARE ALSO I/P'S TO OTHER PROCS.

AND SO ON ....

THANKS|||Here is an example of how to do it. ME is a linked server back to the same.

create proc a
as
select a = 'a'
go

create proc b
as
create table #b (b varchar(32) not null)
insert #b exec ME.master.dbo.a
select * from #b
go

create proc c
as
create table #c (c varchar(32) not null)
insert #c exec ME.master.dbo.b
select * from #c
go

create proc d
as
create table #d (d varchar(32) not null)
insert #d exec ME.master.dbo.c
select * from #d
go

exec d
go|||HI,

WHAT DO YOU MEAN BY :-
"ME is a linked server back to the same."

AND ANY WAY IF I AM USING : -

create proc a
as
select a = 'a'
go

create proc b
as
create table #b (b varchar(32) not null)
insert #b exec a
select * from #b
go

create proc c
as
create table #c (c varchar(32) not null)
insert #c exec b
select * from #c
go

create proc d
as
create table #d (d varchar(32) not null)
insert #d exec C
select * from #d
go

exec d
go

I AM GETTING AN ERROR WHICH IWAS GETTING BEFORE :-

"An INSERT EXEC statement cannot be nested."

HASN'T MADE MUCH DIFFERENCE ?

WHAT IS ME .. IS IT PRESENT IN ALL OF THE SQL SERVER 2000 ?

OR IS IT SOMETHING VIRTUAL THAT YOU HAVE CREATED ?

THANKS.|||"ME" is a linked server. In Enterprise Manager, open the Security folder, right click on Linked Servers, choose New Linked Server from the menu. I named the server "ME." Select "Microsoft OLE DB Provider for SQL Server" as the provider. Enter the actual name of your server in the Data Source field. On the security tab, select "Be made using the login's current security context." On the server options tab, check all of the check boxes.|||HI,

I AM GETTING AN ERROR THAT SAYS :-

"Could not find stored procedure 'master.dbo.a'.
Could not relay results of procedure 'a' from remote server 'ME'."

COULD YOU PLS EXPLAIN ME THE FUNDA OF LINKED SERVERS .?

I AM JUST BLINDLY F9OLLOWING YOU. AND GETTING NOWHERE .

PLS EXPLAIN IT TO ME.

THANKS,

CHETAN B.|||HI,

I AM GETTING AN ERROR THAT SAYS :-

"Could not find stored procedure 'master.dbo.a'.
Could not relay results of procedure 'a' from remote server 'ME'."

COULD YOU PLS EXPLAIN ME THE FUNDA OF LINKED SERVERS .?

I AM JUST BLINDLY FOLLOWING YOU. AND GETTING NOWHERE .

PLS EXPLAIN IT TO ME.

THANKS,

CHETAN B.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
(select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
> (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
[vbcol=seagreen]
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
[vbcol=seagreen]
>
>|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>
> Ok, did that, but see error below.
>
>
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name strin
g.
> You could try declaring your parameters as type varchar, but you have to b
e
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' = (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
> > Ok, I previously had a thread about "Column name as variable".
> > Here's a proc I wrote, and it compiles ok:
> > alter PROCEDURE rptRRTP_By_Date_Range
> > -- Add the parameters for the stored procedure here
> > @.Update_Field as sysname,
> > @.start_date as datetime,
> > @.end_date as datetime,
> > @.contr_Stat as varchar(2),
> > @.seq as int
> > AS
> > declare @.sql_stat as varchar(255)
> > BEGIN
> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> > ',' + @.end_date + ')
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
> > where sequence = ' + @.seq
> > Execute (@.sql_stat)
> > END
> > GO|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>> You can't pass a function in as a parameter. Try:
>> DECLARE @.dt DATETIME;
>> SET @.dt = GETDATE();
>> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
> Ok, did that, but see error below.
>
>
>> --
>> Aaron Bertrand
>> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
>> "d.s." <nodamnspa...@.yahoo.com> wrote in message
>> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>>
>> > Ok, I previously had a thread about "Column name as variable".
>> > Here's a proc I wrote, and it compiles ok:
>> > alter PROCEDURE rptRRTP_By_Date_Range
>> > -- Add the parameters for the stored procedure here
>> > @.Update_Field as sysname,
>> > @.start_date as datetime,
>> > @.end_date as datetime,
>> > @.contr_Stat as varchar(2),
>> > @.seq as int
>> > AS
>> > declare @.sql_stat as varchar(255)
>> > BEGIN
>> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =>> > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
>> > ',' + @.end_date + ')
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
>> > where sequence = ' + @.seq
>> > Execute (@.sql_stat)
>> > END
>> > GO
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
(select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?
You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegrou ps.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
> (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>
|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.
|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
[vbcol=seagreen]
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegrou ps.com...
>
>
>
>
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
[vbcol=seagreen]
>
|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegro ups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>
> Ok, did that, but see error below.
>
>
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>
|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

Error While Running Dts

Hi,
I am trying to run a DTS from SP. The SQL code that I am using is as follows.......

exec master..xp_cmdshell 'dtsrun /S 142.102.27.207 /U sa /P sa /N DTS_TEST1'

But Running this I am getting the the following errror......
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0

Error Detail Records:
Error: 3 (3); Provider Error: 3 (3)
Error string: Error opening datafile: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider

Help file: DTSFFile.hlp
Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.

NULLGuys......... Help me in this regard..............|||Is the SQL Instance your connecting to (142.102.27.207) local (the same server where you're running DTSRun)?

Also it's running from the context of the windows-account that's running SQL Server. Is that account allowed to access the file?|||The SqlServer Computer has been given the full control to the file where the data is to be transferred in the application server through DTS.|||where as when I am running from the command prompt of the application server, its running successfully...............

dtsrun /S 142.102.27.207 /U sa /P sa /N DTS_TEST1

NOTE:
SQLSERVER: 142.102.27.207
APPLICATION SERVER: 142.102.27.154

I have a folder named "TEST" in the application server, where i have given the "full control" to 142.102.27.207 (DB Server). Folder Test is having an excel file which is the destination object for the DTS. DTS is transferring teh data from a table to this excel file.|||it is definitly security related. Does the account running the sql server agent and sql server service have full control on test?

Error while executing a a query string using EXEC statement

Hi,

I have written a stored proc to bulk insert the data from a data file.

I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc

The follwing statement works fine if i give the table name directly in the query

Code Snippet

DECLARE @.LastUpdate varchar(20)

SET @.LastUpdate = 'Dec 11 2007 1:20AM'

INSERT INTO Category

SELECT MSISDN, @.LastUpdate FROM OPENROWSET( BULK '\\remotemachine\datafile.txt',

FORMATFILE = '\\remotemachine\FormatFile.fmt',

FIRSTROW = 2) AS a

To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below

Code Snippet

@.Category - Will be passed as a parameter to the stored proc

DECLARE @.vsBulkSQL VARCHAR(MAX)

DECLARE @.LastUpdate varchar(20)

SET @.LastUpdate = 'Dec 11 2007 1:20AM'

SELECT @.vsBulkSQL ='INSERT INTO '+ @.Category + ' SELECT MSISDN, ''' + @.LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\\remotemachine\datafile.txt'+ ''''+ ' ,' +

+ ' FORMATFILE ' + '=' + ''''+ '\\remotemachine\FormatFile.fmt'+ ''''+ ',' +

' FIRSTROW ' + '=' + '2' + ')' + ' AS a'

Print @.vsBulkSQL - This prints the folliwing statement

INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\\remotemachine\DataFile.txt' , FORMATFILE ='\\remotemachine\FormatFile.fmt', FIRSTROW =2) AS a

Exec @.vsBulkSQL - This statement gives the following error

The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\\remotemachine\Second.txt' , FORMATFILE ='\\remotemachine\FormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.

Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same

~Mohan

Does it work if you alias the @.LastUpdate value?

|||

I suspect you need to execute the query with

EXEC (@.vsBulkSQL)

You have left out the parentheses, and when you do that, EXEC expects the variable to hold a procedure name, not a query string.

Steve Kass

Drew University

www.stevekass.com

|||

Steve,

Good catch. Its working now if i have the string etween ( and ).

Thanks a lot

~Mohan