Insert into not working in Query Template

[former_member203984]

Hi All,

I am using SAP MII 14.0 SP4 patch 2. Most of my queries are calling the Store procedures. But my store procedures are not executing. The reason behind this I found , it was using "Insert Into statements". I tried the below query

1. declare @TempTable TABLE (

ID int,

Date datetime,

Name char(20))

INSERT INTO @TempTable (ID, Date, Name)

SELECT '1' as id, '2104-Mar-10' as Date,'par' as name

select * from @TempTable

2. Create table #TempTable  (

ID int,

Date datetime,

Name char(20))

INSERT INTO #TempTable (ID, Date, Name)

SELECT '1' as id, '2104-Mar-10' as Date,'par' as name

select * from #TempTable

but this also not working. Can you please suggest me the solution?

Regards

G.Partheeban

[former_member203984 · Accepted solution]

HI All,

I got the solution.

This is because of the Store Procedure in which we are not using "SET NOCOUNT ON".

Once we dded in all the store procedure. I am able to get the result.

Thanks for the valuable reply guys.

Regards

G.Partheeban

[former_member203984]

Hi All,

Tried with the below fix also but no luck

The index 1 is out of range -SQL Server Error | SCN

any solution for this?

Regards

G.Partheeban

[Former Member]

Hi Partheeban,

As the credentials which you have used in Data service --> Data connection for SQL Database, insert authorization doesn't have.

So kindly ask SQL Admin to provide write, read and update authorization to that user ID.

Try to login into SQL server Managment studio express with credentials which you have been used in Data connectivity in MII. And try to execute insert query and check

Hope it will solve your problem

Regards,

Praveen Reddy

[former_member203984]

Hi Praveen,

The credential has the permission. SInce I logged with the same user in SQL and executed the same.It works fine....

The query is working fine in 11.5 and why not in 14.0.Can you please try the below

1.declare @TempTable TABLE (

ID int,

Date datetime,

Name char(20))

INSERT INTO @TempTable (ID, Date, Name)

SELECT '1' as id, '2104-Mar-10' as Date,'par' as name

select * from @TempTable

2. Create table #TempTable  (

ID int,

Date datetime,

Name char(20))

INSERT INTO #TempTable (ID, Date, Name)

SELECT '1' as id, '2104-Mar-10' as Date,'par' as name

select * from #TempTable

Regards

G.Partheeban

[agentry_src]

Hi Partheeban,

Have you tried it with Query with Output or Command?

Regards, Mike

SAP Customer Experience Group - CEG

[former_member203984]

Hi Mike,

Below is the results:

Fixed Query with Output -> "com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range."

Command ->" Command Query successful"

Fixed Query -> "Process successful" but no result.

Please let me know what should I do?

Regards

G.Partheeban

[agentry_src]

Fixed Query with Output is for use with Oracle, but I am a little out of date and thought it worth a try with SQL Server in this newer version.  Command is used with Insert, Update, and Delete.  Query and Fixed Query are for Select queries.

I would recommend that you do a search back a few years for "Stored Procedure" and MII using this custom Google Search: Google Custom Search- SAP Community Network Search.

There have been numerous issues using Stored Procedures with MII over the years so it is worth reading some of the solutions people have come up with.

Regards, Mike

SAP Customer Experience Group - CEG

[former_member203984]

Hi Mike,

I checked that but few of the queries related to that was "not answered".

Can you please help me out?

Regards

G.Partheeban