Tag Info

New answers tagged

0

So to adjust your UPDATE statements, they should look like this UPDATE t1 SET a = (SELECT a FROM t2 WHERE t2.b = t.b ) WHERE b in (SELECT b FROM t2 ) And the statement for updating multiple columns could look like this: UPDATE t1 SET ( x, y, z ) = (SELECT x, y, z FROM t2 ...


-2

This also can happen if at least on one of the tables your check constraint was created WITH NOCHECK.


0

That is the most basic form of an update statement: update the_table set the_column = the_colum + 100; and one of the rows, is of the data type int. No. One of the columns is of data type int.


0

It just jumps to a new, uniform extent. There is no mechanism to try to convert the first mixed extent into individual uniform extents. If I understand correctly, when the mixed extent has no data in it any longer, it will switch that extent to being unused. Then it could be used for either a mixed extent or a uniform extent.


3

You're querying two different things here. xp_fixeddrives will show you the amount of free space on the disk. Using sys.dm views will tell you how much free space there is in the tempdb data file. If the data file is 50GB, then you're only using 5GB of it and the rest is empty. Even though it's empty, the file exists and is taking up 50GB of space on your ...


1

Bigint is not a datatype supported by access. If you wanted the value to stay as a numeric value in access you would have to change the source column in the SQL server table to numeric(19,0). https://msdn.microsoft.com/en-us/library/ms714540%28v=vs.85%29.aspx


1

I ended up writing a tool myself. It's available for free download -http://www.gitsql.net I hope it helps other people who want to achieve the same end goal. Here is an article which describes how to source control SQL Server. http://gitsql.net/documentation-04_SQL_Server_and_GIT I've tried to make it as easy as possible. (3 screens) Connect to SQL ...


0

FWIW - and I'm only posting this as a temporary answer because there's no way I could make this a digestible comment - I ran these queries and saw some discrepancies as well: select cp.cacheobjtype, cp.objtype, sum(cast(cp.size_in_bytes as DECIMAL(19,4)))/1024/1024 as sizeMB from sys.dm_exec_cached_plans as cp group by cp.cacheobjtype, cp.objtype ORDER ...


2

This specific case (the exists predicate) is a bit of an outlier as far as correlated subqueries go. ish... Some correlated subqueries are certainly evaluated once for every row in the outer query, but I don't think you can apply that across the board, and I specifically think that's a statement that cannot be applied with authority to the exists ...


2

Today, I think you would have to add logging to the stored procedures (or to the calling application). Trace / profiler are not supported on Azure SQL Database, and never will be. At some point in the future, you should be able to use database-scoped Extended Event sessions. Bob Beauchemin talked about this here and here, and there is an official marketing ...


0

I know my answer might be late. Yes, this used to happen when SQL server is restarted. See here for more information (as given in other answers). With SQL Server 2014, it has been fixed (identity wont increased by 1000) only if there was a planned SQL Server restart. I have solved in another way by adding a start up stored procedure in SQL Server 2012. ...


1

The subquery in your example becomes correlated because it refers to a table (stores) in the outer query. This is the definition of a correlated subquery - a subquery which depends on rows in an outer query. What you ask about is an independent subquery not correlated to the main query. You can do that like: SELECT DISTINCT store_type FROM stores WHERE ...


0

Our solution ended up being ShellRunas. https://technet.microsoft.com/en-us/sysinternals/cc300361.aspx By creating custom shortcuts to launch Excel using the /netonly switch, we can have them enter their domain credentials and then open the needed spreadsheets. It's not 100% transparent, but it's good enough.


2

Check MS documentation. Specifically the top line: The THROW statement honors SET XACT_ABORT RAISERROR does not. New Applications should use THROW instead of RAISERROR. I believe that is your problem, right there. https://msdn.microsoft.com/en-us/library/ms188792.aspx


0

Simple way to query the end time: use MSDB go select * from restorehistory; Or use MSDB go SELECT MAX(restore_date) as LAST_RESTORE_DT FROM restorehistory WHERE destination_database_name = 'DB_NAME' The restorehistory table doesn't have the start time of the refresh.


0

I would prefer to rebuild the indexes where the fragmentation percent of the indexes is greater than 30% and a Re-org of indexes where fragmentation percent is in between 9% and 30%. Coming to update stats, I would prefer to do it with Full Scan depending on the size of the databases. While these are resource intensive database operations, I would prefer ...


0

I would agree with Aron's idea here.... Extended Events can get you all the information you needed. or Querying from DMV's directly would be an alternative. DMV's have the information from the last time when sql server was restarted...


2

You have installed a new instance of SQL Server, so you need to change the way you connect to that new instance. Previously, you had a default instance, so connecting using any of these in the Server name: box would work fine: localhost . 127.0.0.1 <any_local_IP> <your_machine_name> <your_FQDN> Now that you have installed a new instance ...


0

You should really consider upgrading to a more recent version, especially if you want to utilize some of the best parts of Full Text Search. To your question, the short answer is yes. From the documentation for 2005, you have two main features to customize in a thesaurus, Expansion Sets and Replacement Sets: An expansion set contains a group of ...


5

The idea that an application should be written with just standard SQL comes from application developers who think that changing a DBMS is a worthy design goal. I -- and many other DBAs with me -- don't agree. While it holds true for basic features, your application is going to perform best if it uses the features that your particular DBMS provides. This ...


1

SQL Server is atrocious when it comes to the performance of scalar functions as well as the reporting of its impact. (a very useful article with details as to why: T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1)) You are correct that the Query plan (actual) does not reflect the true performance of the two different approaches, this is ...


6

Why does SQL server run ths inline SVF query slower - both in CPU and elapsed time? Scalar valued functions are executed in a different context than the main query and setting that up for each call takes time. By centralising some simple logic it appears I impede performance through code reuse. Yes, for scalar valued functions that is true. ...


0

Throw Execute SQL tasks into the control flow and don't link them to each other. Any task without a preceeding task will run asychronously. Executing 20 - 30 Stored procedures in parallel would bring you a lot of workload. I say think twice about it. I have a developer who did that with a custom written windows service, i am facing resorce_semaphore wait ...


1

Underlying SSIS task that do not require SSIS to be installed to run, nor require you to edit via BIDS or SSDT-BI. They are there more or less for easy deployment of basic database maintenance that does not require much knowledge to build. Is it something that fits any environment? I would say no, it is mostly for the one man/woman shops that just need ...


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 ...


-4

If you only need to eliminate the last rows which have not followed the incremental value of an identity field, there is an easy and safe way: first delete the last record(s) which have 'jumped' change the data type of your Identity Field (from int to bigint or vice versa) save the table add a new record, and check it assigns the number of the highest ...


1

Instead of SSIS I think you might want to look into SQL Server Service Broker. This lets you send messages asynchronously. See here or here for a start. MSMQ or RabbitMQ may also be options you should consider.


0

In order to stop your transaction log growth you need to take regular transaction log backups (along with full database backups). If it is a test database and you don't need to recover it to a point in time in case of a disaster you can switch the database recovery model to SIMPLE (you probably are using FULL recovery because it is default in SQL Server). ...


1

If you're on SQL Server 2012 or 2014, this is an aggregate that you can calculate with an ordered window function. SELECT ItemCode, FiscalYear, FiscalMonth, SUM(StartingQty+PeriodChangeQty) OVER ( PARTITION BY ItemCode, FiscalYear ORDER BY FiscalMonth ROWS UNBOUNDED PRECEDING) AS EndingQty FROM dbo.theTable; ...


-2

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.


7

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.


0

James Anderson got most of it. I just want to add one more. Purging is a huge reason we are working on Partitioning. If you indexes are aligned, then you can do partition switching. For us that means that we can take older partitions and just switch them out to different table and then drop that table. The actual switch allows me to remove over a ...


3

But I read online that I have to stop and start the sql Server services inorder for this change to be effective. Is that True? Yes, you have to stop and restart sql server services. fill factor (%) This has is_dynamic = 0 in the master.sys.configurations. This means that when you run sp_configure 'fill factor (%)', 75, the VALUE will be set to ...


3

Yes, it is clearly stated here which options require a restart: https://msdn.microsoft.com/en-us/library/ms189631.aspx


0

You just need to make sure that the credentials used to set up the linked tables only have the bare minimum permissions they need to do the things the forms do. You will always have to deal with the fact that if you give someone the keys to the door, they will be able to get into your house even when you are not home. So you need to make sure that if your ...


0

The easiest way to do that is on the back end. Define a user that has only the access you want and then update the ODBC DSN that Access uses to that user account.


4

Please don't detach and attach your database. If you detach it and something happens to it, you now have zero copies of your database. Much safer approaches: Backup/Restore USE master; GO BACKUP DATABASE floob TO DISK = 'c:\wherever\floob.bak' WITH INIT, COPY_ONLY; ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE floob ...


1

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

The typical way to resolve this is to set the database to emergency: ALTER DATABASE [YOURDB] SET EMERGENCY; Then you will want to set to single user: ALTER DATABASE [YOURDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; Now you should be able to run DBCC CHECKDB (YOURDB). You probably want to run it first to see what is wrong before you decide how ...


3

I will post it as an answer because it doesnt fit in the comment: What you need to do is detach the database, move log file to a new location, and atach again referencing the new location of the transaction log file. I will not post an answer because i never did this, but I know this is how it works. You can find a lot of information on google. And yes, you ...


1

My first inclination would be to use a script component (e.g. C#) as source in a Data Flow Task. The script would just read the column contents, parse the individual records and fields, and send records down the pipeline. The individual fields (perhaps strongly-typed) can be defined in the output record definition of the designer. ...


1

Create a set of staging tables in the target database. Write rows to these as they are generated, which seems to be one or two at a time. This can be inside a transaction. Once the whole batch (200 rows?) is in these staging tables use a stored procedure in the target database to move them en masse from the staging tables to the real ones.


2

You can use this select st.text, qp.query_plan, qs.plan_handle, total_worker_time/execution_count AS [Avg CPU Time] FROM sys.dm_exec_query_stats AS qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st cross apply sys.dm_exec_query_plan(qs.plan_handle) qp if you are using sql server 2008 and above you can configure Extended Events as ...


1

I've seen similar problems addressed by decoupling the app from the central server: Remote sites install a local SQL Server Express along with the app Apps talk to the local Express instance. Low latency, good availability. SQL Server Express uses Service Broker to deliver the updates to the central server Service Broker handles the network availability, ...


2

It can help query performance by employing partition elimination. This means large sections of big tables can be ignored when looking for values which means much less IO. Index alignment needs to be looked into when partitioning. See details here You can break your backups by partition. This can be useful if you are struggling to complete your backups in ...


0

SELECT [UserName] = ulogin.[name], [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.type_desc -- Schema-contained ...


5

This will be tough to do with the built-in schedule UI, unless you want to create 12 different schedules, but you can easily build this into your job step. Assuming it runs every night: IF DAY(GETDATE()) <= 8 OR DAY(GETDATE()) >= 28 BEGIN -- do backups END On other days (9th -> 27th), the job will still run, but everything inside the IF ...


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

In this context, you should consider "hotfix" to mean any fix that ended up in a Service Pack, Cumulative Update, or on-demand hotfix and falls under the jurisdiction of this trace flag. These fixes are all in the most recent builds of each major supported version, but they are not used unless the trace flag is turned on. This is because, in some cases, the ...



Top 50 recent answers are included