Tag Info

New answers tagged

0

There are two ways of doing this: One based on the name of the day of the week, the other based on the weekday number. An example of using the name of the week day (in English, adapt to your local language) SELECT ID, Name, Salary, Date FROM <table_name> WHERE DATENAME(WEEKDAY, Date) IN ('Saturday', 'Sunday'); Alternatively, you can use ...


3

Use the DATENAME() function and specify the datepart as weekday. select ID, Name, Salary, Date from dbo.yourTable where datename(weekday, Date) in ('Saturday', 'Sunday'); As Aaron pointed out, this relies on the language being set to English. Likewise, you could use the DATEPART() function with weekday and test for Saturday and Sunday values.


6

I've always been a fan of a dynamic sql approach for this type of problem. I find it provides the optimal balance between complexity versus quality query plan. In the following code, I define a base query which does whatever it would need to do and then only add in the filters if the provided parameter is not null. CREATE PROCEDURE [dbo].[GetData] ( ...


6

SELECT * FROM Table1 WHERE Table1.URL LIKE '%' + @Parameter1 + '%' AND Table1.ID = @Parameter2 AND ( @Parameter3 is null or Table1.ID2 = @Parameter3 ); Take a look at the above example. If you change your AND clause to a nested OR clause specifying your initial expression as well as @Parameter3 is null. That will then demand that the nested ...


0

You can automate all of below using SQL Agent. There are many alternatives that you can use : SQLCMD ==> sqlcmd -E -S server_name -q "backup command" You can use it with dynamic sql to connect to different servers for backup and restores. PowerShell ==> There are tons of scripts found on internet that will tell you how to do it. SimpleTalk has -- Backup ...


2

Among the SET statements in your script, SET QUOTED_IDENTIFIER is special in that it is processed at parse time rather than at execute time. From SET Statements (Transact-SQL) (emphasis mine): Considerations When You Use the SET Statements All SET statements are implemented at execute or run time, except for SET FIPS_FLAGGER, SET OFFSETS, SET ...


0

If this is for SQL Server 2012 or later version, you could use the LAG analytic function to produce the required result: SELECT ID, tableName, rowCountDelta = totalRowCount - LAG(totalRowCount, 1, 0) OVER (PARTITION BY tableName ORDER BY Date), Date FROM dbo.yourTable ; The ...


2

As you are using SQL 2012, you could (should?) be using the new THROW syntax, eg BEGIN TRY RAISERROR ( 'dummy', 16, 1 ) END TRY BEGIN CATCH DECLARE @msg NVARCHAR(2048) = ( SELECT 'Message with %% ' AS MSG ); THROW 51000, @msg, 1; END CATCH Although THROW doesn't like percentage signs (%) either so you still have to escape it, presumably ...


2

Use %%: raiserror(N'This is a message with %%', 0, 1);


2

As described in the MSDN documentation CHARACTER_OCTET_LENGTH is the length in bytes, and CHARACTER_MAXIMUM_LENGTH is the length in characters. For parameters of type char or varchar they will be the same, but for parameters of type nchar or nvarchar they will be different, with OCTET-LENGTH being twice (usually if not always) the CHARACTER_LENGTH.


0

If I understand you correctly, try this: with ActionX as ( select ActionID, coalesce(LoginID,'_')) as LoginID, ActionCode from ActionTable ) select a.ActionID, a.LoginID b.ActionCode from ( select ActionID, max(LoginID) as LoginID from ActionX t group by ActionID ) a join ActionX b on b.ActionID = ...


1

In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server log for more info. Do not shrink to 160MB. Determine why did the ...


2

Start out with getting all the procedures from sys.procedures and then use EXEC sp_helptext on each to load the text of the procedures. Search for text to add, add if needed, load the updated text to a variable and execute dynamically.


0

Your code looks like a good candidate for a recursive Common Table Expression (CTE). There are lots of examples out there, but here are a couple to start with: CodeProject Technet They can be a good way to work with hierarchies like manager -> employees, or in your case, Master -> Slaves.


1

You want to increase the speed of your cursors? Wrap them in a transaction. If you are processing millions of records and don't want/need them all in one transaction, you can commit it on occasion to reduce resources. I did this with a cursor that took an hour to run (this is an extreme case) and afterword it ran in 1 1/2 minutes. I know that does not ...


0

the script I use will show you all objects (SP, Tables, Functions) for a specified Database name and all the users that have rights on them, but you can narrow the search and extract exactly what you need. DECLARE @EXEC_SCRIPT NVARCHAR(4000) DECLARE @DBNAME NVARCHAR(256) = 'DB name' SET @EXEC_SCRIPT = 'USE ['+ @DBNAME + '] select DB_NAME () ...


0

