Transaction problem - Database Discussion Boards

gatorsjf14-Mar-05 6:02

14-Mar-05 6:02

SUBSTRING(CAST(Round(100.00 * (a.attempts - Sum(a.accepts)) - Round(100.00 * (b.attempts - Sum(b.accepts)) / Round(100.00 * (a.attempts - Sum(a.accepts AS CHAR),1,4) AS SUMMARY

The above is not complete. Could someone help?
Thanks

Re: Math Function For SQL

Colin Angus Mackay14-Mar-05 6:14

Colin Angus Mackay

14-Mar-05 6:14

Few people are going to spend time working out what it does and then work out what it should do. Don't just say "The above is not complete": What is it supposed to do?

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Math Function For SQL

gatorsjf14-Mar-05 6:29

gatorsjf

14-Mar-05 6:29

Hmmm
okay
4 colums with int values
I want a percent difference

A.Attempts A.Accepts B.Attempts B.Accepts
10000 9000 6500 4500

10000 - 9000 = 1000
Then
6500 - 4500 = 2000
Then difference and yes it may be negative
1000 - 2000 = -1000
Then
10000 - 9000 = 1000 / -1000 = -1

SUBSTRING(CAST(Round(100.00 * (a.attempts - Sum(a.accepts)) - Round(100.00 * (b.attempts - Sum(b.accepts)) / Round(100.00 * (a.attempts - Sum(a.accepts AS CHAR),1,4) AS SUMMARY

Re: Math Function For SQL

Colin Angus Mackay15-Mar-05 0:04

Colin Angus Mackay

15-Mar-05 0:04

I don't see where your SQL matches the example you've given. Where is the SUM() coming from, what are you doing with the SUBSTRING.

From the example you gave this would appear to be the correct code:

SELECT cast((A.Attempts-A.Accepts) as float) / cast(((A.Attempts-A.Accepts) - (B.Attempts-B.Accepts)) as float)
FROM ...

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Math Function For SQL

Anonymous15-Mar-05 3:15

Anonymous

15-Mar-05 3:15

Sorry but what I was attempting to do with the substring function is return 4 characters in lentgh .

The code you provided works and thank you

inserting data Table

vinu.netguy14-Mar-05 4:36

vinu.netguy

14-Mar-05 4:36

Hi,

I am facing a problem while inserting a datarow with data in to a new dataTable.
can anybody plz help me how to add a row with data to a new data table.

regards,
Vinu

Re: inserting data Table

Bahadir Cambel14-Mar-05 5:40

Bahadir Cambel

14-Mar-05 5:40

It would be very helpful , to show us whats the error you get ?
Some code would be helpful too..

Bahadir Cambel

Transaction problem

Vinicius Pontes14-Mar-05 3:38

Vinicius Pontes

14-Mar-05 3:38

Hi,

I'm having a runtime error when I execute the BeginTrans() _Connection method on a MSAccess database.

Can someone tell me if there is any condition which can cause the error on this method?

I don't believe that the problem would be the ADO version, because I have another application that uses this method on a MSAccess database without any problem, but any way, I am working with ADO 2.7.

Thanks in advance,

Vinicius

Re: Transaction problem

BammBamm17-Mar-05 8:29

BammBamm

17-Mar-05 8:29

Is the connection open?

Re: Transaction problem

Vinicius Pontes18-Mar-05 4:27

Vinicius Pontes

18-Mar-05 4:27

Yes it is.

I found, somewhere in MSDN library, that if there is a recordset opened, the BeginTrans doesn't work. In my case, I have a recordset opened. I tryed to do the operation while the recordset was closed and that worked. The problem is that I need the recordset to be opened.

Vinicius

Insert data to dbase table

Axsys13-Mar-05 12:26

Axsys

13-Mar-05 12:26

I am needing to copy data from an MSSQL table to a dbaseIV table and would ideally like to do this with an SQL trigger on the source table.

Can someone please advise if it is possible to do this and if so provide some clues on how to go about this? I have established an ODBC connection to the dbase table folder but I cannot resolve how to address this from Query Analyser.

Many thanks,

Kerry

Kerry Johnson
Business Systems Consultant

Connecting remote SQL server

Yaakov Davis13-Mar-05 4:31

Yaakov Davis

13-Mar-05 4:31

Hi.

I'm trying to connect to a remote SQL serer express through TCP/IP.

I entered the <IP address\instance name> in the server name field. I tried also without the instance name.
I tried with integrated security as well as authentication. Also, I tried with the port address, and I get the error message "TCP Provider: Connection closed forcibly by the remote computer".

P.S. the protocol is enabled on noth the local and remote sides.

Can anyone explain me please how to establish a connection?

Thanks,
Yaakov

Re: Connecting remote SQL server

Colin Angus Mackay13-Mar-05 6:03

Colin Angus Mackay

13-Mar-05 6:03

Given the error message you got it might be a better idea to look on the server to see what the problem is. However, if the problem is with your code then we cannot help you unless you post the code you are using.

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: Connecting remote SQL server

Yaakov Davis13-Mar-05 6:50

Yaakov Davis

13-Mar-05 6:50

I'm trying to connect from the VS IDE.
I own both the server and the client, and I see no problem on both.

Can you reffer me to a relevant artical?

Thanks,
Yaakov

SQL DEC and NUMERIC vs BIGINT performance

Menny Even Danan13-Mar-05 2:15

Menny Even Danan

13-Mar-05 2:15

Hi.
I want to change a column in a table from BIGINT to DECIMAL or NUMERIC.
But i wonder, what is the performance loss when doing sorts,filters, arithmetic operations (like multiply and adds) on these data types (dec and numerics).

and, btw, what is the diffrence between 'NUMERIC' and 'DECIMAL'?

thanks

Re: SQL DEC and NUMERIC vs BIGINT performance

Colin Angus Mackay13-Mar-05 2:51

Colin Angus Mackay

13-Mar-05 2:51

Menny Even Danan wrote:
what is the diffrence between 'NUMERIC' and 'DECIMAL'?

Reading the documentation indicates that they are synonyms for each other.

Menny Even Danan wrote:
But i wonder, what is the performance loss when doing sorts,filters, arithmetic operations

Normally the main killer in performance is to do with volume of data being processed(*) so I'd say that on a large dataset any performance hit is negligable. So, I'd suggest that you are careful about the size of the data. This table from the books on-line will help:

+-----------+---------------+
| Precision | Storage bytes |
+-----------+---------------+
| 1 - 9     | 5             |
| 10-19     | 9             |
| 20-28     | 13            |
| 29-38     | 17            | 
+-----------+---------------+

[EDIT] Missed a bit: By comparison a BIGINT is 8 bytes in length but is constrained to a scale of 0 (nothing after the decimal point) and has a maximum value of (2^63)-1 while a NUMERIC/DECIMAL has a potentially much greater range [/EDIT]

* not necessarlily the same as volume of data returned

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

inserting content of text file to Access data field

pnpfriend11-Mar-05 8:38

pnpfriend

11-Mar-05 8:38

Hi All,
I am wondering if anybody know sql statement of how to import all the text from the text file into Access database field?

I have a text file, a.txt and afield called TextBody in the table.
TextBody is a memo size.
I was inserting like following

<br />
aTextfile.open("c:\\a.txt",ios::in);<br />
if(aTextfile)<br />
{                                         <br />
while(!aTextfile.eof())<br />
{<br />
aTextfile.getline(data,1024,'\n');<br />
temp.Format("%s",data);<br />
<br />
qstr.Format("UPDATE %s Set TextBody = TextBody & '%s' ",tablename,temp);<br />
rs.m_pDatabase->ExecuteSQL(qstr);<br />
}<br />
<br />
aTextfile.close();<br />
}<br />

But it takes so long to import all the text from the a.txt. Is there any better way?
Thanks

Re: inserting content of text file to Access data field

numbrel12-Mar-05 6:18

numbrel

12-Mar-05 6:18

Can you read the whole text file into a string and then write the string to the database?

Grouping by time period

Jeff Martin11-Mar-05 7:50

Jeff Martin

11-Mar-05 7:50

I'm trying to create a query that counts records based on time periods. I'm querying two tables, one containing SubmittedDate, the other CompletedDate. I'm using DateDiff to calculate the time difference between the two. What I want to do is end up w/ a count for mulitple periods of time like....

< 2 Hours 3
2 - 4 Hours 6
4-8 Hours 10
1 Day 12
1 - 3 Days 8
5 Days 4
> 5 Days 2

Any ideas?

Jeff Martin
My Blog

Re: Grouping by time period

Colin Angus Mackay12-Mar-05 6:49

Colin Angus Mackay

12-Mar-05 6:49

You could write a UDF (User defined function) that returns the time period and then use that in your query.

Cada uno es artifice de su ventura
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

tables list in access

Silly Boy11-Mar-05 0:58

Silly Boy

11-Mar-05 0:58

Please tell me! how can i get all tables name in access file (.mdb) ?
Thanks!

xyz

Re: tables list in access

numbrel11-Mar-05 3:59

numbrel

11-Mar-05 3:59

I will assume that you do not have Access and/or can not use it.

When you use the Data Adapter Configuration Wizard, it will show the tables available and after you have choosen the one(s) you want, it will show you the fields in the table.

Re: tables list in access

Suman Singh29-Mar-05 12:51

Suman Singh

29-Mar-05 12:51

Try this, this is working on SQL Server.

Create Sample That Lists Tables in a Database
The following sample lists tables in the SQL Server Northwind database.

OleDbSchemaGuid.Tables returns those tables (including views) that are accessible to a given log on. If you specify an Object array of {Nothing, Nothing, Nothing, "TABLE"}, you filter to include only a TABLE_TYPE of TABLE. You then list the table name (TABLE_NAME) of each row in the returned schema table. 1. Start Visual Studio .NET.
2. Create a new Visual Basic Console Application project. Module1.vb is added to the project by default.
3. Open the Code window for Module1. Paste the following code into the top of the Code window, above the Module declaration: Imports System.Data
Imports System.Data.OleDb

4. In the Code window, paste the following code into the Sub Main procedure.

Note You must change User ID <username> and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

Dim cn As New OleDbConnection()
Dim schemaTable As DataTable
Dim i As Integer

'Connect to the Northwind database in SQL Server.
'Be sure to use an account that has permission to list tables.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=<username>;Password=;Initial Catalog=Northwind"
cn.Open()

'Retrieve schema information about tables.
'Because tables include tables, views, and other objects,
'restrict to just TABLE in the Object array of restrictions.
schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})

'List the table name from each row in the schema table.
For i = 0 To schemaTable.Rows.Count - 1
Console.WriteLine(schemaTable.Rows(i)!TABLE_NAME.ToString)
Next i

'Explicitly close - don't wait on garbage collection.
cn.Close()

'Pause
Console.ReadLine()

Program loaded before Sql service

Anonymous10-Mar-05 23:23

Anonymous

10-Mar-05 23:23

Hi all.

I have a program running from Start > Programs > Startup.
When I start Winows, the program starts automatically, and I get SqlException (Sql Server Express 2005).

If I start the program manually, It starts normally.

I think that the program is loaded before the SQL service.
How can I check or change this?

Thanks,
Yaakov

Re: Program loaded before Sql service

Yulianto.11-Mar-05 16:07

Yulianto.

11-Mar-05 16:07

You can wait until the service is started. Check This[^]. Remember to start the service automatically when you start your OS.

<italic>Work hard, Work effectively and a bit of luck is the key to success.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.