How to create second publisher record on the server?

Hello...
What we are trying to accomplish is to be able to replicate from Sql Server 2000 to some laptops running MSDE across the internet. These laptops are NOT part of our domain. I have set up a publication to allow for anonymous pull subscriptions for transa
ctional replication. Everything works fine now...except that for my test I had to setup/configure the laptop with a Hosts and LMHosts file so that it could resolve the server's name from its fully qualified name. This will be a pain for us to implement
and deploy....as we will have hundreds of client machines connecting to our server. The problem has to do with a sql server table that stores the name of the server publisher & distributor.
I noticed (via the sql server profiler tool) that when replication begins, there is some intial login/handshaking that occurs and one of the principal commands that gets executed is something like: exec sp_helpdistpublisher N'ServerName'. This stored pr
oc queries the MSDistPublisher table in the MSDB database. The stored proc returns a single row of data which includes our server's name...as it has been setup...it's machine name. This table does not include (obviously) a record that has the server's fu
lly qualified domain name (ServerName.Domain.Com).
My question is: is there a way to add an additional row of data and use the fully qualified domain name?
thanks for any help.
- DW
in your application, can't you hard code the publisher name with the FQDN
for the publisher/distributor?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DW" <DW@.discussions.microsoft.com> wrote in message
news:62391DE0-137B-46EE-9024-9E679829FD84@.microsoft.com...
> Hello...
> What we are trying to accomplish is to be able to replicate from Sql
Server 2000 to some laptops running MSDE across the internet. These laptops
are NOT part of our domain. I have set up a publication to allow for
anonymous pull subscriptions for transactional replication. Everything
works fine now...except that for my test I had to setup/configure the laptop
with a Hosts and LMHosts file so that it could resolve the server's name
from its fully qualified name. This will be a pain for us to implement and
deploy....as we will have hundreds of client machines connecting to our
server. The problem has to do with a sql server table that stores the name
of the server publisher & distributor.
> I noticed (via the sql server profiler tool) that when replication begins,
there is some intial login/handshaking that occurs and one of the principal
commands that gets executed is something like: exec sp_helpdistpublisher
N'ServerName'. This stored proc queries the MSDistPublisher table in the
MSDB database. The stored proc returns a single row of data which includes
our server's name...as it has been setup...it's machine name. This table
does not include (obviously) a record that has the server's fully qualified
domain name (ServerName.Domain.Com).
> My question is: is there a way to add an additional row of data and use
the fully qualified domain name?
> thanks for any help.
> - DW

How to create ODBC to SQLServer in a LAN accessed by another LAN through gateway ?

Hi all!
I have two LAN connected with a single PC with two network cards. IP
addresses are 10.0.0.x for the first LAN and 192.168.0.x for the
second. I have a SQLServer at 192.168.0.109 and I would like to define
an ODBC that point to it from the other network, for example on a PC
with IP 10.0.0.164. TCP/IP ping works fine from 10.0.0.164 to
192.168.0.109, but ODBC doesn't work!
Anyone suggest some ideas or point me in the right direction ?
Thanks in advance
Eugenio ALESSIEugenio,
Do you have firewall between this networks?
try from 10.0.0.164: telnet 192.168.0.109 1433.
--
Regards,
Rodrigo Fernandes
"eugenio alessi" wrote:
> Hi all!
> I have two LAN connected with a single PC with two network cards. IP
> addresses are 10.0.0.x for the first LAN and 192.168.0.x for the
> second. I have a SQLServer at 192.168.0.109 and I would like to define
> an ODBC that point to it from the other network, for example on a PC
> with IP 10.0.0.164. TCP/IP ping works fine from 10.0.0.164 to
> 192.168.0.109, but ODBC doesn't work!
> Anyone suggest some ideas or point me in the right direction ?
> Thanks in advance
> Eugenio ALESSI
>|||No, I haven't any firewall...
Is there some user access policy to connect to SQLServer (I use sa
nopassword...)
Eugenio

How to create categories tree with subcategories ?

Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:

News

--With country

--With world

How i write in Select Command ?

SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)

How do you connect categories and subcategories? Does db_subcategories have ID which says to which category that subcategory belongs?

If that is true, than:

SELECT db_categories.ID, [ID_subcategries], [Name_subcategories]FROM [db_subcategories], [db_categories]WHERE db_categories.ID = db_subcategories.IDOrder By 1Asc, 2Asc

|||

Hi,

this is my conncetion in web.config:

<

connectionStrings>

<

addname="ConnectionString"connectionString="Data Source=FS_AMILO_2065;Initial Catalog=sklep.mdf;Integrated Security=True"providerName="System.Data.SqlClient" />

</

connectionStrings>

I have in categories.aspx GridView I link to subcategories.asp

subcategories?id={0}

|||

You can use modification of above query to return all subcategories for given ID:

SELECT [ID_subcategries], [Name_subcategories]FROM [db_subcategories]WHERE db_subcategories.ID = @.catIDOrder By 1Asc, 2Asc

where you use @.catID to store supplied ID

|||

ID is a ID from categories ?

I mustMust declare the scalar variable "@.ID".

So i go to Pramters and ID is a QuertyString and ??

sql

How to create categories tree with subcategories ?

Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:

News

--With country

--With world

How i write in Select Command ?

SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)

That is not easy but the link below will take you in the right direction. Hope this helps.

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm#Figure1

How to create a second independent transaction inside a CLR stored procedure?

I use the context connection for the "normal" work in the CLR procedure.
But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.

What is the best way to do this inside a CLR procedure?

Thanx a lot

Hi!

Create another connection to the server and create a transaction on this second connection. Please note that since your main connection is inproc (“context connection=true” in the connection string), the second connection should be non-inproc (network. Of cause, you can connect to the same server.).

|||Thank you,

I tried this already. But I got an error when I call BeginTransaction on the second connection. When I remember correctly it as "Transcation already in use by another session". I call the procedure from t-sql inside a begin tran block

Any idea about that?

I will try to build a small example an post it here.|||

Hello,
I made a small example to reproduce the error.

CLR procedure
<Microsoft.SqlServer.Server.SqlProcedure()> _

Public Shared Sub Test(ByVal sConnectString As String)

Dim t As SqlClient.SqlTransaction = Nothing

Dim c As SqlClient.SqlConnection = Nothing

c = New SqlClient.SqlConnection(sConnectString)

c.Open()

t = c.BeginTransaction()

t.Commit()

c.Close()

End Sub

T-SQL caller:
begin tran
execute Test 'Server=(local);database=cpwerx;user Id=sa;password=xyz'
commit tran

Error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Cas':
System.Data.SqlClient.SqlException: Transaction context in use by another session.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.PropagateTransactionCookie(Byte[] cookie)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

|||

Hi!

Modify your connection string so that it has "Enlist=false" in it. Otherwise the connection inherits current transaction.

More on "Enlist" option is in doc on SqlConnection.ConnectionString property

How to create a column names using results of query?

I need to generate a report where the column names are defined by the result
s
of the first query and the data by the second query.
The closest I can get is to create a stored procedure that creates a temp
table with named columns fill it with a single row (query 1) and then fill i
t
with data. I then query the table for my results to pass on to the
application dropping the table when done. My issue with this method is that
App I am passing data to shows the temp column names as well (no way to
suppress them).
Over simplified pseudo code:
CREATE TABLE ##c (
ordr int,
Col1 varchar(30),
Col2 varchar(30)
)
INSERT INTO ##c
SELECT ‘1’ AS [ordr], h.header1, h.header2
FROM dbo.configure h
WHERE h.id = ‘105’
INSERT INTO ##c
SELECT ‘2’ AS [ordr], d.data1, d.data2
FROM dbo.detail d
WHERE d.id = ‘105’
ORDER by d.data1
SELECT col1, col2
FROM ##c
ORDER BY ordr, col1, col2
DROP TABLE ##cYou could generate a SELECT string you need with column names from the first
query and rename them use the AS keyword.
Then you can use EXECUTE (T-SQL command) to execute it.
Search the BOL for "Using EXECUTE with a Character String"
Milan
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:B43F7817-1C89-44AF-A4EE-15FCC0F17B52@.microsoft.com...
>I need to generate a report where the column names are defined by the
>results
> of the first query and the data by the second query.
> The closest I can get is to create a stored procedure that creates a temp
> table with named columns fill it with a single row (query 1) and then fill
> it
> with data. I then query the table for my results to pass on to the
> application dropping the table when done. My issue with this method is
> that
> App I am passing data to shows the temp column names as well (no way to
> suppress them).
> Over simplified pseudo code:
> CREATE TABLE ##c (
> ordr int,
> Col1 varchar(30),
> Col2 varchar(30)
> )
> INSERT INTO ##c
> SELECT '1' AS [ordr], h.header1, h.header2
> FROM dbo.configure h
> WHERE h.id = '105'
> INSERT INTO ##c
> SELECT '2' AS [ordr], d.data1, d.data2
> FROM dbo.detail d
> WHERE d.id = '105'
> ORDER by d.data1
> SELECT col1, col2
> FROM ##c
> ORDER BY ordr, col1, col2
> DROP TABLE ##c
>|||You can do something like the following:
IF OBJECT_ID (N'teble1') IS NOT NULL
DROP FUNCTION table1
GO
select * into table1 from Query1 where 0 = 1
insert into table1 select * from Query2
"Dan" <Dan@.discussions.microsoft.com> wrote in message news:B43F7817-1C89-44AF-A4EE-15FCC0F
17B52@.microsoft.com...
>I need to generate a report where the column names are defined by the resul
ts
> of the first query and the data by the second query.
> The closest I can get is to create a stored procedure that creates a temp
> table with named columns fill it with a single row (query 1) and then fill
it
> with data. I then query the table for my results to pass on to the
> application dropping the table when done. My issue with this method is tha
t
> App I am passing data to shows the temp column names as well (no way to
> suppress them).
> Over simplified pseudo code:
> CREATE TABLE ##c (
> ordr int,
> Col1 varchar(30),
> Col2 varchar(30)
> )
> INSERT INTO ##c
> SELECT '1' AS [ordr], h.header1, h.header2
> FROM dbo.configure h
> WHERE h.id = '105'
> INSERT INTO ##c
> SELECT '2' AS [ordr], d.data1, d.data2
> FROM dbo.detail d
> WHERE d.id = '105'
> ORDER by d.data1
> SELECT col1, col2
> FROM ##c
> ORDER BY ordr, col1, col2
> DROP TABLE ##c
>|||Do you mean:
Declare @.c1 as varchar(30)
Set @.c1 = Select col1 from table1
Select newCol1 AS @.c1 from table2
if so I can not get that to work
"Milan Kosanovic" wrote:

