Running a SSIS package remotely on the SQL Server

We have a package (which uses SQL Server Destination Task) imported in the SQL Server. Is it possible to invoke this SSIS package from a remote App Server through C# .NET.

We would like to execute the SSIS package in the SQL Server itself and pass on the results of execution to the calling C#.NET application via the output variables declared in the SSIS package.

Thanks,
Loonysan

You can do this with relatively few lines of code from either a remote component or a Web service, by using one of the Load... methods of the Application class, then calling the Execute method of the package.

-Doug

|||

If i have to write a remote component (may be a Windows Service) - then I need to host that in SQL Server. Is there any workaround without hosting any such remote compoenents in the SQL Server.

Also in some other posts - Someone has suggested the use of SQL Jobs to run SSIS packages remotely. I cannot use that approach also because I need to get some output values from the SSIS package variables. Also I am planning to run multiple instances of the same SSIS package in parallel (with different input parameters) - With the help of SQL Jobs - Running multiple instances in parallel isn't possible.

Thanks,
Loonysan

|||

I'm not sure what you mean by hosting a remote component IN SQL Server. If you mean a CLR stored procedure, unfortunately it's not possible to create a CLR stored procedure (or UDF, etc.) that uses the Integration Services API.

If you mean host a component ON the server where SQL Server is running, then you could configure that component as a remote component or a Web service that can be invoked remotely.

running a script which accepts a parameter against SQL2000 database

Hi there,
I'm fairly new to this stuff - I want to write a batch file or small
vbscript app that will aceept a parameter and then insert this into a
simple update command and execute agaisnt a SQL2000 database. andy help
appreciated!!
Cheers,
Paulat this link you can find about connecting to SQL server and adding new
records to a table using ADO
http://www.microsoft.com/technet/scriptcenter/scripts/misc/database/default.mspx?mfr=true
for using command line arguments, this link:
http://www.microsoft.com/technet/scriptcenter/resources/tales/sg0704.mspx
--
urkec
"pauls1888" wrote:
> Hi there,
> I'm fairly new to this stuff - I want to write a batch file or small
> vbscript app that will aceept a parameter and then insert this into a
> simple update command and execute agaisnt a SQL2000 database. andy help
> appreciated!!
> Cheers,
> Paul
>|||Look up batch files, using replaceable parameters, and then using SQL Server
Books Online, check out osql.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"pauls1888" <pauls1888@.gmail.com> wrote in message
news:1164726964.399837.44300@.45g2000cws.googlegroups.com...
> Hi there,
> I'm fairly new to this stuff - I want to write a batch file or small
> vbscript app that will aceept a parameter and then insert this into a
> simple update command and execute agaisnt a SQL2000 database. andy help
> appreciated!!
> Cheers,
> Paul
>|||Thanks for the help guys

running a script which accepts a parameter against SQL2000 database

Hi there,
I'm fairly new to this stuff - I want to write a batch file or small
vbscript app that will aceept a parameter and then insert this into a
simple update command and execute agaisnt a SQL2000 database. andy help
appreciated!!
Cheers,
Paul
at this link you can find about connecting to SQL server and adding new
records to a table using ADO
http://www.microsoft.com/technet/scriptcenter/scripts/misc/database/default.mspx?mfr=true
for using command line arguments, this link:
http://www.microsoft.com/technet/scriptcenter/resources/tales/sg0704.mspx
urkec
"pauls1888" wrote:

> Hi there,
> I'm fairly new to this stuff - I want to write a batch file or small
> vbscript app that will aceept a parameter and then insert this into a
> simple update command and execute agaisnt a SQL2000 database. andy help
> appreciated!!
> Cheers,
> Paul
>
|||Look up batch files, using replaceable parameters, and then using SQL Server
Books Online, check out osql.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"pauls1888" <pauls1888@.gmail.com> wrote in message
news:1164726964.399837.44300@.45g2000cws.googlegrou ps.com...
> Hi there,
> I'm fairly new to this stuff - I want to write a batch file or small
> vbscript app that will aceept a parameter and then insert this into a
> simple update command and execute agaisnt a SQL2000 database. andy help
> appreciated!!
> Cheers,
> Paul
>
|||Thanks for the help guys
sql

running a script which accepts a parameter against SQL2000 database

Hi there,
I'm fairly new to this stuff - I want to write a batch file or small
vbscript app that will aceept a parameter and then insert this into a
simple update command and execute agaisnt a SQL2000 database. andy help
appreciated!!
Cheers,
PaulLook up batch files, using replaceable parameters, and then using SQL Server
Books Online, check out osql.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"pauls1888" <pauls1888@.gmail.com> wrote in message
news:1164726964.399837.44300@.45g2000cws.googlegroups.com...
> Hi there,
> I'm fairly new to this stuff - I want to write a batch file or small
> vbscript app that will aceept a parameter and then insert this into a
> simple update command and execute agaisnt a SQL2000 database. andy help
> appreciated!!
> Cheers,
> Paul
>|||Thanks for the help guys

