Is there a quick way in Management Studio to ... i don't know ... drag and drop or maybe copy and paste or import the 3 tables (The 2 databases are on the same instance of SqlServer 2005)
-perplexed newbie
I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
SQL Server Tools General
-
- Previous
- 1
- Error Deleteing Remote File This is a known problem with the FTP task in SSIS trying to delete a file on a non-Windows FTP servers. http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1074276&SiteID=1 I do not know if SP2 fixes this problem or not. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 2
- Data Connections: Where the hell are they stored??!! Hi, Thanks for your reply. Forgive my ignorance, but what is BIDS Now OK, connection managers are persisted in the IS packages, but what use are they if they don't store connection information (Server, Authentication, Database) I thought that's what they were for but it now appears that this connection information is stored in the Windows registry, which is not persisted in the IS packages. Is there any way to retain the connection information in the connection managers Cheers, Jerome Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 3
- JPGs added to Image column are truncated at 1024 bytes (but only when import executed as a Job) I have a table in a SQL 2005 database that is designed to hold JPG product images for a website. Here is the schema: create table dbo.Eur_RMISWebInterface_Staging_ProductImages( product_code varchar(12) not null, image_order smallint not null, colour_identifier varchar(10) not null, image_data image not null, constraint PK_Eur_RMISWebInterface_Staging_ProductImages primary key clustered ( product_code, image_order ) ) Product images are inserted into the table from a database on a remote SQL 2000 server. Here is the script that does that (uses dynamic SQL): set @SQLCmd = ' set xact_abort on begin transaction delete Eur_RMISWebInterface_Staging_ProductImages insert Eur_RMISWebInterface_Staging_ProductImages ( product_code, image_order, colour_identifier, image_data ) exec "' + @RMISServerName + '".' + @RMISDatabaseName + '.dbo.Eur_RMISWebInterface_GetProductImagesForWeb ' + convert(varchar(3), @PortalID) + ', ' + @Locale_LCID + ' commit transaction' execute (@SQLCmd) About 500 images, each about 20KB, are transferred at a time. Here is the problem - when this script is run manually, all the images are inserted completely. When the script is run as part of a job (that has several other steps), the job step completes successfully and all the images are inserted, but every single one is truncated to the first 1024 bytes of the image. What this ends up looking like on the website is a a narrow strip of image instead of a complete image. Here are some other observations: - When I changed the "Eur_RMISWebInterface_GetProductImagesForWeb" SP to only return 1 product image instead of 500 it still failed - The owner of the job is the same Windows user as the user I have been running the script manually as - I have tried changing the datatype of image_data from image to varbinary(max) but it made no difference What could possibly be going on Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 4
- create table by code The title says you want to create a Table, but your question is asking about creating a database, no sure which you want since they are different, but... There are always multiple ways to do stuff, so you might want to explore a couple different ways. In code, the two that come to mind are using a SqlConnection object to run your script. The script would be run using the SqlCommand object. Both of these are part of the System.Data.SqlClient namespace which offers many tools for connecting to and running commands in SQL Server. (Note: Depending on the complexity of your script, you may need to parse it into multiple commands. For example, the SqlCommand object can only take a single batch (i.e. the stuff separated by GO when you use the query window, so you have to run those separately.) Another option for work with database in code is SQL Management Objects or SMO. Rather than using T-SQL scripts as you would do with System.Data.SqlClient, SMO exposes the objects in SQL Server and lets you manipulate them like any other programatic object. You can find examples of working with database in SMO here, and working with Tables in SMO here. The is no one right way, use the method that makes the most sense for you. Mike Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 5
- binding textbox text to a database field Yes, that is quite possible. But you will get better assistance if you post your question in one of the .NET ASP related forums. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 6
- Pattern Matching and Full-text search I'm not quite sure that this is possible but, I figured that I would check with you experts out there before trying a new approach. I've done quite a bit of research and have not seen anyone quite figure this out yet. We have a SQL Server 2005 application that stores and indexes documents to the database as an image data type. I'm able to do full-text queries against the documents without any trouble. I begin to run into problems when trying to pattern match social security numbers and drivers licenses stored in a full-text index. I have a user defined function that I call which runs my regular expression that checks for hits of a ssn or license number in the index. I have no problem getting hits when the data sits in a column. I do need to mention that I have no trouble when searching for a ssn with a fixed value and where I know the ssn (ex: 123-45-6789). I am actually trying to find the existence of the pattern of ###-##-#### (ex: ^\d{3}-\d{2}-\d{4}$) anywhere in the index. Any help would be very much appreciated. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 7
- Check remotely if Reporting Services exist i want to check if a remote server has an existing reporting service or runs a reporting service... Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 8
- rda dropping primary key index Thanks for the reply but when running the code I get NO error... just a missing index the index should come down automatically as long as it can fit in the db size... but it does not.. it only comes down whith a smaller set of data. The error I get when creating the index manually is in sql server management studio... and there is no rda object to get rid of can you please explain your reply a little more Regards, eric Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 9
- How to handle multiple result sets return from Stored Procedure Well in VB I usually use rs.nextrecordset when I have a single SP returning 2 resultsets. I'm not sure what language your using to use the data returned back from your SP. You can have different data structures as well, but your calling code has to handle the different column/field names. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 10
- Analysis Service stops soon after starting Hi, Thanks for the reply. I've installed SQL SP2. I doubt If I have the SP1 for the Visual Studio. (I'll install it today anyhow... ) I'm running as an administrator only. I'll update you on the status. Best regards, Markish Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 11
- Read one field in every row during SELECT hi, you can achieve this using a better solution by dong a self join on userid and midnight(dateaccess) you just have to drop the timepart of the date on the join. you dont even need the temp table. here's the pseudocode: you just need to imporve this. select userid, convert( varchar(20),dateaccess,102) as dateaccess, convert( varchar (20),dateaccess,108) as clockin, t2.clockout from table1 t1 where [type of access]='clockin' join (select userid, convert( varchar(20),dateaccess,102) as dateaccess, convert( varchar(20),dateaccess,108) as clockout, from table1 where [type of access]='clockout') as t2 on t1.userid=t2.userid and t1.dateaccess=t2.dateaccess regards, joey Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 12
- Select Statement In Table (Valid Expressions Error). hi you can actually optimize your proc into something like this INSERT INTO dbo.New_Cust_Table SELECT [Name] + '1' AS UserId , [Name] + '1#' AS Pwd , 'And AccountNo LIKE ''%' + AccountNo + '''' AS SelectStmt FROM Cust_Table with the case of multiple account nos. INSERT INTO dbo.New_Cust_Table SELECT [Name] + '1' AS UserId , [Name] + '1#' AS Pwd , dbo.ParseAccountNo(AccountNo) AS SelectStmt FROM Cust_Table where dbo.ParseAccountNo(@AccountNo) is a scalar user defined function that returns something like this 'And AccountNo LIKE ' %A5R7 ' for single account no or 'And (AccountNo Like '%07A11' OR Account LIKE '%13866')' for multiple account nos. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 13
- "An error occurred during local report processing" for Preview Simone, sorry for my ignorance. Could you tell me which log you referred to Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 14
- Deployment files needed by sqlserver 2005 driver Hello all I built an application that connect to MS SqlServer2005 using Native driver (sqlncli.msi) I install that file from MS site, I need to deply my application to the end-user, and I would like to know what files do I need to deploy to make sure the application is gona run okay on the client PC's. I search in the registry for the driver, and I found this "sqlncli.dll", is it enough or I need to include more files !! Thanks and best regrdas Waleed Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 15
- TSQL CASE UPDATE INSTEAD OF UPDATE triggers will intercept the records and allow checks to be made so you don't have to do the work in the original update. Adamus Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 16
- How do you install Management Studio on Full SQL Version Did you eventually install the SQL Server Express edition The non-Advanced edition does not contain any managment tools. (like SQL Server Management Studio Express). If you are using this edition you should in addition download the advanced edition pack that contains Reporting Services and SQL Server Express Management Studio. Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 1
- Next
- 1
- How to do Replication in SQL Server 2005 There are three type of replications in SQL Server (a) Snapshot (b) Transactional (b) Merge What i feel is you can configure Trasactional replication. There are many documentation available in net. pse have a look on this http://blog.csdn.net/longrujun/archive/2006/06/09/783357.aspx Madhu Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 2
- SQL server ADO Connection Pooling Increase you [Max Pool Size] to 100. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 3
- header/footer: drillthrough Link are not supported in RS2005: Fix available? Hi Bob, Thanks for the reply. When I creat a link in the header part of the report and click on that link it throws following error message with a blank page. "The source of the report definition has not been specified." But When I use the same link in the body it works fine. I am using Microsoft report services visual studio 2005. First when i search on the forum for similar problem thread i found that some other guy also faced the problem in 2006 oct till that time fix was not available. Even right now i donot know is there any fix for this or not. hope this will help u to understand the problem. thanks Novin Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 4
- NOT IN not working with GUID? Hi caracol, Can you store the resultset from the OPENQUERY function into a temporary table or table variable, if there are not many rows, and use the EXISTS operator instead NOT IN insert into #t SELECT objectGUID FROM OpenQuery(ADSI, 'SELECT objectGUID FROM ''LDAP://DC=some,DC=org'' WHERE objectCategory = ''Person'' AND objectClass = ''User'' AND homeDirectory = ''\\*'' ') as t UPDATE E SET inactive = 1 FROM TabEmployees AS E WHERE E.personalnumber <> 0 AND not exists ( select * from #t as D where D.objectGUID = E.Personal_GUID ) Another thing, can you filter the restlset from OPENQUERY to pull just the rows that match [objectGUID] IS NOT NULL AMB Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 5
- question about like i tryed the following sql statement: select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x%89D4% ; and got the error message: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'D4'. however this statement: select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x89D4 ; does not give me any errors, but this statement doesn't really help any, since it searches for the exact value 0x89D4. and about the part that image is depricated, well the database that i have to use already exists, and is full of data so i'm not to sure that i have anything to say about the data types that they use, just have to go with what they have ... Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 6
- problem with importing the excel file SQL Server 2k5 what do u mean by connection manager I've no idea. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 7
- More details for: SSPI context / [DBNETLIB][ConnectionRead (recv()).] General network error Hi Ming, thanks for your informationsites. I had already tried out parts of it but reviewed them all the same. The SPN does work correctly, otherwise our other usersgroups wouldn't be able to connect to our servers. I double checked this by setspn -l domain\accountname and got the desired result on the rigth port. When turning on Named Pipes and restarting the instance on the serverside while activating NP on the clientside only (cliconfig.exe), we get the error message "Error on testing the connection while initialising the provider. [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied" (My error messages are translated from our german clients). We are running SQL Servers 2000 Standard English Edition (8.00.760 SP3) on English W2k servers with SP4 and we use named instances with TCP/IP only. Our clients are WinXP Pro with SP2 and Access 2002 (MDAC 2.70.7713.0) or Access 2003 (MDAC 2.81.1117.0). Connection is made by Access. Aliases are not existent. The service account is a domain administrator. Service of WinXP SP2 Firewall is deaktivated or the firewall is configured as inactive. There is no encryption enforcement. Users are local admins. We have installed Trend Micro Server Protect / Antivir Corporate Edition 5.58 respectively on every machine. A firewall is not provided by this version. The Clients are shut down every day, so I doubt deleting the cached credentials when they should be deleted after every restart would make any difference. Do you have any ohter hints Thanks again. Regards, caracol Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 8
- Please can someone recommend? Thanks Joe, I have been loocking at the quest stuff. It would appear that PA may be better suited for historical blocking analysis, http://www.quest.com/Quest-Performance-Analysis-for-SQL-Server/ Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 9
- Job scheduling for Packages in SQL Server 2005 Thank you. I went trough the steps, but didn't really get far. I'm the only user on this pc, thus Server Name, Creator, Author etc. always refer to the same parameters. I ticked for my login all permissions available, but still... The job that I'm trying to schedule with the SQL Server Agent is still failing. It runs fine in BI Visual Studio and also runs ok if executed from Management Studio connected to Integration Services (Stored Packages\MSDB\Maintenace Plans\Package1) Another issue is the log file which doesn't explain much: I've tried to set up a log file (provider for text files with all events ticked) but this is all i get: "OnPreValidate,UKSERV01,NT AUTHORITY\SYSTEM,PACKAGE1,{A9D28A92-C509-4F1D-9630-0848B0036929},{FF9D650E-62D3-44F1-9D76-B34467E8DADC},01/09/2006 16:46:09,01/09/2006 16:46:09,0,0x,(null)" Thanks for the link anyway Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 10
- Using PrimaryKey as a PartitionKey I Have Large Table every week add 900,000 new records,and every week i archive data in partitions how can i use the PK as Partitionkey Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 11
- Problem configuring report server I had the same issue and this same solution worked for me. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 12
- Count ( Distinct Case ..) syntax error My code is below. I can't use the insert code feature because whenever I click on that it makes my whole post dissappear. I'm using Firefox. The error is: Incorrect syntax near 'distinct'. Select DepartmentDetails.DepartmentName ,ProjectCategory ,Count(Distinct Case When (StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0)) And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End) Over (Partition By DepartmentDetails.DepartmentName, ProjectCategory) [NewRequests] From #tempResourceAllocation Inner join dbo.DepartmentDetails On (#tempResourceAllocation.ParentDepartmentID = DepartmentDetails.DepartmentID) Order By ProjectCategory Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 13
- Message when performing end user sort Thanks for your reply. But I am not able to find a sort event for the report. The only events I get are the ReportSnapshotUpdated and ReportHistorySnaphot. Also from my application, I am just calling the Report server URL for the report viewer. Can you please tell me how I can proceed in thi scnario. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 14
- IF statement makes following statement invalid From BOL: CREATE RULE cannot be combined with other Transact-SQL statements in a single batch. Rules are also being deprecated. Consider using a constraint instead. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 15
- How to xfer db_owner from User back to where it belongs? That's a nice interface feature...put in colons together followed by a d and the smiley face shows up. ALTER AUTHORIZATION ON SCHEMA <use two colons here> db_owner TO db_owner -Sue Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 16
- How to store actual millisecond in datetime? Hi Zach. Thank for replying. Actually, the problem is still there. If you replace the date you are storing to '10/25/2006 23:59:59.991' and run the script. You will get '10/25/2006 23:59:59.990'. I need to be able to store the exact millisecond being written. This rounding being done to the millisecond is causing use a lot of headache. Any other idea Thanks again. Rick.. Tag: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
- 1
- Previous
Re: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
Madhu K Nair
AFAIK .. there is no drag & drop or copy paste feature for this in SSMO. If u want only data u can use select *into DB1.DBo.ObjectName from DB1.dbo.Objectname. If you want with exact schema, then script it from DB1 run it in DB2 and then copy the data using insert into statement
Madhu
Re: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
ggciubuc
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables
Re: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
Madhu K Nair
ggciubuc wrote:
Right click on first db->Tasks->Export data... and you will go in an easy way to copy your tables
this will cretae a table with only data not with all constraints. To create constratin u have to script it.
Madhu
Re: I want to 'Copy' 3 tables from an existing DB into a new DB on the same SqlServer instance.
ggciubuc
Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagrams
Yes it is and is a normal situation because in a new database in many cases, there are not same relationships between tables. To the other part these constraints can be easy rebuild with Database Diagrams