I have just used DTS to create a new Database on another server. The problem is that DTS does not create all the objects. At least as far as I can tell, diagrams, full text indexes and catalogs were not created in the new database. Is there anything else that does not get created? Is there a way to create all these objects?
View 1 RepliesHi,
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
Thanks for any help,
KT
Hi!
I did:
alter database mydb set single_user with rollback immediate;
exec sp_detach_db @dbname='mydb', @keepfulltextindexfile='true';
then I tried to copy files to new location on other drives, same server but got
>>Cannot copy <myfile>: Access is denied
Make sure the disk is not full or write-protected and that the file is not currently in use<<
I also tried rename of file without success.
I also tried with db service stoppet (not preferred) without success.
How to find out, which process locks the files?
Best regards
How do I transfer/copy the stored procedures in my Test DB to my LIVE DB? IT won't allow me to export keeps giving me an error.
View 4 Replies View RelatedHello,
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
Or is the datebase name part of the .mdf file?
Regards
Markus
I am attempting to use the copy wizard to copy databases from SQL Server 2005 to SQL Server 2008 R2 w/ FP1.
The copy fails with a login failure to SQL Server 2005. I have a user id & password under Windows for both servers. I have a user id and password under SQL security with the called for admin security rights.
The 2005 server has two instances, 20 databases, two dozen maintenance plans, and over a hundred users. I really would like to use the utility so I don't have to recreate everything manually.
Hi~,
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
- any other guide lines
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
-------------------------------------------------------
My solution is like this. Is it correct?
// CoCreateInstance(...);
// Data source
// Create session
m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;
DBPROP rgProps[1];
DBPROPSET PropSet[1];
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";
PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;
if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}
Hello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a different database.Basically on a project I am working on we are using a table named "Customers" from a database named QTR. We need to copy this database table into a different database named "Research". How can this be done? Is if very complicated?
View 1 Replies View RelatedI need to copy a row data with a slight change (just the PK). How can I do that with a stored proc?
View 1 Replies View RelatedHi Folks !
Is there any way one can move or copy DTS packages from
one database/server to another.
It seems to go only with a backup and recovery process.....
thanks,
Prasad
Hello,
I'm trying to provide our developers a tool to copy databases down to the dev server and DTS transfer objects doesn't copy the primary and foreign key constraints. Is there a fix for this, or do you have a better method? I've thought about using backup / restore but would have to write a program to accomplish this.
Thanks,
Dan
dth6@pge.com
in sql 2005 -- what's the best way for me to make a complete copy of my database - including indexes and keys and everything??
i tried backup but when restoring to the second i get an error with multiple media sets.
Hi
I have 2 similar instances in sql server 2005
in instance A i have some jobs.
I want them to copy to second instance B
Please guide me step by step.
Muralidaran r
I have done a backup of my database, but how do u copy that file to the CD??
View 2 Replies View Relatedis there any easy way is sql 2005 express to copy a db completely with structure and data?
View 1 Replies View RelatedI am running SQL Server Management Studio Express 2005. I am trying to bulk copy the contents of a csv file into a table. I've opened the command window and typed in the following:
bcp "Public_data.dbo" in "c:customer_service_reportcard.csv" -c -q -U"xxxxxx" -P"xxxxxxx"
I get the following error:
SQLState = 08001. NativeError = 2
Error = [Microsoft][SQL Native Client] Named Pipes Provider: Could not open a connection to SQL Server [2]
SQLState = HYT00. NativeError = 0
Error = [Microsoft][SQL Native Client] Login timeout expired
SQLState = 08001. NativeError = 2
Error = [Microsoft][SQL Native Client] An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
SQLServer Management Studio Express is installed on my local PC for development. Does bcp work with Express?
I checked the server properties and "allow remote connections to this server" is checked.
Any help is appreciated.
Hi All,
I want to copy a folder from one server to another using XP_CMDSHELL command.The folder contains many number of folders.HOw can i acheive that.Please help, TIA
RKNAIR
Hi All
As a noob please forgive me if I'm posting this in the wrong place.
I have two SQL2000 servers. There is a table on server A that gets updated with new rows automatically by an overnight DTS job.
A little later, a job runs on server B to pull the updated copy across to be used by another application. If the table on server A gets emptied for some reason, I'd like to not copy it across so there is some data on Server B, albeit one day out.
What I'd like is some form of checkpoint that says:
If the table on Server A is empty, don't copy the contents to Server B (as this will result in an empty table on Server B thus killing two apps.)
I've thought of doing a count of Server A.table and if it is zero then halt. Am I in tree/barking/wrong territory?
How do i copy info from one column to another column in the same Table
Compnetsyslc
Good afternoon.
How, with T-SQL, to copy a database under other name? (i.e. the DB - bd1 should be copied it in bd2. Business occurs on one server MS SQL 2005)
But to copy, so that transferred all procedures, triggers, ....
Thanks!
I'm writing a stored procedure that requires I duplicate records through a loop, one record at a time (required because I need to execute SCOPE_IDENTITY() logic on each insertion). But each row has 40 or so columns, making my stored procedure ridiculously full of long declaration lists. So I want to either:
1) Learn a way to auto-insert the column declarations into my code without having to type them all by hand, or...
2) Learn a way to represent the whole row for insertion, without having to specify each column specifically.
The latter solution would be the most elegant, but I'll take what I can get...
Code Snippet
INSERT INTO StuffCoding
(c_StuffCode, .....)
SELECT @StuffCodeNew, .....
FROM StuffCoding AS StuffCoding_1
WHERE (c_StuffCode = @StuffCodeOld)
not good in Method for me
Because Need Add COLUMN 1,COLUMN 2, ... , COLUMN n in SELECT and INSERT INTO
if add COLUMNn+1 in StuffCoding Table
when in Method not good. Because not edit my view or query
when the datatype is text?Hi, I'm trying to copy 1 row/column value into another in the same table.UPDATE MyTable SET Column2 = (SELECT Column2 FROM MyTable WHERE Column1 = 1)WHERE Column1 = 2This works OK when the datatype of Column2 is varchar, but not when it's text.It fails with:The text, ntext, and image data types are invalid in this subquery or aggregate expression.
View 1 Replies View RelatedIs there anyway I can copy tables and stored procedures from one MS SQL server database to another? I presume there is, so how is it done.Thanks
View 6 Replies View RelatedI have two databases and want to copy tables (with table data) and stored procedures from one database to another.both databases have the same name (so management studio is not letting me have both up)....what should I do?thanks.
View 5 Replies View Relatedhai friends.............. I want to know how to copy table from one database to another in a same sql server 2005? ex : i have one table called sample1 in database db1. we want to copy it and and paste it to database db2.how can i do this explain me elaborately. regards Samuel Chandradoss .J
View 2 Replies View RelatedHow to copy one databse table into another database as a same table with same same tablename?
View 2 Replies View RelatedHi
I have 2 MSSQL databases. I want to copy some tables of DB1 to DB2. how can I do it?
Also, how can I copy some columns of table1 of DB1 to table 2 of DB2?
Thanks for help
Hi Guys,
I have 2 diiferent servers in sqlserver. One is test and second one is production. both servers has same databases and same tables. in test i have Employee as databse same thing in production also. Now i want to copy Production(server)->Employee(database)->Emp_Generated(Table) information into Test(server)-->Employee(databse)-->Emp_Generated(Table). Can anu one help how to write sqlqery to copy table information.?
Thanks
hi,
I need to transfer my database from one computer to another. I am using SQL SERVER 2000. So I made the script of the database and transfered to other computer. Now when I try to run the script it gives me error that (mydatabase name).MDF file missing. So I tried to copy the .MDF file from my computer. but it says that access is denied and sharing volilation. Can anyone please help me that how can I copy the .MDF file and remove the sharing volilation.
Does anyone know how to copy the records from a table in a database into a table in another database?
Query would be great if possible.
I don't know how to use DTS.
Thanks,
Mike
Hi,
I have developed an application using SQl Server 2000.
I want to transfer the database to my new server. I want all the tables and datas to be transferred.
I am new to this application. So i am not able to get mysel started. Can someone help me in this problem.
My database is large. It has stored procedures, database diagrams.
Any help in this regard would be highly appreciated.
Thanks,
-sriram