|
So do you want distinct rows or distinct based on a specific column - if its the first then you have the answer, otherwise you are not asking the right question
Bob
Ashfield Consultants Ltd
|
|
|
|
|
How can I assign the result of a query to a variable. For example
DECLARE @FoundID VARCHAR(10)
IF NOT EXISTS(SELECT @FoundID = Employee.EmployeeID
FROM Employee
JOIN EmployeeTerritory
ON Employee.EmployeeID = EmployeeTerritory.EmployeeID
JOIN Territory
ON EmployeeTerritory.TerritoryID = Territory.TerritoryID
WHERE AssignedPostal = SUBSTRING(@CustomerPostalCode, 1, 1))
RAISERROR('The speicified CustomerPostalCode is not assigned to any salesperson! Unassigned PostalCode!', 11, 1)
So basically I want to assign Employee.EmployeeID from the query result to the variable. Most examples online show assigning result of aggregate functions to variables.
Thanks
|
|
|
|
|
CodingYoshi wrote: So basically I want to assign Employee.EmployeeID from the query result to the variable. Most examples online show assigning result of aggregate functions to variables.
If your SELECT results in only one row being returned then it should work. However, you are doing the assignment inside an IF (NOT) EXISTS which doesn't obey the normal rules of processing. It is designed to short circuit as soon as any matching data is found in order to allow the appropriate conditional statements to be run.
I would suggest moving the SELECT out of the IF condition and changing the IF condition to an ISNULL(@FoundId).
|
|
|
|
|
ISNULL(@FoundID) takes 2 parameters and when provided results in error. But this works
IF @FoundID IS NULL
RAISERROR('The speicified CustomerPostalCode is not assigned to any salesperson! Unassigned PostalCode!', 11, 1)
ELSE
SET @SalesPersonID = @FoundID
Thanks for advising to move the select statement out of if statement.
|
|
|
|
|
CodingYoshi wrote: ISNULL(@FoundID) takes 2 parameters and when provided results in error.
Ah yes... Sorry, I was going off the top of my head. Glad it still pointed you in roughly the right direction.
|
|
|
|
|
DECLARE @FoundID VARCHAR(10)
SELECT @FoundID = Employee.EmployeeID
FROM Employee
JOIN EmployeeTerritory
ON Employee.EmployeeID = EmployeeTerritory.EmployeeID
JOIN Territory
ON EmployeeTerritory.TerritoryID = Territory.TerritoryID
WHERE AssignedPostal = SUBSTRING(@CustomerPostalCode, 1, 1)
IF NOT EXISTS(@FoundID)
RAISERROR('The speicified CustomerPostalCode is not assigned to any salesperson! Unassigned PostalCode!', 11, 1)
|
|
|
|
|
NOT EXISTS(@FoundID) results in error. But this works
IF @FoundID IS NULL
RAISERROR('The speicified CustomerPostalCode is not assigned to any salesperson! Unassigned PostalCode!', 11, 1)
ELSE
SET @SalesPersonID = @FoundID
Thanks for advising to move the select statement out of if statement.
|
|
|
|
|
Could some please translate this error to human language?
I get it after I attempt to do recordset update.
What is a descriptor index and if it is invalid what is is?
Can I get more info from VC 6.0??
Thanks for reading.
Vaclav
PS Yes I did google it and came up with nothing solid.
|
|
|
|
|
I have developed a project for a tutor academy in VS2005. For data accessing I used SQL Server 2005 Express features like Data Adapter etc. Now I want to implement the data accessing through ADO.NET. Is there any conversion tool available on the internet which can translate / convert data accessing methodology of SQL Server Express to ADO.NET? If not how can I achieve it?
DotGuru
|
|
|
|
|
kashshay wrote: I used SQL Server 2005 Express features like Data Adapter etc. Now I want to implement the data accessing through ADO.NET
Data adapter is an ado.net technology, sql express has nothing to do with it. So you arr already using ado.net.
|
|
|
|
|
hi
I want to know how can i connect to other pc sqlserver with another pc
over lan connection
Thanks
|
|
|
|
|
You have to enable TCP/IP on the SQL Server. By default it is switched off. You can do this in the SQL Server Configuration Manager.
You will then be able to connect to the SQL Server from other PCs.
|
|
|
|
|
Yo can use UDL file [^]too.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi,
I am using SQL Server 2005. When I create an index on a foreign key there is an option to check the "Use Page Locks when Accessing the Index" option. I do not know when to check this and when not to?
Can someone please help clear this for me? If checked will it reduce performance, or is it standard procedure to always have it checked?
Thanks
Brendan
|
|
|
|
|
i want to spilit the date on format dd/mm/yyyy and insert the substring as primary key field on another table
i tried the following but it failed
declare @exitDate smalldatetime
set @exitdate=(select Exit_date from invoice_details)
select substring( cast (@exitDate As varchar) As exitDateText,5,4)
from invoice_details
help please
regards
|
|
|
|
|
I'm guessing, because you didn't explain (you just supplied some broken code and said it didn't work) that you want to extract the year. Is that correct?
If so, why not just use the YEAR(@someDate) function? You can then cast that into a varchar.
|
|
|
|
|
Hi,
I'm using SQL Server 2000 with VB.NET 2005.
I'm trying to access the database which is in remote machine thru internet.
Can you help me with the required details & connection string...
Thanks & Regards.
Hariram
|
|
|
|
|
The connection string will be as normal, but with the IP address or full domain name as the server name.
|
|
|
|
|
Thanks Colin,
The IP Address should be a STATIC IP right...?
Regards,
Hariram
|
|
|
|
|
The IP address of the SQL Server must be static
|
|
|
|
|
hi,
This is code i am using I've picked it from some where and I've changed it according to my requirements but its not inserting a new record. it gives error like attempt failed or record set is read only. I don't know i am using dynaset as an opening mode but still i get the error. kindly any one knows where i am going wrong.
CDatabase *mydb=new CDatabase;
ASSERT(mydb);
mydb->OpenEx("DSN=ff");
if(!mydb->IsOpen())
{
MessageBox("Unable to connect to Database. Please\ncontact your server administrator.", "Database Error", MB_ICONEXCLAMATION);
return;
}
CRecordset*myrec=new CRecordset(mydb);
ASSERT(myrec);
myrec->Open(CRecordset::dynaset,_T("SELECT s,p FROM e"),CRecordset::noDirtyFieldCheck);
if(!myrec->CanUpdate())
{
MessageBox("Database reports unable to update.", "Database Error", MB_ICONEXCLAMATION);
myrec->Close();
return;
}
if(!set.Open(CRecordset::dynaset,"SELECT * From e",CRecordset::noDirtyFieldCheck))
{
MessageBox("Unable to open database record. (SELECT * e)", "Database Error", MB_ICONEXCLAMATION);
set.Close();
return;
}
/*if (!set.CanAppend())
{
MessageBox("Database does not support adding records. Please\ncontact your server administrator.", "Database Error", MB_ICONEXCLAMATION);
set.Close();
}*/
set.MoveFirst();
while( !set.IsEOF() )
{
set.MoveNext();
}
int lRecCnt = set.GetRecordCount();
int newID=lRecCnt+2;
m_g=lRecCnt;
UpdateData(FALSE);
// Prepare for a new record addition.
try
{
set.MoveLast();
set.AddNew();
set.m_s = "Becky Dugan";
set.m_p = "Becky Dugan";
set.Requery();
set.SetFieldDirty(NULL);
set.Update();
}
catch(CException* pE)
{
char buf[256];
pE->GetErrorMessage(buf, 256, NULL);
AfxMessageBox(buf);
pE->Delete();
}
// Close Recordset
set.Close();
// Close Database
mydb->Close();
|
|
|
|
|
I have a similar problem, but I think you should not use Requery.
I tryied it in my code and it wiped out the data added to record created by AddNew.
Vaclav
|
|
|
|
|
hey thr...its not working even if i eliminate the Requery statment...anything else ????
|
|
|
|
|
hi friends
what is this error?
i have not sqlserver 2005, i just install sqlserver 2000 but in the error text you see a text about failding to connect to sqlserver 2005?????
what happen for my server or program?
is there anybody to tell me what happen?
nobody help you...
you have to help you yourself
and this is success way.
|
|
|
|
|
Hello,
When i run the report through my application, at that time system perfomance get down(memory usgae get down).
Please help me to sort out this issue.
-Bhushan
Bhushan Kukade
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