Running a asp.net(1.1) app with sql2005 (on a 2.0 framework app)

Hi,
I have developed a simple app using asp.net (vb.net 2003).
Normally I would copy the application files from
c:\Inetpub\wwwroot\
to the server and paste them to the same location on the
server.
I have installed a new server with win2k3 sp1 and installed
beta 2 of sql2005. I have tried the same copying and pasting
but when I run the application, I get the following error:
Cannot view XML input using XSL style sheet. Please correct the error
and then click the Refresh button, or try again later.
----
A name was started with an invalid character. Error processing resource
'http://localhost/webit/login.aspx'. Line 1, Posit...
<%@. Page Language="vb" AutoEventWireup="false" Codebehind="Login.aspx.vb"
Inherits="WebIT.Login"%>
-^
what should i do?
Please help.
Rgs,
MehulI think there's a problem with the IIS 6.0.
I had a sumiliar Problem and there's a discussion about it in the IIS
General UserGroup Now,waiting 4 answer.
"melu" wrote:
> Hi,
> I have developed a simple app using asp.net (vb.net 2003).
> Normally I would copy the application files from
> c:\Inetpub\wwwroot\
> to the server and paste them to the same location on the
> server.
> I have installed a new server with win2k3 sp1 and installed
> beta 2 of sql2005. I have tried the same copying and pasting
> but when I run the application, I get the following error:
>
>
> Cannot view XML input using XSL style sheet. Please correct the error
> and then click the Refresh button, or try again later.
>
> ----
> A name was started with an invalid character. Error processing resource
> 'http://localhost/webit/login.aspx'. Line 1, Posit...
> <%@. Page Language="vb" AutoEventWireup="false" Codebehind="Login.aspx.vb"
> Inherits="WebIT.Login"%>
> -^
>
> what should i do?
> Please help.
> Rgs,
> Mehul

Running 2+ process simultaneously causes exponetial slowness

