Skip to main content

Questions tagged [sql-server-2008-r2]

SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

Filter by
Sorted by
Tagged with
1 vote
1 answer
460 views

Connection to database causes SSPI context error

I have migrated a SQL server from another server box that was decommissioned. They have the same name and also same IP address. However, when I connect to the server from an application using trusted ...
2 votes
2 answers
5k views

How to bulk convert ISO8859 text in Text columns to UTF8 in nvarchar(max) columns?

I've been struggling for days with a problem on a MS SQL database. Here is the situation: I have an old Open Source PHP application (Limesurvey version 1.92) running on a Windows 2008R2 server, with ...
4 votes
4 answers
6k views

How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing?

Weird question?...maybe, but I have a need. :) I have a stored procedure that I want to use universally in any database. The stored procedure generates some dynamic SQL and then executes that SQL in ...
4 votes
4 answers
15k views

Management Studio all buttons disabled

I get an odd problem in my SQL R2. Although I can attach a new db to my db instance, but all buttons on the tool menu panel are disabled. So I cannot execute any sql query statements. It is really ...
0 votes
1 answer
583 views

Can I change database compatibility level from 80 to 90 or higher with log shipping?

I have a database on SQL 2008 R2 with compatibility level 80 and I want to change it to 90 or 100. I assumed that after changing, I may find a regression in our application's performance. Many backups ...
22 votes
3 answers
29k views

Safely moving and creating new tempdb files

Two things I'd like to know: how do you safely move tempdb with minimal downtime? how many tempdb files do you need? Is it 1 file per core? So quad-core = 4 tempdb files, creating three new ones?
6 votes
2 answers
7k views

SET ANSI_NULLS at the database level versus setting it at the table level

I have generated schema-only script for my sql server 2008 R2, here is part of it:- USE [master] GO /****** Object: Database [****] Script Date: 03/25/2015 12:17:09 ******/ CREATE DATABASE [****] ...
15 votes
1 answer
18k views

Encoding issue with SQL Server VARCHAR column retrieved in Python

We recently had an issue with encoding related to a field that's being stored as a varchar(120) in SQL Server. In SSMS, the varchar appears as: "Who Killed JonBen‚t?" However, when it's brought ...
2 votes
1 answer
3k views

Forcing a hash join on a temporary table generates error

