Executing SP From a Batch file

I was wondering how I can create a batch file that call a store procedure an
d
put the output on a file.Silver wrote:
> I was wondering how I can create a batch file that call a store
> procedure and put the output on a file.
Try osql.exe - the command-line options are documented in Books Online or by
typing osql /? at the command line.
John.|||Hi,
Call OSQL inside the command prompt
Entries inside the batch file will be
OSQL -Usa -Ppassword -S Servername -d Dbname -Qprocedurename -oc:\output.log
Thanks
Hari
SQL Server MVP
"Silver" <Silver@.discussions.microsoft.com> wrote in message
news:341FE12A-19E6-4A8B-8003-558797A637BF@.microsoft.com...
>I was wondering how I can create a batch file that call a store procedure
>and
> put the output on a file.

Executing Oracle Stored Procedure with output parameters using ADO.NET connection

I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DB\Microsoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving:

[Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works.

Any help on this would be most appriciated.

hi Jason,

I got the same error. What driver are you using? I use .NET provider Oracle client data provider.

Can you also tell me how did you successfully invoke the stored proc using OLEDB for Oracle?

I have an sp with 1 input parameter of type Number and 14 output parameters some of type Number and some Varchar2.

Can you tell me the sql statement syntax to use and the parameter mapping to use. I know that the parameter name starts with 0,1,2...

Please help

thanks.

sandeep

sql

Executing Oracle Stored Procedure via SQL Server 2000 Linked Serve

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

executing a dymamically built string with an OUTPUT parameter

Hi friends,

I have a dynamically built string that i need to execute and set a parameter to. I have come accross help in the books online but I need to set a variable to the answer of the dynamically executed string. It looks as follows:

DECLARE @.sRCDQueryString nvarchar(1000)
DECLARE @.sActivityName varchar(100)
DECLARE @.sRCDParmDefinition nvarchar(500)
DECLARE @.lGLCodeID int
DECLARE @.rRCDUnitValue real

SET @.lGLCOdeID = 1--391
SET @.sActivityName = 'REPPLA'

SET @.sRCDQueryString = 'SELECT ' + @.sActivityName +
' FROM ABCRCDMatrix WHERE GLCodeID = @.GL'
SET @.sRCDParmDefinition = '@.GL int, @.Value real OUTPUT';
EXEC sp_executesql @.sRCDQueryString,@.sRCDParmDefinition,@.GL = @.lGLCodeID, @.Value = @.rRCDUnitValue OUTPUT

select @.rRCDUnitValue

When I execute this code, I get a value by the line in red but it doesn't seem to be allocating a the result correctly to @.rRCDUnitValue since this variable is null

How can i get the result correctly allocated to the variable?

Regards

Use the following statement, you missed the value assignment,

Code Snippet

DECLARE @.sRCDQueryString nvarchar(1000)

DECLARE @.sActivityName varchar(100)

DECLARE @.sRCDParmDefinition nvarchar(500)

DECLARE @.lGLCodeID int

DECLARE @.rRCDUnitValue real

SET @.lGLCOdeID =1--391

SET @.sActivityName = 'REPPLA'

SET @.sRCDQueryString = 'SELECT @.Value=' + @.sActivityName +

' FROM ABCRCDMatrix WHERE GLCodeID = @.GL'

SET @.sRCDParmDefinition = '@.GL int, @.Value real OUTPUT';

EXEC sp_executesql @.sRCDQueryString,@.sRCDParmDefinition,@.GL = @.lGLCodeID, @.Value = @.rRCDUnitValue OUTPUT

select @.rRCDUnitValue

|||

You are declaring a parameter, but that parameter is not being used at all inside the dynamic statement. See this example:

Code Snippet

use northwind

go

declare @.sql nvarchar(4000)

declare @.orderid int

declare @.order_total money

set @.sql = N'select @.order_total = sum(quantity * unitprice * (1.00 - discount)) from dbo.[order details] where orderid = @.orderid'

set @.orderid = 10250

exec dbo.sp_executesql @.sql, N'@.orderid int, @.order_total money OUTPUT', @.orderid, @.order_total OUTPUT

select @.order_total

go

AMB

|||

You got the answers for how to fix yoru code. But why do you need dynamic SQL in the first place? Are you aware of the risks and performance problems (benefits in some cases) with dynamic SQL code?

|||Hi Manivannan,

The @.sActivityName is actually the name of an unknown column that is looked up on another table, wich has been pivoted on the current table i'm trying to do a lookup on.

Thanks for the response
Mike
|||Hi Umachandar,

Yes I am aware of the performance knock but I have no choice in this matter to use a dynamic string since the columns I need to lookup/use are unknown at the point of execution since they are looked up from another table..

Essentially they are the result of a table that had been pivoted.

Regards
Mike
|||Ahhh, Thank you indeed sir, you have showed me the mistake and I appreciate it coz my variable now returns a value.

Kind Regards
Mike
sql

ExecuteWithResultsAndMessage2 SQL-DMO and perl

Hello to all:

I want to get the output from xp_msver through
ExecuteWithResultsAndMessage2 SQL-DMO and perl. This is the script
I've got so far. Can anyone give me a few more pointers?

use strict;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft SQLDMO Object Library';

my ($server, $obj, $job, $jobs, $pathname, $c, $qr, $m );
my $srv;
my @.servers = qw( KORTRIGHT );

$server = Win32::OLE->new('SQLDMO.SQLServer2')
or die "Could not create SQLDMO object.";

$server->{LoginSecure} = 1;

foreach $srv (@.servers) {

$server->connect($srv, '', '');
if (Win32::OLE->LastError()) {
die "***Err: Could not connect to $srv.";
}

printf qq[\n%10.10s %-50.50s %10.10s %s\n], "Hostname:",
$server->HostName, "Server:", $srv;

my $dbs = $server->Databases();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the alerts collection in $srv.";
}

foreach $obj (in($dbs)) {
printf qq[%10.10s %-50.50s %10.10s %s\n], "Database:",
$obj->Name, "Size:", $obj->Size." Mb";
}

$jobs = $server->Jobserver;

foreach $obj (in($jobs->Jobs() ) ) {
if( $obj->Name =~ /'(.*)'/ ) {
$job = $1;
}
foreach my $step ( in($obj->Jobsteps()) ) {
if( $step->command =~ /-BkUpDB "(\S+)"/ ) {
$pathname = $1;
printf qq[%10.10s %s\n], "Job:", $job;
printf qq[%10.10s %s\n], "pathname:", $pathname;
#print $step->command.qq[\n];
}
}
}

$c = "xp_msver";
$qr = $server->ExecuteWithResultsAndMessages2( $c, $m );
$server->DisConnect;
}Philip Bondi (pjbondi@.SystemDatabase.com) writes:
> I want to get the output from xp_msver through
> ExecuteWithResultsAndMessage2 SQL-DMO and perl. This is the script
> I've got so far. Can anyone give me a few more pointers?

And the problem is?

OK, so I ran your script, and this line:

> $qr = $server->ExecuteWithResultsAndMessages2( $c, $m );

resulted in:

Win32::OLE(0.1701) error 0x8002000f: "Parameter not optional"
in METHOD/PROPERTYGET "ExecuteWithResultsAndMessages2" at slask.pl line 51

This is because you are passing an undef in $m. The correct call would be:

$qr = $server->ExecuteWithResultsAndMessages2( $c, \$m );

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Execute SQL Task w/ XML Output

I have a stored procedure that returns XML and have defined my own "Root" element w/ "ROOT('urlset')" in the stored procedure. When I put this into my Control Flow, I have a subsequent task that takes this XML stream and writes it to a file. The issue I am running into is that SSIS is adding it's own "Root" element before my output. Is there any way of avoiding this?
Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.|||

Matt Masson - MSFT wrote:

Unfortunately, the Execute SQL Task always inserts the <ROOT> ... </ROOT> tags when you ask for an XML result set. The typical approach is to strip out the values you want in an XML Task or Script Task.

Hi Matt,

Why? What's the rationale for this behaviour?

-Jamie

|||

Good question.

The task doesn't really parse the input SQL statement (or the results, for that matter), so it appears the tags were added as a simple way to ensure we were always returning a well-formed XML document.

One could argue that this should be optional behaviour, and that we should provide a property which allows you to tell the task to leave the results alone. I've opened a tracking item to consider the change for Katmai.

Feel free to open an item on Connect if you have ideas on how it should work.

~Matt

|||

No need. You beat me to it

Execute SQL task editor problem

hi !

I have problem here with sql task editor. i have a stored proceedure with 1 input value and 1 output value which needs to be updated in a variable.

stored proc - exec GetRDate ? , ? output

input value is set in a varaible.

I tried giving 1 input and 1 output in the parameter box and result set as fullresultset 1 value for variable -

" failed with the following error: "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Get Retention Date

what should i give in the parameter and result set

Thanks,

Jasmine

Check out "The ExecuteSQL Task" explained @. http://www.sqlis.com/default.aspx?58
Lots of different examples are discussed.

Also check out the sections 'Using Parameters with Stored Procedures'', 'Getting Values of Return Codes', 'Specifying a Result Set Type', 'Populating a Variable with a Result Set' @. http://msdn2.microsoft.com/en-us/library/ms141003.aspx

Thanks,
Loonysan

Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection type

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.

Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection ty

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.

Execute SQL Statment

Hello,
I need to execute "SQL Statments" that belong to a script
but i think that im not doing it in the right way.
I only can print the output, but i dont know how to
execute it.
I've th following code:
if (select count(1) from master..sysdatabases) > 0
declare dbname_cur cursor for select [name] from
master..sysdatabases
declare @.dbname as varchar(500)
declare @.sqlstring as varchar(500
)
open dbname_cur
fetch next from dbname_cur into @.dbname
WHILE @.@.FETCH_STATUS = 0
begin
set @.dbname = 'select [name],[filename] from ' + @.dbname
+ '..sysfiles'
print @.dbname
fetch next from dbname_cur into @.dbname
end
close dbname_cur
deallocate dbname_cur
This code give me the following output:
select [name],[filename] from example..sysfiles
select [name],[filename] from master..sysfiles
select [name],[filename] from model..sysfiles
select [name],[filename] from msdb..sysfiles
select [name],[filename] from Northwind..sysfiles
select [name],[filename] from pubs..sysfiles
select [name],[filename] from tempdb..sysfiles
Now i need to execute this output.
Best regardsPlace the follwoing code after the "print @.dbname" line in your cursor loop:
exec (@.dbname)
--Buddy
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:0e4b01c42613$2eeb8e80$a601280a@.phx.gbl...
> Hello,
> I need to execute "SQL Statments" that belong to a script
> but i think that im not doing it in the right way.
> I only can print the output, but i dont know how to
> execute it.
> I've th following code:
> if (select count(1) from master..sysdatabases) > 0
> declare dbname_cur cursor for select [name] from
> master..sysdatabases
> declare @.dbname as varchar(500)
> declare @.sqlstring as varchar(500
> )
> open dbname_cur
> fetch next from dbname_cur into @.dbname
> WHILE @.@.FETCH_STATUS = 0
> begin
> set @.dbname = 'select [name],[filename] from ' + @.dbname
> + '..sysfiles'
> print @.dbname
> fetch next from dbname_cur into @.dbname
> end
> close dbname_cur
> deallocate dbname_cur
> This code give me the following output:
> select [name],[filename] from example..sysfiles
> select [name],[filename] from master..sysfiles
> select [name],[filename] from model..sysfiles
> select [name],[filename] from msdb..sysfiles
> select [name],[filename] from Northwind..sysfiles
> select [name],[filename] from pubs..sysfiles
> select [name],[filename] from tempdb..sysfiles
> Now i need to execute this output.
> Best regards
>|||Use:
EXEC (@.dbname)
instead of or right after print @.dbname.
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:0e4b01c42613$2eeb8e80$a601280a@.phx.gbl...
> Hello,
> I need to execute "SQL Statments" that belong to a script
> but i think that im not doing it in the right way.
> I only can print the output, but i dont know how to
> execute it.
> I've th following code:
> if (select count(1) from master..sysdatabases) > 0
> declare dbname_cur cursor for select [name] from
> master..sysdatabases
> declare @.dbname as varchar(500)
> declare @.sqlstring as varchar(500
> )
> open dbname_cur
> fetch next from dbname_cur into @.dbname
> WHILE @.@.FETCH_STATUS = 0
> begin
> set @.dbname = 'select [name],[filename] from ' + @.dbname
> + '..sysfiles'
> print @.dbname
> fetch next from dbname_cur into @.dbname
> end
> close dbname_cur
> deallocate dbname_cur
> This code give me the following output:
> select [name],[filename] from example..sysfiles
> select [name],[filename] from master..sysfiles
> select [name],[filename] from model..sysfiles
> select [name],[filename] from msdb..sysfiles
> select [name],[filename] from Northwind..sysfiles
> select [name],[filename] from pubs..sysfiles
> select [name],[filename] from tempdb..sysfiles
> Now i need to execute this output.
> Best regards
>

