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

learn more… | top users | synonyms (2)

2
votes
3answers
520 views

SQL Server 2008r2 replication from multiple sources

I am currently rearchitecting an application so that each of seven businesses will have there own "live" SQL Server 2008 database and at scheduled times during the data the data from all seven ...
3
votes
2answers
491 views

Performance differences between executing via Linked Server vs. SSMS

We've had an issue reported where a stored procedure was behaving badly when called from some .Net code. On the face of it, that sounds similar to this case, which turned out to be due to EDMX, but in ...
2
votes
2answers
436 views

Looking for a way of moving millions of record without impacting the transaction log

I need to move around 10 million records out of a table into a new "optimized" table, but I need to keep the database online. What would be the best method in doing this without impacting the ...
2
votes
1answer
424 views

Is there a way to view the body / contents of a system function in sql server 2008

For the sake of leaning and exploring, I'd like to look at how some of the system functions are built. Is there a way to do this in sql server 2008 or R2? I've tried sp_helptext 'Sum' but I get an ...
1
vote
3answers
95 views

Need to figure out what FKs point to a specific table

In SQLServer 2008 r2. I need to figure for a specific table what FK's in other tables point to it. I try: SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS But this does not give me what I ...
5
votes
1answer
1k views

How does objectproperty() figure out if a function is deterministic in sql server?

How does sql server determine if a function is deterministic or not? Consider the following function, I think it's deterministic (as it's ultimately an integer operation), but sql server returns 0 ...
2
votes
1answer
659 views

sys.sysprocesses versus DMVs

I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and ...
3
votes
2answers
2k views

Keeping “SQL Trace was started” and “SQL Trace stopped” from filling up my SQL logs

I have a SQL2008 server that runs a custom application that starts and then stops a SQL trace every 60 seconds (it does some reporting and summarization on the trace data). The problem is that my SQL ...
1
vote
0answers
89 views

Managing SQL 2008 database [closed]

We have a vendor application that creates a new database monthly for only few tables. Vendor doesn't have solution. Is there any other way besides partitioning as partitioning require enterprise ...
2
votes
3answers
143 views

Document versioning

I want to store in SQL Server 2008 XML files, but in a way that versioning is supported. For example, I have a XML document and I save it in the DB(in the XML datatype column). After that, I edit it. ...
1
vote
1answer
8k views

Getting deadlock information easily

I know to check for live deadlocks select * from sys.sysprocesses where blocked>0 However, that is only for deadlocks that are current. How do we check for deadlocks from the say the last 24 hours? ...
2
votes
2answers
3k views

SQL Server 2008, switching to simple recovery mode

Currently our transaction log is 50 GB; impressive huh? I want to switch to simple recovery mode and remove the transaction log entirely. Do I need to stop any user activity and detach the database. ...
5
votes
3answers
792 views

Is SQL Server 2008 Change Data Capture (CDC) possible NOT to keep record when inserting?

