If I log out will SQL Server still run?

Runnning 2005. I want to log out (not reboot) of Windows Server 2003, log
in as another user, and then log out and back in as the original user. Will
doing this cause the SQL Server to stop running (taking requests from
applications)?No, assuming it is running as a service.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"HB" <replywithingroup@.notreal.com> wrote in message
news:Lws7g.6122$yM.3355@.tornado.socal.rr.com...
> Runnning 2005. I want to log out (not reboot) of Windows Server 2003, log
> in as another user, and then log out and back in as the original user.
> Will doing this cause the SQL Server to stop running (taking requests from
> applications)?
>

If I log out will SQL Server still run?

Runnning 2005. I want to log out (not reboot) of Windows Server 2003, log
in as another user, and then log out and back in as the original user. Will
doing this cause the SQL Server to stop running (taking requests from
applications)?No, assuming it is running as a service.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"HB" <replywithingroup@.notreal.com> wrote in message
news:Lws7g.6122$yM.3355@.tornado.socal.rr.com...
> Runnning 2005. I want to log out (not reboot) of Windows Server 2003, log
> in as another user, and then log out and back in as the original user.
> Will doing this cause the SQL Server to stop running (taking requests from
> applications)?
>

If EXISITS.....DROP Table doesn't work

Hi
Can any of you tell me what I've done wrong in the statement below. The
problem is that when I run the whole statement, it won't drop the table if
it already exist. If I then just run the first 2-3 lines where it looks for
the table and then drop it if it exists, it seems to work.
It might just be me that haven't got the syntax right, but it puzzles me why
it seems to behave differently depending on how much of the statement I run.
The way I have noticed the problem, is that I'm using the same code for 2
databases, where I use 2 different columns in one of the tables. In one
table I use the columns "navn1' and "navn2" where in the other I use
"kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
(and where the temp table doesn't exist already) it works fine and do the
job. Then I change the names of those 2 columns to match the names in the
second database -run the code, and then I get an "Invalid column name
"kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
using the same "version" of the temp table as I created with the first run
of the code.
When I then just execute the first lines of my code where it checks for the
table and drop it if it exist, and then afterwards run the full script, then
it works fine with the new names.
To me that looks like it wont drop the table if it exist, but I might be
wrong?
Additionally I've noticed that temp tables are being named slightly
different if they are local or global temp tables. In this case I'm using a
global temp table and when I run the "SELECT....from
INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
expected. If I use a local temp table it returns the value
"#ejd_adm_temp_____________..." which means that I can't find it in
INFORMATION_SCHEMA.TABLES because it has a different name.
Is this a known issue or is it just me that are missing something on how to
use temp tables (...could very well be the case..:-)...).
Here's the code that I'm running...
USE TESTDE
IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '##ejd_adm_temp')
DROP table ##ejd_adm_temp
select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
, a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
INTO ##ejd_adm_temp
FROM lejer l
JOIN ejendom e on l.ejendomnr=e.ejendomnr
JOIN AdrFunk AF on e.ejendomID=AF.recordID
JOIN Adresse A on A.adresseident=AF.adresseident
Regards
Steen
Use
IF OBJECT_ID('##ejd_adm_temp') IS NOT NULL
For more info see http://www.aspfaq.com/2458
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>
|||Use LIKE instead of = with the table name. Temporary tables can and do
have strange names. Anyway, why are you using global temp tables and why
are you trying to drop it.
Adrian
Steen Persson wrote:
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me why
> it seems to behave differently depending on how much of the statement I run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for the
> table and drop it if it exist, and then afterwards run the full script, then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>
|||temp tables are stored in tempdb. The technique is the same regardless of
whether the temp table is a regular temp table or a global temp table.
set nocount on
create table #emp (emp_id int)
create table ##emp (emp_id int)
select object_id('tempdb..#emp')
select object_id('tempdb..##emp')
if object_id('tempdb..#emp') is not null
drop table #emp
if object_id('tempdb..##emp') is not null
drop table ##emp
if object_id('tempdb..#emp') is not null
drop table #emp
else
print '#emp does not exist'
if object_id('tempdb..##emp') is not null
drop table ##emp
else
print '##emp does not exist'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>
|||Hi Adrian
Maybe there's no real reason to drop it. My original reason for dropping the
table, was because I wanted to run this code for both databases/tables in
the same script. Since I then had slightly different column names in the
tables, I had to drop the table in between. I've later changed my mind
though, so I think I'll run it in two seperate jobs and then I don't need to
drop the table.
Also there where no reason for using a global temp table, but I tried it as
a part of my testing where I had problems in dropping the table if it was
already there. Since I could see that my local temp table was named
differently than a global temp table, I tried with the global temp table.
I can easily get around the issue in this case, but I was just puzzled why
it didn't seemed to drop the table when running the whole script when it can
do it if I just run that part of the script. I'm still a learner in SQL , so
I'd like to find out why it behaves like it does - maybe next time I really
need to get it working...:-).
BTW: I also tried using LIKE instead of =, but that gave me the same
problem.
I'll work a bit more with it and also try Aaron's suggestion...
Regards
Steen
Adrian Edwards wrote:[vbcol=seagreen]
> Use LIKE instead of = with the table name. Temporary tables can and do
> have strange names. Anyway, why are you using global temp tables and
> why are you trying to drop it.
> Adrian
> Steen Persson wrote:

If EXISITS.....DROP Table doesn't work

