Consolidation Query: service

Showing posts with label service. Show all posts

Tuesday, March 27, 2012

conversion tools for sql7 databases

is there any tool / utility , online service / websites that will help me convert a MSSQL 7 database to be compatible to MSSQL2k ?
i have enterprise manager for 7 , but when i try to connect to server which has sql2000 ... i cant .
thanx .You need to uninstall Client Tools for SQL 7.0 and install Client Tools for 2K.

Tuesday, March 20, 2012

Conversations

I am currently designing an auditing application using Service Broker. Right now, when I send a message from a trigger, I start a conversation, and later on when the message has been processed, the conversation has ended. One thing I am concerned with is that when a lot of updates are occurring on the system, if the amount of conversations being created will eat up system resources. Does it make sense to create them and end them later, or should I try to reuse them?
Tim

This subject is quite intricate and has many facets. It also appears often when discussing Service Broker, so I'll try to address it in a series of blog articles. I've started this today, see http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx

HTH,

~ Remus

|||Thanks Remus. I am starting to notice that I am getting messages such as "There is insufficient system memory to run this query." and "There is insufficient memory available in the buffer pool." when I have a lot of conversations occurring (about 300K records in conversation_endpoints view). I am thinking that this is directly attributable to me creating a new conversation for every audit record(s) created. What is the best way for me to test that this is the case...that Service Broker is really the culprit in tying up all of my system memory?|||look in sys.dm_os_memory_clerks to see how memory is allocated|||Ok, sounds good. I am almost 100% sure it relates to me creating a new dialog for each message I pass.

Do you plan to post another blog anytime soon regarding reusing conversations? The situation I am currently trying to figure out is how to handle closing (or handling) the conversations so that I can reuse them....more specifically:
1. I check a table to see if there are any dialog handles free to use. If they are not, I create a new one and send a message to a queue.
2. The activation proc on the queue gets the message from the queue, but the handle it receives is not the same as the one that was created when I sent the message. It seems that this handle represents the target (from sys.conversation_endpoints). At this point, I can't close that end of the conversation when I have processed the message because if I do, it puts the other end, the initiator, in a disconnected_inbound state, which means I can't reuse it later and send another conversation on it. So, what is the best way to handle that? I want to be able to reuse the handle that I originally created, but not really sure the best way to do it. Thanks in advance.
Tim|||

Yes, I plan a post soon. Here is how I recommend doing it: have a criteria when a dialog should be 'recycled' (ended and a new one started). Good candidate criterias would be 'after N messages sent' or 'X minutes/hours/days after was created'. When this criteria is met, the initiator should sent a special message, something like 'EndOfStream' and removes the handle from the association table (So subsequent usp)Send calls will start a new one). When the target receives this EndOfStream message, it responds with and END CONVERSATION. When the initiator receives the EndDialog message, it ends it side (initiator also must have activation on it's queue). I have arguments why I prefer this pattern, I'll detail in blog.

HTH,

~ Remus

|||Thanks Remus, I eagerly look forward to it. Also, here is a small dump of my dm_os_memory_clerks view when I was receiving the errors: type single_pages_kb multi_pages_kb OBJECTSTORE_SERVICE_BROKER 884584 0 CACHESTORE_BROKERTO 176936 0 MEMORYCLERK_BHF 146696 0 OBJECTSTORE_LOCK_MANAGER 126064 0 OBJECTSTORE_SERVICE_BROKER 101168 0 MEMORYCLERK_SQLSERVICEBROKER 19256 192 MEMORYCLERK_SQLSTORENG 10624 7088 CACHESTORE_OBJCP 6304 32 MEMORYCLERK_SOSNODE 6224 6048 MEMORYCLERK_SQLGENERAL 1832 2016 I also started getting a fun new error in one of my activation procedures: Internal Error: Text manager cannot continue with current statement. Run DBCC CHECKTABLE., which I think is directly related to me creating a new dialog for every message created.|||

This is a procedure I wrote to manage a dialog pool. Basically it creates a number of conversations and then uses them until the number available drops below a certain threshold value. It then selects one at random (so you aren't reusing the same one every time). It works great, but I'd like to hear any comments from the experts.

CREATE PROCEDURE [usp_DialogFactoryCreate]

(

@.minDialogs AS INT,

@.maxDialogs AS INT,

@.fromServiceName AS NVARCHAR(256),

@.toServiceName AS NVARCHAR(256),

@.contractName AS NVARCHAR(256),

@.selectedDialog UNIQUEIDENTIFIER OUTPUT

)

BEGIN

SET NOCOUNT ON;

DECLARE @.dialogCount INT;

DECLARE @.conversationHandle AS UNIQUEIDENTIFIER;

-- State should be either STARTED_OUTBOUND or CONVERSING

SET @.dialogCount = (SELECT COUNT(*) FROM sys.conversation_endpoints WITH (NOLOCK)

WHERE far_service = @.toServiceName

AND state IN ('SO', 'CO'));

-- Create dialogs until we hit the maximum

-- This will also dictate how many activated procedures will be created for the queue

IF ( @.dialogCount < @.minDialogs)

BEGIN

WHILE (@.dialogCount <= @.maxDialogs)

BEGIN

-- Create dialogs with infinite lifetime for our pool

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE @.fromServiceName

TO SERVICE @.toServiceName

ON CONTRACT @.contractName

WITH ENCRYPTION = OFF;

SET @.dialogCount = @.dialogCount + 1;

END

-- Randomly select a dialog conversation

SET @.selectedDialog = (SELECT TOP(1) conversation_handle

FROM sys.conversation_endpoints

WHERE far_service = @.toServiceName AND state IN ('SO', 'CO')

ORDER BY NEWID());

RETURN (0);

END

|||Variuos threads/transaction calling this procedure will conflict for the same conversation and cause contention.|||

Hi Remus,

Ive solved my memory problem by reusing dialogs based upon how long they have been in use. However, now I am running into another tricky problem. What I am noticing when many messages are being passed around is that internal service broker tables are causing a huge number of locks in the database, sometimes over 100,000 of them, which will really lock up other processes on the server. How are these internal tables (QUEUE_MESSAGES_) constructed? Is it a matter of one per message received and processed? I have a feeling that it is being caused by me receiving (RECEIVE TOP(1)) one message at a time and processing that way. I know it isn't a great way to do it, and it is slower, but is it what is causing all of these internal locking in the database? BTW...reusing a dialog based upon how long it has been open was a great idea...thank you very much for it.

Tim

Conversation ID cannot be associated with an active conversation

Hi:

My service broker was working perfectly fine earlier. As I was testing...I recreated the whole service broker once again.

Now I am able to get the message at the server end from intiator. When trying to send message from my server to the intiator it gives this error in sql profiler.

broker:message undeliverable: This message could not be delivered because the Conversation ID cannot be associated with an active conversation. The message origin is: 'Transport'.

broker:message undeliverable This message could not be delivered because the 'receive sequenced message' action cannot be performed in the 'ERROR' state.

How do I proceed now ?

Thanks,

Pramod

This is happening randomly....

Now When I am sending the message I am getting this error in intiator sql profiler.

broker:message undeliverable: This message could not be delivered because the Conversation ID cannot be associated with an active conversation. The message origin is: 'Transport'....

What does this mean ?

Thanks,

Pramod

|||Did you backup, move and restore the initiator database? The error you are seeing could be produced because the initiator endpoint and the target endpoint are not in sync which could be the result of a backup/restore operation. If it is possible, can you drop all services and start all over on the two instances?|||I meant "did you backup, move and restore the TARGET database"|||

In fact I created new databases, new services, new endpoints on both sides i.e on both instances.

Pramod

|||

The problem is from the END CONVERSATION ... WITH CLEANUP. Don't use it, use simple END CONVERSATION. See this http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx

HTH,
~ Remus

|||

Remus:

I removed with cleanup in my sprocs...I notice other interesting things happening.

The error still comes up in the SQL profiler, but the message is delivered randomly.If I try sending 3 times 1 time it reachs target service broker.

One more interesting thing is my xml message which I sent is garbled in the target. Its not the way I sent to target from initiator.

Thanks,

Pramod

|||

Pramod S Kumar wrote:

...the message is delivered randomly.If I try sending 3 times 1 time it reachs target service broker.

Typically this means that there are more instances of the target service and Service Broker does a load balancing across them. Make sure you don't have the same target service in another database you forgot about. Alternatively you can specify the desired broker instance in the BEGIN DIALOG to force the selected target service.

Also, see this post here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=335683&SiteID=1

Pramod S Kumar wrote:

One more interesting thing is my xml message which I sent is garbled in the target. Its not the way I sent to target from initiator.

Can you give an example of how the payload is garbled?
Please note that Unicode XML has a Byte Order Mark (BOM) like 0xFFFE in front of the XML stream. Also, make sure you don't mix VARCHAR and NVARCHAR types when sending/receiving the message. The best practice is to always use the XML datatype for this. If the SEND payload is declared in the T-SQL batch, declare it as XML. If is a parameter sent from Ado.NET, use the System.Data.SqlDbType.Xml parameter type. Same applies to receiving the message, assign the message_body to a XML type.

HTH,
~ Remus

|||

Remus:

Prblm 1:
--
I am forcing to target service name here...Hence that should not be problem.

DECLARE @.dialog_handle uniqueidentifier,

@.msg XML

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE CLIENTSERVICE

TO SERVICE 'SERVERSERVICE'

ON CONTRACT MainContract

WITH ENCRYPTION = OFF ;

Prblm 2:
--
This works fine b/w 2 instances in local server but doesnt work b/w 2 different servers.

Here is my table structure for both target and initiator.

CREATE TABLE [dbo].[messages_log](
[logid] [int] IDENTITY(1,1) NOT NULL,
[logdata] [varchar](max) COLLATE Latin1_General_CI_AI NULL,
[msgdata] [xml] NULL,
CONSTRAINT [PK_messages_log] PRIMARY KEY CLUSTERED
(
[logid] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Thanks,

Pramod

|||

Pramod S Kumar wrote:

Remus:

Prblm 1:
--
I am forcing to target service name here...Hence that should not be problem.

DECLARE @.dialog_handle uniqueidentifier,

@.msg XML

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE CLIENTSERVICE

TO SERVICE 'SERVERSERVICE'

ON CONTRACT MainContract

WITH ENCRYPTION = OFF ;

I think you missed my point. Unless you specify a broker instance, the load balancing is probably the problem. Your script does not specify a broker instance.

Pramod S Kumar wrote:

Prblm 2:
--
This works fine b/w 2 instances in local server but doesnt work b/w 2 different servers.

Here is my table structure for both target and initiator.

CREATE TABLE [dbo].[messages_log](
[logid] [int] IDENTITY(1,1) NOT NULL,
[logdata] [varchar](max) COLLATE Latin1_General_CI_AI NULL,
[msgdata] [xml] NULL,
CONSTRAINT [PK_messages_log] PRIMARY KEY CLUSTERED
(
[logid] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

This doesn't help me in any way. I'm asking you to show me an example of how the actual XML message is different between the one you SEND and the one you RECEIVE.

|||

Remus:

Ok you meant to specify broker instance while specifying route...if that is the case here is the script..
Initiator:
CREATE ROUTE SERVERROUTE

WITH

BROKER_INSTANCE = '3F070C35-3C1E-4FA7-B654-33280DA1482B',

SERVICE_NAME = 'SERVERSERVICE' ,

ADDRESS = 'tcp://10.23.2.145:6099';

Target:
CREATE ROUTE CLIENTROUTE

WITH

BROKER_INSTANCE = '4FB2019E-D9D0-4665-9FF6-262D5C33A3D5',

SERVICE_NAME = 'CLIENTSERVICE' ,

ADDRESS = 'tcp://10.23.2.146:6022';

Ok with xml....here is the example...

Original XML

</criteria>

</filters> </queue>

XML received at target:

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

</filters>
<filters columnkey="VDATE8" datatype="0">

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

</criteria>

</filters>
</queue>

Now...when I tried today...I am not able to send any messages from target to initiator.I have also enabled message forwarding.

Thanks,

Pramod

|||

Pramod S Kumar wrote:

Remus:

Ok you meant to specify broker instance while specifying route...

Sorry about the confusion. I actually meant specifying the broker instance in the BEGIN DIALOG statement, like this:

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE CLIENTSERVICE

TO SERVICE 'SERVERSERVICE', '3F070C35-3C1E-4FA7-B654-33280DA1482B'

ON CONTRACT MainContract

WITH ENCRYPTION = OFF ;

Pramod S Kumar wrote:

Ok with xml....here is the example...

Original XML

<queue userid="23" Friendlyname="more download" TemplateName="TempDownloadReportName">

<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="YRMTH" datatype="2" grouporder="-1" summed="0" averaged="0" counted="1" />

<fields field="SLINE" datatype="2" grouporder="1" summed="0" averaged="0" counted="0" />

<fields field="VESSEL" datatype="2" grouporder="2" summed="0" averaged="0" counted="0" />

<fields field="COMMODITY" datatype="2" grouporder="3" summed="0" averaged="0" counted="0" />

<fields field="REEFER" datatype="3" grouporder="4" summed="0" averaged="0" counted="0" />

</criteria>

</filters> </queue>

XML received at target:

<queue userid="23" Friendlyname="more download" TemplateName="TempDownloadReportName">

<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="YRMTH" datatype="2" grouporder="-1" summed="0" averaged="0" counted="1" />

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="SLINE" datatype="2" grouporder="1" summed="0" averaged="0" counted="0" />

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="VESSEL" datatype="2" grouporder="2" summed="0" averaged="0" counted="0" />
</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="COMMODITY" datatype="2" grouporder="3" summed="0" averaged="0" counted="0" />

</criteria>

</filters>
<filters columnkey="VDATE8" datatype="0">

<criteria leftarg="3/26/2005 12:00:00 AM" logop="0" rightarg="4/1/2005 12:00:00 AM">

<fields field="REEFER" datatype="3" grouporder="4" summed="0" averaged="0" counted="0" />

</criteria>

</filters>
</queue>

These are not differences from Service Broker, but either from your processing or from the XML column storage in the table. If you would compare the XML in the queue itself (the one returned by RECEIVE), you'd see it identically with the one sent.

Note that XML data is not a string, you may have different representations of the same XML fragment that are equivalent.

|||

Remus:

I did make all the changes u specified.

Today I am not able to send any message when I send a message...I still have same error...in SQL Profiler.

This message could not be delivered because the Conversation ID cannot be associated with an active conversation. The message origin is: 'Transport'.

Thanks,

Pramod

|||

This means that you still have a conversation that is sending messages to it's peer conversation endpoint that was ended WITH CLEANUP.

Cleanup all databases involved (use ALTER DATABASE ... SET NEW_BROKER) and make sure that there are no more END CONVERSATION ... WITH CLEANUP in your scripts.

HTH,
~ Remus

Conversation Groups

I am thinking of updating my SQL monitoring application to use Service Broker.

Right now I loop through my list of servers performing various checks on each server. Things like 'check last database backup', 'check for new databases', 'check for server restart'. I loop through, one server at a time, doing one check at a time. The more servers I have the longer it is taking.

So, I want to multi-thread the servers, but single-thread the checks on each individual server. This way I can check say, 5 servers at a time, but on each server I will only do one check at a time. This way I won't flood an individual server with multiple checks.

Is this possible? It looks like Conversation groups might be the way to go but I'm not sure.

Conversatiuon groups are always local, as in no conversation group info ever goes across the wire with a message. The purpose of conversation groups is to lock together logically related conversations.

To have each server do only one check at a time, is eanough to restrict the activated max count to 1. This way there's at most one instance of the activated procedure doing one check. Multiple requests can be sent to the same server, they'll simply be queued up and wait their turn.

The problem you describe can also be aproached as a pub-sub problem (see https://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx). The publisher (your central administrative service) publishes 'check requests'. All instances you monitor are subscribed to this publisher and receive the request, perform the check and report the result.

HTH,
~ Remus

|||

Thanks for the reply.

I don't think I gave you enough info. All my monitoring processes are running on the same server. I connect out to the 'monitored' servers using linked servers. I can't have the queues on the 'monitored' server as most of them are SQL 2000.

The whole monitoring is run from one SQL 2005 server.

I will take a look at the article you mentioned.

Thanks

Conversation Group Question

I'm trying to use Service Broker to relate a set of messages together and was trying to use a related conversation group id. From what I can gather (looking at other threads) I can't use this.....

Basically, my ideas was.... I have several tables being updated within a database transaction. These tables will have triggers associated with them which send a message to a SB queue detailing the table that has been affected and the key information.

After the database transaction commits, I wanted to retrieve the group of messages in order to identify exactly what happened to the database during the transaction (for business reasons). I don't need necessarily need them in the same order, but do need them grouped by database transaction.

Service Broker seemed to be ideal i.e. the messages wouldn't commit if the database transaction rolled back and I wouldn't be able to access them until the entire transaction was committed........ My only problem is that I don't seem to be able to associate them with each other!!!!

Can anyone help with a way I can do this with Service Broker, or am I just trying to use the wrong technology?

If you can avoid doing the SEND in the trigger but instead do it in the batch executing the updates, then you could use a single dialog for sending all your messages as shown below:

DECLARE @.dh UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
BEGIN DIALOG @.dh ...;
UPDATE TABLE t0 ...;
SEND ON CONVERSATION @.dh ...;
UPDATE TABLE t1 ...;
SEND ON CONVERSATION @.dh...;
...
COMMIT;

If you must do the SEND in the trigger, then you will have to somehow pass the conversation handle of an opened dialog or a conversation group id to bind new dialogs to from your batch to the trigger. I suppose that will involve some sort of temporary table.

Conversation enpoints are not getting cleaned up on target end

Hi,

We are using service broker between two different instances. But were facing issues with increasing row count in conversation_endpoints view. We found that this was because we were using default value for lifetime for the conversation which is value of size int. Later on we changed the lifetime to 1 minute and conversation_endpoints view start getting cleaned up after 30 minutes

Following commands are used to send message

Before :

BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);

END CONVERSATION @.handle;

After:

BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
WITH LIFETIME = @.lifetime;

SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);

END CONVERSATION @.handle;

But as we use default life time for a long due to which around 15 million records got acumlated in this view. What is the best way to clean up this view.

END Conversation @.handle with cleanup is taking so long is their any other way to do this

Thanks,

Prashant

Not sure if this is the total cause of your problem but ending a conversation before the first message is sent on it tends to leave it in an unstable state. Remus has a good explanation here: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx|||

Thanks for information. But as of now i want to know what is the best way do cleanup on sys.conversation_endpoints with 14 million rows

|||

I see - not interested in doing it right but doing it wrong faster.

I assume you have a script that loads all the dialog handles into a cursor and then calls END DIAlOG WITH CLEANUP on each one. If you want your application to continue working while you are cleaning up then that's the only way. If you can shut down your applicationso that there are no dialogs or messages that you care about then an ALTER DATABASE command with the SET NEW_BROKER parameter will blow away all traces of any dialogs and messages.

|||

If you moved to SP2, you can do ALTER DATABASE ... SET NEW_BROKER, it will truncate every relevant internal table (conversation_endpoints, conversation_groups, transmission_queue and all message queues).

Do not attempt this with pre-SP2 SQL 20005 because it will literaly do 14 mil END CONVERSATION ... WITH CLEANUP in one transaction lasting forever.

Note that NEW_BROKER will nuke every conversation, including currently active ones. If you cannot afford this, then you must END conversations individually, if you batch commit it doesn't take that long actually.

|||

Thanks for the instant replies. Unfortunately we cannot shutdown the application here. So i think only option left is using WITH CLEANUP. But this might be a help in sometime in future. Thanks again for wonderful upport

|||In that case, be sure to use Remus' suggestion of batch commits. END a few hundred conversations and then commit the transaction. This is much more efficient than doing each END CONVERSATION in its own transaction.

Sunday, March 11, 2012

Control SQL Server Service Remotely

I want to start, pause and stop others SQL Server computer remotely from
my computer.
How to do that?
ThanksRobert
There are several ways this can be acheived and here are a just a few of the
options:
Using SQL Server Enterprise Manager Right Click on the SQL Server instance
you want to control the Service of ie. Stop | Pause | Start. To Control SQL
Server Agent, expand the Server | Management node and Right Click SQL Server
Agent.
Using the SQL Server Service Manager (sqlmangr.exe) Select the Server and
the Service you wish to control and the action to take.
Using the Services Controller (sc.exe) command line utility (replacing
\\servername with the server of the server to stop the service on and
service_name with the name of the service to stop)
sc \\servername stop service_name
sc \\servername start service_name
sc \\servername pause service_name
sc \\servername continue service_name
- Peter Ward
WARDY IT Solutions
"Robert Lie" wrote:

>
> I want to start, pause and stop others SQL Server computer remotely from
> my computer.
> How to do that?
> Thanks
>|||search key word 'remote' in books online,you will find the answer.
"Robert Lie" <robert.lie24@.gmail.com>
':uPUmuwbWFHA.1404@.TK2MSFTNGP10.phx.gbl...
>
> I want to start, pause and stop others SQL Server computer remotely from
> my computer.
> How to do that?
> Thanks

Control SQL Server Service Remotely

I want to start, pause and stop others SQL Server computer remotely from
my computer.
How to do that?
Thanks
Robert
There are several ways this can be acheived and here are a just a few of the
options:
Using SQL Server Enterprise Manager Right Click on the SQL Server instance
you want to control the Service of ie. Stop | Pause | Start. To Control SQL
Server Agent, expand the Server | Management node and Right Click SQL Server
Agent.
Using the SQL Server Service Manager (sqlmangr.exe) Select the Server and
the Service you wish to control and the action to take.
Using the Services Controller (sc.exe) command line utility (replacing
\\servername with the server of the server to stop the service on and
service_name with the name of the service to stop)
sc \\servername stop service_name
sc \\servername start service_name
sc \\servername pause service_name
sc \\servername continue service_name
- Peter Ward
WARDY IT Solutions
"Robert Lie" wrote:

>
> I want to start, pause and stop others SQL Server computer remotely from
> my computer.
> How to do that?
> Thanks
>
|||search key word 'remote' in books online,you will find the answer.
"Robert Lie" <robert.lie24@.gmail.com>
?:uPUmuwbWFHA.1404@.TK2MSFTNGP10.phx.gbl...
>
> I want to start, pause and stop others SQL Server computer remotely from
> my computer.
> How to do that?
> Thanks

Control SQL Server Service Remotely

I want to start, pause and stop others SQL Server computer remotely from
my computer.
How to do that?
ThanksRobert
There are several ways this can be acheived and here are a just a few of the
options:
Using SQL Server Enterprise Manager Right Click on the SQL Server instance
you want to control the Service of ie. Stop | Pause | Start. To Control SQL
Server Agent, expand the Server | Management node and Right Click SQL Server
Agent.
Using the SQL Server Service Manager (sqlmangr.exe) Select the Server and
the Service you wish to control and the action to take.
Using the Services Controller (sc.exe) command line utility (replacing
\\servername with the server of the server to stop the service on and
service_name with the name of the service to stop)
sc \\servername stop service_name
sc \\servername start service_name
sc \\servername pause service_name
sc \\servername continue service_name
- Peter Ward
WARDY IT Solutions
"Robert Lie" wrote:
>
> I want to start, pause and stop others SQL Server computer remotely from
> my computer.
> How to do that?
> Thanks
>|||search key word 'remote' in books online,you will find the answer.
"Robert Lie" <robert.lie24@.gmail.com>
':uPUmuwbWFHA.1404@.TK2MSFTNGP10.phx.gbl...
>
> I want to start, pause and stop others SQL Server computer remotely from
> my computer.
> How to do that?
> Thanks

Sunday, February 19, 2012

Consuming Web Services In Sql CLR

I am using SQL Server June CTP and I have a CLR stored procedure that is consuming a web service that has 3 methods:
1) GetXml - returns xml data as string.
2) GetXsd - returns xsd as string.
3) GetData - returns a dataset.

I am only using the GetData method to retrieve data and do some processing in the stored procedure. When I try to deploy the assembly, I get the following error:

CREATE ASSEMBLY failed because method "add_GetXmlCompleted" on type "SqlServerAssembly.EsoDataWebService.ESODataSet" in external_access assembly "SqlServerAssembly" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies. SqlServerAssembly

Am I trying to do something over here that's not possible or not allowed in Sql CLR? Thanks!

Deploy your assembly as UNSAFE, instead of EXTERNAL_ACCESS. That will take care of that issue.
Niels
|||I tried deploying the assembly as UNSAFE and now I am getting this error:

Could not load file or assembly '1316 bytes loaded from Microsoft.VisualStudio.DataTools, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. An attempt was made to load a program with an incorrect format.|||I guess you have an app.config that is causing this issue. Remove that from your project and deploy again.
To make it work in external access follow the steps in the blog:
http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx

Thanks,
-Vineet.|||Thank you Vineet! I was able to deploy the assembly with 'Unsafe' permission level after deleting the app.config file. But, now when I try executing that stored procedure from Management Studio, I get the following error:

Msg 6522, Level 16, State 1, Procedure TestSproc, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'TestSproc':

System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

System.IO.FileLoadException:

at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)

at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)

at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)

at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)

at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)

at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] source

...

System.InvalidOperationException:

at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)

at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)

at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)

at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)

at System.Web.Services.Protocols.SoapClientType..ctor(Type type)

at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()

at SqlServ...

|||For XML Serialization (required for calling web services) you need to pregenerate the serializer assembly and register it in the database. You can generate the serialization assembly using a tool called sgen that is shipped with .NET Framework SDK.

>sgen.exe myAsm.dll

Where myAsm.dll is the assembly that you want to use inside SQL Server and contains code that is calling webservices. If you have installed Visual Studio 2005, you would usually find sgen at C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. When you run sgen, it would generate an assembly with the name myAsm.XmlSerializers.dll.

Once you have these two assemblies - myAsm.dll and myAsm.XmlSerializers.dll, you need to register them in SQL Server as follows:

CREATE ASSEMBLY myAsm from ‘<path>\myAsm.dll’

with permission_set = EXTERNAL ACCESS

CREATE ASSEMBLY myAsmXml from ‘<path>\myAsm.XmlSerializers.dll’

with permission_set = SAFE
To automate this in visual studio, follow the instructions in the blog: http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx
Thanks,
-Vineet.

|||Thank you once again Vineet! Now the stored procedure executes without any errors!

Consuming Web Services from the SQL CLR

Hi there,

I've been following Vineets and David's procedures to consume web

services using SQL CLR to the t. I created my web service in C#.NET

2005, and generated my proxy using this command:

wsdl /par:oldwsdlconfig.xml /o:ExactMobileService.cs /n:Project360.SmsService http://www.exactmobile.co.za/interactive/interactivewebservice.asmx

I added both files to the project, set Generate Serialization Assembly to on and compiled it.
I then generated a strong name key for the assembly and signed my assembly with that key.
Inside my post-build event I added the following script:

"E:\Development\Microsoft

Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force

/compiler:/keyfile:SmsServiceKey.snk /t:StoredProcedures

$(TargetDir)$(TargetName).dll

This compiled into my assembly, the XmlSerializer assembly and then added strong name key to both.

In SQL Server 2005, I enabled CLR, made my DB trustworthy, created my

first assembly with permissions EXTERNAL ACCESS and then the

XmlSerializer assembly with permissions SAFE. I created my stored

procedure and ran it. When I did I got this error which I assumed the

XmlSerializer was supposed to solve for me:

System.InvalidOperationException:

Cannot load dynamically generated serialization assembly. In some

hosting environments assembly load functionality is restricted,

consider using pre-generated serializer. Please see inner exception for

more information. > System.IO.FileLoadException: LoadFrom(),

LoadFile(), Load(byte[]) and LoadModule() have been disabled by the

host.

I have seen alot of posts about this error, but none of them has been able to solve my problem.

Please can you help me?

O'Connor

Hi O'Connor,

As you have experienced, there are a few cases where sgen does not solve the problem of dynamically loading the XmlSerializer assembly.There is usually always a possible workaround, but it can vary depending on exactly what your code is doing.I was intending on writing some more blog posts about solutions to these dynamic assembly loading problems, but haven't had the time to finish them yet.

The problem you're most likely hitting is that the code doing the XmlSerialization uses an XmlSerializer constructor that does not check for the pre-generated (sgened) assemblies.This could be either directly in your code, or in other .NET Framework code that does this, such as in a strongly typed ADO.NET Dataset.This is covered in the msdn documentation for XmlSerializer if you look at the "Dynamically Generated Assemblies" section http://msdn2.microsoft.com/en-us/library/system.xml.serialization.xmlserializer.aspx

If this is indeed the problem, then the easiest way to fix this is to modify your code to instead use one of the constructors that does check for a pre-generated XmlSerializer assembly.This might be more difficult if it is not your code that is directly calling the XmlSerializer constructor.If you can include the full callstack of the error message you shared below and a snippet of the code where the XmlSerialization is happening, I should be able to help you fix it.

