Executing N procedures in 1 Round trip

w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block? For example, if I'm calling a procedure "Update Contact" which takes 2 params @.Campaign, @.Contact 20 times how would I pass in the values for those 20 diffrent versions?

You could pass all the parameters to another stored procedure as a delimited list and parse them over there, create a loop, and call the stored proc in the loop.

|||I'm sure I could. I just thought I'd heard of something similar to ODP.Net's ArrayBinding syntax for SqlServer. I'm fairly sure I wasnt thinking about bulkcopy.|||

To my knowledge I dont think there is any standardized way to make multiple calls in one trip. Perhaps someone else here knows if there's any new feature in 2005. I havent been doing much development in 2005.

|||hrm...

Will the SQL Server provider let me execute blocks of t-sql?

eg something like:

AddContact(@.C1, @.U1);
AddContact(@.C2, @.U2);
AddContact(@.C3, @.U3);
...
AddContact(@.CN, @.UN);
go;|||

Yes, however, you must specify that the call type is text, not stored procedure, and you must properly format your calls like:

EXECUTE AddContact @.C1,@.U1
EXECUTE AddContact @.C2,@.U2
...
GO

|||So what is the "proper" format for sp calls in a 'anonymous block'.

Executing an SSIS package from TSQL without using xp_cmdshell?

How can I execute an SSIS package from TSQL without using xp_cmdshell?

I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell.

I want to do this:-
DECLARE @.returncode int
EXEC @.returncode = xp_cmdshell 'dtexec /sq pkgOne"'

Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)?

Thanksandyabel,

Well...you could create a SQL job that has the command to execute xp_cmdshell in it and then in your web app run the following in TSQL

EXEC MSDB..SP_Start_Job @.Job_Name = 'Your Job Name Here'

The SQL job would not be scheduled to run and would only be run when you tell it to via your app. You would not have to worry about the security policy because the job is going to fire on the SQL server and that is where the xp_cmdshell is going to run from.

The bad is that the SSIS return code is going to be passed back to the SQL job and not your web app. But, if you post the return code to a table on your database and then have your app scan that table you can get the code that way...if you need it.

ExecuteOutOfProcess calling a transactional child package causes Access is Denied.

I have a master package that contains an Execute Package Task whose ExecuteOutOfProcess flag is True, and that calls a child package whose TransactionOption = Required. The job is running in Sql Agent, and the step that calls the master package is configured to run under a certain domain account that is not in the local Administrators group. With this, I get the following:

messageText: Error 0x80070005 while loading package file "C:\program files\microsoft sql server\90\dts\Packages\ETL\Fact_Various_TransactionalChannels.dtsx". Access is denied.

