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

learn more… | top users | synonyms

0
votes
1answer
99 views

Archival strategy for a table with 25 million records in SQL Server 2008 R2 Enterprise [duplicate]

I have a table that stores some audit information and currently has 25 million records. There is a process that inserts to this table every 30 minutes. This is historical data and is not used for any ...
3
votes
2answers
257 views

What can cause slow queries with low resource usage?

I am writing an application which builds a database on a SQL server. The application loads data into some staging tables, then executes a series of stored procedures which merge the staging tables ...
0
votes
1answer
81 views

Get the job scripts out of a MSDB Backup [duplicate]

Long Story Short... my Database server died a month ago. I restored everything but the MSDB as the tape backup failed. I reconstructed some jobs but some essential ones are missing. I finally found ...
3
votes
1answer
1k views

SQL Server 2008 R2 error: 15023, user, group, or role already exists

I have a test database that I'm having permissions issues with. I can't access the reporting database, and the application's help documentation says to do the following: Resolution: 1. Launch the ...
0
votes
1answer
59 views

Do I need to explicitly grant permissions for a login under the sysadmin role for it to access a custom schema?

So I have a login that has sysadmin rights on the db server. However, it's not able to access database objects under a custom schema that was created (eg. it cannot select from tables with ...
0
votes
1answer
60 views

SQL Server 2008 Script Changes from Database Diagram

I have a database diagram which I have made several changes to using SSMS, however instead of committing the changes I want to generate a SQL script to apply them instead. Is there a simple way to do ...
1
vote
0answers
72 views

Disable the SQL profiler to be executed on particular query running from ASP.NET application

I have set of queries executing from an ASP.NET application and I don't want those SQL queries to be traced by SQL Server Profiler. How can I do it?
0
votes
3answers
256 views

Microsoft SQL Server does not start

I have problem with start SQL Server 2008. I needed copy database from one computer to seced. I copy only catalog DATA from my actually working server and past this catalog to my new fresh installed ...
0
votes
0answers
87 views

SQL Server 2008 R2 replication server runs faster than primary

I run a simple query like select * from table and i take the following results: primary database : 90 seconds replication database : 2 seconds The tables are identical and have the same indexes in ...
2
votes
1answer
85 views

Make View Supersede Table in Same Database?

For testing purposes we have a sanitized copy of our production database in our test world. Because of the way it is sanitized, we now have empty fields where encrypted data previously existed. We ...
4
votes
1answer
89 views

Database having only db_datareader shows invalid properties for a index

If a SQL server database user has only public permission in server roles and for a particular database only db_datareader and public permission then while trying script index as CREATE To option in ...
0
votes
1answer
100 views

Sql Server Fix Users

I Create a user in my database with following command : CREATE USER Test WITHOUT LOGIN I created a login with the Test Name. I want to link Test user with Test Login. When I use following command ...
0
votes
1answer
49 views

How to move SQL server transaction replication from one server to another server [closed]

Currently we have SQL Server 2005 as a publisher and SQL 2008 R2 as subscriber for one of our database (about 350 GB in size). I want to move the replication to our different server (Which also has ...
1
vote
1answer
87 views

Can geography datatype Lat/Long properties be returned using a view in SQL Server 2008?

Hopefully the question is fairly self-explanatory. I've been ferreting through the, what appears to be quite minimal/poor microsoft documentation regarding both views and geography datatypes in the ...
3
votes
0answers
45 views

Any checklist/guide when transferring a database to a new server? [duplicate]

I'm looking for steps that I need to follow to transfer a database to a new server. So far I have: I restored the current database that I have in SQL Server 2005 instance to another SQL Server ...
1
vote
1answer
47 views

Create LOG files when creating multiple DATA files for tempdb?

I've a question that I can't seem to find an answer to (and I can't test it out for a few weeks so I'm doing research now during my downtime). Should I be creating corresponding LOG files for every ...
2
votes
1answer
60 views

Get identity value of all rows inserted

I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement? Example insert ...
2
votes
1answer
56 views

How does SQL bind rows in filestream-enabled table with their files

Every BLOB stored in a filestream-Enabled table actually stored in a physical file, How can I know the actual filename for a specific row in that table. For example, I have the Images table which is ...
0
votes
1answer
75 views

SQL Server Reporting Services: How to check who accessed certain report?

I am in process of eliminating who should have access to SQL Server Reporting Services reports that I have created. Is there a way to find out who has accessed certain reports during past month? My ...
2
votes
1answer
177 views

SQL Server SSL connection - What am I missing?

I am trying to set up my SQL Server 2008 R2 instance so that it only accepts SSL connections. My understanding is that once this is set up, any client machine should need to have a certificate ...
6
votes
2answers
98 views

Data Flow Task Produces No Rows with Declare Statement

I have a SSIS package that is doing a simple SELECT statement and then loading into a table. I have anonymized the sql statement which should be painfully obvious when you look at the table name. I ...
1
vote
1answer
65 views

How to isolate that network is issue of bad query performance [closed]

I would like to know how to isolate SQL Server bad query performance is caused by NETWORK and not the SQL Server. Are there any specific tips and tricks in SSMS, wait types, or specific PerfMon ...
4
votes
1answer
379 views

SQL Server 2008 R2 Upgrade (Express to Standard) fails. No “Select Instance”

Hello there, i got stuck in the progress of upgrading 3 systems from SQL Server 2008 R2 Express Edition --> SQL Server 2008 R2 Standard Edition Problem: The Option "Select Instance" in the ...
1
vote
2answers
187 views

How can I change the number of @schedule_uid=N'????' to re-create a SQL Server Agent Job?

I have a SQL Server 2005 job in the SQL-Server Agent Jobs that I need to move/transfer to another updated SQL Server 2008 R2 instance. I was able to script it out and then use it as a new query window ...
0
votes
1answer
81 views

Does the recovery model affect query speed?

Would using the Full Recovery model make something like a long running DELETE operation slower than it would be under the Simple Recovery model?
2
votes
1answer
137 views

XML queries with case insensitive tag- which is the best query

I had asked question about XML queries with case insensitive tag earlier and i found a solution too. But i also found some other solution. So table was something like DECLARE @myTable TABLE ( yourXML ...
1
vote
2answers
96 views

How to move a SQL Agent Job to another Server?

I'm moving from SQL Server 2005 to SQL Server 2008R2, and I see there is one job in the SQL Server Agent Jobs that I need to move but I see it also in the SSIS---> Stored Packages\MSDB\File ...
3
votes
2answers
112 views

XML queries with case insensitive tag

I have a requirement where i want XML tags to be case insensitive in my xml queries. For example: DECLARE @myTable TABLE ( yourXML XML ) INSERT INTO @myTable SELECT ...
1
vote
1answer
106 views

Prefix full text search with numbers in SQL Server

If I create the following table in SQL Server 2008 R2: CREATE TABLE test( id BIGINT IDENTITY NOT NULL, value VARCHAR(10), CONSTRAINT test_pk PRIMARY KEY (id) ); CREATE FULLTEXT CATALOG ...
0
votes
0answers
57 views

SQL instance names not shown in drop-down list when trying to remove cluster node

I was installing SQL Server 2008 R2 on a single-node cluster and accidentally installed into the wrong shared disk on the SAN. But when I tried to use the 'Remove node' from the installation centre ...
2
votes
3answers
373 views

Attempting to restore SQL Server 2005 database to a SQL Server 2008 R2 instance gives reason: 15105 error

I have a SQL Server 2005 database I am attempting to restore a SQL Server 2008 R2 SP2 machine using this statement: RESTORE DATABASE [Db] FROM DISK = N'C:\temp\sql\DB.bak' WITH FILE = 1, MOVE ...
0
votes
0answers
45 views

SQL Server Reporting Services Upgrade/Migration

I couldn't find any documentation on this. I'm currently doing a dry run of SSRS 2005 to SSRS 2008 R2 upgrade/migration. I've backed everything up (encryption key and configuration files) and did a ...
18
votes
1answer
300 views

Weird SQL Server instance crash on casting to numeric

While working with C# Entity Framework I noticed a crash of my SQL Server instance. I was able to track it down to this statement: SELECT * FROM dbo.[TestTable] where mpnr in (1099059904, ...
0
votes
0answers
85 views

Importing data from Firebird to SQL Server 2008 R2 using ODBC

I need to import data from a source table in a FireBird database to SQL Server. Our network admin has created an ODBC connection to the FireBird database. I use SQL Import Data wizard to load data ...
0
votes
1answer
51 views

Don't see the Microsoft.Jet provider in my new SQL Server 2008 R2 instance?

I moved an Excel linked server from SQL Server 2005 (it was built on Jet 4.0, excel 5?) to a new SQL Server 2008 R2 instance (64 bit environment). I matched the settings for both, but it seems the ...
6
votes
1answer
169 views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
5
votes
0answers
141 views

Excessive compile blocking on sp_procedure_params_90_rowset

A resurgence of this question Blocked-process-report: what is this waitresource "OBJECT: 32767:124607697:0 [COMPILE] on MSDN: I have caught these statements in Profiler. They all have durations over ...
2
votes
1answer
138 views

This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY

I am creating log shipping for two databases, db1 and db2. It will be from SQL Server 2008 r2 to newer version, SQL Server 2012 On the SQL 2008R2, they are both dbi_version = 661: DBCC TRACEON ...
2
votes
1answer
79 views

sql script to export table's column data to another table's column

Let's say , I have two table with same schema but different data . Table_A and Table_B . Table_A -------- ID(p_key) Number(p_key) Column3 Column4 ...
2
votes
1answer
208 views

Rights to execute stored procedure, but not read underlying tables directly

I have a stored procedure that contains lots of different columns from many different tables; I want to give 20 users access to the results of the stored procedure via Excel. Is it possible to make ...
2
votes
1answer
78 views

database mirror problem with Network Service account

I have two SQL Server 2008 R2 instances installed on same Server and the engine log on as Network Service account which is set by installation. there is a database TestDB on both instances. I try to ...
1
vote
1answer
49 views

Select Most Recently Updated Row Based on Sequence ID with Three Tables

I've previously asked how to use a sequence ID to select the most recently updated row, but I'm not quite sure how to apply it to a more advanced query involving multiple joins. Here is my current ...
2
votes
1answer
118 views

Interpretation of a column in SQL Server profile trace

In a trace table generated by Profiler I tried to determine the origin of a lock using the column ObjectID2 joined to sys.allocation_units. The results are not good. It identifies many tables that ...
0
votes
0answers
39 views

“Show table data” option to edit compact database

I have installed sql server compact edition 3.5 and i am sql server management studio to edit table.I am using "Show table data" option which comes on right click to a table of compact database. But i ...
1
vote
1answer
55 views

Server audit with more than 1.000 entries

On my Mssql 2008r2 I created a server audit for all login and logout: USE MASTER GO CREATE SERVER AUDIT audit_test TO FILE (FILEPATH = 'D:\Audit') GO ...
1
vote
1answer
172 views

Strategy for Repair Database on SQL Server 2008 R2 Standard

(one guy pushed green button instead of blue, three racks left as was his original target, probably green is nicer then blue, on this SAN are saved database files) after this accident I'm run ...
0
votes
1answer
84 views

Should I use begin tran to do an alter table column that increases the column length?

I want to execute this statement in a production database to alter the length of a varchar column from 50 to 72: ALTER TABLE Users ALTER COLUMN LoginID varchar(72) NOT NULL The column is not null ...
1
vote
1answer
59 views

Language difference in full text search for numeric strings

If I create this table on SQL Server 10.50.2550 (SQL Server 2008 R2): CREATE TABLE test( id BIGINT IDENTITY NOT NULL, number VARCHAR(10), CONSTRAINT test_pk PRIMARY KEY (id) ); CREATE FULLTEXT ...
2
votes
1answer
100 views

T-SQL Query runs quickly on local PC, but slow on SQL Server?

I have a query with a few joins and a couple of basic SUM(...) statements, and no parameters. It's been running without issue for months - but has suddenly started running very slowly on the server ...
0
votes
1answer
1k views

OpenRowSet - How to configure OLE DB Provider to be used for for distributed queries

I have an Excel file with data and would like to update a table in database based on the data the Excel file contains. To do that I want to use OpenRowSet command. But I get the error below when even ...