Steven

|||Hi Steven,

Thanks for getting back to me in such a short time.
I was playing with it last night and finally got it working.
My types aren't complex, I only use strings within my assembly (as it is a sms service)
What I did eventually do to fix it was to remove the post build script and set the "Generate Serialization Assembly" on the build tab of the project properties to ON.
It seems that using the sgen tool, the serialization assembly wasn't generated exactly the way that the CLR required it.

I found this website to be very useful in the end:
http://www.u2u.be/Article.aspx?ART=WebServicesinSQL05

Regards,
O'Connor

|||

Hello Steven,

I am currently facing exactly the same strange problem as the initiator of this thread. I want to call a simple webservice from a stored procedure in SQL 2005. I've already tried the different ways of getting the serializer assemblies (using sgen and the build option in VS 2005). I also registered both assemblies one after another in in my SQL 2005 database. The db is trustworthy, assemblies are both marked with "external_access".

But I keep getting the Dynamic Load exception. In your previous post you said you could help if you have the full callstack; here it is...

Msg 6522, Level 16, State 1, Procedure CreateNotification, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'CreateNotification':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. > System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.

System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
at System.CodeDom.Compiler.Code
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, compilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at MAN.Applications.HFIF.Database.ClrExtensions.WebServiceSoapClient..ctor(String ...

How can I change the behaviour of my soap client class to use a particular XmlSerializer constructor that checks for pre-generated assemblies? Could you please give me a hint?

Thanks very much for your help. Frank

Consuming Web Services from the SQL CLR

Hi there,

I've been following Vineets and David's procedures to consume web

services using SQL CLR to the t. I created my web service in C#.NET

2005, and generated my proxy using this command:

wsdl /par:oldwsdlconfig.xml /o:ExactMobileService.cs /n:Project360.SmsService http://www.exactmobile.co.za/interactive/interactivewebservice.asmx

"E:\Development\Microsoft

Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force

/compiler:/keyfile:SmsServiceKey.snk /t:StoredProcedures

$(TargetDir)$(TargetName).dll

This compiled into my assembly, the XmlSerializer assembly and then added strong name key to both.

In SQL Server 2005, I enabled CLR, made my DB trustworthy, created my

first assembly with permissions EXTERNAL ACCESS and then the

XmlSerializer assembly with permissions SAFE. I created my stored

procedure and ran it. When I did I got this error which I assumed the

XmlSerializer was supposed to solve for me:

System.InvalidOperationException:

Cannot load dynamically generated serialization assembly. In some

hosting environments assembly load functionality is restricted,

consider using pre-generated serializer. Please see inner exception for

more information. > System.IO.FileLoadException: LoadFrom(),

LoadFile(), Load(byte[]) and LoadModule() have been disabled by the

host.

I have seen alot of posts about this error, but none of them has been able to solve my problem.

Please can you help me?

O'Connor

Hi O'Connor,

Steven

|||Hi Steven,

I found this website to be very useful in the end:
http://www.u2u.be/Article.aspx?ART=WebServicesinSQL05

Regards,
O'Connor

|||

Hello Steven,

But I keep getting the Dynamic Load exception. In your previous post you said you could help if you have the full callstack; here it is...

How can I change the behaviour of my soap client class to use a particular XmlSerializer constructor that checks for pre-generated assemblies? Could you please give me a hint?

Thanks very much for your help. Frank

consuming sqlserver 2005 webservice from asp.net 1.1

Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:

Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).

