Database

Mark J. Miller28-Mar-08 9:19

28-Mar-08 9:19

Without knowing why you need to reseed the value, it's difficult to make recommendations.

However, a case statement wouldn't work because CHECKIDENT doesn't return a value. But if you used logic similar to the following it should do what you're asking:

<br />
DECLARE @ident INT<br />
<br />
SET @ident = SCOPE_IDENTITY()<br />
<br />
IF @ident >= {max value here} BEGIN<br />
    DBCC CHECKIDENT({table name here}, reseed, {new seed value here})<br />
END<br />
<br />

User 274316228-Mar-08 21:43

User 2743162

28-Mar-08 21:43

Thanks for your reply. I think the solution you provided should work for me.

Just to clarify my problem.
Identity column is primary key in table that represents movement orders being processed at some moment in automated warehouse. So the table changes very dynamicaly and is generaly empty after each shift (if not it is due to crane or some other device malfunction).
The requirement is that this movement order ID should have values between e.g. 1000000 and 1999999 so when identity reaches critical value should be reseeded.

If this additional information makes any difference to your solution please let me know.

Thanks again.

modified 19-Nov-18 21:01pm.

Mark J. Miller31-Mar-08 5:33

31-Mar-08 5:33

Just a warning that reseeding a primary key value can have some gotchas. As long as the table really is cleared out daily and there's no chance of the numbers being used w/in the same 24-48 hour period then you're probably fine. But if that primary key is being referenced elsewhere in other tables that don't get cleared out on the same schedules you're really in for some problems.

Any solution that depends on a specific constraint on identity keys is problematic. The value of the key should really be completely agnostic. It's purpose is for indexes and relations, not for business logic. That's not to say that you can't use an autonumber, but you may want to question why its being used and why it has to have this kind of constraint on it. And most importantly, if you are using the primary key value for these records elsewhere then you might want to use something else for the actual primary key value.

SQL query optimization i sql server 2005 [modified]

User 274316231-Mar-08 21:25

User 2743162

31-Mar-08 21:25

Thank you for your warnings. I'm not very experienced in designing databases so that kind of info is useful to me.

In this case identity key is not referenced in any other table so I think I'm on the safe side.

Thanks again.

modified 19-Nov-18 21:01pm.

ashok@techxygen27-Mar-08 19:27

ashok@techxygen

27-Mar-08 19:27

Hi all,

how to use query optimization in sql server 2005 for an efficient query. Smile | :)

Ash

modified on Friday, March 28, 2008 1:48 AM

Re: SQL query optimization i sql server 2005

John_Adams27-Mar-08 21:39

John_Adams

27-Mar-08 21:39

Hi Ash,

Here are some of the methods that most of the DBA's use to optimise the speed of MS SWL Server queries:

-Display the minimum number of fields in a query. Set criteria dependant fields that are not required in the dynaset to "not shown".

-Index all restriction based fields, all fields included in expressions, all sorted fields and all join fields.
-Use primary keys or unique indexes wherever possible.
-Use numeric rather than text primary keys.
-Use non blank unique fields.

-Avoid the use of IIf() function in queries.
-Avoid domain aggregate functions such as Dlookup().
-Make careful use of Between and Equal to, rather than > or < speeds up queries.
-Use fixed column headings in Crosstab queries.
-For reports based on queries use Portrait view in preference to Landscape and select Fast Laser
Printing to Yes (View,Options,Other Properties).

-Use Make table queries for running reports on static data. These are called snapshot reports.
-Use Count (*) rather than Count(Column).
-When creating restrictions on a joined column in one-to-many relationships, test out the comparative performance when placing the restriction on the "one" side or the "many" side. The "one" side is not always the fastest - the "many" may have markedly fewer records.

-Short table and field names run faster than long names.
-Normalise tables - join strategies execute more quickly on smaller tables.
-Denormalise tables - reduce the number of joins. Get the balance right between normalisation and denormalisation by experiment.

