Getting a .sql to execute another .sql

I would like to create a "parent" .sql file that when executed goes off and executes the contents a number of other "children" .sql files
Would anybody know how to do this
What happens if one of the .sql files fails? Will the others keep on running
ThankDepending on what you are trying to do, you might be able to use Xp_cmdshell
with OSQL. See BOL for details on both
--
Ray Higdon MCSE, MCDBA, CCNA
--
"lk1" <anonymous@.discussions.microsoft.com> wrote in message
news:A2CBAB73-B4EC-4852-A6D9-5DCE970A7282@.microsoft.com...
> I would like to create a "parent" .sql file that when executed goes off
and executes the contents a number of other "children" .sql files.
> Would anybody know how to do this?
> What happens if one of the .sql files fails? Will the others keep on
running?
> Thanks
>

Getting a .sql to execute another .sql

I would like to create a "parent" .sql file that when executed goes off and
executes the contents a number of other "children" .sql files.
Would anybody know how to do this?
What happens if one of the .sql files fails? Will the others keep on runnin
g?
ThanksDepending on what you are trying to do, you might be able to use Xp_cmdshell
with OSQL. See BOL for details on both
Ray Higdon MCSE, MCDBA, CCNA
--
"lk1" <anonymous@.discussions.microsoft.com> wrote in message
news:A2CBAB73-B4EC-4852-A6D9-5DCE970A7282@.microsoft.com...
> I would like to create a "parent" .sql file that when executed goes off
and executes the contents a number of other "children" .sql files.
> Would anybody know how to do this?
> What happens if one of the .sql files fails? Will the others keep on
running?
> Thanks
>

Getting "Object reference not set to an instance of an object" in Execute SQL Task

When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:

"Object reference not set to an instance of an object"

Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:

1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:

createprocusp_testsp

as

begin

select'whatever';

end

2. I create a new SSIS package in BIS.

3. I create an ADO.NET connection to the above SQL 2005 database

4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.

5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None

When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.

And here's what I get with ResultSet set to Single row:

Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.

Thanks,

Michael

Hi Michael,

I just saw the same error with "Parse Query" on this task, so I've logged an internal defect for the issue.

As far as actually being able to execute this stored procedure through the Execute SQL Task, try setting IsQueryStoredProcedure to True, your ResultSet to None, and set your SQLStatement to simply "usp_testsp", not "exec usp_testsp". I think this might resolve your problem with execution/debugging.

If you needed the results back from this stored procedure, you'd change ResultSet to SingleRow, and also add a result set binding in the "Result Set" page of the Execute SQL Task editor.

-David

|||

David,

Thanks for the followup.

I tried all of the above and still get the same error. If I switch to OLE DB it parses fine. Since it only happens on this machine, I think it might be a problem with the installation...though I only started having this problem in the last 3 days.

-Michael

|||

Additionally, I'm having trouble (but only yesterday) with doing estimated explain plans in managment studio. I don't have the problem today, though. And I still have the parsing problem with BIS. Here's the other problem:

When I try and do a Display Estimated Query Plan in SQL Server Management Studio on a SQL 2005 database, I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.

Cannot execute a program. The command being executed was "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe" /noconfig /fullpaths @."C:\Documents and Settings\xxxxxxx\Local Settings\Temp\zcxrpqb0.cmdline".

Not sure if they're related.

Thanks,

Michael

Getting "Object reference not set to an instance of an object" in Execute SQL Task

When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:

"Object reference not set to an instance of an object"

Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:

1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:

createprocusp_testsp

as

begin

select'whatever';

end

2. I create a new SSIS package in BIS.

3. I create an ADO.NET connection to the above SQL 2005 database

4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.

5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None

When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.

And here's what I get with ResultSet set to Single row:

Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.

Thanks,

Michael

Hi Michael,

I just saw the same error with "Parse Query" on this task, so I've logged an internal defect for the issue.

As far as actually being able to execute this stored procedure through the Execute SQL Task, try setting IsQueryStoredProcedure to True, your ResultSet to None, and set your SQLStatement to simply "usp_testsp", not "exec usp_testsp". I think this might resolve your problem with execution/debugging.

If you needed the results back from this stored procedure, you'd change ResultSet to SingleRow, and also add a result set binding in the "Result Set" page of the Execute SQL Task editor.

-David

|||

David,

Thanks for the followup.

I tried all of the above and still get the same error. If I switch to OLE DB it parses fine. Since it only happens on this machine, I think it might be a problem with the installation...though I only started having this problem in the last 3 days.

-Michael

|||

Additionally, I'm having trouble (but only yesterday) with doing estimated explain plans in managment studio. I don't have the problem today, though. And I still have the parsing problem with BIS. Here's the other problem:

When I try and do a Display Estimated Query Plan in SQL Server Management Studio on a SQL 2005 database, I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.

Cannot execute a program. The command being executed was "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe" /noconfig /fullpaths @."C:\Documents and Settings\xxxxxxx\Local Settings\Temp\zcxrpqb0.cmdline".

Not sure if they're related.

Thanks,

Michael

sql

Get the variable from Execute Process Task to C#

