MSSQL 2000- Backup Activity on a Network Machine

Hi,
I am using an Enterprise Manager and i have created a Database Maintainance Plan, can anybody guide me as to how to create backup on a remote machine.

I also mapped that device(say M/c B) onto my server box(M/c A) but i am not able to see that particular location in my backup activity screen.

Can this be done in the first place ?

Regards,
Tahirwe need some clarification.

do you want to backup databases on machine A to machine B or do you want to manage backups of databases that are on machine B from machine A?|||Yes i want to backup databases on machine A to machine B through Enterprize manager.|||While doing the backup on a networked remote machine which has been given full rights the job fails giving this error

BACKUP failed to complete the command BACKUP DATABASE [qa_tm_bdr_fix2] TO DISK = N'\\192.168.156.7\DBBackup\MUMSV075\qa_tm_bdr_fix2 _db_200612262300.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT|||Is your SQL Instance on A running under the system-account? The system account had no rights on B.

But there should be a more useful messge somewhere about why the job fails. Maybe in the errorlog? Have looked at the details in the jobhistory?|||Lexiflex is right. Make sure that the service account running the SQL server on B has enough permissions on the share (on A) where it should write the backup file. So in your case the account under which SQL Server B is running should have the permissions on the \\192.168.156.7\DBBackup share. I assume you allready shared the folder ... The problem Lexiflex is referring to is that the 'local system account' from machine A is not the same as the 'local system account' on machine B ... although it looks like it's the same one, it's definitely not ! You shouldn't run SQL Server under the local system account ... ever.

Been there, done that, worked fine.

Gr,
Yveau|||I am betting this is the problem as well. You will need to change the sql server and sql agent accounts to run under a domain account that has rights to your backup location.|||Thanks a lot for all ur help......it works......

Regards,
Tahir

MSSQL

I have created a tool using vb.net,with mssql as backend.i need to
create the database and the table in the database using some scripts or
what ever relevent methods.How can we make the database while
installing the software.you could call the following from VB.NET:
osql.exe -Usa -Ppassword -i d:\dbr\create_database_script.sql -n
-oD:\DBR\OUTPUT\SQL_SCRIPT_LOGS\outputfile.txt
--
--
Sasan Saidi
Senior DBA
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Armagadone" wrote:
> I have created a tool using vb.net,with mssql as backend.i need to
> create the database and the table in the database using some scripts or
> what ever relevent methods.How can we make the database while
> installing the software.
>

MSSQL

I have created a tool using vb.net,with mssql as backend.i need to
create the database and the table in the database using some scripts or
what ever relevent methods.How can we make the database while
installing the software.
you could call the following from VB.NET:
osql.exe -Usa -Ppassword -i d:\dbr\create_database_script.sql -n
-oD:\DBR\OUTPUT\SQL_SCRIPT_LOGS\outputfile.txt
--
Sasan Saidi
Senior DBA
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Armagadone" wrote:

> I have created a tool using vb.net,with mssql as backend.i need to
> create the database and the table in the database using some scripts or
> what ever relevent methods.How can we make the database while
> installing the software.
>

MSSQL

I have created a tool using vb.net,with mssql as backend.i need to
create the database and the table in the database using some scripts or
what ever relevent methods.How can we make the database while
installing the software.you could call the following from VB.NET:
osql.exe -Usa -Ppassword -i d:\dbr\create_database_script.sql -n
- oD:\DBR\OUTPUT\SQL_SCRIPT_LOGS\outputfil
e.txt
--
Sasan Saidi
Senior DBA
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Armagadone" wrote:

> I have created a tool using vb.net,with mssql as backend.i need to
> create the database and the table in the database using some scripts or
> what ever relevent methods.How can we make the database while
> installing the software.
>

MSreplication_agent_status in sql server 2005

Is this table still residing in tempdb in 2005? I have created replication but the server doesn't have this table anymore

No, replication does not use this table anymore in SQL 2005.|||

Thanks greg..

Any idea how/why it was used before? why it's not used in 2005? Any articles on this would be much appreciated

|||There was a redesign that eliminated the need for this table.

Msg 7320

Hi,
I've successfully created a connection (linked server) in
SQL Server 2000 to an Oracle database (9i). I'm running
SQL Server on a WIN2K machine.
Now, I know that the connection is made because I can see
the list of tables and views under my Linked Server.
With that said, if I try ANY kind of query on these tables
I get a Msg 7320 error. The name of my Linked Server is
OracleSource. Here are some examples of queries that fail:
select * from openquery(OracleSource, 'select * from
ETA_USER.ABBR_DEVICE')
Select * From OracleSource..ETA_USER.ABBR_DEVICE
ETA_USER is the Schema and ABBR_DEVICE is the table name.
They are the same except one is using OpenQuery and not
the other. I know that this query works because I tried
it successfully in SQL Plus.
I have no idea what to try next and there doesn't seem to
be any help on the net (I've already consulted KB articles
270119, 251238, 248156 and 280106)
Any ideas, please!
Thanks, Skip.You might be encountering a limitation as described in :
244661 INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider
http://support.microsoft.com/?id=244661
You can also try dbcc trace (7300,3604) to see if you can get the provider
to return more information on why it is failing.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Msg 7320

Hi,

I've successfully created a connection (linked server) in SQL Server 2000 to an Oracle database (9i). I'm running SQL Server on a WIN2K machine.

Now, I know that the connection is made because I can see the list of tables and views under my Linked Server.

With that said, if I try ANY kind of query on these tables I get a Msg 7320 error. The name of my Linked Server is OracleSource. Here are some examples of queries that fail:

select * from openquery(OracleSource, 'select * from ETA_USER.ABBR_DEVICE')
Select * From OracleSource..ETA_USER.ABBR_DEVICE

ETA_USER is the Schema and ABBR_DEVICE is the table name.

They are the same except one is using OpenQuery and not the other. I know that this query works because I tried it successfully in SQL Plus.

I have no idea what to try next and there doesn't seem to be any help on the net (I've already consulted KB articles 270119, 251238, 248156 and 280106)

Any ideas, please!

Thanks, Skip.Have you tried it with the 4 part name?|||Originally posted by Brett Kaiser
Have you tried it with the 4 part name?

The second select is using four-part naming, no? I know I'm missing the catalog (between the server name and the schema) but I don't think there's one because sp_table_ex returned NULL for this table's catalog.

Is there a way in SQL Plus to view a table's catalog if there's one?

Thanks,

Skip.|||What do you have selected under Security Tab int the linked server option in EM. I have had a problem where the Linked server to oracle did not work until i had the radio button

Be made with this security context selected.

Another Miracle .... brett|||Thanks Brett but I do need a security context because authentication is required to login to the remote server (a special user has been created for this purpose).

Plus, the EXACT same query works under SQL Plus!

Anyway, any other ideas?

Skip|||Yup ..

Thats what I am talking about ... The problem i faced a long time ago was ...
I was able to run the query from SQL plus ... also from QA but not from a stored procedure ... and using the security context helped me out ... am not sure in your case what is the problem until you elaborate what all you have tried out

msg 4866

I have created a tab delimited text file using MS Foxpro 2.6 for Unix.
The last field on each line is followed only by a linefeed \n.
When I try to use bulk insert I get the message that the 40th (last) field on the line is too long.
I have tried adding a tab after the last field but this makes no difference.
I have added an ! (exclamation mark) after the last field and changed the rowterminator attribute to !\n. Still doesn't work?
I am using sftp to copy the file from the unix system to windows, and there is no change in the size of the file, so I don't think that a carrriage return is being inserted.
Any suggestions?

If I reduce the size of the input file to one record, then the insert works.|||Never mind, I figured it out. Lines have to end with '\r\n' regardless of the fact that the documentation says the default ROWTERMINATOR is '\n'

Msg "cannot add rows to sysdepends... procedure will still be created"

Can I get the missing rows added to sysdepends later, when I create procedures in the wrong order, thus getting the "Cannot add rows to sysdepends for the current stored procedure ... The stored procedure will still be created" ?Can I get the missing rows added to sysdepends later, when I create procedures in the wrong order, thus getting the "Cannot add rows to sysdepends for the current stored procedure ... The stored procedure will still be created" ?

Well?? whatcha guys say?|||Sure you can.

Say that MainProc calls SubProc, but when you create the procs in the database you create MainProc 1st. When you create MainProc, you will get the sysdepends message "Cannot add rows ...". When you create SubProc, sysdepends is **not** updated. If you then drop and add MainProc, sysdepends will be populated with the dependancy information.|||Yeah, that's how I've gotten around a few of the problems created by the scripting/transfers to other databases (mirrors, in my case).

However, being the bull-headed son-of-a-db2 programmer that I am, I refuse to do that to every freakin' proc/table/function/etc. in my script. Seems to me that if I use SQL Server to script out a humongous script and then use that script to build a new DB, that I shouldn't have to manually go through the process of dropping and rebuilding each object in the new db.

Perhaps Uncle Bill wants it that way, but I have always been a problem nephew, so I am trying to figure a more global, one-button solution. I did find this thread on the subject after posting the original query, however. http://www.dbforums.com/t406785.html

I will take a look at that solution and see how it works.

Don't get me wrong, I do truly appreciate your response. Sincerely. I just don't have the time and inclination to use the manual drop/readd thang...when that is actually only a tad more desireable than manually writing down the object dependencies on the chalkboard in the hallway. ;)

Thanks again...and sorry for the rant...I just don't like the appearance of bells and whistles that only impotently blow the ball around inside (though they might do so at a high rate of speed), or dog and pony shows consisting of a toy poodle and a plow nag.|||Argh...alas...the sp_recompile/sp_refreshview solution still only goes from the individual object perspective...so I still will need to order the objects according to referential sequences before doing the recompiles.

Still not my silver bullet.

I guess I'll just requisition a few more chalkboards ;) Thanks again!
Paul|||Dependency is resolved at the time of creation of the first execution plan. Thus, - necessary recompile that can be observed while running profiler.|||Dependency is resolved at the time of creation of the first execution plan. Thus, - necessary recompile that can be observed while running profiler.

Are you saying that just running a stored proc will "rebuild" the dependency information in the sysdepends table? If so, then theoretically that means that all I need to do is wait for all the stored procs in the database to run and then my sysdepends table will be all better?

That would be cool, but it doesn't seem to hold true in my (admittedly limited) experience. I'll have to check it out, but my stuff runs M-F here at work, and yet I still have "missing" or "incomplete" dependencies when I list dependencies in Enterprise Manager *scratching head*

Am I missing your point? Are you saying the profiler must be run to rebuild the dependencies? *LOL* I guess instead of guessing I'll wait until you clarify your comments...

As always, thanks!|||I THOUGHT you were going after a physical dependency, not the ability to execute a procedure even if sysdepends doesn't contain a reference...but hoped that that would not be the case after all...Oh well, you're write, dependency can only be reestablished if you either re-CREATE or ALTER the parent procedure after the child has been created. SP_RECOMPILE does not do the trick, BTW.|||I THOUGHT you were going after a physical dependency, not the ability to execute a procedure even if sysdepends doesn't contain a reference.

I probably know just enough to be dangerous to myself and those around me ;) but I really just wanted to build the sysdepends table so that when I DISPLAY dependencies of the various objects (i.e., right click->all tasks->display dependencies) it would be accurate.

I'm not sure which of your situations that desire falls under. sorry if I don't understand...I think it would be the physical dependency that I am after. As far as I know, I've always been able to execute a procedure even if the sysdepends info is bad (i.e., even if the dependency is not displayed in "display dependencies" - which I have understood to get its info from the sysdepends table).

In any case, I think the bottom line is that there is no easy way to rebuild things in sysdepends so I can be assured that all my dependencies are displayed when I try to see them via right-click...short of the drop/add thing.

Although, as I recall, if I go into the stored proc and modifiy something (as opposed to drop/add the proc) - I think the dependencies are re-evaluated when I save my changes...at least that's what I recall from earlier playing around with 'em|||That's because EM fires ALTER which reevaluates the dependencies, as I mentioned earlier. If you want to achieve the result desired you need to invest into something like DBArtisan, but if you just want to continue bashing EM, - be my guest ;)