getting a set of values from xml

i have imported xml into an xml datatype variable. here is a tiny version of my xml file.

<Root>

<TOP>

<USERS>

<USER>

<USER>

<USERNAME>jukkaw</USERNAME>

</USER>

<USER>

<USERNAME>v-derekn</USERNAME>

</USER>

</USERS>

</TOP>

</Root>'

I need to pullout just the username, so the query method is out as it will return it in xml format. how do i just get a column containing all of the usernames?

You could use nodes table-value function:

create table #xml_table

(

xml_col xml

)

go

insert into #xml_table values('<Root>

<TOP>

<USERS>

<USER>

<USERNAME>jukkaw</USERNAME>

</USER>

<USER>

<USERNAME>v-derekn</USERNAME>

</USER>

</USERS>

</TOP>

</Root>

')

select x.value('.[1]','varchar(100)')

from #xml_table t cross apply xml_col.nodes('/Root/TOP/USERS/USER/USERNAME/text()') as tab(x)

Getting - Setting Environment Variables

Hi,

1st. Issue

I have written a package that uses an environmet variable which contains certain information that the user must enter. A sql script task will then get the information from the environment variable and manipulate it.

I don't have a problem programmatically getting information from the environment variable. However, when I manually edit the contents of the environment variable, the changes won't take place unless the box is rebooted. Is there a way to refresh environment variables without rebooting?

2nd. Issue

I have written another package that also reads data from an environment variable but it must also manipulate the data and save the changes to the environment variable.

I have accomplished this programmatically by using GetEnvironmentVariable() and SetEnvironmentVariable(), but unfortunately the changes live during program execution only. Afte the program executes, the changes are wiped out.

How can I make changes stick to environment variables (programmatically)?

-- I found a solution to this:

When programmatically getting and/or setting an environment variable the 'target' parameter must be specified in the method call. By 'target' I mean the location in the registry where your environment variable is stored.

GetEnvironmentVariable( var, target )

SetEnvironmentVariable( OldValue, NewValue, target )

Please visit this link for more info:

http://msdn2.microsoft.com/en-us/library/96xafkes.aspx

Get Value of Parameter whos name is in a variable

If I have a varialbe that contains the name of a Parameter in my stored
procedure is it possible to get the value of that parameter
Example:
CREATE PROCEDURE test
@.myParam1 varchar(50)
AS
DECLARE @.ParamName varchar(50)
@.ParamName = '@.myParam1'
EXEC ('SELECT ' + @.ParamName)
the above code doesn't work but is there a way to do this?Don't enclose variables in quotes. Also, you need to use SET to assign a
value.
SET @.ParamName = @.myParam1
Select @.ParamName
will give you the result for debugging purposes.
If you plan on using dynamic SQL - i.e. EXEC(SQLStatement)
then check out Erland's article on it...
http://www.sommarskog.se/dynamic_sql.html
<regmellon@.gmail.com> wrote in message
news:1148326918.904046.55660@.g10g2000cwb.googlegroups.com...
> If I have a varialbe that contains the name of a Parameter in my stored
> procedure is it possible to get the value of that parameter
> Example:
> CREATE PROCEDURE test
> @.myParam1 varchar(50)
> AS
> DECLARE @.ParamName varchar(50)
> @.ParamName = '@.myParam1'
> EXEC ('SELECT ' + @.ParamName)
>
> the above code doesn't work but is there a way to do this?
>|||well that was a simplified example of what I am trying to do I am
actually looping through a record set of all the parameters of a stored
proc and then trying to store the values that were passed into the
stored proc in a table to make debuging easyer. So the parameter name
is stored in @.ParamName not the value of that parameter.
A better example would be :
CREATE PROCEDURE test
@.myParam1 varchar(50) ,
@.myParam2 varchar(50)
AS
DECLARE @.ParamName varchar(50)
@.ParamName = '@.myParam' + cast(1, varchar(10))
EXEC ('SELECT ' + @.ParamName)
@.ParamName = '@.myParam' + cast(2, varchar(10))
EXEC ('SELECT ' + @.ParamName)|||well that was a simplified example of what I am trying to do I am
actually looping through a record set of all the parameters of a stored
proc and then trying to store the values that were passed into the
stored proc in a table to make debuging easyer. So the parameter name
is stored in @.ParamName not the value of that parameter.
A better example would be :
CREATE PROCEDURE test
@.myParam1 varchar(50) ,
@.myParam2 varchar(50)
AS
DECLARE @.ParamName varchar(50)
@.ParamName = '@.myParam' + cast(1, varchar(10))
EXEC ('SELECT ' + @.ParamName)
@.ParamName = '@.myParam' + cast(2, varchar(10))
EXEC ('SELECT ' + @.ParamName)|||TRY THIS,IT WILL SURELY WORK and can u plz specify wat type of parameter.Plz
give an example.
CREATE PROCEDURE test
@.myParam1 varchar(50)
AS
DECLARE @.ParamName varchar(50)
SET @.ParamName = @.myParam1
EXEC ('SELECT ' + @.ParamName)
--
MEHAK
"regmellon@.gmail.com" wrote:

