Hi guys,
You know when restoring a database in SQL Server, exclusive access is required. With sp_who I can see the clients. But I don't know how to terminate the client connections easily. Usually I have to stop the server and restart it. I DO think it's too troublesome. Do you have any ideas? Is there any sp or command, or tool that may help? How do you deal with this issue?
Thanks!
I am running SQL Server 2000 on windows XP pro. I have configured (enabled) TCP/IP protocol and left the default port 1433, but SQL server still does does not listen for client connections.
Can anyone tell me what might stop SQL server from listening???
With the server running, here is what I see (nothing on 1433...):
Proto Local Address Foreign Address State
TCP 0.0.0.0:25 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1025 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1030 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1031 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1098 0.0.0.0:0 LISTENING
TCP 0.0.0.0:6401 0.0.0.0:0 LISTENING
TCP 0.0.0.0:10002 0.0.0.0:0 LISTENING
TCP 127.0.0.1:1034 0.0.0.0:0 LISTENING
TCP 127.0.0.1:1037 0.0.0.0:0 LISTENING
TCP 192.168.0.104:139 0.0.0.0:0 LISTENING
TCP 192.168.0.104:1138 38.119.97.5:119 ESTABLISHED
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1027 *:*
UDP 0.0.0.0:3456 *:*
UDP 0.0.0.0:4500 *:*
UDP 127.0.0.1:123 *:*
UDP 127.0.0.1:1900 *:*
UDP 192.168.0.104:123 *:*
UDP 192.168.0.104:137 *:*
UDP 192.168.0.104:138 *:*
UDP 192.168.0.104:1900 *:*
and here is the server log:
2005-10-25 23:23:24.31 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
2005-10-25 23:23:24.31 server Copyright (C) 1988-2000 Microsoft Corporation.
2005-10-25 23:23:24.31 server All rights reserved.
2005-10-25 23:23:24.31 server Server Process ID is 3792.
2005-10-25 23:23:24.31 server Logging SQL Server messages in file 'c:MS SQL ServerMSSQLlogERRORLOG'.
2005-10-25 23:23:24.34 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2005-10-25 23:23:24.71 server SQL Server configured for thread mode processing.
2005-10-25 23:23:24.71 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2005-10-25 23:23:24.74 spid3 Starting up database 'master'.
2005-10-25 23:23:24.92 server Using 'SSNETLIB.DLL' version '8.0.194'.
2005-10-25 23:23:24.92 spid5 Starting up database 'model'.
2005-10-25 23:23:24.96 spid3 Server name is 'GS-DELL'.
2005-10-25 23:23:24.96 spid3 Skipping startup of clean database id 4
2005-10-25 23:23:24.96 spid3 Skipping startup of clean database id 5
2005-10-25 23:23:24.96 spid3 Skipping startup of clean database id 6
2005-10-25 23:23:24.96 spid3 Skipping startup of clean database id 7
2005-10-25 23:23:25.10 spid5 Clearing tempdb database.
2005-10-25 23:23:25.28 server SQL server listening on Shared Memory, Named Pipes, Rpc.
2005-10-25 23:23:25.28 server SQL Server is ready for client connections
2005-10-25 23:23:25.59 spid5 Starting up database 'tempdb'.
2005-10-25 23:23:25.71 spid3 Recovery complete.
2005-10-25 23:23:40.37 spid51 Using 'xpstar.dll' version '2000.80.194' to execute extended stored procedure 'xp_MSADEnabled'.
2005-10-25 23:23:40.71 spid51 Error: 15457, Severity: 0, State: 1
2005-10-25 23:23:40.71 spid51 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2005-10-25 23:23:40.89 spid51 Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure 'xp_msver'.
2005-10-25 23:23:40.93 spid51 Starting up database 'msdb'.
2005-10-25 23:24:54.20 spid51 Starting up database 'gpldb'.
2005-10-25 23:24:54.26 spid51 Analysis of database 'gpldb' (7) is 100% complete (approximately 0 more seconds)
2005-10-25 23:24:54.46 spid51 Starting up database 'Northwind'.
2005-10-25 23:24:54.60 spid51 Starting up database 'pubs'.
www.shanje.comdoes sql server hosting, on shared servers, at a reasonable price. It seems.They also allow client connections. Just playing around I've managed toconnect an Access Data Project to a SQL Server database, and to access thedatabase from Enterprise Manager (running locally).First of all - what does anybody think of that as a general idea - localclient, connecting to MS SQL Server running on a shared host, connectingover the internet (ADSL here). Speed looket OK ish, but of course I haven'tdone any real stress testing.Second. In enterprise manager I could see ALL the database, though I couldonly access mine. What's the security implications of this approach?Third. Anybody got any experience of Shanje? Service/uptime/value etc. Or isthere anybody else offering a similar service?Cheers, Jo
View 3 Replies View RelatedSQL Server is cutting off client connections intermittently and I'm having a hard time figuring out why. Restarting the SQL server service fixes the issue for a few days but then it happens again. It's SQL Server 2005 on Windows SBS 2003 with XP and Win2000 clients connecting. Clients connect via a DSN file--they run an Access database. I run daily backups and all maintenance tasks on the databases and these happen without any errors.
Here are some of the error messages I get from the SQL Log:
Login failed for user 'NT AUTHORITYNETWORK SERVICE'. [CLIENT: 192.168.1.16]
Error: 18056, Severity: 20, State: 23.
The client was unable to reuse a session with SPID 72, which had been reset for conection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x862. Administrator should deregister this SPN manually to avoid client authentication errors.
And a recurring error in the SQL Agent Log:
[165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
any clues into how i can further troubleshoot would be appreciated. I can't reproduce the error on command but it is bound to happen "randomly" during business hours and i really need it to stop. any insight appreciated.
thanks,
k
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.
If I look under SQL native client configuration | Client protocols, I have no entries and no ability to look at the properties of SQL Native client Configuration. Something is missing which I think is related to an error when connecting to my local server.
sqlcmd -E -S .devmain, 1064
I get error :
Encryption not support
sqlcmd: Error: microsoft SQL native Client : Client unable to establish connection
An error has occurred while establish a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
In comparison to other machines here at work, it appears my remote connections are configured. But I have no Client Protocols under sql native client configuration, how can I get them in there?
Thanks, in advance.
As stated in the subject I have a situation where if database mirroring is employed for either manual or automatic failover, all the client (including web connections) connections use ODBC not ADO, or OLEDB etc... so what methods are recommended? Client side redirect is not available so I could not employe the "Data Source =A; Failover Partner=B..." option.
Right now the method employed (pre database mirroring and basically employing log shipping on SQL 2000) is to have a DNS alias for the ODBC connection so that if the server were to change in a failover situation the DNS record would have to be altered, so that all the client connections would not have to be reconfigured.
Regards,
Dominic Baines
I have been investigating the number of connections activeinactive to a certain database server and I have stumbled across an application which seems to not be clearing its database connections.For one instance of a client there was >70 sql connections which eventuated from the closing and reopening one 1 screen in the culprut app. Once the application was closed all of the connections are recycled but its evident that within the application itself it is not correctly reusing already existing open connections.
I have raised a point with the main programmer that we need to investigate more into how the application is managingot managing its ADO .NET connections to SQL.
I am starting with doing some reading here URL... and I was hoping to get some more information about the possible impact of excessive sql connections on the SQL Server itself. Our organization is quite lucky in that our SQl Servers are Overspecced given their workload, bearing that in mind I would like to dig a bit deeper to get some stats if I can to highlight the scope of the issue to the managementprogrammers.Our SQL server peaks at 6500 processes and a good 70% of those are due to this applications mis-management of its sql connections.
I backup the database into tape0 using management studio and can see the contents in the backup media.
However when I tried to restore, the error "timeout expired" appears and sometimes together with the error "restore headeronly is terminated abnormally".
Please advice and thank you in advance!
Below is the error message:
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(String query)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSqlWithResults(Server server, String cmd)
at Microsoft.SqlServer.Management.Smo.Restore.ReadBackupHeader(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.buttonSelectDevice_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
===================================
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476
------------------------------
Server Name: NTSRV1
Error Number: -2
Severity: 11
State: 0
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
Trying to update my local developer version of SQL Server 2005 SP1 to SP2. The setup program terminates with the following information dumped from the logs. Not sure if it is related, but I recently installed the SQL Server 2005 Compact Edition (tools, runtime, etc) on the same development machine. It is a little too deep for me to figure out
Darryl
Here are the last few lines of the HotFix.Log
04/24/2007 14:46:48.374 MSP Error: 29527 The setup has encountered an unexpected error in datastore. The action is RestoreSetupParams. The error is : Source File Name: datastorecachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 138
----------------------------------------------------------
Failed to read property "InstallIds" {"MachineConfiguration", "", "DUTTONDARRYL1"} from cache
Source File Name: datastorecachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection:etProperty
Source Line Number: 164
----------------------------------------------------------
Unable to write property into cache: IsClustered
Source File Name: datastoredatastorecacheschema.cpp
Compiler Timestamp: Wed Jun 14 16:28:00 2006
Function Name: DataStoreCacheSchema::writeProperty
Source Line Number: 115
----------------------------------------------------------
Unable
04/24/2007 14:46:48.694 MSP returned 1603: A fatal error occurred during installation.
04/24/2007 14:46:48.790 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
04/24/2007 14:46:48.790 Registry: Cannot read registry key value "Debug"
04/24/2007 14:46:48.806 Copy Engine: Error, unable to install MSP file: c:f01fc31fd0988e3807HotFixSQLFilessqlrun_sql.msp
04/24/2007 14:46:48.822 The following exception occurred: Unable to install Windows Installer MSP file Date: 04/24/2007 14:46:48.822 File: depotsqlvaultstablesetupmainl1setupsqlsesqlsedllcopyengine.cpp Line: 800
******************************************************
Now the SQL9_Hotfix_KB921896_sqlrun_sql.msp.log shows this as the last few lines
MSI (s) (A4:38) [14:46:48:630]: Product: Microsoft SQL Server 2005 - Update 'Service Pack 2 for SQL Server Database Services 2005 ENU (KB921896)' could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log.
MSI (s) (A4:38) [14:46:48:630]: Note: 1: 1729
MSI (s) (A4:38) [14:46:48:630]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:630]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:662]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:678]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:678]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:678]: Product: Microsoft SQL Server 2005 -- Configuration failed.
MSI (s) (A4:38) [14:46:48:678]: Attempting to delete file c:WINDOWSInstaller6749ee3.msp
MSI (s) (A4:38) [14:46:48:678]: Unable to delete the file. LastError = 32
MSI (s) (A4:38) [14:46:48:694]: Cleaning up uninstalled install packages, if any exist
MSI (s) (A4:38) [14:46:48:694]: MainEngineThread is returning 1603
MSI (s) (A4:2C) [14:46:48:694]: Destroying RemoteAPI object.
MSI (s) (A4:04) [14:46:48:694]: Custom Action Manager thread ending.
=== Logging stopped: 4/24/2007 14:46:48 ===
MSI (c) (58:E4) [14:46:48:694]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (58:E4) [14:46:48:694]: MainEngineThread is returning 1603
=== Verbose logging stopped: 4/24/2007 14:46:48 ===
*************************************************************
The Summary.txt looks like this
Time: 04/24/2007 14:46:48.854
KB Number: KB921896
Machine: DUTTONDARRYL1
OS Version: Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No
**********************************************************************************
Prerequisites Check & Status
SQLSupport: Failed
**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
Database Services (MSSQLSERVER) ENU SP1 2005.090.2047.00 x86 DEVELOPER
Analysis Services (MSSQLSERVER) ENU SP1 2005.090.2047.00 x86 DEVELOPER
Reporting Services (MSSQLSERVER) ENU SP1 9.00.2047.00 x86 DEVELOPER
Notification Services ENU SP1 9.00.2047.00 x86 DEVELOPER
Integration Services ENU SP1 9.00.2047.00 x86 DEVELOPER
SQL Server Native Client ENU 9.00.2047.00 x86
Client Components ENU SP1 9.1.2047 x86 DEVELOPER
MSXML 6.0 Parser ENU 6.00.3890.0 x86
SQLXML4 ENU 9.00.2047.00 x86
Backward Compatibility ENU 8.05.1704 x86
Microsoft SQL Server VSS Writer ENU 9.00.2047.00 x86
**********************************************************************************
Products Disqualified & Reason
Product Reason
**********************************************************************************
Processes Locking Files
Process Name Feature Type User Name PID
**********************************************************************************
Product Installation Status
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 2047
Product Version (Final) :
Status : In Progress
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29527
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
We have 15 clients running our applicaton
14 of then conected to SQL server using TCP/IP and it runs fine
1 of 15 when connected using TCP/IP produce "..Time out error "
but runs fine when swiched from TCP/IP to Named pipes
1.What area should we look to correct problem with Time out using TCP/IP ?
2. Where to get information about using TCP/IP via Named pipes ?
Product: Microsoft SQL Server 2005 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
View 78 Replies View RelatedOn Windows XP systems I get the following issue when trying to browse the MSDB folder in SSIS
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I have noticed another post where several others have noticed the same issue. It appears to only occur on Windows XP installations. Is there a workaround or fix for this?
Hi,
I have SQL2000 installed as the default instance, and now I'm trying to install SQL 2005 standard edition as a named instance.
I receive this error :
SQL Server could not connect to database service for server configuration.. [SQL Native client] Encryption not supported on the client. However I'm able to install client tools
The setup works fine on other box with the same config : SQL 2000/Windows XP, is there any work around for this issue ?
In my SQL 2000 client network utilty "Force proctocol encryption " is desabled and did not find the setting for SQL 2005 !
Thank you
Hello,Is it necessary to upgrade the Client Connectivity Tools on all clientmachines after the SQL Server database server is upgraded from Version7.0 to 2000?Thank you in advance!Eddy
View 1 Replies View RelatedHi there,
Here we have got a
asp.net application that was developed when database was
sitting on SQL server 6.5. Now client has moved all of their databases
to SQL server 2000. When the database was on 6.5 the previous
development team has used oledb connections all over. As the databases
have been moved to SQL server 2000 now i am in process of changing the
database connection part. As part of the process i have a login
authorization code.
Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean
Dim conn As IDbConnection = GetConnection()
Try
Dim cmd As IDbCommand = conn.CreateCommand()
Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation"
'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql
cmd.CommandType = CommandType.StoredProcedure
NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username)
NorisHelpers.DBHelpers.AddParam(cmd, "@password", password)
conn.Open()
'Get string for return values
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
'Split string into array
Dim Values() As String = ReturnValue.Split(";~".ToCharArray)
'If the return code is CONTINUE, all is well. Otherwise, collect the
'reason why the result failed and let the user know
If Values(0) = "CONTINUE" Then
Return True
Else
results.Result = Values(0)
'Make sure there is a message being returned
If Values.Length > 1 Then
results.Message = Values(2)
End If
Return False
End If
Catch ex As Exception
Throw ex
Finally
If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then
conn.Close()
End If
End Try
End Function
''' -----------------------------------------------------------------------------
''' <summary>
''' Getting the Connection from the config file
''' </summary>
''' <returns>A connection object</returns>
''' <remarks>
''' This is the same for all of the data classes.
''' Reads a specific
connection string from the web.config file for the service, creates a
connection object and returns it as an IDbConnection.
''' </remarks>
''' -----------------------------------------------------------------------------
Private Function GetConnection() As IDbConnection
'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON)
Return conn
End Function
in the above GetConnection() method i
have commented out the .net dataprovider for oledb and changed it to
.net dataprovider for SQLconnection. this function works fine. But in
the authenticate method above at the line
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error.
Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate.
If i comment out the
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
and uncomment the .net oledb provider,
Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
then it works fine.
I also have changed the webconfig file as below.
<!--<add
key="Common" value='User ID=**secret**;pwd=**secret**;Data
Source="ESMALLDB2K";Initial Catalog=cj_common;Auto
Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'
/>-->
<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
Please help. Thanks in advance.
Hi everyone
In my project, sometimes, I need to run log shipping manually rather that it runs automally when the schedule occured.
manually run log shipping means call the log shipping in code.
Thanks.
Hi All,
This may sound quite easy for you, however I am a newbie in SQL.
So I have an identity field, which I wish to increment automatically every time I do an insert. However the starting integer has to be the MAX value from another table.
So for example I am doing an insert in a #temp table
INSERT INTO #temp(name, surname)
SELECT name, surname from table1
Now the personId of the #temp table has to start from the MAX of table2
Ie SELECT MAX(personId) from table2
The SELECT MAX(personId) from table2 can also be NULL, ie the first time I am inserting, so I also have to cater for this scenario.
can anyone help?
Thanks
Johann
I am new to the ASP.NET and SQL scene.
How do you run a sql script manually?
I have MSDE SP3 installed and running. I am having a problem with the localhost setup for some of the kits and need to run the scripts manually.
Any help would be great, including helpfull learning resources.
Hi:
I have a UDF that splits a string based on a delimiter.The UDF returns a table of records:1- ID2- ValueThe string I am sending to split is:'2-4-07:00 AM-08:30 AM'I am splitting on '-'What I get back is a table of 4 rows.I want to get each value, convert it to the right data type, then assign it to its own variable.How can I get each row alone manually? Can I? Without using Cursors?if I do a "select * from Split(@myData,'-')", then I cannot retrieve each field alone,Thank you.
Hardware:
IBM Netfinity 8500
2 processors Xeon 700
1,5 Gb memory
Windows 2000 Server SP2 Build 2195
SQL Server 2000 Standard Edition 8.00.534 SP2
There is only on Database (DB) of 16 Gb in drive G.
Drive G has 32 Gb space free.
Yesterday we appended tables to the database and in SQL logs appears the next error:
2003-01-23 12:26:42.57 spid101 fcb::ZeroFile(): GetOverLappedResult() failed with error 2.
2003-01-23 12:26:42.61 spid101 Error: 1105, Severity: 17, State: 2
2003-01-23 12:26:42.61 spid101 Could not allocate space for object 'ttdssc030104' in database 'MYDATABASE' because the 'PRIMARY' filegroup is full..
2003-01-23 12:26:48.03 spid101 fcb::ZeroFile(): GetOverLappedResult() failed with error 2.
DB configured to grow automatically by 100 Mb and transaction log Automatically grow in 10% .
Unrestricted file grow selected on both.
I try to expand the DB manually by Enterprise Manager to 20 Gb but not work and in SQL log appears the error
"2003-01-23 12:26:48.03 spid101 fcb::ZeroFile(): GetOverLappedResult() failed with error 2."
In Enterprise Manager-Databases-Properties-General-Size of DB maintain 16Gb.
Windows explorer say MYDATABASE.MDB is 20480 MB.
I delete the tables inserted and the problem persist.
Thanks in advance,
I am getting 228 rows once I manually inserted the value for the ct.code in test region. I would like to insert into other regions using the SQL, how can I do that ?
select distinct
ocmi.name,
ct.code,
ctv.value
from
cv3ordercatalogmasteritem ocmi
join CV3CatalogClassTypeValue ctv on ctv.CatalogMasterGUID = ocmi.guid
join CV3ClassType ct on ct.guid = ctv.ClassTypeGUID
[code]....
Dear Experts,
please guide me, how can i run checkpoint manually?
and how can i make it done automatically? what is sql server default?
is it a problem (making the checkpoint to run more times)?
Vinod
I've been studying/experimenting with the replication features and though the Updating Susbcribers Replication Model was the answer to my data environment.
What we want: We have a live server (Publisher) that'll serve content to our users, but we want an "offline/data entry" server (Subscriber) that'll pretty much have the exact same data as the live server for data entry and validation purposes. Changes and inserts would be done on the Subscriber locally, vetted through, and once done, it'd be updated to the Publisher.
I've managed to get some semblance of the above working, but the updates are nearly instantaneous. Which means that changes/inserts that may be erroneous on the subscriber machine would almost always be seen on the publisher machine before our operations can do anything about it.
Is there a way to configure the replication process to hold off until the admin triggers it? Or am I using the wrong feature?
Please help me to clean a Windows 2000 Pro instance which is "dirty"with the MSDE 7.0 and/or/both MSDE 8.0 previously incorrectinstalled/removed.I have a software product installation containing MSDE 7.0. When I triedto install MSDE 7.0 I got an error "You have installed MSDE".*** Sent via Developersdex http://www.developersdex.com ***
View 5 Replies View RelatedHi all
I'm trying to use the Slowly Changing Dimension transformation and it seems that in order to get the transformation to work as expected the custom property "sql command" has to be manually changed so that a "RTrim" function is applied to all string columns referenced. As an example consider this:
SELECT Initials, Department_Name from dimEmployee
has to be changed to
Select RTrim(Initials) as Initials, RTrim(Department_Name) as Department_Name from dimEmployee
where Initials is the "bussines key" and "department_name" a historic attribute.
Is this the normal behaviour ??
Regards, Steen
Hi,
I have a table
Code Snippet
CustomerDetail
customerid
customername
status
app_no
deskno
Now everytime a new customer is created, i allocate the deskno. The logic I use is
Code Snippet
create proc spoc_incrementdesk
as
DECLARE @deskno int
select @deskno = (SELECT max(deskno) from customerdetail)
Insert into customers(1,'rick',1,23232,@deskno)
If there are already records present in the customer table, it works well. However if the very first record is getting inserted it inserts null in the deskno because when no records are present (max(deskno) = null).
At time time of insertion, how do i check if @deskno = null and allot it 1.
Vidkshi
I have a report scheduled for email delivery at a certain time of the day. Is there a way I can execute the subscription ON DEMAND so that when ever I execute it, the report is processed and emailed? Is there a way to run the subscription from a batch file/ssis/tsql etc?
any help appreciated.
thanks
Jacob
When I force Mirroring to fail over while watching the Mirroring Monitor I notice something interesting. Before I failover, the servers are synchronized and there is zero unsent log and zero unrestored log. When I failover, the mirror changes to "restoring", stays there for about four minutes, switches to "Principal, restoring", and then the unsent log and unrestored log numbers jump to values in the tens of megabytes, and we spend time restoring.
We wait about five minutes between requesting the failover and having the servers back up again, answering requests.
Why does failover take so long? We've been thinking that failover shouludn't take so long because the servers think they're synched. What causes the log spooling spike? Is the state refelcted by the mirroring monitor inaccurate?
Is there a way to do this? It would be useful to test whether a subsciption works. At the moment I am setting the scheduled time a few minutes ahead and waiting for the report to run.
Subsciptions are also a convenient way to store different parameter settings for a report.
My preference would be to to do this through the Report Server UI (presumably from the subscriptions tab).
Alernatively, being able to call a report (with paramters) from a stored procedure woukd be useful.
Thanks,
Dick Campbell
Hello,
I use Reporting Services in my solution. The data i have as source is very uncertain. Sometimes i manually have to delete a record from the report (not the database itself). I woluld like to have a checkbox or something simular to delete from report and totals, grafs would be updated. At the same time I would like the report to make a comment that a record was taken away, alternative let the user make a comment. Is this possible? DO i have to make a own application for that?
Thank you for your help!
Best regards,
Luskan
hi i have an sqldatasource which has an insert command - a stored procedue is used.I have a text box with a button next to it . it is not in a datagrid.on the onclick event I would like to pass the value of the text box to the sqldatasource insert parameter ( it only expects this one parameter , and use the sqldatasource to do the insert basically doing a manual insert using the sqldatasource.does anyone know if this is possible thanks
View 2 Replies View Related