Hello,
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbnamesysname
declare @.ret_codeint
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.
AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.
|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.
Showing posts with label cant. Show all posts
Showing posts with label cant. Show all posts
Tuesday, March 20, 2012
[TRANSACT] Can't switch from database to another
Hello,
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbname sysname
declare @.ret_code int
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbname sysname
declare @.ret_code int
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.
[TRANSACT] Can't switch from database to another
Hello,
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbname sysname
declare @.ret_code int
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.sql
I encounter a problem with a small portion of sqlcode. I
try to go on database using "use dbname" but i always stay
in master. I execute script with the sa user.
declare @.dbname sysname
declare @.ret_code int
DECLARE db_cursor CURSOR FOR
select
name
from
master..sysdatabases
where
name not in ('master', 'model', 'tempdb', 'pubs',
'Northwind')
-- Open cursor
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE @.@.FETCH_STATUS = 0
BEGIN
execute ('use ' + @.dbname)
execute ('select db_name()')
Thank's for help,
Pierrot.AFAIK, executing just open up a session to excute something, when executing
the next statement you are in another Session, try to combine these two
together with
EXECUTE ('Use Northwind;Select DB_NAME()')
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"pierrot" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.|||As Jens indicates each EXECUTE is its own session, so to do this in an
execute you must place all of the statements together...
Depending on what you are trying to do you could also use the 3part name
Declare @.str varchar(100)
Select @.str = 'select * from ' + @.dbname + '.dbo.thetable'
execute (@.str)
for System Sps you could also do
Declare @.str varchar(100)
Select @.str = 'Execute ' + @.dbname + '.dbo.sp_spaceused'
execute (@.str)
If you wish to do something for each database try sp_foreachdatabase ( I
think is the name of the sp.)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"pierrot" <anonymous@.discussions.microsoft.com> wrote in message
news:104e01c5419c$afcb5850$a401280a@.phx.gbl...
> Hello,
> I encounter a problem with a small portion of sqlcode. I
> try to go on database using "use dbname" but i always stay
> in master. I execute script with the sa user.
> declare @.dbname sysname
> declare @.ret_code int
> DECLARE db_cursor CURSOR FOR
> select
> name
> from
> master..sysdatabases
> where
> name not in ('master', 'model', 'tempdb', 'pubs',
> 'Northwind')
> -- Open cursor
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> execute ('use ' + @.dbname)
> execute ('select db_name()')
> Thank's for help,
> Pierrot.sql
Sunday, March 11, 2012
[SQL 2005] Transactionnal replication via FTP
Here is the context, in short. I have 2 remote sites with SQL Server 2005.
Each server can't see the other one. I need to replicate data between them.
I use MSSQL Management Studio with SQL Server Developer Edition.
When creating a publication, I can say that I want the snapshot to be put on
a FTP in the publication option. no problem here. When I want to create a
subscribtion, I must give the publication to use, and there is the problem
because I can't see the remote server.
The only thing I need to do is to say the subscriber to take the snapshot on
the FTP, but I've not found how I can do this.
If someone can help me ^^
Thanks in advance.
P.S. : sorry for my bad english, I'm not very good with foreign language :p
Divid,
you'll need some sort of connectivity. This could be a VPN, HTTPS or just
TCP/IP (non-trusted). Somehow, the subscriber will need to contact the
publisher. If you want a purely FTP solution then you must use another
technology.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||VPN, in a wonderfull dream. Maybe one day ... or not :p
Merge replication can use HTTPS but the merge replication is not suitable in
my situation. Transactionnal replication can't use HTTPS as far as I know.
For the TCP/IP (non trusted), can you explain about this a little more. I
don't know what is it so maybe it could do the work.
Thanks again.
|||David,
this should help a bit:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||It doesn't really help. I've add the alias but I can't add a "virtual"
publisher (the alias), and then I can't subscribe it, in Management Studio.
Seems that I'm only able to work on local because local publishing/local
subscribtion are the only options in Management Studio.
|||David,
unfortunately I can't repro this at the moment as we only have one SQL
Server 2005 installation. As soon as another is added I'll give it a go, but
hopefully someone else will take up the thread before then.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Each server can't see the other one. I need to replicate data between them.
I use MSSQL Management Studio with SQL Server Developer Edition.
When creating a publication, I can say that I want the snapshot to be put on
a FTP in the publication option. no problem here. When I want to create a
subscribtion, I must give the publication to use, and there is the problem
because I can't see the remote server.
The only thing I need to do is to say the subscriber to take the snapshot on
the FTP, but I've not found how I can do this.
If someone can help me ^^
Thanks in advance.
P.S. : sorry for my bad english, I'm not very good with foreign language :p
Divid,
you'll need some sort of connectivity. This could be a VPN, HTTPS or just
TCP/IP (non-trusted). Somehow, the subscriber will need to contact the
publisher. If you want a purely FTP solution then you must use another
technology.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||VPN, in a wonderfull dream. Maybe one day ... or not :p
Merge replication can use HTTPS but the merge replication is not suitable in
my situation. Transactionnal replication can't use HTTPS as far as I know.
For the TCP/IP (non trusted), can you explain about this a little more. I
don't know what is it so maybe it could do the work.
Thanks again.
|||David,
this should help a bit:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||It doesn't really help. I've add the alias but I can't add a "virtual"
publisher (the alias), and then I can't subscribe it, in Management Studio.
Seems that I'm only able to work on local because local publishing/local
subscribtion are the only options in Management Studio.
|||David,
unfortunately I can't repro this at the moment as we only have one SQL
Server 2005 installation. As soon as another is added I'll give it a go, but
hopefully someone else will take up the thread before then.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)