Hi
Can any of you tell me what I've done wrong in the statement below. The
problem is that when I run the whole statement, it won't drop the table if
it already exist. If I then just run the first 2-3 lines where it looks for
the table and then drop it if it exists, it seems to work.
It might just be me that haven't got the syntax right, but it puzzles me why
it seems to behave differently depending on how much of the statement I run.
The way I have noticed the problem, is that I'm using the same code for 2
databases, where I use 2 different columns in one of the tables. In one
table I use the columns "navn1' and "navn2" where in the other I use
"kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
(and where the temp table doesn't exist already) it works fine and do the
job. Then I change the names of those 2 columns to match the names in the
second database -run the code, and then I get an "Invalid column name
"kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
using the same "version" of the temp table as I created with the first run
of the code.
When I then just execute the first lines of my code where it checks for the
table and drop it if it exist, and then afterwards run the full script, then
it works fine with the new names.
To me that looks like it wont drop the table if it exist, but I might be
wrong?
Additionally I've noticed that temp tables are being named slightly
different if they are local or global temp tables. In this case I'm using a
global temp table and when I run the "SELECT....from
INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
expected. If I use a local temp table it returns the value
"#ejd_adm_temp_____________..." which means that I can't find it in
INFORMATION_SCHEMA.TABLES because it has a different name.
Is this a known issue or is it just me that are missing something on how to
use temp tables (...could very well be the case..:-)...).
Here's the code that I'm running...
--
USE TESTDE
IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '##ejd_adm_temp')
DROP table ##ejd_adm_temp
select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
, a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
INTO ##ejd_adm_temp
FROM lejer l
JOIN ejendom e on l.ejendomnr=e.ejendomnr
JOIN AdrFunk AF on e.ejendomID=AF.recordID
JOIN Adresse A on A.adresseident=AF.adresseident
--
Regards
SteenUse
IF OBJECT_ID('##ejd_adm_temp') IS NOT NULL
For more info see http://www.aspfaq.com/2458
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||Use LIKE instead of = with the table name. Temporary tables can and do
have strange names. Anyway, why are you using global temp tables and why
are you trying to drop it.
Adrian
Steen Persson wrote:
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me why
> it seems to behave differently depending on how much of the statement I run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for the
> table and drop it if it exist, and then afterwards run the full script, then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email, l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||temp tables are stored in tempdb. The technique is the same regardless of
whether the temp table is a regular temp table or a global temp table.
set nocount on
create table #emp (emp_id int)
create table ##emp (emp_id int)
select object_id('tempdb..#emp')
select object_id('tempdb..##emp')
if object_id('tempdb..#emp') is not null
drop table #emp
if object_id('tempdb..##emp') is not null
drop table ##emp
if object_id('tempdb..#emp') is not null
drop table #emp
else
print '#emp does not exist'
if object_id('tempdb..##emp') is not null
drop table ##emp
else
print '##emp does not exist'
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eSlXExKnEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Hi
> Can any of you tell me what I've done wrong in the statement below. The
> problem is that when I run the whole statement, it won't drop the table if
> it already exist. If I then just run the first 2-3 lines where it looks
for
> the table and then drop it if it exists, it seems to work.
> It might just be me that haven't got the syntax right, but it puzzles me
why
> it seems to behave differently depending on how much of the statement I
run.
> The way I have noticed the problem, is that I'm using the same code for 2
> databases, where I use 2 different columns in one of the tables. In one
> table I use the columns "navn1' and "navn2" where in the other I use
> "kontaktnavn1" and "kontaktnavn2". If I then run my code on the first base
> (and where the temp table doesn't exist already) it works fine and do the
> job. Then I change the names of those 2 columns to match the names in the
> second database -run the code, and then I get an "Invalid column name
> "kontaktnavn1 and "kontaktnavn2. To me that looks like the code it still
> using the same "version" of the temp table as I created with the first run
> of the code.
> When I then just execute the first lines of my code where it checks for
the
> table and drop it if it exist, and then afterwards run the full script,
then
> it works fine with the new names.
> To me that looks like it wont drop the table if it exist, but I might be
> wrong?
> Additionally I've noticed that temp tables are being named slightly
> different if they are local or global temp tables. In this case I'm using
a
> global temp table and when I run the "SELECT....from
> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
> expected. If I use a local temp table it returns the value
> "#ejd_adm_temp_____________..." which means that I can't find it in
> INFORMATION_SCHEMA.TABLES because it has a different name.
> Is this a known issue or is it just me that are missing something on how
to
> use temp tables (...could very well be the case..:-)...).
>
> Here's the code that I'm running...
> --
> USE TESTDE
> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
> WHERE TABLE_NAME = '##ejd_adm_temp')
> DROP table ##ejd_adm_temp
> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
l.opkrbetalmeddel
> INTO ##ejd_adm_temp
> FROM lejer l
> JOIN ejendom e on l.ejendomnr=e.ejendomnr
> JOIN AdrFunk AF on e.ejendomID=AF.recordID
> JOIN Adresse A on A.adresseident=AF.adresseident
> --
> Regards
> Steen
>|||Hi Adrian
Maybe there's no real reason to drop it. My original reason for dropping the
table, was because I wanted to run this code for both databases/tables in
the same script. Since I then had slightly different column names in the
tables, I had to drop the table in between. I've later changed my mind
though, so I think I'll run it in two seperate jobs and then I don't need to
drop the table.
Also there where no reason for using a global temp table, but I tried it as
a part of my testing where I had problems in dropping the table if it was
already there. Since I could see that my local temp table was named
differently than a global temp table, I tried with the global temp table.
I can easily get around the issue in this case, but I was just puzzled why
it didn't seemed to drop the table when running the whole script when it can
do it if I just run that part of the script. I'm still a learner in SQL , so
I'd like to find out why it behaves like it does - maybe next time I really
need to get it working...:-).
BTW: I also tried using LIKE instead of =, but that gave me the same
problem.
I'll work a bit more with it and also try Aaron's suggestion...
Regards
Steen
Adrian Edwards wrote:
> Use LIKE instead of = with the table name. Temporary tables can and do
> have strange names. Anyway, why are you using global temp tables and
> why are you trying to drop it.
> Adrian
> Steen Persson wrote:
>> Hi
>> Can any of you tell me what I've done wrong in the statement below.
>> The problem is that when I run the whole statement, it won't drop
>> the table if it already exist. If I then just run the first 2-3
>> lines where it looks for the table and then drop it if it exists, it
>> seems to work.
>> It might just be me that haven't got the syntax right, but it
>> puzzles me why it seems to behave differently depending on how much
>> of the statement I run. The way I have noticed the problem, is that
>> I'm using the same code for 2 databases, where I use 2 different
>> columns in one of the tables. In one table I use the columns "navn1'
>> and "navn2" where in the other I use "kontaktnavn1" and
>> "kontaktnavn2". If I then run my code on the first base (and where
>> the temp table doesn't exist already) it works fine and do the job.
>> Then I change the names of those 2 columns to match the names in the
>> second database -run the code, and then I get an "Invalid column
>> name "kontaktnavn1 and "kontaktnavn2. To me that looks like the code
>> it still using the same "version" of the temp table as I created
>> with the first run of the code.
>> When I then just execute the first lines of my code where it checks
>> for the table and drop it if it exist, and then afterwards run the
>> full script, then it works fine with the new names.
>> To me that looks like it wont drop the table if it exist, but I
>> might be wrong?
>> Additionally I've noticed that temp tables are being named slightly
>> different if they are local or global temp tables. In this case I'm
>> using a global temp table and when I run the "SELECT....from
>> INFORMATION_SCHEMA.TABLES" it returns the name '##ejd_adm_temp" as I
>> expected. If I use a local temp table it returns the value
>> "#ejd_adm_temp_____________..." which means that I can't find it in
>> INFORMATION_SCHEMA.TABLES because it has a different name.
>> Is this a known issue or is it just me that are missing something on
>> how to use temp tables (...could very well be the case..:-)...).
>>
>> Here's the code that I'm running...
>> --
>> USE TESTDE
>> IF EXISTS(SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
>> WHERE TABLE_NAME = '##ejd_adm_temp')
>> DROP table ##ejd_adm_temp
>> select l.lejerid, l.EjendomNr, e.EjendomID, e.AdmStopDato
>> , a.navn1, a.navn2, a.telefondirekte, a.tildato, a.email,
>> l.opkrbetalmeddel INTO ##ejd_adm_temp
>> FROM lejer l
>> JOIN ejendom e on l.ejendomnr=e.ejendomnr
>> JOIN AdrFunk AF on e.ejendomID=AF.recordID
>> JOIN Adresse A on A.adresseident=AF.adresseident
>> --
>> Regards
>> Steensql

if exisits statement

I want to run an if exists statement to see if some sql retuns true.
i have got a simple statement to work
if exists(select * from imagebank1)
execute things

this works.
what i want to be able to do is test a more complecated statment with contains variables.
so is it possible to do if exists(@.sql)

the @.sql is equal to
set @.sql ='SELECT [KEY],RANK,imageID,imagename, txtdescription,Metkeyword,
metsystamatic,MetSubjectArea,MetType,PictURL, PictFileURL
FROM CONTAINSTABLE(imagebankmeta1,*,'''+@.schParam+''') C
JOIN imagebankmeta1 P
ON P.imageID = C.[KEY]
where rank > 5
'+ @.SchPict +
' ORDER BY RANK DESC'

