End Conversation : Security Context Could Not Be Retrieved

Apr 13, 2007

HI



I have a service broker setup between 2 remote server. The message send does get sent to the target, but I am having a problem where the end conversation message from the target is failing. I did a trace on both the target and the source server. here's what I found



On the Target Server:

on Broker: Message undeliverable --- This message could not be delivered because it is a duplicate



On the Source Server

on Broker: Message undeliverable --- This message could not be delivered because the security context could not be retrieved,



I do not understand why the message is delivered, but the end conversation message is not getting thru. On the Target transmission_queue. I have millions of messages like this



conversation_handle to_service_name is_end_of_dialog message_body transmission_status

E0C69E8F-37E9-DB11-AB7A-00145E7A209C source 1 NULL



I reinstalled the broker several times, but always get this problem.



thanks



Paul

View 9 Replies


ADVERTISEMENT

Security Context Could Not Be Retrieved

Mar 23, 2006

some time back i had problem with sending the service broker messages on remote machine,

I had some security issues and they were resolved by the help of Remusu.

since the ip of the remote machine was changed in between so i just re-executed the same scrips which used to successfully send messages on the remote machine.(I just updated the IP in the route )

To my surprise same script did not work now where as no change have been made.

I am doing the following:

1.Created the certificate and end point on the sender side. back up the certificate in a file and copied to the other machine.
same step was repeated for receiving side as well.

2.both the side i created the certificates using the back up files from other sidend proper authorization

3.Then I created the database,route,messagetypes,contract,queues,services etc both the side.

4. then i setup dialog security(ie.created the dialog security certificates both the sides and back up them). Later I copied these back up files to each other, create some dialog user and create certificate using authorization to these remote dialog users created.

5.I also created remote service binding on both the sides and granted send permission to the remote dialog user.

When i send the message from sender to the receiver, and run the profiler, I see that on the sending side none of the broker event gives any error.

In the recever side I get the followng Event:
Broker:Message Undeliverable

This message could not be delivered because the security context could not be retrieved.
Error 11229.


I m surprised that the same script was run in the same order,Why was it running before and not now.

I also checked the End points using telnet and they seem to be fine. Also the firewall was "Off" on both the machines i.e. there was no change in system state also. 

Please provide the solution. Thanks in advance.

View 12 Replies View Related

In Service Broker Message Dialog Security Is Not Available For This Conversation...

Jul 23, 2007

when ever I send my message thru Service Broker I am getting an error message like this "



"Dialog security is not available for this conversation because there is no remote service binding for the target service. Create a remote service binding, or specify ENCRYPTION = OFF in the BEGIN DIALOG statement."



This I found in sys.transmission_queue

Please reply with your comments

View 1 Replies View Related

Trigger Security Context

Oct 5, 2006

I'm currently creating a database that will only allow data operations through stored procedures, ie users will not be able to directly modify tables. I'd like to use an Insert trigger which will run in response to a stored procedure that inserts records. The trigger will check business logic and additionally modify records in a couple of other tables. Given that I've disallowed direct access to the tables, will it run or will the security set-up prohibit that?Thanks in advance for any answers.

View 5 Replies View Related

SQLServeragent Security Context Does Not Have....

Feb 2, 2004

Hi,

We have a sqlserver 7 on windows nt 4.
We'd just change a startup account for sqlserver agent to a
not domain admin for security reason. The startup account for sqlserver service is
still domain admin. We are now getting the following error every 5 second
in our application event log.

"SQLServeragent security context does not have server-autorestart privileges"

Is there a way to get rid of this error without putting a domain admin as
a startup account for sqlserver agent?

Ted

View 1 Replies View Related

C++ Assemblies And Security Context

Mar 23, 2008

I have two questions.



How can I run my C++ assembly in safe mode?

I'm building a SQL function from C++ and compile my assembly with /clrafe. After that I create the assembly with SAFE permission sets and create the function, both in SQL. But the server don't accept load or execute the function because of permission error. It says:



Code Snippet

"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65561. The server may be running out of resources, or the assembly may not be trusted with PERMISSIONSET = EXTERNALACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues."






Does the terminology coince with the technology, or is it diffrent "safety" conditions we are speaking about? One for the SQL permission set, and another safety for CLR type-safe assemblies?



