Answer Questions

• Manech Replication row Commit Size

  Is there a way to get replication to commit records in batches instead of all at once I am in a 24/7 shop and some of my updates end up being thousands of rows and it locks the subscriber table for a few minutes sometimes. If I could get it to commit say every 1000 rows it might give me some relief in this area.. Or am I thinking about this wrong If this is possible, would it help at all... I transactional replication adheres to the ACID properties, so if you apply 10,000 commands in one trasaction at the publisher, the distribution agent will apply the same commands in one transaction at the subscriber. You have a couple options: 1. commit your commands at the publisher in smaller batches. ...Show All

• Shuman Kibria Creating views under INFORMATION_SCHEMA in SQL 2005

  Hello to everyone, I have a couple of useful views created under INFORMATION_SCHEMA in SQL 2000. Now I am trying to create them in SQL 2005 environment. As an example simply run the following code as "sa" in SQL 2005 (it works in SQL 2000): CREATE VIEW INFORMATION_SCHEMA.MyTABLES AS SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' GO In my environment I received the following error: Msg 2760, Level 16, State 1, Procedure MyTABLES, Line 3 The specified schema name "INFORMATION_SCHEMA" either does not exist or you do not have permission to use it. I tried to create the view under other schema and change the owner to INFORMATION_SCHEMA and some other techniq ...Show All

• Cactus77 Microsoft SQL Server 2005 Enterprise Evaluation Edition

  I have installed Microsoft SQL Server 2005 Enterprise Evaluation Edition. The file size is now 4035. When I tried to add new data file name,I am getting the below error. In the below site it says that there is no limit on the size but Iam still getting the problem http://www.microsoft.com/downloads/details.aspx familyid=6931fa7f-c094-49a2-a050-2d07993566ec&displaylang=en http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx Pls can any one help me. Alter failed for database 'test'. (Microsoft.SqlServer.Smo) An exception occured while executing a Transact-SQL statement or batch(Microsoft.SqlServer.ConnectionInfo) Create DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed ...Show All

