Tag Info

New answers tagged

0

I cannot figure out how to get the Windows Owner associated to that PID. You can get the owner using PowerShell: # Identify the name of the remote computer you want to query $computerName = 'SomeRemoteComputerName' # Invoke the command on the remote system to get the information that is necessary Invoke-Command -ComputerName $computerName -ScriptBlock ...


1

No, it is nothing to be worried about - the Agent isn't considered a clustered service to SQL Server simply because it can't fail over on its own. You should test a failover and be confident that SQL Server Agent will start on the other node and that jobs will run etc. (sometimes there can be local dependencies that won't exist on the other node(s)), but ...


2

I suspect that the DISTINCT is a performance barrier. I suggest you get rid of the duplicates (I would think that no device would ever be in 2 distinct locations at the same time, so a unique index on (imei, timestamp) would work just fine). Such a change may need changes in your application, so until you do get rid of the duplicates, you could use this ...


2

Edit - The below is assuming that the number of rows that would be returned by the query (if the limit wasn't present) exceeds the limit. (as in ... it regularly would return 5000 rows, but the limit forces it to return 1000) Any time you have a limit on the number of rows returned by a query, you should not expect the TIMING on that query to have any sort ...


7

There is a simple solution to this: Drop all of the _dta_... statistics and stop blindly applying DTA recommendations. More information The particular problem was that there were multiple sets of statistics for the column in question. The extra dta statistics were created by sampling the data (the default behaviour for statistics not associated with an ...


1

It was a problem about the database owner. Even if you can see an owner on this interface: Be sure that an owner is entered into files tab:


-1

use a View and UNION ALL SELECT col1,col2 FROM tableX UNION ALL SELECT col1,col2 FROM tableX_Archive


1

DECLARE @vcVar AS VARCHAR(500) = 'Week 3, 2011/2011/2012' SELECT STUFF(@vcVAR , CHARINDEX('/',@vcVar) ,5,'')


4

You can use charindex to find the first / and stuff to remove 5 characters. select stuff(Reporting_Week_Name, charindex('/', Reporting_Week_Name), 5, '') Data explorer


1

The problem lies in the referencing. You might have copied the package from an older one and are making the changes or you might have edited the connection managers. The connection managers are internally used using the GUID that you have mentioned. You might have changed most of the references from the UI, but still there are some objects and tasks, that ...


0

I found a solution to this problem. Create the following files: RemoveSubscriptions.bat: @echo OFF SET Server=http://localhost/ReportServer_MSSQL_2008 SET ReportUrl=/test/reports/testreport "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\rs.exe" -i RemoveSubscriptions.rss -s %Server% -e Mgmt2010 -v reportUrl=%ReportUrl% > %date%.log ...


4

Yes there may be impact. To start with, you must read List of SQL Server features deprecated in SQL Server 2012 List of deprecated Database engine features in SQL Server 2012 Breaking changes to DB engine feature in SQL server 2012 You must run SQL server 2012 upgrade advisor and generate report before doing migration. This report would list out all ...


0

Having spent some time looking at this in more detail it would appear that the unusually large transaction log backups are due to excessive internal fragmentation of the transaction log itself. This is due to unrestricted autogrowth of the transaction log over a long period of time. The following excerpt from a SQLSkills.com article more precisely defines ...


4

A SQL Server 2008 R2 mirror is not itself readable. If you are using Enterprise Edition, you can create a named snapshot database from the mirror. This allows you to create a point-in-time readable database that can be used for pretty much any query that does not do any updates. (Though you could use another database (tempdb or a workdb) to do auxiliary ...


2

This Powershell script should be able to solve most of your issue. I use a system table select to get my list of tables, then BCP to export all the data. The challenge is exporting your BLOBs. This script will export those as character data, which may not be useful to you in your situation. Import-Module sqlps -DisableNameChecking $outputdir="C:\TEMP" ...


0

However my question is when a new record is inserted, does SQL Server take an exclusive PAGELATCH_EX on a buffer page, insert the record to the buffer page, write the record to the transaction log and then release the exclusive PAGELATCH_EX You must note that latch only protects physical integrity of page while it is in memory so latch would be taken ...


6

In order to understanding why error 3456 would be thrown, we need to take a little step back and understand how SQL Server handles this corner of recovery. When SQL Server is redoing an operation, and that redo is a page modification, it makes a quick check. In the page header there is ultimately going to be a PageLSN, which is an indication of the last ...


1

Quick and dirty, but I'm assuming you'll be putting this into a more formalizing procedure for spitting out names. DECLARE @Firstname nvarchar(50) DECLARE @Lastname nvarchar(50) SET @Firstname = 'Marc-Oliver' SET @Lastname = 'Saint-Onge' SELECT @FirstName, @Lastname IF CHARINDEX('-', @FirstName) <> 0 SELECT SUBSTRING(@Firstname, 1, 1) + '.' + ...


2

The question is for 2008R2 but here's a way to do it in 2012+ versions, using LAG() and window functions (OVER with ROWS ...): WITH cte AS ( SELECT *, blockstart = CASE WHEN Value >= 80 AND COALESCE(LAG(Value) OVER (PARTITION BY Source ORDER BY PollTime), 0) ...


5

Here's a solution that uses ranking functions only: SELECT source, polltime, value, block = CASE -- ranks the islands WHEN Value >= 80 THEN DENSE_RANK() OVER (PARTITION BY source, CASE bb WHEN 0 THEN 0 ELSE 1 END ORDER BY bb ) ELSE 0 END FROM ( SELECT *, bb = -- creates the "islands" ...


1

Here's what you want: SELECT t.source, t.polltime, t.value, COALESCE(b.idx, 0) AS Block FROM #temptable t LEFT JOIN ( SELECT t1.polltime, DENSE_RANK() OVER (ORDER BY COALESCE(MIN(DATEADD(ms,-1,t2.polltime)), MAX(DATEADD(ms,1,t2.polltime)))) AS idx FROM #temptable t1 LEFT JOIN #temptable t2 ...


0

After doing a lot more investigation this is what I have found. It fixed the issue (significant performance gain and WRITELOG has an average wait time of 0.0126 which was initially 14.681) Apparently the issue was with the Number of Virtual Log files in my physical log file. There is a job scheduled to rebuild indexes every night, the job creates 36GB of ...


2

I think what you are trying to do is relational division. Here is the best explanation I could find https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ Honestly I do not fully understand this myself yet, but I believe this should work (using an intermediate table): CREATE TABLE tblCountry ( countryId int, ...


0

I found the solution that I wanted: -- how to disable a linked server --check the existent linked servers SELECT * FROM SYS.SERVERS WHERE NAME = 'SWLON1' -- Disable data access EXEC sp_serveroption @server = 'SWLON1' ,@optname = 'data access' ,@optvalue = 'FALSE' -- ABOUT procedure sp_serveroption ...


3

SQL Server supports SSL 3.0 and TLS 1.0 only, you must have at least one of them enabled or SQL Server will not be able to start. SQL Server does not support TLS 1.1, 1.2 etc, it specifically uses TLS 1.0. Your options here are to enable SSL 3.0 and/or TLS 1.0, no other way around it I'm afraid. Cheers Mat


1

There are other DMVs that can help you quantify this. sys.dm_exec_sessions has columns cpu_time, memory_usage and total_scheduled_time amongst others. While you're running your plan-getting query in one session you can interrogate this DMV in a second session to find how expensive the first is. Pleasingly, you can also quantify how expensive your second ...


3

As I understand it, this is because the SID's of the logins don't match between our two environments. You understand correctly! How could I make it so that the SID's of the sql server logins on our staging environment are the same as on our production environment going forward? My thought is to use CREATE LOGIN WITH SID ='SID value from PRD' ...


0

Once SQL Server acquires disk space from the operating system and allocates it to a database it will not voluntarily give it back. It takes time and effort to allocate this space so it is more efficient for the database to hang onto it once it has acquired it. If you genuinely have moved the data and not copied it (you would have to post your SQL for us to ...


3

Use simply UPDATE Clause UPDATE statement is used to update existing records in a table UPDATE table_name SET column1=value1 ,column2=value2 ,... WHERE some_column=some_value; In your scenario UPDATE tbl_ngoname Set ngo_image = 'iksha_logo.gif' Where ID= 1 ---- Use Column which Uniquely identify the row.


3

There are only 4 steps to enable service broker for a Database and non of them use logical file name as parameter Try this USE MASTER GO IF EXISTS ( SELECT is_broker_enabled, Service_Broker_Guid,* FROM sys.databases WHERE name = 'DB2' AND is_broker_enabled = 0 ) BEGIN -- Step 1 ...


0

Although this doesn't answer your specific question, it may preclude you from needing to ask the question in the first place: It's possible to set a length on your string variables in your C# model object, which will cause Entity Framework to generate SQL that uses a fixed-length nvarchar type (e.g. nvarchar(50)), instead of nvarchar(max). For example, ...


0

It is normally because there is a difference between how they are written and how sql stores them. As a one time thing to a compare back from the db to the project and you should see some difference on the keys, apply them and it should stop doing it.


0

Our system Sql Historian removes the need for "developer discipline" by automatically maintaining the latest version of your db objects into SVN or TFS. It was designed to be transparent to your developers' current workflow, so you will have a perfect record in version control without any loss of time or complaints from the staff.


0

For now, the issue has been addressed by: Converting AllEligibleEntries to a temporary table Converting NumberOfEligibleEntries to a local variable, obtained from a select on the temporary table Converting SelectedEntries to a DELETE WHERE NOT BETWEEN operation on the temporary table Keeping the remaining CTEs as-is. Performance is measurably better (a ...


2

We want to keep old databases "alive" for some period of time but once we've gone live we don’t want staff accidentally going into old database, so taking the database offline for awhile then drop it would be the solution? Instead of making database OFFLINE, make the database "READ ONLY" using : ALTER DATABASE database_name SET READ_ONLY And monitor ...


1

I had thought you would be able to use policy based management but couldn't find a way to get the PasswordLastSetTime property. It didn't exist in the Login facet. However what you an do is run the following query to get the PasswordLastSetTime for all SQL Logins on the instance, then use a tool such as Idera's Multi Query or a CMS (Central Management ...


0

If you backup your transaction log you will be able to restore up to a given point in time using the stopat option. As previously mentioned you should restore it on a different database. That way you will be able as much data back as possible. RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; ...


2

I'd put this in a comment but I want to give pictures. My guess is that you are hitting the little green arrow, or DEBUG button Instead of the red exclamation point, or RUN button. It's a fairly common mistake. FYI you can also hit Ctrl-E to "execute" the script or Alt-F5 to "debug" it. The TSQL debugger requires elevated rights (sysadmin if I ...


0

You should not use T-SQL to do filesystem tasks like move files, copy files, etc. Instead use PowerShell. SQL Server has support of PowerShell and you can schedule it using SQL Agent job as well. You should look into Move-Item or Copy-Item cmdlets of PowerShell. There are plenty of scripts that will help you.


0

'(There's) no such thing as a stupid question' is a popular phrase that has had a long history. It suggests that the quest for knowledge includes failure, and that just because one person may know less than others they should not be afraid to ask rather than pretend they already know. :) I am using below code to transfer full backups to another location ...


2

I searched on web, but could not find any such stuff which clarifies on what point should we or should we not change the compatibility level of the migrated db's. As a good practice and suggested by Microsoft you should change compatibility level of database to match with server version on which it is hosted. There are few legacy applications which ...


0

Please follow below mentioned steps: Restore your DB backup on other server or on same server if it not a production server and you have enough space exist on this server. Right click on new restored database and select Task --> Export data select --Source server and database ---> destination server and database choose your dropped table and export it on ...


1

You can restore the BACKUP with a different name in the same instance and follow steps below. 1) restore the database ( right click on databases > Restore ( it can be in the same instance )) with a differente name. 2) In this new database ( The restored one ) , open tables , search the table you've deleted, right button, Script table as > CREATE TO. This ...


0

To summarize discussion on the topic, creating this sort of "Uber" view with several tables that have matching primary keys and similar (if not the same) row counts is not necessarily a bad idea for the purpose of streamlining code. Additionally, any tables or columns not used in subsequent queries on such a view are dropped from the execution plan in most ...


0

Yes, this is possible. There are many different ways to achieve it - but I would suggest the following rough steps using SSIS (SQL Server Integration Services). Create the SQL database that will hold the data from Excel, and add a table to it with the structure that you want. Install an SSIS File Watcher Task, such as this one: ...


0

sp_dropserver 'real-server-name' was giving error remote login There are still remote logins or linked logins for the server ... therefore, execute following query sp_dropremotelogin old_physical_server_name but gave following error because of replication There is no remote user '(null)' mapped to local user '(null)' from the remote server ... then ...


0

This error has been observed on a server that had been renamed after the original installation of SQL Server, and where the SQL Server configuration function ‘@@SERVERNAME’ still returned the original name of the server. This can be confirmed by: select @@SERVERNAME go This should return the name of the server. If it does not then follow the procedure ...


-1

I think your view solution could be appropriate in a scenario where you would vertically partition the data, i.e. where all tables contain the same columns and the view would merely UNION those rows with check constraints. However, what you're proposing is a view that joins a large number of tables together, i.e. partitioning horizontally. Even if we assume ...


3

You're confusing a few terms, I think. The ndf file is not an "index file", it is just another database data file to house the data. It sounds like you have two database data files (the MDF and NDF). With the size of your MDF being so small, my guess would be that your database has two different filegroups (the PRIMARY filegroup using the MDF, and the ...


0

Directory Service One way to deliver a connection string is through a directory service such as LDAP. If every human and app looks up the connection string in the directory service, then you have only one place to update when a change happens. Programming environments often provide access to directory services. For example, Java offers JNDI.



Top 50 recent answers are included