New answers tagged

0

In order to create auto numbers without gaps we can use QUEUE object of MS SQL Server. So the idea is that Queue supports concurrency and roll back of transactions. We use SEQUENCE object to generate continuous set of numbers and send them to QUEUE. After that we can receive them back from that Queue concurrently and if one of client's requests fails then ...


0

Use Kenneth Fisher's scripts to identify which roles have these rights. I assume once you identify the roles in question, you want to get a better idea of what they're doing. To script them out, feel free to use a script I put together, here, which will fully script out the database role definition.


3

This is bigger than a comment but something to test first and then implement : You are logshipping 800+ databases. Thats a large amount of databases that you logship every 15 mins. You should offload some databases to another server. IMHO 800 databases on a single server is a lot! We had similar problem with logshipping when we logshipped 200+ databases ...


1

I loaded all the databases being shipped into this query: DECLARE @path NVARCHAR(260); SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc' FROM sys.traces WHERE is_default = 1; SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY DatabaseName ORDER BY StartTime) INTO #blat FROM ...


2

It's really not THAT different to Service Pack patching whereby you'll need to do passive nodes first, then give cluster resource group ownership to one of the upgraded nodes, in order to finally upgrade the active node. Map out your upgrade sequence first and test it if you have the luxury of a dev environment. In place upgrades are supported and the ...


0

The answer was to create a user who would the super administrator of the database. So that we can distinguish between Windows authentication and Sql Authentication, in case it doesn't work !!! Thank you !


3

Here is a starting point. The first query will give you a list of roles with either INSERT or UPDATE permission. From there you will need to look at the permissions being at the DATABASE, SCHEMA, or OBJECT level and decide if they cover the objects you are interested in. Check the state_desc column to make sure it is GRANT (it probably will be) as DENYs ...


4

The module text is encrypted using an RC4 stream cipher. The RC4 initialization key is computed from the SHA-1 hash of: The database family GUID (from sys.database_recovery_status) Converted from uniqueidentifier to binary(16) The module's object ID (from the catalog views) Converted from integer to little-endian binary(4) The module's object sub-ID ...


6

Which of the following primary keys (the column order is different) is preferred? Like all indexing decisions, much depends on how the table will be queried. All partitioned indexes (for SQL Server 2008 and later) have the partition ID (not partitioning key value) as a hidden leading key column in each partitioned index, so the effective competing ...


0

There is a Chrome plugin that fixes a common bug for viewing SSRS reports in Chrome. I had the same issue and this was a fix for me.


0

it can also be a problem with access rights. make sure the user is in the sysadmin role.


0

Making a guess with no information but is the name in sys.objects INFORMATION_SCHEMA.TABLE_CONSTRAINTS? Try running this: SELECT object_schema_name(object_id) AS SchemaName, name FROM sys.all_objects WHERE name like '%TABLE_CONSTRAINTS' I'm guessing you get something like this: SchemaName Name ------------------ ...


2

You should use len() instead of datalength() to get the number of characters in Value. They are different because Value is a double byte string (nvarchar). CREATE TABLE #tmp (Value NVARCHAR(255)) INSERT INTO #tmp (Value) VALUES ('L1 [ABC]'),(' L2 [DEFGH]'),(' L3 [E]') SELECT Value + SPACE(50 - DATALENGTH(Value)) + 'Description text' FROM #tmp ...


0

Max Vernon: you were right, Courier did the trick. I added style:"font-family: Courier New" to the <selected> tag.


0

Its never recommended to delete rows from system defined table. Please follow below steps to remove any orphan logreader agent. Create a dummy transactional replication ‘Dummy1’. Add database(select publisher_db from [distribution].[dbo].[MSlogreader_agents]) as publisher and another database(it would be good if you remember the old subscriber) as ...


-1

Along with all these additional points that are mentioned in other answers please also install latest service pack from microsoft site. To download latest service pack search on Google/Bing "How to obtain latest service pack for Microsoft SQL Server (your version)"


1

I had an issue where an old server needed to read data from a new server for backwards compatibility. The old server cannot be upgraded as it contains a legacy system that is being deprecated, but still used. I needed SQL Server 2012, downloaded it and it refused to run on Server 2003 SP2. I used Orca and removed the installrequirement, and it installed ...


1

