Executing SP in MSSQL takes forever via JDBC?!

Hi all,
I need to execute a stored procedure in our database server, MS SQL Server
2000 and it takes forever...
I'm using Microsoft SQL Server 2000 Driver for JDBC Version 2.2.0037
My code:
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
Connection conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=sec ret;SendStringParametersAsUnicode=false");
CallableStatement cs = conn.prepareCall("{call my_sp(?,?)}");
cs.setString(1,"param1");
cs.setString(2,"param2");
long start = System.currentTimeMillis();
ResultSet rs = cs.executeQuery();
System.out.println("exec time: " + (System.currentTimeMillis() - start) + "
ms");
...
Every time I execute this piece of code, it takes between 45-50 seconds...
I have tried to execute my SP from DBVisualizer(an app using the same JDBC
driver) and it is the same result. But, when I execute my SP from Query
Analyzer it takes less than a second?! I have also tried executing the SP
from TOAD for SQL Server, and then the execution time also is less than a
second. Why? What have I missed? Does anybody recognize this problem?
It is not me personally that has wriiten the SP, so I do not really know
what it does and how it looks, but I know that the SP I'm calling is calling
another SP that is creating a couple of temporary tables while it is
executing...
Greatfull for any suggestions...
Cheers
//Anders =)
anders.hedstrom wrote:

> Hi all,
> I need to execute a stored procedure in our database server, MS SQL Server
> 2000 and it takes forever...
> I'm using Microsoft SQL Server 2000 Driver for JDBC Version 2.2.0037
> My code:
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
> Connection conn = DriverManager.getConnection
> ("jdbc:microsoft:sqlserver://myhost:1433;DatabaseName=MYDB;User=me;Password=sec ret;SendStringParametersAsUnicode=false");
>
> CallableStatement cs = conn.prepareCall("{call my_sp(?,?)}");
> cs.setString(1,"param1");
> cs.setString(2,"param2");
Try these two things:
1 - Change the URL to "sendStringParametersAsUnicode". Note the initial lowercase 's'.
2 -
Statement s = conn.createStatement();
ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
Joe Weinstein at BEA

> long start = System.currentTimeMillis();
> ResultSet rs = cs.executeQuery();
> System.out.println("exec time: " + (System.currentTimeMillis() - start) + "
> ms");
> ...
>
> Every time I execute this piece of code, it takes between 45-50 seconds...
> I have tried to execute my SP from DBVisualizer(an app using the same JDBC
> driver) and it is the same result. But, when I execute my SP from Query
> Analyzer it takes less than a second?! I have also tried executing the SP
> from TOAD for SQL Server, and then the execution time also is less than a
> second. Why? What have I missed? Does anybody recognize this problem?
> It is not me personally that has wriiten the SP, so I do not really know
> what it does and how it looks, but I know that the SP I'm calling is calling
> another SP that is creating a couple of temporary tables while it is
> executing...
> Greatfull for any suggestions...
> Cheers
> //Anders =)
|||
> Try these two things:
> 1 - Change the URL to "sendStringParametersAsUnicode". Note the initial lowercase 's'.
> 2 -
> Statement s = conn.createStatement();
> ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
> Joe Weinstein at BEA
Hi Joe,
thanx alot for your tip! This fixed my problem;
> Statement s = conn.createStatement();
> ResultSet r = s.executeQuery("exec my_sp " + param1 + ", " + param2 );
It would be quite interesting to know why it takes so long to execute the SP
when using CallableStatement and PreparedStatement...
By the way, the lowercase 's' on sendStringParametersAsUnicode made no
difference.
Once again Joe, thanx!!!
Cheers
//Anders =)

Executing Oracle Stored Procedure via SQL Server 2000 Linked Serve

All,
I am trying to execute a simple Oracle stored procedure from SQL Server
2000. The SP has no input or output parameters. I have created the linked
server correctly as I am able to execute Oracle functions just fine. I have
tried the following two methods to execute the SP, SKELETON:
{Call ORADB..MARCO.SKELETON()}
Execute ORADB..MARCO.SKELETON
Both methods result in the same error listed below:
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'SKELETON' on remote server 'ORADB'.
[OLE/DB provider returned message: One or more errors occurred during
processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
Any ideas on how I can get this working properly?
Thanks,
MarcoHere is an old post by Umachandar. See if it helps:
http://tinyurl.com/7dxrr
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
> All,
> I am trying to execute a simple Oracle stored procedure from SQL Server
> 2000. The SP has no input or output parameters. I have created the
> linked
> server correctly as I am able to execute Oracle functions just fine. I
> have
> tried the following two methods to execute the SP, SKELETON:
> {Call ORADB..MARCO.SKELETON()}
> Execute ORADB..MARCO.SKELETON
> Both methods result in the same error listed below:
> Server: Msg 7212, Level 17, State 1, Line 1
> Could not execute procedure 'SKELETON' on remote server 'ORADB'.
> [OLE/DB provider returned message: One or more errors occurred during
> processing of command.]
> [OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
>
> Any ideas on how I can get this working properly?
> Thanks,
> Marco
>

Executing Oracle Stored Procedure via SQL Server 2000 Linked S

Thanks for the link. I have a stored procedure in Oracle that was created a
long time ago by someone else. Without manipulating the stored proc at all,
I wanted to feed it the required input parameters via SQL Server and let it
do its magic. From what I gather, this is not possible? I must create the
proc within a package in Oracle?
"oj" wrote:

> Here is an old post by Umachandar. See if it helps:
> http://tinyurl.com/7dxrr
> --
> -oj
>
> "marco" <marco@.discussions.microsoft.com> wrote in message
> news:792C4015-008A-420A-B307-EAFC6BAC49EE@.microsoft.com...
>
>Yes. Creating a package wrapper is your ticket to get to oracle proc.
-oj
"marco" <marco@.discussions.microsoft.com> wrote in message
news:4653000F-F545-4044-9876-E113CD14AFE6@.microsoft.com...
> Thanks for the link. I have a stored procedure in Oracle that was created
> a
> long time ago by someone else. Without manipulating the stored proc at
> all,
> I wanted to feed it the required input parameters via SQL Server and let
> it
> do its magic. From what I gather, this is not possible? I must create
> the
> proc within a package in Oracle?
> "oj" wrote:
>

executing analysis services query via openrowset

Can you kindly tell me what settings are required to execute an MDX statement via openrowset.

Currently I am executing it by impersonating my user (sql user) as "sa" account and everything goes ok.

The following is the query i am using

SELECT "[Dim Agent].[Dim Agent].[Dim Agent].[MEMBER_CAPTION]" AS AgentNumber,

"[Dim Application].[Dim Application].[Dim Application].[MEMBER_CAPTION]" AS ApplicationId,

ISNULL("[Dim Event].[Dim Event].&[1]",0) AS PropertyViews,

ISNULL("[Dim Event].[Dim Event].&[2]",0) AS ScheduleAShowing,

ISNULL("[Dim Event].[Dim Event].&[3]",0) AS ContactMe

FROM OpenRowset('MSOLAP.3',

'DATASOURCE=RIGGINS2\LFDB2; Initial Catalog=PicassoLnfWebMetric;Integrated Security=SSPI',

'SELECT {[Dim Event].[Dim Event].&[1],[Dim Event].[Dim Event].&[2],[Dim Event].[Dim Event].&[3]} ON COLUMNS,

NON EMPTY([Dim Agent].[Dim Agent].[Dim Agent] * [Dim Application].[Dim Application].[Dim Application]) ON ROWS

FROM [Lnf Web Metric] WHERE {([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].&), ([Dim Date].[Date].&[2007-05-20T00:00:00]:[Dim Date].[Date].&[2007-05-28T00:00:00],[Dim Agent].[Agent Status].[All].UNKNOWNMEMBER)}')

Can you kindly let me know what do i need to do to run this query by impersonating as some windows account?

Warm regards,

Sudhir

I don't think you can do this using OpenRowset(). You could try setting up a linked server and then using OpenQuery(). There are options when you set up a linked server that let you specify a security context. If your SQL and AS services are on the same machine you should be able to get this working, if they are on separate machines you would need to configure Kerberos authentication. (There are various whitepapers available on how to do this)|||can you redirect me to some whitepapers?|||

On configuring Kerberos? sure http://support.microsoft.com/kb/917409 & http://sqljunkies.com/WebLog/mosha/archive/2005/01/25/6905.aspx - specifically relates to AS2005

On adding a linked server http://msdn2.microsoft.com/en-us/library/aa936675(SQL.80).aspx, you also have to make sure with AS that the provider is set to run In-process.

Executing a Stored Procedure via Query Analysier is faster then a

I have a complicated Stored Procedure that when run as a Scheduled Job takes
9 hours to complete. When I run the same Stored Procedure in Query Analysier,
it takes 3 hours.
The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
I run the job every night at the same time, thus the server load should be
comparable.
Is there a fundamental difference in how Query Analyser executes a Stored
Procedure and how a SQL Job is executed?
This Stored Proc uses Temp Tables and Indexes heavily.
We've reindexed the database, recompiled the stored procs and truncated log
tables. The times are still wildly different between the two execution types.
I just don't know where to start / continue troubleshooting.
Please Advise.
- David
you can break it in to more steps ( like inserting getdate() or printing the
name of the step) and direct the output to a file. Then if you compare the
files you can determine the steps which are taking more time and try to find
the reason by analyzing the lock conflicts, wait, parallel processes etc..
"David Hekimian" wrote:

> I have a complicated Stored Procedure that when run as a Scheduled Job takes
> 9 hours to complete. When I run the same Stored Procedure in Query Analysier,
> it takes 3 hours.
> The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
> FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
> I run the job every night at the same time, thus the server load should be
> comparable.
> Is there a fundamental difference in how Query Analyser executes a Stored
> Procedure and how a SQL Job is executed?
> This Stored Proc uses Temp Tables and Indexes heavily.
> We've reindexed the database, recompiled the stored procs and truncated log
> tables. The times are still wildly different between the two execution types.
> I just don't know where to start / continue troubleshooting.
> Please Advise.
> - David
|||David,
Does the stored procedure have SET NOCOUNT ON at the beginning? If not then
try adding it. I have seen jobs do just what you describe by not having SET
NOCOUNT ON in them.
Andrew J. Kelly SQL MVP
"David Hekimian" <David Hekimian@.discussions.microsoft.com> wrote in message
news:F71CDFA4-1822-410B-9F4A-696C6BE4D7F3@.microsoft.com...
>I have a complicated Stored Procedure that when run as a Scheduled Job
>takes
> 9 hours to complete. When I run the same Stored Procedure in Query
> Analysier,
> it takes 3 hours.
> The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
> FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
> I run the job every night at the same time, thus the server load should be
> comparable.
> Is there a fundamental difference in how Query Analyser executes a Stored
> Procedure and how a SQL Job is executed?
> This Stored Proc uses Temp Tables and Indexes heavily.
> We've reindexed the database, recompiled the stored procs and truncated
> log
> tables. The times are still wildly different between the two execution
> types.
> I just don't know where to start / continue troubleshooting.
> Please Advise.
> - David
|||"David Hekimian" <David Hekimian@.discussions.microsoft.com> wrote in message
news:F71CDFA4-1822-410B-9F4A-696C6BE4D7F3@.microsoft.com...
> I have a complicated Stored Procedure that when run as a Scheduled Job
takes
> 9 hours to complete. When I run the same Stored Procedure in Query
Analysier,
> it takes 3 hours.
> The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
> FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
> I run the job every night at the same time, thus the server load should be
> comparable.
> Is there a fundamental difference in how Query Analyser executes a Stored
> Procedure and how a SQL Job is executed?
> This Stored Proc uses Temp Tables and Indexes heavily.
> We've reindexed the database, recompiled the stored procs and truncated
log
> tables. The times are still wildly different between the two execution
types.
> I just don't know where to start / continue troubleshooting.
> Please Advise.
> - David
The SP may be running as different users in Query Analyser and as a Job, but
I've never seen this affect performance.
Can you reproduce the time differential by running the SP against a reduced
data set? - it's a bit difficult to debug issues like this when each test
takes 9 hours.
Regards,
Simon

Executing a Stored Procedure via Query Analysier is faster then a

I have a complicated Stored Procedure that when run as a Scheduled Job takes
9 hours to complete. When I run the same Stored Procedure in Query Analysier,
it takes 3 hours.
The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
I run the job every night at the same time, thus the server load should be
comparable.
Is there a fundamental difference in how Query Analyser executes a Stored
Procedure and how a SQL Job is executed?
This Stored Proc uses Temp Tables and Indexes heavily.
We've reindexed the database, recompiled the stored procs and truncated log
tables. The times are still wildly different between the two execution types.
I just don't know where to start / continue troubleshooting.
Please Advise.
- Davidyou can break it in to more steps ( like inserting getdate() or printing the
name of the step) and direct the output to a file. Then if you compare the
files you can determine the steps which are taking more time and try to find
the reason by analyzing the lock conflicts, wait, parallel processes etc..
"David Hekimian" wrote:
> I have a complicated Stored Procedure that when run as a Scheduled Job takes
> 9 hours to complete. When I run the same Stored Procedure in Query Analysier,
> it takes 3 hours.
> The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
> FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
> I run the job every night at the same time, thus the server load should be
> comparable.
> Is there a fundamental difference in how Query Analyser executes a Stored
> Procedure and how a SQL Job is executed?
> This Stored Proc uses Temp Tables and Indexes heavily.
> We've reindexed the database, recompiled the stored procs and truncated log
> tables. The times are still wildly different between the two execution types.
> I just don't know where to start / continue troubleshooting.
> Please Advise.
> - David|||I've done that, but the log generated is 900MB :) Its taking me a while to
figure out what's going on...
There should be no reason why running the job in Query Analyzer should
execute 3x faster then if it is scheduled to run as a job.
I'm wondering if there is something fundamentally different in the way the
execution of the jobs is being handled. Could running via Query Analyzer use
a different Execution Plan vs the Job?
If so, How do I get SQL to pick the faster Execution Plan when running as a
Job?
If the Stored Procedure was taking longer using both methods, I'd look at
trying to enhance the Stored Procedure... but if the same Stored Procedure is
being executed with such a difference in timing, then something must be
misconfigured or broken.
- David|||try including some insert statements ( at different steps) into a temp tables
with step_name and date_inserted. we can just compare the times then.
"David Hekimian" wrote:
> I've done that, but the log generated is 900MB :) Its taking me a while to
> figure out what's going on...
> There should be no reason why running the job in Query Analyzer should
> execute 3x faster then if it is scheduled to run as a job.
> I'm wondering if there is something fundamentally different in the way the
> execution of the jobs is being handled. Could running via Query Analyzer use
> a different Execution Plan vs the Job?
> If so, How do I get SQL to pick the faster Execution Plan when running as a
> Job?
>
> If the Stored Procedure was taking longer using both methods, I'd look at
> trying to enhance the Stored Procedure... but if the same Stored Procedure is
> being executed with such a difference in timing, then something must be
> misconfigured or broken.
> - David
>|||David,
Does the stored procedure have SET NOCOUNT ON at the beginning? If not then
try adding it. I have seen jobs do just what you describe by not having SET
NOCOUNT ON in them.
--
Andrew J. Kelly SQL MVP
"David Hekimian" <David Hekimian@.discussions.microsoft.com> wrote in message
news:F71CDFA4-1822-410B-9F4A-696C6BE4D7F3@.microsoft.com...
>I have a complicated Stored Procedure that when run as a Scheduled Job
>takes
> 9 hours to complete. When I run the same Stored Procedure in Query
> Analysier,
> it takes 3 hours.
> The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
> FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
> I run the job every night at the same time, thus the server load should be
> comparable.
> Is there a fundamental difference in how Query Analyser executes a Stored
> Procedure and how a SQL Job is executed?
> This Stored Proc uses Temp Tables and Indexes heavily.
> We've reindexed the database, recompiled the stored procs and truncated
> log
> tables. The times are still wildly different between the two execution
> types.
> I just don't know where to start / continue troubleshooting.
> Please Advise.
> - David|||"David Hekimian" <David Hekimian@.discussions.microsoft.com> wrote in message
news:F71CDFA4-1822-410B-9F4A-696C6BE4D7F3@.microsoft.com...
> I have a complicated Stored Procedure that when run as a Scheduled Job
takes
> 9 hours to complete. When I run the same Stored Procedure in Query
Analysier,
> it takes 3 hours.
> The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
> FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
> I run the job every night at the same time, thus the server load should be
> comparable.
> Is there a fundamental difference in how Query Analyser executes a Stored
> Procedure and how a SQL Job is executed?
> This Stored Proc uses Temp Tables and Indexes heavily.
> We've reindexed the database, recompiled the stored procs and truncated
log
> tables. The times are still wildly different between the two execution
types.
> I just don't know where to start / continue troubleshooting.
> Please Advise.
> - David
The SP may be running as different users in Query Analyser and as a Job, but
I've never seen this affect performance.
Can you reproduce the time differential by running the SP against a reduced
data set? - it's a bit difficult to debug issues like this when each test
takes 9 hours.
Regards,
Simon

Executing a stored procedure that uses linked server from vb.NET

I can't execute my stored procedure from .NET (I think it might be
because the stored proc has joins in it via linked servers). I get
timeout error from .NET
MyBase.connString ="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DB;Data Source=Server;"
MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
MyBase.GetDataSet()
The stored procedure takes the 3 arguments and uses joins to 2 other
db-servers that I have access to via linked server connections. (MS SQL
Server)
I can run the stored proc on in QA on my MS SQL with no prob.
Must i open connections to the 2 linked servers from .NET as well?
Glad for any opinions so i dont have to rewrite my sp,
Mikaelbermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?|||> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?
> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>> How long does it take to run in QA, and how long is your timeout set for
>> in .NET?
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M|||bermik wrote:
> > I'm no .NET programmer, but with typical web apps you have a CONNECTION
> > timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> > So, in a nutshell, if your query takes longer than 30 seconds, the web
> > connection is going to give up and report a timeout.
> Not a web app, but still makes sense, will check the times. My test
> procedure worked out fine so it should have something to do with the
> load and not linked server, thanks!
> /M
...and timeout it was thanks for your help!
(MyBase.cmdTimeout = 15000)

Executing a stored procedure that uses linked server from vb.NET

bermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?
> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?

> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||
> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M|||bermik wrote:
> Not a web app, but still makes sense, will check the times. My test
> procedure worked out fine so it should have something to do with the
> load and not linked server, thanks!
> /M
...and timeout it was thanks for your help!
(MyBase.cmdTimeout = 15000)|||I can't execute my stored procedure from .NET (I think it might be
because the stored proc has joins in it via linked servers). I get
timeout error from .NET
MyBase.connString ="Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=DB;Data Source=Server;"
MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
MyBase.GetDataSet()
The stored procedure takes the 3 arguments and uses joins to 2 other
db-servers that I have access to via linked server connections. (MS SQL
Server)
I can run the stored proc on in QA on my MS SQL with no prob.
Must i open connections to the 2 linked servers from .NET as well?
Glad for any opinions so i dont have to rewrite my sp,
Mikael|||bermik wrote:
> I can't execute my stored procedure from .NET (I think it might be
> because the stored proc has joins in it via linked servers). I get
> timeout error from .NET
> MyBase.connString ="Integrated Security=SSPI;Persist Security
> Info=False;Initial Catalog=DB;Data Source=Server;"
>
> MyBase.selectString = "Exec sp_storedprocedure1 var1,var2,var3"
> MyBase.GetDataSet()
>
> The stored procedure takes the 3 arguments and uses joins to 2 other
> db-servers that I have access to via linked server connections. (MS SQL
> Server)
>
> I can run the stored proc on in QA on my MS SQL with no prob.
>
> Must i open connections to the 2 linked servers from .NET as well?
>
> Glad for any opinions so i dont have to rewrite my sp,
>
> Mikael
>
How long does it take to run in QA, and how long is your timeout set for
in .NET?
What are the permissions that you have configured for your linked
servers? Is the user that .NET is connecting with permitted across the
linked servers?|||
> How long does it take to run in QA, and how long is your timeout set for
> in .NET?
The stored proc is a monster it takes about 15 min, but I have other
similar sp that work (but without linked server). I used standard time
of 30 but I tried 60 as well with no success, the timeout is only for
gettinga connection right? Not for the whole time for running and
completing the task?

> What are the permissions that you have configured for your linked
> servers? Is the user that .NET is connecting with permitted across the
> linked servers?
I'm using systems current login security context, which is also used
for the linked servers. I will make a simpler sp with a simple select
to 1 of the linked servers to see if it makes a difference, thanks!
/Mikael|||bermik wrote:
>
> The stored proc is a monster it takes about 15 min, but I have other
> similar sp that work (but without linked server). I used standard time
> of 30 but I tried 60 as well with no success, the timeout is only for
> gettinga connection right? Not for the whole time for running and
> completing the task?
>
I'm no .NET programmer, but with typical web apps you have a CONNECTION
timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
So, in a nutshell, if your query takes longer than 30 seconds, the web
connection is going to give up and report a timeout.|||
> I'm no .NET programmer, but with typical web apps you have a CONNECTION
> timeout and a COMMAND timeout. The defaults, I believe, are 30 seconds.
> So, in a nutshell, if your query takes longer than 30 seconds, the web
> connection is going to give up and report a timeout.
Not a web app, but still makes sense, will check the times. My test
procedure worked out fine so it should have something to do with the
load and not linked server, thanks!
/M

Execute Stored Procedure via Execute SQL Task

Do you know of a bug in the June CTP of SSIS where you cannot, using an Execute SQL Task, execute a stored procedure with parameters via an OLE DB connection? For example, one combination I tried was in the SQL Statement, I have:

EXEC dbo.DimBuild ?,?

And in the parameter mapping, I added two date user variables, both with parameter name ‘?’

--

I also tried

EXEC dbo.DimBuild sd, ed

And in the parameter mapping, I added two date user variables, one with a parameter name ‘sd’ and the other ‘ed’

--

I tried many other combinations as well. The error I would get would say “parameter name unrecognized”. [Execute SQL Task] Error: Executing the query "dbo.DimBuild sd, ed" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Is there something wrong with my syntax? Interestingly, I tried executing the stored procedure using an ADO.Net connection, with similar parameter mappings, and it worked just fine.

Thanks,

- Joel

I was banging my head against the wall trying to figure this out also. I finally created a simple DTS package with an Execute SQL task running a parameterized query. When I migrated it to SSIS, I found the parameter name was 0.

I modified the package I was working on to use parameter names 0 and 1 (with "exec sproc ?, ?" as the query) and it succeeded.

Don't you just love the great documentation?|||I know the documentation lacks on this. In this post http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=57637 Kirk has given us indication which Parameters can be used with which connection manager etc.

Execute SSIS package via sp

Is there a way to run an SSIS package via a stored procedure? I haven't foun
d
anything in BOL. I am currently doing this by running a job that executes th
e
package, but was wondering if there's a new sp for this.
Thanks, and happy new year,
TomTHi Tom,
Happy New Year!
From your descriptions, I understood you would like to know whether it is
possible to execute the SSIS via stored procedure. If I have misunderstood
your concern, please feel free to point it out.
Based on my knowledge, SQL Server 2005 introduced a new command prompt
utility dtexec. Using dtexec, you can run packages that are stored either
in the file system, in an instance of SQL Server 2005, or in the
Integration Services Package Store.
For more information, please refer the links below
How to: Run a Package Using the DTExec Utility
http://msdn2.microsoft.com/en-us/library/ms138023.aspx
Command Prompt Utilities (SSIS)
http://msdn2.microsoft.com/en-us/library/ms140258.aspx
Running Packages
http://msdn2.microsoft.com/en-us/library/ms141708.aspx
Alternatively, SQL Agent has a new feature called subsystems that makes it
possible to directly execute packages.
SQL Server Agent Subsystems
http://msdn2.microsoft.com/en-us/library/ms187100(en-US,SQL.90).aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Michael, I think I'll stick with creating a job, and running that fro
m
an sp.
"Michael Cheng [MSFT]" wrote:

> Hi Tom,
> Happy New Year!
> From your descriptions, I understood you would like to know whether it is
> possible to execute the SSIS via stored procedure. If I have misunderstood
> your concern, please feel free to point it out.
> Based on my knowledge, SQL Server 2005 introduced a new command prompt
> utility dtexec. Using dtexec, you can run packages that are stored either
> in the file system, in an instance of SQL Server 2005, or in the
> Integration Services Package Store.
> For more information, please refer the links below
> How to: Run a Package Using the DTExec Utility
> http://msdn2.microsoft.com/en-us/library/ms138023.aspx
> Command Prompt Utilities (SSIS)
> http://msdn2.microsoft.com/en-us/library/ms140258.aspx
> Running Packages
> http://msdn2.microsoft.com/en-us/library/ms141708.aspx
> Alternatively, SQL Agent has a new feature called subsystems that makes it
> possible to directly execute packages.
> SQL Server Agent Subsystems
> http://msdn2.microsoft.com/en-us/library/ms187100(en-US,SQL.90).aspx
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Tom,
You are welcome, as always:)
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Execute Package Task with SQL Server Location

Have written a SSIS package which in turn runs a couple of child packages via the Execute Package control flow task. All is fine if I use a Location of File System for the task. However when I deploy to production I'd like the main package and it's child packages to be under SQL Server.

I can set the Execute Package task location to SQL Server after copying my packages into a development SQL box and again it works. However as it's now executing the package from SQL rather than wihin BIDS it doesn't give me the debugging/flow information for the Child package when I run the parent package. It's also a bit of a pain because even though all the packages are part of the same solution if I make a change to a child package I've got to remember to re-save a copy of it to the SQL database.

Have looked into seeing if I can make the location field for the Execute package task configurable so that I could run as a File System package within BIDS but as a SQL package in production but the Location field doesn't seem to be exposed as a property for the Execute Package task so can't set this as an expression or from a configuration.

Does anyone have any advice on using the Executing package task for child packages which are stored in SQL.

What I do, for smaller packages, is to create a boolean variable -- SQLServerExecution -- and then create two flows on the control flow. Each flow does exactly the same thing, except on one flow, there's a precedence constraint that checks for SQLServerExecution to be true (and if so, execute that branch) and the other checks for it to be false.

Setup the false branch's Execute Package tasks to use the file system, and setup the true branch's Execute Package tasks to use SQL Server.

Or, create two master packages -- one for SQL Server packages, the other for file system packages.|||

My only advice is to use the same store type in all the environments. Otherwise support and deployment could become a nightmare as you won't be able to reproduce specific issues.

Sorry, I know that this does not answer your question.

|||

Thank you Phil that makes perfect sense. I'll have a think how easy it is to apply the solution to my parent package.

Think in future I'll also need to lay my C#/OO head to one side when doing SSIS and think a lot more carefully about when to split work off to seperate packages rather than have them as seperate Data Flows/Control flow areas etc within the same package.

|||

It wasn't really a specific question more an "any advice on this" type plea so your advice is most welcome Rafael, thank you.