Tag Info

New answers tagged

0

The answer to both of your questions is really the same. You are using a script generation tool. It does what it's told to do. Typically the second command is only needed if you are doing a WITH NOCHECK on the first one. However it's included because the settings for the script generation component of SSMS told it to include it. Here is a post I did on ...


0

ANSI_NULL_DEFAULT { ON | OFF } : When you dont specify ANSI_NULL_DEFAULT in CREATE TABLE or ALTER TABLE statements, this database option will determine the default value, NULL or NOT NULL for a column or CLR user-defined type. When SET to ON, the DEFAULT value is NULL. When SET to OFF, the DEFAULT value is NOT NULL. Connection-level settings that are ...


2

The following is basically your algorithm but implemented as a single statement: WITH ranked_and_counted AS ( SELECT ID, r = ROW_NUMBER() OVER (ORDER BY ID ASC), c = COUNT(*) OVER () FROM dbo.tblLookups ), ranked_and_counted_and_r0 AS ( SELECT *, r0 = MAX(CASE ID WHEN @ID THEN r END) OVER () FROM ranked_and_counted ) ...


4

Quite convoluted but should work and return the 2 rows with values below the @repid, the row with @repid and the 2 rows with values above the @repid (assuming that id is a unique key). If there are less than 2 values below or above, the query will get more from the other side. In total a maximum of 5 rows will be returned. Note: the ORDER BY is needed. ...


0

Instead of creating a trigger I would suggest you to create a new column in the purchase table which will be a computed column. Before creating it, create a scalar function which will accept productname or productid and returns int. Inside it, write a query to find the total from sales for that particular row (by passing productname or id) and subtract it ...


0

I don't think that a trigger is the right place for your application business logic. A stored procedure to update the corresponding rows in the stock table would be much more sensible in my opinion. If you want to prevent users from modifying the sales and purchase tables directly without updating the stock table, don't grant permissions on those objects ...


0

Deleting data is easy from the syntax point of view, but that's not all there is. DELETE FROM myTable WHERE someDate < deletionDate might give you a few nasty suprises. Constraints might block you from doing a delete. A textbook example is a foreign key constraint between customer and order tables. Deleting a customer is not allowed as long as there are ...


3

In SQL Server, a stored procedure's argument can be either a string literal or a variable. It certainly cannot be a string expression, like in your attempt. To resolve your issue, you could declare a variable, assign your expression to it and then pass the variable to the sp_send_dbmail stored procedure: DECLARE @myfilename nvarchar(255); SET @myfilename = ...


3

Declare a variable first - but use format 112 for this. Like this: DECLARE @query_attachment_filename VARCHAR(100) = 'pca-test-' + CONVERT(VARCHAR(10), GETDATE(), 112) + '.csv'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'support', ... @attach_query_result_as_file = 1, @query_attachment_filename = @query_attachment_filename, @query_result_header =0, ...


1

