Tag Info

New answers tagged

0

I think this is a pretty good solution. It uses a union all between two selects with Not Exists sub-queries. Better than combining Union and Except because you can include fields that don't match in the projection. SELECT 'A' TableName, FileType FROM KFX_Inventory I WHERE Not Exists (Select Top 1 1 from KFX_FileType FT WHERE FT.FileType = ...


0

Removal as suggested in comments above. Will this work for you? select Invoice_TBL.ClientFullName ,InvoiceItems_TBL.StockID ,Stock_TBL.ItemCode ,Stock_TBL.ItemCategory ,Stock_TBL.ItemDescription ,Stock_TBL.Unit ,Stock_TBL.NoOfUnitsInACarton ,SUM(InvoiceItems_TBL.Quantity) as TotalQuantity ...


1

With this solution you can easily know which jobs are on the queue to run with a simple select to a table. Also, if the server goes down for some reason, cleaning the jobs that are waiting to run is as easy as a truncate on a table. And still if you want them to run you needen't do anything and they'll run orderly. Create a table (Say, ...


0

I suggest you to use CASE WHEN statement like this: .... WHERE 1 = CASE WHEN <criteria1 is true> THEN 1 ---> (1=1) is true WHEN <criteria2 is true> THEN ---> <also criteria1 is false> ----v nested CASE WHEN <criteria2-1 is true> THEN 1 ...


0

Can you explain what do you need to do ? "If you have one criteria, then you go for the id only, however if you retrieve two criteria then you need to add another column in the where statement." The code below works, but does it solve your problem ? SELECT * FROM datatable WHERE id IS NOT NULL AND name = 'cost' AND value > 5


3

You can convert your string to XML and use a cast as xs:int ? to check if the value is an integer or not. declare @T table(C varchar(100)); insert into @T(C) values ('Widget (1234)'), ('Different Widget (123)'), ('Many Widgets (2x) (4567)'); select T.C, T2.X.value('(for $n in X return $n/text()[1] cast as xs:int ?)[1]', 'int') as Value from @T as T ...


3

If the data you need is always at the end of the string, would something like this work? declare @test varchar(200); set @test= 'Many Widgets (2x) (4567)'; select substring(@test, (len(@test) - charindex('(',reverse(@test))) + 2, charindex('(',reverse(@test)) - 2 ); We can reverse the string, find the ...


5

If you have a table where the relationship between the numbers and letters is stored, it's rather trivial. If you haven't you can simply add it - every time you run the query - as a CTE or derived table: SELECT COALESCE(test.a, test.b, test.c, test.d) AS test, map.letter FROM test LEFT JOIN ( VALUES (1, 'e'), (7, ...


1

In more research and diving into the msdn site documentation I have found and am now using. sp_add_schedule, sp_attach_schedule and sp_detach_schedule, sp_delete_schedule so when the job sees that others are running ATM I am doing this: exec msdb.dbo.sp_add_schedule [MSDN link][1] @schedule_name = @TempScheduleName, @enabled = 1, @freq_interval = ...


1

Yep, indeed you can! So you are hoping to have one job complete and the last step of that job would be to kick off another job? Here is the command to kick off the next job: sp_start_job E.g. The first job has a schedule and final step of the job is to run: exec msdb.dbo.sp_start_job N'My Second Job'; "My Second Job" doesn't have a schedule, relying on ...


0

Well, in the article you reference, he specifically states "The first category of scripts fall out of the realm of database changes." That said, I've worked with plenty of legacy codebases that had select statements in source control; they were just in the application code. (So, subject to all the problems you mention and hidden from the DBA.) Before ...


13

That is just documented behavior. I don't think anyone messed with the settings. See data type precedence on MSDN. As noted in the comments the empty string gets converted to 0 in any numeric type and to 1900-01-01 00:00:00.000 when converted to a date. EDIT: I think your real problem is that your design is so that you have to join on fields of a ...


0

You should use the nodes() method of the XML data type to shred your XML. If I understand you correctly you want to fetch the nodes from the root node where the attribute type is line and from that node you want the nodes where the attribute type is 'item'. I will just assume that the XML you have provided with numeric node names is not the actual XML you ...


0

Is there any way the XML can be better formed (I understand this isn't always possible)? I've never come across dynamic element names before, and while that certainly doesn't mean it can't be done, it's much more difficult. However, if you're able to get some static items in your XML, it makes it much easier to deal with. If the XML was stored in a similar ...


-1

As i understand you need the XML upload option which customized but not hard coded one you need. SELECT * FROM T UPDATE T SET XmlCol =( SELECT * FROM OPENROWSET( BULK 'C:\SampleFolder\SampleData3.txt', SINGLE_BLOB ) AS x ) WHERE IntCol = 1; GO The answer is available in MSDN, Please refer the below link MSDN Article


1

In this case, if you cant serialize this in the application code you can use try/catch or an applock begin tran if APPLOCK_TEST ('public','idx_create','exclusive') = 1 begin EXEC sp_getapplock @Resource = 'idx_create', @lockmode = 'Exclusive', @DbPrincipal='public' print 'I can do it' -- EXEC ...


6

It means the number of columns you select in the top query must be the same as the number of columns in the second. If you don't have the same number, you can work around it. Below I can add a NULL to the second query because it's missing a third column. SELECT col1,col2,col3 FROM t1 UNION ALL SELECT col1,col2, NULL FROM t2


1

You can use this stored procedure to the trick You need to provide the table name you wish to query note that if you'll pass to procedure the @exec parameter = 1 it will execute the select query SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[SP_SELECT_NON_NULL_COLUMNS] ( @tablename varchar (100)=null, @exec int =0) ...


1

I have been troubleshooting an issue within a database that has some serious blocking issues. I am just wondering if there is a way to see what variable is associated with the query. I would suggest, you should use either of below for troubleshooting blocking problems : You can use beta_lockinfo by Erland Sommarskog beta_lockinfo is a stored ...


-1

The question is unanswerable as asked since stored procedures do not produce result sets. Only (certain) statements produce result sets. Executing procedures may produce none, one or more results sets depending on the statements contained and the branches executed. For statements the row count will be tracked in sys.dm_exec_query_stats. Result sets have no ...


4

Adding explanation and a fiddle: http://sqlfiddle.com/#!6/c92b2/5. The query bellow: 1. uses a subquery to select the min and max versions by date (min and max are applied to integers to guarantee that for instance 6 < 16) 2. Then Selects the year (to group later), Date (to order) and the min - max versions SELECT LEFT(UPG_DATE, 4) AS Year , ...


18

This is a bit messy to get the final result because you have multiple SCHEMA_VER for each date. Before I demonstrate how to do this with dynamic SQL, I'll first show how to do it with static code to get the logic correct. In order to get the final result you can utilize both pivot and unpivot. But first, I'd change your original query to use the following: ...


9

Should be something like this: DELETE A FROM table_example1 AS A INNER JOIN table_example2 AS B ON A.COLUMN1 =B.COLUMN1 AND A.COLUMN2 = B.COLUMN2 WHERE COLUMN_DATETIME > @Period; Alternatively: DELETE FROM A FROM dbo.table_example1 AS A WHERE EXISTS ( SELECT * FROM dbo.table_example2 AS B WHERE B.COLUMN1 = ...


4

No, absolutely impossible. Each sqlcmd invocation is a different session and transaction cannot span sessions. Your use of a transaction on the script that invokes the sqlcmd is completely useless, it has nothing to do with the scripts being run.


0

I think something with CROSS APPLY would work well for you. Look at this example: DECLARE @tbl_Supplier TABLE (ID INT PRIMARY KEY, SupplierName VARCHAR(25),Weighting FLOAT) INSERT INTO @tbl_Supplier VALUES (1,'Supplier1',.5),(2,'Supplier2',.25 ),(3,'Supplier3',.25); DECLARE @Items TABLE(SupplierID INT,ItemName VARCHAR(25)) INSERT INTO @Items VALUES ...


3

This sounds like dynamic SQL where you're constructing a query from other subqueries... The thing is that subqueries have slightly different rules. A subquery can't contain a CTE, nor can it be ordered without a TOP/OFFSET/FOR XML. So what you need to do is have a CTE section at the start of your main query, and add your CTE to that. You don't have an ...


2

After finding out exactly what the data was I had to modify the code given from Aaron Bertrand to handle "over punch dibol ascii" DECLARE @x TABLE(col VARCHAR(15)); INSERT @x(col) VALUES ('00000000014545p'),('00000000012645v'), ('00000000012345w'),('00000000012845x'), ('000000004123450'),('000000004512345'); WITH x AS ( SELECT col, switch = ...


2

When running the execution plan, I noticed that each one of the 5 or so queries that are parsing the filter data are costing about 12% which is over 60% of the query just to determine the data we are going to be filtering by. The query costs are based on estimates even in the actual execution plans. They do not tell you how efficient the query ...


3

You should really consider fixing this data before it gets into your tables, because working around bad data is cumbersome. DECLARE @x TABLE(col VARCHAR(15)); INSERT @x(col) VALUES ('00000000014545p'),('00000000012645n'), ('00000000012345p'),('000000004512345'); ;WITH x AS ( SELECT col, switch = CASE WHEN RIGHT(col,1) NOT LIKE '[0-9]' THEN 1 END FROM ...


5

If you just want to Truncate and reload data, then faffing around with indexes is not necessarily useful. If you're inserting data in Clustered Index order i.e. in CONCEPT_CD ASC order, then there's no real advantage to dropping the Clustered Index. It'll be far more pain rebuilding it at the end on 3 billion rows than it would inserting the data in your ...


6

The NTILE function breaks an input set down into N equal sized groups. To determine how many rows belong in each group, SQL Server must first determine the total number of rows in the input set. If the NTILE function includes a PARTITION BY clause, SQL Server must compute the number of rows in each partition separately. Once we know the number of rows ...


0

An identity field is meant to be a surrogate key field. The fact that it is the PK assures uniqueness, not necessarily the identity itself. No meaning should ever be assigned the value of a surrogate key. That means you can assume nothing about the associated row, not even the sequence when it was inserted into the table. That is, you should not assume id ...


1

If you want to be able to view the exact backup history for longer time than those four weeks that you keep the backup files on disk, make sure that you set the cleanup task not to delete the backup history from the msdb database, you can then run the backup report from SSMS (right click database, select reports, standard reports, backup and restore events) ...


7

An alternative solution that avoids the IN and the grouping in Aaron's answer: DECLARE @SelectedLanguageId integer = 48; SELECT SC.Id, SC.StringKeyId, Result = CASE -- No localization available WHEN LST.StringTranslationId IS NULL THEN SK.Name ELSE ( -- ...


6

This will not be fast (this took over a minute to produce 0 results in my local copy of AdventureWorks). DECLARE @SpecificWord NVARCHAR(32); SET @SpecificWord = N'donut'; DECLARE @sql NVARCHAR(MAX); SET @sql = N'SELECT NULL,NULL WHERE 1 = 0'; SET @SpecificWord = N'%' + @SpecificWord + N'%'; SELECT @sql += N' UNION ALL SELECT ''' + QUOTENAME(s.name) + ...


3

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan. If you are ...


2

Rather than calculate the value in code, consider a computed column instead. ALTER TABLE dbo.Customers DROP COLUMN Id; ALTER TABLE dbo.Customers ADD Id AS (CASE CustomerType WHEN 1 THEN N'P' ELSE N'x' END + CAST((SequenceId + 1000000) AS nvarchar(10))); Below is an example of the insert proc using this computed column: CREATE PROCEDURE ...


3

You never set the SequenceId to anything. It is not possible to set the value of a column during an insert using an expression that references the value of an IDENTITY column on that same table that will be set upon that same insert operation. If you think about it this makes sense because the row doesn't exist until you insert it, so there is no IDENTITY ...


1

SequenceID is invalid,since you are not specifying from where to get the value.I suggest,create one more temp variable and add ,some thing like below declare @seqid int select @seqid =next value for from your sequence then in place of sequence ,keep this variable



Top 50 recent answers are included