Hi!

I need help with some C# code. I have build a SSIS package with an Execute Process Task. I need to send dynamic variables in to my C# program so I thought it was a good idea to use the StandardInputVariable.

How do I get the variable in my C# code?
Thanks

CarlYour Main methods parameter collection?|||

Peter K wrote:

Your Main methods parameter collection?

yes. provided that this functionality has been built into the c# code.|||Thanks for your help.

I tried to get the variable through the main method but it dont work, the only thing I got was the argument.
My test code:
static void Main(string[] args)
{

for (int i=0; i<args.Length; i++)
{
Console.WriteLine(args);
Console.ReadLine();
}

Carl|||

ctsand wrote:

Thanks for your help.

I tried to get the variable through the main method but it dont work, the only thing I got was the argument.
My test code:
static void Main(string[] args)
{

for (int i=0; i<args.Length; i++)
{
Console.WriteLine(args);
Console.ReadLine();
}

Carl

did you include the variable as an argument to your c# executable in the execute process task?|||I have a static argument and a variable in StandardInputVarable. I put a value in the variable for testing but it will be dynamic.|||

ctsand wrote:

I have a static argument and a variable in StandardInputVarable. I put a value in the variable for testing but it will be dynamic.

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?|||

My intention was have to have the argument to jump to a special method in the code. The variable will have information about witch rows in the table the code shall read in and treat.

In any case, can I put a dynamic variable in the argument?

Carl

|||

the code below is how to execute package in c# code.the red code tell you how to dymamic edit variable.i think this method can get variable.but i didn't try.please try it

//add reference "Microsoft.SqlServer.ManagedDTS"(in microsoft.sqlserver.manageddts.dll)
Imports Microsoft.SqlServer.Dts.Runtime

Dim pkg As String = "package directory"

Dim app As Application = New Application()
Dim p As Pakage = app.LoadPackage( pkg, Nothing )
p.InteractiveMode = true
Dim pty As DtsProperty

'Dim n As Integer = p.Configurations.Count

Dim strPty As String
For Each pty In p.Properties
strPty = pty.Name & ":"
Try
If pty.Get Then strPty &= pty.GetValue( pty ).ToString()
Catch
End Try
Console.WriteLine( strPty )
Next

Dim vir As Variables = p.Variables
vir( "strFile" ).Value = "C:\MyApp2.txt"

Console.WriteLine( p.Execute( Noting, vir, Nothing, Nothing, Nothing ).ToString() )

|||

ctsand wrote:

My intention was have to have the argument to jump to a special method in the code. The variable will have information about witch rows in the table the code shall read in and treat.

understood

In any case, can I put a dynamic variable in the argument?

Carl

i don't know. did you try?|||

I tried it but it didnt worked. The only thing I got was the name of the variable.

You wrote earlier:

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?

What did you mean by that?

|||

ctsand wrote:

I tried it but it didnt worked. The only thing I got was the name of the variable.

You wrote earlier:

is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?

What did you mean by that?

what are the option settings in the process page of the execute process task editor?|||

RequireFullFileName = True

Executable = M:\Program\Person.exe

Arguments = fakt

WorkingDirectory = M:\Program

StandardInputVariable = User::test

StandardOutputVariable =

StandardErrorVariable =

FailTaskIfReturnCodeIsNotSuccessValue = True

SuccessValue = 0

TimeOut = 0

TerminateProcessAfterTimeOut = True

WindowStyle = Normal

|||what is the value and data type of User::test immediately before the execute process task starts executing? is the value of this variable correct?|||

The data type is string and the value is testing.

I have a question for you.

Is't meaning that I shall use the main-method to get the argument and the variable?

Carl

Get the column name of dynamical SQL?

I need to execute a lot of dynamical SQL (select only). These select
statement have different column number and name.
Any easy way to get the column names of a select statement string?How did your dynamic SQL get the column names in the first place?
"nick" <nick@.discussions.microsoft.com> wrote in message
news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>I need to execute a lot of dynamical SQL (select only). These select
> statement have different column number and name.
> Any easy way to get the column names of a select statement string?|||What I want to implement is:
A function with parameter of SQL statement string,
return the column names.
I am trying to avoid parsing the string. I guess SQL server may have some
internal stored procedure to get these information.
"Aaron Bertrand [SQL Server MVP]" wrote:

> How did your dynamic SQL get the column names in the first place?
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>
>|||not so easily. you could:
1. dump the result into a temp table and look up its definition -
e.g.
exec ('select top 0 * into mytmp from sysobjects')
select column_name
from information_schema.columns
where table_name='mytmp'
2. extract the stuff between "select" and "from".
-oj
"nick" <nick@.discussions.microsoft.com> wrote in message
news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>I need to execute a lot of dynamical SQL (select only). These select
> statement have different column number and name.
> Any easy way to get the column names of a select statement string?|||yes, both way are cumbersome.
or any easy way to get the number of columns?
"oj" wrote:

> not so easily. you could:
> 1. dump the result into a temp table and look up its definition -
> e.g.
> exec ('select top 0 * into mytmp from sysobjects')
> select column_name
> from information_schema.columns
> where table_name='mytmp'
> 2. extract the stuff between "select" and "from".
> --
> -oj
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:0FAAC6CF-0704-4CE8-A8DD-FCBD8E91A49A@.microsoft.com...
>
>|||nick (nick@.discussions.microsoft.com) writes:
> What I want to implement is:
> A function with parameter of SQL statement string,
> return the column names.
> I am trying to avoid parsing the string. I guess SQL server may have some
> internal stored procedure to get these information.
So what is your real business problem? This sort of thing is somewhat
easy to do from a client program, but not from within SQL itself. Which
is not so strange. This sort of information is not so interesting to
the server-side which delivers data. It is of course interesting on
the client-side, as the client needs to be able to investigate what data
it's getting from the server.
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|||if the columns are exploded (i.e. separated by a comma and no * used), you
can count the number of commas.
-oj
"nick" <nick@.discussions.microsoft.com> wrote in message
news:BBB38F4E-4C5D-4D54-BF58-9CAEF8F7966A@.microsoft.com...
> yes, both way are cumbersome.
> or any easy way to get the number of columns?
> "oj" wrote:
>

get records after executing a stored procedure

Hi All,

I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.

EXEC [dbo].[ETLloadGROUPS]
@.countRun =?,
@.startTime =?,
@.endTime = ?

This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.

When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.

Am I missing something here ? Please Advice

Thanks in Advance

I am sure it is a type issue. SSIS has a VERY VERY irritating feature of not telling you it can't convert your var to the SQL type you set in the parameters section, it just ignores it and sets it to nothing.

Try setting your vars to "String" types and your parameters in the task to "VARCHAR". I bet it will work.

You might also try setting vars inside the SQL to the ?. I have had issues where it doesn't like ? in certain places.

DECLARE @.count INT, @.stime datetime, @.etime datetime
SET @.count = ?
SET @.stime = ?
SET @.etime = ?

EXEC [dbo].[ETLloadGROUPS]
@.countRun =@.count,
@.startTime =@.stime,
@.endTime = @.etime|||

Tom,

Thanks for the quick response. but guess am into a soup here... I have done the following in the parameters tab of the Execute SQL Task.

varName Direction Datatype ParaName

user::countRun Input varchar 0

user:endDate input varchar 1

user:runDate Input varchar 2

In the result set , I have done the following,

Result Name variable Name

0 user::countRun

1 user:endDate

2 user:runDate

al the three variables are of the datatype String.

when I execute the package its now failing with the error, the type of the value assigned to the variable differs from the current datatype. I guess the values from the table which are int and date are not accepted in this parameter mapping. How to handle this?

Thanks for the help so far

|||It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

|||

When you say "no rows are fetched" how are you determining this?

what are you doing with the results of the sqltask?

|||

Hi Jeff,

My whole idea is to query a table, get three values onto three variables, pass these values to a stored procedure and then get the entire set of records returned by the proc and then insert it to another OLE DB Destination.

|||

Tom Phillips wrote:

It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

I am selecting all the three values from a table and then passing them to a stored procedure.

|||

Hi All,

I have solved all the issues, now at the last summit though its giving me an error with the OLE DB Source component where am calling the stored proc. The erorr is, A rowset based on the SQL Command was not returned by the OLE DB Provider.

Any idea to resolve this ?

Thanks in advance.

|||adding "SET NOCOUNT ON" at the start of the stored proc resolved the issue. Thx for all the help :)

Get Previous step result in an SSIS Script Task

Hello,

I am using SQL Server 2005 Integration Services to create new values for my tables. One step I must do is execute a query and the script task that receive its constraint (it is set to completion) must do different things depending on the query result.

My question is: how can I know the result of the precedence constraint?

Thank you,

Pablo Orte

If a task executes then it matched your precedence constraint. If you say "Complete" and "VarA == 20", and it executes then you can be assured that was the result. Are you wanting to do branching, or have multiple precedent constraints?|||

The key point to what I think Sean is suggesting, is that if you can get a variable to hold the result, you could use an expression to influence the constraint, whether it is satisfied or not. Expression support on a constraint is very useful for influencing workflow.

I am not sure that this is useful to you, since you have to populate that variable somehow. One way would be to use an on error event handler on the task. Default the variable to true, and change it to false in the event handler.

I don't know why or exactly what you are trying to do, but my first reaction would be this sounds like a bad idea. I think there should be a better, more SSIS way of trying to achieve this. Forgive me, but your method reminds me of the hacks we used in DTS. To conditionally do something why not use two tasks, or why not use event handlers to do an out of band type operation when something fails.

|||

This thread might have somthing for you...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=859001&SiteID=1

Rafael Salas

|||

Hi and thanks for your replies,

The problem I have is that I have to do this in a script task situated after an "Execute SQL". This cannot return any parameter and I need to have something in the code like this

IF objDTSPackage.Steps("DTSStep_DTSExecuteSQLTask_1").ExecutionResult = 0 THEN

This code worked in SQL Server 2000, but not in SQL Server 2005.

Thanks,

Pablo Orte