> If I have a varialbe that contains the name of a Parameter in my stored
> procedure is it possible to get the value of that parameter
> Example:
> CREATE PROCEDURE test
> @.myParam1 varchar(50)
> AS
> DECLARE @.ParamName varchar(50)
> @.ParamName = '@.myParam1'
> EXEC ('SELECT ' + @.ParamName)
>
> the above code doesn't work but is there a way to do this?
>

Get value of MAX(ID) into a variable

Hi All,

Hope someone can help a newbie!

I have the following code:

/* Get MAXID from tblHotels_Web to form the HotelID */SqlCommand cmdGetMaxID =new SqlCommand("Select MAX(HotelID) from tblHotels_Web");int intMaxID;

How do I get the value of HotelID into my intMaxID variable and populate txtID.Text?

Regards,

Brett

see thia example:

static public int AddProductCategory(string newName, string connString){ Int32 newProdID = 0; string sql = "Select MAX(HotelID) from tblHotels_Web"; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = new SqlCommand(sql, conn); try { conn.Open(); newProdID = (Int32)cmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } return (int)newProdID;}

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 Display value of a variable drop-down to display on report

I have a report where the drop-down list is presented to the User at runtime.
This drop-down list displays the User First and Last Name as the option of
the drop-down. But, the value of their selection is set to the EmployeeID.
How do I get the select Full Name to appear on the report for the selected
name from the variable drop-down list?
thanks,
SeanHi Sean.
You need to return the full name in your result set or pass it in as another
parameter.
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:00CC804C-31A7-4740-A670-C605BF162A05@.microsoft.com...
> I have a report where the drop-down list is presented to the User at
runtime.
> This drop-down list displays the User First and Last Name as the option of
> the drop-down. But, the value of their selection is set to the
EmployeeID.
> How do I get the select Full Name to appear on the report for the selected
> name from the variable drop-down list?
> thanks,
> Sean
>

Get script filename within SQL script

Hi,
SQL Server 2000. How do I get the filename of the script currently being
executed from within the .SQL script itself?
Is there a special variable containing the filename of the .SQL script, or
another method of obtaining it?
thanks
Hi
I am not quite sure what you are wanting, but the filename is really only
known by the application and AFAIK will not get passed to the server. If you
are running the command from a command line or batch file you could use
command prompt variables to substitute into a script something that is sent
to the server.
If you expand and post some example of what you are trying to do it may help!
John
"JJ Williams" wrote:

> Hi,
> SQL Server 2000. How do I get the filename of the script currently being
> executed from within the .SQL script itself?
> Is there a special variable containing the filename of the .SQL script, or
> another method of obtaining it?
> thanks
>
>
|||Hi, thanks for your reply. Here's a simple example. test.sql contains:
print 'Script: test.sql'
select @.@.version
Execute it using osql:

