Tag Info

New answers tagged

0

The 'Ignore index options' should be set also to avoid SSDT rebuilding your partitioned index each time the deployment is triggered.


1

Stop using CAST and use CONVERT. Specifically because, in this case, you need to use a certain style parameter in order to get the right string comparison (otherwise it is just converting 0xwhatever to the string value represented by 0xwhatever, which is not 0xwhatever. Compare: SELECT CONVERT(VARCHAR(32), 0x48656C6C6F21), CONVERT(VARCHAR(32), ...


1

You might try looking at msdb.dbo.sysjobactivity. This table includes the session_id and the job_id for actively running jobs. I would expect you could take that and then connect back into the sessions DMVs to get your information.


0

Do you have a backup of the database? If so start the process to restore it, the GUI under the 'files' page will show where the original files existed.


1

Short of checking the default path for your database you can do a search through Windows Explorer for *.mdf. Default path for SQL Server is usually something like C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL__.MSSQLSERVER\Data but that can be changed during installation. Ensure you are searching for the files on the database server, whether it is a ...


2

Sometimes, the lack of an uint type in SQL Server can come in handy. If Order-id is defined as identity(1,1) in DB2, you likely do not have any negative Order-ids. Consider importing all the legacy DB1..Orders.Order-id fields as the same number, but multiplied by -1. If there's a FK to Order-id in DB1, you would use the same multiple-by-negative-one ...


0

Use the official sample databases from MySQL: http://dev.mysql.com/doc/index-other.html.


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

This is a good case for CHECK constraints using a function. Step one, write a function that does something like (EDIT: Used Event instead of Action sorry!): CREATE FUNCTION CheckReasonValid(@intReason, @intEvent) RETURNS bit AS BEGIN DECLARE @NumRows int SELECT @NumRows = COUNT(*) FROM master_action_reason mar INNER JOIN Events e ON ...


5

sys.server_triggers is a server view, so it has the server collation. sys.triggers is a database view, so it has the database collation. You have a DB that has a collation different from the server. QED. Repro: create database foo collate Latin1_General_CI_AI use foo select tr.name from sys.triggers tr union all select tr.name from sys.server_triggers tr; ...


9

Saving your data differently would make that query trivial. Namely: one number per row. Something like: results(Date, Time, Rank, Number) where Rank would be 1 (or zero) for the first number, 2 for the second, etc. (Only if the order has importance, drop the rank if it doesn't.) Then your query boils down to (ignoring reserved identifiers): select ...


0

If you have to dynamically change the reply_to it seems like your going to have to wrap this sp_send_dbmail within a stored procedure and assign the single reply_to (based on some query) to a variable and use that vs. hardcoding the entries.


1

@reply_to can only accept 1 email address. Its by design - see this connect. The document reflects that as well. [ @reply_to= ] 'reply_to' Is the value of the 'reply to address' of the email message. It accepts only one email address as a valid value. This is an optional parameter used to override the settings in the mail profile. This parameter is of ...


4

Ok, so the documentation is not exactly stellar, but you must think this shouldn't work online because the doc says: Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Which - if you invert it - states that an ...


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


1

Yes, of course there are risks. How are you going to "insert fresh"? If there is a primary key on the table, you're going to need to delete everything first. Where are you going to store it? How many things do you think might go wrong in the meantime? Stop using pyodbc's cursor functionality to perform an update. This is calling 67,000 individual UPDATE ...


1

There's a lot more to worry about with a DELETE/INSERT methodology. From the top of my head: Transactions will need to be manually handled. An UPDATE is a single operation that is atomic. With DELETE/INSERT it's possible to have the delete succeed but the insert fail, in which case you have no data. You need to handle this manually and the engine handles ...


4

Thomas has explained why that stored procedure isn't capturing orphaned Windows users, but here is how you can check: SELECT p.name FROM database_name.sys.database_principals AS p WHERE [type] IN (N'U', N'G') AND NOT EXISTS ( SELECT 1 FROM sys.server_principals AS sp WHERE sp.sid = p.sid ); If you need to do this for all databases, you can generate ...


4

That stored procedure sp_change_users_login only reports on SQL users, not Windows users. Here is the actual reporting query that the stored procedure uses (you can get the stored procedure text with sp_helptext 'sp_change_users_login'): select UserName = name, UserSID = sid from sysusers where issqluser = 1 and (sid is not null and sid <> 0x0) ...


1

One to do the backup and restore (running as an sa) and then creating a separate user that only has access to the PRODCOPY database, that then runs the clean up section of this script. Any thoughts on this? This is a good idea. You can have 2 jobs Perform backup and restore of PROD database to dev environments Once above is completed, kick off the ...


0

I had a situation similar to this. Even though the SQL Server Configuration Manager showed that Force Encryption was "No" and the certificate page was clear, I continued to get this error and was unable to start the service at all. In the registry (for my installation): HKLM\SOFTWARE\Microsoft\Microsoft SQL ...


4

The FlushCache message in the error log is caused by checkpoint logging, and in this case by a long checkpoint (which is defined as a checkpoint that is taking longer than the recovery interval). Whether it's logged or not, the behavior is different in pre-2012 and 2012+. Before SQL Server 2012, to get checkpoint logging you'd have to turn on a trace flag ...


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


0

Can you do the calculations in the dataset the report uses? May have an easier time doing that then building the expression needed to calculate the column for SSRS


3

First, this assumption is questionable: "Cannot use transaction logs as it would affect my load performance - datasets are potentially huge with large transactions" When you do work in the database, your transactions are logged regardless. Full recovery mode just means your logged work sticks around until the next log backup. If non-logged operations ...


1

How are you migrating the database? Copying a database to another server via Backup/Restore or Detach/Attach will include the Assemblies as well as the T-SQL wrapper objects that point to the code in the Assemblies. Using a tool that lets you select object types might require that you at least verify that Assemblies have been selected to migrate. If all of ...


1

Have you configured MSDTC on both the source and target machine using dcomcnfg? The distributed transaction coordinator, by default, does not allow distributed transactions. You need to configure it by running dcomcnfg as an administrator, and configure the options applicable to your situation:


2

You can use the hint WITH (RECOMPILE) while creating / executing your store procedure. CREATE PROCEDURE sp_GetRespond WITH RECOMPILE OR EXECUTE sp_GetRespond WITH RECOMPILE Every times, the sp is run SQL Engine re-compiles it and generate the most optimal execution plan.


3

You can change this behavior with the ALTER SERVER CONFIGURATION statement. The flexibility to modify all of the necessary parameters (path, max files, max file size) should give you enough control to get it to where you want. See the bottom of the referenced link above for samples/examples. Here is one copied from the source: ALTER SERVER CONFIGURATION ...


0

No. In environments where you want to enforce this, you're best off using a third party appliance that sits between SQL Server and its network, like Guardium or Imperva. These appliances are like SQL Server firewalls that audit for compliance, plus can filter unsecured traffic.


0

Filtered indexes and statistics won't come into play when you're using local variables. Tim Chapman's MSDN blog post explains with examples.


1

Boiling down the question to the basics: Q: "is there some way to set a policy or something on the Microsoft SQL Server instance itself that will cause any SSMS clients to be colorized?" No. SSMS query color-coding is part of a client-side connection and that data is stored in the registry. You could theoretically export that registry setting and apply it ...


3

That depends. If the assemblies were created and live in the database that is being migrated, then they will go with the database as they are already objects inside of it. If the assemblies were created in other databases that aren't being migrated then they will need to be re-created in the source instance somewhere. For example, if the assembly was made ...


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


3

When you create job using maintenance plan automatically job is created in SQL Server agent under Jobs. So now go to SQL Server agent expand jobs look for your job. Right click on it and select Script job as...Drop and recreate to..New query editor window. you can get the script Edit: You can export the maintenance plans as well. You need to connect to ...


2

I read that the change in recovery model only has effect after a backup is taken. However, if after the performing the above I do This is mainly for when you change from simple recovery to full. After changing from simple to full you need to take full backup to take database out of Pseudo simple recovery. Pseudo simple recovery means even if database is ...


2

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


4

DBCC uses snapshots internally. Snapshots are then implemented as sparse files in Windows. So this is actually a problem with Windows' handling of sparse files, which causes the snapshots used by DBCC to occasionally break on large and very active databases. It's reasonably well documented with a few recommended fixes in ...


3

This is likely due to previous alters (particularly of existing column widths) that are still reflected in the underlying page structure. Try rebuilding the table or dropping/re-creating the clustered index to reclaim that space. http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/alter-table-will-not-reclaim-space.aspx


2

It depends on the method you're using to alter the column. We'll start with a simple table: CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerName NVARCHAR(200)); CREATE INDEX IX_CustomerName ON dbo.Customers(CustomerName); Then use the SQL Server Management table designer to change the CustomerName column to a VARCHAR(200) ...


0

You could reverse the relationship between passenger and ticket so that the ticket has a nullable FK to the passenger. When you create a new trip, create ticket entries for the number of passengers in the trip / seats on the bus. Then relate a passenger to each ticket as the ticket is sold. When a trip has no more tickets with an empty passenger id that trip ...


0

Create a new table that is partitioned as you want and copy the data from the old table into it rather than trying to apply the new partitioning scheme to an existing table. Once the data is copied, remove the relations to the old table, drop it, and rename the new to take the place of the old and recreate all of the relations. Perform this within a ...


0

You do not have to include your partition key in your clustered index if the primary key itself is not partitioned. You can create an identity column to serve as the primary key and the clustered value, but partition the table by another value. That would be my preference because it will result in smaller indexes because the cluster key is smaller. ...


6

Parameter sniffing means that one set of parameters produces a dramatically different execution plan than the other, and that if the wrong plan is cached, you get adverse performance effects. This answer is based on your simplified query - to get accurate advice for your query, you'll need to post the query and the two different plans that resulted from ...


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.


4

Since SQL Server can skip NULL rows to start the range scan, the cost of either index is identical, so this is basically a coin toss for the optimizer. Look at the plans in SQL Sentry Plan Explorer* by default and when you hint the index (click to enlarge): Since it's a toss-up, I don't know what benefit you'd get out of forcing SQL Server to ...


6

Ok, so here is a quick example demonstrating why - in the case where most of your operations (reporting queries, archive operations, partition switches etc.) will identify ranges of rows by date - you're better off clustering on the partitioning column. Let's have a simple date-based partition scheme and function: CREATE PARTITION FUNCTION DateRange (DATE) ...


0

No, there is no policy for this. You could perhaps figure out a way, e.g. via Group Policy somehow, to set this value to 100... ...but that doesn't really solve the problem, and will affect all environments they connect to, including their local instance(s). You can prevent SELECT * by adding this computed column to the table (or to a view that ...



Top 50 recent answers are included