Anyway, after playing around a bit, and following the tricks from this thread. I managed to load and execute the function, but only in UNSAFE permission (unrestricted mode?)



---8<----



My second question is can I use unrestricted mode for assembly compiled with /clr (mixed CLR)?



I'm asking, because I have some C source code that I want to reuse, and for this I can't be type-safe and therefore need to compile only with /clr (mixed CLR).

Should I go back to the plain C API in SQL 2000 to implement such function in SQL 2005, and ignore all new things in c++/CLR/.NET?



If you have any points to C++ sample code, then please forward this to me.

View 4 Replies View Related

Xp_cmdshell Security Context Problem

Jan 15, 2008

I am trying to run the following set of commands on our SQL 2005 SP2 server:


declare @cmd varchar(1000)
select @cmd = '%SystemRoot%system32cscript.exe %SystemRoot%system32iisvdir.vbs /create TestSite testvirtualdir C:Inetpubwwwrootfiles'

exec master..xp_cmdshell @cmd


The objective is to programmatically create an IIS virtual directory from within a stored proc. I already have a proxy account configured using the sp_xp_cmdshell_proxy_account proc. The SQL server is a member server to a domain and the proxy account is a member of Domain Admins (per iisvdir.vbs requirement). When I login to the server as the proxy account and run the dos command in a window it executes just fine. However, the above SQL statements result in the following in server management studio:

"You cannot run this command because you are not an administrator on the server you are trying to configure."

If the xp_cmdshell runs under the proxy account, and the proxy account has been verified to be able to login and run the script independently, what gives?


Edit:
Just to make sure the proxy account was setup properly, I used the following cmd

select @cmd='set'

and it's results set shows

USERNAME=sqladmin

which is indeed the name of the proxy/windows account that I can run the dos script under when logged into windows...

View 5 Replies View Related

SQL 2005, OPENQUERY And Security Context Switching

Oct 4, 2007

Hi,

I need to allow only a specific group of users to use a linked server. Rather than granting each user access in the linked server I create and give access to a local SQL login "link_user" and then grant the group impersonate to "link_user". The folllowing statements then work fine:


EXECUTE AS LOGIN = 'link_user'

SELECT * FROM OPENQUERY(linked_server, 'SELECT * FROM ...)


But when I use the exact same statements in a stored procedure I get the folllowing error:


Msg 7416, Level 16, State 1, Procedure linktest2, Line 5

Access to the remote server is denied because no login-mapping exists.


I need to be able to work with the result set which is why I use OPENQUERY rather than EXECUTE... AS... AT which works fine also in stored procedures!?

Is there another workaround then to create all users in the linked server security?

Rgds
Bertrand

View 1 Replies View Related

DB Design :: How To Switch Security Context In A View

Jun 5, 2015

I have 2 databases one for datawarehouse and one for reporting.

I created a view in the reporting database joining several tables from datawahourse. The view is meant for a users who uses powerpivot. It will contain a few million of rows.

I set up a role in the reporting database. I assigned select rights to that role on the view.

When the  user wants to access the view, he can see the view but a select gives

The server principal "pp_user" is not able to access the database "datawarehouse" under the current security context.

How can I fix this ? I don't want to give the user select rights on tables in the datawarehouse.

View 3 Replies View Related

SQLServerAgent Security Context Does Not Have Server Autorestart Privileges

Oct 21, 1999

SQL Server in on a ‘member’ server in my company domain (We took the ‘stand-alone’ option when installing NT on this server).

I have set up an NT domain account for SQL ServerAgent ‘Service startup account’ which is a different account than the NT domain account listed in the SQL Server Properties, Security tab, ‘Startup service account’.

I log on to this server with the login in the latter.

Replication is working OK, but my application log keeps filling up with the message “SQLServerAgent security context does not have server autorestart privileges”.

What have I done wrong?

Thanks,
Judith

View 1 Replies View Related

Current Security Context Not Trusted When Using Linked Server From SAP

Dec 31, 2006

Hello,

I am experiencing a head-scratcher of a problem when trying to use a Linked Server connection to query a remote SQL Server database from our SAP R/3 system. We have had this working just fine for some time, but after migrating to new hardware and upgrading OS, DBMS, and R/3, now we are running into problems.

The target database is a named instance on SQL Server 2000 SP3, Windows 2000 Server. The original source R/3 system was also on SQL Server 2000 (SP4), Windows 2000 Server. I had been using a Linked Server defined via SQL Enterprise Manager (actually defined when the source was on SQL Server 7), which called an alias defined with the Client Network Utility that pointed to the remote named instance. This alias and Linked Server worked great for several years.

Now we have migrated our R/3 system onto new hardware, running Windows Server 2003 SP1 and SQL Server 2005 SP1. I redefined the Linked Server on the new SQL 2005 installation, this time avoiding the alias and referencing the remote named instance directly, and it tests out just fine using queries from SQL Management Studio. It also tests fine with OSQL called from the R/3 server console, both when logged on as the application service account with a trusted connection, and with a SQL login as the schema owner. From outside of the application, I cannot make it fail. It works perfectly.

That all changes when I try to use the Linked Server within an SAP custom program (ABAP), however. The program crashes with a database interface error. The database error code is 15274, and the error text is "Access to the remote server is denied because the current security context is not trusted."

I have set the "trustworthy" property on the R/3 database, I have ensured the service account is a member of the sysadmin SQL role, I've even made it a member of the local Administrators group on both source and target servers, and I've done the same with the SQL Server service account (it uses a domain account). I have configured the Distributed Transaction Coordinator on the source (Win2003) system per Microsoft KB 839279 (this fixed problems with remote queries coming the other way from the SQL2000 system), and I've upgraded the system stored procedures on the target (SQL2000) system according to MS KB 906954. I also tried making the schema user a member of the sysadmin role, but that was disastrous, resulting in an instant R/3 crash (don't try this in production!), so I set it back the way it was (default).