Try running this, when in the Database - it will give you the permission levels for SERVER, DATABASE, OBJECTs in 3 separate result windows - you can then consider the Group membership of the user vs the permissions levels (copy/paste results into Excel for easier filtering, etc. (PS. not my code, but very useful - I would reference the original ...


2

I would check if the users are part of the db_denydatareader role, check for deny permissions on the tables schema, and also for a deny permission on the public role. I would suspect these are the most likely culprits.


3

Here is a solution that uses CROSS APPLY to find the value for the latest quarter that ends on or before the daily date. If your table is indexed by (ID, Date), this query will be quite efficient with a one-row seek to look up the quarterly value for each daily date. This solution also does not require a calendar table and makes no assumption about the ...


2

Since you have defined quarters using the traditional calendar definition, you don't need to store the "end" of a quarter anywhere, particularly once for every single day in a ~90-day period. (And this could be dangerous if the underlying data is ever expanded to include time, as you may lose all data from the last day of the quarter except that stamped at ...


1

A date table would be very useful in constructing your query. Look into how to create a data table. Here is a quick video on that, https://www.brentozar.com/archive/2014/12/simply-must-date-table-video/ This query uses a very limited date table to list the Quarter start and end dates for the date you have listed. A real date table would have many more dates ...


1

Import-Module SQLPS $ErrorActionPreference = "Stop" Set-StrictMode -Version Latest $serverName = "VM-WIN81" $instanceName = "SQL2014" # or DEFAULT $jobName = "syspolicy_purge_history" $jobSuffix = "_Old" $script = Get-ChildItem SQLSERVER:\SQL\$serverName\$instanceName\JobServer\Jobs | Where { $_.Name -eq $jobName } | %{ $_.Script() } $script = ...


1

It would be a large script as a Job consists of data entries in multiple tables/views: msdb.dbo.sysjobs_view msdb.dbo.sysjobsteps msdb.dbo.sysjobschedules -- you probably dont need these as you are copying to the same server msdb.dbo.sysschedules msdb.dbo.syscategories sys.server_principals msdb.dbo.sysOperators Create Jobs using following SPs: ...


10

I want to know how the query execution works here The general execution model is a pipeline, where each iterator returns a row at a time. Execution starts at the root iterator (on the far left, labelled SELECT in your example). After initialization, the root iterator requests a row from its immediate child, and so on down the chain until an iterator ...


0

Assuming your queries are against regular Access tables, you should be able to replace the Access tables with Linked Tables to your SQL Server. The Linked SQL tables have to have the same names and structure to in order for this to work. I don't have the 2008 version of Access so I can't give exact instructions but look in the menus for something like ...


0

Assuming you missed a column call LastLoginTime in User table or some other Log table, here goes the query. USE ABC; IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'Not_loggedinUsers' AND type ='U') CREATE TABLE Not_loggedinUsers ( Email NVARCHAR(100), NoticeCount INT ); SELECT UserEmail Email INTO #oldUsers FROM User GROUP BY UserEmail ...


0

As others have mentioned, a trigger would work fine. You do not need to change the sproc, just put a INSTEAD OF trigger on the table. The trigger will look to see if the RangeFrom value being inserted is between any of the ranges already in the same and then do the same for the RangeTo value. If it passes that logic, it will insert. If it fails, it will ...


3

select count(*) from ( select RangeTo, LEAD(RangeFrom, 1, 0) OVER (ORDER BY RangeTo) AS LeadRangFrom from table ) ss where @RangeFrom > RangeTo and ( @RangeTo < LeadRangFrom or LeadRangFrom is null) If count(*) is one then you can insert Is that the question you are asking?


0

You could use an insert trigger to check the inserted values against the table data, and fail if the range already exists. But since you are using a Stored Proc, you can check in the proc. Something like this: CREATE PROCEDURE blah (@rangeFrom int, @rangeTo int) AS BEGIN IF EXISTS( SELECT 1 FROM Ranges WHERE @rangeFrom BETWEEN RangeFrom AND RangeTo ...


3

Aaron Bertrand is right. The documentation for the OUTPUT clause is not explicit in saying that a table cannot be created with its use. I've run into the same issue. What I ended up doing was using two statements: --This creates the table with the needed columns without inserting any data. SELECT TOP 0 [myColumn1], [myColumn2], ...etc INTO [myNewTable] FROM ...


0

In SQL what's how many CPU's do you have for that server? What's your MAXDOP and Cost threshold for parallelism'? One thing you can do to check to see if your cost threshold is too low is run the same query with OPTION (MAXDOP 1) SELECT cc.ReportID, cc.ReportDate, cc.UnitID, cc.Callsign, cc.SpeciesCode, cc.ComName, ...


2

Their cannot be a comparison drawn as Snapshot is "photo copy" of data file. The difference lies in amount of activity both does and consistency of product arising out of both the operations. Full backup "Is Always" more reliable than snapshot backup Full backup includes all committed and uncommitted transaction when full backup has finished. Read more ...


4

Mostly because a snapshot isn't really a backup. As explained in How database snapshots work The snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of ...


2

I was a little bit surprised that the Vladimir's cursor solution was so slow, so I also tried to optimize that version. I did confirm that using a cursor was very slow for me as well. However, at the cost of using undocumented functionality in SQL Server by appending to a variable while processing a rowset, I was able to create a simplified version of this ...


1

Discussion will follow the code. declare @Helper table( rn tinyint, dowInt tinyint, dowChar char(3)); insert @Helper values ( 1,1,'Sun'), ( 2,2,'Mon'), ( 3,3,'Tue'), ( 4,4,'Wed'), ( 5,5,'Thu'), ( 6,6,'Fri'), ( 7,7,'Sat'), ( 8,1,'Sun'), ( 9,2,'Mon'), (10,3,'Tue'), ...


3

Try naming that query, and then using the column with PARTITION BY in an OVER clause. WITH MyCTE AS ( SELECT S.Product_Name, SPD.*, Sp.Delivery_Status, CONVERT(decimal(10, 2), (SPD.Quantity * SPD.UnitPrice)) Amount, CONVERT(int, (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * 100) [Cost%], CAST((SPD.Quantity * ...



Top 50 recent answers are included