Here's a different approach that doesn't rely on loops or additional tables, however it does touch the source table twice. (You'll have to replace dbo.aTable with the name of your actual table, and add where clauses where commented below if you want to filter.) DECLARE @i INT = 30; -- interval in minutes DECLARE @ft SMALLDATETIME, @lt SMALLDATETIME; SELECT ...


5

EXEC msdb.sys.sp_executesql N'ALTER ROLE ...';


0

Unfortunately, the starting point of your data makes this rather challenging. If it must be done at runtime, this is one possible solution: -- Mimicing existing data DECLARE @TableName TABLE ([User] VARCHAR(6), [Elapsed Time] VARCHAR(5), [Start Time] DATETIME, [End Time] DATETIME) INSERT INTO @TableName VALUES ('User01', '01:05', '07-02-2015 08:45', ...


0

Fun problem! As Aaron mentions in his comment, a few things are ambiguous. But I put together a script that could be used as a good starting point. The script first identifies all possible 30 minute intervals, then matches user "events" to all matching intervals and does some date math to compute your elapsed times. The script also attempts to handle a ...


0

Another example using cross apply. The idea is to transpose a0, a1, ..., to rows and then check where max and min equals: select d.id from dbo.data as d cross apply ( values (d.a0),(d.a1),(d.a2),(d.a3),(d.a4) ) as x(a) group by d.id having min(a) = max(a);


12

The performance will be the same. The optimizer will recognize this and create the same plan. On the other hand I wouldn't say they are equal. The first form is far more readable and generally expected. For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query. You should be able to ...


9

They are semantically identical and the optimiser should have no trouble recognising this fact and generating identical plans. I tend to put conditions referencing both tables in the ON and conditions referencing just one table in the WHERE. For OUTER JOINS moving the conditions can affect the semantics however.


2

Here's one way: SELECT MAX(A), MAX(B), MAX(C), MAX(D) FROM ( SELECT SUM(A) AS A, SUM(B) AS B, NULL AS C, NULL AS D FROM X UNION ALL SELECT NULL AS A, NULL AS B, SUM(C) AS C, SUM(D) AS D FROM Y ) AS T You may have to cast NULL to whatever type A, B, C and D have


5

Two subqueries? SELECT Q1.A, Q1.B, Q2.C, Q2.D FROM (SELECT SUM(A) A, SUM(B) B FROM @X) Q1(A, B) CROSS APPLY (SELECT SUM(C) C, SUM(D) D FROM @Y) Q2(C, D);


0

The idea of such code is not to repeat "the same" statement twice when nullable variable passed. Another option in your case is to have 2 different statements , IF @term1 IS NULL SELECT .... -- note - no "WHERE" ELSE SELECT .... WHERE ua.FirstName LIKE @term1 OR ua.SecondName LIKE @term1 Approach with just one statement may be still valid in ...


3

You can use sqlmigration - Start-SqlServerMigration.ps1 This script provides the ability to migrate databases using detach/copy/attach or backup/restore. SQL Server logins, including passwords, SID and database/server roles can also be migrated. In addition, job server objects can be migrated and server configuration settings can be exported or ...


4

SQL Server does NOT have guaranteed short circuiting in that scenario. The point of this construct is so that businessLogicCondition is only evaluated as part of the WHERE clause in the case where @term1 is populated. It may still be evaluated as part of query processing, because you can only control order of evaluation in a few special cases (no pun ...


2

If the NULL value is explicitly specified, you'll need to use a DML trigger to update NULL values to 0 after insertion. This can't be done with a default constraint.


5

Using a subselect to get the missing msg: select a.dbname , coalesce(b.msgval, (select msgval from msg c where dbname = 'alldbs')) as msgval from list a left join msg b on a.dbname = b.dbname


7

Another way (not as pretty looking to me after seeing @ypercube's suggestion, but still...): SELECT l.dbname, x.msgval FROM dbo.list AS l CROSS APPLY ( SELECT TOP (1) m.msgval FROM dbo.msg AS m WHERE m.dbname IN (l.dbname, 'alldbs') ORDER BY CASE m.dbname WHEN 'alldbs' THEN 2 ELSE 1 END ASC ...


8

Using another left join: select a.dbname , coalesce(b.msgval, d.msgval) as msgval from list a left join msg b on a.dbname = b.dbname left join msg d on b.dbname is null and d.dbname = 'alldbs' ; or a cross join (this requires an 'alldbs' row to be present, otherwise it will return 0 results.): select a.dbname ...


2

I assume you are talking about the same query, executing with different indexes. You can't just arbitrarily compare 2 totally different queries that do 2 different things. As Aaron stated in the comments, I can compare my 2001 VW to a 2015 Camaro and there will be no correlation, the Camaro may go faster but that's because it has a bigger engine. ...


1

You need to create the type inside the database where you will declare it - data types can not be reached across databases or servers. This is no different from creating a local variable using an alias type - it has to exist in the context of the current database (or your code has to execute in the context of the database where the type exists, e.g. EXEC ...


2

CREATE TYPE [tvf_id] AS TABLE ( [id] [int] NULL ,[OrdCol] [INT] NOT NULL ) GO CREATE PROCEDURE [sp_Test] @pID tvf_id READONLY as begin set nocount on SELECT a.* FROM datatable a inner join @pID b on a.id = b.id ORDER BY b.OrdCol ASC end GO -------------------------------------------------------- DECLARE @data tvf_id ...


4

DECLARE @Name varchar(100) = 'Doe, Jane' SELECT SUBSTRING(@Name,CHARINDEX(',',@Name)+1,LEN(@Name) - CHARINDEX(',',@Name) )+ ' ' + SUBSTRING(@Name,1,CHARINDEX(',',@Name)-1)


8

Given the sample data: CREATE TABLE dbo.Data ( ID integer PRIMARY KEY, A0 character(1) NULL, A1 character(1) NULL, A2 character(1) NULL, A3 character(1) NULL, A4 character(1) NULL ); INSERT dbo.Data (ID, A0, A1, A2, A3, A4) VALUES (14, 'A', 'B', 'A', 'C', 'A'), (15, 'A', 'A', 'A', 'A', 'A'); An alternative way to ...


4

I can suggest another solution. You can use COMPUTED COLUMN. Of course this is if you can modify table structure and if you will do that comparison very often. Inside the COMPUTED COLUMN you can calculate if all columns that you need to compare contains the same data with boolean value. After that you only compare that value in your queries. Also if you ...


10

I'm not familiar with SQL Server's brand of SQL, but it must be something like: SELECT ID FROM My_Table WHERE (A0 = A1) AND (A1 = A2) AND (A2 = A3) AND (A3 = A4);


4

I often generate T-SQL like this: select 'ALTER DATABASE ' + quotename(name) + ' MODIFY FILE (...)' from sys.databases where databaseIsMisconfigured = 1 Select the entire column, past it into a new window and F5.


1

you could script out all settings. make the changes you want in the the GUI but instead of click OK to apply the change, click script at the top of the window and the SQL for your changes will be scripted out. You will now just have to change database name/file names/ sizes/ etc for each database. USE [master] GO ALTER DATABASE [test] MODIFY FILE ( NAME = ...


0

After hunting around a lot more, I found a few articles discussing security and granting permissions that sent me on a wild goose chase and ultimately made no difference to the outcome. The script continued to work outside of sp_send_dbmail, but would not work with it. I don't recall how, but I have subsequently discovered that stored procedures the ...


0

but statements AFTER this will Not be using the new db assignment - which is what I want You can use 3 part naming dbname.schema.objectName. Below is an example to get you started. You can modify as per your needs : SET NOCOUNT ON DECLARE @dbname VARCHAR(max) ,@SQLText VARCHAR(8000) SELECT @dbname = min(NAME) FROM master..sysdatabases WHERE ...


3

sp_executesql creates a new execution context which ceases to exist when the executed statements return. The USE only has effect within the sp_executesql. Try this SET @SQL02 = 'USE ['+ convert(nvarchar(50),@dbName) +']; SELECT DB_NAME();' and you'll get what you're after.


2

As the discussion in the comments revealed, an fcontainer can be related to many products, due to the 1-to-many relationship between FContainer and Stämplingar. Try this query, which will choose only one product per fcontainer. The missing fcontainers are due to the INNER join. Changing it to LEFT join will show all fcontainers, even those not related to ...


1

It is not neccesary to modify Jeff Moden's function. I have devised a little bit sophisticated way of achieving the result just using the original function. First Here it is the original Moden's function: (Source: http://www.sqlservercentral.com/articles/Tally+Table/72993/) CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters ...


0

My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the name of the table and its column. use AdventureWorks2012; declare @ConstraintName varchar(max) set @ConstraintName = 'CK_Product_ProductLine' --<--- Change here with your constraint Name if exists ( select 1 from sys.objects ...


0

This is probably not the best solutuion. But since your proposal was a bit particular I decided to create an ad-hoc solution. It was fun. Can be tested here: http://sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/623 declare @string varchar(max) set @string = '!1;100;10;200;0;500;2;1000;30!2;100;3;500;1;2000;5' declare @t1 table (row varchar(max)) ...


2

Your friend is cmdline tools SQLCMD in SSMS : :connect sql1\instance1 BACKUP DATABASE DATABASENAME TO DISK ='BACKUP SERVER' :connect sql2\instance2 BACKUP DATABASE DATABASENAME TO DISK ='BACKUP SERVER' PowerShell


4

EXEC SQL2.master.sys.sp_executesql N'BACKUP DATABASE dbname TO DISK = N''D:\path_on_remote_server\...'';';


3

You can use SQLCMD Mode in SSMS with :CONNECT and servername\instance to connect to particular server and run your command. Navigate to SQLCOMD Mode:Open New Query > SQLCMD Mode just type in query in following format if you have multiple servers to connect and take backup. :CONNECT SQL2 BACKUP DATABASE DATABASENAME TO DISK ='C:\' :CONNECT SQL3 BACKUP ...


0

Jeff Moden created a splitter function over on www.SQLServerCentral.com called DelimitedSplit8K. It takes two parameters @pString (the string to split) & @pDelimiter (the delimiter, in your case ;). However in your case you aren't doing a straight up split, so it will have to be modified a little bit. In fact we have to do two different types of ...



Top 50 recent answers are included