How to debug CLR SP using Visual Studio 2005?

.NET Framework inside SQL Server Katmai

Index ‹ SQL Server

- Previous
  - 1
    - Using Database Mail to notify job failures help? How do I get the option my database mail profile to appear in the drop down list on the "Notifications Page" in my job properties I have configured the mail profile and sucessfully sent a test E-mail. I have also set "Enable mail profile" in the SQL Agent Properties and restarted the agent. What else do I need to do, help please Tag: How to debug CLR SP using Visual Studio 2005?
  - 2
    - what permission do I need to grant ? hello, what permission do I need to grant to a user to let him to read a table in a linked server object Thanks Tag: How to debug CLR SP using Visual Studio 2005?
  - 3
    - Importing Data Into XML Column - UPDATED Okay...I as actually able to get fairly far in my attmepts to copy data from a SQL Server table to an XML column. Here is the XSD I created: < xsd chema xmlns:xsd = " http://www.w3.org/2001/XMLSchema " > < xsd:element name = " CustomColumns " > < xsd:complexType > < xsd:complexContent > < xsd:restriction base = " xsd:anyType " > < xsd equence > < xsd:element name = " CN_CUST_KEY " nillable = " true " > < xsd impleType > < xsd:restriction base = " xsd:float " /> </ xsd impleType > </ xsd:element > < xsd:element name = " ITM_SUF_NO " nillable = " true " > < xsd impleType > < xsd:restriction base = " xsd:float " /> </ xsd impleType > </ xsd:element > < xsd:element name = " Model " nillable = " true " > < xsd impleType > < xsd:restriction base = " xsd tring " /> </ xsd impleType > </ xsd:element > < xsd:element name = " Serial " nillable = " true " > < xsd impleType > < xsd:restriction base = " xsd tring " /> </ xsd impleType > </ xsd:element > < xsd:element name = " SaleYear " nillable = " true " > < xsd impleType > < xsd:restriction base = " xsd:int " /> </ xsd impleType > </ xsd:element > < xsd:element name = " SaleDate " nillable = " true " > < xsd impleType > < xsd:restriction base = " xsd ateTime " /> </ xsd impleType > </ xsd:element > < xsd:element name = " prd_itm_no " nillable = " true " > < xsd impleType > < xsd:restriction base = " xsd:int " /> </ xsd impleType > </ xsd:element > </ xsd equence > </ xsd:restriction > </ xsd:complexContent > </ xsd:complexType > </ xsd:element > </ xsd chema > This is the SQL query I'm running against the source table: INSERT INTO [clmnt] ( name1 , addr1 , addr2 , city , [state] , zip5 , zip4 , CustomColumns ) SELECT LastName , StreetAddress1 , StreetAddress2 , City , [State] , Postal5 , Postal4 , ( SELECT CN_CUST_KEY , ITM_SUF_NO , Model , Serial , SaleYear , SaleDate , prd_itm_no FROM TblClass_Customers FOR XML PATH ( '' ), ELEMENTS , ROOT ( 'CustomColumns' ), TYPE ) FROM TblClass_Customers ; ...and here is the error I'm getting: Msg 6965, Level 16, State 1, Line 1 XML Validation: Invalid content. Expected element(s) erial where element 'SaleYear' was specified. Location: /*:CustomColumns[1]/* aleYear[1] From what I can see, the SELECT statement matches up with the XSD I'm using...the datatypes coincide... Kind of at a loss here... This is what the contents of the target xml column should look like: < CustomColumns > < CN_CUST_KEY > XXXYYYZZZZ </ CN_CUST_KEY > < ITM_SUF_NO > 45 </ ITM_SUF_NO > < Model > alpha </ Model > < Serial > djdjxh3455skx </ Serial > < SaleYear > 2005 </ SaleYear > < SaleDate > 10/25 </ SaleDate > < prd_itm_no > df345f </ prd_itm_no > </ CustomColumns > Tag: How to debug CLR SP using Visual Studio 2005?
  - 4
    - Passing Parameter to another Report using Jump to URL option in Reporting Services 2005 Hi, I tried to pass parameter from one report to another report. I can send the parameter using following option: I used jump to url option and write the following expression: ="javascript:void(window.open('http://hpsi-dev/Reports/Pages/Report.aspx ItemPath=%2fNextGen+Reports%2fMAUA%2fSales+Order+Detail&rs:Command=Render&SalesOrderNumber="+Fields!SalesOrderNumber.Value+"'))" and it shows me the following in browser url http://hpsi-dev/Reports/Pages/Report.aspx ItemPath=/NextGen+Reports/MAUA/Sales+Order+Detail&rs:Command=Render&SalesOrderNumber=SO43667 now the problem is how to get this ordernumber in my report any option pls urgent...any javascript function to take this no into my another report Tag: How to debug CLR SP using Visual Studio 2005?
  - 5
    - SQL 2005 Cannot Drop User Hello, I am new to SQL 2005 and I am trying to drop a user from a database called prod. When I try to delete the user I get the following error message: "The database principal has granted or denied permissions to objects in the database and cannot be dropped." Any help would be greatly appreciated. Thank you. Tag: How to debug CLR SP using Visual Studio 2005?
  - 6
    - Updating only changed fields My client wants to minimize concurrency issues by only updating fields that the user actually changed. Whereas most update stored procedures I have created just updated all of the fields WHERE key=value, they want to update individual fields. What I came up with so far does not look very efficient and I was wondering if anyone has suggestions for improvement. Here is a snippet from the stored proc script: Code Snippet CREATE PROCEDURE dbo.DriverSaveChangedColumns @DriverNumber VarChar (10) OUTPUT , @CompanyName VarChar (50), @DriverName VarChar (50), @Address1 VarChar (50), ... IF @CompanyName <> @Org_CompanyName BEGIN UPDATE Driver SET DR_CompanyName = @CompanyName WHERE DR_DriverNumber = @DriverNumber AND DR_CompanyName = @Org_CompanyName END IF @DriverName <> @Org_DriverName BEGIN UPDATE Driver SET DR_DriverName = @DriverName WHERE DR_DriverNumber = @DriverNumber AND DR_DRiverName = @Org_DriverName END ... Any suggestions The only other thing I thought of is to forget the stored procedure and do inline SQL ... but I'd rather have a proc. Thanks! Tag: How to debug CLR SP using Visual Studio 2005?
  - 7
    - Should i upgrade to SP2 ? I am using sql 2005 Enterprise with SP1. And its in a clustered environment. I am planning to apply SP2. I would like to know if there are any issues with SP2. i am lil cocerened if it will affect anything. Should i upgrad to SP2 Are there any issues esp. in clusered environment It wold be really helpful if someone can share his exp. Thanks Tag: How to debug CLR SP using Visual Studio 2005?
  - 8
    - Which SQL Server 2000 Edition should I install in at UAT environment Hi, We have a suite of applications which go through the full product life cycle and I was wondering which SQL Server edition should be installed in every each environment from Proof of Concept -> Dev -> -> UAT -> Systest... Production is straight forward but I am just not sure about all the other environments. thanks Lyn Tag: How to debug CLR SP using Visual Studio 2005?
  - 9
    - Error processing Cube - The syntax for ',' is incorrect. Hi guys! Could you help me with the next problem: Failed to build the OLAP cubes. Error: Analysis Services session failed with the following error: Query (1, 10) Parser: The syntax for ',' is incorrect. I have installed sql2005 sp2, but didn't get much Pls. help Tag: How to debug CLR SP using Visual Studio 2005?
  - 10
    - Rerunning failed subscriptions We're using RS2005 SP1. One of our users has subscribed to a report to run the 15th of each month. This past July 15 was on a Sunday and our servers were down for backing up & maintenance. Since the servers were down, the job never ran and the user did not receive the report they were expecting Is there anyway to have jobs that failed to run because a server was down automatically run when the server comes back online Or perhaps have maintenance windows during which reports can not be scheduled to be run TIA -bruce Tag: How to debug CLR SP using Visual Studio 2005?
  - 11
    - SQL Server Data Migration and Synchronization Hi, I need to move my database servers from one location to another. The issue is that I have over 200 databases to move and my clients can't afford a downtime. The collective volume of all the databases is over 2.5 TB and growing. I am thinking to copy these databases in batches over the WAN to the new location and replicate them using Transactional replication till I have all the databases moved and synchronized. Will it be wise enough to use replication for synchronizing 200 databases or is there a better approach which I can use to move these databases with minimum downtime and compromise on performance of applications. Note: Migration is from SQL Server 2000 to SQL Server 2005. Thanks in advance. Ritesh Tag: How to debug CLR SP using Visual Studio 2005?
  - 12
    - Different Font size when browsing and printing a report ? Hi all, Is it possible to setup different font size when a user is browsing or printing a report I have a lot of data to print on a report and i need tu use a font size of 7pt which is fine for printing but when the user is looking at the report on the browser a font size of 7pt is a bit too small and the bold doesn't work. Tia Tag: How to debug CLR SP using Visual Studio 2005?
  - 13
    - Analysis Services - Step by Step: Errors related to feature availability and configuration On page 56 of SQL Server 2005 Analysis Services Step by Step, I tried to DEPLOY the project, and it failed with this msg: Errors related to feature availability and configuration: The 'Measure expressions' feature is not included in the 'Standard Edition' SKU. Is it because of my Standard Edition If it is, could I change the 'Measure expressions' some how to get by this msg and go on with the book Tag: How to debug CLR SP using Visual Studio 2005?
  - 14
    - SSIS Job Failed I created a simple SSIS package. I build the package. I can run the package with no problem. However, when I schedule a job, the job always fail. Does anyone know what is the problem The error message of the job doesn't help at all. Here is the error message I got from job history: Date 5/19/2006 2:02:21 PM Log Job History (ssistest) Step ID 0 Server ABC Job Name ssistest Step Name (Job outcome) Duration 00:00:01 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message The job failed. The Job was invoked by User CHICAGO\ABC. The last step to run was step 1 (ssistest). I tried to enable logging of my SSIS. I didn't see any additional message. Does anyone know the problem Thanks. Renching Tag: How to debug CLR SP using Visual Studio 2005?
  - 15
    - Creating a Mobile Application with SQL Server Compact Edition Hello!! I completed that example that I pasted in the subject part and when I try to synchronize my mobile database, the data from the server appear in my pocket pc; but when i refresh the data on my pocket pc they do not show on the server. Can anyone give me a hand thanks Tag: How to debug CLR SP using Visual Studio 2005?
  - 16
    - Use of a SSIS variable of type “Object” inside Script Component and Task Component In a Data Flow, I have the necessity to use a SSIS variable of type “Object” inside Script Component and assign to it the content of 'n' variables of string type. On exiting from the script the variable of type object should contain something like in the following lines: AAAAAAAAAAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBBBBBBBBBBBBB CCCCCCCCCCCCCCCCCCCCCCCCCCCCC DDDDDDDDDDDDDDDDDDDDDDDDDDDDD …………………. …………………. On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion. Is there anyone who have experienced something like this Could anyone provide any example of that Thanks in advance! Tag: How to debug CLR SP using Visual Studio 2005?
- Next
  - 1
    - reporting and monitoring Hey all I am new here and fairly new to SQL server. And yes i have a question, that maybe dumb. My boss wants me to reporting to him the health of the DB each week. So here are a few questions. 1. What really defines the "health" of a DB What elements should I be reporting to him 2. Is there any utilities out there that would be helpful or can SQL provide enough info 3. I am using 2005 standard, so I have the shipped reports in summary page. But is this enough and can I customize them Sorry I am having a hard time even asking the questions cause I am not really sure what to ask. Any help or pointing down t he correct path would be greatly appreciated. Thanks, jason and what about 3rd party utilities such as this>>>> Tag: How to debug CLR SP using Visual Studio 2005?
  - 2
    - SQL 2005 Query Analyzer I just installed SQL2005 Workgroup edition. How do I install Query Alayzer to go along with this Tag: How to debug CLR SP using Visual Studio 2005?
  - 3
    - "Can't find PInvoke DLL 'dbnetlib.dll'." every time when I opened the connection from my windows CE4.2 device to sql server I got the error "Can't find PInvoke DLL 'dbnetlib.dll'. My OS is MS CE4.2, I've installed Compact Framework 2.0 and SQL client. It worked fine at OS CE5.0 but not at CE4.2. My application is working correctly, but when I started the connection I received this error. Any idea thanks, Jan Tag: How to debug CLR SP using Visual Studio 2005?
  - 4
    - Number of reads in Profiler is not the same when running the same SP on different PCs I'm running the same query on two different PCs and tracing results in Profiler on my PC. When executing the query on PC1 - the total number of reads is 200000. When executing the same query on PC2 - the toal number of reads is 13000. It is almost 15 times more reads when executing query on PC2. The executed query is same on PC1 and PC2. Any reason for this I'm trying to analyse that query and reduce the number of logical reads as it's is too high but then I get completly different result on different PC. Thanks. Tag: How to debug CLR SP using Visual Studio 2005?
  - 5
    - Problem when exporting data into Excel file from SSIS I have problems when exporting data into Excel file from SSIS. It all works fine with numeric columns but an apostrophe is attached at the beginning of each text cell. I tried using derived columns and data conversions but it didn't work. It seems to me that problem is in 'excel destination' task... I saw many people had this kind of problems too... Is there any solution possible Thanks. Tag: How to debug CLR SP using Visual Studio 2005?
  - 6
    - adding a new data flow item calendar transformer http://www.microsoft.com/downloads/details.aspx familyid=e603bde7-44bb-409a-890f-ed94a20b6710&displaylang=en#top I've downloaded this and installed it but i seems to fail to get this item into my data flow items list. i've read the readme.txt but i think the part where they explain the build is poorly explained. For example - Place gacutil.exe (packaged with Visual Studio) on the system path. --> wtf Anyone can help me get this component in my visual studio working Thanks Tag: How to debug CLR SP using Visual Studio 2005?
  - 7
    - Find and delete reversed key pairs. Hi! I have a table with only two columns, which contain some city codes in the form of AAA, BBB. What I need to do is to find reversed key pairs in the same table and get rid of them so only one pair stays. In example, I have: AAA, BBB CCC, DDD BBB, AAA EEE, FFF FFF, EEE DDD, CCC AAA, BBB and BBB, AAA mean the same to me, so I would like to leave only one of them. Do you have any idea on how to achieve this I tried to use a cursor (yeah, I know cursors are bad, but this is a task I will most likely run once or every few weeks), but the table has over 130k records and it looks like I need at least a few hours for the cursor to complete it's work. Here's what I did: Code Snippet declare @origCity char(3) ,@destCity char(3) declare crs_cities cursor local fast_forward for select origCity, destCity from dbo.airports order by origCity open crs_cities fetch next from crs_cities into @origCity, @destCity while @@fetch_status = 0 begin -- Delete rows having the same city pair in reverse order. delete from dbo.airports where destCity = @origCity and origCity = @destCity fetch next from crs_cities into @origCity, @destCity end close crs_cities deallocate crs_cities I could also add an autoincrement column before the pairs and use a while loop instead of a cursor, but there must be some SQL query to do it the proper way. It may be a whole procedure, whatever. Thanks. Tag: How to debug CLR SP using Visual Studio 2005?
  - 8
    - Can I create a database schema from a DataSet and/or .xsd file? I have some questions about creating SQL Server CE databases. Based on my experiments and what I've read on these forums, it looks like there are a couple ways to create a database schema. I can edit the database schema via the Server Explorer in Visual Studio, or use an external program like SQL Server Management Studio and somehow convert those files to .sdf files. I find Visual Studio's built in tools to be cumbersome to use and limited in functionality, and using SSMS seems like a roundabout way of approaching the problem. I understand Microsoft will be releasing better tools with Orcas, but in the meantime, I'm wondering if there are alternative ways to generate database schemas. For instance, I find Visual Studio's DataSet designer fairly easy to use. The DataSet designer generates schema definitions (.xsd files), and an instantiated DataSet can both read and write schema definitions via Read/WriteXMLSchema. Furthermore, DataAdapter's Fill and FillSchema methods can be used to push a schema from a database to a DataSet. So, can I somehow go the other direction and push a schema from a DataSet to a database It seems like all the tools are there... For example, if I create the DataSet schema, could I use a small app to create a new .sdf file, instantiate a DataSet, write the schema from the DataSet to the database, and then save the .sdf file Or given the generated .xsd file, is there any way to create a SQL database from that Thanks in advance for any replies. Tag: How to debug CLR SP using Visual Studio 2005?
  - 9
    - Can't reinstall sql server 2005 express Hi I installed SQL server 2005 Express and it was successfully set up but I kept on getting error 26 when I try to connect to a database, so I remove all SQL programs from the control panel and reinstall SQL server 2005, but this time the set up stopped halfway saying "cannot start server". How can I successfully reinstall SQL server 2005 express Tag: How to debug CLR SP using Visual Studio 2005?
  - 10
    - How to protect my parameters Hello. I have a report with parameter called "parm1", that gets a value of "true" or "false" depanding on another parameter. When the report is runnig the parm1 value is "false". How can I protect this parameter from a change by the user I mean - the user can run the report and then add to the url "&param1 = true". Can I do anything against that I tried marking it as "internal" and I thought that now it can get his value only from inside the report but it didn't worked. Any ideas Thanks. Tag: How to debug CLR SP using Visual Studio 2005?
  - 11
    - Check to see if file exist on server Hi Folks, I'm attempting to check to see if a file exists on our server before showing a link it. I put this function in the Report Properties: Code Block Function isFile(PathFileName As String) As Boolean Return isFile = System.IO.FileExists(PathFileName) End Function I chose a situation where I'm getting back two rows of data, one row is true and the other row is false however, when I run the report they're both coming back as 'True'. We created a virtual directory to the file system that houses our files, so my PathFileName variable will contain something like http://Sales/North/Sales110907.pdf . Can I use the FileExists in this fashion or am I going down the wrong path I appreciate any help you can give. Best regards, Tag: How to debug CLR SP using Visual Studio 2005?
  - 12
    - error hi i have one varchar field i m convarting that field to datetime in format of dd/mm/yyyy but i m getting eror select top 30000 CMS_Upload_Details_ID,Scheme_Code,DrCr, convert(varchar(10),cast(AdditionalField5 as datetime),103) 'ValDate' ,AdditionalField13 'dept_slip',AdditionalField14 'dept_dt',Cheque_No 'Instrm_No',Amount,CONVERT (varchar(11),Instrm_Date,103)Instrm_Date ,AdditionalField21 'Drawer Name' from Tbl_CMS_UploadDetails with(nolock) where Compare_Status='Pending' and ltrim(rtrim(CMS_Upload_Details_ID)) not in (select top 0 ltrim(rtrim(CMS_Upload_Details_ID)) from Tbl_CMS_UploadDetails with(nolock) where Compare_Status = 'Pending ' and Format_ID =83)and Compare_Status='Pending' and Format_ID =83 error is Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Tag: How to debug CLR SP using Visual Studio 2005?
  - 13
    - Formating how to format an integer in sql 2005 for ex: 1.09876 i want to format to 1.1 in access i can use : format(field,'0.0') as field1 thanks Tag: How to debug CLR SP using Visual Studio 2005?
  - 14
    - sql cluster - fulltext error We have a sql server 2000 active/passive cluster running on Windows Server 2003 and within the last 6 months have started getting the following errors which eventually cause the SQL Resource Group to failover. An error occurred in the configuration monitor for instance <SQL Server Fulltext>: 80070005 - Access is denied . SQL is at the latest service pack/hotfixes. (8.00.2187) This cluster has been running fine for the past 3 years with no problems. Nothing has changed on the servers. We've had Symantic Antivirus running on the servers for a long time but the FTDATA folder and the sql data and transaction logs are excluded and nothing has changed on the configuration. We also run Diskeeper 2007 server on these servers but I can't see any problems with this. Once it fails over it will run fine for several days and then the same errors will occur and it fails over again. Any ideas on what might be causing this I can't really find anything on the Microsoft site that matches this error. Note: This has also just started happening on our test cluster which is an identical configuration. Tag: How to debug CLR SP using Visual Studio 2005?
  - 15
    - MSSQLServerOLAPService Stops MSSQLServerOLAPService Service seems to crash rather sporadically during our nightly ETL with the error of: The description for Event ID ( 22 ) in Source ( MSSQLServerOLAPService ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Internal error: An unexpected exception occured. Internal error: An unexpected exception occured.. The time of the error in the event viewer seems to correspond with the step in the ETL that processes our AS 2005 database (Analysis Services Command Task). The job step reports success, and the data is refreshed so I don't understand why it is reporting an error in Event Viewer. The server is x86 with SQL 2005 sp2 installed. Has anyone else run across this Tag: How to debug CLR SP using Visual Studio 2005?
  - 16
    - How to Add Subscription or Subscribers manually through scripts instead of programming Hello everyone, I am new for SQL SERVER 2005 NS. I have gone through some samples about NS. Most of them add subscriptions and subscribers through vbs or .net application. So I got some questions to ask for your help: 1. I just wonder if there are some ways for me to add subscriptions and subscribers manually through T-SQL scripts instead of programming. Because sometimes, we don't need to ask our users to subscribe our message. 2. Is that possible for NS to send notification by SMTP without adding subscription For example, I have already populated all emails from database, what I need is just to send simple notes to these emails, and at this time, I don't need users to subscribe them. I just need to get NS works as database mail at this point - only send email notification without subscription. I am not sure if this is possible. ( I know with databse mail, this can be done easily, how about with SQL NS, any idea ) Would you please give me some advices I appreciate your help in advance. Best Regards, Tag: How to debug CLR SP using Visual Studio 2005?

Alexey Yeltsov - MSFT TAP custmr

We know that automatic deplyment is currently broken.

How does one debug the stored procedures in this case

Thanks,

Alexey

Top

Re: How to debug CLR SP using Visual Studio 2005?

Alexey Yeltsov - MSFT TAP custmr

Ok I got it.

I have to manually load .PDB as well:

CREATE ASSEMBLY ExecuteExternalReturnTable

FROM '<...>\ExecuteExternalReturnTable\bin\Debug\ExecuteExternalReturnTable.dll'

WITH PERMISSION_SET = UNSAFE;

ALTER ASSEMBLY ExecuteExternalReturnTable ADD FILE

FROM \'<...>\ExecuteExternalReturnTable\bin\Debug\ExecuteExternalReturnTable.pdb'

Then I can attach to SQL Server and my breakpoints will work.

Top