SQLServerCentral / SQL Server 2005 / SQL Server 2005 Strategies

Deleting Large Tables

johnsonchase7 — Fri, 06 Nov 2009 11:12:59 GMT

Quick question, what is the best thing to do with a database to save space after after deleting large tables from it? Especially since the dbcc index defrag option is no longer supported in the sql server 2008. What about the database shrink option?

Membership constraint

stricknyn — Thu, 05 Nov 2009 22:19:27 GMT

Hello all,I believe this can be done, but wanted to make sure. Ok I have two tables. The Dues table contains the member dues owed by each memeber in a non profit organization (This may difffer between members). The Payment Table contains all the payments the members have paid. There is a relationship between these tables. I wanted to make sure there is a constraint on the Payment table that will not allow the sum of the payments for a member in the payment table to be greater that that member's dues owed in the Dues table.Is there a way to add a constraint like this? If so, how would I go about doing that?Thanks,Strick

3rd-party enterprise-level backup tools - Should DBAs still be in charge of backups/restores?

Marios Philippopoulos — Mon, 06 Apr 2009 11:18:25 GMT

With the advent of third-party enterprise-level backup solutions, such as Symantec or CommVault, how justifiable is it to maintain that DBAs should still be the owners of the database-backup/restore process? Some of these (enterprise) backup tools include the entire O/S system in the backup plan, in addition to database files. In a company using a tool like this, one might argue that the responsibility of database backups and restores should now belong to the system administrators or operations staff or a specially designated backups team. The DBA team might then be shut out of one of the most important roles of the DBA, which is data security and data access management.What do people think about this? How important is it for DBAs to stay in control of database backups/restores, even if the tools used are third-party tools that do system-level backups?I'm a DBA BTW... ;-)

Recording CPU. Disk I/I, memory to help determine SAN configuration

Paul Perez — Fri, 23 Oct 2009 09:22:00 GMT

Greetings,I have been looking for ways to record CPU and disk I/O on one of my SQL 2005 servers to determine a SAN configuration, but have not found anything concrete. I have used PerfMon but I'm not quite sure how to interpret the results I'm getting. I have also run the Glenn Berry scripts but again - not sure how to interpret the results. Any idea where I can get some clarity on all of these things or a simpler way to obtain the current CPU and disk I/O capacity or bottlenecks for my server?thanksFor instance,A particular data gathering from one of my servers go like this..Cache faults/sec: 2 (with spikes on 145)Page Faults/Sec: 5 (with spikes on 152)Pages/sec: 0Paginf file: 2.841Avg. Disk Queue Length: 0 - 0.336Processor: 1.9 - 19Bytes Total/sec: 0Processor queue length: 0threads: 555thank you

Reporting Services 2005 - Keep Together option?

Erin-489205 — Thu, 05 Nov 2009 08:01:45 GMT

The problem I'm having is that a user's data spans multiple pages. It starts on the bottom of 1 page (for 1 transaction) and then continues onto the top of the next page (3 more transactions). How do I keep the data together on a page? In Crystal there is a keep together option on groups.In the GROUP properties, there is a setting for pagebreak at start and / or page break at end, but it puts a page break after every grouping. One grouping per page. I don't want to orphan small group sections over 2 pages.

Executing a SQL Job based on the Current Job Status

lbodine — Wed, 04 Nov 2009 13:21:18 GMT

I was faced with a situation where a SQL Job that runs a batch of 5000 records has to restart itself right after it is completed. Setting a schedule(i.e every 10 minutes) would not work because the job execution time varies. My solution was as follows:Create a table(job_status) that will hold the results from executing xp_sqlagent_enum_jobs. The value of the STATE field will show the current status..1 = executing 4 = not executing.Use a trigger on the table to start the SQL Job when the STATE value is 4[i]BEGINWAITFOR DELAY '00:00:10' IF( select State from job_status WHERE job_id = 'xxxxxxxx')=4 exec msdb.dbo.sp_start_job @job_name='yyyyyy'END[/i]To populate the table I run a SQL Job that executes every minute[i]SET NOCOUNT ONtruncate table job_statusinsert into job_status exec master.dbo.xp_sqlagent_enum_jobs 1,t[/i]I added error handling and notifications, for instance if the Job 'YYYY' fails the Job_status job will get disabled and a notification is sent.

decimal or money

repent_kog_is_near — Mon, 19 Oct 2009 19:20:52 GMT

I am designing a table with lot of dollar values. What are the pros/cons to use decimal Vs money datatype in SS2005?

duplicating data across servers

siemoe — Mon, 02 Nov 2009 12:44:05 GMT