• winstonSmith [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

  I am using PowerDesigner to reverse engineer a SQL 2000 database. I am getting the following error. I can't find out what this error means. Does anyone know Unable to list the users. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. SQLSTATE =37000 This error is caused when the client is asking the server to prepare a statement which has errors. For example if there is a syntax error in the statement that is being attempted to be executed and SQLPrepare ODBC API is called on it, you will hit this problem. Do you know if the T-SQL you are trying to execute valid or not. To find out the exact problem try using SQL Profiler to see which statement failed to exe ...Show All

• YunXu TSQL Logic Help

  I have a Rule table that looks like this. Rule_ID (PK) vMin vMax vName I would be recieving the vName as an input. I need to go through the table to make sure that the rules (Min and Max) are not intermingeled. Rules can be within another rule but can not cross. This is Correct Rule 1 vMin = 10, vMax = 20 Rule 2 Correct vMin = 15, vMax = 18 Rule3-Incorrect vMin = 8, vMax = 12 Every time the SP is fired I need to be able to check if this rule is valid or not by comparing it to all the rules in the table. if it is not valid I need to fire an error back. how can we get the logic of this code to work Please help Try this out: select t1.RuleID,t1.vMin,t1.vMax,t1.v ...Show All

• Di Wu performance issue,help please

  Hi, I have a table,which increases fast, 1,000,000rows/day. insert/update/delete/select on this table. an index on accout column was created, and we often use this account column in the where condition. like : select name,account,amount... from mytable where account='12345678' update mytable set ... where account='12345678' How to increase the performance on it partition the table Thanks, any ideas will greatly appreciated. table structure: account int, name varchar(20), desc varchar(50), amount money, tr_date datetime, balance money one account may have one or more records. Primary key Indexes If you have neither then your queries ...Show All

• Anand Raman - MSFT Database Login fails

  Hi I can logon to my Sql server using the SSMS management tool, I am using Windows Authentication to logon to SSMS. Create a database OK, with all the necessary tables. However when I start a VB Express app and try to connect to this database I get an error that login filed for user AMD\Larry, AMD is my PC name and my login (Larry) is the computer Administrator logon. The PC is a standalone ie not part of a domain etc. Can anyone help Regards. Larry. Thanks for these I wil try them and get back. Are you also using integrated Authentication in VB Express. Make sure that you don’t really *type* in the user as this will be recognized as a SQL ...Show All

• Woyler ack message not picked up by stored procs

  Hi I setup a message between 2 database on a local instance. The message is send to the target and the target did send a reply. However, the reply is not picked up by the initiator. I did a try but there was nothing telling me why it did not pick up the reply. there is nothing in the textdata of the brokerclassify event. I query the queue on the initiator, the message is there, but the stored proc attached to the queue is not doing anything it seems. What else can I check here, thanks P If you're positive the reply is in the initiator's queue but activation does not kick in, I'd look at the following: - activation is not turned on. Check the is_activation_enabled column of sys.service_queues ...Show All

• Quimbo What to use in place of MS Access to view SQL Server tables?

  Our business application is running SQL Server in a remote data center and we had been using MS Access from a local PC to occasionally fix data problems in the data base. As the tables continue to grow, it's painfully obvious MS Access just can't handle the volume of data. One of our tables we need to get into is about 200,000 rows, another is over 100,000 rows. We need to be able to change individual rows, and perform search and replace commands across one or more columns in select tables. What software can we install on local desktops to allow viewing and editing of SQL table data I downloaded and have started to play with SQL Server Management Studio Express. M ...Show All

• scorpion007 can't update view

  Hi all , I have a problem to insert new data to a view which created from 2 tables. I am trying to so from run time Any help will be appreciated use an instead of INsert trigger for that. HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Itamarqza, did the Instead Of resolve your issue If not could you please provide more information. Thanks, Derek ...Show All

• Naheem Concatenate Rows into Columns

  I have a table 2 columns (Object and Weight) with data like below: Object Weight(lb) table 5 Chair 6 Computer 3 Computer 5 TV 20 TV 15 Radio 10 Computer 10 Question: I would like to create a new table with one column that would join the above two columns like below Object table 5 Chair 6 Computer 3, 5, 10 TV 20, 15 Radio 10 Thanks for your help in advance. CTE (Common Table Expression) is a new function in SQL Server 2005. You can look it up from Book Online. MyCTE is a name like you would call a table mytable in your database. I like Louis's solution a lot. It uses another new CROSS APPLY funtion in SQL Server 2005 too. What if I do not know the Max number of Weights for each ...Show All

• Joao Pinto Error when Deploying Cube

  Hey guys, Have a really strange error, I have a AS Cube designed and ready to go. Have been deploying it previosly and was erroring out due to attributes not being found in the tables. So ive worked on fixing all of them and now, i have an error message occuring and all it says is: Server: The Operation has been cancelled. I havnt cancelled anything, it doesnt show any more details for the error either.. Is there a log file somewhere that i can look further into Or has anyone had this message before I am stumpped and now its annoying me as i was so close to having my first cube up and deployed... Thanks in advance Scotty If you have made some changes in the source DB, you can try re creating the Data source ...Show All

• Blackuke Frequently update and select Table Locking Problem

  Hi guys, I faced problem related to Locking and Isolation Level on Table(s). My problems is there r some tables which r frequently updated, and I also want to fire select query over those tables every 1 seconds and want to get only committed records. In current scenario we start transactions with Read Committed Lock for updating records. But in this scenario I can’t get select query result because of some of recourses r used by transactions so after some time it gives Deadlock error . So I want solution like both operation run simultaneously and get only committed records at a time of transaction running. Please help me for solving my problem. ...Show All

• Mutola Does SSRS support cluster?

  SQL Server 2005 Reporting Services, Notification Services, and Integration Services are not cluster-aware; they are always installed as stand-alone components, but can use a clustered SQL Server database. Words above come from sql 2005 help on msdn, but the guide of ms reporting manager says, SQL Server Reporting Services (SSRS) must already be installed and configured before you can install SCRM 2006. Although SQL Server 2005 and SSRS support a scaled-out deployment model configuration using clustered servers, SCRM 2006 does not support clustered servers. Why they are different which one is correct Thanks. sorry, the first section comes from official sql 2005 cluster white paper, <Fa ...Show All

• DellS SAN/Filegroup Question

  Does anyone know if there is a significant advantage to break up a database into seperate files & filegroups if the files are located on the same SAN Should the logical drives that they are located on matter Thanks, Tim On the same SAN probably no performance benefit. However, there are other reasons to create filegroups, etc. It depends somewhat on your SAN. We use a 3PAR SAN, which spreads all files across all available drives in the SAN using 256MB "chunklets". With 3PAR, it does not matter quite so much how you setup your files. With more conventional SANs (like your typical EMC), I like to have several smaller data files in separate filegroups instead of one large one, just ...Show All