Does service broker use MSDTC? What is sb's underlying technology

We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRAN
in our code. We are passing data from one server.database to another
server.database. These servers are on windows 2003 sp2 and using sql server
2005 sp2 plus clustering. We have run into a couple of issues where the
MSDTC acts flaky. Log files and error messages from msdtc queries is
extremely poor. The only solutions that we have found in this situation is
to failover the cluster and/or restart the sql server services. I am
pondering if we should consider editing our code. I know that the service
broker is a new feature. We are not currently using it. In passing data
around from server to server what is the underlying technology? Does it use
MSDTC. If so, I think a re-write of our code to piggy back ontop of service
broker is not going to help us. Can someone elaborate on cross server query
messages using service broker?
Thanks,
TimNo, SB does not use DTC. As far as I understand, the re-try of message sending is built-in to SB
itself, which uses TCP sockets (defined when you create your endpoint). As I understand it, the data
format (the protocol between two SB instances) isn't public, and it is in binary format (no XML or
similar). I suggest you pick up Roger Wolter's book on SB, great reading, with lots of insight...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"timw86" <timw86@.discussions.microsoft.com> wrote in message
news:7508D9D5-A4ED-4993-89B1-4CFE079F0905@.microsoft.com...
> We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRAN
> in our code. We are passing data from one server.database to another
> server.database. These servers are on windows 2003 sp2 and using sql server
> 2005 sp2 plus clustering. We have run into a couple of issues where the
> MSDTC acts flaky. Log files and error messages from msdtc queries is
> extremely poor. The only solutions that we have found in this situation is
> to failover the cluster and/or restart the sql server services. I am
> pondering if we should consider editing our code. I know that the service
> broker is a new feature. We are not currently using it. In passing data
> around from server to server what is the underlying technology? Does it use
> MSDTC. If so, I think a re-write of our code to piggy back ontop of service
> broker is not going to help us. Can someone elaborate on cross server query
> messages using service broker?
> Thanks,
> Tim

Does service broker use MSDTC? What is sb's underlying technology

We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRAN
in our code. We are passing data from one server.database to another
server.database. These servers are on windows 2003 sp2 and using sql server
2005 sp2 plus clustering. We have run into a couple of issues where the
MSDTC acts flaky. Log files and error messages from msdtc queries is
extremely poor. The only solutions that we have found in this situation is
to failover the cluster and/or restart the sql server services. I am
pondering if we should consider editing our code. I know that the service
broker is a new feature. We are not currently using it. In passing data
around from server to server what is the underlying technology? Does it use
MSDTC. If so, I think a re-write of our code to piggy back ontop of service
broker is not going to help us. Can someone elaborate on cross server query
messages using service broker?
Thanks,
Tim
No, SB does not use DTC. As far as I understand, the re-try of message sending is built-in to SB
itself, which uses TCP sockets (defined when you create your endpoint). As I understand it, the data
format (the protocol between two SB instances) isn't public, and it is in binary format (no XML or
similar). I suggest you pick up Roger Wolter's book on SB, great reading, with lots of insight...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"timw86" <timw86@.discussions.microsoft.com> wrote in message
news:7508D9D5-A4ED-4993-89B1-4CFE079F0905@.microsoft.com...
> We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRAN
> in our code. We are passing data from one server.database to another
> server.database. These servers are on windows 2003 sp2 and using sql server
> 2005 sp2 plus clustering. We have run into a couple of issues where the
> MSDTC acts flaky. Log files and error messages from msdtc queries is
> extremely poor. The only solutions that we have found in this situation is
> to failover the cluster and/or restart the sql server services. I am
> pondering if we should consider editing our code. I know that the service
> broker is a new feature. We are not currently using it. In passing data
> around from server to server what is the underlying technology? Does it use
> MSDTC. If so, I think a re-write of our code to piggy back ontop of service
> broker is not going to help us. Can someone elaborate on cross server query
> messages using service broker?
> Thanks,
> Tim

Does service broker use MSDTC? What is sb's underlying technology

