Backup to Network Drive: difficulty

I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.

So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:

http://64.225.154.232/temp_dbDiagram.gif

I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.

SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL,
topic.TOPIC_NAME, topic.TOPIC_URL
FROM tbl_CCF_Headlines headline INNER JOIN
tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN
tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID
WHERE (headline.HEADLINE_DATE IN
(SELECT TOP 3 HEADLINE_DATE
FROM tbl_CCF_HEADLINES
GROUP BY HEADLINE_DATE
ORDER BY HEADLINE_DATE DESC))
ORDER BY headline.HEADLINE_DATE DESCTry to cast youe text column to varchar(1000).|||What if the nText-field contains more than 1000 characters? What if it contains more than 4000 characters which I believe is the limit for nVarChar? I have this very same situation and am yet to find a solution...

-Tuukka

I'm having difficulty searching for an answer to this challenge. Can
someone give me a clue on the right keywords to use to find a
discussion on this subject? All of the ones I saw appear to touch on
older versions. I'm working with SQL Server 2000, and need to backup
and restore only tables, because the full backup/restore appears to
mess up users/security, or something or another.

Thanks for the assist."javelin" <google.1.jvmail@.spamgourmet.comwrote in message
news:1169944694.026131.168800@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

There really isn't. You backup and restore an entire database. Unless
you're using filegroups, etc.

However, most likely what you need is sp_change_users_login to reconcile the
users in the database with the logins on the server.

Check that out. It will most likely solve your problems.

Quote:

Originally Posted by

>
Thanks for the assist.
>

|||javelin (google.1.jvmail@.spamgourmet.com) writes:

Quote:

Originally Posted by

You cannot backup and restore individual tables. SQL 6.5 had such a
feature, but thankfully this folly was dropped.

You can however backup and restore single filegroups.

But it is not likely to be the solution to your problem. BACKUP/RESTORE
as such does not mess up users, but if you restore a backup on a different
server, you lose the mapping between database users and server logins.
Obviously - the logins in two servers are likely to be different.

As Greg said, user sp_change_users_login to sort out the situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||There are two requirements here, the first being the need to restore
tables to a different server than the backup originated from without
messing up mapping of server logins. The second is the need for a user
to work remotely on one single table without implementing fancy
database features, such as replication. Why not? Because the DBA is a
real "P.I.T.A.", and there's no way to convince him he's wrong! I did
discover one script to "generate INSERT statements from the existing
data" (found here: http://vyaskn.tripod.com/code.htm#inserts).
However, this one doesn't work well with tables with many columns, and
my table is definitely a "many-columned" table. If someone has
experience with fixing this script to be more flexible and suit my
needs, I could use it to have the remote user modify records in the
table and have the insert scripts generated from this code.

Thanks again for further advice.

On Jan 28, 4:34 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

javelin (google.1.jvm...@.spamgourmet.com) writes:

Quote:

Originally Posted by

feature, but thankfully this folly was dropped.
>
You can however backup and restore single filegroups.
>
But it is not likely to be the solution to your problem. BACKUP/RESTORE
as such does not mess up users, but if you restore a backup on a different
server, you lose the mapping between database users and server logins.
Obviously - the logins in two servers are likely to be different.
>
As Greg said, user sp_change_users_login to sort out the situation.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||javelin (google.1.jvmail@.spamgourmet.com) writes:

Quote:

Originally Posted by

There are two requirements here, the first being the need to restore
tables to a different server than the backup originated from without
messing up mapping of server logins.

As I said, this can be handled with sp_change_users_login, please
see Books Online for details.

Quote:

Originally Posted by

The second is the need for a user to work remotely on one single table
without implementing fancy database features, such as replication.

Have you looked at bulk copy? There is also some Import/Export GUI
stuff in Enterprise Manager that I have never used myself.

Quote:

Originally Posted by

Why not? Because the DBA is a real "P.I.T.A.", and there's no way to
convince him he's wrong!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Since you rule out the traditional methods for BACKUP/RESTORE, then maybe
you can take a look at linked servers (that is if you have direct
connectivity between the two servers, which I assume you do because you
mention replication). You can set up a remote linked server and then
directly query and transfer the tables that you need. It can be easily
automated via a stored procedure that can be scheduled to run as a job.
Simple enough. Not the fastest approach but seems you are already looking at
generating INSERT statements to dump the data out...

Another alternative is to use a DTS package to transfer the tables. It can
be also fully automated, but assumes as above that you have connectivity
between the servers.

If you do not have direct connectivity then you can use a similar approach
to dumping with INSERT statements, but via DTS. In essence in the source
server you can create a DTS task to dump the table data to a text file, then
on the destination server import the data to a table using the reverse
process. The same scenario is doable using the BCP utility.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Plamen:

Thanks for the interesting advice. I am going to try and get direct
connectivity to the target server. If I can, the copy tables DTS
function is nice and straightforward. I don't know if the BCP utility
has been tried, but I'll give that a shot as well.

Thanks also to Erland Sommarskog for all your great advice. I'll be
experimenting with options for a few days.

Thanks again.

On Jan 29, 11:06 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Since you rule out the traditional methods for BACKUP/RESTORE, then maybe
you can take a look at linked servers (that is if you have direct
connectivity between the two servers, which I assume you do because you
mention replication). You can set up a remote linked server and then
directly query and transfer the tables that you need. It can be easily
automated via a stored procedure that can be scheduled to run as a job.
Simple enough. Not the fastest approach but seems you are already looking at
generating INSERT statements to dump the data out...
>
Another alternative is to use a DTS package to transfer the tables. It can
be also fully automated, but assumes as above that you have connectivity
between the servers.
>
If you do not have direct connectivity then you can use a similar approach
to dumping with INSERT statements, but via DTS. In essence in the source
server you can create a DTS task to dump the table data to a text file, then
on the destination server import the data to a table using the reverse
process. The same scenario is doable using the BCP utility.
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Tuesday, March 27, 2012

Basic Query: Alternatives to Group By for nText column

Thursday, March 22, 2012

Bar chart problem

Sunday, February 19, 2012

backup/restore of Tables ONLY (please)

Backup to Network Drive

Blog Archive

About Me