How to debug database engine stored procedures on SQL Server 2005?

SQL Server Database Engine

Index ‹ SQL Server

- Previous
  - 1
    - Using Integrated Security from .Net Web to access SQL Server 2005 Well, then you will have to set the SPN (service principal name) for security delegation. http://support.microsoft.com/kb/319723 Jens K. Suessmeyer --- http://www.sqlserver2005.de --- Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 2
    - Accessing the underlying sql query from the rdl file Our client uses the report builder to generate reports for collection of employees. We would like to use the employee records in this report to perform some additional processing (such as the list of employees gets assigned to a particular group). Programmatically, I can retrieve a byte stream from rs.GetReportDefinition( "\myClientEmpReport") and deserialize the stream into Report object (as define by ReportDefinition.xsd). I can then manually drill down and retrieve the SematicQuery xml from the commandText field. The problem is how to convert the SemanticQuery format into a T-Sql query that I can run against the view that the report model is based off of. Is this possible Thanks, Arjay P.S. Running SQL 2005 Reporting Services, VC# 2005, ASP.Net 2.0. Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 3
    - IBM DB2 as Data Source Hi, I installed the OLE DB Provider for DB2 from the link provided. However, when I am trying to create a new connection using the "Connection Manager", I am getting the following error: "Test connection failed because of an error in initializing the provider. The parameter is not correct." I have provided the Server IP address in the "Server or filename" field and a specific user name and password. Interestingly, I am able to connect to the same server in my DTS package using the iSeries Access ODBC driver (by creating a system DSN). I am using SQL Server 2005 Developer Edition - is that the problem Regards, B@ns Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 4
    - Reporting Services 2005 Subreport issue... Maybe I'm not fully understanding the problem, but why can't you use navigation from the subreport back to the parent report and simply pass the value that you need back as a parameter Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 5
    - SSIS Data Conversion: Convert a String (like yyyymmdd) into Datatype dt_DBDATE or dt_DBDATE Hi Rafael, Thank your for the link to the thread. The urgent thing I found: "It's vitally important to have both a success path and an error path coming from your Derived Column transform." I configured an ErrorOutput and now it works!!! Great. ulrikeG Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 6
    - SQL Compilation and execution plan Hi all, I’m having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer: QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2). Is SQL server working the way expected or am I missing something Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 7
    - SQL Reporting Services - Page X of XX counts in header Thank you for responding to my question. The issue I am finding with row count is that the rows in a group can vary. I need a group to end (total) on one page and start the next group on the following page (keep groups together). I can also have some variation in row height for txt wrap. I have been playing with this. I was able to place a textbox in the header and point to the report item value and all looked great, page X of XX. Then I checked out the print rendering and yikes, it is all over the place. Thats where I am at right now. I will need to export to PDF and Excel too. I have another suggestion to try using a subreport, so I will look into that as well. If you can think of anything else please advise. Thanks! Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 8
    - Export to Excel Try to merge all the cells in the header. If it was a list you had to make sure that all fields (header and data) are vertically aligned i.e. same Y-Axis location. Shyam Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 9
    - ?? 933097 Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2 is available I've read about this hotfix at http://www.kbalertz.com/Feedback_933097.aspx but I can't find anything on microsoft.com about it. It sounds like a worthwhile hotfix, but I'm wondering about the absence of an announcement on microsoft.com. Anyone else know about this hotfix Thanks! Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 10
    - Layout question We have a report that is printed landscape and contains a main table with values. Inside this table we also want to create a "sub-report" table based on two other criteria (charges and credits), but this data is still grouped with the main table. Here is a simple schematic of the report XXXXXXX XXXXXXXX XXXXXXXXXX XXXXXXXXX Charges XXXXXXXXX XXXXXXXXX Credits XXXXXXXXX XXXXXXXXX XXXXXXX We have placed a rectangle in the last column of the main table to accomodate two other "sub-tables": one for Charges and the other for Credits. We need to keep the heighth of each row in the main table the same (without extra white space) so we cannot move the second table for Credits below the table for charges. I hope this makes sense and anybody with any suggestions, it is appreciated. Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 11
    - Flat File Data Source with variable number of delimited columns I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file). Is it possible to use a single flat file data source that will work with all of the different width files Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 12
    - Stuck on a Inner Query Guys- i notice you're using DATEDIFF(YY,DOB,GETDATE()) < 18. Doesn't this just do the difference in the year dateparts of a date To actually do ages, i think you'd need to do something along the lines of: dob > dateadd ( yy , - 18 , convert ( varchar , getdate ()) Make sense Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 13
    - help with WHERE clause I have a situation where I want to be able to match an exact string value and if that is unsuccessful then match all strings like the search parameter. The scenario I'm working on right now is searching for users by username. An example for simplicities sake is if I have the user "admin" and "administrator" in a database, if I search for "admin", I only want that single user to be returned, however if I searched for "adm", I want both users to be returned. Here's the approach I've taken so far: SELECT * FROM Users WHERE ((@Username IS NULL) OR ((Username = @Username) OR (Username LIKE N'%' + @Username + N'%'))) I expected that if (Username = @Username) was found, a single row would be returned and the rest of the clause would be ignored. However, it seems as though the LIKE is still being executed because if I search for "admin", I still get two rows returned even though I only want the single row with the exact username. What do I need to do to achieve the results I'm looking for Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 14
    - SQL in OLE DB Just select the table that you want to use as your lookup. Select the checkbox for the key that you will be matching on. Don't mess with the SQL at all. Then, map the input key column to the chosen key column from the lookup table. Stop there. That's all you have to do. The green arrow coming out of the lookup transformation is for matches (typically used for updates -- because the data exists). The red arrow coming out of the lookup (usually an error output) indicates that a match was not found and is usually used for inserting data that does not exist. Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 15
    - Unused Connection Managers In short, yes. You would have one config setting that would be set independent of the rest, usually as an environment variable. If you search the forums there are plenty of threads on this subject already... Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 16
    - Please help me SSIS... Are the source and destination tables in the same database, different databses or on different servers Tag: How to debug database engine stored procedures on SQL Server 2005?