I need to access data that resides on an externally hosted server. I come from an application background and have limited SQL Server (especially 2005) experience (basically I know just enough to be dangerous). I've been reading up and taking DVD training, but I'd be a fool not to ask for assistance.SRVR01EXT is outside our firewall and supports an externally hosted application (APP01). My group isn't involved with this project, but it is our understanding that our firm has no control at all (ie, we can't create Publications on the server).SRVR01INT is inside our firewall and is the only server with access to the external server. My firm controls this one, but only one person has access and he is neither a SQL Server expert, nor a member of my group...although we can tell him how to set it up for us.SRVR02 is inside our firewall and supports an internal application (APP02) which will consume data from SRVR01EXT. Our firm controls this one and my group has full admin rights.Performance permitting, we would like SRVR02 to include realtime data from SRVR01EXT; otherwise, a scheduled snapshot could be used. The data, which consists of only 1K parent records and several related tables totaling less than 100K records, has been exposed for us by way of Linked Servers and Views on SRVR01INT. We tried using Linked Servers and Views on SRVR02 (daisy-chained to the Views on SRVR01INT), but performance isn't acceptable. We are considering Indexed Views, Replication and Packages. Any suggestions?

dynamic xml deserialization in a stored procedure

mcginn — Thu, 29 Oct 2009 13:16:42 GMT

I am passing data to a store procedure in an XML string. I am inserting into tables with insert into .... select ... from @myXML.nodes() as X(nref) syntax. I want the value defined for nodes to be dynamic.I have: insert into table1 (aa, bb) select nref.value(...), nref.value(...) from @myXML.nodes('//root/dataDef1') as X(nref) insert into table1 (aa, bb) select nref.value(...), nref.value(...) from @myXML.nodes('//root/dataDef2') as X(nref)With the literal string in the nodes() clause I need to either have 2 statements in a stored procedure or 1 statement in 2 procedures. I wantto make it dynamic such asset @message = '''' + '//root/'+@var+''''where @var is passed to the procedure with a value of either dataDef1 or dataDef2. The from clause would then be from @myXML.nodes(@message) as X(nref)But I get the message:Msg 8172....The argument 1 of the xml data type method "nodes" must be a string literal.Any advice on how to resolve this error condition?

Integrating SQL backend systems with Cloud applications

SO-462683 — Thu, 29 Oct 2009 10:46:32 GMT

So my company is "in the cloud" with CRM. I am looking at ways to integrate our existing ERP system which is currently running on SQL 2005. Today, I have multiple jobs that export CSV files directly to the cloud vendor and am looking at replacing that batch processing to API's. Now it seems like everyone wants to sell me middleware or additional software to accomplish this.Anyone else out there integrating in-house SQL systems with applications in the cloud? What has been your experience? Is it absolutely necessary to purchase and implement integration tools when I already own the cloud vendor apis?Thanks and I'm sorry if this is a no-brainer but I really would like to get SQL expert opinions about this :)

Create database tables for small cigarrette store

dhoang101 — Thu, 29 Oct 2009 16:35:01 GMT

I need help. I want to create an inventory database for a cigarrette store. Here are the different products sell in this store and I need help creating the tables and relationship. Please help.1. Products> a. Cigarrettes> b. Snow_Cones> c. Soda_Drinks> d. Candies> e. Chips> f. Cig_Lighters > >2. Cigarrettes> a. Brand> b. Type> c. Price> d. Qty>3. SNOW_CONES TABLE> a. Flavor> b. Cups> c. Spoon> d. Ice> e. Price> f. Qty>4. SODA_DRINKS TABLE> a. Type> b. Price> c. Qty >5. CANDIES TABLE> a. Type > b. Price> c. Qty>6. CHIPS TABLE> a. Type> b. Price> c. Qty>7. CIG_LIGHTERS TABLE> a. Price> b. QtyHere's what I have so far:PRODUCT TABLE ProductNo ProductName (PK)ITEM TABLEItemNo (PK)ProductName (FK) Brand Type Package_Qty Single_Qty Price

Views or Triggers when Migrating Data

lucidspoon — Wed, 28 Oct 2009 10:37:54 GMT