Thanks,

Olja

Yes, but if you are using the WSDL to generate stub class code, then you will need to retrieve the simple WSDL (ie. http://server/url?wsdlsimple). For additional information regarding simple WSDL please refer to MSDN article http://msdn2.microsoft.com/en-us/library/ms175476.aspx

If you are using a .Net Frameworks 1.1 DataSet object to serialize the result from a SELECT statement, please note that .Net Frameworks 1.1 DataSet XML serialization is not fully compatible with SQL Server 2005 Native Web Services. Please use VS 2005/.Net Frameworks 2.0.

Jimmy

consuming sqlserver 2005 webservice from asp.net 1.1

Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:

Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).

Thanks,

Olja

Jimmy

Consuming from Web Service and Load data into SQL

Hi:

Can someone help me with a SSIS package that would consume from a Web Service (in fact two of them) and then load the data into SQL Server. I currently have Web Service task which connects to ForEachLoop task, and inside the loop task, I have a DFT. I am thinking, I would need to call the webservice utilizing the Web Services Task, and then store the output in a Full ResultSet variable. In my loop, I would like to loop thru the resultset, and store the data into SQL server. Inside the DFT, how would I construct this mechanism? Also, is this a good way to consume from a Web Service and then populate SQL Server? Are there any alternate ideas on this? Any documentation on this yet? Thanks.

