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

learn more… | top users | synonyms

1
vote
2answers
25 views

Trying to filter msdb..sysjobs by job_id

I'm attempting to determine the name of the SQL Server Agent Job that is currently running by comparing the output of master.sys.dm_exec_sessions.program_name to msdb..sysjobs.job_id I'm using the ...
0
votes
0answers
17 views

Unknown Error when attempting to run Import Data wizard

In SQL Mgmt Studio, when right-clicking on a database, choosing Tasks and selecting Import Data, we receive the below error message. Any idea what might be causing this? We are running SQL Server ...
-1
votes
1answer
32 views

Displaying records in a SQL Server #temp_table [on hold]

what's the select query syntax for displaying the content/records in a #temp_table on a SQL Server
0
votes
0answers
19 views

SSIS Package truncation and possible data loss (error code: 0xC020901C)

Good morning all, It's been 2 days now trying to figure out how to solve this issue, still no answer. Maybe you can help me out. Here is the thing. I have created an SSIS package to import data from ...
1
vote
0answers
38 views

SQL Query Optimizer - how does cost vary with resource availability

When the query optimizer calculates the cost of an execution plan, can you expect the plan to differ depending on the amount of resource available to it? Take the following scenario: You have a ...
1
vote
0answers
37 views

Cursors and concurrent users

I typically avoid cursor operations like the plaque, however, I have come across my first problem where using a cursor outperforms a query. So I am forced to use it. I have created an elaborate ...
1
vote
0answers
15 views

Change Data Capture missing changes for a specific record

I have enabled CDC in SQL Server and it generally works well but sometimes the changes are not recorded for some reason. And only for a couple of records. But then if I issue an UPDATE from SSMS, the ...
1
vote
1answer
41 views

Linked Server using ODBC too slow

The scenario is that developers used a linked server object on ServerM to reach ServerST. This linked server was setup as a "SQL Server" type using a domain login that exists as a login on ServerST. ...
2
votes
1answer
56 views

Scripting a maintenance plan

I created a maintenance plan for my SQL Server 2008 R2 using SQL Server Management Studio (SSMS). However, I am unable to export/convert it to a script, in case I need to redo it on another server. ...
1
vote
1answer
40 views

Restoring a database, changing recovery model

I regularly make a backup of my production database, then restore the same onto a test database, so developers can always have recent data for testing. The production database uses full recovery ...
0
votes
1answer
90 views

Why straight SQL query run faster than stored procedure?

I am facing-out a very surprising problem in SQL Server. The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will ...
0
votes
0answers
30 views

SQL Server 2008 R2 to 2012 [on hold]

We had developed the ssis packages using BIDS 2008 and deployed on SQL 2008 R2 machine and it was working fine. Now our DB's are migrated to sql server 2012. But we have deployed the old packages and ...
1
vote
1answer
41 views

Maintenance plan backup and reload the same database

I want in my maintenance plan every night to copy the production database to a backup, and reload the newly-created backup into a test database. By reading here: Restore database - maintenance plan ...
0
votes
1answer
21 views

SSIS unpivot returns different results when run via a SQL agent job

I hope someone can help me. I have an SSIS package which grabs data from an excel (.xls) file, unpivots it from a rubbish format into a 'proper' format, transfers it to a SQL server 2008 R2 db, and ...
1
vote
1answer
64 views

SQL Server Multiple JOINS with Table Value Function - query never ends

