hi,
i have developed an web-enabled database application using ASp.net, C# and sql server 2000.
now i want to implement transaction controls over the same
can anyone plz help me in implementing the same?
thanks in advance
I need to live update my SQL-Server 2000 database from AS400 database.
I usually use Access 2002 database between the AS400 database and the SQL-Server 2000 database to reflect any live update.
I use the (Link Tables...) option when creating a new table in access and link it with the ODBC to connect with the AS400 database.
After that I work with the Access link table.
Is there any tool in SQL-Server 2000 to show the live transactions in AS400 databasee so I can work on it?
Please advise
Hi Guyz
it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ?
Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2
"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:
Explicitly denying the ALTER DATABASE statement.
A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.
If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."
thanks in advance.
We just upgraded from SQL 7 to 2000 and for some reason I am unable to perform distributed transactions. I keep getting the following error:
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransaction Join:: JoinTransaction returned 0x8004d00a]. Operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction
I tried a few of the fixes recommended on the microsoft website but they did not solve the problem. We are still using the same O/S (Windows 2000 Pro), so the only thing that has changed is the server.
Any help would be appreciated.
i have two computers with Sql server 2000 std edition(SP4) and windows 2003 std edition(SP1).
how should i do to balance the loading of sql server and sync these two servers?
The sql server mirroring means that if one server is down, then the other one will replace it?
Hi,
I have created a ASP.net website and i have a page called Investment choices where the user can either type in the data in a text box or there they can select them from a grid view(called Funds). The Typed data or the selected data goes in to another datagrid(called plans). and i want to implement a display order.
right now when the user adds 6 numbers using the text box and since i seperate them using commas. In Plans datagrid i get the numbers from 1-6. then after words if the user selects the from the Funds gridview it just its adds a number 1.
So how can i have the display order sequentially so if the user types 6 no's it should be 1- 6 and if he selects 2 funds from the gridview it should be 7 and 8. I have a field in the database which is called display order which is a integer. so do u think it makes sense as making that field as an identity field or how should i deal with it.
Any ideas will be appreciated.
Regards,
Karen
I need to perform some operations that seem to require multiple nested tables.
Is the following code the way I should be attempting this or is there a better way?
Here is the sample code:
SELECT tt.tdl_ID, tt.tx_ID, adj.Prof_Chgs, rc.Rvu_Comp
FROM Table1 as tt
LEFT JOIN (
SELECT tt1.Tdl_Id,
CASE
WHEN tt1.tdl_ID = '1000'
[code]....
Hello,
I want to set up As400 DB as a publisher and the sql server 2005 as a subscriper, so that I can read some data from AS400 DB in sql server 2005 and every evening, I need the replicated data from AS400 to be refreshed.
Can I do it ? If yes, how to do it?
Thanks
We need to implement incremental load in database. A sample scenario is, there is a view (INCOMEVW) which is build on top of a query like
CREATE VIEW INCOMEVW
AS
SELECT CLIENTID,COUNTRYNAME,SUM(OUTPUT.INCOME) AS INCOME
(SELECT EOCLIENT_ID AS CLIENTID,EOCOUNTRYNAME AS COUNTRYNAME,EOINCOME AS INCOME FROM EOCLIENT C INNER JOIN EOCOUNTRY CT ON
C.COUNTRYCODE=CT.COUNTRYCODE
[code]...
This is a sample view. As of now there is a full load happening from the source(select * from INCOMEVW) and loads to target table tbl_Income.We need to pick only the delta and load to the target table using a staging. The challenge is,
1) If we get the delta(Insert,update or deleted rows in the source tables EOCLIENT,EOCOUNTRY,ENCLIENT,ENCOUNTRY, how to load the incremental to
single target table tbl_Income.
2) How to do the Sum operation with group by in incremental load?
3) We are planning to have a daily incremental load and thinking to create the same table structure as source with Date and Flag column to identify
the date and whether that source row is an Insert or Update or Delete with the flag. But not sure how to frame something like this view and load to single target with Sum operations.
Hi,
I need to synchronize the data between two SQL Server 2005 Express.
The database is really simple, just few tables (but data are inserted very often) without any triggers.
Since Express edition could not behave as a Publisher I was thinking about implementing manually a sort of Replication mechanism... It should be a sort of Transactional Replication with queueud updating.
Does it sound a good idea?
Has anyone done this before o there are better solution?
Thanks in advance,
Stefano
Hi All,
This is the code for calculating a formula field in Crystal Reports.
I want to implement the same in Sql Server Reporting Services..
But it doesn't have the feature of For Loop....
The strings started with @ are formula fields....
Can anyone tell me, how can the below code be implemented in Sql Server Reporting Services
numbervar YR;
for YR := 1 to {@CalcFiscalAge} step 1 do
(
IF YR = {@CalcLifeCode} +1 then
locFactor1 := 1;
exit for;
IF (YR = 1 OR YR = {@CalcFiscalAge}) THEN
HALF_YEAR := 2
ELSE
HALF_YEAR := 1 ;
LINEAR := ROUND(REM/ ({@CalcLifeCode}-YR+1.5)/HALF_YEAR,4);
MACR := ROUND(REM / {@CalcLifeCode}*{@CalcSRate}/HALF_YEAR,4);
IF MACR >= LINEAR then
DEP := MACR
ELSE
DEP := LINEAR;
locFactor1 := locFactor1 + DEP ;
REM := 1 - locFactor1;
locFactor := locFactor1;
);
Thanks in advance
Regards,
Hi,
We are developing the web application using ASP.NET 2.0 using C# with Backend of SQL Server 2005.
Now we have one requirement with my client, Already our application live in ASP using MS Access Database. In Access Database our client already wrote the query, those query call the another query from same database. So I want to over take this functionality in SQL Server 2005.
In ASP Application, We call this query €œ081Stats€? from Record set.
This €˜081Stats€™ query call below sub query itself
Master Query: 081Stats
SELECT DISTINCTROW [08Applicants].school, [08Applicants].Applicants, [08Interviewed].Interviewed, [Interviewed]/[Applicants] AS [interviewed%], [08Accepted].Accepted, [Accepted]/[Applicants] AS [Accepted%], [08Dinged].Dinged, [Dinged]/[Applicants] AS [Dinged%], [08Waitlisted].Waitlisted, [Applicants]-[Accepted]-[Dinged] AS Alive, [08Matriculating].Matriculating, [Matriculating]/[Accepted] AS [Yield%]
FROM ((((08Applicants LEFT JOIN 08Interviewed ON [08Applicants].school = [08Interviewed].school) LEFT JOIN 08Accepted ON [08Applicants].school = [08Accepted].school) LEFT JOIN 08Dinged ON [08Applicants].school = [08Dinged].school) LEFT JOIN 08Waitlisted ON [08Applicants].school = [08Waitlisted].school) LEFT JOIN 08Matriculating ON [08Applicants].school = [08Matriculating].school;
Sub Query 1: 08Accepted
SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 2: 08Applicants
SELECT statusTbl.school, Count(1) AS Accepted
FROM statusTbl
WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 3: 08Dinged
SELECT statusTbl.school, Count(1) AS Dinged
FROM statusTbl
WHERE (((statusTbl.decision)=0) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 4: 08Interviewed
SELECT statusTbl.school, Count(1) AS Interviewed
FROM statusTbl
WHERE (((statusTbl.interview)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
Sub Query 5: 08Matriculating
SELECT statusTbl.school, Count(1) AS Matriculating
FROM statusTbl
WHERE (((statusTbl.userdec)=True) AND ((statusTbl.yearapp)="2008"))
GROUP BY statusTbl.school
ORDER BY Count(1) DESC;
So now I got the solution from SQL Server 2005. I.e. Common Table Expressions, So I got the syntax and other functionality, Now my doubts is how do implement the CTE in SQL Server 2005, where can I store the CTE in SQL Server 2005.
How can I call that CTE from ASP.NET 2.0 using C#?
CTE is replacing the Stored Procedure and Views, because it is memory based object in SQL Server 2005.
How can I implement the CTE, where can I write the CTE and where can I store the CTE.
And how can I call the CTE from ASP.NET 2.0 using C#.
It€™s Very Urgent Requirements.
We need your timely help.
With Thanks & Regards,
Sundar
Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
Hi,
I want to implement site search in my application, I got the solution of searching the static content, but most of the pages in my application is content based(content is coming from database at runtime), so those content I am not able to search from database.
While searching about the same, I got information about Full Text search (FT) in SQL Server 2005, but whether it will work in SQL Server 2005 Express or not?
I didnt get exact steps to implement FT on my database so far....
Any help?
Hi All,
I am now working on the design phase of my project, we are looking to implement Change Data Capture (CDC) but i need some help if you guys has implemented before using the SSIS 2005 componets. I am trying to use the Following:
Source---------Derived Column---------Lookup---------------Conditional Split (to split New records and Updated Records)-----------Destination. Respectively.
Lets make it clear, my source holds (Old records and newly added or Updated records), the Derived Column is to Derive new columns called Insert_Date and Update_Date. The Lookup i am Using is to look the Fact_Table(the Old Records) as Reference, and then based on this lookup i will split the records on timely based using the Conditional Split. My question is
1. Am i using the right components?
2. what consideration should i have to see to make it true (some Logics on the conditional split)?
3. Any script which helps in this strategy?
4. If you have a better idea please try to help me, i need you help badly.
Thank you,
SamiDC
I need to implement a file system for an application that allows me to roll back to a point in time. I can do this with either a journaling file system (Unix based such as JFS) or with a database file system such as Oracle Internet File system (now Oracle Content Services). I would MUCH prefer to use SQL Server but cannot find anything that supports this other than a 2000 ppt referencing the then up and coming SQL Server .NET File System.
The application(s) in question are older and store data in proprietary data files and need to access a local (or mapped) drive in standard form (d:programsmyprogram).
Does SQL Server 2005 or 2008 support this type of access? I have searched but cannot find anything to support this.
Thanks.
B.W.
I am using ADO.Net for data access and was wondering if anyone knows a good resource for information of sql transactions? Also, do you know if the ForEach statement can be made in sql transactions?
View 3 Replies View RelatedHi,I am currently having dificulties in finding a way to use transactions while using classes for table representation.Let me explain, i have two tables a Customers and a Movements one, so two tables = two classes. Each class supports Insert, Edit and Delete, so if this tables worked seperatly there would be no problem but in this case whenever i create a customer i must create a movement for that customer so the connection don't pass between classes and i cannot use transactions ;(Is there any brilliant way to use transactions any other way? Even between connections or getting a solution for my implementation?Best Regards,Luis Simões
View 1 Replies View RelatedHi,I am new to SQL Server Administraion, and straight away thereoccured a case in which some users modified the database and I have totrack 'em out something this :-A table was modified on 17 - 03 -2006 and now I want to see whatqueries occured on that day how can I do that! I have seen thetransaction log but it ain't helpful. Do help me out!
View 1 Replies View RelatedHello guys!
I am working with SQL Server 2005 a few months, and i need your help about transactions topic.
In first, i hope you are patient with my english, i will try to explain it the best i can ;-)) and now... my question and thanks you in advance.
I am trying to set the transaction option of a foreach loop container task in run time using the Expressions option. There, I have configured the transaction option of this task with a variable value (var type int) and this variable is informed at the beginning of my dts using a script task (values: 0 for Not Supported, 1 for Supported, 2 for Required).
The problem is the transaction option gets the variable value but... the variable value by default. After, during the dts execution, the variable gets the new value but not the transaction option.
In summary, my question is ... is it possible to set the transaction option during the run time?.
Thanks for your help!
Chris.
I have a requirements to collect Transactions per second on 50 databases from a sql server instances.provide me a script to collect TPS for 50 databases?
View 8 Replies View RelatedI have a million plus line program that uses DAO and JET/Access databases. We are modifying the code so that it will work with BOTH JET and SQL Server databases with an eye to moving to just SQL Server eventually.
Much of the code works just fine without change when using DAO and SQL server as 90+ percent of the code is accessing data READ-ONLY. (Jet's ODBC capability is used)
There are however some transactions that will not work.
When I examine the SQL Trace, I notice that after the transaction has begun, the SPID has changed. This happens often when cursors are used, BUT happens even if the only activity after a DBEngine.BeginTrans is issued DO NOT USE CURSORS as in the following code:
DBEngine.BeginTrans
strSQL = "INSERT INTO [TestTable] (MyKey,MyData, MyData2) VALUES (1,'apple','fruit')"
DB1.Execute strSQL
strSQL = "UPDATE [TestTable] SET [MyData] = 'apple2' WHERE [MyKey] = 1"
DB1.Execute strSQL
DBEngine.Committrans
The second DB1.Execute times out: [Microsoft][ODBC SQL Server Driver]Timeout expired
The trace shows a new SPID is opened when the UPDATE query is executed. A new SPID is spawned and the JET engine is sending an SELECT SQL to get the value for the MyKey:
SELECT "dbo"."TestTable"."MyKey" FROM "dbo"."TestTable" WHERE ("MyKey" = 1 )
The second SQL statement is waiting for the first one that is being done in a transaction to be completed. The program is expecting that both of these will be being done on the SAME SPID or at least the same transaction scope.
So, since I have no way of knowing how and when a new SPID will be spawned using DAO, we are not going to use it for when we do transactions.
I have tried to use ADO (Provider=SQLOLEDB.1), but ADO has the same problems as DAO as far as a new SPID being spawned in some circumstances, but it works in more situations than DAO.
The Connection Object is not guaranteed to be attached to a specific SPID - it can change at will. So Cn.Execute done twice in succession may be done on separate SPIDs.
My question is this: Is there a way to make sure that no new SPID is spawned?, Is there a way to know when a new SPID will be spawned so we can know how to avoid this situation? Is there a better way than using ADO? Keep in mind that this program has to be able to use BOTH Access/Jet databases and SQL server.
Note:
I have spent over 160 hours reading about Jet, the VB Guide to SQL Server, searching MSDN, Google etc. No article really does justice to this subject of transactions using DAO, ADO and SQL Server and the issue of the SPIDs (equivalent to a session and the session is the scope of the transaction). If you could be sure to stay on the same SPID, then you could just issue your own SQL Begin Transaction and control it all if you only plan on doing SQL statements without the need for cursors. A Microsoft article on this subject would be really helpful to all the programmers that are just now getting around to migrating to SQL Server.
Also, note, that if we did not do transactions, then we would not have to modify a single line of our code to make it work with SQL Server.
There is virtually no documentation for much of what I have written about in this Thread. I am very surprised as it seems this would have come up for thousands of programmers. I must be missing something, so thank you in advance for your help.
Hi,
i wanna know how to use rollback in sql server without actually using transaction...
is there any way i can do that?
thanx in advance
Hi
I have a couple of sql servers registered in EM which are outside of the domain. Whenever I run queries on them with the query analyzer, they seem not to commit the transaction. When I'm about to close down the window, I'm always told that there are uncomitted transactions.
Is this some kind of a bug, and is there something I can do about it?
Thanks
Martin N Jensen
Hi all,
When i begin a distributed transaction..., do i need to always
set xact_abort on?
But when i tried to put custom error handling to roll back a transaction with a linked server , it seems its impossible (as set xact_abort on, already automatically rolls back the transaction)
Does anyone have any ideas on this?
Thanks in advance
Benny
Hi,
I've tried to enclose a few database operations in a TransactionScope block but it looks like SQL Server CE RM does ignores ambiental transaction.
Here is the code:
static void TestTxn() {
// Command to insert an integer in a table with a single integer column
string cmdPassText = "INSERT TESTTABLE (INTFIELD) VALUES(1)";
// Command to force field type mismatch exception
string cmdFailText = "INSERT TESTTABLE (INTFIELD) VALUES('Foo')";
using (TransactionScope scope = new TransactionScope()) {
using (SqlCeConnection conn = new SqlCeConnection("DataSource = 'Test.sdf'")) {
try {
conn.Open();
SqlCeCommand cmdPass = new SqlCeCommand(cmdPassText, conn);
returnValue = cmdPass.ExecuteNonQuery();
SqlCeCommand cmdFail = new SqlCeCommand(cmdFailText, conn);
returnValue = cmdFail.ExecuteNonQuery();
}
catch (Exception ex){
Console.WriteLine("Command failed");
Console.WriteLine("Exception Message: {0}", ex.Message);
}
}
scope.Complete();
}
}
After first command suceeds and seccond command failes table still has one affected row after transaction.
Am I doing something wrong or System.Transactions.Transaction is not supported with SQL Server CE RM?
Thanks,
Aleksandar
I have a SQL Server 2005 with a linked server to an Oracle RDB 6.0, using the Microsoft OLE DB for ODBC provider. I would like to select some rows from the Oracle RDB and then Update those specific rows afterwards. Whenever I try to do this in a transaction (BEGIN TRAN) I get an error "Driver not capable". Is there any way to do this in a single transaction?
View 1 Replies View RelatedHi ,
I got problem in production server at client place(No backup copy & not replicated,it's a SQL SERVER 2000 Enterprise server),by mistake client updated the data without using where condition then updated lakhs of rows (in SQL server autocommited),Now I need to recover this data from LOG file(.ldf).I tried with LOG EXPLORER(Third party tool) Trail version recovered from default database(Northwind,Pubs).But client not willing purchase this S/W for simple cause,How can we recover the data from LOG file.
1.Can we write the Program in C# to read the SQL Server Log and show the past transactions?
2.Is There any Stored procedures exist in SQL Server to read the day transactions in log file and take the backup?
3. How to read Transactions Log file in SQL Server 2000?
It's very Urgent,I am not expert in SQL Server 2000.
Could U Please Help me with possible solutions.
Regards,
Harikrishna.
Hello,I'm trying to follow some sql sentences that my system send to SQL 2005express and I don't have a deep knowlegde of databases. I know thatthere's a transactions log that keeps all sentences that go intodatabase motor. Is it correct? in case yes, is there a way to look atthis archived sentences?Thanks in advanceIgnacio
View 1 Replies View RelatedI'm trying to create a transaction replication from SQL Server 2000 to 2005. Basic replication between the servers works just fine. However, what I want to accomplish is to be able to skip some of the transactions. Example - from time to time we want to purge some of the historical data from the main database (the publisher). We don't want the same purging to occur on the destination database, which will be used for reporting purposes and needs to include all the historical information. I wanted to simply stop the replication log reader, purge the records, backup the transaction log with truncation and then restart the reader. The only problem - the truncation on the replicated database keeps the transactions of the purging until they are replicated, so the transaction log backup doesn't help. Any ideas would be greatly appreciated!
View 5 Replies View RelatedHi All,
I am writing an interface between SQL Server and Access. I have chosen to use a linked server using the Microsoft.Jet.OLEDB.4.0 provider. One of the requirements is to have multiple SQL statements in one transaction, which is where I am encountering the problem. (see below)
BEGIN TRAN
INSERT INTO ACCES...Table
Msg 7390, Level 16, State 2, Line 2
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "HES" does not support the required transaction interface.
I am having a really really hard time finding documentation on the Microsoft.Jet.OLEDB.4.0 provider, specifically I want to know for sure if it supports the ITransactionJoin interface.
The main question is - does MS Access linked servers support transactions?
Thank you for your help,
I have exausted all resources available to me in finding out the answer.
Thanks,
Eric
May i have a list of Which queries or codes couldn't use in a Transactions in Sql Server ?
I have a piece of code that has happily run daily for at least 4 years. I have moved it to a machine that is meant to have an identical set-up, and it fails. I need to work out why it fails, but I am stuck. The code works as follows:
CREATE storedProc1 (Parameter1, etc)
DECLARE @TransactionName nvarchar(127), @TransactionActive bit
-- Plus Other Variables...
BEGIN
BEGIN TRY
SET @TransactionName= 'TransactionName1'
SET @TransactionActive = 'True'
BEGIN TRANSACTION @TransactionName WITH MARK
[Code] .....
When I EXECute StoredProc1, it fails with the Error Message "Cannot roll back . No transaction or savepoint of that name was found." I think that message comes from a ROLLBACK TRANSACTION statement in the CATCH of StoredProc2. I have tried commenting out that ROLLBACK statement, which changes the error message to: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2."