MS SQL Server Indentity Jumps

Hi All

I have a table in SQL Server with ID having indentity inrement by one.
Table has not any trigger. Frequently ID in the table jumps.

Any help !!!

ThanksHi

If the transaction fails and rolls back the identity value will not be
reused and can therefore jump. Identities are not guaranteed to be
contiguous.

There is an example in Books online on how to fill non-contiguous identity
values.

John

"Jashan" <Jashan101@.hotmail.com> wrote in message
news:db88da90.0310131559.6d511798@.posting.google.c om...
> Hi All
> I have a table in SQL Server with ID having indentity inrement by one.
> Table has not any trigger. Frequently ID in the table jumps.
> Any help !!!
> Thanks|||Jashan101@.hotmail.com (Jashan) wrote in message news:<db88da90.0310131559.6d511798@.posting.google.com>...
> Hi All
> I have a table in SQL Server with ID having indentity inrement by one.
> Table has not any trigger. Frequently ID in the table jumps.
> Any help !!!
> Thanks

IDENTITY columns are not guaranteed to maintain a sequence of numbers
with no gaps. The new identity value is assigned before the INSERT is
executed, but if the INSERT fails or is rolled back in a transaction,
then the value will not be reused. Books Online also says that the
value may jump if there are a lot of deletions happening on the table
- see the "IDENTITY (property)" topic.

Another possibility is that someone simply inserted the values you see
with IDENTITY_INSERT, perhaps as part of a bulk load operation.

If you need to ensure that there are no gaps in the values in your
column, then you'll need to code your own solution. You could search
Google for "mssql and sequences" to get some ideas.

Simon

MS SQL Server 2000 / MS Access - ODBC connection question

Hello all!

I have such question to all of you.
I have some tables linked from MS SQL Server 2000. Is time of processing
query
based on these linked tables from MS SQL Server 2000, faster or slower than
the time of processing the same query based on tables, which are not linked
but
imported to MS Access?

Can you answer this question?

Thank you in advance for posts
Marcin from Poland.No idea - it depends how big the tables are, what your queries look
like, how you execute your queries etc. If you're using stored
procedures, for example, then I would expect MSSQL to be faster, as all
the processing happens on the server, and it can use indexes, caching
etc. The best way to get an answer is simply to test it yourself.

Simon

ms sql mdf database file attached vs created on sql server

Hi all

I have a question concerning sql database mdf files. In the old days I would user a ms access database. This file would be stored with the actual web files and would utilise a dsn connection.

I have noted when designing with vwd 2005 express it allows you to use 2 methods of creating a mdf database. You can either create it as an attachment mdf or you can create it directly using sql manager.

My question is, if you create the mdf database as an attachement file can you store it in the same manner as if you where using a ms access database, meaning can you store it with the web site's files so it uses the file storage allocated size and then create a connection similar to a dsn (but for sql) to the isp's sql engine or does it have to be uploaded to the isp' s sql server.

The reason for this question is some of my customers do not want to pay the extra cost to have an sql allocation, however I do not want to go back to using old asp methods to create advanced sites as I prefer using stored procedures.

Any help will be appreciated

Hi,

you can attach your .mdf file (SQL Server Express) to a database (Attaching a .mdf file when you don't have the .ldf file available). However I think the hosting company will charge your customers for attaching it. To my knowledge you don't have a replacement available like DSN.

Grz, Kris.

|||

Hi there,

My first website I did using Visual Studio I used an sql express database. Most hosting companys only use SQL server so you'll have to do some changes to your express database to get it hosted. So it doesnt really matter which way you design your database.

The hosting company will usually place your database on a separate secure server where you'll be able to access through SQL Server Manager once you have a static IP address. I'm not sure which hosting company you use but I live in Ireland and the best company here for windows hosting iswww.blacknight.ie

I use the standard windows hosting which allows you have up to 16 websites and 16 SQL server databases. An excellent company.

I recommend if you are going to use this a lot, upgrade your package to Visual Studio pro and this includes SQL Server Developer edition which allows you to create a SQL Server 2005 database which makes it easier when you go to publish your application.

Hope this is some use to you.

Anthony

|||

Hi Kris

So what you are saying is you can attach a database to an sql server without having to import it directly. Which means the database can run externally from the sql server. My hosting company I think offers it for free if you are attaching and they have a control panel which makes it user friendly to do this by yourself. I will contact them again to verify. www.sahost.co.za

|||

Hi,

about 1,5 years ago I tried out a free hosting for ASP.NET 2.0 where you could upload a .mdf file to and in their admin pages they provided such an attach procedure. Unfortunately I don't remember their name.

Grz, Kris.

Ms Sql Blob ?

Hi to all

I'm starting ussing Microsoft SQL2000, and i need help about how to insert any type of data file( .xls,.pdf, .jpg, .txt, ...) Into a column to extract later the files on web.

I heared about data tipe BLOB, but i can't find the way to work with it...

I'm lost... some body can help me?

Thanks.I struggled for a long time to try to store images in the SQL 2000 database. After long hours and many frustrations, I decided to leave these kinds of files in the capable hands of the file system. Once I did that, life got a lot easier.

Ling

MS SQL 2000 TCPIP Port Number and SQL Replication

Hi all

I'm struggling to find an answer to this one!

We've configured our SQL boxes to run under a different port number,
e.g. from 1433 to 4533. This works fine under our Citrix enviornmnet
where the dsn is configured to use the new port. The problem comes in
terms of SQL Server management. For example, when you try and
configure SQL Replication, it is not possible to add the subscriber
unless the SQL Servce runs under port 1433, the default port. The
same goes when you try to add the server as a registered server within
the Enterprise Manager MMC.

Does anyone know how you can configure SQL Server to run under a
different port number, without naffecting SQL REPLICATION, etc?

Many thnaks
Nik
nik@.nastek.co.uknik@.nastek.co.uk wrote in message news:<a30v10lk653eu3j878hfj0tpflub252qrl@.4ax.com>...
> Hi all
> I'm struggling to find an answer to this one!
> We've configured our SQL boxes to run under a different port number,
> e.g. from 1433 to 4533. This works fine under our Citrix enviornmnet
> where the dsn is configured to use the new port. The problem comes in
> terms of SQL Server management. For example, when you try and
> configure SQL Replication, it is not possible to add the subscriber
> unless the SQL Servce runs under port 1433, the default port. The
> same goes when you try to add the server as a registered server within
> the Enterprise Manager MMC.
> Does anyone know how you can configure SQL Server to run under a
> different port number, without naffecting SQL REPLICATION, etc?
> Many thnaks
> Nik
> nik@.nastek.co.uk

I haven't tried this myself, but this KB article seems to suggest that
creating a server alias (in Client Network Utility) with the correct
port number should work:

http://support.microsoft.com/defaul...2&Product=sql2k

Simon|||sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0402030644.62669b66@.posting.google.com>...
> nik@.nastek.co.uk wrote in message news:<a30v10lk653eu3j878hfj0tpflub252qrl@.4ax.com>...
> > Hi all
> > I'm struggling to find an answer to this one!
> > We've configured our SQL boxes to run under a different port number,
> > e.g. from 1433 to 4533. This works fine under our Citrix enviornmnet
> > where the dsn is configured to use the new port. The problem comes in
> > terms of SQL Server management. For example, when you try and
> > configure SQL Replication, it is not possible to add the subscriber
> > unless the SQL Servce runs under port 1433, the default port. The
> > same goes when you try to add the server as a registered server within
> > the Enterprise Manager MMC.
> > Does anyone know how you can configure SQL Server to run under a
> > different port number, without naffecting SQL REPLICATION, etc?
> > Many thnaks
> > Nik
> > nik@.nastek.co.uk
> I haven't tried this myself, but this KB article seems to suggest that
> creating a server alias (in Client Network Utility) with the correct
> port number should work:
> http://support.microsoft.com/defaul...2&Product=sql2k
> Simon

We have some servers with non-standard ports. We access them using the
Client Network Utility with the correct port and have no problems with
MMC, ISQLW or OSQLW or even ODBC.