If I run a report from our 4GL app (using ODBC) it takes say 5 mins to run.
If I run 2 instances of it simultaneously, they will slow down by a factor
5+. Reads and Duration of some transactions in Profiler will jump up
significanty.
If anything you might have expected the second process to benefit from data
caching, but for some reason the opposite occurs.
Anyone got any ideas why this might occur?
On SP3 - cannot yet install SP4 because of AWE.MrTim wrote:
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> to run. If I run 2 instances of it simultaneously, they will slow
> down by a factor 5+. Reads and Duration of some transactions in
> Profiler will jump up significanty.
> If anything you might have expected the second process to benefit
> from data caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
A 5 minute run is extremely long. Running two 5 minute queries will amp
up the cpu and disk activity accordingly and force each of them to take
much longer to complete - not to mention all other queries running. You
usually will not see a change in the number of reads or cpu values for
each. You should see a significant change in duration, however. A 5
minute operation is probably flushing all good data out of cache
multiple times while it scans tables. Not to mention that the disks are
probably quite busy during the operation, making each one take longer as
they contend for physical reads - which are very, very slow.
I would suggest you get the performance of the 5 minute query down to a
few seconds if possible, run it off-hours, or run it off a dedicated
reporting server to keep it's activity from degrading the performance of
other queries.
Have a look at the execution plan for the 5 minute query and see if it
can be tuned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Why do you think a 5 minute report is long? Given the amount of data it's
processing it damn quick. It's actually a cursor loop processing tens of
thousands of records, not a single SELECT.
Anyway, it's just an example for this post. The point is that [any] 2
processes running simultaneously take significantly longer than twice as long
to run.
"David Gugick" wrote:
> MrTim wrote:
> > If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> > to run. If I run 2 instances of it simultaneously, they will slow
> > down by a factor 5+. Reads and Duration of some transactions in
> > Profiler will jump up significanty.
> >
> > If anything you might have expected the second process to benefit
> > from data caching, but for some reason the opposite occurs.
> >
> > Anyone got any ideas why this might occur?
> > On SP3 - cannot yet install SP4 because of AWE.
> A 5 minute run is extremely long. Running two 5 minute queries will amp
> up the cpu and disk activity accordingly and force each of them to take
> much longer to complete - not to mention all other queries running. You
> usually will not see a change in the number of reads or cpu values for
> each. You should see a significant change in duration, however. A 5
> minute operation is probably flushing all good data out of cache
> multiple times while it scans tables. Not to mention that the disks are
> probably quite busy during the operation, making each one take longer as
> they contend for physical reads - which are very, very slow.
> I would suggest you get the performance of the 5 minute query down to a
> few seconds if possible, run it off-hours, or run it off a dedicated
> reporting server to keep it's activity from degrading the performance of
> other queries.
> Have a look at the execution plan for the 5 minute query and see if it
> can be tuned.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||MrTim wrote:
> Why do you think a 5 minute report is long? Given the amount of data
> it's processing it damn quick. It's actually a cursor loop
> processing tens of thousands of records, not a single SELECT.
>
Cursors are terrible performers in the SQL Server world. Set based
solutions can be hundreds of times faster. If you can imagine a
production SQL Server running a single query for 5 minutes when other
transactions take milliseconds, it starts to sound like a long time. For
a dedicated reporting server, it may be acceptable.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice
> as long to run.
The expected duration of a query is partly based on the overall system
CPU load. If a single query loads up the CPU to 25% utilization and
completes in about 60ms, running a second query simultaneously causes
each query to take about 100ms. That doesn't even take into account all
the disk activity. When you have long running processes, they are
normally accessing a lot of data. When SQL Server reads data, it loads
it into cache, moving out potentially more important data that would
benefit from staying in memory. Since it's usually the case where data
sizes far exceed available memory, you end up with a lot of disk
thrashing. And in the I/O world there is nothing slower than that.
That's why I suggest you try and tune the queries and avoid cursors
whenever possible. There are ususally set-based solutions that can work,
or at least get you most of the way there.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||You're correct in assuming that something else is going on if n processes run
significantly slower than nX time. Memory thrashing, disk thrashing, or
lock contention are the likely candidates.
Check your free resources and i/o queue while both are running. Also, if
you can drop the cursor entirely, or use a fast-forward readonly cursor (w/
nolock), try that as well.
"MrTim" wrote:
> Why do you think a 5 minute report is long? Given the amount of data it's
> processing it damn quick. It's actually a cursor loop processing tens of
> thousands of records, not a single SELECT.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice as long
> to run.
>
> "David Gugick" wrote:
> > MrTim wrote:
> > > If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> > > to run. If I run 2 instances of it simultaneously, they will slow
> > > down by a factor 5+. Reads and Duration of some transactions in
> > > Profiler will jump up significanty.
> > >
> > > If anything you might have expected the second process to benefit
> > > from data caching, but for some reason the opposite occurs.
> > >
> > > Anyone got any ideas why this might occur?
> > > On SP3 - cannot yet install SP4 because of AWE.
> >
> > A 5 minute run is extremely long. Running two 5 minute queries will amp
> > up the cpu and disk activity accordingly and force each of them to take
> > much longer to complete - not to mention all other queries running. You
> > usually will not see a change in the number of reads or cpu values for
> > each. You should see a significant change in duration, however. A 5
> > minute operation is probably flushing all good data out of cache
> > multiple times while it scans tables. Not to mention that the disks are
> > probably quite busy during the operation, making each one take longer as
> > they contend for physical reads - which are very, very slow.
> >
> > I would suggest you get the performance of the 5 minute query down to a
> > few seconds if possible, run it off-hours, or run it off a dedicated
> > reporting server to keep it's activity from degrading the performance of
> > other queries.
> >
> > Have a look at the execution plan for the 5 minute query and see if it
> > can be tuned.
> >
> >
> > --
> > David Gugick
> > Quest Software
> > www.imceda.com
> > www.quest.com
> >
> >|||Also check to ensure that one process is not blocking another...sp_who,
sp_who2
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MrTim" <MrTim@.discussions.microsoft.com> wrote in message
news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> run.
> If I run 2 instances of it simultaneously, they will slow down by a factor
> 5+. Reads and Duration of some transactions in Profiler will jump up
> significanty.
> If anything you might have expected the second process to benefit from
> data
> caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
>|||This is running in a dev enivornment, and there are only these 2 processes
running. I know that if it's run in a production environment other processes
could be blocking it. That sort of thing has all been checked.
And, unfortunately the cursor cannot be avoided. It's a 4GL program and a
cursor loop is required to feed data into the report. When only one report
is running, transactions are generally showing 0-16 ms duration (so quite
quick). Only when the two are in contention do thse start rising to 300+ms.
Given no other mitigating factors this still isn't what you'd expect.
"Wayne Snyder" wrote:
> Also check to ensure that one process is not blocking another...sp_who,
> sp_who2
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MrTim" <MrTim@.discussions.microsoft.com> wrote in message
> news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> > If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> > run.
> > If I run 2 instances of it simultaneously, they will slow down by a factor
> > 5+. Reads and Duration of some transactions in Profiler will jump up
> > significanty.
> >
> > If anything you might have expected the second process to benefit from
> > data
> > caching, but for some reason the opposite occurs.
> >
> > Anyone got any ideas why this might occur?
> > On SP3 - cannot yet install SP4 because of AWE.
> >
>
>|||> Only when the two are in contention do thse start rising to 300+ms.
> Given no other mitigating factors this still isn't what you'd expect.
Again, you expect this when two processes are contending over some limited
resource. The first step is to identify which resource that is. What type
cursor are you using? If your isolation level is too high, it may be a
locking issue. It might be a memory issue, with each query causing the
other to repeatedly swap to disk. Or it could be an i/o issue, as two
queries running simultaneously can turn a fast sequential disk read into a
large number of slow random access reads.
If you can't use a better cursor, then you need to run SQL profiler and see
whats causing the contention.

