Answer Questions

• guy87 sp_cycle_agent_errorlog fails/SSMS recycle Agent log fails

  Hi, The sp_cycle_agent_errorlog stored procedure fails on our primary SQL Server 2005 server; the SSMS context menu option (right click Error Logs under SQL Server Agent in object explorer) to recycle the agent log also fails. If we run the SP then we get the following: Msg 22022, Level 16, State 1, Line 0 SQLServerAgent Error: 32. If we try the SSMS option we get this (in a nice dialog): Cycle agent error log failed for JobServer 'UKSQL04'. (Microsoft.SqlServer.Smo) Additional information: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) SQLServerAgent Error: 32. (Microsoft SQL Server, Error: 22022) We are running SQL Server 2005 x86 Enterprise E ...Show All

• John Padilla Joining two tables with repeating rows

  Hi. I am trying to get data from two different tables. The first table contains user access data for access to different modules of our application. There are only records for the modules that the user has access to. So if User1 can only access 2 of the 5 modules, there will be 2 User1 records. The second table lists the modules. UserAccess table: UserID ModuleID AccessLevel User1 1 1 User1 2 1 User2 1 1 Modules table: ModuleID Description 1 Mod1 2 Mod2 3 Mod3 4 Mod4 5 Mod5 What I am trying to select is a list of all modules for each user, whether they have access or not. So basically I would like the data f ...Show All

• 2162 Transfer Data by using SQL SMO

  Hi, I wonder if i can transfer and update data every day from one server to other by using sql smo. For example, i have two server with database. One is primary and have new data in many tables every day. I'd like to move the new data only to the second server. How can i do that What methods in SQL SMO I'd think the cleanest way to do what you want to do is through Replication, and RMO will help you manage the replication, but SMO is a set of tools to manage SQL Server, not perform data manipulation. ...Show All

• Marmot2006 Trace Flag 9059 gone in SQL Server 2005

  Does anyone know if the behavior in SP4 which was disabled with the Trace Flag of 9059, has been made permanent in Sql 2005 We have a vendor app. that required us to turn on TF9059 after upgrading to SP4, so we are wondering, since TF is not in any Sql2005 doc, if moving the app to Sql2005 will cause the same performance issue the app had in SP4 when the TF9059 was not enabled. thanks tf9059 (as described in kb 899976) forces the system to convert from a higher precision or scale to a lower precision or scale to do an index seek. Having it turned on by default is definitely not desired in normal operation. So, as with sp4, sql2k5 has this turned off. oj, thanks for responding b ...Show All

