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 specific files from directory

Hi,

I have some some files names in SQL DATABASE but my actuall files are keep in a seperate folder. so please help me that becasue i dont know that how i can select the specific files from folder and fetch into the imageArray according to the database table. In my coding its get the all files from directory but i want to get from database with where class and then select from directory.

The structure of my table is

create table event_pic
(
event_sub_id integer,
event_pic_name varchar(50)
)

here is code below:

Sub displayMe()

dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")
dim cmd as new SQLCommand("select * from event_pic where event_sub_id='5' ",con)

con.open()

dim SDR as SQLDataReader
SDR = cmd.ExecuteReader()



con.close()


Dim imageArray() As String
Dim i As Integer

' grab full path and file of images on server in images folder
imageArray = Directory.GetFiles(Server.MapPath("upload/"), "*.*")

' remove the full path from the image filenames
For i = 0 To (imageArray.Length - 1)
imageArray(i) = Replace(imageArray(i), Server.MapPath("upload/"), "")

Next


ViewImages.DataSource = imageArray
ViewImages.DataBind()


End Sub

If you are using 2.0 you can solve it with a List.

Sub displayMe()Dim conAs New SqlConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")Dim cmdAs New SqlCommand("select * from event_pic where event_sub_id='5' ", con) con.Open() Dim SDR As SqlDataReader SDR = cmd.ExecuteReader() Dim imageList As New List(Of String)() While SDR.Read() imageList.Add(SDR("event_pic_name").ToString())End While ViewImages.DataSource = imageList ViewImages.DataBind() con.Close()End Sub

|||

but the thing is that the database table only keeps the name of images actuall images are in a seperate folder and I will trace from folder according to the sql query.

|||

? In your initial query you strip out the path of the image, so imageArray will only contain the actual name of the image - without path. How do you want it? Does the entries in the database contain full path? Do you want to strip the path prior to binding?

|||

In my first query I get the files from DIRECTORY.GETFILES and then from loop I remove the paths and then bind to datagrid. anyway just pls tell me that the script that how I can get the specific files from folder according to the sql query?

|||

my database entries only keep only the names of files not full path

|||

If all your files are placed in the same folder, you can simply prefix the path of the image with that folder name. You should not use Server.MapPath for this, as it translates to a physical path. You need a virtual path in order serve the image URL correctly to the client. Also you must make sure that the image folder is located under the web root, either physically or through a virtual directory.

To get the correct virtual path in an asp.net app, you should use Page.ResolveUrl method.

So, the updated code would be:

imageList.Add(Page.ResolveUrl("~/imagefolder/") + SDR("event_pic_name").ToString())

|||

when i declare the image list its give me this error

Dim imageList As New List(Of String)()

Error: Type 'List' is not defined.|||

imports System.Collections.Generic

(Requires .net 2.0 or higher)

|||

Ok, Thanks but its display no images when i set the

ViewImage.DataList = imageList

ViewImage.Databound()

<asp:DataGrid runat="server" ID="ViewImages" AutoGenerateColumns="false" >
<columns>
<asp:TemplateColumn>
<itemtemplate>
<table id="tt" border="1" cellpadding="5" cellspacing="0">
<tr>
<td width="210"><img src="http://pics.10026.com/?src=upload/<%# Container.dataItem() %>" border="0" /></td>
</tr>
</table>
</itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>

|||

You already have the image path specified in your asp.net code. You have to choose - either you have it there, or you have it in your page class. Can't have both, or else the path will be invalid. My suggestion is that you set the entire path from your page class, with the code I provided (Page.ResolveUrl). If you do a view source on the page, you should see that the images are there but the path is invalid.

|||

can you explain me that how i can set this path and shows the images through datagrid, My acutall path is in the root directory and name of the folder is "upload". and i use this script to get files

do while SDR.Read()

imageList.Add(Page.ResolveUrl("~/upload/") + SDR("event_pic").ToString())

Loop

|||

If you want to display only the pictures, I would recommend you to use a Repeater instead, as it is more logical.

See this tutorial:http://msconline.maconstate.edu/tutorials/ASPNET20/ASPNET08/aspnet08-01.aspx

Also, see this previous post:Using a repeater to display images side by side?

|||

hmmmm, My problem is still remain actually i am a newly person in asp.net and so thats why i have a some problem, suppose if i use repeater but how i can display the images because my database hold only the names of images and the images are in a seperate folder. Is it possible to write a short script for me I shall be very thankful to you.

|||

This is another way to do it, may not be the best, but it works.

<asp:GridViewrunat="server"ID="GridView1"AutoGenerateColumns="false">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<tableid="tt"border="1"cellpadding="5"cellspacing="0">
<tr>
<tdwidth="210">
<%# GetImage(Eval("imagename").ToString()) %>
</td>
</tr>
</table>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Then inside the codebehind

Sub DataBindPhotos()

Dim mConAsNew SqlConnection("server=london-home; Database=tony; uid=rashid2; pwd=test;")

Dim mComAsNew SqlCommand("select * from event_pic where event_sub_id='5' ", mCon)

Dim myAdapAsNew SqlDataAdapterDim myTableAsNew DataTable

myAdap.SelectCommand = mCom

myAdap.Fill(myTable)

GridView1.DataSource = myTable

GridView1.DataBind()

End Sub

Function GetImage(ByVal ImageNameAsString)AsString

Return"<img src=path/to/your/pictures/" + ImageName +" border=0 />"

EndFunction

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

Get path of SYSTEM32 directory

Hello all, i have a little problem...

...Is there any method for getting the path of the SYSTEM32 directory from a stored procedure?

I need it for register an assembly that is in this directory or the server.

Thanks.

Alejandro F.

In SQL Server 2005, I would use the CLR to do this. You would have to mark it as unsafe allowing external access, but then you could just use .NET's native functionality to get the value.

|||

Thanks, but I need the path of system32 directory for registering my DLL with my CLR functions.

It’s any way to register a DLL without specify the entire path of the DLL?

Sorry about my English…

|||

No, but what I was suggesting was to build a CLR function (that you will be building and deploying manually) and use a windows API type call to get the path of system32.

Since this directory doesn't change, why not just have a configuration table that you put the path into? That would be easiest.

|||It's not a bad idea, thanks for all.|||

Consider doing these type of operations outside of the database? What is the purpose of this task? Is it an administrative routine? How often does it run? Do you really need to enable CLR or xp_cmdshell for such simple operations? Who runs these operations? For example, if you are creating assemblies using a script then it is trivial to obtain the path using various client-side techniques. It takes more programming and manageability to do even simple operations that require access to OS resources. And not to mention the security implications.

So if you have a choice keep TSQL for operations it can do best. I usually find it strange when people try to use TSQL to solve every problem. For example, in a CMD script you need to just reference %windir%\system32 - this assumes you run the script on the server. Now, if you want to do the same using TSQL then you need to enable xp_cmdshell, create temporary table, insert results into temporary table, read from temporary table, check for errors etc. The CMD script approach is more robust easy to execute on server, isolates higher-privilege operations, easy to modify (what if you later want to deploy assemblies by synching from your source code control system on different server) and so on.