Execute SQL Statment

Hello,
I need to execute "SQL Statments" that belong to a script
but i think that im not doing it in the right way.
I only can print the output, but i dont know how to
execute it.
I've th following code:
if (select count(1) from master..sysdatabases) > 0
declare dbname_cur cursor for select [name] from
master..sysdatabases
declare @.dbname as varchar(500)
declare @.sqlstring as varchar(500
)
open dbname_cur
fetch next from dbname_cur into @.dbname
WHILE @.@.FETCH_STATUS = 0
begin
set @.dbname = 'select [name],[filename] from ' + @.dbname
+ '..sysfiles'
print @.dbname
fetch next from dbname_cur into @.dbname
end
close dbname_cur
deallocate dbname_cur
This code give me the following output:
select [name],[filename] from example..sysfiles
select [name],[filename] from master..sysfiles
select [name],[filename] from model..sysfiles
select [name],[filename] from msdb..sysfiles
select [name],[filename] from Northwind..sysfiles
select [name],[filename] from pubs..sysfiles
select [name],[filename] from tempdb..sysfiles
Now i need to execute this output.
Best regards
Place the follwoing code after the "print @.dbname" line in your cursor loop:
exec (@.dbname)
--Buddy
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:0e4b01c42613$2eeb8e80$a601280a@.phx.gbl...
> Hello,
> I need to execute "SQL Statments" that belong to a script
> but i think that im not doing it in the right way.
> I only can print the output, but i dont know how to
> execute it.
> I've th following code:
> if (select count(1) from master..sysdatabases) > 0
> declare dbname_cur cursor for select [name] from
> master..sysdatabases
> declare @.dbname as varchar(500)
> declare @.sqlstring as varchar(500
> )
> open dbname_cur
> fetch next from dbname_cur into @.dbname
> WHILE @.@.FETCH_STATUS = 0
> begin
> set @.dbname = 'select [name],[filename] from ' + @.dbname
> + '..sysfiles'
> print @.dbname
> fetch next from dbname_cur into @.dbname
> end
> close dbname_cur
> deallocate dbname_cur
> This code give me the following output:
> select [name],[filename] from example..sysfiles
> select [name],[filename] from master..sysfiles
> select [name],[filename] from model..sysfiles
> select [name],[filename] from msdb..sysfiles
> select [name],[filename] from Northwind..sysfiles
> select [name],[filename] from pubs..sysfiles
> select [name],[filename] from tempdb..sysfiles
> Now i need to execute this output.
> Best regards
>
|||Use:
EXEC (@.dbname)
instead of or right after print @.dbname.
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:0e4b01c42613$2eeb8e80$a601280a@.phx.gbl...
> Hello,
> I need to execute "SQL Statments" that belong to a script
> but i think that im not doing it in the right way.
> I only can print the output, but i dont know how to
> execute it.
> I've th following code:
> if (select count(1) from master..sysdatabases) > 0
> declare dbname_cur cursor for select [name] from
> master..sysdatabases
> declare @.dbname as varchar(500)
> declare @.sqlstring as varchar(500
> )
> open dbname_cur
> fetch next from dbname_cur into @.dbname
> WHILE @.@.FETCH_STATUS = 0
> begin
> set @.dbname = 'select [name],[filename] from ' + @.dbname
> + '..sysfiles'
> print @.dbname
> fetch next from dbname_cur into @.dbname
> end
> close dbname_cur
> deallocate dbname_cur
> This code give me the following output:
> select [name],[filename] from example..sysfiles
> select [name],[filename] from master..sysfiles
> select [name],[filename] from model..sysfiles
> select [name],[filename] from msdb..sysfiles
> select [name],[filename] from Northwind..sysfiles
> select [name],[filename] from pubs..sysfiles
> select [name],[filename] from tempdb..sysfiles
> Now i need to execute this output.
> Best regards
>

Execute SQL Statment