any help much appreciated
thanksHave you tried @.@.rowcount ?|||No.
I have read about it but have not put it in to any of my procedures.
how could i utilise this function

would i call my seq first and then do the @.@.rowcount to see if no results have been returned then run some other sql ...

the recordset from the procedure is been sent to an ASP page which then displays the results. So if i checked the sql after it had run i would have some how delay the recodset been set to the asp page until it had been passed through a checker of some kind..

sorry but my knowledge of stored procedures is limited.

thanks
john|||The solution really depends on what you are trying to accomplish. Could you explain in greater detail what you are doing - are you checking the existence of rows within the stored procedure or asp - does it matter ?

If Else question URGENT

I want to run a query that will insert rows into another table.

I also want to do some calculations on a couple of the columns:


SELECT
KEYCODESTRINGDESCRIPTION,
STRING,
Mailed,
Sales,
Orders,
CATALOGTITLE,
Response = Orders / Mailed,
[Average Invoice] = Sales / Orders,
SMP = (Sales / Mailed) * 1000

INTO TP_GA_REPORT
FROM TP_GA_REPORT_TEMP

I have a condition where some of the colums might have a 0 in them, which of course causes a "Divide by 0" error. What I would like to do is put an IF statement in the query to deal with this 0.

i.e.

if orders = 0 then response=0
else response = Response = Orders / Mailed

Hope this makes sense!

Thanks KenTry using SQL CASE:

SELECT ...,
CASE WHEN Mailed = 0 THEN 0 ELSE Orders / Mailed END,
...
FROM ...|||Thank you!!! So very much, worked like a charm!

IE7 and RS2005 navigation issues

When I run a report under Firefox, it works fine.
If the report contains a drill through, and I drill through and then try to
back up, the report will spin and never refresh unless I manually click
refresh.
Steps to repro:
1. Create two reports, one with a drill through and the second the target
of a drill through.
2. Publish the reports.
3. Using either URL access or the report manager GUI, browse to the parent
report in IE7. Not sure if it matters, but this is on Vista.
4. From the parent report, drill through to the child report.
5. Using any of the various ways to navigate backwards, backup to the
parent report. The report will spin and never resolve.
Is this a configuration issue on my end or is this an IE7 issue? I've tried
various browser options and I get this behavior no matter what. It works
fine in Firefox and Opera.
Thanks,
JustinHello Justin,
I would like to know whether you have add your server into the trust area
and enabled the javascript.
I tested on my Vista machine and I could drillthrough and backwards
correctly.
Please let me know if you still could not navigate the report.
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
Thank you for fielding this.
I've tried various IE7 security settings, including setting it to low and
protection mode to off.
I did a bit more research and here's what I see.
1. When you drill through to the subreport, it loads fine.
2. When you hit back and check the URL of the iframe for the
reportview.axd, the parameter information is gone.
It still has the report the target in the query string, but parameters have
been lost.
Note - I am running without default parameters.
Perhaps you could try and use a drill through that requires parameters and
let me know if you can reproduce it.
Thanks,
Justin
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:nFvYtABfHHA.4368@.TK2MSFTNGHUB02.phx.gbl...
> Hello Justin,
> I would like to know whether you have add your server into the trust area
> and enabled the javascript.
> I tested on my Vista machine and I could drillthrough and backwards
> correctly.
> Please let me know if you still could not navigate the report.
> Thanks!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Justin,
I would like to know where did you view the report? Direct in IE to view
the reports on Report Server? Or are you using the Sharepoint Web Part to
view the report?
Also, what did you use to drillthough the report. Jump to Report or Jump to
URL?
I tested the child report with paremeter which still could drillthrough
correctly.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||On Apr 12, 5:10 am, w...@.online.microsoft.com (Wei Lu [MSFT]) wrote:
> Hello Justin,
> I would like to know where did you view the report? Direct in IE to view
> the reports on Report Server? Or are you using the Sharepoint Web Part to
> view the report?
> Also, what did you use to drillthough the report. Jump to Report or Jump to
> URL?
> I tested the child report with paremeter which still could drillthrough
> correctly.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
I am having the exact same issue with any drilldown reports. When I
try to go back to the parent report, it just spins and never
resolves. If I hit the refresh button, then it resolves. Has anyone
figured out what this issue is?
Thanks,
Scott|||I also have the same problem. I have a three layer report in which the drill
downs are just fine but the IE7 Back button leaves me spinning forever. The
project runs fine in the designer. At this stage of development, I am running
locally so there should not be any network problem.
Any assistance would be greatly appreciated.
Thanks
Dennis
"scott.hunley@.cox.net" wrote:
> On Apr 12, 5:10 am, w...@.online.microsoft.com (Wei Lu [MSFT]) wrote:
> > Hello Justin,
> >
> > I would like to know where did you view the report? Direct in IE to view
> > the reports on Report Server? Or are you using the Sharepoint Web Part to
> > view the report?
> >
> > Also, what did you use to drillthough the report. Jump to Report or Jump to
> > URL?
> >
> > I tested the child report with paremeter which still could drillthrough
> > correctly.
> >
> > Sincerely,
> >
> > Wei Lu
> > Microsoft Online Community Support
> >
> > ==================================================> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> >
> > ==================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
>
> I am having the exact same issue with any drilldown reports. When I
> try to go back to the parent report, it just spins and never
> resolves. If I hit the refresh button, then it resolves. Has anyone
> figured out what this issue is?
> Thanks,
> Scott
>

IE v7 SP3 - Crashes on SSRS Reports

I installed IE 7 beta 3 on two of my systems, now when I try to run any of
my SSRS reports IE starts using 100% of the CPU and never stops. Has anyone
else seen this issue?
I know this is an IE issue, just thought I would check with other SSRS
users.Yes. This is a known problem. In my conversation with MS I was told that the
IE team knows this and has promised to fix the problem. IE7 beta 2 did not
have trouble but beta 3 does.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Morisoli" <michael@.removeme.vieon.net> wrote in message
news:%23LrQvBtxGHA.3440@.TK2MSFTNGP06.phx.gbl...
>I installed IE 7 beta 3 on two of my systems, now when I try to run any of
>my SSRS reports IE starts using 100% of the CPU and never stops. Has
>anyone else seen this issue?
> I know this is an IE issue, just thought I would check with other SSRS
> users.
>

IE hangs after running Report viewer

Hi all,

I run a web application with form authentication. In my web application, I have some links to call some reports from a reporting server. When user clicks the link of web report, a login dialog box asks user to enter the id and password. It works fine. However some web pages sometime can not run as usual after calling the report server. Can anyone give me some advisement to fix the problem?

Thanks in advance.

Best regards,

Tom

Hi,

From your description, it seems that your IE Browser hangs after you run your report viewer, right?

In most cases, the cause of this kind of issue is that you have not installed the service pack for your IE or your SQLServer Reporting Service.

Here are some links which should be installed:

If you are using SQLServer2000.

SQLServer2000 Reporting Service SP2
http://www.microsoft.com/downloads/details.aspx?FamilyId=502C0D89-1308-4662-8F58-CEC55EF1235B&displaylang=en

If you are using SQLServer2005

SQLServer2005 SP2

http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en

If you are using IE6:

IE6 SP1
http://www.microsoft.com/downloads/details.aspx?FamilyID=1e1550cb-5e5d-48f5-b02b-20b602228de6&DisplayLang=en

Thanks.

|||

I tried to update the SP2 for the SQL server 2005, but the said error does not be fixed. I am using IE 7 for testing. Can anyone help me and give me some advisement?

Thanks a lot.

Tom

IE 7 and Document Map is Blank

All,

I have a report with a document map and when I run the report through
IE 7.0, the Document Map is blank. Running that same report through IE
6.0 and it is fine.

Both instances are run against the same server via Report Viewer so
there is no difference other than the client IE version.

Anyone know of a work around for this?

Thanks,
Sherry

I have the exact same issue. Anybody has a solution?

IDW15: Execute package using Execute Package Task

I just finished installing IDW15 and started testing some of my packages. When I run the package directly it works fine; when I run it through a parent package I get the error:
[DTS.Pipeline] Error: The product level is insufficient for component "Derived Column" (408).

I get a similar error for the SCD and OLEDB Dest components. The control flow item works fine, it is the data flow components that have the problem.
Anyone else have a similar issue?
-Evan Black

Evan, this is similar to other issues we've seen in the past with SKU differentiation. Will you please file a bug and include the packages if possible? We'll take a look.
Make sure you include information about your setup, what versions were previously installed etc. This sounds like it may be a corrupted setup issue.
Thanks,
K|||Wandering if there is a solution to this? I am having the same issue with executing an SSIS package from code. Package loads, properties are accessable, but execute returns

The product level is insufficient for component "Scorecard Source CSV"

I have VS 2005 RC1 and Sql Server 2005 CTP installed

|||Any resolution/update to this? Getting the same error when attempting to automate via DTEXEC & DTEXECUI|||Never saw it before today; but this was the first time I ran from "debug|start w/o debugging"... it opens a command window and the first ten or so containers err with this message.

Any resolution?

Running VS RC1 and September SS 2005|||Have you installed SSIS, or just the workstation components? If later, the package will run fine in designer/debugger, but most tasks would not run outside of designer.

SSIS ships as part of SQL 2005, but the setup give you a choice of whether to install it or not.

Please rerun the setup. In one of the first screens the setup program asks which components to installs, there are 5 checkboxes total: SQL/SSAS/SSRS/SSIS and Workstation components. Please make sure the SSIS check box is checked.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=110020|||

This is probably the reason for the error. The full Visual Studio 2005 product and the SQL Server 2005 tools ONLY have been installed on this XP box. The database is on a 2003 Advanced Server... and a Sybase ASA data source on another.

Thank you... I will test the external execution of the package on a different host.

But let me vent a bit here now.... while the answer is a good one, and seems like it might just be the right explanation for this issue, the solution (as a general solution to a problem) is not a good one for us...

This is currently somewhat of a controlled environment with Ghosting taking place at various stages along the way. We are trying to develop in a way that simulates our product deployment. We have two medical devices that each run Sybase ASA (and two schema versions of one, and three schema versions of the other), and we are replicating those five schemas into one staging area where we are picking up data with SSIS and transforming it (ETL) into a snowflake within SQL Server which could possible be our reporting solution for the next ten years or so - IF THINGS WORKED! There have been roadblocks ever step of the way though, as much as people are raving about SSIS, SSRS and SSAS (OLAP)... the products just do not perform as anticipated and reported. Stumbling blocks? We can not currently read data from ASA when the replication server is on a different subnet. This is because in order to get from one subnet to the next the ASA connection must include the parameter "commlinks=tcpip" and we have found no way to include this in the string. Perhaps a Sybase issue (aside from the fact that Sybase tools don't have the problem and VS 2003 as well as 2005 do, so not a beta issue either); but Sybase is working on it for us. And our SQL Server database does not reside on the developer's box, only to find out later that SQL Sever Destination do not currently work with anything except a "local database"... after weeks of working with it, and around it....

We constantly have problems with packages just breaking and not being fixable, with the only solution being to rebuild the package from scratch... for instance, today, after five or six days dealing with null values in a containers failure path (the four columns being correct in the success stream and two being null in the failure stream) we discover that there are no "outputs" in the advanced editor for that container. It lets us add them; but the data types are empty and it warns us of that... but when we try to change them (they do change) and save them, it errs with "you cannot change the data types" on this element. At one point I tried to delete the error elements (hoping to fool the tool) and it would not let me; but it let me rename them to my output's names... and then it also would not allow me to change the data types from int4 to what I wanted (string). Anyway, the only solution was to start a fresh package and build it correctly from the beginning.

And it was during this tedious debugging process that the "debug | run without debugging" was discovered and this new error was found.

but we have been dealing with installation and configuration issues for three months now - and while I have been the primary, full-time developer/DBA doing so, there have been a team of others (sometimes as many as ten) doing the same thing, and experiencing the same problems - including an onsite, fulltime Microsoft employee who is on the premises doing WebPieces demos for our evaluation could not use SQL Server 2005 (September) for a database (using Access instead) because he "could not get it installed".

I have spent six full-time weeks, first with April, then with June CTPs... and finally with September doing NOTHING but installation, cleanup, start-overs, and reinstallation on various platforms... and another six weeks struggling with SSIS.

The final position being taken that none of the uninstallation or clean up tools work properly and the ONLY way to do the installation is to do fresh OS installs... Ghost, and be prepared to start over - frequently.

But even this is tedious by the time you get the other (even minimal) pieces you need installed (antivirus, spam blocks, tools) and installed in the domain, etc. It all takes time...

and the "fix" to every new problem cannot be "run install and make sure you did"... whatever. I mean, I know that this is valuable advice for some people, and for some circumstances; but it is not an acceptable solution.

The latest major change in direction has been to eliminate the external data source in replication server, add Sybase "direct connect" and replicate straight into SQL Server into a relational store (as a staging area), and then use SSIS to transform into the reporting database (star, actually modified star, snowflake).

This gives us the added advantage of allowing us to slowly migrate a subset of the 400 existing Crystal Reports from an operational store to the new tools - so instead of providing just a prototype/demo model in January, we are giving them a platform that can evolve... but, again, ONLY IF IT WORKS!

Having to change strategy this late in the game - three months into a six month prototype - is NOT a good thing.

Did I mention I have heartburn?

Thanks again for your response... and listening to my pain :)

IDW15: Execute package using Execute Package Task

I just finished installing IDW15 and started testing some of my packages. When I run the package directly it works fine; when I run it through a parent package I get the error:
[DTS.Pipeline] Error: The product level is insufficient for component "Derived Column" (408).

I get a similar error for the SCD and OLEDB Dest components. The control flow item works fine, it is the data flow components that have the problem.
Anyone else have a similar issue?
-Evan Black

Evan, this is similar to other issues we've seen in the past with SKU differentiation. Will you please file a bug and include the packages if possible? We'll take a look.
Make sure you include information about your setup, what versions were previously installed etc. This sounds like it may be a corrupted setup issue.
Thanks,
K|||Wandering if there is a solution to this? I am having the same issue with executing an SSIS package from code. Package loads, properties are accessable, but execute returns

The product level is insufficient for component "Scorecard Source CSV"

I have VS 2005 RC1 and Sql Server 2005 CTP installed|||Any resolution/update to this? Getting the same error when attempting to automate via DTEXEC & DTEXECUI|||Never saw it before today; but this was the first time I ran from "debug|start w/o debugging"... it opens a command window and the first ten or so containers err with this message.

Any resolution?

Running VS RC1 and September SS 2005|||Have you installed SSIS, or just the workstation components? If later, the package will run fine in designer/debugger, but most tasks would not run outside of designer.

