I have a problem upgrading a McAfee ePolicy Orchestrator which uses SQL Server 2008 R2 SP2. McAfee logs indicate that server names from SQL server and from McAfee installer does not match.It seems that the reason is that machine name was changed after SQL Server was installed.
MSDN instructions for sql server name changing give next commands to solve this problem:
SELECT @@SERVERNAME AS 'Server Name';
sp_dropserver <old_nameinstancename>;
GO
sp_addserver <new_nameinstancename>, local;
GO
But it didn't work for me, because "SELECT @@SERVERNAME AS 'Server Name'" shows correct (actual) name of sql server. So sp_dropserver says that there is now such instance.In the same time if I use this commands:
We ran into weird/interesting issue with below details.
Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script step # 2 - Actual DDL SQL statements step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
SQL Server 2012R2 Express, I need to change db owner from Domain1User1 to Domain2User1.
Both SQL login Domain2User1 and db user Domain2User1 exist; db user Domain2User1 is mapped to SQL login Domain2User1.
When I try to change db owner from Domain1User1 to Domain2User1
I get the error:
This article does not imply any restrictions on users that can be made db owners: [URL]....
"In SQL Server, the owner of the current database can be changed. Any user, a SQL Server login or Microsoft Windows user, who has access to connect to SQL Server can become the owner of a database."
Why can't I make the owner of a db an SQL login that have a mapped user in that db?
I am trying to change the default trace value from 2 to 6, to incorporate rollover and server shutdown. I am using this code to create a new trace (with id: 2) with the value of 6 and use that to overwrite the default trace (with id: 1):
Then I disable the default trace. I am then using this code to overwrite the default trace (with id: 1) and replace with new value default trace (with id: 2):
-- get trace status SELECT * FROM ::fn_trace_getinfo(NULL) -- stop trace EXEC sp_trace_setstatus @traceid = 1 , @status = 0 go -- delete trace EXEC sp_trace_setstatus @traceid = 1 , @status = 2
Then I enable the default trace. It works perfectly (default trace with id 1 is showing value 6) until I restart. Upon restart no default traces are enabled, once I run the script to enable the default trace then the values for the default trace (with id: 1) are back, and my value for rollover/server restart is back to 2.
I want to move the log location to the E drive because it can get quite large. I already changed the setting in the SQL Server Error and Usage config tool, but this did not seem to make a difference.
We are experiencing authentication/ Kerberos issues after a password for the MSA's has changed. We use MSA for our SQL 2012 and windows 2012 combination Servers. This errors creates issues while backing up and Service Broker connectivity.We had to restart sql services to fix it, but this not seem to be a resolution for me because the next time the password changes on these MSA's we may have to restart sql services.There seems to be a known issue for windows 2008 R2 servers and fix is available and we incorporated it. But the issue we are facing is on windows 2012 Server.
We are developing an application that requires change tracking.We tested it in development and test environments and we are preparing our production deployment.The very first thing that needs to be done is an
ALTER DATABASE [db_name] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
We are holding on this first step because this statement alone executed for a good 4min on the development server. The production environment is many times larger and busier, and we can’t afford service disruption, so we are at the point where we need to understand what’s involved in running this ALTER DATABASE statement.Is there any documentation on what is happening behind the scene when this statement executes such that we can assess the risks of running it in production?
I found one database in availability group state change automatically. I found ERR started at
0000233c.00002848::2015/09/11-03:51:09.840 ERR [RES] SQL Server Availability Group: [hadrag] ODBC Error: [HYT00] [Microsoft][SQL Server Native Client 11.0]Query timeout expired (0)
And I use google to search and found the link below. It indicated a network issue. Is it the source of the problem. By the way, My failure Condition Level is 1 and I have increased Health Check Timeout from 30,000ms to 150,000ms just now. [URL] ...
--- SQLDIAG ---
cluster log in UTC+8:
0000233c.00002848::2015/09/11-03:51:09.840 INFO [RES] SQL Server Availability Group: [hadrag] SQLMoreResults() returns -1 with following information 0000233c.00002848::2015/09/11-03:51:09.840 ERR [RES] SQL Server Availability Group: [hadrag] ODBC Error: [HYT00] [Microsoft][SQL Server Native Client 11.0]Query timeout expired (0)
one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.
DECLARE @Parameter_XYZ varchar(25);
Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.
Can we get stored procedure text between different alteration.
I added a secondary data file to TEMPdb yesterday and gave it a wrong location by mistake. If I try to change the location, then I am getting an error now. I think that is because TEMPdb is in use and that is why I cant change it's secondary file's location. Do I need to take TempDB offline and then change the secondary file's location??
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
I'm new to SQL. I have a scenario, Where customer want to move all the jobs from original SQL server to some remote SQL server and want to trigger jobs on remote server to do its work on original server.
I want to schedule a job which pulls files from a non SQL server (Sybase) which later needs to have a step 2 kicking the ssis package. Problem is that, on the source a batch file will run every 4 hours and outputs total of 10 text files. (takes 5 minutes complete). Now, on destination i want to pull these files via SQL job but while scheduling;
1. I don't see any option saying like 4 hours 10 minutes or so 2. If its out there, then i believe this might be a problem as this time would be an increment one e.g next run would be 4 hours 20 minutes in that case.
How should i achieve pulling these files up because we have an SSIS package on destination that needs those text files to be used as soon as they arrive on SQL server(destination)
The SQL Server Agent (MSSQLSERVER) service on server started and then stopped. If i Manually start the services also SQL Server Agent services are stopping automatically.Though I selected Auto Restart SQL Server Agent if it stops unexpectedly, The services are stopping automatically.TCP/IP protocol was enabled for port 1433.Are there any setup I missed?
I want to create a database on my local machine and work with it. But I am not able to see the name of my local server under "Database Engine" in the list of "Registered Servers".
Options I tried:
(1) In Registered Servers window, On Database Engine, I right clicked and in that selected "Update Local Server Registration"
(2) In Registered Servers window, On Database Engine, I right clicked and in that selected "New" -> Server Registration. In "New Server Registration" Window, in "Server Name" drop down I clicked "Browse for more" but still in "Browse for Servers" window, I am not able to see the name of my local server in "Local Servers" tag under Database Engine.
(3) If I do File -> Connect Object Explorer -> Connect to Server window in "Server name" drop down box, if I click "Browse for more", I am able to see local server name eg. "XYZ" under Analysis Services, Reporting Services, and Integration Services but not under "Database Engine"
Please help me create a new database engine in SQL Server 2005 or list my local server under "Database engine" ?
Hi everybody. I was trying to install MS SQL Server Engine this morning. At the command prompt I typed setup SAPWD=password SECURITYMODE=SQL (where password was my password). After running the MSI I got the following error: Product: Microsoft SQL Server Desktop Engine -- Setup failed to configure the server. Refer to the server error logs and setup error logs for more information.The Event ID is 1013. Can anybody tell me how to install this program. Thanks in advance for any help.
hi,I'm building a simple search engine using keyword and title of a page:select ID, content_ID, 3 as 'weight'from tblPagewhere keywords LIKE '%test%'unionselect ID, content_ID, 2from tblPagewhere title LIKE '%test%'now I want to sum and order 'weight'thx
Using the SQL Server Everywhere CTP, when I create multiple SqlCeConnections using the same data file, do they each open the file, or is there some sort of engine component running in the process that they each use?
I have been trying to configure a linked server to AD and have found plenty of write ups on how to do it, but have had zero luck with getting it to work. I also read that the ability was removed after SQL 2008; is that correct? I am running 2014 and am seeing the following error trying to expand the tree:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)...An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ADSDSOObject" for linked server "ADSI". (Microsoft SQL Server, Error: 7301). URL....Then the following error when trying to query:
SELECT * FROM OpenQuery(ADSI, 'SELECT displayName FROM ''LDAP://<DOMAIN>/DC=DOMAIN,DC=<DOMAIN>'' WHERE objectCategory=''User'' ') Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT displayName FROM 'LDAP://----/DC=----,DC=----' WHERE objectCategory='User' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
The linked server is set for 'Be made by the login's current security context' and local to remote is configured SA to a domain account we use for lookups. It does not seem to matter if I am logged in to the server with SQL or Windows credentials.
What's new with NUMA in SQL Server? Is there any way to get a reading, from SQL Server or perfmon or hardware vendor tools or VM tools, of how much you are crossing NUMA boundaries, and what that is costing? The only "NUMA management" trick I know is setting maxdop at or below the number of cores on a physical processor. I guess the rest is supposed to be automagic.Is there anything else one can see or do regarding NUMA?
Server consolidation is the word of the day here. One of the elements is to use, when applicable, virtual machines in our devstageprod environment. Our sql servers (with local storage) are well utilized and run fine. I don't want to move the entire server (sql 2005 engine + data) into a VM as I think performance will be bad. But, per my question, if only the sql engine is running in a VM while the data is stored on our SAN, I think we may achieve acceptable performance.
Do you have any thought or experience with this mix of Vm and SAN from a sql perspective?
i want to ask why database i cannot back up or expand if i make expand the database the message showing "the database (database name) is not accessible. (object explorer)before the database is working fine,
I have SQL 2008 R2 version. The server has enough disk space where the SQL is running. But the log file is not refreshed. in other words a new sql server log file does not create a new file if the old one is full.
I have a few questions about the SQL Server Desktop Engine (MSDE), sorry there are so many ..
1. Which software products does this come with ? 2. If I create a SQL Server Database and want to distribute it to clients, how do I actually do it with MDSE ? 3. If I need to "upgrade" my database at some point in the future (that may contain data at a client site) how would I go about getting all the data out - would I need to write an external app ? 4. What are it's pros and cons when compared to installing full SQL Server on a client site ? 5. I've heard that the preformance of an MSDE Database degrades severely after more than 5 concurrent connections, is this just due to the performance of the product or is it something to do with the licencing model ? 6. Is it possible to write applications in, say VB, to alter the database once it has been installed on client site e.g. modify/create tables, indexes, stored procedures ?
SQL Server 2005 SP2 (build 3054)Consider the following scenario:- A complex multi-statement table valued function is created. Let's callit dbo.tfFunc(@Param1, @Param2)- A SELECT statement is executed, that calls the above function twice,each time with a different set of parameters. In pseudocode:SELECT <column list>FROM dbo.tfFunc(1, 2) AS f1<some JOIN operatordbo.tfFunc(3, 4) AS f2ON f1.col = f2.colINNER JOIN dbo.Table1 AS t1ON ...etc.The exact statement is probably irrelevant, as long as the same table-valued function is called twice (I have observed the issue in two verydifferent statements calling the same function). The statement isexecuted in a SNAPSHOT isolation level transaction, although this mayalso be irrelevant.- The statement continues executing for a long time. If sp_who2 is run atthat time, the following row is returned for the statement connection(only relevant columns are shown):SPIDStatusBlkByCommandCPUTimeDiskIOLastBatch63 SUSPENDED63SELECT2928268308/31 18:17:37The statement appears to be blocked by itself. If sp_lock is run at thattime, the following rows are returned:spiddbidObjIdIndIdTypeResourceModeStatus63213166246410TABSch-SGRANT63213166246410TABSch-MWAITIt appears that SQL Server waits indefinitely trying to obtain a schema-modification lock on a resource which already has a schema-stability lockplaced on it by the same connection.The following is pure speculation, but it seems reasonable to assume thatthe server has materialized the result of the first call to the functionusing a temporary table in tempdb, and is trying to materialize theresult of the second call using the same temporary table (same ObjId insp_lock results).I do not know why this does not cause a deadlock error.Unfortunately, I do not have a simple repro script for this. The actualcode is rather complex. While I can devise a workaround, this does looklike a bug. I am posting it here before submitting a bug on Connect, incase anyone can shed some light. Thanks.--remove a 9 to reply by email
I am restoring a backup of sql server 2005 which I inherited on to sql server 2012 and is in a recovery pending state and the reason why is, this is a backup from a different domain and does not have the logins from the legacy domain, looks like its some access issue as its not being able to find the necessary login on sql server 2012.
I try to find some feedback regarding setting the TempDB files on a RAM disk.Specifically I am looking for "production results" that could show the difference/benefit of such an usage.The tests on physical server and VM I already made have shown a boost in overall SQL Server 2012 performance on SQL Server instances housing data for SharePoint 2013 and Dynamics AX 2012 R2.Graphic below show differences between 5 different configuration on the same physical server:
- Physical HD: Server with local HD - Physical SANEX1PRD: Server with TempDB files stored on a low-end SAN - Physical SAN1: Server with TempDB files stored on a high-end SAN (around 100000 IOps) - Physical SAN1 Jumbo: same setup with Jumbo Frame activated on NIC and DB engine - Physical RAMdrive: with TempDB files stored on a 16 GB soft RAM drive within OS memory
Results were really impressive for the DB engine housing Dynamics AX data. My colleagues from the SharePoint team told me it also boosted a bit overall SharePoint performances but they did not have any baseline comparison to show.If you have some feedback, results, links, whatever I am interested.Indeed before setting this to all our SQL Server 2012 instances I y rather collect some *real world* feedback.