I'm trying to compare two different ways of migrating data from one set of tables to another. By migrating, I mean if I set up a table structure, but then based on how the system is used, I realize the structure isn't as efficient as is should be, so I redesign it and want to get all the data moved over and all of the DB code switched to the new tables, meanwhile keeping the data synced between the two sets of tables so the change can be gradual.In the past, what I've done is to create the new tables, migrate the existing data, and then set up triggers on the initial tables to update, insert, and delete the records in the new tables. Then, I go through every function and stored procedure that is selecting data, and update those to select from the new tables. Once I've tested (and had other people tested) that, then I go through and start updating any code that is updating, inserting, or deleting data. That way, there is no downtime and everything can be fully tested and not rushed.The main problem with that approach is that there's always the chance that I missed something when creating the triggers, so the data gets out of sync.My other thought is to create a view that will just pull the data into a structure that matches the new tables. This should keep everything synced between the original tables and the view at all times. Then, it also wouldn't matter if I switched all of the selecting code before the updating code, because the view could update the original tables as well and still be in sync.I've heard people before saying that this is a good approach when migrating data, but I'm not clear how to go from the view to the actual new tables without essentially taking the system down to update the code to go from table_structure_view to table_structure and load all the data. In most cases, it shouldn't take more than 10 minutes or so I wouldn't think, but our database is accessed 24/7, so downtime is avoided at all cost.I guess my question is there a good way to "swap" a table and a view in this type of situation?

Tool for DATA MODELLING

San-847017 — Thu, 29 Jan 2009 00:46:07 GMT

Dear All, Can anyone suggest me a good tool for DATA MODELLING pls...?I have never used one. Thanks in advance.Santhu.

Creating a listener to update another database

James Tech — Tue, 06 Oct 2009 17:29:31 GMT

Hi,I am hoping that this forum would lead me to a correct direction. We are currently designing a process in where we have two databases, one for maintenance ([b]Database A[/b]) and one for production ([b]Database B[/b]). Per databases resides a table named [b]Item[/b], the item table has flag column which indicates if a data is ready to be inserted to the production database (Database B) or it can be by scheduled insert to database B.Flag column can have values "Ready" and "Scheduled", if it is a scheduled insert then we will refer to its scheduled date which is also one of the Item table column.I need to create some sort of a listener or something that will check if there are any changes with the [b]Item[/b] table and will do the following task I have describe above. Any suggestions on how to efficiently handle this kind of process is always welcome.Thanks for your time.

Check Constraint Person Relations

luisxvarg — Thu, 22 Oct 2009 01:17:43 GMT

Hi Guys. I have 2 tables. TPerson(PersonID, ...) and TPersonRelation(PersonRelationID, Person1ID (FK1), Person2ID(FK2), TypeOfRelationID). All fields are integers. The relations are the follow: PersonID --> Person1ID and the same PK Fields PersonID --> Person2ID. How can I implement both relations, if SQL Server don´t allow me to do 2 relations from the same field (PersonID). A trigger could solve this problem, but ... Do you know if exits a more better solution than the trigger?I need to:1)check Parent Child Relation, cascade deletion. 2)build an index with unique key using Person1ID and Person2ID(FK2).Thanks in advance.. Luis

Need suggestions on possible reinstall of sql server 2005

peggy.gaines — Mon, 12 Oct 2009 21:33:32 GMT

I've acquired and been charged with 'fixing' a 2-node cluster (active/passive) sql server 2005 environment with the database instances residing in a SAN. I've met with a few techs that have worked at this organization longer than I have and found out the following:- No documentation from initial install- sa password unknown (running mixed mode)- passive server is actually running as active (continual problems when actual active runs with applications)I have also ran the MS Best Analyzer for SQL Server 2005 and compared the environment against a SQL Server 2005 Clustering Best Practices and identified the following:- Installations not identical between the two servers (SQL Services, Accounts). - The current SQL Server 2005 authentication mode is ‘mixed mode’. - Currently, several SQL Server services run under one service account - Several database instances within our Enterprise SQL Server 2005 installation are tied to a previous contractor (shows as owner of several database instances). More investigation is needed to determine how to proceed with this issue.With all the issues listed above, my initial idea to 'fix' this environment is to reinstall cluster environment, uninstall this sql server application, create necessary service accounts and reinstall sql server 2005 (same apps on both servers) with windows authentication. MY CONCERN is this...the data!!! My thought is that since the data resides in the SAN, once we zone the cluster environment back into the SAN that once we reinstall the sql server 2005 application, we should 'see' (be able to access) the data. Is that correct??If anyone has any experience, recommendations, suggestions with this PLEASE, I APPRECIATE YOUR ADVICE!!

Questions about bulk data movement

sqlislife — Wed, 14 Oct 2009 12:22:22 GMT

Hi Experts,I have a very unique requirement and I need some advice on the best strategy to tackle it. I have 5 tables with a combined data of about 538 million rows (40GB). out of those 5 tables, 1 of them (5GB) is actively queried. I need to consolidate all 5 tables into one table that would be queried from time to time. Here is what I think should be done i.e. my ideas-Consolidate the other 4 (non queried table) first into one table-Move the 5th active table data to this new tableAre there better ways to achieve this? What about locking on the actively queried table when moving data? Would I really need to partition (date range) the 22GB data or indexes would do just fine for querying? Whats is the best way to actually move the data? BCP? SSIS? Any suggestions from you experts would be greatly appreciated. Thanks

