Importing XML to SQL Server: National "Do Not Call" database

I need to import the "National Do Not Call" database into SQL server.
It is in XML format (*.XML) and I understand it consists of 2 fields: area
code and phone number and about 50 million records.
What is the best way to get this file into a SQL Server table?One option would be to look at using SQLXML and XML Bulk
Load. You can find more information, links and a download
link at:
http://msdn.microsoft.com/nhp/Default.asp?contentid=28001300
-Sue
On Thu, 18 Sep 2003 16:56:48 -0700, "DaveF"
<davef@.comcast.net> wrote:
>I need to import the "National Do Not Call" database into SQL server.
>It is in XML format (*.XML) and I understand it consists of 2 fields: area
>code and phone number and about 50 million records.
>What is the best way to get this file into a SQL Server table?
>

Importing XML for Newbies

I need to store log files that are created in XML format so I can query some
of the fields. I have only minor knowledge of XML, but have been SQL DBA fo
r
a while. I just have not had a need to use XML in the SQL environment. We
are using SQL2000. What is the best way to do the following:
1. Create a DB for storing the log files? Some variation on the usual way?
2. Import the individual files into the DB. Eventually I can write a DTS or
something to import?
3. Query the DB. Create some reports on usage.
If somene could post a link to get me started it would be very helpful.DaveK wrote:
> I need to store log files that are created in XML format so I can query so
me
> of the fields.
Why not just use an XQuery implementation to query them direct? I see no
requirement here to involve a database at all.
///Peter

> I have only minor knowledge of XML, but have been SQL DBA for
> a while. I just have not had a need to use XML in the SQL environment. W
e
> are using SQL2000. What is the best way to do the following:
> 1. Create a DB for storing the log files? Some variation on the usual way
?
> 2. Import the individual files into the DB. Eventually I can write a DTS
or
> something to import?
> 3. Query the DB. Create some reports on usage.|||After a five minute web search and SQL help search I don't see how I can
avoid using SQL to build some sort of datbase just so I can address backup,
security, etc. It looks like XPath and XQuery can do some searching, but th
e
other items that I need to cover are not really addressed. This is an
example of one event log item I want to store.
<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>
I have to admit I am not a convert to the XML world, but as I said, I am a
newbie to it as well. In this case it just seems like a fancy way to
eliminate delimited importing. The format is not likely to change.|||DaveK wrote:
> After a five minute web search and SQL help search I don't see how I can
> avoid using SQL to build some sort of datbase just so I can address backup
,
> security, etc. It looks like XPath and XQuery can do some searching, but
the
> other items that I need to cover are not really addressed. This is an
> example of one event log item I want to store.
> <CreateDate>7/31/2007</CreateDate>
> <CreateTime>10:19:25</CreateTime>
> <Logger>xxxxd7yrrt11</Logger>
> <Events>
> <Event>
> <EventID>8001</EventID>
> <Description>Login</Description>
> <Category>Audit</Category>
> <Source>LAN Client</Source>
> <SubSource>xxxx_8</SubSource>
> <UserName>test.name</UserName>
> <UserID>347</UserID>
> <Computer>xxx7YRRT11</Computer>
> <Date>07/31/2007</Date>
> <Time>10:19:49</Time>
> <ObjectType>User</ObjectType>
> <Details></Details>
> </Event>
> I have to admit I am not a convert to the XML world, but as I said, I am a
> newbie to it as well. In this case it just seems like a fancy way to
> eliminate delimited importing. The format is not likely to change.
AFAIK all database systems now offer some kind of "Import XML" plugin.
If yours doesn't, you'll need to turn the XML into CSV or whatever your
system consumes. The easiest way to do this is to write an XSLT script,
and I think there are several quoted or linked in Dave Pawson's XSL FAQ
at http://www.dpawson.co.uk/xsl/
XML is just a fancy way of identifying information: you can see from the
above example that it is much clearer in naming items and positioning
them in the hierarchy than (for example) CSV. If the format is stable,
then a little routine to run XSLT over the data and spit out CSV should
do you just fine.
The following appears to work for the sample above (with the addition of
the missing </Events> end-tag and the enclosing root element
<data>...</data> ):
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output method="text"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/">
<xsl:apply-templates select="data/CreateDate"/>
<xsl:apply-templates select="data/CreateTime"/>
<xsl:apply-templates select="data/Logger"/>
<xsl:apply-templates select="data/Events/Event/*"/>
</xsl:template>
<!-- fields that start a record -->
<xsl:template match="CreateDate|EventID">
<xsl:text>"</xsl:text>
<xsl:value-of select="."/>
</xsl:template>
<!-- fields that occur in mid-record -->
<xsl:template match="*">
<xsl:text>,"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>"</xsl:text>
</xsl:template>
<!-- fields that end a record -->
<xsl:template match="Logger|Details">
<xsl:text>,"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>" </xsl:text>
</xsl:template>
</xsl:stylesheet>
$ java -jar /usr/local/saxon/b8.5/saxon8.jar -o test.csv test.xml test.xsl
$ cat test.csv
"7/31/2007,"10:19:25","xxxxd7yrrt11"
"8001,"Login","Audit","LAN
Client","xxxx_8","test.name","347","xxx7YRRT11","07/31/2007","10:19:49","Use
r",""
$
This makes the assumption that your import routine can do something
different with record #1...
///Peter
--
XML FAQ: http://xml.silmaril.ie/|||Thank you. This is very helpful. I did not paste in the whole log so misse
d
the ending tags.

Importing XML for Newbies

I need to store log files that are created in XML format so I can query some
of the fields. I have only minor knowledge of XML, but have been SQL DBA for
a while. I just have not had a need to use XML in the SQL environment. We
are using SQL2000. What is the best way to do the following:
1. Create a DB for storing the log files? Some variation on the usual way?
2. Import the individual files into the DB. Eventually I can write a DTS or
something to import?
3. Query the DB. Create some reports on usage.
If somene could post a link to get me started it would be very helpful.
After a five minute web search and SQL help search I don't see how I can
avoid using SQL to build some sort of datbase just so I can address backup,
security, etc. It looks like XPath and XQuery can do some searching, but the
other items that I need to cover are not really addressed. This is an
example of one event log item I want to store.
<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>
I have to admit I am not a convert to the XML world, but as I said, I am a
newbie to it as well. In this case it just seems like a fancy way to
eliminate delimited importing. The format is not likely to change.
|||Thank you. This is very helpful. I did not paste in the whole log so missed
the ending tags.

Importing XML

This might be a dumb question but I am not an exper on XML. I have
daily exports in XML format that need to be imported into SQL Server.
What's the easiest way of accomplishing what seems to be an easy task.
The XML schema is the same as the DB schema.
Many thanks...
BobCreate a stored procedure to handle this. BOL is pretty thorough on
importing XML data using SP.
MJKulangara
http://sqladventures.blogspot.com|||Which version of SQL Server are you using?
Anith|||2000|||In 2000, you have limited options. If you want to have the entire XML doc as
a single value, then you'd have to use a VARCHAR(n) or TEXT datatype in your
table & pass the entire doc through a stored procedure.
Another option is to create a ADO/OLEDB wrapper which executes the FOR XML
query and stream it back to the table. You can use sp_OA* procedures to call
these objects directly from t-SQL.
If you want to shred them into individual column, then you'll have to use
OPENXML along with sp_xml_* procedures.
Also refer to:
http://msdn.microsoft.com/library/e...penxml_759d.asp
Anith

Importing Text File into SQL Server Problem

Hello,
I am trying to load a text file into SQL Server but the text file seems to be in an unsual format that SQL Server is having a problem reading. I have tried the various options for delimited and fixed file formats.
Any ideas would be appreciated.

Sample of the file:
Dn DCHB
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 200508082345079999 BANNER PAGE
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 20050808234507 420
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 20050808234507 425
;… b` DCHCVDR SMGSWP04JOB08748SMA 704DSEARS VDR SWEEP 4 RDSSWSM REPTPROCSTEP1 V-1 &? &? BRTA_UA46 20050808234507 440
What you are getting is called character conversion, try the link below to use DTS to move the file. Hope this helps.
http://www.sqldts.comsql

Importing multiple flat files to multiple tables in SSIS

I have a couple of hundred flat files to import into database tables using SSIS.

The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.

However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.

Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.

I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?

In an OLEDB destination you can select to use a "Table or view name from variable" -in this way the destination can be dynamic.

|||Thanks, I missed that.|||

Any suggestions on the best way to assign the destination table name variable?

I imagine could do it in a Script item in the Foreach Loop before the Data Flow item is executed, but is there somewhere to do it more in-line, like building it into the Data Flow item's properties somehow?

|||

Use some logic to derive the table name based on the format, which I guess must be derivable from the filename?

You have the filename, so how do you expect to transform this into a table name. I would expect an expression to be used somewhere, the tablename variable for example, but you may need to refer to some logic table. an Execute SQL Task inside the loop could query a SQL table that gave you the destination table from a filename. The Exec SQL Task result could then be assigned to the tablename variable.

Importing multiple files to SQL

I have over three hundred text files that I need to import to SQL
Server.
Each is in the exact same format.
I want to import tham as seperate tables.
Is there any way to do it in one process?

Regards,
CiarnYou can use DTS:
http://www.sqldts.com/default.aspx?246

If all the files are the same format then why not import them to a
single table with an extra column to identify the source file? That
should be much more convenient than creating 300 separate tables.

--
David Portas
SQL Server MVP
--|||I use the above method, but if you want seperate tables:

You could add in dynamic properties and some scripts to the workflow of
the DTS. A script task sets a source filename dynamic property. A
create table task uses the dynamic property as the table name. Then
import into the new table from the file(again using the dynamic
properties to set the source filename and destination table name).

Using only one table will make some things much easier, and other
things harder. Consider that with multiple tables you will have to
either have a copy of all your queries for each table, or have a stored
procedure that allows the table name to be specified as a parameter. I
think it would be easier to put all the data in one table and have a
sproc that takes the code for the special column as one of the
filtering criteria. I think the syntax for specifying the criteria for
that column is no harder than specifying a table name.|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?

Importing MS Word Forms

I have some complex Word forms that need to be converted into reports. Are
there any tools for converting/importing Word to RDL format as you can't
paste into the desinger.
Thanks
Toby.SQL Server 2000 Reporting Services does not support this directly. You
might check to see if one of our partners is able to assist you:
http://www.microsoft.com/sql/reporting/partners/softwareapps.asp
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Toby" <toby.maillist@.exmlsystems.com> wrote in message
news:uMKD5gshEHA.1276@.TK2MSFTNGP09.phx.gbl...
> I have some complex Word forms that need to be converted into reports. Are
> there any tools for converting/importing Word to RDL format as you can't
> paste into the desinger.
> Thanks
> Toby.
>

importing LongText fields

Hi all,
I've got a large db I downloaded in a .SQL format that came originally from
MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
I've got it mostly translated over, but I'm running into a problem with
MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
into problems with fields being too long to enter.
I guess the crux of my question is this: is there any way to go over the
8000-character limitation?
Regards,
Scott McNair
"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns95DAA939F3E2Dsfmco@.207.46.248.16...
> Hi all,
> I've got a large db I downloaded in a .SQL format that came originally
> from
> MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
> I've got it mostly translated over, but I'm running into a problem with
> MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
> called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
> into problems with fields being too long to enter.
> I guess the crux of my question is this: is there any way to go over the
> 8000-character limitation?
> Regards,
> Scott McNair
Take a look at text and ntext. You have a 2GB/1GB limit respectively.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||"Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
@.TK2MSFTNGP10.phx.gbl:

> Take a look at text and ntext. You have a 2GB/1GB limit respectively.
Perfect. I created a UDDT associating "longtext" with text, and it imports
more-or-less perfectly.
|||My apology for btting in this reply but I have the same issue where I need to
store +- up to 16000 characters in one field and I tried text or ntext but
the max size value is 16, can you tell me how to set up that field to accept
the 16000 i need or more?
Thanks
Xaviervp
"Scott McNair" wrote:

> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
> @.TK2MSFTNGP10.phx.gbl:
>
> Perfect. I created a UDDT associating "longtext" with text, and it imports
> more-or-less perfectly.
>
|||As Rick stated, the max text/ntext/image size is 2GB. You don't need to do
anything special.
The reported 16-byte length is the default text-in-row length. The default
length of 16 will hold the pointer to the separately stored value.
Hope this helps.
Dan Guzman
SQL Server MVP
"xaviervp" <xaviervp@.discussions.microsoft.com> wrote in message
news:66C5708A-B2F2-44ED-BC46-727AFD090EEB@.microsoft.com...[vbcol=seagreen]
> My apology for btting in this reply but I have the same issue where I need
> to
> store +- up to 16000 characters in one field and I tried text or ntext but
> the max size value is 16, can you tell me how to set up that field to
> accept
> the 16000 i need or more?
> Thanks
> Xaviervp
> "Scott McNair" wrote:

importing LongText fields

Hi all,
I've got a large db I downloaded in a .SQL format that came originally from
MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
I've got it mostly translated over, but I'm running into a problem with
MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
into problems with fields being too long to enter.
I guess the crux of my question is this: is there any way to go over the
8000-character limitation?
Regards,
Scott McNair"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns95DAA939F3E2Dsfmco@.207.46.248.16...
> Hi all,
> I've got a large db I downloaded in a .SQL format that came originally
> from
> MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
> I've got it mostly translated over, but I'm running into a problem with
> MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
> called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
> into problems with fields being too long to enter.
> I guess the crux of my question is this: is there any way to go over the
> 8000-character limitation?
> Regards,
> Scott McNair
Take a look at text and ntext. You have a 2GB/1GB limit respectively.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
@.TK2MSFTNGP10.phx.gbl:

> Take a look at text and ntext. You have a 2GB/1GB limit respectively.
Perfect. I created a UDDT associating "longtext" with text, and it imports
more-or-less perfectly.|||My apology for btting in this reply but I have the same issue where I need t
o
store +- up to 16000 characters in one field and I tried text or ntext but
the max size value is 16, can you tell me how to set up that field to accept
the 16000 i need or more?
Thanks
Xaviervp
"Scott McNair" wrote:

> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
> @.TK2MSFTNGP10.phx.gbl:
>
> Perfect. I created a UDDT associating "longtext" with text, and it import
s
> more-or-less perfectly.
>|||As Rick stated, the max text/ntext/image size is 2GB. You don't need to do
anything special.
The reported 16-byte length is the default text-in-row length. The default
length of 16 will hold the pointer to the separately stored value.
Hope this helps.
Dan Guzman
SQL Server MVP
"xaviervp" <xaviervp@.discussions.microsoft.com> wrote in message
news:66C5708A-B2F2-44ED-BC46-727AFD090EEB@.microsoft.com...[vbcol=seagreen]
> My apology for btting in this reply but I have the same issue where I need
> to
> store +- up to 16000 characters in one field and I tried text or ntext but
> the max size value is 16, can you tell me how to set up that field to
> accept
> the 16000 i need or more?
> Thanks
> Xaviervp
> "Scott McNair" wrote:
>

importing LongText fields

Hi all,
I've got a large db I downloaded in a .SQL format that came originally from
MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
I've got it mostly translated over, but I'm running into a problem with
MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
into problems with fields being too long to enter.
I guess the crux of my question is this: is there any way to go over the
8000-character limitation?
Regards,
Scott McNair"Scott McNair" <scott.mcnair@.sfmco.takethispartout.com> wrote in message
news:Xns95DAA939F3E2Dsfmco@.207.46.248.16...
> Hi all,
> I've got a large db I downloaded in a .SQL format that came originally
> from
> MySQL. I'm trying to alter the code to allow me to run it under MS-SQL.
> I've got it mostly translated over, but I'm running into a problem with
> MySQL's LONGTEXT data type. I created a unique data type (using SQL 2K5)
> called LONGTEXT that I assigned to a VARCHAR(8000), and I'm still running
> into problems with fields being too long to enter.
> I guess the crux of my question is this: is there any way to go over the
> 8000-character limitation?
> Regards,
> Scott McNair
Take a look at text and ntext. You have a 2GB/1GB limit respectively.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
@.TK2MSFTNGP10.phx.gbl:
> Take a look at text and ntext. You have a 2GB/1GB limit respectively.
Perfect. I created a UDDT associating "longtext" with text, and it imports
more-or-less perfectly.|||My apology for btting in this reply but I have the same issue where I need to
store +- up to 16000 characters in one field and I tried text or ntext but
the max size value is 16, can you tell me how to set up that field to accept
the 16000 i need or more?
Thanks
Xaviervp
"Scott McNair" wrote:
> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
> @.TK2MSFTNGP10.phx.gbl:
> > Take a look at text and ntext. You have a 2GB/1GB limit respectively.
> Perfect. I created a UDDT associating "longtext" with text, and it imports
> more-or-less perfectly.
>|||As Rick stated, the max text/ntext/image size is 2GB. You don't need to do
anything special.
The reported 16-byte length is the default text-in-row length. The default
length of 16 will hold the pointer to the separately stored value.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"xaviervp" <xaviervp@.discussions.microsoft.com> wrote in message
news:66C5708A-B2F2-44ED-BC46-727AFD090EEB@.microsoft.com...
> My apology for btting in this reply but I have the same issue where I need
> to
> store +- up to 16000 characters in one field and I tried text or ntext but
> the max size value is 16, can you tell me how to set up that field to
> accept
> the 16000 i need or more?
> Thanks
> Xaviervp
> "Scott McNair" wrote:
>> "Rick Sawtell" <quickening@.msn.com> wrote in news:ODEu8w#9EHA.2568
>> @.TK2MSFTNGP10.phx.gbl:
>> > Take a look at text and ntext. You have a 2GB/1GB limit respectively.
>> Perfect. I created a UDDT associating "longtext" with text, and it
>> imports
>> more-or-less perfectly.sql

Importing DB4 files into SQL Server 2005

I'm not sure this is the right forum for this, but here goes,

I have some DB4 format files. created by the Borland Database engine.

I need to import these into SQL Server 2005.

I can open them with Access 2000 & 2002, but not 2003.

The Import Wizard also fails with the DB4 format, but can read the Access 2000 and 2002 converted files.

The Import Wizard indicates success, but no tables appear in the Server Management Studio.

There must be a way. HELP!!!!

Try getting FOXPRO driver and use DTS to recognise the DB4 files.

Importing DB2 ixf files into SQLServer 2000

Does anybody know of an application / tool that will allow me to import some DB2 ixf format files into a SQL Server 2000 database ?
Many thanksOriginally posted by shopper
Does anybody know of an application / tool that will allow me to import some DB2 ixf format files into a SQL Server 2000 database ?

Many thanks

Hello you can use FoxPro import and from there send it to SQL2000

my two cents

marcos oliva

Importing DB backup file to SQL Server 2005

Hi all,

I'm using shared sql space on a new provider and am trying to import a database backup file - of the format filename.db.

I cannot figure out how to import this file type using the 2005 express tools - I can connect to the database and create and drop tables alright, but I cannot just import the existing database.

I really haven't got the time on my connection to download the fully blown SQL server trail to be able to do this, so can anyone help me out with any easier solutions?

Cheers, Mike

If it is a SQL backup file, then use RESTORE. Check in Books Online about RESTORE -especially the WITH MOVE option.

Importing DB backup file to SQL Server 2005

Hi all,

I'm using shared sql space on a new provider and am trying to import a database backup file - of the format filename.db.

I cannot figure out how to import this file type using the 2005 express tools - I can connect to the database and create and drop tables alright, but I cannot just import the existing database.

I really haven't got the time on my connection to download the fully blown SQL server trail to be able to do this, so can anyone help me out with any easier solutions?

Cheers, Mike

If it is a SQL backup file, then use RESTORE. Check in Books Online about RESTORE -especially the WITH MOVE option.

importing Data to an existing database column from an .xsl file or .cvs file

good morning,

I want to load data that i receive everydays from my customers in .xls file format (excel) or cvs file format, to the database that i have created on this purpose. but when trying to do that whith SSIS; i got an error message .... that i can't import redudant data in my database column.

Best regards.

Can you please post the exact message?

Importing Data through DTS

Hi all,
I have a problem when I import a text delimited data into SQL Server. This happens only with the date. My date format is dd/mm/yyyy in both the text file and regional settings in Windows. I have created a DTS package and schedule the job to run daily. The
imported data in SQL Server show mm/dd/yyyy.
The funny part is when I execute the package directly from DTS, the data were imported without the problem. But when I execute the job in SQL Server Agent, the problem arises. Also this happens only early of the month, from 1st - 12th.
I would appreciate it if anyone can help me in resolving this problem.
ps : My data type for the date column is datetime.
when you execute the job in SQL Server Agent, it runs with the settings on the server.
when you execute the package directly from DTS, it runs with your local settings.
It suggests that the regional settings in Windows on the server are mm/dd/yyyy - which explains why it works until the 12th.
|||Hi Rubes,
I have checked on the server's regional settings, the date format is
'dd/MM/yyyy'.
However, the problem still occurs.
Uskaka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Adding to my query, I have done the data transformation test and the result shows in format 'dd/MM/yyyy' but when inserted, the format goes 'MM/dd/yyyy'.

Importing Data through DTS

Hi all,
I have a problem when I import a text delimited data into SQL Server. This h
appens only with the date. My date format is dd/mm/yyyy in both the text fil
e and regional settings in Windows. I have created a DTS package and schedul
e the job to run daily. The
imported data in SQL Server show mm/dd/yyyy.
The funny part is when I execute the package directly from DTS, the data wer
e imported without the problem. But when I execute the job in SQL Server Age
nt, the problem arises. Also this happens only early of the month, from 1st
- 12th.
I would appreciate it if anyone can help me in resolving this problem.
ps : My data type for the date column is datetime.when you execute the job in SQL Server Agent, it runs with the settings on t
he server.
when you execute the package directly from DTS, it runs with your local sett
ings.
It suggests that the regional settings in Windows on the server are mm/dd/yy
yy - which explains why it works until the 12th.|||Hi Rubes,
I have checked on the server's regional settings, the date format is
'dd/MM/yyyy'.
However, the problem still occurs.
Uskaka
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Adding to my query, I have done the data transformation test and the result
shows in format 'dd/MM/yyyy' but when inserted, the format goes 'MM/dd/yyyy'
.

Importing Data through DTS

Hi all
I have a problem when I import a text delimited data into SQL Server. This happens only with the date. My date format is dd/mm/yyyy in both the text file and regional settings in Windows. I have created a DTS package and schedule the job to run daily. The imported data in SQL Server show mm/dd/yyyy
The funny part is when I execute the package directly from DTS, the data were imported without the problem. But when I execute the job in SQL Server Agent, the problem arises. Also this happens only early of the month, from 1st - 12th
I would appreciate it if anyone can help me in resolving this problem
ps : My data type for the date column is datetime.when you execute the job in SQL Server Agent, it runs with the settings on the server.
when you execute the package directly from DTS, it runs with your local settings.
It suggests that the regional settings in Windows on the server are mm/dd/yyyy - which explains why it works until the 12th.|||Adding to my query, I have done the data transformation test and the result shows in format 'dd/MM/yyyy' but when inserted, the format goes 'MM/dd/yyyy'.

Importing Data into SQL

Anyone know what's the best format for SQL to read when importing data?
XML seems to be the hottest method of late
"Melissa" <anonymous@.discussions.microsoft.com> wrote in message
news:2D2EF699-94E4-4598-A352-4D9FA2458169@.microsoft.com...
> Anyone know what's the best format for SQL to read when importing data?
|||Melissa,
it really depends. If you can onnect to another database, then importing
directly from it (linked servers/DTS) would be the preferred method,
possibly to a staging area initially in SQL Server where the data is
cleaned. If there is no OLEDB provider for the datasource, then you have to
dump out the data and then read it in. If you already understand the schema,
csv is much faster than XML. Loading in data to the DOM is slow, and XML
files are typically much larger than CSV files due to duplication of field
names. On the other hand, there is now a bulk load interfact to XML files
which speed things up, and having schema information for an XML file can
help determine the datatypes, constraints etc needed in SQL.
HTH,
Paul Ibison