IDENTITY_INSERT

dear jeff johnson ,
thanks for ur response.
I have one table name fixtures in client machine .In that table one column
is identity type .now i am insert one Row from server database to that clien
t
database
now i have to on the identity_insert in client machine from server machine
give some suggestion.
sangeetha-server machine
sankar--client machine
i am running this query from my machine my machine name is sangeeta
EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
"set identity_insert scoremate.dbo.fixtures on "'
insert into openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select * from scoremate.dbo.fixtures')
select compcode,seasonid,matchid,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
openrowset('MSDASQL','DRIVER={SQL
Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
'select compcode,seasonid,matchid=15,matchdt,
time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
ground,umpires,genuser,
gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
scoremate.dbo.fixtures where matchid=13')Hi
Everything seems to point to the client machine?
You may be better off connecting to the server where you are doing the
inserts and not using your OPENROWSET as the destination of the insert.
John
"MOHAMED NASEER" wrote:

> dear jeff johnson ,
> thanks for ur response.
> I have one table name fixtures in client machine .In that table one colum
n
> is identity type .now i am insert one Row from server database to that cli
ent
> database
> now i have to on the identity_insert in client machine from server machine
> give some suggestion.
> sangeetha-server machine
> sankar--client machine
> i am running this query from my machine my machine name is sangeeta
> EXEC master.dbo.xp_cmdshell 'osql -U scoremate -P scoremate -S sankar -Q
> "set identity_insert scoremate.dbo.fixtures on "'
> insert into openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select * from scoremate.dbo.fixtures')
> select compcode,seasonid,matchid,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> openrowset('MSDASQL','DRIVER={SQL
> Server};SERVER=sankar;UID=scoremate;PWD=
scoremate',
> 'select compcode,seasonid,matchid=15,matchdt,
> time,round,roundtype,rounddesc,team1catg
,team1code,team2catg,team2code,
> ground,umpires,genuser,
> gendate,editdate,umpire1,thirdumpire,loc
ked,userid from
> scoremate.dbo.fixtures where matchid=13')sql

identity seed questions

My client has a need for the auto identity field to be 6 digits in length starting with the number 001000. They want the leading 0's preserved since this will be a casenumber. Even if I set the identity seed to 001000 it gets rid of the leading 0's. How can I get it to keep those?

Since the identity property works on numeric column leading zeroes doesn't matter. You can however use an identity column with seed 1000 and then use a computed column that formats the value in the desired format. See example below:

create table #t ( i int identity(1000, 1) not null check(i between 1000 and 999999), inum as right(replicate('0', 6) + cast(i as varchar), 6))

insert into #t default values

select * from #t

drop table #t

Identity Range Problem

Hi guys - A client just encountered a problem where he could not insert
into a table because of pk constraints. The database is a replica of a merge
publication with automatic identity range with settings:
Range at Pub : 1000
Range at Sub : 1000
Thresh.: 80
When I checked what the next identity was going to be, it returned '12'
but it should really be over 1000. As a matter of fact, it seems that all
tables seem to be using the Publisher's range.
Does anybody know what could have caused this?
What are the side-effects if I reseed the tables?
Thanks - Maer
You probably have a check constraint in place which is limiting the range of
values which can be inserted. Automatic identity range management had a
nasty habit of lingering after the subscription was dropped you might be
running into this - if so I would delete this constraint or adjust it.
I don't really understand what you mean by replica? Do you mean its a
subscriber, or you someone made a replica of the publication database
(through a backup perhaps).
You might also want to review this article -
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Maer" <maer@.auditleverage.com> wrote in message
news:%23VIoj$%23NGHA.420@.tk2msftngp13.phx.gbl...
> Hi guys - A client just encountered a problem where he could not
> insert into a table because of pk constraints. The database is a replica
> of a merge publication with automatic identity range with settings:
> Range at Pub : 1000
> Range at Sub : 1000
> Thresh.: 80
> When I checked what the next identity was going to be, it returned '12'
> but it should really be over 1000. As a matter of fact, it seems that all
> tables seem to be using the Publisher's range.
> Does anybody know what could have caused this?
> What are the side-effects if I reseed the tables?
> Thanks - Maer
>
|||Hi Hilary - Thanks for your response. I should have said subscriber
instead of replica.
It turned out that the client was still in SP 1 and I heard there were
some issues with identity ranges prior to SP 3. So we applied the latest SP
and dropped all subscriptions and re-subscribed. So far it seems to be
working.
It is good to know the issue with triggers so that this is one more
thing to check if that happens again.
Thanks - Maer
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uCviJ1BOGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You probably have a check constraint in place which is limiting the range
> of values which can be inserted. Automatic identity range management had a
> nasty habit of lingering after the subscription was dropped you might be
> running into this - if so I would delete this constraint or adjust it.
> I don't really understand what you mean by replica? Do you mean its a
> subscriber, or you someone made a replica of the publication database
> (through a backup perhaps).
> You might also want to review this article -
> http://www.simple-talk.com/2005/07/05/replication/
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Maer" <maer@.auditleverage.com> wrote in message
> news:%23VIoj$%23NGHA.420@.tk2msftngp13.phx.gbl...
>

identifying which license version installed

this is a stupid question but..I have a sql server installed, how can I tell
how it is licensed? Meaning per processor or per client? Thanks for your he
lpSELECT SERVERPROPERTY('LicenseType');
If it is per seat you can also see:
SELECT SERVERPROPERTY('NumLicenses');
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"jennifer" <jennifer@.discussions.microsoft.com> wrote in message
news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> this is a stupid question but..I have a sql server installed, how can I
> tell
> how it is licensed? Meaning per processor or per client? Thanks for your
> help
>|||"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
>
>|||Thanks so much just found it in control panel
now I know 2 ways.
Jennifer
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
>
>

identifying which license version installed

this is a stupid question but..I have a sql server installed, how can I tell
how it is licensed? Meaning per processor or per client? Thanks for your help
SELECT SERVERPROPERTY('LicenseType');
If it is per seat you can also see:
SELECT SERVERPROPERTY('NumLicenses');
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"jennifer" <jennifer@.discussions.microsoft.com> wrote in message
news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> this is a stupid question but..I have a sql server installed, how can I
> tell
> how it is licensed? Meaning per processor or per client? Thanks for your
> help
>
|||Thanks so much just found it in control panel
now I know 2 ways.
Jennifer
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
>
>

identifying which license version installed

this is a stupid question but..I have a sql server installed, how can I tell
how it is licensed? Meaning per processor or per client? Thanks for your helpSELECT SERVERPROPERTY('LicenseType');
If it is per seat you can also see:
SELECT SERVERPROPERTY('NumLicenses');
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"jennifer" <jennifer@.discussions.microsoft.com> wrote in message
news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> this is a stupid question but..I have a sql server installed, how can I
> tell
> how it is licensed? Meaning per processor or per client? Thanks for your
> help
>|||Thanks so much just found it in control panel
now I know 2 ways.
Jennifer
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> > this is a stupid question but..I have a sql server installed, how can I
> > tell
> > how it is licensed? Meaning per processor or per client? Thanks for your
> > help
> >
> >
>
>|||"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT SERVERPROPERTY('LicenseType');
> If it is per seat you can also see:
> SELECT SERVERPROPERTY('NumLicenses');
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
>
> "jennifer" <jennifer@.discussions.microsoft.com> wrote in message
> news:58E2895C-8CAB-4142-BCA3-CAC7C9C91C64@.microsoft.com...
> > this is a stupid question but..I have a sql server installed, how can I
> > tell
> > how it is licensed? Meaning per processor or per client? Thanks for your
> > help
> >
> >
>
>