|
hi
try this qry
select name from syscolumns where id=
(select id from sysobjects where name=)
|
|
|
|
|
In SQL,
Assume columnname- id(It should be identity and autoincrement),Name
then you will try this query
select name from tablename where id=0
Rajendran
|
|
|
|
|
In MS SQL Server syscolumns table holds column names and sysobjects table names.
SELECT * <br />
FROM syscolumns co<br />
INNER JOIN sysobjects so ON co.id = so.id<br />
WHERE so.name = 'mytable'
Regards
KP
|
|
|
|
|
Hi Jaya,
For a simple solution try this
sp_help ''
Senthil
|
|
|
|
|
|
sp_who/sp_who2 is not available in MySql. Use
SHOW FULL PROCESSLISTS , this shows which threads are running. Alternatively you can use MySql Administrator(comes with MySQL GUI Tools) to know the users and processes.
|
|
|
|
|
thank for help.it's work 
|
|
|
|
|
hey Guys, i am having troubling with this query., i want to Assign the RefinanceAmount instead of LoanAmount if the LoanID exists in tblLoanPurpose(LoanID, RefinanceID, RefinanceAmount) which i joined the table.
the way i want is
L.LoanAmount = 'NewfundsAmt'+@RefinanceAmount
the whole Query is
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
Anyone pls Help..
ALTER PROC [dbo].[upExceptions_GetPending]
@UserID varchar(10)
AS
BEGIN
Declare @LoanAmount money
Declare @RefinanceAmount money
SELECT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'' [LoanIncrease], --tooltip
A.ShortName,
LS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
L.LoanAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
NULL [ExceptionDays],
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID
AND LS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID IN (1,2)
AND E.LoanIncreaseID IS NULL
UNION ALL
--UPFRONT LOAN INCREASES
SELECT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'Loan Increase' [LoanIncrease], --tooltip
A.ShortName,
LIS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
LI.IncreaseAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
NULL [ExceptionDays],
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID
AND LIS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID IN (1,2)
AND E.LoanIncreaseID IS NOT NULL
UNION ALL
--TRAIL LOANS
SELECT DISTINCT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'' [LoanIncrease], --tooltip
A.ShortName,
LS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
L.LoanAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
E.ExceptionDays,
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID
AND LS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID = 3
AND E.LoanIncreaseID IS NULL
AND CA.ApplicantTypeID = 1
UNION ALL
--TRAIL LOAN INCREASES
SELECT DISTINCT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'Loan Increase' [LoanIncrease], --tooltip
A.ShortName,
LIS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
LI.IncreaseAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
E.ExceptionDays,
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID
AND LIS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID = 3
AND E.LoanIncreaseID IS NOT NULL
AND CA.ApplicantTypeID = 1
ORDER BY TradingName, SettlementDate
--CRT.CommissionRunType, CR.CommissionRunDate, SettlementDate
END
|
|
|
|
|
Is this problem somewhat similar to this one [^]?
Do you need to add a prefix to L.LoanAmount?
[Please post an answer to my post not yours so I can get an e-mail notification when you do so.]
|
|
|
|
|
First of all thanks to your response.,
yes, i need to add the prefix, if the RefinanceAmount is exists., i.e Ref.Amt 10000.00 otherwise just display the LoanAmount'. Just to find out which amount is that.
Thanks Heaps
|
|
|
|
|
Do you mean something like this?
DECLARE @foo money
SET @foo = 10.02
SELECT 'PREFIX ' + CAST(@foo AS varchar(10))
|
|
|
|
|
Yeah, exactly thats all i want!! I tried it in different ways but couldn't succeed
Thanks a lot Buddy!!
|
|
|
|
|
|
I created a table in SQL Express with an autoinc column. When I remove the autoinc the table will create, but when I make the id column an auto increment it will not creawte the table.
Does anyone know why?
Is there aqnother way to auto increment with sql express?
Here is the table.
create table tbl_teachers (<br />
teach_id int AUTOINC NEXTVALUE 1 STEP 1,<br />
teach_fname text,<br />
teach_lname text,<br />
teach_add text,<br />
teach_unit_num text,<br />
teach_city text,<br />
teach_state text,<br />
teach_zip int,<br />
teach_instruments text,<br />
teach_email text,<br />
teach_phone int,<br />
teach_cell int,<br />
primary key(teach_id));
Here is the error I get.
Msg 102, Level 15, State 1, Line 3<br />
Incorrect syntax near 'AUTOINC'.
|
|
|
|
|
AUTOINC NEXTVALUE is Visual FoxPro syntax. IDENTITY is the SQL Server equivalent.
Paul Marfleet
|
|
|
|
|
OleDb connects fine?
VS2005; C#; SQL 2005 Developer (remote server)
SqlClient connection string:
"data source=MyRemoteServer;user id=MyUserID;password=MyPassword;initial catalog=DbName;workstation id=PC1"
OleDb connection string:
"provider=SQLxxxx;data source=MyRemoteServer;user id=MyUserID;password=MyPassword;initial catalog=DbName;workstation id=PC1"
(xxxx = I forget the exact provider string)
OleDb connects fine, SqlClient times out looking for the server?
Am I missing something obvious? Should I really worry about this - I thought SqlClient was faster and better?
Thanks
Ian
|
|
|
|
|
Did you get this to work?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Hi ,
All my fellow Iranians , Please Vote for this!
https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=307273
|
|
|
|
|
You should learn how to do a clickety link here on CP (it will keep people from having to copy the URL and paste in the address bar). Highlight the URL and press Ctrl+L 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Hello. I have a very basic question (at least for most of you pros out there ). Are stored procedures executed in isolation - meaning that, while one SP is being executed no other SP or query can execute on the same record.
More specifically I would like to know if there is a chance that during the execution of the below SP the value of @@ROWCOUNT can be changed on line 16 by another SP or query (in which case the below SP is under the risk of returning a wrong value).
1: CREATE PROCEDURE ProductsUpdate
2: (
3: @ProductID int,
4: @Name varchar(40),
5: @Concurrency timestamp
6: )
7: AS
8: UPDATE
10: Products
11: SET
12: ProductName = @Name
13: WHERE
14: ProductID = @productID AND
15: Concurrency = @Concurrency
16: -- Is it possible to have another SP or query change @@ROWCOUNT at this point?
17: return @@ROWCOUNT
Also I was wondering about the same question but with generic queries, like below:
1: UPDATE
2: Products
3: SET
4: ProductName = @Name
5: WHERE
6: ProductID = @productID AND
7: Concurrency = @Concurrency;
8: -- Can any other query or SP get in here and alter @@ROWCOUNT?
8: SELECT @@ROWCOUNT
Thank you for your help  .
Niaher
-- modified at 10:52 Tuesday 30th October, 2007
|
|
|
|
|
Hi Niaher
The @@ROWCOUNT variable (and others like @@ERROR) are local to each database connection. Your stored procedure can use this to implement concurrency control.
Regards
Andy
|
|
|
|
|
Thank you. So what about a generic query with multiple statements, does it have a single connection? Can I use them to implement my concurrency control?
|
|
|
|
|
Yes you can use them for concurrency control. Which development language are you using?
|
|
|
|
|
Using C#. Btw I know it is a very controversial question, but what is a better choice performance-wise, a stored procedure or a dynamic query? In my case I will have not more than 15 tables and my goal is to support concurrecy in insert, update and delete logic, be it in SPs or dynamic queries.
Niaher
|
|
|
|
|
You're right - it is a very controversial question
Performance should be very similar for this type of logic - SQL-Server will cache query plans for both methods.
Stored procedures encourage:- Encapsulation of data-access logic - so you can substantially change the underlying SQL at a later date.
- Better security - You can deny direct access to the database tables, and force all access via the stored procedure layer.
- Reduced susceptablity to SQL-Injection attacks.
The downside is that you need to write more code, and many programmers don't like writing SQL.
I normally use Stored Procedures in my projects (because I tend to work for clients whose the coding standards dictate that they should be used). I normally write code generators to do the boring stuff for me.
Regards
Andy
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
