Import Directory Structure Into SQL Server Database
Jan 23, 2008
Hi,
As stated in the subject, I need to import a complete directory structure, from a root folder that I specify into a SQL Server database. To add further complexity, I also need to see what files exist in each directory.
At the end of the day what I am trying to achieve is to populate a table that will essentially have two columns, the filename and the directory that the file resides in. I am running SQL Server 2005.
Has anybody ever done anything like this before using T-SQL?
Thanks in advance.
View 5 Replies
ADVERTISEMENT
Jan 18, 2007
I need to be able to import a directory file list into a sql table.
i.e. table contains fields for filename, date etc...
is this possible?
thanks?
View 9 Replies
View Related
Apr 8, 2008
Say I do File -> New -> Project and make a new project called RedApple under C:. For simplicy, I chose root, but I would designate location for all my SSIS projects. I also check the box "Create directory for solution".
I see that this creates C:RedApple
Under this, I have another RedApple directory with a .sln and .suo files.
In this 2dn RedApple directory, I have .database, .dtproj, and .dtproj.user files.
I deleted the default package1, so I don't have any .dtsx files yet.
As far as the above files are concerned (.sln, .suo, .database, .dtproj, .dtproj.user), I understand these are some kind of system or meta data files and I shouldn't be messing around with them. And that's as far as I need to concern myself on these files.
As I add packages to my project, I see that it is creating .dtsx files under the 2nd RedApple directory. So, I'm thinking these are the package files, one for each package I see in my solution explorer, correct?
MY QUESTION:
Now, I am looking at another SSIS directory tree for a project that I created before I started to pay attention to all this. In this other directory tree, I see that there is also a bin directory, which I do not yet have in C:RedAppleRedApple. In this bin directory I see more .dtsx files, the same ones as I see in the directory above it, but also other .dtsx files that have the names of other packages that I onoce created and deleted along the development of this particular project. What are these .dtsx files?
View 3 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
Sep 26, 2005
Hi,I've just installed SQL server and then IIS and SQLCE tools.I created a virtual directory and was trying to update the NTFSpermissions from SQLServer Connectivity management when i got thefollowing error - Access Control List (structure) invalid.Has anyone come across this and if so, what did u do to fix it.ThanksLyn
View 2 Replies
View Related
May 11, 2007
Hi,
I am writing a contingency plan for incase our SQL Server (2005) goes down. Basically when our applicatino is about to write data to the SQL Server it checks the connection. If the connection passes it outputs to SQL, if it fails it generates an XML document detailing the data so that the data can be imported later.
Each XML document needs to define which database and which table needs to be updated/inserted.
So the SQL needs to be able to read a number of XML documents and update the relevant tables according dependent on the data held within the XML; some examples:
<Output>
<Record>
<Database>DW</Database>
<Table>dbo.tblActivity</Table>
<DateTime>11-05-2007 10:03:33.099</DateTime>
<Data>
<strActivityPK>9353|HCR008</strActivityPK>
<strActivityCode>HCR008</strActivityCode>
<strActivityDescription>HCR Complete</strActivityDescription>
<datTarget>2007-05-18</datTarget>
<datActual />
<intMatterIntCode>9353</intMatterIntCode>
<intRecurringActivityFlag>0</intRecurringActivityFlag>
<intETLFlag>0</intETLFlag>
</Data>
</Record>
</Output>
I need SSIS to open this XML, read the Table element and insert or update dbo.tblActivity (in this case) with the data defined in the Data element. If updating, the update should only take place if the DateTime is more recent than a field (datLastUpdate) on the corresponding row in the database.
Can anyone offer some guidance (for example is the XML optimally structured for import by SQL) and advice on how (which tasks to use) to go about setting this SSIS package up.
Ps. thanks to Darren I already have the file selection and open process sorted using a Foreach and some enumeration to select each XML file. It is what to do with the XML afterwards I need get working...
Thanks,
Drammy
View 6 Replies
View Related
Nov 29, 2006
I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.
I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.
Trouble is that it gets most of the way through the import until it spews forth the following error messages:
- Prepare for Execute (Error)
Messages
Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard).
There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.
Does anyone know how I can get the import to work?
View 2 Replies
View Related
Apr 24, 2006
Hello,
I'm looking for an example on how to import a Flat file that is not a csv file but had data on several lines, a small indicator at the beginning of the line indicates what kind of data is on the rest of the line. Not every field is availabye for each block.
Anybody got an idea on where to find this kind of samples.
Thx.
View 3 Replies
View Related
Feb 9, 2007
Which is better, to store the files onto the server's folders or to a database?
I tried storing to MSSQL 2000 but the varbinary does not allow me to set "MAX" for the data type.
View 1 Replies
View Related
Jun 20, 2008
1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.
View 2 Replies
View Related
May 23, 2007
I need to add one of our servers to active directory. I found some documents on SQL 2000 but no good document on SQL 2005. Can you please guide me to the documentation for this? Thanks
View 4 Replies
View Related
Dec 23, 2004
Hi all! I know I might sound stupid, but I have this problem. I have developed my database on my laptop which does not use Active directory. Lets say I access the database with: SPIDERMANDB1 on SQL server, where the name of my laptop is SPIDERMAN, and the database is DB1. Now, I create an SQL script from sql server and make the necessary changes, like changing the computer name to say, SUPERMAN where my deployment server's name is SUPERMAN. But when I run the SQL script on the SUPERMAN server, it gives me an error like: "User or role SUPERMANDB1 does not exist in this database". I have deployed databases in this way on servers without Acive directory, so I'm sure its not a mistake on my side. So how am I supposed to go around this active directory thingi! Please help me out! I'm counting on u guys!
Wes
View 4 Replies
View Related
Jun 1, 2015
I have an issue in my production , SQL server 2008 R2 , in Error log it is said “ An error occurred in dialog transmission Error : 9655 , State : 3 and Error : 9736 Severity : 16 State 0
I checked in sysmessages , it is said :” The transmission queue table structure in database is inconsistent. Possible database corruption “
So do you think I need to run DBCC checkdb ? Should I run it after working hours or it is fine within working hours ?
View 9 Replies
View Related
Apr 24, 2015
I have more than 3000 Active Directory Users, I have created Role Level Security on one table by using Suser_name(), Now all the active directory users need to connect sql server and access the database role object. How can I achieve that without Using .net?
I am able to add all active directory users to sql server in one go, but I am also trying to achieve same time to map with database role as well? Is it possible ?
View 2 Replies
View Related
Jul 20, 2005
Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh
View 3 Replies
View Related
Feb 24, 2006
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies
View Related
Jan 12, 2008
I have a backup of sqlserver 2000 data base . Now presently i am working on sqlserver 2005. Is there any facility to IMPORT entire 2000 database intosqlserver 2005.Please help me if there is any chance. Thanks in advance.
View 1 Replies
View Related
Jan 6, 2008
Hi,
I have a complicated sql server mobile database (.sdf) and need to create a SQL SERVER database with the same tables. How can I do it without scripting the whole thing? I thought of using the views.information_schema databases, but it is still a lot of coding.
thanks
View 3 Replies
View Related
Apr 6, 2007
Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.
View 6 Replies
View Related
Aug 3, 2006
I need to import an SQL database into SQL express 2005. The database was created with an older edition of Sql Server. I need to import the database onto a server that only supports SQL 2005, and I have the express edition. Can it be done with transact SQL. I know very little about transact SQL and I dont mind very long answer with lots of explination.
View 4 Replies
View Related
Sep 21, 2004
Hello everyone,
I need help with a situation that iam facing right now.
Here is the problem:
I have a tab limited or comma delimited CSV file. I want to read the contents of that CSV file and import it in one of the tables in the database.
id firstname lastname
-- ---------- ----------
"1" "John" "Smith"
"2" "Louis" "Garcia"
I assume that the columns in the CSV files and the table in the database match the datatype.
What would be a good approach to do it ? If anyone have a code that does the job, please post it.
Thank You.
View 7 Replies
View Related
Mar 10, 2005
Help needed!
I am a final year uni student and as part of my project I've developed a .Net site with a SQL Server 2000 backend.
This week a virus hit our house and my PC got infected (despite anitvirus and firewall!). Anyway, I have had to format my hard disk and reinstall everything.
My site is back in the wwwroot folder and looks fine, trouble is I need to get the database back up and running.
Before I wiped the disk I took the .mdf and .ldf files from the data file in the SQL Server folder in Program Files.
How can I get my database backup and running.
Ive tried creating a new database (with the same name as the .mdf and .ldf files) and replacing the default created files with the copies I saved but this didnt work.
Any help would really be appreciated as I have to demo this to my lecturers soon and my whole grade is riding on it! (no pressure)
Matt
View 5 Replies
View Related
Jun 1, 2005
Hi,i have developed an web-enabled student database application, where in i'll put all the details of the students from different universities.while uploading the details of the students, i wanna just import the data given by the universities into the server.I dont know how exactly the same could be done, also, since each university might be having there own format for the data, how can i import these into singe database?Any ideas ... Please. Thanks in advance
View 3 Replies
View Related
Sep 12, 2007
How import database mysql to mysql server 2005?--magix
View 1 Replies
View Related
Sep 22, 2015
I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
[code]...
The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.
Secondly I need to make 2 more changes to the code posted above.
1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.
2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')
View 7 Replies
View Related
Dec 28, 2007
i am creating a program with Local Database (sdf file).
i want my application to read the Database file from a different directory and not the application program?
does one SQL Database can hold many users Data's or can i create Database per user..
i am novice about SQL and Databases...
Thank you ..
View 4 Replies
View Related
Sep 25, 2007
Hello I am a final year student and for my final year project I have decided to try and create a price comparison website due to the fact that i feel it would be a good project to work on and develop my technical skills in C# and microsoft sql server 2005, since these are mainly the two programs I would say i have more experience with. Basically I want to create a website such as www.pricerunner.com. At this point in time i have started playing around with how i feel the database will look, in terms of tables and columns, below is what i have produced;
Company
Company_ID
Company_name
ADDRESS
Website link
contact
Products
Product_ID
Product_name
Product_Category
Product_Price
I am now stuck as how i will link each company with a product bearing in mind a company will stock different categories of products, also do you think i need any more columns in any of the above tables, as well as am i missing out on anything, I am hoping someone with more knowlegde than myself can shed some knowledge on my problem, thank you.
View 5 Replies
View Related
Aug 10, 2005
I'm sure there is a simple way to do this but I cannot think of it.I have an ASP.NET application that I am publishing to a webhost. I have chosen to use FTP to copy my files (aspx, etc.). They work as far as links etc. Now, the next step is to create my SQL database on their server as they instruct, which I have done in name only. (Oh, if it makes a difference, I access the hosting service using Plesk control panel.) Is there any simple way to get my database structure from my PC to the host server? All responses welcome. Thanks.
View 5 Replies
View Related
Oct 9, 2001
Hi All,
I need to get some info on how I can get the script of the entire database stucture. I need to write a procedure to do this. I know in SQL Server I could get the script in a mouse click. But I need to write a procedure to do the task. Is there a package in SQL Server that I could use to accomplish this task?
Any thoughts will be of help.
Thanks in advance.
sj
View 1 Replies
View Related
Sep 2, 1999
I am very new to SQL server, and am completely stumped here. I need to transfer the data from an old database to a new one (both SQL Server 6.5 DBs.) The only problem is that the database has been restructured, and I can't for the life of me figure out how to get the data imported. I am working on manually making all of the structure changes, but there must be a better way. Any ideas? Thank you SO much!
Jeff
View 1 Replies
View Related
Nov 1, 2004
This is a sort of database structure question that could I guess apply to any type of database...
Say I have 4 production lines and I want to log serial numbers on the start of every line and off the end of every line.
Is there any advantage in having a separate table for each Production Line and serial number logging point? (i.e. t_Line1Start, t_Line1End, t_Line2Start etc) or is it just the same as having one big table and having a LineNumber field and a StartEnd field?
Is SQL Server just as happy dealing with fewer transactions to several tables as it is to many transactions to one table?
View 1 Replies
View Related
Apr 9, 2008
Hi,
I have a question. I want to build a db for the following entities:
drivers
cars
companies
each driver nay has a car (NULL or carId)
each driver belongs to company
each car belongs to company
The db structure I built is:
car(carId, companyId)
driver(driverId, companyId, carId)
company(companyId)
The problem is that in this structure driver from company a can have a car from company b.
but this situation is not possible.. How can I prevent such situation??
tnx
View 14 Replies
View Related
Jul 8, 2006
Is there a way I can copy my SQL 2000 database including procedures, views tec. the whole works but not the data in any table.
I want to share with a relative a copy of my application (web based) he has his own SQL 2000 server but I do not want to give my data just the database so the application will run and he can start inputting his own data.
How can I do this like a backup which has everything but with no data.
I tried scripting it using the EM but it did not give me everything just the system db's
Thanks
Xavier
View 3 Replies
View Related