When I add the domain account to the local Administrators group, this error does not occur. From a blog entry, I read that when a child package is executed out of process, the resultant OS process is called dtshost.exe (http://blogs.conchango.com/jamiethomson/comments/1414.aspx). Do I simply need to give my domain account permission to spawn this process? If so, what permission is it? Is there a group that contains this permission?

Is it possible that the domain account does not have access to "C:\program files\microsoft sql server\90\dts\Packages\ETL\"

-Jamie?

|||Unfortunately, no. The account has full control over that path.

Execute UDF/extended stored procedure only through view?

Lets say I have a view, MyView, that calls MyUDF and/or MyExtendedProcedure.
Is there a way I can allow a user to access MyView, but stop them from
directly executing MyUDF or MyExtendedProcedure?
E.g., I'd like them to be able to do this:
select * from MyView
but stop them from doing this:
Exec MyExtendedStoredProcedure
Is this possible? Thanks for any tips.As long as the objects referenced by your view are owned by the same user,
permissions on indirectly referenced objects are not checked. This behavior
is known as ownership chaining. Beginning with SQL 2000 SP3, you also need
to also turn on the 'db chaining' database option (a.k.a. cross-database
chaining) when objects reside in different databases.
Also, the databases need to be owned by the same login in order to maintain
an unbroken chain for your dbo-owned objects in different databases. The
master database is owned by the 'sa' login so your user database needs to
also be owned by 'sa' to provide an unbroken ownership chain to your
dbo-owned extended stored procedure. You can use sp_changedbowner if
needed.
Note that 'db chaining' should be enabled in an sa-owned database when only
sysadmin role members have permissions to create dbo-owned objects. See
Cross DB Onership Chaining <adminsql.chm::/ad_config_8d7m.htm> in the Books
Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neil W" <neilw@.REMOVEnetlib.com> wrote in message
news:uLP5XyJ1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Lets say I have a view, MyView, that calls MyUDF and/or
> MyExtendedProcedure.
> Is there a way I can allow a user to access MyView, but stop them from
> directly executing MyUDF or MyExtendedProcedure?
> E.g., I'd like them to be able to do this:
> select * from MyView
> but stop them from doing this:
> Exec MyExtendedStoredProcedure
> Is this possible? Thanks for any tips.
>
>

EXECUTE SQL string with parameter and return value

Hi all,

I would like execute an SQL string who calls a stored procedure with param and return a value:

declare @.query nvarchar(50)

set @.query = 'sp_test 1'

declare @.resultat int

exec @.resultat = @.query

select @.resultat

Its returns a error message:

"Could not find stored procedure 'sp_test 1'"

The command

exec(@.query)

works fine, but I can't retreive the return value and I can't do

exec @.resultat = (@.query)

How can I do?

Thanks,

Aurlien

use sp_executesql|||

Thanks very much,

With sp_executesql, the stored procedure is correctly executed, but it don't return the return value...

exec @.resultat = sp_executesql @.requete

@.resultat is still at 0 event if my stored procedure returns other :'(

How can I do?

Thanks very much

|||

check this example i use a cursor.. good luck

CREATE procedure test
as
begin
DECLARE @.AuthorID char(11)
declare @.sql nvarchar(4000)
set @.sql=' SET @.c1 = CURSOR STATIC FOR SELECT au_id FROM authors; OPEN @.c1'--'SELECT au_id FROM authors'

DECLARE @.c1 CURSOR

EXEC sp_executesql N'SET @.c1 = CURSOR STATIC FOR SELECT au_id FROM authors; OPEN @.c1', N'@.c1 cursor OUTPUT', @.c1 OUTPUT

FETCH NEXT FROM @.c1
INTO @.AuthorID
WHILE @.@.FETCH_STATUS = 0
BEGIN

PRINT @.AuthorID

FETCH NEXT FROM @.c1
INTO @.AuthorID
END
CLOSE @.c1
DEALLOCATE @.c1
end

|||

Another exemple when using dynamic queries

declare @.query nvarchar(50)

set @.query = 'select ''' + 'sp_test 1' + ''''

CREATE TABLE #resultat
(resultat sql_variant)

INSERT INTO #resultat exec sp_executesql @.query

select resultat from #resultat

Execute Process Task

Hi,

We have an SSIS Execute Process Task which calls an executable along with the required parameters.

When we run this package, it intermittently gives the error as shown below in red

Executing "ppscmd.exe" "StagingDB /Server http://SERVERNAME:46787 /path OSB_FY08.Planning.dimensionECFuncArea /Operation LoadDataFromStaging" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:05:08 PM Finished: 5:08:40 PM Elapsed: 212.203 seconds. The package execution failed. The step failed.

We are not able to debug this issue. We had a look at the logging information as well but we are not getting any information on this issue.

How can we resolve this issue ?

Any help on this would be highly appreciated.

Thanks & Regards

Joseph Samuel

You are getting the error simply because the process you are exeuting is returning some error code (non-zero). If you want to simply ignore the error, you can set the "ForceExecutionResult" property.|||

If you want to capture the error from PPSCMD, redirect the output to a file. See this post for details (you have to call it from cmd.exe):

http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

Execute Process Task

Hi,

We have an SSIS Execute Process Task which calls an executable along with the required parameters.

When we run this package, it intermittently gives the error as shown below in red

Executing "ppscmd.exe" "StagingDB /Server http://SERVERNAME:46787 /path OSB_FY08.Planning.dimensionECFuncArea /Operation LoadDataFromStaging" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:05:08 PM Finished: 5:08:40 PM Elapsed: 212.203 seconds. The package execution failed. The step failed.

We are not able to debug this issue. We had a look at the logging information as well but we are not getting any information on this issue.

How can we resolve this issue ?

Any help on this would be highly appreciated.

Thanks & Regards

Joseph Samuel

You are getting the error simply because the process you are exeuting is returning some error code (non-zero). If you want to simply ignore the error, you can set the "ForceExecutionResult" property.|||

If you want to capture the error from PPSCMD, redirect the output to a file. See this post for details (you have to call it from cmd.exe):

http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

Hi All,
We are running a job for archiving old data. The job calls a stored
procedure. The data gets copied successfully to a different database but when
a query is run to delete the data from the source database, it does not get
completed.
What is it trying to do ? Why does it not move forward?
The profiles shows the following continuously...
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- sp_sqlagent_get_perf_counters
SET NOCOUNT ON
-- sp_sqlagent_get_perf_counters
CREATE TABLE #temp
(
performance_condition NVARCHAR(1024) COLLATE database_default NOT NULL
)
-- sp_sqlagent_get_perf_counters
INSERT INTO #temp VALUES (N'dummy')
IF (@.all_counters = 0)
INSERT INTO #temp
SELECT DISTINCT SUBSTRING(performance_condition, 1, CHARINDEX('|',
performance_condition, PATINDEX('%[_|_]%', performance_condition) + 1) - 1)
FROM msdb.dbo.sysalerts
WHERE (performance_condition IS NOT NULL)
AND (enabled = 1)
SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
'instance_name' = CASE spi1.instance_name
WHEN N'' THEN NULL
ELSE RTRIM(spi1.instance_name)
END,
'value' = CASE spi1.cntr_type
WHEN 537003008 -- A ratio
THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE
spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
FROM
master.dbo.sysperfinfo spi2
WHERE
(spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('%
Base%', spi2.counter_name)))
AND
(spi1.instance_name = spi2.instance_name)
AND
(spi2.cntr_type = 1073939459))
ELSE spi1.cntr_value
END
FROM master.dbo.sysperfinfo spi1,
#temp tmp
WHERE (spi1.cntr_type <> 1073939459) -- Divisors
AND ((@.all_counters = 1) OR
(tmp.performance_condition = RTRIM(spi1.object_name) + '|' +
RTRIM(spi1.counter_name)))
sp_verify_job_identifiers '@.job_name',
'@.job_id',
@.job_name OUTPUT,
@.job_id OUTPUT,
'NO_TEST'
sp_sqlagent_get_perf_counters is a system procedure which feeds alerts for
SQL Agent service. There might be an entry in the SQL Server registry under
the key PerformanceSamplingInterval in MSSQLServer\SQLServerAgent which sets
the sampling interval. You can reduce this default by changing the value
there.
Alternatively you can totally avoid them by removing all the alerts set in
SQL Server( some of them are demo alerts set by SQL Server install by
default). Goto EM, under management, SQL Server Agent, delete all the
alerts. If you have no alerts the procedure will not run.
Anith
|||Hi Anith,
Thanks for a quick reply Do these alerts affect the performance of any
other query running in parallel ? One of our queries to delete bulk records
is taking a helll lot of time and is not returning, profiler traces show that
the query has started and then a large number of these
sp_sqlagent_get_perf_counters are shown, can you give some more inputs?
Regards
Sachin
"Anith Sen" wrote:

> sp_sqlagent_get_perf_counters is a system procedure which feeds alerts for
> SQL Agent service. There might be an entry in the SQL Server registry under
> the key PerformanceSamplingInterval in MSSQLServer\SQLServerAgent which sets
> the sampling interval. You can reduce this default by changing the value
> there.
> Alternatively you can totally avoid them by removing all the alerts set in
> SQL Server( some of them are demo alerts set by SQL Server install by
> default). Goto EM, under management, SQL Server Agent, delete all the
> alerts. If you have no alerts the procedure will not run.
> --
> Anith
>
>
|||>> Do these alerts affect the performance of any other query running in[vbcol=seagreen]
Generally, it should be negligible. However, you can check the trace file to
see how often this procedure is being run. If it is being run every 5 sec,
or 10 sec depending on the polling interval, then in heavy transaction
oriented systems, it might have some impact.
[vbcol=seagreen]
I do not have a first hand experience of how it impacts huge deletes, but on
a related note, do you have a profiler trace running 24/7 on the production
machine? In transaction-heavy systems, that itself can have dampening effect
on the overall performance.
Anith
|||No we do not have profiler running on production. Its just on the test
environment.
Not sure what is it trying to do?
And yes, I checked for msdb.dbo.sp_sqlagent_get_perf_counters ? Its taking
around 15-16 ms
Please find below some other traces as well.
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- SELECT N'Testing Connection...'
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- SELECT N'Testing Connection...'
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
...
...
-- SET TEXTSIZE 64512
-- select @.@.microsoftversion
-- select convert(sysname, serverproperty(N'servername'))
-- SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
-- EXECUTE msdb.dbo.sp_help_jobstep @.job_id =
0x63314D2F34B5AC449E29CF142843F50F
-- EXECUTE @.retval = sp_verify_job_identifiers '@.job_name',
'@.job_id',
@.job_name OUTPUT,
@.job_id OUTPUT,
'NO_TEST'
-- EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72
-- exec @.retcode = dbo.sp_MSsubscription_cleanup @.cutoff_time
Thanks,
Sachin
"Anith Sen" wrote:

> Generally, it should be negligible. However, you can check the trace file to
> see how often this procedure is being run. If it is being run every 5 sec,
> or 10 sec depending on the polling interval, then in heavy transaction
> oriented systems, it might have some impact.
>
> I do not have a first hand experience of how it impacts huge deletes, but on
> a related note, do you have a profiler trace running 24/7 on the production
> machine? In transaction-heavy systems, that itself can have dampening effect
> on the overall performance.
> --
> Anith
>
>