Tag Info

New answers tagged

5

Why isn't it caught? Because TRY / CATCH doesn't catch all types of errors. But you can get it to catch the non-system-critical stuff by wrapping it in an EXEC(), such as: BEGIN TRY EXEC('ALTER TABLE...'); END TRY The error in the ALTER will fail out to the EXEC, which in turn will return to the TRY / CATCH block reporting a simple, catchable error. ...


3

Yes, you can choose instance to apply service pack. Instance components along with shared components between side by side instances will be upgraded to higher service pack. Sql server has two types of component\features. 1. Instance specific features 2. Shared Features When you upgrade one component with a given instance name to a new version or ...


2

Yes, but you have to understand that there are certain shared components. See Work with Multiple Versions and Instances of SQL Server.


1

One option is create login with default database option and add role member to login user in database USE [master] GO CREATE LOGIN [CUREMD\NewLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[AdventureWorks] GO USE [AdventureWorks]--- Change DB name to Sharepoint DB Name GO CREATE USER [CUREMD\NewLogin] FOR LOGIN [CUREMD\NewLogin] GO USE [AdventureWorks] --- ...


1

View the default database in user's properties, may be it is master, if so, then change it


1

Verify if your user has permision to connect to database Engine. Right click on Logins -> Properties -> Status. Or grant CONNECT for user. CREATE LOGIN [mydomain\mygroup] FROM WINDOWS; GRANT CONNECT SQL TO [mydomain\mygroup]


3

There are two things to consider if you want to store values > 8000 bytes in a single column in SQL Server. First, the column must be capable of holding values of this length. For strings, this is typically done by giving the column a type of varchar(max) (for single-byte characters) or nvarchar(max) (for Unicode). As an example, the following table ...


-1

For me the problem was that the SQL Server Agent wasn't running on the server. Open the SQL Server Configuration Manager and make sure SQL Server Agent is running. Before doing that you may have to open it's properties and set the Start Mode to Automatic and save the changes.


1

There are a few ways to do this. If there is a 1-2-1 relationship between the parents and widgets ( ie a parent only has one widget and vice versa ) you can just add a column in the CTE to count how many rows have passed through. See the xlevel example below. If that doesn't work for you ( and I would probably expect a parent to have multiple widgets ) ...


0

Instead of trying to find a way to release your RAM, you might be better served trying to determine how much of your RAM your SQL server is allowed to use. Seeing as how you're using a 16GB server, try leaving 2-3GB for the OS (or more depending on what else is installed on there) The following commands would set your max server memory (the amount of ...


2

We don't need automatic failover, and we don't necessarily need the data to be replicated in real-time: once every 5-15 minutes would be fine. First thing you need to do is to change your database recovery model to FULL as logshipping is not supported in simple mode. In your scenario, logshipping would be best choice IMHO without the need to upgrade to ...


2

You're right about each of your options. Log Shipping is perhaps the easiest way to almost accomplish what you want, but there is the drawback that you won't be able to read from your secondaries while the logs are being restored. If your goal is simply to become more highly available in case something bad happens, I advise you to at least get your backup ...


2

Fill factor is critical in fragmentation What does the author mean with "page splits occur when there is no room for data"? There are basically two types of splits good page split or sequential page split which occurs when leaf level of index page is full and when new record is inserted page split occurs which results in allocation of new page and ...


3

Yes, this is possible by creating a user with 'securityadmin' server role and then if required Deny his access to SQL Server Query Window through LOGON Trigger USE [master] GO ----Create Login CREATE LOGIN [DBA_ErrorLogUser] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO --- ...


0

We found traces of a replication that had been activated in the past in a specific table. Replication was not fully removed from this table. We also notice that the script works in different database name (a database with the same structure). Here is the history: We restored database from DATABASE_A (with traces of replacation) to DATABASE_B We run ...


-2

While cursors are slow in SQL Server, you can avoid deadlocking in a cursor by pulling the source data for the cursor into a Temp table and running the cursor on it. This keeps the cursor from locking the actual data table and the only locks you get are for the updates or inserts performed inside the cursor which are only held for the duration of the ...


2

Since you stopped the index creation, the index was not created. However, the space it had reserved already (10GB) was still added to the database size. SQL Server will not automatically shrink the database file size back down unless you explicitly instruct it to (which is not recommended unless you are about to run out of space). You can add up to 10GB of ...


0

Yes, you are able to restore a database with NORECOVERY and then perform more restore produces within one script, allowing you to recover a database to a point in time. When you restore full and differential backups you should have a MOVE statement for both the data and logs. I would recommend moving the backups you want to restore to a directory which ...


1

Assuming the id column is an identity column and you want to retrieve the identity value of the record you just inserted, you can use the OUTPUT clause, like this: INSERT INTO sometable (name) OUTPUT inserted.id VALUES ('some name');


6

I don't want to use any primary key or timestamp or others Sorry. The only reliable way is to store this information yourself. Most commonly by storing some increasing value such as an identity generated value or sufficiently high precision timestamp as a column in every row. (which would be most suitable could depend on various factors - Should rows ...


0

You can turn off the system stoplist and create your own, eg ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = OFF CREATE FULLTEXT STOPLIST myCustomStoplist; ALTER FULLTEXT STOPLIST myCustomStoplist ADD 'someWord' LANGUAGE 'English'; ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = myCustomStoplist If you create an empty stoplist, it means all ...


0

I just completed a 4 day course with the Kimball group and they had this to say on naming conventions: Always use full descriptive names ie. Brand Name. and don't use abbreviations. Otherwise you may need to spend time training users. Don't use Dim or Fact in your table names because you end up spending a lot of time deleting these from the reporting ...


1

This query should do what you want and avoid any issues with 1 to many relationships messing up the SUMs SELECT i.job_no, installment_billing_amount - transaction_billing_amount FROM ( SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount FROM [FCU_LIVE].[dbo].[jm_installment] GROUP BY job_no ) i ...


0

I know this is an old topic, but I was effectively looking to do the same thing and thought I'd share my solution. It is true that by default CREATE USER requires that you assign that to a server principal (login). However, you can specify "WITHOUT LOGIN" on the user you wish to create (in this example, the individual group member for whom I want to assign ...


5

No different. Space Reserved for your MDF file: 100.21 GB, which is equal to 105,082,944 KB (105,082,944 / 1024 / 1024 = 100.21) Space Reserved for *_1.ndf: 148.62 GB, which is equal to 155,843,968 KB (155,843,968 / 1024 / 1024 = 148.62) The numbers match up just fine. The Operation System is going to see Space Reserved, and that's how you would need to ...


1

Here's a powershell solution: import-module sqlps; $s = new-object microsoft.sqlserver.management.smo.server '.' foreach ($db in $s.Databases | where {$_.IsAccessible -eq $true}) { $u = $db.users | where {$_.Login -eq 'foobar'} if ($u -ne $null) { #login is mapped to a user in the db foreach ($role in $db.Roles) { if ...


4

Here's one way using dynamic SQL. There's not really any way to do this without iterating but this approach is much safer than undocumented, unsupported and buggy options like sp_MSforeachdb. This will get a list of all online databases, the mapped user (if it exists) along with the default schema name and a comma-separated list of the roles they belong to. ...


4

This script is slightly modified from a script mentioned at will do what you are looking for. Replace 'ThursdayClass' with the login you need info for. https://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/ SET NOCOUNT ON CREATE TABLE #temp ( SERVER_name SYSNAME NULL , Database_name SYSNAME NULL , ...


2

Try sp_dbpermissions. It will probably give you more info than you need but it will do what you want. Once it's installed run this. sp_dbpermissions @dbname = 'All', @LoginName = 'LoginName' Fair warning at the moment it does a "like" match so if other logins are similar and match then you will see them also. For example MyLogin and MyLoginForThis will ...


2

Sadly you are going to have to iterate through all of the databases in order to get the information. You'll want to join sys.database_principals to sys.server_principals for each database matching on the SID. Don't use sp_msforeachdb as it is known to miss databases at times. Self plug, I did something in PS for auditing permissions that might be a ...


0

about the transaction log I personally initially set it to 10% of the database size, and keep monitoring to get to the right usable amount (you want to avoid autogrowth). according to this article by Kimberly L. Tripp, you should grow the log in multiples of (not greater than) 8 GB and keep an eye on the number of VLFs. this is the way I generally get this ...


0

No auto shrink can't permanently damage a database. It may lead to highly fragmented tables and indexes which may hurt performance. The fragmentation could be difficult to remove completely, but minor fragmentation itself shouldn't be a concern. You need to assess whether this is really an issue for you worth spending time and effort to fix.


0

There is nothing wrong with setting the initial log size to 70GB, if you need that large of a transaction log. There is a problem, however, with it set to grow in 10MB increments. Such a small increment of growth can result in numerous VLFs (Virtual Log Files) which can result in long recovery of the database during startup and lengthen restore times. You ...


0

SQL server does not trace revoke or deletes by default. In order to trace those activities, you need to run a server side trace (or XEvents as Aaron pointed out). Below trace will help you to find command (Text), start date, end date, login, database (I have filtered it for test_kin), and many other fields. Adjust the filters as per your need and ...


0

Ensure you have the right extension and path to the directory set in your variables within each object including the whereloop and connection file. *.xls versus *.xls*


3

First I'm assuming since you didn't specify that you want your primary key to be clustered. It's the default and most common usage. If not then the order of your column will depend more on the selectivity of the two columns and your usage. However, if you are making your primary key clustered then if you look in this Technet article on the aligning ...


-3

if you have identity field, PK should be on 'K1' and 'id_filed' create table T (...) on partitionscheme(K1) alter table T add primary key clustred (K1,id_field) on partitionscheme(K1) if not alter table T add primary key clustred (K1) on partitionscheme(K1) ,but the clustered index always must be on same file group as table.


7

Using TOP(n) without ORDER BY is going to return you n rows in whatever way Sql Server cares. As per the documentation: If the query has no ORDER BY clause, the order of the rows is arbitrary. In addition, there is no WITH TIES. Consider using it too, lest you might miss some rows too: WITH TIES Specifies that additional rows be returned from the ...


0

How about creating a table holding all times for a day and using it to drive the query? In this simple demo, I set up a table similar to yours, load it with data, then use the dbo.timeLookup table to drive it. The example assumes you are only working with one day, but it could be extended if your timespan needed to span multiple days: USE tempdb GO SET ...


2

You can use a Filtered Index to remove the NULL values from consideration regarding uniqueness. You would first drop the existing UNIQUE CONSTRAINT and then replace it with a UNIQUE INDEX. These two objects are essentially the same given that a UNIQUE CONSTRAINT actually exists via a UNIQUE INDEX (the documentation for creating a column constraint states ...


0

There are several authorities assuming different conditions for the first week of the year. Some assumes first day of the week starts the first week but the most common idea is that the first week which has the first Thursday is the first week of the year. So ISO_WEEK accepts that and like in 2010, 2011 or 2012 as you can check that ISO_WEEK says 1st ...


-1

Create a function for your calculation, and call that function everywhere you need that column, For example in your case; Create FUNCTION [dbo].[GetSubString] ( @Column1 AS nvarchar(MAX) , @StartIndex AS int , @EndIndex as int, ) RETURNS nvarchar(MAX) AS BEGIN Declare @value nvarchar(MAX); SET @value = SUBSTRING(@Column1,@StartIndex,@EndIndex); ...


1

In short, no it won't work. There are two types of windows authentication, local and domain. Seeing as how you're asking about your domain logon, that's clearly the second kind. When you try to authenticate to a domain logon, the local machine will go up the chain in its domain group, and ask for a domain controller to authenticate your credentials. Seeing ...


1

Plan answer: No. Let me explain: Windows Authentication is just that, authenticating with a Window's login. So if you are dealing with a workgroup computer where you are on a domain it will not work, because that server can only authenticate a local account. If you are talking about connecting with SSMS, you can try the runas coomand through command ...


0

Set the 'server name' to be the name of the computer and select Windows Authentication then this should log you on locally.


0

Order of the columns on the index matter but not in not the order of anded values on the where clause.


1

I just came across a KB article for a bug that may be related to this issue. In FIX: Performance problems occur when database lock activity increases in SQL Server (KB 2926217), one of the symptoms described is that sp_reset_connection may take a long time to complete. The hotfix is included in the following updates: Cumulative Update 17 for SQL Server ...



Top 50 recent answers are included