New answers tagged

0

The answer was to create a user who would the super administrator of the database. So that we can distinguish between Windows authentication and Sql Authentication, in case it doesn't work !!! Thank you !


0

I'm assuming that you have no foreign key constraints on the phone column and you have a Primary Key defined: DELETE FROM foo_schema.bar_table WHERE primary_key_column NOT IN (SELECT MAX(primary_key_column) FROM foo_schema.bar_table GROUP BY phone) This should group all ...


0

Run the following command to track the corrupt pages: SELECT * FROM msdb.dbo.suspect_pages Review the suspect pages. Run DBCC CHECKDB command to check the inconsistencies of the database. Please check the PAGE_VERIFY CHECKSUM option and turn it on. Note: Also check the hardware of your machine.


1

Since you are planning to DROP a column and the indices that support it, I'll assume that doing so is a product of well-thought plan. Also, I'll assume that you want to drop ANY indice that use the to-be-deleted column, regardless of how it is used. If so, you can build your script based upon something like below. You can put this in a stored procedure ...


0

I have had this problem for some time now and finally got fed up and started writing my own script to make restoring to Dev or DR work that little bit easier. Point the script at your backup folder, it will read all the files and print to the screen the full restore scripts for each database, it works with Olas backup structure as well. Here is the link to ...


0

One would hope that the indexes were created for a good reason and not just to annoy other people who would use the database. Assuming these indexes have (or at least had) a good reason to exist, writing a script to blow them away without understanding why they were created or having a plan to replace them seems like a bad idea. Potentially a better ...


0

Are you installing SQL Server 2014 on a Windows installation with no previous version of SQL Server in? Have a look at C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn


4

I posted this in the comments section but I feel this should give you an answer that is simpler and cleaner. https://paulbrewer.wordpress.com/sp_restoregene/ Simply install the script and run sp_Restoregene. You can pass it @database and @stopat too. This script will script all the restore scripts automagically for you. Including FULLs, DIFFs and LOGs ...


2

I think this query may help you. I took it from this link. SELECT physical_device_name, backup_start_date, backup_finish_date, backup_size/1024.0 AS BackupSizeKB FROM msdb.dbo.backupset b JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id WHERE database_name = 'YourDB' ORDER BY ...


2

Here is a starting point. The first query will give you a list of roles with either INSERT or UPDATE permission. From there you will need to look at the permissions being at the DATABASE, SCHEMA, or OBJECT level and decide if they cover the objects you are interested in. Check the state_desc column to make sure it is GRANT (it probably will be) as DENYs ...


2

I had similar error few months ago and solved by this. USE master; GO EXEC master.dbo.sp_delete_log_shipping_primary_secondary @primary_database = N'<PrimaryDatabase>' ,@secondary_server = N'<SecondaryServer>' ,@secondary_database = N'<SecondaryDatabase>' GO


1

After posting this question and getting some comments about upgrading SQL Server to the latest SP, I found this KB article. The behavior described in the article seemed to be very similar to what we were seeing. We went ahead and applied SP3 + CU3 to get to the most recent version of SQL 2012 that we could. After installing the SP and CU, the problem ...


4

Sure, you can generate the list of years in dynamic SQL, but I suggest passing them as numbers (just one less thing that can go wrong if people pass in bad data, and I'm sure you didn't really mean to hard-code the values inside the procedure, defeats the purpose): DECLARE @year1 smallint, @year2 smallint SET @year1 = 2014; SET @year2 = 2016; DECLARE ...


0

My CLR SQL Server Aggregate Function (Spread) EDIT: I added my Split function to make working with the results easier. These might not be the best example. What it does is lists the unique values comma separated and can give you the occurrence count or percentage, Considering this table Use these queries to get the corresponding results. Real ...


7

What are the best or most helpful Stored Procedures for a New DBA to have in their tool kit, for SQL 2012/2014? You are asking Tools that are most useful to DBA In no particuliar order ... Brent Ozar Crew has sp_Blitz®, sp_BlitzCache®, sp_BlitzIndex®, sp_AskBrent®, sp_BlitzRS™, sp_BlitzTrace™ - This helps to do health check for sql servers. To ...


1

No*, it won't. The Package Deployment Model, if deployed to the database, will store individual packages in msdb.dbo.syspackages90 (SQL Server 2005) and msdb.dbo.sysssispackages (SQL Server 2008+) The Project Deployment Model, deploys to the SSISDB. There it takes the form of Folders/Projects/Packages which are stored in SSISDB.catalog.folders ...


