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 Views

How do you import a view from one database to another? I tried using the DTS wizard but when I pick the view I want to import, it is imported as a table and not a view. And I don't see anything in Management studio then can help me. Does anyone have any suggestions.

hi,

try generating the DDL script for the desire view, and then run it against the desired database..

regards

sql

Importing stored procedures from 1 db to another?

Hi there,

Can anyone help?

I just recently imported my Sql server express 2005 db into the full version of sql server 2005... All went according to plan (i used import in studio management 2005 vers not 2005 express) and the data was imported but no stored procedures went with it...

it seemed to only include tables and views...

Is there another way round this?

Unfortunatley i don't have FULL access to the SQL Server 2005 as this is at my hosts... but i have full access to the sql server 2005 express which is locally on my machine

Any advice would be really appreciated

Thanks

ian

Several of the easiest ways:

1. backup the database and restore it to your new server

2. In Object Explorer, you can script out the stored procedures and apply them to the new server

3. Use snapshot replication, but I'd stick with #1 or #2 first.

|||

The previous post is correct - the easiest way to make sure you get all objects from one DB to another is to back it up and restore it to another.

If you don't own the other box that might not be possible. You've already got the data, so to get the other objects you can right-click each and script them out. You can also use the scripting model in DMO to do the same thing, but get all of the objects at once. I have a series of tutorials on that starting here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=110

Buck Woody

Carpe Datum

Importing DTS From 2000 to 2005

Hi, I'm getting slightly frustrated...!

I've imported a couple of DTSs from 2k to 2005, now I know that they are stored in MSDB and I know that I need to export them to a file before I can work on them in SSIS.

The problem is:

How do I export them to a flat file? I've looked everywhere and it's driving me nuts!

Also I can't seem to find out how to delete them either?! I can see the data sotred in the dtspackages90 table but how do I edit/get rid of them.

Help!

SQuirreLs_r_nutz wrote:

Hi, I'm getting slightly frustrated...!

I've imported a couple of DTSs from 2k to 2005, now I know that they are stored in MSDB and I know that I need to export them to a file before I can work on them in SSIS.

The problem is:

How do I export them to a flat file? I've looked everywhere and it's driving me nuts!

You need to import the package into an existing SSIS project. Then, in Solution Explorer, right-click on "SSIS Packages" and select "Add Existing Package". From there you'll get a dialog box from which you can import your package.

Also I can't seem to find out how to delete them either?! I can see the data sotred in the dtspackages90 table but how do I edit/get rid of them.

Help!

There are two ways to manage packages: SQL Server Management Studio and Dtutil. Either of these tools can be used to delete packages stored in MSDB.

Importing data from Excel to SQL

I populate SQL tables with excel data and need to have one of the fields be <NULL>.

The field is a DateTime field and when imported places a date of 1900-01-01 00:00:00.000 I need that field to be NULL. Is there a way to represent NULL in excel so when it is importing it is not transformed to a date?

ThanksThe problem is not how your data is represented in Excel, but how it is being imported, or possibly the defaults on your SQL table. How are you importing the Excel data?

blindman

Importing Access Reports

I am trying to import access reports from an Access 2003 database. I am
getting two different errors, and none of the reports are being imported.
The first error is:
An error occurred while the report DailySales was being imported:
Reserved Error
The second error is:
An error occurred while the report Report: Weekly was being imported: The
given path's format is not supported.
I believe the second error is due to the naming convention not allowing ":"
in the name, but I do not know how to fix the first error.
Any suggestions?The first error could be coming from Access. Could you try the following?
Open your database in Microsoft Access. Create a new Module and paste the
following code into it:
Sub Export()
Application.ExportXML ObjectType:=acExportReport, _
DataSource:="DailySales ", _
ImageTarget:=".", _
PresentationTarget:="DailySales .xsl", _
OtherFlags:=acPersistReportML
End Sub
Set the cursor at the beginning of the Sub and click the Run button. If
ExportXML succeeds, it should create a file called "DailySales_report.xml"
in your "My Documents" folder. If you can send the file to me, I'll look at
it. If ExportXML fails, then it's an Access problem. You could try the
Access newsgroups for help.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:68663BEB-7AD6-4A4B-A263-A3D4D86FAE48@.microsoft.com...
>I am trying to import access reports from an Access 2003 database. I am
> getting two different errors, and none of the reports are being imported.
> The first error is:
> An error occurred while the report DailySales was being imported:
> Reserved Error
> The second error is:
> An error occurred while the report Report: Weekly was being imported:
> The
> given path's format is not supported.
> I believe the second error is due to the naming convention not allowing
> ":"
> in the name, but I do not know how to fix the first error.
> Any suggestions?|||Thank you Albert. I know the reports work because they reside in a database
we use on a frequent basis and run the reports. Tommorow when I get into the
office, I will try your solution. If the reports do generate the .xml file,
where should I email it to so you can look at it?
I have a feeling my visual studio .net install is corrupt, so before i try
your solution, I am going to uninstall everything and do a fresh install. I
am also having a problem using mdb files as datasources (visual studio
crashes when adding tables to a query), so maybe my errors are connected. I
did however try creating a blank report with just a label in the header, and
I still got the same error.
Thanks again,
Ben
"Albert Yen [MSFT]" wrote:
> The first error could be coming from Access. Could you try the following?
> Open your database in Microsoft Access. Create a new Module and paste the
> following code into it:
> Sub Export()
> Application.ExportXML ObjectType:=acExportReport, _
> DataSource:="DailySales ", _
> ImageTarget:=".", _
> PresentationTarget:="DailySales .xsl", _
> OtherFlags:=acPersistReportML
> End Sub
> Set the cursor at the beginning of the Sub and click the Run button. If
> ExportXML succeeds, it should create a file called "DailySales_report.xml"
> in your "My Documents" folder. If you can send the file to me, I'll look at
> it. If ExportXML fails, then it's an Access problem. You could try the
> Access newsgroups for help.
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:68663BEB-7AD6-4A4B-A263-A3D4D86FAE48@.microsoft.com...
> >I am trying to import access reports from an Access 2003 database. I am
> > getting two different errors, and none of the reports are being imported.
> >
> > The first error is:
> > An error occurred while the report DailySales was being imported:
> > Reserved Error
> >
> > The second error is:
> > An error occurred while the report Report: Weekly was being imported:
> > The
> > given path's format is not supported.
> >
> > I believe the second error is due to the naming convention not allowing
> > ":"
> > in the name, but I do not know how to fix the first error.
> >
> > Any suggestions?
>
>|||You can just attach the file to your newsgroup post. If you are unable to
do that, you can get my email address from this message. Just remove the
string "online."
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:4D58444F-CD86-4F1B-A028-7096C27A0FEC@.microsoft.com...
> Thank you Albert. I know the reports work because they reside in a
> database
> we use on a frequent basis and run the reports. Tommorow when I get into
> the
> office, I will try your solution. If the reports do generate the .xml
> file,
> where should I email it to so you can look at it?
> I have a feeling my visual studio .net install is corrupt, so before i try
> your solution, I am going to uninstall everything and do a fresh install.
> I
> am also having a problem using mdb files as datasources (visual studio
> crashes when adding tables to a query), so maybe my errors are connected.
> I
> did however try creating a blank report with just a label in the header,
> and
> I still got the same error.
> Thanks again,
> Ben
> "Albert Yen [MSFT]" wrote:
>> The first error could be coming from Access. Could you try the
>> following?
>> Open your database in Microsoft Access. Create a new Module and paste the
>> following code into it:
>> Sub Export()
>> Application.ExportXML ObjectType:=acExportReport, _
>> DataSource:="DailySales ", _
>> ImageTarget:=".", _
>> PresentationTarget:="DailySales .xsl", _
>> OtherFlags:=acPersistReportML
>> End Sub
>> Set the cursor at the beginning of the Sub and click the Run button. If
>> ExportXML succeeds, it should create a file called
>> "DailySales_report.xml"
>> in your "My Documents" folder. If you can send the file to me, I'll look
>> at
>> it. If ExportXML fails, then it's an Access problem. You could try the
>> Access newsgroups for help.
>> --
>> Albert Yen
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
>> news:68663BEB-7AD6-4A4B-A263-A3D4D86FAE48@.microsoft.com...
>> >I am trying to import access reports from an Access 2003 database. I am
>> > getting two different errors, and none of the reports are being
>> > imported.
>> >
>> > The first error is:
>> > An error occurred while the report DailySales was being imported:
>> > Reserved Error
>> >
>> > The second error is:
>> > An error occurred while the report Report: Weekly was being imported:
>> > The
>> > given path's format is not supported.
>> >
>> > I believe the second error is due to the naming convention not allowing
>> > ":"
>> > in the name, but I do not know how to fix the first error.
>> >
>> > Any suggestions?
>>|||It seems the "given path is not supported" error comes from the name having
either semi colons (:) or ampersands (&). Now i get reserved errors and item
already added errors...
"Albert Yen [MSFT]" wrote:
> You can just attach the file to your newsgroup post. If you are unable to
> do that, you can get my email address from this message. Just remove the
> string "online."
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:4D58444F-CD86-4F1B-A028-7096C27A0FEC@.microsoft.com...
> > Thank you Albert. I know the reports work because they reside in a
> > database
> > we use on a frequent basis and run the reports. Tommorow when I get into
> > the
> > office, I will try your solution. If the reports do generate the .xml
> > file,
> > where should I email it to so you can look at it?
> >
> > I have a feeling my visual studio .net install is corrupt, so before i try
> > your solution, I am going to uninstall everything and do a fresh install.
> > I
> > am also having a problem using mdb files as datasources (visual studio
> > crashes when adding tables to a query), so maybe my errors are connected.
> > I
> > did however try creating a blank report with just a label in the header,
> > and
> > I still got the same error.
> >
> > Thanks again,
> > Ben
> >
> > "Albert Yen [MSFT]" wrote:
> >
> >> The first error could be coming from Access. Could you try the
> >> following?
> >> Open your database in Microsoft Access. Create a new Module and paste the
> >> following code into it:
> >>
> >> Sub Export()
> >>
> >> Application.ExportXML ObjectType:=acExportReport, _
> >> DataSource:="DailySales ", _
> >> ImageTarget:=".", _
> >> PresentationTarget:="DailySales .xsl", _
> >> OtherFlags:=acPersistReportML
> >> End Sub
> >>
> >> Set the cursor at the beginning of the Sub and click the Run button. If
> >> ExportXML succeeds, it should create a file called
> >> "DailySales_report.xml"
> >> in your "My Documents" folder. If you can send the file to me, I'll look
> >> at
> >> it. If ExportXML fails, then it's an Access problem. You could try the
> >> Access newsgroups for help.
> >>
> >> --
> >> Albert Yen
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> >> news:68663BEB-7AD6-4A4B-A263-A3D4D86FAE48@.microsoft.com...
> >> >I am trying to import access reports from an Access 2003 database. I am
> >> > getting two different errors, and none of the reports are being
> >> > imported.
> >> >
> >> > The first error is:
> >> > An error occurred while the report DailySales was being imported:
> >> > Reserved Error
> >> >
> >> > The second error is:
> >> > An error occurred while the report Report: Weekly was being imported:
> >> > The
> >> > given path's format is not supported.
> >> >
> >> > I believe the second error is due to the naming convention not allowing
> >> > ":"
> >> > in the name, but I do not know how to fix the first error.
> >> >
> >> > Any suggestions?
> >>
> >>
> >>
>
>