Running 2+ process simultaneously causes exponetial slowness

If I run a report from our 4GL app (using ODBC) it takes say 5 mins to run.
If I run 2 instances of it simultaneously, they will slow down by a factor
5+. Reads and Duration of some transactions in Profiler will jump up
significanty.
If anything you might have expected the second process to benefit from data
caching, but for some reason the opposite occurs.
Anyone got any ideas why this might occur?
On SP3 - cannot yet install SP4 because of AWE.
MrTim wrote:
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> to run. If I run 2 instances of it simultaneously, they will slow
> down by a factor 5+. Reads and Duration of some transactions in
> Profiler will jump up significanty.
> If anything you might have expected the second process to benefit
> from data caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
A 5 minute run is extremely long. Running two 5 minute queries will amp
up the cpu and disk activity accordingly and force each of them to take
much longer to complete - not to mention all other queries running. You
usually will not see a change in the number of reads or cpu values for
each. You should see a significant change in duration, however. A 5
minute operation is probably flushing all good data out of cache
multiple times while it scans tables. Not to mention that the disks are
probably quite busy during the operation, making each one take longer as
they contend for physical reads - which are very, very slow.
I would suggest you get the performance of the 5 minute query down to a
few seconds if possible, run it off-hours, or run it off a dedicated
reporting server to keep it's activity from degrading the performance of
other queries.
Have a look at the execution plan for the 5 minute query and see if it
can be tuned.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Why do you think a 5 minute report is long? Given the amount of data it's
processing it damn quick. It's actually a cursor loop processing tens of
thousands of records, not a single SELECT.
Anyway, it's just an example for this post. The point is that [any] 2
processes running simultaneously take significantly longer than twice as long
to run.
"David Gugick" wrote:

> MrTim wrote:
> A 5 minute run is extremely long. Running two 5 minute queries will amp
> up the cpu and disk activity accordingly and force each of them to take
> much longer to complete - not to mention all other queries running. You
> usually will not see a change in the number of reads or cpu values for
> each. You should see a significant change in duration, however. A 5
> minute operation is probably flushing all good data out of cache
> multiple times while it scans tables. Not to mention that the disks are
> probably quite busy during the operation, making each one take longer as
> they contend for physical reads - which are very, very slow.
> I would suggest you get the performance of the 5 minute query down to a
> few seconds if possible, run it off-hours, or run it off a dedicated
> reporting server to keep it's activity from degrading the performance of
> other queries.
> Have a look at the execution plan for the 5 minute query and see if it
> can be tuned.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||MrTim wrote:
> Why do you think a 5 minute report is long? Given the amount of data
> it's processing it damn quick. It's actually a cursor loop
> processing tens of thousands of records, not a single SELECT.
>
Cursors are terrible performers in the SQL Server world. Set based
solutions can be hundreds of times faster. If you can imagine a
production SQL Server running a single query for 5 minutes when other
transactions take milliseconds, it starts to sound like a long time. For
a dedicated reporting server, it may be acceptable.

> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice
> as long to run.
The expected duration of a query is partly based on the overall system
CPU load. If a single query loads up the CPU to 25% utilization and
completes in about 60ms, running a second query simultaneously causes
each query to take about 100ms. That doesn't even take into account all
the disk activity. When you have long running processes, they are
normally accessing a lot of data. When SQL Server reads data, it loads
it into cache, moving out potentially more important data that would
benefit from staying in memory. Since it's usually the case where data
sizes far exceed available memory, you end up with a lot of disk
thrashing. And in the I/O world there is nothing slower than that.
That's why I suggest you try and tune the queries and avoid cursors
whenever possible. There are ususally set-based solutions that can work,
or at least get you most of the way there.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||You're correct in assuming that something else is going on if n processes run
significantly slower than nX time. Memory thrashing, disk thrashing, or
lock contention are the likely candidates.
Check your free resources and i/o queue while both are running. Also, if
you can drop the cursor entirely, or use a fast-forward readonly cursor (w/
nolock), try that as well.
"MrTim" wrote:
[vbcol=seagreen]
> Why do you think a 5 minute report is long? Given the amount of data it's
> processing it damn quick. It's actually a cursor loop processing tens of
> thousands of records, not a single SELECT.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice as long
> to run.
>
> "David Gugick" wrote:
|||Also check to ensure that one process is not blocking another...sp_who,
sp_who2
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MrTim" <MrTim@.discussions.microsoft.com> wrote in message
news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> run.
> If I run 2 instances of it simultaneously, they will slow down by a factor
> 5+. Reads and Duration of some transactions in Profiler will jump up
> significanty.
> If anything you might have expected the second process to benefit from
> data
> caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
>
|||This is running in a dev enivornment, and there are only these 2 processes
running. I know that if it's run in a production environment other processes
could be blocking it. That sort of thing has all been checked.
And, unfortunately the cursor cannot be avoided. It's a 4GL program and a
cursor loop is required to feed data into the report. When only one report
is running, transactions are generally showing 0-16 ms duration (so quite
quick). Only when the two are in contention do thse start rising to 300+ms.
Given no other mitigating factors this still isn't what you'd expect.
"Wayne Snyder" wrote:

