importing sql2005 database diagram to word doc

Hi I have a large database diagram and am only displaying the table names and
relationships. When I copy the diagram to the clipboard and paste it to a
word doc the diagram is hard to read because the letering on each table
appears quite small. I tried changing the zoom within management studio
prior to the copy but it has no effect. Any ideas or is there a specific
method of exporting the image other than just copy to clipboard and paste?
thanks.
--
Paul G
Software engineer.If you take a big diagram and squeeze it into a small space, I don't
know how to avoid the text getting small. I know that when I wanted
to print a diagram to a single page the approach I took was to expand
it to 200%, copy it, then past it into Irfanview and print from there.
I could also save it from there as a jpeg. I suppose you could try
that, and then see if placing the jpeg into the Word document works
any better, but I would not hold out much hope.
Roy Harvey
Beacon Falls, CT
On Fri, 25 Apr 2008 09:46:00 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi I have a large database diagram and am only displaying the table names and
>relationships. When I copy the diagram to the clipboard and paste it to a
>word doc the diagram is hard to read because the letering on each table
>appears quite small. I tried changing the zoom within management studio
>prior to the copy but it has no effect. Any ideas or is there a specific
>method of exporting the image other than just copy to clipboard and paste?
>thanks.|||thanks for the information. I ended up just using the table names to gain
some space and was able to move the objects around (closer together) within
SQL Server Management Studio. Have not heard of Irfenview, might have to
check it out.!
--
Paul G
Software engineer.
"Roy Harvey (SQL Server MVP)" wrote:
> If you take a big diagram and squeeze it into a small space, I don't
> know how to avoid the text getting small. I know that when I wanted
> to print a diagram to a single page the approach I took was to expand
> it to 200%, copy it, then past it into Irfanview and print from there.
> I could also save it from there as a jpeg. I suppose you could try
> that, and then see if placing the jpeg into the Word document works
> any better, but I would not hold out much hope.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 25 Apr 2008 09:46:00 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi I have a large database diagram and am only displaying the table names and
> >relationships. When I copy the diagram to the clipboard and paste it to a
> >word doc the diagram is hard to read because the letering on each table
> >appears quite small. I tried changing the zoom within management studio
> >prior to the copy but it has no effect. Any ideas or is there a specific
> >method of exporting the image other than just copy to clipboard and paste?
> >thanks.
>|||On Fri, 25 Apr 2008 14:00:00 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Have not heard of Irfenview, might have to
>check it out.!
Irfanview is a free utility for viewing image files, but it also has
some manipulation ability such as changing resolution. A great
utility, but nothing unique.
Roy Harvey
Beacon Falls, CT

importing MDF database

As a newbie, I'm having problems importing a copy of my SQLServer2k development database from my laptop (via a CD), to my production SQL Server2k database.

I've tried the DTS Import/Export wizard & also the Copy wizard, but can't seem to see the dialog that prompts & identifies the source path & filename.

I've tried setting up an ODBC DSN for the database, but get the same problem.

Coming from an Access background, I've been used to the copying of *.mdb files, and half expected that level of simplicity with SQLS2K - but clearly, I'm wrong and am missing something, which the on-line help isn't able to clarify for me...

Can anyone please give me any pointers for me to get a copy of my development database along with its stored procedures, from my laptop (not networked), to my desktop server. Both use SQLS2k.

Thanks in advance, AlexSo let me get this strait. You want to restore a copy of your database thats are on a CD?|||The easiest for you would be to either copy the MDF file along with LDF file onto a local drive of the server from the CD and use Enterprise Manager (EM) to Attach Database, or create a backup on your laptop and then restore from that backup file.|||Originally posted by rdjabarov
The easiest for you would be to either copy the MDF file along with LDF file onto a local drive of the server from the CD and use Enterprise Manager (EM) to Attach Database, or create a backup on your laptop and then restore from that backup file. ...and remember to mark the files Read/Write again! I've never been bitten by that, but I read about this guy that was once!

-PatP|||This link http://vyaskn.tripod.com/moving_sql_server.htm refers moving database from one system to another, just follow instead moving consider copying the files.

Also refer to the corresponding KBAs which refers to resolve any login issue arises.|||Thanks all very much for your prompt replies. Just as I was applying the suggested "Attach" method, the motherboard in my Dell server went kaput! So I won't be able to try it until the weekend - when I hope to report back success.

Interestingly the link provided in one of the responses, was very useful and included code to automate the attachment within a stored procedure (if necessary). I aim to try the menu-dropdown "manual" attachment first, & see how I get on with that.

All the best - & thanks again
Alex|||Thanks to all who replied & helped (especially Pat, Rdjabarov, & Satya).

I've just had the server's power supply replaced & tried your suggestions - the attach worked fine (from the Enterprise>Actions>AllTasks>Attach)

Best regards, & thanks again
Alexsql

importing from excel

I need to copy the data in two spreadsheets into two
tables in "SQL Server 2000". Below are the details:

One spreadsheet contains the data that needs to be added
to an existing table in SQL server. All field names in the
spreadsheet match the DB table column names.
Another spreadsheet contains the data that needs to be
copied to a new DB table. The table currently does NOT
exist in the DB.

I'm not sure how to accomplish this. ANy help would be
appreciated.use DTS
If its one time work you can just query the Excel tables and inser the data to existing table
or Import and Export data|||They want to append the data, but this is the problem I'm having now...

The first row of the spreadsheet matches the column heading in the table - but when I go to import the spreadsheet, it is showing me THREE seperate tables or views on the Select Source tables/views screen. I don't know why this is if I'm importing from ONE spreadhsheet... unless the spreadsheet is formatted incorrectly. I went ahead and selected the first one and set my destination table and I when I run it, i get an error: Violation of Primary Key constraint 'PK_Faultresolutions' cannot insert duplicate Key in FaultResolutions.

maybe this is somethig simple.. but can anyone help??sql

Importing Error

Hi all,

From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.

Please suggest me to solve the problem

Thanks in advance
Karna

You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.

Right click on a database and choose tasks-> export data

which then lets you copy tables.

I hope that is what you are looking for.

|||Hi,

Thanks for reply,

As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.

My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).

Please suggest me to solve my problem

Thanks in advance
Karna|||

Have you looked at the Copy Objects task available in the SSIS designer?

Thanks.

|||Hi,

Thanks for the reply.

It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.

Thanks in advance
Karna

Importing Error

Hi all,

From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.

Please suggest me to solve the problem

Thanks in advance
Karna

You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.

Right click on a database and choose tasks-> export data

which then lets you copy tables.

I hope that is what you are looking for.

|||Hi,

Thanks for reply,

As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.

My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).

Please suggest me to solve my problem

Thanks in advance
Karna|||

Have you looked at the Copy Objects task available in the SSIS designer?

