|
If you're dealing with Microsoft Access, then the range for the autonumber is between
-2,147,483,648 and 2,147,483,647
So, it's highly unlikely you'll exhaust the autonumber field. One calculation I saw elsewhere, that I verified myself, shows that even if you added a record every second of every day of every year, it would take a little over 68 years to go from 1 to 2,147,483,647 in the autonumber field.
If you do manage to exhaust the field (due to some program error that starts adding fields uncontrollably), it probably will wrap around and start at -2,147,483,648.
I hope this helps.
|
|
|
|
|
Hi evrybuddy
my boss says that TinyInt is better in performance that Bit when u need to use a boolean field. well I dont agree to that,
I've been trying to get some info on this over google but couldnt find any . I dont have the SQL2005 books online at the moment. Can any one plz comment on that. Please give some technical comment on this.
Many thanks!
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
Rocky# wrote: dont have the SQL2005 books online at the moment.
The clue's in the name - SQL Books Online. If you can post a message on CodeProject, you can get to SQL Books Online.
Anyway, your boss sounds like an idiot. Bit fields have a specific purpose - to store true/false values. The Tinyintt data type stores values between 0 and 255. If you were to use tinyint instead of bit, you would have to add constraints to ensure data entry was constrained to 0 and 1.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Agreed.
Don't try and second-guess your subsystems. If you are storing a boolean value then use a boolean data type.
If for some strange reason, the tinyint type is faster (I strongly doubt this - its probably fairytales from the "stored procs are faster" crowd) it will be so marginal it's nothing to worry about. Also you won't be guaranteed that you have picked the best data type for the job if MS changes anything in SQL server.
Seriously. Don't second-guess your subsystems. Don't second-guess the compiler. In both cases the people that wrote them are a hell of a lot smarter and better engineers than your boss. 
|
|
|
|
|
Mark Churchill wrote: Don't try and second-guess your subsystems. If you are storing a boolean value then use a boolean data type.
Mark Churchill wrote: the people that wrote them are a hell of a lot smarter and better engineers than your boss.
yea I agree with you 100%
Thanks!
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
pmarfleet wrote: Anyway, your boss sounds like an idiot
ahhh I was thinking abt showing this conversation to my boss... 
pmarfleet wrote: If you were to use tinyint instead of bit, you would have to add constraints to ensure data entry was constrained to 0 and 1.
that's exactly what I was thinking and offcourse it can't imporve efficiency if you have constraints on...
Many thanks for ur response. Cheers...
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
The server will have to do a tiny, tiny bit more work to isolate the bit value from the byte it's stored in, but this is just a mask and shift which the processor will have no trouble with, a couple of cycles probably at most. The time taken to do this will be absolutely dwarfed by the time necessary to pull the data from main memory into the processor's cache.
Bit columns have the disadvantage that they can't be included in an index, but generally they are not very selective (having only two values) and would make poor indexes anyway; even if you could build it, the query optimizer would probably not use it.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
I have some software that relies on an MS Access database, both to supply the parameters for various tests and to store the results of those tests. This software is rapidly undergoing revision, however, and each new revision often has a change in the defintion of the database that is supplied with the software.
For current customers, I want to be able to supply the update without losing any of their existing data. Typically, I do not delete fields from tables, or tables from the database (nor do I change names of fields or tables), so all changes are additions, necessary additions as the scope of the program changes.
Now, I can "hard code" in the creation of tables or addition of fields to each new revision, but I'd rather have some automated method of creating tables and adding fields, as needed. Right now, I can detect and respond to a new version of the software, but I'm seeking the most efficient response to the version change. Here is my idea, along with some questions.
1) Get number of tables in newer version of database and compare to previous version-add tables as needed
Is there a SQL command that will return the data table count?
2) Scan through each table to detect if there are any changes in the table definition for each table...add fields, as needed
Any suggestions would be appreciated.
|
|
|
|
|
Hi
Please provide a solution for this issue 
My Report consists of a matrix and table . I Kept the matrix inside the table because i need to calculate subgroups total as well as Grand Total using the group i provided inside the table . The issue when i tried to render the report to Excel it shows "Data Regions within table/matrix cells are ignored." I dont know how to solve it . When i google it . It says that its a Microsoft SSRS limitation . Anyway i could solve it any help will be appreciated
|
|
|
|
|
i m also getting same error, any idea? 
|
|
|
|
|
|
SreejithAchutan wrote: return dbtObj.GetColumnValue(sqlstatement, "UnitId").ToString();
SreejithAchutan wrote: if the user login had no unitid thn exception in sql is seen.How i overcome it
Look at your logic. What happens if GetColumnValue returns null? What does ToString() have to operate on?
You have to handle the case that GetColumnValue() may return null and not attempt to perform a ToString() operation on it.
Also your SQL prone to attack. You have not defended yourself against SQL Injection Attacks. Please read SQL Injection Attacks and Tips on How to Prevent Them[^]
SreejithAchutan wrote: I want it urgently.
Finally, this irritates people. Do not say that you want an answer urgently. I don't care if it is true, it makes you look selfish that you demand attention before others. If it really is urgent then you will be willing to pay for support and since this is a free volunteer service you will get help when people have time to help you.
|
|
|
|
|
ToString() is to convert the retrieved column into string...In getcolumn value null condition is checking but an exception of object reference is not set is returned.How i overcome it...
|
|
|
|
|
First, don't delete your original question - That is considered extremely rude.
SreejithAchutan wrote: ToString() is to convert the retrieved column into string...In getcolumn value null condition is checking but an exception of object reference is not set is returned.How i overcome it...
I know what ToString() does! Or rather I know what you think it does.
You may check in GetColumn() if the column is null - but what do you do with it then? Do you just return null back? In which case, what does ToString() have to work with?
How do you overcome it? Perhaps you need to check for null values in more than one place? Or you need to do something different when you find a null value the first time. As you have not posted the code for GetColumn() I can't tell you what to do. I can only suggest things for you to look at with the hope that you can figure it out for yourself.
|
|
|
|
|
if execute the following query
Select * from table1 where table1id in('100,101,102')
getting error....
Conversion failed when converting the varchar value '100,101,102' to data type int.
anybody tell me solution for this..
thanks
Ramana
|
|
|
|
|
Ramana. G.V wrote: table1id in('100,101,102')
why to enclose search expression in quotations
hope table1id column in the table is numeric
remove quotations and use
Select * from table1 where table1id in(100, 101, 102)
Regards
KP
|
|
|
|
|
if run like this...
declare @x varchar(100)
set @x = '100,101,102'
Select * from table1 where table1idin(@x)
getting that error.
how can i cast to int?
Ramana
|
|
|
|
|
Why bother using the @x variable at all?
If you are doing this by trying to pass the string as a parameter to the stored procedure, i'm afraid this won't work in quite this way.
|
|
|
|
|
then, to be used like this
<br />
declare @x varchar(100), @y varchar(100)<br />
set @x = '100,101,102'<br />
set @y = 'Select * from a where c1 in (' + @x + ')'<br />
exec (@y)<br />
Regards
KP
|
|
|
|
|
Hi friends
I am facing a problem while accessing database sqlserver 2005 from command prompt.
Here i am giving my code. I don't have any compilation errors,
But runtime errors as follows....
Unhandled Exception: System.Data.SqlClient.SqlException: An error has occurred w
hile establishing a connection to the server. When connecting to SQL Server 200
5, this failure may be caused by the fact that under the default settings SQL Se
rver does not allow remote connections. (provider: Named Pipes Provider, error:
40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
my code is :
using System;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Demonstrates how to work with SqlConnection objects
/// </summary>
class SqlConnectionDemo
{
static void Main()
{
// 1. Instantiate the connection
string strConnection = "server=localhost; Trusted_Connection=yes; timeout=120";
SqlConnection Conn = new SqlConnection(strConnection);
SqlDataReader rdr = null;
try
{
// 2. Open the connection
if (Conn.State == ConnectionState.Closed)
{
Conn.Open();
}
// 3. Pass the connection to a command object
SqlCommand cmd = new SqlCommand("select * from Customers", Conn);
// 4. Use the connection
// get query results
rdr = cmd.ExecuteReader();
// print the CustomerID of each record
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// 5. Close the connection
if (Conn != null)
{
Conn.Close();
}
}
}
}
Note: I am executing my program at cmd prompt.
Help please...........
|
|
|
|
|
mohanallam wrote: string strConnection = "server=localhost; Trusted_Connection=yes; timeout=120";
You haven't specified the database you want to connect to in your connection string.
Visit ConnectionStrings.com[^] to see how to include this information in your connection string.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
you should specify the database ,user ,password in you contention string
my english is very bad!
|
|
|
|
|
Hello,
I am trying to find as to what would cause a sql-agent to roll-back/fail a job, where-as same job (aka stored-proc) when executed via QA, runs ok.
Here are more details:
I have a stored-proc (SP) that gets executed via a sql-agent. There is a primary-key error that it runs into and hence fails, with appropriate primary-key constraint error-message.
Now, when i execute this same SP, via QA (exec sp-name), it also generates the primary-key related message, but it does populate the db tables.
Platform:
- Windows 2003 server.
- SQL Server 2000
Any clues/direction/suggestions?
Thanks.
|
|
|
|
|
Can you post both error messages (from the job and QA one)?
|
|
|
|
|
I have a MS Visual Studio 2005 C# project within which I've created and SQL Server database (my first one I've ever done). The database resides on my hard drive. OS is Windows XP.
Any records I add, edit, or delete database records within Server Explorer's Data Connections update accordingly. Any records I add, edit, or delete programmatically, the changes to the records are there while I query or debug as the program is running. However, when I close down my program, the database returns to its original state, as if I didn't do any adding, updating, or deleting.
I've tried using and not using System.Data.SqlClient.SqlDataAdapter, System.Data.SqlClient.SqlCommand (with and without using Parameters.Add), and System.Data.DataSet. I've also tried using IDbCommand using Transaction with Commit or Rollback.
Any ideas why my database is not updating?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
