Hey guy and gals, I need to connect to a web service from a Sproc, can this be done? I've google and found out about Httpendpoints but that is running the web service on the server, not connecting to one from the server in a sproc. Any ideas or site links would be helpful. TIA. Jason
I try to implement mirroring using certificate authentication. When I do this query :
create certificate Principal_cert with subject = 'Principal certificate', start_date = '2007/11/01', expiry_date = '2020/11/01'; GO
Create endpoint endpoint_Princ state = started
as tcp(listener_port = 7024, listener_ip = all)
for database_mirroring (authentication=certificate Principal_cert, encryption = disabled, role = all);
GO
I have this error :
Msg 1088, Level 15, State 1, Line 1
Cannot find the object "Principal_cert" because it does not exist or you do not have permissions.
I have also noticed that the error does not occured when I try this in the master database.
So, I have two questions:
1. Why SQL server does not find a certificate that just been created ? 2. Should the certificate be in the master database or in the database being mirrored ?
Good day to all!I have a problem to resolve.Which is the script in sql in order to define one hierarchy of products categories?I have need to define the script for the creation of the table and store procedures (INSERT,DELETE,UPDATE,GET,GETALL).Thanks for the eventual answers !
executing the first script listed below resulted in the error message (translation):
Message 7890, Level 16, Status 1, Line 1 Error when registrating Endpoint. Potential port conflict ...
use AdventureWorks; go CREATE ENDPOINT SQLEP_AWPersons STATE = STARTED AS HTTP ( PATH = '/AWpersons', AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR), SITE = '*' ) FOR SOAP ( WEBMETHOD 'PersonInfo' (NAME='AdventureWorks.dbo.uspGetPersonInfo'), BATCHES = DISABLED, WSDL = DEFAULT, DATABASE = 'AdventureWorks', NAMESPACE = 'http://Adventure-Works/Persons' )
I changed the port in the script to 8084:
PORTS = (8084),
Error Message 102: Wrong Syntac near ')'.
I changed the port in the script to '8084':
Error Message 7888: The stated IPv6-Address is not supported. The server is potentially not configured for the IPv6 Connectivity or the address is not formated in an known IPv6-Format.
How can I access my endpoint output set of values/xml using internet explorer? I am able to access the wsdl file now I need to access the actual value returned from sql server 2005. Now i need to display it on my Internet Explorer. All the examples of microsoft leads to access using CLR. Please let me know also the difference between Get/Post with respect to EndPoints.
I have two MS Windows 2003 Server Ent Ed. SP2 in cluster. They have MS SQL Server 2005 in cluster.
I have created and endpoint and when I try to access I get the attached error in client machine. This problem only occurs in cluster configuration, because the same installation in an SQL (no cluster) works fine.
EventID: 4 Source: Kerberos The kerberos client received a KRB_AP_ERR_MODIFIED error from the server host/fra-lille-hel03.ea.holcim.net. This indicates that the password used to encrypt the kerberos service ticket is different than that on the target server. Commonly, this is due to identically named machine accounts in the target realm (EA.HOLCIM.NET), and the client realm. Please contact your system administrator.
Is there a sample way to define string constant which every stored procedure can use in SQL 2005 ? 1. In stored procedure A, there is select a1,a2,a3,a4 from mytable where usename='qaz'2. In stored procedure B, there isselect a1,a2,a3,a4 from mytable where VisitNumber>33. I hope there is a sample way to define string constant such as: constant mystring='a1,a2,a3,a4'4. So I can use this string constant both stored procedure A and stored procedure bsuch as:select mystring from mytable where usename='qaz' select mystring from mytable where VisitNumber>35. How can I do that? is there a sample way? Mnay Thanks!
Hello, How are you? I have a problem when I try to create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE. When I€™m going to choose the data source, the SQL Server 2005 Compact Edition provider doesn€™t appear in the list. I installed the SQL Server 2005 Compact Edition from this page http://www.microsoft.com/downloads/details.aspx?FamilyId=%2085E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en. In the additional information says by installing SQLServerCE31-EN.msi installs the provider (System.Data.SqlServerCe.dll) in the GAC (global assembly cache) and registers the OLEDB provider (sqlceoledb30.dll). So, I don€™t understand why couldn€™t I create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE?
Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SqlServerProject1.StoredProcedures.insertlogin(SqlString log, SqlString pas).No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[insertlogin]. ***************************************************************all i am trying to do is : creating a SP in VS using managed code and then trying to execute it. But every time i get the above error. If you can tell me how to edit connection string in this that would be very helpful. At present i am using : Using conn As New SqlConnection("context connection=true") I tried to do "" ALTER ASSEMBLY SqlServerProject1 WITH PERMISSION_SET=EXTERNAL_ACCESS""but i get this error "" Msg 10327, Level 14, State 1, Line 1ALTER ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.""" *********************************************************************Plz help
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in my SSMSE by using either of 2 sets of the following EXEC code: (1) USE AP GO --Code that passes the parameters by position DECLARE @MyInvTotal money EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%' PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1) GO (2) USE AP GO DECLARE @InvTotal as money EXEC spInvTotal3 @InvTotal = @InvTotal OUTPUT, @DateVar = '2006-06-01', @VendorVar = '%' SELECT @InvTotal GO //////////////////////////////////////////////////////////////////////////////////////////// Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class ///////////////////////////////////////////////////////////////////// I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!?? I have 4 questions to ask for solving the problems in this project: #1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1". How can I do it? #2 Question: Did I set the CommandType property of the command object to CommandType.StoredProcedure correctly? #3 Question: How can I define the 1 output parameter (@InvTotal) and 2 input parameters (@DateVar and @VendorVar), add them to the Parameters Collection of the command object, and set their values before I execute the command? #4 Question: If I miss anything in print out the result for this project, what do I miss?
I try to consume a SSL Endpoint that is locate in localhost, but i get the follow error msg:
The underlying connection was closed: An unexpected error occurred on a receive.
m_safeCertContext is an invalid handle.
I do not know what gone wrong with my VS2005, i tried in IE with https://localhost/test?wsdl, and it was okay and display the wsdl document successfully. Beside i also tried with CLEAR port, everything is ok and i managed to consume the web service too.
I have created an EndPoint in SQL Server 2005 as per the code below.
CREATE ENDPOINT OSTC_LMS_Endpoint
AUTHORIZATION LMSEndPointUsers
STATE = STARTED
AS HTTP(
PATH = '/ostc_sql_endpoint',
AUTHENTICATION = (BASIC),
PORTS = (SSL),
SITE = 'OSTC-DEV-001'
)
FOR SOAP (
WEBMETHOD 'ostc_SQLSoapTester'
(name='OSTC_LMS_06.dbo.ostc_SQLSoapTester',
FORMAT = ROWSETS_ONLY,
SCHEMA=STANDARD),
WSDL = DEFAULT,
LOGIN_TYPE = MIXED,
SCHEMA = STANDARD,
DATABASE = 'OSTC_LMS_06',
NAMESPACE = 'http://tempUri.org/'
)
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [LMSEndPointUsers]
GO
USE master
GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint
TO [ostc-dev-001endPointUsers]
GO
----------------------------------------------------------------------------------------------------------------- The SPROC being exposed as the webmethod: -
The computer in question is our dev server and is running as a workgroup machine with the following: - Win Server 2003 SQL Server 2005 .net Framework 2.0 No firewalls or Proxies are in the way.
The computer has to be as a workgroup machine to reflect our live server.
The user LMSEndPointUsers is a SQL Server Login The user ostc-dev-001endPointUsers is a machine login
We have employed the Security class as per the information given in the SQL Server documentation with the intention of using the WS-Security headers that apparently are to be used when trying to authenticate using a SQL Server login.
Hello I am using SQL Server 2005 I want to delete the data from my table after specific number of days.This should happen automatically. After the days I specified the data from the table should automatically get deleted. Can anyone let me know how to achieve this.If anyone can give an example for this it will be great.Thanks in advance.
In 2005 the security login will not allow me to add a user, database role, application role or schema manually. If I use a Transact-SQL in Query Editor to create a Login with a password, it said it was successful yet The T-SQL added my login as an application role is this correct? and why can I not add manually? is there another function that must be performed first? I am very new to the environment but know 2000 very well and miss it being so user friendly.
I am developing an application in VB.NET that is using a sql database. I am trying to start testing with multiple computers running the app while the database is on a remote computer. I have 2 desktop computers networked right now with 1 of them having the database and the other I want to run the program on. I am trying to figure out how to use windows authentication to login to sql server from the remote computer. I can login using Sql authentication but it is taking forever and I keep getting timeout errors so I wanted to try Windows Authentication to see if it is faster. If someone could walk me through the steps I need to take or give me some suggestions to fix my problems I would greatly appreciate it.
I just need some information on creating the second instance in sql server 2005. In my production server we have the databases with different collation. we need to separate those two different collation databases by creating the new instance. I have to create the instance by this afternoon. I think we need to install the sql server 2005 again and select the option of named instace. As long as this is production server are databases are going to be available for the users while installing the sql server to create the different instance? Could some one please respond on this. Thanks!
Hello all, I am new to MS SQL Server and am having troubles figuring out how to create a new database file. I have installed everything I can and and still cannot find any where to add a database file. I am upgrading from Access to SQL and feel like I am missing something... I will eventually need to create a couple of SQL databases and connect from Classic ASP and ASP.net in different applications. Before I worry about how to connect I need ot create something to connect to.
Any help would be greatly appreciated. Thanks Panhead
I have two problems on creating new SQL server database in Visual Web Developer 2005 Express using SQL server 2005 Express.
The 1st problem: (It is resolved)
After installed SQL Server 2005 express and Visual Web Developer 2005 Express. When I tried to create a SQL server database in VWD the following error occurred:
"connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251"
I was stuck with this problem for few days. I did several times of install/reinstall of VWPExpress and SQL server Express, made very sure following proper de-installation sequence as specified in the release readme. But it did not resolve the problem.
Latter I found out that if I login with another Windows XP user account, I don't get the problem.
Changing the security authority of XP file systems and IIS didn't help.
And to prove that I was doing the installation correctly, I tried a new installation of the VWDExpress with SQL server 2005 express in another machine, it worked and I could create new empty SQL database.
After 2 days of frustrating changing of options, parameters, server names, messing with the registry etc.... Finally, I found a way to overcome this problem.
The solution is:
In Visual Web Developer 2005 Express, select:
Tools -> Options -> Database Tools -> Data Connections,
Change the setting: "SQL Server Instance Name (blank for default)" to "SQLEXPRESS". (where SQLEXPRESS is the default server instance name)
That solved this 1st problem.
I still don't know the exact reason for this problem. But I recalled I did installed the VWDExpress few weeks ago, I might have installed it without selecting SQL server 2005 express at that point. I guess the subsequent install/re-install did not clean up those junk in the registry.
I still have the 2nd problem:
The "Create new SQL database..." option is always grayed out when I right click on the Data connections in the "Database Explorer" tab.
I have a bypass for this difficulty for time being:
I have to do it by using the "Add connection....", put in the server name: "myserver/SQLEXPRESS" and a new database name.
I created a HTTP-Endpoint on SQL Server 2005. Now I try to call this web service from a c# client but using SQL-Server authentication (i.e. my user is a sql server user but not a windows user). But it doesn't work. I can connect to the service using a windows user and NTLM authentication. But for some reasons Iwould much prefer if I could use SQL-Server authentication. Is it possible?
Here is how I created the endpoint:
Code Snippet CREATE ENDPOINT myService STATE = STARTED AS HTTP( PATH = '/services', AUTHENTICATION = (INTEGRATED ), PORTS = ( SSL ), SITE = '*' ) FOR SOAP ( WEBMETHOD 'GetStuff' (name='MyDB.dbo.FKT_getStuff', SCHEMA=STANDARD ), WSDL = DEFAULT, LOGIN_TYPE = MIXED, SCHEMA = STANDARD, DATABASE = 'MyDB', NAMESPACE = 'Services' ); GO
Can a MIRROR server have mirrored databases on many instances or must all of the mirrored databases on a server reside on one instance?
This initial implementation worked fine:
PRINCIPAL ServerPROD1instance01 endpoint1430 MIRROR ServerDRinstance01 endpoint1440 WITNESS ServerZ endpoint1450
This second implementation (different PRINCIPAL server to same MIRROR but to a different instance)
PRINCIPAL ServerPROD2instance5 endpoint1430 MIRROR ServerDRinstance02 endpoint1440 <<same mirror server but different instance WITNESS ServerZ endpoint1450
I know that you can only install one endpoint on each server. Does that mean that the endpoint can only resolve to one instance though?
The second implementation looks fine until the final step (start mirroring). I get the following error:
An error occured while starting mirroring Alter failed for database 'test' An exception occurred while executing transaction-sql statement or batch The remote copy of database 'test' does not exist. Check the database name and resissue the command
I have created the test database in instance02 and rolled a log onto it. It is currently in recovery mode - waiting patiently for another log.....
I have read books online and 2k5 unleashed but can't see anything that relates to an implementation across mutliple instances on the DRmirrored servers.
Has anyone tried to do this? Can you confirm its possible?
Hello! I hope that I have posted in the right spot. We are looking for advise. I know that there is no right or wrong answer. "It depends."
We currently have a server with the OS as Windows Server 2003 SP2 and SQL Server 2000 SP3. This Server has 6 physical drives; only 3 of these physical drives are being used. These 3 physical drives are 1 container with Raid 5. This 1 container is divided into 3 logical drives.
We would like to fill the other 3 physical drive slots and create another container. We were thinking of making this Raid 1.
I should put in my disclaimer that Raid 10 is out of the question and so is SAN.
Is Raid 1 the best choice? This is my first question.
Next is how should we split up the files among the containers.
For example, OS, log and swap file on container 1 with Raid 1 and datafiles on container 2 with Raid 5?
What are most people doing? Is there a standard? Can people provide examples of what they are doing or provide suggestions?
I have several general questions about this but I thought I would describe what I am doing first. My senior project team is developing a web application that will wind up using SQL Server for the database. We are writing it in ASP.net and developing the database on SQL Server Express. My job is to develop and deploy the database. I have an ERD designed and am getting ready to start coding the tables, constraints and stored proceedures but am unsure of a couple of things.
1) How do I create a new schema? 2) I can see how to create tables in the GUI but what I am trying to do is create scripts that can be used to create the tables and objects on the actual server that our client will be using. How do I create scripts in 2005 Express? Do I just go click on the new query button? 3) Are scripts that work for Express going to have any problem executing on SQL Server?
Hi All, Is it possbile to define connection string for web and database server which is running on different machine. Note( Not same LAN). E.g (My Web Server is in London and Database server in Sydney). Please can any one help me.?? Thanks in advance...
Hi All... We're cloning an application currently under development on Server 2003 / SQL Server 2005 / dotnet 2.0. The clone's going to live on an XPSP2 box with SQL Server 2003 - it's for demo purposes... The application's able to get to our own database, but it's having a hard time creating the ASPNETDB that dot net likes to have. Aside from the OS, the only difference between the two SQL Servers is that on the working system, we're using windows authentication on the database while on the non-working system, we're using SQL Server authentication. But we've tuned the connection strings appropriately - again, we can get to our own db just fine. We have the following lines in our web.config: <connectionstrings> <clear /> <add name="LocalSqlServer" connectionstring="Server=localhost; Database=ASPNETDB; UID=sa; PWD=mypassword" /></connectionstring> On the working system, the Server is an actual IP address, integrated security is set to true, and of course the uid/pwd is a bit different. But again, using a very similar connection string, we able to get to OUR database on the non-working system... The error we're getting at runtime is: Cannot open database "ASPNETDB" requested by the login. The login failed. Login failed for user 'sa'. When we poke into the database from SQL Server Management Studio - on a remote machine - we can see our own database is there, but the ASPNETDB isnt there. Yet... I suspect it failing getting in to just create the db.... Any thoughts? Thanks for the help!!! -- Curt
I have a varchar column in a table, which is an unique key column. Now when I design this table should I use this column as a primary key or should I add one more Integer column as a primary key column for the performance?
One more point is, when ever I do a search on this table I will search based on that unique varchar column values only, so even then if I add new integer column just for Primary key I will not use this column for searching values.
With these information, can some body help me in deciding this?
I currently have a website which is using ASP.NET 2.0, C#, and SQLServer 2005. The website will be used to enter grants for auniversity. When a new grant is entered, I need to generate a primarykey. The primary key will need to follow the format: Two digit forfiscal year, then number of the grant for that year. Example:Year 08 and 14th grant of the year would be: 0814How can I implement this. Right now, I have a "New Grant.aspx" pagewith a Submit button. I am guessing the date is going to be formattedin C#. How can I check what the last primary key in the database is?Also, it seems to me that SQL Server insists that the primary key be32 bits long, however my primary key will only be 4. How can Ioverride this? Thanks.
