Which Queries Or Codes Couldn't Use In A Transactions In Sql Server

Feb 21, 2008

May i have a list of Which queries or codes couldn't use in a Transactions in Sql Server ?

I Couldn't Find Appropriate SQL Queries

Feb 6, 2007

Because forum HTML codes are OFF I had to put my question between code tags. Because to tell my question I had to show the database tables and wanted output. I succeeded retrieving the datas but combining these three tables together with SQL or ASP codes is too complex. Please help me determining the table. As I am subscribed to this forum you can ask me anything about my question. I can clarify my question if you need. Your answers will be replied immediately.
(To see my question pls save the codes below as htm file or see it with a wysiwyg editor.)

tbl_PRODUCTIONS
<table border="1" width="971" id="table1">
<tr>
<td height="19" width="54">fld_DATE</td>
<td width="161" height="19">fld_PRODUCER_UNITCODE</td>
<td height="19" width="124">fld_PRODUCT_CODE</td>
<td height="19" width="222">fld_DAILY_PRODUCTION_PROGRAM</td>
<td height="19" width="176">fld_PRODUCTION_QUANTITY</td>
<td height="19" width="136">fld_STOCK_QUANTITY</td>
<td height="19" width="52">fld_SELL</td>
</tr>
<tr>
<td width="54" >......</td>
<td width="161" >....</td>
<td width="124">....</td>
<td width="222">....</td>
<td width="176">....</td>
<td width="136">.....</td>
<td width="52">....</td>
</tr>
<tr>
<td width="54" >18.01.2007</td>
<td width="161" >IST</td>
<td width="124">1.10</td>
<td width="222">100</td>
<td width="176">115</td>
<td width="136">100</td>
<td width="52">10</td>
</tr>
<tr>
<td width="54" >18.01.2007</td>
<td width="161" >IST</td>
<td width="124">1.20</td>
<td width="222">200</td>
<td width="176">190</td>
<td width="136">200</td>
<td width="52">54</td>
</tr>
<tr>
<td width="54" >18.01.2007</td>
<td width="161" >ANK</td>
<td width="124">2.110.12</td>
<td width="222">300</td>
<td width="176">350</td>
<td width="136">450</td>
<td width="52">745</td>
</tr>
<tr>
<td width="54" >18.01.2007</td>
<td width="161" >ANK</td>
<td width="124">12.128.43</td>
<td width="222">150</td>
<td width="176">150</td>
<td width="136">4664</td>
<td width="52">55</td>
</tr>
<tr>
<td width="54" >18.01.2007</td>
<td width="161" >IZM</td>
<td width="124">6.142.42</td>
<td width="222">100</td>
<td width="176">100</td>
<td width="136">234</td>
<td width="52">77</td>
</tr>
<tr>
<td width="54" >18.01.2007</td>
<td width="161" >IZM</td>
<td width="124">9.86.50</td>
<td width="222">125</td>
<td width="176">135</td>
<td width="136">654</td>
<td width="52">80</td>
</tr>
<tr>
<td width="54" >18.01.2007</td>
<td width="161" >IZM</td>
<td width="124">12.43.250</td>
<td width="222">75</td>
<td width="176">80</td>
<td width="136">233</td>
<td width="52">101</td>
</tr>
<tr>
<td width="54" >19.01.2007</td>
<td width="161" >IST</td>
<td width="124">1.10</td>
<td width="222">100</td>
<td width="176">99</td>
<td width="136">100</td>
<td width="52">10</td>
</tr>
<tr>
<td width="54" >19.01.2007</td>
<td width="161" >IST</td>
<td width="124">1.20</td>
<td width="222">200</td>
<td width="176">210</td>
<td width="136">200</td>
<td width="52">54</td>
</tr>
<tr>
<td width="54" >19.01.2007</td>
<td width="161" >ANK</td>
<td width="124">2.110.12</td>
<td width="222">300</td>
<td width="176">309</td>
<td width="136">450</td>
<td width="52">745</td>
</tr>
<tr>
<td width="54" >19.01.2007</td>
<td width="161" >ANK</td>
<td width="124">12.128.43</td>
<td width="222">150</td>
<td width="176">155</td>
<td width="136">4664</td>
<td width="52">55</td>
</tr>
<tr>
<td width="54" >19.01.2007</td>
<td width="161" >IZM</td>
<td width="124">6.142.42</td>
<td width="222">100</td>
<td width="176">100</td>
<td width="136">234</td>
<td width="52">77</td>
</tr>
<tr>
<td width="54" >19.01.2007</td>
<td width="161" >IZM</td>
<td width="124">9.86.50</td>
<td width="222">125</td>
<td width="176">90</td>
<td width="136">654</td>
<td width="52">80</td>
</tr>
<tr>
<td width="54" >19.01.2007</td>
<td width="161" >IZM</td>
<td width="124">12.43.250</td>
<td width="222">75</td>
<td width="176">60</td>
<td width="136">233</td>
<td width="52">101</td>
</tr>
<tr>
<td width="54" >20.01.2007</td>
<td width="161" >IST</td>
<td width="124">1.10</td>
<td width="222">100</td>
<td width="176">90</td>
<td width="136">100</td>
<td width="52">10</td>
</tr>
<tr>
<td width="54" >20.01.2007</td>
<td width="161" >IST</td>
<td width="124">1.20</td>
<td width="222">200</td>
<td width="176">190</td>
<td width="136">200</td>
<td width="52">54</td>
</tr>
<tr>
<td width="54" >20.01.2007</td>
<td width="161" >ANK</td>
<td width="124">2.110.12</td>
<td width="222">300</td>
<td width="176">350</td>
<td width="136">450</td>
<td width="52">745</td>
</tr>
<tr>
<td width="54" >20.01.2007</td>
<td width="161" >ANK</td>
<td width="124">12.128.43</td>
<td width="222">150</td>
<td width="176">156</td>
<td width="136">4664</td>
<td width="52">55</td>
</tr>
<tr>
<td width="54" >20.01.2007</td>
<td width="161" >IZM</td>
<td width="124">6.142.42</td>
<td width="222">100</td>
<td width="176">120</td>
<td width="136">234</td>
<td width="52">77</td>
</tr>
<tr>
<td width="54" >20.01.2007</td>
<td width="161" >IZM</td>
<td width="124">9.86.50</td>
<td width="222">125</td>
<td width="176">140</td>
<td width="136">654</td>
<td width="52">80</td>
</tr>
<tr>
<td width="54" >20.01.2007</td>
<td width="161" >IZM</td>
<td width="124">12.43.250</td>
<td width="222">75</td>
<td width="176">90</td>
<td width="136">233</td>
<td width="52">101</td>
</tr>
<tr>
<td width="54" >...</td>
<td width="161" >....</td>
<td width="124">....</td>
<td width="222">....</td>
<td width="176">....</td>
<td width="136">....</td>
<td width="52">....</td>
</tr>
</table>