Hello,
I need to execute "SQL Statments" that belong to a script
but i think that im not doing it in the right way.
I only can print the output, but i dont know how to
execute it.
I've th following code:
if (select count(1) from master..sysdatabases) > 0
declare dbname_cur cursor for select [name] from
master..sysdatabases
declare @.dbname as varchar(500)
declare @.sqlstring as varchar(500
)
open dbname_cur
fetch next from dbname_cur into @.dbname
WHILE @.@.FETCH_STATUS = 0
begin
set @.dbname = 'select [name],[filename] from ' + @.dbname
+ '..sysfiles'
print @.dbname
fetch next from dbname_cur into @.dbname
end
close dbname_cur
deallocate dbname_cur
This code give me the following output:
select [name],[filename] from example..sysfiles
select [name],[filename] from master..sysfiles
select [name],[filename] from model..sysfiles
select [name],[filename] from msdb..sysfiles
select [name],[filename] from Northwind..sysfiles
select [name],[filename] from pubs..sysfiles
select [name],[filename] from tempdb..sysfiles
Now i need to execute this output.
Best regardsPlace the follwoing code after the "print @.dbname" line in your cursor loop:
exec (@.dbname)
--Buddy
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:0e4b01c42613$2eeb8e80$a601280a@.phx.gbl...
> Hello,
> I need to execute "SQL Statments" that belong to a script
> but i think that im not doing it in the right way.
> I only can print the output, but i dont know how to
> execute it.
> I've th following code:
> if (select count(1) from master..sysdatabases) > 0
> declare dbname_cur cursor for select [name] from
> master..sysdatabases
> declare @.dbname as varchar(500)
> declare @.sqlstring as varchar(500
> )
> open dbname_cur
> fetch next from dbname_cur into @.dbname
> WHILE @.@.FETCH_STATUS = 0
> begin
> set @.dbname = 'select [name],[filename] from ' + @.dbname
> + '..sysfiles'
> print @.dbname
> fetch next from dbname_cur into @.dbname
> end
> close dbname_cur
> deallocate dbname_cur
> This code give me the following output:
> select [name],[filename] from example..sysfiles
> select [name],[filename] from master..sysfiles
> select [name],[filename] from model..sysfiles
> select [name],[filename] from msdb..sysfiles
> select [name],[filename] from Northwind..sysfiles
> select [name],[filename] from pubs..sysfiles
> select [name],[filename] from tempdb..sysfiles
> Now i need to execute this output.
> Best regards
>|||Use:
EXEC (@.dbname)
instead of or right after print @.dbname.
--
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:0e4b01c42613$2eeb8e80$a601280a@.phx.gbl...
> Hello,
> I need to execute "SQL Statments" that belong to a script
> but i think that im not doing it in the right way.
> I only can print the output, but i dont know how to
> execute it.
> I've th following code:
> if (select count(1) from master..sysdatabases) > 0
> declare dbname_cur cursor for select [name] from
> master..sysdatabases
> declare @.dbname as varchar(500)
> declare @.sqlstring as varchar(500
> )
> open dbname_cur
> fetch next from dbname_cur into @.dbname
> WHILE @.@.FETCH_STATUS = 0
> begin
> set @.dbname = 'select [name],[filename] from ' + @.dbname
> + '..sysfiles'
> print @.dbname
> fetch next from dbname_cur into @.dbname
> end
> close dbname_cur
> deallocate dbname_cur
> This code give me the following output:
> select [name],[filename] from example..sysfiles
> select [name],[filename] from master..sysfiles
> select [name],[filename] from model..sysfiles
> select [name],[filename] from msdb..sysfiles
> select [name],[filename] from Northwind..sysfiles
> select [name],[filename] from pubs..sysfiles
> select [name],[filename] from tempdb..sysfiles
> Now i need to execute this output.
> Best regards
>|||After print @.dbname, add
exec (@.dbname)
Regards,
Paul Ibison|||Thanks everybody,
Best Regards
>--Original Message--
>Hello,
>I need to execute "SQL Statments" that belong to a script
>but i think that im not doing it in the right way.
>I only can print the output, but i dont know how to
>execute it.
>I've th following code:
>if (select count(1) from master..sysdatabases) > 0
> declare dbname_cur cursor for select [name] from
>master..sysdatabases
> declare @.dbname as varchar(500)
> declare @.sqlstring as varchar(500
>)
> open dbname_cur
> fetch next from dbname_cur into @.dbname
> WHILE @.@.FETCH_STATUS = 0
> begin
> set @.dbname = 'select [name],[filename] from ' +
@.dbname
>+ '..sysfiles'
> print @.dbname
> fetch next from dbname_cur into @.dbname
> end
>close dbname_cur
>deallocate dbname_cur
>This code give me the following output:
>select [name],[filename] from example..sysfiles
>select [name],[filename] from master..sysfiles
>select [name],[filename] from model..sysfiles
>select [name],[filename] from msdb..sysfiles
>select [name],[filename] from Northwind..sysfiles
>select [name],[filename] from pubs..sysfiles
>select [name],[filename] from tempdb..sysfiles
>Now i need to execute this output.
>Best regards
>.
>

Execute Process Task / Console Output

I'm just starting to find my way around SSIS, coming from SQL 2000 DTS, but I can't see a way of including a Win32 Console application's output into the logging process.

I've started playing around with SSIS Logs (OnTaskFailed etc..), but I can find no where to allow me to capture the output from a console app. In SQL 2000 DTS I could capture this by specifying an "output file" on the advanced tab of the job step definition. Is there something as straight forward as this in SSIS ?

I'd be grateful for a few pointers in the right direction.

One way would be to pipe the console output to a text file using appropriate calling syntax (myconsoleapp.exe >> myoutput.txt) , then read it in via a flat file source in a following step. of course the console output might need to be structured or parsed in a way that meets your needs for the further processing of the data, but it's one way.

Ken

|||

Thanks for the suggestions Ken. I see where you are going with it, but it's not that I need to further process the output from console app's, just log what they are producing. In fact, they do already produce a flat file which mirrors the console output it's just that I'd like the whole output from the package to be in one place .

Under SQL 2000, the job steps manage to allow you to easily capture both the SQL server messages for tasks along with the output from an application. It seems that the logging in 2005 is much more complete and varied, but makes this simple task more complex to accomplish. I'm sure it can be done, I just don't know the best avenue for investigation. My last thought was to try using the StandardOutputVariable property on the execute process task to capture the data to a string. I haven't persued this as it seemed a bit of a long shot, but that's where I'm at.

|||Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.

Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.|||

jaegd wrote:

Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.

Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.

I just tried setting the StandardOutputVariable and StandardErrorVariable properties, and now the task pops up a window each time the process is executed (it's in a loop), even though I've also set the WindowStyle property to Hidden. Is that to be expected?

Execute Process Task / Console Output

I'm just starting to find my way around SSIS, coming from SQL 2000 DTS, but I can't see a way of including a Win32 Console application's output into the logging process.

I've started playing around with SSIS Logs (OnTaskFailed etc..), but I can find no where to allow me to capture the output from a console app. In SQL 2000 DTS I could capture this by specifying an "output file" on the advanced tab of the job step definition. Is there something as straight forward as this in SSIS ?

I'd be grateful for a few pointers in the right direction.

One way would be to pipe the console output to a text file using appropriate calling syntax (myconsoleapp.exe >> myoutput.txt) , then read it in via a flat file source in a following step. of course the console output might need to be structured or parsed in a way that meets your needs for the further processing of the data, but it's one way.

Ken

|||

Thanks for the suggestions Ken. I see where you are going with it, but it's not that I need to further process the output from console app's, just log what they are producing. In fact, they do already produce a flat file which mirrors the console output it's just that I'd like the whole output from the package to be in one place .

Under SQL 2000, the job steps manage to allow you to easily capture both the SQL server messages for tasks along with the output from an application. It seems that the logging in 2005 is much more complete and varied, but makes this simple task more complex to accomplish. I'm sure it can be done, I just don't know the best avenue for investigation. My last thought was to try using the StandardOutputVariable property on the execute process task to capture the data to a string. I haven't persued this as it seemed a bit of a long shot, but that's where I'm at.

|||Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.

Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.|||

jaegd wrote:

Using StandardOutputVariable is not a long shot. Its a sure bet. Just use that variable, and in a subsequent Script task, call Dts.Events.FireInformation() using that variable as the message parameter.

Provided your logging options are set on the script task for the event type ("OnInformation") and for whatever log provider you choose, you can have the data in one place.

I just tried setting the StandardOutputVariable and StandardErrorVariable properties, and now the task pops up a window each time the process is executed (it's in a loop), even though I've also set the WindowStyle property to Hidden. Is that to be expected?