Need Some Explanation On Transactions
Apr 28, 2005
I am having some problems with transactions, although it seems that the problem exists within the vb code making the db call it may be that sql is the source of the problem.
1.
There is a number of stored procs that contain transactions most of which are inserts followed by a select statement to retrieve the most recently added ID. So to start with is a select the best way or is the @@Identity. I have read the @@Identity is global, so for a external server that is running a number of databases I stayed away from it. Did I take the right actions or is there a better way?
2.
Is having sql transactions within a call from vb.net ok? I know that the sqlClient class doesn't support nested transactions, but does that include transactions within sql?
3.
if a stored proc is called, from vb contained in a sqlClient transaction, are ALL the calls from within the sproc able to be rolled back?
If there is a reliable way to obtain the Identity without containing it in a transaction, and having the internal sql transactions in the problem, then I am home free, so I am hoping this is the case.
Thanks for any help
View 11 Replies
ADVERTISEMENT
May 22, 2005
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
View 3 Replies
View Related
Jan 22, 2008
Hi,
I have a project that has been given to me and need help please. The complete class is as follows
Public Class CarAccessData
Public Function Getcarinfo() As List(Of CarInfo)
Dim AllCarInfo As New List(Of CarInfo)
Dim SQL As String SQL = "SELECT [SKU], [CarMake], [CarModel], [Carprice]"
Dim MyConnection As SqlConnection MyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("AntConnectionString1").ConnectionString)
Dim aCmd As SqlCommand aCmd = MyConnection.CreateCommand
aCmd.CommandText = SQL aCmd.CommandType = CommandType.Text
Dim aDataReader As SqlDataReader
Try
MyConnection.Open()
aDataReader = aCmd.ExecuteReader
While aDataReader.Read()
AllCarInfo.Add(New CarInfo(aDataReader)) End While
Catch ex As Exception Throw ex Finally
aDataReader.Close()
MyConnection.Close() End Try
Return AllCarInfo End Function
End Class
The bit I dont quite understand is the following snippet from above
Dim aCmd As SqlCommand aCmd = MyConnection.CreateCommand
aCmd.CommandText = SQL aCmd.CommandType = CommandType.Text
Dim aDataReader As SqlDataReader
Try
MyConnection.Open()
aDataReader = aCmd.ExecuteReader
While aDataReader.Read()
AllCarInfo.Add(New CarInfo(aDataReader)) End While
Catch ex As Exception Throw ex Finally
Can anyone explain this in real ABC style step by step please, just so I can start to understand this, ( I am quite new to this)
many thanks,
Anteater
View 8 Replies
View Related
Jun 1, 2004
the statement
if ((columns_updated() & 2 + 4 + 8)) > 0) is supposed to tell me if the 2nd, 3rd or 5th columns were updated. My question is, what desginates the column 2,3,5, when 2,4,8 are in the statement
View 1 Replies
View Related
Jul 23, 2005
PREDICATESUsed as a clause.A. What does PREDICATES mean?B. What does it mean when used in a where clause?I checked BOL (Glossary) but get no explanation there.ThanksJay
View 5 Replies
View Related
Oct 31, 2007
I am wondering what are od1 and od2 used for in SQL Queries? Are they used for joining... can anyone explain their significance in the queries below... (especially the commands in red)
USE Northwind
SELECT OrderID, CustomerID
FROM Orders o
WHERE 20 < (SELECT Quantity
FROM [Order Details] od
WHERE o.OrderID = od.OrderID AND od.ProductID = 23)USE Northwind
SELECT DISTINCT ProductName, Quantity
FROM [Order Details] od1 JOIN Products p
ON od1.ProductID = p.ProductID
WHERE Quantity = (SELECT MAX(Quantity)
FROM [Order Details] od2
WHERE od1.ProductID = od2.ProductID)
View 3 Replies
View Related
Jun 20, 2007
can anyone explain how this works:
USE NORTHWIND
GO
SELECT freight
FROM orders E1
WHERE (N =
(SELECT COUNT(DISTINCT (E2.freight))
FROM orders E2
WHERE E2.freight >= E1.freight))
replace N by a number. To find that Nth value from the table.
Ashley Rhodes
View 2 Replies
View Related
Jul 23, 2005
In T sql for sql server, what is the technical difference between thecomparisons "is" and "="for example:set @test = nullprint @test is null -> trueprint @test = null -> false
View 3 Replies
View Related
May 31, 2006
Ok, here is a asample table representing the problem more clearlyA | B | C | D-----------------a1 b1 c1 d1a1 b2 c2 d2a3 b3 c1 d3a4 b4 c4 d3a5 b5 c5 d5a6 b6 c6 d3Tha duplications are:row 1+2 in param Arow 1+3 in param Crow 3+4+6 in param Donly row 5 is unique in all parameters.conclusion: row 1+2+3+4+6 are the same usergoal: to find all duplicated rows & to delete them all accept oneinstance to leave.Note:Finding that row 1similar to 2 in A & deleting it will loose databecause we won't know that row 1 is ALSO similar to 3 on C & later onfinding that 3 is similar to 4 & 6 on D & so onThe simple time consuming (about 2 weaks) query to acomplish the taskis:SELECT count(*),A.B,C,DFROM tblGROUP BY A,B,C,DHAVING count(*)>1I THANK YOU ALL
View 3 Replies
View Related
Feb 22, 2000
When a database is created in SQL Server, 4 files are made:
(example)
DB_Data.DAT
DB_data.mdf
DB_Log.DAT
DB_log.ldf
What do each of these files contain? I can figure out that the main database is the DB_Data.DAT, but why is the transaction log a .DAT and why is there four files instead of two? etceterea.
Llyal
View 3 Replies
View Related
Jan 15, 2001
I went to Microsoft to find some info about the function Instr. I need to
perform a search with a string similar to their example I found below. Can
anyone explain to me Microsoft's example?? I am little confused by the
parameters used and the explanation it gives back to me??
Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.
SearchChar = "P" ' Search for "P".
MyPos = Instr(4, SearchString, SearchChar, 1) ' A textual comparison
starting at position 4. Returns 6.
MyPos = Instr(1, SearchString, SearchChar, 0) ' A binary comparison
starting at position 1. Returns 9.
MyPos = Instr(SearchString, SearchChar) ' Comparison is binary by default
(last argument is omitted). Returns 9.
MyPos = Instr(1, SearchString, "W") ' A binary comparison starting at
position 1. Returns 0 ("W" is not found).
My problem is this:
I need to scan within SearchString for blanks/spaces characters. When I
find one, then place the values to the left and right of it in seperate
columns. For example, I would need to scan 'John Smith A' and then place
'John' in FirstName column, 'Smith' in LastName column, and 'A' in MidName
column.
I think this is how my code would read, but I am confused on how to place
the results into my table to the correct columns?
my search string would be SearchString = 'John Smith A'
my SearchChar would be SearchChar = ' ' (note I am searching for a
space/blank character)
So would then my code be like:
Dim SearchString, SearchChar, MyPos
SearchString = 'John Smith A'
SearchChar = ' '
MyPos = Instr(1, SearchString, SearchChar, 0)
How do I get whatever is returned from the Instr function to a column in a
table??
Any help would be great.
Rey
View 1 Replies
View Related
Apr 30, 2008
We've read about a single installation of SSIS applies to all SQL instances on a server. We've also read how by default SQL Server stores SSIS packages in the MSDB database on the default instance and if you want to have packages stored on a named instance you must modify msdtssrvr.ini.xml. Here are our questions.
1 - If msdtssrvr is modified to reference a named instance, such as <ServerName>SQLTESTINST1<ServerName>, when I connect to Integration Services through Management Studio do I still specify the default instance name in the login window? That's the only way we have been able to connect and despite specifying the default instance name, the packages displayed are those of the named instance.
2 - If I am correct that you must always specify the default instance name when connecting to Integration Services, then in order to eliminate confusion regarding what SQL instance's SSIS packages I am viewing under the folder structure I can create SQL Server folders by modifying msdtssrvr.ini.xml. For example:
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB-INST1</Name>
<ServerName>SQLTESTINST1</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB-INST2</Name>
<ServerName>SQLTESTINST2</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
I then restart the SSIS service and can now see the following folder structure.
Running Packages
Stored Packages
+ File System
+ MSDB
+ MSDB-INST1
+ MSDB-INST2
If I expand MSDB-INST1 I will only see packages saved on the instance named INST1. The same is true for INST2. If I expand MSDB I will only see packages saved on the default instance.
Does this sound like I am doing things correctly?
Thanks, Dave
View 3 Replies
View Related
Jun 26, 2007
Hello,
In the ErrorLog of my Sql Server , i found this line :
2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Operating System XP Home SP2
SQL Server 2005 Express Edition with Advanced SP1 )
( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express
My problem is :
i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )
i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN
How can i do it ? ( activating NTLM ? but how ?)
I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.
As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.
I don't know whether i'm querying with the "correct" forum.
Sorry for my poor english.
I shall appreciate any help about this problem
Have a nice day
View 1 Replies
View Related
Jun 9, 2007
Hello Everyone,
I am reading DataMining Tutorial and right now I am at the Mining Algorithms section. I cannot understand any of the algorithms. For example take the following text... what a bunch of mouthful bla bla bla it is ....
"The Microsoft Decision Trees algorithm supports both classification and regression and it works well for predictive modeling. Using the algorithm, you can predict both discrete and continuous attributes.
In building a model, the algorithm examines how each input attribute in the dataset affects the result of the predicted attribute, and then it uses the input attributes with the strongest relationship to create a series of splits, called nodes. As new nodes are added to the model, a tree structure begins to form. The top node of the tree describes the breakdown of the predicted attribute over the overall population. Each additional node is created based on the distribution of states of the predicted attribute as compared to the input attributes. If an input attribute is seen to cause the predicted attribute to favor one state over another, a new node is added to the model. The model continues to grow until none of the remaining attributes create a split that provides an improved prediction over the existing node. The model seeks to find a combination of attributes and their states that creates a disproportionate distribution of states in the predicted attribute, therefore allowing you to predict the outcome of the predicted attribute"
In the above text what is meant by discrete and continious attributes? what is regression? what is predicted attributes? what are input attributes? what is distribution of states?
Is there a source which explains these algorighms in a easier way ....
View 4 Replies
View Related
Sep 21, 2005
I've been reconfiguring my Windows service accounts for the SQL Server service and the SQL Agent service to comply with the security best practices for SQL Server 2005. Specifically, I created two new network accounts. One account runs the SQL Server service, the other runs the SQL Agent service.
View 1 Replies
View Related
Jan 7, 2008
If you know the answer please explain what you're doing if possible, that'll help me :)I have the following tables:CREATE TABLE [dbo].[tblUserData]( [UserCode] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DisplayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) ON [PRIMARY]CREATE TABLE [dbo].[tblFriends]( [UserCodeOwner] [int] NOT NULL, [UserCodeFriend] [int] NOT NULL, [createdate] [datetime] NOT NULL CONSTRAINT [DF_tblFriends_createdate] DEFAULT (getdate())) ON [PRIMARY]in tblFriends relations are stored twice, so for a relation between user 5 and 6, there will be 2 rows: 5-6 and 6-5Now, I want to get the columns (UsercodeOwner,UsercodeFriend,createdate,username,displayname) for relations that were created in tblFriends in the last 10 days for the FRIENDS of a person with usercode 5.Example:tblUserdata5 peter Petertje6 john Johnny11 simon SimonSays15 monique MontjetblFriends5 6 'createdate 30 days ago'5 11 'createdate 5 days ago'6 5 'createdate 30 days ago'6 11 'createdate 3 days ago'6 15 'createdate 7 days ago'11 5 'createdate 5 days ago'11 6 'createdate 3 days ago'15 6 'createdate 7 days ago'The resultset for a query on usercode 5 would now be (usercode1, username1, displayname1,usercode2, username2, displayname2,createdate):6 john Johnny 11 simon SimonSays 'createdate 3 days ago'6 john Johnny 15 monique Montje 'createdate 7 days ago'As you can see each relation is only returned twice even though there are always two entriesWhat would be the SQL statement, if possible without temp table..Thanks!
View 21 Replies
View Related
Apr 3, 2006
I am getting a new server online at a customer, and our system shows very funny erorrs under full load. No explanations could be found. Anyone has some?
Here we go:
Server failed to resume the transaction, desc: 360000054a. The transaction active in this session has been committed or aborted by another session.,
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
and
New request is not allowed to start because it should come with valid transaction descriptor.
System.Data.SqlClient.SqlException
I am a little lost on those. Simple fact is that I do not find any description of those. Anyone an idea what causes these?
View 2 Replies
View Related
Jul 23, 2005
Hi,I can see that by using the object ID rather that the object name, thefollowing SQL query works. Has anybody got any idea what is causing theerror?-- Works OKselect o.id,checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by o.id-- Error-- Server: Msg 1540, Level 16, State 1, Line 1-- Cannot sort a row of size 8096, which is greater than the-- allowable maximum of 8094.select object_name(o.id),checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by object_name(o.id)-- Error-- Server: Msg 1540, Level 16, State 1, Line 1-- Cannot sort a row of size 8096, which is greater than the-- allowable maximum of 8094.select o.name,checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by o.name-- Workaroundselect getdate(),object_name(x.id),check_sumfrom (select m.id,checksum_agg(binary_checksum(m.text)) as check_sumfrom syscomments minner joinsysobjects oon m.id = o.idwhere o.xtype in ('FN','IF','P','TF','TR','V')group by m.id) as xRegardsLiam
View 1 Replies
View Related
Jan 22, 2007
We are trying to create a model of linear regression with nested table. We used the create mining model sintax as follow :
create mining model rate_plan3002_nested2
( CUST_cycle LONG KEY,
VOICE_CHARGES double CONTINUOUS predict,
DUR_PARTNER_GRP_1 double regressor CONTINUOUS ,
nested_taarif_time_3002 table
( CUST_cycle long CONTINUOUS,
TARIFF_TIME text key,
TARIFF_VOICE_DUR_ALL double regressor CONTINUOUS
)
) using microsoft_linear_regression
INSERT INTO MINING STRUCTURE [rate_plan3002_nested2_Structure]
(CUST_cycle ,
VOICE_CHARGES ,
DUR_PARTNER_GRP_1 ,
[nested_taarif_time_3002](SKIP,TARIFF_TIME ,TARIFF_VOICE_DUR_ALL)
)
SHAPE {
OPENQUERY([Cell],
'SELECT CUST_cycle ,
VOICE_CHARGES ,
DUR_PARTNER_GRP_1
FROM dbo.panel_anality_3002
order by CUST_cycle ')}
APPEND
({OPENQUERY([Cell],
'select CUST_cycle,
TARIFF_TIME,
CYCLE_DATE
from dbo.nested_taarif_time_3002
order by CUST_cycle,TARIFF_TIME')
}
relate CUST_cycle to CUST_cycle
) as nested_taarif_time_3002
The results we got are a model with intercept only. if we don't use the nested variable (the red line) we get a rigth model . (we had more variable ....)
Is there a way to do this regression correctly?
Thanks,
Dror
View 7 Replies
View Related
May 1, 2008
(I wish I was able to attach pictures....anyhow I'll try my best without them)
The customer's goal is to via ReportBuilder to select fields from multiple
many-to-many relationships. They are able to see the fields that exists
within the various many-to-many relationships but IS ONLY able to select
fields from ONE many-to-many relationship. Example using AdventureWorks:
DataSourceView (relationships automatically generated by wizard)
Contact ---< VendorContact >--- Vendor
Contact ---< StoreContact >--- Store
Contact ---< ContactCreditCard >--- CreditCard
So the goal in the ReportBuilder is to be able select fields from
Contact/Vendor/Store/ and CreditCard.
PROBLEM is that the ReportBuilder ONLY allows user to select fields from
Contact/Vendor OR Contact/Store OR Contact/CreditCard (exclusively)
The only workaround I've found is to reverse the relationships in the
DataSourceView to the following
Contact >--- VendorContact ---< Vendor
Contact >--- StoreContact ---< Store
Contact >--- ContactCreditCard ---< CreditCard
This works however we need an explanation as to why it only works this way?
Thanks in advance
View 1 Replies
View Related
Jan 17, 2008
Hi, I am a extreme beginer to sql server and i am i'm having big trouble trying to display my sql query properly. Bascially i want to put the results of a one to many query into one row per record. I have read articles and forums discussing 'concatenating the values' or creating a function??? but i dont follow what they mean and i am completely lost. Can anyone provide a really simple explanation on what i need to do to resolve my duplicate row issue? i urgently need to find a solution to this.
Regards
View 2 Replies
View Related
Oct 24, 2007
Question is in the subject.
Thanks in advance
-Jamie
View 7 Replies
View Related
Jul 15, 2002
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
View 1 Replies
View Related
Aug 13, 2003
Hi All,
Can anybody suggest me a website where I can find articles on Managing transactions with Sql server. Also a scenario where the transactions take place in a environment involving 2 different databases, Like the bank account and credit card transactions (specifically of 2 way kind)
Thanks
View 5 Replies
View Related
Apr 6, 2005
I have a web application with a shopping cart, how do I stop all the shopping cart transaction from going into the db log? Is this possible? These are are only transient data movements, and will never be need to to restore to, and they are cause log bloat. Or is there a better way to stop log bloat?
Thanks
View 2 Replies
View Related
Apr 17, 2000
Hi All,
How can we change connection properties in a DTS pkg with connection?
You can loop through the connection count but the connection ID is not static one.So can’t rely on that.
Is there another way of changing connection properties?
Thanks in Advance
Barath
View 4 Replies
View Related
Sep 5, 2000
Hi all....
I am currently designing a DTS Package to import data that is processed daily into a large database.
I have to design the package such that if any step fails when importing, I roll back the entire transaction.
I have designed the package with this in mind, checked "join transaction if present" and "rollback transaction on failure" in all of the workflows. I have also made all workflows serialized.
However, when I run the package, it fails on one of the data pumps with the error:
Transaction context in use by another session.
Any ideas?
Thank you,
Brian
View 2 Replies
View Related
Dec 1, 2000
I am replicating (finally!!) and on my publishers agent history I can see it says xx transactions with xx commands were delivered. (xx being the number)
Where can I look to see what the transactions or commands are?
Is there a place the system stores this information?
View 1 Replies
View Related
Oct 17, 2003
what is maximum limit of no. of transactions per sec. in sql server 2000
View 2 Replies
View Related
Sep 6, 2004
Is there a point to wrapping a single UPDATE or INSERT statement in an explicit TRANSACTION:
BEGIN TRANSACTION
INSERT INTO Table (...) VALUES (...)
COMMIT TRANSACTION
I understand ACID and concept of transactions. However, I thought they were only necessary for multi-statement operations. I'm maintaining code that does this and am wondering if this is necessary. Does SQL Server guarantee ACID for single statements? Are single UPDATE/INSERT statements prone to race condition like affects without using explicit transactions?
View 2 Replies
View Related
Apr 17, 2008
Are there any scenarios where an un-commited transaction would block further queries?
View 1 Replies
View Related
May 27, 2008
If you run the Begin Transaction code and then run a create such as an update query and you see that it effects the number of rows that you wanted it to effect is there a way to look at the actual data that changed before you Commit Transaction?
Thanks!
View 7 Replies
View Related
Jul 1, 2007
I have a table with around 240 columns and one of the column in the Table is the Inserttime ( DATETIME ) and I using a GETDATE() function in the stored Proc, when we insert data into the table. In the same Milli second 2007-06-27 09:32:58.303 , I have around 7600 records in the database. The Stored Proc is called for each Individual record and we don't bunch the transactions. Is this possible.
I did some bench marking on this server and I can insert only 700 - 800 records approx / sec on this particular table.
Thanks
View 6 Replies
View Related