� 
tbl_ANNUAL_PRG
<table border="1" width="552" id="table2">
<tr>
<td width="62">fld_YEARS</td>
<td>fld_PRODUCER_UNITCODE</td>
<td>fld_PRODUCT_CODE</td>
<td width="177">fld_PRODUCTION_PROGRAM</td>
</tr>
<tr>
<td width="62">2006</td>
<td width="161" >IST</td>
<td width="124">1.10</td>
<td width="177">15000</td>
</tr>
<tr>
<td width="62">2006</td>
<td width="161" >IST</td>
<td width="124">1.20</td>
<td width="177">22000</td>
</tr>
<tr>
<td width="62">2006</td>
<td width="161" >ANK</td>
<td width="124">2.110.12</td>
<td width="177">35000</td>
</tr>
<tr>
<td width="62">2006</td>
<td width="161" >ANK</td>
<td width="124">12.128.43</td>
<td width="177">50000</td>
</tr>
<tr>
<td width="62">2006</td>
<td width="161" >IZM</td>
<td width="124">6.142.42</td>
<td width="177">170000</td>
</tr>
<tr>
<td width="62">2006</td>
<td width="161" >IZM</td>
<td width="124">9.86.50</td>
<td width="177">12000</td>
</tr>
<tr>
<td width="62">2006</td>
<td width="161" >IZM</td>
<td width="124">12.43.250</td>
<td width="177">123000</td>
</tr>
<tr>
<td width="62">2007</td>
<td width="161" >IST</td>
<td width="124">1.10</td>
<td width="177">18000</td>
</tr>
<tr>
<td width="62">2007</td>
<td width="161" >IST</td>
<td width="124">1.20</td>
<td width="177">25000</td>
</tr>
<tr>
<td width="62">2007</td>
<td width="161" >ANK</td>
<td width="124">2.110.12</td>
<td width="177">32000</td>
</tr>
<tr>
<td width="62">2007</td>
<td width="161" >ANK</td>
<td width="124">12.128.43</td>
<td width="177">55000</td>
</tr>
<tr>
<td width="62">2007</td>
<td width="161" >IZM</td>
<td width="124">6.142.42</td>
<td width="177">190000</td>
</tr>
<tr>
<td width="62">2007</td>
<td width="161" >IZM</td>
<td width="124">9.86.50</td>
<td width="177">10000</td>
</tr>
<tr>
<td width="62">2007</td>
<td width="161" >IZM</td>
<td width="124">12.43.250</td>
<td width="177">129000</td>
</tr>
</table>
� 
tbl_PRODUCT_GROUPS 
(This table gives the names to the product of the tbl_PRODUCTIONS table)
<table border="1" width="366" id="table3">
<tr>
<td>fld_PRODUCT_GROUP_CODE</td>
<td width="175">fld_PRODUCT_GROUP_CODE</td>
</tr>
<tr>
<td width="175">1.10</td>
<td width="175">Pencil</td>
</tr>
<tr>
<td width="175">1.20</td>
<td width="175">Rubber</td>
</tr>
<tr>
<td width="175">2.110.12</td>
<td width="175">Ruler</td>
</tr>
<tr>
<td width="175">12.128.43</td>
<td width="175">Pencil Box</td>
</tr>
<tr>
<td width="175">6.142.42</td>
<td width="175">School Bag</td>
</tr>
<tr>
<td width="175">9.86.50</td>
<td width="175">Pen</td>
</tr>
<tr>
<td width="175">12.43.250</td>
<td width="175">Calendar</td>
</tr>
</table>
� 
These 3 tables are in Oracle. I am trying to access the tables
with an asp page. I succeed to retrieve the values to a page, but processing the
values and joining the tables together is too complex for me. The table I want
is like this. Would you help me creating ASP page and SQL queries
to create this table:
Parameters to send the asp page that process the SQL queries: 
startdate=18.01.2007 
finishdate=20.01.2007
The Productions of -18.01.2007-20.01.2007
<table border="1" width="1066" id="table4">
<tr>
<td>Comes From tbl_PRODUCTIONS table</td>
<td>Comes From tbl_PRODUCT_GROUPS table</td>
<td width="155">Comes from tbl_ANNUAL_PRG table</td>
<td width="208">Comes from tbl_PRODUCTIONS table summing
each value for each product</td>
<td width="179">Comes from tbl_PRODUCTIONS table summing
each value for each product</td>
<td width="108">Comes from tbl_PRODUCTIONS retrieving the
values for "finishdate". Not cumulative like other values!</td>
<td width="187">Comes from tbl_PRODUCTIONS table summing
each value for each product</td>
</tr>
<tr>
<td>Producer Locations</td>
<td>Products</td>
<td width="155">Annual Production Program</td>
<td width="208">Production Program for wanted period</td>
<td width="179">Production Quantity for wanted period</td>
<td width="108">Stock Quantity</td>
<td width="187">Selling Quantity for wanted period</td>
</tr>
<tr>
<td width="119" >IST</td>
<td width="61">Pencil</td>
<td width="155">18000</td>
<td width="208">300</td>
<td width="179">304 </td>
<td width="108">100</td>
<td width="187">30</td>
</tr>
<tr>
<td width="119" >IST</td>
<td width="61">Rubber</td>
<td width="155">25000</td>
<td width="208">600</td>
<td width="179">590</td>
<td width="108">200</td>
<td width="187">162</td>
</tr>
<tr>
<td width="119" >ANK</td>
<td width="61">Ruler</td>
<td width="155">32000</td>
<td width="208">900</td>
<td width="179">1009</td>
<td width="108">450</td>
<td width="187">2235</td>
</tr>
<tr>
<td width="119" >ANK</td>
<td width="61">Pencil Box</td>
<td width="155">55000</td>
<td width="208">450</td>
<td width="179">461</td>
<td width="108">4664</td>
<td width="187">165</td>
</tr>
<tr>
<td width="119" >IZM</td>
<td width="61">School Bag</td>
<td width="155">190000</td>
<td width="208">300</td>
<td width="179">320</td>
<td width="108">234</td>
<td width="187">231</td>
</tr>
<tr>
<td width="119" >IZM</td>
<td width="61">Pen</td>
<td width="155">10000</td>
<td width="208">375</td>
<td width="179">365</td>
<td width="108">654</td>
<td width="187">240</td>
</tr>
<tr>
<td width="119" >IZM</td>
<td width="61">Calendar</td>
<td width="155">129000</td>
<td width="208">225</td>
<td width="179">230</td>
<td width="108">233</td>
<td width="187">303</td>
</tr>
</table>

OS: WinXP Pro
DB: Oracle (Version ?)
Script: ASP

View 4 Replies View Related

Couldn't Debug SQL By Step Into Stored Procedure On Server Explorer Of VS2008 (or VS2005) On Remote Machine

Oct 21, 2007

Hi all,

I couldn't debug SQL Server by "Step into Stored Procedure" on Server Explorer of VS2008 (or VS2005) to SQL 2005 Developer on remote Windows Server 2003 machine, it allway issue exception "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"

1) The environment:
The Client: Windows XP SP2 (WORKGROUP)
Visual Studio 2008 (or VS2005)

The SQL Server Machine: Windows 2003 Server Sp1 (DOMAIN)
SQL Server 2005 Developer

2) User account and Permission login:
I create the same user account for both Client and Domain Server with the same password, i also add that user to "Administrators" group in both machine.

At the SQL Server on Server machine, i added that account to ServerSecurityLogin with 'sysadmin' role already

3) Connection and authentication:
I used "Windows Authentication" for my connection to SQL server, and i checked sure my account of the connection by SQL command

SELECT SYSTEM_USER,
IS_SRVROLEMEMBER ('sysadmin')

4) Firewall:
I checked firewall like MSDN helping (i also tried to test by turn off firewall in both machine)

5) Visual Studio Remote Debugger:
I read "How to: Enable SQL Server 2005 Debugging" on MSDN with comment "The SQL Server can run on the same machine as the application or on a remote machine. If you are debugging T-SQL code only, then no remote setup is required."
so i didn't config Visual Studio Remote Debugger any thing.

