I have two SQL tables
Coupon and Coupon CouponStore
In Coupon there is a column of CouponID and in CouponStore the same column name.
Also in CouponStore there is a column called StoreID that has the value of each store 1, 2, 3 and 4.
How would I write a script to map any CouponID in the Coupon table to each Store Value that are not already mapped to a StoreID?
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,
I want to know if there's an easier way to do a calculation on the remaining total of the months. For example: month 09 would be (total *3 ) because 9+3 = 12 and there are 12 months in a year.
month total
01 2217.3700
02 2187.6600
03 2243.6500
04 2216.1000
05 2374.4200
06 2296.2200
07 2266.6000
08 2164.2600
09 2085.2400
recently i noticed that my trans Log size is not increasing(it is constant) , but the databse size is increasing at high speed.
why is it so? earlier the reverse used to happen i.e databse size increased only after a certain period. and log size increased at const. rate.
I have already created Tran. Replication for one database and it is working fine. But i have added 3 tables to the database and i want to do replication for that tables with same publications . I mean i can go to Publishers Properties and add articles but the problem is:
1)When i run the snapshot agent,it creates the snapshot whole database which is bigger in size .
2)Is there a way i can run snapshot agent for remaining articles in same publication.
3)How to do it step by step
Any help will be appreciated
Thanks
Experts
Hello,
I need to pull records from single table such that I get a subset defined like this:
(
acctcode = 'xh364'
and
product = 'T&E'
)
And return all of the rest of the records:
(
acctcode = '%'
and
product = '%'
)
Can I do this within a WHERE clause, or will this require CASE / ELSE? There will be other specific acctcode/product rules that will be added later. I could do this with a UNION, but I need to avoid that if possible.
Thank you for your help!
cdun2
Hi
I have seen the threads already in here about connections and SQL Server but I too am having a connection pool problem.
I am explicitly making a connection, creating a command object, executing a datareader then closing the command and then disposing the connection. The connection just prior to the dispose is definately closed.
However when I do an sp_who in SQL Server the connection is still listed. I have a connection timeout of 30 seconds, so I am considering reducing this to 5 seconds but dont wish to. So therefore when a process is called repeatedly instead of using an existing connection in the pool, it thinks that this connection is still active and therefore creates a new connection. Because all of this is in an ascx, I cant code for one connection to stay open for all 30 instances of the control.
This is a problem because I am creating controls at run time and using page.databind and at that point, I exceed the connection pool and therefore the site crashes out to the "SQL Server Timeout" error.
Anyone else seen this?
Hi,
My replication application need to be able to skip the from-last-stop remaining logs (that means to skip the logs generated from the last stop time of my replication application), how can i realize it? thanks.
I have a table 'library' with two columns
user_name, access_time
every user is having more than 500 entries, i want to keep only top 50 records for every user and delete the remaining records.
Hello everyone,
I have a package that extracts data from a Flat File. If any errors or truncation occur during the extraction of the input data, the package should fail. All fields that have erroneous values should be reported in the log file.
My Solution:
- I have created a Data Flow Task that contains a Flat File Source Adapter and a dummy destination.
- I have left the default "Error Output" configuration of the Flat File Source adapter, namely if a truncation or an error occur for a certain column, then the reaction is "Fail Component".
Problem:
This configuration gives me only the first erroneous column in the row being processed.
Question:
Is it possible to make the Flat File Source adapter continue parsing the current row before it fails? This way, I would be able to get all the erroneous columns in the row in one shot.
Thanks in advance...
Samar
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies View Related
Hi All,
I my requirement I need to read all the files from folder (one by one) and insert data from those files into the database table . I am facing one issue here . If suppose while executing if any of the file fails its not executing the other files. Package stops execution.
I cannot use Redirect rows option beacuse as per requirment if file has some Data problem , I am suppose to ignore the file instead of Data Rows.
Is there is any property in for each file task .....kindly suggest
Regards
Shagun
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.