Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

1
vote
1answer
1k views

Partial restore to a different database

I have a database with 3 filegroups: PRIMARY, AP, and GL. I did a full database backup. I did a partial restore of just the PRIMARY and AP files successfully using this command: -- Partial Backup ...
1
vote
1answer
1k views

Partial Backup / Restore Sql Server Simple Recovery

I have a scenario where we are using SQL Server 2008 R2 with FileStream. I have partitioned the table where our files live into multiple filegroups. What I want to be able to do is backup our ...
3
votes
1answer
1k views

Restoring SQL Server Database Command Line Error

I have a restore database script in a SQL file, and I'm trying to restore a backup generated from one DB to a new DB (that exists). That SQL file is being executed by SQLCMD on the command line. The ...
1
vote
1answer
546 views

Partial backup and restore using filegroups

I have a single database that I put all tables into with multiple schemas for the different products I have. I know I can't backup and restore just one schema, but if I move all tables for a schema ...
0
votes
1answer
239 views

Remote RESTORE of database through SSMS

How can I RESTORE a database through SQL Server Management Studio by connecting "remotely" to it. If I know the "IP address" of the target machine what else do I need to know? If the port number is ...
0
votes
1answer
88 views

Remote update/restore of database through SSMS

How can I update or restore a database through SQL Server Management Studio by connecting "remotely" to it? If I know the "IP address" of machine, what else do I need to do? If a port number is ...
3
votes
2answers
894 views

SQL Server Deadlock Log Entries

We're analysing a deadlock situation on a SQL Server 2005 database and have set the trace flags 1204 and 1222. In the resulting logs we're seeing the following line repeatedly: "Log Viewer could not ...
1
vote
1answer
1k views

Isn't a tab (char(9) ) whitespace in the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3)?

If there is any sane reason to ignore trailing blanks in varchar comparisons, why doesn't that apply to tab-characters too ? On SQL Server I get SELECT case when '' = ' ' then 'true' else ...
18
votes
2answers
4k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
3
votes
2answers
2k views

How to restore SQL Server database just by dbName and physical path

I have several database backups and I want to restore all of them to a server. Can someone point me in the right direction with the SQL script to modify the logical file names to the correct path and ...
-2
votes
1answer
193 views

SQL Server reports for management

I have been placed in charged of overseeing our DB and web servers (SQL Server 2005/2008.) Not being a technical person, what reports should I request on a weekly/monthly basis to asses the health of ...
0
votes
2answers
6k views

Joining two tables on multiple criteria

I have two tables products and positions. products: id BIGINT PRIMARY KEY, product VARCHAR(100), criterium1 VARCHAR(100), criterium2 VARCHAR(100) positions: id BIGINT PRIMARY KEY, position ...
4
votes
1answer
3k views

How to write a check constraint to disallow empty varchar fields?

On SQL Server not nullable varchar columns can hold empty strings. Under ORACLE they can't, because varchar2 treat '' as equivalent to NULL. If you design a database schema suitable for both RDBMS ...
3
votes
2answers
10k views

How to install SQL Server Management Console in 2008 Express Version?

I started a dozen attempts to install the SQL Server Management Studio for the 2008 Express Version of SQL Server. What i use: Windows Vista 2007 SP 2 What i did: a) I downloaded the install file ...
4
votes
2answers
2k views

SQL Server 2012 RC installation failing

I'm trying to test out the new SS 2012 RC, but I'm having problems with the installation. My installation stops a ways in saying that the resource needed cannot be found. I'm using the ISO from MSDN ...
2
votes
1answer
545 views

Will Entity Framework and/or Linq-To-SQL queries show up as “Adhoc Queries” in SSMS' “Server Dashboard” Report?

Its pretty much all in the title. I have a SQL Server which is showing that 90%+ of CPU Usage and Logical IO Performed is due to "Adhoc Queries" The rest rest of the usage shows up under the actual DB ...
2
votes
0answers
2k views

Could not open File Control Bank (FCB) for invalid file ID X when restoring from snapshot

We have a scenario in which we, during integration tests, make a backup of an existing database and restore it under a new name. We then create a snapshot of our "new" database. After each test we ...
1
vote
2answers
1k views

SQL Server 2008 R2 - How to check Foreign Key constraints in a transaction only when committed?

We are synchronizing tables between two databases (yes, it needs to be done by our software and not by replication etc.) Both databases are identical with many foreign-key constraints on tables. ...
1
vote
1answer
2k views

Can't restore database: A transport-level error has occurred when receiving results from the server

This is not a good day for me. I have a backup (master.bak) for SQL Server 2005 Express SP4 (v.9.0.500) that I want to restore to my master db instance. I am running SQL Server in single-user mode ...
3
votes
2answers
4k views

Shared and IX locks causing deadlock (Sql server)

In my environment, I have 2 tables - Issue and IssuePropertyValues (which has foreign key reference to the Issue table). Now, we insert values into these 2 tables using the below 2 queries: INSERT ...
15
votes
2answers
977 views

Why did creating this new index improve performance so much when existing index included all columns in new index?

I have Log and LogItem tables; I'm writing a query to grab some data from both. There are thousands of Logs and each Log can have up to 125 LogItems The query in question is complicated so I'm ...
7
votes
4answers
5k views

Do SQL Server stored procedures cache data results?

I have heard this from friends, but I never investigated whether this is true. Is it true that the data results of an executed query are stored in cache? I mean, if I have a stored procedure like : ...
1
vote
2answers
222 views

SQL Server RandomNumbers Stored Procedure Error

I tried to create a stored procedure for generating random numbers but it doesn't work; the random numbers are identical in every record. CREATE PROCEDURE Update_Driver_Position @TempName ...
6
votes
1answer
259 views

32-bit or 64-bit for development and deployment?

I'm creating a Windows desktop application and want to include a database. I've chosen SQL Server Compact 4 but when I download it there are two versions (32-bit and 64-bit). My development machine is ...
3
votes
1answer
1k views

How are bit fields sorted for an index?

I am building a compound index, and I'm not 100% sure which way to include a bit field that is part of the table. The index will be for something similar to a list of forum topics, where I want the ...
6
votes
1answer
170 views

On-the-fly query/command modification

I'm responsible for administrating an instance of SQL Server 200x which has the misfortune of serving as a backend to a turnkey application written with a disgusting performance problem. It repeatedly ...
3
votes
5answers
2k views

Track deleted rows/records

A project has the requirement that it shall not be possible to lose data from certain tables. That is, if I "delete" a row, that row's information should somehow be preserved. Additional information ...
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 ...
2
votes
1answer
2k views

Disable SQL Server Log Shipping to nonexistent destination

I am administering a SQL Server 2008 instance in a dev environment. At some point before I became an administrator, someone enabled log shipping from one of the databases on this dev instance to ...
2
votes
2answers
2k views

Replace the server name in email sender address when sending email from SQL Server 2008

I'm sending emails directly to Microsoft Outlook accounts from SQL Server 2008. Everything is fine, email was sent. Only problem is that in the header of the email the SQL Server name is embedded. ...
2
votes
1answer
450 views

how to restore database from other server - sql

I have backup database from server A. Then I want to restore it in server B. I have manage to restore it, but I cannot connect to it when running my VB program. I already change the configuration ...
2
votes
2answers
614 views

SQL Server Backup Sizes Differential, Compressed Backup Sizes

I'm trying to backup my organization's production databases on SQL Server 2008 R2 Standard. I'm using a T-SQL script to backup with DIFFERENTIAL and COMPRESSION selected. I'm astounded at the size ...
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 ...
5
votes
2answers
190 views

How the indexes are managed?

I am trying to understand how the indexes are managed for below common data types: 1- Numeric (Integer, Decimals) 2- String (Varchar, Char) 3- DateTime I have few questions: 1- How the indexes are ...
1
vote
3answers
430 views

SQL Server 2008 eval expired

I have (more than one instance) some of my experiments with SQL Server 2008 eval (why eval --> brutal bureaucracy about using licenced version for testing or short life-cycle experiments), and after ...
1
vote
1answer
682 views

Replicate/Mirror SQL Server schema to slave databases

I have a empty database called MASTERDB and around 10, or more, slave databases that are copies of the MASTERDB database. MASTERDB will be always empty, as I only need the schema. No data is going to ...
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
6
votes
1answer
4k views

Whats the difference between Transaction logs and Binary logs?

Coming from a SQL Server shop, I now work with MySQL and I was curious. What are the differences between MySQL's binary log and MSSQL's transaction log? By the outlook thus far, it seems that there ...
8
votes
1answer
3k 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
223 views

MDX misunderstanding: complex FROM clause

I have a MDX query which syntax is familiar to me: SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY { ([Projects].[Id].[Id].ALLMEMBERS * [Priorities].[Id].[Id].ALLMEMBERS ) } ...
1
vote
4answers
784 views

Top 2 rows per partition from an absolute value date difference

I have a database of security camera footage in a denormalized database. I have Locations which have multiple Cameras which take multiple images. Location + Camera + image capture_date is the ...
1
vote
1answer
4k views

How do I allow Service Broker access to Master Key of database?

I am trying to set up SqlDependency on my dev system. The db on my system is a few months old production db which I have attached. When running SqlDependency from ASP.NET application, I see the ...
2
votes
1answer
129 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 ...
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 ...
1
vote
1answer
169 views

Foreign key to subtype or super types?

I'm using SQL Server and in this case i have the following tables: Restock: Foreign key to an R&D Staff member R&D_Staff: subtype from Staff Sales_Staff: subtype from Staff Shipping_Staff: ...
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 ...
7
votes
3answers
22k views

How do I run a batch file from a scheduled Job via SQL Server Agent

I'm attempting to run a (Windows) batch file from within a SQL Server agent job under SQL Server 2008 R2. In the Job Steps, I've created a new step, set its type to "Operating system (CmdExec)". I ...
2
votes
1answer
541 views

SQL Server 2008 - Return records in chronological order, given a separation factor

This question was originally posted here http://stackoverflow.com/questions/8492149/return-records-in-chronological-order-given-a-separation-factor and re-posted on this forum given a user's advice. ...
8
votes
2answers
548 views

How does SQL Server determine the order it takes locks in while selecting a table?

I have two stored procedures that are deadlocking when the system is under load. Proc A is selecting from a table while the Proc B is inserting into the same table. The Lock Graph is showing that Proc ...
1
vote
1answer
865 views

Syspolicy_purge_history connection issue

I am having an issue with the syspolicy_purge_history SQL Agent job on a 2008 SQL Server system. The server is using a non default port number and is in a cluster. Before it was put in a cluster, it ...