Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Who to switch database within stored proc.

6 posts in General Discussion Last posting was on 2013-01-11 20:05:09.0Z
Sid. Posted on 2013-01-09 21:17:07.0Z
Sender: 643f.50edd932.1804289383@sybase.com
From: Sid.
Newsgroups: sybase.public.ase.general
Subject: Who to switch database within stored proc.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50edde53.64ea.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 9 Jan 2013 13:17:07 -0800
X-Trace: forums-1-dub 1357766227 172.20.134.41 (9 Jan 2013 13:17:07 -0800)
X-Original-Trace: 9 Jan 2013 13:17:07 -0800, 172.20.134.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31639
Article PK: 1158875

Is there anyway I can switch database while executing
dynamic sql from within a stored proc.

exec ( 'use dbname set replication off delete from
dbname..tablename')

Thanks,
Sid.


Sid. Posted on 2013-01-09 21:19:43.0Z
Sender: 643f.50edd932.1804289383@sybase.com
From: Sid.
Newsgroups: sybase.public.ase.general
Subject: How to switch database within stored proc.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50eddeef.64ff.1681692777@sybase.com>
References: <50edde53.64ea.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 9 Jan 2013 13:19:43 -0800
X-Trace: forums-1-dub 1357766383 172.20.134.41 (9 Jan 2013 13:19:43 -0800)
X-Original-Trace: 9 Jan 2013 13:19:43 -0800, 172.20.134.41
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31640
Article PK: 1158874


> Is there anyway I can switch database while executing
> dynamic sql from within a stored proc.
>
> exec ( 'use dbname set replication off delete from
> dbname..tablename')
>
> Thanks,
> Sid.

I am generating the dynamic SQL from another database.


Rob V Posted on 2013-01-09 21:24:40.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to switch database within stored proc.
References: <50edde53.64ea.1681692777@sybase.com> <50eddeef.64ff.1681692777@sybase.com>
In-Reply-To: <50eddeef.64ff.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50ede018$1@forums-1-dub>
Date: 9 Jan 2013 13:24:40 -0800
X-Trace: forums-1-dub 1357766680 172.20.134.152 (9 Jan 2013 13:24:40 -0800)
X-Original-Trace: 9 Jan 2013 13:24:40 -0800, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31641
Article PK: 1158872


On 09-Jan-2013 22:19, Sid. wrote:
>> Is there anyway I can switch database while executing
>> dynamic sql from within a stored proc.
>>
>> exec ( 'use dbname set replication off delete from
>> dbname..tablename')
>>
>> Thanks,
>> Sid.
>
> I am generating the dynamic SQL from another database.
>

You can do this indirectly by creating a procedure named sp_something in
sybsystemprocs and then executing it as:

execute mydb..sp_something -- executes in mydb
execute yourdb..sp_something -- executes in yourdb

These statements can be used inside a stored proc.
This is also described in more detail at
http://www.sypron.nl/new_ssp_txt.html, though the first example (for
sp_dboption) has long been addressed.


--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Rob V Posted on 2013-01-09 21:38:35.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to switch database within stored proc.
References: <50edde53.64ea.1681692777@sybase.com> <50eddeef.64ff.1681692777@sybase.com>
In-Reply-To: <50eddeef.64ff.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50ede35b$1@forums-1-dub>
Date: 9 Jan 2013 13:38:35 -0800
X-Trace: forums-1-dub 1357767515 172.20.134.152 (9 Jan 2013 13:38:35 -0800)
X-Original-Trace: 9 Jan 2013 13:38:35 -0800, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31642
Article PK: 1158873


On 09-Jan-2013 22:19, Sid. wrote:
>> Is there anyway I can switch database while executing
>> dynamic sql from within a stored proc.
>>
>> exec ( 'use dbname set replication off delete from
>> dbname..tablename')
>>
>> Thanks,
>> Sid.
>
> I am generating the dynamic SQL from another database.
>

Apart from my earlier suggestion, of course you can also do it this way:

declare @c varchar(200), @db varchar(30)
set @db = 'mydb'
set @c = 'delete from ' + @db + '..mytable'
exec(@c)

... so you can customize your SQL statement as much as you like.


--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Sid. Posted on 2013-01-11 17:56:24.0Z
Sender: f2e.50f050e8.1804289383@sybase.com
From: Sid.
Newsgroups: sybase.public.ase.general
Subject: Re: How to switch database within stored proc.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50f05248.f7f.1681692777@sybase.com>
References: <50ede35b$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 11 Jan 2013 09:56:24 -0800
X-Trace: forums-1-dub 1357926984 172.20.134.41 (11 Jan 2013 09:56:24 -0800)
X-Original-Trace: 11 Jan 2013 09:56:24 -0800, 172.20.134.41
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31644
Article PK: 1158940

That is exactly what I am doing. Sqltext is being created
based on bunch of parameters from a config table. For not
being able to test in real replication environment I want to
make sure this works.

set replication off
exec (delete statement )

I want the replication to be off for this delete statement.
Will it do that?

Thanks,
Sid.

> On 09-Jan-2013 22:19, Sid. wrote:
> >> Is there anyway I can switch database while executing
> >> dynamic sql from within a stored proc.
> >>
> >> exec ( 'use dbname set replication off delete from
> >> dbname..tablename')
> >>
> >> Thanks,
> >> Sid.
> >
> > I am generating the dynamic SQL from another database.
> >
>
> Apart from my earlier suggestion, of course you can also
> do it this way:
>
> declare @c varchar(200), @db varchar(30)
> set @db = 'mydb'
> set @c = 'delete from ' + @db + '..mytable'
> exec(@c)
>
> ... so you can customize your SQL statement as much as you
> like.
>
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------


Rob V Posted on 2013-01-11 20:05:09.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to switch database within stored proc.
References: <50ede35b$1@forums-1-dub> <50f05248.f7f.1681692777@sybase.com>
In-Reply-To: <50f05248.f7f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50f07075$1@forums-1-dub>
Date: 11 Jan 2013 12:05:09 -0800
X-Trace: forums-1-dub 1357934709 172.20.134.152 (11 Jan 2013 12:05:09 -0800)
X-Original-Trace: 11 Jan 2013 12:05:09 -0800, vip152.sybase.com
Lines: 68
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31645
Article PK: 1158948

Yes, it should.

R.

On 11-Jan-2013 18:56, Sid. wrote:
> That is exactly what I am doing. Sqltext is being created
> based on bunch of parameters from a config table. For not
> being able to test in real replication environment I want to
> make sure this works.
>
> set replication off
> exec (delete statement )
>
> I want the replication to be off for this delete statement.
> Will it do that?
>
> Thanks,
> Sid.
>
>> On 09-Jan-2013 22:19, Sid. wrote:
>>>> Is there anyway I can switch database while executing
>>>> dynamic sql from within a stored proc.
>>>>
>>>> exec ( 'use dbname set replication off delete from
>>>> dbname..tablename')
>>>>
>>>> Thanks,
>>>> Sid.
>>>
>>> I am generating the dynamic SQL from another database.
>>>
>>
>> Apart from my earlier suggestion, of course you can also
>> do it this way:
>>
>> declare @c varchar(200), @db varchar(30)
>> set @db = 'mydb'
>> set @c = 'delete from ' + @db + '..mytable'
>> exec(@c)
>>
>> ... so you can customize your SQL statement as much as you
>> like.
>>
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide"
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------