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

Hi
I have to create a series of reports that look back over certain days ie.
day before
current w
current month, quarter, year
The reports will be scheduled to be run between the hours of 10pm to 6am
when the business is shut.
I am using getdate - days to get my time span an example is
WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime,
CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
CONVERT(varchar(11), GETDATE() + 0, 102), 102))
this would get me my range for yesterdays results
However having run a query with the above where clause it pulled some out
for today, which make smy results wrong because it has used some of today, i
am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hours
giving me 2006-01-04 16:30:32 , this may not be a problem if the reports are
scheduled to run out of hours but is there a way of using the date and
adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
sure my reports were accurate, plus it would be nice to know
hope i made sense
regardsHave a look at the DATEDIFF function is SQL Books
HTH. Ryan
"Steven Scaife" <sp@.nospam.com> wrote in message
news:ey0ywdhEGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN
> CONVERT(datetime, CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND
> CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
> i am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24
> hours giving me 2006-01-04 16:30:32 , this may not be a problem if the
> reports are scheduled to run out of hours but is there a way of using the
> date and adding my own time constraint in eg 00:00:01 and 23:59:59 this
> would make sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>|||Hope that following syntax can help you. This will give yout todays date at
10:00 PM.
select convert(datetime,convert(char(8),getdate
(),112) + ' 22:00:00',120)
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||For yesterday's results, try
WHERE DATEDIFF(dd, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
For last w's results
WHERE DATEDIFF(wk, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
For last month's results
WHERE DATEDIFF(mm, dbo.vw_MIS_AppWritten.Date_App_Written, GETDATE()) = 1
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||Also you can use a calendar table|||There are several ways to remove the timestamp from getdate() and retaining
it as a datetime that I know of.
Here is what I typically do
1) SELECT CAST(CONVERT(VARCHAR(10), getdate(), 102) as DATETIME)
I would avoid the "adding your own time" and just use comparisons against
dates without times (ie. Midnight of that day).
For Instance, if i wanted all rows where dateFromTable is Today the WHERE
clause would be:
WHERE dateFromTable BETWEEN CAST(CONVERT(VARCHAR(10), getdate(), 102) as
DATETIME)
AND CAST(CONVERT(VARCHAR(10), dateadd(dd, 1, getdate()), 102) as DATETIME)
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Steven Scaife" wrote:

> Hi
> I have to create a series of reports that look back over certain days ie.
> day before
> current w
> current month, quarter, year
> The reports will be scheduled to be run between the hours of 10pm to 6am
> when the business is shut.
> I am using getdate - days to get my time span an example is
> WHERE (dbo.vw_MIS_AppWritten.Date_App_Written BETWEEN CONVERT(datetime
,
> CONVERT(varchar(11), GETDATE() - 1, 102), 102) AND CONVERT(datetime,
> CONVERT(varchar(11), GETDATE() + 0, 102), 102))
> this would get me my range for yesterdays results
> However having run a query with the above where clause it pulled some out
> for today, which make smy results wrong because it has used some of today,
i
> am assuming it used 2006-01-05 16:30:32 as the getdate and took off 24 hou
rs
> giving me 2006-01-04 16:30:32 , this may not be a problem if the reports a
re
> scheduled to run out of hours but is there a way of using the date and
> adding my own time constraint in eg 00:00:01 and 23:59:59 this would make
> sure my reports were accurate, plus it would be nice to know
> hope i made sense
> regards
>
>|||This works well too
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
Returns today's date at 00:00:00.000
"Ryan Powers" wrote:
> There are several ways to remove the timestamp from getdate() and retainin
g
> it as a datetime that I know of.
> Here is what I typically do
> 1) SELECT CAST(CONVERT(VARCHAR(10), getdate(), 102) as DATETIME)
> I would avoid the "adding your own time" and just use comparisons against
> dates without times (ie. Midnight of that day).
> For Instance, if i wanted all rows where dateFromTable is Today the WHERE
> clause would be:
> WHERE dateFromTable BETWEEN CAST(CONVERT(VARCHAR(10), getdate(), 102) as
> DATETIME)
> AND CAST(CONVERT(VARCHAR(10), dateadd(dd, 1, getdate()), 102) as DATETIME)
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Steven Scaife" wrote:
>|||Nice. Thanks Mark.
That is a little cleaner that what I proposed. Good to know.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Mark Williams" wrote:
> This works well too
> SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
> Returns today's date at 00:00:00.000
> --
> "Ryan Powers" wrote:
>|||thank you it is much appreciated
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:0DBCD236-D731-4201-B577-558AA3708FE3@.microsoft.com...
> This works well too
> SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
> Returns today's date at 00:00:00.000
> --
> "Ryan Powers" wrote:
>

get todays date and a certain time

Hi Everyone,

I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Thanks in advance,
AnnaYou can use DATEADD, for example:

SELECT DATEADD(hour,14,CONVERT(CHAR(10),getdate(),102))

Razvan

AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

Hi Everyone,
>
I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?
>
Thanks in advance,
Anna

|||On 27 Sep 2006 11:36:59 -0700, AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

>Hi Everyone,
>
>I am trying to write something to give me back all the data for a
>sertain time range for today.
>So for example: I need to get all records where change_date is <= today
>2pm and today at 8pm.
>I know i can get just the date for today by using
>CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Hi Anna,

Use either

CONVERT(datetime, CONVERT(CHAR(10), getdate(), 126) + 'T14:00:00')

or

DATEADD(day, DATEDIFF(day, 0, getdate()), '14:00:00')

to get current date with a time of 2PM.

--
Hugo Kornelis, SQL Server MVP

Get rows with duplicate values in certain columns

Hi there,

I would like to know how to get rows with duplicate values in certain
columns. Let's say I have a table called "Songs" with the following
columns:

artist
album
title
genre
track

Now I would like to show the duplicate songs to the user. I consider
songs that have the same artist and the same title to be the same song.
Note: All columns do not have to be the same.

How would I accomplish that with SQL in SQL Server?

Thanks to everyone reading this. I hope somebody has an answer. I've
already searched the whole newsgroups, but couldn't find the solution.<agekay@.gmx.de> wrote in message
news:1120392807.001826.295060@.g49g2000cwa.googlegr oups.com...
> Hi there,
> I would like to know how to get rows with duplicate values in certain
> columns. Let's say I have a table called "Songs" with the following
> columns:
> artist
> album
> title
> genre
> track
> Now I would like to show the duplicate songs to the user. I consider
> songs that have the same artist and the same title to be the same song.
> Note: All columns do not have to be the same.
> How would I accomplish that with SQL in SQL Server?
> Thanks to everyone reading this. I hope somebody has an answer. I've
> already searched the whole newsgroups, but couldn't find the solution.

You probably need something like this:

select
s.artist,
s.album,
s.title,
s.genre,
s.track
from
dbo.Songs s
join
(
select
artist,
title
from
dbo.Songs
group by
artist,
title
having count(*) > 1
) dt
on s.artist = dt.artist and
s.title = dt.title

If this doesn't give the results you expect, then you should post some DDL
and sample data to clarify exactly what you need:

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||Thank you so much! That's exactly what I was looking for. Works like a
charm!

Get only a certain piece of a string

I have a table that holds file paths for reports. Let's say it looks like this:

C:\Jeremy\Testing\JCScoobyRS\Testing.txt

Let's assume that none of the files are in the same directory and the directory is not known, as I'm running a report to get only file names. How can I get only file names from the string listed above? Thanks, JeremyBetter to store PATH & FILENAME in seperate variables.

Refer to this link (http://www.nigelrivett.net/CheckIfFileExists.html) for XP_FILEEXISTS which searches for the specified file, where you can use it for your task.

HTH|||That's my (lazy) way to do it:

declare @.FP varchar(255)
select @.FP='C:\Jeremy\Testing\JCScoobyRS\Testing.txt'
select reverse(substring(reverse(@.FP),1,charindex('\',rev erse(@.FP))-1))|||Valid tip Kukuk, keep it up.