-Avoid the use of Distinct Row queries - Union queries do not need the distinct row feature as they are automatically return unique fields unless set to Union All.

You may visit the following link for more details about queyr optimization techniques.

http://blogs.msdn.com/QueryOptTeam/[^]

Hope this helps Smile | :)

Regards,
John Adams
ComponentOne LLC

Re: SQL query optimization i sql server 2005

Gandalf_TheWhite30-Dec-09 4:35

Gandalf_TheWhite

30-Dec-09 4:35

That was a good answer!! covered lots of things.
thanks for sharing

Believe Yourself™

I dont get it :-) Get Discounted price by porcentage

AlexeiXX327-Mar-08 18:32

AlexeiXX3

27-Mar-08 18:32

Hi thanks for looking

I have a weird situation here
im doing a case on a column, i need to get the final price
Price = 15.0000
Discount = 10

when '%' then dbo.vwProducts.Price * ((100 - dbo.Promos.Discount) / 100)
Shouldnt this give a result of 13.5?? (15 * ((100-10)/100))
It gives .0000

If i change the 100 for a 10 it works!! It gives a result of 135.0000
(15 * ((100-10)/10)) = 135.0000

Whats happening??

Alexei Rodriguez

Re: I dont get it :-) Get Discounted price by porcentage

Ashfield28-Mar-08 0:50

Ashfield

28-Mar-08 0:50

Check your data types. I just tried it with price and discoutn as decimal and it worked. I changed discount to int and got zero.

Hope this helps

Bob
Ashfield Consultants Ltd

Re: I dont get it :-) Get Discounted price by porcentage

Sgg24528-Mar-08 2:21

Sgg245

28-Mar-08 2:21

Hi,
You are doing division and multiplication through only int datatype only.
Do type-casting of one of the variables or value.

say (price * ((100-10) / 10.0))

Hope this shud work 4 U...
Smile | :)

Regards
SG

Re: I dont get it :-) Get Discounted price by porcentage

AlexeiXX328-Mar-08 9:11

AlexeiXX3

28-Mar-08 9:11

Datatypes were were the problem Smile | :)

Thanks a lot

Alexei Rodriguez

Weird Error Message in MS Access...Please help

new_phoenix27-Mar-08 7:37

new_phoenix

27-Mar-08 7:37

I am looping through a recordset, and I am both creating and destroying a QueryDef with what I believe to be a pass-through query. The records get processed correctly most of the time but then I get this abberant Error #3218 which states:

"DAO.QueryDefs. Could not update. Currently locked."

What does this mean and how do I prevent it from occurring? The application works correctly most of the time, but this error occurs intermittently, thus, it is the hardest cause to identify. Why is it locked up, and what is locked up?

Re: Weird Error Message in MS Access...Please help

Justin Porteous27-Mar-08 8:54

Justin Porteous

27-Mar-08 8:54

Error 3218 "Could not update; currently locked."
This error occurs when a user tries to save a record that is locked by another user.

To handle this error, program your solution to wait for a short period of time, and then try to save the record again. Or, you can display a message that explains the problem and give users the opportunity to try the operation again.

Databinding for SSRS CustomReportItem

Mark J. Miller27-Mar-08 7:13

27-Mar-08 7:13

I am trying tie a reporting services data source with a custom report item and can't seem to find any helpful samples/documentation anywhere. This task is taking much longer than it should have at this point and I could really use some help if there's anyone who has done this before.

What I want to do is create a series designer properties which allow me to :

1) select a data source from a list of available sources which have been defined for the report
2) select fields from the data source and assign them to designer properties
3) navigate the CustomReportItem.CustomData (is that the correct object?) object using the field names which were selected in step 2 (above) to retrieve the runtime values and bind them to my custom report item.

Re: Databinding for SSRS CustomReportItem

Mark J. Miller27-Mar-08 10:42

