New answers tagged sql-server-2008-r2
1
An addittion to Mark Sinkinson's excellent comment:
NOT IN requires you compare a single column from one table with a single column from another table or subquery.
You can, actually, perform NOT IN with more than one column.
E.g. this is a prefectly legal* SQL query:
SELECT E.first_name, E.last_name
FROM employees E
WHERE (E.first_name, E....
2
On SQL Server 2008 R2, SELECT...INTO queries are not eligible for parallelism (this was introduced in SQL Server 2014). So the increased CPU count in production will not help your overall runtime unfortunately.
You could re-run your test with a pre-created BACKUP_TABLE, and then use INSERT INTO...SELECT to see how parallelism affects the test.
Regardless ...
0
there is a built-in feature for this that requires minimum maintenance and is very fast except it works with timestamps.
if you can work with timestamps instead of the version I highly recommend this over manual work.
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017
2
I know that it's old thread, but I think it will be beneficial to share Paul Randal's post here.
Algorithm Speed
An index rebuild will always build a new index, even if there’s no
fragmentation. The length of time the rebuild takes is related to the
size of the index, not the amount of fragmentation in it.
https://www.sqlskills.com/blogs/...
3
You cannot use an alias in your where clause
Without questioning any parts of the query, you could do this:
SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount
FROM BASE_Chq
WHERE (IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') = 'PARTY');
A better approach would be directly filtering on the IS NOT NULL:
...
-1
Execute both queries to reset auto incremented id
string Query1 = @"ALTER TABLE [Your Table] DROP COLUMN id";
string Query2 = @"ALTER TABLE [Your Table] ADD id int IDENTITY";
0
Workaround: The accepted answer seems to indicate that the conversion needs to happen locally because the OLEDB driver doesn't support it.
So I think a simple workaround (at least in the case of my query which is selecting a null uniqueidentifier in the base case of a recursive CTE) is to declare a null variable:
declare @nullGuid as uniqueidentifier = ...
3
You mentioned database and server-level settings are the same, but how sure about that are you? And is it possible something is changing a setting at the session level?
This produces the error for me (of course):
SELECT CAST(40000 AS smallint);
But this does not - it just returns NULL as the value:
SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
SELECT CAST(...
-1
Using the bulk export feature of SSMS, here is the best and easiest method mentioned
SQL Server import and export wizard:
The SQL Server Import and export wizard provides a graphical user interface onto a SQL Server Integration Services (SSIS) package. Once created the package can be automated, to run on a schedule. It can be further configured and ...
1
This should do the work. I tested it in my test environment, works for me.
USE [master]
GO
CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TutorialDB]
GO
CREATE USER [TestLogin] FOR LOGIN [TestLogin]
GO
GRANT CREATE TABLE TO [TestLogin];
GO
Create a Login on the server with public ...
0
Firstly, if they are installing their application then surely it would require their own database and not access to the other databases on this server?
However, granting their login member of the dbcreator server role allows them to CREATE new databases on the server.
If you need to allow them to create a table in an existing database then I'd suggest that ...
0
I have used a Domain Account for Snapshot Agent and Log Reader Agent,
called SQLAgent.
If you check this answer:
The process could not connect to Subscriber
you will see that the SQLAgent account must have a login on your ServerB and must be a db_owner on your subscription database there.
It does not need to be a sysadmin on your ServerA.
It has ...
2
It's kind of a barebone question, but...
@DateToUse is the date you are searching for.
@StringOutput is the cto column.
I'm using the ID field as a tie-breaker in order to protect against duplicate or overlapping ranges in from/to fields.
DECLARE @DateToUse DATE
DECLARE @StringOutput VARCHAR(50)
SET @DateToUse = '3/2/2019'
SET @StringOutput = (...
4
Supported?
I know this is tagged as 2008R2. Since that's officially out of support, perhaps an upgrade is in your future. If you end up on a version of SQL Server > 2012, you can use code like this:
CREATE OR ALTER PROCEDURE dbo.dynamic_temp ( @TableName NVARCHAR(128))
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #t ( Id INT );
DECLARE @sql NVARCHAR(...
Top 50 recent answers are included
