Showing posts with label sql2005. Show all posts
Showing posts with label sql2005. Show all posts

Thursday, March 29, 2012

Dropping scripts into sql 2005

Something that really irritates me with sql2005 is when I try to drag and drop a script into sql2005 I have to relog basically. Say I'm dropping in 20 different scripts, I have to relog and select the database every time for each script. Sql2000 did not do this, I drag and drop in scripts and whatever database I was currently in, it would use. Is this just a setting that I need to change or is it a horrible "enhancement"?

Thanks for your help.

Hi,

no I think currently this is not possible, one query windows per query will be opened and prompt you for the information where to connect to.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Wednesday, March 21, 2012

drop table

I upgraded my system from Sql2000 to sql2005.

And now i have a problem.

I have job wich runs every minute and perform the following

select * into tabler_1 from .....

begin tran

if exists(select * from sysobjects where xtype ='u' and name = 'tabler')

drop table tabler

exec sp_rename tabler_1, tabler

commit

And on client side i have IIS which runs asp pages which connect to database with

ado connction using ODBC and runs procedures wich performs select * from tabler

and there is a problem they some times failes with error tabler does not exists.

It worked fine in sql2000

all connections opened with default setting.

(transaction isolation level read commited)

What abou this here:

if not exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler_1'

AND TABLE_TYPE = 'BASE_TABLE'

)

select * into tabler_1 from SomeTable

if exists

(

SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'tabler'

AND TABLE_TYPE = 'BASE_TABLE'

)

BEGIN

DROP TABLE tabler

EXEC sp_rename tabler_1, tabler

END

Make sure that you check the existence of the table before doing a SELECT INTO.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

It is not a problem.

it make drop very nice.

the problem is on the client which runs store procedure like this

create procedure test_sp

as

select * from tabler

go

OR

create procedure test_sp_d

as

exec ('select * from tabler')

go

when i droping table i do it in transaction so schema is locked

the procedures have to wait for commit and then select

it worked fine sql2000

sql

Monday, March 19, 2012

Drop replication question

Using SQL2005 SP2 transactional replication. Whenever we have a deploy to
production that contains DDL, I drop the publication and then drop the
subscription on the replicated server. Once the update is complete, I
recreate the publication with any changes that are necessary and then
re-create the subscription, and then the snapshot agent runs. Is that what
most people do?
This works fine, but I feel it is overkill. Is there a simpler way to
temporarily disable the replication until the DDL changes are made without
dropping and recreating the publication and subscription?
Is there any negative impact by my dropping and recreating the publication
and subscription.
Thanks
Ron
Many schema changes will be automatically replicated is the replicate_ddl
parameter is set to true for sp_addpublication and sp_addmergepublication.
There are some which you will have to do the drop subscriptions and drop
publications, for example changes to pk, adding defaults, identities, etc.
The problem with your approach is that there is add overhead and your
subscriber tables have to be recreated each time.
http://www.zetainteractive.com - Shift Happens!
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
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:A7ED2B66-F49D-4FD3-AD22-54CD8A54DAB9@.microsoft.com...
> Using SQL2005 SP2 transactional replication. Whenever we have a deploy to
> production that contains DDL, I drop the publication and then drop the
> subscription on the replicated server. Once the update is complete, I
> recreate the publication with any changes that are necessary and then
> re-create the subscription, and then the snapshot agent runs. Is that
> what
> most people do?
> This works fine, but I feel it is overkill. Is there a simpler way to
> temporarily disable the replication until the DDL changes are made without
> dropping and recreating the publication and subscription?
> Is there any negative impact by my dropping and recreating the publication
> and subscription.
> Thanks
> Ron
|||Just so I'm clear, I think what you're saying is that there isn't any
intermediate step such as disable or turn off replication temporarily, you
would need to drop subscription and publication.
We do have "replicate Schema Changes" = TRUE, however one of our deploy code
failed due to replication being active. I guess since not knowing exactly
what can and can't be replicated, it wouldn't hurt to simply drop publication
and subscription, perform the changes and then add the publication and
subscription back again. Since a deploy is done after hours and replication
currently only takes about 15 minutes to add publication and subscription as
well as run the snapshot agent, it sounds like it's the safest way to go as
the time it takes to recreate the publication and subscription isn't an issue
to us.
Thanks.
"Hilary Cotter" wrote:

> Many schema changes will be automatically replicated is the replicate_ddl
> parameter is set to true for sp_addpublication and sp_addmergepublication.
> There are some which you will have to do the drop subscriptions and drop
> publications, for example changes to pk, adding defaults, identities, etc.
> The problem with your approach is that there is add overhead and your
> subscriber tables have to be recreated each time.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:A7ED2B66-F49D-4FD3-AD22-54CD8A54DAB9@.microsoft.com...
>
>
|||Exactly, there is no step that makes replication disabled.
There are some schema changes which are not replicated as you have found
out. I would probably try the deploy code out in a test environment to find
out what breaks and then remove those articles from the publication, make
the change and then add them back in.
http://www.zetainteractive.com - Shift Happens!
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
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:57CDE6A7-E4C1-45D7-8F1F-02BCD8A763C6@.microsoft.com...[vbcol=seagreen]
> Just so I'm clear, I think what you're saying is that there isn't any
> intermediate step such as disable or turn off replication temporarily, you
> would need to drop subscription and publication.
> We do have "replicate Schema Changes" = TRUE, however one of our deploy
> code
> failed due to replication being active. I guess since not knowing exactly
> what can and can't be replicated, it wouldn't hurt to simply drop
> publication
> and subscription, perform the changes and then add the publication and
> subscription back again. Since a deploy is done after hours and
> replication
> currently only takes about 15 minutes to add publication and subscription
> as
> well as run the snapshot agent, it sounds like it's the safest way to go
> as
> the time it takes to recreate the publication and subscription isn't an
> issue
> to us.
> Thanks.
> "Hilary Cotter" wrote:
|||Thanks for your help and clarification.
"Hilary Cotter" wrote:

> Exactly, there is no step that makes replication disabled.
> There are some schema changes which are not replicated as you have found
> out. I would probably try the deploy code out in a test environment to find
> out what breaks and then remove those articles from the publication, make
> the change and then add them back in.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:57CDE6A7-E4C1-45D7-8F1F-02BCD8A763C6@.microsoft.com...
>
>