Answer Questions

• lou_1 DBCC Errors - related to hardware? Can you tell

  Hi, I've got a DB thats throwing up a whole heap of errors in a dbcc command. I'm trying to track where these are from and the likely result of an fix using  'allow data loss' If anyone can throw any light on the error i'd be real greatful. thanks     Server: Msg 8909, Level 16, State 1, Line 2 Table error: Object ID -2088070838, index ID 12303, page ID (1:714907). The PageId in the page header = (2612:1024331787). Server: Msg 8909, Level 16, State 1, Line 2 Table error: Object ID -2055175028, index ID 7452, page ID (1:714860). The PageId in the page header = (525:520452223). Server: Msg 8909, Level 16, State 1, Line 2 Table error: Object ID -2038263743, index ID 33159, page ID (1:714850). The PageId in the ...Show All

• Wojtek How to block access during peer-to-peer transaction recovery?

  Is there a clean way to block access to a peer which has been offline, during peer-to-peer transaction recovery (while the outstanding transactions are being applied) Thanks, Liston Hi Liston. You could set the db into priviledged user mode, which would only allow dbo's to connect to the given database. Of course, this assumes that standard users/application users aren't dbo's as well. Aside from this, you'd have to write custom code to handle the scenario... HTH, Chad Chad - thanks. Thought I might be missing something more straight forward - will do. Liston ...Show All

• Ceex Problem displaying data the way i want on a report- pls help!

  I have output from a query in the following format: metric value abc 1514.98 def 878.95 ghi 618.98 I need to present a series of ratios on my report based on set formulae: eg liquidity ratio= abc/ghi*100 gearing ratio=def/ghi*100 etc Basically I need to be able to reference both the metric and value where I want on the report. Any ideas Andrew - T4G wrote: Are you using Reporting Services 2005 with .NET 2.0 Framework Yes. I am just now installing the SQL 2005 SP 2 (community preview or whatever) though. Can you help me with basically the same display issue on RS2005 I'm not getting the error mentioned ...Show All

• Stunt Catching more then one Error

  Hello all, not to sure if this can be done but I am attempting to catch a series of errors with in a single TRY CATCH statement. For example, the following code triggers a single error ( which work fine) SET NOCOUNT ON declare @errmsg nvarchar(4000) begin try select 0/0 end try begin catch SET @errmsg = 'Msg ' + cast(ERROR_NUMBER() as varchar(20)) + ', Level ' + cast(ERROR_SEVERITY() as varchar(20)) + ', State ' + cast(ERROR_STATE() as varchar(20)) + ', Line ' + cast(ERROR_LINE() as varchar(20)) + ', ' + CHAR(13) + 'Procedure ' + isnull(ERROR_PROCEDURE(),'') + CHAR(13) + isnull(ERROR_MESSAGE(),'') end catch select @errmsg AS ERRMSG RESULT: Msg 8134, Level 16, State 1, Line 4, Procedure Divide by zero error encountered. But ...Show All

• Troy L simple dts - set some constant values

  Hello, I have a simple dts which imports data from one table to another. I want to insert some constant data to some columns of the target table, since there are no corresponding columns in source table, and target columns do not allow null values. How do I do that In the mapping table I can only map source columns to target columns. How can I map target columns to constant values. Thanks in advance Yes I could do that. I was just curious how this is done using a dts since its easier to map columns graphically. you can use the dts designer to do that there should be two packages involved. one that creates the database before designing "package two" run the first package to have the schema change design ...Show All

• yabansu cannot create jobs on x64

  Hi all, Just installed sql 2005 x64 (DB Server + Client Tools incl. management tools) on a win2003 x64 server and encountered the following problems: 1) Could not create jobs Getting Error: Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent. JobObjectKey '. (Microsoft.SqlServer.Smo) - Tried several different job types, always same result. - Installing SP1 does not help. - Installing with or without Integration Service does not help. Any help is highly appreciated TIA Dan Thanks (acki4711) for the info! Hi Guys We succeded create job remotly (from another computer 32 bit ...Show All

• pfd No ADO type command for Data Flow?

  I'm probably not looking in the right place, but all I could find when creating a data flow task was OLE DB Commands.  I was trying to utilize a dataaccesslayer piece of code that we use every where in our projects, but because it uses ADO and not OLE DB, it caused an issue between the column data types. Is there an ADO command object available   Or are we forced to use the OLE DB command object   All I was looking to do was to Execute a SQL command.  There's an object on the Control Flow level to do that, but not on the Data Flow level---not sure why that is. Thanks, Jeff Tolman E&M Electric JazzGeek wrote: It's not necessarily a particular command. It's using ...Show All

• Manivannan.D.Sekaran Resume execution after error

  Hi, I have a stored procedure containing iterating cursor in which iam inserting records in a table. My problem is that whenever any data mismatch occurs whole process gets stops. I want it should skip that error record and continue with next iteration. Like on error resume next in vb. Please suggest. Sharma: Cursors are usually a bad idea. What are you doing that makes you believe that you must use a cursor Dave Thanks Tom for your suggestion. Now iam thinking to change my approch of doing the same job since expected error handling is not supported by sql server 2000. Thanks ManiD for your response. So what approch will ...Show All

• NathanM Sql Server does not start

  hello all, I am using SQL Server 2000. I am facing a problem since yesterday that my sql server does not start. It gives me the error that 'Could not start sql server. sql server does not exists or access denied....Instance already running' when I try to start or connect to the server. I changed Authentication mode from WIndows Only to Sql Server and Window a week ago. It worked fine. Then I changed it back to WIndows Only. When I did that, server is not starting up. it is set to automatically start with operating system. But when I restart my computer, even then server does not start. What has happened to the server Is it because of Authentication mode change that I mentioned above or something else Please help me! I nee ...Show All

• Jean-Pierre Fouche Help please - New user

  Hi all My project is a bit like eating an elephant, and I am about to take the first bite! I have built a number of Access databases over the last 4 years which support our local Production reporting, these work well and are within the scale supported by Access. However, my boss does not want to move forward with the Access platform. He wants me to redevelop the applications into one SQL database, which will have a new, and yet to be identified back end (something like clickview). Once developed we want to roll out the application to the rest of our group. The tables in my database will mainly be populated from our ERP system. The tables will either be master files, or transactions for a given range of dates. The master file ...Show All

• Romana Need Help on this SQL Statement

  Hi All, sqlLstProds="Select * From PRODUCT_DEF "&_ "WHERE productID IN(Select productID From PRODUCT_DEF WHERE (IDProductCriteria = 5) AND ([Value] = '"&getSession("ModelID")&"')) "&_ " Order By IDProduct, IDProductCriteria" I have the above SQL Code in an ASP page. This queries the Product_Def table, however there is also a table called Product_sType, the primary key in that table is productID which corresponds to productID ub the Product_Def table. I need to add to the query a where clause, where active = 1 active 0 is obviously discontinued products, how would I do this So basically, it's somewhat of a join of some sort, like select * from Produc ...Show All

• Carver42 Change Edition of existing isntance?

  I have a named instance of SQL Server 2005 Developer Edition. I want to install VS Team System Foundation Server on the same box, but this needs a default instance of Standard or Enterprise edition. I installed the Standard version alongside the existing Developer instance but now I can't start SSIS and without it VSTS Foundation Server won't install. I have tried to uninstall SSIS and reisntall it from both the Standard Edition and Developer edition media, but it makes no difference, the service still won't start. Could the SSIS failure be due to having different editions on the same box If so, is it possible to change the edition of the Developer instance to Standard (or to change them both to Enterprise edition) without having to ...Show All

• Andrei Kuzmenkov Can not debug component?

  Who can help me I defined project properties: Start action--start external program : dtexec Action options--command line arguments: /f ***.dtsx (using component which i debug) Configuration: active(debug) I specified breakpoint after clicking build,dtexec can popup but then generate error: The following modle was built either with optimizations enabled or without debug information C:\winnt\assembly\GAC_MSIL\***\***.dll To debug this module,change it's project configuration to debug mode. I have no idea about this,and i can find my dll file in C:\winnt\assembly,but no GAC_MSIL folder Who can help me Thanks in advance not yet,so frastrating ...Show All

• Andrea Gramm Case statemant- separate forenames

  Hi All I need to separate the forenames within the staff table, at present it looks something like this: John-Test John - Test John - Test Richard Bill Jones John - Test MiddleName John-Test Mid I need to split the middlenames out and remove the spaces within the double barrelled names John-Test John-Test John-Test Richard Bill John-Test John-Test Thanks in advanced Rich It will do.. Create Table Names ( FullName varchar(100) ) Go Insert Into Names values ('John-Test ') Insert Into Names values ('John - Test') Insert Into Names values ('John - Test') Insert Into Names values ('Richard') Insert Into Names values ('Bill Jones') Insert Into Names values ('John - Test MiddleName') Ins ...Show All

• AlexKL Momitoring when job has executed

  Is there a technique for determining when a job has been executed - using T-SQL I tried using 'sp_help_job' by inserting the output into a table (e.g. INSERT jobtable EXEC sp_help_job...) but I get the following error: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 68 An INSERT EXEC statement cannot be nested. Any ideas anyone Rgds Bertrand You can all the info from msdb..sysjobs and msdb..sysjobsteps table. I suggest you take a look at the following article (thanks Gert) so you can handle the sqlagent datetime. http://sqldev.net/sqlagent/SQLAgentDateTime.htm ...Show All