Tag Info

New answers tagged

0

Using select * from vManyUnions where p = 1 will already avoid having to access the other tables, because of the contradiction optimisation. If you look at the actual execution plan, you should see that only one of the external tables is actually accessed. Any others would have the NumberOfExecutions property set to zero. You can see me demonstrate this in ...


0

You should use ID as bigint instead of numeric(18,0) if state_id is more "selective" then region_id, you should make index on (state_id, region_id).


-1

You can setup the sql profiler and start recording all your queries into a table. Filter the table for the host and see which host, and possibly which users are doing this.


5

Queries aren't logged by SQL Server, unless they meet some criteria that puts them into the default trace or system_health Extended Events session (some details on what they collect here). If you can control data access via stored procedures, you can easily add your own logging to those. But that doesn't sound like the case. Otherwise you will have to rely ...


0

So, after a bit of thought, I realized that I can just treat my User Information as another piece of data. So I can add to each table a "ChangedBy" and a "ChangedWhen" column. Whenever my service updates a record, it will update the ChangedBy and ChangedWhen. Then in the Change Data Capture history, the changes include needed data.


2

What you need to do is use the LEAD windowing function to look at one row down, two rows, three rows, etc for however long your pattern is. Example: LEAD(gradeissued,1) over (partition by studentid order by whenissued) as one_row. Put that in a CTE or subquery and then in the next query do a where criteria on your pattern (one_row = 'D' and two_row = 'C' and ...


5

One way of doing it would be to use LEAD. WITH T AS ( SELECT StudentID, GradeIssued AS g1, LEAD(GradeIssued, 1) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g2, LEAD(GradeIssued, 2) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g3, LEAD(GradeIssued, 3) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g4 ...


0

No that is not possible. The commit mode (sync/async) is between primary and secondary logical roles, and not between secondary and secondary (which is why are you aiming for). I'm not sure what you'd even gain from that. All your logical design would be adding (even if it was possible) would be a second hop of log blocks for SQL-B2. You would want your ...


0

I would go with detailed if it finishes in a timely basis. I run a check index statistics script every night and if it can rebuild within the time allocated for low activity I would go with detailed because the resulting statistics seem to produce better query plans.


3

Using the newly added LAG() and LEAD() functions makes it look easy: WITH a AS ( SELECT PersonID, FloorNum, EnterTime, PrevFloorNum = LAG(FloorNum) OVER (PARTITION BY PersonID ORDER BY EnterTime) FROM RoomAssignments ) SELECT PersonID, FloorNum, FromTime = EnterTime, ToTime = ...


0

I have an idea that MAY cause it: The tables you are showing us are not SELECT * FROM ... are they? I think that in dtr_tbl, the logdate has a time part that is not 00:00:00 and this causes that in the WHERE condition, log_date BETWEEN CAST('03/13/2015' AS datetime) AND CAST('03/23/2015' AS datetime) leads to ie 2015-03-23 8:00:05 between ...


4

So it's an indexed view - SQL Server can automatically choose to use an indexed view if the base table is referenced and the indexed view can (better) satisfy the query. From MSDN (emphasis mine): A query does not have to explicitly reference an indexed view in the FROM clause for the query optimizer to use the indexed view. If the query contains ...


4

The actual syntax for the command shows that there is no such thing as FOR UPDATE, UPDATE - or rather that it just doesn't make any sense: CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ...


5

John is right that the updated syntax error message you get: Msg 1034, Level 15, State 1, Procedure trigger Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration. This is absolutely because the trigger is specified as FOR UPDATE, UPDATE. This syntax was allowed in older compatibility levels but not in modern ones. ...


1

In the folder %APPDATA%\Microsoft\Microsoft SQL Server\110\Tools\Shell you should find a file named RegSrvr.xml. That's the file where Management Studio stores local registered servers. If the file is missing or corrupt, SSMS won't display anything. In this case, you can create a new file and paste this: <?xml version="1.0"?> <model ...


0

So, if windows sets the low memory resource notification, when the Resource Monitor discovers it, is there a way for the DBA to know it too? An event notification? An alert? Something else? Ideally, I'd like to send an email when it happens. This is normal and happens quite a few times on a busy SQL Server OLTP system. I am not sure how to put alert ...


0

It was the same case with me. My SQL Server Management Studio was open for couple of days. I restarted it and it got solved.


1

This is called external memory pressure assuming that you set the max memory setting to a reasonable value. Generally it happens when processes other than SQL server (ssis, monitoring agents, iis etc.) consumes memory. You can identify if a memory pressure is internal ör external from ring buffers which is explained in Jonathan's post An easier way of ...


1

It depends a little on what storage system you have behind the scenes. You see, read and write IO operations are very different. On a RAID 5 to perform a single block write you must: Read the update block. Read the parity block. Write the new block. Write the new parity block. So for a single random write, RAID 5 needs 4 operations per write. This is ...


5

There are two types of activity to consider here: reads and writes. As you correctly point out, MDF/NDF writes are done through the checkpoint process, and users shouldn't have their transaction time affected. Reads happen when the data that is needed is not yet in RAM (in the buffer cache). So ideally, user activity shouldn't be affected often. But when ...


3

Offline is offline; the database can't be "doing anything" unless there is application code somewhere that changes it from offline to online and then back again. As to whether you can drop it, well, we have no idea. It may be cruft, but it may be important. (Think about me asking if this cable I found in my basement goes to anything important - I wasn't ...


3

You can set the database to SINGLE_USER so that no other session can come in while you are performing your changes. Also, sp_addrolemember has been deprecated; you should be using ALTER ROLE instead. Finally, please get in the habit of using statement terminators. USE master; GO ALTER DATABASE SQL SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE SQL ...


0

The problem with finding saved creds in SQL Server is that it's usually stored client side. You will have to capture people trying to connect to the server, but it's interesting that it only occurs You could run a extended event or a trace and capture failed logins by that user, that will tell you if it's MS SQL Server that is locking him out. Then you ...


0

Referring https://msdn.microsoft.com/en-us/library/ms188414.aspx Update the stats - sysviews are out of sync. EDIT: [thanks for the advice, John] It seems the system views are not always accurate, and sp_spaceused is notorious for this. A form of caching is applied to non-critical, but heavy process data collection functions - like measuring partitions ...


1

I believe that a truly passive server in an active/passive arrangement does not need its own licensing, but I recommend asking an official licensing representative, especially if you already have SQL Server licenses as you might have purchased them under specific conditions, as that is the only way to be properly sure. does one need to have a fail-over ...


0

As long as your 2nd server is solely used as a standby and you do not use it for reporting or any other querying then a single license will cover you for that 2nd machine. Any usage beyond this would require a 2nd license. The secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly ...


1

Replace 'drop database [myDb_snapshot]' with: declare @cmd nvarchar(max); set @cmd = 'drop database [myDb_snapshot]'; exec (@cmd); The entire SQL statement is evaluated for correctness prior to running it. Since the snapshot doesn't exist, it won't run. Dynamic SQL removes that limitation.


0

If I understand the situation correctly, you might be able to use an expression to create a dynamic connection string in the data source for the linked report. I've done this in the past where I create a parameter called @Server and then set the connection string of the data source to the following expression: ="Data Source=" & Parameters!Server.Value ...


0

This is a known issue with SQL Server 2012. It used to work in previous versions. The Microsoft Connect item can be found here: https://connect.microsoft.com/SQLServer/feedback/details/769502/permissions-not-replicating-for-certain-object-types-when-using-a-sql-server-2012-distributor We first noticed it on the MSDN Forums here: ...


3

As your administrator I'd like to confirm that the problem has been caused by firewall restriction. SQL port is opened so you can complete linked server setup. Cheers, mate


1

how to do this for a mixed environment of 2012 and 2014. Doing HA in a mixed environment is a recipe for disaster because during disaster once you failover from lower (2012) to higher (2014) version, you cannot failback. Since you are using Enterprise edition of SQL Server, you can leverage AlwaysON technology wherein you can configure writes occurring ...


1

Managed to get it to work, below is the answer. Thanks to everyone that replied. ;with cte as( select [CAB Part No.] ,[RFT/Scrap/Rework] ,[Process No.] ,[Process] ,[Date & Time] ,[Quantity] ,cast([Date & Time] as date) UtcDay ,DATEPART(hour, [Date & Time]) UtcHour from [SFDLOG] where ...


0

Querying the linked server seems to get it to reconnect. Calling sp_testlinkedserver doesn't always work, but a select statement will. Sometimes there is a two minute lag from the start of the downed server to the next successful run of the job. In any case, it's simply a matter of waiting.


0

We had a similar problem. We build a VM with 1 socket and 32 cores. Sql would only recognize 8 CPUs. We switch it to 8 sockets and 4 cores the issue went away. We where running enterprise editon sql 2008r2 on windows 2008r2.


1

It's converting the integer to a datetime- the column is a datetime. You're seeing the datetime conversion of the results of the DATEDIFF function. Implicit conversion from integer to datetime is in days beginning with 1/1/1900 as 0. SELECT CAST(1 AS DATETIME) Returns 1900-01-02 00:00:00.000 If you want to store the integer result in the column, ...


0

This document from Microsoft lists a known issue with .net 3.5 erroring out at times. While not specifically identifying your issue in DNS --It may be the cause and is probably worth reading: https://msdn.microsoft.com/en-us/library/hh213080.aspx#MultiSubnetFailover


2

Oracle Golden Gate has always supported replication between Oracle and SQL Server. For a project I recently confirmed that Golden Gate for Oracle 10 is still available if you push a bit. If you are using an Oracle version that is out of support then you need to update for a number of reasons. Synchronization with Azure is detailed here.


0

For alwayson availability groups, you can't do that. After a failover SQL server will revert these settings to the current primary node. So it won't work.


4

Do you mean IsOk = 1 if the other three bit columns all = 1 else IsOk = 0? If so: - CREATE TABLE #Employee ( [empNumb] [INT] identity(1,1) PRIMARY KEY, [IsOk] AS [HasValidHours] & [NoDemerits] & [NoAccidents], [HasValidHours] [BIT] NOT NULL, [NoDemerits] [BIT] NOT NULL, [NoAccidents] [BIT] NOT NULL ); See & (Bitwise AND) ...


2

The expression you're looking for is (regardless of whether you do a computed column or an external UPDATE): CASE WHEN HasValidHours = 1 AND NoDemerits = 1 AND NoAccidents = 1 THEN 1 ELSE 0 END


3

That exception usually doesn't refer to the SQL Server itself running out of memory, but instead the client workstation that is processing the results running out of memory. You will generally see this if you are outputting a very large result set into the Output window. To troubleshoot the issue (and possibly resolve it) allow SQL to write the results to ...


0

When testing my backup plan on a VM test server which was disconnected from the network I was running a backup and then used the shrink command only. I use a SIMPLE recover model, still I had to force the setting of the database to SIMPLE mode, only then I could shrink my t-logs. The conclusion seems that if you have a database in SIMPLE recover model, not ...


0

After some more research I realised that what I wanted was data regions. I'm now using different lists and tablixes to bind to the different datasets.


0

At the risk of stating the obvious, nothing is physical in Azure as far as you are concerned. The best thing you can do is go to the Azure Pricing Calculator, find the 'SQL Server' section, select the 'Standard' section, find D-Series, select 'D4' and slide the slider to 1. That's what you will be paying, eg SQL Server will treat that box as having 8 ...


5

This works: DECLARE @mycol VARCHAR(30) = 'Tue Apr 1 12:20:40 2014' SELECT CAST(SUBSTRING(@mycol, PATINDEX('% %', @mycol), 100) AS DATETIME) The day name at the beginning is what prevents it from being able to convert directly. Instead of declaring the variable, you can just substitute the column containing your dates for @mycol in the script above.


1

I really like your idea of inserting XML before and after images of the changed data. IMHO, it is not light weight enough for a trigger. I've changed the whole WHILE loop to the following (sorry for the different names) insert into audit (table_name, old_content, new_content) select @table_name, case when d.table_id is null then null else (select ...


1

Your question is bit incomplete as you have not posted the query used to rebuild the index. A lot depends on what options you selected during index rebuild. Did you use the GUI with default options? The size of the mdf file would also be affected by the Fill Factor value chosen during index rebuild. 1.Why did the .mdf grow that fast? About 1 gb/(10/sec) ...


0

I don't consider it dead. You can easily roll it out across large numbers of servers and have those policies enforced on each instance. I don't think it's about to disappear, but I can't comment for Microsoft.


0

If you just want to have central reporting, take a look at https://epmframework.codeplex.com With this you can check your servers centrally against one policy store.



Top 50 recent answers are included