MA,

Let me clarify. Do you want to consume data from a web service from within the data-flow?

-Jamie

|||

Well, the goal is to call a web service, and pump data into SQL server, although I thought it's less complex to hook up to a Web Services Task, and then utilize the output from the Web Services within a DFT somehow but not sure. Is there a better way to do this. Thanks.

|||

I think so, yes. It is possible to consume data from a web service from directly within the pipeline. What you are proposing would be an extra step.

To consume from a web service in the pipeline you will need a script component. Donald Farmer's book (http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254/ref=pd_bbs_sr_1/104-7087211-5731917?ie=UTF8&s=books&qid=1181582618&sr=8-1) has a chapter explaining how to do it.

-Jamie

|||

It looks like utilizing the XML Adapter task in the DFT, would allow us to read data from a Variable, not sure how this feature works, but will provide comments, once its working for me. My goal is to avoid using the script component, and utilize existing tasks to accomplish this goal, lets see where I get with that :-)

|||

MA2005 wrote:

It looks like utilizing the XML Adapter task in the DFT, would allow us to read data from a Variable, not sure how this feature works, but will provide comments, once its working for me. My goal is to avoid using the script component, and utilize existing tasks to accomplish this goal, lets see where I get with that :-)

Fair enough. I think that's a worthy aim.

Out of interest, why do you not want to use the script component?

-Jamie

|||

No reason, just exploring an alternate solution. :-)

Friday, February 10, 2012

Constant Disk Activity

While running MSDE it looks like the service hits the disk every 30
45 seconds. Any hints as to if / how to configure the service to not
hit the disk so often?
Do you have any reason to believe that is kind of activity is unusual? Are
there any clients connected to the MSDE instance that are doing extracting
data or doing updates or deletes? If data pages are being changed the the
Lazy Writer will be flushing them out to disk on a periodic basis.
Jim
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405181249.8c0609f@.posting.google.co m...
> While running MSDE it looks like the service hits the disk every 30 -
> 45 seconds. Any hints as to if / how to configure the service to not
> hit the disk so often?
|||Our application only connects with and interacts with the database
infrequently. We are seeing this disk activity constantly, whether
there is any application currently accessing the database or not.
|||You might try using Filemon from www.sysinternals.com. It's a free utility
that can show any file acivity on your system by any process, including SQL
Server. You can use it to see exactly what process is hitting the file
system and what file it is accessing.
Jim
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405191328.657e38c3@.posting.google.c om...
> Our application only connects with and interacts with the database
> infrequently. We are seeing this disk activity constantly, whether
> there is any application currently accessing the database or not.
|||ryan_columbus@.agilent.com (Ryan Columbus) wrote:
>While running MSDE it looks like the service hits the disk every 30
>45 seconds. Any hints as to if / how to configure the service to not
>hit the disk so often?
Why are you worrying about this? What problem are you trying to solve?
- Tim Roberts, timr@.probo.com
Providenza & Boekelheide, Inc
|||The hard drives that our product will be running on have a finite
lifetime (i.e. only a certain number of continuous hours of
operation). With MSDE constantly accessing the disk, the disk is
never able to spin down. Thus, the life of our product will be
significantly shortened if we cannot stop MSDE from making these
constant disk accesses.
|||We have used a similar tool to determine that MSDE is constantly
hitting the disk. It doesn't really matter which file it is
accessing. What we need is to find a way to stop MSDE from causing
this constant disk activity.
|||I'd check if the autoclose database option is turned on...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ryan Columbus" <ryan_columbus@.agilent.com> wrote in message
news:d1ec1be8.0405181249.8c0609f@.posting.google.co m...
> While running MSDE it looks like the service hits the disk every 30 -
> 45 seconds. Any hints as to if / how to configure the service to not
> hit the disk so often?

Tuesday, March 27, 2012

conversion tools for sql7 databases

Tuesday, March 20, 2012

Conversations

Conversation ID cannot be associated with an active conversation

Conversation Groups

Conversation Group Question

Conversation enpoints are not getting cleaned up on target end

Sunday, March 11, 2012

Control SQL Server Service Remotely

Control SQL Server Service Remotely

Control SQL Server Service Remotely

Sunday, February 19, 2012

Consuming Web Services In Sql CLR

Consuming Web Services from the SQL CLR

Consuming Web Services from the SQL CLR

consuming sqlserver 2005 webservice from asp.net 1.1

consuming sqlserver 2005 webservice from asp.net 1.1

Consuming from Web Service and Load data into SQL

Friday, February 10, 2012

Constant Disk Activity

Consolidation Query

Blog Archive

About Me