Referential Integrity - How to handle the following sitiuation

meichner — Wed, 14 Oct 2009 06:44:26 GMT

I have a Customer Addresses table[code="sql"]CREATE TABLE dbo.CustomerAddresses( CustomerAddressID int IDENTITY, AddressLine1 varchar(50) NOT NULL, AddressLine2 varchar(50) NOT NULL, City varchar(50) NOT NULL, StateCode char(2) NULL, ZipCode varchar(20) NOT NULL, CountryCode char(2) NOT NULL}[/code]Note: This is not a complete copy of the table. I only display what I think is relevant to the question.Let's say I have the following addresses in the CustomerAddresses table:ID Address10 10 Lost In Space Street.....20 20 Fortess of Steel.....I have several other tables that reference the address by using the CustomerAddressID as a Foreign key into the CustomerAddresses table. Lets say the Customer moves from 10 Lost In Space Street to 20 Fortess of Steel. Is there some way that I can automagically find each table that depends on the CustomerAddresses table and change the id in those records from 10 to 20?My current solution is to change the stored procedure that 'moves' the customer so that it changes the address id in each of the child tables. This means that I have to modify this stored procedure each time I introduce a new table that depends on the CustomerAddresses Table. Is there a better way?Thanks

Questions about Dependencies

meichner — Tue, 13 Oct 2009 06:36:06 GMT

I have the following two tables: CustomerAddresss and CustomerAddressesXref. For each CustomerAddress, I might have one or more CustomerAddressesXref records. What distinguishes one CustomerAddressesXref row from another is the Customer Number and TypeCode. The type code represents a shipping type, billing type, corporate type... etc. This means that I might have several different customers or types that point to the one CustomerAddress.I am in the process of building a Sales Order table. Each sales order points to one billing address. 1. Should the field in the Sales Order table be dependent on the CustomerAddresses table or the CustomerAddressesXref table?2. If it should be the CustomerAddressesXref table should I add an identity column to the CustomerAddressesXref table so that the Sales Order table can use this field as its foreign key into the CustomerAddressesXref table?Thanks so much - The tables are listed at the bottom of this post.[code="sql"]CREATE TABLE dbo.CustomerAddresses( CustomerAddressID int IDENTITY, CompactUniqueAddress varchar(120) NOT NULL, AddressLine1 varchar(50) NOT NULL, AddressLine2 varchar(50) NOT NULL, City varchar(50) NOT NULL, StateCode char(2) NULL, ZipCode varchar(20) NOT NULL, CountryCode char(2) NOT NULL, RowVersion int NOT NULL, LastChangedDateTime datetime NOT NULL, OperID int NOT NULL, CONSTRAINT PK_CustomerAddresses PRIMARY KEY CLUSTERED (CustomerAddressID), CONSTRAINT Unique_CustomerAddresses UNIQUE NONCLUSTERED (CompactUniqueAddress), CONSTRAINT FK_CustomerAddresses_CountryCodes FOREIGN KEY (CountryCode) REFERENCES dbo.CountryCodes (CountryCode), CONSTRAINT FK_CustomerAddresses_USStates FOREIGN KEY (StateCode) REFERENCES dbo.USStates (StateCode))CREATE TABLE dbo.CustomerAddressesXref( CustomerNumber int NOT NULL, TypeCode int NOT NULL, CustomerAddressID int NOT NULL, SendTo varchar(50) NOT NULL, ShipLabelNumber int NULL, Memo varchar(100) NULL, CONSTRAINT PK_CustomerAddressesXref PRIMARY KEY CLUSTERED (CustomerNumber,TypeCode,CustomerAddressID), CONSTRAINT FK_CustomerAddressesXref_Customers FOREIGN KEY (CustomerNumber) REFERENCES dbo.Customers (CustomerNumber), CONSTRAINT FK_CustomerAddressesXref_TypeCodes FOREIGN KEY (TypeCode) REFERENCES dbo.TypeCodes (TypeCode), CONSTRAINT FK_CustomerAddressesXref_CustomerAddresses FOREIGN KEY (CustomerAddressID) REFERENCES dbo.CustomerAddresses (CustomerAddressID), CONSTRAINT FK_CustomerAddressesXref_ShipLabelNumbers FOREIGN KEY (ShipLabelNumber) REFERENCES dbo.ShipLabelNumbers (ShipLabelNumber))[/code]

Question about SQL Server 2005 in a Cluster

nunucorreia — Tue, 13 Oct 2009 09:34:35 GMT

HelloFirst of all, I'm no expert in SQL. But need to implement a cluster solution running SQL Server 2005 and I have a simple doubt that I need an answer. Been searching about it all morning, but so far, didn't find a reliable conclusion.It's very simple.We have a [u]Single Instance[/u] scenario with 2 nodes on a cluster in win2008.We would like the [u]SQL Server service[/u] to run on [b]Node1[/b]. [u]Analysis, Integration and Report services[/u] to run on [b]Node2[/b].The major point is to distribute load on both machines(since the CPU's are not quite good,sometimes they jump to 100% load when reporting or analysis are running) and still have failover clustering running in case of any failure on one of the nodes.Is it possible?

How do pros store Critical Employee info in tables?

ed-1075072 — Wed, 07 Oct 2009 08:39:52 GMT

Hi everyone!,I have a basic question and pardon me if I sound dumb about it.How do you store critical information such as passwords, SSN, Salary info in tables?For example are passwords stored as binary or varbinary? What about Salary info? SSN? DOB? What is the best datatypes for storing this sensitive info?I am sure employees at corporations are concerned if the DBA or anyone with the authority can dig into the employee tables and find out how much they make a year.I have never been involved in the design of the tables but I want to learn; and I believe this is the right place to ask, I would like to hear it from the pros.Are there any good books that cover all of this out there? Can anyone recommend one? When I took database analysis and design at school, we barely touch this topic.Thanks in advanceEd

How "normal" is normaliization on your production databases?

GabyYYZ — Wed, 09 Sep 2009 08:29:49 GMT

It is always a laudible goal to get to at least 3NF, but let's be real, how many DBA's here scratch their heads more than once when the developers give them yet another set of scripts to create glorified spreadsheets? In theory, you can get as normalized as you want to keep the queries efficient and manageable, meeting business requirements, but sometimes, the business needs some easy to read way of accessing the data.In SQL Server, would views be the ideal way to de-normalize the tables and how much would it impact performance? I guess the reality is that this would never happen in any and all cases. Anyone here made headway into this issue of normalizing all/most of their databases?Thanks.

Partitioning and Foreign Keys

Pam Brisjar — Thu, 08 Oct 2009 13:22:39 GMT

See crappy pseudo-code below for the basic idea...PartitionTable is partitioned on a non-PK column. Yet we need the PK column to be the clustered key. Which means the PK must be part of the partitioning. BUT, we have tables that reference the PK as their FK. That doesn't work with having the partition column as part of the PK. Only options I can see are to drop the FK's (would not be a complete tragedy but not really a good idea) or not cluster on the PK, which would be really, really bad for performance. Partitioning on the PK (col_id) rather than col_status is NOT an option.Any other ideas anyone?Crappy pseudo-code:PartitionTable(Col_id int, Col_status int, Col_otherstuff varchar(25));ALTER TABLE PartitionTable ADD CONSTRAINT PK_PartitionTable PRIMARY KEY CLUSTERED ( Col_ID ,Col_STATUS ) ON PART_SCH_STATUS(Col_STATUS)TableLink(link_id int,col_id int)ALTER TABLE TableLink WITH CHECK ADD CONSTRAINT Partition_LINK_FK FOREIGN KEY(Col_ID)REFERENCES [PartitionTable] (Col_ID)

Admin account kicked off a SQL Trace

SQL New New — Fri, 09 Oct 2009 11:16:49 GMT

SQL 2005 Profiler--------------------------------------------------------------------------------From: "Jonathan Chong" <j0nathon@xxxxxxxxxxx> Date: Tue, 29 Apr 2008 15:25:48 +0800 --------------------------------------------------------------------------------I found below entries in Event Viewer's Application Log and System Log whichworries me as I know for sure thatthere is no one login to SQL and use profiler on that time. There are onlytwo of us have the access to the SQL server and it is firewalled to onlyallow office's IP to SQL 2005 server (on Windows 2003 server).Application Log:7:24:39 Login failed for user 'sa'. [CLIENT: <local machine>]7:29:03 SQL Trace ID 2 was started by login "sa".7:30:56 SQL Trace stopped. Trace ID = '2'. Login Name = 'sa'.7:46:07 SQL Trace ID 2 was started by login "sa".7:46:35 SQL Trace stopped. Trace ID = '2'. Login Name = 'sa'.7:49:03 SQL Trace ID 2 was started by login "sa".7:49:12 SQL Trace stopped. Trace ID = '2'. Login Name = 'sa'.7:49:31 SQL Trace ID 2 was started by login "sa".7:49:46 SQL Trace stopped. Trace ID = '2'. Login Name = 'sa'.Since both of us are not in office and for sure neither one of us that usesprofiler as shown in the log. My question is: Is there any possibility thatthe logs is triggered by SQL itself besides human?If it is an intruder works, where can I look for more traces leave behind byintruder?

