Excel Import: execute

Showing posts with label execute. Show all posts

Thursday, March 29, 2012

execure store procedure from Query Analizer

Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
Message posted via http://www.droptable.com
Hi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>
|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrs Taylor
|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
Message posted via http://www.droptable.com
|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>

execure store procedure from Query Analizer

Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
--
Message posted via http://www.sqlmonster.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.developersdex.com/gurus/default.asp?p=4223
---
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrés Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
--
Message posted via http://www.sqlmonster.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>sql

execure store procedure from Query Analizer

Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
Message posted via http://www.droptable.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
---
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I ge
t
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrs Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
Message posted via http://www.droptable.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example belo
w.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I ge
t
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>

exec(select...), how supress the output?

Hi
I have a dynamically constructed sql query that I want to execute, e.g.
exec('select * from ' + @.tablename)

(1) Can I suppress the output somehow if this returns no values?
(2) Can I use the result of this query in another query somehow? e.g.
select
(3) Can I control the size of the columns in the output somehow

Thanks
F(foldface@.yahoo.co.uk) writes:
> I have a dynamically constructed sql query that I want to execute, e.g.
> exec('select * from ' + @.tablename)
> (1) Can I suppress the output somehow if this returns no values?

EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
@.tablename)

> (2) Can I use the result of this query in another query somehow? e.g.
> select

INSERT #tmp (...)
EXEC('...')

> (3) Can I control the size of the columns in the output somehow

This question is unclear. Output columns from SQL Server does not
really have any size, but that is up to the client tool you use.

But if you are using Query Analyzer, and want some nice output there,
you can use convert(varchar(n), ...) where n is the size of your choice.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > (1) Can I suppress the output somehow if this returns no values?
> EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
> @.tablename)

worked this out in the end but as I'm using google for news access from
work I couldn't reply to my own mail. Thanks anyway

> > (2) Can I use the result of this query in another query somehow? e.g.
> > select
> INSERT #tmp (...)
> EXEC('...')

sorry, don't understand this? Can you elaborate?

> > (3) Can I control the size of the columns in the output somehow
> But if you are using Query Analyzer, and want some nice output there,
> you can use convert(varchar(n), ...) where n is the size of your choice.

create table #TempTable (id int)
insert #TempTable Values (convert(varchar(50), 2))
select * from #TempTable

I know this is meant to convert values but what exactly should I be seeing
here? I am talking about Query Analyser|||(foldface@.yahoo.co.uk) writes:
>> > (2) Can I use the result of this query in another query somehow? e.g.
>> > select
>>
>> INSERT #tmp (...)
>> EXEC('...')
> sorry, don't understand this? Can you elaborate?

You can save the output from an EXEC() statement in a temp table, and
then use the temp table in the next query.

>> > (3) Can I control the size of the columns in the output somehow
>>
>> But if you are using Query Analyzer, and want some nice output there,
>> you can use convert(varchar(n), ...) where n is the size of your choice.
> create table #TempTable (id int)
> insert #TempTable Values (convert(varchar(50), 2))
> select * from #TempTable
> I know this is meant to convert values but what exactly should I be seeing
> here? I am talking about Query Analyser

I don't know exactly what you are trying to achieve, and you might be
better off if you explained more about your business requirements.

But the column width in QA depends on two things: the column name and the
data type. If you want control over the column width, all output columns
must be varchar. You cannot control the width of a float or an int column.

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

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

EXEC WITH RECOMPILE

Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint whe
n
atypical or temporary values are used in only a subset of queries that belon
g
to the stored procedure.
"Leila" wrote:

> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>

EXEC WITH RECOMPILE

Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
That's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila
|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:

> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>
|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David
|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>

EXEC WITH RECOMPILE

Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
LeilaThat's the way it works. Consider an EXEC WITH RECOMPILE to be an
"exception" - a one-time use of the plan. If you want a "permanent' new
plan, check out sp_recompile in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
Hi,
It seems that when you execute an SP along with WITH RECOMPILE option, the
SP is recompiled for that particular execution (only) and the old plan is
not replaced with new one in ProcCache. Is it correct or there's something
wrong with my experimentations?!
Thanks in advance,
Leila|||Per Books Online:
RECOMPILE
Indicates that the Database Engine does not cache a plan for this procedure
and the procedure is compiled at run time. This option cannot be used when
FOR REPLICATION is specified. RECOMPILE cannot be specified for CLR stored
procedures.
To instruct the Database Engine to discard plans for individual queries
inside a stored procedure, use the RECOMPILE query hint. For more
information, see Query Hint (Transact-SQL). Use the RECOMPILE query hint when
atypical or temporary values are used in only a subset of queries that belong
to the stored procedure.
"Leila" wrote:
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
A quick experiments with BOL confirms your findings:
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is
executed. If there is an existing query plan for the module, this plan
remains in the cache.
Use this option if the parameter you are supplying is atypical or if the
data has significantly changed. This option is not used for extended stored
procedures. We recommend that you use this option sparingly because it is
expensive.
David|||Thanks every body :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23YCk3t0VGHA.2760@.TK2MSFTNGP11.phx.gbl...
> That's the way it works. Consider an EXEC WITH RECOMPILE to be an
> "exception" - a one-time use of the plan. If you want a "permanent' new
> plan, check out sp_recompile in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:u6K4wp0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Hi,
> It seems that when you execute an SP along with WITH RECOMPILE option, the
> SP is recompiled for that particular execution (only) and the old plan is
> not replaced with new one in ProcCache. Is it correct or there's something
> wrong with my experimentations?!
> Thanks in advance,
> Leila
>

Exec time for a query to run in QA?

Hi All
I want to see how long it takes for my query to execute in QA. How do i do
that? Thanx in advanceThere is an Execution Time element at the lower right hand part of the
screen in QA. Optionally you could turn on Client Statistics, Statistics
Time or using GETDATE() before and after your query.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||When it's done, look in the lower right corner of the status bar, the third
box from the right shows elapsed time.
You can also do another tactic, like
SELECT @.dt = CURRENT_TIMESTAMP
-- query here
SELECT DATEDIFF(MS, @.dt, CURRENT_TIMESTAMP)
You can also look at SET STATISTICS TIME and SET STATISTICS IO topics in
books online to see how to return different stats about the query or
queries. Showing execution plan and server/client statictics can also be
useful.
http://www.aspfaq.com/2245
A
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:00E0F137-A60C-44F6-964B-23C0D27A89C9@.microsoft.com...
> Hi All
> I want to see how long it takes for my query to execute in QA. How do i do
> that? Thanx in advance|||MittyKom wrote:
> Hi All
> I want to see how long it takes for my query to execute in QA. How do
> i do that? Thanx in advance
Best way is to use Profiler. But you can use SET STATISTICS TIME ON /
OFF from Query Analyzer to see the execution. STATISTICS IO is also
useful. Try this:
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT * FROM pubs.dbo.authors
GO
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO
David Gugick
Quest Software
www.imceda.com
www.quest.com

Exec Stored Procedure from command line?

Is it possible to execute a stored proc form the DOS command Line?Hi,
Yes , you could use the command line utility OSQL or ISQL to do execute a
procedure / tSQL from command line.
OSQL -Usa -Sserver -Ppassword -Qmaster..sp_help -oc:\result.txt'
Thanks
Hari
MCDBA
"Steve" <streesh@.bellsouth.net> wrote in message
news:zJ_0d.115942$0o5.2550@.bignews1.bellsouth.net...
> Is it possible to execute a stored proc form the DOS command Line?
>sql

Exec Stored Procedure from command line?

Is it possible to execute a stored proc form the DOS command Line?
Hi,
Yes , you could use the command line utility OSQL or ISQL to do execute a
procedure / tSQL from command line.
OSQL -Usa -Sserver -Ppassword -Qmaster..sp_help -oc:\result.txt'
Thanks
Hari
MCDBA
"Steve" <streesh@.bellsouth.net> wrote in message
news:zJ_0d.115942$0o5.2550@.bignews1.bellsouth.net. ..
> Is it possible to execute a stored proc form the DOS command Line?
>

EXEC stored procedure for every line of SELECT result table - how?

Hello,

Is it possible to EXEC stored procedure from a query?
I want to execute stored procedure for every line of SELECT result
table.

I guess it's possible with cursors, but maybe it's possible to make it
easier.

Give an example, please.

Thank you in advance.

Hubert<hubert.trzewik@.gmail.com> wrote in message
news:1110986308.176019.292290@.l41g2000cwc.googlegr oups.com...
> Hello,
> Is it possible to EXEC stored procedure from a query?
> I want to execute stored procedure for every line of SELECT result
> table.
> I guess it's possible with cursors, but maybe it's possible to make it
> easier.
> Give an example, please.
> Thank you in advance.
> Hubert

