GETDATE() and the local time zone

I have used the GETDATE() function within an expression to create a directory name based on the current date. I am in the Sydney time zone and the new day's folder name doesn't change until after 11 am - so GETDATE() is picking up the date and not adjusting for the time zone. How do I either set the time zone within the package or make the GETDATE() function look at time zone of the system on which it is run?You'll have to explain, because getdate() returns the system time where it is executed, time zone included. So.......|||Hmm.. that set me thinking about another date issue we have. We run in a citrix environment and I have noticed odd date displays before. This function behaves differently depending on whether I use RDP or ICA. I tested it by logging in under both types of session and creating an expression with GETDATE() in it and then evaluating it. The results were correct for RDP but not for ICA - so it looks like the problem is outside SSIS.

get the search term from a URL for reporting purpose

Hi guys,

I have a field called URL in my table. I want to get the SEARCH TERM from a given URL and create a report based on that information. I'm getting difficulties, because the URL have different format depending up on the search engine
that the users use to browse. Some of the search engines are "google",".excite.com", "search.msn.","search.netscape", "search.lycos", "altavista", "search.yahoo" and many more.

Examples of the URLs from google :

http://www.google.com/search?q=S26+Collet+Chuck&hl=en&client=firefox-a&rls=org.mozilla:en-USfficial&start=30&sa=N -- The search term is S26 Collet Chuck
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2006-02,GGLG:en&q=kt21+kia -- The search term is kt21 kia
http://www.google.com/search?hl=en&q=Slagger+burning+Tables -- The search term is Slagger burning Tables

Does anybody have a sql query or used a CLR functions to get the SEARCH TERM from different search engine (URL).

Thanks in advance.

Here is a blog entry from Chrissy LeMaire that seems to do what you want in both C# and straight SQL:

http://blog.netnerds.net/2007/02/mssql-parse-search-engine-querystrings-for-search-terms/

Larry

|||

10Q Larry, that's what exactly I was looking for.

get the data directory

Hi,

I am searching for how getting the data directory where default mdf files are based.

(C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)

My goal is to deploy mdf file in this folder during installation.

Thanks

hi,

I'm little in trouble giving advieces as well as registry values are not honored as the seem..

you can query, via a NON documented extended stored procedure the Windows registry for some info..

you can see a HKLM\Microsoft\Microsoft SQL Server key..

SQL Server 2005 registers instances as MSSQL.1, MSSQL.n, and this for every engine type, like Report server, Olap engine, ...

but you can get the MSSQL.x value querying the \Instance Names\SQL key as well

something like

SET NOCOUNT ON;

DECLARE @.test varchar(256);

DECLARE @.instance VARCHAR(128);

DECLARE @.regKey VARCHAR(128);

SELECT @.instance = CONVERT(varchar, SERVERPROPERTY('InstanceName'));

IF @.instance IS NULL

SET @.regKey = 'MSSQLServer';

ELSE

SET @.regKey = @.instance;

SELECT @.regKey AS [Instance name];

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',

@.key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\',

@.value_name = @.regKey,

@.value = @.test OUTPUT;

SELECT @.test AS [base instance directory];

SET @.regKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @.test + '\Setup';

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',

@.key = @.regKey ,

@.value_name = 'SQLDataRoot',

@.value = @.test OUTPUT;

SELECT @.test AS [SQL Path as per Setup key];

--<-

Instance name

MSSQLServer

base instance directory

MSSQL.1

SQL Path as per Setup key

--

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

but here problems arise... as usually data folder is in \...\MSSQL.1\MSSQL\Data , but that Data part is not shown anywhere.. .you can "perhaps" going littbe bit further, querying the \Parameters\SQLArg0 , which reports the full path of the master.mdf file formatted as the startup parameter required by SQL Server, thats to say

SQLArg0 = "-dC:\Program Files\..here full path.......\master.mdf"

and parse the result to extract the required "path".. or just hope no one changes that path and hardcode a + '\Data\' addition...

but, again, this whole post is in complete undocumented and unsupported mode..

regards

|||Thanks for your help Andrea