Hi!
I have a legacy database in SQL 2000 which I want to submit transactions thru triggers to SQL Broker, until I eventually upgrade this db to 2005. Can you tell me if this is possible?
Thanks
How do I change the account that Service Broker runs under?
Thanks,
1) I have the Beta Preview of Roger Wolter's book on the subject. Has anything major changed from the beta version to the full version (of the book as well as the product)?
2) I want to build a front-end that queues requests for processing that is ultimately done by a standalone console application. The console app knows nothing about SQL Server 2005, so the database will have to fork/execute this app, pass it arguments on its command line, and pick up the result (written to either standard output or standard error) when it finishes. Is this possible with Service Broker? If so, how?
TIA
Andrew
Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?
Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.
I am using the SQL Server 2000 query analyser to write stored procedures for an SQL Server 2000 database.
I would like to find a debugger to help me "look into" my stored procedures as they are executed (e.g. to watch a Cursor fill a table row by row, see the contents of temporary tables as a stored procedure progresses, etc.)
Could anybody, please, direct me to-
* Any debugger that does this
OR
* Simple instructions how to run stored procedures from Visual Basic. I suspect :) that VB's debugger may be able to look into the SQL as it is being executed, but I don't actually know how to use VB itself.
Thanks very much,
Angelos
Hi,
I'm a newbie on SSIS and am trying to grasp my way through this.
I am trying to copy data from a Sql Server 2000 database to a simplified table in Sql Server 2005 database.
What I want is to move the data to a staging table, then drop the main
table and rename the staging table to the main table, to minimize the
down-time of the data. I can't get the workflow to work, because the
staging table has to exist when I run the package. I thought I could
use an "Execute SQL" task to generate the table before I would run the
task, but that doesn't work. Am I going about this the wrong way? Is
there an optimal solution to this problem so my data can be accessible
as much as possible.
Regards,
Atli
Hi All
Well I have some queries as a newbie to SRSS 2000 reports deployment.
1) I have to build a VB .NET application with a web interface to display reports in that program. Is it possible? Is there any example codeing floating around which I can take a look? Or how to do it?
2) I have to pass the parameters for the report via a VB text box into the report and the report should be displayed. So the parameter bar in the report as it comes in IE7 should not come. How can I do that?
Or in simpler terms
- How to turn the SQL 2000 reporting features off to allow a
simpler report display
- How to display the report in a Microsoft Windows Form
application.
Hope I have made myself clear.
Thanks
Hello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER β''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' β'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
View 1 Replies View RelatedHi,
I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight.
I look forward to some responses.
Kind regards,
Neeva
How to prevent the hang on the initator service broker if the target service broker is not started?
Our case has two service brokers (two databases), sometime, the target is need to turn off. But the sitation is the initator service broker (in fact, the message is sent from triggers) become hang, I want to prevent this case and continue to operation, and the messages should queue and will continue to send to target service broker when it startup. How should I do?
Hi there
I sorry if I have placed this query in the wrong place.
I'm getting to grips with ASP.net 2, slowly but surely!
When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:
Server Error in '/jarebu/site1' Application.
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.
Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735075
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
This is the connection string that I am using:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
The database is definitly in the folder that the error message relates to.
What I'm finding confusing is that the connection string seems to be finding "aranga"s database.
Is it something daft?
Many thanks.
James
Back in July I released the (then) latest version of SsbAdmin. For you who
don'r know what it is; it's a tool which allows you to graphically administer
SQL Server Service Broker.
I have now uploaded a new version which is compiled against the released
versions of SQL Server 2005 and .NET 2.0.
You can download it from here: [0].
After download, un-zip to some directory and read the README.doc file.
[0]: http://staff.develop.com/nielsb/code/ssbadmin.zip
Can't believe no one has commented on this in here yet. If you're into SQL Server Service Broker (and you are if you are reading this forum), you must go over and take Rushi's Service Broker Challange.
View 1 Replies View RelatedRecently there has been questions on some of the newsgroups about examples for SQL Server Service Broker. So, yesterday on a flight back to England I crofted up three different SQL Server projects as examples: LocalSampleRemoteSampleServer1RemoteSampleServer2
All three examples are jus your very basic Hello World? example, but they show: LocalSample communication between two databases on the same SQL server instance. MasterKeys in a database and the database being TrustWorthyRemoteSampleServerX These two samples are meant to run on two different machines/instances and in the samples you set up both transport security and dialog security by using Certificates.
Download the zip file from here [0], unzip and read the README.txt file, and Have Fun!!
[0] : http://staff.develop.com/nielsb/code/servicebrokerexamples.zip
Does SQL SVR 2005 Service Broker use connection pooling?
We are building a laptop application that will use SB to send messsages
to one SQL SVR 2005 instance. SQL 2005 Express will be installed on
each laptop and will be the "sender" to one SQL SVR instance, the "receipient"
It will be deployed to over 1500+ laptops. Each laptop will have a different
IP address.
Our SQLDBA wants to know if each of the laptops will use a different
connection when sending messages?
I answered "Yes", but another team member said "No".
What is the correct answer?
I have a database with Broker_Enabled set via the following command:
ALTER DATABASE 'xxxx' SET ENABLE_BROKER
All works well, I made a backup and want to move to my development machine. When I run I still get an error saying I need to enable broker service again.
"The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications."
How can I move the database and get this setup? Where are the BrokerID's Stored?
Thanks,
~ Steve
helloi use Visual Web Developer and SQL server 2005 express , how can i enable service borker of my sql server ?thaks for any helpderin
View 1 Replies View RelatedI have configured a non-SQL linked server (via an OLE DB provider) and I wish to insert data into it via Service Broker but I am getting the following error in the SQL Server log:
The activated proc [dbo].[sp_ mytableServiceProgram] running on queue TestDB.dbo.mytableQueue output the following: 'Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.'
As you see below, my strored proc. is not issuing any 'save trans' statements, so why is it not allowing me to wrap my code in a transaction? How else can I use a transaction (in order to not lose anything from the queue) and yet still be able to insert to the linked server?
CREATE PROC sp_mytableServiceProgram
AS
SET NOCOUNT ON;
DECLARE
@XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName nvarchar(256),
@Dialog UNIQUEIDENTIFIER;
-- This procedure continues to process messages in the queue until the
-- queue is empty.
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION;
--BEGIN DISTRIBUTED TRANSACTION; --Tried this but didn't help.
-- Receive the next available message
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@Dialog = conversation_handle
FROM mytableQueue
), TIMEOUT 2000 ;
-- If RECEIVE did not return a message, roll back the transaction
-- and break out of the while loop, exiting the procedure.
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END ;
SET @XML = CAST(@MessageBody AS XML);
INSERT INTO LINKEDSERVER.dbname.user.mytable
SELECT tbl.rows.value('@doc_no', 'INT') AS doc_no,
tbl.rows.value('@queryid', 'NVARCHAR(50)') AS queryid,
tbl.rows.value('@ar_num', 'NVARCHAR(50)') AS ar_num,
tbl.rows.value('@status', 'NVARCHAR(20)') AS status,
tbl.rows.value('@creationtime', 'DATETIME') AS creationtime,
tbl.rows.value('@note', 'NVARCHAR(250)') AS note,
tbl.rows.value('@posted', 'NCHAR(1)') AS posted,
tbl.rows.value('@kms', 'INT') AS kms,
tbl.rows.value('@schresid', 'NVARCHAR(50)') AS schresid,
tbl.rows.value('@resolution_code', 'NCHAR(8)') AS resolution_code,
tbl.rows.value('@page_count', 'INT') AS page_count,
tbl.rows.value('@new_serial_number', 'NVARCHAR(20)') AS new_serial_number,
tbl.rows.value('@taskresolution', 'NVARCHAR(250)') AS taskresolution
FROM @XML.nodes('/inserted') tbl(rows);
-- If the INSERT did not insert any rows, rollback.
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
COMMIT TRANSACTION;
END
GO
I have an application that is reading a message from a Service Broker Queue. When I use integrated security with an NT account it works fine. When I use a SQL Server User through Management Studio I can select from the Queue however, when I use this same account through the web app with the SQL Server User, I cannot see the Queue. Is there a grant that I must do to this account to get it to see the Service Broker Objects?
Gary
Hi,
I have installed sql server standard edition 2005.I want to enable service broker feature or services after intalling the sql server.Please let me known any tool or utiltiy to enable the feature
Alter datatabase set enable_broker is not working.
Its giving syntax error
Thanks
I am writting a huge Service Broker Application. I notice that each message that is processed appears in the SQL Server Logs. Currently I am just testing with a single queue and tons of messages are showing up in the log. Is there a way that I can turn these informational messages off? I think in the end I may have something like 15 or 20 queues. These messages are very useful for debugging but I may not desire this extensive logging to be running all the time on all of my queues.
Gary
I have a problem with a server broker conversation between two instances on the same machine.
I have used Server Listning Manager to setup the communication between the instances.
When i a try to send a dialog it stops in sys.transmissin_queue and transmission_status is "The Service Broker protocol transport cannot listen on port 4022 because it is in use by another process."
Can someone help me
/ Dan Svensson
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
I had to restore a database in one of the staging servers the other day. However, upon completion, I found out that the service broker queue in the database is not working anymore. The service broker queue error was similiar to this error:
The activated proc dbo.procedure_name running on queue database_name.dbo.queue_name output the following: '<error message>.'
Let's say that I have a stored proc that is assigned to a service broker queue and is constantly running while it waits for messages in said queue. When a message comes in on the queue, the stored proc creates a table variable based off of the contents of the message and performs various operations with the data. Since the stored proc is constantly running, do the contents of this table variable ever truly get emptied? Should I be deleting the contents of the table variable at the end of the operation to ensure that stale data doesn't persist?
View 5 Replies View RelatedI created the table dbo.customer from the orginal table sales.customer. dbo.customer works but sales.customer does not. WHY? We took the time to design our tables under different schemas to help control, organize and assign permissions.Private Function GetCustomers() As DataTable
Dim sqlconnection As New SqlConnection(WebConfigurationManager.ConnectionStrings("connectionstringAlliance").ConnectionString.ToString)WORKS:Dim command As New SqlCommand("SELECT CustomerID, CustomerType FROM dbo.Customer WHERE CustomerID=1234", sqlconnection)DOES NOT WORK:Dim command As New SqlCommand("SELECT CustomerID, CustomerType FROM Sales.Customer WHERE CustomerID=1234", sqlconnection)
Dim dependency As New SqlCacheDependency(command)
Dim dad As SqlDataAdapter = New SqlDataAdapter(command)
Dim customers As New DataTable
dad.Fill(customers)Cache.Insert("Customers", customers, dependency)
Return customersEnd Function Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadDim customers As DataTable = CType(Cache.[Get]("Customers"), DataTable)
If customers Is Nothing Then
customers = GetCustomers()
Label1.Text = System.DateTime.Now.ToString()
End If
gridviewCustomer.DataSource = customers.DefaultView
gridviewCustomer.DataBind()
End Sub
Hello,
I solved the previous issue I had and now need to find out how to make it so I have Service Broker running on one machine and the services running on another machine. I mean you can set a connection string to post a message to the service broker but then it will need to respond and the connection string in the method called does not allow you to put a server in the connection string. The other issue is because the stored procedure calls the method in your code based on a namespace and then a method but does not specify a server. I am sure a way exists but am not familiar with it. This is the last piece of the architecture I need to work out before I finish designing the system.
Thanks,
Scott Allison...
Hi,
I am new to DTS. I am also not sure whether this is the solution for my criteria. First my criteria.
I have an Excel file where i would be capturing the data on a daily/weekly basis. Now, i want to transfer the data to sqlserver at a regular interval let me say weekly once. I want the record to append. At later stages, planned to call from asp through asp.net.
Which is the best solution for to accomplish this task.
Any good sample would be of great help.
Thanks in Advance to all.
Rgs
Ramu
Hi ,
My company will have a new application running On an NT Cluster .It has MICrosoft SQL 6.5 as its core element .I have been given Two Months to know Microsoft SQL 6.5 inside out .( commands configurationa and all ) .Please does anyone know any good books on Microsoft SQL 6.5 and knowing SQL thoroughly .
Please help
HAPPY NEW YEAR .
Thanks in anticipation .
Mibsun
I have a bunch of .sql files that will create a data model for me.
I've created a new database in my SQL Server 7 Enterprise Manager called Test.
How do i run this script? In part it looks like:
create table AGP_TYPES (
WL_AGP_TYPE VARCHAR2(4) not null,
WL_AGP_TYPE_DESC VARCHAR2(40),
constraint PK_AGP_TYPES primary key (WL_AGP_TYPE)
)
/
create unique index PK_AGP_TYPES on AGP_TYPES (
WL_AGP_TYPE ASC
)
/
create table ALARM_SYSTEMS (
WL_ALARM_TYPE VARCHAR2(4) not null,
WL_ALARM_TYPE_DESC VARCHAR2(40),
constraint PK_ALARM_SYSTEMS primary key (WL_ALARM_TYPE)
)
/
create unique index PK_ALARM_SYSTEMS on ALARM_SYSTEMS (
WL_ALARM_TYPE ASC
)
/
Hi,
I received an alert 'The SQL Server Service Broker or Database Mirroring transport is disabled or not configured'
How can I check the status of the two? and if they are disabled how do I enable them?
Hello. I'm new to SQL server and I've run into a really basic issue. I'm working on a Visual Studio 2005 project and I have a SQL databse with a .BAK extension I need to use with the project. I've read online that I need to restore the database to a new database to work with SQL Server Express. Here's where I am lost. I can't find good instructions on how to restore the DB to a new one. Any help is really appreciated. Thanks.
View 3 Replies View RelatedGood day. Is there any version of SQL server 2005(not include Express Edition) same as SQL server 2000 Personal edition ? I am planning to install new SQL server 2005 in my pc now. I am using Windows XP SP2 now. I tried to install to Entreprise Trial version, unfortunately it just works as client. Thank you in advance.
Regards,
Anson