27-Mar-08 10:42

Well, I found the solution to the problem. The requirements for using the CustomData property are this:

A) In the CustomReportItemDesigner class (your custom class which inherits from it as a base), override the InitializeNewComponent() method like this:

<br />
public override void InitializeNewComponent()<br />
{<br />
  base.InitializeNewComponent();<br />
<br />
  CustomData = new CustomData();<br />
<br />
  //at least one grouping is required for both rows and columns<br />
<br />
  //initialize a static row grouping<br />
  CustomData.DataRowGroupings = new DataRowGroupings();<br />
  CustomData.DataRowGroupings.DataGroupings = new List<DataGrouping>(1);<br />
  CustomData.DataRowGroupings.DataGroupings.Add(new DataGrouping());<br />
  CustomData.DataRowGroupings.DataGroupings[0].Static = true;<br />
  <br />
  // initialize a Static column grouping<br />
  CustomData.DataColumnGroupings = new DataColumnGroupings();<br />
  CustomData.DataColumnGroupings.DataGroupings = new List<DataGrouping>(1);<br />
  CustomData.DataColumnGroupings.DataGroupings.Add(new DataGrouping());<br />
  CustomData.DataColumnGroupings.DataGroupings[0].Static = true;<br />
<br />
  //initialize data row with empty values for designer<br />
  CustomData.DataRows = new List<DataRow>(1);<br />
  CustomData.DataRows.Add(new DataRow());<br />
  CustomData.DataRows[0].Add(new DataCell());<br />
<br />
  //define the fields you will store (these will be populated with expressions by custom designer properties you define)<br />
  CustomData.DataRows[0][0].Add(new DataValue("Label", String.Empty));<br />
  CustomData.DataRows[0][0].Add(new DataValue("X", String.Empty));<br />
  CustomData.DataRows[0][0].Add(new DataValue("Y", String.Empty));<br />
  }<br />

B) Define custom designer properties to set the expressions for the DataValuesCollection (only showing one property here to keep things shorter)

<br />
[Browsable(true), Category("Data")]<br />
public string XValue<br />
{<br />
	get<br />
	{<br />
		if (CustomData.DataRows.Count > 0 && CustomData.DataRows[0].Count > 0)<br />
			return GetDataValue(CustomData.DataRows[0][0], "X");<br />
		else<br />
			return "X Coordinate";<br />
	}<br />
	set<br />
	{<br />
		SetDataValue(CustomData.DataRows[0][0], "X", value);<br />
	}<br />
}<br />
<br />
<br />
private string GetDataValue(DataCell cell, string name)<br />
{<br />
	foreach (DataValue value in cell)<br />
		if (value.Name == name)<br />
			return value.Value;<br />
	return null;<br />
}<br />
<br />
private void SetDataValue(DataCell cell, string name, string expression)<br />
{<br />
	foreach (DataValue value in cell)<br />
		if (value.Name == name)<br />
		{<br />
			value.Value = expression;<br />
			return;<br />
		}<br />
	DataValue datavalue = new DataValue(name, expression);<br />
	cell.Add(datavalue);<br />
}<br />

C) Make sure your designer provides a property to set the data set name (CustomData.DataSetName).

D) Your CustomReportItem class can then read the data like this:

<br />
private void ReadCustomData()<br />
{	<br />
	DataCellCollection rows = m_CRI.CustomData.DataCells;<br />
<br />
	//get the position of each field in the data value collection<br />
	int xpos = -1, ypos = -1, labelpos = -1;<br />
<br />
	for (int field = 0; field < rows[0, 0].DataValues.Count; field++)<br />
	{<br />
		switch (rows[0, 0].DataValues[field].Name)<br />
		{<br />
			case "X":<br />
				xpos = field;<br />
				break;<br />
			case "Y":<br />
				ypos = field;<br />
				break;<br />
			case "Label":<br />
				labelpos = field;<br />
				break;<br />
		}<br />
	}<br />
<br />
	//populate the series for the chart data<br />
	for (int row = 0; row < rows.RowCount; row++)<br />
	{<br />
		DataValueCollection values = rows[row, 0].DataValues;<br />
		<br />
		double x = Convert.ToDouble(values[xpos].Value);<br />
		double y = Convert.ToDouble(values[ypos].Value);<br />
		string label = (string)values[labelpos].Value;<br />
	}<br />
}<br />

NOTE: This assumes the data in your data set is already grouped and the report does not need to perform any grouping on the data in your dataset.

DYNAMICALLY Generating Reports in SSRS-2005

Aswanth26-Mar-08 23:12

Aswanth

26-Mar-08 23:12

I'm Working with Asp.Net with C#.. & I'm Generating Reports in SSRS-2005..

Till Now I'm Generating Reports in SSRS-2005 with Stored Procedure.. in Which I'm Generating Reports for One Particular User Details(ie I wrote Stored Procedure for Getting One Users Details)..
Now I Want to Generate the Reports for Different Users DYNAMICALLY (Here, I wrote another Stored Procedure which contains UsersID's as Input Parameters.. when we give the User ID then that Particular User's Details will be Executed).. Here I want to Place one Dropdownlist Control which contains All the User's ID's.. After selecting the Particular User ID, I Want to Generate that User's Details REPORT..
I done this as like that I generated Report for One User(ie as like First Report).. But it is Not Working..

Please give me the Suggestions for this...

Thank You..

Prefix 'sp' for stored procs

Brady Kelly26-Mar-08 22:47

Brady Kelly

26-Mar-08 22:47

A good while ago there was a lot of fuss made about not using this prefix. Is this still relevant?

Jammer27-Mar-08 0:02

Jammer

27-Mar-08 0:02

Hi,

Our head SQL guy insists on using the 'sp' as a prefix ... or at the very least maybe two characters prefixing the 'sp' prefix related to the project.

it just makes sense.

Jammer

Going where everyone here has gone before! Smile | :)

My Blog

Brady Kelly27-Mar-08 0:08

Brady Kelly

27-Mar-08 0:08

My motivation is somewhat unusual: I'm prefixing all application procedures with ap and all system procedures with sp, where application procedures access data that changes during normal tasks, and system procedures access data that only changes during system configuration.

pmarfleet27-Mar-08 0:10

pmarfleet

27-Mar-08 0:10

Jammer wrote:
it just makes sense.

Does it? If so, why?

Isn't Hungarian notation[^] a bit passe these days? In Management Studion, stored procedures are listed under their own section so you don't need a naming convention to distinguish them from other objects. Also, stored procedures have a different object type in the SQL Server metadata so you can find all instances of them without having to search on a particular prefix.

Naming stored procedures based on what they do, not what they are seems the most sensible approach.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Rob Philpott27-Mar-08 3:09

Rob Philpott

27-Mar-08 3:09

Agreed. I find hungarian notation just unnecessary ugly keystrokes. And I don't like putting all SQL statements in upper case, but I seem to be alone in my dislike of that.

Regards,
Rob Philpott.

Pete O'Hanlon27-Mar-08 3:16

Pete O'Hanlon

27-Mar-08 3:16

This limitation has nothing to do with Hungarian Notation, and everything to do with maximising SQL Server performance. See my post below.

Deja View - the feeling that you've seen this post before.

My blog | My articles

pmarfleet27-Mar-08 3:36

pmarfleet

27-Mar-08 3:36

This article relates to a problem caused by prefixing stored procedure names with 'sp_'. The OP was talking about the prefix 'sp', which presumably doesn't cause the same problem.

My issue is with the practice of using unnecessary naming conventions. Prefixing a stored procedure with 'sp' adds no value. You can tell by the context in which it is used that a database object is a stored procedure. What is useful is to choose a name which indicates the its basic purpose.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush