Tag Info

New answers tagged

6

rowversion is just an alias for timestamp. Under the covers, they are the exact same thing. If you're using the GUI (prior to 2012), you have to use timestamp. (Peripheral hint: please stop using the GUI.) But it doesn't stop there. Even if you generate this table using DDL: CREATE TABLE dbo.x(y ROWVERSION); The data type in the metadata will be ...


0

Timestamp is a synonym for the rowversion datatype. You can find it in this MSDN article. Microsoft has marked timestamp as deprecated as you can see in the article mention above, but still in SQL 2012 SSMS uses timestamp even if you create the table with the rowversion datatype.


1

Check for DBCC OPENTRAN for any transaction on these tables. If NOT then you are free to KILL these sessions as these are only select statements. Find the sessions by querying sp_who2 and kill by syntax KILL :-)


1

I recommend using a Calendar table or Date Dimension (whichever name you prefer). Here is an answer with using a quick CTE. /* Date Range CTE */ -- Updated based on @AaronBertrand's articles linked in the comments -- Basically ends up being the same query as the last half of @AaronBertrand's post declare @FromDate date; declare @ThruDate date; set ...


1

Well, you should have a Numbers table or a Calendar table. I'll start with a Numbers table: CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY CLUSTERED); INSERT dbo.Numbers WITH (TABLOCKX) (Number) SELECT TOP (1000000) Number = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2; Then: DECLARE ...


1

As Max says it sounds similar to a problem we had under a similar situation, especially for those working remote to the main comapny network. Ensure that under the Access databse Options Advanced tab that the OLE/DDE timeout that the figure is set to something like 300 and not the default of 30. Also in any VBA requests to the database adjust the ADODB ...


1

No, please don't store this data redundantly - you already have all of the information you need in the base table, without incurring the overhead of triggers or additional writes. Just create an index that supports your query, somewhat similar to what @Colin suggested, but I'm adding the output columns to avoid key lookups: CREATE INDEX ix_last_position ...


0

Features Supported by the Editions of SQL Server 2008 R2 states that SQL Server Express edition can only act as a subscriber. Am I correct in thinking that that sites B & C will actually act as the publishers and site A the subscriber, in which case you will not be able to implement any type of replication. Will any DDL changes be made at site A? If ...


3

This is not definitive proof but just my opinion based on what I learned from a PluralSight course Paul Randal did on wait stats. PREMEPTIVE waits are indicative that SQL Server Operating System (SQLOS) has to change a thread to preemptive mode to handle something outside of SQL Server, at the OS level. Now as you have already determined this types of ...


5

E-mail address: I go with NVARCHAR(320) - 64 characters for local part + @ + 255 for domain name. You may also consider normalizing the domain away, since it's pretty wasteful to store hotmail.com 500,000 times when a much smaller INT or even SMALLINT is probably capable (I don't know how many domains you plan to support). I used to be against NVARCHAR but ...


1