> Also check to ensure that one process is not blocking another...sp_who,
> sp_who2
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MrTim" <MrTim@.discussions.microsoft.com> wrote in message
> news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
>
>
|||> Only when the two are in contention do thse start rising to 300+ms.
> Given no other mitigating factors this still isn't what you'd expect.
Again, you expect this when two processes are contending over some limited
resource. The first step is to identify which resource that is. What type
cursor are you using? If your isolation level is too high, it may be a
locking issue. It might be a memory issue, with each query causing the
other to repeatedly swap to disk. Or it could be an i/o issue, as two
queries running simultaneously can turn a fast sequential disk read into a
large number of slow random access reads.
If you can't use a better cursor, then you need to run SQL profiler and see
whats causing the contention.

Running 2+ process simultaneously causes exponetial slowness

If I run a report from our 4GL app (using ODBC) it takes say 5 mins to run.
If I run 2 instances of it simultaneously, they will slow down by a factor
5+. Reads and Duration of some transactions in Profiler will jump up
significanty.
If anything you might have expected the second process to benefit from data
caching, but for some reason the opposite occurs.
Anyone got any ideas why this might occur?
On SP3 - cannot yet install SP4 because of AWE.MrTim wrote:
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins
> to run. If I run 2 instances of it simultaneously, they will slow
> down by a factor 5+. Reads and Duration of some transactions in
> Profiler will jump up significanty.
> If anything you might have expected the second process to benefit
> from data caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
A 5 minute run is extremely long. Running two 5 minute queries will amp
up the cpu and disk activity accordingly and force each of them to take
much longer to complete - not to mention all other queries running. You
usually will not see a change in the number of reads or cpu values for
each. You should see a significant change in duration, however. A 5
minute operation is probably flushing all good data out of cache
multiple times while it scans tables. Not to mention that the disks are
probably quite busy during the operation, making each one take longer as
they contend for physical reads - which are very, very slow.
I would suggest you get the performance of the 5 minute query down to a
few seconds if possible, run it off-hours, or run it off a dedicated
reporting server to keep it's activity from degrading the performance of
other queries.
Have a look at the execution plan for the 5 minute query and see if it
can be tuned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Why do you think a 5 minute report is long? Given the amount of data it's
processing it damn quick. It's actually a cursor loop processing tens of
thousands of records, not a single SELECT.
Anyway, it's just an example for this post. The point is that [any] 2
processes running simultaneously take significantly longer than twice as lon
g
to run.
"David Gugick" wrote:

> MrTim wrote:
> A 5 minute run is extremely long. Running two 5 minute queries will amp
> up the cpu and disk activity accordingly and force each of them to take
> much longer to complete - not to mention all other queries running. You
> usually will not see a change in the number of reads or cpu values for
> each. You should see a significant change in duration, however. A 5
> minute operation is probably flushing all good data out of cache
> multiple times while it scans tables. Not to mention that the disks are
> probably quite busy during the operation, making each one take longer as
> they contend for physical reads - which are very, very slow.
> I would suggest you get the performance of the 5 minute query down to a
> few seconds if possible, run it off-hours, or run it off a dedicated
> reporting server to keep it's activity from degrading the performance of
> other queries.
> Have a look at the execution plan for the 5 minute query and see if it
> can be tuned.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||MrTim wrote:
> Why do you think a 5 minute report is long? Given the amount of data
> it's processing it damn quick. It's actually a cursor loop
> processing tens of thousands of records, not a single SELECT.
>
Cursors are terrible performers in the SQL Server world. Set based
solutions can be hundreds of times faster. If you can imagine a
production SQL Server running a single query for 5 minutes when other
transactions take milliseconds, it starts to sound like a long time. For
a dedicated reporting server, it may be acceptable.

> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice
> as long to run.
The expected duration of a query is partly based on the overall system
CPU load. If a single query loads up the CPU to 25% utilization and
completes in about 60ms, running a second query simultaneously causes
each query to take about 100ms. That doesn't even take into account all
the disk activity. When you have long running processes, they are
normally accessing a lot of data. When SQL Server reads data, it loads
it into cache, moving out potentially more important data that would
benefit from staying in memory. Since it's usually the case where data
sizes far exceed available memory, you end up with a lot of disk
thrashing. And in the I/O world there is nothing slower than that.
That's why I suggest you try and tune the queries and avoid cursors
whenever possible. There are ususally set-based solutions that can work,
or at least get you most of the way there.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||You're correct in assuming that something else is going on if n processes ru
n
significantly slower than nX time. Memory thrashing, disk thrashing, or
lock contention are the likely candidates.
Check your free resources and i/o queue while both are running. Also, if
you can drop the cursor entirely, or use a fast-forward readonly cursor (w/
nolock), try that as well.
"MrTim" wrote:
[vbcol=seagreen]
> Why do you think a 5 minute report is long? Given the amount of data it's
> processing it damn quick. It's actually a cursor loop processing tens of
> thousands of records, not a single SELECT.
> Anyway, it's just an example for this post. The point is that [any] 2
> processes running simultaneously take significantly longer than twice as l
ong
> to run.
>
> "David Gugick" wrote:
>|||Also check to ensure that one process is not blocking another...sp_who,
sp_who2
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MrTim" <MrTim@.discussions.microsoft.com> wrote in message
news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
> If I run a report from our 4GL app (using ODBC) it takes say 5 mins to
> run.
> If I run 2 instances of it simultaneously, they will slow down by a factor
> 5+. Reads and Duration of some transactions in Profiler will jump up
> significanty.
> If anything you might have expected the second process to benefit from
> data
> caching, but for some reason the opposite occurs.
> Anyone got any ideas why this might occur?
> On SP3 - cannot yet install SP4 because of AWE.
>|||This is running in a dev enivornment, and there are only these 2 processes
running. I know that if it's run in a production environment other processe
s
could be blocking it. That sort of thing has all been checked.
And, unfortunately the cursor cannot be avoided. It's a 4GL program and a
cursor loop is required to feed data into the report. When only one report
is running, transactions are generally showing 0-16 ms duration (so quite
quick). Only when the two are in contention do thse start rising to 300+ms.
Given no other mitigating factors this still isn't what you'd expect.
"Wayne Snyder" wrote:

> Also check to ensure that one process is not blocking another...sp_who,
> sp_who2
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MrTim" <MrTim@.discussions.microsoft.com> wrote in message
> news:72451A09-E4F8-46E6-A5A4-B4ACC24877BD@.microsoft.com...
>
>|||> Only when the two are in contention do thse start rising to 300+ms.
> Given no other mitigating factors this still isn't what you'd expect.
Again, you expect this when two processes are contending over some limited
resource. The first step is to identify which resource that is. What typ
e
cursor are you using? If your isolation level is too high, it may be a
locking issue. It might be a memory issue, with each query causing the
other to repeatedly swap to disk. Or it could be an i/o issue, as two
queries running simultaneously can turn a fast sequential disk read into a
large number of slow random access reads.
If you can't use a better cursor, then you need to run SQL profiler and see
whats causing the contention.

Run SQL Server Report from .Net WIndows App...

Does a report have to be run from the Report Manager (IIS), or can rdl files
be executed from .Net code in a Windows Application?
I quess what I am asking is, with Crystal and VB 6, I can lauch a report
through a control embedded in the exe file.
Is there a control to place in a Windows Form that can view a report without
haing a IIS Report Manager?
--
Thanks,
ThomasLL, MCDBAI'm pretty sure SQL Reporting Services 2005 will have a WinForms and a
WebForms viewer.
"Thomas.LeBlanc@.NoSpam.Com" wrote:
> Does a report have to be run from the Report Manager (IIS), or can rdl files
> be executed from .Net code in a Windows Application?
> I quess what I am asking is, with Crystal and VB 6, I can lauch a report
> through a control embedded in the exe file.
> Is there a control to place in a Windows Form that can view a report without
> haing a IIS Report Manager?
> --
> Thanks,
> ThomasLL, MCDBAsql

Run SQL Script from Web App

Hi,

I have a asp.net 2.0 web app that needs to allow the user to run sql scripts on a database. I provided the user with a large text box which allows them to cut and past their script in. However the script contains complex SQL which cannot be handeled in the normal way of passing a string to a connection object and doing a ExecuteNonQuery.

The scripts contains words such as "GO" which makes teh script fail. Is there a wrapper or method for sending cuch SQL statment. Is there a stored procedures I can call or something that will allow me to excute the TSQL exactly in teh same way Query Analyser does?

Many thanks in advance

PS. yes im aware of the security implications of doing this, I have put the relevant security measures in place

check out the OSQL utility.|||

I am not sure but I think StoredProcedure can not do this because it executes SQL querys. I say I am not sure but if there is some body who is sure 100%, he should let us know:-)

|||

GO is not a valid T-SQL command, so no, you can't send it to the SQL Server and expect it to work. GO is a command to Query Analyzer/osql/sqlcmd/Management Studio, and if you want to emulate it's functionality, your program will have to handle it or call one of the above utilities to do it on your behalf.

Something like (pseudo code here):

dim cmds as string() =string.split({Your query},vbCRLf & "GO" & vbcrlf)

for each cmd in cmds

sqlcmd.commandtext=cmd

sqlcmd.executenonquery

next

|||

Hi

Thanks to everybody for their replies, I have been looking into OSQL Utility approach.

From my basic understanding of OSQL it can only be run from the Command Prompt so their for I would need to create a batch file. Will ASP.NET 2.0 let me call a batch file from within my web application?

Or is there another approach apart from using a batch file?

Many thanks in advanve

Run DTS from VB app (problems..)

Hi all,
I am successfully executing a DTS package from a VB app on the
development workstation, but having issues when trying to execute from
another client workstation...
The DTS connects to an .xls table, drops an existing SQLServer2000
table, creates a new (copy) SQL table, then finally populates the table
from the .xls file.
This same exact process works just fine for another set of SQLServer
Tables and .xls files on a network file server, but for some unknown
reason this one fails.
No errors are raised, but what happens after the process runs is that
the table gets dropped (as it's supposed to), then re-created (as it's
supposed to). But NO data is inserted from the originating .xls table.
Any insight/comments are greatly appreciated.
james
VB Code:
Public Function RunChickenPackage()
'Run the package stored in file C:\DTS_UE\TestPkg\VarPubsFields.dts.
Dim objPackage As DTS.Package2
Dim objStep As DTS.Step
Dim objTask As DTS.Task
Dim objExecPkg As DTS.ExecutePackageTask
'On Error GoTo PackageError
Set objPackage = New DTS.Package
'Set mobjPkgEvents = objPackage
objPackage.FailOnError = True
'Create the step and task. Specify the package to be run, and link the
step to the task.
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
Set objExecPkg = objTask.CustomTask
With objExecPkg
.PackagePassword = "user"
.FileName = "H:\Shared\GIS\AO_VCMS\db\MonitoringPositives.dts"
.Name = "ExecPkgTask"
End With
With objStep
.TaskName = objExecPkg.Name
.Name = "ExecPkgStep"
.ExecuteInMainThread = True
End With
objPackage.Steps.Add objStep
objPackage.Tasks.Add objTask
'Run the package and release references.
objPackage.Execute
Set objExecPkg = Nothing
Set objTask = Nothing
Set objStep = Nothing
'Set mobjPkgEvents = Nothing
objPackage.UnInitialize
End Function
Sorry -- wrong forum... This posting has been moved to the DTS group.
j

Run DTS from VB app (problems..)

Hi all,
I am successfully executing a DTS package from a VB app on the
development workstation, but having issues when trying to execute from
another client workstation...
The DTS connects to an .xls table, drops an existing SQLServer2000
table, creates a new (copy) SQL table, then finally populates the table
from the .xls file.
This same exact process works just fine for another set of SQLServer
Tables and .xls files on a network file server, but for some unknown
reason this one fails.
No errors are raised, but what happens after the process runs is that
the table gets dropped (as it's supposed to), then re-created (as it's
supposed to). But NO data is inserted from the originating .xls table.
Any insight/comments are greatly appreciated.
james
VB Code:
Public Function RunChickenPackage()
'Run the package stored in file C:\DTS_UE\TestPkg\VarPubsFields.dts.
Dim objPackage As DTS.Package2
Dim objStep As DTS.Step
Dim objTask As DTS.Task
Dim objExecPkg As DTS.ExecutePackageTask
'On Error GoTo PackageError
Set objPackage = New DTS.Package
'Set mobjPkgEvents = objPackage
objPackage.FailOnError = True
'Create the step and task. Specify the package to be run, and link the
step to the task.
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
Set objExecPkg = objTask.CustomTask
With objExecPkg
.PackagePassword = "user"
.FileName = " H:\Shared\GIS\AO_VCMS\db\MonitoringPosit
ives.dts"
.Name = "ExecPkgTask"
End With
With objStep
.TaskName = objExecPkg.Name
.Name = "ExecPkgStep"
.ExecuteInMainThread = True
End With
objPackage.Steps.Add objStep
objPackage.Tasks.Add objTask
'Run the package and release references.
objPackage.Execute
Set objExecPkg = Nothing
Set objTask = Nothing
Set objStep = Nothing
'Set mobjPkgEvents = Nothing
objPackage.UnInitialize
End FunctionSorry -- wrong forum... This posting has been moved to the DTS group.
j

Run DTS from VB app (problems..)