Thanks.

|||Hi,

Thanks for the reply.

It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.

Thanks in advance
Karna

importing DB from another SQL server

Hello,
I need to set up a development platform at home with a copy of a DB from a
SQL server at a friend's company.
So far, all my attempts at backup/restore or export/import have failed
despite my reading all the documentation I could find (I am a newbie).
Using DTS/Export wizard, I tried to export the DB first to a plain text
file, then to an excells file. After having gone through all the screens,
the export script runs. It exported all the data for close to one hour, but
at the end I only get a 0kb empty text file, or 5kb empty excells file.
Is there a feature on SQL server preventing export (such as for security
reasons)? Then why would the export wizard run for so long, just to output
nothing?
Since it's only for developement, I don't need the latest data yet, so I
burned on CD the .bak backup files previously made, but I couldn't import
nor restore them on the other machine. Depending on what I tried, I had
various error messages, like
* an improper login and password (which should this be: that of the owner
of the DB on the source SQL server, or that of the SQL admin or windows
admin on the target server?),
* an error saying that the backup doesn't belong to the empty DB I try to
restore to...
* I even managed to import a mydbname.bak.dns file with the DTS wizard
(apparently it was a backup of an Access DB, not the SQL Server DB I need),
but the import script runs apparently smoothly for a while, but then the
target DB didn't contain any of the imported tables, only the sys tables.
For the last several weeks, I have tried everything I could think of, read
the docs, but still I am at a loss as to how to import or make a copy on my
system of the BD from the office.
What would be the proper procedure to import/restore the copies of the
backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
2000?
Failing that, I can go back to my friend's office and try again to export
the data, but how do I make sure I don't end up with an empty file?
thank you,
Anguo.

http://www.gnosis-usa.com/
Revolutionary Psychology, White Tantrism, Dream Yoga...
http://www.reuniting.info/
Intimate Relationships, peace and harmony in the couple.
Message posted via http://www.sqlmonster.com
If you do not need the data, why not just script the objects out and run it
against your new server. You can use Enterprise Manager/QueryAnalyser to
script the DDL. You can also use QALite from rac4sql.net for this.
-oj
"Anguo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:86f3fb22900e440bbd05d784d291fc06@.SQLMonster.c om...
> Hello,
> I need to set up a development platform at home with a copy of a DB from a
> SQL server at a friend's company.
> So far, all my attempts at backup/restore or export/import have failed
> despite my reading all the documentation I could find (I am a newbie).
> Using DTS/Export wizard, I tried to export the DB first to a plain text
> file, then to an excells file. After having gone through all the screens,
> the export script runs. It exported all the data for close to one hour,
> but
> at the end I only get a 0kb empty text file, or 5kb empty excells file.
> Is there a feature on SQL server preventing export (such as for security
> reasons)? Then why would the export wizard run for so long, just to output
> nothing?
> Since it's only for developement, I don't need the latest data yet, so I
> burned on CD the .bak backup files previously made, but I couldn't import
> nor restore them on the other machine. Depending on what I tried, I had
> various error messages, like
> * an improper login and password (which should this be: that of the owner
> of the DB on the source SQL server, or that of the SQL admin or windows
> admin on the target server?),
> * an error saying that the backup doesn't belong to the empty DB I try to
> restore to...
> * I even managed to import a mydbname.bak.dns file with the DTS wizard
> (apparently it was a backup of an Access DB, not the SQL Server DB I
> need),
> but the import script runs apparently smoothly for a while, but then the
> target DB didn't contain any of the imported tables, only the sys tables.
> For the last several weeks, I have tried everything I could think of, read
> the docs, but still I am at a loss as to how to import or make a copy on
> my
> system of the BD from the office.
> What would be the proper procedure to import/restore the copies of the
> backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
> 2000?
> Failing that, I can go back to my friend's office and try again to export
> the data, but how do I make sure I don't end up with an empty file?
> thank you,
> Anguo.
>
>
> --
> http://www.gnosis-usa.com/
> Revolutionary Psychology, White Tantrism, Dream Yoga...
> http://www.reuniting.info/
> Intimate Relationships, peace and harmony in the couple.
> --
> Message posted via http://www.sqlmonster.com
|||Thanks, but I do need some data even if it is not up to date, in order to
work with as a sample.
I have been advised to read this article:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...en-us;314546#9
I am looking into it.
I still would like to know how come the export procedure didn't work and I
only got an empty file.
Thank you.
Message posted via http://www.sqlmonster.com
|||> I still would like to know how come the export procedure didn't work and I
> only got an empty file.
I suggest you post that question to the DTS group. There are many many possibilities within DTS, and
you would need to give us information on exactly how you specified the DTS execution.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anguo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:3298983102dd4e1db62952dfbea8e130@.SQLMonster.c om...
> Thanks, but I do need some data even if it is not up to date, in order to
> work with as a sample.
>
> I have been advised to read this article:
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/default...en-us;314546#9
> I am looking into it.
> I still would like to know how come the export procedure didn't work and I
> only got an empty file.
> Thank you.
> --
> Message posted via http://www.sqlmonster.com
|||The best (most reliable, fastest and fewest steps involved) method to
restore a DB on other server is to detach the production MDFs, make a copy
of the MDF files, re-attach the production MDFs, then reattach the MDFs on
your development PC. You don't have to worry about data type conversion,
scripting SPs, re-importing RI data in proper order, etc.
If you want to use DTS, then keep in ming the following. When exporting
to text, you may encounter data type conversion issues. Also, the problem
with Excel is that it supports only 60k rows, and perhaps that is the
problem you are currently having. The best externals file format for DTSing
into is MS Access. It preserves the data types and can contain all the
tables in a single Access database file. Regardless of the export format you
choose, the SPs, Views, etc. need to be scripted sperately.
"Anguo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:86f3fb22900e440bbd05d784d291fc06@.SQLMonster.c om...
> Hello,
> I need to set up a development platform at home with a copy of a DB from a
> SQL server at a friend's company.
> So far, all my attempts at backup/restore or export/import have failed
> despite my reading all the documentation I could find (I am a newbie).
> Using DTS/Export wizard, I tried to export the DB first to a plain text
> file, then to an excells file. After having gone through all the screens,
> the export script runs. It exported all the data for close to one hour,
but
> at the end I only get a 0kb empty text file, or 5kb empty excells file.
> Is there a feature on SQL server preventing export (such as for security
> reasons)? Then why would the export wizard run for so long, just to output
> nothing?
> Since it's only for developement, I don't need the latest data yet, so I
> burned on CD the .bak backup files previously made, but I couldn't import
> nor restore them on the other machine. Depending on what I tried, I had
> various error messages, like
> * an improper login and password (which should this be: that of the owner
> of the DB on the source SQL server, or that of the SQL admin or windows
> admin on the target server?),
> * an error saying that the backup doesn't belong to the empty DB I try to
> restore to...
> * I even managed to import a mydbname.bak.dns file with the DTS wizard
> (apparently it was a backup of an Access DB, not the SQL Server DB I
need),
> but the import script runs apparently smoothly for a while, but then the
> target DB didn't contain any of the imported tables, only the sys tables.
> For the last several weeks, I have tried everything I could think of, read
> the docs, but still I am at a loss as to how to import or make a copy on
my
> system of the BD from the office.
> What would be the proper procedure to import/restore the copies of the
> backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
> 2000?
> Failing that, I can go back to my friend's office and try again to export
> the data, but how do I make sure I don't end up with an empty file?
> thank you,
> Anguo.
>
>
> --
> http://www.gnosis-usa.com/
> Revolutionary Psychology, White Tantrism, Dream Yoga...
> http://www.reuniting.info/
> Intimate Relationships, peace and harmony in the couple.
> --
> Message posted via http://www.sqlmonster.com

