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

learn more… | top users | synonyms (2)

1
vote
1answer
439 views

Slow running query with inner SELECT in where clause

I have a query that looks like the following: USE DATABASE3; GO SELECT t1.COL1 AS COLUMN1, t2.COL2 AS COLUMN2, t1.COL3, t1.COL4 AS COLUMN4, t1.COL5 AS COLUMN5, ...
3
votes
1answer
260 views

Why am I getting blocked when I call OBJECT_NAME(object_id, database_id) from sys.dm_tran_locks?

I'm working on a query to tell what objects have locks from a given session and I'm running into some blocking issues. Here is my basic query: SELECT request_session_id AS session_id, ...
3
votes
1answer
124 views

Log Shipping in lieu of replication

I have two SQL Server 2008 R2 systems that, due to network restrictions, cannot use SQL Server replication. However, data needs to be shared between these systems. It may be possible to send a file ...
3
votes
1answer
123 views

Should I align my SQL Server file and growth sizes with the block sizes on my SAN?

It was brought to my attention that our (Hitachi) SAN handles data in 42MB blocks. It is tiered storage so each block is evaluated when the director makes decisions as to when it should promote/demote ...
3
votes
1answer
274 views

How can I compare the server impact of transaction replication

We are developing a system that must extract data from an existing SQL Server 2008 R2 cluster. One of our design goals is to limit the performance impact on that cluster as much as is practical. ...
3
votes
1answer
374 views

SQL Server Backup and Restore Incrementally

I am investigating a reporting solution for a production database. We currently have a production db which backs up fully at 01:00 each day. This backup is then copied using a Robocopy job to a ...
1
vote
0answers
97 views

How to set a 'real' field to accept an out-of-scale value?

I have an SQL Server database and one particular field that is a real. The program that feeds it was modified and is now sending a value too small to be considered a real. Is there a setting for ...
0
votes
1answer
66 views

Comparing Environments [closed]

I am a SQL Server dba. I have been asked to compare our production environment to test from a number of different perspectives. These are windows patch level,sql service pack, to database size and ...
21
votes
2answers
88k views

SQL server databases stuck in restoring state

I have a Sharepoint server. I guess our network administrator has miss configured his back up tool (I guess he uses Symantec backup exec) and now some of my databases are stuck in restoring state! ...
0
votes
1answer
4k 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 ...
4
votes
2answers
3k 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
862 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 ...
11
votes
2answers
29k 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
317 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
504 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
361 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
3k 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
230 views

SQL Server 2008 R2 replication server runs faster than primary

I run a simple query like select * from table and I get the following results: primary database : 90 seconds replication database : 2 seconds The tables are identical and have the same indexes in ...
2
votes
1answer
114 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 ...
5
votes
1answer
852 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
5k 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
0answers
319 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 ...
2
votes
1answer
933 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
141 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 ...
3
votes
1answer
2k 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
267 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 ...
1
vote
1answer
362 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 ...
4
votes
1answer
2k 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
470 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
632 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
2k 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 ...
4
votes
2answers
3k 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 ...
1
vote
1answer
885 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
568 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
3answers
12k 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
967 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
894 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 ...
3
votes
3answers
4k 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 ...
1
vote
0answers
107 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
397 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
1answer
692 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 ...
8
votes
1answer
2k 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 ...
14
votes
1answer
598 views

Excessive compile blocking on sp_procedure_params_90_rowset

A resurgence of this questionon MSDN: Blocked-process-report: what is this waitresource "OBJECT: 32767:124607697:0 [COMPILE]" I have caught these statements in Profiler. They all have durations over ...
4
votes
1answer
2k 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
317 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 ...
3
votes
1answer
4k 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
0answers
312 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
325 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 ...
4
votes
1answer
818 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 ...