I have a query with 4 joins using a table value function to get the data and when I execute it the query never ends. Issue Details Table valued function: CREATE FUNCTION [dbo].[GetIndicator] ( ...
0
votes
1answer
28 views

Missing Database Diagrams node in Management Studio [on hold]

It seems that I dont see this diagrams node when I log into my database in the testing SQL Server 2008R2 (on a 32 bit server) but when I connect to the same instance from the production SQL Server (64 ...
2
votes
2answers
55 views

Why do I need to backup the Database Master Key?

When I make a Database Master Key for a database: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some Long Password' This page from Microsoft recommends I back it up offsite: ...
4
votes
3answers
369 views

Why does transaction log continue to grow in Simple recovery mode with nightly backups

Before immediately marking as duplicate, I have read Mike Walsh's Why Does the Transaction Log Keep Growing or Run Out of Space?, but I don't think it gave an answer to my situation. I looked through ...
2
votes
2answers
65 views

Database events that will cause deletion of index

In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem ...
1
vote
0answers
53 views

A query that does a lot of reads, but plan is OK [migrated]

I´m experiencing a strange behaviour in a specific query in the SQL Server 2008 R2. I´ve got a query that does 19 million reads and is very time-consuming and when I try to check its cached plan, the ...
3
votes
2answers
154 views

Alert when Job isn't triggered

I have a Sql Job on my server that is triggered nightly from a remote server (once it has completed its backups). I have successfully configured the job to notify me when it fails, but I would like ...
1
vote
1answer
26 views

Backup Certificate SQL Server and return bytes with a Select Statement

I have a certificate in a database in Microsoft SQL Server 2008 R2. I want to get a copy of the certificate so I can burn it onto a USB device and put it in a safe. I don't want to have to go onto the ...
2
votes
2answers
102 views

Is Information in sys.dm_db_index_usage_stats reliable

I'm archiving data from an old system that has no documentation. Lucky me... I'd like to find out when the tables were created, last accessed etc. Can I trust that this query will give me the correct ...
0
votes
0answers
32 views

I am trying to implement Scott Chamberlain's code in SQL Server 2008 R2 . Getting Assembly not present errors

I am trying to use Scott Chamberlain's code, here: http://dba.stackexchange.com/a/17466/45380 I was able to compile it and create the DLL, but now when I try to run SQL CREATE ASSEMBLY I get: Msg ...
3
votes
2answers
193 views

Can I update every datetime in bulk?

The current database assumes all times are local to the user. Now we're changing this so the database stores everything in UTC and our application converts the UTC time in the database to the correct ...
0
votes
0answers
26 views

How to access another Connect server table in my Local server in SQL Server 2008R2? [closed]

In same server name using different database select * from AnotherDB.dbo.AnotherDBTable (this query running fine) Suppose I want to access a table on another server from my local database - how ...
0
votes
1answer
52 views

User defined data types causing implicit conversions

user defined datatypes, i'm looking at some execution plans and seeing implicit conversions happening in the background when a var is declared of type user defined data type and pulling a value into ...
0
votes
0answers
17 views

Run SSIS task regardless of checkpoint

Is it possible to make a task always run, regardless of whether the package starts from scratch or from a checkpoint restart?
5
votes
1answer
54 views

Full Text Search Term Casing Affects Results

I have been experiencing a full text problem when searching using CONTAINS. I have the full text catalog accent sensitive setting set to false. The table contains following rows: Microsoft C# ...
0
votes
3answers
35 views

List all tables that a specific login has permissions [duplicate]

This might be repetitive, but I haven't found a solution that serves my purpose 100%. What I'd like to achieve is to have a script (or SP) that'll list all tables that a user has permissions on. Any ...
0
votes
1answer
28 views

How to find when change tracking was enabled?

How can I determine when change tracking was enabled on a table in a SQL database? Assume that this table has a regular cleanup routine, which means that looking at the oldest record in the change ...
0
votes
0answers
35 views

Migrating SQL Server 2008 R2 to SQL Server 2014. What are the importing replication changes?

We currently have SQL Server 2008 R2 on our server. I now have the opportunity to migrate to SQL Server 2014. The database we use isn't large. Migration of data is not a issue. The question I have ...
2
votes
1answer
31 views

Reporting services scheduled reports are clogging up sql job history log

This is a bit of a cross-disciplinary question - Hopefully I’ve posted in the right place. SQL server 2008 R2 Question: How can I limit the number of rows in [sysjobhistory] for specific jobs, ...
0
votes
1answer
38 views

SQL Server 2008 R2, error creating file for FileStream

I'm trying to create a file to add to a filestream file group and I get the error: Msg 5120, Level 16, State 106, Line 1 Unable to open the physical file "d:\data\filestreamstore.mdf". Operating ...
3
votes
2answers
48 views

How to tell what invoked a trigger in SQL Server?

I'm working on a legacy SQL Server application, with close to 3,000 tables and more than 10,000 stored procedures. One of those stored procedures is deleting some needed data from a table. I don't ...
-4
votes
1answer
33 views

How to run mssql server dependent application to mysql workbench [closed]

I am very new in mysql workbench. I have created an application in c# which uses sql server 2008 r2 database. Now i have migrated my database on mysql workbench. i want to run my application now. My ...
4
votes
2answers
128 views

How to write a query which finds all circular references when a table references itself? (II)

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY ...
0
votes
1answer
48 views

How to deploy SSIS package with XML config, using SQL agent

I wonder if anyone can help me with a question. Context I've created a database which needs a front end in MS Excel. When the user presses upload button I want it to start an SSIS package which moves ...
0
votes
0answers
37 views

How to avoid copy of audit trace files

My database is copying all of SQL-Server trace data file everyday. The result is I have to delete them manually everyday because hard disk gets filled. How can I stop this?
1
vote
1answer
61 views

SQL Server database Mirroring on different servers

I tried to perform SQL Server database Mirroring(with SQL Server 2008R2). And I got success to perform mirroring with "High Safety" mode. And for that I have used three instances of SQL Server ...
0
votes
0answers
28 views

Sql Server Agent Job

In my Local SQL Server 2008 I created a scheduled job (like daily email will send to particular user from the database based on condition). Now I decided to host my schedules in hosting server so can ...
4
votes
2answers
93 views

What are the risks (if any) of sharing a service account on more than one server?

I use domain accounts for SQL Server service accounts. Sometimes, when I have multiple servers that are logically or thematically related, I'll use the same set of domain accounts for the service ...
1
vote
2answers
261 views

Joins between linked servers are dangerous, so can we just load the data into a local table before doing the join?

I was just reading the following article which states that it's a worst practice to do JOINs between two linked servers. The only alternative I can see is to get the necessary data from both linked ...
1
vote
2answers
79 views

Fixing an error 9004 without taking the database offline

My database (SQL Server 2008 r2) is running fine but I’m getting the following error in my event log. Error: 9004, Severity: 23, State: 6. An error occurred while processing the log for database ...
2
votes
3answers
82 views

Should I shrink my database?

I have a 1.2Tb SQL Server 2008 R2 database which is more than 50% empty, because I migrated a lot of functionality to PostgreSql. The database is still in use, and still somewhat growing, but I do not ...
3
votes
4answers
505 views

How do I place leading zeros for numbers less than 10 without affecting those 10+?

Probably a newb question, but... I have a table with a VARCHAR field containing numbers 1 through 300. I need to place a leading zero in front of any numbers less than 10 while retaining the original ...
1
vote
1answer
31 views

It is mandatory to create a route when creating an event notification?

I'm starting with service broker. Already managed to setup an event notification for blocked processes based on this answer and it's working flawlessly. Then I wanted to setup another one to notify ...
3
votes
1answer
53 views

difference between index REBUILD and REORGANIZE in relation to recompile in SQL Server 2008 R2

Does the INDEX REORGANIZE index cause recompilation of the stored procs the same way as INDEX REBUILD
0
votes
3answers
64 views

How to display posted on date and time like1 hr ago or 30 min ago

i want to show the posted date like 1 hr ago.. or 30 min ago.. declare @date datetime ='2014-07-24 11:50:22.033'; SELECT CASE WHEN DATEPART(DD,@date)=DATEPART(DD,GETDATE()) THEN CASE WHEN ...
0
votes
1answer
42 views

Securing an Internet Database Connection

I am looking to setup a website and database that are connected via the internet, and am looking to secure the connection. The connection will probably not be over a VPN, so shall be openly exposed. ...