Answer Questions

• Phonics3k Best practice for writing own system procedures

  Hello, I'm searching for a best practice or other documentation for writing my own 'system procedure'. I want to write procs which I can call in the context of every database without using a database name analogous to sp_who for example. I read about the 'Resource Database'. All system procedures are stored in that readonly database and appear logically in the sys schema of every database. But I couldn't find documentation about writing my own 'system procedure'. I discover so far that procs with prefix sp_ stored in the master database do what I want. But is that the only way or is there a better way to do it In other threads I read that it is recommended not to use sp_ as prefix for procedures Wolfgang ...Show All

• CS05pp2 How to see all users' subscriptions?

  In SSRS 2005 Report Manager, we have a "My Subscriptions" link at the top right of the form. However, system admins need to see the subscriptions of all users. How can they do that I hope SP2 would come with some useful sample reports to grab those data of the ReportServerDB RS has been great so far, but some minor issues really cause pain, e.g. the Select All "bug" in SP1, etc.. Right now there is no way to do that in Report Manager. A workaround is to open the ReportServer database (on the same server where Reporting Services Server is installed) and see all the entries in the Subscriptions table. Shyam ...Show All

• Cory Cundy Conditional Split problem

  Don't know if anybody else has had this - In a dataflow task I added a conditional split that currently has two possible outcomes. Initially this was a straight choice. Later I added an "or" clause on the second leg thus expression1 || expression2 on which the second leg ceased working. After some mental anguish I deleted the component and then reimplemented it using identical logic and magically (or so it appears) it all worked. So, it appears the old tricks still apply... I have had people report something similar, but every time it turned out to be a slight error in the expression that resulted in one output never receiving data. What would be compelling as a bug would be i ...Show All

• Dottj temdb is full when I run DTS task with OLAP processing

  Hi, I have some problem: Evryday I autmoaticaly run SqlAgent job with DTS task that run tasks: 1. SQL Task: Shrink tempdb database 2. Analysis services task: Process ALL Database From some time the job fails to run. I have error indicating that tempdb is full The error string is: "The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space" The size of temdb file growth to 20GB. When I process Databse manually in Analysis Manager the database process correctly (because Analysis Manager do not use tempdb but temp folder) What I should do in this case I shrink tempdb before every processing so back up of transaction log will not help me. Any sugestions I'am using SBS 20 ...Show All

• oaix Performance and Execution Plan

  Performance !!! Hi! I am trying to execute an stored procedured that have already been used in another server (the test server ) but it takes 25 hours and in the another server takes just 40 minutes. !! I have reviewed the indexes, statistics an so on, I have exactly the same quantity of data but cannot explain why does it takes so long in the server that is supposed to be the better one. When I show de Execution Plan in the Query Analyzer in one server I get: - Select (0%) - Compute scalar (0%) - Remote Query ( 100 %) When I executed in the another one I get: - Select - Compute Scalar - Nested Loops Left Outer Join ( 80 %) - Merge Join / Right - Remote Query - Sort - Hash Match Right … ...Show All

• simon_ Group by on a concatenate field

  Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount) from dbo.Enterprise_Credits_Import_90_days where CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0' and Service_Prefix like 'F' Group by How can I group by the field that I selected as CODE right - I should have looked more closely at your original query - I didn't see the inline view before... :) So has it helped Or if you're interested in readability you could make it: select top 100 code, date_issued, sum(amount) from (select CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Se ...Show All

• Dale Beyer BIDS bug with SSIS Annotations

  I would like to report what appears to me to be a bug. I found it while researching an answer for another thread. http://forums.microsoft.com/TechNet/ShowPost.aspx PostID=554926&SiteID=17 Since that thread has not received any further replies I thought I would start a new one to see what answer we can get from MS as well as if anyone else would care to reproduce. The bug is not a major one and the work around is easy, but it can be annoying. The bug is this: if I open an SSIS package in BIDS (RTM or SP1) and the only change I make is adding or modifying an annotation and then click on Save and then close BIDS does not save the new annotation or changes made to existing annotations. If I change something other than an annotatio ...Show All

• sarc Development Index granularity

  Hi, I was looking at the new index locking granularity option available in 2005. I did not understand in what case can this be a performance enhancement. Has anybody looked into this Please post some sample code demonstrating the issues. The index options you talked about can be set via CREATE / ALTER INDEX also in SQL Server 2005. This is the only new change otherwise it was available in SQL Server 2000 too via sp_indexoption. There is no issue as such. I just want to know when this option when set ON, can be of any advantage. And what is the situation when this can be a performance issue. Thanks Sapna Hi, I am not talking about the sp_indexoption. ...Show All

• Dark Dominion Getting larger drive array

  We are planning to install more disk space and need to temporarily move the data and log files and move them back after the disk space has been added. Is Detach/Attach the best way to handle this You have to detach the database and then copy the .bak files to the directory and then attach it. Steve Russell, MCDBA Your absolutely correct, my bad; the .ldf and .mdf files. Steve, Thanks for the response. What will I need the .Bak file for I thought I would just be working with the .mdf & .ldf files. In other words, I was planning to do a Detach, then move the .mdf and .ldf to another server, uninstall the current 30 GB drives then restall 200 GB drives and finally Attach the ...Show All

• Adam Machanic How to export the result of a sql 'for xml' query to an xml document

  I have a sql query with a 'for xml explicit' clause. I would like to export the results of the query as an .xml file. I specify the query as 'SQL Command' in an OLEDB source adapter, but I don't know see an adapter that I can use for the destination. Any ideas Thanks. I got the book and attempted to write a script component to serve as an xml destination.  I tried following the example in chapter 9 of the book, but in my case the input to my (destination) component appears to be a single row with a single column named 'XML_F52E2B61-18A1-11d1-B105-00805F49916B' data type DT_Image.  This represents the result of the SQL 'FOR XML AUTO, ROOT('Root')' query.  I convert it to a ...Show All

• KitGreen date parameter issue

  hi all We are a multi site / multi language company so do not want to hardcode the date format - these reports will be on intranet Using MS Sql - Reporting Services 2005 Using no formatting on fields = yyyy-mm-dd - is how it is displayed. I added a @startdate and an @enddate parameter I read up and saw this information: Report properties set 'Language' setting to '=User!Language' to detect user culture automatically My regional settings are set to English (New Zealand) but whatever I do I receive no data - (data is definately there eg when run without parameters) so it is obviously the dates incorrect. Can someone give me some advice on what else to check. Dianne,  I ...Show All

• Polity4h Stuck between "Failed to generate user instance" and "An attempt to attach...failed"

  I wrote an application using Visual Studio 2005 beta 2 which uses a SQL Express .mdf file, included in the project, for the database.  After installing Visual Studio 2005 RC and the SQL Express that comes with it (I followed all of the uninstall instructions first) I can no longer add a SQL Express database to any of my projects, nor can I open the SQL Express database in my original project.  When I try either of these tasks, I get the "Failed to generate user instance of SQL Server due to a failure in starting the process for the user instance.  The connection will be closed."  I read the thread which dealt with this error, and changed the connection string for the database file that had worked before to User Instance ...Show All

• WestyManBC datetime format

  Hi. I have two parameters called StartDate and EndDate.These parameters are from datetime type. I want to view the records between these dates.I have some questions: 1)In the database, these parameters' values are like 15.11.1984 23:59:14. It has time value near the date value.But I don't want to view the time value.I only want the date part. 2)In the preview tab, I choose a date clicking the calendar image near the parameter textbox. For example I choose 02.05.2001 and when I click the view report button, it changes to 05.02.2001.So there is a format difference.I want it to show like dd.mm.yyyy 3)By default, if the user doesn't enter a date, I want to view all the records.Any idea about this Thanks! k ...Show All

• Fahd Job scheduling for Packages in SQL Server 2005

  ---------------------- MS Win XP Pro 2002 SP2 MS SQL Server 2005 MS Visual Studio 2005 ---------------------- Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005. I've configured providers logging, but still the error file doesn't give me any explanation why the error happens: "#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message OnPreValidate,PC1234,NT AUTHORITY\SYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)" I've also done the same on the Step properties under 'Logging' In Manageme ...Show All

• scottsignalscape Blank password works for user account - all other passwords don't!

  Hi Group (and thanks for any help given): I am new to SQL 2005 and have an issue with passwords. I set the password on a user account and tried to access my program (Time Matters.) I received an error message that the application was unable to connect to the system database. When I set the password to blank (no password), the application was able to connect to the SQL db and everything worked just fine. I've tried several different passwords. Any password besides blank does not work. Blank always does. Obviously I don't want to use a blank password. But where in SQL Server is the setting or could a copy of the original password be "stuck" Thanks for your help. Jeff what client appli ...Show All