Tag Info

New answers tagged

0

I always favor PowerShell when it comes to needing OS level information such as file system info or manipulation, and even WMI calls to get hardware info of a server. So this is just my version using PowerShell...Oh and in the event you ask I learned how to do it this way via Laerte Junior's article The PoSh DBA: Solutions using PowerShell and SQL Server. ...


1

In addition to Mark Iannucci's comments, I'd recommend making ArtistID a field in Files, not Albums, to support soundtracks and other multi-artist collections. You may retain an AlbumArtistID in the Albums table, if you think it would be valuable. If you want to be really thorough, you could consider having several artist fields in Files: ComposerID, ...


1

I recommend using unique keys instead of artist or album names. Doing this will improve the database design because currently a change in an artist's name would require updating each entry in your audio_files table for that particular artist. You and I likely agree it would be strange for an artist to change their name, however they have a penchant for ...


0

If you are looking for a running total for each row, you have to set up a user variable. The purpose of the user variable is to keep the total value after each row like a spreadsheet cell. PROPOSED CODE SET @rt = 0; SELECT *,(@rt:=@rt+IF(transtype='D',MemTot,-MemTot)) RunningTotal FROM member_trans; SAMPLE DATA I am using the first ten(10) rows from ...


0

I'm going to have to make some assumptions here. I've done something similar to this in the past so I think I know what you are looking for. Note: I'm using SQL Server 2012 since you did not specify a DBMS but the theory will work for anything. First you have a detail table with a CustomerId and a series of values for that customer. In this case call it ...


0

Would that work? :) It does work in SQL Server 2012 SELECT t.mem_id, (MAX(CASE WHEN t.transtype = 'D' THEN t.MemTot END) - MAX(CASE WHEN t.transtype = 'C' THEN t.MemTot END)) AS Diff FROM ( SELECT 1 AS mem_id, 'D' AS transtype, 1129.80 AS MemTot UNION ALL SELECT 1, 'C', 750.90 UNION ALL SELECT 2, 'D', 568.60 UNION ALL SELECT 2, ...


0

Try www.sqlscripter.com It supports INSERT statements as well as UPDATE etc., so you can do something like this: IF NOT EXISTS (...) INSERT ... ELSE UPDATE ... It works for us so it may work for you.


2

If your data structure is fixed and out of your control, then the best way to do this is to use a recursive common table expression (CTE) like in this question. If you can change the structure then there are ways to make such queries considerably more efficient. What you have there is often called a "naive tree" - while it allows easy construction and easy ...


0

Dont kill the SPID's instead try to see you can stop from your end to see. Also the suspend means it is waiting for some resources as in your case it is cxpacket- Firstly the query which is running from the SPID's is normal(consider it is running many days with the same ) or did any changes happened like any logic has changed or any outdated statistics(if ...


4

Restore the backup you have from 15.12.2014 to a new database and then you can delete the rows from your production database that does not exist in your restored backup database. There is no hidden datetime information in the table that you can use.


0

The post you link to only discusses capturing a single scalar value into the variable, not an entire result set. You are trying to capture more than one column and presumably more than one row. MySQL variables can only hold one scalar value. It seems like what you are looking for is a temporary table, but on the other hand, your explanation is difficult ...


0

This is what worked for me in the end. SELECT [StartTime].[PossibleTime] AS [StartTime] ,[EndTime].[PossibleTime] AS [EndTime] , (SELECT COUNT(1) FROM [Log] AS [L] WHERE [StartTime].[PossibleTime] >= [L].[loginTime] AND [EndTime].[PossibleTime] <= [L].[logoutTime]) AS [UserCount] SELECT ROW_NUMBER() OVER (ORDER BY [PossibleTime]) AS [ROWNUM] FROM ( ...


0

"a boolean column stating whether or not a group has any users" Use EXISTS: CREATE VIEW group_info AS SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty FROM groups g; This returns 1 row per group, no matter whether there are users or not - not one row per user like you had, but probably didn't want - so we don't need ...


0

I was able to figure this out by just using multiple CTE's, then selecting what I needed to combine.


1

Here is a working FOR XML EXPLICIT example. They are a bit harder to code, but I tend to build them up, section by section so they're not so bad: USE tempdb GO SET NOCOUNT ON GO IF OBJECT_ID('dbo.Borrowers') IS NOT NULL DROP TABLE dbo.Borrowers CREATE TABLE dbo.Borrowers ( ID INT IDENTITY PRIMARY KEY, FirstName ...


1

Check for DBCC OPENTRAN for any transaction on these tables. If NOT then you are free to KILL these sessions as these are only select statements. Find the sessions by querying sp_who2 and kill by syntax KILL :-)


1

To add single quotes in a string literal, just double it, or use the chr function. E.g. 'hello '' world' represents the string hello ' world. 'hello '||char(39)||' world' is the same. '''' is a string literal representing a single single-quote. To get your fixed end time, just concatenate it. select ''''||to_date(:From_date,'dd-mon-yyyy')||'''' from ...


2

To get partition elimination, your queries need to be explicit about the bit data type, for example: ...WHERE Archivable = CONVERT(bit, 0) -- NOT ...WHERE Archivable = 0 Without this, the risk of truncation in the implicit conversion means partition elimination is not applied. The implicit conversion in the CREATE PARTITION FUNCTION statement is not a ...


3

If you do 'NewID' as [field/@id] you will get a field element with an id attribute. On the next line you add B.ID as [field] ot get the value of ID as the node value to the field node you created on the line before. <field id="NewID">1</field> After that you want a new field node and to create that you can use a node without a name that has no ...


1

Apparently, creation of CDATA nodes is possible only in the FOR XML EXPLICIT mode. Moreover, Microsoft implementation of XML does not respect them, in accordance with W3C recommendations. Here is a link: How to get [CDATA] with FOR XML PATH? So, if you truly need this, you'll have to write an ugly monster, and don't even think about the TYPE option - it ...


1

Figured it out SELECT temp1.[file], temp1.[ChangeDate] AS [AddedDate] ,temp1.[OldFolder] ,temp1.[NewFolder] ,(SELECT MIN(temp2.[ChangeDate]) FROM [Changes] AS temp2 WHERE temp2.[OldFolder] = temp1.[NewFolder] AND temp2.[ChangeDate] > temp1.[ChangeDate] AND temp2.[file] = temp1.[file] ) AS [RemovedDate] FROM [Changes] AS temp1


0

Here is a non-correlated version that should be more efficient since it isn't doing the lookup per each row: UPDATE t1 SET t1.[temp] = t2.[temp] FROM [myTable] t1 INNER JOIN [myTable] t2 ON t2.[uid] = (t1.[uid] + 1) WHERE t1.[uid] <= 9;


1

You should be able to use SQL Server's proprietary and admittedly awkward self-reference form of delete: DELETE RemoteTable FROM OPENQUERY(...) AS RemoteTable ...


0

Got it !! -- shift column values up one row. UPDATE [myTable] SET [temp] = (SELECT [temp] FROM [myTable] AS s2 WHERE s2.[uid] = ([myTable].[uid] +1)) WHERE [uid] <= 9; This moves just the specific cells up one row, as desired. It was more complicated than I thought it would be, but this seems ...


1

I recommend using a Calendar table or Date Dimension (whichever name you prefer). Here is an answer with using a quick CTE. /* Date Range CTE */ -- Updated based on @AaronBertrand's articles linked in the comments -- Basically ends up being the same query as the last half of @AaronBertrand's post declare @FromDate date; declare @ThruDate date; set ...


1

Unless you have cross-database ownership chaining enabled (off by default) and have the same User in both databases, then giving access to an object in one database does not imply anything permission-wise to other databases. Of course, I am not recommending that cross-database ownership chaining be enabled, and neither is Microsoft. Instead, you should ...


1

Well, you should have a Numbers table or a Calendar table. I'll start with a Numbers table: CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY CLUSTERED); INSERT dbo.Numbers WITH (TABLOCKX) (Number) SELECT TOP (1000000) Number = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2; Then: DECLARE ...


0

You need to work with your storage vendor to figure out why the writes are being torn. If it's happening to tempdb it's very likely that it's happening to another database as well. This is especially true if it's happened more than once.


2

UPDATE ... SET (a, b, c) = (1, 2, 3) ; Yes, your understanding is correct and this is perfectly legal SQL syntax. As @a_horse_with_no_name mentioned in the comments, you can also use it in conditions (WHERE, HAVING, CASE WHEN, ...). Examples: WHERE (a,b,c) = (1, 2, 3) WHERE (a,b,c) >= (1, 2, 3) WHERE (a,b,c) IN ((1, 2, 3), (1, 1, 1), (2, 2, 2), (4, ...


1

I'd say that this is pretty standard stuff ; Create table employees (id int primary key, name varchar(100) -- Plus some additional columns ) Create table responsibilities (id int primary key, description varchar(100) ...


3

First off, you do not want to use char(50). Use varchar(50) or just text. Read more: Any downsides of using data type “text” for storing strings? Assuming the following rules: Basic slugs never end with a dash. Duplicate slugs are suffixed with a dash and a sequential number (-123). Note that all of the following methods are subject to a race ...


0

Thanks for your input. Actually i found it. Those tables created by one SP which is set to execute on start-up of database engine service and configured with SP_PROCOPTION system stored procedure.


1

I don't think that this completely satisfies your request, but it is a start. If you want further ordering, I think you need to go into more detail with the sorting rules. Also, the example data from your fiddle has "five" with a created date older than "four". SqlFiddle select * from table1 order by coalesce(position,2147483647) , created; ...


0

It may be more efficient to simply use a cascading delete via a foreign key. When a row with the same product_code is deleted from products, it will be deleted in sales_detail as well. ALTER TABLE sales_details ADD FOREIGN KEY fk_product_code(product_code) REFERENCES products(product_code) ON DELETE CASCADE; But if you'd like to use a trigger to delete ...


2

Yes, it looks like you're using Ola Hallengren's backup scripts which operate in hours, so 14 days * 24 hours = 336 hours. Every time this is run, a full backup of the user databases will be taken and any copies older than 2 weeks will be deleted. Edit: Based on the OP's edit, you are fine to schedule a job executing your first script as a replacement ...


0

Irrespective of your BACKUP and RESTORE statements not matching to think you actually restored what you thought your issue is that the Table_1 is apparently in the file group you tried to restore. The syntax for your RESTORE command should include in the WITH clause the command NORECOVERY or RECOVERY. The later tells SQL Server to complete the rollback ...


0

Create a log table for your use such as: CREATE TABLE dbo.ImportLogging (ImportID INT IDENTITY, ImportFileName NVARCHAR(200), ImportBeginTime DATETIME, ImportEndTime DATETIME); That assumes that you want to save the file name, begin time, and end time. That is up to you, of course. I would log the ImportBeginTime at the start of the import, then add ...


0

Research recovery model. Default - simple - contains transactions in action. Full recovery records transactions since last log backup; in case you want to restore back to before the transaction ( corruption ) occurred [need to have a full backup first!] Simplistically: Two files: two states. MDF - truth. LDF intended truth. Backing up log commits the ...


2

2nd query: Of course you get duplicate rows. 1 row per producto each fabricante is connected to - multiplied with the number of rows in pais each combination is connected to. 1st query: An explicit JOIN binds before (groups of) comma-separated items in the FROM list. This is why you cannot reference fabricante in the JOIN condition between pais and ...


0

The transaction log allows for two features: Performance. It allows for sequential writes to finish the 'commit' phase more quickly than if the database engine had to write to all the random pages necessary for the transaction(s). Changes are written to the log file sequentially (faster than random writes) and then flushed during the 'commit' phase, at ...


3

Whenever I make a change to a table, does it get written to both the database file AND the transaction log file? It is written to both, but it is written differently to each. The changes are made to the data pages in memory and are eventually flushed to disk via the checkpoint process. the changes are sent to a log buffer and hardened to disk at some ...


0

In the second query you ommited nomencladores.pais.* in the select and as there can be several pais_id for each distribuidor_producto_solicitud.producto_solicitud_id it is very likely that you are getting repeated results for this reason. However, if the only problem is that you are getting repeated results the only thing you need is to add the keyword ...


1

If you are mostly loading the data, and seldom querying it, then no need to split the table. Any "home-grown" attempt at splitting the table is bound to cause grief; use Oracle's (extra-cost) partitioning option if your queries are usually date-ranged; but be careful; partitioning does not necessarily improve performance. It can if the queries include ...


0

The quick and dirty way: Select * into [dbo].[NewTable] from [dbo].[ExistingTable] Inserting Rows by Using SELECT INTO via Technet


1

I followed @ypercube's suggestion and just rewrote both queries - turns out there wasn't a relationship in the database that was needed to tie two tables together, and when there were multiple relationships on a set of columns MySQL did what @JNK said and was non-deterministic and grouped inconsistently. If you run into something like this, check your table ...


1

If you have advanced partitioning and can partition by date range and sub partition by charge, then you can reduce the amount of logical IO required to get to your data, provided that you are doing date range based searches that mostly use charge to filter. If you don't have advanced partitioning, you can still partition it your self, but will have a higher ...


5

You have to break it up into batches because the [Column] does not exist since the statements are being parsed as one batch so it cannot see the new column, hence you receive the error. Once you break it up into batches with GO statement the column will be added to the table and then your next batch updating the new column will be successful. Full example: ...


0

The chances are that it's more efficient to use a FTS rather than an index scan. If you're doing: SELECT a,b,c FROM table1 with no WHERE clause, then the RDBMS is going to have to read the whole table anyhow. Depending on the size of the table, it's almost certainly quicker to read the whole table rather than look up 1 row at a time. The RDBMS can read ...


0

Since you specifically asked about phpMyAdmin and the previous answers, while correct, are both about doing this on the command line: Navigate to the database you wish to export. Across the top row of tabs click "Export" Next, click the Custom radio button Halfway down the "Format-specific options" area, select "structure" (the default is "structure ...


1

Well. it seems it was Oracle SQL Developer fault because when I deleted it and downloaded the newer edition it worked.



Top 50 recent answers are included