Tag Info

New answers tagged

0

I would suggest that @@TRANCOUNT=0 because [@@TRANCOUNT] Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. If your INSERT or UPDATE statement is not part of explicitly defined transaction, then @@TRANCOUNT is not incremented. Try in SSMS: SELECT @@TRANCOUNT BEGIN TRAN SELECT @@TRANCOUNT ROLLBACK ...


1

Straight to answer your questions : Can I stop, pause, or cancel the backup after it has started if it is taking too long or slowing things down too much? You cannot pause a backup - either running using Tsql or using sql agent job. You can stop it or kill it using the kill spid command. you have to be cautious as if you kill a job that is already ...


0

I will be assuming that you are using native backups as you have not mentioned a specific product. However, depending on what backup product you are using (Litespeed, RedGate Hyperbac, etc) you may be able to stop a backup but it may not be as simple as clicking stop from within management studio for an Ad-Hoc backup command. For a native backup the engine ...


0

When you drop the index, the database is in the same state as before in terms of indexing. If you want to go back to a previous state then you'll need to get a backup for that time range. Depending on the requirements it's either just the FULL Backup or additional DIFFERENTIAL Backups or even a point-in-time recovery using the transaction logs. But the ...


0

@JeremieGrund - If the database physical file architecture is same( data & log file drive names & locations), then if you shrink the log file on the principal, the shrink command will be sent to mirror and do the same on mirror. So in this way your mirror log file can be maintained.. If the files architecture is not identical then you should follow ...


2

From SQL 2012 onwards, there is the WITH RESULT SETS option, which makes it easy to use the output from the OUTPUT clause with no sticky issues in SSIS not understanding the metadata for the resultset, eg you can use the output from an OUTPUT clause in SSIS Source components, eg OLE DB Source. I set up a simple example using a stored procedure with MERGE ...


5

You can't, ish, is the short answer. You have 3 ish components in a data flow task that can modify data. OLE DB/ADO.NET Destination, OLE DB Command and a Script Component. I'm ignoring Script Component for this answer as it's the swiss army knife of components. If you want to do something badly enough, the Script Component will allow you to. Whether ...


2

SQL Server optimizer does constant folding, when possible. But is not a black-or-white issue, there are many shades of gray. See Compute Scalars, Expressions and Execution Plan Performance or Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation. You also need to read Conor vs. Runtime Constant ...


0

I guess this cuts it insert into trip values ('point1', '10/15/2014 09:10:10') ,('point2', '10/15/2014 10:15:00') ,('point3', '10/15/2014 10:20:00') ,('point2', '10/15/2014 11:10:00') ,('point1', '10/15/2014 12:10:00') ,('point1', '10/15/2014 12:15:00') ,('point2', '10/15/2014 13:15:10') ,('point3', '10/15/2014 ...


1

A filegroup restore can be done on a database in FULL recovery model. There are requirements and consequences of the restore. You must be using Enterprise Edition. The primary filegroup must be the first restore. Filegroups that are not yet restored remain offline. Start reading at this topic in MSDN: ...


1

The following code will restore the database to the desired location: RESTORE DATABASE x FROM DISK = 'Filepathofbackupfile.bak' WITH RECOVERY, MOVE 'logicalDatafilename' TO 'Newfilepath.mdf', MOVE 'logicalLogfilename' TO 'Newfilepath.ldf'


0

Here are your duplicates, then everything else is your non-duplicate. with c as ( select *, row_number() over (partition by PostSur, Name order by ID) as rowID from Aron_Reporting.dbo.Customer_NewSort ) select c.* from c inner join c c2 on c.PostSur = c2.PostSur AND c.rowID > 1 AND c2.rowID = 1 as rowID order by PostSur This ...


1

As part of a different issue I had to give my login the sysadmin role. I haven't seen the problem I described above since then. Yes, it may work now, but I am not happy with this solution, partially because I don't quite understand what was going on. Later edit: As pointed out by Thomas in a comment, here's what's going on: If the database exists, ...


0

This KB isn't directly related but the quote in it is. http://support.microsoft.com/kb/2615182 When Windows Server Backup attempts to backup a disk volume, a Volume Shadow Copy Snapshot is created for the volume. When the snapshot is created any VSS writer associated with the volume is called. If there are SQL Server VSS writer backups being taken ...


0

When all windows user accounts with sysadmin role or sa account are disabled in SQL server then how to access the instance? Below are the steps to solve this problem: Stop existing instance of SQL Server on which all windows user accounts or sa account are disabled Start SQL Server instance in single user mode with following steps 2.1 Open command ...


0

Same problem here, this is how I solved it find running job NAME with this somewhere in command 'Replicate_ASG_Reference_Data' use this SQL: USE master GO SELECT job.job_id,name,command FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id WHERE job.enabled = 1 AND command LIKE '%ASG%' this SQL will show 1 or ...


1

If you need to change the data type of a column, you'll have to change every data type referencing that column, too. At the database level, you'll have to change any column referencing this column. Since you need to change a PK column, this will affect all other tables where this column is used as a foreign key reference. In all of those tables you'll need ...


0

You can use GROUP BY and SUM of particular column value. SELECT EmployeeName, SUM(Salary) FROM YourTable GROUP BY EmployeeName


1

you can create a table variable(or temp table) and insert all the .bak file metadata in that table and then fetch the row with max value of modified time. DECLARE @FileMeta table (columns nvarchar(200)); INSERT INTO @FileMeta EXEC sys.xp_cmdshell 'dir D:\FolderName\*.bak'; SELECT * FROM @FileMeta WHERE columns not like '%volume%' AND columns is not ...


0

main table for this purpose is msdb..restorehistory SELECT [rs].[destination_database_name] , [rs].[restore_date] , [bs].[backup_start_date] , [bs].[backup_finish_date] , [bs].[database_name] AS [source_database_name] , [bmf].[physical_device_name] AS [backup_file_used_for_restore] FROM msdb..restorehistory rs ...


1

Assuming your largest range will be 366 days (and you can adjust that number), I think this approach will scale better than a recursive CTE or a while loop. This only handles days, because it's not quite clear to me from the question what the output should look like, exactly, when the range is months or years. ;WITH x AS ( SELECT TOP (366) n = number ...


2

This will allow the defaults to show up in the lower pane of the table designer: GRANT VIEW DEFINITION ON dbo.tablename TO [username]; Of course this doesn't give them the ability to make any changes to the table, and in general I discourage the use of the designers anyway. They're littered with bugs and inconsistencies. With VIEW DEFINITION rights, your ...


1

OK, I've refactored (hopefully for the last time) for your new data and to use a calendar table. In this simple demo I create and populate the calendar table (for years 2000 to 2030), then do the day|month|year code. This version honours the time component and will scale well. The calendar table is one I use in my dev. Please note this is intended as a ...


0

This should do what you need. DECLARE @Interval NVARCHAR(10), @I DATETIME, @IntervalMin DATETIME, @IntervalMax DATETIME --- Set Interval DAY or MONTH or YEAR ----------------- SET @Interval = 'DAY' ------------------------------------------------------- DECLARE @Records TABLE (StartDateTime DATETIME ,EndDateTime DATETIME) INSERT INTO @Records ...


1

Until we see the actual code, we can only speculate but for what is worth, I'll do a guess. Assuming that your code involves only a table and its columns (no @variables involved) and is something like: UPDATE tablename SET column_a = column_b, column_b = 0 WHERE (condition) ; It is valid SQL and 100% correct. Correct, meaning that ...


0

You can use REPLACE on the date column in the @PivotColumnHeaders string to build the concatenated column. SELECT @PivotColumnHeaders = COALESCE( @PivotColumnHeaders + ',[' + cast(SystemFullName as Nvarchar) + ']', '[' + cast(SystemFullName as varchar)+ ']' ) FROM System WHERE (@SelectedSystemIDs IS NULL OR System.ID IN(select * from ...


1

Microsoft also has a nice TechNet article, Disk Partition Alignment Best Practices for SQL Server, that discusses Disk partition alignment as it relates to SQL Server 2008. In most cases, 64KB is the best choice. Best recommendation would be to make sure your partitions are aligned, test your I/O performance and if Latency and other specs are not to the ...


1

Use your original query as a derived table and outer-join it to a dummy row, referencing only the query's columns in the main SELECT: SELECT q.* FROM (SELECT NULL) AS d (dummy) LEFT JOIN ( your query ) AS q ON 1 = 1 ; This is almost like a cross join with a single row, except in this case the final output will always have at least one row. If ...


1

Using NOT EXISTS should work, eg SELECT * --!!TODO add column list FROM dbo.CompanyDomains cd WHERE CompanyId = @CompanyId AND NOT EXISTS ( SELECT * FROM @domains t WHERE cd.Domain = t.domain )


0

If you don't want cdc clean up to run, do not run the system stored procedure to enable it. Then the sql agent job won't get created. exec sys.sp_cdc_add_job 'capture' GO FYI - I have observed disabling CDC clean up long term may cause latency issues within your database. (I would assume you might have that issue too)


1

What I'm totally stopped on is I don't see any equivalent of a List is SQL. Is there a way to build all this up in a list? So a list in SQL Server is basically a table. You can use table-valued parameters to pass in your complete list from C#. You basically just create a table type to define that list you are passing in, see the example in the MSDN ...


6

Add a persistent calculated field that contains a CHECKSUM on the 5 fields, and use that to perform the comparisons. The CHECKSUM field will be unique for that specific combination of fields, and is stored as an INT that results in a much easier target for comparisons in a WHERE clause. USE tempdb; /* create this in tempdb since it is just a demo */ ...


1

The big reasons for the "Keep the clustered key sequential" advice is all around insert/update/delete operations and fragmentation. As you make changes to the values (insert new ones out of order, updating an old value that causes it to move, deleting a value) SQL has to move data around on the pages and do page-splits to keep the data in order (the whole ...


0

Because your condition not correct. You change the condition + CASE WHEN @Store = 'MA000' THEN '' ELSE 'AND rtiersS.alias = ''''' + @Store + '''''' END + to + CASE WHEN @Store <> 'MA000' THEN ' AND rtiersS.alias = ''' + @Store + '''' END +


0

I solved - add defaults to parameters and Report Builder can detect columns Its a Report Builder bug - it not ask for parameters when refresh fields IF @Store IS NULL SET @Store = 'MA000' IF @Date IS NULL SET @Date = '19092014'


1

Check out SET CONTEXT_INFO and CONTEXT_INFO(). This lets you set and retrieve 128 bytes of connection-specific metadata (as a varbinary(128) value). So, after the connection is opened, you can do this: DECLARE @ci varbinary(128) = CAST('myhostname.domain.com' AS varbinary(128)) SET CONTEXT_INFO @ci Then in your triggers, you can see if the value has been ...


1

As far as I'm aware, you can't manipulate HOST_NAME() inside of SQL Server. And I think that's a good thing. The fact that you can spoof this in the connection string is something I've long considered a security problem waiting to happen. Instead, you will need to change the logic in your triggers. For example you could store HOST_NAME() in a variable, then ...


0

The reason this isn't working for you when you have multiple rows in the employee table is that it is not correlated back to the main query. By this I mean that SQL has no way of knowing which employee to count in your subqueries for a given row in your main query. You can avoid this buy adding a reference back to your main query like this: SELECT ...


2

A very similar question was asked and answered on StackOverflow: How can I force a subquery to perform as well as a #temp table? To summarize, The most reliable method is simply to use a #temp table and materialize it yourself. Failing that, see Provide a hint to force intermediate materialization of CTEs or derived tables. The use of TOP(large_number) ...


1

Ok I got the answer. I used the following: EXEC sys.sp_cdc_add_job @job_type = N'cleanup' ,@start_job = 0 ,@retention = 52494800; This adds the cleanup job but does not start it immediately i.e. start_job set to 0 does the magic. Retention can be set accordingly (100 years in my case as we do not want data to be deleted)


2

I don't have hands-on with this but have observed someone else go through the same thing. To keep the old history you might want to migrate it to a separate table. When the jobs get created, the default retention period is 72 hours. Unfortunately I don't know how to change that because the system procedures also run the job for you, before you have a ...


3

I see you're combining Failover Cluster Instances (FCIs) and Database Availability Groups. That's fine, except that the installer won't allow you to set up an availability group on failover cluster instances that can be owned by the same servers, just like the error message says. In other words, the set of servers that can run HRITS\BAK can't overlap with ...


8

Since it's only UPDATE,INSERT you can say: IF EXISTS (SELECT 1 FROM deleted) -- update ELSE -- insert You have a bigger problem, though. There is no such thing as new.Id, and an insert or update can affect multiple rows (in some platforms, triggers fire per row; in SQL Server, they fire per operation). So you need to either: Use a loop to call the ...


1

I had the same exact issue. My SQL job would hang and say "Executing..." and never complete. Add "--trust-model always" to your .bat file. Basically your .bat file runs when you run it manually because you're the owner. It trusts you. When you run the .bat as a different user, the cmd will actually display a warning and ask you if you trust it. ...


1

Firstly, you are correct in that the (slightly counter-intuitive) way to prevent access to the [sys] and [INFORMATION_SCHEMA] schemas is to first ensure that the login (well, server-level principal) exists as a user (erm, database-level principal) in the master database. Assume you have a SQL login for simplicity: CREATE LOGIN [testy] WITH ...


1

This looks like a "kitchen sink" stored procedure - meaning let the user search for any or all parameters. Impossible for SQL Server to generate a single, effective plan that will work well for any combination of parameters supplied (never mind their values). My first two approaches at optimization are always: Add OPTION (RECOMPILE) to the query - you pay ...


1

Yes, the primary key of the proj_user table can be a composite key of project_id and user_id. The data types of these columns can be different, such as integer and varchar in your example. The data type of these columns must match the data type in the referenced project and user tables.


0



Top 50 recent answers are included