I wrote a script a while ago, that does just this. I've posted it on my blog, http://sqlsunday.com/downloads/. Remember that a Windows user can be a member of a Windows group, and in SQL Server, you can't see those memberships, so you'll have to look at Windows users and Windows groups separately, if you're doing a security audit, for instance. If you want ...


0

Off the top of my head (no MSSQL boxes up at the moment, sorry): EXECUTE AS <database user> ; SELECT * FROM sys.objects WHERE type = 'u' ; Metadata visibility means that if a database user queries sys.objects then they will only see the objects they have permissions to access or that they own. If you require the actual permissions they have on ...


1

Instead of writing your own solution, I would highly recommend to use Ola Hallengren's SQL Server Backup Solution. Refer to : Bad habits to kick : avoiding the schema prefix by Aaron Bertrand. Below should work for you (I have not tested it): CREATE SCHEMA job go CREATE PROC job.BACKUPS @dir varchar(max) AS BEGIN DECLARE @comando nvarchar(max) = ...


1

The following T-SQL will enable Database Mail in SQL Server Agent: USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1 GO EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1 GO EXEC master.dbo.xp_instance_regwrite ...


6

It makes no difference: the optimiser evaluates the query and will work out predicate order for itself, matching a suitable index and all the other good stuff it does. This is because SQL is "declarative" not "procedural": you say what you want, not how to do it. It's nicer to read though...


2

One way to accomplish this is: USE tempdb; CREATE TABLE dbo.PersonHierarchy ( PersonHierarchyID HierarchyID ); INSERT INTO dbo.PersonHierarchy VALUES ('/1/'); INSERT INTO dbo.PersonHierarchy VALUES ('/1/1/'); INSERT INTO dbo.PersonHierarchy VALUES ('/1/2/'); INSERT INTO dbo.PersonHierarchy VALUES ('/2/'); INSERT INTO dbo.PersonHierarchy VALUES ...


1

For the sake of completeness, you could use the following: DECLARE @t TABLE ( rowId INT IDENTITY PRIMARY KEY, yourString CHAR(13) ) INSERT INTO @t VALUES ( 1010000123456 ), ( 1010001234567 ), ( 1010012345678 ), ( 1010123456789 ), ( 1011234567890 ) SELECT CAST(RIGHT(yourString, LEN(yourString)-3) AS BIGINT) FROM @t; This presumes ...


3

There are other ways to do it, eg STUFF or just some simple integer maths, but these do make certain assumptions about the string, eg DECLARE @t TABLE ( rowId INT IDENTITY PRIMARY KEY, yourString CHAR(13) ) INSERT INTO @t VALUES ( 1010000123456 ), ( 1010001234567 ), ( 1010012345678 ), ( 1010123456789 ), ( 1011234567890 ) SELECT ...


2

The SUBSTRING(n, 3+PATINDEX('%[^0]%', SUBSTRING(n, 4, LEN(n))), LEN(n)) expression should work. first, it strips the first 3 characters: SUBSTRING(n, 4, LEN(n)) then it use PATINDEX() with the '%[^0]%' pattern to locate where the digits after the 0s start: PATINDEX('%[^0]%', SUBSTRING(n, 4, LEN(n))) then it uses SUBSTRING() and the previously found number ...


4

Making use of Jeff Moden's Tally-Ho! CSV splitter from here: CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values ...


1

If I understand you correctly you are looking to see who is blocking and why, not just gathering overall statistics. (Both approaches have real value, of course.) Since every event is transient, so it is not surprising that some values would not return what you expect. We have been using the approach outlined by Tony Rogerson quite a few years ago that ...


2

Example 4 has the fewest scans and reads: Example 1 SQL Server parse and compile time: CPU time = 4 ms, elapsed time = 4 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. example1 Id FirstName 1 2 Aaron 1 3 John 1 8 Aaron 1 9 John 1 14 Aaron 1 15 John 1 20 Aaron 1 ...


0

Adding the ORDER BY clause made it run almost instantaneously and I realized that it was only returning records from the first date because, duh, it was a SELECT TOP 10 query, there were more than 10 records from the first date. If I turned it into a SELECT * query I got everything in the range and if I added the ORDER BY clause I got it quickly. Thank ...


7

To start off, NULL does not mean "no value" it means "Unknown value" in SQL Server. There is a session setting called ANSI_NULLS that could make your queries behave as you would like them to, however, it's deprecated and will be forced to ON (which you don't seem to like) in a future version: http://msdn.microsoft.com/en-us/library/ms188048.aspx I get what ...


3

The NULL problem is a thorny issue with SQL. It is basically a mistake that is now burnt into all SQL software on the planet. We have to deal with it. value <> 26 or value is null is a good way to implement this logic. There are other formulations of the same semantics. If you know that value is never -1 (for example) you can say ISNULL(value, -1) ...



Top 50 recent answers are included