Tag Info

New answers tagged

0

Below is an example to generate the needed script. DECLARE @SqlScript nvarchar(MAX); SELECT @SqlScript = (SELECT N'ALTER LOGIN ' + QUOTENAME(name) + N' WITH DEFAULT_DATABASE = tempdb;' FROM sys.server_principals WHERE default_database_name = 'master' AND type NOT IN('C', 'K') FOR XML PATH('')); EXEC sp_executesql @SqlScript;


2

You should never try to update system tables directly, in most cases it is not going to let you as you have found. In your case you will want to build out a dynamic statement for the ALTER LOGIN without knowing how many there are on the instance. However, you will need to be cautious in doing this to ensure you do not touch logins you shouldn't. This query ...


2

For SQL Server: Neither. To insert a lot of rows you should use bulk insert APIs, and thrive to achieve minimally logged insert. Bulk inserts can be achieved using IRowsetFastLoad (OleDB), using the Bulk Copy Functions (ODBC) or using SqlBulkCopy (.Net). All these APIs have in common that they establish a fast insert pipe with the server, and then they ...


0

Question: Have CTE changes from 2008 r2 to 2012 and now 2014. If so how and is there a link to show what has been improved upon.


3

In SQL Server at least (can't speak for the other RDBMS you've mentioned, sorry), a single statement will scale better than multiple, to a point. You can test this yourself using your exact statements and data, of course; nobody here can test that for you with your specifics, and your specifics may tilt things one way or another. "Which is faster, x or y?" ...


0

in SQL management 2012, under Database Design menu... select "Copy Diagram To Clipboard"... paste into any program which will let you print or save to PDF.


0

Some thoughts, using a prepared statement will probably be a good idea since the statement wont have to recompiled by the DBMS for every new row. Something like: insert into emp(name,age) values(?,?); Sending several insert at once will minimize the network latency, in some languages there is a batch update operation which "mimics" this. If your host ...


1

If the FuncWeight field in the #Temp2 table is truly defined as float(3), then the most likely problem is that the datatype of @constVal is DECIMAL(x, 0) or just DECIMAL. Either of those would round up the 0.7 values to be just 1, though the 0.3 and 0.35 values would round down to 0. Nothing in that posted code or data indicates what could force all values ...


1

Use Microsoft Performance Analyzer or Media Experiance Analyzer with Windows Performance Recorder. Any of these is capable of answering your question in great detail, quickly and foul-proof, using system ETW capabilities. This article is a good walk through: Analyzing Storage Performance using the Windows Performance Analysis ToolKit. At the simplest, it ...


1

Likely, the issue is caused by the very long IN lists. Instead of using the IN list, create a temporary table with the values you want to filter on. Then rewrite the query as a join: CREATE TABLE #FilterOutputArea (...) INSERT #FilterOutputArea VALUES ('E00139237') ... etc... CREATE TABLE #FilterColumnID () INSERT #ColumnID VALUES (298) ... etc... SELECT ...


0

Run Performance Monitor, then click Resource Monitor on the opening page. You can then use the disks tab to see all the action. Look under Disk Activity. It will show you the culprits.


1

One approach would be to use optimistic locking. Before displaying the update form, you retrieve all the original values from the database. Or, at a minimum, a column that is guaranteed to change on update, such as last_update_date or similar. When updating you check that all values are still the same in your where clause. If the update fails to update any ...


1

You've already checked that an instance of SQL Server is running and listening on TCP port 1433. Check if the SQL Server Browser Service is running: sc query sqlbrowser | find /I "STATE" If it's not, set it to start automatically and try starting it: sc config sqlbrowser start= auto sc start sqlbrowser Check that it's listening on UDP port ...


0

In SSMS, go to SQL Server Agent Click on Jobs Now Click on View->Object Explorer Details You will have a list of all jobs on your right window. This way you can select multiple jobs that you want to script, right click and select "Script Job As" and then the options that you want.


0

I would Get together a simple spreadsheet of your estate, find out exactly what you are responsible for. You are almost putting together a small CMDB for SQL really, you want to know the application names, service owner names etc. This will be invaluable later. Find out if you have any company build standards on SQL, someone may have left behind a ...


0

Indexes are technically redundant only if they are identical in all respects: clustered/non-clustered, key list, column order, sequence specification, included columns, filtering specification. Even slight variations may result in one index being more beneficial to a query than another similar one. Examine sys.dm_db_index_usage_stats over time to identify ...


0