importing DB from another SQL server

Hello,
I need to set up a development platform at home with a copy of a DB from a
SQL server at a friend's company.
So far, all my attempts at backup/restore or export/import have failed
despite my reading all the documentation I could find (I am a newbie).
Using DTS/Export wizard, I tried to export the DB first to a plain text
file, then to an excells file. After having gone through all the screens,
the export script runs. It exported all the data for close to one hour, but
at the end I only get a 0kb empty text file, or 5kb empty excells file.
Is there a feature on SQL server preventing export (such as for security
reasons)? Then why would the export wizard run for so long, just to output
nothing?
Since it's only for developement, I don't need the latest data yet, so I
burned on CD the .bak backup files previously made, but I couldn't import
nor restore them on the other machine. Depending on what I tried, I had
various error messages, like
* an improper login and password (which should this be: that of the owner
of the DB on the source SQL server, or that of the SQL admin or windows
admin on the target server?),
* an error saying that the backup doesn't belong to the empty DB I try to
restore to...
* I even managed to import a mydbname.bak.dns file with the DTS wizard
(apparently it was a backup of an Access DB, not the SQL Server DB I need),
but the import script runs apparently smoothly for a while, but then the
target DB didn't contain any of the imported tables, only the sys tables.
For the last several weeks, I have tried everything I could think of, read
the docs, but still I am at a loss as to how to import or make a copy on my
system of the BD from the office.
What would be the proper procedure to import/restore the copies of the
backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
2000?
Failing that, I can go back to my friend's office and try again to export
the data, but how do I make sure I don't end up with an empty file?
thank you,
Anguo.
--
http://www.gnosis-usa.com/
Revolutionary Psychology, White Tantrism, Dream Yoga...
http://www.reuniting.info/
Intimate Relationships, peace and harmony in the couple.
--
Message posted via http://www.sqlmonster.comIf you do not need the data, why not just script the objects out and run it
against your new server. You can use Enterprise Manager/QueryAnalyser to
script the DDL. You can also use QALite from rac4sql.net for this.
--
-oj
"Anguo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:86f3fb22900e440bbd05d784d291fc06@.SQLMonster.com...
> Hello,
> I need to set up a development platform at home with a copy of a DB from a
> SQL server at a friend's company.
> So far, all my attempts at backup/restore or export/import have failed
> despite my reading all the documentation I could find (I am a newbie).
> Using DTS/Export wizard, I tried to export the DB first to a plain text
> file, then to an excells file. After having gone through all the screens,
> the export script runs. It exported all the data for close to one hour,
> but
> at the end I only get a 0kb empty text file, or 5kb empty excells file.
> Is there a feature on SQL server preventing export (such as for security
> reasons)? Then why would the export wizard run for so long, just to output
> nothing?
> Since it's only for developement, I don't need the latest data yet, so I
> burned on CD the .bak backup files previously made, but I couldn't import
> nor restore them on the other machine. Depending on what I tried, I had
> various error messages, like
> * an improper login and password (which should this be: that of the owner
> of the DB on the source SQL server, or that of the SQL admin or windows
> admin on the target server?),
> * an error saying that the backup doesn't belong to the empty DB I try to
> restore to...
> * I even managed to import a mydbname.bak.dns file with the DTS wizard
> (apparently it was a backup of an Access DB, not the SQL Server DB I
> need),
> but the import script runs apparently smoothly for a while, but then the
> target DB didn't contain any of the imported tables, only the sys tables.
> For the last several weeks, I have tried everything I could think of, read
> the docs, but still I am at a loss as to how to import or make a copy on
> my
> system of the BD from the office.
> What would be the proper procedure to import/restore the copies of the
> backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
> 2000?
> Failing that, I can go back to my friend's office and try again to export
> the data, but how do I make sure I don't end up with an empty file?
> thank you,
> Anguo.
>
>
> --
> http://www.gnosis-usa.com/
> Revolutionary Psychology, White Tantrism, Dream Yoga...
> http://www.reuniting.info/
> Intimate Relationships, peace and harmony in the couple.
> --
> Message posted via http://www.sqlmonster.com|||Thanks, but I do need some data even if it is not up to date, in order to
work with as a sample.
I have been advised to read this article:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546#9
I am looking into it.
I still would like to know how come the export procedure didn't work and I
only got an empty file.
Thank you.
--
Message posted via http://www.sqlmonster.com|||> I still would like to know how come the export procedure didn't work and I
> only got an empty file.
I suggest you post that question to the DTS group. There are many many possibilities within DTS, and
you would need to give us information on exactly how you specified the DTS execution.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anguo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:3298983102dd4e1db62952dfbea8e130@.SQLMonster.com...
> Thanks, but I do need some data even if it is not up to date, in order to
> work with as a sample.
>
> I have been advised to read this article:
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546#9
> I am looking into it.
> I still would like to know how come the export procedure didn't work and I
> only got an empty file.
> Thank you.
> --
> Message posted via http://www.sqlmonster.com|||The best (most reliable, fastest and fewest steps involved) method to
restore a DB on other server is to detach the production MDFs, make a copy
of the MDF files, re-attach the production MDFs, then reattach the MDFs on
your development PC. You don't have to worry about data type conversion,
scripting SPs, re-importing RI data in proper order, etc.
If you want to use DTS, then keep in ming the following. When exporting
to text, you may encounter data type conversion issues. Also, the problem
with Excel is that it supports only 60k rows, and perhaps that is the
problem you are currently having. The best externals file format for DTSing
into is MS Access. It preserves the data types and can contain all the
tables in a single Access database file. Regardless of the export format you
choose, the SPs, Views, etc. need to be scripted sperately.
"Anguo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:86f3fb22900e440bbd05d784d291fc06@.SQLMonster.com...
> Hello,
> I need to set up a development platform at home with a copy of a DB from a
> SQL server at a friend's company.
> So far, all my attempts at backup/restore or export/import have failed
> despite my reading all the documentation I could find (I am a newbie).
> Using DTS/Export wizard, I tried to export the DB first to a plain text
> file, then to an excells file. After having gone through all the screens,
> the export script runs. It exported all the data for close to one hour,
but
> at the end I only get a 0kb empty text file, or 5kb empty excells file.
> Is there a feature on SQL server preventing export (such as for security
> reasons)? Then why would the export wizard run for so long, just to output
> nothing?
> Since it's only for developement, I don't need the latest data yet, so I
> burned on CD the .bak backup files previously made, but I couldn't import
> nor restore them on the other machine. Depending on what I tried, I had
> various error messages, like
> * an improper login and password (which should this be: that of the owner
> of the DB on the source SQL server, or that of the SQL admin or windows
> admin on the target server?),
> * an error saying that the backup doesn't belong to the empty DB I try to
> restore to...
> * I even managed to import a mydbname.bak.dns file with the DTS wizard
> (apparently it was a backup of an Access DB, not the SQL Server DB I
need),
> but the import script runs apparently smoothly for a while, but then the
> target DB didn't contain any of the imported tables, only the sys tables.
> For the last several weeks, I have tried everything I could think of, read
> the docs, but still I am at a loss as to how to import or make a copy on
my
> system of the BD from the office.
> What would be the proper procedure to import/restore the copies of the
> backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
> 2000?
> Failing that, I can go back to my friend's office and try again to export
> the data, but how do I make sure I don't end up with an empty file?
> thank you,
> Anguo.
>
>
> --
> http://www.gnosis-usa.com/
> Revolutionary Psychology, White Tantrism, Dream Yoga...
> http://www.reuniting.info/
> Intimate Relationships, peace and harmony in the couple.
> --
> Message posted via http://www.sqlmonster.com

