Tag Info

New answers tagged

0

I think this is a pretty good solution. It uses a union all between two selects with Not Exists sub-queries. Better than combining Union and Except because you can include fields that don't match in the projection. SELECT 'A' TableName, FileType FROM KFX_Inventory I WHERE Not Exists (Select Top 1 1 from KFX_FileType FT WHERE FT.FileType = ...


4

You can create a trigger to update the UserID columns in related tables, such as: USE Tempdb; CREATE TABLE dbo.Users ( UserID INT NOT NULL CONSTRAINT PK_Users PRIMARY KEY CLUSTERED , ApplicationID INT NOT NULL , Country VARCHAR(255) NOT NULL , DefaultTenantID INT NOT NULL ); GO CREATE TABLE dbo.Tasks ( TaskID INT NOT NULL , ...


2

The validity depends on how users want to look at the data. You are looking at it as just a transaction fact. Other types of fact tables include periodic snapshots and accumulating snapshots. If you want to see all the times that someone corrected a row to help decrease erroneous entries, the effective dates may be appropriate so it's clear that the ...


1

Seems to me its redundancy for future extensibility. There may not be any other use for TRUNCATE currently, but one could be added in the future. Or at least that would have been the mindset of the developers.


2

In SQL Server, you can create file groups that consist of 1 or more files. You can then migrate data into those file groups individually by rebuilding the clustered index for each table on that file group. You can also do the same for indexes by rebuilding the index by specifying the desired file group. You can use file size caps to ensure the files do ...


0

where will that data be saved by default ? Agent history clean up: distribution calls EXEC [distribution_db_name].dbo.sp_MShistory_cleanup @history_retention = 48 This PROC deletes from MSsnapshot_history , MSlogreader_history, MSdistribution_history, MSqreader_history and MSmerge_history and bunch of other tables. This is the entire code for a ...


0

First thing if transaction log is not damaged you should go for tail log backup. That would cause minimal data loss. Then restore would be Weekly full backup with no recovery 10:05PM Differential backup with no recovery All log backups after this differential backup with no recovery and finally tail log backup with stopat command with recovery Please ...


8

Personally, whenever I build a new server for a new project I always enable TF4199 globally. The same applies when I upgrade existing instances to newer versions. The TF enables new fixes that would affect the behaviour of the application, but for new projects the risk of regression is not an issue. For instances upgraded from previous versions, the ...


3

You need to restore last FULL backup, the Differential, the 10:15 transaction backup and the 10:30 one with the STOPAT = '2015-05-22 22:24:00' clause.


2

Would this cause any impact on application side? Even if you run BPA or Upgrade Advisor, they are not going to catch everything that would potentially break your application (if it is using deprecated stuff). The best way is to take a full backup of your database in question, restore it on a test machine with newer compatiblity mode and do a regression ...


3

RANK() OVER (PARTITION BY xxx, yyy ORDER BY zzz) provides a number ranking for each row where xxx and yyy are identical, and orders that list by zzz The sub-query contained in your question: SELECT Sr_No FROM ( SELECT *, [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC) FROM dbo.LeadsContacts ) T WHERE T.Rank>1 ...


0

Create a linked server connection with read access from Server B to Server A. Have a process running on Server B that does the following: Examine the table on ServerB to identity the last inserted record from Server A. Hopefully you have an auto-incrementing primary key (int). If not, look for the greatest "last updated timestamp". Connect to Server A ...


0

Start by checking your T-SQL syntax and reserved keywords. The differences between compatibility levels are documented in BOL: https://msdn.microsoft.com/en-us/library/bb510680(v=sql.105).aspx


6

The syntax choice is likely for consistency, albeit redundant. TRUNCATE TABLE follows the same pattern as other DDL statements (CREATE, ALTER, DROP), where the object type immediately follows the action keyword. I should add TRUNCATE TABLE is part of the ANSI SQL Standard as of the 2008 version, although it was implemented in SQL Server long before then.


1

You already inserted 'DAMASCUS' and 'Alepo' values and they are unique. SQL server is rejecting data according to your rules. Try: SELECT * FROM governorate WHERE name = 'DAMASCUS'; You will found record is already there.


2

So I am wondering how this might have been broken (SQLServer update?). SQL Server update wont change any login / user or role permissions as part of update (installing CUs or SPs). The permissions might have changed due to a possible database upgrade (your application version upgrade). Check your upgrade scripts. To find out who changed the ...


6

Developer Edition is free No its not free, but its very cheap. For all editions, you can check here. If you want to explore sql server (2014,2012,etc) then you can download SQL Server Evaluation Edition for 180 days and then purchase it.


1

I tend to use OFFLINE strictly because on active instances trying to set a database to SINGLE_USER and be the single user that connects is not always consistent. If you set it offline that is it, it is offline. I will also add that a better practice to do the restore is to have the application stopped or block access from the server. This will save from ...


0

I have create following Procedure which goes thru all SP and Views in particular DB (can be extend to Functions, ...) and store each code script one by one into TXT files. Tested ON MS SQL 2008 R2 and 2014 First part Inserting all scripts of SP and Views into Temp table. Then later using BCP utility. If you wish you can can use for export SSIS package and ...


1

The last LSN of the FULL BACKUP should match the Last LSN of the log backup. This will be your first log backup and then after that the consecutive log backups will be having A.LAST_LSN = B.FIRST_LSN --> where B = log backup taken immediately after A. From BOL : Now look at the error you are getting : The log in this backup set begins at LSN ...


1

You are trying to apply a Log file that finish with LSN 421814812000000025600001 into a DB restored with LSN 421787067000000013800001 , so, the LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file would not possible to apply , being a change in sequence. So possible cause here being that you are not ...


4

I would set it offline. I say this because SINGLE_USER mode is generally used to allow an administrator to perform some kind on maintenance on a database without users attempting to access and possibly interfering with the maintenance. It doesn't really matter either way for a restore as your users will experience the same thing which is they wont be able ...


0

Assuming you're working with read committed isolation level (=the default), then the deadlock will happen when a reads the row, then b reads the row, then a tries to update it and then b tries to update it. In addition to deadlocks you might have another problem if you're dealing with the same rows and the process isn't that fast, since the second process ...


5

Sure : select * from database_n.dbo.Table_1 UNION ALL select * from database_n+1.dbo.Table_1 UNION ALL select * from database_n+2.dbo.Table_1 -- etc.


2

No buffer pool and VAS are not the same thing. In SQL Server direct physical memory access is not allowed any memory request which comes is first mapped to process VAS and then if SQL Server find memory free it would map this VAS address to physical memory and then memory becomes committed. Bufferpool is physical memory VAS is virtual memory. VAS VAS is ...


0

Based on the answer of Mark, I was able to define the exact query as it follows: INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid) SELECT username, max(name), max(startdate), max(enddate), max(saledate), 41 FROM subscription group by username


