Does anyone know whats the best to transfer sqlserver to new server but maintain the same Sqlserver name. The reason for this is that we have thousands of users out in the field and it would be too much trouble to identify these users and update the odbc entry to point to new sqlserver.
Thanks in advance for all your help.
Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?
thanks,
Henk
Hi,
I am trying to transfer data from a sql server 2000 database to a Sql Server 2005 database... and i just want to transfer around 2 tables.. and my sql server 2000 database is located in one machine and the 2005 database is located in the other machine.. So i am trying to insert data into the sql server 2005 database. So can i do it like this..
Insert into SqlServer2005comp.Databasename..TableName
(
Columns
)
Select
Columns
From
sqlserver2000comp.Databasename..tablenames
Where UserId = @UserId.
Is this the right way to do it.. and can i give this query in the sqlserver2005 db or the 2000 db
any help or ideas will be appreciated.
Regards...
Karen
I have written an SSIS package with a Transfer SQLServer Objects task which I want to use to copy database objects from a SQL Server 2000 database to SQL Server 2005.
When I run this task, I find the following error:
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Version80 database compatibility level is not supported
Is there a way around this aside from changing the compatibility level of my SQL Server 2005 database?
Any help is much appreciated.
Hi, I am attempting to create SSIS packages to extract data from our AS/400 DB2 databases and populate tables for analysis and reporting in SQLServer. We have recently installed a new 2005 SQL Server to replace our existing 2000 databases. I have several DTS packages setup to connect to the 400s and copy the needed data to SQL2000 and these work great but do not transition to SSIS very well. I can get my SSIS packages to work if I create a seperate data flow task for each table but that does not seem appropriate for a tool like SSIS. I would think that what I am attempting is a very common thing to do. Maybe other enumerators could be used but I have not been able to get it to work.
I am trying to use a foreach loop, currently with a forech item enumerator, to first truncate the table on the SQL Server database using an Execute SQL task, and the to use a Data Flow task to refresh the data using an ole db source and destination. I have the truncate portion working, but when I try to setup the Ole db source using a variable it tells me I have no input columns. I have a variable from the forech loop that loops through the table names, this variable is used in a second variable which is set to EvaluateAsExpression with the expression set to the follwoing: "select * from " + @table_name
I have set delayvalidation to true and validateexternalmetadata to false. What do I do in order to get around this issue? Also, how should the old db destination be set so that it load the data into the table specified in the variable?
Thanks for any help you can provide.
Keith C
Hi,
I am new to SQL Server 2005 SSIS Packages. I want to transfer data from multiple tables from sql server to oracle database. I cannot use export wizard as it creates new tables in the destination (oracle) DB. I already have tables created in the destination DB. When I created an SSIS package, it allowed me create package to tranfer data for only for one table from source to destination. I have created DTS packages in 2000, where you have source db and destination db and just add links for multiple tables. Is there a way I can do it in SSIS. Please let me know.
Hi,
I am trying to transfer data from a sql server 2000 database to a Sql Server 2005 database... and i just want to transfer around 2 tables.. and my sql server 2000 database is located in one machine and the 2005 database is located in the other machine.. So i am trying to insert data into the sql server 2005 database. So can i do it like this..
Insert into SqlServer2005comp.Databasename..TableName
(
Columns
)
Select
Columns
From
sqlserver2000comp.Databasename..tablenames
Where UserId = @UserId.
Is this the right way to do it.. and can i give this query in the sqlserver2005 db or the 2000 db
any help or ideas will be appreciated.
Regards...
Karen
I am using transfer manager in SQL 6.5 to copy a database and
all objects with data to another server. Transfer manager is not recreating all stored procedures. This even happens when I used it to another database on the same server. Any ideas??
Is it possible/advisable when transfering very large amounts of data from server to server to:
trasnfer the data to a new table first
second alter new table adding indexes, defaults, ets based on original table
if it is what flow item would be used to transfer/alter the indexes and defaults?
I'm very new to ssis so the more detail you can give the better.
Thanks
Hello:
I have been trying to run transfer manager to transfer all of the data
from the production database on one server to a test database on another
server(to refresh it). In order to make sure it runs on the server, I have
been scheduling it under EM to do so and I am pointing to the log on the
destination server on the EM Transfer panel.
For some reason, I am getting the following message in the destination
server error log:
99/02/16 10:24:41.42 ods Error : 17824, Severity: 10, State: 0
99/02/16 10:24:41.42 ods Unable to write to ListenOn connection
'.pipesqlquery', loginname 'sa', hostname 'TEMP09'.
99/02/16 10:24:41.42 ods OS Error : 232, The pipe is being closed.
99/02/16 10:24:41.42 spid17 Error : 1608, Severity: 21, State: 2
99/02/16 10:24:41.42 spid17 A network error was encountered while sending
results to the front end. Check the SQL Server errorlog for more
information.
I checked the event viewer error log and see no messages for today.
Can any one advise me what I need to do for this to run successfully?
Thanks. Any information furnished will be greatly appreciated.
David Spaisman
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
namespace Predict.Services
{
public static class Foo
{
public static string GetIntradayQuote(string symbol)
{
string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
request.Timeout = 1000;
HttpWebResponse response = (HttpWebResponse)(request.GetResponse());
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close();
response.Close();
return returnQuote;
}
}
}
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
Regards,
Skipper.
Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?„¢"
„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')
and select again voila i see
"Œ€„¢"
Does anyone have an idea?
Thanks
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
Thanks!!
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
View 2 Replies View RelatedI have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
Hello,
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
Thanks in advance,
TassosTS
Hello people.
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers.
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
Can someone help me find a solution to this?
Regarding KB935356, is there a "post" service pack 2 update for SQLServer Express?
Thanks.
Hello Expert!
I have 2 Database €“ Access & SQLServer(ver 7)
I need to Import Data TblShift from Access to SQLServer €“ using DTS I€™ve done this successfully!
Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)
In SQLServer, I have created table to store the date range as following:
TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006
How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?
Is this possible or any better solution for this?
TIA
Regards,
Hi Friends,
Can some please let me know the differences between sqlserver 2000 and sqlserver 7.0
Hi,
I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query:
SELECT * FROM SYS.Objects. It gives me following error:
Invalid object name 'sys.objects'.
Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.
Why is this difference? What is the difference when I login in these 2 different ways.
Any help would be appreciated.
Thanks in advance.
Any help
In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?
I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?
I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?
Hi every body,
I have SQLServer 2005 runs well for months and stop working after install SqlServer2005 SP1. I try to reinstall the SQLServer 2005 but I have problem when install work station component on my and the error is "There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor". Please help me to fix this bug. I do not want to reformat my machine.
John Dang
Hi,
I have the following method which transfers data SQL to SQL on the same server but when I try to change the destination server it won't transfer the data. The method runs through as expected with no exceptions or errors but with no data transfered.
Private Sub TransferSQLData(ByVal SourceDetails As Admin_upload.EnvironmentDetails, ByVal DestinationDetails As Admin_upload.EnvironmentDetails)
'Transfer database from source to destination.
Dim oPackage As New DTS.Package2
Dim oConnection As DTS.Connection2
Dim oStep As DTS.Step2
Dim oTask As DTS.Task
Dim oCustomTask As DTS.TransferObjectsTask2
Try
oStep = oPackage.Steps.New
oTask = oPackage.Tasks.New("DTSTransferObjectsTask")
oCustomTask = oTask.CustomTask
oPackage.FailOnError = False
With oStep
.Name = "Copy Database design and data"
.ExecuteInMainThread = True
End With
With oTask
.Name = "GenericPkgTask"
End With
With oCustomTask
.Name = "DTSTransferObjectsTask"
.SourceServer = "MYSERVER"
.SourceUseTrustedConnection = True
.SourceDatabase = SourceDetails.MetaDB
.SourceLogin = SourceDetails.MetaUser
.SourcePassword = SourceDetails.MetaPWD
.DestinationServer = "MYSERVER"
.DestinationUseTrustedConnection = True
.DestinationDatabase = DestinationDetails.MetaDB
.DestinationLogin = DestinationDetails.MetaUser
.DestinationPassword = DestinationDetails.MetaPWD
.CopyAllObjects = True
.IncludeDependencies = False
.IncludeLogins = False
.IncludeUsers = False
.DropDestinationObjectsFirst = True
.CopySchema = True
.CopyData = DTS.DTSTransfer_CopyDataOption.DTSTransfer_ReplaceData
End With
oStep.TaskName = oCustomTask.Name
oPackage.Steps.Add(oStep)
oPackage.Tasks.Add(oTask)
oPackage.Execute()
Catch ex As Exception
LabelUploadMeta.Text = "Failed to Upload MetaData: " & ex.Message
Throw ex
Finally
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
If Not (oPackage Is Nothing) Then
oPackage.UnInitialize()
End If
End Try
End Sub
This works fine but when I set the following within the method:
.DestinationServer = "ANOTHERSERVER"
It won't transfer the data.
I can access the remote server and read and write data to it.
Any ideas?
Hi ,
Can anyone suggest me to transfer a database from 65 to 70.
I trasfer a database using DTS, but i am only getting data. I need my users , views, stored procedures, permissions.
I need to upgrade three databases from 65 to another 70 server.
i also generated a script from 65, but which is showing errors.
Could u pls give any suggestions.
thank u.
--Raj
I recently transferred a db from our production machine to a test machine. After the transfer was complete I realized that all copied logins were copied with blank passwords. Is this default behavior? Can I set the transfer to include passwords?
Dave King
Hi,
stupid question but i have forgotten how to transfer a DTS package between servers, its been so long since i last had to!!!
TIA
Hi Everybody...
I have recently tried to transfer my database over from one machine to another, but failed to do so because it gets jammed on 30%. I am just wondering if there is any other ways I can take to migrate the database over.
I am currently running sql server 6.5 in concurrent with iis 4 and nt4. I am trying to seperate the iis to a new server machine and sql server to another server.
Please advise me which route to take if you have any suggestions and your solution is important to me. Thanks!
-Ken
Hi,
I want to transfer rows from one database to another.
Source My Source tables in database 'Staging'
CustomersEng
Name
Age
StCode
CityCode
InternalID
CustomersSpanish
Name
Age
StCode
CityCode
InternalID
CustomersEng and CustomersSpanish contains a replica..only difference being that 'Name' in the CustomerSpanish table is written in spanish language. The Stcode, citycode and Internalid together makes the row unique.
Code BlockSample script
Create table #CustomersEng (name nvarchar(20),age int, st_code int,
citycode int, internalid int)
Insert into #CustomersEng values('Aron',23,1,1,12);
Insert into #CustomersEng values('Cathy',28,1,1,13);
Insert into #CustomersEng values('Zarog',33,1,1,14);
Insert into #CustomersEng values('Michale',25,1,2,12);
Insert into #CustomersEng values('Linda',43,1,2,13);
Insert into #CustomersEng values('Burt',53,1,2,14);
Create table #CustomersSpanish (name nvarchar(20),age int, st_code int,
citycode int, internalid int)
Insert into #CustomersSpanish values('Arona',23,1,1,12);
Insert into #CustomersSpanish values('Cathylo',28,1,1,13);
Insert into #CustomersSpanish values('Zarogo',33,1,1,14);
Insert into #CustomersSpanish values('Michalo',25,1,2,12);
Insert into #CustomersSpanish values('Lindalo',43,1,2,13);
Insert into #CustomersSpanish values('Burto',53,1,2,14);
---------------------------------------------------------------------------------------------------------------
Destination tables in database 'CMO'
CustomerMaster
CustomerId (identity) (pk)
StCode
CityCode
InternalID
CustomerDetails [customerid and languagecode together are primary keys]
CustomerId (fk)
Name
Age
languagecode
Language
languagecode (pk)
languagename
Code Block
Create table #CustomerMaster (customerid int identity(1,1), st_code int,
citycode int, internalid int)
Create table #CustomerDetails (customerid int,name nvarchar(20),age int,
languagecode int)
Create table #CustLanguage (languagecode int, languagename varchar(10))
Insert into #CustLanguage VALUES (1,'English')
Insert into #CustLanguage VALUES (2,'Spanish')
Code BlockEXPECTED OUTPUT
CustomerMaster
1 1 1 12
2 1 1 13
3 1 1 14
4 1 2 12
5 1 2 13
6 1 2 14
CustomerDetails
1 Aron 23 1
1 Arona 23 2
2 Cathy 28 1
2 Cathylo 28 2
3 Zarog 33 1
3 Zarogo 33 2
4 Michale 25 1
4 Michalo 25 2
5 Linda 43 1
5 Lindalo 43 2
6 Burt 53 1
6 Burto 53 2
How can i write t-sql for this?
thanks
Hi All;
I know that in the SQL Server, the logins are not contained in the database (thanks to Kevin Yu) but I don't know where are they stored. For example if I want to transfer to another server I'll have to backup users too; but HOW?
In order to be more clear: When I simply take a backup from my server and restore it to my computer I can not login again with the user login information which is created on the server; However even if I make some changes; after backing the DB up from my computer and restoring to the server the logins are working (with re-mapping)
Thanks...
I had Sql Server 2005 installed on my machine, with that I am working on Visual Studio 2005. I just need to add an existing database into the app_data folder. The database is existing in the Sql server, it contains 180 tables so I cannot manually copy that tables in the app_data folder. for that is there any another way to copy all the tables into the database into the folder app_data with the data in the table.
Thanks & Regards
Girish Nehte
I recently transferred my SQL DB from one server to another, I backed the DB up, copied across and restored it into the new location, the problem is that I get the following error.
System.Data.SqlClient.SqlException: Invalid object name 'Table Name'
I have created a new user for this database and changed my code accordingly.
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Page Language="VB" Debug="true" %>
<Script Runat="Server">
Sub Page_Load
Dim conPubs As SqlConnection
Dim cmdSelectContacts As SqlCommand
Dim dtrContacts As SqlDataReader
conPubs = New SqlConnection("Server=Treebeard;UID=Intranet;Pwd=glglgl;Database=Contacts")
ConPubs.Open()
cmdSelectContacts = New SqlCommand ("Select * From Clientdata", ConPubs)
dtrContacts = cmdSelectContacts.ExecuteReader()
While dtrContacts.Read()
Response.Write( "<li>" )
Response.Write( dtrContacts( "c_forename" ))
End While
dtrContacts.Close
conPubs.Close()
End Sub
</script>
Any Ideas?
Hi everyone,I need some help transfering my database(SQL2005) to the host's server.In SQL 2000 I had the Management tools > DTS or ImportExport tools.SQL2005 lacks this, all I've got is Express Manager > can't connect to multiple databases.Can connect to one database and has a screen to excute TSQL commands.I don't want to have to recreate the whole database on the server.....This would be crazy... 150 stored procedures and 30 tables. Not to mention all the individual settings for each column.I know there is SQL Studio Management tools for the full version of SQL 2005. Anyone got any idea's ?Any input/suggestions is greatly appreciated.Thanks,JShep
View 1 Replies View Related