> osql -U sa -i test.sql
Instead of hardcoding the script filename within the script, I want to get
the script filename programmatically within the script itself, e.g. via SQL
statement/command or otherwise. I've got dozens of .sql scripts and want a
general method to output the filename of the script (it doesn't matter if
the filename printed includes the whole folder path or not) and would rather
not hardcode the print statement in each script.
Hope that clarifies what I want to do.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not quite sure what you are wanting, but the filename is really only
> known by the application and AFAIK will not get passed to the server. If
> you
> are running the command from a command line or batch file you could use
> command prompt variables to substitute into a script something that is
> sent
> to the server.
> If you expand and post some example of what you are trying to do it may
> help!
> John
> "JJ Williams" wrote:
|||The origin of a batch is beyond the knowledge of the sql server engine that
executes it (which is where tsql code is interpreted and converted into
executable statements). This functionality would have to be something
implemented within the client application (osql in your example). The
execution of sql scripts isn't that difficult a task - you could write your
own application to do this - or perhaps wrap an application around osql -
you didn't indicate how you wanted to use this information.
|||Hi
If you had two files script.sql and sript1.sql you could do something like:
script.sql
PRINT 'Script Script1.sql'
:r Script1.sql
script1.sql
SELECT @.@.VERSION
Then run script.sql
osql -E -S (local) -d master -n -i script.sql -o script.out
If you want to do this for multiple files you could create the file and then
run it something like:
del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
script.sqf > script.out
If you have a large number of scripts it may be useful to limit how many are
run at any one given time.
John
"JJ Williams" wrote:

> Hi, thanks for your reply. Here's a simple example. test.sql contains:
> print 'Script: test.sql'
> select @.@.version
>
> Execute it using osql:
>
> Instead of hardcoding the script filename within the script, I want to get
> the script filename programmatically within the script itself, e.g. via SQL
> statement/command or otherwise. I've got dozens of .sql scripts and want a
> general method to output the filename of the script (it doesn't matter if
> the filename printed includes the whole folder path or not) and would rather
> not hardcode the print statement in each script.
> Hope that clarifies what I want to do.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
>
>
|||Hi
If you don't want to use the command prompt you may want to look at DMO to
do this.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If you had two files script.sql and sript1.sql you could do something like:
> script.sql
> PRINT 'Script Script1.sql'
> :r Script1.sql
> script1.sql
> SELECT @.@.VERSION
> Then run script.sql
> osql -E -S (local) -d master -n -i script.sql -o script.out
> If you want to do this for multiple files you could create the file and then
> run it something like:
> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
> script.sqf > script.out
> If you have a large number of scripts it may be useful to limit how many are
> run at any one given time.
> John
>
> "JJ Williams" wrote:
|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:OrWEkAc$GHA.4808@.TK2MSFTNGP03.phx.gbl...
> The origin of a batch is beyond the knowledge of the sql server engine
> that executes it (which is where tsql code is interpreted and converted
> into executable statements). This functionality would have to be
> something implemented within the client application (osql in your
> example). The execution of sql scripts isn't that difficult a task - you
> could write your own application to do this - or perhaps wrap an
> application around osql - you didn't indicate how you wanted to use this
> information.

> you didn't indicate how you wanted to use this information.
I just want to log the filename along with the script contents and results
to an output file as the script executes. I have multiple scripts running
in sequence from separate osql command lines, all outputting to the same
file and I want to be able to see within the file which script did which
bit.
I think I'll stick with the simple hardcoded method.
thanks,
|||OK thanks again for your reply.
I'll stick with hardcoding the filename in a PRINT statement within each
script.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2A3737FA-C8B3-44DD-94EA-D0AFA16E02AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't want to use the command prompt you may want to look at DMO to
> do this.
> John
> "John Bell" wrote:

Get script filename within SQL script