SQL Server 2005 vs Oracle 9i for JAVA Application

Mahesh Bote — Wed, 07 Oct 2009 07:33:17 GMT

[font="Verdana"]Hi All,We are group of 8 peoples which consists FE developers, BE developers and Business Analyst. We have plan for developing one HMS - Hospital Management System application.Now the main debate is going on which platform to be used, as we have JAVA developers and SQL Server developers with us. For DB we have two options, 1. SQL Server 2005, 2. Oracle 9iWe have DB team which is well versed with SQL Server 2000/2005 and aware with Oracle 9i. We have come to a conclusion for using SQL Server 2005 as BE. Now for the FE we have only one option and that is JAVA.So I wanted to know how SQL Server 2005 is suitable with JAVA or vice-versa? Which things to be taken care of while working on such platform?Thanks in advance,Mahesh[/font]

Recover Log Shipping and then Resume Log Shipping

BlackHawk-17 — Tue, 06 Oct 2009 13:55:17 GMT

Quick question that I'm sure others have been through.If we recover our log shipped DR instance as part of a DR exercise what effort will be required to get it participating in log shipping again?My first thought was to take a copy-only back up after the restore and use that as the seed dB. When we finish with testing we would restore the full backup with no recovery, then resume the LS jobs. Will the log restores match up wrt LSNs?Our DR site is across a slow pipe and I don't relish having to squeeze a 100 GB file across it. :-PIt would also leave us without a DR instance for quite awhile.Regards;Greg

Fastest way to move SP and Functions from One Server to Another

repent_kog_is_near — Thu, 01 Oct 2009 13:03:52 GMT

I have some 110 new SPs and Functions in a Development Database; they need to be moved to the testing Database in another server. I have the list of which ones I need. The total number of SPs and Functions are around 250 (including the new ones).what is the best/fastest way to get this done?ThanksDan

Best Autogrowth/AutoShrink Practise for TempDB

repent_kog_is_near — Fri, 02 Oct 2009 19:33:24 GMT

We are considering a dedicated for the TempDB, with no other files in the drive.Will it be a good idea to remove/disable both autogrowth and also autoshrink and keep the TempDB size right away at the max of the drive capacity? (the drive is 35GB, so should I set the TempDB size right away at 34.99GB, or should I leave room for any other factors )ThxDan.

Dynamic Column Names with sp_executesql

krayknot — Sun, 04 Oct 2009 23:57:17 GMT

I am facing a weired problem while using 'sp_executesql' to execute the dynamic queries inside the Stored Procedures: Here is the sample codedeclare @cols as nvarchar(50)declare @tables as nvarchar(50)DECLARE @ParmDefinition NVARCHAR(200) declare @sql as nvarchar(300)set @cols = 'Name'set @tables = 'Bank'set @sql = N'SELECT @cols FROM ' + @tables SET @ParmDefinition = N'@cols VARCHAR(50)' EXECUTE sp_executesql @sql, @ParmDefinition, @cols='Name' Here I am passing the column name as a parameter, But, as an Output i m getting the column name 'Name' instead of getting the Column contents. Well, I know that this is just a preventive measure of not to add any SQL from outside but from the parameter. Is there any work around of this problem?

Table Indexes

Viky — Thu, 01 Oct 2009 07:14:48 GMT

I have a table with two varchar data type fields, It will be very frequently the searchs of these two fileds. Is recommendable to have an index for these fields?ThanksViky.

Performance cost of double validation: middle tier and database

8kb — Tue, 29 Sep 2009 18:18:55 GMT

One of the tenets of my company's development philosophy is to create modularized, "loosely coupled" software components that can be sold / used interchangably. Specifically, they want a C# middle tier that can access / modify data in Oracle, SQL Server, etc.. On the flip side, they want a database (we have SQL Server obviously) that can be used by any middle tier language. The issue we face involves basic validation. We end up keeping it in two places.Example: The Employee object has an attribute EmployeeType that only allows the enums Permanent, Temp, Offshore.In the database, we have an Employee table. The EmployeeType column has a check constraint for the values Permanent, Temp, and Offshore. For something simple, the only concern is duplication of business logic. But when we have requirements that are much more complex, the cost of validating the data both in the middle tier and our stored procedures becomes a performance issue. My question: is the performance hit of double-validation something that must be accepted when creating modular software?

Load balancing

bmw110001 — Fri, 25 Sep 2009 20:32:30 GMT

Dear AllNeed to know can we perform load balancing among different databases located on the same server.

Using differents SQL Server versions within a Microsoft Cluster

vincent.delabre — Fri, 25 Sep 2009 08:13:11 GMT

Hello,We are currently using a MSCS having two SQL Server 2000 instances. We would like to upgrade to 2008, but unfortunatly, our CRM application needs to remain with 2005.So we would like to upgrade the OLTP database to 2005, and the OLAP to 2008.Is it possible to have a MSCS with both versions, and switching nodes without issue ? Thanks for you advice on this,Regards,Vincent

Need a named instance to act as a default instance

campbell.noel — Thu, 24 Sep 2009 09:55:48 GMT

6 months into a migration project, the vendor MAS500 informed me a module we what to use, DIM which publishes jobs to SSIS will only work on the default instance.I changed my named instance IP port to 1433 and changed the line in msdstsrv.ini.xml to ., when accessing the catalogs in SSIS i recieve an error the sql server is not present or not available. what is / how does SSIS connect to the default instance. I would like to fool it into thinking my named instance is a default instance.

location of business logic: database vs. C# code

8kb — Tue, 15 Sep 2009 16:33:21 GMT

At my job, there is new requirement for one of our products to work in "offline" mode. That means the user must be able to view and manipulate data without a SQL Server connection. The decided-upon approach will be to get the critical data from the SQL Server tables when there is a connection, transform the data into objects in C#, and then store those objects locally (either as xml files or using some other method devised by the developers). But the overall goal is to create a database logic layer that can be used to manipulate and query the data wherever it may be (whether it is local or on SQL Server). This layer will basically do all the things that our stored procedures used to do and our SQL Server database will essentially be used for storage and nothing else. I have heard of other companies using this approach on a very large scale with all the business logic written in Java and the data stored in MySql. Is there a place for SQL Server in this type of architecture? I understand the desire to have all the logic in one place, and if you want an off-line mode (and don't want to use SQL Server CE), I don't see how stored procedures and logic in the database can fit into the equation.

Windows Cluster Service and SQL 2005 multiple instances

Gino-1124355 — Wed, 23 Sep 2009 08:37:16 GMT

Hey y'all! Thanks in advance for any advice to my dilemma.I have two separate DB applications that require their own SQL 2005 instance. I've set up a MSCS with an IP, Name, Quorum, Local Quorum and a MSDTC Resource. I would like to set up two separate SQL Instances (groups) that that use the same MSCS. Are there any issues/concerns with this configuration?Also, I'd like to use a shared SQL Backup partition and plan to assign it to the MSCS so it is available to both SQL instances.Thanks in advance!Cheers,

Issues returning data from remote stored procedure (via linked server using SQLNCI on SQL 2005)

Jeff Lucido — Wed, 23 Sep 2009 10:42:56 GMT

I am having quite an issue trying to call a stored procedure on a linked server. Specifically, the stored procedure on the remote server takes three parameters and sets the result of the procedure to an output variable. Originally the output variable was an XML datatype but I changed this to be varchar(MAX) thinking I could return the data this way. The issue is I am finding conflicting information both on Microsoft's website as well as other sites saying both this is possible and not possible. In fact, I found a blog posting today on MSDN stating this sort of functionality has a memory leak ([url]http://blogs.msdn.com/psssql/[/url]).My ultimate goal I am trying to obtain is to calculate sales data for a product set. The initial report calculation is performed on the primary server where we determine the associated items, etc. and then pass these product values (as varchar(MAX) -- which is actually formatted XML) to the remote stored procedure to gather the sales information for the item set from each remote location. Ideally this return data would be XML but varchar(MAX) will work since we can convert the data into XML on the primary server. Once we have all of the data from the remote locations we would then join all datasets into one temp table where we can then perform our pivot operation to remote the item sales by date range.Any suggestion would greatly appreciated as how to make this scenario work within SQL Server, if possible.

Shrinking db with sql2005

Patrick Groce — Mon, 21 Sep 2009 15:37:59 GMT

I run a large ERP database with 180gig mdf file. SSMS always reports that the file has 20% or so free space but i could never successfully complete a shrink. As an alternative I began looking at SSIS to perform an ETL (transfer sql server objects task) to load a new database with the same tables, indexes, views and security. To my delight after performing the ETL my db was 30gig smaller. Why is sql2005 unable to shrink an mdf file? Is sql 2008 any better? Are there other suggested methods for shrinking a db? It should be noted that i used a new SAN partition with aligned partitions at 1024Kb on the drive used to create the new db. Is it possible that a properly aligned partition can have an impact of reducing the size of the db 30gig?Thanks!G

Proposed designed architecture

Shamshad Ali — Fri, 18 Sep 2009 04:32:36 GMT