I have the following query: select afp.period_start , afp.period_end , COUNT(*) from services s Left join ( select entityId, resolutionDate from ... ...
0 votes
1 answer
3k views

Error when running job using SSIS package

I have SQL Server 2008R2 installed on my Windows server 2008. I created a package using the Import and Export Wizard from file system. After that I created a job (SQL Agent Job ) that used the SSIS ...
36 votes
4 answers
6k views

Still wrong to start the name of a user stored procedure with sp_?

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database sp_something. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
4 votes
1 answer
8k views

Setting up Mirroring in SQL Express Edition

I know doing something like mirroring in SQL Server express edition is not possible, but can we do real-time db backup and restore and setup some automated task which acts like mirroring and keep them ...
2 votes
1 answer
11k views

Enable TLS 1.2 and disable SSL 3 on SQL Server 2008 [closed]

I know similar questions have been asked. What I'm asking for are concrete instructions on what to do, or not do, for my particular installation. I have some questions that I can't find the answer to, ...
1 vote
1 answer
4k views

Synonym/Alias equivalent for a Database

If we have code in an SP Database1 then queries Database2 (on the same server) we want the same code to work on the databases Database1Dev and Database2Dev. But this currently means editing the full ...
1 vote
0 answers
32 views

Execution plan showing high hash match [closed]

i have a sql query running from database 1 that joins table company on to a view SELECT DISTINCT RTRIM([K].[cmpCompanyCode]) AS [cmpCompanyCode] FROM [dbo].[view] AS [K] JOIN [database2].[dbo].[...
0 votes
1 answer
348 views

MS SQL Login failed for user 'sa'.[CLIENT: <local machine>]

I changed the password of sa using the command [ALTER LOGIN WITH PASSWORD]. Then , it keeps showing the below message in fail log every minute. Login failed for user 'sa'. Reason: Password did not ...
23 votes
1 answer
57k views

Upgrade SQL Server 2000 database to 2008 R2 and enable new features

I have recently upgraded SQL Server 2000 database to 2008 R2. What I did was: Shutdown SQL Server 2000 (express) service on old machine, Move datafiles (mydatabase.mdf and mydatabase.ldf) to new ...
0 votes
3 answers
7k views

What is Fastest way to shrink a datafile?

I have a 4 Tb SQL Server 2008 R2 database. I'm going to move this DB to a new server with a new release of SQL Server 2012. (the application does not support SQL Server > 2012, but that is not the ...
0 votes
1 answer
450 views

Log backup Job disable and enable during FULL backup -Sql Server 2008R2

I am planning to test the Sql server migration of Production databases but i am thinking to stop TLog Backup job during Backup and Restore as I don't need TLog backup and my Backup and Restore process ...
1 vote
1 answer
435 views

Query Tuning and Index Tuning - SQL Server

I've been asked to improve the performance of the following query. I can't amend any existing Indexes but I can create new ones. The table structures can be found in this SQL Fiddle The row sizes of ...
1 vote
1 answer
448 views

SQL Server Merge Replication Computed Column Conflict Resolution

I'm setting up a merge replication web publication for a table that needs to use the DateTime (Later Wins) conflict resolver so that the most recently modified row wins in any conflict. This table (...
0 votes
0 answers
175 views

Dealing with Corruption

I started getting Error 823 alerts from my SQL Server. When I checked SQL Server error log, I found lots of this: A read of the file 'path\to\my\file.ndf' at offset 0x00000d5f850000 succeeded after ...
0 votes
1 answer
758 views

Query DB2 without using Openquery

Is there a way to query a DB2 Linked Server without using Openquery? IE, is it possible to query DB2 through a linked server by doing something like the following? select * from [db2_linked_server].[...
1 vote
1 answer
2k views

I am facing issue while decryption (Gives NULL value), While export/Import my Colum level encrypted data

I am facing issue while decryption, Please help me if my column encryption/decryption process is wrong. I encrypted table in Source DB and back up certificate, then exported my encrypted table result ...
9 votes
1 answer
2k views

Change Tracking across several Tables

I'm looking into providing DB level change tracking within my system. I need to be able to track changes at an entity level, rather than just individual tables. Right now, we have triggers on each of ...
2 votes
1 answer
347 views

Identity management in Transaction replication with updatable subscription

I have migrated my publisher DB to new server yesterday. For migration what I did was removed subscriptions and publication completely backed up DB on which publication was configured restored DB to ...
0 votes
2 answers
417 views

How to count Id from a table where the data is in ranges?

I have a table cricket_overs having two columns like below. mid overs 1 3 2 5 3 6 4 8 5 10 I need a count of mid where overs is in between 0-3 and 4-6 and >6. The output should be: for 0-...
2 votes
1 answer
1k views

"Login failed for user..." with cross-instance access on same server in SSIS package

I have 2 SQL Server instances running on the same server. Lets call the instances Instance_A and Instance_B and the server Svr0045. Both server use the same service accounts (a service account is ...
2 votes
2 answers
1k views

SQL Server database has corruption, but displays no errors

We have an extensive database of tables. I moved over a table from our dev db to our production db using a program called SQL Compare. I just copy paste the SQL it produces into Management Studio and ...
43 votes
5 answers
317k views

Why is SQL Server consuming more server memory?

SQL Server is consuming 87.5 % of my server RAM. This recently caused a lot of performance bottlenecks such as slowness. I researched this issue. One common solution I could find on the internet is to ...
16 votes
1 answer
132k views

SQL-Server - Grant user DBO permission on database

I have a reporting database that I restore every night from a backup of the production database. When I restore, I need to add a user to the DBO role for that database. I thought this would work: ...
2 votes
1 answer
584 views

SSIS: How to export more than 4000 characters to Excel

I have the following basic setup in SQL 2008 R2. This does not work however as I always get the errors Error 1 Validation error. SQL to XLS: SQL to XLS: Columns "Original" and "...
2 votes
0 answers
57 views

Views that were created in SQL Server 2008 R2 are encountering compatibility issues when re-created in SQL Server 2016 with identical definitions

We've created a view in 2008 R2. Provided below are the definitions for both the base table and the view. Base table: CREATE TABLE dbo.test( fcrecid char(25) NOT NULL, fctype char(1) NOT NULL, ...
2 votes
2 answers
5k views

SSMS 2012 / 2014 / 2016 Object Explorer hangs when Expanding Databases list on a server with many DBs

When accessing SQL Server with SSMS versions (2012/2014 and 2016), opening the databases list from the object explorer is really slow. If I try it using SSMS 2008 R2 on the same instance the result is ...
6 votes
5 answers
40k views

Possibly tempdb out of space or a system table is inconsistent

While running DBCC CHECKDB I got this error Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table ...
0 votes
0 answers
52 views

Elapsed time of the query is abnormal in SQL server

I am trying to find the elapsed time of the query in SQLSERVER using the view, sys.dm_exec_requests. For some queries the elapsed time is more than an hour, most of the queries coming up when using ...
3 votes
3 answers
4k views

database corruption

The database is corrupted and there is no recent backup, Error from DBCC CHECKDB Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type ...
2 votes
0 answers
715 views

How do I fix the Failed to retrieve data for attaching mdf file is which not primary database file

I'm using SQL Server 2019 in Windows 10 Enterprise and Administrator user I have an MDF file and I want to attach it, and it has full access in the form of Everyone Full Control , when I try to attach ...
16 votes
5 answers
91k views

Get Job id OR Job name from within executing job

I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at ...
16 votes
4 answers
26k views

Move log file without taking database offline

I need to move a database log file to a new partition without taking the database offline. The normal way of doing this would be to detach the DB, move the log file then reattach the db. Is it ...
3 votes
3 answers
3k views

Unable to fully drop a database snapshot

I have a job that drops and re-creates a snapshot, it's currently failing when trying to create the database as it says the database already exists. The database however does not have an entry in ...
-1 votes
1 answer
57 views

sp_BlitzWho while executing gives error message as 'Incorrect syntax near the keyword 'COALESCE'.' in SQL SERVER 2008

To know the spids which are causing slow down/blocking each other; I am trying to use 'sp_BlitzWho' by brilliant Brent Ozar; however on executing on SQL SERVER 2008; am getting message as below. Msg ...
35 votes
2 answers
100k views

Decode Base64 String Natively in SQL Server

I have a varchar column in a table in SQL Server that holds a Base64-encoded text string, which I would like to decode into its plain text equivalent. Does SQL Server have any native functionality to ...
10 votes
2 answers
56k views

The INSERT/UPDATE/DELETE permission was denied on the object 'TheTable', database 'TheDb', schema 'dbo' [closed]

I have two SQL Server user accounts on a database that behave differently on INSERT / UPDATE / DELETE. SQL Server version is 2008 R2 (10.50.4042, 64 bits). UserA (SQL Login): can do these commands (on ...
0 votes
1 answer
902 views

Intall SQL Server 2008 On Windows Server 2016

We update our Server from Windows Server 2008 to Windows Server 2016. And we use SQL Server 2008 R2 on the old server. I read from the website said that SQL Server 2008 is compatible with Windows 7, ...
36 votes
5 answers
8k views

Why is there still a varchar data type?

Many of my databases have fields defined as varchars. This hasn't been much of a problem since I live and work in America (where the only language that exists is "American". ahem) After ...
11 votes
3 answers
8k views

memory used by Locks

I am kind of curious, one of SQL 2012 enterprise edition with 128 GB of RAM size of database is 370 GB and growing, amount of memory used by locks (OBJECTSTORE_LOCK_Manager) memory clerk showing ...
0 votes
2 answers
1k views

why a convert from varchar do date fails on a different row everytime it runs?

I have a database with a field like this (Dont know why it's varchar): 19950105 i'm trying to convert it to DATE as DDMMYYYY. I could do this with: convert(varchar(10), cast(The_ugly_field as ...
51 votes
5 answers
113k views

How export a sql server 2008 diagram to PDF filetype?

I want to have an export from my database diagram to PDF or image types. How can I do this? I worked with SQL Server 2008 R2.
5 votes
2 answers
31k views

Applying Quotes Across Multiple Lines

In SQL Server Management Studio you can use the shortcut CTRL + K > C or CTRL + K > U to comment/uncomment lines in bulk. Is there a similar keyboard function that applies single quotes across ...

1
2 3 4 5
76