Hi,
SQL Server 2000. How do I get the filename of the script currently being
executed from within the .SQL script itself?
Is there a special variable containing the filename of the .SQL script, or
another method of obtaining it?
thanksHi
I am not quite sure what you are wanting, but the filename is really only
known by the application and AFAIK will not get passed to the server. If you
are running the command from a command line or batch file you could use
command prompt variables to substitute into a script something that is sent
to the server.
If you expand and post some example of what you are trying to do it may help!
John
"JJ Williams" wrote:
> Hi,
> SQL Server 2000. How do I get the filename of the script currently being
> executed from within the .SQL script itself?
> Is there a special variable containing the filename of the .SQL script, or
> another method of obtaining it?
> thanks
>
>|||Hi, thanks for your reply. Here's a simple example. test.sql contains:
print 'Script: test.sql'
select @.@.version
Execute it using osql:
> osql -U sa -i test.sql
Instead of hardcoding the script filename within the script, I want to get
the script filename programmatically within the script itself, e.g. via SQL
statement/command or otherwise. I've got dozens of .sql scripts and want a
general method to output the filename of the script (it doesn't matter if
the filename printed includes the whole folder path or not) and would rather
not hardcode the print statement in each script.
Hope that clarifies what I want to do.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
> Hi
> I am not quite sure what you are wanting, but the filename is really only
> known by the application and AFAIK will not get passed to the server. If
> you
> are running the command from a command line or batch file you could use
> command prompt variables to substitute into a script something that is
> sent
> to the server.
> If you expand and post some example of what you are trying to do it may
> help!
> John
> "JJ Williams" wrote:
>> Hi,
>> SQL Server 2000. How do I get the filename of the script currently being
>> executed from within the .SQL script itself?
>> Is there a special variable containing the filename of the .SQL script,
>> or
>> another method of obtaining it?
>> thanks
>>|||The origin of a batch is beyond the knowledge of the sql server engine that
executes it (which is where tsql code is interpreted and converted into
executable statements). This functionality would have to be something
implemented within the client application (osql in your example). The
execution of sql scripts isn't that difficult a task - you could write your
own application to do this - or perhaps wrap an application around osql -
you didn't indicate how you wanted to use this information.|||Hi
If you had two files script.sql and sript1.sql you could do something like:
script.sql
PRINT 'Script Script1.sql'
:r Script1.sql
script1.sql
SELECT @.@.VERSION
Then run script.sql
osql -E -S (local) -d master -n -i script.sql -o script.out
If you want to do this for multiple files you could create the file and then
run it something like:
del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
script.sqf > script.out
If you have a large number of scripts it may be useful to limit how many are
run at any one given time.
John
"JJ Williams" wrote:
> Hi, thanks for your reply. Here's a simple example. test.sql contains:
> print 'Script: test.sql'
> select @.@.version
>
> Execute it using osql:
> > osql -U sa -i test.sql
>
> Instead of hardcoding the script filename within the script, I want to get
> the script filename programmatically within the script itself, e.g. via SQL
> statement/command or otherwise. I've got dozens of .sql scripts and want a
> general method to output the filename of the script (it doesn't matter if
> the filename printed includes the whole folder path or not) and would rather
> not hardcode the print statement in each script.
> Hope that clarifies what I want to do.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
> > Hi
> >
> > I am not quite sure what you are wanting, but the filename is really only
> > known by the application and AFAIK will not get passed to the server. If
> > you
> > are running the command from a command line or batch file you could use
> > command prompt variables to substitute into a script something that is
> > sent
> > to the server.
> >
> > If you expand and post some example of what you are trying to do it may
> > help!
> >
> > John
> >
> > "JJ Williams" wrote:
> >
> >> Hi,
> >>
> >> SQL Server 2000. How do I get the filename of the script currently being
> >> executed from within the .SQL script itself?
> >>
> >> Is there a special variable containing the filename of the .SQL script,
> >> or
> >> another method of obtaining it?
> >>
> >> thanks
> >>
> >>
> >>
>
>|||Hi
If you don't want to use the command prompt you may want to look at DMO to
do this.
John
"John Bell" wrote:
> Hi
> If you had two files script.sql and sript1.sql you could do something like:
> script.sql
> PRINT 'Script Script1.sql'
> :r Script1.sql
> script1.sql
> SELECT @.@.VERSION
> Then run script.sql
> osql -E -S (local) -d master -n -i script.sql -o script.out
> If you want to do this for multiple files you could create the file and then
> run it something like:
> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
> script.sqf > script.out
> If you have a large number of scripts it may be useful to limit how many are
> run at any one given time.
> John
>
> "JJ Williams" wrote:
> > Hi, thanks for your reply. Here's a simple example. test.sql contains:
> >
> > print 'Script: test.sql'
> > select @.@.version
> >
> >
> > Execute it using osql:
> >
> > > osql -U sa -i test.sql
> >
> >
> > Instead of hardcoding the script filename within the script, I want to get
> > the script filename programmatically within the script itself, e.g. via SQL
> > statement/command or otherwise. I've got dozens of .sql scripts and want a
> > general method to output the filename of the script (it doesn't matter if
> > the filename printed includes the whole folder path or not) and would rather
> > not hardcode the print statement in each script.
> >
> > Hope that clarifies what I want to do.
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
> > > Hi
> > >
> > > I am not quite sure what you are wanting, but the filename is really only
> > > known by the application and AFAIK will not get passed to the server. If
> > > you
> > > are running the command from a command line or batch file you could use
> > > command prompt variables to substitute into a script something that is
> > > sent
> > > to the server.
> > >
> > > If you expand and post some example of what you are trying to do it may
> > > help!
> > >
> > > John
> > >
> > > "JJ Williams" wrote:
> > >
> > >> Hi,
> > >>
> > >> SQL Server 2000. How do I get the filename of the script currently being
> > >> executed from within the .SQL script itself?
> > >>
> > >> Is there a special variable containing the filename of the .SQL script,
> > >> or
> > >> another method of obtaining it?
> > >>
> > >> thanks
> > >>
> > >>
> > >>
> >
> >
> >|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:OrWEkAc$GHA.4808@.TK2MSFTNGP03.phx.gbl...
> The origin of a batch is beyond the knowledge of the sql server engine
> that executes it (which is where tsql code is interpreted and converted
> into executable statements). This functionality would have to be
> something implemented within the client application (osql in your
> example). The execution of sql scripts isn't that difficult a task - you
> could write your own application to do this - or perhaps wrap an
> application around osql - you didn't indicate how you wanted to use this
> information.
> you didn't indicate how you wanted to use this information.
I just want to log the filename along with the script contents and results
to an output file as the script executes. I have multiple scripts running
in sequence from separate osql command lines, all outputting to the same
file and I want to be able to see within the file which script did which
bit.
I think I'll stick with the simple hardcoded method.
thanks,|||OK thanks again for your reply.
I'll stick with hardcoding the filename in a PRINT statement within each
script.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2A3737FA-C8B3-44DD-94EA-D0AFA16E02AC@.microsoft.com...
> Hi
> If you don't want to use the command prompt you may want to look at DMO to
> do this.
> John
> "John Bell" wrote:
>> Hi
>> If you had two files script.sql and sript1.sql you could do something
>> like:
>> script.sql
>> PRINT 'Script Script1.sql'
>> :r Script1.sql
>> script1.sql
>> SELECT @.@.VERSION
>> Then run script.sql
>> osql -E -S (local) -d master -n -i script.sql -o script.out
>> If you want to do this for multiple files you could create the file and
>> then
>> run it something like:
>> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i'
>> >>
>> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d
>> Master -n -i
>> script.sqf > script.out
>> If you have a large number of scripts it may be useful to limit how many
>> are
>> run at any one given time.
>> John
>>
>> "JJ Williams" wrote:
>> > Hi, thanks for your reply. Here's a simple example. test.sql
>> > contains:
>> >
>> > print 'Script: test.sql'
>> > select @.@.version
>> >
>> >
>> > Execute it using osql:
>> >
>> > > osql -U sa -i test.sql
>> >
>> >
>> > Instead of hardcoding the script filename within the script, I want to
>> > get
>> > the script filename programmatically within the script itself, e.g. via
>> > SQL
>> > statement/command or otherwise. I've got dozens of .sql scripts and
>> > want a
>> > general method to output the filename of the script (it doesn't matter
>> > if
>> > the filename printed includes the whole folder path or not) and would
>> > rather
>> > not hardcode the print statement in each script.
>> >
>> > Hope that clarifies what I want to do.
>> >
>> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> > news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
>> > > Hi
>> > >
>> > > I am not quite sure what you are wanting, but the filename is really
>> > > only
>> > > known by the application and AFAIK will not get passed to the server.
>> > > If
>> > > you
>> > > are running the command from a command line or batch file you could
>> > > use
>> > > command prompt variables to substitute into a script something that
>> > > is
>> > > sent
>> > > to the server.
>> > >
>> > > If you expand and post some example of what you are trying to do it
>> > > may
>> > > help!
>> > >
>> > > John
>> > >
>> > > "JJ Williams" wrote:
>> > >
>> > >> Hi,
>> > >>
>> > >> SQL Server 2000. How do I get the filename of the script currently
>> > >> being
>> > >> executed from within the .SQL script itself?
>> > >>
>> > >> Is there a special variable containing the filename of the .SQL
>> > >> script,
>> > >> or
>> > >> another method of obtaining it?
>> > >>
>> > >> thanks
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

