|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
It would be very helpful , to show us whats the error you get ?
Some code would be helpful too..
Bahadir Cambel
|
|
|
|
|
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
|
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Can you read the whole text file into a string and then write the string to the database?
|
|
|
|
|
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
|
|
|
|
|
|
Please tell me! how can i get all tables name in access file (.mdb) ?
Thanks!
xyz
|
|
|
|
|
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.
|
|
|
|
|
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()
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
Wow, thanks a lot.
I didn't know that.
|
|
|
|
|
Hi, I'm choosing between transaction on stored proc level and transaction on application level. You see:
<br />
DECLARE @ValidationCode INTEGER<br />
BEGIN TRAN<br />
SET @ValidationCode = 0<br />
EXEC sp_CreateSupplier 1, 'M', 1, 'Golden Lucky and Associates', 'Golden Lucky and Associates', NULL, NULL, 'A', 3, 8, @ValidationCode OUTPUT<br />
PRINT 'Validation code: ' + CAST (@ValidationCode AS VARCHAR(10))<br />
<br />
... do other stuff...<br />
<br />
COMMIT TRAN<br />
<br />
CREATE PROCEDURE sp_CreateSupplier(<br />
@supplier_no INTEGER,<br />
@type VARCHAR(8),<br />
@group_no INTEGER = NULL,<br />
@short_name VARCHAR(50) = NULL,<br />
@full_name VARCHAR(100) = NULL,<br />
@address VARCHAR(100) = NULL,<br />
@country_code VARCHAR(10) = NULL,<br />
@status VARCHAR(8) = NULL,<br />
@createBy INTEGER,<br />
@ownerGroup INTEGER,<br />
@ValidationCode INTEGER OUTPUT<br />
)<br />
AS<br />
BEGIN<br />
<br />
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />
BEGIN TRANSACTION<br />
<br />
-- STEP 1: Validation<br />
SET @ValidationCode = 0<br />
IF EXISTS(SELECT * FROM fe_supplier WHERE supplier_no=@supplier_no)<br />
BEGIN<br />
SET @ValidationCode = -10<br />
ROLLBACK TRANSACTION<br />
RETURN<br />
END<br />
...<br />
COMMIT TRAN<br />
END<br />
The above snippet will result in:
Server: Msg 266, Level 16, State 2, Procedure sp_CreateSupplier, Line 26<br />
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
Now, what I'm really doing in my code is I called the stored proc from a method in application level - And this method is executed in context of a transaction (Which wraps around the stored proc with its own transaction):
Public Function Create(ByVal sup As vendormanagement.to.Supplier, ByVal tran As IDbTransaction) As vendormanagement.to.Supplier<br />
<br />
Dim sql As String = "sp_CreateSupplier"<br />
Dim cmd As IDbCommand<br />
Dim rdr As IDataReader<br />
Dim param As IDataParameter<br />
Dim factory As IDbProviderFactory<br />
Dim validationCode As Int32 = 0<br />
<br />
Try<br />
If _conn.State <> ConnectionState.Open Then<br />
_conn.Open()<br />
End If<br />
<br />
factory = apputil.GlobalVariables.ProviderFactory<br />
cmd = factory.CreateCommand(sql, Conn)<br />
cmd.CommandType = CommandType.StoredProcedure<br />
cmd.Transaction = tran<br />
<br />
...<br />
rdr = cmd.ExecuteReader()<br />
...<br />
Catch<br />
...<br />
Finally<br />
...<br />
End Try<br />
End Function
What I wish to do is to MAINTAIN transaction in stored proc level - which suggest that I must remove application level transaction. BUT I can't do that because:
<br />
vconn = vFactory.CreateConnection(...)<br />
tran = vconn.BeginTransaction(...serializable...)<br />
SupplierBean.Create(..., tran)<br />
AnotherBean.Update(..., tran)<br />
tran.Commit()<br />
So, my question is, how can I reconcile the two: Application Vs Stored proc transaction. The ONLY way to just recitify the problem seems to be removing stored proc level transaction.... Am I right?
Thanks in advance!
Norman Fung
|
|
|
|
|