if the case statement used in the WHERE condition and the first case when statement involve evaluating column values from the table, and the first row in the table does not satisfy this condition, the case statement will go to next case when statement. declare @tbl table(id int) insert into @tbl values(1) insert into @tbl values(2) insert into @tbl ...


0

The latency of of the I/O system (which the average latency per IOPS is an indicator of) tell you if you are adding more load on the I/O system than it can handle. Because of this, the average latency, when compared with the media you are running on provides a good indicator as to whether I/O is the problem or not. As a rule of thumb, here are the latencies ...


0

That is a great list. I would also use Brent Ozar Unlimited sp_Blitz, http://www.brentozar.com/first-aid/sql-server-downloads/. It will give you great overview of what might not be setup quite right.


0

Not quite. If the PK is A,B,C, you already have that unique index. A,B,C,D is a covering index for queries that need exactly those four columns, but would be better as A,B,C INCLUDE D A,D,B,C is definitely not redundant; it's the only one that can work with both A and D on queries that use those two but don't use B or C in the index. Depending on the ...


1

To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using: =join(Parameters!<your param name>.Value,",") This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive ...


0

Case: Accidently When u run below command on Principal server instead of mirror server: alter database 'Database_Name' set partner off After running above command your DB goes in [mirror, disconnected] State on mirror server. Resolution: Step 1:Run below query on mirror exec sp_resetstatus 'Database_Name' Step 2: After running below command your DB will ...


11

Because dropping of a NCI is already as much online as it gets. Is a metadata only operation. There is not even data deletion, a dropped index rowset is simply deallocated, ie. the same operation as truncate does. Dropping a clustered index, on the other hand, implies a rebuild and is a size-of-data operation, so it does make sense to have an online ...


1

You can derive this information easily by joining sys.tables.object_id = sys.objects.parent_object_id for those object types. DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + N' ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' DROP CONSTRAINT ' + QUOTENAME(c.name) + ';' FROM sys.objects AS c INNER JOIN sys.tables AS t ...


0

Well I copied every index that the query was calling from the prod to the replication server and updated stats…finally works…14 seconds…better than 12 minutes! 


2

You could use the Windows System Resource Manager (WSRM) feature for this. It is not present in Windows starting from 2012 R2, but since this is an old SQL 2005, I would expect the OS to be old-ish as well. I've never used it myself, but, though a bit complicated, it might do the trick for you.


2

This is kind of how log shipping has to work. You need exclusive access to a database to apply a log backup, which means your script has to set the database to single_user or otherwise evict all the users before it can apply the log backup(s). Some workarounds: Schedule your restores for outside of business hours, and let them continue querying stale data ...


0

Well the first thing I would check is if the database is actually restoring, this is the query I use to report on the status. SELECT session_id AS SPID ,command ,a.TEXT AS Query ,start_time ,percent_complete ,dateadd(second, estimated_completion_time / 1000, getdate()) AS estimated_completion_time FROM ...


3

You can use an outer apply instead of a cross join. Using this simplified table structure. create table T1(ID1 int); create table T2(ID2 int); Add three rows to T1 insert into T1 values(1); insert into T1 values(2); insert into T1 values(3); Query with outer apply. select T1.ID1, T2.ID2 from T1 outer apply ( select T2.ID2 ...


0

We have run into issues when servers were re-named and we had to set the DTC security properties to "No Authentication Required" on all servers involved in the transaction. This is not a best practice but works for internal network only servers. Directions to the DTC security settings can be found here: ...


0

I would use something like the following. This is pretty much what I have been using for years, with your error message logging worked into the CATCH block. CREATE PROCEDURE [SchemaName].[ProcedureName] ( @Param DataType ... ) AS SET NOCOUNT ON; DECLARE @InNestedTransaction BIT; BEGIN TRY IF (@@TRANCOUNT = 0) ...


0

I often run into this issue in environments where we have domains with no trusts between them. The issue with using SQL Auth is that by default it's in clear text, so your passwords are easily sniffed out and it doesn't pass a lot of compliance requirements. I made a shortcut which executes SSMS as a different user. When you log in, it'll show your ...


2

If you are connecting from outside the domain you can either 1) Use SQL authentication to login. If you have enabled mixed mode during the install you will have an sa account and you can create a sql login for yourself 2) There are other tricks, such as enabling named pipes and then authenticating against the IPC$ share with your domain account and then ...


-1

It will give you the latest refresh date for your DB: select top 1 * from restorehistory where destination_database_name='DB_NAME' order by restore_history_id desc


0

If it's stored procedure, perhaps PIVOT tblConsolidadoJob table and insert results into hash table and then use very same hash table on a left join? Something like this: SELECT * INTO #temporary FROM ( SELECT CodJob, Quant, TipoCons, Perc FROM tblConsolidadoJob ) AS SourceTable PIVOT ( MAX(Perc) FOR TipoCoins IN ...


5

You can't get an empty string because you're returning the DATE value type from ISNULL. Per MSDN, ISNULL Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an ...


-2

something is wrong here. Back up the table again inc. all the NOT NULL columns, drop it and restore it If it doesn't work, please paste your backup and restore procedures here. about you last question: yes, you can. In our world you can do everything that makes logical sense and even more.


1

For each data cell of your table (or matrix) you will have to set an expression on the font color (general tab under text box properties): Now I am making assumptions of what your dataset/columns are called so you will have to translate those appropriately to your report setup. For the Revenue and Quantity cells within each category the expression will be ...


0

It might help a lot if you would have listed the indexes you have or the query plan. If the columns 4, 5 or 6 are indexed, that can slow the process down, and having a clustered index on columns 1, 2 and 3 should speed it up. If columns 4,5 or 6 are variable length (e.g. varchar) then updating new value can cause a lot of page splits, that definitely will ...


2

A simpler way to write the MERGE is as follows: MERGE #target /* WITH (SERIALIZABLE) */ AS T USING #source AS S ON S.Id = T.Id WHEN MATCHED AND T.IsDeleted = 0 AND S.Modified > T.Modified THEN UPDATE SET Modified = S.Modified, NoteText = S.NoteText WHEN NOT MATCHED THEN INSERT (Id, Modified, IsDeleted, NoteText) ...


0

I have a similar problem in SQL Server 2008. We have hourly schedule which pulls data from multiple Sybase Adaptive and Sybase IQ servers and populates the SQL Server (Using System DSN, Linked Server). Every Friday after 2 AM those jobs hang. After a little bit of research on my Windows Server which has the SQL Server I found that the McAfee weekly schedule ...


0

Before proceeding with below steps make sure to have 1: DB (Name of the database in picture) the database backed up: Check for any important sessions that might be an critical for production before we D/C them a per below methods: Method 1 : Use below script on the DB you want to restore, so that all open connections can be killed: DECLARE ...


0

This would be an unusual deployment pattern: normally you'd want to have 1-to-1 correspondence between an application and a database. This mainly is because you'd want your development environment to mirror your future production environment as much as possible. If you cannot create databases on the server you could still look at pre-creating a set of ...


0

No Need to turn off the SQL Server. You can restore your DB1 without turn off your SQL Server. Case 1 # Just right click the Database node. one pop up window will open there select source from Device . you shall find elipses button there just click there again pop up window will open that is (Select Backup Devices) your backup media type will be by ...


3

It depends on how strictly you are defining "programmatically". If you mean a process that knows nothing of the proc it is evaluating, then you aren't going to get very far. In addition to the obstacles mentioned by @Aaron in his answer (i.e. Linked Servers, Dynamic SQL, OPENQUERY / OPENROWSET, deferred name resolution, etc), you also have the issue of ...


0

If the secondary is up and running, when the log block is flushed to disk (either because it is full or a commit), the record gets pushed to the log writer on the primary and to the log scanner (log reader) process on the primary simultaneously. Then the log scanner communicates with the secondary and the secondary then pulls the transaction from the log ...


2

The easiest programmatic way would be just to try and execute them (using TRY/CATCH) and determining the class of error that results. Of course you'd only catch the first error, and this assumes that there isn't error handling in any of those procedures for missing objects. Plus, as @srutzky points out, there is more to calling stored procedures than just ...


2

The best option is to use the SEQUENCE object, introduced in 2012. Since it is an independent object, you don't run the risk of querying it at the same time and retrieving the same value - it'll always provide the next in the chain. Set the object with a specific start and increment value, then call it to get the next value desired. One of the biggest ...


0

I got below definition from Bob Dorr about what Max server memory in SQL Server 2012 controls. You can also read Books Online for more details Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in ...


1

Max server memory controls buffer pool and all page size allocations, but still does not control things like direct Windows allocations (linked servers, sp_OA, XPs), memory required for threads/thread stacks, etc. You can probably expect this to be higher on NUMA (though I'm not sure 20 GB is normal); the point is, you can't expect max server memory to ...



Top 50 recent answers are included