How To Load XML From Filename And Path
Sep 6, 2005
Hi,
I have inherited a database that has a table called "Resources" that contains a field called "FilePath". FilePath is a VarChar(100) and contains the server path and filename of an XML file on the server hard disk.
I need to join the data in the XML with rows in the "Resources" table (and other tables).
Is it possible to get a Stored Procedure to load the XML file into a temporary table?
Is loading it into a table the best way to do it - or can I join directly to the XML file somehow?
What is the best approach? (I know nothing about XML in SQL Server yet)
Thanks in advance,
Chiz.
View 3 Replies
ADVERTISEMENT
Sep 7, 2007
Doesn't appear you can do this.
Am I wrong?
Please tell me I am.
View 11 Replies
View Related
Nov 8, 2013
I have to extract only the filename from the below filepath.
The first part prodserverexport always remains the same. A new folder is created prodserverexport 20071001, prodserverexport 200710012 etc each time the file is dropped . I want to extract only the part after that namely 20071001-200001-e430f8fd-PREC272.wma and like wise and dump it into a table.
prodserverexport2007100120071001-200001-e430f8fd-PREC272.wma
prodserverexport2007100220071002-013203-d00b2007-PREC120.wma
prodserverexport2007100220071002-032038-18381b92-PREC207.wma
prodserverexport2007100820071008-144758-16359a7b-PREC224.wma
prodserverexport2007100920071009-170426-4ed3535c-PREC97.wma
View 7 Replies
View Related
Jan 20, 2004
I'm guessing the best way / only way to store a filename is to store it as a varchar?
I'm using it as a reference to pictures wefor a gallery
View 2 Replies
View Related
Apr 4, 2008
Hi, for my photo album database I'm trying to decide whether to store the actual photos as images in the db or whether to leave them in file directories with just the path & filename in the db. As a beginner in this field I don't know how either of these options compare (e.g. in terms of performance, complexity, flexibility and maintainablity) so any advice on the pros and cons of each would be appreciated.
View 5 Replies
View Related
Feb 19, 2007
Hi,
I am adding a file to a filegroup on a remote system, and i don't know the direcotry structure of that machine, how can i provide the path for FILENAME param. :eek:
ALTER DATABASE TESTDB ADD FILE (NAME = N'TESTFILE', FILENAME = N'physicalfilepath.ndf') TO FILEGROUP TESTFILEGROUP
Warm Regards,
Mushq
View 1 Replies
View Related
Jan 22, 2008
Hi all,
I'm trying to let certain users execute an SSIS package through an SP in SQL.
I've set up a proxy account for xp_cmdshell that the other users will use to execute dtexec with, but i get this message:
Could not load package "File SystemCODA_Actuals" because of error 0x80070005.
Description: Access to the path 'C:Program FilesMicrosoft SQL Server90DTSPackagesCODA_Actuals.dtsx' is denied.
When i log onto the server with the proxy account, i can navigate to that folder and open the package.
When try to execute it from the command shell:
dtexec /DTS "File SystemCODA_Actuals" /Decrypt "password" /Reporting E /SET Package.Variables[User::JobId].Value;25'
i get the same message. How come? How do i fix this? I've read somewhere that i should grant read to the package store to that user , how do i do that? Can't find that option in the tools or manuals.
Thanks in advance,
Gert-Jan
View 2 Replies
View Related
Jul 23, 2007
In my SQL Server, I see the below message in the Application Event Viewer
"18265 :
Log backed up: Database: HSD, creation date(time): 2007/01/06(05:05:05), first LSN: 1439:495055:1, last LSN: 1439:496675:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQLBACKUPHSDHSD_tlog_200707141300.TRN'})."
When I save the application event viewer and open it in another server, I do not see the above message, instead I get the following message:
" Can't find or load message DLL. Message DLL must be in path or in current directory."
Any thoughts to overcome this problem is appreciated.
Thanks
Santhosh
View 3 Replies
View Related
Apr 19, 2008
Hi Champs,
Scenario Configuration : VB.net 2005 Code, WebService for Executing SSIS on Server, SSIS deployed on the Database Server
Problem Description : We are developing windows applicaiton in which we call webservice which was deployed on the same server where SSIS packages are deployed.
Now from Code we are passing FilePath name in variable and execute the Package. But the SSis result says that
The file name "\computernamefol1fol2fol3fol4abc.txt" specified in the connection was not valid.
More Information:
1. Full Permission are given on this network folder.
2. Package executes successfully from SSIS development solution (BIS solution)
3. Deployed packed executes successfully from the Database Server.
4. From Development pc packege executes successfully.
5. Other packages deployed on the same server executed suucessfully with same configuration and scenario.
Only this package is not executing.
-- the only differece with this package with other is -
using ".txt" extension in Flat file connection and using VB Script task
Can any one suggest the appropirate solution for this problem...
Thanks
Tarang Pandya
View 21 Replies
View Related
Apr 16, 2008
When trying to install Business Contact Manager (BCM) for Outlook 2007, the setup failed and I was refered to a log file in my Local Settings/Temp folder. The log actually says that Business Contact Manager was installed sucessfully! BCM is supposed to install SQL Express 2005 as an instance or as instance if SQL Express is already installed. There is an MSSMLBIZ instance in Services..
Who can I send the Log File to for analysis and the fix feedback?
When I first went into Computer Management and clicked on Services and Applications in the left panel, the error message appeared "Snap-in failed to intialize. Name: SQL Server Configuration Manager CLSID:{CA9F8727-31DF-41D2-975C-887D84903967} This message diappeared when I clicked on Services and Applications again. Under Services, there are 3 SQL services - one is an application that was uninstalled 3-4 weeks ago and I disabled this service. The other 2 are: SQL Server (MSSMLBIZ) and the other one is SQL Server (SQLEXPRESS) When I tried to start either of the last 2, the message appeared: Services "Could not start the SQL Server (MSSMLBIZ) service on Local Computer. Error 3: The system cannot find the path specified. Under Program Files/Microsoft SQL Server/MSSGL.1 folder is mostly empty. So, it seems like the Path in the Registry is not valid and that nothing is being installed in the MSSQL.1 folder. If so, how do I fix this?
How do I get the BCM SQL instance to install and run properly? what do the messages in Services mean and how do I resolve these.
Thank you!
Gary
View 3 Replies
View Related
Oct 4, 2007
After updating TempDB path to a wrong path (without file name only folder name) the service is not starting. How can i sovle this and start the service
thanks
Leena
View 13 Replies
View Related
Dec 14, 2007
Hi,
Here I will describe my problem.
1. We are loading large amount of data from database on background thread which is starting on Application_start event in global.aspx.cs file.The data is later cached for subsquent request to improve the performance.
2. Now when we put the application on web farm garden, it is not able to load the application.
3. We are sending the request the servers through Router kind of application.
4 This application is working fine on single server enviornment.
Please help us.
Ajay Kumar Dwivedi
View 1 Replies
View Related
Apr 27, 2007
I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run???
I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?
Thank
View 3 Replies
View Related
May 23, 2008
If you use "SELECT filename FROM sys.sysdatabases" this will return
the full path , such as "E:MSSQLdatamyData.MDF" ,
is it possible to return just the "myData.MDF" part of it?
I know that you can parse the string , but is it stored somewhere else , or is there a command that will return just the physical file name ?
Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
View 1 Replies
View Related
Mar 25, 2008
Hi Guys, got a question that I'm not sure there is an answer to other than 'No Way'.
I have to export a file whose filename must include a sequence number. I have created the table to hold the seq# and the code to increment it. My problem is how do I include the seq# in the output filename. I am using the Flat File Destination tool for exporting. should I be using another tool?
Any suggestions?
Thanx,
Trudye
View 2 Replies
View Related
Jun 19, 2002
Hello,
Is there any way to change the logical filename of exsiting database
Thanks
Lara
View 1 Replies
View Related
Oct 10, 2001
Does anyone know how to execute a directory listing of a file and return the file name to a variable in SQL? For instance, there's a file called c:datach20011010.txt and i execute the following:
xp_cmdshell 'dir c:datach*.txt'
or any other transact sql or procedure, how can i get the filename into a variable...all in sql?
FYI - I need to pass a filename to a process so that it can bcp the data into a temp table. But I don't know the filename at the time the process is setup/scheduled.
TIA,
Mike Nolen
View 3 Replies
View Related
Jul 26, 2007
Hi,
I am using a foreach loop to go through the .txt files inside a folder.
Using a variable I can pickup the filenames the loop is going through.
At present there is a sql task inside the foreach loop which takes the filename as a parameter and passes this filename to a stored procedure.
Now I would like to add one extra step before this sql task. Would like to have a dataflow with flatfile source which connects to oledb destination.
The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?
Please note, this is a different question to my other post.
Many Thanks
View 1 Replies
View Related
Jul 26, 2007
Hi,
I think I may have mis-guided some of you in my previous post.
So I thought I explain it properly this time in a new post...
Here is the description of the ssis package:
Inside the connection manager there is a connection to the sql server database and a flat file connection.
The flat file connection inside the connection manager has the expression set
i.e. ConnectionString set to @[User::FileName]
And so there is nothing inside the connectionstring property of this flatfile connection.
Inside control flow:
1)
Dataflow which contains a flat file source and an ole db destination
The flatfile source is supposed to pickup the .csv file that gets passed to the ssis package.
Then the ole db destination is where the .csv gets imported into a sql server table.
2)
Back in the control flow, there is a task that calls a stored procedure with the parameter which is set to FileName
The question is:
How does this FileName parameter gets set before it is passed to the stored procedure?
Many thanks
View 1 Replies
View Related
Mar 28, 2008
HI,
I am writing data from a file in to a table and i have a field in that table for filename. How to read the file name to write in that table.
Thanks in advance.
View 4 Replies
View Related
Jul 26, 2007
Hi,
Using a ssis package I have several tasks.
A variable is also created called FileName.
There is a flat file connection called InputFileName.
initially when I designed the package, I hardcoded the pass of one of the .csv files into the path of the flatfile connection.
There is a dataflow which has flatfile source and oledb destination. These two basically pickup the file and import into the database using mapping.
So therefore the package works and data gets imported into the database.
Then I used the variable to make the filename dynamic. So I removed the hardcoded connectionstring in the InputFileName.
Is this the right thing to do to make the package dynamic? I would like to pass in any .csv filename and so the package runs.
My question is now that the connectionstring is deleted but instead placed an expression for the filename, then how does the system know which file to use?
Thanks
View 1 Replies
View Related
Jul 11, 2007
Using SSIS foreach loop I get the files names inside a folder on the network.
How do I pass this variable i.e. file name to a stored procedure?
Thanks
View 9 Replies
View Related
Feb 27, 2008
Hi, I want to get my SSIS package to look for a file in a named directory that has a ceratin string in the filename.
example - My file is in a folder called data and the filename is 'System_UT_INCR_BOOKINGHEADER_20080214000000.TXT' Every day the fiel name changes to reflect the current date. I need my package to search for the String "BOOKINGHEADER". I know I need to create a variable with that value but I am not sure (using the example below) how to write the code to do this.
Imports System.IO
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Try
File.Move(Dts.Variables("Source").Value.ToString, Dts.Variables("Destination").Value.ToString)
Dts.Events.FireInformation(0, "", "File Moved Succesfully", "", 0, True)
Catch ex As Exception
Dts.Events.FireError(1, "", "Source file or destinations does not exist", "", 0)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
View 3 Replies
View Related
Sep 11, 2007
I take a backupfile every night and its named like 20070911_xvall.bak.
Now I need a procedure for an automatic restore in another sql server but I have to do a check to se that the date of the backupfile is the same as actual date so it can¨t happened that an older backupfile being restored.
Is it a good way to take actual date och build up the wanted filename and how can I then check if the file exist, its stored on another computer?
Anyone who has some sample I can start with?
Thanks
View 10 Replies
View Related
Feb 15, 2006
Hello,
I have a question regarding the report file which is included in an email, when a subscription is created:
Is it possible to programmatically add something to the name of the created file of the subscription? I want to have one parameter of the report, which is a date, added at the end of the filename to let the clients distinguish between different report files.
Thank you in advance.
Sandra Geisler
View 2 Replies
View Related
Apr 28, 2008
i have four text file which comes in different format , one of the fields i need to generate is the source Filename without extension as one of the coloums in the target tables.
The name of the file is requirment in the destination, how could i read the file name of the text file source and make it a coloumn and insert it into target.
Please advise
View 12 Replies
View Related
Aug 3, 2006
How do I correct this error:
"The database filename can not contain the following 3 characters: [ (open square brace), ] (close square brace) and ' (single quote)" ?
Where is the database stored and/or how can I change the relevant settings?
I have Both Visual Web Developer Express and SQL server express.
Exception detail:
System.Web.HttpException was unhandled by user code Message="The database filename can not contain the following 3 characters: [ (open square brace), ] (close square brace) and ' (single quote)" Source="System.Web" ErrorCode=-2147467259 StackTrace: at System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) at System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) at System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) at System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) at System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) at System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) at System.Configuration.SettingsBase.SetPropertyValueByName(String propertyName, Object propertyValue) at System.Configuration.SettingsBase.set_Item(String propertyName, Object value) at System.Web.Profile.ProfileBase.SetInternal(String propertyName, Object value) at System.Web.Profile.ProfileBase.set_Item(String propertyName, Object value) at System.Web.Profile.ProfileBase.SetPropertyValue(String propertyName, Object propertyValue) at ProfileCommon.set_MyNewProperty(String value) in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Fileslesson04385e3cebf08b8b90App_Code.jqcpv0qn.0.cs:line 24 at _Default.Page_Load(Object sender, EventArgs e) in D:Daniel's DocumentsVisual Studio 2005WebSitesLesson04Default.aspx.vb:line 15 at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Thanks in advance
View 3 Replies
View Related
Dec 13, 2006
how can I store a filename in the database, i have some GIF files which i want to show up on report, how can i specify those filename with a path in the database.
i tried manually entering the path c:Dataimages.gif but it didnt work. Can someone pls tell me how to do it.
Regards,
Karen
View 5 Replies
View Related
Mar 7, 2008
I have a page called 'upload.vb', which is the code for a file upload project I am working on. I am trying to write the filenames to an SQL database that I have built, but I keep getting this error: Compiler Error Message: BC30201: Expression expected. It doesn't seem to like the @ on line 46 in the VALUES part of my INSERT statement. Can anybody shed any light on this. Thanks. 1 Imports System
2 Imports System.Data
3 Imports System.Configuration
4 Imports System.Web
5 Imports System.Web.Security
6 Imports System.Web.UI
7 Imports System.Web.UI.WebControls
8 Imports System.Web.UI.WebControls.WebParts
9 Imports System.Web.UI.HtmlControls
10 Imports System.IO
11 Imports System.Data.SqlClient
12
13 ''' <summary>
14 ''' Upload handler for uploading files.
15 ''' </summary>
16 Public Class Upload
17 Implements IHttpHandler, IReadOnlySessionState
18
19 Public Sub New()
20 End Sub
21
22 #Region "IHttpHandler Members"
23
24 Public ReadOnly Property IsReusable() As Boolean Implements System.Web.IHttpHandler.IsReusable
25 Get
26 Return True
27 End Get
28 End Property
29
30
31
32 Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
33
34 If context.Request.Files.Count > 0 Then
35 ' get the applications path
36 Dim tempFile As String = context.Request.PhysicalApplicationPath
37 ' loop through all the uploaded files
38 Dim j As Integer = 0
39 While j < context.Request.Files.Count
40 ' get the current file
41 Dim uploadFile As HttpPostedFile = context.Request.Files(j)
42 ' if there was a file uploded
43 If uploadFile.ContentLength > 0 Then
44
45 uploadFile.SaveAs(String.Format("{0}{1}{2}", tempFile, "Uploads", uploadFile.FileName))
46 SqlDataSource.InsertCommand = "INSERT INTO tblDocuments(TheFileName) VALUES ('" & @TheFileName & "')"
47
48 End If
49 System.Math.Max(System.Threading.Interlocked.Increment(j), j - 1)
50 End While
51 End If
52 ' Used as a fix for a bug in mac flash player that makes the
53 ' onComplete event not fire
54 HttpContext.Current.Response.Write(" ")
55 End Sub
56 #End Region
57
58 End Class
View 10 Replies
View Related
Feb 23, 2006
Basically like the topic says, I need to populate a filename with data from other columns and im having a tough time doing it.
Code:
UPDATE nice_cls_calls_0027
SET vcarchivepath = 'G:Nice Storageicestorage.safeautonet.netSafe Auto Task - Logger (807101)2006_feb_1SC_807101_'start_time'_'=stop_time'_'=channel'_'=cls_call_id'.aud'
I need start_time, stop_time, channel, and cls_call_id to populate the filename. The first 2 are datetime fields in the db and no conversion is needed for them, just raw data. The remaining two are smallint and raw data is needed there also. Any help would be greatly appreciated. TY
View 4 Replies
View Related
Dec 15, 2005
Hi!
Is there a way I can make the source file in my DTS dynamic so that every time I run it using ASP I can tell it which file to use?
View 1 Replies
View Related
Jan 30, 2006
I need to use DTS to create a dynamic destination filename based on the value of a field. I tried creating a DTS package using the wizard. It works fine if I wanted a static filename for the destination filename. I tried creating a Dynamic Properties Task by selecting Connection2/OLEDB Properties/Data Source and then using a SQL Statement to return a value for the name of the file. It doesn't seem to run the query everytime the DTS is ran. The first time I run it it names the file correctly but after that it keeps naming the file the old value from the SQL statement. Any idea what I need to do to fix this?
View 5 Replies
View Related
Dec 18, 2006
I am running the following sqllitespeed backup script on Windows 2003 Server
against SQL Server 2000
sqllitespeed -Bdatabase -T -DNorthwind -FH:MicrosoftSQLServerBACKUPSDATABACKUPSNorthWi ndorthwind.bak
How could I place the date variable into the output filename?
I've tried variations of %date% etc but nada.
Any thoughts?
Thanks in advance.
Gerry
View 3 Replies
View Related