importing DB from another SQL server

Hello,
I need to set up a development platform at home with a copy of a DB from a
SQL server at a friend's company.
So far, all my attempts at backup/restore or export/import have failed
despite my reading all the documentation I could find (I am a newbie).
Using DTS/Export wizard, I tried to export the DB first to a plain text
file, then to an excells file. After having gone through all the screens,
the export script runs. It exported all the data for close to one hour, but
at the end I only get a 0kb empty text file, or 5kb empty excells file.
Is there a feature on SQL server preventing export (such as for security
reasons)? Then why would the export wizard run for so long, just to output
nothing?
Since it's only for developement, I don't need the latest data yet, so I
burned on CD the .bak backup files previously made, but I couldn't import
nor restore them on the other machine. Depending on what I tried, I had
various error messages, like
* an improper login and password (which should this be: that of the owner
of the DB on the source SQL server, or that of the SQL admin or windows
admin on the target server?),
* an error saying that the backup doesn't belong to the empty DB I try to
restore to...
* I even managed to import a mydbname.bak.dns file with the DTS wizard
(apparently it was a backup of an Access DB, not the SQL Server DB I need),
but the import script runs apparently smoothly for a while, but then the
target DB didn't contain any of the imported tables, only the sys tables.
For the last several weeks, I have tried everything I could think of, read
the docs, but still I am at a loss as to how to import or make a copy on my
system of the BD from the office.
What would be the proper procedure to import/restore the copies of the
backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
2000?
Failing that, I can go back to my friend's office and try again to export
the data, but how do I make sure I don't end up with an empty file?
thank you,
Anguo.
http://www.gnosis-usa.com/
Revolutionary Psychology, White Tantrism, Dream Yoga...
http://www.reuniting.info/
Intimate Relationships, peace and harmony in the couple.
Message posted via http://www.droptable.comIf you do not need the data, why not just script the objects out and run it
against your new server. You can use Enterprise Manager/QueryAnalyser to
script the DDL. You can also use QALite from rac4sql.net for this.
-oj
"Anguo via droptable.com" <forum@.droptable.com> wrote in message
news:86f3fb22900e440bbd05d784d291fc06@.SQ
droptable.com...
> Hello,
> I need to set up a development platform at home with a copy of a DB from a
> SQL server at a friend's company.
> So far, all my attempts at backup/restore or export/import have failed
> despite my reading all the documentation I could find (I am a newbie).
> Using DTS/Export wizard, I tried to export the DB first to a plain text
> file, then to an excells file. After having gone through all the screens,
> the export script runs. It exported all the data for close to one hour,
> but
> at the end I only get a 0kb empty text file, or 5kb empty excells file.
> Is there a feature on SQL server preventing export (such as for security
> reasons)? Then why would the export wizard run for so long, just to output
> nothing?
> Since it's only for developement, I don't need the latest data yet, so I
> burned on CD the .bak backup files previously made, but I couldn't import
> nor restore them on the other machine. Depending on what I tried, I had
> various error messages, like
> * an improper login and password (which should this be: that of the owner
> of the DB on the source SQL server, or that of the SQL admin or windows
> admin on the target server?),
> * an error saying that the backup doesn't belong to the empty DB I try to
> restore to...
> * I even managed to import a mydbname.bak.dns file with the DTS wizard
> (apparently it was a backup of an Access DB, not the SQL Server DB I
> need),
> but the import script runs apparently smoothly for a while, but then the
> target DB didn't contain any of the imported tables, only the sys tables.
> For the last several weeks, I have tried everything I could think of, read
> the docs, but still I am at a loss as to how to import or make a copy on
> my
> system of the BD from the office.
> What would be the proper procedure to import/restore the copies of the
> backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
> 2000?
> Failing that, I can go back to my friend's office and try again to export
> the data, but how do I make sure I don't end up with an empty file?
> thank you,
> Anguo.
>
>
> --
> http://www.gnosis-usa.com/
> Revolutionary Psychology, White Tantrism, Dream Yoga...
> http://www.reuniting.info/
> Intimate Relationships, peace and harmony in the couple.
> --
> Message posted via http://www.droptable.com|||Thanks, but I do need some data even if it is not up to date, in order to
work with as a sample.
I have been advised to read this article:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...;en-us;314546#9
I am looking into it.
I still would like to know how come the export procedure didn't work and I
only got an empty file.
Thank you.
Message posted via http://www.droptable.com|||> I still would like to know how come the export procedure didn't work and I
> only got an empty file.
I suggest you post that question to the DTS group. There are many many possi
bilities within DTS, and
you would need to give us information on exactly how you specified the DTS e
xecution.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anguo via droptable.com" <forum@.droptable.com> wrote in message
news:3298983102dd4e1db62952dfbea8e130@.SQ
droptable.com...
> Thanks, but I do need some data even if it is not up to date, in order to
> work with as a sample.
>
> I have been advised to read this article:
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/defaul...;en-us;314546#9
> I am looking into it.
> I still would like to know how come the export procedure didn't work and I
> only got an empty file.
> Thank you.
> --
> Message posted via http://www.droptable.com|||The best (most reliable, fastest and fewest steps involved) method to
restore a DB on other server is to detach the production MDFs, make a copy
of the MDF files, re-attach the production MDFs, then reattach the MDFs on
your development PC. You don't have to worry about data type conversion,
scripting SPs, re-importing RI data in proper order, etc.
If you want to use DTS, then keep in ming the following. When exporting
to text, you may encounter data type conversion issues. Also, the problem
with Excel is that it supports only 60k rows, and perhaps that is the
problem you are currently having. The best externals file format for DTSing
into is MS Access. It preserves the data types and can contain all the
tables in a single Access database file. Regardless of the export format you
choose, the SPs, Views, etc. need to be scripted sperately.
"Anguo via droptable.com" <forum@.droptable.com> wrote in message
news:86f3fb22900e440bbd05d784d291fc06@.SQ
droptable.com...
> Hello,
> I need to set up a development platform at home with a copy of a DB from a
> SQL server at a friend's company.
> So far, all my attempts at backup/restore or export/import have failed
> despite my reading all the documentation I could find (I am a newbie).
> Using DTS/Export wizard, I tried to export the DB first to a plain text
> file, then to an excells file. After having gone through all the screens,
> the export script runs. It exported all the data for close to one hour,
but
> at the end I only get a 0kb empty text file, or 5kb empty excells file.
> Is there a feature on SQL server preventing export (such as for security
> reasons)? Then why would the export wizard run for so long, just to output
> nothing?
> Since it's only for developement, I don't need the latest data yet, so I
> burned on CD the .bak backup files previously made, but I couldn't import
> nor restore them on the other machine. Depending on what I tried, I had
> various error messages, like
> * an improper login and password (which should this be: that of the owner
> of the DB on the source SQL server, or that of the SQL admin or windows
> admin on the target server?),
> * an error saying that the backup doesn't belong to the empty DB I try to
> restore to...
> * I even managed to import a mydbname.bak.dns file with the DTS wizard
> (apparently it was a backup of an Access DB, not the SQL Server DB I
need),
> but the import script runs apparently smoothly for a while, but then the
> target DB didn't contain any of the imported tables, only the sys tables.
> For the last several weeks, I have tried everything I could think of, read
> the docs, but still I am at a loss as to how to import or make a copy on
my
> system of the BD from the office.
> What would be the proper procedure to import/restore the copies of the
> backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
> 2000?
> Failing that, I can go back to my friend's office and try again to export
> the data, but how do I make sure I don't end up with an empty file?
> thank you,
> Anguo.
>
>
> --
> http://www.gnosis-usa.com/
> Revolutionary Psychology, White Tantrism, Dream Yoga...
> http://www.reuniting.info/
> Intimate Relationships, peace and harmony in the couple.
> --
> Message posted via http://www.droptable.com