SSIS ships as part of SQL 2005, but the setup give you a choice of whether to install it or not.

Please rerun the setup. In one of the first screens the setup program asks which components to installs, there are 5 checkboxes total: SQL/SSAS/SSRS/SSIS and Workstation components. Please make sure the SSIS check box is checked.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=110020|||

This is probably the reason for the error. The full Visual Studio 2005 product and the SQL Server 2005 tools ONLY have been installed on this XP box. The database is on a 2003 Advanced Server... and a Sybase ASA data source on another.

Thank you... I will test the external execution of the package on a different host.

But let me vent a bit here now.... while the answer is a good one, and seems like it might just be the right explanation for this issue, the solution (as a general solution to a problem) is not a good one for us...

This is currently somewhat of a controlled environment with Ghosting taking place at various stages along the way. We are trying to develop in a way that simulates our product deployment. We have two medical devices that each run Sybase ASA (and two schema versions of one, and three schema versions of the other), and we are replicating those five schemas into one staging area where we are picking up data with SSIS and transforming it (ETL) into a snowflake within SQL Server which could possible be our reporting solution for the next ten years or so - IF THINGS WORKED! There have been roadblocks ever step of the way though, as much as people are raving about SSIS, SSRS and SSAS (OLAP)... the products just do not perform as anticipated and reported. Stumbling blocks? We can not currently read data from ASA when the replication server is on a different subnet. This is because in order to get from one subnet to the next the ASA connection must include the parameter "commlinks=tcpip" and we have found no way to include this in the string. Perhaps a Sybase issue (aside from the fact that Sybase tools don't have the problem and VS 2003 as well as 2005 do, so not a beta issue either); but Sybase is working on it for us. And our SQL Server database does not reside on the developer's box, only to find out later that SQL Sever Destination do not currently work with anything except a "local database"... after weeks of working with it, and around it....

We constantly have problems with packages just breaking and not being fixable, with the only solution being to rebuild the package from scratch... for instance, today, after five or six days dealing with null values in a containers failure path (the four columns being correct in the success stream and two being null in the failure stream) we discover that there are no "outputs" in the advanced editor for that container. It lets us add them; but the data types are empty and it warns us of that... but when we try to change them (they do change) and save them, it errs with "you cannot change the data types" on this element. At one point I tried to delete the error elements (hoping to fool the tool) and it would not let me; but it let me rename them to my output's names... and then it also would not allow me to change the data types from int4 to what I wanted (string). Anyway, the only solution was to start a fresh package and build it correctly from the beginning.

And it was during this tedious debugging process that the "debug | run without debugging" was discovered and this new error was found.

but we have been dealing with installation and configuration issues for three months now - and while I have been the primary, full-time developer/DBA doing so, there have been a team of others (sometimes as many as ten) doing the same thing, and experiencing the same problems - including an onsite, fulltime Microsoft employee who is on the premises doing WebPieces demos for our evaluation could not use SQL Server 2005 (September) for a database (using Access instead) because he "could not get it installed".

I have spent six full-time weeks, first with April, then with June CTPs... and finally with September doing NOTHING but installation, cleanup, start-overs, and reinstallation on various platforms... and another six weeks struggling with SSIS.

The final position being taken that none of the uninstallation or clean up tools work properly and the ONLY way to do the installation is to do fresh OS installs... Ghost, and be prepared to start over - frequently.

But even this is tedious by the time you get the other (even minimal) pieces you need installed (antivirus, spam blocks, tools) and installed in the domain, etc. It all takes time...

and the "fix" to every new problem cannot be "run install and make sure you did"... whatever. I mean, I know that this is valuable advice for some people, and for some circumstances; but it is not an acceptable solution.

The latest major change in direction has been to eliminate the external data source in replication server, add Sybase "direct connect" and replicate straight into SQL Server into a relational store (as a staging area), and then use SSIS to transform into the reporting database (star, actually modified star, snowflake).

This gives us the added advantage of allowing us to slowly migrate a subset of the 400 existing Crystal Reports from an operational store to the new tools - so instead of providing just a prototype/demo model in January, we are giving them a platform that can evolve... but, again, ONLY IF IT WORKS!

Having to change strategy this late in the game - three months into a six month prototype - is NOT a good thing.

Did I mention I have heartburn?

Thanks again for your response... and listening to my pain :)

Identity_insert is set to off

A installation has been moved to run on Windows 2000 / SQL 2005, but when
adding new items we get "Identity_insert is set to off" in our application.
The application has not been changed.
- Is this related to Windows 2000 running SQL 2005?
- Is it related to access on the SQL 2005 server?
- Can it be changed on the SQL server - or only in the application
programming?This probably happened when you migrated the data to the new server. If you
have a table with IDENTITY columns and you replicate the data over to a new
table, you first need to turn identity_insert on in order to copy the
existing data. It might be that in your old design your application
required this setting to be OFF because it was using the MAX(id_col)+1 trick
instead (in which case, why use an IDENTITY at all).
To find all tables with IDENTITY columns:
SELECT OBJECT_NAME([object_id]) FROM sys.columns WHERE is_identity = 1;
You may need to identify which specific table(s) cause the application this
problem (it might not be all). Does the error message return the table name
as well?
When you identify the table(s) that need to have IDENTITY_INSERT set to ON,
you can do this:
SET IDENTITY_INSERT table_name ON;
Cheers,
Aaron
"Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>A installation has been moved to run on Windows 2000 / SQL 2005, but when
> adding new items we get "Identity_insert is set to off" in our
> application.
> The application has not been changed.
> - Is this related to Windows 2000 running SQL 2005?
> - Is it related to access on the SQL 2005 server?
> - Can it be changed on the SQL server - or only in the application
> programming?
>|||Could also be that when you created the tables again, you checked the "is
for replication" property of identity columns, which sets identity_insert to
false (at least vaguely according to BOL).
"Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>A installation has been moved to run on Windows 2000 / SQL 2005, but when
> adding new items we get "Identity_insert is set to off" in our
> application.
> The application has not been changed.
> - Is this related to Windows 2000 running SQL 2005?
> - Is it related to access on the SQL 2005 server?
> - Can it be changed on the SQL server - or only in the application
> programming?
>|||Since this setting is session-specific and not server-wide, I would do a
search of your codebase for "SET IDENTITY_INSERT"...
"Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>A installation has been moved to run on Windows 2000 / SQL 2005, but when
> adding new items we get "Identity_insert is set to off" in our
> application.
> The application has not been changed.
> - Is this related to Windows 2000 running SQL 2005?
> - Is it related to access on the SQL 2005 server?
> - Can it be changed on the SQL server - or only in the application
> programming?
>|||Thanks!
We will test it tomorrow and let you know.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Since this setting is session-specific and not server-wide, I would do a
> search of your codebase for "SET IDENTITY_INSERT"...
>
> "Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
> news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
> >A installation has been moved to run on Windows 2000 / SQL 2005, but when
> > adding new items we get "Identity_insert is set to off" in our
> > application.
> >
> > The application has not been changed.
> >
> > - Is this related to Windows 2000 running SQL 2005?
> > - Is it related to access on the SQL 2005 server?
> > - Can it be changed on the SQL server - or only in the application
> > programming?
> >
> >
>
>

Identity_insert is set to off

A installation has been moved to run on Windows 2000 / SQL 2005, but when
adding new items we get "Identity_insert is set to off" in our application.
The application has not been changed.
- Is this related to Windows 2000 running SQL 2005?
- Is it related to access on the SQL 2005 server?
- Can it be changed on the SQL server - or only in the application
programming?
This probably happened when you migrated the data to the new server. If you
have a table with IDENTITY columns and you replicate the data over to a new
table, you first need to turn identity_insert on in order to copy the
existing data. It might be that in your old design your application
required this setting to be OFF because it was using the MAX(id_col)+1 trick
instead (in which case, why use an IDENTITY at all).
To find all tables with IDENTITY columns:
SELECT OBJECT_NAME([object_id]) FROM sys.columns WHERE is_identity = 1;
You may need to identify which specific table(s) cause the application this
problem (it might not be all). Does the error message return the table name
as well?
When you identify the table(s) that need to have IDENTITY_INSERT set to ON,
you can do this:
SET IDENTITY_INSERT table_name ON;
Cheers,
Aaron
"Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>A installation has been moved to run on Windows 2000 / SQL 2005, but when
> adding new items we get "Identity_insert is set to off" in our
> application.
> The application has not been changed.
> - Is this related to Windows 2000 running SQL 2005?
> - Is it related to access on the SQL 2005 server?
> - Can it be changed on the SQL server - or only in the application
> programming?
>
|||Could also be that when you created the tables again, you checked the "is
for replication" property of identity columns, which sets identity_insert to
false (at least vaguely according to BOL).
"Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>A installation has been moved to run on Windows 2000 / SQL 2005, but when
> adding new items we get "Identity_insert is set to off" in our
> application.
> The application has not been changed.
> - Is this related to Windows 2000 running SQL 2005?
> - Is it related to access on the SQL 2005 server?
> - Can it be changed on the SQL server - or only in the application
> programming?
>
|||Since this setting is session-specific and not server-wide, I would do a
search of your codebase for "SET IDENTITY_INSERT"...
"Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>A installation has been moved to run on Windows 2000 / SQL 2005, but when
> adding new items we get "Identity_insert is set to off" in our
> application.
> The application has not been changed.
> - Is this related to Windows 2000 running SQL 2005?
> - Is it related to access on the SQL 2005 server?
> - Can it be changed on the SQL server - or only in the application
> programming?
>
|||Thanks!
We will test it tomorrow and let you know.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Since this setting is session-specific and not server-wide, I would do a
> search of your codebase for "SET IDENTITY_INSERT"...
>
> "Jesper Lohse" <JesperLohse@.discussions.microsoft.com> wrote in message
> news:7B4BB2B7-27ED-4C45-B555-858D2BD71FA0@.microsoft.com...
>
>

identity_insert in SQL 2005

Hi,

we're currently doing test runs to make our application run on SQL 2005. In one of our projects, we need to replicate data from one master database to several slave databases. As the primary keys need to be the same, I used the "set identity_insert <table name> on" command to insert the exact same keys in the respective identity columns. This has always worked on our SQL 2000 databases. However, in the test runs on SQL 2005, I get the following error message:

"Cannot insert explicit value for identity column in table 'NomenclatuurSleutel' when IDENTITY_INSERT is set to OFF."

I cannot understand why I get this error message. Our components run in COM+, I execute the "set identity_insert on" statement right before the insert statement, and switch it off again right away. This all happens in the same method, and thus I expect it to be executed in the same transaction. A trace on SQL server confirms this: the statements all have the same transaction ID. However, there are 2 lines that are worrying me a bit: between the "set identity_insert" and the insert statement, there seems to be a rollback-action... but this has a different transaction ID, so I don't know whether this affects the identity-insert statement...

Here's a screenshot of the trace:

trace

I am clueless as to where I should look next, is there a difference in transaction management between SQL 2000 and 2005, is the identity_insert statement executed differently,... ?

Looks like you are using connection pooling...notice the RPC "sp_reset_connection" call in between your statements? That is used by data providers (i.e. OleDB, Sql Client, etc.) to reset a connection when reused from a pool...One of the many things that does is abort any open transactions...

Take a look here for some additional information:

http://www.sqldev.net/misc/sp_reset_connection.htm

I'm only guessing that the trace was performed on a single SPID, if so this is your problem, as the sp_reset_connection will abort your transaction amoung other things...

HTH,

|||

I was afraid that would be the case, as I came across that very page you're linking to. I do find it strange, however, that this has always worked on our SQL 2000 databases. And the sp_reset_connection stored procedure was executed as well, no problems there though.

I did find the following comment on a blog somewhere:

When a connection gets pulled out of the pool, an "exec sp_reset_connection" quitely gets sent, and this resets the connection state. With SQL 2000, a few things (like the isolation level) don't get reset, but most things, including the current database, do. With SQL 2005, everything is supposed to get reset.

Apparently, the few things that didn't get reset, are the things that kept my code running.

I tested some new code to work around this, by sending the three SQL-statements (identity_insert on, insert-statement and identity_insert off) in one batch to the server instead of three separate commands. But I'm a little reluctant to go ahead with this, as I'd need to change a lot of code. I'd much rather see a solution in the form of a configuration adjustment in SQL Server, could this be possible?

Identity Seed

Can you run an alter table script to change the seed on a table or do you
have to redefine the table DDL?
I want the seed to be 0 instead of 1Check for the DBCC CHECKIDENT in BOL and see if it helps.
MC
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E7B08050-BD84-4AD1-8E4C-D3C6192C39B7@.microsoft.com...
> Can you run an alter table script to change the seed on a table or do you
> have to redefine the table DDL?
> I want the seed to be 0 instead of 1|||thanks MC but I want to cheange the seed not the identity
Can I update the INFORMATION_SCHEMA.TABLES Seed value'|||Itried this but that aint gunna work.
Any other ideas?
SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME) AS IDENT_SEED, *
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL
and TABLE_NAME in ('Pot_lu_rd_county')
update INFORMATION_SCHEMA.TABLES
set IDENT_SEED(TABLE_NAME) = 0
WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL and TABLE_NAME in
('Pot_lu_rd_county')|||You can change the seed value by using DBCC CHECKIDENT (table, reseed, new
seed value) options. Perhaps I misunderstood something?
MC
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8D21B11D-46D9-46B0-B397-843015B31F86@.microsoft.com...
> Itried this but that aint gunna work.
> Any other ideas?
> SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME) AS IDENT_SEED, *
> FROM INFORMATION_SCHEMA.TABLES
> WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL
> and TABLE_NAME in ('Pot_lu_rd_county')
> update INFORMATION_SCHEMA.TABLES
> set IDENT_SEED(TABLE_NAME) = 0
> WHERE IDENT_SEED(TABLE_NAME) IS NOT NULL and TABLE_NAME in
> ('Pot_lu_rd_county')|||marcmc wrote on Thu, 13 Apr 2006 04:52:01 -0700:

> thanks MC but I want to cheange the seed not the identity
DBCC CHECKINDENT is for changing the seed. Read the description again. The
seed is the value to be used for the first row if the table is empty, or the
next value to use -1 if the table has rows (ie. if you set the seed to 10
and the table has a row in it already, the next new row will be given the
value 11 for the identity)
Or do you mean you want to change the increment?
Dan|||Hi,
I copied the contents of the table to a temp table and ran
DBCC CHECKIDENT ('Pot_lu_rd_county', reseed, 0)
sp_help still says the seed is 1
and populated the table again. The first id is still 1.
I want the tables first id to be 0|||There doesn't seem to be a way apart from dropping the table & redefining th
e
ddl with the following'
IDENTITY (0, 1)|||Did you try setting the seed to -1
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:6BDD2FF5-0619-4EE4-86AB-BB8C3F80A4E6@.microsoft.com...
> Hi,
> I copied the contents of the table to a temp table and ran
> DBCC CHECKIDENT ('Pot_lu_rd_county', reseed, 0)
> sp_help still says the seed is 1
> and populated the table again. The first id is still 1.
> I want the tables first id to be 0

