Remote Messages Not Working – Message Vanishes
Jan 9, 2006
I need some assistance with getting service broker to work across servers. I have 2 separate servers and I am trying to send a message from one server to the other. No error is generated when sending the message and I get no records in the transmission queue of the sending server, however the message does not arrive on the receiving server. Where is the message going?
Here is the code I use to create the service broker objects that are being used€¦
RUN THIS ON THE SENDING SERVER:
CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE
CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE
GO
CREATE CONTRACT [MyContract] (
MyMessage SENT BY INITIATOR,
MyResponse SENT BY TARGET)
GO
CREATE QUEUE [MyInitiatorQueue] with status = ON
CREATE QUEUE [MyTargetQueue] with status = ON
GO
CREATE SERVICE [MyInitiatorService] ON QUEUE [MyInitiatorQueue]
GO
CREATE ROUTE [RouteToODS]
WITH
SERVICE_NAME = N'MyTargetService',
BROKER_INSTANCE = '1BB213E2-67A7-4059-BAF8-D9B5F31E358E',
ADDRESS = N'TCP://CONSULT01:4022'
GO
CREATE ENDPOINT DWHEndPoint
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED)
GO
RUN THIS ON THE RECEIVING SERVER:
CREATE MESSAGE TYPE [MyMessage] VALIDATION = NONE
CREATE MESSAGE TYPE [MyResponse] VALIDATION = NONE
GO
CREATE CONTRACT [MyContract] (
MyMessage SENT BY INITIATOR,
MyResponse SENT BY TARGET)
GO
CREATE QUEUE [MyInitiatorQueue] with status = ON
CREATE QUEUE [MyTargetQueue] with status = ON
GO
CREATE SERVICE [MyTargetService] ON QUEUE [MyTargetQueue] ([MyContract])
GO
CREATE ROUTE [RouteToDWH]
WITH
SERVICE_NAME = N'MyInitiatorService',
BROKER_INSTANCE = 'F0BF4E80-704E-4CFE-80FC-637A1EC128C5',
ADDRESS = N'TCP://DWH:4022'
GO
CREATE ENDPOINT ODSEndPoint
STATE = STARTED
AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER (
AUTHENTICATION = WINDOWS,
ENCRYPTION = DISABLED)
GO
SEND A MESSAGE USING THE FOLLOWING:
Declare @ConversationHandle uniqueidentifier
Begin Transaction
Begin Dialog @ConversationHandle
From Service [MyInitiatorService]
To Service 'MyTargetService'
On Contract [MyContract]
With Encryption = Off,
Lifetime = 600;
Send on Conversation @ConversationHandle
Message Type [MyMessage] (N'This is a my message')
End Conversation @ConversationHandle
Commit
Select GET_TRANSMISSION_STATUS(@ConversationHandle)
View 21 Replies
ADVERTISEMENT
Feb 14, 2006
I was trying to send a message on a different instance in the network using service broker. I have created the endpoint and route both the side. I could see that the messages are in the transmission_queue in the sender side with no error in the status.
What could be the reason.
I am doing the following:
CREATE ROUTE inst02
WITH
SERVICE_NAME = 'SERVICE2',
ADDRESS = 'tcp://10.14.43.149:2341'
in the send script i am using this:
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract] WITH ENCRYPTION = OFF ;
in the sending side if i change the endpoint's authentication as windows kerberos , then i get thefollowing error in the transmission queue:
"Connection handshake failed. An OS call failed: (8009030e) 0x8009030e(No credentials are available in the security package). State 66."
I also have given access to the remote user on this endpoint(on the recever side) using this :
GRANT CONNECT ON ENDPOINT::Endpoint_test to paras
I am executing the sending side send script using the same user wich has access to the remote endpoint.
Can some one resolve this issue.
Thanks
View 9 Replies
View Related
May 30, 2008
Hi,
To be more clear. My problem is :
1) I launched the stataments behing via sql server studio :
Use AutClust
GO
SET NOCOUNT ON;
Set statistics IO ON
GO
select * FROM table_1;
Go
Set statistics IO OFF
GO
2) I have then obtained in tab message the result displayed in text format in tab messages :
Table 'table_1'. Scan count 1, logical reads 490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The objective is to implement those statements in a stored procedure that will be able to recover in automatic manner the content of tab messages.
This text is a system response connected to the statements above. I think that exist a system variable which contain the result displayed in tab messages.
I tried tu use @@rowcount it deos not work because this system variable return number of row affacted by last statement.
Does it exist another system variable that could contain the text which is in tab messages?
I do need a solution for this problem.
Please bring me your help.
Thanks for your help.
Tietie.
View 2 Replies
View Related
May 29, 2008
Hi,
I execute a query via queries editor embedded in sql srver studio.then i have query result in results tab and a text message in tab messages. Does it exist a T-SQL sys global variable as of i could to retrieve the text message that is displayed in tab messages?
To be more clear. My problem is :
1) I launched the stataments behing via sql server studio :
Use AutClust
GO
SET NOCOUNT ON;
Set statistics IO ON
GO
select * FROM table_1;
Go
Set statistics IO OFF
GO
2) I have then obtained in tab message the result displayed in text format in tab messages :
Table 'table_1'. Scan count 1, logical reads 490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The objective is to implement those statements in a stored procedure that will be able to recover in automatic manner the content of tab messages.
This text is a system response connected to the statements above. I think that exist a system variable which contain the result displayed in tab messages.
I do need a solution for this problem.
Please bring me your help.
Thanks for your help.
Tietie.
Thanks in advance.
Tietie
View 3 Replies
View Related
Oct 13, 2006
Hi
I am trying to deploy our service broker application into our live enviroment. I have been using the excellent service listings manager to configure security and routes when deploying to our test enviroments and had no problems. However, after having gone through the same steps in live, i can not get it working.
The messages I try sending from the remote initiating service arrives at the target service, but the ack is not sent back from the target service. I have used profiler to find out what is going on and noticed that on the target after the ack is received, a broker:message classify event occurs with an event sub class of 1 - Local. It then starts going through lots of broker: Message undeliverable events. From what I understand this means that the target service has classified that the reply to the message should be sent to a local service instead of a remote service which is not the case.
I think the remote route is set up correctly on the target server. I have run this query on the target server after one message has been sent
select r.*
from sys.conversation_endpoints e
inner join sys.routes r on e.far_service = r.remote_service_name and e.far_broker_instance = r.broker_instance
and it returns the row from sys.routes that points to the initiating service.
Can anybody help with what else I can do to solve this problem?
Antony
View 3 Replies
View Related
Oct 24, 2006
an error has occurred while establishing a connection to the server.when connneting to sql server 2005,this failure
may be caused by the fact that under teh default settings sql server dose not allow remote connectings.(provider:
sql network interfaces,error:26-error locating server/instance specified)
when connect to database,the error message shows up.Help!
View 1 Replies
View Related
Jun 22, 2006
Good day good people of the forum thanks for the response this is the error message i encountered "an error occured while attempting to validate the setup source files unc path. verify that you have entered a valid unc path."
View 1 Replies
View Related
Jul 28, 1999
Hi We have SQL6.5 servers with serpack3. I have registered the remote sql servers on both sqlservers. I have login name as same and trusted.(by the way I did this through GUI)
when I run a remote procedure it comes and tell me
**********************************************
DB-Library: Unexpected EOF from SQL Server. General network error. Check your documentation.
Net-Library error 10054: ConnectionCheckForData ((null)()).
DB-Library Process Dead - Connection Broken
*************************************************
???????
in some servers I did the same configuration it works fine.
All the servers are configured Uniqly. Protocols and dblib were same.
Can anyone Please...Please>>please explain how to configure this or is there any other way to execute a remote procedure.......?????
I really, infact really appreciate any help regarding this.
Thanks a lot.
Mini
View 1 Replies
View Related
Jul 27, 2006
I notice when I copy an SSIS package 'A' to a new package 'B', the new package 'B' will generate a "login failed for user" message in the data flow components. To copy I "save copy of Package 'A' as."
Some config info:
Package ProtectionLevel = EncryptSensitiveWithPassword
Connections are Data Sources
Connection strings with password are stored using PackageConfigurations to an SQLServer table. I've verified Package 'A' is in fact using the config table (e.g. it is not using a password or user stored in the package)
Data connectios are all SQLServer Native OLE DB Client
The account is an SQLServer account (not integrated security)
The original Package 'A' works flawlessly and I get success when I test the connections in Package 'B'.
But executing package 'B' I get: The error message I get is: [Connection manager "MyConnection"] Error: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'dwuser'.".
Does anyone know why this would occur and/or how to work around it? I saw another thread where a potential workaround is to create a new data flow task and copy all the data flow components to that task. That won't work well for us because the data flow is moderately complex and when you copy and paste it, SSIS completely re-orders the layout.
This is a typical data warehouse ETL setup where there is a master package that executes child packages (e.g. 'A', and 'B' mentioned above) that each perform the ETL for a specific dimension or fact table.
Thanks for any help,
Lee Cascio
View 3 Replies
View Related
Dec 13, 2007
Hi,
In my localmachine everything is working fine. But online only Update, Delete and Select statements are working correctly, while INSERT doesn't work and I got a similar error to this when I try to insert any data into any table:
Server Error in '/' Application.
Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.The statement has been terminated. 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: Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.The statement has been terminated.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): Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447
System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72
System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation) +388
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +602
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
So wat's wrong?
Note:1-The Insert statement work correctly in localmachine.2-The application and the sql database are both in remote hosts.3-The application configuration had been checked and is correct.
View 3 Replies
View Related
Oct 5, 2006
Im getting quite desperate and frustrated!! I have installed SQL 2005 Express successfully and can connect to it fine using Management Studio locally.
If I try and connect remotely using Management Studio Im getting the old Error 10061 : The machine actively refused the connection!
I have set it up exactly as per all documentation and help from the forums. Remote connections is on TCP and Named Pipes, I even have the firewall disabled.
Im running it on Windows Server 2003 Standard.
Any help would be really apriciated as i have tried everything!!!
View 17 Replies
View Related
Jun 22, 2007
I have a SQL Server 2000 cluster running on x64 OS. I found the threads in the forum to run perfmon locally by using the x86 version of perfmon (mmc /32 perfmon). However, I cannot run a perfmon remotely from another machine and see the SQL Server perfmon data on any of the nodes in the cluster. The remote perfmon picks up all of the other perfmon variables but no SQL.
I found another thread where somebody asked this question but it wasn't answered. Thanks in advance.
Mark
View 4 Replies
View Related
Feb 20, 2007
I am trying to set up a ODBC connection to a remote database using the ODBC data source administrator. I am getting an error message complaining of an invalid instance(). The database I am trying to connect to does have a default instance and a named instance. Do I need to specify the instance name somewhere in the connection settings? I tried specifying the server name as <Remote computer IP Address>/<Instance Name> but this didn't work. Any ideas?
View 4 Replies
View Related
May 16, 2007
Hello Every one,
here to food for SSIS gurus,
I am preparing SSIS package programmetically using C#, in a web wethod, to perform fuzzy lookup and other transformations in to sql server data.
now situaltion is like this,
there are three system (independent m/cs)
m/c 1 :- webserver
m/c 2 :- sql database (sqlserver)
m/c 3 :- client (it can be any where , we are using onc click deplyment)
Now case is like this.
A user sitting at m/c no. 3 (a thin client) has one-click-deployed application, clicks the button "Create SSIS Package" , which sends this message to webserver (i.e. m/c 2 ) where C# code constructs the package programmatically,
there are three component inside package
1. oldedb Source (a sql server table -- sqlserver is at m/c no 3)
2. fuzzylookup component
3. oledb destination (a sql server table -- sqlserver is at m/c no 3)
Now 1 and 3 component of the package need to connect to sqlserver for initialization there metadata from actual tables.
here is code :
public IDTSComponentMetaData90 oledbDest = null;
oledbDest = dataFlowTask.ComponentMetaDataCollection.New();
oledbDest.Name = "Destination";
oledbDest.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
CManagedComponentWrapper srcDesignTime = oledbDest.Instantiate();
srcDesignTime.ProvideComponentProperties();
oledbDest.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[connName].ID;
oledbDest.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[connName]);
srcDesignTime.SetComponentProperty("AccessMode",0);
srcDesignTime.SetComponentProperty("OpenRowset",tableName);
// Tries to connect to sqlserver hosted at different m/c
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
now the 3rd last line which says srcDesignTime.AcquireConnections(null);
tries to open the connection to the selserver (in order to get information abt actual table and its columns) which is installed at another m/c, and this is possible cause of error (i think !!)
there are two cases in one case i am getting an error and in second case it is working just fine..
case 1. If i use (local webservice) web service to create package it works.
case 2 if i use published (on local IIS ) webservice to create package, It fails (at the code srcDesignTime.AcquireConnections(null); )
so i think problem is related to some authorization, may be i am wrong !!1
Please help me.
thanks in advance
Pradeep
(I am really looking for a reply for MR. Jamie Thomson )
View 3 Replies
View Related
Jan 20, 2004
I am having problem creating/copying "txt" file in a remote machine by using the "exec master..xp_cmdshell" command in query analyzer. For example, we are getting errors for the following command:
exec master..xp_cmdshell 'copy d: estingauthors.txt \<MyServerName><myFolderName>authors.txt'
(but exec master..xp_cmdshell 'copy d: estingauthors.txt d: esting1authors1.txt' is working without any problems)
Any ideas?
View 7 Replies
View Related
Jan 20, 2004
I am having problem creating/copying "txt" file in a remote machine by using the "exec master..xp_cmdshell" command in query analyzer. For example, we are getting errors for the following command:
exec master..xp_cmdshell 'copy d: estingauthors.txt \<MyServerName><myFolderName>authors.txt'
(but exec master..xp_cmdshell 'copy d: estingauthors.txt d: esting1authors1.txt' is working without any problems)
Any ideas?
View 2 Replies
View Related
Dec 20, 2006
Hello,
I have a bundling package that runs about 20 other packages. It has been working fine for a while but a couple of days ago it fail with the following message,
Error 0x800706BE while loading package file "D:PackagesToradSales.dtsx". The remote procedure call failed.
I´m running the SSIS packages in an 64-bit environment.
Thankful for help with this!
//Patrick
View 3 Replies
View Related
Apr 5, 2007
Hi,
I am getting an error while communicating between SvrA and SvrB. I am able to see the messages in the sys.transmission_queue on serverA. On server B could not see the message.
I ran the SQL Server Profiler to trace the events and on 'Broker/Broker:Message Undeliverable' event following error message is displayed :
"This message could not be delivered because an internal error (code 15404, state 19) was encountered while processing it. Check the error log for more information."
Where is this error log located? Is there any way to pinpoint exact error.
Any help is greatly appreciated
Thanks,
Don.
View 3 Replies
View Related
Apr 2, 2008
Hello, please help!!
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
kind regards,
David Weeden
Database Developer
View 2 Replies
View Related
Jul 23, 2007
In my SQL Server, I see the below message in the Application Event Viewer
"18265 :
Log backed up: Database: HSD, creation date(time): 2007/01/06(05:05:05), first LSN: 1439:495055:1, last LSN: 1439:496675:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQLBACKUPHSDHSD_tlog_200707141300.TRN'})."
When I save the application event viewer and open it in another server, I do not see the above message, instead I get the following message:
" Can't find or load message DLL. Message DLL must be in path or in current directory."
Any thoughts to overcome this problem is appreciated.
Thanks
Santhosh
View 3 Replies
View Related
Aug 8, 2007
I run SB between 2 SQL servers. In profiler on an initiator side I see next error: 'This message could not be delivered because its message timestamp has expired or is invalid'. For the conversation we use best practice, i.e. target closes a conversation. Target side succeed to close conversation, but initiator still stay in DO (disconnect_outbound).
What is a reasone for the error? What to do?
View 5 Replies
View Related
Aug 8, 2007
I see in profiler this error: "This message could not be delivered because its message timestamp has expired or is invalid"
What is a reason for error?
View 1 Replies
View Related
Nov 17, 2006
Hello,
I am having trouble specifying a message body that is valid. I mean for the client to send. If I leave it as null then everything is ok but if I create a memorystream and add a line of text it reports back it did not pass validation. I do not understand this and am not sure what to do. I need to send a message based on a code and text but do not know the format of the body that is allowed. The code I am refering to comes out of HelloWord_CLR because that is what I am formating my sample after. I call it the same way it calls the return message done in ServiceProc. I need to know the message format including body since this does not seem to work. A sample of the call is bellow.
// Create an empty request message
string Msg = "Hello";
MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(Msg));
Message request = new Message("Request", body);
Thanks,
Scott Allison...
View 1 Replies
View Related
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Jun 2, 2015
- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?
- what is the impact of changing the remote query timeout (on the same page) from default value?
View 4 Replies
View Related
Aug 23, 2006
Fellow Devs,
I have an instance of SQL Server Express 2005 running on another box and I have Remote Connections enabled over both TCP/IP and Named Pipes, but on my other box I keep getting the error that the server does not accept Remote Connections.
Any ideas why this might be happening? Is there some other configuration?
View 25 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Oct 11, 2007
HiI'm going insane.I have a club starter kit on my local server which is working fine.I have migrated the database to my remote host and seems to have transfered ok.For a brief period I was able to connect to the database remotely, but now I cannot I get the following, even though SQL Server Surface Area Config is set to accept remote connections.An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)This is my connection string;<add name="ClubSiteDB" connectionString="Data Source=xx.xxx.xx.xx,xxxx; Network Library=DBMSSOCN; Initial Catalog=tets; User ID=sqladminbob;Password=sqlbob4398;" providerName="System.Data.SqlClient="/>I hav also tried to use aspnet_regsql.exe to add the remote provider but I get the same message. Thanks
View 3 Replies
View Related
Nov 10, 2006
I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.
I can create a linked server using the option "Be made using the login's current security context" but can not when trying to specify the security context, i.e. sa and the sa password. When I try I get the following message:
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.
I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.
The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:
Executed as user: NT AUTHORITYSYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.
I'm sure the two errors are related. Any ideas what is going on?
View 7 Replies
View Related
Feb 15, 1999
Trying to obtain a full list of SQL 6.5 error messages into a file. There are
2,215 messages present in master.sysmessages, but there is a total of 2,733
messages listed in the dialog box within enterprise manager. Message numbers
17026 thru 19020 don't seem to be stored in the sysmessages table.
Anyone know how I could obtain an extract to obtain a full list of messages ?
View 2 Replies
View Related
Apr 15, 2008
After sending a message the message is just gone.
Our transmissionand and out our transactionqueue is empty.
This is our CLIENT:
CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML ;
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[InitiatorQueue] ;
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue];
GO
drop endpoint SqlEndpoint
CREATE ENDPOINT SqlEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL)
FOR service_broker(AUTHENTICATION = WINDOWS)
GO
use master
GRANT CONNECT ON ENDPOINT:qlEndpoint to public
GO
use test1
DROP ROUTE SqlRoute
GO
CREATE ROUTE SqlRoute
AUTHORIZATION [dbo]
WITH
SERVICE_NAME = 'TargetService',
BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66',
ADDRESS = 'TCP://localhost:5900';
GO
GRANT SEND ON SERVICE::[TargetService] to public
create master key
encryption by password = 'azertyuiopqsdfghjklm'
go
SERVER
CREATE MESSAGE TYPE HelloWorldMessage
VALIDATION = WELL_FORMED_XML ;
GO
CREATE CONTRACT HelloWorldContract
( HelloWorldMessage SENT BY INITIATOR);
GO
CREATE QUEUE [dbo].[InitiatorQueue] ;
GO
CREATE SERVICE InitiatorService
ON QUEUE [dbo].[InitiatorQueue];
GO
drop endpoint SqlEndpoint
CREATE ENDPOINT SqlEndpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 1235, LISTENER_IP =ALL)
FOR service_broker(AUTHENTICATION = WINDOWS)
GO
use master
GRANT CONNECT ON ENDPOINT:qlEndpoint to public
GO
use test1
DROP ROUTE SqlRoute
GO
CREATE ROUTE SqlRoute
AUTHORIZATION [dbo]
WITH
SERVICE_NAME = 'TargetService',
BROKER_INSTANCE = '80EFDE56-4088-4015-B6C0-D12285C60F66',
ADDRESS = 'TCP://localhost:5900';
GO
GRANT SEND ON SERVICE::[TargetService] to public
create master key
encryption by password = 'azertyuiopqsdfghjklm'
go
Hopefull you have an idea?
View 1 Replies
View Related
Dec 4, 2007
I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for :
1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well.
2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters.
3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken.
I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure :
CREATE PROCEDURE sp_hitMe@ntid varchar(10),@page varchar(50),@thisHit datetimeASSET NOCOUNT ON
DECLARE @tempDate datetimeDECLARE @yearCount intDECLARE @monthCount intDECLARE @inMonth varchar(20)DECLARE @monthColumn varchar(10)SET @inMonth = DATENAME(mm, @thisHit)SET @monthColumn = CASE WHEN @inMonth = 'January' THEN 'hitsInJan' WHEN @inMonth = 'February' THEN 'hitsInFeb' WHEN @inMonth = 'March' THEN 'hitsInMar' WHEN @inMonth = 'April' THEN 'hitsInApr' WHEN @inMonth = 'May' THEN 'hitsInMay' WHEN @inMonth = 'June' THEN 'hitsInJun' WHEN @inMonth = 'July' THEN 'hitsInJul' WHEN @inMonth = 'August' THEN 'hitsInAug' WHEN @inMonth = 'September' THEN 'hitsInSep' WHEN @inMonth = 'October' THEN 'hitsInOct' WHEN @inMonth = 'November' THEN 'hitsInNov' WHEN @inMonth = 'December' THEN 'hitsInDec' END DECLARE @insString varchar(500)DECLARE @updString varchar(500)SET @insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @monthColumn + ') VALUES (' + @ntid + ', ' + @page + ', ' + @thisHit + ', 1, 1)'
if exists(select * from tblTracking where ntid = @ntid and @page = page) begin if exists(select * from tblTracking where lastVisit = @thisHit) begin -- DO NOTHING! end else begin DECLARE @theColumn varchar (100) SET @theColumn = 'SELECT ' + @monthColumn + ' FROM tblTracking WHERE ntid = @ntid AND @page = page' SET @yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @ntid AND @page = page) + 1 SET @monthCount = (Exec @theColumn) SET @monthCount = @monthCount + 1 SET @updString = 'UPDATE tblTracking SET lastVisit = ' + @thisHit + ', hitsThisYear = ' + @yearCount + ', ' + @monthColumn + ' = ' + @monthCount + ' WHERE ntid = @ntid AND @page = page' Exec @updString end endelse begin Exec @insString endGO
And to my surprise, the only 3 errors I got were :
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39Incorrect syntax near the keyword 'end'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45Incorrect syntax near the keyword 'Exec'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50Incorrect syntax near the keyword 'end'.
However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?
View 9 Replies
View Related
Sep 26, 2005
i'm trying to make private messages like the one in this site, i didnt start programming yet, the problem is in the DB schema, i have 3 tables (Users, Messages, MessageDetails) i think the problem that the UserID is related to the other two Tables, so to know the sender and reciever, when i try to view all messages for specific user and show all users who sent it, it give nothing back, so any help in the DB, thanx 4 help.
View 13 Replies
View Related