Importing databases in SQL 2005

I'm having a lot of trouble importing/moving databases between SQL2005
servers.
-If i detach/copy files/attach, I get user security problems (ie. user
defined in database "does not exist" on new server, but cannot be removed
from database as owns schemas etc.)
-Same with Backup/restore
-If I try to copy or import data after creating a "blank" database with only
the user ID in question as dbo, I lose primary keys.
All actions are being performed through Management Console with the SQL
admin ID. Most databases are running in 2000 compatability mode following the
recent upgrades. Servers are not on Active Directory.
Can someone give me some hints about how to migrate databases?
Seems that some Login of the Source Server is not existing on the Target
Server. If it is so, then first create those Logins on the Target Server and
then try the Backup/Restore method.
"The Vogon" wrote:

> I'm having a lot of trouble importing/moving databases between SQL2005
> servers.
> -If i detach/copy files/attach, I get user security problems (ie. user
> defined in database "does not exist" on new server, but cannot be removed
> from database as owns schemas etc.)
> -Same with Backup/restore
> -If I try to copy or import data after creating a "blank" database with only
> the user ID in question as dbo, I lose primary keys.
> All actions are being performed through Management Console with the SQL
> admin ID. Most databases are running in 2000 compatability mode following the
> recent upgrades. Servers are not on Active Directory.
> Can someone give me some hints about how to migrate databases?
|||Thanks for the reply... I'll try that again and let you know...
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Seems that some Login of the Source Server is not existing on the Target
> Server. If it is so, then first create those Logins on the Target Server and
> then try the Backup/Restore method.
> "The Vogon" wrote:

Importing databases in SQL 2005

I'm having a lot of trouble importing/moving databases between SQL2005
servers.
-If i detach/copy files/attach, I get user security problems (ie. user
defined in database "does not exist" on new server, but cannot be removed
from database as owns schemas etc.)
-Same with Backup/restore
-If I try to copy or import data after creating a "blank" database with only
the user ID in question as dbo, I lose primary keys.
All actions are being performed through Management Console with the SQL
admin ID. Most databases are running in 2000 compatability mode following th
e
recent upgrades. Servers are not on Active Directory.
Can someone give me some hints about how to migrate databases?Seems that some Login of the Source Server is not existing on the Target
Server. If it is so, then first create those Logins on the Target Server and
then try the Backup/Restore method.
"The Vogon" wrote:

> I'm having a lot of trouble importing/moving databases between SQL2005
> servers.
> -If i detach/copy files/attach, I get user security problems (ie. user
> defined in database "does not exist" on new server, but cannot be removed
> from database as owns schemas etc.)
> -Same with Backup/restore
> -If I try to copy or import data after creating a "blank" database with on
ly
> the user ID in question as dbo, I lose primary keys.
> All actions are being performed through Management Console with the SQL
> admin ID. Most databases are running in 2000 compatability mode following
the
> recent upgrades. Servers are not on Active Directory.
> Can someone give me some hints about how to migrate databases?|||Thanks for the reply... I'll try that again and let you know...
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Seems that some Login of the Source Server is not existing on the Target
> Server. If it is so, then first create those Logins on the Target Server a
nd
> then try the Backup/Restore method.
> "The Vogon" wrote:
>

Importing databases in SQL 2005

