1
vote
1answer
18 views

What happens on the primary when log cannot be restored on the secondary on Log Shipping

I would like to setup a log shipping but I have a concern as to what would happen if the restore operation on the secondary would fail for some reason. I want the primary database keep working no ...
2
votes
1answer
26 views

Error during SQL Server 2008 R2 SP3 upgrade: MODIFY FILE failed. File 'MSDBLog' does not exist

I have just installed SQL Server 2008 R2 SP3 and after installing it and trying to start the SQL Server instance again, it won't come online. In the SQL Server error log I can see that the following ...
2
votes
2answers
120 views

How to get OUTPUT $action, Inserted.*,Deleted.* records in In SSIS

I was just wondering, in MERGE in T-SQL, you can output the OUTPUT $action, Inserted.* and Deleted.* records. I want to ask how can I get OUTPUT $action, Inserted.*, Deleted.* records in SSIS?
0
votes
1answer
69 views

How many LEFT JOINS make a VIEW unusable?

I have a view which involves LEFT JOINS on 20+ tables to a base table but when querying this view and looking at the execution plans I am getting something totally unexpected. For example, with the ...
3
votes
1answer
63 views

Need help with a Running Total Issue

Using SQL-Server 2008 R2 and Report Builder 3.0 Here is my query without the running total column: SELECT CostManagement_CommitmentDetails.Id ,CostManagement_CommitmentDetails.LineNumber AS ...
1
vote
2answers
29 views

Export structure and data without a wizard [duplicate]

Using Sql Server 2008 R2 (but I could possibly upgrade if required). I want to generate scripts for the database structure as well as the data for a schema. The Generate Scripts wizard does what I ...
2
votes
1answer
24 views

Restore backup to different filegroup

Using Sql Server 2008R2. I have a backup of a filegroup. Is it possible to restore this to a different file group (possibly even on a different database)? I have tried RESTORE DATABASE my_database ...
1
vote
0answers
31 views

Log shipping option not showing

I am using SQL Server 2008 R2 Standard Edition. I am trying to implement log shipping, but the log shipping option is not showing in the database properties window. The database recovery option is ...
0
votes
1answer
34 views

Restore Database with Multiple Restore commands

For automation, I have to dynamically construct the restore commands and thus I have created a script. A .bak file is created on a remote server and I receive it daily. The script builds the restore ...
0
votes
1answer
17 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
3answers
213 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 ...
1
vote
2answers
49 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
51 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
60 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
68 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
2answers
253 views

Terrible Bad Query Plan

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
46 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
42 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 ...
4
votes
2answers
410 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
38 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
0answers
42 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
1answer
54 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
58 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
42 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 ...
1
vote
2answers
54 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 ...
0
votes
1answer
62 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 ...
0
votes
1answer
36 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 ...
0
votes
0answers
64 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
35 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 ...
7
votes
1answer
110 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
2answers
81 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 ...
2
votes
0answers
57 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
114 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
27 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
23 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 ...
3
votes
1answer
79 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 ...
0
votes
1answer
32 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
67 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 ...
4
votes
2answers
117 views

SQL Server cluster resource won't come online unless its on port 1433

The 2-node SQL Server 2008 R2 cluster works fine until one of the resources got removed. I successfully added it back. However, the cluster is not able to online the SQL Service unless it is on 1433 ...
4
votes
1answer
100 views

Find reason long running stored proc SQL Server

I have a stored proc which is called every 2.5 seconds by a program, 24-7. This proc normally runs under the 10 milliseconds. A few times a day (3 -5 times) it takes around 42953 milliseconds to run ...
2
votes
1answer
176 views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
2
votes
0answers
106 views

Why does a simple select query produce a deadlock?

I am trying to analyze randomly occurring deadlock problems on an SQL Server 2008 R2 database. The deadlocks occur in very rare cases when SSRS tries to access one of 5 different stored procedures. ...
1
vote
2answers
43 views

Single Virtual Drive Vs Multiple Virtual Drives for SQL Server files

Hi guys I have question about how two different Hard disk setups can possibly affect performance of my database. I have two Options. Option 1: One Physical Harddisk and then Multiple Virtual hard ...
1
vote
1answer
131 views

Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page

I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files): DBCC SHRINKFILE('tempdbfile8', EMPTYFILE); ...
1
vote
1answer
166 views

What is the possibility that mirroring a database will damage, corrupt, or erase either the primary or target database? [closed]

The last time my company had tried to mirror some databases, everything worked great until one morning they came in and the primary database in one of the mirroring schemes was just blank. They had ...
2
votes
0answers
77 views

Implementation of SSL ON SQL SERVER DEFAULT INSTANCE

We've purchased an SSL certificate for our Server with a specific CN and implemented the certificate on IIS. We imported the same certificate on the SQL Server machine. However, when trying to ...
4
votes
1answer
78 views

Encoding Debug UTF8 & Latin 1

We have on our DB some international people that got some characters saved incorrectly on our DB (MS SQL-Server 2008 R2). For example we have strings like 'WilcoxonÃ'. We got this data into ...
0
votes
1answer
56 views

Find Without primary key Table in SQL Server 2008 R2? [duplicate]

This is my query to find out primary key, foreign key in SQL Server 2008 R2: select * from information_schema.table_constraints where constraint_type = 'PRIMARY KEY' Know I need to select without ...
1
vote
0answers
39 views

What will do After Press F5? [closed]

select * from Employee; Name id Col1 Col2 Row1 1 11 1 Row2 2 12 3 Row3 3 13 30 Row4 4 14 44 Above is my table After PRESS F5 What process is running in Background ...
9
votes
1answer
167 views

Can you use COUNT DISTINCT with an OVER clause?

I'm trying to improve the performance of the following query: UPDATE [#TempTable] SET Received = r.Number FROM [#TempTable] INNER JOIN (SELECT AgentID, ...