|
Hi - I've tried, got the following error after removing GO after DECLARE CURSOR statement:
Message: ERROR [HY000] [MERANT][ODBC Sybase driver][SQL Server]DECLARE CURSOR must be the only statement in a query batch.
If I *need* cursor, what are my options? Can we have GO in stored proc? (wrap sql statement by stored proc?)
devy
|
|
|
|
|
Hi every body!
I have a data base to store a large number of records and I want an Unique ID for each of them, so I used an UniqueIdentifier field, but the problem is when I delete a record, as you know, the identifier is not reordered automatically, so what should I do?
by the way, as I told I have many records to save. How can I prevent data from overflowing?
thanks for any help.
|
|
|
|
|
Is it necessary for the IDs to be contiguous? They'll still be unique, even if a record is deleted.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
infact no!
but because there are many deletion operations in data base and there are a large number of records, so the ID value grows too fast to the maximum value it can accept.
|
|
|
|
|
You probably shouldn't be using an IDENTITY column then.
Have a look at this article[^] about created custom auto-generated sequences.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi
I wrote one stored procedure it’s returning two parameters. How to get the two output parameters retuned by SP .
|
|
|
|
|
Do a Google search for the OUTPUT keyword.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
If using a .NET language, set the parameter object's Direction property to ParameterDirection.Output (or, if you're passing values in through this parameter as well, ParameterDirection.InputOutput). The parameter will then be set to this value once all resultsets have been read to the end.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Hi,
I have a database correupted when altering a field in db.
while it was altering i've killed the enterprise manager, then database returned to suspect mode we turned the db to emergency mode then we execute,
dBCC CHECKALLOC (yourDBname, REPAIR_ALLOW_DATA_LOSS)
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER
we get error that couldn't fix some corrupted pages
Anyone can help me, please
Thanks
Ahmet GULBAY
|
|
|
|
|
i have been trying to find out what is the difference between INNER JOIN and WHERE CLAUSE.
Any one with help please
Back off i am coding
|
|
|
|
|
Joins - clickety
Where - clickety
You always pass failure on the way to success.
|
|
|
|
|
No real difference. You can express your inner joins in the WHERE clause if you wish (I believe in the early versions of SQL you had to), but your code will be easier to understand if you separate join conditions from the other conditions.
|
|
|
|
|
Is there anything special I should consider in a security model for an ASP.NET web site with a dedicated database, possibly on a shared server? I want to use Windows Authentication, but not the individual users.
|
|
|
|
|
1) Setup a Windows Group and grant that group login priviledges to your SQL Server instance.
2) Configure SQL Server to grant that group the needed priviledges for your database.
3) Create a Windows login and make that user a member of the windows group in step #1
4) Configure your IIS AppPool identity to be the Windows login you created in step #3
If you are working in a windows domain the group and user should be created at the domain level so that you can eventually separate the web server and database server.
|
|
|
|
|
I have an identity column of type int in my table and I need to reseed it when it reaches certain value.
I'm thinking of a trigger on insert that would check last generated identity value and reseed it if needed.
At the moment I believe I should use SCOPE_IDENTITY() and DBCC CHECKIDENT (table, reseed, initial_value) in CASE statement. If this is the case I need some help in putting things together otherwise I need a better solution.
Any help will be very appreciated.
Thanks in advance.
modified 19-Nov-18 21:01pm.
|
|
|
|
|
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 />
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
Hi all,
how to use query optimization in sql server 2005 for an efficient query. 
Ash
modified on Friday, March 28, 2008 1:48 AM
|
|
|
|
|
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 .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
That was a good answer!! covered lots of things.
thanks for sharing
Believe Yourself™
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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...
Regards
SG
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