Hi all,
I am successfully executing a DTS package from a VB app on the
development workstation, but having issues when trying to execute from
another client workstation...
The DTS connects to an .xls table, drops an existing SQLServer2000
table, creates a new (copy) SQL table, then finally populates the table
from the .xls file.
This same exact process works just fine for another set of SQLServer
Tables and .xls files on a network file server, but for some unknown
reason this one fails.
No errors are raised, but what happens after the process runs is that
the table gets dropped (as it's supposed to), then re-created (as it's
supposed to). But NO data is inserted from the originating .xls table.
Any insight/comments are greatly appreciated.
james
VB Code:
Public Function RunChickenPackage()
'Run the package stored in file C:\DTS_UE\TestPkg\VarPubsFields.dts.
Dim objPackage As DTS.Package2
Dim objStep As DTS.Step
Dim objTask As DTS.Task
Dim objExecPkg As DTS.ExecutePackageTask
'On Error GoTo PackageError
Set objPackage = New DTS.Package
'Set mobjPkgEvents = objPackage
objPackage.FailOnError = True
'Create the step and task. Specify the package to be run, and link the
step to the task.
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
Set objExecPkg = objTask.CustomTask
With objExecPkg
.PackagePassword = "user"
.FileName = "H:\Shared\GIS\AO_VCMS\db\MonitoringPositives.dts"
.Name = "ExecPkgTask"
End With
With objStep
.TaskName = objExecPkg.Name
.Name = "ExecPkgStep"
.ExecuteInMainThread = True
End With
objPackage.Steps.Add objStep
objPackage.Tasks.Add objTask
'Run the package and release references.
objPackage.Execute
Set objExecPkg = Nothing
Set objTask = Nothing
Set objStep = Nothing
'Set mobjPkgEvents = Nothing
objPackage.UnInitialize
End FunctionSorry -- wrong forum... This posting has been moved to the DTS group.
j

Run DTS from ASP.NET (VB)

I have a DTS package which imports data from a legacy app to SQLServer 2000. Right now I have a page in classic ASP which executes a SP which in turn calls the DTS package. After the user hits the "Update" button on the ASP page they are redirected to a page that says "This process takes several minutes, check back later." The current setup is causing problems because Joe may hit the "Update" button to refresh the data and get an error because the DTS package is already running from when Mary just ran the same process.

I want to rewrite this in .NET (2.0) to accomplish the following:

Eliminate the SPCheck the status of the DTS package and alert the user if it is already running.Show a "Please wait while we process your request" message to the person who started the update while the DTS is running.Automatically show the report when the DTS is done.

I think I can find code in the forum to execute the DTS, but how do check the current status first? I've read about SQLDMO but the Web server and SQLServer are on different machines. Would this still work?

Thanks in advance.

I'm having trouble getting the DTS execute to run asynchronously. All the examples I've found show executing queries asynchronously, but nothing on DTS (there all in C# as well). Here's what I have in my class.

Imports System.IO
Imports System.NetImports System.Net.Mail
Imports System.ThreadingImports System.Runtime.Remoting.Messaging
Imports DTS
Imports DTS.DTSSQLServerStorageFlags

PartialClass Reporting_AmSherReports_rptColStats
Inherits System.Web.UI.Page
'----------------------------
'Remember, if you change the package, these numbers will need to be modified too.
'----------------------------

Private sPkgIDAs String ="{4CFD14F0-12D8-445C-AD98-E0DD784B166A}"
Private sPkgVersionAs String ="{F0CAFA3B-9A52-4780-B0C6-5B86C38BCFC5}"
Protected sMessageAs String =""

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load
If Page.IsPostBackThen

'Show processing message...

Processing.Visible =True
Report.Visible =False
Refresh.Visible =False

Dim stateAs New Object()
Dim beginHandlerAs New BeginEventHandler(AddressOf ExecuteDTS)
Dim endHandlerAs New EndEventHandler(AddressOf DTSComplete)
Dim timeoutHandlerAs New EndEventHandler(AddressOf DTSTimeout)
Dim taskAs New PageAsyncTask(beginHandler, endHandler, timeoutHandler, state)
RegisterAsyncTask(task)
End If
End Sub

Private Function ExecuteDTS(ByVal senderAs Object,ByVal eAs EventArgs,ByVal cbAs AsyncCallback,ByVal stateAs Object)As IAsyncResult
Try
Dim sSourceAs String =""
Dim sDescAs String =""
Dim lErrAs Long
Dim oPKGAs DTS.Package =New DTS.Package
Dim oStepAs DTS.Step

'Load Package

oPKG.LoadFromSQLServer("server","sa","", DTSSQLStgFlag_UseTrustedConnection, , sPkgID, sPkgVersion,"Refresh Stats")

'Execute

oPKG.Execute()

'Get Status and Error Message

For Each oStepIn oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_FailureThen
oStep.GetExecutionErrorInfo(lErr, sSource, sDesc)
sMessage = sMessage &"Step """ & oStep.Name &""" Failed" & vbCrLf & _
vbTab &"Error: " & lErr & vbCrLf & _
vbTab &"Source: " & sSource & vbCrLf & _
vbTab &"Description: " & sDesc & vbCrLf & vbCrLf
End If
Next
Catch exAs Exception
Finally
Processing.Visible =False
Report.Visible =True
Refresh.Visible =False
End Try
End Function

Private Sub DTSComplete(ByVal arAs IAsyncResult)
Processing.Visible =False
Report.Visible =True
Refresh.Visible =True
End Sub

Private Sub DTSTimeout(ByVal arAs IAsyncResult)
'No implementation -- needed for PageAsyncTask

End Sub
End Class