Identity problem. (Need generate on subscriber new identity values)

If you change the publication to queued updating, use
automatic identity range management and don't run the
queue reader, you should achieve what you require.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
But where in that case subcribers updates will be stored? On distributor?
Is it known bug with incorrect handling identity values in replications? Or
it is my own issue?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:050b01c50853$c6e94010$a601280a@.phx.gbl...
> If you change the publication to queued updating, use
> automatic identity range management and don't run the
> queue reader, you should achieve what you require.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||The subscriber updates will be held on the queue table at
the subscriber, or you could remove the subscriber
triggers. It is assumed that subscribers are read only in
normal circumstances, so any issues regarding data
changes at a subscriber and problems with identities on
the subscriber are not really catered for. Identities are
only coded for if the subscriber uses immediate updating
or queued updating.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

IDENTITY column

Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
The application is trying to run this insert into the table:
insert into X (MYID, MYVAR) values (null, 'test').
This error is being returned:
Cannot insert explicit value for identity column in table 'X' when
IDENTITY_INSERT is set to OFF.
I cannot modify the application code insert. Is there a way to configure the
IDENTITY to replace the null with the IDENTITY value and not error on the
attempt.
I know a trigger can be written to do this, but I want to know if something
can be configured in the IDENTITY or TABLE.
Thanks!!
BevoI don't believe a trigger will work for you either Bevo. You are trying to
push a null into a NOT NULL column. This constraint should be checked
before the data is modified and the Triggers can even fire.
Is there a reason you can't modify the INSERT statement to something like:
INSERT INTO X (MyVar) VALUES ('test')
Rick Sawtell
MCT, MCSD, MCDBA
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> The application is trying to run this insert into the table:
> insert into X (MYID, MYVAR) values (null, 'test').
> This error is being returned:
> Cannot insert explicit value for identity column in table 'X' when
> IDENTITY_INSERT is set to OFF.
>
> I cannot modify the application code insert. Is there a way to configure
the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
> I know a trigger can be written to do this, but I want to know if
something
> can be configured in the IDENTITY or TABLE.
> Thanks!!
> Bevo|||The table should replace the NULL value with an IDENTITY value, we do not
want NULL inserted.
The INSERT cannot be modified because it is in application code that cannot
be modified.
"Rick Sawtell" wrote:
> I don't believe a trigger will work for you either Bevo. You are trying to
> push a null into a NOT NULL column. This constraint should be checked
> before the data is modified and the Triggers can even fire.
> Is there a reason you can't modify the INSERT statement to something like:
> INSERT INTO X (MyVar) VALUES ('test')
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> > Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> >
> > The application is trying to run this insert into the table:
> >
> > insert into X (MYID, MYVAR) values (null, 'test').
> >
> > This error is being returned:
> >
> > Cannot insert explicit value for identity column in table 'X' when
> > IDENTITY_INSERT is set to OFF.
> >
> >
> > I cannot modify the application code insert. Is there a way to configure
> the
> > IDENTITY to replace the null with the IDENTITY value and not error on the
> > attempt.
> >
> > I know a trigger can be written to do this, but I want to know if
> something
> > can be configured in the IDENTITY or TABLE.
> >
> > Thanks!!
> > Bevo
>
>|||> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
A small but significant correction: IDENTITY isn't a datatype.
> Is there a way to configure the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
No, not if you try to insert an explicit NULL - IDENTITY columns are
implicitly non-nullable.
> I know a trigger can be written to do this
The only solution I can think of using a trigger is to create an INSTEAD OF
trigger on a view over this table:
CREATE VIEW X1 (myid, myvar)
AS SELECT myid+0 , myvar
FROM X
GO
CREATE TRIGGER trg_x ON x1 INSTEAD OF INSERT
AS
INSERT INTO x (myvar)
SELECT myvar
FROM Inserted
GO
INSERT INTO X1 (myid, myvar) VALUES (NULL,'test')
Then you'd have to insert into the view rather than the table. You could
always rename the table and call the view X instead. That would avoid a
change to your INSERT statement but would likely break some other code
elsewhere.
> I cannot modify the application code insert.
!!' Even when it won't work? That seems a bizzare and unreasonable.
--
David Portas
SQL Server MVP
--|||Here's another possibility using a trigger:
ALTER TABLE X DROP COLUMN myid
ALTER TABLE X ADD myid INTEGER NULL -- Not IDENTITY!
ALTER TABLE X ADD new_myid INTEGER IDENTITY -- IDENTITY
GO
CREATE TRIGGER trg_x ON X FOR INSERT
AS
UPDATE X SET myid = new_myid
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE new_myid = X.new_myid)
AND myid IS NULL
GO
INSERT INTO X (myid, myvar) VALUES (NULL, 'test')
--
David Portas
SQL Server MVP
--|||> The INSERT cannot be modified because it is in application code that
cannot
> be modified.
So presumably something has changed since the application went into
production (I'm assuming that it worked at one time)? What was changed and
why? If you explain what you are actually trying to achieve then maybe
someone can suggest some alternatives that won't break your application
code. It appears that the problem isn't in the INSERT statement but
elsewhere.
--
David Portas
SQL Server MVP
--

IDENTITY column

Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
The application is trying to run this insert into the table:
insert into X (MYID, MYVAR) values (null, 'test').
This error is being returned:
Cannot insert explicit value for identity column in table 'X' when
IDENTITY_INSERT is set to OFF.
I cannot modify the application code insert. Is there a way to configure the
IDENTITY to replace the null with the IDENTITY value and not error on the
attempt.
I know a trigger can be written to do this, but I want to know if something
can be configured in the IDENTITY or TABLE.
Thanks!!
Bevo
I don't believe a trigger will work for you either Bevo. You are trying to
push a null into a NOT NULL column. This constraint should be checked
before the data is modified and the Triggers can even fire.
Is there a reason you can't modify the INSERT statement to something like:
INSERT INTO X (MyVar) VALUES ('test')
Rick Sawtell
MCT, MCSD, MCDBA
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> The application is trying to run this insert into the table:
> insert into X (MYID, MYVAR) values (null, 'test').
> This error is being returned:
> Cannot insert explicit value for identity column in table 'X' when
> IDENTITY_INSERT is set to OFF.
>
> I cannot modify the application code insert. Is there a way to configure
the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
> I know a trigger can be written to do this, but I want to know if
something
> can be configured in the IDENTITY or TABLE.
> Thanks!!
> Bevo
|||The table should replace the NULL value with an IDENTITY value, we do not
want NULL inserted.
The INSERT cannot be modified because it is in application code that cannot
be modified.
"Rick Sawtell" wrote:

> I don't believe a trigger will work for you either Bevo. You are trying to
> push a null into a NOT NULL column. This constraint should be checked
> before the data is modified and the Triggers can even fire.
> Is there a reason you can't modify the INSERT statement to something like:
> INSERT INTO X (MyVar) VALUES ('test')
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> the
> something
>
>
|||> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
A small but significant correction: IDENTITY isn't a datatype.

> Is there a way to configure the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
No, not if you try to insert an explicit NULL - IDENTITY columns are
implicitly non-nullable.

> I know a trigger can be written to do this
The only solution I can think of using a trigger is to create an INSTEAD OF
trigger on a view over this table:
CREATE VIEW X1 (myid, myvar)
AS SELECT myid+0 , myvar
FROM X
GO
CREATE TRIGGER trg_x ON x1 INSTEAD OF INSERT
AS
INSERT INTO x (myvar)
SELECT myvar
FROM Inserted
GO
INSERT INTO X1 (myid, myvar) VALUES (NULL,'test')
Then you'd have to insert into the view rather than the table. You could
always rename the table and call the view X instead. That would avoid a
change to your INSERT statement but would likely break some other code
elsewhere.

> I cannot modify the application code insert.
!!? Even when it won't work? That seems a bizzare and unreasonable.
David Portas
SQL Server MVP
|||Here's another possibility using a trigger:
ALTER TABLE X DROP COLUMN myid
ALTER TABLE X ADD myid INTEGER NULL -- Not IDENTITY!
ALTER TABLE X ADD new_myid INTEGER IDENTITY -- IDENTITY
GO
CREATE TRIGGER trg_x ON X FOR INSERT
AS
UPDATE X SET myid = new_myid
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE new_myid = X.new_myid)
AND myid IS NULL
GO
INSERT INTO X (myid, myvar) VALUES (NULL, 'test')
David Portas
SQL Server MVP
|||> The INSERT cannot be modified because it is in application code that
cannot
> be modified.
So presumably something has changed since the application went into
production (I'm assuming that it worked at one time)? What was changed and
why? If you explain what you are actually trying to achieve then maybe
someone can suggest some alternatives that won't break your application
code. It appears that the problem isn't in the INSERT statement but
elsewhere.
David Portas
SQL Server MVP
|||Why you ask. Well, the application is currently supported on MySQL and
Oracle, we are attempting to adopt SQL Server support.
There have been many issues which required application code and database
changes to support SQL Server. For example, the rowsize and index size limits
required changes. Reserved words, such as IDENTITY, and the lack of support
for key SQL functions, like CONCAT (posting on this as well). There are many
others...
This particular issue of the INSERT statement would require extensive
application changes, we view that solution as a last resort.
"David Portas" wrote:

> cannot
> So presumably something has changed since the application went into
> production (I'm assuming that it worked at one time)? What was changed and
> why? If you explain what you are actually trying to achieve then maybe
> someone can suggest some alternatives that won't break your application
> code. It appears that the problem isn't in the INSERT statement but
> elsewhere.
> --
> David Portas
> SQL Server MVP
> --
>
>

IDENTITY column

Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
The application is trying to run this insert into the table:
insert into X (MYID, MYVAR) values (null, 'test').
This error is being returned:
Cannot insert explicit value for identity column in table 'X' when
IDENTITY_INSERT is set to OFF.
I cannot modify the application code insert. Is there a way to configure the
IDENTITY to replace the null with the IDENTITY value and not error on the
attempt.
I know a trigger can be written to do this, but I want to know if something
can be configured in the IDENTITY or TABLE.
Thanks!!
BevoI don't believe a trigger will work for you either Bevo. You are trying to
push a null into a NOT NULL column. This constraint should be checked
before the data is modified and the Triggers can even fire.
Is there a reason you can't modify the INSERT statement to something like:
INSERT INTO X (MyVar) VALUES ('test')
Rick Sawtell
MCT, MCSD, MCDBA
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
> The application is trying to run this insert into the table:
> insert into X (MYID, MYVAR) values (null, 'test').
> This error is being returned:
> Cannot insert explicit value for identity column in table 'X' when
> IDENTITY_INSERT is set to OFF.
>
> I cannot modify the application code insert. Is there a way to configure
the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
> I know a trigger can be written to do this, but I want to know if
something
> can be configured in the IDENTITY or TABLE.
> Thanks!!
> Bevo|||The table should replace the NULL value with an IDENTITY value, we do not
want NULL inserted.
The INSERT cannot be modified because it is in application code that cannot
be modified.
"Rick Sawtell" wrote:

> I don't believe a trigger will work for you either Bevo. You are trying t
o
> push a null into a NOT NULL column. This constraint should be checked
> before the data is modified and the Triggers can even fire.
> Is there a reason you can't modify the INSERT statement to something like:
> INSERT INTO X (MyVar) VALUES ('test')
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:DBE9A6EB-4343-44FC-875C-9BFAF633D9F1@.microsoft.com...
> the
> something
>
>|||> Table X has column MYID as type IDENTITY (1,1) and MYVAR as varchar(255).
A small but significant correction: IDENTITY isn't a datatype.

> Is there a way to configure the
> IDENTITY to replace the null with the IDENTITY value and not error on the
> attempt.
No, not if you try to insert an explicit NULL - IDENTITY columns are
implicitly non-nullable.

> I know a trigger can be written to do this
The only solution I can think of using a trigger is to create an INSTEAD OF
trigger on a view over this table:
CREATE VIEW X1 (myid, myvar)
AS SELECT myid+0 , myvar
FROM X
GO
CREATE TRIGGER trg_x ON x1 INSTEAD OF INSERT
AS
INSERT INTO x (myvar)
SELECT myvar
FROM Inserted
GO
INSERT INTO X1 (myid, myvar) VALUES (NULL,'test')
Then you'd have to insert into the view rather than the table. You could
always rename the table and call the view X instead. That would avoid a
change to your INSERT statement but would likely break some other code
elsewhere.

> I cannot modify the application code insert.
!!' Even when it won't work? That seems a bizzare and unreasonable.
David Portas
SQL Server MVP
--|||Here's another possibility using a trigger:
ALTER TABLE X DROP COLUMN myid
ALTER TABLE X ADD myid INTEGER NULL -- Not IDENTITY!
ALTER TABLE X ADD new_myid INTEGER IDENTITY -- IDENTITY
GO
CREATE TRIGGER trg_x ON X FOR INSERT
AS
UPDATE X SET myid = new_myid
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE new_myid = X.new_myid)
AND myid IS NULL
GO
INSERT INTO X (myid, myvar) VALUES (NULL, 'test')
David Portas
SQL Server MVP
--|||> The INSERT cannot be modified because it is in application code that
cannot
> be modified.
So presumably something has changed since the application went into
production (I'm assuming that it worked at one time)? What was changed and
why? If you explain what you are actually trying to achieve then maybe
someone can suggest some alternatives that won't break your application
code. It appears that the problem isn't in the INSERT statement but
elsewhere.
David Portas
SQL Server MVP
--|||Why you ask. Well, the application is currently supported on mysql and
Oracle, we are attempting to adopt SQL Server support.
There have been many issues which required application code and database
changes to support SQL Server. For example, the rowsize and index size limit
s
required changes. Reserved words, such as IDENTITY, and the lack of support
for key SQL functions, like CONCAT (posting on this as well). There are many
others...
This particular issue of the INSERT statement would require extensive
application changes, we view that solution as a last resort.
"David Portas" wrote:

> cannot
> So presumably something has changed since the application went into
> production (I'm assuming that it worked at one time)? What was changed and
> why? If you explain what you are actually trying to achieve then maybe
> someone can suggest some alternatives that won't break your application
> code. It appears that the problem isn't in the INSERT statement but
> elsewhere.
> --
> David Portas
> SQL Server MVP
> --
>
>