Having Problems enumerating publishers

Hi,
i want to enumerate all publishers from a sql server instance.
Sometimes it works and i get the publishers, sometimes not, i get an empty
queryresults-list with just the column - names.
I tried publisher and publisher2 object:
Syntax
object.EnumPublications( Database , ReplicationType , AgentLogin ,
bSecurityCheck) as QueryResults
e.g.:
dim queryres as queryresult
Dim serverpub As SQLDMO.SQLServer
Dim publisher2 As publisher2
Set publisher2 = serverpub.Replication.Publisher
Set queryres = publisher2.EnumPublications(SQLDMORepType_Transact ionalMerge,
"%", "", False)
sometimes queryres cotains publishers, sometimes not.
the same problem occures, using sp_MSenumallpublicastions in Queryanalyzer.
Is this a known problem?
What can i do?
Regards
Georg Notter
I am confused as to what you are trying to enumerate - publishers or
publications?
If you want to enumerate publishers use sp_helpdistpublisher @.check_user = 1
or this sqldmo snippet
set objSQLServer=CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure=True
objSQLServer.Connect "."
for each Publisher in
objSQLServer.Replication.Distributor.DistributionP ublishers
wscript.echo Publisher.Name
next
If you want to enumerate all publications I'd try this:
exec distribution..sp_MShelp_publication @.@.servername
or
set objSQLServer=CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure=True
objSQLServer.Connect "."
for each Publisher in
objSQLServer.Replication.Distributor.DistributionP ublishers
wscript.echo Publisher.Name
next
for each Publisher in
objSQLServer.Replication.Distributor.DistributionP ublishers
for each publication in Publisher.DistributionPublications
wscript.echo Publication.Name
next
next
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
"georg notter" <georgnotter@.discussions.microsoft.com> wrote in message
news:ED795200-E450-4328-BF6F-384BBD8F2674@.microsoft.com...
> Hi,
> i want to enumerate all publishers from a sql server instance.
> Sometimes it works and i get the publishers, sometimes not, i get an empty
> queryresults-list with just the column - names.
> I tried publisher and publisher2 object:
> Syntax
> object.EnumPublications( Database , ReplicationType , AgentLogin ,
> bSecurityCheck) as QueryResults
>
> e.g.:
> dim queryres as queryresult
> Dim serverpub As SQLDMO.SQLServer
> Dim publisher2 As publisher2
> Set publisher2 = serverpub.Replication.Publisher
> Set queryres =
publisher2.EnumPublications(SQLDMORepType_Transact ionalMerge,
> "%", "", False)
> sometimes queryres cotains publishers, sometimes not.
> the same problem occures, using sp_MSenumallpublicastions in
Queryanalyzer.
> Is this a known problem?
> What can i do?
> Regards
> Georg Notter
>
sql

Hardcode subtotal

Hello,

Can I hardcode matrix subtotal?

For instance, I want to hardcode "100%" in the following Sales report:

2007 Year

Quantity

Phones 21%

Notebooks 30%

Total 100%

Please don't ask me why I need to do that, it's a complex MDX query and the report is much more complex than I depicted.

I just need to statically write 100% in subtotal.

Write the following expression

Code Snippet

=100 & "%"

in the text box where u want to display 100%.

Best Regards,

Rajiv

|||

RajivDotNet wrote:

Write the following expression

Code Snippet

=100 & "%"

in the text box where u want to display 100%.

Best Regards,

Rajiv

I would just like to mention the InScope function. If you have a calculation in that column to determine the percentage, you can just add an IIf and InScope function to the expression to determine whether you are within the bounds of your row group, if you are within bounds then do your normal percentage calculation, if you are out of bounds then just return "100" (and use the code p0 in the Format property so that things are formatted correctly when exported to Excel).

|||

Dear Rajiv and Sluggy,

Thank you very much for your quick response.

Your approach worked!

Hard Stored Procedure?

I'm writing a stored procedure that will look for 'string' in the 'n'th
column. For instance,
exec QueryTable 1,'aString'
...should look for the string 'aString' in the first column of a specific
table. Does anyone know how to do this in a generic way, so that I don't hav
e
a big IF statement where I write the query once for each column?Try this, salt to taste (and add better exception handling):
create procedure ap_get_rows
@.table sysname,
@.field int,
@.value varchar(4000)
as
declare @.fieldname sysname
declare @.sql varchar(4000)
select @.fieldname = [name]
from syscolumns
where id = object_id(@.table) and colid = @.field
set @.sql = 'select * from [' + @.table + '] where [' + @.fieldname + '] = '''
+ @.value + ''''
print @.sql
exec (@.sql)
go
Mike|||courtesy of Steve Kass:
http://www.users.drew.edu/skass/sql...lTables.sql.txt
-oj
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:EAF72EFC-B55A-4F56-B6AF-DF48BB05177A@.microsoft.com...
> I'm writing a stored procedure that will look for 'string' in the 'n'th
> column. For instance,
> exec QueryTable 1,'aString'
> ...should look for the string 'aString' in the first column of a specific
> table. Does anyone know how to do this in a generic way, so that I don't
> have
> a big IF statement where I write the query once for each column?|||I forgot the disclaimer: "This stored procedure uses dynamic SQL which is
known to perform less efficiently than compiled SQL statements. Use with
caution especially in performance sensitive operations."
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:%23LXQyFeRFHA.4028@.tk2msftngp13.phx.gbl...
> Try this, salt to taste (and add better exception handling):
>
> create procedure ap_get_rows
> @.table sysname,
> @.field int,
> @.value varchar(4000)
> as
> declare @.fieldname sysname
> declare @.sql varchar(4000)
> select @.fieldname = [name]
> from syscolumns
> where id = object_id(@.table) and colid = @.field
> set @.sql = 'select * from [' + @.table + '] where [' + @.fieldname + '] =
'''
> + @.value + ''''
> print @.sql
> exec (@.sql)
> go
>
> Mike
>|||You have to use dynamic sql.
Example:
use northwind
go
create procedure dbo.proc1
@.ts sysname = N'dbo',
@.tn sysname,
@.ordinal_position int,
@.value varchar(50)
as
set nocount on
declare @.sql nvarchar(4000)
declare @.cn sysname
select
@.cn = column_name
from
information_schema.columns
where
table_schema = @.ts
and table_name = @.tn
and ordinal_position = @.ordinal_position
if @.cn is not null
begin
set @.sql = N'select ' + quotename(@.cn) + N' from ' + quotename(@.ts) + '.' +
quotename(@.tn) + N' where ' + quotename(@.cn) + N' like ''%' + replace(@.value
,
'''', ''') + N'%'''
print @.sql
exec sp_executesql @.sql
end
go
exec dbo.proc1 @.tn = N'customers', @.ordinal_position = 2, @.value = 'La
maison d''Asie'
go
exec dbo.proc1 @.tn = N'employees', @.ordinal_position = 4, @.value =
'Representative'
go
drop procedure proc1
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Ken" wrote:

> I'm writing a stored procedure that will look for 'string' in the 'n'th
> column. For instance,
> exec QueryTable 1,'aString'
> ...should look for the string 'aString' in the first column of a specific
> table. Does anyone know how to do this in a generic way, so that I don't h
ave
> a big IF statement where I write the query once for each column?