|
mrsuman wrote: im using two tables , tbl1,tbl2 . im multiplying tb1.column1 ,tbl2.column and displaying as result . i want to display sum(column1.column2) as total (another column).
can any body tell me the query for this ,
Is this what you are looking for:
select tbl1.column1,tbl2.column2,tbl1.column1*tbl2.column2 as Result, sum(tb1.column1*tbl2.column2) as Total
This query will have the Total column as the same value for all the rows.
|
|
|
|
|
Hi,
Sorry for late reply .
here im having price of seat in one table and no. of seats in another table.
i want to get result(Price * No. Of Seats ) as amount to pay as one cloumn and total of these values as another column.
i think now u can understand my problem ..
can u tell me the query how can i do this .
thanks .
|
|
|
|
|
i will explain where i stuck ..
i'm having two tables transaction and pricing . in transaction table i'm
having number of seats column and price/seat in pricing . here i want a
column which displays (no. of seats * price/ seat ) and sum of all these
rows as next row or separate column.i'm getting(no. of seats * price/seat)
using join but im not getting how to do sum of all these rows .
can u tell me this pls ..
thanks
csn
|
|
|
|
|
Can you give an example output you want from this query?
|
|
|
|
|
Thanks for u r reply ..
ex :
result
------
200
350
500
400
250
in next row or separate column i want sum of these rows .
|
|
|
|
|
If having it in the same row is Ok, then my earlier query should work for you (providing of course that you specify the correct join condition).
|
|
|
|
|
Hi...
I'm trying to get programmatically a backup file of my database, here is the code:
<br />
string BakFilePath = System.IO.Path.GetTempFileName();<br />
<br />
SQLDMO.Backup bak = new SQLDMO.BackupClass();<br />
bak.Devices = bak.Files;<br />
bak.Files = "[" + BakFilePath + "]";<br />
bak.Database = "MyDB";<br />
bak.BackupSetDescription = "Something";<br />
<br />
bak.SQLBackup(srv);<br />
Under VS2005 c# SQL Server 2005, in debug mode there are no problem.
When installing on a win xp with SQL Express 2005, I have the following error:
<br />
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'C:\Documents and Settings\JOZ\Local Settings\Temp\tmp19.tmp'. Operating system error 5(Access denied).<br />
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.<br />
I like sqldmo backup because is really quick... but this issue is driving me crazy... Can somebody help me please? 
Life is not short... the problem is only how you organize yourself
|
|
|
|
|
Maybe your SQL Server 2005 service is running under an account which has permissions to the backup folder, which the account used by SQL Express does not.
Try giving write permissions to the account under which SQL Express runs.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi all,
I m new in VC++, i m working on MFC-SDI document type application,
in my application i want to use MS-Acess 2007 Database,
but i dont have any knowledge about this.
please anyone can tell me at starting how an i use Access databse in my application.
thanks in advance.
|
|
|
|
|
|
i have 2 table in my database:one table for insert records,another table for backup of first table.i want write a trigger that execute when insert one record in first table(execute backup process)and calling a web service on this trigger.please help me as soon as soon
elham.a
|
|
|
|
|
I have a program written in VC++6 that connects to a database through SQL-ADO. Occasionally the DB server will disconnect or reconnect or fail in some way. Right now I have no indicator when this happens on my program side, so users who are currently logged don't know about the error and when they try to save their work an SQL error comes up saying...
Error(0)80004005:Connection Failure
I'm looking for some kind of Disconnect or Reconnect event or indicator of some sort in SQL-ADO. SQL-DMO has the ConnectionBroken Event which seems to be about what I want. Is there anything similar in ADO? If not, what is the best way to detect a broken connection? I'd rather not resort to polling the server if possible.
|
|
|
|
|
If you're using System.Data.SQLClient.SQLConnection, you have a property called State (SqlConnection.State) which will return the current state of the connection.
Reference
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Thanks. I can use the State property, but it isn't updating correctly. When the error occurs, state is still set to Open, even though the connection has been broken. Is there any kind of message or event that happens when there is a disconnect?
|
|
|
|
|
Hi
here is what I have
1- I have 2 different tables in 2 different databases
2- I need to write a stored procedure to start a transaction to add data in both tables so if any error occured then rollback
so I want to access the table in the other database to add data to it
any help
thanks
Shaimmaa
|
|
|
|
|
Get the data from one database table to anothter databse
select * from databasename.dbo.tablename
smile 
|
|
|
|
|
Assumes the DBs are on the same server (valid assumprion from the ?)
If the DBs are on different servers you will need to look into Linked servers (nasty things linked servers)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
tran log of my database is now hge in size and my disc space is almost full.
what do i do now? should i take backup of tran log or change the path of transaction log file to another disc location..
please suggest..
|
|
|
|
|
I'm assuming you aren't backing up your transaction log at all. If that is the case then change the recovery model of your database to Simple. Then detach your database, delete the transaction log file and re-attach your database. A new log file will be created for you.
To set your recovery model to simple use:
ALTER DATABASE [databasename] SET RECOVERY SIMPLE
Do NOT do this while users are logged in. Wait for a time when there is no activity on your database. If you don't have a maintenance window which allows you to do this you will need to truncate your log and then shrink it.
To shrink your transaction log file, first get the logical name of your transaction log by running sp_helpdb 'your database name'. There will be 2 resultsets, the second one will have a column "name", for the row that refers to your log file, that is the logical name.
Then you can run the following script:
<br />
BACKUP LOG [Database Name] WITH TRUNCATE_ONLY<br />
GO<br />
<br />
DBCC SHRINKFILE([LOGICAL FILE NAME], [SIZE]) <br />
GO<br />
where [size] is an integer value in megabytes.
Again, this is only if you are not using your transaction log. The commands will be different if you need to backup your log file. Refer to BOL.
Mark's blog: developMENTALmadness.blogspot.com
Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher
|
|
|
|
|
thanx a lot... i just did as per ur procedure..
|
|
|
|
|
In answer to your question:
sohne wrote: change the path of transaction log file to another disc location..
It's a good idea to keep the transaction log on a different disk to the database - doing this will speed up SQL Server.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
I have a column in my table that is both telephone numbers and text. I have made the field type text as I want to be able to use both in my database. However when I import it into my database, it wants to import that field type as double?! so when it enters my database, all the text entries are null.
Please can someone tell me how to get both the numbers and the text in the database?
|
|
|
|
|
Create the table first in SQL
CREATE TABLE tblCust(RowId int IDENTITY(1,1) PRIMARY KEY, Phone nvarchar(20) NULL)
Then import and use column mapping.
|
|
|
|
|
I've just done that, but even when I am on the mapping form(hopefully you've used the import tool with SQL 2005) although the type field says nvarchar, at the bottom of the page it says this:
Source Column Destination Telephone No Double(15)
But that makes no sense because in my excell spreadsheet the field is a text field, I changed it from a general field to a text field?????
|
|
|
|
|
Hey all, Please help!
I have a delete statement that relys on data not being in another table, it is as follows:
delete MyTable
where substring(MyColumn, 1, charindex('/', MyColumn) - 1) not in (select MyOtherColumn from MyOtherTable)
The problem is that not all the rows contain a '/' so the statement will error as no int value is found for the length parameter coming from char index.
This needs to be done as a case statement I think, but I just can't find the correct syntax, I'm trying to avoid looping through each row as theres lots of them!
Any help would be greatly appreciated.
Many thanks in advance
Mark
Many divs make for very hard work
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