Note: If i "Step Into Store Procedure" at Server locally, it works okey, so on at my PC client locally. But if i move debugging from my client to my Server, it occur error "Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'Server'. Click Help for more information"???

If i execute store procedure on Server Explore, it works okey!

Please help me to find out what problem is???

Thanks,
Haiasc

View 2 Replies View Related

Couldn't Replicate ???

Jan 4, 2006

Hi,

I'm new to replication.
I configured Server named TESTER as publisher & distributer. Create new publication for database named TEST ( type : Transactional ), then Create a new push subcriptions for database TESTREPLICATION ( on the same Server : TESTER). Then I open a table in database TEST to input some data but I couldn't see any tables or data in database TESTREPLICATION ( I chose Continuously, not scheduled).

Yet, after the lunch, I could see the tables, no data. I tried to choose Start Synchronizing but not helped.

Please tell me if I configured incorrectly ?

Thanks,

View 7 Replies View Related

Connecting To SQL Server 2005 Using Vb Codes.

Mar 26, 2007

The data resides in app_data.

In my webconfig file I have this.
<add name="vau" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|vau.mdf" providerName="System.Data.SqlClient"/>

I understand that there are several different ways to connect and manage information to the database. I think I want to use the behind the codes (.aspx.vb) and on the top of the page I have this. Imports System.Data.

That is where I am stuck. Can you push me in the direction by either explaing to me or post a link that has more information about this.

Thanks.

View 4 Replies View Related

Couldn't Find SSMS

Feb 2, 2006

Hi,

I have just installed sql server 2005 enterprise edition. I am totally new to sqlserver. I want to create a database in sql server and access that from my VB.net application. I couldn't find sql server management studio to create a database. can somebody help me with this...

thanks,
Leela

View 3 Replies View Related

Couldn't Connect To Reports

Feb 6, 2007

Hi,

I'm not able to browse through http://localhost/reports.

When I'm trying to connect it gives me "Server Error in '/Reports' Application."At the same time I'm able to view Reportserver.At the same time I'm able to view Reportserver.(i.e)http://localhost/reportserver

please can anyone suggest me some solution to this problem..
Server Error in '/Reports' Application.

The trust relationship between the primary domain and the trusted domain failed.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.SystemException: The trust relationship between the primary domain and the trusted domain failed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:

[SystemException: The trust relationship between the primary domain and the trusted domain failed.
]
System.Security.Principal.NTAccount.TranslateToSids(IdentityReferenceCollection sourceAccounts, Boolean& someFailed) +1167
System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean& someFailed) +42
System.Security.Principal.NTAccount.Translate(IdentityReferenceCollection sourceAccounts, Type targetType, Boolean forceSuccess) +46
System.Security.Principal.WindowsPrincipal.IsInRole(String role) +205
System.Web.Configuration.AuthorizationRule.IsTheUserInAnyRole(StringCollection roles, IPrincipal principal) +120
System.Web.Configuration.AuthorizationRule.IsUserAllowed(IPrincipal user, String verb) +300
System.Web.Configuration.AuthorizationRuleCollection.IsUserAllowed(IPrincipal user, String verb) +191
System.Web.Security.UrlAuthorizationModule.OnEnter(Object source, EventArgs eventArgs) +3403086
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

View 11 Replies View Related

I Couldn't Find TRIM Function

Oct 18, 2006

Hi,

I found rtrim and ltrim functions, but I didn't find trim, so should I use rtrim(ltrim(' a ' ))? Am I right?

cheers,

Alessandro Camargo

View 5 Replies View Related

SQL Server 2012 :: Query Get Data In Double Codes In String

Feb 9, 2014

I have data like this

"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}

I want to get only data in double codes in using sql query.

View 9 Replies View Related

What Is Wrong With My Codes To Connect VB.net To Sql Server 2005 Express Database?

Mar 16, 2007

Hi am very new with VB.net (Visual Studio 2005) and the first time try to use the sql server 2005 express. I did create a trial.mdf (local server) and complete procedure to attach the trial1.mdf in Microsoft SQL Management Studio Express.

I try to use VB to connect /open/close it with the following codes: (but failed)

-----------------------------------------------

Imports System.Data.SqlClient

Public Class Form1
'Link database
Dim DB_Location = "c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTrial1.mdf"
Dim MyConnection = "Server=localhost; DataBase= " & DB_Location & "Integrated Security=SSPI"
Dim sqlConnection As New SqlConnection(MyConnection)

'Use database
Dim strSQL As String = "SELECT FROM Trial1 WHERE col2 = 'trial' "
Dim cmd As New SqlCommand(strSQL, MyConnection)

sqlConnection.Open() ' ----> error
cmd.ExecuteNonQuery() ' ----> error
sqlConnection.Close() ' ----> error

End Class

-------------------------------------------

At the last 3 line of codes I got the errors ... can someone explain why & help me to correct it?

Thanks for any help

View 10 Replies View Related

Why I Couldn't Run The Transfer Database Object Task With SQL Authentication

May 8, 2006

Hi all,

I have created a package to copy 10 tables from a database to another one. I have created a datasource with SQL Authentication mode. And it couldn't run until I changed the datasource connection to Windows Authentication mode. How could it be like that, it's just the authentication mode?

Is there anyone have the right answer for this situation?

View 3 Replies View Related

SQL Server 2012 :: Return Descriptions From Table2 In Same Format As Codes From Table1?

Apr 22, 2015

Table1 contains a CODES column which may store multiple codes separated by commas.

The descriptions for these codes are stored individually in a column in in Table2.

I would like to return the descriptions from Table2 in the same format as the codes from Table1.

Table1 Stores multiple codes per ID in a single column.

ID CODES
-- --------------
01 Code1,Code2,Code3

Table2 Stores the definitions for the codes in Table1

CODE DESCRIPTION
----- ---------------
Code1 Description1
Code2 Description2
Code3 Description3

The tables are what the tables are. Here is what I am trying to accomplish:

ID CODES DESCRIPTIONS
-- ----------------- --------------------------------------
01 Code1,etc Description1,Description2,Description3

I've found two different queries which return each code and associated description by ID in separate rows. Perhaps the next step is to aggregate from this?

View 7 Replies View Related

I Couldn't Access The Secondary Database After I Finished Shipping Configuration

Apr 19, 2006

Dear All

Please I need an urgent help

After i finished all Transaction Log Shipping Configuration.

I tried to use the database in the secondary database but i couldn't access it

i saw it in SQL Managment Studio as (Restoring......)

i tired to make a database snapshot from it , i had a message

Msg 1822, Level 16, State 1, Line 1

The database must be online to have a database snapshot.

Please urgently

View 1 Replies View Related

Changing Connection Transactions To Database Transactions

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

Transactions In Sql Server

May 9, 2007

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 Related

ASP.NET SQL Server Transactions

May 28, 2005

Hi,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 Related

Monitoring SQL Server Transactions

Mar 22, 2006

Hi,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 Related

SQL Server 2005 - Transactions

Jan 24, 2007

Hello 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.

View 7 Replies View Related

Transact SQL :: Server - Transactions Per Second

Oct 13, 2015

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 Related

DAO, ADO Transactions With SQL Server 2005 Using Vb6

Mar 19, 2007

I 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.

View 4 Replies View Related

Can This Be Done Just In Database (I Have Given Codes)

Oct 4, 2006

Hi , I have a database that records the users entrance to a building.The dates are recorded also .I have written some codes that to detect the period of dates that the person has entered.Lets say that a person named as jhon has entered the building on the days 02/08/2006 and 05/08/2006 and 11/08/2006 .Those dates are formated as dd/mm/yyyy . So that john has entered building for 3 times and the periods for the dates are one after another entrance is 3 days + 6 days =9 days .If you divide 9 by 3 we get the arithmetic average number 3 .So that we can say as john visits this building every 3 days and we can guess the next day that he may come. I have coded this and works great .I will paste the codes to the end of my message.But a master programmer friend of mine has said that I should have get this result by without writing code , by just using sql database .But some kind of stored procedure I mustn't use.So that I thought it can be done by views in sqldb .By using sql server enterprise manager I tried to use views but I could not succees. Can anyone guess this ? Here are my vb codes ... database data types : entry_id : int , identity user_id : int movie : nvarchar (50) dateenter : datetime stored procedure that selects the data from db: CREATE PROCEDURE veri_al ( @user_id int ) AS SELECT entry_id ,user_id, movie, dateenter from uye_aktiviteleri where (user_id=@user_id) ORDER BY entry_id ASC GO Code:Dim conn As New SqlClient.SqlConnection conn.ConnectionString = "data source=localhost;initial catalog=AFM;uid=nusret;pwd=araz" Dim command As New SqlClient.SqlCommand command.CommandText = "[veri_al]" command.CommandType = CommandType.StoredProcedure command.Connection = conn command.Parameters.Add("@user_id", SqlDbType.Int, 4).Value = Val(TextBox1.Text) If Not IsNumeric(TextBox1.Text) Then Exit Sub End If Try Dim adapter As New SqlClient.SqlDataAdapter adapter.SelectCommand = command Dim ds As New DataSet adapter.Fill(ds, "uyeler") DataGrid1.DataSource = ds.Tables("uyeler") Dim recordcount As Integer 'Found the recordcount recordcount = ds.Tables("uyeler").Rows.Count Dim mydatarow_ilk As DataRow Dim mydatarow_son As DataRow 'in stored procedure I used ORDER BY entry_id ASC so that first datarow 'will always be the first visit date and the last record of the 'datarow will be the last visit date mydatarow_first= ds.Tables("uyeler").Rows(0) mydatarow_last = ds.Tables("uyeler").Rows(recordcount - 1) Dim first_date As String Dim last_date As String first_date = mydatarow_first("dateenter") last_date =mydatarow_last("dateenter") Dim average_ As Integer 'What is the aveare of the visits ortalama = DateDiff("d", first_date , last_date ) / recordcount Label2.Text = "Member had visited for " & recordcount & "times" Label3.Text = "by average he/she comes here " & ortalama & " day to another." Dim last_time_visit As Integer last_time_visit = DateDiff("d", last-date , DateTime.Today()) Label4.Text = "Until the last visit it had been" & last_time_visit & " days" Label5.Text = "Guess for the next visit is" & DateAdd("d", average_ , last_date) ' MsgBox("average visits" & ortalama & " days" ) Catch z As Exception MsgBox("error : " & z.Message()) Finally MsgBox("Successfully calculated") End Try End Sub

View 1 Replies View Related

Brick Codes

Jul 20, 2005

Hi,I have a table of Locations around the country. My system produces reportsbased on these Locations. I also have a table containing Brick Codes e.g.Brick Post CodeAB51 AB51AB52 AB52AB55 AB55AB56 AB56AL01 AL1AL02 AL2AL03 AL3AL04 AL4How can I compare the first 3 or 4 letters of the postcode in the Locationstable to the corresponding entry in the Brick Codes table so I can add it tomy report?Thanks for your help

View 2 Replies View Related

ADO Error Codes...

Oct 10, 2006

Hi,

I have another question about ADO (using C++). I have searched the MSDN but I haven't found a answer to my question (maybe I'm just too stupid)... If I make a connection to my SQL Server there may occur some errors, but how to find out what went wrong ?

In terms of code I have e.g. something like that:
try { connection->Open(ConnectionString,Username,Password,ADODB::adConnectUnspecified);
...} catch (_com_error& e) { long numErrors = connection->Errors->Count; for (long i=0; i<numErrors; i++) { ADODB::ErrorPtr pErr = connection->Errors->GetItem(i); ..... }}

Now I could get the error number by pErr->GetNumber().
But with which symbolic constant has this to be compared to find out which error occured ?? I didn't find any...Or is there another better way to do this ?

P.S. I am using SQL Server 2005 Express

View 7 Replies View Related

Test Sql Codes

Jan 8, 2008

How can I test sql codes and don't want to connect to company database?
My company is using sql server 2000.
Below is the test codes which create a simple table, calculate and just want to check before
writing more complicated codes.

Thanks
Daniel Ku

-------------------------------------------------------------------------------------------------------------------------------

