|
you kind of lost the thread there with your second post, however..
Either change the tables to allow nulls,
Or ensure no null values are passed to the procedure,
Or best of all, use isnull checks for each insert.
e.g.
INSERT INTO tbcontactaddress (emp_id,city,town,street, phone)
VALUES
(
isnull(@emp_id, '')
,isnull(@ccity, '')
,isnull(@ctown, '')
,isnull(@cstreet, '')
,isnull(@cphone, '')
)
Another consideration is that rather than passing so many parameters you may be better off passing it all wrapped up in a single XML parameter.
|
|
|
|
|
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses
Above query lists all hosts currently logged in using which login name.
But i want which hostname have logged in past using which login name . where do i found this information ?
Is there any log is maintained for that.
|
|
|
|
|
Member 6392036 wrote: i want which hostname have logged in past
AFAIK, the information isn't stored historically. If you want to know who was online when, then you'll need to add some kind of logging.I are Troll 
|
|
|
|
|
CREATE PROCEDURE sp_Get_Borderel_4d_data
@MaatschappijID numeric,
@VervalDatum datetime,
@LanguageID char(1)
AS
DECLARE @MaatschappijIDLoc numeric
DECLARE @VervalDatumLoc datetime
DECLARE @LanguageIDLoc char(1)
SET @MaatschappijIDLoc = @MaatschappijID
SET @VervalDatumLoc = @VervalDatum
SET @LanguageIDLoc = @LanguageID
SELECT
Agent.AgentID,
Agent.Naam AS AgentNaam,
ProRataDetail.InternPolisNummer,
(sum(ProRataNettoPremie) - sum(ProRataCommissie)) AS Techpremie,
sum(ProRataNettoPremie) As ProRataNettoPremie,
sum(ProRataTaksen) As ProRataTaksen,
sum(ProRataBrutoPremie) As ProRataBrutoPremie,
sum(ProRataCommissie) As ProRataCommissie,
sum(ProRataBeheerskosten) As ProRataBeheerskosten
FROM
fn_Get_ProRataDetail(0, '{00000000-0000-0000-0000-000000000000}', 0, 0, @MaatschappijIDLoc, @VervalDatumLoc, @LanguageIDLoc)
as ProRataDetail
LEFT JOIN Polis ON Polis.PolisID = ProRataDetail.PolisID
LEFT JOIN Klant ON Klant.KlantID = Polis.KlantID
LEFT JOIN Agent ON Agent.AgentID = Polis.AgentID
WHERE
Polis.Demo <> 1
GROUP BY
Agent.AgentID,
Agent.Naam,
ProRataDetail.InternPolisNummer
ORDER BY
Agent.Naam,
ProRataDetail.InternPolisNummer
GO
----------------------------------------------------------
CREATE PROCEDURE sp_Get_Borderel_4d_data
@MaatschappijID numeric,
@VervalDatum datetime,
@LanguageID char(1)
AS
SELECT
Agent.AgentID,
Agent.Naam AS AgentNaam,
ProRataDetail.InternPolisNummer,
(sum(ProRataNettoPremie) - sum(ProRataCommissie)) AS Techpremie,
sum(ProRataNettoPremie) As ProRataNettoPremie,
sum(ProRataTaksen) As ProRataTaksen,
sum(ProRataBrutoPremie) As ProRataBrutoPremie,
sum(ProRataCommissie) As ProRataCommissie,
sum(ProRataBeheerskosten) As ProRataBeheerskosten
FROM
fn_Get_ProRataDetail(0, '{00000000-0000-0000-0000-000000000000}', 0, 0, @MaatschappijID, @VervalDatum, @LanguageID)
as ProRataDetail
LEFT JOIN Polis ON Polis.PolisID = ProRataDetail.PolisID
LEFT JOIN Klant ON Klant.KlantID = Polis.KlantID
LEFT JOIN Agent ON Agent.AgentID = Polis.AgentID
WHERE
Polis.Demo <> 1
GROUP BY
Agent.AgentID,
Agent.Naam,
ProRataDetail.InternPolisNummer
ORDER BY
Agent.Naam,
ProRataDetail.InternPolisNummer
GO
--------------------------------------------------------------------------
Why is the first procedure, faster than the second?
The ony difference are the extra local variables!
thanks,
A.
|
|
|
|
|
The question is why are you using 3 extra variables, that takes 3 extra cycles and therefore it is slower  Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I can run it a 1000 times!
the difference is:
the first one with local variables: I get a result in 5 seconds.
the second one without local variables: I get a result in 20 minutes!!!!
Thank you
|
|
|
|
|
This looks like a problem caused by Parameter sniffing. Just google on it and you'll see what I mean.Wout Louwers
|
|
|
|
|
thank you all,
it has something to do with Parameter Sniffing!
It explains a lot!
Is this the best practice? to disable parameter sniffing?
Thank you
|
|
|
|
|
Well,
You are using the parameters in the call to fn_Get_ProRataDetail. You could try what happens ig you do the local var trick in that function. That way other sp's may benefit too. But the local var trick is the only one I know besides using With Recompile.Wout Louwers
|
|
|
|
|
See thats the reason I spend time here, never heard of parameter sniffing, sounds disgusting. There are some excellent articles out there, another tool in the ongoing fight for performance.
Thanks Wout.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm not sure if this is the best place to post this, but I'm retrieving a table form an SQL database and databinding the contents into a DataGridView.
I have some code that searches the entire DataGridView and locates null values, and I intend to color the fields red so the user knows he needs to put something in.
private void button1_Click(object sender, EventArgs e)
{
for (int column = 0; column < 1; column++)
{
for (int row = 0; row < this.dataGridView.RowCount; row++)
{
if (this.dataGridView.Rows[row].Cells[column].Value == null)
{
this.dataGridView.Rows[row].Cells[column].Style.BackColor= Color.Red;
}
}
}
}
theres still some messageboxes in there that I used to figure this out...
If I change the conditional statement to (this.dataGridView.Rows[row].Cells[column].Value != null) so that it searches for non null fields, it successfully colors the cells.
I guess my problem has to do with the nullity (lol is that a word?) of my cells.
I guess I should include how I am populating my dataGridView...:
private void Form1_Load(object sender, EventArgs e)
{
this.pickups___CarsTableAdapter.Fill(this._Vehicle_EquipmentDataSet._Pickups___Cars);
}
that was auto generated =)
Perhaps I can handle the null data when the list is populated?
Thanks in advance!!!modified on Thursday, March 18, 2010 12:45 PM
|
|
|
|
|
Mattzimmerer wrote: this.dataGridView.Rows[row].Cells[column].Style.ForeColor = Color.Red;
how do you see the ForeColor of an empty cell?
|
|
|
|
|
Ohh, i should have changed that back. No I first tryed backcolor or w/e, which works find for populated cells... that was a purley desperate attempt..
THE PROBLEM PERSISTS!!!!
|
|
|
|
|
You should check the value from the data source itself, not datagridview's cells. Subscribe to the datagridview's CellFormatting event, then in the handler get the DataBoundItem (object, datarow etc) and set color base on the DataBoundItem's data itself.
I'm binding to collection of object, btw, so YMMV.
private void dataGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
string dataPropertyName = this.dataGridView.Columns[e.ColumnIndex].DataPropertyName;
ObjectType theObject;
switch (dataPropertyName)
{
case "ColumnName":
theObject = (ObjectType)this.dataGridView.Rows[e.RowIndex].DataBoundItem;
break;
}
}
|
|
|
|
|
Fixed my problem:
private void Null_check_Click(object sender, EventArgs e)
{
for (int row = 0; row < this.dataGridView.RowCount-1; row++)
for (int column = 0; column < this.dataGridView.ColumnCount; column++)
if (this.dataGridView.Rows[row].Cells[column].Value.ToString() == "")
this.dataGridView.Rows[row].Cells[column].Style.BackColor = Color.Red;
}
just needed sleep I guess...
|
|
|
|
|
CREATE TABLE [PreferenceType] (
[PreferenceTypeId] [BIGINT] IDENTITY (1,1) NOT NULL,
[LongName] [NVARCHAR](500),
[ShortName] [NVARCHAR](250)
);
CREATE TABLE [Table2]
(
[Table2Id] [BIGINT] IDENTITY (1,1) NOT NULL,
[PreferenceTypeId] [INT]
)
Given the Preference Lookup table above, if I do an Inner Join to Table2 on the PreferenceTypeId field, I should index like this:
CREATE INDEX [PreferenceTypeId_Idx] ON [Table2]([PreferenceTypeId]); <--- good?
But I shouldn't index like this:
CREATE INDEX [PreferenceTypeId2_Idx] ON [PreferenceType]([PreferenceTypeId]); <--- bad?
because Primary Key fields should already be indexed, correct?
|
|
|
|
|
You should make PreferenceType.PreferenceTypeId a PrimaryKey (Yes it will be automatically indexed) and you should make Table2.PreferenceTypeId a ForeignKey constraint referencing PreferenceType.PreferenceTypeId (this will also be automatically indexed).
[Edit]
Foreign keys are NOT automatically indexed.modified on Wednesday, March 17, 2010 5:06 PM
|
|
|
|
|
KEWL, i learned something new, that foreign keys automatically get indexed for me.
|
|
|
|
|
| I believe that's incorrect. Creating an index on a foreign key is usually recommended, but not done automatically[^]. "When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thank you for the correction and the links.
|
|
|
|
|
| You're welcome. "When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Sorry in advanced for a duplicate question or a really basic question, but is nvarchar better than varchar? Is there an advantage for nvarchar? I've always just used varchar. Thanks!
|
|
|
|
|
Nvarchar stores unicode characters which means you don't have the problem with different codepages in different countries. But it also uses twice the space.
If you need to store characters from several alphabets in the same field you really will like nvarchar."When did ignorance become a point of view" - Dilbert
|
|
|
|
|
multi language benefit, but space cost...got it, thanks!
|
|
|
|
|
Heres another one, SQL Server CE ONLY supports nvarchar, nchar etc so if you need to use CE then you know the requirement. Also does not support stored procedures 
I am currently bleeding from this particular bite me!Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
