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 @@Rowcount = .... problem

Hello again everyone....

Ok here's my problem... This is definetly the strangest problem Ive had yet in my coding career.... anyways here it is:

I have a stored procedure which keeps a total number of hits for specific pages:

Procedure CMRC_Hits_Pages_Temp_Update
@.Transaction nvarchar(20),
@.Hits int = NULL,
 @.Page nvarchar(50) = NULL
AS
IF @.Transaction = 'Delete'
BEGIN
DELETE FROM CMRC_Hits_Pages_Temp
END
IF @.Transaction = 'Add'
BEGIN
CREATE TABLE #TempTableUpdate
(
 Hits int
)
INSERT INTO #TempTableUpdate
(
 Hits
)
SELECT
 Hits
FROM
 CMRC_Hits_Pages_Details
WHERE
 Page = @.Page
SELECT
 Hits
FROM
 #TempTableUpdate
IF @.@.Rowcount > 0
BEGIN
UPDATE CMRC_Hits_Pages_Temp
SET
 Hits = Hits + @.Hits
WHERE
 Page = @.Page
END
IF @.@.Rowcount < 1
BEGIN
INSERT INTO CMRC_Hits_Pages_Temp
(
 Hits,
 Page
)
VALUES
(
 @.Hits,
 @.Page
)
END
END

I've written it so if there hasn't been an entry for @.Page, make a new one.... And if there is an entry allready for @.Page, add @.Hits to Hits.

Here's the strange part. When I run it in Query Analyzer (so I know there isn't a problem with my pages code), it works fine when I send @.Page a value of 'Default' (As in my default page). But when I put any other value (ei. 'ProductsList', ProductDetails', 'test', 'Defauls') it doesn't work. It creates a new record even if there was a record for that page allready. I've tried erasing everything from the table over and over to give it a fresh start and it still only works for 'Default.'

I've tried every length of string possible thinking it may be the length, same problem.

It makes no sense to me why specific letters could make any difference in what this procedure does. A string is a string, right? Why should one string be more recognizable than another? Again, the most confusing thing I've enountered yet in my coding career.

I seem to always run into problems and think "this makes no sense" and then come to figure "Ohhh... thats whats wrong..." But this problem here is definetely the cream... It makes NO sense....

Thank you to whomever can solve this mystery.... (If it is much of one...)

Just incase the Table info is important:

I have two Columns: Hits, int (4) & Page, nvarchar (50)

-AlecSorry, if you took your time to read this problem, but I figured it out.....

Again, sorry if you read that long story...

Thank you though for your time anyways...

-Alec|||... ok what was the problem again? and what was the solution?

Identity ranges

Using merge replication, I've decided to change over to identity ranges
(versus uniqueidentifiers). At the publisher, I'm setting a range on the
tables that would be consistent with the records expected not to be exceeded
in the database. Since these values are for an entire state of consumers, I
set the publisher range to 500,000 for that consumer table.
Three questions:
1. Does the publisher range sound excessive and would I be better off using
the auto-range to up that value or should I err on the high side initially?
2. On the subscriber side (all PPC), the normal range of records that would
be pushed would probably be 3000-5000 and the actual changes made would
probably only amount to about 500 per day per PPC. What would be a safe
range on the subscriber side?
3. Does it really matter how WIDE the range is set?
You really need to consider how long the PPC can go without a sync. Can
they stay disconnected for 5 days, 10, longer? Do some calculating and make
sure that not only the range is wide enough, but that the threshold is low
enough so they receive a new range to cover the next worst case senario.
One thing you can guarantee is that the user are not forced to sync
everyday...they probably will not.
"Earl" wrote:

> Using merge replication, I've decided to change over to identity ranges
> (versus uniqueidentifiers). At the publisher, I'm setting a range on the
> tables that would be consistent with the records expected not to be exceeded
> in the database. Since these values are for an entire state of consumers, I
> set the publisher range to 500,000 for that consumer table.
> Three questions:
> 1. Does the publisher range sound excessive and would I be better off using
> the auto-range to up that value or should I err on the high side initially?
> 2. On the subscriber side (all PPC), the normal range of records that would
> be pushed would probably be 3000-5000 and the actual changes made would
> probably only amount to about 500 per day per PPC. What would be a safe
> range on the subscriber side?
> 3. Does it really matter how WIDE the range is set?
>
>
|||Thanks Brian, those are good thoughts. A couple of followup questions: What
would the criteria be to ensure that the "range is wide enough"? I didn't
really understand about the threshold being "low enough".
"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...[vbcol=seagreen]
> You really need to consider how long the PPC can go without a sync. Can
> they stay disconnected for 5 days, 10, longer? Do some calculating and
> make
> sure that not only the range is wide enough, but that the threshold is low
> enough so they receive a new range to cover the next worst case senario.
> One thing you can guarantee is that the user are not forced to sync
> everyday...they probably will not.
>
>
> "Earl" wrote:
|||IMHO best way is to give range of 10.000.000 or even hundred million
and forget about ranges, tresholds etc. forever.
Max identity is something like 9223372036854775807, so why to be
worried about ranges.
Is there any drawback with this approach?
Pagus
On Fri, 12 Nov 2004 10:13:23 -0500, "Earl"
<brikshoe@.newsgroups.nospam> wrote:
[vbcol=seagreen]
>Thanks Brian, those are good thoughts. A couple of followup questions: What
>would the criteria be to ensure that the "range is wide enough"? I didn't
>really understand about the threshold being "low enough".
>"Brian Reuter" <BrianReuter@.discussions.microsoft.com> wrote in message
>news:885F4F00-0793-478F-B363-7FCCF97C97C9@.microsoft.com...
|||FAO Earl - Pagus is talking about BigInts (8 bytes) and
not Ints (4 bytes), where the range is +/-2billion or so.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I was thinking that range wouldn't work :=)
I want that replication book, can I get that as an ebook too (I didnt' see
anything on the site)?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5e3f01c4c999$7e3be410$a601280a@.phx.gbl...
> FAO Earl - Pagus is talking about BigInts (8 bytes) and
> not Ints (4 bytes), where the range is +/-2billion or so.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
the book is now available - I've read a draft and it looks v.good.
Hilary mentioned something about internet updates, but I don't recall
hearing about it as an ebook. Hopefully he'll see this thread and reply to
you.
Rgds,
Paul Ibison, SQL Server MVP
|||No, ebook. The merge volume may be released as an ebook, but its future is
yet undecided.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Ot8sppbyEHA.2568@.TK2MSFTNGP11.phx.gbl...
> Earl,
> the book is now available - I've read a draft and it looks v.good.
> Hilary mentioned something about internet updates, but I don't recall
> hearing about it as an ebook. Hopefully he'll see this thread and reply to
> you.
> Rgds,
> Paul Ibison, SQL Server MVP
>

Identity range when rows already exist

I've created a merge publication with automatic range management. Insert
fails because the ranges assigned have already been used. How do I specify
that I want the new identity ranges to start above those which have already
been used?
I created this publication by backing up my production database and
restoring it to my test database. Then I created the publication on my test
database by manually editing the auto-generated script for creating the
publication on the production database. I don't know if this is the reason
things don't work out as I want them to.
I think you would be best to drop this publication and its subscriptions and
recreate from start.
If you are a masochist you can do the following.
Look in your distributor for a table called MSrepl_identity_range. The
highest range is the range which is deployed to one of your subscribers. You
can bump this value up to give yourself a cushion.
For instance if the highest range is 10000, bump it up to 20000, which will
be the next value assigned.
Now go to your problem subscriber and fix the table there. Use dbcc
checkident('tablename') to determine what the current range is, and then
reseed to the value you found on your publisher's distribution database
MSrepl_identity_range table.
Now issue a sp_help 'problemTableName' to get the name of the check
constraint used to restrict the range of possible values acceptable for this
table. script out the check constraint and recreate it with a set of values
which matches the range you assigned with the checkident reseed statement.
If you are really feeling like punishing yourself you might want to read
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel" <daXniel_kriXstensXen_@.hotmail.com (remove the Xs)> wrote in
message news:92B2698A-DE7C-485C-9381-78295E49A335@.microsoft.com...
> I've created a merge publication with automatic range management. Insert
> fails because the ranges assigned have already been used. How do I specify
> that I want the new identity ranges to start above those which have
already
> been used?
> I created this publication by backing up my production database and
> restoring it to my test database. Then I created the publication on my
test
> database by manually editing the auto-generated script for creating the
> publication on the production database. I don't know if this is the reason
> things don't work out as I want them to.
|||"Hilary Cotter" wrote:

> I think you would be best to drop this publication and its subscriptions and
> recreate from start.
I already did that. Perhaps the problem was that I created the publication
using the script generated by EM. For each merge article it did:
exec sp_addmergearticle ... @.article = [tableName] ...
go
To solve the problem, for all merge articles for which I use automatic range
management I added:
declare @.NewID int
Select @.newID = max(ID)+1 FROM [tableName]
DBCC CHECKIDENT([tableName],RESEED,@.newID)
exec sp_addmergearticle @.article = [tableName] ...
go
That is, I reseed the identity for each table before adding it to the
publication. It seems to work.

>If you are really feeling like punishing yourself you might want to read
> http://www.simple-talk.com/2005/07/05/replication/
Thanks I did that. You got all these great articles scattered all over the
net. But your book about merge replication is due any week now, right? It
would be nice to have the information gathered in one place

Identity Primary Field in Merge Replication

Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.

What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e

On Server 1 - Values start from 1 then 2,3,4 etc etc

Server 2 - 24001, 24002, 24003 etc etc

Server 3 - 46001, 46002, 46003 etc etc

Server 4 - 68001, 68002, 68003

My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?

Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.

cheers

I wouldn;t advice you use only this for your primary key.

Check out this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423523&SiteID=1

Identity Primary Field in Merge Replication

Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.

What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e

On Server 1 - Values start from 1 then 2,3,4 etc etc

Server 2 - 24001, 24002, 24003 etc etc

Server 3 - 46001, 46002, 46003 etc etc

Server 4 - 68001, 68002, 68003

My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?

Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.

cheers

I wouldn;t advice you use only this for your primary key.

Check out this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1423523&SiteID=1