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:

OK

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.

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:

OK

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_who2 'active' - Status = Sleeping?

OK... I'm getting confused...
I'm executing:
exec sp_who2 'active'
And getting the below results:
SPID Status Login
-- -- --
198 sleeping Domain\User
...
...
With that said, can someone please refresh my memory about sleeping SPID's
status? How can it be active but sleeping?
Thanks,
MichaelI ask because when I query sysprocesses, I see CPU and physical I/O increase
while it's sleeping...
SYSPROCESSES ServerName 134219768
**********************************
spid cpu physical_io
-- -- --
69 188 135499
69 79 135499
69 141 135499
69 172 135499
69 218 135499
69 187 135499
69 171 135499
69 172 135499
Thanks!|||Hi Batman
There is no value 'active'.
Take a look at the code for sp_who2. You'll see that if you specify
'active', it eliminates spids where status='sleeping' and command is one of
a small list of values, including 'AWAITING COMMAND', and there is nothing
blocking. So a spid can be considered active if it is currently waiting for
some resource. What does the COMMAND column report?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:073AFE2C-C957-4978-8AFF-420C5C7A6D86@.microsoft.com...
> OK... I'm getting confused...
> I'm executing:
> exec sp_who2 'active'
> And getting the below results:
> SPID Status Login
> -- -- --
> 198 sleeping Domain\User
> ...
> ...
> With that said, can someone please refresh my memory about sleeping SPID's
> status? How can it be active but sleeping?
> Thanks,
> Michael|||The COMMAND column says "SELECT"...
Not to mention, we use the sp_blocker_pss80 sproc and is shows the same
status and command.
Again it shows sleeping but the cumulative CPU and I/O grew over a minute
and then disapeared...|||A process can sleep occasionally, but still be an active process, and use
system resources.
Perhaps what you mean by 'active' and what sp_who2 means by active are not
the same thing.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Batman" <Batman@.discussions.microsoft.com> wrote in message
news:B63A566F-E9B6-4754-90EB-E2B2DD17D938@.microsoft.com...
> The COMMAND column says "SELECT"...
> Not to mention, we use the sp_blocker_pss80 sproc and is shows the same
> status and command.
> Again it shows sleeping but the cumulative CPU and I/O grew over a minute
> and then disapeared...
>
>
>

Exec procedures inside other procedures

This should be pretty simple but I am not getting an luck getting what I need by googling.

I am executing a few stored procedures inside of another stored procedure. One of stored procedures returns a value that the other 2 procedures will use how do I get that get and use that variable?

Example

ALTERPROCEDURE [dbo].[ins_mainproc_sp]

AS

(

parameters

)

BEGIN

this procedure returns an ID

exec ins_procedure1_sp 'Value', 'Value'

these 2 procedures will use the value returned by the above procedure.

exec ins_procedure2_sp id,'Value'

exec ins_procedure3_sp id,'Value'

END

I was going to break this into 2 different procedures. Run ins_procedure1_sp get the value then pass it into ins_mainproc_sp but I was hoping to not make 2 calls.

You can use the OUTPUT parameter here.

Code Snippet

ALTER PROCEDURE [dbo].[ins_mainproc_sp]

AS

(

parameters

)

Begin

Declare @.Id as int

exec ins_procedure1_sp 'Value', 'Value', @.Id OUTPUT

..

...

....

--Reuse the @.Id on the SP calls

End

Go

Alter Proc ins_procedure1_sp

(

other parameters,

@.Id int OUTPUT

)

as

Begin

Insert ......

Set @.Id = <inserted Value>;

End

EXEC Permission

Hi,
I got this 'EXECUTE permission denied on object <mySproc>' error message
everytime I try executing my SQL server Sproc.
What's this and how to fix this err?
many thnaks in advance,
mikeMike
You are not probably an owner of the stored procedure.
So, DBA should grant you 'EXECUTE' permissions on the object
Also ,If you use dynamic sql within the stored procedure it will be forced
you to grant permissions on underlying tables
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ONJfv8pBFHA.1004@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I got this 'EXECUTE permission denied on object <mySproc>' error message
> everytime I try executing my SQL server Sproc.
> What's this and how to fix this err?
> many thnaks in advance,
> mike
>
>|||Thanks Uri,
Actually, I got this prob after I moved my ASP.NET web appl to a new
hosting.
And I never such prob with my old hosting.
How can I grant any permissions in such hosted environment?
rgds,
mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8wyEHrBFHA.4004@.tk2msftngp13.phx.gbl...
> Mike
> You are not probably an owner of the stored procedure.
> So, DBA should grant you 'EXECUTE' permissions on the object
> Also ,If you use dynamic sql within the stored procedure it will be forced
> you to grant permissions on underlying tables
>
> "Mike L." <globalwebservices2005@.yahoo.com> wrote in message
> news:ONJfv8pBFHA.1004@.TK2MSFTNGP15.phx.gbl...
>|||One more thing,
Everytime I log in to my SQL server using my login name/pwd, i've never get
my default DB as the first db in QA db list. It's always 'master' db.
I found out also, that my default db for my login name is not assigend to my
default db, it's to 'master' db instead.
Could this be a 'sign' that I really don't have enough permissions on my own
db?
rgds,
mike|||Hello Mike L.,
It's starting to sound like you should contact your hosting provider about
the way that your database is set up. From the sound of it it looks like
your login doesn't have permission to your database.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've
> never get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend
> to my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on
> my own
> db?
> rgds,
> mike|||You can change your default database using sp_defaultdb:
EXEC sp_defaultdb 'MyLogin', 'MyDatabase'

> Could this be a 'sign' that I really don't have enough permissions on my
> own
> db?
The default database is used when you haven't specified an initial database
context in your connection string. Since you got far enough to get a
permission error, this indicates that your login has access to your database
but insufficient permissions to execute the proc.
Your hosting provider needs to either:
1) Provide a separate administrative login that you can use to manage your
database and permissions
2) Add your login to a privileged role like db_owner
3) Perform database administration on your behalf. Was this how the
database was created?
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ORub5jsBFHA.1396@.tk2msftngp13.phx.gbl...
> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've never
> get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend to
> my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on my
> own
> db?
> rgds,
> mike
>|||Thanks Aaron, Dan,
I'll try to contact my hosting provider to fix this up.
rgds,
mike
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ORub5jsBFHA.1396@.tk2msftngp13.phx.gbl...
> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've never
get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend to
my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on my
own
> db?
> rgds,
> mike
>sql

Exception : Create Database failed 'tmpDb'

Hi

I'm getting Create Database failed 'tmpdb'

when I'm executing fallowing code

ServerConnection scon = new ServerConnection("local", "sa", "");

Server GSer=new Server(scon);

Database db2 = new Database(GSer, "xyz");

GSer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;

db2.Create();

Can Anybody help me in this?

Thanks in advance

hi,

there should be an inner exception stating what the actual problem is. if you can′t find any more information, try to use the capture mode instead of the execution mode and execute the script in the SSMS Query Window. You will get a mote detailed explanation in here.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks jens

can I create database object in C# code.

|||

Its not comming now with

GSer.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteSql;

getting "ExecuteNonQuery failed for Database 'xyz'.

error when I'm trying to execute scripts on db like

StreamReader sw=new StreamReader("C:\\script.sql");

string script = sw.ReadToEnd();

db2.ExecuteNonQuery(script);

What the error in this

|||

I am using SMO to create a database with this code

StreamReader sr = new StreamReader(@."C:\GestMed\Bases\Create_GestMed_Prod.sql");

string script = sr.ReadToEnd();

script = script.Replace("GestMed_Prod",nombase);

ComCr?erSite.Serveur.ConnectionContext.Connect();

try

{

ComCr?erSite.Serveur.Databases["master"].ExecuteNonQuery(script);

str = " Base " + nombase + " : cr?ation OK";

Ecrire_Journal(sw,str);

}

catch (Exception e)

{

str = " Erreur Cr?ate Base " + nombase;

Ecrire_Journal(sw,str);

str = " Message : " + e.Message;

sw.WriteLine(sw);

SuiviTxt.Text += str + nl;

if ( e.InnerException != null )

{

str = " InnerException : " + e.InnerException.Message;

sw.WriteLine(str);

sw.Flush();

SuiviTxt.Text += str + nl;

}

sw.Flush();

ComCr?erSite.Cr?? = false;

this.Refresh();

return false;;

}

ComCr?erSite.Serveur.Refresh();

That's working

Maybe you have forgotten to connect to the server

I am using a generic script where i'm able to choose the name of the database

GestMed_Prod ==> GestMedSite0Prod for example

I had some problems with SMO it's why i decided to build an installation textfile where i keep all messages Exception.Message and InnerException.Message

I hopthat it will be useful for you and excuse me for my poor english

Have a good day

|||Hi,

sorry for not keeping up quicker. You should use the capture mode to see the commands which are generated by the SMO framework:

SqlExecutionModes.CaptureSql

After catching the string[] of command you can see which commands are causing the errors.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I guess you are trying to create a database on local server.

If that is the case please use (local) or . instead of local (notice paranthesis).

Ex:Microsoft.SqlServer.Management.Common.ServerConnection scon = new Microsoft.SqlServer.Management.Common.ServerConnection("(local)", "sa", "");

I executed your code with just this change and it worked fine for me. Hope this helps.

Thanks,

Sravanthi.

|||

using the above code

i write dingdong.sql which is following

create database dingdong
go

use [dingdong]
go

create table test(name varchar(20));
go

create table test1(father varchar(21));
go

database created but table did not created, what is the reason

Thanks

|||

Sorry my problem solved,by using following code

StreamReader sr = new StreamReader(@."C:\jehan.sql");

string script = sr.ReadToEnd();

textBox1.Text = script;

Microsoft.SqlServer.Management.Smo.Server srvSql;

Microsoft.SqlServer.Management.Common.ServerConnection srvConn = new Microsoft.SqlServer.Management.Common.ServerConnection

(Global.ConnectionToDatabase1());

srvSql = new Server(srvConn);

srvSql.Databases["master"].ExecuteNonQuery(script);

Global.con1.Close();