Proposed designed architectureWe are developing a website with following details:The basic functionality of the site is1. The website has two portions Admin & Client.2. Admin portion manages user and site contents and generate reports. No. of users hitting that portion are expected to be few hundred at max.3. Client portion is the mostly used portion and No. of users hitting that portion are expected to be 100,000 at a time.4. As a whole,a. The site provides online video trainings to individuals through web interface.b. The web application capture tracking information like when employee/visitor started viewing online training and when viewing was completed. The web application automatically confers credit to individuals based on tracking information.c. Both visitors and admin then can view individuals' credit in the form of reports.The basic goals of this design are:1. Serve 100,000 user with response time not more than 3 seconds.2. The website down time should be closest to zero.3. Maintenance cost for the designed architecture should be very small or zero.The proposed design is as follows:Diagram #1:[url]http://w8qm4a.blu.livefilestore.com/y1pwMRi3QdGjPri5uegqwLWz_yKjPQb9s604fsE5tQl4a60bdu8Ycz3_0zuMiX8xQ80UV4ZfN3Tfz_CZTLvHQSSC0G_eYbKpBqd/Diagram1.png[/url]Explanation of the picture "SQL Cluster with Merge & Transactional Replication"No. in Picture Description1 We are targeting 100,000 no. of users for providing online video trainings at a time2 There would be a load balancing device that will share visitors requests among three online web servers WEB1, WEB2 and WEB33 The three web servers will be running the same website (as to balance the load)4 Server side caching will be implemented (using MemCache logic) in web site application. This is to minimize database hits, gain performance and hence achieve better user experience.5 For saving and updating users online activity/transactions in database, web server will use Frontend SQL Cluster connection6 For Admin site configuration and management, reports, uploading training videos etc, web server will use Backend SQL Cluster connection. Also if the data in reports requested by visitors would be older than a week then it would be also served from Backend SQL Cluster7 To distribute the load of Visitors and Admin users, we are implementing two SQL Clusters as explained in diagram. Frontend and Backend SQL Cluster.8 Tracking Database will keep only last 7 days data. Rest of the data will be purged on daily bases. This DB contains users' site navigation activity. Viewing of online trainings etc. is logged in Tracking DB. There are total 12 tables in this DB. Out of 12 there are 2 tables that would be updated by Backend DB later on for some business needs. The change in data by Backend Database requires updating Frontend database if the data is within a range on past 7 days, so we need to configure Merge and transactional replication both. See diagram 2 for more details9 General DB contains most of the lookup tables data, i.e Users' Profile Data, online Trainings information, Training schedules, site's configuration, visitors locations/department hierarchy to identify users and provide training material based on user location. There are around 45 tables in this DB. Out of 45 tables 9 of them would be updated by Backend DB as well for business requirement. For example. If a new user hit our website first time and its information is not preset in User Profile table (Table updated by ILM 2007 one time daily), we will immediately add this user in User table. So it should be replicated to Backend Database for reporting purpose. Most of the General Information tables would be cached on Web Servers to minimize querying database as mentioned in Point No. 4 above.10 Exception Log DB is self explanatory. This db is used to log users errors while surfing the site. This db would be backed up and provided to development team for analyzing and fixing issues faced by users11 Backend SQL Cluster database on Node 1 will consist Tracking and General database tables both and will be used for site management by Admin users to update lookup tables, reporting, Upload training materials etc. as mentioned in Point No. 6 above.12 In Backend SQL Cluster, Node 2 would be Passive to Node 1 and incase of any failure of Node 1, Node 2 will provide services.13 Filtered Merge Replication would be setup for Tracking Database to keep only 1 weeks data in Frontend. All data would be present in backend SQL Cluster.14 Merge replication and transactional replication would be configured for General DB.15 Backend db will also receive information from 2 other servers like ILM2007 Server which provide up to date Active Directory users information in Users Profile table. So this table would require Merge Replication setup.16 Similarly Schedules are maintained from third party Server and require Backend Cluster db to be updated immediately. It also update/fix users training/transaction data upon successful updates to third party servers via web servicesDiagram #2:[url]http://w8qm4a.blu.livefilestore.com/y1pmIGYz8lhQImJa7akEoFm-bcRFMUF6XVtVsr-zJq3YZCl7A7bavoiMVHZqWxTCSY9wu0275VNsKA72Cna57COPkrZ_o0irSWK/Diagram2.png[/url]

Stored Procedure best practices

MaverickMan — Mon, 14 Sep 2009 15:30:10 GMT

Hello,I am a newbie who started working as a SQL Server developer and I spend my whole day creating stored procedures. I wanted to get some inputs/advices from the senior DBA's to know some best practices while creating stored procedures. Ex: I got an advice from a senior DBA that stored procedures in 2008 shouldn't be named with sp_xxx as system procedures too are named with sp_xxx this would create a duplicity problem.Some one also gave me an advice that using comments for each of the parameters helps in the future.Similarly if you can share anything that could guide me and other newbies would help!I appreciate you taking your time.