I have a DTS package on SQL Server 2000 that loads a table from a text file daily. The text file has 148 distinct fields that are mapped to a corresponding 148 columns on the table. I need to recreate the package to SQL Server 2005. Is there any shortcut to replicating the mapping from DTS to SSIS or do I need to redo from the beginning?
View 1 RepliesHello.
I am attempting to migrate an old third party app from 2000 to 2005 but I have a problem that I hope you can help me with.
To explain,
On 2000, in sysusers of the third party app db I have a username that contains a . This user has an associated server login (that uses SQL Server authentication) of the same name but without the .
so, the database user name is APP1 and the server login is APP1
I checked the SID's between the user and the login and they are the same which would suggest they are mapped to each other , however when I run sp_helplogins the login does not show as mapping to any user. The database user also has a different status, altuid and Isaliased value than any other user in sysusers but I confess to not knowing what the significance of this might be.
As far as I was aware you can't create a database user with a name that contains this invalid character so firstly I'm wondering how it got there. When I look at the databases access tab of the properties through enterprise manager of the APP1 server login, the APP1 user is not displayed. If the SID's between the login and the user match then shouldn't the APP1 user be displayed in the database access list? or have I got the wrong end of the stick here?
Despite all that, it isn't really a problem on 2000 because the app works ok. Now, to migrate it to 2005 I restored a full back up of the 2000 database and now the problem is that when I look in sys.database_principals the APP1 user is not there after I've restored it. It still shows in sysusers however. The main problem is that I cannot map it to a login using sp_change_users_login because I get the following error:
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'SQSUPD' is absent or invalid.
If anyone can tell me why this user does not show in sys.database_principals or more importantly, give me any pointers on how to resolve thie login mapping it would be much appreciated as I am now stuck!
Thanks
Hi Friends,
I have a small problem in parameter mapping for Execute SQL Task.
I am using a delete statement with 2 conditions.
Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider.
I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for
c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR.
i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype.
This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also,
I set the property RetainSameConnection = TRUE for oracle connection manager.
I am not able to trace this logical error.
The same is working fine in my local machine.
But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping?
What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and
inside the parameter mapping.
Any thoughts!
Hi,
how to clone a database in sqlserver and what are the pre-requisites
we need to take care before doing that if it is a production server,,, since we can not bounce the database ...
We need a drive cloning app, like Ghost, that will allow us to transfer a production image off of a HDD to other PCs. The problem with this is that
our production needs SQL in order to run and transferring the initial image to uniquely named workstations causes DB registration problems. Is there
an app that will allow us to configure this transferred image so that SQL will refer to the "new" drive instead in of the "old"?
Tengo una aplicacion desarrollada en POWERBUILDER y accede a una base de Datos SQL, deseo utilizar la misma aplicacion para acceder a otra base de Datos SQL en el mismo servidor. La segunda Base de Datos tiene que ser inicialmente un clon del original para hacer pruebas con los mismos usuarios. A nivel de la aplicacion puedo solucionar cambiando los parametros de nombre de base de datos, pero como puedo hacer a nivel del servidor?.
View 2 Replies View RelatedTengo una aplicacion desarrollada en POWERBUILDER y accede a una base de Datos SQL, deseo utilizar la misma aplicacion para acceder a otra base de Datos SQL en el mismo servidor. La segunda Base de Datos tiene que ser inicialmente un clon del original para hacer pruebas con los mismos usuarios. A nivel de la aplicacion puedo solucionar cambiando los parametros de nombre de base de datos, pero como puedo hacer a nivel del servidor?.
View 1 Replies View RelatedHi,
Can anyone provide the docs or steps for cloning a server.
RKNAIR
In my .NET code, with SQL Server 2000 as the backend, I need to create anexact copy, without data, of an existing table in the same database, thatthe user selects from a combo box. Any ideas on how to do this?
View 1 Replies View RelatedUsing SQL Server 2005 Express.I need to periodically duplicate an entire database and rename it. Mosttables are empty but some are to be prepopulated. I was thinking of havinga "template" database called perhaps "EmptyDatabase", and then copy thatinto a freshly created database with a new name.Has anyone coded anything like this?Thanks.GS
View 3 Replies View RelatedI loved the DTS feature that allowed saving DTS package as a VB model. With a bit of coding you could generate a number of packages from one template.
Are there any analogous solutions for SSIS? I cannot find anything.
My goal is simple. I have an ETL step that transfers data from staging dimension table to the corresponding star schema table in the subject matter database. I have two types of packages for SCD type 1 and type 2. Do you have any suggestions on how I can clone packages so that I don€™t have to go manually through each of them to replace certain items such as stored procedure names, etc.
Thank you!
Guys!!
How are you all? Im new to this forum as you can probably tell. I have joined up because i need advice in a matter and this seems like the best pace on the net for SQL Server help. Let me outline first what im trying to do;
Im part of a large company of roughly 5000 or so desktops and 3000 or so laptops. We currently have a project under way where we inted to build 50 or so identical laptops that will be used for testing mechanical equipment. Now the team involved have designed there own program that will connect to a main server in Head Office and transfer over the updated SQL server databases on the latop. every couple of days after doing tests, this tests - stored in a SQL DB - will be synced with the server to prevent loss of data.
Now my queston, is it possible to Clone an operating system with the following apps already installed;
SQL Server 2005
SQL Server Nature Client
SQL Server Setup Files
SQL Server Vss Writer
I made an inital clone, but when i went into the user groups, i found SQL server users with a PC name of the inital 'base build' PC. It seems these accounts cannot be copied from one machine to another.
Will the SQL database still run fine, or will there be big problems when copnnecting the the Server in Head Office?
Would it be better to just install the SQL Server components after a new latop has been created?
Anyway any help at all would be great on this matter, cheers!!
Hi Patrons,
Can any one tell me how and what are the steps to Clone the Database and how do the set of users (lets say 300) will be connected to that Cloned Database.
Your helping steps would be appreciated.
Thanks
Hi;
Can anyone please tell me how to do re-mapping? or give some resources address that I can learn easily?
Thanks.
hi! i have two different databases (SQL 2005 and Oracle) and i need to map their tables with one another. how will i do this?
thanks
I'm using NHibernate with SQL Server Express Edition and I've been problems with the data type to be defined in the "type" attribute on the "property" tag in the XML file mapping.Has someone worked with that?I need to match the appropriate NHibernate type to the following SQL Server Types: uniqueidentifier;bit;ntext;I have been getting "type mismatch" error when the code is trying access the data. Thanks a lot!
View 2 Replies View RelatedHi all, I have to provide user mapping between two databases on the same server so that the tables and content of both the databases can be accessed by each other. I know how to do it using Enterprise Manager, but I have to do it via script as in my asp.net application the databases are created dynamically. If I am not wrong, then "sa" login will be required to execute this script, but no problems as we will be having the "sa" password also. The server is sql server 2005.
Can anybody please let me know how to do it exactly. Its very urgent. Thanx a lot in advance.
hi friends..
i want to insert data in sql server2005 table through asp.net
column names of this table comes from diffrent tables.....
like Rollno--- in my new table....but in old table1 this is like RNO & from old table2 this is R_no
so i want insert this two old diffrent table data into my new table...but i have problem coz of diffrent column names of old table...
can you help me 4 that...
Hello,
I have a requirement to link two databases on the same server. Besides using the microsoft link option is there any other method by which we can link these two databases. Responses will be highly appreciated.
Thanks
I'm trying to find out the best O/R mapping tool for .NET. A perfect tool would begin with the database model and would be capable of generating 2 layers: the Domain layer (normal classes) and the Persistence layer (methods for storing and retrieving the domain layer objects from the database.
This tool would also be capable of generating the corresponding Visual Studio solution and projects.
I've already tried NetTiers, but it generates so many layers that the code is very dificult to manage and understand.
So any of you know a tool similar to the described above?
We have a Item table and a Price table. Structure is mentioned below. An Item need to be matched with the Price table (or vice versa is also fine) and get the Cost from the Price table. The challenge (which I feel :) ) is, mapping or Columns to be looked up between these two tables are not fixed.For an Item, Country, City and Number of Days columns can have a value or can be null. For ex, Paper, US, New York <10 days is one combination which should be matched to the Item table. For Paper, UK, NULL, <5 days is another combination which need to be checked. it is kind of dynamic look up of columns between the two tables.
Price Table
Item Country City Number of Days Cost
Paper US New York <10 100
Paper UK <5 150
Paper Chicago >10 200
Pen China <10 250
Item Table
Item Country City Number of Days
Paper US New York 5
Paper UK London 3
Paper US Chicago 15
Pen China Shangai 5
Paper China Beijing 15
Paper US Chicago 5
I need to get a list of users and databases that a sql login is mapped to. Is there a view or a sp that can get me this information?
View 18 Replies View RelatedIn an event of a system restore from development to production, what would cause the mappings for a database user to be whiped out?
Thank you in advanced for the clarifications.
Al
Hi Guys
I have a table with a column and value like below.
A1JAN
A1FEB
A1MAR
A1APR
A1MAY
A1JUN
A1JLY
A1AUG
A1SEP
A1OCT
A1NOV
A1DEC
A2JAN
A2FEB
A2MAR
A2APR
A2MAY
A2JUN
A2JLY
I want to map all these values to one value like AL AVG
How do i implement the solution so that all those different values map directly to one value.
Thanks.
I need to generate a document with the fields mapping from a DataFlow Task. Anybody knows how to do this?
Know I'm using screenshots from the destination object mapping.
Thanks
I read in my record. As we proceed down the pipeline, I want to put kind of record (with length 742) into one path, and a different kind of record (with length 242) into a different path. My problem is that I have (obviously) different fixed width column mappings for the two kinds of records. How can I change the column mapping mid-stream? Can I? Do I need to just write out to flat files and go from there?
Thanks!
Jim Work
Hello, I am developping a web application who is connected to SQLServer 2005 via the connection sring as below
<add name="LocalSqlServer" connectionString="Data Source=IBM7;Initial Catalog=Reclamation;Integrated Security=True "
providerName="System.Data.SqlClient" />
I want to know which user (bydefault ) who is connecting to the DB . and how is mapped this user with user in SQL server?
Thanks in advance
Hi,I use SqlDataReader to read one row from database and than set some properties to values retrieved like this:string myString = myReader.GetValue(0) // this sets myString to first value in a rowIf, however, I change order of columns returned by stored procedure myString would be set to wrong value. Is there a way to do something like this: string myString = myReader.GetValue["ColumnName"];
View 7 Replies View RelatedI am trying to attach a db to SQL Server 2005 and assign a User to the DB that can read and write. I am using one user (CreateDBs) to attach the DB and a second user (TestUser) to do the reading and writing. I run the following code and the DB is attached but I get the following error when I run the code below.
********************ERROR********************
System.Data.SqlClient.SqlException: Cannot open database "hello2" requested by the login. The login failed.
Login failed for user 'CreateDBs'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at NewSite.submit_Click(Object sender, EventArgs e) in X:InetpubRMA4NewSite.aspx.vb:line 120
********************CODE********************
'Need to attach to SQL Server
Dim objConn As New SqlConnection("Server=Web-testbedSQLExpress;uid=CreateDBs;pwd=ABC123;database=master")
objConn.Open()Dim objCmd As New SqlCommand("EXEC sp_attach_db @dbname = N'" & sitename & "', @filename1 = N'X:sites" & sitename & "Database" & sitename & ".mdf', @filename2 = N'X:sites" & sitename & "Database" & sitename & "_log.ldf'", objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()
objConn.Close()objConn = New SqlConnection("Server=Web-testbedSQLExpress;uid=CreateDBs;pwd=ABC123;database=" & sitename)
objConn.Open()objCmd = New SqlCommand("EXEC sp_addrolemember db_datareader, TestUser", objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()
objConn.Close()objConn = New SqlConnection("Server=Web-testbedSQLExpress;uid=CreateDBs;pwd=ABC123;database=" & sitename)
objConn.Open()objCmd = New SqlCommand("EXEC sp_addrolemember db_datawriter, TestUser", objConn)
objCmd.CommandType = CommandType.Text
objCmd.ExecuteNonQuery()
objConn.Close()
It still doesn't work if I use sa as the user. I know I am missing a step, what is it?
Hi
I have 2 tables defined as follows:
Table1 = uid, Field1, Field2, Field3 ... Fieldn, FormUID
Table2 = FormUID, Label, Position
When I query Table1 I would like to replace the column name of Field1...Fieldn with the Label from Table2 where the Position = n value of Field lable
e.g. lets say Table2 contains the following
1, customerName, 1
1, customerTitle, 2
1, customerDOB, 3
and Table1 might contain
1, Paul Jones, Mr, 21/09/1987, 1
when I query Table1 I would get
uid = 1, Field1 = Paul Jones, Field2 = Mr, Field3 = 21/09/1987
what I would like to get is
uid = 1, customerName = Paul Jones, customerTitle = Mr, customerDOB = 21/09/1987
I have up to 20 Fieldn columns so need to do this for all columns even if there is no matching columns.
any help would be great
regards
Hi all,
I'm not sure how easy of a task this is, or how to go about it, so I thought I'd ask here. I am doing a major overhaul to my site and am re-programming the backend which involves re-formatting my tables, table names etc.
What I want to do is set up a new table in the format I want for the new site, but still be able to have the live data that is being submitted to the site now, be entered into the new table so that I don't have to shut down the site for days to transfer and reformat data. Example
CURRENT SITE
Table 1
Field1: Name
Field2: Address
NEW SITE
Table 1
Field1: Name
Field2: myAddress
Field3: City
I want to be able to somehow map the two together so that when data is entered into the Name field on the current site, that same name is also inserted into the new site table as well. Mapping like this:
(current site)Name -> (new site)Name
(current site)Address -> (new site)myAddress
The city field would be left unaffected as that data would only be entered once the new site is up and running.
I know I could do this from a stored procedure on the exiting site or by re-writting all of my SQL to insert into both tables, but right now all of the SQL is hardcoded into the pages and I have A LOT of pages. Is there a way to map the tables together from within SQL Server?
Cheers,
Jeff
Has anyone used DTS packages for migrating old data to a new schema?
If so are there any tutorials on this?
I'd prefer not to do this by hand. ;-)
I have a question regarding BCP. We are doing a BCP operation from one machine to another machine. (i.e) Flat file from the web server are BCP on to the DB server. For this operation we have mounted the FTP directory of the web server on to the DB server. Say G: drive of the DB server is mapped to the Ftp directory of the web server.
Now when we run the BCP we get the following error.
SQLState = 08001, NativeError = 6
Error = [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (Create File()).
The connection is using NAMED PIPES.
Say when we BCP out the above error message comes but the flat file is generated with out any records in it.
Are we missing any parameter settings.
Please help.