This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx

learn more… | top users | synonyms

0
votes
1answer
24 views

Replicating tables from LAN database to web server?

I have an in-house database (that's actually part of a third-party product) running on 2008R2 Express. We'd like to display some of the data on a new website we're working on (running 2008R2). I'm ...
2
votes
2answers
66 views

Using collate: When to use and when not to use

In one of my applications we had tsql like shown below SELECT DISTINCT [Dblist].[DbName] AS [DbName] FROM [Common].dbo.[Dblist] WHERE dbname not in ( SELECT [name] FROM ...
1
vote
2answers
150 views

Why can't I obtain OBJECT_NAME() for a temporary table?

According to this MSDN article http://msdn.microsoft.com/en-us/library/ms190328.aspx When a temporary table name is specified, the database name must come before the temporary table name, unless ...
2
votes
3answers
520 views

Run multiple stored procedures in sequence

I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for ...
0
votes
3answers
101 views

index rebuild/reorganize frequency

I am a dba by accident. I am doing index rebuild/reorganize. I would love to use some scripts once I become comfortable doing these tasks. I did go through some of the forum pages regarding this ...
1
vote
2answers
65 views

Indexed views on double linked server

A server SERVER_A has some data. A server SERVER_B defines A as a linked server, and defines some views on the data. The queries are like this: CREATE view myview as select * from openquery ( ...
1
vote
1answer
153 views

“Could not insert a backup or restore history/detail record in the msdb database” While restoring msdb

Today I found msdb database was in suspected mode, so I restored MSDB from my latest backup(As I have daily backup for this database). When I restore the MSDB database using Tsql i got below errors, ...
0
votes
0answers
86 views

Problem with EXEC MyLinkedServer…sp_executesql within transaction

I rather clueless about this so any help is appreciated. We have got a stored procedure that is running within a transaction. The stored procedure is called from a .NET program (via normal ADO.NET). ...
0
votes
1answer
116 views

Best approach to have a Live copy of a table in the same database

I have a table where 15,000 to 20,000 rows are being inserted every hour. Table Schema is something like ColumnName DataType ID BIGINT IDENTITY(1,1) Column1 INT Column2 INT ...
5
votes
3answers
365 views

Parallel Plan Selection

I have a weird query plan problem. I have two databases (Let's call them DB1 and DB2) where both are sitting in the same SQL-Server instance and have identical schema. In there, we have a couple ...
3
votes
1answer
53 views

How to join by most recent preceding date?

Imagine I have a database with historic price data and a second table with dates Item | Price | Date Customer | Item | date ------------------------- --------------------------- ...
1
vote
0answers
71 views

The replication Merge Agent failed to propagate a Publisher schema change to the Subscriber

We are running 2 Microsoft SQL Servers - publisher (2008R2) / subscriber (2008 Express) with merge replication. I tried to make some schema changes via Transact-SQL and partly failed. So tried to ...
0
votes
1answer
75 views

If you schedule a TSQL Job in SQL Server 2008 R2 and additionally need to copy files with it, is it better just to use a script?

I noticed that alot of backup jobs are scheduled using SQL Server 2008 R2, but we have a system here at work that also requires the backup of a directory in addition to the database contents. I've ...
4
votes
2answers
443 views

Restored Database is different from the original

I have recently performed a restore of our data-warehouse staging database onto a separate instance in our dev environment. After the restore I did a comparison and it looks like the database sizes ...
0
votes
1answer
57 views

how to select top ID from join operation and staging tables

I have this inner join from staging and operations table jobsCode I want to select the top surrogate key ID and get the Start_Date from this join my problem is my Query rerunning 2 records original ...
0
votes
1answer
21 views

Why different users from same login group can not access the database objects?

I have SQL login group as "SC_NT\xxxx" in server. There are multiple users using this login group to access database objects. User "A" can able to access db objects from .net application. But when ...
0
votes
0answers
127 views

Importing OLE Object from Access to SQL Server

I am trying to import a table from MS Access to MS SQL Server which contains a BLOB field which contains a .zip file. The zip file has two images. This field is null sometimes. I am using the Import ...
0
votes
0answers
23 views

Altering the size of user defined datatype in SQL Server

I had created the user defined datatype varchar(5)..But now i have to change the size as varchar(10)..the thing is the udd is used in many tables.. can anyone help me to change the size without using ...
0
votes
1answer
67 views

Indexing a view with xQuery in it

I'm not sure if this can be done at all but if it can it would be a huge help. I have a system where I am not allowed to make changes to the database structures. I have yet to have code changes put ...
0
votes
1answer
47 views

Is there a more efficient way to apply a new schema to an existing collection when there's existing data?

I'm needing to add a new schema to the schema collection on an existing table. Right now, I simply unbind the old schema collection from the table column, drop it, create the new schema, and rebind it ...
1
vote
1answer
312 views

BACKUP failed to complete the command BACKUP DATABASE

Not sure if this is a false-positive issue or not, but I need quell my curiosity about it. I get the following error message: Error: 3041, Severity: 16, State: 1. BACKUP failed to complete the ...
0
votes
0answers
38 views

How to ensure a field is populated before use in a trigger

When I assign a new bed number in a hospital, the number must be prefixed by the ward number the bed is in. How do I know my user is going to populate Ward_Id before Bed_Id? What does one do in a case ...
0
votes
1answer
61 views

Package performs differently on two different instances on the same server

I'm investigating some performance issues for our BI team. They have a package which runs extremely slow and will sometimes run indefinitely on one instance but finishes normally on another instance. ...
1
vote
2answers
57 views

Compress files and directories into zip file and put it into blob field

What is the best way to do the following using SQL Server? Compress all the files, folders and subfolders from a specific location (specified in a parameter in a stored procedure) - the server side ...
0
votes
1answer
88 views

How can I get all orphan records in a single query?

I just noticed few tables that has orphan records (foreign keys) that no longer point to any primary key. How can I query the tables to get a list of all such rows? When these parent records got ...
1
vote
2answers
72 views

How do I know if a AWS Server instance has license SQL Server Standard or SQL Server Web?

I'm looking my AWS billing and this doubt has come up. How do I know if a specific instance is running SQL Server 2008-R2 Standard or SQL Server Web? (using the AWS Console or SQL Server Management ...
1
vote
2answers
82 views

sql server minus one day

I have a server with SQL Server 2008 R2 installed in it. This server is used as a D -1 Database, in other words, the database it is one day behind from production server. The problem is that my ...
0
votes
1answer
82 views

READ_COMMITTED_SNAPSHOT locking on Non-Clustered Index

I'm in big trouble with one database instance of SQL Server 2008 R2. I have this structure that I've created it to simulate my real problem: USE [sor] GO ALTER DATABASE [sor] SET ...
1
vote
1answer
43 views

How can I log the way in which a job was started?

Using SQL Server Agent, I've created a Job that runs some SQL. This SQL does some work on one database, then adds a row to a logging table on a separate database. The Job is run on a couple of ...
1
vote
1answer
35 views

Unable to view SQL Server services

Whenever I enter SQL Server Configuration Manager and choose SQL Server Services I would always get the following error message: "The remote Procedure call failed. [0x800706be]" everything else works ...
1
vote
2answers
88 views

SQL Server TempDB Growth issue

We have an OLTP database (running SQL Server 2008 R2) that has 6 tempdb files. All of them have an Autogrowth by 10%, restricted growth to 10000mb. At one point, someone changed one of the growths to ...
0
votes
0answers
292 views

why I can't delete old transaction log backup files with an existing scheduled job?

I have a job scheduled in my SQL Server Agent 2008 R2 that runs every 30 minutes, Its part of a maintenance plan that runs: A daily full database backups, differential every hour, and transaction log ...
1
vote
0answers
65 views

Odd Execution Plan

I have a table in SQL Server that is performing very slowly and I have been trying different indexing options to try to speed it up to no avail. I suspect all of the varchar(max) fields may be a ...
3
votes
2answers
57 views

What permissions should the account for MS SQL Server Agent have?

I'm in a situation where I need to change the account for the Server Agent of SQL Server from NETWORK SERVICE to an AD domain account. All I'm doing is making the domain account sysadmin in the ...
1
vote
1answer
52 views

Understanding “Date Last Modified” for “System Stored Procedures”

Whilst reviewing code on a SQL 2008R2 Instance I inspected the Date Last Modified of the System Stored Procedures of the databases. The ones in the DBO schema had a datetime that predates the ...
1
vote
1answer
92 views

Perfmon and SQL Server Clustering

I'll be going onsite to run some performance tests for a client. They have a Windows Failover Cluster, which has a clustered instance of SQL Server. Let's assume for this post that my client's ...
7
votes
1answer
134 views

SQL Server trace based on client TCP port

I have a Windows terminal server with many different users logging in via RDP to run an application. The application makes one or more connections per user to a SQL Server 2008 R2 instance. All users ...
2
votes
1answer
76 views

SQL User with only access to maintain indexes and statistics

I need to have an AD user account with access to all databases on a specific instance of SQL Server 2008 R2, and the only thing that user will be doing is; Reorganize indexes Database integrity ...
2
votes
2answers
83 views

Reviewing all code on a SQL Server Instance

Shortly our development team will be moving an existing SQL Server 2008R2 instance into our Production environment. Ahead of this I have been asked to review all the code on it for anything malacious ...
3
votes
0answers
163 views

SQL Server not starting at Windows startup but starts manually

My SQL Server instance's (SQL Server 2008 R2 Express) service has Startup Type = Automatic. The past couple of times I've restarted my computer the service has failed to start on its own, but it ...
1
vote
2answers
231 views

Disabling certain steps in a job

I have a project coming this weekend where all the jobs has to be stopped but only 1 step in a particular job has to be run. for eg: i have a a 2 step job and the second step must run and the first ...
0
votes
1answer
32 views

SQL Server, firewall and linked servers

I have: SQL Server 2008R2 database server in my LAN and under my control (call it LOCALSQL) remote SQL Server, on which I have much less control (call it REMOTESQL), for which I only have IP, ...
1
vote
1answer
30 views

Separating system databases from MDW SQL Server 2008 R2

We have the Management Datawarehouse feature configure on two of our SQL Servers. Both point to the same central database and upload their results to it. Everything works fine with the built in ...
1
vote
0answers
16 views

update new ID_MergeTo from operation table ID if MergeFlag is true and MergeTo defined?

I have problem I don't know how to solve it. I have two tables Staging and Operation.I create Data Flow Task using SSIS to move data columns from ...
0
votes
1answer
161 views

Missing Database Mail Configuration Wizard in SQL Server 2008 R2

I was able to verify that Database Mail is installed and running but when I expand the management folder there is no Database Mail Configuration Wizard.
0
votes
1answer
42 views

How to delay replication start

I'm creating a new publication for replication using the @allow_initialize_from_backup option to avoid the snapshot immediate creation and therefore locking all objects replicated on articles. The ...
3
votes
1answer
86 views

Total Fragments 99% and I cant get it to stay down [closed]

I have a SQL Server 2008 R2 database it is of size 13000.31 MB and my largest table has around half a million records. It is a database that I have bought. It already has indexes but when I run the ...
1
vote
2answers
742 views

How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?

I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.
0
votes
1answer
38 views

SQL Server: compare names ignoring non-alpha

I have a database table with a text column "title". New rows are inserted all the time, and every time I would like to check for potential duplicates on this column. I define duplicate by "having the ...
1
vote
1answer
393 views

Dynamic Number of Columns in Pivot Table

I have a table to store StudentID, SubjectID, ObtainedMarks. Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate ...