Showing posts with label project. Show all posts
Showing posts with label project. Show all posts

Friday, March 30, 2012

IF NOT EXISTS problem

Hi,

I am migrating a project from SQL to SQL Compact Edition and the following statement keeps failing in the CE project:

Code Snippet

IF NOT EXISTS (SELECT * FROM Court2 WHERE BookingDate = '2007-05-28') INSERT INTO Court2 (BookingDate,T1100) VALUES ('2007-05-28',52) ELSE UPDATE Court2 SET T1100 = 52 WHERE (BookingDate = '2007-05-28')

It works fine in SQL.

I've done a fair bit of searching for the solution and it appears my syntax is not perfect but I can't see where.

Any suggestions.

Here's the error that CE produces:

Code Snippet

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

Thanks,

Glen.

Hi Glen,

IF.. ELSE is not available in SQL CE, as only a subset of the full T-SQL grammar is supported by SQL CE,

so you will have to do this in code, like:

Get a DataReader with

Code Snippet

SELECT * FROM Court2 WHERE BookingDate = '2007-05-28'

and perform your insert or update depending on the result of this.

For documentation on SQL CE SQL syntax see: http://msdn2.microsoft.com/en-us/library/ms173372.aspx

|||

Thank you.

I'm now working on the next issue:

Unfortunately I'm only a hacker and learning the hard way .. Is there somewhere that I can go that lists the differences and workarounds for the CE that I can read.

Cheers.

|||See SQL CE Compact Edition BOL, - for documentation on SQL CE SQL syntax see: http://msdn2.microsoft.com/en-us/library/ms173372.aspx

Wednesday, March 21, 2012

IDENTITY_INSERT On/Off in sql compact

Hi,

Im working in a project that uses sql server compact. While migrating some data from another database I needed to "Turn off" the identity insert :

SET IDENTITY_INSERT <tablename> OFF

and set it back to "ON" after the migration was completed. Unfortunately SQL Server Compact is giving me the following error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 5,Token in error = @.@.IDENTITY_INSERT ]

Im i doing something wrong or this instruction is not supported by the Compact edition.

Does anybody knows another way to do this?

Thank you

Alexander75

The IDENTITY_INSERT concept is not supported by SQL Compact Edition. Your best workaround for this is to use the ALTER TABLE / ALTER COLUMN IDENTITY to set the appropriate identity value for each row you want to insert. Not nice? You bet! But it's the only documented solution. I have it working on my database tranfer products so you can trust me it works.|||ok. Thanks|||You wouldn't happen to have a code sample for this, would you?

Thanks in advance...|||My samples use native code, but you can use the .NET CF classes for this. The critical part is to determine the IDENTITY properties before altering the table and this can be done by querying the INFORMATION_SCHEMA (see the BOL).

Identity_Insert is set to OFF

Hi,
I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
to Duplicate a record from a form and the linked records in its subform.
Parent form duplicate goes to table "Jobs" where JobID is the key, records
from subform with (Link JobID) go to table "Samples". I've created an update
query in order to do this and I'm getting the following error-message:
Cannot insert explicit value for identity column in table "Jobs" when
IDENTITY_INSERT is set to OFF
Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
other way to duplicate these records?
Thanks in advance
gaba
Sorry,
I've meant "append query" not update
gaba
"gaba" wrote:

> Hi,
> I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
> to Duplicate a record from a form and the linked records in its subform.
> Parent form duplicate goes to table "Jobs" where JobID is the key, records
> from subform with (Link JobID) go to table "Samples". I've created an update
> query in order to do this and I'm getting the following error-message:
> Cannot insert explicit value for identity column in table "Jobs" when
> IDENTITY_INSERT is set to OFF
> Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
> other way to duplicate these records?
> Thanks in advance
>
> --
> gaba
|||gaba wrote:
> Hi,
> I'm using SQL Server MSDE RelA (BackEnd) Access Project (FrontEnd). I need
> to Duplicate a record from a form and the linked records in its subform.
> Parent form duplicate goes to table "Jobs" where JobID is the key, records
> from subform with (Link JobID) go to table "Samples". I've created an update
> query in order to do this and I'm getting the following error-message:
> Cannot insert explicit value for identity column in table "Jobs" when
> IDENTITY_INSERT is set to OFF
> Do I need SP4 Service Pack SP4 or am I doing something wrong? Is there any
> other way to duplicate these records?
>
Hi gaba,
The clue is in the question :-)
In order to insert data into a table which has an identity column, if
you want to insert a value instead of accepting the default, do the
following:
SET IDENTITY_INSERT <table name> ON
then doing your inserts, and then
SET IDENTITY_INSERT <table name> OFF
Rather irritatingly, you can only set this option against one table at
a time.
|||Thanks Damien. That was it. So simple...
gaba
"Damien" wrote:

> gaba wrote:
> Hi gaba,
> The clue is in the question :-)
> In order to insert data into a table which has an identity column, if
> you want to insert a value instead of accepting the default, do the
> following:
> SET IDENTITY_INSERT <table name> ON
> then doing your inserts, and then
> SET IDENTITY_INSERT <table name> OFF
> Rather irritatingly, you can only set this option against one table at
> a time.
>

Friday, March 9, 2012

Identity fields - Why use them

I am building a database for a new project. I am not a DBA or a database designer so please bear with me on this question.

My boss believes that the only time to use an identity column is when we cannot determine a unique primary key. On tables where we can determine a unique primary key identify fields are a waste of reasources. For instance, one of the tables that I need to create is a customer table. Since all of our customers have unique customer numbers my boss believes that in this case an identity column is useless. I don't have enough experience to determine if he is correct or not.

Can someone please explain the pro's and con's of using identity fields to me.

Thanks

Check if any of these help:http://www.sqlteam.com/item.asp?ItemID=2599

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5113

|||

NewToDotNet:

My boss believes that the only time to use an identity column is when we cannot determine a unique primary key. On tables where we can determine a unique primary key identify fields are a waste of reasources. For instance, one of the tables that I need to create is a customer table. Since all of our customers have unique customer numbers my boss believes that in this case an identity column is useless.

I agree with your boss, but I know some people would disagree. It's like religion or politics, there's no point arguing about it.

You need some kind of unique reference for each record, or else you say update that record right there, and accidentally update another thousand that have the same surname and forename.

So if I just had a person's name, I might have several different John Smiths, and so I would want to generate an identity. If I had a real-world unique reference, like a social security number, and could rely on each person to supply it accurately, I would not create another "fictitious" Id.

Unless I was collecting names at a fast food restaurant, where I could not rely on each customer accurately supplying a social security number, so I would have to generate something.

Or unless I had to handle foreign tourists, who would not have a social security number.

Or unless maybe I had databases in different branches, and there was the chance that Joe Public with social security number xxx had done business with more than one branch.

So you have to stop and think how confident you are about the reliability of your "real world" reference, but if you have something dependable, use it.

|||

The issue here is whether one should use a "dataless" key or not. A dataless key is a key that has no intrinsic meaning, it's just a way to uniquely identify a row. Many data modelers believe that dataless keys should be used in all cases because they solve the problem of what happens when your keys domain changs (eg, you discover after 5 years that the key field you thought was big enough isn't and you have to make your item number a 12 character number instead of an 8 character field). This happens more than you think. Here's a nice article describing the issue:http://blogs.ittoolbox.com/eai/implementation/archives/physical-db-design-key-fields-15666. There's a lot of literature on this topic in the database/data modeling world.

OTOH, programmers hate dataless keys for obvious reasons. They are unintuitive, don't lead you "naturally" to join conditions, and make more work up front.

Personally, I hate dataless keys but concede that they are probably a better way to go.

I would bring the issue of dataless keys up with your boss to show him that you know more than he does but in the end go with what he wants -- 'cause he writes your review.

|||

I want to thank everyone who responded to my question. Based on the posts and articles that were presented I see that there are many opposing opionions on this subject, so I can't really say my boss is wrong. And as dbland07666 says, my boss does my review.

Thanks

|||

Your boss may be correct.

If you can find a natural primary key that is reasonable in size, is precise, guarantees uniqueness, will never change, and can never change, then using that as a primary key is ok.

Unfortunately, finding a primary key like that is harder than you may think. If you can't find one that has all the above attributes, then you may still be able to use it as a primary key with some drawbacks. Whether the drawbacks are worth it is a matter of opinion, and as you said, the only real opinion that counts is the one that writes your review.

|||

Thanks for the additional input. I appreciate it.

Identity crisis: GUID VS. Range

Hi!
I'm developing a project in which I have several Sql Server Express and
Sql Server Mobile 2005 databases and all of them are subscribed to a
central Sql Server 2005 database.
Now, I'm in trouble with the primary keys because if i insert two
registers with the same primary key in two different subscribers, when
the record is merged to the publisher, there is a primary key
collision.
I'm studying two different solutions:
A) Assign ranges of primary keys in each subscriber in order to assign
different IDs in each subscriber
B) User GUID as primary keys, so each primary key will be diferent.
Which are the pros and cons of each solution? Do you think one is best
than the other? Will I have performance problems using GUIDs in a Sql
Server Mobile database?
Thaks in advance!
Jordi
Guid's don't perform as well as int identity. If you use the
newsequentialid() property you will get sequential guids but although
fragmentation is reduced you still don't get the performance of a int data
type.
Your choices are to extend your primary key to include a location identifier
or implement automatic identity range management.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<jordics@.gmail.com> wrote in message
news:1166626358.727257.205170@.t46g2000cwa.googlegr oups.com...
> Hi!
> I'm developing a project in which I have several Sql Server Express and
> Sql Server Mobile 2005 databases and all of them are subscribed to a
> central Sql Server 2005 database.
> Now, I'm in trouble with the primary keys because if i insert two
> registers with the same primary key in two different subscribers, when
> the record is merged to the publisher, there is a primary key
> collision.
> I'm studying two different solutions:
> A) Assign ranges of primary keys in each subscriber in order to assign
> different IDs in each subscriber
> B) User GUID as primary keys, so each primary key will be diferent.
> Which are the pros and cons of each solution? Do you think one is best
> than the other? Will I have performance problems using GUIDs in a Sql
> Server Mobile database?
> Thaks in advance!
>
> Jordi
>

Sunday, February 19, 2012

Identifying the Logs

Hi All,
We almost have 30 developers in our project and we do have
admin user rights for a username and that is being given to all the the
users,
My issue is somebody deletes one object [ tables, SP]
How will i track it down [ who has deleted it], is there anything that
i will be able to find in the logs.
thanks in advance
thomsonHi,
From logs u can identify all the activities that happend to the database. U
can use the third party tool like 'Lumigent log explorer', which i think is
the best tool for dealing with the logs.
Regards Lara
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1129615680.263276.53610@.o13g2000cwo.googlegroups.com...
> Hi All,
> We almost have 30 developers in our project and we do have
> admin user rights for a username and that is being given to all the the
> users,
> My issue is somebody deletes one object [ tables, SP]
> How will i track it down [ who has deleted it], is there anything that
> i will be able to find in the logs.
>
> thanks in advance
> thomson
>|||Hi,
Thanks for the information Do we have any open source tool to
manipulate these things.
Regards
thomson
Aneesh R wrote:
> Hi,
> From logs u can identify all the activities that happend to the database.
U
> can use the third party tool like 'Lumigent log explorer', which i think i
s
> the best tool for dealing with the logs.
> Regards Lara
>
> "thomson" <saintthomson@.yahoo.com> wrote in message
> news:1129615680.263276.53610@.o13g2000cwo.googlegroups.com...|||If all 30 people use that single user-name that has admin privs, then you
will not know who did what. The best thing is to restrict the permissions
and hand out accounts carefully.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1129615680.263276.53610@.o13g2000cwo.googlegroups.com...
> Hi All,
> We almost have 30 developers in our project and we do have
> admin user rights for a username and that is being given to all the the
> users,
> My issue is somebody deletes one object [ tables, SP]
> How will i track it down [ who has deleted it], is there anything that
> i will be able to find in the logs.
>
> thanks in advance
> thomson
>|||Hi
No, only expensive commercial tools.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1129618925.118960.27150@.g49g2000cwa.googlegroups.com...
> Hi,
> Thanks for the information Do we have any open source tool to
> manipulate these things.
>
> Regards
> thomson
>
> Aneesh R wrote:
>