SQL Server 2008 introduces a new feature for data auditing. The basic operation of this feature is as below: When CDC is enabled on a table, SQL Server creates another table (so-called cdc table ...
3
votes
1answer
523 views

Maintaining replicated data in a staging-to-warehouse environment

I am trying to come up with a way to move metric data from the staging server to the data warehouse servers. I have a bunch of dimension tables that gets populated during the ETL process (on the ...
2
votes
2answers
1k views

How do you block ad-hoc distributed queries from specific sql server instances?

For one reason or another, a particular development SQL Server is unable to openrowset or opendatasource to production servers, but can hit other dev servers. I've tried sqloledb and sqlncli ...
1
vote
2answers
810 views

SQL Server 2008 R2 - user disk quota

Is it possible to apply restrictions on how much data a user / login may store in a database? I found no way to apply such restrictions to schemas, for instance. If there is no built-in mechanism ...
30
votes
9answers
73k views

Passing array parameters to a stored procedure

I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm ...
2
votes
0answers
229 views

Change tracking on, but columns not tracking

In SQL 2008, I have change tracking turned on both on the database and table levels, with column tracking turned on at the table level. The CHANGETABLE(CHANGES...) function returns the expected ...
2
votes
1answer
137 views

How to improve this query (using table variables or not?)

I'm having a bit of problems with these tables... TableCost ( TableCostId int, Cost numeric, MonthsId int ) and Taxes ( Percentt numeric, MonthsId int ) I have to calculate a new value by ...
5
votes
1answer
1k views

What is the optimal way to design an approval queue and audit trail for entities in SQL Server 2008 R2?

Simplified Explanation: I have three main tables: customer, phone, and customer_phone to resolve the many-to-many. I need to ensure that only admins can insert/update these tables without approval. ...
3
votes
1answer
158 views

Is it possible to install a single instance of sql server on a server with 2 NICS and 2 different subnet?

I have a server that has 2 NICS and each is connected to 2 different subnet. Is it possible to connect to this Sql server in each of the different subnet? If so, what special procedure do I have to ...
1
vote
3answers
440 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 ...
2
votes
1answer
207 views

Varchar to date in SQL Server 2008 [closed]

I have varchar data in database table which stores date in DD-MM-YYYY format. I want to select that varchar in MM-DD-YYYY format. I'm using select convert (varchar(30), (select CONVERT(datetime, ...
7
votes
1answer
564 views

Adding SPARSE made table much bigger

I have a generic log table, about 5m rows. There's a "strongly typed" field that stores event type, and a bunch of "losely typed" columns that contain data relevant to the event. That is, meaning of ...
2
votes
2answers
917 views

How do I determine what object two SQL process are contending over?

When long running Query#1 exclusively locks an object (table, index or page), and then Query#2 is executed by another user and becomes blocked on the lock held by Query:1, how would I get the name of ...
6
votes
2answers
3k views

sp_executesql with user defined table type not behaving correctly

Problem Is there a known issue with user defined table types as parameters to sp_executesql? Answer - no, I'm an idiot. Set up script This script creates one each of table, procedure and user ...
1
vote
1answer
416 views

Giving selective permissions in a database

I have a user [AD\dbusersgroup_limitedrights] that should have on two schema's [ICD] and [XCD] within a database only SELECT and EXECUTE rights, however for each of the users in the ...
9
votes
4answers
5k views

Query becomes drastically slower when wrapped in a TVF

I have a fairly complex query which runs in just a few seconds on its own, but when wrapped in a table-valued function, it's far slower; I've not actually let it finish, but it's run for up to ten ...
16
votes
3answers
458 views

How can I guarantee that inserts to SQL Server 2008 R2 are cached in RAM first?

Imagine a stream of data that is "bursty", i.e. it could have 10,000 events arrive very quickly, followed by nothing for a minute. Your expert advice: How can I write the C# insert code for SQL ...
5
votes
1answer
2k views

Why does it take so long to calculate an Execution Plan?

One of our customers has just upgraded to a new server. For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second. ...
2
votes
2answers
97 views

Is it Possible to have Multiple Named Instances with No Default Instance?

Using SQL Server 2008 R2, is it possible to install multiple named instances, and no default instance?
1
vote
2answers
7k views

Single User mode can't be turned off and no users are connected

I have a database that is in Single User Mode. When I run this: exec sp_dboption 'MyDb', 'single user', 'FALSE' It fails, telling me that there is still a user connected. But when I run both of ...
0
votes
1answer
153 views

Merge Replication Invalid Object ( on an object that no longer exists ) ComErrorException

My merge replication scheme has suddenly started failing. When trying to sync it complains that there is an invalid object. 'dbo.myfunction'. The function used to exist but was removed. There was ...
4
votes
3answers
5k views

SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc

I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup ...
7
votes
3answers
7k views

Executing Stored Procedure that accesses another SQL instance

I apologize if this question repeats another already asked. I have searched for hours and have not found one that fits my situation. Desired Outcome A user using SQL authentication has execute ...
0
votes
1answer
2k views

sp_dts_getfolder isn't in my MSDB database, any ideas why?

We're currently moving our applications from a SQL 2005 to a SQL 2008 R2 database and everything has gone well(ish) but now one of our applications calls a stored proc called sp_dts_getfolder which is ...
0
votes
1answer
46 views

How can I disallow restore from backup on SQL Server?

I want to disallow restore from backup. How can I do this? One Of my friends said to me "Take a certificate for your database and anyone cant restore from your backup" I searched about ...
1
vote
2answers
399 views

Can I physically split a SQL Server database?

I seem to remember some time ago reading an article saying I could split a SQL Server database into more than one file, allow me to elaborate. I have a database that has a dozen or so tables that ...
9
votes
2answers
702 views

How many Sproc Parameters is too many?

I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
3
votes
3answers
6k views

How do i set up an Alert that tells me when a replication of data failed?

I've got Database Mail setup and tested fine. Alerts do work when I stop and start the replication agent job, but I cannot get it to throw any alerts when data moves from a publisher db to a ...
1
vote
1answer
220 views

Sending mail using DatabaseMail in SQL Server 2008

I have a profile called GPMail, and previously there was an account called comments@guitarplayer.com, which was working as expected. Now I want one more account with the same site, so instead of ...
0
votes
2answers
51 views

Failure to log in using just the server name

I have more than one PC connected by LAN that I access from Server Management Studio. When I try to connect with the DB server, if I place the server IP instead of the server name in the serverName ...
2
votes
2answers
591 views

T-SQL tool that requires a semicolon to end statements

I have what is probably a rather unusual question. Is there a T-SQL tool that requires using a semicolon to terminate statements? I know that it's not required to use semicolons with SQL Server 2008 ...
1
vote
3answers
465 views

How to implement superkey sub key method on cascading tables

If I design a database with a securitymaster table that has all the common columns and then have a final column called securitytype which acts as a reference to the sub table, how is it possible to ...
3
votes
2answers
5k views

SQL Server 2008 T-SQL select hanging, but not dead locked

This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
0
votes
1answer
471 views

Coalesce Function Returning Duplicates

I have a many-to-many relationship setup between three tables. CompanyNotices (M-M) CompanyNoticesLocations (M-M) Locations I would like to retrieve all rows from the CompanyNotices table for ...
2
votes
1answer
334 views

Immediate e-mail alerting for error 229 (permission denied)

For a few reasons, I'd like to have all permission denied errors (number 229) emailed to me. Checking master.sys.messages shows that is_event_logged for this error is 0. Thus that rules out using SQL ...
5
votes
1answer
814 views

NHibernate Parameter Sniffing, SQL Server 2005 vs SQL Server 2008

NHibernate automatically uses sp_executesql with different paramters to execute it's data. Now what we're noticing in production is that we're beeing hit by parameter sniffing sometimes. In such a way ...
1
vote
2answers
3k views

SSMS: Show execution plan for a stored procedure as a single tree

I execute a stored procedure with an option "Include Actual Execution Plan". The execution plan always is represented by multiple statements each of which has its own execution plan. MSDN states the ...
8
votes
5answers
8k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...