> You could generate a SELECT string you need with column names from the fir
st
> query and rename them use the AS keyword.
> Then you can use EXECUTE (T-SQL command) to execute it.
> Search the BOL for "Using EXECUTE with a Character String"
> Milan
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:B43F7817-1C89-44AF-A4EE-15FCC0F17B52@.microsoft.com...
>
>|||I am missing somthing hear as well:
I can not get my query clauses to run within the framework laid out.
"fish" wrote:

> You can do something like the following:
> IF OBJECT_ID (N'teble1') IS NOT NULL
> DROP FUNCTION table1
> GO
> select * into table1 from Query1 where 0 = 1
> insert into table1 select * from Query2
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message news:B43F7817-1C89-
44AF-A4EE-15FCC0F17B52@.microsoft.com...
>
>|||Have you ever considered being a good programmer who does his reports
in the front end instead of writing kludges in the database? This is
the whole idea of a tiered architecture.
Have you ever thought about writing declarative code instead mimicking
the steps of a procedural program? You are even trying to name a table
the way we used to name a tape on a mag tape system.|||> Have you ever considered being a good programmer who does his reports
> in the front end instead of writing kludges in the database? This is
> the whole idea of a tiered architecture.
IMHO report writing is a simple and boring task for 95% of time. IMHO
using a good programmer for report writing is an overkill - in my
recent experience a high scholl senior quickly mastered Crystal Reports
and
did all that booooooooring label aligning pretty well, and for a cheap
price. Overall it was a faster and cheaper approach as opposed to the
traditional one...|||Requestor
Character string
Trust me this is not my preferred method but the client’s dictated report
application and the predefined legacy applications db structure are off
limits to me. And I have this one obstacle to getting sign off. My boundarie
s
are that the I can pass a string to the db and the grid returned is what the
user see’s
I have figured out that I can
declare @.col1 as Varchar(15)
declare @.sql as varchar(755)
set @.col1 = (select some stuff)
Set @.sql = 'create table#t ([' + @.col1 + ] varchar(30))'
exec (@.sql)
not great but it gets me out the door within the clients constraints
"Alexander Kuznetsov" wrote:

> IMHO report writing is a simple and boring task for 95% of time. IMHO
> using a good programmer for report writing is an overkill - in my
> recent experience a high scholl senior quickly mastered Crystal Reports
> and
> did all that booooooooring label aligning pretty well, and for a cheap
> price. Overall it was a faster and cheaper approach as opposed to the
> traditional one...
>

How to Copy Job

Hi
I have 2 similar instances in sql server 2005
in instance A i have some jobs.
I want them to copy to second instance B
Please guide me step by step.
Muralidaran rHave you tried to Googled (http://www.google.co.uk/search?hl=en&q=copy+jobs+in+sql+server&meta=) this?
There are a handful of articles out there that explain it step by step!|||created script of jobs in instance A

executed in instance b using sql server enterprise management studio.

thanks

muralidaran r