What's really strange is no matter how I try this from outside the R/3 system, it works perfectly, but from within R/3 it does not. A search of SAP Notes, SDN forums, SAPFANS, Microsoft's KnowledgeBase, and MSDN Forums has not yielded quite the same problem (although that did lead me to learning about the "trustworthy" database property).

Any insight someone could offer on this thorny problem would be most appreciated.

Best regards,

Matt

View 12 Replies View Related

Unable To Access The Database Master Under The Current Security Context.

Jun 16, 2007

I have a login that is mapped to a Windows sysadmin account. I used it to login to Sql Server 2005. I then created a database called Freedom. I then added a Windows login and user called FreedomAdmin, with Freedom set as the default database. When I login in to Windows using FreedomAdmin and then try to login in to Sql Server 2005, I get the following error:

The server principal "FREEDOM1FreedomAdmin" is unable to access the database "master" under the current security context.

If the default database for FreedomAdmin is Freedom (and it is - I checked from my sysadmin login account), why can't I login. Must I give FreedomAdmin permissions to master?

View 6 Replies View Related

Question On Internal Activation Stored Procedure Security Context

Jun 25, 2006



CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:

SqlCommand command = Connection.CreateCommand();

command.CommandText = "CREATE ASSEMBLY " + """ + AsmName + """ +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";

command.ExecuteNonQuery();

I am getting the following error:

"Could not impersonate the client during assembly file operation."

The CLR function is invoked from Service Broker internal activation stored procedure.

"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.

SqlContext.WindowsIdentity.Name is "NT AUTHORITYSYSTEM" as the Data Engine runs with the LocalSystem account.

How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?

Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".

Many Thanks.

View 13 Replies View Related

Linked Server Issue Using Logins Current Security Context

Nov 9, 2007

From my desktop in Management Studio (MS) I can connect to server A and run a query like,
select top 1 * from serverB.mydatabase.dbo.mytable, and all seems to be working. If I leave and come back after a few minutes it no longer works and I get this error:
Msg 18452, Level 14, State 1, Line 1

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

If I remote connect to server B and run the query it works and when I try it from my desktop MS it starts working again. Any ideas on why this is happening?

View 8 Replies View Related

User Is Not Able To Access The Database Model Under The Current Security Context

Feb 7, 2008

I have a restriced user on SQL Server that is only permitted to creat a new database and manage it only. All other database are hidden to that user.

when that user login to SQL Server and create a database and try to change the default folder path for data and log files, gets an error,

The Server principal "User" is not able to access the database "model" under the current security context. (MSSQL Server, Error: 916)