We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRAN
in our code. We are passing data from one server.database to another
server.database. These servers are on windows 2003 sp2 and using sql serve
r
2005 sp2 plus clustering. We have run into a couple of issues where the
MSDTC acts flaky. Log files and error messages from msdtc queries is
extremely poor. The only solutions that we have found in this situation is
to failover the cluster and/or restart the sql server services. I am
pondering if we should consider editing our code. I know that the service
broker is a new feature. We are not currently using it. In passing data
around from server to server what is the underlying technology? Does it use
MSDTC. If so, I think a re-write of our code to piggy back ontop of service
broker is not going to help us. Can someone elaborate on cross server query
messages using service broker?
Thanks,
TimNo, SB does not use DTC. As far as I understand, the re-try of message sendi
ng is built-in to SB
itself, which uses TCP sockets (defined when you create your endpoint). As I
understand it, the data
format (the protocol between two SB instances) isn't public, and it is in bi
nary format (no XML or
similar). I suggest you pick up Roger Wolter's book on SB, great reading, wi
th lots of insight...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"timw86" <timw86@.discussions.microsoft.com> wrote in message
news:7508D9D5-A4ED-4993-89B1-4CFE079F0905@.microsoft.com...
> We are using MSDTC in our stored procedures. We use BEGIN DISTRIBUTED TRA
N
> in our code. We are passing data from one server.database to another
> server.database. These servers are on windows 2003 sp2 and using sql ser
ver
> 2005 sp2 plus clustering. We have run into a couple of issues where the
> MSDTC acts flaky. Log files and error messages from msdtc queries is
> extremely poor. The only solutions that we have found in this situation i
s
> to failover the cluster and/or restart the sql server services. I am
> pondering if we should consider editing our code. I know that the servic
e
> broker is a new feature. We are not currently using it. In passing data
> around from server to server what is the underlying technology? Does it u
se
> MSDTC. If so, I think a re-write of our code to piggy back ontop of servi
ce
> broker is not going to help us. Can someone elaborate on cross server que
ry
> messages using service broker?
> Thanks,
> Tim

Does Reporting Services allow you to select a dataset dynamically?

I have a remote report ...
I need to call one of two stored procedures depending on which parameters were passed to the report. (Both stored procedures return the same fields.)
Does SQL Reporting Services allow you to switch the dataset or stored procedure name dynamically? If so, where do I put the logic? Right now I have it working with one stored procedure.
I'm thinking that I should just make one stored procedure that takes all parameters and calls one of the other two stored procedures. Do I have any options besides this?

Thanks in advance!
Your query can be expression-based, e.g.; =Iif(Parameters!SomeParameterValue = 0, "EXEC sp1 1<parameter list>", EXEC sp2 1<parameter list>"). Alternatively, if the sp choice can be based on a configuration convention, you can use the Report Server web.config file to store the config value. The later case is demonstrated by the DynamicConnection report in this download. The report uses a dynamic connection string but the same approach can be applied to the query.|||That worked. Thanks!
sql

Does anyone know that where have the most mssql extended procedures for download?

I have a extended procedure for sending email by smtp already.

Are there anywheres can be downloaded the rest?
e.g. copy, move, detect, and delete some files and directories in harddisk
access Active directories
read and write .ini files
etc......I suggest you search 'Books Online', the help files for MS Sql Server.|||I just know C# & VB only, and i don't know C/C++.

those are useless.

Documenting existing stored procedures

Does anyone have any scripts, or know of any tools, that can scan through all of the SPs that I have inherited, documenting details?
ThxThis will retrieve the name and text of all the stored procedures.

SELECT a.name, b.text
FROM sysobjects a INNER JOIN syscomments b ON
a.id = b.id
WHERE
a.xtype = 'P'

This will retrieve the input parameters for all stored procedures in a database:

SELECT a.name, b.*
FROM sysobjects a INNER JOIN syscolumns b ON
a.id = b.id
WHERE
a.xtype = 'P'

What else are you looking to do?|||thanks! :o :o|||Did you use Enterprise Manager to script out the stored procedures and the like?

You can use this to Search your database (http://weblogs.sqlteam.com/brettk/archive/2004/02/05/841.aspx)

Good Luck

Do you replicate views, stored procedures, and user functions?

Greetings,

We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.

Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?

Thanks for any ideas that you share.

BCB

There shouldnt be any issues replicating views , sprocs , udfs.

|||

It works great appart from when you want to run the snapshot. It will take much longer to run the snapshot for a couple of 100 sp's than for a couple of hundered tables.

Other than that it works great and with 2005 will replicate your changes nicely.

We keep them in a separate publication because of the snapshot issue and to make things simpler for the main publication.

Martin

Do you replicate views, stored procedures, and user functions?

Greetings,

We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.

Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?

Thanks for any ideas that you share.

BCB

There shouldnt be any issues replicating views , sprocs , udfs.

|||

It works great appart from when you want to run the snapshot. It will take much longer to run the snapshot for a couple of 100 sp's than for a couple of hundered tables.

Other than that it works great and with 2005 will replicate your changes nicely.

We keep them in a separate publication because of the snapshot issue and to make things simpler for the main publication.

Martin

Do you replicate views, stored procedures, and user functions?

Greetings,

We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.

Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?

Thanks for any ideas that you share.

BCB

There shouldnt be any issues replicating views , sprocs , udfs.

|||

It works great appart from when you want to run the snapshot. It will take much longer to run the snapshot for a couple of 100 sp's than for a couple of hundered tables.

Other than that it works great and with 2005 will replicate your changes nicely.

We keep them in a separate publication because of the snapshot issue and to make things simpler for the main publication.

Martin