DMO & .NET: How to get default database

I have a SQL2K instance that has granted me access through an unusual setup.
The SQL2K instance has granted me access through BUILTIN\Administrators by
including "NT_Authority\Authenticated Users" as part of that Windows Group.
Based on that, using DMO and C#, I need to be able to determine the default
database context. Normally, using DMO, you can access the Database property
off of the Login object, however, since my login name "DOMAIN\lgrissom" is
NOT explicitly defined anywhere, that won't work. Any ideas appreciated.
Lee
I found "SELECT DB_NAME()" T-SQL command, and used ADO.NET to execute that
against my ADO.NET connection instead of relying on my hidden DMO
connection. Works great.
Lee

Distribution database

Do we need to do any maintenance tasks on distribution database in
transactional replication after the replication is setup..?
Any advise...
Regards,
Ravi
No, there is a distribution clean up agent which will prune the distribution
database of replication metadata.
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
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:2248D094-CD16-4F9F-82CC-C38E19BAFFD1@.microsoft.com...
> Do we need to do any maintenance tasks on distribution database in
> transactional replication after the replication is setup..?
> Any advise...
> Regards,
> Ravi
|||In terms of removing metadata please see Hilary's reply. In terms of disaster
recovery, it depends on what your strategy is. If you're using transactional
replication, then there should definitely be a backup plan in case of the
database and transaction logs. Because of the way merge works this is less
important in terms of keeping perfectly up to date, but is still required if
you don't want to reset up replication in any way.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distribution Agent TimeOut

I have setup pull transactional replication with multiple publications and
distribution agents.
One of my distribution agent always says the following error--
Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
while other agents are executed just fine..
any suggestions..?
Regards,
bump up LoginTimeout and QueryTimeout to 120.
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
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:65DC7C0C-6149-4DB0-8C28-06DAC9EAAA59@.microsoft.com...
> I have setup pull transactional replication with multiple publications and
> distribution agents.
> One of my distribution agent always says the following error--
> --
> Timeout expired
> (Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
> --
> while other agents are executed just fine..
> any suggestions..?
> Regards,

Distribution Agent Stopped Working

I recently started having an issue with a distribution agent I have setup. I
am doing a simple transactional replication of a production database to
another server at a different location. A few days ago I started to receive
the error message " Error 14262:The
specified@.job_id('5E6241B8-55E2-472F-A3B9-346B7B66CFF5') does not exist"
whenever I tried to restart the distibution agent. It seems like the agent
was stopped a few days ago and now it cannot be restarted because of a
missing job. Can I recreate the agent and the job it is looking for? Or can I
recreate just the job and have the agent use that?
It seem like this job no longer exists. I do not see any reference to this id
in the sysjobs table. I do see a job in the sql server agent that performs
the same replication I am having problems with, but I believe this job is
from my attempts to recreate replication. The original job is not there. We
do have consultants that script things against our server, but as of yet none
of them have admitted to doing anything. Is it possible to recreate the job
that this agent is referencing or point it to the other job that is
performing the same function?
"Paul Ibison" wrote:

> I can't understnad how this can occur and would like to better understand
> it. Does the job id ('5E6241B8-55E2-472F-A3B9-346B7B66CFF5') exist in
> sysjobs? Can you start the job from sql server agent? If so,then it is the
> mapping of the logical distribution agent to the physical job that has gon
> awry. Has anything changed in the configuration recently - anyone scripted
> in the replication setup perhaps?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Have a look at syssubscriptions - the distribution_jobid column. It might be
that you could update this with the id of the job which is correct for you.
We're not really supposed to edit this type of info of course and the
supported mechanism would be to reinitialize.
Cheers,
Paul Ibison

Distribution Agent History Clean Up Fails

Hi everyone.
i'm using sql database 7.0 setup for merge replication.suddenly i found out
that the "Agent History Cleanup:Distribution" had a red x mark.When i
examined,it shows that the agent did not run on its specied running
schedule.Even if i run it manually,still it won't work. When i check the
history of the agent why it fails to run it state their that:
could not find the index entry for RID '361526d0688a801749800300101801000'
in index page (1:1084), index ID 0, database 'distribution'. [SQLSTATE HY000]
(Error 644). The step failed.
Is this error will affect the replication process of my server?How can i fix
this one?
I will really appreciate any help you could give and thanksin advance.
I'd run dbcc checkdb on the distribution database.
Also consider dbcc dbreindex on each table in the distribution database.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||hi sir.first i want to thank you for your response.
I still have some query about my problem sir.
I already run a dbcc checkdb on my distribution database,but unfortunately
it still not solve my problem.
The dbcc checkdb returns a result that "checkdb found 0 allocation errors
and 0 consistency errors in my database".
Is there other way to resolve this problem sir?
Will this miscellaneous agent error affects my database replication?
Thank you and more power.
"Paul Ibison" wrote:

> I'd run dbcc checkdb on the distribution database.
> Also consider dbcc dbreindex on each table in the distribution database.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||OK - then please try running dbcc dbreinxdex on all tables in the
distribution database and we'll see if this fixes it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Distribution agent failes with Invalid Column name 207 error

Hi,
We have setup one way transactional replication on SQL 2000 SP3a.
It was running fine for weeks and now the distribution agent fails with
"Invalid Column Name 'Name.' " message. I tried re-starting the agent,
even reintializing, but it doesn't work. I set the verboselevel to 3,
and captured the output to a log file.
I tried executing sp_MSupd_Item in QA and I get the same message, but
don't know what's causing it.
I would really appreciate if someone can help me with this.
Thanks
GG
The output log:
[5/11/2005 5:57:22 PM]MSNDMART-STG1.Item: {CALL sp_MSupd_Item
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0, 0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0 ,0,NULL,NULL,NULL,?,NULL,NULL,NULL,NULL,NULL,NULL, NULL,
0,?,0x00000000008000)}
Last transaction timestamp: 0x000011a00000249a000100000000
Transaction seqno: 0x000011a0000024a50004
Command Id: 1
Partial: 0
Type: 68
Command: STLDMART-PRD1Item
Parameterized values for above command(s): {{2005-05-11 17:07:05.523,
'820HK14 '}}
Agent message code 20046. Invalid column name 'Name'.
Repl Agent Status: 6
[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_distribution_history(3, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 5,
0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 7,
Transaction Seqno = 000011a00000249a000100000000, Command ID = 5
Message: Replication-Replication Distribution Subsystem: agent
STLSQL-PROD04-Item-MSNDMART-STG1-3 failed. Invalid column name
'Name'.[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_repl_alert(3, 3, 7, 14151, ?, 5, N'STLSQL-PROD04', N'Item',
N'MSNDMART-STG1', N'Item', ?)}
ErrorId = 7, SourceTypeId = 1
ErrorCode = ''
ErrorText = '{CALL sp_MSupd_Item
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0, 0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0 ,0,NULL,NULL,NULL,2005-05-11
17:07:05.523,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, '820HK14
',0x00000000008000)} '
[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_repl_error(7, 0, 1, ?, N'', ?)}
Category:COMMAND
Source: Failed Command
Number:
Message: {CALL sp_MSupd_Item
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0, 0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0 ,0,NULL,NULL,NULL,2005-05-11
17:07:05.523,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, '820HK14
',0x00000000008000)}
Repl Agent Status: 3
ErrorId = 7, SourceTypeId = 5
ErrorCode = '207'
ErrorText = 'Invalid column name 'Name'.'
[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
Category:SQLSERVER
Source: MSNDMART-STG1
Number: 207
Message: Invalid column name 'Name'.
Repl Agent Status: 3
ErrorId = 7, SourceTypeId = 5
ErrorCode = '207'
ErrorText = 'Invalid column name 'Name'.'
[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
Category:SQLSERVER
Source: MSNDMART-STG1
Number: 207
Message: Invalid column name 'Name'.
Repl Agent Status: 3
ErrorId = 7, SourceTypeId = 5
ErrorCode = '207'
ErrorText = 'Invalid column name 'Name'.'
[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
Category:SQLSERVER
Source: MSNDMART-STG1
Number: 207
Message: Invalid column name 'Name'.
Repl Agent Status: 3
[5/11/2005 5:57:22 PM]MSNDMART-STG1.Item: exec
dbo.sp_MSupdatelastsyncinfo N'STLSQL-PROD04',N'Item', N'', 0, 6,
N'Invalid column name ''Name''.'
Disconnecting from Subscriber 'MSNDMART-STG1'
Disconnecting from Distributor 'STLDBA-PRD1'
Disconnecting from Distributor History 'STLDBA-PRD1'
Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent:
STLSQL-PROD04-Item-MSNDMART-STG1-3
Startup Delay: 3427 (msecs)
Connecting to Distributor 'STLDBA-PRD1'
Repl Agent Status: 3
Connecting to Distributor 'STLDBA-PRD1.'
[5/11/2005 5:57:36 PM]STLDBA-PRD1.: exec sp_helpdistpublisher
N'STLSQL-PROD04'
[5/11/2005 5:57:36 PM]STLDBA-PRD1.Dist_Prod04: select @.@.SERVERNAME
can you edit the proc and put [] around a column called name?
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
"GG" <gdabbara@.gmail.com> wrote in message
news:1115855598.735328.54830@.g44g2000cwa.googlegro ups.com...
> Hi,
> We have setup one way transactional replication on SQL 2000 SP3a.
> It was running fine for weeks and now the distribution agent fails with
> "Invalid Column Name 'Name.' " message. I tried re-starting the agent,
> even reintializing, but it doesn't work. I set the verboselevel to 3,
> and captured the output to a log file.
> I tried executing sp_MSupd_Item in QA and I get the same message, but
> don't know what's causing it.
> I would really appreciate if someone can help me with this.
> Thanks
> GG
> The output log:
> [5/11/2005 5:57:22 PM]MSNDMART-STG1.Item: {CALL sp_MSupd_Item
>
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,0,0,0,
NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0,0 ,NULL,NULL,NULL,?,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL,0,?,0x00000000008000)}
> Last transaction timestamp: 0x000011a00000249a000100000000
> Transaction seqno: 0x000011a0000024a50004
> Command Id: 1
> Partial: 0
> Type: 68
> Command: STLDMART-PRD1Item
> Parameterized values for above command(s): {{2005-05-11 17:07:05.523,
> '820HK14 '}}
> Agent message code 20046. Invalid column name 'Name'.
> Repl Agent Status: 6
> [5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_distribution_history(3, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 5,
> 0x01, 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 7,
> Transaction Seqno = 000011a00000249a000100000000, Command ID = 5
> Message: Replication-Replication Distribution Subsystem: agent
> STLSQL-PROD04-Item-MSNDMART-STG1-3 failed. Invalid column name
> 'Name'.[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_repl_alert(3, 3, 7, 14151, ?, 5, N'STLSQL-PROD04', N'Item',
> N'MSNDMART-STG1', N'Item', ?)}
> ErrorId = 7, SourceTypeId = 1
> ErrorCode = ''
> ErrorText = '{CALL sp_MSupd_Item
>
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,0,0,0,
NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0,0 ,NULL,NULL,NULL,2005-05-11
> 17:07:05.523,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, '820HK14
> ',0x00000000008000)} '
> [5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_repl_error(7, 0, 1, ?, N'', ?)}
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: {CALL sp_MSupd_Item
>
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,0,0,0,
NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0,0 ,NULL,NULL,NULL,2005-05-11
> 17:07:05.523,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, '820HK14
> ',0x00000000008000)}
> Repl Agent Status: 3
> ErrorId = 7, SourceTypeId = 5
> ErrorCode = '207'
> ErrorText = 'Invalid column name 'Name'.'
> [5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
> Category:SQLSERVER
> Source: MSNDMART-STG1
> Number: 207
> Message: Invalid column name 'Name'.
> Repl Agent Status: 3
> ErrorId = 7, SourceTypeId = 5
> ErrorCode = '207'
> ErrorText = 'Invalid column name 'Name'.'
> [5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
> Category:SQLSERVER
> Source: MSNDMART-STG1
> Number: 207
> Message: Invalid column name 'Name'.
> Repl Agent Status: 3
> ErrorId = 7, SourceTypeId = 5
> ErrorCode = '207'
> ErrorText = 'Invalid column name 'Name'.'
> [5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
> Category:SQLSERVER
> Source: MSNDMART-STG1
> Number: 207
> Message: Invalid column name 'Name'.
> Repl Agent Status: 3
> [5/11/2005 5:57:22 PM]MSNDMART-STG1.Item: exec
> dbo.sp_MSupdatelastsyncinfo N'STLSQL-PROD04',N'Item', N'', 0, 6,
> N'Invalid column name ''Name''.'
> Disconnecting from Subscriber 'MSNDMART-STG1'
> Disconnecting from Distributor 'STLDBA-PRD1'
> Disconnecting from Distributor History 'STLDBA-PRD1'
> Microsoft SQL Server Distribution Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent:
> STLSQL-PROD04-Item-MSNDMART-STG1-3
> Startup Delay: 3427 (msecs)
> Connecting to Distributor 'STLDBA-PRD1'
> Repl Agent Status: 3
> Connecting to Distributor 'STLDBA-PRD1.'
> [5/11/2005 5:57:36 PM]STLDBA-PRD1.: exec sp_helpdistpublisher
> N'STLSQL-PROD04'
> [5/11/2005 5:57:36 PM]STLDBA-PRD1.Dist_Prod04: select @.@.SERVERNAME
>
|||We don't have a column called name in any of our tables, being
replicated.
Thanks
GG

Distribution agent failed but didn't ?

Hi There

I have the following setup.

Server A transactionally replicates to Server B , using Server C as remote distributor.

Now the distribution agent runs on Server B, under distribution agents in replication monitor the distribution agent is the same job as the job on Server B defined as REPL-Dsitributor, correct ?

Now this morning at 4am under distribution agents on the remote distributor the job was marked as failed (error: no response in 10 minutes), but the "same job" on Server B, was not failed ?

The replication agent failure alert did not fire, neither did the notification failure alert on the job it self ?

What the %#$&#% happened ? This is a production environment so it is very worrying ?

Only according to the distribution agent on the remote distributor did the job fail, but not the job itself on the subscriber ?

Firstly what happened here ?
Secondly how can i make the agent keep retrying ? Once it failed it stayed failed, can i configure it to keep trying ?

Thanx
How can i make this agent keep retrying ?As for your case, distribution agent may not run on B but C (remote distributor) and replmonitor actually monitor agent job on C.

If your subscription is push, distribution agent will run under C. But if your subscription is pull, it will run under B.

About your another question, if agent job can keep retrying if it fails. That's depends, if it fails to access some meta data or subscription database, some access issue, it will retry five times and fail. Other problem cause the agent job fail, it just fail, until your solve the problem and restart it again. Otherwise, it won't run.

Hope it will help you some.

-Yunjing

Distribution Agent Error: Syntax error or access violation

I am getting this error in the distribution agent that I setup for
transactional replication. The last command which failed is like "CALL
sp_MSins_CompanyName$Trans_ Sales Entry ("
The table name is lengthy and contains spaces too. I believe this is because
of the spaces in the stored procedure name which is causing the syntax
error.
The problem is, I cant rename the tables to ommit the spaces. Isnt there any
way to rename the stored procedures and relink them with the new name so
that the new renamed procedure should be called rather than system keep on
looking for the procedure with the original name?
If there any solution in SQL for this issue that would be great otherwise I
must have to do some workaround if possible.
Muhammad
When you create your publication you can specify the name of the stored
procedure you wish to use. SQL Server will autogenerate that stored
procedure with the name.
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
"Muhammad Shahzad Saleem" <msaleem@.simplywireless.com> wrote in message
news:uJ9xOIuHHHA.1044@.TK2MSFTNGP02.phx.gbl...
>I am getting this error in the distribution agent that I setup for
>transactional replication. The last command which failed is like "CALL
>sp_MSins_CompanyName$Trans_ Sales Entry ("
> The table name is lengthy and contains spaces too. I believe this is
> because of the spaces in the stored procedure name which is causing the
> syntax error.
> The problem is, I cant rename the tables to ommit the spaces. Isnt there
> any way to rename the stored procedures and relink them with the new name
> so that the new renamed procedure should be called rather than system keep
> on looking for the procedure with the original name?
> If there any solution in SQL for this issue that would be great otherwise
> I must have to do some workaround if possible.
> Muhammad
>

distribution agent

I'm developing a setup package for an application that involves some
replications.
I create the new replication database, transactional publication, merge
pulication and a pull named subscription on the rtansactional
publication.
For checking that the snapshots have been generated successfully I do
the following:
get the job_name from the mssnapshot_agents table on distribution
database.
update job_state to run agent.
This worked well on both snapshots (trans, merge)
The problem is that I try to do the same steps for running the
distribution agent to initialize the subscription:
get the job_name from msdistribution_agents table on distribution
database.
BUT the job name is always "dose not exist"
I use sp_update_job to update the current step to start
and sp_start_job to start the job.
Is there a problem with those SPs or another way to do the same with
other SPs.
Thanks.
Have you looked at the replication ActiveX controls?
I think this is probably the simplest way to carry out what you are trying
to accomplish.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"taghreed" <tssamak@.gmail.com> wrote in message
news:1103290693.352210.19720@.z14g2000cwz.googlegro ups.com...
> I'm developing a setup package for an application that involves some
> replications.
> I create the new replication database, transactional publication, merge
> pulication and a pull named subscription on the rtansactional
> publication.
> For checking that the snapshots have been generated successfully I do
> the following:
> get the job_name from the mssnapshot_agents table on distribution
> database.
> update job_state to run agent.
> This worked well on both snapshots (trans, merge)
> The problem is that I try to do the same steps for running the
> distribution agent to initialize the subscription:
> get the job_name from msdistribution_agents table on distribution
> database.
> BUT the job name is always "dose not exist"
> I use sp_update_job to update the current step to start
> and sp_start_job to start the job.
> Is there a problem with those SPs or another way to do the same with
> other SPs.
> Thanks.
>
|||Thanks Hilary,
but I don't have time to reconstruct the application using ActiveX
controls.
Do you have other ideas?

Distributing Setup files SQL Server 2005

Hi

I would like to know, if it is at all possible for Setup files of SQL Server 2005 to be distributed. We have clients who currently use our software which makes use SQL Server 2000. We are looking ahead at distributing our new version with SQL Server 2005 and would like create an Installation package that will upgrade the database engine to the Workgroup or Enterprise editions of SQL Server 2005. Are there any suggestions around this topic, we are looking at ways in making this task of upgrading the engine as streamlined and automated for our Client. Please include advise with regards to licensing.

cheers

Kevin

Here are a couple of resources that should point you in the 'right' direction:

Deploy Database with MSI
http://msdn.microsoft.com/msdnmag/issues/04/09/customdatabaseinstaller/

Deploy Database with Application

http://www.codeproject.com/useritems/Deploy_your_database.asp

Distributed transaction error

Hi,
I am getting the below error after a timeout when trying
to connect to an SQL Server database. This refers to the
connection setup to the database from my asp page. This
was working perfectly fine until 2 days ago and this same
connection is used throughout the system with out any
problems or timeout issuse.
Microsoft OLE DB Provider for ODBC Drivers
error '8004d00a'
[Microsoft][ODBC SQL Server Driver]Distributed
transaction error
/hbc/weightcoach/counselling/weeklyreport.asp, line 8
Line 8 refers to the following code:
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Application("DSN"), Application("UserName"),
Application("Password")
where the application variables are set out in the global
file as used by most pages on the site.
I changed the page to just the connection and removed all
transactions to narrow down the problem but no change. It
seems to be the connection that's causing the problem on
just this one page.
Could this probably be a lost packet issue? Any help
apprecaited.
Regards,
DeirdreDid this ever get resolved? I'm having the same problem.
/J
deirdre wrote:
> *Hi,
> I am getting the below error after a timeout when trying
> to connect to an SQL Server database. This refers to the
> connection setup to the database from my asp page. This
> was working perfectly fine until 2 days ago and this same
> connection is used throughout the system with out any
> problems or timeout issuse.
> Microsoft OLE DB Provider for ODBC Drivers
> error '8004d00a'
> [Microsoft][ODBC SQL Server Driver]Distributed
> transaction error
> /hbc/weightcoach/counselling/weeklyreport.asp, line 8
>
> Line 8 refers to the following code:
> Set oConn = Server.CreateObject("ADODB.Connection")
> oConn.Open Application("DSN"), Application("UserName"),
> Application("Password")
> where the application variables are set out in the global
> file as used by most pages on the site.
> I changed the page to just the connection and removed all
> transactions to narrow down the problem but no change. It
> seems to be the connection that's causing the problem on
> just this one page.
> Could this probably be a lost packet issue? Any help
> apprecaited.
> Regards,
> Deirdre *
jojje
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message174004.html

distributed transaction

Hi everyone,
I've 2 Windows 2000 server running each own instance of SQL2000. I've setup
both linked servers @. both end.
At server A, it'll call a sp in server B, whereby this sp will update server
B tables based on server A's data. And the server A table A will trigger ba
ck to server B.
Server_A call ServerB.sp
|
V
store procedure @. serverB
insert into serverB.dbB.dbo.tableB
select * from serverA.dbA.dbo.tableA where key=X
|
V
serverB.dbB.dbo.tableB trigger back to serverA
if record not found in serverA
insert into serverA
else
update into server A
However I encountered error stating:
Server: Msg 7391, Level 16, State 1, Procedure sp_update_across_linked_serve
r, Line 112
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransac
tion returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the spec
ified transaction coordinator. ]
I found out if I drop the trigger, the sp will perform correctly. But the pr
oblem is I need to keep the trigger.
Can someone enlighten me on this ?
thanks in advance
KristeHi
http://groups-beta.google.com/group...=UTF-8&oe=UTF-8
Regards
Mike
"Kriste L" wrote:

> Hi everyone,
> I've 2 Windows 2000 server running each own instance of SQL2000. I've setu
p both linked servers @. both end.
> At server A, it'll call a sp in server B, whereby this sp will update serv
er B tables based on server A's data. And the server A table A will trigger
back to server B.
> Server_A call ServerB.sp
> |
> V
> store procedure @. serverB
> insert into serverB.dbB.dbo.tableB
> select * from serverA.dbA.dbo.tableA where key=X
> |
> V
> serverB.dbB.dbo.tableB trigger back to serverA
> if record not found in serverA
> insert into serverA
> else
> update into server A
>
> However I encountered error stating:
> Server: Msg 7391, Level 16, State 1, Procedure sp_update_across_linked_ser
ver, Line 112
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTrans
action returned 0x8004d00a].
> [OLE/DB provider returned message: New transaction cannot enlist in the sp
ecified transaction coordinator. ]
> I found out if I drop the trigger, the sp will perform correctly. But the
problem is I need to keep the trigger.
> Can someone enlighten me on this ?
> thanks in advance
> Kriste
>|||I've checked all the necessary things,
- use the dtcping, the 2 servers' rpc server and reverse binding are ok in
both direction.
- there's no firewall in both servers
- in the sp and trigger, SET XACT_ABORT ON and SET REMOTE_PROC_TRANSACTIONS
OFF
in my situation, is the trigger causing a loopback operations?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:75A495AD-7C8F-4B82-8E0C-C72C68609339@.microsoft.com...
> Hi
>
http://groups-beta.google.com/group...=UTF-8&oe=UTF-8
> Regards
> Mike
> "Kriste L" wrote:
>
setup both linked servers @. both end.
server B tables based on server A's data. And the server A table A will
trigger back to server B.
sp_update_across_linked_server, Line 112
'SQLOLEDB' was unable to begin a distributed transaction.
ITransactionJoin::JoinTransaction returned 0x8004d00a].
specified transaction coordinator. ]
the problem is I need to keep the trigger.

Distributed SSIS server setup

I work in my organization's warehouse team and we are rearchitecting our hardware. One of the ideas on the table is to devote a server to running ETL. There would be several beefy database servers, and a separate server that would run the ETL (SSIS). I'm wondering if that actually hinders the process.

The DB servers will still do all of the query processing, so that isn't off loaded to the ETL server. Then the recordsets I think would need to go over the network to the ETL server for the SSIS packages to work on. And then another trip over the network back to their destination. Would it be better to place the ETL process on either the source or destination DB server? What could be gained by using hardware in this way as described here?

Thanks.

Hi, its a good question but at the same time there is no perfect answer. It all depends. In a perfect world everything on 1 server that is understressed would be screamingg fast. Unlimited hard drive space (and high RPMs), bucket loads of free RAM, minimal network interaction.

So it depends a lot on how stressed you feel the box would be, AND its expected growth.

Are you running ETL jobs during most of the day?

Are they small but frequent or infrequent but intensive?

Capture performance with perfmon or some other tool of even basic stuff like cpu utilization and free memory for even a day and see where are the peaks and duration of the peaks.

Eggs in your basket and downtime are other things to consider. So a huge down side of the single server is if it smoked out you lose everything.

If an isolated ETL box dies, then all your other operaitons keep going. So now a quest ion for your question is, what is your recovery/backup plan/needs/desires?

Do you have the hardware to have a duplicate of each type of server that you can work into production quickly if need be? Perhaps a good approach is you have the DBs and ETL process on multiple boxes but one is the primary DB server the other the ETL...but if something happened to the ETL server, you could in a pinch get the ETL process up and running fast on the DB box because all the needed bits are there but you just need to get it 'live'. Similarly assume your ETL box has mirrored (in some wya) data of whats on the DB server and then if the DB server has problems you could get that process live quickly from the ETL box.

Again, there is no 1 perfect answer. Hope this was useful food for thought.

|||

A distributed SSIS farm will quite well for micro-batch ETL (sub-day processing). Using Craig's terms, the job resource utilization profiles are "small (data wise), frequent AND intensive", meaning not much RAM per job (200MB), lots of CPU, and lots of jobs (thousands to millions per day). This is a generalizaton, since the there are certainly some packages which run on a less frequent schedule which have different resource utilization profiles.

The distributed SSIS server architecture can scale out nicely too. There is custom work up front to get it going, because there needs to be some type of queue-ing (database table, msmq, service broker queue, file system) mechanism which farms out the work, which operates outside of and interacts with the SSIS infrastructure.

As far as mirroring dimensional data out to the SSIS servers (in an effort to reduce network traffic) something to consider is the use of SQL Compact. SQL Compact is not 64 bit compatible (yet), but it looks like a good option if and when it does have a 64 bit version. If not, something that works right now, is a local SQL installation together with SSIS which each ETL server uses to "assist" in areas like caching of stock (relatively non-volatile) data.