I'm having a lot of trouble importing/moving databases between SQL2005
servers.
-If i detach/copy files/attach, I get user security problems (ie. user
defined in database "does not exist" on new server, but cannot be removed
from database as owns schemas etc.)
-Same with Backup/restore
-If I try to copy or import data after creating a "blank" database with only
the user ID in question as dbo, I lose primary keys.
All actions are being performed through Management Console with the SQL
admin ID. Most databases are running in 2000 compatability mode following the
recent upgrades. Servers are not on Active Directory.
Can someone give me some hints about how to migrate databases?Seems that some Login of the Source Server is not existing on the Target
Server. If it is so, then first create those Logins on the Target Server and
then try the Backup/Restore method.
"The Vogon" wrote:
> I'm having a lot of trouble importing/moving databases between SQL2005
> servers.
> -If i detach/copy files/attach, I get user security problems (ie. user
> defined in database "does not exist" on new server, but cannot be removed
> from database as owns schemas etc.)
> -Same with Backup/restore
> -If I try to copy or import data after creating a "blank" database with only
> the user ID in question as dbo, I lose primary keys.
> All actions are being performed through Management Console with the SQL
> admin ID. Most databases are running in 2000 compatability mode following the
> recent upgrades. Servers are not on Active Directory.
> Can someone give me some hints about how to migrate databases?|||Thanks for the reply... I'll try that again and let you know...
"Absar Ahmad" wrote:
> Seems that some Login of the Source Server is not existing on the Target
> Server. If it is so, then first create those Logins on the Target Server and
> then try the Backup/Restore method.
> "The Vogon" wrote:
> > I'm having a lot of trouble importing/moving databases between SQL2005
> > servers.
> > -If i detach/copy files/attach, I get user security problems (ie. user
> > defined in database "does not exist" on new server, but cannot be removed
> > from database as owns schemas etc.)
> > -Same with Backup/restore
> > -If I try to copy or import data after creating a "blank" database with only
> > the user ID in question as dbo, I lose primary keys.
> >
> > All actions are being performed through Management Console with the SQL
> > admin ID. Most databases are running in 2000 compatability mode following the
> > recent upgrades. Servers are not on Active Directory.
> >
> > Can someone give me some hints about how to migrate databases?

Importing database to an offline machine.

I have an SQL server 2005 database on my laptop and i want to copy it to my machine at wotk. The two machine can't be connected other than through internet. I am unable to figure out a way to do this. Anyone has any ideas ?!?! Thanks !!

Hi,

You could do an object transfer with DTS or SSIS to the target machine, but that would imply opening some ports to your work machine which is (in the most cases) hardly possible. The best way to do a data transfer is either to detach your db , copy it over and attach it to the working machine OR backup your database copy it over and restore it again on the target system.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Importing Data Into XML Column - UPDATED

Okay...I as actually able to get fairly far in my attmepts to copy data from a SQL Server table to an XML column. Here is the XSD I created:

<xsdchema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="CustomColumns">

<xsd:complexType>

<xsd:complexContent>

<xsd:restriction base="xsd:anyType">

<xsdequence>

<xsd:element name="CN_CUST_KEY" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsd:float" />

</xsdimpleType>

</xsd:element>

<xsd:element name="ITM_SUF_NO" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsd:float" />

</xsdimpleType>

</xsd:element>

<xsd:element name="Model" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsdtring" />

</xsdimpleType>

</xsd:element>

<xsd:element name="Serial" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsdtring" />

</xsdimpleType>

</xsd:element>

<xsd:element name="SaleYear" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsd:int" />

</xsdimpleType>

</xsd:element>

<xsd:element name="SaleDate" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsdateTime" />

</xsdimpleType>

</xsd:element>

<xsd:element name="prd_itm_no" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsd:int" />

</xsdimpleType>

</xsd:element>

</xsdequence>

</xsd:restriction>

</xsd:complexContent>

</xsd:complexType>

</xsd:element>

</xsdchema>

This is the SQL query I'm running against the source table:

INSERT INTO [clmnt]

(

name1,

addr1,

addr2,

city,

[state],

zip5,

zip4,

CustomColumns)

SELECT

LastName,

StreetAddress1,

StreetAddress2,

City,

[State],

Postal5,

Postal4,

(SELECT CN_CUST_KEY,

ITM_SUF_NO,

Model,

Serial,

SaleYear,

SaleDate,

prd_itm_no

FROM TblClass_Customers FOR XML PATH (''), ELEMENTS, ROOT('CustomColumns'), TYPE)

FROM TblClass_Customers;

...and here is the error I'm getting:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]

From what I can see, the SELECT statement matches up with the XSD I'm using...the datatypes coincide...

Kind of at a loss here...

This is what the contents of the target xml column should look like:

<CustomColumns>

<CN_CUST_KEY>XXXYYYZZZZ</CN_CUST_KEY>

<ITM_SUF_NO>45</ITM_SUF_NO>

<Model>alpha</Model>

<Serial>djdjxh3455skx</Serial>

<SaleYear>2005</SaleYear>

<SaleDate>10/25</SaleDate>

<prd_itm_no>df345f</prd_itm_no>

</CustomColumns>

One thing I did find...if I just run the portion of the query that selects the columns for my XML column, the query runs, but doesn't give the expected results. It concatenates everything into one long string...

<CustomColumns><row><CN_CUST_KEY>1000422</CN_CUST_KEY><ITM_SUF_NO>9</ITM_SUF_NO><Model>11073954200</Model><SaleYear>2003</SaleYear><SaleDate>2003-08-23 00:00:00</SaleDate><prd_itm_no>73954</prd_itm_no></row><row><CN_CUST_KEY>1000812</CN_CUST_KEY><ITM_SUF_NO>13</ITM_SUF_NO><Model>11063932101</Model><Serial>MM2610444</Serial><SaleYear>2002</SaleYear><SaleDate>2002-08-26 00:00:00</SaleDate><prd_itm_no>63932</prd_itm_no></row><row><CN_CUST_KEY>1001610</CN_CUST_KEY><ITM_SUF_NO>14</ITM_SUF_NO><Model>11062952100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-03 00:00:00</SaleDate><prd_itm_no>62952</prd_itm_no></row><row><CN_CUST_KEY>1004682</CN_CUST_KEY><ITM_SUF_NO>15</ITM_SUF_NO><Model>11072932100</Model><Serial>ML140325</Serial><SaleYear>2001</SaleYear><SaleDate>2001-04-21 00:00:00</SaleDate><prd_itm_no>72932</prd_itm_no></row><row><CN_CUST_KEY>1004867</CN_CUST_KEY><ITM_SUF_NO>12</ITM_SUF_NO><Model>11073952200</Model><SaleYear>2003</SaleYear><SaleDate>2003-04-08 00:00:00</SaleDate><prd_itm_no>73952</prd_itm_no></row><row><CN_CUST_KEY>1005117</CN_CUST_KEY><ITM_SUF_NO>7</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-18 00:00:00</SaleDate><prd_itm_no>72972</prd_itm_no></row><row><CN_CUST_KEY>1005320</CN_CUST_KEY><ITM_SUF_NO>27</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2001</SaleYear><SaleDate>2001-08-28 -07-01 00:00:00</SaleDate><prd_itm_no>73942</prd_itm_no></row><row><CN_CUST_KEY>5806620</CN_CUST_KEY><ITM_SUF_NO>2</ITM_SUF_NO><Model>11062952100</Model><Serial>ML4334523</Serial><SaleYear>2002</SaleYear><SaleDate>2002-02-17

It also is inserting the <row> tag as opposed to actually creating an entirely new row. And lastly, the <CustomColumns> tag should encapsulate/wrap each record. When I run the select, every single row is contained within <CustomColumns>...

|||

You should have PK or unique columns in the table tblClass_Customers right? Let's assume it's CustomerID. Try change to this:

.....

FROM TblClass_Customers T1 WHERE T1.CustomerID=T2.CustomerID

FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)

FROM TblClass_Customers T2;

|||

Thanks a million!!!

Do you think you could just real quick explain to me what it is that made your chnges do the trick?

I'm learning from this whole XML experience...

Thanks again!!

|||

Phe:

The SELECT alone works fine, but when coupled with the INSERT it blows up:

Code Snippet

INSERT INTO [clmnt]

(

name1,

addr1,

addr2,

city,

[state],

zip5,

zip4,

CustomColumns

)

SELECT

LastName,

StreetAddress1,

StreetAddress2,

City,

[State],

Postal5,

Postal4,

(SELECT CN_CUST_KEY,

ITM_SUF_NO,

Model,

Serial,

SaleYear,

SaleDate,

prd_itm_no

FROM TblClass_Customers2 T1 WHERE T1.CN_CUST_KEY = T2.CN_CUST_KEY

FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)

FROM TblClass_Customers2 T2

The validation error again:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]

|||

I figured out hat the issue is, but not sure how to address it...

If a column that I'm using to generate my XML is NULL, the element is ignored...that's causing the XML to fail validation.

How would you tell the query to select the column/element even if there is no value or NULL in the source column?

I know there's the whole ELEMENTS XMISIL directive, but I don't wnat to get all the extra "stuff" it generates...

|||

In your schema, you already set nillable to true for certain columns. SO you can just include XSNIL after the ELEMENT jkey word in the query.

If you don't want to include the element with NULL, change the schema to include minOccurs="0" and remove the nillable attribute.

|||

Phe:

Thanks for the reply....

I've actually tried the XSINIL keyword and saw it did bring in the NULL element. But it also brough along some other stuff - attributes and the header:

Code Snippet

<CustomColumns xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<CN_CUST_KEY>1000422</CN_CUST_KEY>

<ITM_SUF_NO>9</ITM_SUF_NO>

<Model>11073954200</Model>

<Serial xsi:nil="true" />

<SaleYear>2003</SaleYear>

<SaleDate>2003-08-23 00:00:00</SaleDate>

<prd_itm_no>73954</prd_itm_no>

</CustomColumns>

Is there any other way to accomplish this without having to have the attribute and the header there? Or is that the only way? I'm going to assume even if the attribute is there, I would just need to modify my queries to account for that particular attribute...

I've never used XQUERY before, and I've been reading through BOL, but again, it's all Greek to me. How would you formulate a basic query against the XML above. For eample, I just want to select all rows where the <Serial> value is NULL (NIL)...?

Am I making sense...? I hope so!!

Thanks again...

|||

You can check e.g.

Code Snippet

SELECT @.x.query(

'CustomColumns[Serial/@.xsi:nil = true()]'

);

|||

Thanks Martin:

But all that query returned was a NULL - and I'm sure there are at least 1000 rows that have a NULL/NIL element

Last question I swear....!

So what is all the namespace declarations I see in all the examples in BOL? Are those necessary and what purpose do they serve?

For example, I want to return all rows from the CLMNT table where the value of the <serial> element in the CustomColumns XML column is 140325. Here is the query I concocted:

Code Snippet

SELECT clmnt.query('

declare default namespace "http://schemas.microsoft.com/sqlserver/2004/07/EVEN/CustomColumns";

/CustomColumns/Serial

') as Result

FROM [CustomColumns].[Serial]

WHERE Serial = '140325'

I'm sure it's completely way off the mark, but if someone could give some insight that would be great.

Also, does anyone know of some realy good very beginner XQUERY documentation/tutorials? I've been through BOL and frankly it didn't help...

Thanks for tolerating an XML noob...

Importing Data Into XML Column - UPDATED

Okay...I as actually able to get fairly far in my attmepts to copy data from a SQL Server table to an XML column. Here is the XSD I created:

<xsdchema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="CustomColumns">

<xsd:complexType>

<xsd:complexContent>

<xsd:restriction base="xsd:anyType">

<xsdequence>

<xsd:element name="CN_CUST_KEY" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsd:float" />

</xsdimpleType>

</xsd:element>

<xsd:element name="ITM_SUF_NO" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsd:float" />

</xsdimpleType>

</xsd:element>

<xsd:element name="Model" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsdtring" />

</xsdimpleType>

</xsd:element>

<xsd:element name="Serial" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsdtring" />

</xsdimpleType>

</xsd:element>

<xsd:element name="SaleYear" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsd:int" />

</xsdimpleType>

</xsd:element>

<xsd:element name="SaleDate" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsdateTime" />

</xsdimpleType>

</xsd:element>

<xsd:element name="prd_itm_no" nillable="true">

<xsdimpleType>

<xsd:restriction base="xsd:int" />

</xsdimpleType>

</xsd:element>

</xsdequence>

</xsd:restriction>

</xsd:complexContent>

</xsd:complexType>

</xsd:element>

</xsdchema>

This is the SQL query I'm running against the source table:

INSERT INTO [clmnt]

(

name1,

addr1,

addr2,

city,

[state],

zip5,

zip4,

CustomColumns)

SELECT

LastName,

StreetAddress1,

StreetAddress2,

City,

[State],

Postal5,

Postal4,

(SELECT CN_CUST_KEY,

ITM_SUF_NO,

Model,

Serial,

SaleYear,

SaleDate,

prd_itm_no

FROM TblClass_Customers FOR XML PATH (''), ELEMENTS, ROOT('CustomColumns'), TYPE)

FROM TblClass_Customers;

...and here is the error I'm getting:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]

From what I can see, the SELECT statement matches up with the XSD I'm using...the datatypes coincide...

Kind of at a loss here...

This is what the contents of the target xml column should look like:

<CustomColumns>

<CN_CUST_KEY>XXXYYYZZZZ</CN_CUST_KEY>

<ITM_SUF_NO>45</ITM_SUF_NO>

<Model>alpha</Model>

<Serial>djdjxh3455skx</Serial>

<SaleYear>2005</SaleYear>

<SaleDate>10/25</SaleDate>

<prd_itm_no>df345f</prd_itm_no>

</CustomColumns>

One thing I did find...if I just run the portion of the query that selects the columns for my XML column, the query runs, but doesn't give the expected results. It concatenates everything into one long string...

<CustomColumns><row><CN_CUST_KEY>1000422</CN_CUST_KEY><ITM_SUF_NO>9</ITM_SUF_NO><Model>11073954200</Model><SaleYear>2003</SaleYear><SaleDate>2003-08-23 00:00:00</SaleDate><prd_itm_no>73954</prd_itm_no></row><row><CN_CUST_KEY>1000812</CN_CUST_KEY><ITM_SUF_NO>13</ITM_SUF_NO><Model>11063932101</Model><Serial>MM2610444</Serial><SaleYear>2002</SaleYear><SaleDate>2002-08-26 00:00:00</SaleDate><prd_itm_no>63932</prd_itm_no></row><row><CN_CUST_KEY>1001610</CN_CUST_KEY><ITM_SUF_NO>14</ITM_SUF_NO><Model>11062952100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-03 00:00:00</SaleDate><prd_itm_no>62952</prd_itm_no></row><row><CN_CUST_KEY>1004682</CN_CUST_KEY><ITM_SUF_NO>15</ITM_SUF_NO><Model>11072932100</Model><Serial>ML140325</Serial><SaleYear>2001</SaleYear><SaleDate>2001-04-21 00:00:00</SaleDate><prd_itm_no>72932</prd_itm_no></row><row><CN_CUST_KEY>1004867</CN_CUST_KEY><ITM_SUF_NO>12</ITM_SUF_NO><Model>11073952200</Model><SaleYear>2003</SaleYear><SaleDate>2003-04-08 00:00:00</SaleDate><prd_itm_no>73952</prd_itm_no></row><row><CN_CUST_KEY>1005117</CN_CUST_KEY><ITM_SUF_NO>7</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2002</SaleYear><SaleDate>2002-02-18 00:00:00</SaleDate><prd_itm_no>72972</prd_itm_no></row><row><CN_CUST_KEY>1005320</CN_CUST_KEY><ITM_SUF_NO>27</ITM_SUF_NO><Model>11072972100</Model><SaleYear>2001</SaleYear><SaleDate>2001-08-28 -07-01 00:00:00</SaleDate><prd_itm_no>73942</prd_itm_no></row><row><CN_CUST_KEY>5806620</CN_CUST_KEY><ITM_SUF_NO>2</ITM_SUF_NO><Model>11062952100</Model><Serial>ML4334523</Serial><SaleYear>2002</SaleYear><SaleDate>2002-02-17

It also is inserting the <row> tag as opposed to actually creating an entirely new row. And lastly, the <CustomColumns> tag should encapsulate/wrap each record. When I run the select, every single row is contained within <CustomColumns>...

|||

You should have PK or unique columns in the table tblClass_Customers right? Let's assume it's CustomerID. Try change to this:

.....

FROM TblClass_Customers T1 WHERE T1.CustomerID=T2.CustomerID

FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)

FROM TblClass_Customers T2;

|||

Thanks a million!!!

Do you think you could just real quick explain to me what it is that made your chnges do the trick?

I'm learning from this whole XML experience...

Thanks again!!

|||

Phe:

The SELECT alone works fine, but when coupled with the INSERT it blows up:

Code Snippet

INSERT INTO [clmnt]

(

name1,

addr1,

addr2,

city,

[state],

zip5,

zip4,

CustomColumns

)

SELECT

LastName,

StreetAddress1,

StreetAddress2,

City,

[State],

Postal5,

Postal4,

(SELECT CN_CUST_KEY,

ITM_SUF_NO,

Model,

Serial,

SaleYear,

SaleDate,

prd_itm_no

FROM TblClass_Customers2 T1 WHERE T1.CN_CUST_KEY = T2.CN_CUST_KEY

FOR XML PATH ('CustomColumns'), ELEMENTS, TYPE)

FROM TblClass_Customers2 T2

The validation error again:

Msg 6965, Level 16, State 1, Line 1

XML Validation: Invalid content. Expected element(s)erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/*aleYear[1]

|||

I figured out hat the issue is, but not sure how to address it...

If a column that I'm using to generate my XML is NULL, the element is ignored...that's causing the XML to fail validation.

How would you tell the query to select the column/element even if there is no value or NULL in the source column?

I know there's the whole ELEMENTS XMISIL directive, but I don't wnat to get all the extra "stuff" it generates...

|||

In your schema, you already set nillable to true for certain columns. SO you can just include XSNIL after the ELEMENT jkey word in the query.

If you don't want to include the element with NULL, change the schema to include minOccurs="0" and remove the nillable attribute.

|||

Phe:

Thanks for the reply....

I've actually tried the XSINIL keyword and saw it did bring in the NULL element. But it also brough along some other stuff - attributes and the header:

Code Snippet

<CustomColumns xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<CN_CUST_KEY>1000422</CN_CUST_KEY>

<ITM_SUF_NO>9</ITM_SUF_NO>

<Model>11073954200</Model>

<Serial xsi:nil="true" />

<SaleYear>2003</SaleYear>

<SaleDate>2003-08-23 00:00:00</SaleDate>

<prd_itm_no>73954</prd_itm_no>

</CustomColumns>

Is there any other way to accomplish this without having to have the attribute and the header there? Or is that the only way? I'm going to assume even if the attribute is there, I would just need to modify my queries to account for that particular attribute...

I've never used XQUERY before, and I've been reading through BOL, but again, it's all Greek to me. How would you formulate a basic query against the XML above. For eample, I just want to select all rows where the <Serial> value is NULL (NIL)...?

Am I making sense...? I hope so!!

Thanks again...

|||

You can check e.g.

Code Snippet

SELECT @.x.query(

'CustomColumns[Serial/@.xsi:nil = true()]'

);

|||

Thanks Martin:

But all that query returned was a NULL - and I'm sure there are at least 1000 rows that have a NULL/NIL element

Last question I swear....!

So what is all the namespace declarations I see in all the examples in BOL? Are those necessary and what purpose do they serve?

For example, I want to return all rows from the CLMNT table where the value of the <serial> element in the CustomColumns XML column is 140325. Here is the query I concocted:

Code Snippet

SELECT clmnt.query('

declare default namespace "http://schemas.microsoft.com/sqlserver/2004/07/EVEN/CustomColumns";

/CustomColumns/Serial

') as Result

FROM [CustomColumns].[Serial]

WHERE Serial = '140325'

I'm sure it's completely way off the mark, but if someone could give some insight that would be great.

Also, does anyone know of some realy good very beginner XQUERY documentation/tutorials? I've been through BOL and frankly it didn't help...

Thanks for tolerating an XML noob...