Hello. I have set up an SQL Endpoint using NTLM authentication. When I try to retrieve the WSDL file I am prompted for credentials. I put in username/password (this user has Connect access to the Endpoint) but its not accepted. The dialog reappears. The SQL error log shows an HTTP authentication failed error. I've triple checked that the username/password is correct.
Now here's the strange part: the same endpoint (exact same code for setting up endpoint, permissions, user, etc.) works just fine on another box (username/password is accepted and WSDL is shown). This leads me to believe that the problem isn't with SQL or the endpoint, but someplace else. I really don't know how to debug this one.
I'm trying to write an SP which takes a string parameter like '1,3,6,9' and then uses it in an IN clause of a SELECT statement to return a recordset, along the lines of:
SELECT custname FROM mytable WHERE theid IN @myparameter
...any ideas how to get this kind of thing to work. In the example, theid is a numeric value. What it needs to do, I guess, is do a string replacement of the parameter value, but the SP naturally enough attempts to compare the numeric theid value with the string @myparameter value and fails. It works OK if there's only one value in the string, because the server auto-converts the paremeter into a numeric, but if there are 2 or more values in the string then the server obviously can't do the conversion. How can I make this work?
I am trying to have a query with the option for items to be null. (So users don't need to fill in the other fields if they choose not too) SELECT Tickets.TicketID, Tickets.UserID, Tickets.SystemID, Tickets.Title, Tickets.Description, Tickets.Software, Tickets.Date, Systems.OS, OS.OS AS OstitleFROM Tickets INNER JOIN Systems ON Tickets.SystemID = Systems.SystemID INNER JOIN OS ON Systems.OS = OS.osIDWHERE (Tickets.Title LIKE '%' + @title + '%') AND (Tickets.Software LIKE '%' + @software + '%') AND (Tickets.Description LIKE '%' + @descrip + '%') AND (Systems.OS = @osid) OR (@osid IS NULL)This works when i give the LIKE values % as a parameter. So they can choose to search by title + software but not description or description and nothing else etc etc etc. The problem is, the osid. If I give it a value it works but if i try to do null, *. or % it always displays every item in the databse ignoring any of the previous like statements. Anyone have an idea?
I wish to enter some string data with period(.) in a column with char type. It doesn't accept period(.) What data type should I choose for this field ?
When I run the Query I get... Message="Parameterized Query '(@ID int,@EmpID int,@Photo nvarchar(260),@DoB smalldatetime,@Med' expects parameter @EmpID, which was not supplied."
I also get this when the parameter can be a string. I have set the parameter properties so that SourceColumnNullMaping to true (it was set as false - so hoped this might fix it!)
I am inserting a value from vb.net to sql server 7. i created a table in the sqlserver with primary key. In VB.net form i didnt check the values of the check box. when i try to save the empty or null the sql server should throw the error. but its accepting the null value. how is it possible? can anyone help me? FYI: table name: JobMast Fields: jobcode int primary key jobtitle varchar(10)
Hello I am newbies with SQL 2005 I want install Windows sharepoint services 3 in french on my SBS 2003 R2 SP2 also in frecnh langage and at the end of the install, a message say to me : Bin caracter are not accepted in your configuration SQL Langage is not correct (I have French_CS_AS)
Where is possible to change these parameters ? I try to find but never find Thank You in advance for your help
++ Michel If you have the answer in French it's better for me but not neccessary !
I am trying to view my reporting service web site for the first time, but I've got this Error message "StringStartsWith can't accept null parameters" I am using Widows Vista and Report Manager and Report Server Web site has been set to Default web Site and Windows Server Identity.BuiltIn Account has been set to Local System and Web Service Identity.ReportServer and Report Manager has been set to Classic .Net App Pool.
I worked with someone else to create a query that gives us the age of a stock. How long it has been in the warehouse since the Purchase order date (without completely selling out). It does exactly what I want, the problem is that it only accepts 1 row as a result.
The error message I get is:
quote:Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So my question is; can this code be modified to pass it multiple SKUS and run a report on every item currently in stock?
SELECT TOP 1 @skuVar, CAST(GETDATE()-ReceivedOn AS INT) AS 'Age' FROM (SELECT pir.id AS id,aggregateQty AS aggregateQty,-qtyreceived as qtyreceived, (aggregateQty - qtyreceived) AS Diff, ReceivedOn AS ReceivedOn ,( SELECT SUM (PurchaseItemReceive.qtyreceived) FROM bvc_product pp
Working on this issue ... SSIS package trying to load data from DB2 to SQL ....
IBM OLE DB Provider for DB2" Hresult: 0x8004D01C Description: " SQL1224N The database manager is not able to accept new requests, has terminated all requests in progress, or has terminated the specified request because of an error or a forced interrupt. SQLSTATE=55032
SSIS Error Code DTS_E_PRIMEOUTPUTFAILED - The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput().
The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
I cannot understand how this was allowed to happen, but I can recreate the situation all day long. Someone, please tell me that I have missed an obvious solution to this;
With a fresh install of SQL 2005 Express (SP2) I have 4 databases installed and two service-based applications running. One of the service applications uses/requires three of the four databases and the second service application uses/requires the fourth database.
On boot, with the above described services set to depend on SQL Server service startup I get repeated failures on initial database access. Looking at the event log reveals why (in sequence):
SQL Server Startup...
Event: Server local connection provider is ready to accept connection on...
Event: Server named pipe provider is ready to accept connection on...
Event: Server is listening on [ 'any' <ipv4> 1911]
Application Service Startup...
Event: SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again
Moments Later:
Event: SQL Server is now ready for client connections. This is an informational message; no user action is required.
Now, its clear that the SQL service has started, and that this opens the floodgates for dependant services to start, each of which is told - essentially - to go away and come back once the SQL server has **really** started. With the accompanying suggestion to 'look for the informational message that indicates that SQL Server is ready' in the event log.
Am I reading the situation right so far?
In response to this, with my developer way of thinking, I could probably script the startup of application services rather than have them start automatically at boot or some such insane idea. But its not really the way that I had planned to spend the next few hours of my life, and it seems to me that I should not have to do such a thing.
Our in house service application (one of the two described) is in fact robust enough (just enough) to survive and to try the connection periodically, accomplishing a successful startup even with no database available.
The second of the two services is external and is unfortunately unable to start with no database available (Business Objects XI CMS). The normal means of making this service robust against database outages is the service restart procedure setting. But since the service doesnt ever start successfully, this doesnt work (learn something new every day - the restart is literal. It does not provide an initial startup retry and the service must have been started and fail for the retry to kick in)
Now, im not trying to bash anyone over this situation and regardless of the inflexible nature of the external service application and the general sillyness of the SQL server startup sequence - i mean c'mon... we are started but not ready? How about dont report startup until after you are ready? - the end result is that I need to come up with a solution to this problem.
I cant imagine that this is the first time that this behaviour has been questioned and I have to believe that there will be a simple, reliable workaround. I will keep looking for myself, but if any of this sounds familiar and you have a suggestion, your response will be greatly appreciated.
After reading all the comments about microsoft ole db provider for db2, I've started converting a project from ibm db2 provider to microsoft ole db provider, for the reason that the server would not need a ibm client software installation anylonger. However in one of the dataflows there is a select statement on the DB2 database (AS400 iseries DB2 V5R4) that uses common table expression. The Oledb data source in the Dataflow returns zero input columns when I use the microsoft provider instead of the ibm provider. When I set it back to use the ibm oledb provider the input columns are available again. The common table expression is used in the select statement like: WITH x AS (SELECT ... FROM mytable WHERE ...) SELECT x.*, (x.a - x.b) As diff FROM x
We are not allowed to change anything in the DB2 database, so using views or sprocs is not an option.
Did anyone else experience this and can it be avoided without changing the selct logic?
I want to create a SP that creates a new database, so I script-ed out the db and paste the script into new SP gui (SQL 2000). I want to pass it a variable for the data/log file location that is not the default location. The original script looks like this: CREATE DATABASE [PWRR_DDS] ON (NAME = N'PWRR_DDS_Data', FILENAME = N'F:SQL SERVER FILESDatabasesdbName.mdf' , SIZE = 3118, FILEGROWTH = 10%) LOG ON (NAME = N'PWRR_DDS_Log', FILENAME = N'F:SQL SERVER FILESDatabasesdbName_Log.LDF' , SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS. I replaced the path of the FILENAME variable like this: CREATE DATABASE [PWRR_DDS] ON (NAME = N'PWRR_DDS_Data', FILENAME = @DBPath, SIZE = 5000, FILEGROWTH = 10%) LOG ON (NAME = N'PWRR_DDS_Log', FILENAME = @LogPath , SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS, declaring the variables as char(500). The error I get is "Incorrect sybtax near '@DBPath'. Any ideas for workaround? Thanks, EJM
I have a table-valued function in mssql 2005 as below: ALTER FUNCTION fn_test{ @test nvarchar(1000)}RETURNS@TEMP TABLE{ test nvarchar(1000)}ASBEGIN INSERT INTO @TEMP SELECT @test RETURNEND Everytime, I passed in chinese character (@test), such as 測驗, the function will return ????. What should I do for the table-valued function, so that the chinese character can be passed in? Please help. Note: I can search and get the chinese characters if I use stored procedures; and the columns in the tables can store chinese chararcters as well. Only table-valued function is not working with the chinese characters. Is it a bug from MSSQL 2005?
SQL Server 2005 isn't accepting any connection other than SQL Management Studio from the local machine. However, even that's exhibiting strange behavior (please see below for more details).
I've read through all the common errors regarding enabling remote connections via the SAC tool, making sure protocols are enabled, etc. This case seems to be completely outside. Any advice appreciated.
Thanks,
Elias
[1] Client side: What is the connection string in you app or DSN? N/A. Can't connect via any remote method, including client Control Panel > ODBC > create DSN, SQL Server Enterprise Manager, third party application. If client fails to connect, what is the client error messages? Several, depending on method: "server does not exist or access denied", "error has occurred... under default settings, SQL Server does not accept remote connections", etc. Is the client remote or local to the SQL server machine? Remote Can you ping your server? YesCan you telnet to your SQL Server? No. However, there's a functional instance of SQL Server 2000 on the same domain which has no problems- telnet also fails to that instance, even locally from either machine using the 127.0.0.1 address. Telnet functions for other ports. What is your client database provider? N/A Or/And, what is your client application? SQL Management Studio and others Is your client computer in the same domain as the Server computer? Same domain What protocol the client enabled? TCPIP when I attempted to create the DSN. Not sure what protocol SQL Management Studio uses. Do you have aliases configured that match the server name portion of your connection string? NoDo you select force encryption on server and/or client? No
[2] Server side:
What is the MS SQL version? SQL Server 2005 What is the SKU of MS SQL? Standard What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ] Have tried all three individually and together. Configuration Manager exhibits strange behavior (see below)Does the server start successfully? Server starts successfully when the machine boots. However once it's stopped, it can't be restarted (see below) If SQL Server is a named instance, is the SQL browser enabled? Default instance What is the account that the SQL Server is running under? Local SystemDo you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? Not sure, but SQL Server 200 instance is working OK Do you make firewall exception for SQL Browser UDP port 1434? Unknown
[3] Platform: What is the OS version? Client: Windows XPSP2 Server: Windows Server 2003 Do you have third party antivirus, anti-spareware software installed? Client: Trend Micro Server: UnknownOther behavior:
A separate instance of SQL Server 2000 on a different machine on the same domain is functioning without problems.
Telnet to either SQL Server (functional 2000 or problem 2005) on port 1433 is not possible - even when attempted from the local machine using 127.0.0.1! Telnet to both machines works using other ports.
When creating a DSN from a client machine, problem 2005 instance is in list of available SQL Servers, however connection fails ("SQL Server denied access or does not exist").
MSSQLSERVER service starts automatically on boot. However, once stopped the service can not be restarted. Message given: "SQL Server (MSSQLSERVER) service on Local Computer Started and then Stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."
SQL Server Surface Area Configuration for Services and Connections tool does not display Database Engine option as indicated by this MS guide to enabling remote connections: http://support.microsoft.com/default.aspx?scid=kb;en-us;914277
SQL Server communication manager shows TCP, Shared memory, named pipes enabled. However event log seems to show instance not listening via any protocol. Message displayed as: "SQL Server listening on ." (should read: "Server is listening on ['any' <ipaddress> <ipv4> <Port Number>])
Several answers indicated uninstall and reinstall could solve these problems. However, wizard refused to uninstall SQL Server.
Attempted disabling Shared Memory as documentation indicates this protocol gets used first. No luck.
Access through SQL Server Management Studio works, from local machine only.
When using SQL CE 3.5 and I try to insert data to DataSet with NTEXT type column that has more than 4000 characters I get this error
"InvalidOperationException was unhandled @p4 : String truncation: max=4000, len=8414
I had not problem with SQL Compact 3.1 I didn't even changed DataSet I just upgraded my database 3.1 -> 3.5 and I get this error. This is serious bug....
There are other people who have the same problem http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=812683&SiteID=17
SQL Server 2005 isn't accepting any connection other than SQL Management Studio from the local machine. However, even that's exhibiting strange behavior (please see below for more details).
I've read through all the common errors regarding enabling remote connections via the SAC tool, making sure protocols are enabled, etc. This case seems to be completely outside. Any advice appreciated.
Thanks,
Elias
[1] Client side: What is the connection string in you app or DSN? N/A. Can't connect via any remote method, including client Control Panel > ODBC > create DSN, SQL Server Enterprise Manager, third party application. If client fails to connect, what is the client error messages? Several, depending on method: "server does not exist or access denied", "error has occurred... under default settings, SQL Server does not accept remote connections", etc. Is the client remote or local to the SQL server machine? Remote Can you ping your server? YesCan you telnet to your SQL Server? No. However, there's a functional instance of SQL Server 2000 on the same domain which has no problems- telnet also fails to that instance, even locally from either machine using the 127.0.0.1 address. Telnet functions for other ports. What is your client database provider? N/A Or/And, what is your client application? SQL Management Studio and others Is your client computer in the same domain as the Server computer? Same domain What protocol the client enabled? TCPIP when I attempted to create the DSN. Not sure what protocol SQL Management Studio uses. Do you have aliases configured that match the server name portion of your connection string? NoDo you select force encryption on server and/or client? No
[2] Server side:
What is the MS SQL version? SQL Server 2005 What is the SKU of MS SQL? Standard What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ] Have tried all three individually and together. Configuration Manager exhibits strange behavior (see below)Does the server start successfully? Server starts successfully when the machine boots. However once it's stopped, it can't be restarted (see below) If SQL Server is a named instance, is the SQL browser enabled? Default instance What is the account that the SQL Server is running under? Local SystemDo you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? Not sure, but SQL Server 200 instance is working OK Do you make firewall exception for SQL Browser UDP port 1434? Unknown
[3] Platform: What is the OS version? Client: Windows XPSP2 Server: Windows Server 2003 Do you have third party antivirus, anti-spareware software installed? Client: Trend Micro Server: UnknownOther behavior:
A separate instance of SQL Server 2000 on a different machine on the same domain is functioning without problems.
Telnet to either SQL Server (functional 2000 or problem 2005) on port 1433 is not possible - even when attempted from the local machine using 127.0.0.1! Telnet to both machines works using other ports.
When creating a DSN from a client machine, problem 2005 instance is in list of available SQL Servers, however connection fails ("SQL Server denied access or does not exist").
MSSQLSERVER service starts automatically on boot. However, once stopped the service can not be restarted. Message given: "SQL Server (MSSQLSERVER) service on Local Computer Started and then Stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."
MSSQLSERVER service starts automatically on boot. However, once stopped the service can not be restarted. Message given: "SQL Server (MSSQLSERVER) service on Local Computer Started and then Stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."
SQL Server Surface Area Configuration for Services and Connections tool does not display Database Engine option as indicated by this MS guide to enabling remote connections: http://support.microsoft.com/default.aspx?scid=kb;en-us;914277
SQL Server communication manager shows TCP, Shared memory, named pipes enabled. However event log seems to show instance not listening via any protocol. Message displayed as: "SQL Server listening on ." (should read: "Server is listening on ['any' <ipaddress> <ipv4> <Port Number>])
Several answers indicated uninstall and reinstall could solve these problems. However, wizard refused to uninstall SQL Server.
Attempted disabling Shared Memory as documentation indicates this protocol gets used first. No luck.
Access through SQL Server Management Studio works, from local machine only.
Hi, I have installed on my laptop SQL server 2005 Developer edition, I can change ANY of the settings of the SQL server and OS.
I am writing code with a team using continuous integration where the connection string is the following: "Provider=SQLNCLI;Server=.SQLEXPRESS;Database=Secret;Trusted_Connection=Yes;"
is there a way (create an alias,rename server, other?) to make my local dev SQL server accept the connection string above, as at the moment I have to rename the connection string every time before checking in code and after downloading the latest version from the code repository.
I am trying to get my query to pull all the occurances where someone called 411 assistance. It pulls anything that has 411 in it right now, and I just need it to find where the number is just either '411' or '1411' and thats it. Here is what I have so far:
SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007' and (Left(Endpoint,3) = '411' or Left(Endpoint,4) = ('1411')) UNION SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007' and (Left(Endpoint,3) = '411' or Left(Endpoint,4) = ('1411'))
we've gotten this to work from sqlclr and understand the dependence wcf has on the framework but still feel compelled to ask if somehow t-sql can send a message to a wcf endpoint without getting sqlclr involved. I've moved this question over here from the t-sql forum.
I have created an Endpoint based on a stored procedure that has a parameter of datatype XML. I have added a reference to the endpoint in a web project, I have no problems accessing but I do have a question on how to pass XML or an entire XML file to the XML datatype? How is this done in VB.net? I have loaded the XML file into a vb string but this is not compatible with the sql server xml datatype? Any examples anywhere?
I have two servers both installed sql server.The name of the servers are A and B respectively. The admin of sql server in A is 'domainuserA', and admin of sql server B is 'domainuserB'.
My purpose is create a mirroring for a database in server A, but i can establish endpoint in server A(network address can not be reached or does not exist), i have grant the 'connect to endpoint' permission to each login .
i past my code:
step1
Code Snippet --Server A, principal create database db_test backup database db_test to disk='\sharefolderdb_test.bak' with init
create endpoint endpoint1 as tcp(listener_port=5002) for database_mirroring (role=partner)
alter endpoint endpoint1 state=started
create login [domainuserB] from windows grant connect on endpoint::endpoint1 to [domainuserB]
step2
Code Snippet --Server B, mirror restore database db_test from disk='\sharefolderdb_test2.bak' with norecovery
create endpoint endpoint1 as tcp(listener_port=5002) for database_mirroring (role=partner)
alter endpoint endpoint1 state=started
create login [domainuserA] from windows grant connect on endpoint::endpoint1 to [domainuserA]
alter database db_test set partner='TCP://A.corp.com:5002'
--step3
Code Snippet ----Server A, principal
alter database db_test set partner='TCP://B.corp.com:5002'
this step return a error message:
The server network address "TCP://B.corp.com:5002" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
I am trying to grasp endpoint security, or actually more security/certiicates in general, at the moment i am trying to write a distributed service broker app, all the examples i have seen use certificates for the endpoint authentication.
Why must one create a certificate at each endpoint? Why can i not create a single certificate and let all endpoints use it ?
As you can imagine of this app gets distributed to hundreds of places creating a certificate at each one is a mission?
So can i use a single certificate for all endpoints authentication?
I have a stored procedure which needs to access the files on a remote server... a server which does not belong to the same domain as the SQL Server 2005 machine.
I have an account which has access to the remote server. If the account name is "NewAccountAccountName", how can I execute the stored procedure using that account? Is this possible?
I have the need to have a client application connect to a remote database server for retrieval of large datasets (~13000 rows at time) and am trying to leverage existing technologies to build my solution.
To test the difference in retrieval times I created 2 test apps - testA and testB. I created an HTTP endpoint in SQL Server (2005) that exposes one web method corresponding to a sproc that executes one select query and configured testA to execute query and bind the results to a data grid. I exposed my SQL Server so that I could connect directly to the SQL Server using a connection string and configured testB to execute the same SQL statement in an ad-hoc fashion, binding the results to a data grid.
On average, the HTTP endpoint would take ~ 34 seconds to return the 13,000 row data set whereas the direct connection returned that same result set in 4 seconds or less.
I have a bunch of questions: * Why would I be seeing such a dramatic difference in the retrieval times? * How is SQL Server communicating when there is a direct connection? * What are the immediate disadvantages to connecting directly to the remote SQL Server? * If I were to consider allowing my client application to communicate directly with the SQL Server, is there any way to secure the communication without VPN? * In addition, is it even possible to create a SQL login that has all of these requirements? ** select and update only ** cannot view schema (cannot connect to the SQL Server using SQL Server Management Studio) ** cannot view any system tables ** cannot retrieve any information about the schema of the database whatsoever
I guess I'm wondering if there is a way to overcome the obvious serialization overhead that comes with the HTTP endpoint to get the speed of the direct connection while being secure at the same time. Is that being greedy? :P
Anyhow, what are your opinions? I'm very interested to hear experiences and/or advice.
