June 17, 2003 at 8:31 pm
I have created a DTS Package which exports text files.
The package creates the files with no problem. I hten continue to manipulate hte filenames of he file using an ActiveX object which invokes the FileSystemObject in Windows. I use the FileExists method to check the text files have been successfully exported to give me some basic error checking and therefore I know I have no problems with the FileSystemObject. I then try to use the CopyFile or MoveFile methods to find I do not have permissions. How is SQL Server can create the file but yet when it calls a method on the file to rename or copy I find I do not have permission when the same routine actually created the file in the first place....
Any help, workarounds or samle code would be most welcome!!!!
June 18, 2003 at 12:42 am
Hi bobingham,
sorry, this is not exactly an answer to your question, but maybe you'll get some ideas where to look deeper.
So time ago I was playing with FileSystemObject, mainly to see what it can do. I found this is a very tricky one! The scripts I am about to post I've placed on my private homepage. After calling them via browser I was able to see not only directory, but the whole directory tree on the providers server. This is because of not properly drilled down access privileges (at least on Win2k).
I will post two scripts (in two separate posts for better reading). This script are only for demonstration purposes to show what the FileSystemobject can do.
Here's the first.
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>CheckIT</title>
</head>
<body>
<p align="CENTER">
<table width="970" border="0">
<tr>
<td align="left">
<form action="CheckIt.asp" method="get">
<%
Server.ScriptTimeout = 3000
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oDrives = oFS.Drives
For Each oDrive In oDrives
cDrives = cDrives & "<OPTION VALUE=""" & oDrive & """>" & oDrive & "</OPTION>"
x = x + 1
Next
%>
<select name="cPath" WIDTH="20"><%= cDrives %></select>
<input type="submit" name="submit" value="Laufwerk wechseln">
</form>
</td>
</tr>
</table>
<TABLE WIDTH="970" BORDER="1" CELLSPACING="2" CELLPADDING="1">
<TR>
<%
cDir = Request("cPath")
If cDir = "" Then cDir = Server.MapPath("/")
cParse = cDir
If Right(cParse, 1) <> "\" Then cParse = cParse & "\"
lPos = Instr(1, cParse, "\")
cOut = "<A HREF=""CheckIt.asp?cPath=" & Mid(cParse, 1, lPos) & """><IMG SRC=""../images/OFolder.gif"" BORDER=""0"">" & Left(cParse, lPos) & "</A><BR>"
x = 2
Do While lPos <> 0
oldPos = lPos
lPos = InStr(oldPos + 1, cParse, "\")
If lPos = 0 Then Exit Do
For y = 1 To x
cIndent = cIndent & " "
Next
cOut = cOut & cIndent & "<A HREF=""CheckIt.asp?cPath=" & Mid(cParse, 1, lPos) & """><IMG SRC=""../images/OFolder.gif"" BORDER=""0"">" & Mid(cParse, oldPos + 1, lPos - (oldPos + 1)) & "</A><BR>"
x = x + 2
If lPos = Len(cParse) Then Exit Do
Loop
Response.Write("<TD width=""500"" VALIGN=""TOP"">")
Response.Write(cOut)
cIndent = cIndent & " "
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(cDir)
Set oSubFolders = oFolder.SubFolders
For Each cFolder in oSubFolders
cFName = cFolder.Name
Response.Write(cIndent & "<A HREF=""CheckIt.asp?cPath=" & cFolder.Path & """><IMG SRC=""../images/CFolder.gif"" BORDER=""0""> " & cFName &"</A><BR>" & vbcrlf)
Next
Response.Write("</TD>")
Response.Write("<TD width=""500"" VALIGN=""TOP"">")
Set cFiles = oFolder.Files
Response.Write("<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0>")
For Each cFile in cFiles
cFName = cFile.name
Response.write "<TR><TD WIDTH=""970""><A HREF=""CheckIt2.asp?cFile=" & cParse & cFName & """><IMG SRC=""../images/File.gif"" BORDER=""0""> " & cFName &"</A></TD></TR>"
Next
Response.Write("</TABLE> </TD>")
%>
</TR>
</TABLE>
</P>
</body>
</html>
Name it CheckIT.asp (or whatever you like)
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 18, 2003 at 12:45 am
Here's the second one!
Name it CheckIT2.asp. If you change this name, don't forget to change it in <a href="...." in the first script
<%@ LANGUAGE="VBSCRIPT" %>
<%
On Error Resume Next
Set oFSO = Server.CreateObject("Scripting.FileSystemObject")
Set Out = oFSO.OpenTextFile (Request("cFile"), 1, FALSE, FALSE)
If Err <> 0 Then
%>
<HTML>
<HEAD><TITLE>CheckIT2: Error</TITLE></HEAD>
<BODY>
<%=Err.Description%>
</BODY>
</HTML>
<%
Else
cContents = Out.ReadAll
Out.Close
Select Case Right(Request("cFile"), 3)
Case "htm"
Response.ContentType = "text/HTML"
Response.Write(cContents)
Case "asp"
Response.ContentType = "text/plain"
cContents = Server.HTMLEncode(cContents)
cContents = Replace(cContents, vbcrlf, "<BR>")
Response.Write("<HTML><HEAD></HEAD><BODY>" & cContents & "</BODY>")
Case "gif"
Response.ContentType = "image/gif"
Response.Write(cContents)
Case "jpg"
Response.ContentType = "image/jpeg"
Response.Write(cContents)
Case "zip"
Response.ContentType = "application/x-tar"
Response.Write(cContents)
Case Else
Response.ContentType = "text/html"
Response.Write(Replace(cContents, vbcrlf, "<BR>"))
End Select
'Flush the buffer
'Response.End
End if
%>
Hope this will help a bit! (and I hope you'll get many permission denied errors)
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 18, 2003 at 3:54 am
Hey Frank,
A nice piece of code and an interesting example of using FileSystemObject.
But I'm still not sure why I can write text file using DTS, can see the file exists using VBScript and FileSystemObject in an ActiveX object in the same package but I'm unable to manipulate the file because of permission problems. Surely if I created the file in the first place I should be able to move and/or copy it?
But I enjoyed debugging your code and getting it working, you're a much more advanced coder than me, I'm just trying to move oceans of data from legacy systems left over from the ark!
Cheers,
Bob
June 18, 2003 at 4:37 am
Hi Bob,
quote:
A nice piece of code and an interesting example of using FileSystemObject.But I'm still not sure why I can write text file using DTS, can see the file exists using VBScript and FileSystemObject in an ActiveX object in the same package but I'm unable to manipulate the file because of permission problems. Surely if I created the file in the first place I should be able to move and/or copy it?
basically I was wondering if asp pages are secure, so that you can't see the source code behind. I believe they are secure, but as long as the OS hosting them is also secure. So I've put up the code. I guess a really good coder (or someone with malicious intent ), can improve the code much further, but for my belongings it works.
I have no deeper insight into OS. All I can imagine is, that different security contexts are applying to DTS, VBScript and ActiveX. Maybe someone else knows?
I guess it hasn't brought you really closer to a solution, but must it be the FileSystemObject? To move, copy, delete.. you can also use xp_cmdshell.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply