I am managing my database file, currently my primary data file size exceed 200GB.
I have deleted some unnecessary tables, the data file size doesn't smaller but still with the same size.
What makes the size doesn't change and how to make my data file become smaller?
Thanks alot for your sharing, i really hope for kindly reply.
Hi All,1. i want to transfer the .csv file data into sql server table, itried with the DTS but while creating DSN it not prompt to attechthe .csv file. give me the proper steps to perform the datatransfer...2. i want result of my query into excel or text file by using the sqlquery( like Select * from employee where emp_salary>10000 to 'c:emp.xls).i know the other way right click into query analyzer windowand select option result to file, but want the result by using SQLquery.....
Quote:
Hi All,
I am trying to import data via DTS from a CSV file. I have the "empty" tables already created with proper column names. Now, I have a subset data of each of these tables, which I am trying to import. I am facing problem in that, there is a datatype conflict between the source (CSV file) and destination (table already in the DB). All the data in the CSV file appears to be of DBTYPE_WSTR, where as in the table it is different (some are DBTYPE_WSTR, some are DBTYPE_DATE, and so on). Is there a way that I can import data successfully? This has become a work stoppage issue now. I had to actually go for this approach of creating the empty tables first and then importing data because, the backup file was very very large and could not be copied to our domain. Please help me out in this.
Thanks a lot.
Mannu.
Hi,How could i do to convert data from CSV files to tables on database in SQL server 2000 ? Please show me !Thank very muchdvl_lang
View 3 Replies View Relatedi want ot import data from excel .xls file to sql server 2000
into an existing table.
should i use some stored procedures or else
Hi,
Can anyone please help me on how to export data from SQL server 2000 to text file using C#. I could use bcp command to directly import data, but there are some changes need to be made to some codes from other tables in database and the data to be downloaded is also very huge. probably 10 million records.
Friends
Any one of you share your knowledge how to transfer data from a database to a excel using dts packages in sqlserver 2000.
I want clear steps how to create a dts package
Appreciate your help
Thanks
satish
Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).
View 1 Replies View RelatedHi Everone,I'm writing a script to import data from an XML file, and this tutorial isreally an awesome guide :http://www.kbalertz.com/Q316005/Imp....Component.aspx ... problemthough is here's a snippet of my XML file:<planets><sun rise="6:23" set="20:33"/><moon rise="18:54" set="4:26"/><mercury rise="7:50" set="22:11"/><venus rise="4:24" set="17:38"/><mars rise="9:45" set="23:40"/><jupiter rise="17:23" set="4:20"/><saturn rise="10:09" set="23:53"/><uranus rise="1:38" set="13:10"/><neptune rise="0:23" set="11:13"/><pluto rise="20:38" set="7:25"/></planets><moon><phase date="7/6/2006" text="Waxing Gibbous">9</phase><phase date="7/7/2006" text="Waxing Gibbous">10</phase><phase date="7/8/2006" text="Waxing Gibbous">11</phase><phase date="7/9/2006" text="Waxing Gibbous">12</phase><phase date="7/10/2006" text="Waning Gibbous">14</phase></moon>The tutorial seems to work well with entries in XML that look like this:<nighttime><txtshort>A moonlit sky</txtshort><txtlong>A moonlit sky</txtlong><weathericon>33</weathericon><hightemperature>100</hightemperature><lowtemperature>74</lowtemperature><realfeelhigh>108</realfeelhigh><realfeellow>74</realfeellow></nighttime>But when the XML file has more info (not sure technically what it's called)like the date and text options above in the phase tag, I'm not sure how toimport these into MS SQL. In MS Access I did do an import from this XMLfile, but alas it didn't see these entries in the phase tags either.Suggestions? I'm trying to use the XML Bulk Load component, but I'm sureI'm missing something. Thanks for any suggestions ...Sam---Sam Alexander - sam.alexander(at)sidebandbbs(dot)comhttp://www.samandflip.com or telnet://sidebandbbs.com---"Data is not information, Information is not knowledge, Knowledge is notunderstanding, Understanding is not wisdom." -- Cliff Stoll--- Synchronet 3.13b-Linux NewsLink 1.84--[SideBand BBS - telnet://sidebandbbs.com]--
View 3 Replies View RelatedI just installed SQL 2005 on a new box. I want to move a database from a SQL 2000 server to the new server. Can I detach the database, copy it and attach it in the new server without having problems?
I'm concerned that if the datafile is in SQL2000 format, when I connect it to SQL2005 server, will it still be in old format or will it upgrade?....or is this something you don't worry about....and why?
Thanks,
Craig
Currently we have a DTS that extracts data from a cube to a SQL Server table and then from SQL Server to a flat file.
This is taking a considerable time is there a faster way of doing this?
Thanks.
Mark.
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 Replies View RelatedHi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.
Thanks
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?
View 3 Replies View Relatedone of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.
View 9 Replies View RelatedHi,My webhost (1and1) is running SQL Server 2000 and their web tool supports the import of .bak files. However, when I try to import my .bak files created in SQL Server Management Studio Express I get the following error:"The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RESTORE FILELIST is terminating abnormally."I have Googled this error and learnt that 2005 .bak files are not compatible with 2000 .bak files. I'm just wondering if there are any work arounds to this or alternative tools that I can create 2000 compatible .bak files from from 2000/2005 .mdf files.Thanks in advance.
Hi Dear,
May Any one guide me?
I have a backup file of database which is in SQL Server 2000. it has no Extension.I want to restore this backupfile or this database in my SQL Server 2005.
I have tried to Attach Database or attach this backup file in Sql Server2005 but it also fails .
First I have created New database name as is on the backupfile and then I have also tried to rename this file with .bak extension and then try to restore again it fails.
Plese Guide me urgently........
:eek: :eek: :eek: :eek: :eek:
hi there.
i'm using asp.net 2 page i'm accessing a table consists of 100 thousands rows and its slow and i'm wondering instead of accessing directly to the table how about if i access via xml ?
generate xml and cache it and use the xml file rather going to sql server database?
has anybody have any help on this?
the steps invloved:
1) first generate a xml file from table something like this:
select * from dbo.LOOK_UP FOR XML AUTO, XMLDATA ?SELECT * FROM dbo.LOOK_UP FOR XML RAW, ELEMENTS ?SELECT * FROM dbo.LOOK_UP FOR XML AUTO ?
which one should i use and how do i access after i gnerate a xml file
thanks.
I know I know this is kind of reverse thing I am asking.but as I am working on project and it is very time consuming to connect every time I want to for modifying database or dataset.
View 1 Replies View Relatedhi
i am using VS2003 and .net 1.1. i need to upload a cert file into sql server 2000. code is as follows, i am not sure if this code is working or not, its not giving any error nor throwing any exception. in this function i am only converting ther .cer file to byte array. and in other function i am passing this byte to a stored procedure. in stored procedure i have declare a parameter as "@cert binary". i cant see the binary data in sql server 2000, its only showin <binary> in that column. i need to download this file now, how can i do that. Dim selectedCert As HttpPostedFile
Dim certLength As Integer
Dim isValid As Boolean = False
'Dim imageType As String
'Dim fileUpload As System.Web.UI.HtmlControls.HtmlInputFile = .FindControl("fileUpload")
selectedCert = fileUpload.PostedFile
certLength = selectedCert.ContentLength
'imageType = selectedCert.ContentType
Session.Add("isDefaultLogo", 0) ' To check if default logo is used
If fileUpload.PostedFile.ContentLength = 0 Then
' DO NOTHING
ElseReDim binaryCert(certLength - 1)
'check if there a certificate file or not
If (selectedCert.ContentLength = 0) Then
' DO SOMETHING
Else
selectedCert.InputStream.Position = 0
selectedCert.InputStream.Read(binaryCert, 0, certLength - 1)
Response.Write(selectedCert.ContentType.ToString)
If (selectedCert.ContentType.ToLower <> "application/x-x509-ca-cert") Then
lblMsg.Visible = True
lblMsg.Text = "Only Certificate (*.cer) files are allowed."
isValid = True
Return False
End If
End If
End If
If isValid Then
TryDim strm As IO.FileStream = System.IO.File.Open(System.IO.Path.GetFullPath(fileUpload.PostedFile.FileName), IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.ReadWrite)ReDim binaryCert(strm.Length - 1)
strm.Read(binaryCert, 0, strm.Length)
strm.Close()Catch ex As Exception
Return False
End Try
End If
thnx in advance
cheers
Am running several databases on SQL server 2000.
Have several overnight jobs for interface and maintenance, etc.
Log file on one specific database increases in size 10 fold overnight. Unable to trace why.
I have a job to truncate the logs before the final overnight backup. Several times a week the job hangs - on nights when log file expands, so it is a pain. Able to resolve the issue after the event, but do not understand why the log file increases in size (at that time) in the first place. No other activity on the server at the time.
How do I reattached a SQL server database if the log file was not backed up and none is available. Our server went belly up and the backups were only copying the *.MDF files. The log files were not being included in the backup for some reason.
I know in version 7.0 you could reattach with out the log file but I am having a more difficult time figuring out how to get around thin in SQL 2000. Anybody got any ideas???
Thanks!!!
Hi all,I have a file with an extension of .sdf. I "believe" it is a text fileof some sort but I am uncertain. The source agency hasn't returned anyof my calls so I'm wondering if anyone is familiar with this extension?I'd like to import the file into my database - when I use DTS and chosea text format, regardless of what delimiter I choose, the format isstill really ugly. when I pull it up in a huge text editor, it is hardfor me to tell what it is there.I saw in one of my searches that it could be a comma delimited (it'snot) .. could be a unisys file? I know it's not much information to goon - but where should I start in trying to get this into my databasewithout knowing the format? Any suggestions would be greatlyappreciated.Thanks!Bethany*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi,
My database corrupted because when I was running an update query, there is a power failure. After the computer booted, I cannot open the database anymore, it just not responding. Then I stop the sql server service, and tried to rename the .mdf and .ldf. After that it worked normally, but I need the data from the corrupted mdf file, I tried to attach the database but it just hanged. I even tried to attach without the .ldf file but it didn't work either, so I concluded that the problem is with the mdf file.
Is there any way to recover my data ??
Thanks in advance
Regards,
Edwin
Dear All,
We have a DB on sql server 2000 64 bit and the build is 2187.
The database consisted of two transaction log files and several data files at the time we took backup. However, the backup taken was a cold backup (backup of files through OS). In this backupset, one transaction log file was skipped due to some error...... Now I want to restore the database for referring to some history data. Will I be able to attach the database with all MDF and one of the transaction log files.??
I heard this kind of recovery is not possible. If anybody can post some solution , I would be grateful...!!
Regards,
Chetan
Is this even possible? I tried to move an existing Sql Server Express .mdf file to SQL Server 2000 by copying the .mdf file to the server's folder and then tried to attach it. I get:
Error 602: Could not find row in sysindexes for database ID 9, object ID 1, index ID1. Run DBCC CHECKTABLE on sysindexes.
I realize that I'm moving back a version in SQL, however the structure of the database is very simple. Does anyone know?
Even if I can just script the thing to SQL 2000 would be helpful, however the .mdf is a file within my ASP.Net site, and does not show up in my Microsoft SQL Server Management Studio Express under the appropriate SERVERNAMESQLEXPRESS either, so I can't figure out how to script it out?
I need help importing a complex xml file using the XML Bulk Load component. I need there to be 2 tables as shown below. I just
cannot seem to figure out how to get this to work with such a complex XML structure. I have shown below my table structure, a
sample of one of the entries of the XML files and what I have so far for my XSD schema. Any help would be great!!!
My Tables:CREATE TABLE [dbo].[WPXML] ( [Part] [varchar] (100) PRIMARY KEY, [BaseVehicle] [int] NULL , [Qty] [int] NULL , [PartType] [int] NULL , [EngineBase] [int] NULL , [EngineDesignation] [int] NULL , [ImageURL] [varchar] (100) NULL , [ThumbURL] [varchar] (100) NULL) GOCREATE TABLE [dbo].[WPPRODUCT] ( [Part] [varchar] (100) PRIMARY KEY , [PartNumber] [varchar] (100) NULL , [BrandID] [varchar] (4) NULL , [BrandDescription] [varchar] (100) NULL , [Price] [varchar] (10) COLLATE NULL , [ListPrice] [varchar] (10) COLLATE NULL, [Weight] [varchar] (10) COLLATE NULL, [Popularity] [varchar] (10) NULL, [OEFlag] [varchar] (10) NULL, [ProductRemark] [varchar] (1000) NULL, [Note] [varchar] (5000) NULL ) GOSample of XML:<App action="A" id="1484266"> <BaseVehicle id= "5899"/> <EngineBase id= "555"/> <EngineDesignation id= "138"/> <Qty>0</Qty> <PartType id= "6192"/> <Part>W0133-1621038</Part> <Product> <PartNumber>W0133-1621038</PartNumber> <BrandID>FUL</BrandID> <BrandDescription><![CDATA[Full]]></BrandDescription> <Price>17.38</Price> <ListPrice>36.60</ListPrice> <Available>Y</Available> <Weight>1.05</Weight> <Popularity>B</Popularity> </Product> <Product> <PartNumber>W0133-1611982</PartNumber> <BrandID>KN</BrandID> <BrandDescription><![CDATA[K&N Filters]]></BrandDescription> <Price>68.78</Price> <ListPrice>105.81</ListPrice> <Available>Y</Available> <Weight>1.80</Weight> <Popularity>E</Popularity> </Product> <Product> <PartNumber>W0133-1626304</PartNumber> <BrandID>ND</BrandID> <BrandDescription><![CDATA[Denso]]></BrandDescription> <Price>22.34</Price> <ListPrice>36.60</ListPrice> <Available>Y</Available> <OEFlag>OEM</OEFlag> <Weight>1.05</Weight> <notes>Notes For This Part</notes> <Popularity>D</Popularity> </Product> <ImageURL><![CDATA[http://img.eautopartscatalog.com/live/W01331621038OES.JPG]]></ImageURL> <ThumbURL><![CDATA[http://img.eautopartscatalog.com/live/thumb/W01331621038OES.JPG]]></ThumbURL> </App>
My XSD Schema Thus Far:<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation> <xsd:appinfo> <sql:relationship name="test" parent="WPXML" parent-key="Part" child="WPPRODUCT" child-key="Part" /> </xsd:appinfo></xsd:annotation> <xsd:element name="App" sql:relation="WPXML" sql:relationship="test"> <xsd:complexType> <xsd:sequence> <xsd:element name="Qty" type="xsd:integer" /> <xsd:element name="Part" type="xsd:string" /> <xsd:element name="BaseVehicle"> <xsd:complexType> <xsd:attribute name="BaseVehicle" type="xsd:integer" sql:field="BaseVehicle" /> </xsd:complexType> </xsd:element> <xsd:element name="PartType"> <xsd:complexType> <xsd:attribute name="id" type="xsd:integer" sql:field="PartType" /> </xsd:complexType> </xsd:element> <xsd:element name="EngineBase"> <xsd:complexType> <xsd:attribute name="id" type="xsd:integer" sql:field="EngineBase" /> </xsd:complexType> </xsd:element> <xsd:element name="EngineDesignation"> <xsd:complexType> <xsd:attribute name="id" type="xsd:integer" sql:field="EngineDesignation" /> </xsd:complexType> </xsd:element> <xsd:element name="ImageURL" type="xsd:string" /> <xsd:element name="ThumbURL" type="xsd:string" /> <xsd:element name="Product" sql:relation="WPPRODUCT" sql:key-fields="Part" sql:relationship="test"> <xsd:complexType> <xsd:sequence> <xsd:element name="Part" type="xsd:string" /> <xsd:element name="PartNumber" type="xsd:string" > </xsd:element> <xsd:element name="BrandID" type="xsd:string" > </xsd:element> <xsd:element name="BrandDescription" type="xsd:string" > </xsd:element> <xsd:element name="Price" type="xsd:string" > </xsd:element> <xsd:element name="ListPrice" type="xsd:string" > </xsd:element> <xsd:element name="Weight" type="xsd:string" > </xsd:element> <xsd:element name="Popularity" type="xsd:string" > </xsd:element> <xsd:element name="OEFlag" type="xsd:string" > </xsd:element> <xsd:element name="ProductRemark" type="xsd:string" > </xsd:element> <xsd:element name="Note" type="xsd:string" > </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:element></xsd:schema>
I am trying to import an Excel file - when I pick the file I get the message "Could not open file for reading. Close any other application that may be locking the file."
I have verified that the file is not open - I have even rebooted the machine - still the same message - what am I doing different?
Please advise.
Thanks
the mdf file is attached successfully in sql 2005 while i attached in sql server 2000 it shows some error
View 13 Replies View RelatedHi guys
I need to apply a sql script file on Sql server 2000 by .net 2.0 program. but on the current running machine, there is not Osql.exe file. Do you guys know how to execute the sql file without the command? Is there any way provided in .net library ?
Thanks for you response!
Data for Source Column 15 'Notes' is too large for the specified buffer size. How do I get around this, I can see some of the notes entries are beyond 255 chars so I changed the destination datatype to textI have never seen this error when importing before. What do I do?
View 1 Replies View Relatedhi..
I want to store a RMVB file to SQL SERVER 2000 ,and read from it,iwant to play the RMVB file in web,the size of the RMVB file is more than 300MB less 1G.the SQL Field Image can include it.
Now My Quesstion is How can i Store and Read the RMVB file from SQL Server2000?
I used SqlInsertCommand.ExecuteNoquery() in my program,but it Too slow,ao make a unknown error.
Thank you for your help.