error: connection is busy

Error: 0xC0202009 at Load SQL Deals, Insert SQL Deals [12494]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Connection is busy with results for another command".

Why isn't the connection manager managing this?

How can I trace the cause? The data flow component (insert SQL deals) is just a simple INSERT statment.You can always run SQL Profiler to see what's going on.

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,again

tentaively, got:
create #table(a,b,c,d)
insert into #table
select A,B,C,D
from s,t,x,y,z where y.price > 20

select *,
(select max(D) from
(select distinct top 2 with ties from #temp v where
v.B=W.B and v.C = W.c),
(select min(D) from
(select distinct top 2 with ties from #temp v where
v.B=W.B and v.C = W.c)
from #temp w
drop Table #temp

This works, but if y.price > 20 condition is not met,
unsupported error message gets produced within crystal
report. i expected blank field report instead in this
case,

included If exists around the select statement ,
still same error.

BTW, is there a way to find max(D) and min(D),
two highests, can be improved?.

--
Sent by 3 from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comalexqa2003@.yahoo.com (u128845214@.spawnkill.ip-mobilphone.net) writes:
> tentaively, got:
> create #table(a,b,c,d)
> insert into #table
> select A,B,C,D
> from s,t,x,y,z where y.price > 20
> select *,
> (select max(D) from
> (select distinct top 2 with ties from #temp v where
> v.B=W.B and v.C = W.c),
> (select min(D) from
> (select distinct top 2 with ties from #temp v where
> v.B=W.B and v.C = W.c)
> from #temp w
> drop Table #temp
> This works, but if y.price > 20 condition is not met,
> unsupported error message gets produced within crystal
> report. i expected blank field report instead in this
> case,

If the condition is not met, the temp table is empty, and you will
get no rows back at all.

You could do:

IF EXISTS (SELECT * FROM #temp)
SELECT *, ...
ELSE
SELECT NULL, NULL, NULL, NULL, NULL, NULL -- As many as needed.

Although, it would be better to rewrite the report to handle an
empty result set...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

error,again

tentaively, got:
create #table(a,b,c,d)
insert into #table
select A,B,C,D
from s,t,x,y,z where y.price > 20
select *,
(select max(D) from
(select distinct top 2 with ties from #temp v where
v.B=W.B and v.C = W.c),
(select min(D) from
(select distinct top 2 with ties from #temp v where
v.B=W.B and v.C = W.c)
from #temp w
drop Table #temp
This works, but if y.price > 20 condition is not met,
unsupported error message gets produced within crystal
report. i expected blank field report instead in this
case,
included If exists around the select statement ,
still same error.
BTW, is there a way to find max(D) and min(D),
two highests, can be improved?.
--
Sent by 3 from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comalexqa2003@.yahoo.com (u128845214@.spawnkill.ip-mobilphone.net) writes:
> tentaively, got:
> create #table(a,b,c,d)
> insert into #table
> select A,B,C,D
> from s,t,x,y,z where y.price > 20
> select *,
> (select max(D) from
> (select distinct top 2 with ties from #temp v where
> v.B=W.B and v.C = W.c),
> (select min(D) from
> (select distinct top 2 with ties from #temp v where
> v.B=W.B and v.C = W.c)
> from #temp w
> drop Table #temp
> This works, but if y.price > 20 condition is not met,
> unsupported error message gets produced within crystal
> report. i expected blank field report instead in this
> case,
If the condition is not met, the temp table is empty, and you will
get no rows back at all.
You could do:
IF EXISTS (SELECT * FROM #temp)
SELECT *, ...
ELSE
SELECT NULL, NULL, NULL, NULL, NULL, NULL -- As many as needed.
Although, it would be better to rewrite the report to handle an
empty result set...
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

error(2)

create #table(A nvarchar(30), B nvarchar(20), c
nvarchar(15), D int)
insert into #table
select a,b,c,d
from x,y,z where c like 'Norm%'
group by a,b,c,d

create #table(class,name, exam,score) as:
A name1 math 100
A name1 math 88
A name1 Phy 98
A name1 Chm 98
A name1 SPT 89
A name2 math 54
A name2 math 79
A name2 Phy 79
A name2 Chm 44
A name2 SPT 34
B name1 math 54
B name1 math 23
B name1 Phy 54
B name1 Chm 98
B name1 SPT 89
B name2 math 35
B name2 math 35
B name2 Phy 33
B name2 Chm 66
B name2 SPT 12

Finding max(score) for exam started with 'm'
grouped by class and name.

select *,
(select max(result) from #table T where T.class =
D.class and
T.name = D.name and T.exame like 'm%' ) as mx

from #table D
DROP TABLE #table
GO

NOw, if there is no data inserted into #table , got:
not supported error message otherwise it works OK.
Do not like to get this error message when no data
is inserted into #table. how?.

If exists(select *,
(select max(result) from #table T where T.class =
D.class and
T.name = D.name and T.exame like 'm%' ) as mx
from #table D)

Did not resolve it.

--
Sent by from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comu359375000@.spawnkill.ip-mobilphone.net (alexsql2000@.yahoo.com) wrote in message news:<l.1066087533.1806060791@.[63.127.215.130]>...
> create #table(A nvarchar(30), B nvarchar(20), c
> nvarchar(15), D int)
> insert into #table
> select a,b,c,d
> from x,y,z where c like 'Norm%'
> group by a,b,c,d
>
> create #table(class,name, exam,score) as:
> A name1 math 100
> A name1 math 88
> A name1 Phy 98
> A name1 Chm 98
> A name1 SPT 89
> A name2 math 54
> A name2 math 79
> A name2 Phy 79
> A name2 Chm 44
> A name2 SPT 34
> B name1 math 54
> B name1 math 23
> B name1 Phy 54
> B name1 Chm 98
> B name1 SPT 89
> B name2 math 35
> B name2 math 35
> B name2 Phy 33
> B name2 Chm 66
> B name2 SPT 12
> Finding max(score) for exam started with 'm'
> grouped by class and name.
> select *,
> (select max(result) from #table T where T.class =
> D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D
> DROP TABLE #table
> GO
>
> NOw, if there is no data inserted into #table , got:
> not supported error message otherwise it works OK.
> Do not like to get this error message when no data
> is inserted into #table. how?.
> If exists(select *,
> (select max(result) from #table T where T.class =
> D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D)
> Did not resolve it.

Your explanation isn't completely clear (your query doesn't match your
column names, and you don't give the exact error message you're
getting), but it looks like this should simply work:

select class, name, exam, max(score)
from #table
where exam like 'm%'
group by class, name, exam

If this isn't what you need, then consider posting working CREATE
TABLE statements, along with INSERTs to add your sample data, and the
output you expect.

Simon|||Hi

The following are untested by you may want to try:

If exists(select * from #table T where T.exame like 'm%' )
select *, (select max(T.result)
from #table T
where T.class = D.class
and T.name = D.name
and exame like 'm%' ) as mx
from #table D

Or try the following without the check:
select D.*, T.Mx
from #table D JOIN

(select Class, Name, max(result) as MX
from #table
WHERE exame like 'm%'
GROUP BY Class, Name ) T ON T.class = D.class
and T.name = D.name
WHERE D.exame like 'm%'

John

"alexsql2000@.yahoo.com" <u359375000@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1066087533.1806060791@.[63.127.215.130]...
> create #table(A nvarchar(30), B nvarchar(20), c
> nvarchar(15), D int)
> insert into #table
> select a,b,c,d
> from x,y,z where c like 'Norm%'
> group by a,b,c,d
>
> create #table(class,name, exam,score) as:
> A name1 math 100
> A name1 math 88
> A name1 Phy 98
> A name1 Chm 98
> A name1 SPT 89
> A name2 math 54
> A name2 math 79
> A name2 Phy 79
> A name2 Chm 44
> A name2 SPT 34
> B name1 math 54
> B name1 math 23
> B name1 Phy 54
> B name1 Chm 98
> B name1 SPT 89
> B name2 math 35
> B name2 math 35
> B name2 Phy 33
> B name2 Chm 66
> B name2 SPT 12
> Finding max(score) for exam started with 'm'
> grouped by class and name.
> select *,
> (select max(result) from #table T where T.class =
> D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D
> DROP TABLE #table
> GO
>
> NOw, if there is no data inserted into #table , got:
> not supported error message otherwise it works OK.
> Do not like to get this error message when no data
> is inserted into #table. how?.
> If exists(select *,
> (select max(result) from #table T where T.class =
> D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D)
> Did not resolve it.
>
>
> --
> Sent by from yahoo subdomain of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com

error(2)

create #table(A nvarchar(30), B nvarchar(20), c
nvarchar(15), D int)
insert into #table
select a,b,c,d
from x,y,z where c like 'Norm%'
group by a,b,c,d
create #table(class,name, exam,score) as:
A name1 math 100
A name1 math 88
A name1 Phy 98
A name1 Chm 98
A name1 SPT 89
A name2 math 54
A name2 math 79
A name2 Phy 79
A name2 Chm 44
A name2 SPT 34
B name1 math 54
B name1 math 23
B name1 Phy 54
B name1 Chm 98
B name1 SPT 89
B name2 math 35
B name2 math 35
B name2 Phy 33
B name2 Chm 66
B name2 SPT 12
Finding max(score) for exam started with 'm'
grouped by class and name.
select *,
(select max(result) from #table T where T.class = D.class and
T.name = D.name and T.exame like 'm%' ) as mx
from #table D
DROP TABLE #table
GO
NOw, if there is no data inserted into #table , got:
not supported error message otherwise it works OK.
Do not like to get this error message when no data
is inserted into #table. how?.
If exists(select *,
(select max(result) from #table T where T.class = D.class and
T.name = D.name and T.exame like 'm%' ) as mx
from #table D)
Did not resolve it.
--
Sent by from yahoo subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.comu359375000@.spawnkill.ip-mobilphone.net (alexsql2000@.yahoo.com) wrote in message news:<l.1066087533.1806060791@.[63.127.215.130]>...
> create #table(A nvarchar(30), B nvarchar(20), c
> nvarchar(15), D int)
> insert into #table
> select a,b,c,d
> from x,y,z where c like 'Norm%'
> group by a,b,c,d
>
> create #table(class,name, exam,score) as:
> A name1 math 100
> A name1 math 88
> A name1 Phy 98
> A name1 Chm 98
> A name1 SPT 89
> A name2 math 54
> A name2 math 79
> A name2 Phy 79
> A name2 Chm 44
> A name2 SPT 34
> B name1 math 54
> B name1 math 23
> B name1 Phy 54
> B name1 Chm 98
> B name1 SPT 89
> B name2 math 35
> B name2 math 35
> B name2 Phy 33
> B name2 Chm 66
> B name2 SPT 12
> Finding max(score) for exam started with 'm'
> grouped by class and name.
> select *,
> (select max(result) from #table T where T.class => D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D
> DROP TABLE #table
> GO
>
> NOw, if there is no data inserted into #table , got:
> not supported error message otherwise it works OK.
> Do not like to get this error message when no data
> is inserted into #table. how?.
> If exists(select *,
> (select max(result) from #table T where T.class => D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D)
> Did not resolve it.
Your explanation isn't completely clear (your query doesn't match your
column names, and you don't give the exact error message you're
getting), but it looks like this should simply work:
select class, name, exam, max(score)
from #table
where exam like 'm%'
group by class, name, exam
If this isn't what you need, then consider posting working CREATE
TABLE statements, along with INSERTs to add your sample data, and the
output you expect.
Simon|||Hi
The following are untested by you may want to try:
If exists(select * from #table T where T.exame like 'm%' )
select *, (select max(T.result)
from #table T
where T.class = D.class
and T.name = D.name
and exame like 'm%' ) as mx
from #table D
Or try the following without the check:
select D.*, T.Mx
from #table D JOIN
(select Class, Name, max(result) as MX
from #table
WHERE exame like 'm%'
GROUP BY Class, Name ) T ON T.class = D.class
and T.name = D.name
WHERE D.exame like 'm%'
John
"alexsql2000@.yahoo.com" <u359375000@.spawnkill.ip-mobilphone.net> wrote in
message news:l.1066087533.1806060791@.[63.127.215.130]...
> create #table(A nvarchar(30), B nvarchar(20), c
> nvarchar(15), D int)
> insert into #table
> select a,b,c,d
> from x,y,z where c like 'Norm%'
> group by a,b,c,d
>
> create #table(class,name, exam,score) as:
> A name1 math 100
> A name1 math 88
> A name1 Phy 98
> A name1 Chm 98
> A name1 SPT 89
> A name2 math 54
> A name2 math 79
> A name2 Phy 79
> A name2 Chm 44
> A name2 SPT 34
> B name1 math 54
> B name1 math 23
> B name1 Phy 54
> B name1 Chm 98
> B name1 SPT 89
> B name2 math 35
> B name2 math 35
> B name2 Phy 33
> B name2 Chm 66
> B name2 SPT 12
> Finding max(score) for exam started with 'm'
> grouped by class and name.
> select *,
> (select max(result) from #table T where T.class => D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D
> DROP TABLE #table
> GO
>
> NOw, if there is no data inserted into #table , got:
> not supported error message otherwise it works OK.
> Do not like to get this error message when no data
> is inserted into #table. how?.
> If exists(select *,
> (select max(result) from #table T where T.class => D.class and
> T.name = D.name and T.exame like 'm%' ) as mx
> from #table D)
> Did not resolve it.
>
>
> --
> Sent by from yahoo subdomain of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com

Error with an Insert stored procedure

I am getting this error when executing an insert procedure
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would
be truncated.The length of the string you are trying to insert is larger than the maximum
length of the column you are trying to insert into. Either the datatypes of
the parameters that the stored procedure accepts do not map to the datatypes
of the columns of the table you are trying to insert into, or the procedure
uses INSERT INTO without a column list and you are trying to insert the
wrong data in the wrong column.
Jacco Schalkwijk
SQL Server MVP
"Earnie" <Earnie@.discussions.microsoft.com> wrote in message
news:6831DA16-57F3-4920-A254-42D7A849BCD2@.microsoft.com...
>I am getting this error when executing an insert procedure
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
> [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would
> be truncated.|||Try
set ansi_warnings off
GO
before the instert statement.!|||1) You can't use GO in a stored procedure
2) Do you really want to make information disappear? What if someone types
in a description to store in your database and the last sentence is cut off
because it is too long and you have set ansi_warnings off? Don't you think
the users will prefer to be given a warning that the description is too long
so that they can reword it in less words?
Jacco Schalkwijk
SQL Server MVP
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1107473880.870415.62730@.l41g2000cwc.googlegroups.com...
> Try
> set ansi_warnings off
> GO
> before the instert statement.!
>

Error while trying to get predictions from relational DB

I am trying to get predictions and insert them into a DB table.

Following is the code I am using , but I am getting an error saying

'

An error occurred while preparing the query

,

Pl. help.

begin

declare @.v_query varchar(5000);
declare @.full_query varchar(5000);
declare @.v_dbquery varchar (200);

set @.v_dbquery = char(39)+'SELECT
[ProspectAlternateKey],
[FirstName],
[LastName],
[MaritalStatus],
[Gender],
[YearlyIncome],
[TotalChildren],
[NumberChildrenAtHome],
[HouseOwnerFlag],
[NumberCarsOwned]
FROM
[dbo].[ProspectiveBuyer]'+char(39);

set @.v_query = 'SELECT
[TM_Cluster].[Bike Buyer],
t.[ProspectAlternateKey],
PredictProbability([TM_Cluster].[Bike Buyer])
From
[TM_Cluster]
PREDICTION JOIN
OPENQUERY([Adventure Works DW],@.v_dbquery) AS t
ON
[TM_Cluster].[Marital Status] = t.[MaritalStatus] AND
[TM_Cluster].[Gender] = t.[Gender] AND
[TM_Cluster].[Yearly Income] = t.[YearlyIncome] AND
[TM_Cluster].[Total Children] = t.[TotalChildren] AND
[TM_Cluster].[Number Children At Home] = t.[NumberChildrenAtHome] AND
[TM_Cluster].[House Owner Flag] = t.[HouseOwnerFlag] AND
[TM_Cluster].[Number Cars Owned] = t.[NumberCarsOwned]'
-- print @.v_query

set @.full_query = 'select * from openquery (DMserver,'+char(39)+ @.v_query +char(39)+')' ;

print @.full_query;

EXEC (@.full_query);

end

Are you able to execute other DMX/MDX queries against the linked server to AS?

If not, you may need to make sure the MSOLAP provider is allowed in-proc on your SQL Server 2005 database engine instance. You can do this via SQL Server Management Studio - connect to the database engine, navigate to Server Objects -> Linked Servers -> Providers in the Object Explorer, right-click on MSOLAP to select Properties and check the "Allow inprocess" provider option.

|||

Yes, I was able to run DMX queries against the linked server.

For that purpose I had to check 'Allow Inprocess' as well as I had to go to linked server properties and in server option I had to set Rpc = True.

|||

You had some syntax errors that cause the query to fail (first in T-SQL and then in DMX) - see my comments marked "Raman:" in the corrected query below ( this one ran successfully on my machine):

begin

declare @.v_query varchar(5000);

declare @.full_query varchar(5000);

declare @.v_dbquery varchar (500); -- Raman: you had varchar(200) which was too small for the query

set @.v_dbquery = char(39) + -- Raman: need to escape the enclosing quotes when sending to AS as well

char(39)+'SELECT

[ProspectAlternateKey],

[FirstName],

[LastName],

[MaritalStatus],

[Gender],

[YearlyIncome],

[TotalChildren],

[NumberChildrenAtHome],

[HouseOwnerFlag],

[NumberCarsOwned]

FROM

[dbo].[ProspectiveBuyer]'+char(39)+char(39); -- Raman: need to escape the enclosing quotes when sending to AS as well

set @.v_query = 'SELECT

[TM Clustering].[Bike Buyer],

t.[ProspectAlternateKey],

PredictProbability([TM Clustering].[Bike Buyer])

From

[TM Clustering]

PREDICTION JOIN

OPENQUERY([Adventure Works DW],'

+ @.v_dbquery + -- Raman: @.v_dbquery needs to appended - you had it in the query string where it will not get substituted

')AS t

ON

[TM Clustering].[Marital Status] = t.[MaritalStatus] AND

[TM Clustering].[Gender] = t.[Gender] AND

[TM Clustering].[Yearly Income] = t.[YearlyIncome] AND

[TM Clustering].[Total Children] = t.[TotalChildren] AND

[TM Clustering].[Number Children At Home] = t.[NumberChildrenAtHome] AND

[TM Clustering].[House Owner Flag] = t.[HouseOwnerFlag] AND

[TM Clustering].[Number Cars Owned] = t.[NumberCarsOwned]'

--print @.v_query

set @.full_query = 'select * from openquery (DMserver,'+char(39)+ @.v_query +char(39)+')' ;

print @.full_query;

EXEC (@.full_query);

end

Error while trying to get predictions from relational DB

I am trying to get predictions and insert them into a DB table.

Following is the code I am using , but I am getting an error saying

'

An error occurred while preparing the query

,

Pl. help.

begin

declare @.v_query varchar(5000);
declare @.full_query varchar(5000);
declare @.v_dbquery varchar (200);

set @.v_dbquery = char(39)+'SELECT
[ProspectAlternateKey],
[FirstName],
[LastName],
[MaritalStatus],
[Gender],
[YearlyIncome],
[TotalChildren],
[NumberChildrenAtHome],
[HouseOwnerFlag],
[NumberCarsOwned]
FROM
[dbo].[ProspectiveBuyer]'+char(39);

set @.v_query = 'SELECT
[TM_Cluster].[Bike Buyer],
t.[ProspectAlternateKey],
PredictProbability([TM_Cluster].[Bike Buyer])
From
[TM_Cluster]
PREDICTION JOIN
OPENQUERY([Adventure Works DW],@.v_dbquery) AS t
ON
[TM_Cluster].[Marital Status] = t.[MaritalStatus] AND
[TM_Cluster].[Gender] = t.[Gender] AND
[TM_Cluster].[Yearly Income] = t.[YearlyIncome] AND
[TM_Cluster].[Total Children] = t.[TotalChildren] AND
[TM_Cluster].[Number Children At Home] = t.[NumberChildrenAtHome] AND
[TM_Cluster].[House Owner Flag] = t.[HouseOwnerFlag] AND
[TM_Cluster].[Number Cars Owned] = t.[NumberCarsOwned]'
-- print @.v_query

set @.full_query = 'select * from openquery (DMserver,'+char(39)+ @.v_query +char(39)+')' ;

print @.full_query;

EXEC (@.full_query);

end

Are you able to execute other DMX/MDX queries against the linked server to AS?

If not, you may need to make sure the MSOLAP provider is allowed in-proc on your SQL Server 2005 database engine instance. You can do this via SQL Server Management Studio - connect to the database engine, navigate to Server Objects -> Linked Servers -> Providers in the Object Explorer, right-click on MSOLAP to select Properties and check the "Allow inprocess" provider option.

|||

Yes, I was able to run DMX queries against the linked server.

For that purpose I had to check 'Allow Inprocess' as well as I had to go to linked server properties and in server option I had to set Rpc = True.

|||

You had some syntax errors that cause the query to fail (first in T-SQL and then in DMX) - see my comments marked "Raman:" in the corrected query below ( this one ran successfully on my machine):

begin

declare @.v_query varchar(5000);

declare @.full_query varchar(5000);

declare @.v_dbquery varchar (500); -- Raman: you had varchar(200) which was too small for the query

set @.v_dbquery = char(39) + -- Raman: need to escape the enclosing quotes when sending to AS as well

char(39)+'SELECT

[ProspectAlternateKey],

[FirstName],

[LastName],

[MaritalStatus],

[Gender],

[YearlyIncome],

[TotalChildren],

[NumberChildrenAtHome],

[HouseOwnerFlag],

[NumberCarsOwned]

FROM

[dbo].[ProspectiveBuyer]'+char(39)+char(39); -- Raman: need to escape the enclosing quotes when sending to AS as well

set @.v_query = 'SELECT

[TM Clustering].[Bike Buyer],

t.[ProspectAlternateKey],

PredictProbability([TM Clustering].[Bike Buyer])

From

[TM Clustering]

PREDICTION JOIN

OPENQUERY([Adventure Works DW],'

+ @.v_dbquery + -- Raman: @.v_dbquery needs to appended - you had it in the query string where it will not get substituted

')AS t

ON

[TM Clustering].[Marital Status] = t.[MaritalStatus] AND

[TM Clustering].[Gender] = t.[Gender] AND

[TM Clustering].[Yearly Income] = t.[YearlyIncome] AND

[TM Clustering].[Total Children] = t.[TotalChildren] AND

[TM Clustering].[Number Children At Home] = t.[NumberChildrenAtHome] AND

[TM Clustering].[House Owner Flag] = t.[HouseOwnerFlag] AND

[TM Clustering].[Number Cars Owned] = t.[NumberCarsOwned]'

--print @.v_query

set @.full_query = 'select * from openquery (DMserver,'+char(39)+ @.v_query +char(39)+')' ;

print @.full_query;

EXEC (@.full_query);

end

Error while Inserting!

Hey folks,
I am trying to updat a table by inserting values from another table into the parent table. The SQL being used is:
"insert into "LogSchema"."_rt1182" select * from "LogSchema"."_rt557" where recordID not in (select * from "LogSchema"."_rt1182") ORDER BY TIMESTAMP"
I have used the above statement and was successful, but with these two particluar tables I am getting an error message" Wrong number of values to INSERT"
Please lemme know, what this means and how to correct this problem!
TIAYou've used this code, and it worked at least once?

-PatP|||Originally posted by Pat Phelan
You've used this code, and it worked at least once?

-PatP
Yes the code worked for other tables, but am facing the problem with these two particluar tables namely _rt1182 and _rt557!
Any thoughts?
TIA|||How about this:

insert into [LogSchema].[_rt1182] (<put_your_field_list_here>) select <put_your_field_list_here> from [LogSchema].[_rt557] r1 left outer join [LogSchema].[_rt1182] r2 on r1.recordID = r2.recordID where r2.recordID is null ORDER BY TIMESTAMP

Error while inserting data in DataBase ?

my requirment is insert TableName and JourneyDate and FlightNumber alongwith otherdata at RunTime but i get error, I tried it several times.

Table Structure is:

Create Table HA142(JourneyDateDateTime primary key,FlightNochar(5)not nullFOREIGN kEY REFERENCES FLIGHTS(FlightNo),FirstClassSeatAvalableint,BusinessClassSeatAvalableint,EconomyClassSeatAvalableint,FsWaitingAvalableint,BsWaitingAvalableint,EcWaitingAvalableint)

string flightno = drpFlightNo.SelectedItem.Text;string JourneyDate = Session["JourneyDate"].ToString();string newStrign =",18,42,280,3,7,35)";SqlConnection myConn =new SqlConnection("workstation id=JASIM;packet size=4096;user id=ASPNET;data source=JASIM;persist security info=False;initial catalog=Test");SqlCommand populateFlightTable =new SqlCommand("INSERT INTO "+flightno+" VALUES("+JourneyDate+","+flightno+newStrign,myConn);myConn.Open();populateFlightTable.ExecuteNonQuery();myConn.Close();

whenever compiler reached topopulateFlightTable.ExecuteNonQuery();I received error. i tried it to rectify several times but no result.

plz hemp me...

Your insert statement is incorrect.

Read this article to know how that works

http://www.w3schools.com/sql/sql_insert.asp

HTH
Regards

|||

I am passing Table name at runtime becouse user does't not know in which table he is inserting date, it is based on his input record. that's why i am trying to accept table name and first two field of that table from user at run time.

So if I made any mistake in my code, plz tell me what is wrong and how to solve it. By using stored prosedure it is not possible to accept table along name with their Fields at runtime.

|||Thank you it was Syntax Error.

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