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

GETDATE

I have a database that contains, amongst others, the following fields
DocNo, DocDate, DocAmt
I need to extract the data in the these fields, but only where the date (in
yyyy-mm-dd format) is equal to the current date. In other words, I need a
schedule of documents produced on the day of running the query.
I am battling with the WHERE statement. Can anyone help please?Try this:
WHERE CONVERT(DATETIME, CONVERT(CHAR, DocDate, 105), 103) =
CONVERT(DATETIME, CONVERT(CHAR, GETDATE, 105), 103)
This statement sets the time to 00:00:00 on both the sides.
Regards,
Peri
"Chris Lane" <chris.lane@.lantic.net> wrote in message
news:dl3uul$jfc$2@.ctb-nnrp2.saix.net...
> I have a database that contains, amongst others, the following fields
> DocNo, DocDate, DocAmt
> I need to extract the data in the these fields, but only where the date
(in
> yyyy-mm-dd format) is equal to the current date. In other words, I need a
> schedule of documents produced on the day of running the query.
> I am battling with the WHERE statement. Can anyone help please?
>
>|||Thanks Peri
"Peri" <Peri@.newsgroups.nospam> wrote in message
news:OaGOLy05FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Try this:
> WHERE CONVERT(DATETIME, CONVERT(CHAR, DocDate, 105), 103) =
> CONVERT(DATETIME, CONVERT(CHAR, GETDATE, 105), 103)
> This statement sets the time to 00:00:00 on both the sides.
> Regards,
> Peri
> "Chris Lane" <chris.lane@.lantic.net> wrote in message
> news:dl3uul$jfc$2@.ctb-nnrp2.saix.net...
> (in
>|||"Chris Lane" <chris.lane@.lantic.net> wrote in message
news:dl401b$lkt$1@.ctb-nnrp2.saix.net...
> Thanks Peri
> "Peri" <Peri@.newsgroups.nospam> wrote in message
> news:OaGOLy05FHA.3296@.TK2MSFTNGP09.phx.gbl...
>
Avoid putting the CONVERT on the column. Instead, an expression like the
following is more likely to make better use of any index on docdate.
...
WHERE docdate >= CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND docdate < CONVERT(CHAR(8),DATEADD(DAY,1,CURRENT_TI
MESTAMP),112) ;
David Portas
SQL Server MVP
--|||Thanks David
Most helpful
regards
Chris
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:kpednTVQCsA5X-jeRVnyhA@.giganews.com...
> "Chris Lane" <chris.lane@.lantic.net> wrote in message
> news:dl401b$lkt$1@.ctb-nnrp2.saix.net...
> Avoid putting the CONVERT on the column. Instead, an expression like the
> following is more likely to make better use of any index on docdate.
> ...
> WHERE docdate >= CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
> AND docdate < CONVERT(CHAR(8),DATEADD(DAY,1,CURRENT_TI
MESTAMP),112) ;
> --
> David Portas
> SQL Server MVP
> --
>

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 top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. Severson
I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID =
(SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:

>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson

Get top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. SeversonWith SQL 2005, you can try something like that :
with Ordering (JOB_ID, TASK_ID, RN) as
(select JOB_ID,
TASK_ID,
ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY PRIORITY)
from MyTable
)
select JOB_ID, TASK_ID
from Ordering
where RN=1
With SQL 2000, you should work with # tables or table variables. (with ORDER
BY ...)
JN.
"NerdRunner" <sseverson@.2sts.biz> a écrit dans le message de news:
1169075237.717157.102200@.s34g2000cwa.googlegroups.com...
> My task table contains the id of the task, the jobid that the task is
> associated with, and a numeric value representing the priority of the
> task. There can be multiple tasks for a job. I need a view that
> returns the task id, and job id of the task with the higest priority
> for each job. I tried grouping but this does not appear to work.
> Thanks for the help.
> Sean M. Severson
>|||I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID = (SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:
>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson|||Roy,
That did it. I was missing the JobID comparison. Thanks so much!!
Sean M. Severson
Roy Harvey wrote:
> I think this will do it.
> SELECT *
> FROM Tasks as A
> WHERE TaskID => (SELECT TOP 1 TaskID
> FROM Tasks as B
> WHERE A.JobID = B.JobID
> ORDER BY B.Priority DESC)
> Roy Harvey
> Beacon Falls, CT
> On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
> wrote:
> >My task table contains the id of the task, the jobid that the task is
> >associated with, and a numeric value representing the priority of the
> >task. There can be multiple tasks for a job. I need a view that
> >returns the task id, and job id of the task with the higest priority
> >for each job. I tried grouping but this does not appear to work.
> >
> >Thanks for the help.
> >
> >Sean M. Severson

Get top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. SeversonWith SQL 2005, you can try something like that :
with Ordering (JOB_ID, TASK_ID, RN) as
(select JOB_ID,
TASK_ID,
ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY PRIORITY)
from MyTable
)
select JOB_ID, TASK_ID
from Ordering
where RN=1
With SQL 2000, you should work with # tables or table variables. (with ORDER
BY ...)
JN.
"NerdRunner" <sseverson@.2sts.biz> a crit dans le message de news:
1169075237.717157.102200@.s34g2000cwa.googlegroups.com...
> My task table contains the id of the task, the jobid that the task is
> associated with, and a numeric value representing the priority of the
> task. There can be multiple tasks for a job. I need a view that
> returns the task id, and job id of the task with the higest priority
> for each job. I tried grouping but this does not appear to work.
> Thanks for the help.
> Sean M. Severson
>|||I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID =
(SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:

>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson|||Roy,
That did it. I was missing the JobID comparison. Thanks so much!!
Sean M. Severson
Roy Harvey wrote:[vbcol=seagreen]
> I think this will do it.
> SELECT *
> FROM Tasks as A
> WHERE TaskID =
> (SELECT TOP 1 TaskID
> FROM Tasks as B
> WHERE A.JobID = B.JobID
> ORDER BY B.Priority DESC)
> Roy Harvey
> Beacon Falls, CT
> On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
> wrote:
>

Get PK for inserted record in SQLdatasource

I have a table named invoice that contains the following columns

-invoiceno - Primary key and is set to autonumber
-customerno
-incoicedate

and on my VB code i did the following InsertCommand

SqlDataSource1.InsertCommand = "INSERT INTO invoice(customerno, invoicedate) VALUES('" & Session("UID") & "', GetDate()) "
SqlDataSource1.Insert()

My Question is how do i get the Primary Key Value it generated during the insert operation(invoice['incoiceno'])? Besides the creationg a stored procedere like the one in the MSDN Library

Well, since you really aren't using the sqldatasource as a datasource, just do it manually.

Dim conn as new sqlconnection("{Your connect string}")
conn.open
dim cmd as new sqlcommand("INSERT INTO invoice(customerno, invoicedate) VALUES(@.customerno,getdate()) SELECT @.newid=SCOPE_IDENTITY()",conn)
cmd.parameters.add("@.customerno",sqldbtype.uniqueidentifier).value=session("UID")
cmd.parameters.add("@.newid",sqldbtype.int).direction=output
cmd.executenonquery()
dim newid as integer=cmd.parameters("@.newid").value
conn.close

I believe you can also try to create a parameter on the insert with a direction of output as well, but I think you have to actually pull the value in sqldatasource1_inserted by referencing e.command.parameters("@.newid").value, but you can try and see if you can pull it directly after your insert too.