This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (1)

0
votes
1answer
2k views

Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should ...
3
votes
1answer
162 views

SQL Server - Using spatial data

I'm using SQL Server 2008 R2 for real-time location aware service. The service mostly receives location data (lat/lon) from a user and returns locations around the user. The data is being sent from ...
2
votes
2answers
396 views

X5687 vs X5690 CPUs for a SQL 2008 R2 Server

I have the choice between: 2 x X5687 (Intel® Xeon® Processor X5687 - QuadCore (12M Cache, 3.60 GHz, 6.40 GT/s Intel® QPI) or 2 x X5690 (Intel® Xeon® Processor X5690 - SixCore (12M Cache, 3.46 GHz, ...
2
votes
1answer
116 views

Restore diff backup only on SQL Server

Is there a way to restore ONLY a differential backup on SQL Server? I have a situation where I only need to view one table that was truncated from an entire database. And instead of trying to ...
0
votes
1answer
2k views

unique key violation occuring on a unique value combination

SQL SERVER 2008 I have a mapping table that contains a deleted date column. Since the table uses a surrogate key as its unique identifier there is a uniqueness constraint on the table. This ...
0
votes
2answers
990 views

Deny drop permissions for all other users except actual administrator - SQL Server 2008

I have a database "myDB" running in a Windows Server machine. Users (coming under Administrators group) remotely access this server and log-in to this database using Windows Authentication mechanism. ...
3
votes
4answers
5k views

In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns?

In my legacy database, a lot of the time the schema needlessly allows null entries for particular columns. To help find out which columns in particular, I need to do some querying of SQL Server 2008 ...
10
votes
1answer
3k views

Empty Strings: Why or when is '' equal to ' '?

Who can explain why select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' '); yields ----------- ----------- ----------- ----------- ----------- 1 ...
1
vote
1answer
1k views

SQL Server 2008 - Cannot create index on Indexed View

I am attempting to create an index on a view. The view is contained in a different schema than the underlying table it's based on. When I attempt to create the view, I get the following error: Msg ...
4
votes
1answer
4k views

Backing up database without transaction log

When I backup a database is there a way to exclude the transaction log in that backup? Or am I thinking about this wrong and to restore the original database the content of the transaction log is ...
4
votes
1answer
240 views

Can I attach a SQL 2008 R2 database to SQL 2008 that is not R2?

Can I attach a SQL 2008 R2 database to SQL 2008 that is not R2? I have a database that I am detaching from SQL 2008 (not R2) and can attach this to SQL 2008 R2. Can I then detatch the database from ...
10
votes
1answer
3k views

How to find corrupt page in SQL Server 2008

I know I can execute a DBCC CHECKDB and get a status of my database. Questions How can I find if there is some corrupt data page into my database? If an error is throwndue to page corruption, where ...
4
votes
1answer
340 views

Sql Server Enterprise 2008 R2 Partition Switch with Xml Index

Ok, sql db partitioning and switching doesn't seem to be a highly discussed topic, but it is something I'm implementing. I've had success in all areas of partition functions, partition schema, file ...
2
votes
2answers
520 views

Can I implement clustering with just 2 servers and one NAS?

I have the following architecture, it consists in only 2 servers, connected to a NAS. I'm having trouble in implementing clustering because I can't have a domain controller in any of them (if I do ...
1
vote
1answer
6k views

reindex database in sql server 2008

The best way to reindex 500GB database in SQL Server 2008 Is it by using Maintenance plan? Would I face performance problems while reindexing?
3
votes
3answers
5k views

Why so many logical reads?

I have a very simple query that is showing in Activity monitor and other statistics as having the most logical reads on my entire DB server. SELECT MAX(RESULT_DATE) FROM TABLEX mm WITH (NOLOCK) JOIN ...
3
votes
1answer
534 views

Can you set up mirroring for sql server reporting services?

I have mirroring set up for our SQL Server 2008 R2 Standard database server. Questions How do I ensure high availability for reporting services if the server goes down? Is there a kind of mirroring ...
3
votes
2answers
2k views

Default SQL Server 2008 locking behaviour

I am using SQL Server 2008. What is the default lock behaviour with UPDATE? NOLOCK? ROWLOCK? PAGLOCK? How can I tell what the current level of lock is for a table? Thanks, Bruce
2
votes
1answer
171 views

SQL Server Express 2008 installation problem

I just installed SQL Server Express 2008 on my Windows 2003 server. The sql base is started, but I look in config manager and my SQL Server browser is not started. I can't start it. When I try to ...
4
votes
2answers
294 views

Same queries, different servers, different exec plans, stats are up to date

I've got the same query running on two different servers, and getting vastly different performances. I have updated all of the stats on dependent objects and it has not fixed the issue. I'm lost on ...
5
votes
1answer
191 views

Composite or Single-Field Clustering Key

My DBA and I are having a disagreement on index structure. Consider data for a medical claim... We have the Header table with fields like: ClaimId (varchar(50)), PaidAmount, MemberID... and for ...
2
votes
1answer
859 views

How to Backup and Restore Filestream Data

I am trying to backup and restore my database which includes a filestream filegroup. I backed up the database without the filestream filegroup and was interested to know what happens when you restore ...
2
votes
2answers
993 views

How to Test Log Shipping

I am evaluating log shipping as a solution to keep a permanent failover copy of our live database. It looks ideal. We are using Filestream which is part of the reason to use it over mirroring. I ...
4
votes
3answers
5k views

Stored procedure executes too long

We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
8
votes
1answer
4k views

Is it possible to create an alias or synonym for a database?

Is it possible to create a database alias or synonym? What I am hoping to be able to do is have NewDatabaseName aliased as OldDatabaseName to prevent having to rewrite a ton of code. Backstory: I ...
2
votes
1answer
132 views

Is execution of synonyms not logged in target database?

I've been working on an issue where a single select within a stored procedure failed due to permission-issues, and we traced it to a call to a Synonym: USE DatabaseA GO CREATE SYNONYM ...
1
vote
1answer
1k views

SQL-Server: sp_spaceused gives zero rows but big dataSize for cleaned table

After cleaning my table with DELETE FROM MyTable (and executing DBCC shrinkdatabase('MyDB') should that matter) I run the statement EXEC sp_spaceused MyTable. The results confuse me: tableName ...
0
votes
1answer
3k views

Run SQL Server Snapshot Agent automatically

I am trying to setup automatic transactional replication. Basically I have some script files that are executed after each other. They set up a replication from one server to another and this works ...
4
votes
2answers
1k views

SSAS queries time out when Windows File System Cache drops

We have a SQL 2008 Analysis Services instance on Windows 2003 that is experiencing near-daily performance issues (query timeouts) during peak activity. A second identical instance queried from the ...
4
votes
2answers
137 views

Are my Indexes Being Used?

I am using SQL server, and the person creating our database added lots of indexes on our biggest table. These indexes only include one field each. To me it seems that these indexes are mostly ...
2
votes
2answers
4k views

Reducing Key Lookups

I am using SQL server, and I have been looking closely at the concept of key lookups, ...
0
votes
2answers
186 views

Cant connect to Server using SSMS on my desktop

SQL SERVER 2008 R2 When attempting to connect to the default instance of sqlserver on our local server from SSMS on my desktop I'm receiving the following error. A network-related or ...
0
votes
1answer
836 views

What privileges do I have to grant to allow a user to select from information_schema.table_privileges?

I'm using MS SQL Server 2008, and I am trying to get a complete listing of all privileges assigned to all grantees by running this query: select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES However, ...
1
vote
0answers
513 views

DBCC CheckDB Consistency error on an empty table [duplicate]

I restored a DB from SQL Server 2000. I ran DBCC CHECKDB on SQL Server 2000, and it did not report any errors. What could be the reason behind a DBCC CHECKDB reporting a consistency error on a empty ...
1
vote
2answers
4k views

Can't connect to/from SQL Server 2008 from my Windows 2008 server

I have a server with: Windows 2008 Server SQL Server 2008 enterprise From my server using SQL Server Management Studio, I can't logon to any SQL Server and vice verse, I can't connect to my SQL ...
1
vote
1answer
76 views

SQL Server 2000 query error

When I attempt to run on the SQL Server 2000 to a query which was developed for SQL Server 2008,I receive the following the error. ambiguous column name SucNo Here is my query: SELECT ...
1
vote
2answers
2k views

SQL Server 2008 - Log file out of control & cant shrink it [duplicate]

Possible Duplicate: Why Does the Transaction Log Keep Growing or Run Out of Space? I am having trouble with my database, the log files is currently 250GB in size, I need to shrink this down ...
2
votes
2answers
1k views

SQL Server 2008 simple recovery mode issue

During my daily maintenance backup task, I am attempting to set specific databases to recovery model simple along with shrinking the database. ALTER DATABASE db_name SET RECOVERY SIMPLE GO DBCC ...
4
votes
4answers
870 views

What's the equivalent of --safe-updates of mysql in MS SQL Server?

In MySQL, you can use the feature called --safe-updates (--i-am-a-dummy) to limit the number of rows updated per query. http://dev.mysql.com/doc/refman/5.0/en/mysql-tips.html#safe-updates Is there ...
16
votes
4answers
11k views

Should dbo schema be avoided?

When it comes to the dbo schema: Is it a best practice to avoid using the dbo schema when creating database objects? Why should the dbo schema be avoided or should it? Which database user should own ...
5
votes
3answers
203 views

How to monitor how much data is transfered in a linked server?

I have a linked server to a remote computer and doing some insert, update and delete operations between local and remote databases. I want to know how much data is transfered when operation is doing. ...
0
votes
2answers
837 views

In SQL Server 2008, is possible to one database subscribe 2 publications for the same database? (merge replication)

I'm trying to set up an high availability architecture using merge replication. I have 3 databases, and I'm trying to keep them all up to date with merge replication. So far I've manage to create one ...
1
vote
1answer
305 views

Is my case statement broken by the use of outer apply?

I'm struggling with a case statement not working. Simple enough I need to supply a value of 0 where the question marks are. I have several other case statements which handle separate columns, but this ...
4
votes
2answers
2k views

Can I have different publishers and one subscriber in replication?

I have the same databases in three cities which each database have three tables: 1- tbl_worker 2-tbl_workshop 3-tbl_users and cities are:city1,city2,city3 city2 and city3 insert informations in ...
1
vote
1answer
769 views

Can't run query using “localhost\sqlexpress”

I am trying dynamically build an SQL Statement that includes linked server. That works. When I use "localhost" to reference the current local machine SQL Server, that doesn't work. For example: ...
2
votes
2answers
2k views

How To Restore A Replicated Database

I am replicating our live database using transaction replication. This is basically to have a backup copy of our database in the case of server failure. The replicated database is read only, so I ...
3
votes
1answer
265 views

Does blocking always mean open transaction?

This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
1
vote
1answer
334 views

How to take Snapshot of Restoring Database

I have set up log shipping with my database to make a backup. The backup copy of the database is in a status called '(Restoring...)'. I understand this is to be expected? Is there a good way I can ...
6
votes
3answers
23k views

Read XML file in SQL Server 2008

How can I read an XML file and store the data in XML to our table in SQL Server 2008?
3
votes
1answer
560 views

SQL Globalization - DateTimeOffset

What is the recommended best practice when it comes to storing date/time for an application that needs to support multiple timezones? Which is best ? Use the SQL DateTime data type and insert ...