• AdriM is there a collection of all database objects

  i want to write a tool to make scripting of databases easy. the user will select the server and dababase, then they will select what they want to script: eg tables, sprocs, permissions, scheemas etc etc. currently i have a load of check boxes , one for each type of object in the db ( one for tables, one for sprocs etc). this is very long winded,and if there was a collection of the db objects i could call into a list box it would be a lot tidier and easier. There are a number of collections within the Database object in SMO which you can use, such as Tables, Triggers, StoredProcedures, Users and Views. Each of these is a collection of the named objects within the database. (At the Server level of course, t ...Show All

• SERware SQL Server 2000 - Availability whilst restoring logs

  I need to ensure users can submit SQL queries (readonly) to a database whilst transaction logs are restored (at 15 minutes intervals.) Is this possible If you're restoring log backups every 15 minutes, this sounds like a log-shipping environment, right In order to keep applying logs, your database must be in the 'restoring' state, and thus is not available for queries. I am therefore assuming that there is no way to keep a system 'live' for select queries whilst updating it in SQL 2000 I am currently providing views on some tables held in a SQL 2000 database from SQL Server 2005. The reason I cannot bring this database in to 2005, is that it would then be upgraded and is then incompatable to ...Show All

• Alok_sriashoka_Patna _city Reissue restore statement

  I am trying to restore a database but i keep getting an eror message saying "reissue restore statement using with replace to overwrite existing files." How can I get around this problem to get my datatabsed restored I am abit of a novice as well so i could do with some easy to understand instructions. Thank you for your time Cheers for the help scott. Do i need to type that into a query Also this bithere RESTORE DATABASE MyDb FROM <insert info here> is this the destination of my backup for example mydocuments/testdb RESTORE DATABASE MyDb FROM <insert info here> WITH REPLACE The above will "get around" the problem you experience. Essentially you a ...Show All

• Eugene Ye Error starting Tuning Advisor

  When I try to run the database engine tuning advisor from SQLServer2005 I get a messagebox with the following dialog: RuntimeError! Program: C:\Progr... R6031 -Attempt to initialize the CRT more than once. This indicates a bug in your application I have read the MS KB that says the solution is to delete a registry value. I dont however have the key in the registry that the KB article refers to. (HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers) I am missing the AppCompatFlags key. Any suggestions Is this the right forum for this The key does not exist there either. Thanks for the suggestion though. ...Show All

• ron2464 Change Print Defaults

  I have noticed that all the reports I create in VS 2000 for RS 2000 all have the default print set to potrait with 1" margins. I was wondering if there was a way to make the default different for different reports. Some I only want a .5" and some and I want to print landscape and still other I need both. Does anyone know how to go about this Thanks You can set the print settings for every report individually. In VS 2003 got to the Report Menu and choose Report Properties. Go to the layout tab and adjust the width, height and margins as per your printing requirements. Note there is no specific Portrait/Landscape option here, you control it using width and the height. One technique that I use ...Show All

• SpaceCadet How to get started on Microsoft SQL Server Management Studio Express

  Hi, Was trying to open AdventureWorks when i got this error: "There is no editor available "C: Program Flies.....\AdventureWorksDW_Log.LDF' Make sure the application for the file type(.LDF) is installed. How can this error be corrected I also have some error on mdf not installed.May i know how should be the initally steps to be done inorder for me to view this database. And if i do have a database from microsoft access, what are the steps that i need to do inorder to get it export to Microsoft SQL Server Management Studio Express. Please advise as i really new to it. Thanks! Hi, I have managed to attached the AdventureWorks data. But it seem like there are some restrictions. Must we set any rights to user so that we ...Show All

• KarlShare difference among them

  Hi everyone, What is the real difference among them CREATE PROCEDURE sp_devamsizlik @ADI CHAR(20) AS SELECT * FROM OGRENCI WHERE ADI=@ADI GO -------------------------------------------------------------------------------------- CREATE FUNCTION sp_devamsizlik ( @ADI CHAR(20) ) RETURN TABLE BEGIN RETURN( SELECT * FROM OGRENCI WHERE ADI=@ADI ) END Akbar Khan wrote: - You can'nt call a UF in SP while in SP u can -In a Query u can c ...Show All

• Freddde Format date in a Stored Procedure

  Dear friends, I have a stored procedure that returns some fiels. One of the fields is a datetime type. The field return in the follow format : 2006-11-13 0:00:00 How can I return only 2006-11-13 How can I use the format function regards!!! For example: I want to create the follow stored procedure: CREATE PROCEDURE TEST @ID INT AS UPDATE TABLE1 SET MyFieldTime=@MySystemTime WHERE MyFieldID=@ID Understood I want to save in my database th system time... Thanks!! Or... Select Convert(varchar(10), GetDate(), 120) Lookup the convert function in Books On Line for more formats. Dear friens, First, let me thank for all your suppor ...Show All

• badsign Using SQL Server CE in a NON .NET Delphi Application

  Hi, I'm looking at using SQL Server CE as a replacement for MS Access in a WIN32 Delphi Application. This limits my database options to Providers that is usable from ADO. I see that the OLE DB Interfaces are implemented, but I can't connect to a SQL Server CE database from Delphi. I've cut and paste a sample connection string (replacing the data source bit) but no go I've installed the Runtime Environment, VS2005 bit, Connectivity Tools and the SDK :) Any help Anything that I might have missed Thanks, Dawid Please make sure you have sqlceoledb30.dll registered on your desktop PC. What is the provider name you are using SDAC 4 for Delphi 6 and some other IDEs will be uploaded in ...Show All

• mstoler Format / Alignment

  Does anyone else have this issue When making a report with VS2005, things get spaced out wierd and dropped down when deployed to the web I can't get things like images to position properly. Does it have anything to do with the fact that I am using a Matrix For example, I create an image and embed it, I have it all the way to the left of the form and when deployed it is more towards the middle of the page Very frustrating, just wondering if anyone has any tips/solutions. yes, I tried that already, nothing is even close to overlapping. Thanks anyways I figured out a way around it in case anyone has the same problem. Keep the Matrix in the body and create a header and footer to put all the other stuff in. E ...Show All

• David Reynolds Save to XML file

  i'm trying to save data to an XML file from an OLE DB source using the "For XML" clause in the SQL command. Do I need to use a flat file connection manager or a raw file connection manager And what destination do I use jschroeder wrote: i'm trying to save data to an XML file from an OLE DB source using the "For XML" clause in the SQL command. Do I need to use a flat file connection manager or a raw file connection manager And what destination do I use There are no built in components to allow you to output as XML but Donald Farmer's book contains code showing you how to do it in a script component. If you don't want to buy the book (although i recommend you do - its very cheap) then read th ...Show All