7

Some alternatives to adding the auto-increment column via the IDENTITY() function as suggested by @Shaneis are: Create the table explicitly using CREATE TABLE instead of using SELECT INTO. I much prefer this method as it gives you complete control over the Table that is being created, such as including the auto-increment column and specifying that it be ...


7

I wonder if this imbalance of the number of rows maybe a result of the Timeout Almost without exception, no. An initial cardinality estimation is performed before optimization begins. Subsequent optimizer transformations may require new estimates to be computed. There is no general rule to say which of the estimates will be more "accurate". See this ...


3

Ensure you have a valid backup; hopefully it will be from prior to the corruption, but not so long ago that the data isn't useful. You should set this aside in case direct repair isn't possible and you need to recover data from the backup. The documentation explains what to do to correct the problem - you can try the REPAIR_REBUILD option, and if that ...


0

I've never had a corrupt page before. Can you provide any information from the dbo.suspect_pages table? Please see the below resources: Brent Ozar on Corruption SQL Server Central Corruption


4

Sounds like you are looking for the IDENTITY() function: Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. USE [MYDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[spSchedula_Scadenzario] as begin drop table MYDB.dbo.tmpTable select -- Create new ...


0

It appears that the dates you are clustered on are not being inserted sequentially. If your date field was recent purchases for example they would be inserted at the 'end of the table'. Because it is taking so long to do the insert it seems that SQL is having to reorder your data with each insert instead of tacking it on the end. Determine if your ...


4

A good option for you, I would imagine, is to split table logically, so every section would be in its own folder and have under 300k files(=records). In SQL Server logical table split is usually done with Partitioning. Partitioning essentially maps sections of table (partitions) to physical storage (file groups). Various partitions can be assigned to ...


4

Without further info, this is more of speculation but judging on what we have: a table that is quite wide (1.3 to 4.0 rows per page on average) the query that is slow is using: only PWFID on the join condition, two columns Title, SITime on the select list and no other column anywhere (WHERE, HAVING etc.) Then a covering non-clustered index on ...


0

I would investigate the following scenario: Setup a failover cluster to take care of the HA requirement and set up a third box with database mirroring to offload the reporting part. Take a look at these articles for more details: https://msdn.microsoft.com/en-us/library/ms191309(v=sql.120).aspx and ...


1

You just installed SQLManagementStudio_x64_ENU this is SQL Server management studio. This is a tool used to connect to SQL server database engine. Since you have not installed SQL Server database engine you cannot connect. You have to browse This Link Click on download Then you would get something like below Downlaod the files marked in rectangle. ...


2

If this is a job consisting of a Transact-SQL step, you can specify a SET TRANSACTION ISOLATION LEVEL statement at the beginning of your Transact-SQL script: SET TRANSACTION ISOLATION LEVEL SNAPSHOT; … /* the rest of your script */


0

Were you able to get this sorted out? If not , try the following: Go to the Failover Cluster Manager ( cluadmin.msc from Run). Expand the Cluster name ( Expand meaning click on the + sign ). Expand Services and Applications. You should now see your Availability Group listed there. R-click on that and go to Properties. In the General Tab you should see ...


0

I had the same question, and even though I've been a DBA for a while, I haven't really dealt with replication deeply enough to be completely comfortable with it, so I thought the following resources and guides were helpful: This blog, which provided a good outline of the process. It also reminds us that, if you have a large existing publication, and it's ...


5

Instead of doing an expensive CROSS JOIN plus aggregating a potentially large number of rows you can use this approach: SUM the days including the duplicates and subtract the number of rows with duplicate dates. ;with cte as ( select *, row_number() over (partition by ToolId order by StartDate, EndDate) as rn from ...


5

You can avoid the expensive recursive CTE by using a date table. These are easy to construct and very useful. I used a variation of the code here to generate one. Note: I'm using a temp table for the date here. This is just for the demo code. The permanent solution should use a permanent date table. It should probably also include some/all of the ...


4

A couple of things that have caught you out here. In the SQL Server version using the Point, the order of the coordinate is Lat Lon, eg Y X. The OGC Point construct is X Y or Lon Lat. The next is that the PostGIS query you've posted actually returns 8.20039023523232e-005 which while appearing similar to the SQL Server result, is quite a lot smaller. The ...


0

Since you recently upgraded a few months ago from SQL Server 2005 EE to 2012 SE, you should save off and clear your event logs because SqlAgent may crash when it sees Event Log IDs from an older build of SQL Server. For example, the Agent may crash because it reads an event logged by a SQL Server build earlier than the current build, which only contains data ...


2

I've used Openrowset a number of times for this task. This code will create a table in SQL. SELECT * INTO EXCEL_IMPORT FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1', 'SELECT * FROM [Sheet1$]'); Ideally you want to create the table first and then use INSERT INTO instead of the SELECT INTO. ...


6

I think this will get you what you want. It creates a recursive CTE (common table expression) that generates all the dates between January 1 and March 1. It then joins those dates to the tool rental data, checking to see if each date record falls between the rental dates. This gives you one record per toolid for every day within the range of rental ...


-3

try this SELECT ToolId,SUM(RentalDays) As RentalDaysSum FROM #tmpToolRentalDays GROUP BY ToolId , Cast(StartDate as DATE)


-2

it is better to install the latest SP as it is supposed to include all previous SPs and the CU from the previous SP till this last one Some thoigh, SPs do not include ALL the CUs. If a specific one is fixing an issue you face, then install it. here is a list you may find useful http://sqlserverbuilds.blogspot.ie/


0

Provided customer_users has ID and the order needed is customers.customerid , customer_users.ID you may ask sql server to show NULL in all but first row in a partition. Adjust ordering as needed. SELECT '' AS campaigndaysremaining, COUNT(*) OVER () AS totalrecordcount, *, [show CustomerId]=CASE ROW_NUMBER() OVER (PARTITION BY C.customerid ORDER BY CU.ID) ...


0

This is login and permission related error. What kind of account are you using to connect when you build the AOA features? If you are using a domain account you can fix this error it in two ways. Use the same domain account on all of the participating replicas. If you can't (allowed) change the account you could use the second option Add the service ...


1

It's not really how SQL Server works and I think you're going to see some underwhelming real life implementations of the data distribution process. Many data publishers (post offices, automotive and parts suppliers) will publish a set data format document along with full monthly data extracts usually in a CSV or similar text format followed by ongoing ...


0

You could try : select distinct parentId, substring(( select ','+cast(childId as varchar) from MyTable t1 with(nolock) where t1.parentId = t2.parentId for xml path ('') ),2, 1000) as childId from MyTable t2


4

You can use a pattern like this: SELECT files.database_id, db.name AS DatabaseName, STUFF((SELECT ', ' + names.name FROM sys.master_files names WHERE names.database_id = files.database_id FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)') , 1, 2, '') AS NameList FROM sys.master_files files ...


3

TailLog backup contains the transaction log that was generated after the last log backup.so this is necessary if you want to restore to latest point in time.When we try to restore the database which is online and whose tail log has not been backed up ,SQL Server will throw the error you are getting To avoid this error use NoRecovery. But with above ...


2

Something like : select t2.* from table2 t2 join table1 t1 on t2.rowdate between t1.fromDate and t1.toDate where t1.cat = 1


15

From Express Edition: SQL Server Express includes 10GB of storage per database and from Features Supported by the Editions of SQL Server 2014, Cross-Box Scale Limits: Maximum relational database size: 10 GB This latter reference makes it clearer that the 10GB limit per database is applied to relational data, so it excludes log files* as well as ...


1

The more common and accepted way is that once a job lands on a server in an AG you start editing it: Add a first job step that checks whether this is the primary. Exit on success if it isn't. Otherwise continue onto the normal job step. This has been explored in a few other SE questions (they don't specifically relate to CDC which is why you didn't find ...


11

I think the following query will at least get you quite close. It makes use of a DMV that was introduced in SQL Server 2014: sys.dm_exec_query_profiles (and thanks to Martin Smith for introducing it to me via this related DBA.StackExchange Answer: Progress of SELECT INTO statement :-). Please note: !! You will need to add SET STATISTICS PROFILE ON; or SET ...


2

The server has too little memory. 16GB is the amount of memory found in a high end tablet. That said, this may be secondary because varbinary aside, the database is quite small. But even that is overloading the server memory. This is quite important because of... For most database use, disk speed is paramount, CPU does not matter (because memory as cache ...


7

This table should have a clustered index on UserId. If there is no more than one thumbnail per user, then the clustered index can be the primary key. Since you say you have a currently-running system on SQLServer 2012 standard edition, online index builds are not possible. You could wait for a low-activity period to create the index, or create a new table, ...



Top 50 recent answers are included