0

The problem is that the query has to retrieve 13.7 billion rows from FIELDDATA in aggregate. This is caused by two significant issues with your query. One will be easy to solve, one will be harder. The easy problem is with the index. As one commented suggested, you need to add a nonclustered index on FIELDDATA(ID, FIELDID). The clustered index scan is ...


2

No loops are needed in this (please!). You should be doing an INSERT...SELECT. Always run the SELECT part of the statement on it's own first, to make sure it is only selecting what you expect to be inserted. INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid) SELECT DISTINCT username, name, startdate, enddate, saledate, 41 ...


0

I believe since you have not opted for a default instance and named it, you have created a named instance: So you should be able to connect to this named instance. Please refer to this MS article on how to connect the instance https://msdn.microsoft.com/en-us/library/ms165614(SQL.90).aspx Also, just a add on: Consider the following information when you ...


3

The user account that is running the SQL Server agent might not have permissions to use the database mail profile you can check that with EXECUTE msdb.dbo.sysmail_help_profileaccount_sp; But you are having some issues with the agent account security, please try the following steps. Remove all extra privileges from the Agent user account (or create a new ...


0

Execute the following: select SERVERPROPERTY('Edition') SELECT name, cast(value_in_use as int)/1024 from sys.configurations where name = 'max server memory (MB)' If the first query returns standard or web edition you are stuck and need to upgrade to the enterprise edition. The second query returns the set maximum size of memory that the engine is ...


0

Possible cause you may be seeing this error is: Either The BUILTIN\Administrators has been removed from the Logins (Security-> Logins) section or BUILTIN\Administrators does not have sufficient priviliges to access the SQL server. As per MS The issue does not occur if the SQLAgent service is starting under a domain or computer account that is listed ...


0

Well, it depends on the application but, yes you will have retrieved the records and presented them. If you don't need to access the temporary table again you can drop it.


0

In SQL Server you could do the following, some of this will not work in MySQL. ;WITH MinPopulation AS ( select distinct name as name ,CountryCode as CountryCode ,SUM (population) over (partition by name,CountryCode) NumberOfInhabitians ,AVG (population) OVER (partition by name,CountryCode) AverageNumberWithinDistrict from population) ...


0

It was seriously LOL. I gave NetworkService user full control to mailroot folder residing in C:\inetpub\ and Vola email was sent from localsmtp. I bet if someone has faced this problem and has find any solution like same. Reason OF Solution - login context/User that is rendering report on ReportServer and creating an email template to throw it in pickup ...


5

The CPU time is tiny compared to the amount of actual time. The second time the query runs, it's fast - presumably once the data is in RAM (being one of the few things which benefits from the second run). Sounds to me like the problem isn't SQL, but the disk. Notice the PAGEIOLATCH waits increasing while your query runs. Have a look at what's happening on ...


1

How Connection Pooling works The issue with connection pool fragmentation is that each connection pool is bound to the exact text of the Connection String, including the SID of the Windows Login when using a Trusted Connection / Integrated Security. The connection pool works because the connection is exactly the same in every way. Hence, changing the ...


0

If you use SQL Server 2008 R2 Standard Edition, you are limited to 64GB RAM per SQL-Server instance. Memory Supported by the Editions of SQL Server


0

You have to read it carefully. It means you want to have an sql user for each webapp but only one pool per user. For example: if you have a forms authentication for webA(.. N) to the generic authentication database AuthDB and use userA(.. N) and(!) connect with userA(.. N) to the webA(.. N) DB you will get two pools for each webapp A..N because of the ...


3

If you are running SQL Server 2008 R2 SP1 and up then you can query sys.dm_server_registry to find out TRACE FLAGS that are enabled at startup. select * from sys.dm_server_registry where cast(value_data as varchar(max)) like '%-T%' Just incase if you or future visitors need the output in one row then, you can use below T-SQL : select distinct ...


1

Not in a T-SQL function, but executing stored procedures can be done in SQLCLR functions. An example of using a .NET / C# SQLCLR user-defined function to execute a stored procedure is shown in the following article (which I wrote): Stairway to SQLCLR Level 2: Sample Stored Procedure and Function


0

I have found the solution to my problem. My "server name" part of the sproc was being loaded from a column in the database which included the port number (because it is a named instance), therefore it read [Servername\Instance,Port].[database].... but when I removed the port it worked. Demonstration below. USE [master] GO CREATE DATABASE TestDatabase go ...


2

The data should be "the same" as on my machine, that I can dismiss my server and point to new machine in the end. Based on your comments, your best option would be Database Mirroring or Log shipping. You have to evaluate your RPO and RTO. Since you are using VPN to your actual machine - meaning it is point-to-point connection : If you go with ...


2

It sounds like you're trying to setup a disaster recovery environment, so that if your local machine blows-up you can switch over to the Azure machine. There are a number of options, each with their own pros and cons. These are well documented elsewhere (see this link for an overview from Brent Ozar: ...


0

Create dummy, empty tables on the local server so that the procedure creation can happen. Change the procedure code to use two-part synonyms. On the server that needs to use the linked server: CREATE SYNONYM dbo.whatever FOR linkedserver.dbo.whatever; On the local server that doesn't need the remote references to exist: CREATE SYNONYM dbo.whatever FOR ...


0

After further investigation, I found the answer. I ran Exec sp_delete_log_shipping_primary_secondary @Primary_database = N'DATABASE_NAME' ,@secondary_server = N'SECONDARY_SERVER_NAME' ,@secondary_database = N'SECONDARY_DATABASE_NAME' To delete all reference to that server. followed by EXEC sp_delete_log_shipping_primary_database @database = ...


2

It's not supported, but you can get around it.. select * from openquery( LinkedServerName, 'exec [database].dbo.sproc @Id=@Id, @PItems=@PItems output'','' @PId bigint, @PItems xml output'', valueOf@PId, valueOf@PItems output' ) There ...


0

Yes it is possible to set one of the instances to run on port 1433. It is also safe as the dynamic ports will be set in the range of 49152 and 65535 as stated above. You use the configuration manager to set one of the instances to start at port 1433 (clearing the configuration for the dynamic port) and it will run on that port afterwards.


0

I suggest you to use CASE WHEN statement like this: .... WHERE 1 = CASE WHEN <criteria1 is true> THEN 1 ---> (1=1) is true WHEN <criteria2 is true> THEN ---> <also criteria1 is false> ----v nested CASE WHEN <criteria2-1 is true> THEN 1 ...


1

If you need to execute sql file use sqlcmd documented in the MSDN: Run Transact-SQL Script Files Using sqlcmd This did the trick for me: sqlcmd -S serverName\instanceName -i script.sql -o output.txt



Top 50 recent answers are included