create table EquipmentUptime (
equipmentId int not null
,transactionId int not null
,dateOccured datetime not null
)
go
--
insert into EquipmentUptime values (1,200,'01/01/2007')
insert into EquipmentUptime values (2,200,'01/01/2007')
insert into EquipmentUptime values (3,200,'01/01/2007')
insert into EquipmentUptime values (2,100,'02/12/2007')
insert into EquipmentUptime values (1,100,'02/25/2007')
insert into EquipmentUptime values (3,100,'03/10/2007')
insert into EquipmentUptime values (1,200,'03/14/2007')
go
--
select
equipmentId
,100*(cast((cast(sum(UpDays)as decimal(6,3))/cast(365 as decimal(6,3))) as decimal(4,3))) 'PctUpTime'
from
(
select
c.equipmentId
,datediff(dd,c.[UpDate],c.DownDate) 'UpDays'
from
(
select
a.equipmentId
,a.dateOccured as 'UpDate'
,coalesce(
(select top 1
b.dateOccured
from
EquipmentUptime b
where
transactionId=100
and b.equipmentId=a.equipmentId
and b.dateOccured >= a.dateOccured
order by
b.dateOccured asc
),'01/01/2008') as 'DownDate'
from
EquipmentUptime a
where
a.transactionId=200
) c
) d
group by
d.equipmentId

View 1 Replies View Related

Error Codes?

Jun 28, 2007

My data flow component is throwing an error and the only help I get is the following:

error code: -1071607694

error column: 257

What in the world does this mean? Can it get more cryptic than this?

View 1 Replies View Related

Is There Any Way I Can Roll Back In Sql Server Without Using Transactions ?

May 5, 2005

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

View 3 Replies View Related

How To Implement Transactions In Sql Server 2000

May 11, 2005

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

View 4 Replies View Related

Uncomitted Transactions On Server Outside Of Domain

Mar 16, 2004

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

View 2 Replies View Related

Doing Transactions With Linked Server(ole Db Provider)

May 22, 2004

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

View 2 Replies View Related

Does SQL Server CE Support System.Transactions?

Feb 15, 2007

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

View 3 Replies View Related

Transactions Using Oracle RDB Linked Server

Feb 7, 2008

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 Related

Generating Serial Codes

Jul 29, 2001

Hi,

I have a table with a primary key, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do, automatically incrementing as fields are inserted.

View 1 Replies View Related

Inconsistent Return Codes Using BCP

Mar 2, 1999

I apologize for the length of this message, but I think I need to include all this info so that the problem is understood. I am having what appears to be a problem capturing the return code from a failed BCP.

I create a stored proc to use BCP to load a table:

create procedure sp_bcp_load as
declare @RC int
execute @RC = master..xp_cmdshell "bcp JON..W4KPV in e:inetpubftprootfinreslaw4kpv.g4000.data /Sdbmtss1 /m 0 /f d:mssqluserdatafinresW4KPV.fmt /Usa /P /e d:mssqluserdatafinrescp1.err /t""|"" /r "

select 'Return code from bcp = ', @RC
if @RC <> 0
BEGIN
print 'BCP Error.'
return (8)
END
GO

If I execute the SP, and encounter a transaction log full error, the return code is still zero:

1000 rows sent to SQL Server. 45000 total
1000 rows sent to SQL Server. 46000 total
Msg 1105, Level 17, State 2:
Server 'DBMTSS1', Line 1:

Can't allocate space for object 'Syslogs' in database 'Jon' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

(54 row(s) affected)

----------------------- -----------
Return code from bcp = 0

If I execute the SP again, it correctly returns a non-zero value:

Msg 1105, Level 17, State 2:
Server 'DBMTSS1', Line 1:
Can't allocate space for object 'Syslogs' in database 'Jon' because
the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

(6 row(s) affected)

----------------------- -----------
Return code from bcp = 1

(1 row(s) affected)

BCP Error.

Does anybody have an idea why this behaves this way? Any suggestions on how to trap an error on the first call?

Thanks,
Jon Carter

View 2 Replies View Related