All -
I have a SQL Server 2005 HTTP endpoint. I create a transaction scope in a client application and call two methods on that endpoint. Will the operations performed in the two methods, really, be in a transaction?
In other words, will SQL Server enlist in the ambient transaction even when I am accessing it through a HTTP endpoint?
Thanks.
I developed one DLL in vb .Net which call two web services (sql endpoints) I wish to use this solution but for hundreds of servers (endpoints) I want to ask if I could do that with dynamic parameters passed to de DLL CALL in sql server clr.
Actually when I compile de DLL I need to have referenced (hardcoded) the webservices (endpoints) and when I need a brand new endpoint I need to reference it, compile the dll and release.
I cannot find how to invoke the service within the DLL with the namespace or webservice name as parameter.
Mi code to invoke now my referenced end point is: (note that my endpoint name is rperez)
Dim CallWebService As New rperez.IntelisisServiceEndPoint
CallWebService.Credentials = System.Net.CredentialCache.DefaultCredentials
Call CallWebService.IntelisisService(ArchivoOriginal)
My class rperez.IntelisisServiceEndPoint is referenced (hardcoded) in the dll project I need it dynamic
Do I need to change my code to PROXY object ?
Can anyone direct me to a code that would automatically apply transaction logs to a DB in the standby server? We have a process that dumps the transaction log backup from the primary server into the backup server every hour on the hour, but I need to apply that transaction log as soon as it is in the standby server.
I am sure someone will ask, why not do transactional replication or log shipping? My answer to that is I have yet to learn how to setup replication between servers. I need to get our backup server up and running in the next few days.
Thanks in advance for your help.
Helen
How do I stop a remote query that INSERTS into a local table from being automatically "upgraded" to a distributed transaction?
I am using Windows 2000 server and SQL Server 2000 SP3a on both machines.
I am executing the following statement in Query analyzer.
INSERT MyLocalServer (col1)
EXECUTE MyRemoteServer.Master.dbo.sp_executesql
@RemoteQuery,
@ParameterDefinition,
@Paramter = 'somevalue'
@RemoteQuery consists of a SELECT four-table join, all tables are on the same linked server.
The Linked server has been set up on MyLocalServer using the "Microsoft OLE DB for SQL Server" provider. In the "Provider Options" for the linked server properties I checked "Non transacted updates" and "dynamic parameters". In the "Server Options" tab I have checked "RPC", "RPC Out", "Data Access".
The EXECUTE part of the query runs great (and returns the data very fast) by itself. But with the INSERT part, the query fails and returns the error:
"Server: Msg 7391, Level 16, State 1, Line 17
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
The two servers are seperated by firewalls, so I believe the reason the query is failing is that I haven't followed the procedures for setting up the ports etc described in one of the microsoft support articles: e.g.: 250367.
Configuring the ports involves too much company politics, and besides, for what this query does, it does not need the benefits of a distributed transaction.
How can I execute my query without SQL Server automatically trying to upgrade it to a distributed transaction?
More Info: I can execute the query as a straight INSERT/SELECT linked-server query and it does the INSERT on the local SQL Server just like I want it to, so I assume it is not trying to use distributed transactions; but it takes around 7 seconds to run even though the entire SELECT is executed on the linked server, whereas executing with sp_executesql takes only 1 second.
I thought selected "non-transacted updates" in the provider would solve this problem, but it did not.
Anyone know the answer?
When I write code for a multiple statements transaction do I need to check 'if @@ERROR > 0 ' after each SELECT, INSERT, DELETE or UPDATE statement so that the 'rollback tran' statement can be given, or SQL server will automatically rollback the transaction and we don't need to check for @ERROR > 0 ?
View 2 Replies View RelatedHi
View 7 Replies View RelatedWe have tested mirroring with success.
Now we want to completly disable mirroring, and have done this on all mirrored database.
It seems though that the server stille maintains the mirror endpoints, hence you can do a "telnet localhost 5022".
How can we disable the mirroring endpoints ?
Martin Tan
SQL DBA
I am planning to delete a login from SQL logins because he moved out from project .when i try to delete the login , it throws an error saying " The server principal owns an endpoint and cannot be dropped , error 15141 "
Same problem facing on different servers.
Note : Environment is SQL 2012,SQL 2008 including cluster servers .
I am new to web services and as a DBA I only have limited .net experience. Our development team is creating a web services interface for our new IVR phone system, and we've decided on a solution that will send each of the phone call parameters from our database to the phone system in an XML based on line number and extension. I've found that Http Endpoints in sql server will be a perfect solution for exactly that and I won't need any .net to develop it. Thats the good news. There are, however, other requirements for our phone system to communicate back to our database. All of the examples I've seen for endpoints use a stored procedure performing a select statement.
Is it possible to create an endpoint that references a stored procedure performing an insert or update? To be more specific, can I create an endpoint that references a stored procedure that has parameters? If so, how do I pass those parameters through a web service request? Is it as simple as adding &variable="value" to the url?
If so I could develop this entire solution from the database side and not involve our .net programmer!
Another alternative: the phone system does have it own database in sql server. I'm not sure if our phone system provided can or will do this, but if they are willing to work with this, maybe a service broker can fit into this solution.
I look forward to any response. Thanks!
Hi,
I am experiencing some problems calling reports in ReportServer using URL.
I have several multivalue parameters that are not being set through the URL. The values for this parameters should be the default ones that I have set in VS.
When I preview the report in VS, the default values are correctly set but, when using the URL call, I keep being prompt for this parameters and the report is not rendered until I set them manually.
Does anyone ever had this problem?
Thank you in advance
Catarina Ribeiro
SELECT MAX(ID)
FROM [LinkedServer].[Database].dbo.[TableName] (NOLOCK)
WHERE <Condition>
The above SQL Script ran successfully up to yesterday. But today its throws the below error message.
Remote table-valued function calls are not allowed.
Now i have modified the SQL script as follows
SELECT MAX(ID)
FROM [LinkedServer].[Database].dbo.[TableName] WITH (NOLOCK)
WHERE <Condition>
I want to know how the 1st SQL script runs successfully up to yesterday.
How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
View 7 Replies View RelatedDear all,
I am very new to the subject of writing CLR code inside SQL Server, so I apologise if my questions seem naive.
I have a requirement to populate an asp.net 2.0 GridView control with data columns, some of which are directly from a SQL Server 2005 database, but some of which are calculated by calling CLR methods passing the values from the database columns to those methods.
However, the methods I need to call only make sense in the process context of the client web site which is calling the stored procedure which I want to write to return the data columns.
In effect, I want to be able to make a remote procedure call from within the SQL Server CLR code to the methods available in the client process.
Is this possible? If so, could someone please refer me to an example of how to do it.
If it can be done, it opens up lots of very cool possibilities!
Thanks.
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
When I constructed my endpoint:
CREATE ENDPOINT MyNewEndPoint
STATE = STARTED
AS HTTP
(
PATH = '/MyEndpoint',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = 'arkansas'
)
FOR SOAP
(
WEBMETHOD 'GetSession'
(NAME='MyDB.dbo.spGetSession'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'MyDB',
NAMESPACE = 'http://MyDB/MyEndpoint'
)
the site is hardcoded. But, when I'm getting ready to deploy I'll need the site to be my production site. Is there an easier way to accomplish this then to edit the script each time before delpoyment?
TIA,
Dave C.
Are HTTP Endpoints able to be reached by another application that does not run on the same network?
For example, we have a partner that has a Web Application that would like to retrieve data from our database. We would like them to call a SOAP method through the Endpoint and in essence let their Web Application consume the Web Service. Is this possible?
I tried asking a similar question over at the asp.net, but I'm not getting any replies.
I created an endpoint in SS 2005 using DIGEST authentication, and I was successful in adding the web service to my project and getting results from a call to it.
However, the production environment does not exist in a domain environment, which eliminates even DIGEST (which requires a valid windows domain logon).
But, when I create the endpoint using BASIC authentication, I can no longer "find" the service. SS says the command(s) completed successfully after the Create Endpoint command. As a test, the documentation says that you can enter the http site into IE and the WSDL will display. And that works in digest mode. However, I've tried both:
http://<server>/path?WSDL and
https://<server>/path?WSDL
And neither returns the WSDL in IE (nor can it be added to my project as a web service).
I'm hoping someone has some ideas on how I can resolve this problem.
TIA,
Dave
Hi All,
I have mirroring set up in a test environment and it works great however I need to know/understand how the endpoints are encrypted. I have them set to use the AES algorithm; however I can not tell from BOL what keys they are using. I know the service master key is the root encryption key and endpoints sit at the server level (same level as service masterkey) but I am not sure if the endpoints use it to perform the encryption or not; do they and if not what do they use?
Thanks in advance,
Coleman
hi,
I am new to sql so i created this http endpoint int sql 2005.
now how to use it?
for example i create stored procedure and i can use it by "execute procedurename"
how it works on endpoint side?
Hi,
I am trying to use a SQL Endpoint in my SQL 2005 DB to retrieve information from my C#.Net program. It works fine for me when I run it, but I am set as an administrator in SQL Server. When I try to run it as a normal/test user, I get the error message: "There was an error in the incoming SOAP request packet: Client, Endpoint, UnmappedSoapMethod."
Now, I have set up the endpoint in SQL and I can see the method I am trying to call when I do a select on the sys.endpoint_webmethods table. I have also allowed the domain group which the test user is a member of execute permission to the stored procedures and Access permissions to the endpoint.
What have I missed in the set up for me to be receiving this error message?
Cheers and thanks in advance.
Sam.
Is it possible to return typed data in an Endpoint for an ASP.Net Web Reference to Proxy? If so, is there any specific terminology I should be aware of to target my search?
I realize there is a choice between Object (returns a dataset or error) or dataset, but the automatically generated WebReference Proxy in ASP.Net (2.0) is untyped and we can't change the typing there as you have to remove the entire WebReference to pick up new WebMethods (or changes to signitures I'd assume).
I'm able to create my own typed proxy as a psuedo-DAL assembly which takes the WebReference and cast rows/objects into typed rows/objects one at a time, but this seems like a lot of work and probobly not the best practice.
Any help would be greatly appreciated
I am trying to expose a stored procedure on SQL Server 2005 as a web service. So I just have an end point created with the procedure as my web method. Now when I try to reference this web service in my C# windows application, I am only able to see the procedure definition by requesting for the WSDL http://servername/path?wsdl. But when I try to reference it, I get the error 501/505 - Not Implemented or not supported. Any ideas what the issue here could be? Would really appreciate the help here as I am kinda stuck! - Thanks, Manmeet
View 2 Replies View RelatedHi,
I'm looking into hosting SQL Server endpoints on Vista. Unfortunately they don't work nicely because, as far as I know, they require http.sys which is part of Windows Server 2003. Hence, I can create my endpoint but i can't browse to it over http.
So, siple question (not so simple answer) how can I access SQL Server endpoints on Vista?
Thanks
Jamie
i have posted here a few times on my endpoint issues but i cant find a resolution so im approaching the issue from a different angle. can you guys tell me how you create and consume endpoints, preferably with the endpoint code, the url you use to see the wsdl, and any network/pc settings you may have altered to get endpoints to work. im looking for tips or steps that may not be included in basic endpoint tutorials. I really am stuck and have no idea what else to try. thanks all
View 3 Replies View RelatedI have application who like to use HTTP Endpoints in our cosolidate enviornment. How I can measure the performance on HTTP Endpoints? I would like to know how much CPU, Memory and IO used by HTTP Endpoints?
Any suggestion? Thanks in advance.
Has anyone worked out how to trace SOAP/HTTP Endpoints in SQL 2005 SP 2?
I'm not having a particular problem, just want to know how things work, such as which layer is responsible for the HTTP Authentication. I've traced the HTTP.sys stack using logman.exe (see http://www.thelastpickle.com/2008/03/22/tracing-sql-soap-endpoints-through-httpsys/) .
I cannot see any SQL Profiler events, and I've tried setting trace 7801 and 7803 as described in http://www.sqljunkies.com/Article/5CCAC423-1407-4A36-AF71-ED6A67D9646A.scuk#_Toc52964195 but they do not result in any logging.
thanks
Aaron
We have been having a problem with service broker for quite a while now and searching on these forums and more generally on the web has not yielded any kind of answer...
Our application utilises service broker within a single database (there is no cross database, cross instance or cross server communication). There are approximately 12 queues which are used to varying degrees. A few of the queues have a throughput rate in the order of up to 100s per minute at peak periods.
In some scenarios we are able to make use of persistent conversations but the majority of messages are sent on their own conversation or in small batches on their own conversation.
Some time ago we found that the database was growing in size more than expected due to a build up of data in the service broker meta data internal tables, exposed via the sys.conversation_endpoints system view.
We identified a problem in the application that was preventing some conversations from being closed and have now fixed this.
However, we are still experiencing a build up in the service broker tables and sys.conversation_endpoints shows this is now due to a very large number of conversations in the "CLOSED" state.
I know that conversations are kept around to prevent replay attacks but thought they were supposed to be cleared after about 30 minutes. This is certainly not happening as we currently have conversations that were opened on the 18th September, a full week ago. We currently have about 350,000 closed conversations and this figure is increasing!
We have tried restarting the SQL Server instance with no effect.
I have been using a script to loop through all closed conversations and get rid of them using the "WITH CLEANUP" clause but I'm loath to create a scheduled task that does this in the background when service broker should be doing the job itself.
Has anybody experience this problem and, even better, have any idea how to solve it?
Daniel
Hi,
I implemented the pattern suggested in the 'Recycling Conversations' article that Remus Resanu presented. Everything works great except ended conversations on the receiver remain in the sys.conversation_endpoints table forever in the 'CLOSED' state.
Is there some setting I am missing to have those conversations purged from the endpoints table. I am concerned that in the production environment this table will grow very large.
Thanks
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies View RelatedHi.
I’m having a conceptual problem with tracking sales vs. call center calls.
Each record in the fact table represents a call to the call center.
In this record are various facts and foreign keys that map to marketing campaigns, etc.
The product sold ID is NULL on no sale, and filled in for a sale.
When creating MDX to retrieve data by campaign, for example, to track the number of calls for a campaign vs. it’s sales, that works fine (because there’s a column labeled sale that’s either 0 or 1 for the sale and I just sum it). This way I get the conversion percentage of calls to sales.
But when creating MDX to track the product sold, as soon as I do a crossjoin on campaign and products sold, for example, I lose the total calls- the number of calls is the same as the sales (similar results as a SQL join). Am I doing something wrong or is it conceptually impossible to track this type of metric down to the product when there are NULLs in the fact table? I’ve tried converting the nulls to a NONE category, but that doesn’t stop the crossjoin from not giving me the desired results, plus I then have to filter out NONE as a product.
Is that why a lot of sample warehouses have a separate sales cube?
Thank you,
Richard
Hi group,
I've got a performance issue.
Here's in global what the sp (Let's call it SP_A) does.
Step 1 Call a different SP (Lets call it SP_B) and store the output in a variable
Step 2 SP_B runs a select statement that returns 1 value
Step 3 SP_A uses this value as a parameter in a select statement.
Step 4 The result of the SP_A is the result of the select statement (744 rows (always))
All tables used in SP_A and SP_B are temp tables.
Total performance of SP_A is between 0.090 and 0.140 seconds.
The problem is that this SP is called 180 times from outside SQL server. That means that the total processing time is somewhere between 21 and 25 seconds.
When I move the entire processing to within SQL server I gain only 2 seconds. So I lose 2 seconds in connecting to the database 180 times.
Can someone give me some pointers on where to look for performance wins?
If you like I can add the SP's
Regards,
Sander
I have an old app that I'm trying to recode and improve performance.
From the start it makes three seperate calls to a db, checks to see if the record exists, if it doesn't it adds it, then takes the data from all three and inserts it into a final call.
Here is a quick example of the script
Select * from table1 where id = " & tempVariable
If Not RS.EOF Then
strTable1 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec1
RS.UPDATE
RS.Requery
strTable1 = RS("SomeRec")
End If
RS.CLOSE
Select * from table2 where id =2
If Not RS.EOF Then
strTable2 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec2
RS.UPDATE
RS.Requery
strTable2 = RS("SomeRec")
End If
RS.CLOSE
Select * from table3 where id =3
If Not RS.EOF Then
strTable3 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec3
RS.UPDATE
RS.Requery
strTable3 = RS("SomeRec")
End If
RS.CLOSE
INSERT INTO Table4 (Table1, Table2, Table3) VALUES ('" & strTable1 & "', '" & strTable2 & "', '" & strTable3 & "'
These is probably an easy solution however I don't know where to start. Any help or ideas will be greatly appreciated.
Thanks
-Scott
I am attempting to see if an application is calling a particular stored proc. i have the Profiler setup to only display the login and it is. I have all items set to display under Stored Procedures, but I can't see names of procedures in the Text Data.
View 4 Replies View Related