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?
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..
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.'
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)
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?
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.
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.
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.
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.
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
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.
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
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.
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
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?
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...
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
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 ***
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
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
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
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
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.
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.
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.
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