A cursor is probably the best solution, unless you can rewrite your stored
proc to operate on a set of data. First, you need to get the results from
your stored proc into a table (or convert the proc to a function) - this is
described here:

http://www.sommarskog.se/share_data.html

After that, you can do something like this:

declare @.c1 int, @.c2 int, ...
declare cur cursor fast_forward
for select col1, col2, ...
from dbo.TableWithProcResults -- or from dbo.MyTableFunction()

open cur
fetch next from cur into @.c1, @.c2...
while @.@.fetch_status = 0
begin
exec dbo.MyProc @.c1, @.c2, ...
fetch next from cur into @.c1, @.c2...
end

close cur
deallocate cur

Simon|||Thanks. It was quite fast and easy to create such procedure.

EXEC stored procedure

I have a stored procedure that creates temp tables. It has two parameters,
@.tablename and @.type. I execute it with the following command.
EXEC _sp_CreateTempTable 'TableA', 'T'
With these parameters, this will create a temp table named ##TMP_TableA
based on the original table TableA.
The problem is when I run this on one of our servers it works and when I run
it on another I get the following error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Help!
Dave
Can you post the proc?
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>
|||Unfortunately not. It is proprietary information.
Do you think the error is coming from within the executed proc?
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
> Can you post the proc?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>
|||> Do you think the error is coming from within the executed proc?
Yes. I suspect the proc is executing a dynamic SQL statement (since you are
passing the table name parameter) and that's where the error is.
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:6Ungh.9006$495.7049@.trnddc06...
> Unfortunately not. It is proprietary information.
> Do you think the error is coming from within the executed proc?
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
>
|||I have a solution for you but unfortunately can't post it because it's
proprietary.
Just kidding. Try this and see if it works any better:
EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
Make sure the SP is the same version (accepts the same # of parameters) on
both servers.
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>
|||Thanks for the idea but that didn't work.
I decided that since it worked on one machine and not the other that I would
compare Management Studio options and sure enough I found a difference. I
made the change and the error went away. Don't ask me why this particular
change fixed it because I don't know why.
Options-->Query Execution-->SQL Server-->Advanced
check SET CONCAT_NULL_YIELDS_NULL
Dave
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23loaRX$HHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I have a solution for you but unfortunately can't post it because it's
>proprietary.
> Just kidding. Try this and see if it works any better:
> EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
> Make sure the SP is the same version (accepts the same # of parameters) on
> both servers.
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>
|||"DaveF" <dave@.aol.com> wrote in message news:i2Hgh.51$hy6.13@.trnddc05...
> Thanks for the idea but that didn't work.
> I decided that since it worked on one machine and not the other that I
> would compare Management Studio options and sure enough I found a
> difference. I made the change and the error went away. Don't ask me why
> this particular change fixed it because I don't know why.
> Options-->Query Execution-->SQL Server-->Advanced
> check SET CONCAT_NULL_YIELDS_NULL
Sounds like there's a NULL value being concatenated into a string somewhere
in your SP, most likely in some dynamic SQL you're trying to execute. That
setting controls null concatenation in strings. When it's set to ON,
concatenating NULL to a string returns NULL. When it's set to OFF, when a
NULL is concatenated to a string it's treated like '' - an empty string.
Here's an example that shows the difference:
DECLARE @.table VARCHAR(255)
SELECT @.table = 'syscomments'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: syscomments', @.sql
SET CONCAT_NULL_YIELDS_NULL ON
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
GO
SET CONCAT_NULL_YIELDS_NULL ON
When CONCAT_NULL_YIELDS_NULL is ON, appending NULL to a string returns NULL.
SQL Server will EXEC a NULL dynamic SQL string without complaint. When
CONCAT_NULL_YIELDS_NULL is OFF, the table name in this SELECT query is
appended as an empty string which will cause an error when SQL Server tries
to execute it.
Based on what you said, it sounds like one of the dynamic SQL statements
you're trying to execute has a NULL value appended to it somewhere, and
that's causing your error when that setting is OFF. It also means that that
particular dynamic SQL statement you think is being executed is not being
executed. Might want to check all your dynamic SQL statements that should
be executing (SELECT or PRINT them out right before the EXEC statement).
sql

EXEC stored procedure

I have a stored procedure that creates temp tables. It has two parameters,
@.tablename and @.type. I execute it with the following command.
EXEC _sp_CreateTempTable 'TableA', 'T'
With these parameters, this will create a temp table named ##TMP_TableA
based on the original table TableA.
The problem is when I run this on one of our servers it works and when I run
it on another I get the following error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
Help!
DaveCan you post the proc?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>|||Unfortunately not. It is proprietary information.
Do you think the error is coming from within the executed proc?
Dave
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
> Can you post the proc?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>>I have a stored procedure that creates temp tables. It has two
>>parameters, @.tablename and @.type. I execute it with the following command.
>> EXEC _sp_CreateTempTable 'TableA', 'T'
>> With these parameters, this will create a temp table named ##TMP_TableA
>> based on the original table TableA.
>> The problem is when I run this on one of our servers it works and when I
>> run it on another I get the following error.
>> Msg 102, Level 15, State 1, Line 1
>> Incorrect syntax near ','.
>> Help!
>> Dave
>|||> Do you think the error is coming from within the executed proc?
Yes. I suspect the proc is executing a dynamic SQL statement (since you are
passing the table name parameter) and that's where the error is.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DaveF" <dave@.aol.com> wrote in message news:6Ungh.9006$495.7049@.trnddc06...
> Unfortunately not. It is proprietary information.
> Do you think the error is coming from within the executed proc?
> Dave
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:edQytE$HHHA.420@.TK2MSFTNGP06.phx.gbl...
>> Can you post the proc?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "DaveF" <dave@.aol.com> wrote in message
>> news:5Ingh.8974$495.126@.trnddc06...
>>I have a stored procedure that creates temp tables. It has two
>>parameters, @.tablename and @.type. I execute it with the following
>>command.
>> EXEC _sp_CreateTempTable 'TableA', 'T'
>> With these parameters, this will create a temp table named ##TMP_TableA
>> based on the original table TableA.
>> The problem is when I run this on one of our servers it works and when I
>> run it on another I get the following error.
>> Msg 102, Level 15, State 1, Line 1
>> Incorrect syntax near ','.
>> Help!
>> Dave
>>
>|||I have a solution for you but unfortunately can't post it because it's
proprietary.
Just kidding. Try this and see if it works any better:
EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
Make sure the SP is the same version (accepts the same # of parameters) on
both servers.
"DaveF" <dave@.aol.com> wrote in message news:5Ingh.8974$495.126@.trnddc06...
>I have a stored procedure that creates temp tables. It has two parameters,
>@.tablename and @.type. I execute it with the following command.
> EXEC _sp_CreateTempTable 'TableA', 'T'
> With these parameters, this will create a temp table named ##TMP_TableA
> based on the original table TableA.
> The problem is when I run this on one of our servers it works and when I
> run it on another I get the following error.
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ','.
> Help!
> Dave
>|||Thanks for the idea but that didn't work.
I decided that since it worked on one machine and not the other that I would
compare Management Studio options and sure enough I found a difference. I
made the change and the error went away. Don't ask me why this particular
change fixed it because I don't know why.
Options-->Query Execution-->SQL Server-->Advanced
check SET CONCAT_NULL_YIELDS_NULL
Dave
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23loaRX$HHHA.1468@.TK2MSFTNGP04.phx.gbl...
>I have a solution for you but unfortunately can't post it because it's
>proprietary.
> Just kidding. Try this and see if it works any better:
> EXEC _sp_CreateTempTable @.tablename = 'TableA', @.type = 'T'
> Make sure the SP is the same version (accepts the same # of parameters) on
> both servers.
> "DaveF" <dave@.aol.com> wrote in message
> news:5Ingh.8974$495.126@.trnddc06...
>>I have a stored procedure that creates temp tables. It has two
>>parameters, @.tablename and @.type. I execute it with the following command.
>> EXEC _sp_CreateTempTable 'TableA', 'T'
>> With these parameters, this will create a temp table named ##TMP_TableA
>> based on the original table TableA.
>> The problem is when I run this on one of our servers it works and when I
>> run it on another I get the following error.
>> Msg 102, Level 15, State 1, Line 1
>> Incorrect syntax near ','.
>> Help!
>> Dave
>|||"DaveF" <dave@.aol.com> wrote in message news:i2Hgh.51$hy6.13@.trnddc05...
> Thanks for the idea but that didn't work.
> I decided that since it worked on one machine and not the other that I
> would compare Management Studio options and sure enough I found a
> difference. I made the change and the error went away. Don't ask me why
> this particular change fixed it because I don't know why.
> Options-->Query Execution-->SQL Server-->Advanced
> check SET CONCAT_NULL_YIELDS_NULL
Sounds like there's a NULL value being concatenated into a string somewhere
in your SP, most likely in some dynamic SQL you're trying to execute. That
setting controls null concatenation in strings. When it's set to ON,
concatenating NULL to a string returns NULL. When it's set to OFF, when a
NULL is concatenated to a string it's treated like '' - an empty string.
Here's an example that shows the difference:
DECLARE @.table VARCHAR(255)
SELECT @.table = 'syscomments'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: syscomments', @.sql
SET CONCAT_NULL_YIELDS_NULL ON
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT @.table = NULL
SELECT @.sql = 'SELECT TOP 10 * FROM ' + @.table
SELECT 'Table: NULL, CONCAT_NULL_YIELDS_NULL ON', @.sql
GO
SET CONCAT_NULL_YIELDS_NULL ON
When CONCAT_NULL_YIELDS_NULL is ON, appending NULL to a string returns NULL.
SQL Server will EXEC a NULL dynamic SQL string without complaint. When
CONCAT_NULL_YIELDS_NULL is OFF, the table name in this SELECT query is
appended as an empty string which will cause an error when SQL Server tries
to execute it.
Based on what you said, it sounds like one of the dynamic SQL statements
you're trying to execute has a NULL value appended to it somewhere, and
that's causing your error when that setting is OFF. It also means that that
particular dynamic SQL statement you think is being executed is not being
executed. Might want to check all your dynamic SQL statements that should
be executing (SELECT or PRINT them out right before the EXEC statement).

Exec Stored Proc (C#) - the Size property has an invalid size of 0

Hi All
I am trying to execute a stored procedure that does a very simple
lookup and returns a text field. However, when I try to execute it, I
am getting a rather strange error that I can't seem to fix!
There is defiantely information coming back as I have tested this in
Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
String[1]: the Size property has an invalid size of 0.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: String[1]: the
Size property has an invalid size of 0.
Many thanks in advance for your help
Darren
STORED PROC CODE
=================
ALTER PROCEDURE [dbo].[sp_ReadSessionXML]
-- Add the parameters for the stored procedure here
@.iID int,
@.tXML text = null output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @.tXML = [XML] FROM T_Requests WHERE ResponseID = @.iID
pRINT @.tXML
END
C# CODE
=======
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = m_sConnectionString;
oConn.Open();
SqlCommand oCmd = new SqlCommand("sp_ReadSessionXML",
oConn);
oCmd.Connection = oConn;
oCmd.CommandType = CommandType.StoredProcedure;
SqlParameter spID = oCmd.Parameters.Add("@.iID",
SqlDbType.Int);
spID.Direction = ParameterDirection.Input;
spID.Value = iSQLCacheID;
SqlParameter spXML = oCmd.Parameters.Add("@.tXML",
SqlDbType.Text);
spXML.Direction = ParameterDirection.Output;
oCmd.ExecuteNonQuery();
oConn.Close();
XmlDocument xdDBCache = new XmlDocument();
xdDBCache.LoadXml(oCmd.Parameters["@.tXML"].Value.ToString());
return xdDBCache;
}Just a guess, but since the sp is going to return data, I don't think you
should use ExecuteNonQuery. ExecuteNonQuery is used for executing statements
that don't return a result set (like UPDATE or DELETE).
"daz_oldham" wrote:

> Hi All
> I am trying to execute a stored procedure that does a very simple
> lookup and returns a text field. However, when I try to execute it, I
> am getting a rather strange error that I can't seem to fix!
> There is defiantely information coming back as I have tested this in
> Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
> String[1]: the Size property has an invalid size of 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
> Exception Details: System.InvalidOperationException: String[1]: the
> Size property has an invalid size of 0.
> Many thanks in advance for your help
> Darren
> STORED PROC CODE
> =================
> ALTER PROCEDURE [dbo].[sp_ReadSessionXML]
> -- Add the parameters for the stored procedure here
> @.iID int,
> @.tXML text = null output
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- Insert statements for procedure here
> SELECT @.tXML = [XML] FROM T_Requests WHERE ResponseID = @.iID
> pRINT @.tXML
> END
>
> C# CODE
> =======
> SqlConnection oConn = new SqlConnection();
> oConn.ConnectionString = m_sConnectionString;
> oConn.Open();
> SqlCommand oCmd = new SqlCommand("sp_ReadSessionXML",
> oConn);
> oCmd.Connection = oConn;
> oCmd.CommandType = CommandType.StoredProcedure;
> SqlParameter spID = oCmd.Parameters.Add("@.iID",
> SqlDbType.Int);
> spID.Direction = ParameterDirection.Input;
> spID.Value = iSQLCacheID;
> SqlParameter spXML = oCmd.Parameters.Add("@.tXML",
> SqlDbType.Text);
> spXML.Direction = ParameterDirection.Output;
> oCmd.ExecuteNonQuery();
> oConn.Close();
> XmlDocument xdDBCache = new XmlDocument();
> xdDBCache.LoadXml(oCmd.Parameters["@.tXML"].Value.ToString());
> return xdDBCache;
> }
>|||daz_oldham (Darren.Ratcliffe@.gmail.com) writes:
> There is defiantely information coming back as I have tested this in
> Query Analyzer. The error actuall comes on my oCmd.ExecuteNonQuery();
> String[1]: the Size property has an invalid size of 0.
> Description: An unhandled exception occurred during the execution of
> the current web request. Please review the stack trace for more
> information about the error and where it originated in the code.
The error message is unknown to me, and I can't say where it's coming
from. However, I do spot an error:

> @.tXML text = null output
This won't fly. text for output parameters is bound to fail. You
cannot assign to variables of the type text.
If you are on SQL 2005, use varchar(MAX) instead. Or even better the
xml data type.
If you are on SQL 2000, return the XML column as a result set instead.
(In which case you must use something different than ExecuteNonQuery
to retrieve the data.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Mark Williams (MarkWilliams@.discussions.microsoft.com) writes:
> Just a guess, but since the sp is going to return data, I don't think
> you should use ExecuteNonQuery. ExecuteNonQuery is used for executing
> statements that don't return a result set (like UPDATE or DELETE).
But Daz's procedure does not return any result set, but returns data in
an OUTPUT parameter (or would have returned, had he chosen a data type
that is eligible for output parameters). The procedure also includes a
PRINT statement. Both of these are fine with ExecuteNonQuery. (To get
the data from the PRINT statement you need an InfoMessage event handler.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland
I have changed this to get the value out via a data reader, and it is
spot on.
I have never been aware in the past about having a text value as an
output parameter, but I know now!
I am currently using SQL 2000 so can't take advantage of the added XML
benefits in 2005 which is a shame really.
Many thanks for your help - and everyone else too.
Regards
Darren

Tuesday, March 27, 2012

EXEC SQL TASK to FLAT FILE - questions

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?

You mentioned the parameter is used to build up the stored procedure statements. If it is the case, try to write the line of SQL command you used for invoking the stored procedure into a flat file for logging purpose. The log file can be used to check syntax and display parameters passed in. Sometime, it will help debugging.

|||

K108 wrote:

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.

Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?

Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.

You may have more than one option to get this done; but we need more info to help you

|||

Did you ever resolve this issue? I'm having the same problem using the Foreach loop container to access excel files on the network. I'm getting the same exact error when trying to use a variable to change the connection string in my excel file connection. Here's the error message:

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

Any help would be greatly appreciated!

Kevin

|||

You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.

Thanks.

EXEC SQL TASK to FLAT FILE - questions

Hi,

I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.

However, I now need to send the output of each stored stored procedure to a flat file.

I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.

But I get this error when I try to save:

Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C

An OLE DB Recored is available. Source: blah

"Command text was not set for the command object"

What to do?

Thanks

Would the data flow task work well as a stand alone task with one fixed parameter before it is wrapped into the "FOREACHLOOP"?

|||

K108 wrote:

Hi,
I have a FOREACHLOOP container that contains an EXECUTE SQL TASK. The EXECUTE SQL TASK is executing a list of stored procedures from a table. This part is working.
However, I now need to send the output of each stored stored procedure to a flat file.
I dropped a DATA FLOW task inside the FOREACHLOOP container, and then created an OLEDB source and FLAT FILE destination on the Data Flow tab.
However, I'm not sure how this is going to work. When I click on the OLEDB source, Connection Manager, I thought I should select the Data Access Mode as SQL Command from variable. Then select the variable name.
But I get this error when I try to save:
Error at Data Flow Task [OLE DB Source [42]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C
An OLE DB Recored is available. Source: blah
"Command text was not set for the command object"
What to do?
Thanks

Something is not clear here for me. If you use an Execute SQl Task to execute the SPs; I don't see a way you can get their resultsets inside of the data flow.

Can you provide the SQL statement you are placing in the variable used in the OLE DB SOurce component?

Are the SPs' resulet structures consistent? if not, you will need a separate dataflow.

You may have more than one option to get this done; but we need more info to help you

|||

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

Any help would be greatly appreciated!

Kevin

|||

You will need to provide a valid initial value in used variables or try to play with the DelayValidation flag.

Thanks.

exec sp_primarykeys and exec sp_foreignkeys

Hi,
I need to check the primary and the foreign keys of
existing user tables in one table to create the
corresponding Data Model.
I execute the following procedure, i see the BOL and i
need to enter one linked server for the procedure execute
fine. Im not doing this because im doing this in the local
machine.
exec sp_primarykeys
How can i generate this Data Model or how can i get this
relations with the minimum effort?
Best RegardsCC&JM
Try put your local sever name and remember you have to enable access data
exec sp_serveroption 'Server','data access','true'
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bded01c47a14$cec4e000$a601280a@.phx.gbl...
> Hi,
> I need to check the primary and the foreign keys of
> existing user tables in one table to create the
> corresponding Data Model.
> I execute the following procedure, i see the BOL and i
> need to enter one linked server for the procedure execute
> fine. Im not doing this because im doing this in the local
> machine.
> exec sp_primarykeys
> How can i generate this Data Model or how can i get this
> relations with the minimum effort?
> Best Regards
>

Monday, March 26, 2012

Exec Process Task problem

I am trying to use a variable in an Execute Process Task

Here is my code that I use to set the variable and it works and has the right information:

Public Sub Main()

Dim serverNamePart As String = Mid(CStr(System.Environment.MachineName()), 1, 5)

Dts.Variables.Item("gvServerName").Value = (CStr(System.Environment.MachineName()))

Select Case serverNamePart

Case "ATCDE" 'Development server

Dts.Variables.Item("gvAppServer").Value = "ATCDEVRPNAPP01"

Case "ATCIN" 'Intergration server

Dts.Variables.Item("gvAppServer").Value = "ATCINTRPNAPP01"

Case "ATCDE" 'Development server

Dts.Variables.Item("gvAppServer").Value = "ATCQARPNAPP01"

Case "LTC1P" 'Development server

Dts.Variables.Item("gvAppServer").Value = "LTC1RPNAPP01 LTC1RPNAPP02 LTC1RPNAPP03 LTC1RPNAPP04"

End Select

' MsgBox(Dts.Variables.Item("gvServerName").Value)

MsgBox(Dts.Variables.Item("gvAppServer").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

On my Execute Process task I have User::gvAppServer as my StandardInputVariable but when I execute the task I get

[Execute Process Task] Error: In Executing "D:\RPNUtility\bin\AutoTransmitCall.exe" "" at "", The process exit code was "1" while the expected was "0".

If I take out the StandardInputVariable and hard code the value in Arguments it works. What am I doing wrong?

Phil, I believe you want to parameterize your "Arguments" property rather than your StandardInputVariable property. Use the Expressions page of the Execute Process Editor to use your gvAppServer variable for Arguments.

Your StandardInputVariable would be used for data that is processed by an executable, rather than a parameter for it.

|||That looks like it worked. Thanks.

Thursday, March 29, 2012

execure store procedure from Query Analizer

execure store procedure from Query Analizer

execure store procedure from Query Analizer

exec(select...), how supress the output?

EXEC WITH RECOMPILE

EXEC WITH RECOMPILE

EXEC WITH RECOMPILE

EXEC WITH RECOMPILE

Exec time for a query to run in QA?

Exec Stored Procedure from command line?

Exec Stored Procedure from command line?

EXEC stored procedure for every line of SELECT result table - how?

EXEC stored procedure

EXEC stored procedure

EXEC stored procedure

Exec Stored Proc (C#) - the Size property has an invalid size of 0

Tuesday, March 27, 2012

EXEC SQL TASK to FLAT FILE - questions

EXEC SQL TASK to FLAT FILE - questions

exec sp_primarykeys and exec sp_foreignkeys

Monday, March 26, 2012

Exec Process Task problem

Excel Import

Blog Archive

About Me