|
You could always replace the CASE statement with a COALESCE so that it becomes:
COALESCE (dbo.tblRequest.inQuoteId, 0)
Coalesce simply searches the items from left to right, looking for the first none NULL items. So, if inQuoteId is null, it uses 0 else it uses inQuoteId.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks man!
The View works. I modified the code so that it looks like this :
<br />
SELECT dbo.vw_ProjectStage.vchProjectStage, dbo.tblRequest.inRequestID, dbo.tblRequest.vchRequestSummary, dbo.tblRequest.inTimeEstimate, <br />
dbo.tblRequest.inOrder, dbo.tblRequest.dtCompletionDate, dbo.tlkpProgress.vchProgressName, dbo.tlkpImportance.vchImportanceName, <br />
dbo.tblRequest.vchRequestNumber, dbo.tlkpRequestType.vchRequestTypeName, dbo.tlkpRequestType.inRequestTypeID,<br />
COALESCE(dbo.tblRequest.inQuoteID, 0) AS "inQuoteID" <br />
<br />
FROM dbo.tlkpProgress INNER JOIN<br />
dbo.tblRequest ON dbo.tlkpProgress.inProgressID = dbo.tblRequest.inProgressID INNER JOIN<br />
dbo.tlkpImportance ON dbo.tblRequest.inImportanceID = dbo.tlkpImportance.inImportanceID INNER JOIN<br />
dbo.vw_ProjectStage ON dbo.tblRequest.inStageID = dbo.vw_ProjectStage.inStageID INNER JOIN<br />
dbo.tlkpRequestType ON dbo.tblRequest.inRequestTypeID = dbo.tlkpRequestType.inRequestTypeID<br />
|
|
|
|
|
Cool
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
See the following page
http://www.ataland.com/pages/tour/tours.aspx[^]
In this page I have four combo boxes. I fill these combo boxes one by one ,I mean for filling each one I open and close a connection. And then fill the datagrid. I want to know if I am doing right.??? How much can this degrade performance of the site ,is it worth modifing the code or it will not make much difference ???
|
|
|
|
|
If your design allows it and filling these 4 combos at the same time will happen frequently enough then you can always call a stored procedure with 4 SELECTs in it to get the data for the combos.
If you are using a DataReader you can move between the results from the SELECTs with NextResult().
If you are using a DataAdapter the tables in the DataSet will be populated in the order of the SELECTs MyDataSet.Tables[0] --> MyDataSet.Tables[3]
|
|
|
|
|
How the results are returned in dataset if I have a stored procedure that calls other stored procedures containing a select statement. does it make any difference ???
|
|
|
|
|
The results will be returned in the order in which they were encountered.
|
|
|
|
|
Hi,
Whenever I add a new VS auto-generated class they are created as Public by default. Is it possible to make some of the classes internal by default?
If I edit the code manually it will get auto-generated when I change the dataset, which I don't want to happen.
Any help much appreciated.
Andrew
|
|
|
|
|
Hi all
I have a very general query. Can anyone throw some light on the variious reasons for a failure in a database transaction and what shud b the steps for averting it and recovering for it..
Any help is appreciated..
Thanx
|
|
|
|
|
amu_sapra wrote: Can anyone throw some light on the variious reasons for a failure in a database transaction and what shud b the steps for averting it and recovering for it..
There are 152 unique reasons for a transaction to fail fatally. Which would you like to discuss?
|
|
|
|
|
i hv to make a study of the various reports so can u tell me the most obvious and common reasons..maybe the around 20 reasons....
|
|
|
|
|
amu_sapra wrote: i hv to make a study of the various reports so can u tell me the most obvious and common reasons
If they are obvious then surely you should have notice them already. If they are common surely you should have received a message already.
This is obviously a homework question. And the most common response is that you should attempt to do the work yourself and if you have any specific problems then come back and we'll help you out. You have to show you are at least willing to study on your own.
|
|
|
|
|
Hi all,
I got a problem with running distributed transactions between two servers that is connected via VPN connection (using RRAS).
after the VPN connection is connected I can connect to the remote server with Microsoft SQL server management Studio and I can run query againts server.
The problem occures when one of my store procedures start a distributed transaction and wants to insert at the remote server. (with just one insert command I can insert rows in the remote server but I can not wrap in a transaction) I got this error :
"OLE DB provider "SQLNCLI" for linked server "172.26.0.11\CENTER" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure p_InsertPointOnline, Line 54
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "172.26.0.11\CENTER" was unable to begin a distributed transaction."
I tried the following things :
1. turn off firewall in both sides.
2. Checking MS DTC security to allow outbound an inbound transactions on both sides
3. SET XACT_ABORT ON
4. SET REMOTE_PROC_TRANSACTIONS ON
the strange thing is when I switched the firewall off and reconnect the VPN I got some transactions through. But the day after that the system did not work, after a lot of time I again turn the firewall back on with some added port as exceptions. Again it works until I disconnect the VPN and reconnect. I again turn the firewall off and reconnect the VPN it works again, you see the loooop  !!!
if any one could help me with this issue I would be gratefull.
Hope you never stuck in such situations.
|
|
|
|
|
Check which ports your firewall are blocking.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
The firewall on both sides are off.
I discovered the reason but the do not know what to do exactly.
The thing is that the remote server which is VPN server can not use netbios to reply to local server it can not resolve the Computer name. after the connection is made it is for about two seconds can reslove the name but after that it stop.
I you know beside firewall what can cause such problem do not hesitate to tell me.
Extra information :
the remote server is win2003
local server is Winxp Pro
both have sql 2005 standard edition
No firewall software is installed and windows firewall is off.
Thanks
|
|
|
|
|
hi friends,
I want a delete query to delete rows from multiple tables. but all the tables containg one command field as 'id'. and the deletion based on some input given to this common field at run time in msaccess. Please help. I am trying like this. But it gives Could not delete message.
DELETE contact.*, address.*
FROM contact, address
WHERE contact.consultantid=[@consultantid] and address.consultantid=[@consultantid];
Thanks in advance.
Regards,
Prya
|
|
|
|
|
You cannot perform a delete on two tables at once. You can perform two deletes, and this is what you should do.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
also you could set up cascading deletes and then if you delete from one table the record gets removed from the related table. I'm never sure how much i like cascading deletes though. Pete's method is the one i would use in my code you can pass the two commands separated by ; if you only want to make 1 call (i believe)
Russ
|
|
|
|
|
You also need to be careful about the order of the delete statements if you are using foreign key constraints. In this case, you would need to delete from the address table before the contact table.
Ian
|
|
|
|
|
declare @ID as int<br />
set @ID=41 --instad 41 use parameter<br />
DELETE FROM contact WHERE ID =@ID<br />
DELETE FROM addressWHERE ID =@ID
"My advice to you is to get married. If you find a good wife, you will be happy; if not, you will become a philosopher." Socrates
|
|
|
|
|
If you are using SQL Server you can write a FOR DELETE trigger on the table 'contact'. Try this: -
--------------------------------------------------------------------------------------------------
CREATE PROCEDURE DeleteContact @consultantid int
AS
DELETE FROM contact WHERE consultantid=@consultantid
--------------------------------------------------------------------------------------------------
Now create a trigger on the table 'contact': -
--------------------------------------------------------------------------------------------------
CREATE TRIGGER DeleteAddress ON contact
FOR DELETE
AS
CREATE TABLE #DeletedContacts
(
consultantid int
)
INSERT INTO #DeletedContacts SELECT consultantid FROM deleted
DELETE FROM address WHERE consultantid IN (SELECT consultantid FROM deleted)
--------------------------------------------------------------------------------------------------
Now, you execute the stored procedure: EXEC DeleteContact <anyconsultantid>
After it deletes from the table 'contact' the trigger will be excuted and it will delete corresponsing rows from the table 'address'.
I am a Software Developer using C# on ASP.NET.
|
|
|
|
|
Hy,
Backup a database with fulltext catalog doesn't work properly with a maintenance plan?
Error =>
Executing the query "BACKUP DATABASE [BD_ALA] TO DISK = N'E:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\BD_ALA\\BD_ALA_backup_200611232301.bak' WITH NOFORMAT, NOINIT, NAME = N'BD_ALA_backup_20061123230123', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The backup of the file or filegroup "sysft_FullTextSlug" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Executing the query "BACKUP DATABASE [BD_ALA] TO DISK = N'E:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\BD_ALA\\BD_ALA_backup_200611232301.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'BD_ALA_backup_20061123230139', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The backup of the file or filegroup "sysft_FullTextSlug" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Executing the query "BACKUP DATABASE [BD_ALA] TO DISK = N'E:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\BD_ALA\\BD_ALA_backup_200611232301.bak' WITH NOFORMAT, NOINIT, NAME = N'BD_ALA_backup_20061123230123', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The backup of the file or filegroup "sysft_FullTextSlug" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Where I can find model's of plan maintenance for medium database (300 000 records per year) ?
Any ideas.
Best Regards
-- modified at 8:49 Friday 24th November, 2006
|
|
|
|
|
hello all,
am a begginner in sql2000 db. i want to know, how to create composite primary key in sql? using table creation wizard..can any one help me...
and also how to create triggers using sql... how to create tables and triggers using query Analyzer...please give me one simple example for create table and trigger using query analyzer.
thank you..
|
|
|
|
|
rrrriiizz wrote: i want to know, how to create composite primary key in sql? using table creation wizard..can any one help me...
I have no idea what you mean by "table creation wizard". However, in both the design view and the diagram view you can select multiple columns then right click and select "Primary Key" from the menu.
rrrriiizz wrote: and also how to create triggers using sql... how to create tables and triggers using query Analyzer...please give me one simple example for create table and trigger using query analyzer.
Have a look in the SQL server Books On Line (look up CREATE TRIGGER in the index, it will give you all the details and a few examples)
|
|
|
|
|
Hey all,
how do I update table1 depending on its relation with table2 and table3?
eg.,
UPDATE table1 SET table1.col1 = (SELECT table4.col1 FROM table4 WHERE table4.col2 = "asdfgf" ) WHERE table1.col2 = table2.col1 AND table1.col3 = table3.col1
Is the above correct?
Or should I use JOIN to do the above? If so, how do I do it?
Can anyone help?
Thanks in advance.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