Any idea???

Thanks,

View 7 Replies View Related

The Server Principal XYuser Is Not Able To Access The Database Ydb Under The Current Security Context

May 23, 2007

SQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:



The server principal "XYuser" is not able to access the database "Ydb" under the current security context



This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john

View 17 Replies View Related

Access To The Remote Server Is Denied Because The Current Security Context Is Not Trusted

Jun 27, 2007

I am unable to run a stored procedure that accesses a linked Oracle database as a dataset for a deployed report in Reporting Services. I receive the following error:



An error has occurred during report processing.

Query execution failed for data set 'spName'.

Access to the remote server is denied because the current security context is not trusted.



A few facts ...

The report previews fine in Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) report designer with VS being installed on my local machine and a shared data source referencing Server1
The deployed report on Server2 produces the error
The stored procedure code is similar to the following:

SELECT x.FieldName as OracleField

,y.FieldName as SSfield

FROM OracleLinkedServer..DatabaseName.TableName x

LEFT JOIN Server1.dbo.TableName y

The deployed report runs fine when the stored procedure code is changed as follows. This indicates to me that the problem is truly an Oracle connection issue;

SELECT 'ABC' as OracleField

,y.FieldName as SSfield

FROM Server1.dbo.TableName y

The Oracle database is a linked database on both SQL Server1 and on SQL Server2 database engines. (I don't see where in Server2 Reporting Services I can specify additional server connections.)
I've tried to trick Mr. Reporting Services on Server2 by creating a @L_TempTable in my stored procedure, inserting the data into it, then selecting from a SQL Server1.dbo.TableName LEFT JOIN @l_TempTable, but Server2 still knows I'm trying to get out to Oracle and rewards me with the error.
I can effectively run the Server1 stored procedure script on Server2 through MSSMS -- it's just the deployed report that references the stored procedure that doesn't work
The data source properties of the deployed report are as follows:

Select -- A Custom Datasource

Connection Type: Microsoft SQL Server

Connectoin String: Data Source=Server1;Initial Catalog=DatabaseName

Select -- Credentials stored securly in the report server

Username: UserName

Password: Password

Check -- Use as Windows credentials when connecting the the data source

Check -- Impersonate the authenticated user ...



(Please do not get sidetracked on the 'A Customer Datasource' selection above. I'd rather be using a shared data source, but right now, this is just the way it is.)



Anyone's expertise would be greatly appreciated. We have a great data warehouse but sometimes you just have to go back to the Oracle ERP and slug it out. What do I need to do to get a deployed report to effectively read from a stored procedure that references an Oracle table?



To everyone, thanks for reading!



~ Delora









View 2 Replies View Related

Access To The Remote Server Is Denied Because The Current Security Context Is Not Trusted.

Aug 25, 2006

Hello,

In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:

The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.
The remote database is set to Trustworthy.
I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.
I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.

Thank you for your help on this - Amos.

View 17 Replies View Related

Access To The Remote Server Is Denied Because The Current Security Context Is Not Trusted

Jun 27, 2007

I am unable to run a stored procedure that accesses a linked Oracle database as a dataset for a deployed report in Reporting Services. I receive the following error:



An error has occurred during report processing.

Query execution failed for data set 'spName'.

Access to the remote server is denied because the current security context is not trusted.



A few facts ...

The report previews fine in Microsoft Visual Studio 2005 Version 8.0.50727.762 (SP.050727-7600) report designer with VS being installed on my local machine and a shared data source referencing Server1
The deployed report on Server2 produces the error
The stored procedure code is similar to the following:

SELECT x.FieldName as OracleField

,y.FieldName as SSfield

FROM OracleLinkedServer..DatabaseName.TableName x

LEFT JOIN Server1.dbo.TableName y

The deployed report runs fine when the stored procedure code is changed as follows. This indicates to me that the problem is truly an Oracle connection issue;

SELECT 'ABC' as OracleField

,y.FieldName as SSfield

FROM Server1.dbo.TableName y

The Oracle database is a linked database on both SQL Server1 and on SQL Server2 database engines. (I don't see where in Server2 Reporting Services I can specify additional server connections.)
I've tried to trick Mr. Reporting Services on Server2 by creating a @L_TempTable in my stored procedure, inserting the data into it, then selecting from a SQL Server1.dbo.TableName LEFT JOIN @l_TempTable, but Server2 still knows I'm trying to get out to Oracle and rewards me with the error.
I can effectively run the Server1 stored procedure script on Server2 through MSSMS -- it's just the deployed report that references the stored procedure that doesn't work
The data source properties of the deployed report are as follows:

Select -- A Custom Datasource

Connection Type: Microsoft SQL Server

Connectoin String: Data Source=Server1;Initial Catalog=DatabaseName

Select -- Credentials stored securly in the report server

Username: UserName

Password: Password

Check -- Use as Windows credentials when connecting the the data source

Check -- Impersonate the authenticated user ...



(Please do not get sidetracked on the 'A Customer Datasource' selection above. I'd rather be using a shared data source, but right now, this is just the way it is.)



Anyone's expertise would be greatly appreciated. We have a great data warehouse but sometimes you just have to go back to the Oracle ERP and slug it out. What do I need to do to get a deployed report to effectively read from a stored procedure that references an Oracle table?



To everyone, thanks for reading!



~ Delora









View 1 Replies View Related

SQL 2012 :: Access To Remote Server Is Denied - Current Security Context Is Not Trusted

Feb 10, 2014

We've got 3 SQL Servers all SQL Server 2012. We have a Master server and 2 production servers (let's call them A and B). The Master has linked servers defined for both A and B in an identical way.

A and B both have the same databases and each has a Stored Proc that gets called by the Master server.
An SQL Agent job executes the stored proc on both A and B.
It works fine against A but not B.

If we execute the stored proc in a query window under our windows account (domainowner) it works, if we use SetUser to pretend to be the SQL Server Agent account (let's call it domainagent) A works and B doesn't.

Executing against B returns the error "Access to the remote server is denied because the current security context is not trusted."

domainagent is a sysadmin on both Server A and B.

Trustworthy is set to ON in both the Master Server Database it's executing from and the remote databases on both A and B.

All 3 databases have the same owner (let's call it domainowner).

View 5 Replies View Related

For Remus - Access To The Remote Server Is Denied Because The Current Security Context Is Not Trusted.

Aug 25, 2006

Remus (or anyone who can help) -

I am sorry for posting this question here. The only reason I am doing so is because Remus answered a question similar to this late last year, but I couldn't get access to the whole thread.

In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:

a.. The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.
b.. The remote database is set to Trustworthy.
c.. I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.
d.. I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.


Thank you for your help on this - Amos.

View 1 Replies View Related

Error 15274: Access To The Remote Server Is Denied Because The Current Security Context Is Not Trusted.

Sep 12, 2007

Hi,

I have a stored procedure servicing a queue (sql 2005).
The stored procedure updates some data in a different server and database.
If the queue contains messages and I execute manually the stored procedure, I don't have any problem at all.
However, if I set the queue to use the same stored procedure, it gives me the following error:

Error 15274: Access to the remote server is denied because the current security context is not trusted.

Any ideas?

Thanks,

Aron

View 4 Replies View Related

Error Log Peppered With --&&> 'The Conversation Handle Is Missing. Specify A Conversation Handle.'

Dec 3, 2007

Hi

I'm using service broker and keep getting errors in the log even though everythig is working as expected

SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.

I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.

In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'

error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'

I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?

i can provide code of the stored procs if that helps.

thanks.

View 10 Replies View Related

Conversation ID Cannot Be Associated With An Active Conversation

Apr 19, 2006

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

View 14 Replies View Related

Conversation Handle Reuse And Conversation Handle XXX Not Found

Jan 18, 2008



We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?

Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?

I have attached an example of one of the queuing procs below:




Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);

SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)

END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;

View 2 Replies View Related

Can A Value Be Retrieved From An Insert?

Aug 12, 2005

I need to return the "autoID" number that is created at the time of an insert. Is there way of doing that?

Thanks,
Ed

View 4 Replies View Related

How Do You SUM The Fields Retrieved By COUNT?

Mar 15, 2004

I am trying to do this in MS Access actually but it is an SQL question.

This query:

SELECT LoanNo, COUNT(LoanNo) AS 'Count'
FROM DedupTest031504
GROUP BY LoanNo
HAVING COUNT(LoanNo) > 1;


returns:
LoanNo Count
46690128 2
46861821 2
47762138 3
47762154 3
48257239 2
48257663 2
48257719 2
48258143 2
48258167 2

which is correct but how do you SUM the COUNT field? In other words, I want the total number of duplicate records in the table. Is there another way alltogether?

I tried:

SELECT LoanNo, SUM(COUNT(LoanNo))
FROM DedupTest031504
GROUP BY LoanNo
HAVING COUNT(LoanNo) > 1;

but I get a "CANNOT HAVE AGGREGATE FUNCTION IN 'SUM(COUNT(LoanNo))' error.

Thanks.

ddave

View 12 Replies View Related

Increment The Retrieved Value Using @@identity

Jul 12, 2007

hi,

i really need ur help..

I am creating an a stored procedure which could insert new records, the identity of the primary key in my table is not incremented. i want to increment the id when adding a new record in the stored procedure. what would be the system for that?

thanks

Funnyfrog

View 10 Replies View Related

Updating Retrieved Records SQL Problem

Feb 15, 2008

Hello,
I am retriving records on a page, I am able to make changes cuase the data is displayed in textboxes, my question is how to save the changes I made in the database.
 Also in my below code for retriving the data what statement do I need to add so that I can use a QuerryString to filter the results. ex (default.aspx?CID=1) and only records that
match that would display.
Here is my code: for retriving data
Private Sub displayrecord()SqlConnection1.Open()        Dim SqlDataAdapter1 As New SqlDataAdapter("SELECT * FROM table1", SqlConnection1)        Dim objReader As SqlDataReader        Dim SqlCommand1 As New SqlCommand("SELECT * FROM table1", SqlConnection1)        SqlDataAdapter1.Fill(dsMember)        objReader = SqlCommand1.ExecuteReader()        objReader.Read()        txtbox_confirmpass.Text = objReader("MemberPassword")        txtbox_name.Text = objReader("MemberName")        txtbox_birthdate.Text = objReader("MemberDOB")        txtbox_email.Text = objReader("MemberEmail")        Dropdown_Gender.SelectedValue = objReader("Gender")             objReader.Close()        SqlConnection1.Close()    End Sub
Private Sub btnUpdate_Click

View 2 Replies View Related

Track All Records Retrieved From Search

Jun 29, 2004

Hi,

I have a table full of items which can be searched.
I also have another table with the ID of each item and columns for no of times details shown, no of times saved etc.

What I would like to do is increment a value in this second table for each item every time it is returned in a search.

What would be the best way to do this?

(Im using a Stored Procedure)

Thanks in advance,

Pete

View 5 Replies View Related

Saving The Rows Retrieved From Sql Svr 05 In Excel

May 17, 2008

Hi,
I need to save all the rows that are retrieved from the sql server 2005 db into any external file such as excel.

Can any one give me any clue?


Gaurish Salunke
Software Developer
OPSPL

View 3 Replies View Related

Wrong Record Retrieved After Move To SQL Svr

Jul 20, 2005

I've moved the database tables from the .mdb file to Sql Svr and now I havean *intermittent* problem. When I select a record from a combo box, it willintermittently pull up the wrong record. The code that does the search isbelow. I have a column named 'ProsId' that is a unique number (identitycolumn). The error -- when it happens -- seems to only happen on higherProsId numbers. At first it appeared to me as some sort of data conversionthing, but I'm not so sure now. Here's some examples:Record selected Record actually displayed---------------- -------------------------100,000 41,85959,073 15,79457,000 11,27357,001 11,27445,000 45,000 <--- appears to return correctvalue every time. Numbers lower than this also seem to return the correctrecord every time.Private Sub cboFindProsName_AfterUpdate()' Find the record that matches the control.Dim rs As ObjectIf Len(Trim(Me.cboFindProsName.Text)) > 0 ThenSet rs = Me.Recordset.Cloners.FindFirst "[ProsId] = " & (Me![cboFindProsName])Me.Bookmark = rs.BookmarkEnd IfEnd SubMany Thanks!

View 5 Replies View Related

How To Display Data Retrieved In DataGridView Using SQL With Vb.net?

Feb 27, 2008

hi, could anyone tell me how to display data retrieved using sql in DataGridView?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved