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

learn more… | top users | synonyms (2)

5
votes
1answer
933 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 ...
2
votes
1answer
6k 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
336 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
1k 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
46 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
149 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
3k 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
311 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
459 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
3k 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
517 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
793 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
1k 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
647 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 ...
2
votes
3answers
14k 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
1k 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
1k 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 ...
0
votes
0answers
113 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
408 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
887 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
660 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
351 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 ...
4
votes
1answer
5k 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
334 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
359 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
932 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
80 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
195 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
3k 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
261 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
329 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
1k 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 ...
2
votes
3answers
25k 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 ...
6
votes
1answer
3k views

How to migrate between schemas in bulk in SQL Server?

We currently have multiple databases but would like to combine them and, instead, separate our domain contexts using schemas. In MS SQL Server 2008 R2, how can I relocate all the contents of one ...
2
votes
1answer
15k views

SQL Server : keep track of all connections/disconnections

I have a Microsoft SQL Server 2008r2 SP1. I need to know which users connect to, and disconnect from, database. Ideally, I would like a list of (datetime, username, connect/disconnect). Can this be ...
5
votes
2answers
818 views

SQL Server R2 Standard Edition MAXDOP setting

I'm looking for confirmation/guidance on setting MAXDOP in a SQL Server instance with the following config: Version : SQL Server 2008 R2 Standard Edition Processor : 2 x AMD Opteron(TM) ...
8
votes
1answer
34k views

SQL-Server - Grant user DBO permission on database

I have a reporting database that I restore every night from a backup of the production database. When I restore, I need to add a user to the DBO role for that database. I thought this would work: ...
2
votes
1answer
2k views

sql server 2008 r2 DBCC checktable error

This morning my database went into suspect mode. I reverted back to last night's full backup to get it back online. I went through the error log and found the last command that appeared to cause the ...
0
votes
2answers
750 views

Installing SQL Server Express in parallel of Standard

On a server I have a SQL Server 2008 r2 standard performing very bad. To make a quick & dirty test I would like to try to install express edition. This server is not administrated by me, it is a ...
2
votes
1answer
707 views

SSIS FTP Sending Blank Files

I'm supporting an existing data interface that is a series of SSIS packages (SQL 2008 R2) for sending and receiving data with a third party. The communication is being done through FTP, and it uses a ...
0
votes
2answers
570 views

What locking can I expect with DBCC CHECKIDENT(RESEED)

I'm working on a project that has hit a bit of a snag: I'm replacing some replication with a home built system so that if replication goes down we don't blow our SLAs with our customers and shell out ...
3
votes
2answers
3k views

Quickly restoring a backup over top of an existing database - error “database in use”

I'm working on a set of scripts (in SQL Server Management Studio) to fix certain data inconsistencies in a customer database (SQL Server 2008 R2). I got a .bak file with the database, and I restored ...
0
votes
1answer
197 views

Backup of Job's List only

Goal: Reuse the SQL server agent jobs list. Problem: The list contains a lot of jobs and I need to reuse the jobs list in a another time without recreating it. Based on the example of the picture, ...
0
votes
1answer
510 views

Need C# Sql Server Project Set Up Guide [closed]

I am creating a window application on c# .net and Sql Server 2008 R2. I will give the setup of this application to my client but I am little bit confuse about SQL Server Database. How the database is ...
1
vote
3answers
8k views

SSRS Reports Manager Security Configuration

I happen to be setting up a reporting server at work from scrach. Having very little almost no experience of sql server administration I am finding it really difficult to configure the security ...