Get script filename within SQL script

Hi,
SQL Server 2000. How do I get the filename of the script currently being
executed from within the .SQL script itself?
Is there a special variable containing the filename of the .SQL script, or
another method of obtaining it?
thanksHi
I am not quite sure what you are wanting, but the filename is really only
known by the application and AFAIK will not get passed to the server. If you
are running the command from a command line or batch file you could use
command prompt variables to substitute into a script something that is sent
to the server.
If you expand and post some example of what you are trying to do it may help
!
John
"JJ Williams" wrote:

> Hi,
> SQL Server 2000. How do I get the filename of the script currently being
> executed from within the .SQL script itself?
> Is there a special variable containing the filename of the .SQL script, or
> another method of obtaining it?
> thanks
>
>|||Hi, thanks for your reply. Here's a simple example. test.sql contains:
print 'Script: test.sql'
select @.@.version
Execute it using osql:

> osql -U sa -i test.sql
Instead of hardcoding the script filename within the script, I want to get
the script filename programmatically within the script itself, e.g. via SQL
statement/command or otherwise. I've got dozens of .sql scripts and want a
general method to output the filename of the script (it doesn't matter if
the filename printed includes the whole folder path or not) and would rather
not hardcode the print statement in each script.
Hope that clarifies what I want to do.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...[vbcol=seagreen]
> Hi
> I am not quite sure what you are wanting, but the filename is really only
> known by the application and AFAIK will not get passed to the server. If
> you
> are running the command from a command line or batch file you could use
> command prompt variables to substitute into a script something that is
> sent
> to the server.
> If you expand and post some example of what you are trying to do it may
> help!
> John
> "JJ Williams" wrote:
>|||The origin of a batch is beyond the knowledge of the sql server engine that
executes it (which is where tsql code is interpreted and converted into
executable statements). This functionality would have to be something
implemented within the client application (osql in your example). The
execution of sql scripts isn't that difficult a task - you could write your
own application to do this - or perhaps wrap an application around osql -
you didn't indicate how you wanted to use this information.|||Hi
If you had two files script.sql and sript1.sql you could do something like:
script.sql
PRINT 'Script Script1.sql'
:r Script1.sql
script1.sql
SELECT @.@.VERSION
Then run script.sql
osql -E -S (local) -d master -n -i script.sql -o script.out
If you want to do this for multiple files you could create the file and then
run it something like:
del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i' >>
script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n -i
script.sqf > script.out
If you have a large number of scripts it may be useful to limit how many are
run at any one given time.
John
"JJ Williams" wrote:

> Hi, thanks for your reply. Here's a simple example. test.sql contains:
> print 'Script: test.sql'
> select @.@.version
>
> Execute it using osql:
>
>
> Instead of hardcoding the script filename within the script, I want to get
> the script filename programmatically within the script itself, e.g. via SQ
L
> statement/command or otherwise. I've got dozens of .sql scripts and want
a
> general method to output the filename of the script (it doesn't matter if
> the filename printed includes the whole folder path or not) and would rath
er
> not hardcode the print statement in each script.
> Hope that clarifies what I want to do.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:7D7CA363-E705-4180-A8C5-18563818251B@.microsoft.com...
>
>|||Hi
If you don't want to use the command prompt you may want to look at DMO to
do this.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If you had two files script.sql and sript1.sql you could do something like
:
> script.sql
> PRINT 'Script Script1.sql'
> :r Script1.sql
> script1.sql
> SELECT @.@.VERSION
> Then run script.sql
> osql -E -S (local) -d master -n -i script.sql -o script.out
> If you want to do this for multiple files you could create the file and th
en
> run it something like:
> del script.sqf script.out && (for %i in (*.sql) do echo PRINT 'Script %i'
>>
> script.sqf &&echo :r %i >> script.sqf) && osql -E -S (local) -d Master -n
-i
> script.sqf > script.out
> If you have a large number of scripts it may be useful to limit how many a
re
> run at any one given time.
> John
>
> "JJ Williams" wrote:
>|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:OrWEkAc$GHA.4808@.TK2MSFTNGP03.phx.gbl...
> The origin of a batch is beyond the knowledge of the sql server engine
> that executes it (which is where tsql code is interpreted and converted
> into executable statements). This functionality would have to be
> something implemented within the client application (osql in your
> example). The execution of sql scripts isn't that difficult a task - you
> could write your own application to do this - or perhaps wrap an
> application around osql - you didn't indicate how you wanted to use this
> information.

> you didn't indicate how you wanted to use this information.
I just want to log the filename along with the script contents and results
to an output file as the script executes. I have multiple scripts running
in sequence from separate osql command lines, all outputting to the same
file and I want to be able to see within the file which script did which
bit.
I think I'll stick with the simple hardcoded method.
thanks,|||OK thanks again for your reply.
I'll stick with hardcoding the filename in a PRINT statement within each
script.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:2A3737FA-C8B3-44DD-94EA-D0AFA16E02AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you don't want to use the command prompt you may want to look at DMO to
> do this.
> John
> "John Bell" wrote:
>

Get Return Value from Stored Procedure

Greetings All,
I am a newbie to SSIS and need some help. I need to get the return value from a stored procedure into a SSIS variable. I'm assuming I would use an OLE DB Command but I havn't a clue on how to capture the return value. Can someone get me started on how I can do this?

Note, the return value is actually an identity of the inserted value. I need this value in my data flow for further processing.

Thanks is advance!If you search this forum you'll find numerous examples and posts on this topic. You'll also want to use the OUTPUT keyword on the stored procedure call.|||I did search the forum and many of them seemed a bit abstract for a beginner. I was hoping someone could lend me the "cliff notes".

Otherwise, I'll keep diggin'.|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1001679&SiteID=1|||Thanks Phil.|||Assuming I get the return identity in the output column, how can I then get this value into a IS variable?

Thanks

Get Page Number not in page header and footer.

Hello,
Does anybode know how to determine current page number in report body?
There is Globals.PageNumber variable but it is accessible only in page
header and footer.
Thanks,
Paul.I have many pages report and I want to click on header column in table to
jump to the same page of the report. But for this I have to pass current
page number.
How can I do this?
"Paul Zorin" <Paul.Zorin@.bridge-quest.com> wrote in message
news:#TMkriAuEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Hello,
> Does anybode know how to determine current page number in report body?
> There is Globals.PageNumber variable but it is accessible only in page
> header and footer.
> Thanks,
> Paul.
>