Expanding on James' answer Try this code on SqlFiddle. Also check out @SqlKiwi Paul White's post regarding Delete vs Truncate create table tracker_live ( client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null , constraint pk_tracker_live primary key ...


0

I believe something like this will do what you want: DECLARE @Test DATE=GETUTCDATE() SELECT @Test -- compute the DATE for the last day of the previous month DECLARE @LastDayOfLastMonth DATE=DATEADD(day,-DATEPART(Day,@Test),@Test) -- dump out the DATE for the last day of the previous month SELECT LastDayOfLastMonth=@LastDayOfLastMonth -- dump out the first ...


0

For merge replication issues, first stop is to read Chris Skorlinski's blog post at http://blogs.msdn.com/b/repltalk/archive/2010/06/28/merge-replication-capacity-planning.aspx He links you to several resources at the top of his blog post. The post is from 2010, but merge replication has not had any significant update as far as I know. Chris's bottom line ...


0

It sounds like you may have overflowed the RAM required for the working set for some of the denormalization operations. High CPU usage often means lots of page reads. Check the cache hit ratio as well as the logical page reads during the denormalization process. Somewhere around when a table gets larger than available memory (or the amount allowed by single ...


0

Shyamev, Just to make sure the database selected should be a user and not any system databases. But in many cases we have seen this even if its user database is selected and the option of log shipping is missing. Solution: Re-install SSMS with the Management Tools - Complete option checked. Refer to below link: ...


3

Indeed Merge Replication seems to be the best fit for this scenario. You can also check Transactional Replication with Updateable Subscriptions with queued updates, but it is less suited for situations where the servers are mostly disconnected, and the conflict detection is quite limited. You are also correct about the versions you need: Standard or ...


5

But recently I came across some database on a server where every database had multiple mdf files. That is because of incorrect naming convention. Microsoft says that every database has one primary data. A primary data file is the data file associated when first created the database but it does not means it can have only one mdf data file it can have ...


2

I think you need to be clearer on your exact intended setup. That will allow for a better reading of those two linked questions. I would guess that those two links are actually irrelevant. SQL Server Express comes with a stripped down version of SSIS (or can be installed via the Tools or Advanced install), according to Link #2. But yet your question regards ...


2

The database on the end of log shipping has to be in NORECOVERY to accept the log backups being shipped over. A database involved in AlwaysOn Availability Group has to be removed from the group in order to be restored or set to any restoring state. So setting up log shipping to that database would require it to no longer being replicated. I would go back to ...


1

The current T-SQL I'm using to evaluate PLE vs max server memory is: /* Purpose: Returns a resultset describing various server level stats including PLE Max and Min Server Memory, etc. By: Max Vernon Date: 2014-12-01 */ SET NOCOUNT ON; /* wait stats for PAGELATCH_IO */ DECLARE ...


0

It may be due to the fact that you specified Trusted_Connection=Yes in your connection string. This will have the connection be established using Windows authentication using the domain account you're currently logged in as at the client, effectively ignoring the UID parameter. Try removing that parameter and rerunning. You may need to provide the password ...


1

After reviewing your error. I tried to setup in my TEST environment and I have received an error. If you setup connection from Excel and after selecting specific tables to test go to connection properties. You will be able yo see connection string. Try to verify it with your query and see if it is changing any parameters in connection string from your ...


4

I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging. No. Local temporary tables (#temp) are private to the creating session, so a table lock hint is not required. A table lock hint would be required for a global temporary table (##temp) or a regular table ...


3

Personally I would create another job and schedule it to run each morning at 7am. A job step within that job could execute something like USE msdb ; GO EXEC dbo.sp_stop_job N'<Your job name>' ;


2

After a lot of try and error I can't seem to be able to contain critical error in the try. The big problem here is that performing the remote insert inside the trigger involves a distributed transaction. Triggers execute inside the transaction of the calling statement; in this case, the transaction is the auto-commit transaction for the original INSERT ...


1

The challenge here is that numbers don't take into account end user experience. Great example: I have database server used to track every web site that the company employees visit. I don't care if it can't keep up with inserts during peak loads because the front end app batches the inserts off periodically, and slow inserts don't cause a problem for users. ...


0

In SQL Server 2014 you could use this two new permissions: CONNECT ANY DATABASE SELECT ALL USER SECURABLES Source For SQL Server 2008R2 you could add your user to model database or write DDL trigger for database creation action to add your user to new databases, when they are created.


0

A transaction log contains transaction details for many parallel both transactions that are (a) completed or (b) still processing. A transaction log backup is intended to harden the log file with all data needed to restore the database to the point in time that includes the last completed transaction. This also includes all the transactions still in ...


2

Although not a direct answer to the question, I'm not entirely convinced that the examples that were shown in the blog post you linked to were really a good example of SET DATEFORMAT. One of the key reasons you should use DATEFORMAT, is when a date can be interpreted in multiple ways. If you work for a global business where the date formats vary between ...


6

1) SET DATEFORMAT has no effect on the actual storage of the table. It is only used to format output and interpret strings. Behind the scenes, all the date formats are stored as integers in a canonical format. The actual representation of the data type depends on which type is used. For example, SMALLDATETIME is an integer that stores the number of seconds ...


0

LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup. (http://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/)


-1

Make sure you TCP/IP is enabled under SQL SErver Network Configuration If it is disabled than please enable it and restart services for that instance. you will be able to log-in.


1

You should use the same database for UCP and MDW. Actually, you can't use a different database. UCP is in fact a specialized collection set that uploads its data to a MDW database. The MDW database is unique for a SQL Server instance and Data Collector cannot upload different collection sets to different MDW databases. The database name for UCP must be ...


4

This type of formatting is generally best done in your application if possible. The problem is that the case expression returns a result based on the highest datatype precedence of any branch. So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar. Also you should probably add year into your ...


2

Your current method won't work, because it assumes that master was placed in the default data/log path, that it hasn't been moved at any point, and that the defaults haven't been changed since then. The registry approaches work, but they are kind of tedious (especially if you don't use the instance-specific XPs and have to provide the registry path ...


3

You won't want to use this method as the location of the master database files won't necessarily indicate the default location. I specify my master databases files to be on a separate drive, for example, while all the user databases are defaulted to another. You can use the following query from SQLandMe to find the default database locations: CREATE TABLE ...


5

Most SQL Servers are configured for the non-system databases to exist apart from the system databases. That is what is normally meant by the default path. You can lookup some code for finding this at: http://www.codeproject.com/Articles/684020/Checking-MS-SQL-Server-database-files-default-path Also, Alex Aza has provided a popular post over at ...


0

Create a batch file that runs the SQL Server installer in quiet mode with a configuration file that specifies how you want it configured. Then, have somebody put the installer and batch file on the computer you're looking for. This is where the magic happens: Have someone go into Folder and Search Options. In the general tab, set the "Click items as ...


6

There's no way to do this automatically but you can query the catalogue views to find constraint names not in the desired pattern then generate the desired script that way. Something like DECLARE @Script NVARCHAR(MAX); WITH FK AS (SELECT *, 'FK_' + OBJECT_NAME(parent_object_id) + '_' + OBJECT_NAME(referenced_object_id) ...


4

Create a configuration file for the installation. Then create a script that calls the installation using that configuration file. Technically it will be a double click though, not a single click.


1

You can solve the determinism issue using a CTE or a derived table, as follows: SELECT TOP 10 team_name, score_points FROM (SELECT team_name, score_points=<big expression> FROM teams) T WHERE score_points >= 100 ORDER BY score_points DESC Should you still encounter performance problems, probably there could be something to revise in ...


3

First I will just summarize my answer to this same question posted on Stack Overflow: No, it is not possible to intercept a query request. Even if it were possible, what you are requesting ignores the complexity of what is considered valid SQL. The variations on a simple SELECT * FROM sys.objects increases dramatically when considering spacing, returns, [ ...


0

Just changing the recovery model can take quite a while, depending on the activity in the database. If you are running a denormalization process, I'm guessing you set the database to read/write as part of the process? Then you would set it back to read only afterwards. One reason you could be seeing the overall process running much slower is that your ...


2

Views do not have datatypes; they reference columns in tables that do have datatypes. You can do an explicit CONVERT() in a View, but just like any query, it will return the underlying type. Now, doing what amounts to a "default" (especially inline with your request to use DATETIME2 even if someone tries to use another type) is a bit complicated and shown ...


2

No, there isn't a way to automatically change a datetime field to a datetime2 field if the former is specified in a table creation. To do it manually: ALTER TABLE [table] MODIFY COLUMN [column] DATETIME2; If the issue pertains to end users, education is your best bet to make the creation of tables more uniform and compliant with using datetime2.


5

Since you do not know the schema or tables names to grant them to, no to specifically doing it. You can add your AD account to the db_datareader role within the model database, and this should be copied into each database created on that instance. In addition, if databases happen to get created via restore or anything other than create database statement ...


3

So you need to make a choice: Change to Compatibility Level = 100 and break your code. Set Compatibility Level = 80 and your code will live a little longer. Find some way to globally edit your code, dynamically or statically, so that you can switch to Compatibility Level = 100. It would be interesting to understand why you want Level = 100 when you do ...


5

This can't help with the determinism issue, but CROSS APPLY is a good tool for big expressions that need to be reused: SELECT TOP 10 team_name, CxA.Score_Points FROM teams t CROSS APPLY (SELECT Score_Points = <big Expression, refer to t>) CxA WHERE CxA.Score_Points >= 100 ORDER BY CxA.Score_Points DESC CROSS APPLY expressions get calculated for ...


1

You can use sp_rename systems stored procedure to change the name of the keys. sp_rename 'dbo.PK_TableName_OldName', 'PK_TableName_NewName';


2

You can use sp_rename to change the names of constraints such as Foreign Keys. The syntax is simple: From the SQL Server documentation: http://msdn.microsoft.com/en-us/library/ms188351.aspx exec sp_rename 'YourSchema.FK_OldName', 'FK_NewName'; Note that the schema is required when renaming constraints.



Top 50 recent answers are included