How To Select From Data In Copy Of MSDB?
Mar 14, 2008
In SQL Server 2000 I developed a utility stored procedure for synchronising all the jobs relating to a specified job category from one SQL Server instance to another. It was quite simple... I restored the MSDB database from a source server to the target server but restored as a different db name, eg. MSDB_Copy. The sproc then just queried some system tables like sysjobs and syscategories and recreated the jobs on the target server. Now, in SQL Server 2005, some things have changed of course! For example, if I do...
select * from msdb_copy.dbo.syscategories
The result is from msdb.dbo.syscategories! Obviously, this is because 2005 have replaced the old concept of system tables with views. The question is, can anyone suggest a way to select data from 'tables' like syscategories in a copy of the MSDB database?
Thanks,
Clive
View 4 Replies
ADVERTISEMENT
Jun 28, 2002
I need to copy the msdb to another Server, same network, different Server Name. What's the best way to do this?
View 3 Replies
View Related
Sep 16, 2015
Suddenly one day I found some corruptions have occurred in my SQL Server 2012 installation. Because my 'msdb' has been marked SUSPECT/CORRUPT; hence no new work can be done as per my schedules.
I have gone about trying to repair it. I followed this article here: [URL] [the region 'Create new MSDB Database'].
I am facing some sizeable difficulties in doing this on my own. Steps 1. & 2. I have done. I am now stuck at step no. 3.My instance name is SQLEXPRESS. So, when I give,
SQLCMD -E -S<SQLEXPRESS> -dmaster -Q"EXEC sp_detach_db msdb" [as per the article], what I get is:
The System cannot find the file specified.
So I am stuck at that point. What's with the 'cannot find file specified'? The stored procedure, or the mdf, ldf files for my msdb? What is it?Also, if and when I am through with step no. 3 I would like to know about steps 5. & 6. also which are soon to follow [like how to do them correctly, safely and from where, the different options]. This is a huge priority for me to get my sql server up and running again because I can't do my other coding works without it. Everything is stalled. Slowly reaching the desperation, SOS mode..
View 7 Replies
View Related
Nov 21, 2005
Whenever I start my SQL Express 2005 database, I get the following in the logs :
View 10 Replies
View Related
Mar 19, 2008
Hi,
When I am trying to drop a user using following statement
--First remove access from all databases
Set @SQL =
'
USE [?];
if ''' + @login_name + ''' in (Select name from sysusers )
EXEC [?].dbo.sp_revokedbaccess @name_in_db = N''' + @login_name + ''';
'
Exec sp_msforeachdb @SQL
Print 'Access Removed.'
exec @ret_value=master.dbo.sp_droplogin @login_name
I am getting an error,
User has been dropped from current database.
User has been dropped from current database.
Access Removed.
No permission to access database 'model'.
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'sysjobs', database 'msdb', owner 'dbo'.
Login dropped.
The SQL Version I am using is
-------------------------------------------
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Please help me to solve this issue.
Mujeeb.
View 5 Replies
View Related
Oct 20, 2006
We are running SQL Server 2000, SP4. I recently noticed that my DTS packages were missing from the local server. Since I had saved them as Structure Storage Files, I imported them back into DTS Local Packages. That was yesterday. Today I opened the Meta Data browser to see what was in it. (We don't use Meta Data Services.) It displayed a message:"An error occurred while trying to access the database information. The msdb database could not be opened." These (restore of DTS pkgs and Meta Data error) may not be related, but I need to know why I am getting this error, because I am about to upgrade to SQL Server 2005 for Workgroups.
1. Does anyone know what causes this error message?
2. Are local DTS packages stored in the MSDB database?
View 1 Replies
View Related
Sep 26, 2015
On one of our SQL Server 2008R2 instances the MSDB log file (MSDBLog.ldf) has grown to 300GB+. The data (.mdf) file is only 3GB.
Neither DBCC Shrinkfile(MSDBLog) nor an interactive "Tasks / Shrink / ..."
I've already deleted much of the historyThe MSDB database is in "Simple" recovery modeI've done a full backup (in case the log was "waiting" for a backup)
I hesitate to do things (such as as Detach / Attach) with MSDB that I might do with a user database.
View 7 Replies
View Related
Jan 20, 2015
I have a bunch of packages in msdb. Is there any easier way to move those packages from msdb to DataTools/BIDS, instead of adding one package at a time using ADD option.
View 9 Replies
View Related
May 26, 2006
Hello anyone / everyone,
If you are having trouble accessing Meta Data Services on W2k3 and SQL 2000 and get the error that the "msdb database could not be opened" I found that hot fix 912812 on the operating system is the culprit. Remove it and Meta Data goes back to working, although I am being asked to approve the ActiveX control every time the page refreshes.
Hope this helps somebody, to took me long enough to track it down.
If anyone has a resolution to the ActiveX question I'd love to know the answer.
Thank you,
Uncle Davy
View 5 Replies
View Related
Jun 22, 2014
Is there a way to change an image data type? I want to make a change to some deployed SQL 2008 SSIS deployed packages. I have a TSQL SELECT that searches the packages for a string. But I would like to be able to change a string. I have googled it but cannot find anything.
View 5 Replies
View Related
Aug 12, 2014
I have 1 table with some empty product codes that I have imported from a txt file. And I would like to fill in the empty product codes by taking the previous product code
date product_code
01/01/14 | BIC01 |
03/01/14 | |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | |
03/01/14 | CASH |
I though of using the LAG function but the table does not have any specific order.So after running the select, the table should look like:
date product_code
01/01/14 | BIC01 |
03/01/14 | BIC01 |
01/01/14 | CASH |
04/01/14 | BIC01 |
02/01/14 | CASH |
05/01/14 | CASH |
03/01/14 | CASH |
View 5 Replies
View Related
Dec 10, 2005
Hi,I have(had) an old Win2k Server server with about 30 web site databases(SQL 2000) that just went under due to hardware problems. Thankfully, Ihave backups of all the databases plus the MDF and LDF files from thehard drive.I want to move all of these sites and their data to a newer server(Win2003) running SQL2000.What's the best way to copy the database from the old server hard drive(now mounted as an extrnal drive to a local machine; I'm currentlyFTPing all of the web site directories from it to the new server)?Just upload the original data to the new server and then mount the MDFand LDF files within the new SQL server? Or do I restore the backupfiles in the new SQL2000?All of my previous data migrations have been DTS operations from onelive server to another, so no experience with either of the abovescenarios. I'll certainly have a lot more experience at one of them bythe time this weekend is through.Thanks for any help you can offer.
View 1 Replies
View Related
Aug 12, 2006
Can anyone provide an expample of bulk copying XML data to a SQL table. I am also looking at using column mapping so that I can map fields and also insert a new GUID into the key of the SQL table.
Many thanks
View 1 Replies
View Related
Aug 28, 2015
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
View 8 Replies
View Related
Dec 5, 2005
i have a claims_tbl that i am inserting into with claim information, the tbl contains a claim_seq number that i want to copy over to a user_info_tbl at the same time insert user information below is what i have tried with no luck
insert into user_info(claim_seq) select Max(claim_seq) from claim_tbl
update user_info set lname =upper(@lname), fname =upper(@fname), mname =upper(@mname), personnel = @personnel, p_position =upper(@position)where claim_seq = (Select Max(claim_seq) from user_info)
i am getting duplicate rows of information ..........can someone help
thanks
View 2 Replies
View Related
Jul 25, 2001
Hi!
What is the best way to copy data from SQL server to Access database located on differet machines?
Thank you,
Elena.
View 1 Replies
View Related
Sep 11, 2006
Hello,
there is a database with an table x and I want a copy of that in my testdatabase.
I am pretty sure, that I have done that the whole last week, but it's not working anymore.
I thinkt, that last time I used a statement like that to copy the table with structure and all.
insert into dbtest.dbo.x select * from db.dbo.x
Today it's only telling me, that the destinationtabel x is not present.
The table has about 100 fields and I swear, I didnt create it by hand in the testdatabase, I'm definitely too lazy for that.
I can't use the enterprise manager, I only can use sql.
There are still tables in my testdatabase, I copied last week. So what is wrong with my insert into?
View 1 Replies
View Related
Apr 7, 2008
All,
Does anyone have amethod for copying db(all tables, procs etc..) but without the data. Scripting is not feasible because of number of objects constraints ..etc...
any help would be appreciated
thanks,
JB
View 6 Replies
View Related
May 26, 2008
hi
How to copy data from one column(field) to another column(field).
I have two column colA & ColB
Both column have data.
but i want to replace all ColB data with ColA.
it is like this,
COLA COLB
abcd grfr
dfere kieidk
fere fkerie
but, I want like this
COLA COLB
abcd abcd
dfere dfere
fere fere
please help me
View 2 Replies
View Related
Oct 6, 2005
I am trying to copy item names from one database to another if they share the same code number.
e.g.
UPDATE TABLE1
SET NAME = DB2.TABLE2.NAME
WHERE CODE = DB2.TABLE2.CODE
I dont know how to specify the second db as using a '.' here doesnt seem to work. I have also tried '..' and DB2.dbo.TABLE2 but get the same error - 'The column prefix 'Train_DB_FinancialsProd.dbo.ITEM_DEFINITIONS' does not match with a table name or alias name used in the query.'
I have checked that the spelling is ok so not sure why it doesnt work.
I have also tried using a temp db in between to copy the values across then try to update from the temp table but couldnt make that work either. e.g.
create table #tdhname
(
code char(50),
name char(100)
)
INSERT INTO #tdhname (code,name)
select distinct code,name from Train_DB_FinancialsProd..item_definitions tritm
where code = tritm.code
and tritm.ittyp_refno = 241
update pharmacy_items
set name = #tdhname.name <----- THIS DOESNT WORK
where #tdhname.code = code
Thanks!
View 5 Replies
View Related
Jul 23, 2005
hi their,How can i copy data in MS-SQL 2000 from one table to another withdifferent charactristic?thanks*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
Mar 10, 2008
Help!!! I am trying to consolidate 5000 odd databases. Each database contains hundreds of tables. The tables in each database are identical to the tables in all of the other databases. Sounds simple doesn't it? However, SSIS has defied every trick that I have applied to this task including changing connectionstrings at run time through package configuration. has anyone out there ever tackled a similar challenge?. Please let me know
Desperate
View 7 Replies
View Related
Dec 5, 2007
I have SQL Server Express installed on my local development machine and on a remote server. I have identical databases set up on each. How can I copy the data from one to the other? How can I copy the data from one to SQL Server 2005? And can I create an Access database from SQL Server Express? Diane
View 3 Replies
View Related
Dec 5, 2007
I have SQL Server Express installed on my local development machine
and on a remote server. I have identical databases set up on each and I'm trying to keep them co-ordinated to make testing easier.
How can I copy the data from one to the other? How can I copy the data
from one to SQL Server 2005? And can I create an Access database from
SQL Server Express?Diane
View 9 Replies
View Related
Dec 16, 2007
Hi: At the moment, I know how to copy a db to another (create a .bak file), but I am not sure what is the best way to copy one the table (with it data) from a db to another.Would u please give some links or suggestions?Thanks.jt
View 4 Replies
View Related
Dec 31, 2007
I am creating an ASP.Net project at work and have created a database in Microsoft SQL Server 2005. I need to copy data from an old database (which is in Access) that the company has used for years. The problem is that these databases are not very similar (the Access database is not normalized at all). My question is simply this -- is there an easy way to do this. I would like to avoid writing a lot of code if possible as this will only run one time. Any help would be appreciated. Thanks.
View 1 Replies
View Related
Jan 17, 2008
i have a table called t_CustomerAcct in 'Dev' db and want to copy the data in t_CustomerAcct table in 'Production' db.
but i have some records in the table in 'production' db which i dont want to be updated. my primary key in both tables is 'email'
i bit lost on how to do this and i dont want to loose any data from production db accidentally.
View 1 Replies
View Related
Jun 6, 2008
Hi,
I have two users tables. One users table has a deleted column. The other users table is made up of users based on what dept they are in etc. I would like to get the values from a column (deleted) in the first table and copy the values over to a column in the second table.
I would appreciate any suggestions you might have.
Regards,
Tony.
View 2 Replies
View Related
Aug 23, 2005
hi
i want copy Data base fron 2 sql server2000 in the same network. so i want copy all object(table,users,diagrams,....)
View 1 Replies
View Related
Oct 14, 2004
I need to create a new field and copy over an existing field from the same table. Since this table is about 3 million rows. It will take about 1 hour 30 minutes to complete.
Any good way around?
Thanks!
-J8
-----------------------------------------
UPDATE Customer SET ColB = ColA WHERE ColA IS NOT NULL
View 3 Replies
View Related
Dec 6, 2004
Dear All,
Can anyone tell me what's the best/easiest way to copy one column of data from one table to another column of another table.
Thanks in advance.
View 3 Replies
View Related
Jan 21, 2005
I am trying to copy information from one Database to another. Each of the databases reside on a different server.
My question is
1. How can I use the DSN name in a stored procedure?
2. Do I need to use dynamic SQL?
3. Any other ideas of how to implement this?
Thanks
View 1 Replies
View Related
Aug 24, 2006
I need to copy the data in the last name table to the field2 table do i use the update query in sql 2005?
View 5 Replies
View Related