- Next
  - 1
    - using uniqueidentifier datatype Thanks Manivannan ! Your replay was a good help for my searching. Thanks Maju. ---------------------------------------------------------------------------- Learn as if I were to live until death. after that Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 2
    - Error 18210 Backup up SQL Server 2005 Database to network drive Backing up database to a UNC path is fully supported. You must be thinking of running the database from a network drive. http://support.microsoft.com/kb/304261 http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 3
    - Adding records Sorry, didn't think about the VB 6 issue. I think the only way forward will be to use pConn.Execute with an INSERT statement. Where does the error occur - when calling .AddNew or .Update Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 4
    - Value of next record up Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 5
    - Date filter in MDX The sample you posted only had one side converted to a date, the other was a string value. The example you posted has >= "2/6/2007" as the first comparison, which is a comparson to a string value where as if you look at what suggested in my last post it was to use something like: >= CDATE("2/6/2007") I tested this in SP2 and it works for me. Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 6
    - ADO:Dispaly Limited Records Hi all; qrymillcgetbytype_app ( stored query in MS-Access) in my code : MS-ACCESS Part: query defination PARAMETERS prmMillToolType Long; SELECT MILLC.ID, MILLC.InsMillBdyID AS [Tool ID], MILLC.SubType, MILLC.Radius AS [End Radius], MILLC.CuttingDia AS [Tool Dia], MILLC.EffAxlCutLen AS [Effec Cut Length], MILLC.OverallLen AS [Overall Length], MILLC.HandOfCut AS [Hand Of Cut], MILLC.NoOfFlutes AS [No Of Flutes], MILLC.TmcID AS [Tool Class], MILLC.Comment, MILLC.Protrusion, MILLC.ShankDia, millC.ShoulderLen AS [Shoulder Length] FROM MILLC WHERE (((MILLC.[Mill Tool Type])=[prmMillToolType]) AND ((MILLC.[ON])=True)) ORDER BY MILLC.ID; C++ PART bstrSQL contain stored query name "qrymillcgetbytype_app" hr = piRecordSet->put_Source(bstrSQL); piRecordSet->Open(vNull, vNull, adOpenKeyset, adLockOptimistic, adCmdUnknown) it opens fine but when bstrSQL contain this query name " qrymillcgetbytype_app WHERE ID > -1 AND [Tool Dia] >= 0.000000 AND [Tool Dia] <= 5.000000 " it displays the same output. Where clause won't work Pls help me out. Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 7
    - Property DefaultSchema is not available for Database [DatabaseName] Unfortunately, we cannot design a solution assuming that group structures will be well designed and that users can be members of at most one group in any database. So, in SQL Server 2005, setting a default schema for a group is not supported. The problem is that a default schema is a property, not a privilege, and is therefore not cumulative. Given two different default schemas, we cannot predictably choose one. Thanks Laurentiu Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 8
    - Performance In Simple Scenarios I have done some performance testing to see if asynchronous triggers performs any better than synchronous triggers in a simple audit scenario -- capturing record snapshots at insert, update and delete events to a separate database within the same instance of SQL Server. Synchronous triggers performed 50% better than asynchronous triggers; this was with conversation reuse and the receive queue activation turned off, so the poor performance was just in the act of forming and sending the message, not receiving and processing. This was not necessarily surprising to me, and yet I have to wonder under what conditions would we see real performance benefits for audit scenarios. I am interested if anyone has done similar testing, and if they received similar or different results. If anyone had conditions where asynchronous triggers pulled ahead for audit scenarios, I would really like to hear back from them. I invite any comments or suggestions for better performance. The asynchronous trigger: Code Snippet ALTER TRIGGER TR_CUSTOMER_INSERT ON DBO.CUSTOMER FOR INSERT AS BEGIN DECLARE @CONVERSATION UNIQUEIDENTIFIER , @MESSAGE XML , @LOG_OPERATION CHAR(1) , @LOG_USER VARCHAR(35) , @LOG_DATE DATETIME; SELECT TOP(1) @CONVERSATION = CONVERSATION_HANDLE , @LOG_OPERATION = 'I' , @LOG_USER = USER() , @LOG_DATE = GETDATE() FROM SYS.CONVERSATION_ENDPOINTS; SET @MESSAGE = ( SELECT CUST_ID = NEW.CUST_ID , CUST_DESCR = NEW.CUST_DESCR , CUST_ADDRESS = NEW.CUST_ADDRESS , LOG_OPERATION = @LOG_OPERATION , LOG_USER = @LOG_USER , LOG_DATE = @LOG_DATE FROM INSERTED NEW FOR XML AUTO ); SEND ON CONVERSATION @CONVERSATION MESSAGE TYPE CUSTOMER_LOG_MESSAGE ( @MESSAGE ); END; The synchronous trigger: Code Snippet ALTER TRIGGER TR_CUSTOMER_INSERT ON DBO.CUSTOMER FOR INSERT AS BEGIN DECLARE @LOG_OPERATION CHAR(1) , @LOG_USER VARCHAR(15) , @LOG_DATE DATETIME; SELECT @LOG_OPERATION = 'I' , @LOG_USER = USER() , @LOG_DATE = GETDATE() INSERT INTO SALES_LOG.DBO.CUSTOMER SELECT CUST_ID = NEW.CUST_ID , CUST_DESCR = NEW.CUST_DESCR , CUST_ADDRESS = NEW.CUST_ADDRESS , LOG_OPERATION = @LOG_OPERATION , LOG_USER = @LOG_USER , LOG_DATE = @LOG_DATE FROM INSERTED NEW END; Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 9
    - Append one Table one with Table Two Thank you, that really helps to see what we are working with. I noticed that in [Main Table], [IR Number] can be NULL, while in [IncidentReports], [IR Number] is NOT NULL. Could there be NULL values in the [Main Table] for [IR Number] If so, attempting to add those record to the reporting table will fail due to the NOT NULL constraint. Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 10
    - Outputting ￡ sign to file using BCP problem............ can any1 please help me here as it's driving me mad!!! I've got a bcp command which outputs to file from a query. One of these fields is a nvarchar that contains text including the ￡ sign. Whenever i output it to file it changes the ￡ to "\x9c" (black square) in PFE or " " in Notepad. I've tried changing this to a $ sign and it outputs fine. The script works by taking various information / hardcoded text and inserting to a temporary table then outputs to file. I've checked the information in the temporary table and it displays the ￡ no problem, its just seems to go crazy when outputting to file! I've checked the regional settings and everything is set to UK and currency ￡'s. Am i missing something here the output part of the script i'm running is: ................. ................. DECLARE @FileName varchar ( 50 ), @bcpCommand varchar ( 200 ) SET @FileName = 'e:\' + CONVERT ( char ( 8 ), GETDATE (), 112 )+ '_ITEMPOSIDENTITY.0' SET @bcpCommand = 'bcp "SELECT data FROM ##itemp" queryout "' SET @bcpCommand = @bcpCommand + @FileName + '" -U -T -P pw -c' Whilst i'm on, can any1 tell me why i can't use a local temporary table with BCP I've had to use a global temp because during the stored procedure it finds the table fine but when it comes to outputting the query in BCP it says it can't find it any help much appreciated!!! Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 11
    - Excel error when click to drilldown the dimension! Hi, This is my problem: I have a cube that I created in AS2005, I have a hierarchy and drillthrough action defined. When I create a pivot table in Excel 2007 based on that cube and I try to drill down from dimension on the cell in the pivot table I can not drill down to the next level. I get an Excel error. This dimension I had to build from parent-Child method, but another aslo build from this method and it work ok can drilldown to the next level. Same in the BIDS, I can see and drilldown it. Another way, I had to use ProClarity to browse this cube and try to drilldown on this dimension, it can drlldown normally. I don't know what something wrong. Any help is greatly appreciated!! rgds., Fu Cherming Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 12
    - Error deploying large Report Model Hi, I also want to know the location of web.confing if i am doing right!! on my system i have web.config and web.config.comment. and there is only MaxRequestLenght in .comment file and i updated that to 102400!! also the execution time out to 1200: the location is: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 Thanks Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 13
    - loop through all stored procedures in database and modify. You can get the script of this storedprocedure to a file using SQLCMD. but replacing the text u will have to do by .NET program. read about SQLCMD SQLCMD -q"SP_HelpText 'Your SP Name'" -o "Outputfile.sql" Madhu Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 14
    - Cannot get Report Server to work Perhaps it has something to do with the line: Evaluation copy: 0 days left Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 15
    - DBNull Error Am getting errors on this syntax: Public Overrides Sub Input0_ProcessInputRow( ByVal Row As Input0Buffer) Row.myColumn = DBNull.Value Value of type 'System.DBNull' cannot be converted to type 'String' Any ideas Just want to set the myColumn to NULL. Thanks Tag: How to debug database engine stored procedures on SQL Server 2005?
  - 16
    - Parameters in a OleDb Command Transformation Hello again, Thank you Phil and Rafael for the quick reply... This solves my problem... I didn't know I could "return" a dummy that way... And it also improves the overall performance. Thanks for the help Andre Tag: How to debug database engine stored procedures on SQL Server 2005?

UncleSam89

1). When you right click stored procedure in the Query Analyzer on 2000 you can select debug from the list of menues.

I can't find this functionality in the SQL Server Management studio.

2). By the way I also can't find where went the output of my print statement.

3). Does server cursor functionality is still working in 2k5

Re: How to debug database engine stored procedures on SQL Server 2005?

Arnie Rowland

No Query Analyzer anymore. The debug functionality has 'moved' into Visual Studio.

1. Try this resource: http://msdn2.microsoft.com/en-us/library/ms165034(VS.80).aspx

2. Print statements show in the 'Messages' pane.

3. Yes, cursors still function the same.

Re: How to debug database engine stored procedures on SQL Server 2005?

Jens K. Suessmeyer

Hi,

1) You will need Visual Studio for this.
2) it will be printed out in the messages tab (if you used the Table mode) or in the Results Tab for text mode

3) Yes, this still works although you should always try to use a setbased approach where possible.

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---